-- Slice 8Z-n · Balance confirmation + proof-of-payment.
--
-- Two additions to close the "did we have the money" loop:
--   1) FM can record a Balance snapshot at approval (advisory) — stamped on the schedule so
--      Exec sees it. If the payment later fails, audit trail shows what FM thought was there.
--   2) At Record Disbursement, batch proof (M-Pesa confirmation PDF, bank statement excerpt) is
--      required; per-line proofs are optional. Stored in attachments/schedules/{id}/.

-- ── 1. payment_schedules · balance snapshot fields ──────────────────────
SET @sch := DATABASE();
SET @tbl := 'payment_schedules';

SET @c := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@sch AND table_name=@tbl AND column_name='fm_balance_snapshot');
SET @sql := IF(@c=0, "ALTER TABLE payment_schedules ADD COLUMN fm_balance_snapshot DECIMAL(20,2) NULL AFTER source_account_id, ADD COLUMN fm_balance_snapshot_at DATETIME NULL", 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ── 2. schedule_disburse_attachments (proof of payment) ─────────────────
CREATE TABLE IF NOT EXISTS schedule_disburse_attachments (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    schedule_id INT UNSIGNED NOT NULL,
    lpo_id INT UNSIGNED NULL,
    scope ENUM('BATCH','LINE') NOT NULL DEFAULT 'BATCH',
    file_path VARCHAR(500) NOT NULL,
    original_name VARCHAR(300) NOT NULL,
    mime VARCHAR(120) NOT NULL,
    size_bytes INT UNSIGNED NOT NULL,
    uploaded_by INT UNSIGNED NULL,
    uploaded_by_name VARCHAR(160) NULL,
    uploaded_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_schedule (schedule_id),
    KEY idx_lpo (lpo_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── 3. Perm for attachment download ────────────────────────────────────
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'PaymentSchedules', 'proofDownload', 0, NOW()
FROM user_groups g
WHERE g.user_group_name IN ('Accountant','Finance Manager','Executive','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 = 'proofDownload');
