-- Phase E: Fine policies, exemptions, ledger (+ seeds aligned to attendance triggers).

CREATE TABLE IF NOT EXISTS hr_fine_policies (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description_html MEDIUMTEXT NULL,
  trigger_key VARCHAR(64) NOT NULL,
  amount_type ENUM('fixed','percent_of_basic') NOT NULL DEFAULT 'fixed',
  amount_value DECIMAL(14,4) NOT NULL DEFAULT 0,
  threshold_minutes INT NOT NULL DEFAULT 0,
  tier_mode ENUM('once_per_event','per_block','daily_flat') NOT NULL DEFAULT 'once_per_event',
  block_minutes INT NULL,
  cap_per_day DECIMAL(14,2) NULL,
  cap_per_month DECIMAL(14,2) NULL,
  scope_type ENUM('global','department','designation','employee') NOT NULL DEFAULT 'global',
  scope_ids JSON NULL,
  stack_mode ENUM('stack_all','highest_amount_wins','most_specific_only') NOT NULL DEFAULT 'most_specific_only',
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  effective_from DATE NOT NULL,
  effective_to DATE 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_fine_pol_trigger (trigger_key, status, deleted_at)
);

CREATE TABLE IF NOT EXISTS hr_fine_exemptions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  policy_id INT NOT NULL,
  employee_id INT NOT NULL,
  reason VARCHAR(512) NULL,
  exempt_from DATE NOT NULL,
  exempt_to DATE NULL,
  created_by_user_id INT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_fine_exempt (policy_id, employee_id),
  CONSTRAINT fk_fine_ex_pol FOREIGN KEY (policy_id) REFERENCES hr_fine_policies(id) ON DELETE CASCADE,
  CONSTRAINT fk_fine_ex_emp FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
  CONSTRAINT fk_fine_ex_user FOREIGN KEY (created_by_user_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS hr_fine_ledger (
  id INT AUTO_INCREMENT PRIMARY KEY,
  employee_id INT NOT NULL,
  policy_id INT NULL,
  work_date DATE NOT NULL,
  trigger_key VARCHAR(64) NOT NULL,
  amount DECIMAL(14,2) NOT NULL,
  amount_type ENUM('fixed','percent_of_basic') NOT NULL,
  status ENUM('applied','reversed','exempted') NOT NULL DEFAULT 'applied',
  notes VARCHAR(512) NULL,
  reversed_by_user_id INT NULL,
  reversed_at DATETIME NULL,
  reverse_reason VARCHAR(512) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_fine_ledger_emp (employee_id, work_date, status),
  KEY idx_fine_ledger_pol (policy_id),
  CONSTRAINT fk_fine_led_emp FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
  CONSTRAINT fk_fine_led_pol FOREIGN KEY (policy_id) REFERENCES hr_fine_policies(id) ON DELETE SET NULL,
  CONSTRAINT fk_fine_led_user FOREIGN KEY (reversed_by_user_id) REFERENCES users(id) ON DELETE SET NULL
);

INSERT IGNORE INTO permission_actions (action_key, label, sort_order) VALUES
  ('exempt_employee', 'Exempt employee', 24);

INSERT IGNORE INTO permission_modules (module_key, label, module_group, sort_order) VALUES
  ('hr_fine_policies', 'HR Fine Policies', 'HR', 28);

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

-- Starter policies (trigger keys match attendance rollup / ignore_prompt).
INSERT INTO hr_fine_policies
  (name, description_html, trigger_key, amount_type, amount_value, threshold_minutes, tier_mode, scope_type, stack_mode, status, effective_from)
SELECT * FROM (
  SELECT 'Late clock-in' AS name,
         '<p>Applied when clock-in is after shift start + grace.</p>' AS description_html,
         'late_clock_in' AS trigger_key,
         'fixed' AS amount_type,
         100.0000 AS amount_value,
         1 AS threshold_minutes,
         'once_per_event' AS tier_mode,
         'global' AS scope_type,
         'most_specific_only' AS stack_mode,
         'active' AS status,
         CURDATE() AS effective_from
) s
WHERE NOT EXISTS (SELECT 1 FROM hr_fine_policies WHERE trigger_key = 'late_clock_in' AND deleted_at IS NULL);

INSERT INTO hr_fine_policies
  (name, description_html, trigger_key, amount_type, amount_value, threshold_minutes, tier_mode, scope_type, stack_mode, status, effective_from)
SELECT * FROM (
  SELECT 'Open shift' AS name,
         '<p>Applied when a past work day still has an open clock-in.</p>' AS description_html,
         'open_shift' AS trigger_key,
         'fixed' AS amount_type,
         200.0000 AS amount_value,
         0 AS threshold_minutes,
         'once_per_event' AS tier_mode,
         'global' AS scope_type,
         'most_specific_only' AS stack_mode,
         'active' AS status,
         CURDATE() AS effective_from
) s
WHERE NOT EXISTS (SELECT 1 FROM hr_fine_policies WHERE trigger_key = 'open_shift' AND deleted_at IS NULL);

INSERT INTO hr_fine_policies
  (name, description_html, trigger_key, amount_type, amount_value, threshold_minutes, tier_mode, scope_type, stack_mode, status, effective_from)
SELECT * FROM (
  SELECT 'Ignored attendance prompt' AS name,
         '<p>Applied when an employee ignores a clock/break reminder.</p>' AS description_html,
         'ignored_prompt' AS trigger_key,
         'fixed' AS amount_type,
         50.0000 AS amount_value,
         0 AS threshold_minutes,
         'once_per_event' AS tier_mode,
         'global' AS scope_type,
         'most_specific_only' AS stack_mode,
         'active' AS status,
         CURDATE() AS effective_from
) s
WHERE NOT EXISTS (SELECT 1 FROM hr_fine_policies WHERE trigger_key = 'ignored_prompt' AND deleted_at IS NULL);

INSERT INTO hr_fine_policies
  (name, description_html, trigger_key, amount_type, amount_value, threshold_minutes, tier_mode, block_minutes, scope_type, stack_mode, status, effective_from)
SELECT name, description_html, trigger_key, amount_type, amount_value, threshold_minutes, tier_mode, block_minutes, scope_type, stack_mode, status, effective_from FROM (
  SELECT 'Break overrun' AS name,
         '<p>Per 15-minute block beyond allowed break + grace.</p>' AS description_html,
         'break_overrun' AS trigger_key,
         'fixed' AS amount_type,
         25.0000 AS amount_value,
         0 AS threshold_minutes,
         'per_block' AS tier_mode,
         15 AS block_minutes,
         'global' AS scope_type,
         'most_specific_only' AS stack_mode,
         'active' AS status,
         CURDATE() AS effective_from
) s
WHERE NOT EXISTS (SELECT 1 FROM hr_fine_policies WHERE trigger_key = 'break_overrun' AND deleted_at IS NULL);

INSERT INTO hr_fine_policies
  (name, description_html, trigger_key, amount_type, amount_value, threshold_minutes, tier_mode, scope_type, stack_mode, status, effective_from)
SELECT * FROM (
  SELECT 'Early clock-out' AS name,
         '<p>Applied when clock-out is before shift end minus grace.</p>' AS description_html,
         'early_clock_out' AS trigger_key,
         'fixed' AS amount_type,
         75.0000 AS amount_value,
         1 AS threshold_minutes,
         'once_per_event' AS tier_mode,
         'global' AS scope_type,
         'most_specific_only' AS stack_mode,
         'active' AS status,
         CURDATE() AS effective_from
) s
WHERE NOT EXISTS (SELECT 1 FROM hr_fine_policies WHERE trigger_key = 'early_clock_out' AND deleted_at IS NULL);