CREATE TABLE IF NOT EXISTS attendance_correction_requests (
  id TEXT PRIMARY KEY,
  record_id TEXT NOT NULL REFERENCES attendance_records(id) ON DELETE CASCADE,
  employee_id TEXT NOT NULL REFERENCES employees(id) ON DELETE CASCADE,
  reason TEXT NOT NULL,
  requested_record_type TEXT CHECK (requested_record_type IN ('present', 'absent', 'late', 'sick', 'leave')),
  requested_check_in_time TIMESTAMPTZ,
  requested_check_out_time TIMESTAMPTZ,
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected')),
  reviewed_by TEXT,
  reviewed_at TIMESTAMPTZ,
  review_reason TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_att_corr_employee_id ON attendance_correction_requests(employee_id);
CREATE INDEX IF NOT EXISTS idx_att_corr_status ON attendance_correction_requests(status);
CREATE INDEX IF NOT EXISTS idx_att_corr_created_at ON attendance_correction_requests(created_at);
CREATE INDEX IF NOT EXISTS idx_att_corr_record_id ON attendance_correction_requests(record_id);