-- Slice 8Z-k · Payee accounts + payment methods (Tanzania-first).
--
-- Registers approved disbursement destinations (bank / mobile money / cheque) per payee
-- (STAFF/VENDOR/CUSTOMER). Accounts start PENDING, get approved by FM (or FM+Exec if the
-- associated LPO amount is above the exec threshold). Adds requester-preference + Accountant-
-- confirmed disburse fields to lpos, plus a disbursement audit trail.
--
-- Provider list is admin-editable so a new bank / mobile-money operator can be added without
-- a schema change. Seeded with the full Tanzania set.

-- ── 1. payment_providers (admin-editable lookup) ────────────────────────
CREATE TABLE IF NOT EXISTS payment_providers (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    provider_code VARCHAR(30) NOT NULL,
    provider_name VARCHAR(120) NOT NULL,
    method ENUM('MOBILE_MONEY','BANK_TRANSFER','CHEQUE') NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    sort_order INT NOT NULL DEFAULT 100,
    created_by INT UNSIGNED NULL,
    created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    edited_by INT UNSIGNED NULL,
    edited_on TIMESTAMP NULL,
    UNIQUE KEY uq_provider_code (provider_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed Tanzania providers (idempotent via UNIQUE KEY).
INSERT INTO payment_providers (provider_code, provider_name, method, sort_order) VALUES
    ('MPESA',    'M-Pesa',         'MOBILE_MONEY', 10),
    ('TIGOPESA', 'Tigo Pesa',      'MOBILE_MONEY', 20),
    ('AIRTEL',   'Airtel Money',   'MOBILE_MONEY', 30),
    ('HALOPESA', 'HaloPesa',       'MOBILE_MONEY', 40),
    ('TPESA',    'T-Pesa',         'MOBILE_MONEY', 50),
    ('CRDB',     'CRDB Bank',      'BANK_TRANSFER', 110),
    ('NMB',      'NMB Bank',       'BANK_TRANSFER', 120),
    ('NBC',      'NBC',            'BANK_TRANSFER', 130),
    ('STANBIC',  'Stanbic Bank',   'BANK_TRANSFER', 140),
    ('EXIM',     'Exim Bank',      'BANK_TRANSFER', 150),
    ('DTB',      'Diamond Trust Bank', 'BANK_TRANSFER', 160),
    ('EQUITY',   'Equity Bank',    'BANK_TRANSFER', 170),
    ('ABSA',     'Absa Bank',      'BANK_TRANSFER', 180),
    ('KCB',      'KCB Bank',       'BANK_TRANSFER', 190),
    ('BOA',      'Bank of Africa', 'BANK_TRANSFER', 200),
    ('TIB',      'TIB Development Bank','BANK_TRANSFER', 210),
    ('STANCHART','Standard Chartered', 'BANK_TRANSFER', 220),
    ('AZANIA',   'Azania Bank',    'BANK_TRANSFER', 230),
    ('ACCESS',   'Access Bank',    'BANK_TRANSFER', 240),
    ('IANDM',    'I&M Bank',       'BANK_TRANSFER', 250),
    ('CHEQUE',   'Cheque',         'CHEQUE', 900)
ON DUPLICATE KEY UPDATE provider_name = VALUES(provider_name);

-- ── 2. payee_accounts (approved destinations per payee) ─────────────────
CREATE TABLE IF NOT EXISTS payee_accounts (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    payee_type ENUM('STAFF','VENDOR','CUSTOMER') NOT NULL,
    payee_id INT UNSIGNED NOT NULL,
    payee_name VARCHAR(200) NOT NULL,
    method ENUM('MOBILE_MONEY','BANK_TRANSFER','CHEQUE') NOT NULL,
    provider_id INT UNSIGNED NULL,
    provider_name VARCHAR(120) NULL,
    account_number VARCHAR(60) NOT NULL,
    account_holder_name VARCHAR(200) NOT NULL,
    branch_name VARCHAR(120) NULL,
    swift_code VARCHAR(20) NULL,
    status ENUM('PENDING','APPROVED','DISABLED') NOT NULL DEFAULT 'PENDING',
    approval_id INT UNSIGNED NULL,
    approved_by INT UNSIGNED NULL,
    approved_by_name VARCHAR(160) NULL,
    approved_on DATETIME NULL,
    is_default TINYINT(1) NOT NULL DEFAULT 0,
    notes VARCHAR(500) NULL,
    created_by INT UNSIGNED NULL,
    created_by_name VARCHAR(160) 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_payee (payee_type, payee_id),
    KEY idx_status (status),
    KEY idx_provider (provider_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── 3. payee_account_audit (change trail — every add/approve/disable/edit/ad-hoc use) ─
CREATE TABLE IF NOT EXISTS payee_account_audit (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    account_id INT UNSIGNED NOT NULL,
    action ENUM('CREATE','APPROVE','DISABLE','EDIT','USE_AD_HOC','REACTIVATE') NOT NULL,
    performed_by INT UNSIGNED NULL,
    performed_by_name VARCHAR(160) NULL,
    performed_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    lpo_id INT UNSIGNED NULL,
    notes VARCHAR(1000) NULL,
    KEY idx_account (account_id),
    KEY idx_action (action)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── 4. lpos disburse fields ─────────────────────────────────────────────
-- Requester-side preference (what they'd like) + Accountant-confirmed disburse plan
-- + disbursement record (ref, timestamp, actor).
SET @sch := DATABASE();
SET @tbl := 'lpos';

SET @c := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@sch AND table_name=@tbl AND column_name='preferred_method');
SET @sql := IF(@c=0, "ALTER TABLE lpos ADD COLUMN preferred_method ENUM('PETTY_CASH','CASH_HANDOVER','MOBILE_MONEY','BANK_TRANSFER','CHEQUE') NULL AFTER return_to_float_amount", 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @c := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@sch AND table_name=@tbl AND column_name='preferred_account_id');
SET @sql := IF(@c=0, "ALTER TABLE lpos ADD COLUMN preferred_account_id INT UNSIGNED NULL", 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @c := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@sch AND table_name=@tbl AND column_name='disburse_method');
SET @sql := IF(@c=0, "ALTER TABLE lpos ADD COLUMN disburse_method ENUM('PETTY_CASH','CASH_HANDOVER','MOBILE_MONEY','BANK_TRANSFER','CHEQUE') NULL", 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @c := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@sch AND table_name=@tbl AND column_name='disburse_account_id');
SET @sql := IF(@c=0, "ALTER TABLE lpos ADD COLUMN disburse_account_id INT UNSIGNED NULL", 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @c := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@sch AND table_name=@tbl AND column_name='accountant_prep_notes');
SET @sql := IF(@c=0, "ALTER TABLE lpos ADD COLUMN accountant_prep_notes VARCHAR(1000) NULL", 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @c := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@sch AND table_name=@tbl AND column_name='override_reason');
SET @sql := IF(@c=0, "ALTER TABLE lpos ADD COLUMN override_reason VARCHAR(1000) NULL", 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @c := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@sch AND table_name=@tbl AND column_name='scheduled_disburse_date');
SET @sql := IF(@c=0, "ALTER TABLE lpos ADD COLUMN scheduled_disburse_date DATE NULL", 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @c := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@sch AND table_name=@tbl AND column_name='disburse_ref');
SET @sql := IF(@c=0, "ALTER TABLE lpos ADD COLUMN disburse_ref VARCHAR(120) NULL", 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @c := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@sch AND table_name=@tbl AND column_name='disbursed_at');
SET @sql := IF(@c=0, "ALTER TABLE lpos ADD COLUMN disbursed_at DATETIME NULL", 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @c := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@sch AND table_name=@tbl AND column_name='disbursed_by');
SET @sql := IF(@c=0, "ALTER TABLE lpos ADD COLUMN disbursed_by INT UNSIGNED NULL", 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @c := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@sch AND table_name=@tbl AND column_name='disbursed_by_name');
SET @sql := IF(@c=0, "ALTER TABLE lpos ADD COLUMN disbursed_by_name VARCHAR(160) NULL", 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ── 5. finance_settings extensions ──────────────────────────────────────
INSERT INTO finance_settings (setting_key, setting_value, description) VALUES
    ('mobile_money_max',                '2000000', 'Preferred cap for mobile-money disbursements (TZS). Above this Accountant should default to bank transfer.'),
    ('petty_cash_max',                  '200000',  'Preferred cap for petty-cash disbursements (TZS). Above this Accountant should default to mobile money or bank.'),
    ('cash_handover_requires_reason',   '1',       'Boolean · when 1 Accountant must justify picking CASH_HANDOVER method (audit trail).'),
    ('require_approved_account',        '0',       'Boolean · when 1 Accountant cannot disburse to a PENDING/ad-hoc account. Default 0 (allow with reason).'),
    ('account_exec_approval_threshold', '5000000', 'Payee accounts backing LPOs above this (TZS) require FM + Exec approval; below → FM only.')
ON DUPLICATE KEY UPDATE description = VALUES(description);

-- ── 6. Approval chains for payee-account approval ──────────────────────
INSERT INTO fin_chain_defs (chain_key, title, description, status, created_by, created_on) VALUES
    ('PAYEE_ACCOUNT_APPROVAL_FM',      'Payee Account · FM Approve',            'Fast-track approval for payee accounts backing payments up to the exec threshold.', 'ACTIVE', 0, NOW()),
    ('PAYEE_ACCOUNT_APPROVAL_FM_EXEC', 'Payee Account · FM Approve → Exec Authorise', 'Payee accounts backing large payments (above the exec threshold) get an extra Exec gate.', 'ACTIVE', 0, NOW())
ON DUPLICATE KEY UPDATE title = VALUES(title);

-- Steps (idempotent — insert only if the chain has no steps yet).
SET @fm_chain := (SELECT c0d FROM fin_chain_defs WHERE chain_key = 'PAYEE_ACCOUNT_APPROVAL_FM');
SET @fm_has_steps := (SELECT COUNT(*) FROM fin_chain_steps WHERE chain_id = @fm_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 @fm_chain, 1, 'FM_APPROVE', 'FM Approve', 'FINANCE MANAGER', 1, 'Finance Manager approves the payee account.', 1
WHERE @fm_has_steps = 0 AND @fm_chain IS NOT NULL;

SET @big_chain := (SELECT c0d FROM fin_chain_defs WHERE chain_key = 'PAYEE_ACCOUNT_APPROVAL_FM_EXEC');
SET @big_has_steps := (SELECT COUNT(*) FROM fin_chain_steps WHERE chain_id = @big_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 @big_chain, 1, 'FM_APPROVE',   'FM Approve',      'FINANCE MANAGER', 0, 'Finance Manager approves.',            1
WHERE @big_has_steps = 0 AND @big_chain IS NOT NULL
UNION ALL
SELECT @big_chain, 2, 'EXEC_APPROVE', 'Exec Authorise',  'EXECUTIVE',       1, 'Executive authorises (large amounts).', 2
WHERE @big_has_steps = 0 AND @big_chain IS NOT NULL;

-- ── 7. Perms ────────────────────────────────────────────────────────────
-- PaymentProviders: Admin only (system data).
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'PaymentProviders', ac, 0, NOW()
FROM user_groups g
CROSS JOIN (SELECT 'index' AS ac UNION ALL SELECT 'upsertAj' UNION ALL SELECT 'toggleActiveAj') actions
WHERE g.user_group_name = 'Administrator'
  AND NOT EXISTS (SELECT 1 FROM user_groups_perms p WHERE p.user_group_id = g.c0d AND p.controller_allowed = 'PaymentProviders' AND p.action_allowed = actions.ac);

-- PayeeAccounts:
--   my/newAj:            everyone who raises requests (all operational roles)
--   index/approveAj/
--   disableAj:           FM + Executive + Administrator (approvers)
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'PayeeAccounts', ac, 0, NOW()
FROM user_groups g
CROSS JOIN (SELECT 'my' AS ac UNION ALL SELECT 'newAj' UNION ALL SELECT 'listAj') actions
WHERE g.user_group_name IN ('Projects Coordinator','Project Manager','Procurement','Field Supervisor','Accountant','Finance Manager','Executive','Administrator','Stores','CRM')
  AND NOT EXISTS (SELECT 1 FROM user_groups_perms p WHERE p.user_group_id = g.c0d AND p.controller_allowed = 'PayeeAccounts' 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, 'PayeeAccounts', ac, 0, NOW()
FROM user_groups g
CROSS JOIN (SELECT 'index' AS ac UNION ALL SELECT 'approveAj' UNION ALL SELECT 'disableAj' UNION ALL SELECT 'reactivateAj') actions
WHERE g.user_group_name IN ('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 = 'PayeeAccounts' AND p.action_allowed = actions.ac);

-- Lpos: markDisbursedAj (Accountant + FM + Admin post-approval)
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'Lpos', 'markDisbursedAj', 0, NOW()
FROM user_groups g
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 = 'Lpos' AND p.action_allowed = 'markDisbursedAj');

-- FinChains: accountantPrepAj (Accountant + Admin — used at the ACCOUNTANT_PREPARE step)
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'FinChains', 'accountantPrepAj', 0, NOW()
FROM user_groups g
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 = 'FinChains' AND p.action_allowed = 'accountantPrepAj');
