-- Slice 8Z-m · Payment proposal review (SAP F110 pattern)
--
-- FM reviews the schedule at their approval step: picks source account, adjusts per-line
-- amounts (partial pay), excludes items (delete + audit log), applies a group cap that
-- FIFO-allocates within a payee group. Residual amounts stay open on the LPO (amount_paid <
-- amount) and become schedulable again in the next run.
--
-- Also introduces company-side accounts (where OUR money leaves from) as a proper table.

-- ── 1. lpos partial-payment tracking ─────────────────────────────────────
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='amount_paid');
SET @sql := IF(@c=0, "ALTER TABLE lpos ADD COLUMN amount_paid DECIMAL(20,2) NOT NULL DEFAULT 0 AFTER 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='first_partial_paid_on');
SET @sql := IF(@c=0, "ALTER TABLE lpos ADD COLUMN first_partial_paid_on DATETIME NULL", 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ── 2. payment_schedule_items UNIQUE swap ────────────────────────────────
-- Drop old UNIQUE(lpo_id) so an LPO can appear on multiple schedules over time.
-- Add UNIQUE(lpo_id, schedule_id) so it can't appear twice on the same schedule.
-- Application rule (in model) blocks adding an LPO that's on another *active* schedule.
SET @has_old_uq := (SELECT COUNT(*) FROM information_schema.statistics
                    WHERE table_schema=@sch AND table_name='payment_schedule_items' AND index_name='uq_lpo');
SET @sql := IF(@has_old_uq > 0, "ALTER TABLE payment_schedule_items DROP INDEX uq_lpo", 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_new_uq := (SELECT COUNT(*) FROM information_schema.statistics
                    WHERE table_schema=@sch AND table_name='payment_schedule_items' AND index_name='uq_lpo_schedule');
SET @sql := IF(@has_new_uq = 0, "ALTER TABLE payment_schedule_items ADD UNIQUE KEY uq_lpo_schedule (lpo_id, schedule_id)", 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ── 3. company_bank_accounts (source accounts — where OUR money leaves from) ─
CREATE TABLE IF NOT EXISTS company_bank_accounts (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    account_name VARCHAR(160) NOT NULL,
    provider_id INT UNSIGNED NULL,
    provider_name VARCHAR(120) NULL,
    account_number VARCHAR(60) NOT NULL,
    currency VARCHAR(8) NOT NULL DEFAULT 'TZS',
    branch_name VARCHAR(120) NULL,
    swift_code VARCHAR(20) NULL,
    balance_hint DECIMAL(20,2) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    notes VARCHAR(500) NULL,
    created_by INT UNSIGNED NULL,
    created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    edited_by INT UNSIGNED NULL,
    edited_on TIMESTAMP NULL,
    KEY idx_currency (currency, is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed 3 test source accounts (CRDB Operating TZS, NMB USD, M-Pesa Business Till).
SET @crdb_id  := (SELECT c0d FROM payment_providers WHERE provider_code = 'CRDB');
SET @nmb_id   := (SELECT c0d FROM payment_providers WHERE provider_code = 'NMB');
SET @mpesa_id := (SELECT c0d FROM payment_providers WHERE provider_code = 'MPESA');

INSERT INTO company_bank_accounts (account_name, provider_id, provider_name, account_number, currency, branch_name, is_active, notes, created_by)
SELECT 'CRDB Operating (TZS)', @crdb_id, 'CRDB Bank', '01J1050123456', 'TZS', 'Head Office', 1, 'Main operating account. Default TZS source.', 0
WHERE NOT EXISTS (SELECT 1 FROM company_bank_accounts WHERE account_name = 'CRDB Operating (TZS)');

INSERT INTO company_bank_accounts (account_name, provider_id, provider_name, account_number, currency, branch_name, is_active, notes, created_by)
SELECT 'NMB USD', @nmb_id, 'NMB Bank', '20501234567890', 'USD', 'Head Office', 1, 'USD FX account for imports.', 0
WHERE NOT EXISTS (SELECT 1 FROM company_bank_accounts WHERE account_name = 'NMB USD');

INSERT INTO company_bank_accounts (account_name, provider_id, provider_name, account_number, currency, branch_name, is_active, notes, created_by)
SELECT 'M-Pesa Business Till', @mpesa_id, 'M-Pesa', '565656', 'TZS', NULL, 1, 'Vodacom M-Pesa Business till for outbound mobile-money batches.', 0
WHERE NOT EXISTS (SELECT 1 FROM company_bank_accounts WHERE account_name = 'M-Pesa Business Till');

-- ── 4. payment_schedules.source_account_id (FM picks at approval) ────────
SET @c := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@sch AND table_name='payment_schedules' AND column_name='source_account_id');
SET @sql := IF(@c=0, "ALTER TABLE payment_schedules ADD COLUMN source_account_id INT UNSIGNED NULL AFTER scheduled_date, ADD KEY idx_source (source_account_id)", 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ── 5. schedule_exclusion_audit (FM's excludes tracked with comment) ────
CREATE TABLE IF NOT EXISTS schedule_exclusion_audit (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    schedule_id INT UNSIGNED NOT NULL,
    lpo_id INT UNSIGNED NOT NULL,
    action ENUM('EXCLUDED','AMOUNT_REDUCED') NOT NULL,
    original_amount DECIMAL(20,2) NULL,
    new_amount DECIMAL(20,2) NULL,
    comment VARCHAR(1000) NULL,
    performed_by INT UNSIGNED NULL,
    performed_by_name VARCHAR(160) NULL,
    performed_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_schedule (schedule_id),
    KEY idx_lpo (lpo_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── 6. Perms ────────────────────────────────────────────────────────────
-- CompanyBankAccounts: FM + Admin manage; Executive views
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'CompanyBankAccounts', 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 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 = 'CompanyBankAccounts' 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, 'CompanyBankAccounts', 'index', 0, NOW()
FROM user_groups g
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 = 'CompanyBankAccounts' AND p.action_allowed = 'index');

-- New PaymentSchedules endpoints: FM edits at approval; Accountant/FM/Admin records disbursement
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 'updateItemAmountAj' AS ac UNION ALL SELECT 'excludeItemAj' UNION ALL SELECT 'applyGroupCapAj') 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 = 'PaymentSchedules' 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, 'PaymentSchedules', 'recordDisbursementAj', 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 = 'PaymentSchedules' AND p.action_allowed = 'recordDisbursementAj');

-- New FinChains endpoint for the proposal-review approval
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'FinChains', 'paymentScheduleApproveAj', 0, NOW()
FROM user_groups g
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 = 'FinChains' AND p.action_allowed = 'paymentScheduleApproveAj');
