-- Professional biometric architecture: Devices / Enrollment / Attendance Logs

CREATE TABLE IF NOT EXISTS biometric_devices (
  id VARCHAR(50) PRIMARY KEY,
  device_id VARCHAR(100) NOT NULL,
  device_name VARCHAR(255) NOT NULL,
  brand VARCHAR(100),
  model VARCHAR(100),
  serial_number VARCHAR(150) NOT NULL,
  ip_address VARCHAR(45) NOT NULL,
  port INT NOT NULL DEFAULT 4370,
  communication_key VARCHAR(255),
  branch_id VARCHAR(50),
  timezone VARCHAR(80) NOT NULL DEFAULT 'Asia/Riyadh',
  provider_type VARCHAR(50) NOT NULL, -- zkteco, suprema, hikvision, anviz, tcpip, cloud_api, push_sdk
  sync_mode VARCHAR(20) NOT NULL DEFAULT 'pull', -- push, pull
  status VARCHAR(20) NOT NULL DEFAULT 'active', -- active, inactive, maintenance, offline
  last_sync_at TIMESTAMPTZ,
  firmware_version VARCHAR(100),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  CONSTRAINT uq_biometric_devices_device_id UNIQUE (device_id),
  CONSTRAINT uq_biometric_devices_serial_number UNIQUE (serial_number),
  CONSTRAINT uq_biometric_devices_endpoint_provider UNIQUE (ip_address, port, provider_type),
  CONSTRAINT chk_biometric_devices_sync_mode CHECK (sync_mode IN ('push', 'pull')),
  CONSTRAINT chk_biometric_devices_status CHECK (status IN ('active', 'inactive', 'maintenance', 'offline'))
);

CREATE INDEX IF NOT EXISTS idx_biometric_devices_branch_id ON biometric_devices(branch_id);
CREATE INDEX IF NOT EXISTS idx_biometric_devices_provider_type ON biometric_devices(provider_type);
CREATE INDEX IF NOT EXISTS idx_biometric_devices_status ON biometric_devices(status);
CREATE INDEX IF NOT EXISTS idx_biometric_devices_last_sync_at ON biometric_devices(last_sync_at);

CREATE TABLE IF NOT EXISTS biometric_enrollments (
  id VARCHAR(50) PRIMARY KEY,
  employee_id VARCHAR(50) NOT NULL,
  device_id VARCHAR(50) NOT NULL,
  biometric_user_id VARCHAR(120) NOT NULL,
  card_number VARCHAR(120),
  enrollment_status VARCHAR(20) NOT NULL DEFAULT 'active', -- active, pending, suspended, revoked
  enrolled_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  CONSTRAINT fk_biometric_enrollments_employee FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
  CONSTRAINT fk_biometric_enrollments_device FOREIGN KEY (device_id) REFERENCES biometric_devices(id) ON DELETE CASCADE,
  CONSTRAINT uq_biometric_enrollments_device_user UNIQUE (device_id, biometric_user_id),
  CONSTRAINT uq_biometric_enrollments_device_card UNIQUE (device_id, card_number),
  CONSTRAINT chk_biometric_enrollments_status CHECK (enrollment_status IN ('active', 'pending', 'suspended', 'revoked'))
);

CREATE INDEX IF NOT EXISTS idx_biometric_enrollments_employee_id ON biometric_enrollments(employee_id);
CREATE INDEX IF NOT EXISTS idx_biometric_enrollments_device_id ON biometric_enrollments(device_id);
CREATE INDEX IF NOT EXISTS idx_biometric_enrollments_status ON biometric_enrollments(enrollment_status);
CREATE INDEX IF NOT EXISTS idx_biometric_enrollments_enrolled_at ON biometric_enrollments(enrolled_at);

CREATE TABLE IF NOT EXISTS attendance_device_logs (
  id VARCHAR(50) PRIMARY KEY,
  device_id VARCHAR(50) NOT NULL,
  enrollment_id VARCHAR(50),
  employee_id VARCHAR(50),
  log_time TIMESTAMPTZ NOT NULL,
  raw_payload JSONB,
  processed_record_id VARCHAR(50),
  source_type VARCHAR(50) NOT NULL DEFAULT 'device', -- device, sdk_push, sdk_pull, cloud_api, mobile
  punch_type VARCHAR(30), -- check_in, check_out, break_in, break_out, unknown
  sync_status VARCHAR(20) NOT NULL DEFAULT 'pending', -- pending, processed, failed, duplicate, retried
  duplicate_key VARCHAR(255),
  is_duplicate BOOLEAN NOT NULL DEFAULT FALSE,
  manual_adjustment BOOLEAN NOT NULL DEFAULT FALSE,
  manual_adjusted_by VARCHAR(50),
  manual_adjustment_reason TEXT,
  retry_count INT NOT NULL DEFAULT 0,
  audit_trail JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  CONSTRAINT fk_attendance_device_logs_device FOREIGN KEY (device_id) REFERENCES biometric_devices(id) ON DELETE CASCADE,
  CONSTRAINT fk_attendance_device_logs_enrollment FOREIGN KEY (enrollment_id) REFERENCES biometric_enrollments(id) ON DELETE SET NULL,
  CONSTRAINT fk_attendance_device_logs_employee FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE SET NULL,
  CONSTRAINT fk_attendance_device_logs_record FOREIGN KEY (processed_record_id) REFERENCES attendance_records(id) ON DELETE SET NULL,
  CONSTRAINT chk_attendance_device_logs_sync_status CHECK (sync_status IN ('pending', 'processed', 'failed', 'duplicate', 'retried'))
);

CREATE INDEX IF NOT EXISTS idx_attendance_device_logs_device_id ON attendance_device_logs(device_id);
CREATE INDEX IF NOT EXISTS idx_attendance_device_logs_employee_id ON attendance_device_logs(employee_id);
CREATE INDEX IF NOT EXISTS idx_attendance_device_logs_log_time ON attendance_device_logs(log_time DESC);
CREATE INDEX IF NOT EXISTS idx_attendance_device_logs_sync_status ON attendance_device_logs(sync_status);
CREATE INDEX IF NOT EXISTS idx_attendance_device_logs_duplicate_key ON attendance_device_logs(duplicate_key);