-- Slice 8Z-o · FM Prepare / Accountant Execute split.
--
-- After Exec authorises the schedule (APPROVED), FM allocates the payment instrument
-- (cheque #, batch code, bank letter ref, planned release date + notes for cashier) — this
-- flips the schedule to READY_TO_EXECUTE and nudges Accountant. Accountant then executes
-- (records actual ref + proof) — same Record Disbursement action as before. In a pinch,
-- Accountant or FM can execute directly on APPROVED (bypass Prepare) — soft workflow, not
-- a hard gate.

-- ── 1. payment_schedules · READY_TO_EXECUTE status + prep fields ────────
-- Add READY_TO_EXECUTE to the status enum.
SET @sch := DATABASE();
SET @cur_enum := (SELECT COLUMN_TYPE FROM information_schema.columns WHERE table_schema=@sch AND table_name='payment_schedules' AND column_name='status');
SET @sql := IF(INSTR(@cur_enum, "READY_TO_EXECUTE") = 0,
    "ALTER TABLE payment_schedules MODIFY COLUMN status ENUM('DRAFT','SUBMITTED','APPROVED','READY_TO_EXECUTE','DECLINED','PAID','CANCELLED') NOT NULL DEFAULT 'DRAFT'",
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Add FM prepare fields (idempotent).
SET @c := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@sch AND table_name='payment_schedules' AND column_name='disburse_instrument');
SET @sql := IF(@c=0, "ALTER TABLE payment_schedules
    ADD COLUMN disburse_instrument ENUM('CHEQUE','BANK_BATCH','MOBILE_BATCH','BANK_LETTER','CASH_WITHDRAWAL','MIXED') NULL AFTER fm_balance_snapshot_at,
    ADD COLUMN disburse_instrument_ref VARCHAR(200) NULL,
    ADD COLUMN disburse_planned_date DATE NULL,
    ADD COLUMN disburse_prep_notes VARCHAR(1000) NULL,
    ADD COLUMN disburse_prepared_by INT UNSIGNED NULL,
    ADD COLUMN disburse_prepared_by_name VARCHAR(160) NULL,
    ADD COLUMN disburse_prepared_at DATETIME NULL",
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ── 2. Perm: preparePaymentAj (FM + Admin) ──────────────────────────────
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'PaymentSchedules', 'preparePaymentAj', 0, NOW()
FROM user_groups g
WHERE g.user_group_name IN ('Finance Manager','Administrator')
  AND NOT EXISTS (SELECT 1 FROM user_groups_perms p WHERE p.user_group_id = g.c0d AND p.controller_allowed = 'PaymentSchedules' AND p.action_allowed = 'preparePaymentAj');
