-- Slice 8Z-d · Batch 2 · Retirement pipeline for cash advances.
--
-- One retirement per LPO. Requester enters spend lines + attaches receipts + returns unused cash.
-- Accountant reviews, approves or rejects. On approval → LPO marked RETIRED + best-effort journal posted.

-- ── 1. lpo_retirements header ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS lpo_retirements (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lpo_id INT UNSIGNED NOT NULL,
    status ENUM('DRAFT','SUBMITTED','APPROVED','REJECTED') NOT NULL DEFAULT 'DRAFT',
    total_spent DECIMAL(20,2) NOT NULL DEFAULT 0,
    cash_returned DECIMAL(20,2) NOT NULL DEFAULT 0,
    variance DECIMAL(20,2) NOT NULL DEFAULT 0,
    submitted_by INT UNSIGNED NULL,
    submitted_by_name VARCHAR(160) NULL,
    submitted_on DATETIME NULL,
    reviewer_id INT UNSIGNED NULL,
    reviewer_name VARCHAR(160) NULL,
    reviewer_notes VARCHAR(1000) NULL,
    reviewed_on DATETIME NULL,
    journal_entry_id INT UNSIGNED NULL,
    created_by INT UNSIGNED NULL,
    edited_by INT UNSIGNED NULL,
    deleted TINYINT(1) NOT NULL DEFAULT 0,
    created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    edited_on TIMESTAMP NULL,
    UNIQUE KEY uq_lpo (lpo_id),
    KEY idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── 2. lpo_retirement_lines ────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS lpo_retirement_lines (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    retirement_id INT UNSIGNED NOT NULL,
    line_no INT UNSIGNED NOT NULL DEFAULT 1,
    description VARCHAR(500) NOT NULL,
    amount DECIMAL(20,2) NOT NULL DEFAULT 0,
    coa_account_id INT UNSIGNED NULL,
    receipt_attachment_path VARCHAR(500) NULL,
    receipt_original_name VARCHAR(300) NULL,
    receipt_mime VARCHAR(120) NULL,
    receipt_size_bytes INT UNSIGNED NULL,
    notes VARCHAR(500) NULL,
    created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_retirement (retirement_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── 3. Perms ──────────────────────────────────────────────────────────────
-- draft/submit + line CRUD: any operational role (they raise + retire their own advances)
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'Retirements', ac, 0, NOW()
FROM user_groups g
CROSS JOIN (SELECT 'new' AS ac
             UNION ALL SELECT 'saveHeaderAj'
             UNION ALL SELECT 'addLineAj'
             UNION ALL SELECT 'updateLineAj'
             UNION ALL SELECT 'removeLineAj'
             UNION ALL SELECT 'submitAj'
             UNION ALL SELECT 'view'
             UNION ALL SELECT 'my'
             UNION ALL SELECT 'receiptDownload') actions
WHERE g.user_group_name IN ('Projects Coordinator','Project Manager','Procurement','Field Supervisor','Accountant','Finance Manager','Executive','Administrator','Stores')
  AND NOT EXISTS (
    SELECT 1 FROM user_groups_perms p
    WHERE p.user_group_id = g.c0d
      AND p.controller_allowed = 'Retirements'
      AND p.action_allowed = actions.ac
);

-- queue + approve/reject: Accountant + Admin + FM (FM oversees but Accountant is primary)
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'Retirements', ac, 0, NOW()
FROM user_groups g
CROSS JOIN (SELECT 'queue' AS ac UNION ALL SELECT 'approveAj' UNION ALL SELECT 'rejectAj') 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 = 'Retirements'
      AND p.action_allowed = actions.ac
);
