-- Slice 8Z-e · Batch 3 · Petty cash box + movements ledger.
--
-- One physical cash tin, held by an Accountant (custodian). Path A of the disbursement fork:
-- small APPROVED cash LPOs get paid straight from this box instead of drafting a PV.
-- Every disbursement, replenishment (float top-up), return (from a retirement), and
-- adjustment is journalled here — box balance == float − sum(disburse) + sum(return) + sum(replenish) + sum(adj).

-- ── 1. petty_cash_box (MVP: one row) ───────────────────────────────────────
CREATE TABLE IF NOT EXISTS petty_cash_box (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL DEFAULT 'Head Office Petty Cash',
    custodian_id INT UNSIGNED NULL,
    custodian_name VARCHAR(160) NULL,
    currency VARCHAR(8) NOT NULL DEFAULT 'TZS',
    float_amount DECIMAL(20,2) NOT NULL DEFAULT 0,
    current_balance DECIMAL(20,2) NOT NULL DEFAULT 0,
    max_disbursement DECIMAL(20,2) NOT NULL DEFAULT 200000,
    low_water_pct TINYINT UNSIGNED NOT NULL DEFAULT 30,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_by INT UNSIGNED NULL,
    edited_by INT UNSIGNED NULL,
    created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    edited_on TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed a single box row (idempotent).
INSERT INTO petty_cash_box (name, currency, float_amount, current_balance, max_disbursement, low_water_pct)
SELECT 'Head Office Petty Cash', 'TZS', 0, 0, 200000, 30
WHERE NOT EXISTS (SELECT 1 FROM petty_cash_box);

-- ── 2. petty_cash_movements ────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS petty_cash_movements (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    box_id INT UNSIGNED NOT NULL,
    movement_type ENUM('DISBURSE','REPLENISH','RETURN','ADJUSTMENT') NOT NULL,
    amount DECIMAL(20,2) NOT NULL,
    balance_after DECIMAL(20,2) NOT NULL,
    related_lpo_id INT UNSIGNED NULL,
    related_retirement_id INT UNSIGNED NULL,
    narrative VARCHAR(500) NULL,
    performed_by INT UNSIGNED NULL,
    performed_by_name VARCHAR(160) NULL,
    performed_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_box (box_id),
    KEY idx_lpo (related_lpo_id),
    KEY idx_ret (related_retirement_id),
    KEY idx_type (movement_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── 3. Perms ──────────────────────────────────────────────────────────────
-- Read + disburse + replenish: Accountant primary, FM oversees, Admin. Nobody else sees the box.
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'PettyCashBox', ac, 0, NOW()
FROM user_groups g
CROSS JOIN (SELECT 'index' AS ac
             UNION ALL SELECT 'movements'
             UNION ALL SELECT 'disburseAj'
             UNION ALL SELECT 'replenishAj'
             UNION ALL SELECT 'adjustmentAj') 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 = 'PettyCashBox'
      AND p.action_allowed = actions.ac
);

-- Config (edit float/threshold/custodian): FM + Admin only.
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'PettyCashBox', 'configAj', 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 = 'PettyCashBox'
      AND p.action_allowed = 'configAj'
);
