-- Phase C: Offboarding cases + checklist (+ HR offboarding RBAC).

CREATE TABLE IF NOT EXISTS hr_offboarding (
  id INT AUTO_INCREMENT PRIMARY KEY,
  employee_id INT NOT NULL,
  exit_type ENUM('resignation','termination','end_of_contract','other') NOT NULL DEFAULT 'resignation',
  notice_start DATE NULL,
  last_working_day DATE NULL,
  reason_html MEDIUMTEXT NULL,
  status ENUM('in_progress','completed','cancelled') NOT NULL DEFAULT 'in_progress',
  started_by_user_id INT NULL,
  completed_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  KEY idx_offboard_emp (employee_id, status),
  CONSTRAINT fk_offboard_emp FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
  CONSTRAINT fk_offboard_user FOREIGN KEY (started_by_user_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS hr_offboarding_checklist (
  id INT AUTO_INCREMENT PRIMARY KEY,
  offboarding_id INT NOT NULL,
  title VARCHAR(255) NOT NULL,
  done TINYINT(1) NOT NULL DEFAULT 0,
  sort_order INT NOT NULL DEFAULT 0,
  completed_at DATETIME NULL,
  completed_by_user_id INT NULL,
  CONSTRAINT fk_off_chk_off FOREIGN KEY (offboarding_id) REFERENCES hr_offboarding(id) ON DELETE CASCADE,
  CONSTRAINT fk_off_chk_user FOREIGN KEY (completed_by_user_id) REFERENCES users(id) ON DELETE SET NULL
);

INSERT IGNORE INTO permission_modules (module_key, label, module_group, sort_order) VALUES
  ('hr_offboarding', 'HR Offboarding', 'HR', 26);

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 = 'hr_offboarding'
  AND pa.action_key IN ('read', 'create', 'update', 'delete', 'export');