-- Slice 8Z-c · Batch 1 · Unified internal cash-out (LPO-based).
--   1. Register INTERNAL_CASH_LARGE_APPROVAL chain (PM → FM → EXEC) for large requests.
--      Standard flow (≤ threshold) keeps using the existing PETTY_CASH_APPROVAL (PM → FM).
--   2. Perm grants for the new CashRequests controller — any role that can raise cash + Accountant queue.

-- ── 1. Register INTERNAL_CASH_LARGE_APPROVAL chain ─────────────────────────
-- Idempotent: insert def only if missing; then wipe + re-seed steps.
INSERT INTO fin_chain_defs (chain_key, title, description, status, created_by, created_on)
SELECT 'INTERNAL_CASH_LARGE_APPROVAL',
       'Internal Cash (Large) · PM → FM → Exec',
       'Large internal cash requests (> threshold) — PM checks, FM approves, Executive authorises.',
       'ACTIVE', 0, NOW()
  FROM DUAL
 WHERE NOT EXISTS (SELECT 1 FROM fin_chain_defs WHERE chain_key = 'INTERNAL_CASH_LARGE_APPROVAL');

-- Wipe + re-seed steps (safe replay).
DELETE FROM fin_chain_steps WHERE chain_id = (SELECT c0d FROM fin_chain_defs WHERE chain_key = 'INTERNAL_CASH_LARGE_APPROVAL');
INSERT INTO fin_chain_steps (chain_id, step_no, step_key, step_label, role_required, is_terminal, description, sort_order, created_on)
SELECT c0d, 1, 'PM_CHECK',     'PM Check',       'PROJECT MANAGER',    0, 'Project Manager confirms the request is legitimate + within scope.', 1, NOW() FROM fin_chain_defs WHERE chain_key = 'INTERNAL_CASH_LARGE_APPROVAL'
UNION ALL
SELECT c0d, 2, 'FM_APPROVE',   'FM Approve',     'FINANCE MANAGER',    0, 'Finance Manager approves the financial commitment.',                2, NOW() FROM fin_chain_defs WHERE chain_key = 'INTERNAL_CASH_LARGE_APPROVAL'
UNION ALL
SELECT c0d, 3, 'EXEC_APPROVE', 'Exec Authorise', 'EXECUTIVE',          1, 'Executive authorises the large disbursement (final step).',         3, NOW() FROM fin_chain_defs WHERE chain_key = 'INTERNAL_CASH_LARGE_APPROVAL';

-- Refresh existing PETTY_CASH_APPROVAL title to make its "standard" role obvious.
UPDATE fin_chain_defs
   SET title = 'Internal Cash (Standard) · PM → FM',
       description = 'Standard internal cash requests (≤ threshold) — PM checks, FM approves.'
 WHERE chain_key = 'PETTY_CASH_APPROVAL';

-- ── 2. Perms ──────────────────────────────────────────────────────────────
-- CashRequests/new · saveAj · queue · my — draft/save open to most operational roles;
-- queue is Accountant/Admin only.
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'CashRequests', ac, 0, NOW()
FROM user_groups g
CROSS JOIN (SELECT 'new' AS ac UNION ALL SELECT 'saveAj' UNION ALL SELECT 'my') actions
WHERE g.user_group_name IN ('Projects Coordinator','Project Manager','Procurement','Field Supervisor','Accountant','Finance Manager','Executive','Administrator','Stores')
  AND NOT EXISTS (
    SELECT 1 FROM user_groups_perms p
    WHERE p.user_group_id = g.c0d
      AND p.controller_allowed = 'CashRequests'
      AND p.action_allowed = actions.ac
);

INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'CashRequests', 'queue', 0, NOW()
FROM user_groups g
WHERE g.user_group_name IN ('Accountant','Finance Manager','Administrator','Executive')
  AND NOT EXISTS (
    SELECT 1 FROM user_groups_perms p
    WHERE p.user_group_id = g.c0d
      AND p.controller_allowed = 'CashRequests'
      AND p.action_allowed = 'queue'
);
