-- Slice 8Z-l · Payment Schedules (batched Payables).
--
-- Reshape:
--   LPO chains SHORTEN → PM Check → Accountant Prepare (terminal). Accountant Prepare now
--   flips the LPO to BOOKED (not APPROVED). BOOKED LPOs sit in the Payables Queue waiting
--   for the Accountant to bundle them into a Payment Schedule.
--   Payment Schedule → FM Approve → Exec Authorise (new chain). On schedule terminal APPROVED,
--   every LPO in it flips to APPROVED (ready for disburse).
--
-- FM-created schedules auto-approve the FM step (creator ≠ approver rule) and jump to Exec.

-- ── 1. Chain rewrites (safe — checked in-flight approvals first) ────────
-- PETTY_CASH_APPROVAL step 2 (FM_APPROVE): repurpose to ACCOUNTANT_PREPARE + terminal.
UPDATE fin_chain_steps cs
  JOIN fin_chain_defs  cd ON cd.c0d = cs.chain_id
   SET cs.step_key      = 'ACCOUNTANT_PREPARE',
       cs.step_label    = 'Accountant Prepare',
       cs.role_required = 'ACCOUNTANT',
       cs.is_terminal   = 1,
       cs.description   = 'Accountant confirms disburse plan (method + account + scheduled date). LPO becomes BOOKED — awaits Payment Schedule.',
       cs.edited_on     = NOW()
 WHERE cd.chain_key = 'PETTY_CASH_APPROVAL' AND cs.step_key = 'FM_APPROVE' AND cs.deleted = 0;

UPDATE fin_chain_defs
   SET title = 'Internal Cash · PM → Accountant', edited_on = NOW()
 WHERE chain_key = 'PETTY_CASH_APPROVAL';

-- INTERNAL_CASH_LARGE_APPROVAL: Accountant Prepare becomes terminal, FM + Exec steps soft-deleted.
UPDATE fin_chain_steps cs
  JOIN fin_chain_defs  cd ON cd.c0d = cs.chain_id
   SET cs.is_terminal = 1,
       cs.description = 'Accountant confirms disburse plan (method + account + scheduled date). LPO becomes BOOKED — Payment Schedule handles the FM/Exec funds approval next.',
       cs.edited_on   = NOW()
 WHERE cd.chain_key = 'INTERNAL_CASH_LARGE_APPROVAL' AND cs.step_key = 'ACCOUNTANT_PREPARE' AND cs.deleted = 0;

UPDATE fin_chain_steps cs
  JOIN fin_chain_defs  cd ON cd.c0d = cs.chain_id
   SET cs.deleted = 1, cs.edited_on = NOW()
 WHERE cd.chain_key = 'INTERNAL_CASH_LARGE_APPROVAL' AND cs.step_key IN ('FM_APPROVE','EXEC_APPROVE') AND cs.deleted = 0;

UPDATE fin_chain_defs
   SET title = 'Internal Cash (Large) · PM → Accountant', edited_on = NOW()
 WHERE chain_key = 'INTERNAL_CASH_LARGE_APPROVAL';

