-- Slice 8Z-j · Procurement Batch 4 · refinement pass.
--
-- 1) finance_settings table (single-source config for Finance-owned tunables — Discovery caps
--    initially; extensible for other thresholds later).
-- 2) INTERNAL_CASH_LARGE_APPROVAL chain rewritten to 4-step (Option B): PM → Accountant → FM → Exec.
--    Applies to every internal-cash type once above threshold.
-- 3) Encoding hotfix on the chain title (mojibake from earlier UTF-8/CP1252 mishap).
-- 4) Perms for FinanceSettings controller (FM + Executive + Administrator).

-- ── 1. finance_settings ────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS finance_settings (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(60) NOT NULL,
    setting_value VARCHAR(500) NOT NULL,
    description VARCHAR(500) NULL,
    edited_by INT UNSIGNED NULL,
    edited_by_name VARCHAR(160) NULL,
    edited_on TIMESTAMP NULL,
    created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_setting_key (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed the two Discovery defaults (5% cap, 3% Exec threshold). Idempotent via UNIQUE KEY.
INSERT INTO finance_settings (setting_key, setting_value, description)
VALUES
    ('discovery_cap_pct',            '5', 'Product Discovery request cannot exceed this % of the Client PO total (hard block).'),
    ('discovery_exec_threshold_pct', '3', 'Product Discovery requests above this % of the Client PO total route to Exec approval.')
ON DUPLICATE KEY UPDATE description = VALUES(description);

-- ── 2 + 3. Chain rewrite + title hotfix ────────────────────────────────
-- Insert Accountant Prepare step between PM Check (1) and FM Approve (currently 2, becomes 3).
-- Idempotency: guarded on step_key ACCOUNTANT_PREPARE existing.

SET @chain_id := (SELECT c0d FROM fin_chain_defs WHERE chain_key = 'INTERNAL_CASH_LARGE_APPROVAL');
SET @has_acct := (SELECT COUNT(*) FROM fin_chain_steps
                  WHERE chain_id = @chain_id AND step_key = 'ACCOUNTANT_PREPARE' AND deleted = 0);

-- Only run the rewrite if the Accountant step isn't already in place
SET @sql := IF(@has_acct = 0 AND @chain_id IS NOT NULL, '
    UPDATE fin_chain_steps SET step_no = 999, sort_order = 999
      WHERE chain_id = @chain_id AND step_key = ''FM_APPROVE'' AND deleted = 0
', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(@has_acct = 0 AND @chain_id IS NOT NULL, '
    UPDATE fin_chain_steps SET step_no = 998, sort_order = 998
      WHERE chain_id = @chain_id AND step_key = ''EXEC_APPROVE'' AND deleted = 0
', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(@has_acct = 0 AND @chain_id IS NOT NULL, '
    INSERT INTO fin_chain_steps
        (chain_id, step_no, step_key, step_label, role_required, is_terminal, description, sort_order)
    VALUES
        (@chain_id, 2, ''ACCOUNTANT_PREPARE'', ''Accountant Prepare'', ''ACCOUNTANT'', 0,
         ''Accountant prepares the disbursement — validates budget line + schedules PV / petty cash path.'', 2)
', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(@has_acct = 0 AND @chain_id IS NOT NULL, '
    UPDATE fin_chain_steps SET step_no = 3, sort_order = 3
      WHERE chain_id = @chain_id AND step_key = ''FM_APPROVE'' AND deleted = 0
', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(@has_acct = 0 AND @chain_id IS NOT NULL, '
    UPDATE fin_chain_steps SET step_no = 4, sort_order = 4
      WHERE chain_id = @chain_id AND step_key = ''EXEC_APPROVE'' AND deleted = 0
', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Title hotfix: overwrite the mojibake with clean UTF-8 (unconditional — always safe to rewrite).
UPDATE fin_chain_defs
   SET title = 'Internal Cash (Large) · PM → Accountant → FM → Exec'
 WHERE chain_key = 'INTERNAL_CASH_LARGE_APPROVAL';

-- ── 4. Perms ────────────────────────────────────────────────────────────
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'FinanceSettings', ac, 0, NOW()
FROM user_groups g
CROSS JOIN (SELECT 'index' AS ac UNION ALL SELECT 'updateAj') 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 = 'FinanceSettings'
      AND p.action_allowed = actions.ac
);
