-- Phase B: Leave types, balances, requests (+ HR leave RBAC).
-- Holidays remain in hr_holidays (Phase A) — not duplicated here.

CREATE TABLE IF NOT EXISTS hr_leave_types (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(128) NOT NULL,
  code VARCHAR(32) NOT NULL,
  paid TINYINT(1) NOT NULL DEFAULT 1,
  accrual_days_per_year DECIMAL(8,2) NOT NULL DEFAULT 0,
  carry_forward TINYINT(1) NOT NULL DEFAULT 0,
  max_consecutive_days INT 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_leave_type_code (code)
);

INSERT IGNORE INTO hr_leave_types (name, code, paid, accrual_days_per_year, carry_forward) VALUES
  ('Annual Leave', 'ANNUAL', 1, 14, 1),
  ('Sick Leave', 'SICK', 1, 10, 0),
  ('Unpaid Leave', 'UNPAID', 0, 0, 0),
  ('Work From Home', 'WFH', 1, 0, 0);

CREATE TABLE IF NOT EXISTS hr_leave_balances (
  id INT AUTO_INCREMENT PRIMARY KEY,
  employee_id INT NOT NULL,
  leave_type_id INT NOT NULL,
  year INT NOT NULL,
  opening_balance DECIMAL(8,2) NOT NULL DEFAULT 0,
  accrued DECIMAL(8,2) NOT NULL DEFAULT 0,
  used DECIMAL(8,2) NOT NULL DEFAULT 0,
  adjusted DECIMAL(8,2) NOT NULL DEFAULT 0,
  UNIQUE KEY uq_leave_bal (employee_id, leave_type_id, year),
  CONSTRAINT fk_leave_bal_emp FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
  CONSTRAINT fk_leave_bal_type FOREIGN KEY (leave_type_id) REFERENCES hr_leave_types(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS hr_leave_requests (
  id INT AUTO_INCREMENT PRIMARY KEY,
  employee_id INT NOT NULL,
  leave_type_id INT NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  days DECIMAL(8,2) NOT NULL,
  reason_html MEDIUMTEXT NULL,
  status ENUM('pending','approved','rejected','cancelled') NOT NULL DEFAULT 'pending',
  reviewed_by_user_id INT NULL,
  reviewed_at DATETIME NULL,
  review_note VARCHAR(512) 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_leave_req_emp (employee_id, status),
  CONSTRAINT fk_leave_req_emp FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
  CONSTRAINT fk_leave_req_type FOREIGN KEY (leave_type_id) REFERENCES hr_leave_types(id),
  CONSTRAINT fk_leave_req_user FOREIGN KEY (reviewed_by_user_id) REFERENCES users(id) ON DELETE SET NULL
);

INSERT IGNORE INTO permission_modules (module_key, label, module_group, sort_order) VALUES
  ('hr_leave', 'HR Leave', 'HR', 24),
  ('hr_leave_types', 'HR Leave Types', 'HR', 25);

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

-- Employees with employees.read can request leave and see types.
INSERT IGNORE INTO role_permissions (role_id, module_key, action_key, allowed)
SELECT DISTINCT rp.role_id, 'hr_leave', '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_leave', 'create', 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_leave_types', 'read', 1
FROM role_permissions rp
WHERE rp.module_key = 'employees' AND rp.action_key = 'read' AND rp.allowed = 1;