-- Phase A: Attendance + holidays (+ HR RBAC). Shift tables already from 060.

CREATE TABLE IF NOT EXISTS hr_attendance_days (
  id INT AUTO_INCREMENT PRIMARY KEY,
  employee_id INT NOT NULL,
  work_date DATE NOT NULL,
  status ENUM('present','late','absent','on_leave','holiday','incomplete','weekend') NOT NULL DEFAULT 'incomplete',
  first_clock_in DATETIME NULL,
  last_clock_out DATETIME NULL,
  worked_minutes INT NOT NULL DEFAULT 0,
  break_minutes INT NOT NULL DEFAULT 0,
  late_minutes INT NOT NULL DEFAULT 0,
  early_leave_minutes INT NOT NULL DEFAULT 0,
  break_overrun_minutes INT NOT NULL DEFAULT 0,
  open_shift TINYINT(1) NOT NULL DEFAULT 0,
  schedule_snapshot JSON NULL,
  locked TINYINT(1) NOT NULL DEFAULT 0,
  notes TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_att_day (employee_id, work_date),
  KEY idx_att_day_date (work_date, status),
  CONSTRAINT fk_att_day_emp FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS hr_attendance_punches (
  id INT AUTO_INCREMENT PRIMARY KEY,
  employee_id INT NOT NULL,
  work_date DATE NOT NULL,
  punch_type ENUM('clock_in','clock_out','break_in','break_out') NOT NULL,
  punched_at DATETIME NOT NULL,
  source ENUM('self','correction','system') NOT NULL DEFAULT 'self',
  corrected_by_user_id INT NULL,
  correction_reason VARCHAR(512) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_att_punch_emp_date (employee_id, work_date, punched_at),
  CONSTRAINT fk_att_punch_emp FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
  CONSTRAINT fk_att_punch_user FOREIGN KEY (corrected_by_user_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS hr_attendance_ignores (
  id INT AUTO_INCREMENT PRIMARY KEY,
  employee_id INT NOT NULL,
  work_date DATE NOT NULL,
  prompt_type ENUM('clock_in','clock_out','break_in','break_out') NOT NULL,
  ignored_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  reason VARCHAR(512) NULL,
  KEY idx_att_ignore_emp (employee_id, work_date),
  CONSTRAINT fk_att_ignore_emp FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS hr_holidays (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  holiday_date DATE NOT NULL,
  description_html MEDIUMTEXT NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  UNIQUE KEY uq_holiday_date (holiday_date, deleted_at)
);

INSERT IGNORE INTO permission_actions (action_key, label, sort_order) VALUES
  ('punch_own', 'Punch own attendance', 20),
  ('punch_correct', 'Correct punches', 21),
  ('ignore_prompt', 'Ignore clock prompts', 22),
  ('lock_period', 'Lock period', 23);

INSERT IGNORE INTO permission_modules (module_key, label, module_group, sort_order) VALUES
  ('hr_shifts', 'HR Shifts', 'HR', 21),
  ('hr_attendance', 'HR Attendance', 'HR', 22),
  ('hr_holidays', 'HR Holidays', 'HR', 23);

INSERT IGNORE INTO role_permissions (role_id, module_key, action_key, allowed)
SELECT wr.id, pm.module_key, pa.action_key, 1
FROM workspace_roles wr
CROSS JOIN permission_modules pm
CROSS JOIN permission_actions pa
WHERE wr.slug = 'super-admin'
  AND pm.module_key IN ('hr_shifts', 'hr_attendance', 'hr_holidays')
  AND pa.action_key IN ('read', 'create', 'update', 'delete', 'export');

INSERT IGNORE INTO role_permissions (role_id, module_key, action_key, allowed)
SELECT wr.id, 'hr_attendance', a.action_key, 1
FROM workspace_roles wr
CROSS JOIN (
  SELECT 'punch_own' AS action_key
  UNION ALL SELECT 'punch_correct'
  UNION ALL SELECT 'ignore_prompt'
  UNION ALL SELECT 'lock_period'
) a
WHERE wr.slug = 'super-admin';

-- Employees with employees.read get punch_own + attendance read for self-service.
INSERT IGNORE INTO role_permissions (role_id, module_key, action_key, allowed)
SELECT DISTINCT rp.role_id, 'hr_attendance', 'read', 1
FROM role_permissions rp
WHERE rp.module_key = 'employees' AND rp.action_key = 'read' AND rp.allowed = 1;

INSERT IGNORE INTO role_permissions (role_id, module_key, action_key, allowed)
SELECT DISTINCT rp.role_id, 'hr_attendance', 'punch_own', 1
FROM role_permissions rp
WHERE rp.module_key = 'employees' AND rp.action_key = 'read' AND rp.allowed = 1;

INSERT IGNORE INTO role_permissions (role_id, module_key, action_key, allowed)
SELECT DISTINCT rp.role_id, 'hr_holidays', 'read', 1
FROM role_permissions rp
WHERE rp.module_key = 'employees' AND rp.action_key = 'read' AND rp.allowed = 1;