-- Create attendance_records table for PostgreSQL
-- Stores attendance check-in/check-out records with multiple record types
CREATE TABLE IF NOT EXISTS attendance_records (
  id VARCHAR(50) PRIMARY KEY,
  employee_id VARCHAR(50) NOT NULL,
  date VARCHAR(10) NOT NULL, -- YYYY-MM-DD
  
  -- Record type: present, absent, late, sick, leave
  record_type VARCHAR(20) NOT NULL DEFAULT 'present',
  
  -- Check-in and check-out times
  check_in_time TIMESTAMP,
  check_out_time TIMESTAMP,
  
  -- Duration in hours (calculated)
  duration_hours DECIMAL(5, 2),
  
  -- Late minutes (if applicable)
  late_minutes INT DEFAULT 0,
  
  -- Device info
  device VARCHAR(30) NOT NULL DEFAULT 'web', -- web, biometric, face_recognition, mobile
  device_id VARCHAR(100), -- Biometric ID or device MAC
  ip_address VARCHAR(45),
  
  -- Notes and justification
  notes TEXT,
  justification TEXT,
  justification_type VARCHAR(30), -- sick-leave, personal-leave, permission
  
  -- Approval status
  status VARCHAR(20) NOT NULL DEFAULT 'pending', -- pending, approved, rejected
  approved_by VARCHAR(50),
  approval_date TIMESTAMP,
  approval_reason TEXT,
  
  -- Metadata
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by VARCHAR(50),
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_by VARCHAR(50),
  
  -- Foreign key
  CONSTRAINT fk_attendance_employee FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
);

-- Create indexes for common queries
CREATE INDEX IF NOT EXISTS idx_attendance_employee_date ON attendance_records(employee_id, date);
CREATE INDEX IF NOT EXISTS idx_attendance_record_type ON attendance_records(record_type);
CREATE INDEX IF NOT EXISTS idx_attendance_status ON attendance_records(status);
CREATE INDEX IF NOT EXISTS idx_attendance_created_at ON attendance_records(created_at);
CREATE INDEX IF NOT EXISTS idx_attendance_date ON attendance_records(date);

-- Create attendance_audit_log table
-- Audit trail for all attendance record changes
CREATE TABLE IF NOT EXISTS attendance_audit_log (
  id VARCHAR(50) PRIMARY KEY,
  record_id VARCHAR(50) NOT NULL,
  
  -- Action type
  action VARCHAR(30) NOT NULL, -- created, updated, approved, rejected, deleted
  
  -- Change tracking
  changed_by VARCHAR(50),
  changed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  
  -- Previous and new values
  previous_value JSONB,
  new_value JSONB,
  
  -- Reason for change
  reason TEXT,
  
  -- Foreign key
  CONSTRAINT fk_audit_record FOREIGN KEY (record_id) REFERENCES attendance_records(id) ON DELETE CASCADE
);

-- Create indexes for audit log
CREATE INDEX IF NOT EXISTS idx_audit_record_id ON attendance_audit_log(record_id);
CREATE INDEX IF NOT EXISTS idx_audit_changed_at ON attendance_audit_log(changed_at);
CREATE INDEX IF NOT EXISTS idx_audit_action ON attendance_audit_log(action);

-- Create employee_biometric_mapping table
CREATE TABLE IF NOT EXISTS employee_biometric_mapping (
  id VARCHAR(50) PRIMARY KEY,
  employee_id VARCHAR(50) NOT NULL,
  employee_name VARCHAR(255) NOT NULL,
  biometric_id VARCHAR(100) NOT NULL UNIQUE,
  device_type VARCHAR(30) NOT NULL DEFAULT 'fingerprint', -- fingerprint, face, iris, other
  device_mac_address VARCHAR(17),
  is_active BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  
  -- Foreign key
  CONSTRAINT fk_biometric_employee FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
);

-- Create indexes for biometric mapping
CREATE INDEX IF NOT EXISTS idx_biometric_employee_id ON employee_biometric_mapping(employee_id);
CREATE INDEX IF NOT EXISTS idx_biometric_id ON employee_biometric_mapping(biometric_id);
CREATE INDEX IF NOT EXISTS idx_biometric_is_active ON employee_biometric_mapping(is_active);