-- Phase F: Salary components, employee compensation, payroll periods/lines.

CREATE TABLE IF NOT EXISTS hr_salary_components (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(128) NOT NULL,
  code VARCHAR(32) NOT NULL,
  component_type ENUM('earning','deduction') NOT NULL DEFAULT 'earning',
  is_basic TINYINT(1) NOT NULL DEFAULT 0,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  UNIQUE KEY uq_sal_comp_code (code)
);

INSERT IGNORE INTO hr_salary_components (name, code, component_type, is_basic) VALUES
  ('Basic Salary', 'BASIC', 'earning', 1),
  ('Housing Allowance', 'HRA', 'earning', 0),
  ('Transport Allowance', 'TA', 'earning', 0);

CREATE TABLE IF NOT EXISTS hr_employee_compensation (
  id INT AUTO_INCREMENT PRIMARY KEY,
  employee_id INT NOT NULL,
  component_id INT NOT NULL,
  amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  effective_from DATE NOT NULL,
  effective_to DATE NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_emp_comp (employee_id, effective_from),
  CONSTRAINT fk_emp_comp_emp FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
  CONSTRAINT fk_emp_comp_comp FOREIGN KEY (component_id) REFERENCES hr_salary_components(id)
);

CREATE TABLE IF NOT EXISTS hr_payroll_periods (
  id INT AUTO_INCREMENT PRIMARY KEY,
  label VARCHAR(64) NOT NULL,
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  status ENUM('open','locked','approved','paid') NOT NULL DEFAULT 'open',
  locked_at DATETIME NULL,
  approved_at DATETIME NULL,
  paid_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_payroll_period (period_start, period_end)
);

CREATE TABLE IF NOT EXISTS hr_payroll_lines (
  id INT AUTO_INCREMENT PRIMARY KEY,
  period_id INT NOT NULL,
  employee_id INT NOT NULL,
  basic_salary DECIMAL(14,2) NOT NULL DEFAULT 0,
  earnings DECIMAL(14,2) NOT NULL DEFAULT 0,
  unpaid_leave_deduction DECIMAL(14,2) NOT NULL DEFAULT 0,
  fine_deduction DECIMAL(14,2) NOT NULL DEFAULT 0,
  other_deductions DECIMAL(14,2) NOT NULL DEFAULT 0,
  net_pay DECIMAL(14,2) NOT NULL DEFAULT 0,
  breakdown_json JSON NULL,
  payslip_storage_key VARCHAR(512) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_payroll_line (period_id, employee_id),
  CONSTRAINT fk_pay_line_period FOREIGN KEY (period_id) REFERENCES hr_payroll_periods(id) ON DELETE CASCADE,
  CONSTRAINT fk_pay_line_emp FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
);

INSERT IGNORE INTO permission_modules (module_key, label, module_group, sort_order) VALUES
  ('hr_compensation', 'HR Compensation', 'HR', 29),
  ('hr_payroll', 'HR Payroll', 'HR', 30);

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