-- Biometric Device Integration - Phase 1
-- Date: 2025-11-17
-- Description: Create tables for biometric device management and audit logging

-- ============================================================================
-- 1. BIOMETRIC DEVICES REGISTRY
-- ============================================================================
-- Devices must be pre-registered before they can connect
CREATE TABLE IF NOT EXISTS biometric_devices (
    biometric_device_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    device_sn VARCHAR(50) UNIQUE NOT NULL,
    device_type VARCHAR(20) CHECK (device_type IN ('F500', 'M50', 'M91')),
    cloud_id VARCHAR(100),
    token UUID DEFAULT gen_random_uuid(),
    branch_id UUID REFERENCES branches(branch_id) NOT NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    approved BOOLEAN DEFAULT TRUE,
    is_online BOOLEAN DEFAULT FALSE,
    last_seen TIMESTAMP,
    websocket_session VARCHAR(100),
    settings JSONB DEFAULT '{}',
    org_id UUID REFERENCES orgs(org_id) NOT NULL,
    createdBy UUID REFERENCES users(user_id),
    createdAt TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updatedAt TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    isDeleted BOOLEAN NOT NULL DEFAULT FALSE
);

COMMENT ON TABLE biometric_devices IS 'Registry of biometric attendance devices';
COMMENT ON COLUMN biometric_devices.device_sn IS 'Physical serial number from device (must be pre-registered)';
COMMENT ON COLUMN biometric_devices.token IS 'UUID token for device authentication';
COMMENT ON COLUMN biometric_devices.branch_id IS 'Branch/location where device is installed';
COMMENT ON COLUMN biometric_devices.approved IS 'Whether device is approved to connect and send data';
COMMENT ON COLUMN biometric_devices.is_online IS 'Current connection status';
COMMENT ON COLUMN biometric_devices.websocket_session IS 'Current WebSocket session ID if connected';

-- Indexes for performance
CREATE INDEX idx_biometric_devices_device_sn ON biometric_devices(device_sn);
CREATE INDEX idx_biometric_devices_branch ON biometric_devices(branch_id);
CREATE INDEX idx_biometric_devices_online ON biometric_devices(is_online) WHERE is_online = TRUE;
CREATE INDEX idx_biometric_devices_org ON biometric_devices(org_id);

-- ============================================================================
-- 2. DEVICE CONNECTION AUDIT LOG
-- ============================================================================
CREATE TABLE IF NOT EXISTS biometric_device_logs (
    biometric_device_log_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    biometric_device_id UUID REFERENCES biometric_devices(biometric_device_id) ON DELETE SET NULL,
    device_sn VARCHAR(50),
    event_type VARCHAR(50) NOT NULL,
    details JSONB DEFAULT '{}',
    org_id UUID REFERENCES orgs(org_id) NOT NULL,
    createdAt TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    isDeleted BOOLEAN NOT NULL DEFAULT FALSE
);

COMMENT ON TABLE biometric_device_logs IS 'Audit trail of device connection events';
COMMENT ON COLUMN biometric_device_logs.event_type IS 'register_attempt, login_success, login_rejected, disconnect, attendance_rejected';

CREATE INDEX idx_biometric_device_logs_device ON biometric_device_logs(biometric_device_id);
CREATE INDEX idx_biometric_device_logs_type ON biometric_device_logs(event_type);
CREATE INDEX idx_biometric_device_logs_created ON biometric_device_logs(createdAt DESC);
CREATE INDEX idx_biometric_device_logs_org ON biometric_device_logs(org_id);

-- ============================================================================
-- 3. TIME CLOCKS ENHANCEMENTS
-- ============================================================================
-- Add biometric device tracking and validation columns
ALTER TABLE time_clocks 
    ADD COLUMN IF NOT EXISTS biometric_device_id UUID REFERENCES biometric_devices(biometric_device_id),
    ADD COLUMN IF NOT EXISTS device_log_id INTEGER,
    ADD COLUMN IF NOT EXISTS verification_method VARCHAR(50),
    ADD COLUMN IF NOT EXISTS attendance_status VARCHAR(50),
    ADD COLUMN IF NOT EXISTS gps_latitude DECIMAL(10, 8),
    ADD COLUMN IF NOT EXISTS gps_longitude DECIMAL(11, 8),
    ADD COLUMN IF NOT EXISTS location_validated BOOLEAN DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS validation_notes TEXT,
    ADD COLUMN IF NOT EXISTS sync_status VARCHAR(20) DEFAULT 'synced';

COMMENT ON COLUMN time_clocks.biometric_device_id IS 'Biometric device that recorded this attendance';
COMMENT ON COLUMN time_clocks.device_log_id IS 'Device internal log ID for tracking';
COMMENT ON COLUMN time_clocks.verification_method IS 'How user verified: FP, FACE, CARD, PWD, QR, etc.';
COMMENT ON COLUMN time_clocks.attendance_status IS 'Status from device: Duty On, Duty Off, In, Out, etc.';
COMMENT ON COLUMN time_clocks.location_validated IS 'Whether employee was authorized for device location';
COMMENT ON COLUMN time_clocks.validation_notes IS 'Reason for rejection if location_validated is false';
COMMENT ON COLUMN time_clocks.sync_status IS 'synced, pending, failed, rejected, orphan';

CREATE INDEX IF NOT EXISTS idx_time_clocks_biometric_device ON time_clocks(biometric_device_id);
CREATE INDEX IF NOT EXISTS idx_time_clocks_user_date ON time_clocks(user_id, clocked_at);
CREATE INDEX IF NOT EXISTS idx_time_clocks_location_validated ON time_clocks(location_validated);
CREATE INDEX IF NOT EXISTS idx_time_clocks_sync_status ON time_clocks(sync_status);

-- ============================================================================
-- 4. GRANT PERMISSIONS
-- ============================================================================
-- Grant access to application user (adjust user name as needed)
DO $$
BEGIN
    IF EXISTS (SELECT FROM pg_roles WHERE rolname = 'taf_user') THEN
        GRANT ALL ON biometric_devices TO taf_user;
        GRANT ALL ON biometric_device_logs TO taf_user;
    END IF;
END $$;

-- ============================================================================
-- ROLLBACK SCRIPT (for reference)
-- ============================================================================
-- To rollback this migration:
-- DROP TABLE IF EXISTS biometric_device_logs CASCADE;
-- DROP TABLE IF EXISTS biometric_devices CASCADE;
-- ALTER TABLE time_clocks DROP COLUMN IF EXISTS biometric_device_id;
-- ALTER TABLE time_clocks DROP COLUMN IF EXISTS device_log_id;
-- ALTER TABLE time_clocks DROP COLUMN IF EXISTS verification_method;
-- ALTER TABLE time_clocks DROP COLUMN IF EXISTS attendance_status;
-- ALTER TABLE time_clocks DROP COLUMN IF EXISTS gps_latitude;
-- ALTER TABLE time_clocks DROP COLUMN IF EXISTS gps_longitude;
-- ALTER TABLE time_clocks DROP COLUMN IF EXISTS location_validated;
-- ALTER TABLE time_clocks DROP COLUMN IF EXISTS validation_notes;
-- ALTER TABLE time_clocks DROP COLUMN IF EXISTS sync_status;
