-- Phase 17: employee advances settlement groundwork

ALTER TABLE employee_advances
  MODIFY status ENUM('draft','posted','canceled') NOT NULL DEFAULT 'posted';

ALTER TABLE employee_advances
  ADD COLUMN settlement_status ENUM('open','partial','settled') NOT NULL DEFAULT 'open' AFTER status,
  ADD COLUMN settled_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00 AFTER settlement_status,
  ADD COLUMN remaining_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00 AFTER settled_amount,
  ADD COLUMN last_settlement_at DATETIME NULL AFTER remaining_amount;

CREATE TABLE IF NOT EXISTS employee_advance_settlements (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  advance_id BIGINT UNSIGNED NOT NULL,
  employee_id BIGINT UNSIGNED NOT NULL,
  payroll_run_id BIGINT UNSIGNED NULL,
  payroll_item_id BIGINT UNSIGNED NULL,
  settlement_date DATETIME NOT NULL,
  amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  notes TEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_eas_advance_date (advance_id, settlement_date),
  KEY idx_eas_employee_date (employee_id, settlement_date),
  KEY idx_eas_payroll (payroll_run_id, payroll_item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

UPDATE employee_advances
SET settlement_status = 'open',
    settled_amount = 0,
    remaining_amount = CASE WHEN status='posted' THEN amount ELSE 0 END,
    last_settlement_at = NULL;