-- Slice 8Z-s · Supplier LPO Payment Requests (Slice D2)
--
-- Wires the existing payment_requests stub into a full PR1 ladder:
--   DRAFT → SUBMITTED → PC_CONFIRMED → PM_REVIEWED → SCHEDULED → PAID (or REJECTED/CANCELLED)
--
-- Uses next_check_status (not fin_chains) — matches Works PR1 muscle memory. Each PR references
-- one or more lpo_payment_terms rows via payment_request_lines. Term-gated availability math
-- lives in the PHP model.
--
-- Adds:
--  1) Audit rung columns on payment_requests (pc_ / pm_ / fm_ / accountant_ + reject stamps).
--  2) Supplier invoice fields for NET_DAYS triggers.
--  3) payment_request_lines — junction (payment_request_id, term_id, amount, computed_share).
--  4) Perms for PaymentRequests controller endpoints.

-- ── 1. payment_requests · audit + invoice columns ──────────────────────
SET @sch := DATABASE();
SET @c := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@sch AND table_name='payment_requests' AND column_name='pc_confirmed_at');
SET @sql := IF(@c=0, "ALTER TABLE payment_requests
    ADD COLUMN supplier_invoice_no   VARCHAR(80)  NULL,
    ADD COLUMN supplier_invoice_date DATE         NULL,
    ADD COLUMN submitted_at          DATETIME     NULL,
    ADD COLUMN submitted_by          INT UNSIGNED NULL,
    ADD COLUMN submitted_by_name     VARCHAR(160) NULL,
    ADD COLUMN pc_confirmed_at       DATETIME     NULL,
    ADD COLUMN pc_confirmed_by       INT UNSIGNED NULL,
    ADD COLUMN pc_confirmed_by_name  VARCHAR(160) NULL,
    ADD COLUMN pc_confirm_note       VARCHAR(500) NULL,
    ADD COLUMN pm_reviewed_at        DATETIME     NULL,
    ADD COLUMN pm_reviewed_by        INT UNSIGNED NULL,
    ADD COLUMN pm_reviewed_by_name   VARCHAR(160) NULL,
    ADD COLUMN pm_review_note        VARCHAR(500) NULL,
    ADD COLUMN fm_scheduled_at       DATETIME     NULL,
    ADD COLUMN fm_scheduled_by       INT UNSIGNED NULL,
    ADD COLUMN fm_scheduled_by_name  VARCHAR(160) NULL,
    ADD COLUMN fm_schedule_note      VARCHAR(500) NULL,
    ADD COLUMN payment_schedule_id   INT UNSIGNED NULL,
    ADD COLUMN acct_recorded_at      DATETIME     NULL,
    ADD COLUMN acct_recorded_by      INT UNSIGNED NULL,
    ADD COLUMN acct_recorded_by_name VARCHAR(160) NULL,
    ADD COLUMN acct_disburse_ref     VARCHAR(200) NULL,
    ADD COLUMN acct_journal_entry_id INT UNSIGNED NULL,
    ADD COLUMN rejected_at           DATETIME     NULL,
    ADD COLUMN rejected_by           INT UNSIGNED NULL,
    ADD COLUMN rejected_by_name      VARCHAR(160) NULL,
    ADD COLUMN rejected_at_stage     VARCHAR(30)  NULL COMMENT 'PC_CONFIRM|PM_REVIEW|FM_SCHEDULE|ACCT_RECORD',
    ADD COLUMN rejected_reason       VARCHAR(500) NULL,
    ADD KEY idx_lpo (lpo_id),
    ADD KEY idx_status (status),
    ADD KEY idx_ncs (next_check_status)",
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ── 2. payment_request_lines ───────────────────────────────────────────
CREATE TABLE IF NOT EXISTS payment_request_lines (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    payment_request_id INT UNSIGNED NOT NULL,
    term_id INT UNSIGNED NOT NULL COMMENT 'FK lpo_payment_terms.c0d',
    amount DECIMAL(20,2) NOT NULL DEFAULT 0 COMMENT 'Amount claimed against this term on this PR',
    note VARCHAR(500) NULL,
    created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_pr (payment_request_id),
    KEY idx_term (term_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── 3. Perms ───────────────────────────────────────────────────────────
-- Raising PRs: Procurement + Administrator
-- Confirming / reviewing / scheduling / recording: role-specific queues.
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'PaymentRequests', ac, 0, NOW()
FROM user_groups g
CROSS JOIN (SELECT 'raiseDialogAj' AS ac
            UNION ALL SELECT 'raiseAj'
            UNION ALL SELECT 'view'
            UNION ALL SELECT 'cancelAj'
            UNION ALL SELECT 'forLpoAj') actions
WHERE g.user_group_name IN ('Procurement','Administrator')
  AND NOT EXISTS (SELECT 1 FROM user_groups_perms p WHERE p.user_group_id = g.c0d AND p.controller_allowed = 'PaymentRequests' AND p.action_allowed = actions.ac);

-- PC queue + confirm
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'PaymentRequests', ac, 0, NOW()
FROM user_groups g
CROSS JOIN (SELECT 'pcQueue' AS ac
            UNION ALL SELECT 'pcConfirmAj'
            UNION ALL SELECT 'view'
            UNION ALL SELECT 'rejectAj') actions
WHERE g.user_group_name IN ('Projects Coordinator','Administrator')
  AND NOT EXISTS (SELECT 1 FROM user_groups_perms p WHERE p.user_group_id = g.c0d AND p.controller_allowed = 'PaymentRequests' AND p.action_allowed = actions.ac);

-- PM queue + review
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'PaymentRequests', ac, 0, NOW()
FROM user_groups g
CROSS JOIN (SELECT 'pmQueue' AS ac
            UNION ALL SELECT 'pmReviewAj'
            UNION ALL SELECT 'view'
            UNION ALL SELECT 'rejectAj') actions
WHERE g.user_group_name IN ('Project Manager','Administrator')
  AND NOT EXISTS (SELECT 1 FROM user_groups_perms p WHERE p.user_group_id = g.c0d AND p.controller_allowed = 'PaymentRequests' AND p.action_allowed = actions.ac);

-- FM queue + schedule
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'PaymentRequests', ac, 0, NOW()
FROM user_groups g
CROSS JOIN (SELECT 'fmQueue' AS ac
            UNION ALL SELECT 'fmScheduleAj'
            UNION ALL SELECT 'view'
            UNION ALL SELECT 'rejectAj') actions
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 = 'PaymentRequests' AND p.action_allowed = actions.ac);

-- Accountant queue + record disbursement
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'PaymentRequests', ac, 0, NOW()
FROM user_groups g
CROSS JOIN (SELECT 'acctQueue' AS ac
            UNION ALL SELECT 'acctRecordAj'
            UNION ALL SELECT 'view'
            UNION ALL SELECT 'rejectAj') actions
WHERE g.user_group_name IN ('Accountant','Administrator')
  AND NOT EXISTS (SELECT 1 FROM user_groups_perms p WHERE p.user_group_id = g.c0d AND p.controller_allowed = 'PaymentRequests' AND p.action_allowed = actions.ac);

-- Read-only view for Exec + FM (visibility on the flow)
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'PaymentRequests', 'view', 0, NOW()
FROM user_groups g
WHERE g.user_group_name IN ('Executive','Finance Manager','Project Manager','Projects Coordinator','Procurement','Accountant')
  AND NOT EXISTS (SELECT 1 FROM user_groups_perms p WHERE p.user_group_id = g.c0d AND p.controller_allowed = 'PaymentRequests' AND p.action_allowed = 'view');