-- ── 2. payment_schedules + payment_schedule_items ───────────────────────
CREATE TABLE IF NOT EXISTS payment_schedules (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    schedule_no VARCHAR(30) NOT NULL UNIQUE,
    currency VARCHAR(8) NOT NULL DEFAULT 'TZS',
    status ENUM('DRAFT','SUBMITTED','APPROVED','DECLINED','PAID','CANCELLED') NOT NULL DEFAULT 'DRAFT',
    total_amount DECIMAL(20,2) NOT NULL DEFAULT 0,
    item_count INT UNSIGNED NOT NULL DEFAULT 0,
    approval_id INT UNSIGNED NULL,
    scheduled_date DATE NULL,
    notes VARCHAR(1000) NULL,
    created_by INT UNSIGNED NULL,
    created_by_name VARCHAR(160) NULL,
    created_by_group VARCHAR(80) NULL,
    created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    edited_by INT UNSIGNED NULL,
    edited_on TIMESTAMP NULL,
    deleted TINYINT(1) NOT NULL DEFAULT 0,
    KEY idx_status (status),
    KEY idx_scheduled (scheduled_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payment_schedule_items (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    schedule_id INT UNSIGNED NOT NULL,
    lpo_id INT UNSIGNED NOT NULL,
    amount DECIMAL(20,2) NOT NULL,
    method ENUM('PETTY_CASH','CASH_HANDOVER','MOBILE_MONEY','BANK_TRANSFER','CHEQUE') NULL,
    account_id INT UNSIGNED NULL,
    added_by INT UNSIGNED NULL,
    added_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_lpo (lpo_id),
    KEY idx_schedule (schedule_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── 3. New chain PAYMENT_SCHEDULE_APPROVAL ─────────────────────────────
INSERT INTO fin_chain_defs (chain_key, title, description, status, created_by, created_on) VALUES
    ('PAYMENT_SCHEDULE_APPROVAL', 'Payment Schedule · FM Approve → Exec Authorise',
     'Batched payables approval — Accountant assembles a run, FM approves the whole batch, Exec authorises the outflow. If FM creates the schedule, their step auto-approves (creator ≠ approver).', 'ACTIVE', 0, NOW())
ON DUPLICATE KEY UPDATE title = VALUES(title), description = VALUES(description);

SET @sch_chain := (SELECT c0d FROM fin_chain_defs WHERE chain_key = 'PAYMENT_SCHEDULE_APPROVAL');
SET @sch_has_steps := (SELECT COUNT(*) FROM fin_chain_steps WHERE chain_id = @sch_chain AND deleted = 0);
INSERT INTO fin_chain_steps (chain_id, step_no, step_key, step_label, role_required, is_terminal, description, sort_order)
SELECT @sch_chain, 1, 'FM_APPROVE',   'FM Approve',     'FINANCE MANAGER', 0, 'Finance Manager approves the batched schedule.', 1
WHERE @sch_has_steps = 0 AND @sch_chain IS NOT NULL
UNION ALL
SELECT @sch_chain, 2, 'EXEC_APPROVE', 'Exec Authorise', 'EXECUTIVE',       1, 'Executive authorises the outflow. On terminal all LPOs in the batch move to APPROVED.', 2
WHERE @sch_has_steps = 0 AND @sch_chain IS NOT NULL;

-- ── 4. Perms ────────────────────────────────────────────────────────────
-- PaymentSchedules:
--   queue/create: Accountant + FM + Administrator (both can build schedules)
--   index/view:   Accountant + FM + Executive + Administrator
--   submit/addLpo/removeLpo: creators only (Accountant + FM + Admin)
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'PaymentSchedules', ac, 0, NOW()
FROM user_groups g
CROSS JOIN (SELECT 'index' AS ac UNION ALL SELECT 'view' UNION ALL SELECT 'queue'
             UNION ALL SELECT 'createAj' UNION ALL SELECT 'addLposAj' UNION ALL SELECT 'removeLpoAj'
             UNION ALL SELECT 'submitAj') actions
WHERE g.user_group_name IN ('Accountant','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 = actions.ac);

-- Executive sees index/view only (they approve via the chain, not by creating)
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'PaymentSchedules', ac, 0, NOW()
FROM user_groups g
CROSS JOIN (SELECT 'index' AS ac UNION ALL SELECT 'view') actions
WHERE g.user_group_name = 'Executive'
  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 = actions.ac);

-- ── 5. Test seed: 2 approved payee accounts per active staff user ─────
-- One M-Pesa, one CRDB Bank. Idempotent — skips if user already has any accounts.
-- Only for users in roles that raise cash requests.
SET @mpesa_id := (SELECT c0d FROM payment_providers WHERE provider_code = 'MPESA');
SET @crdb_id  := (SELECT c0d FROM payment_providers WHERE provider_code = 'CRDB');

INSERT INTO payee_accounts
    (payee_type, payee_id, payee_name, method, provider_id, provider_name,
     account_number, account_holder_name, branch_name, status, approved_by, approved_by_name, approved_on,
     is_default, notes, created_by, created_by_name)
SELECT
    'STAFF', u.c0d, u.full_name, 'MOBILE_MONEY', @mpesa_id, 'M-Pesa',
    CONCAT('0754', LPAD(u.c0d, 6, '0')), u.full_name, NULL,
    'APPROVED', 0, 'System seed', NOW(),
    1, 'Test seed account · slice 8Z-l', 0, 'System seed'
  FROM users u
  JOIN user_groups g ON g.c0d = u.user_group_id
 WHERE u.deleted = 0
   AND g.user_group_name IN ('Projects Coordinator','Project Manager','Procurement','Field Supervisor','Accountant','Finance Manager','Executive','Stores')
   AND NOT EXISTS (SELECT 1 FROM payee_accounts a WHERE a.payee_type = 'STAFF' AND a.payee_id = u.c0d AND a.deleted = 0);

INSERT INTO payee_accounts
    (payee_type, payee_id, payee_name, method, provider_id, provider_name,
     account_number, account_holder_name, branch_name, status, approved_by, approved_by_name, approved_on,
     is_default, notes, created_by, created_by_name)
SELECT
    'STAFF', u.c0d, u.full_name, 'BANK_TRANSFER', @crdb_id, 'CRDB Bank',
    CONCAT('0150', LPAD(u.c0d, 10, '0')), u.full_name, 'Head Office',
    'APPROVED', 0, 'System seed', NOW(),
    0, 'Test seed account · slice 8Z-l', 0, 'System seed'
  FROM users u
  JOIN user_groups g ON g.c0d = u.user_group_id
 WHERE u.deleted = 0
   AND g.user_group_name IN ('Projects Coordinator','Project Manager','Procurement','Field Supervisor','Accountant','Finance Manager','Executive','Stores')
   AND (SELECT COUNT(*) FROM payee_accounts a WHERE a.payee_type = 'STAFF' AND a.payee_id = u.c0d AND a.deleted = 0 AND a.method = 'BANK_TRANSFER') = 0;
