-- Salary advances: money paid to staff ahead of payday, recovered from later
-- payroll. It's a receivable (asset '1160 Salary Advances'), not an expense.
--   Give an advance   → Dr Salary Advances   Cr Cash / Bank / Mobile Money
--   Recover in payroll→ the run credits Salary Advances by the recovered amount
--                        (Dr Payroll Expense wage / Cr Cash net / Cr Salary Advances)
--
-- Idempotent — safe to re-run.

-- 1) Salary Advances asset account for every farm.
INSERT INTO chart_of_accounts (farm_id, code, name, type, role, map_key, is_system, sort_order)
SELECT f.id, '1160', 'Salary Advances', 'asset', 'salary_advances', NULL, 1, 5
FROM farms f
WHERE NOT EXISTS (
  SELECT 1 FROM chart_of_accounts c
  WHERE c.farm_id = f.id AND c.role = 'salary_advances'
);

-- 2) Recovery columns on the payroll tables.
ALTER TABLE payroll_runs      ADD COLUMN IF NOT EXISTS advance_recovered DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER deductions;
ALTER TABLE payroll_run_lines ADD COLUMN IF NOT EXISTS advance_recovery  DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER deductions;

-- 3) The advances themselves.
CREATE TABLE IF NOT EXISTS salary_advances (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  employee_id   INT UNSIGNED  NULL,
  employee_name VARCHAR(160)  NOT NULL,
  amount        DECIMAL(14,2) NOT NULL,
  recovered     DECIMAL(14,2) NOT NULL DEFAULT 0,
  method        VARCHAR(40)   NULL,
  advanced_on   DATE          NOT NULL,
  note          VARCHAR(255)  NULL,
  status        VARCHAR(20)   NOT NULL DEFAULT 'open',
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_adv_farm (farm_id),
  KEY idx_adv_employee (employee_id),
  CONSTRAINT fk_adv_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE,
  CONSTRAINT fk_adv_employee FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- 4) Exact per-run recovery allocation (for precise reversal).
CREATE TABLE IF NOT EXISTS payroll_advance_recoveries (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  run_id        INT UNSIGNED  NOT NULL,
  advance_id    INT UNSIGNED  NOT NULL,
  amount        DECIMAL(14,2) NOT NULL,
  KEY idx_par_run (run_id),
  KEY idx_par_advance (advance_id),
  CONSTRAINT fk_par_run FOREIGN KEY (run_id) REFERENCES payroll_runs(id) ON DELETE CASCADE,
  CONSTRAINT fk_par_advance FOREIGN KEY (advance_id) REFERENCES salary_advances(id) ON DELETE CASCADE
) ENGINE=InnoDB;