-- Slice 8Z-q · Retirement enrichment + receipt confirmation + Related-LPOs support.
--
-- Adds:
--  1) lpo_retirement_receipts: cross-cutting receipts (one receipt may back many spend lines).
--     Receipt types NORMAL / EFD_VAT / EFD_NON_VAT with TZS EFD fields (receipt no, TIN, VAT amount).
--  2) lpo_retirement_line_attachments: photos / extra proof per spend line (junction, capped at 4 in UI).
--  3) lpo_retirement_lines.receipt_id: link to the receipt this line belongs to (nullable — cash-with-no-receipt lines still valid).
--  4) lpos.receipt_confirmed_* + retirement_due_date: Procurement (requester) confirms receipt post-payment;
--     retirement clock starts from that confirmation + 14 days (finance-settings default).
--  5) finance_settings: retirement_default_days = 14, receipt_auto_confirm_days = 5.
--  6) Perms: Lpos.confirmReceiptAj (all requester roles), receiptDownload on Retirements.

-- ── 1. lpo_retirement_receipts ─────────────────────────────────────────
CREATE TABLE IF NOT EXISTS lpo_retirement_receipts (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    retirement_id INT UNSIGNED NOT NULL,
    receipt_type ENUM('NORMAL','EFD_VAT','EFD_NON_VAT') NOT NULL DEFAULT 'NORMAL',
    subject VARCHAR(500) NOT NULL COMMENT 'Required — describes what this receipt covers (e.g. "3 SIM cards + top-up")',
    supplier_name VARCHAR(200) NULL,
    efd_receipt_no VARCHAR(60) NULL,
    efd_date DATE NULL,
    supplier_tin VARCHAR(30) NULL,
    gross_amount DECIMAL(20,2) NOT NULL DEFAULT 0,
    vat_amount DECIMAL(20,2) NULL,
    attachment_path VARCHAR(500) NULL,
    attachment_original_name VARCHAR(300) NULL,
    attachment_mime VARCHAR(120) NULL,
    attachment_size_bytes INT UNSIGNED NULL,
    notes VARCHAR(500) NULL,
    created_by INT UNSIGNED NULL,
    created_by_name VARCHAR(160) NULL,
    created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    edited_on TIMESTAMP NULL,
    KEY idx_retirement (retirement_id),
    KEY idx_type (receipt_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── 2. lpo_retirement_line_attachments ─────────────────────────────────
CREATE TABLE IF NOT EXISTS lpo_retirement_line_attachments (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    line_id INT UNSIGNED NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    original_name VARCHAR(300) NOT NULL,
    mime VARCHAR(120) NOT NULL,
    size_bytes INT UNSIGNED NOT NULL,
    kind ENUM('PHOTO','OTHER') NOT NULL DEFAULT 'PHOTO',
    uploaded_by INT UNSIGNED NULL,
    uploaded_by_name VARCHAR(160) NULL,
    uploaded_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_line (line_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── 3. lpo_retirement_lines.receipt_id ─────────────────────────────────
SET @sch := DATABASE();
SET @c := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@sch AND table_name='lpo_retirement_lines' AND column_name='receipt_id');
SET @sql := IF(@c=0, "ALTER TABLE lpo_retirement_lines ADD COLUMN receipt_id INT UNSIGNED NULL AFTER coa_account_id, ADD KEY idx_receipt (receipt_id)", 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ── 4. lpos: receipt confirmation + retirement due date ─────────────────
SET @c := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@sch AND table_name='lpos' AND column_name='receipt_confirmed_at');
SET @sql := IF(@c=0, "ALTER TABLE lpos
    ADD COLUMN receipt_confirmed_at DATETIME NULL AFTER disbursed_at,
    ADD COLUMN receipt_confirmed_by INT UNSIGNED NULL,
    ADD COLUMN receipt_confirmed_by_name VARCHAR(160) NULL,
    ADD COLUMN receipt_confirm_note VARCHAR(500) NULL,
    ADD COLUMN receipt_confirm_auto TINYINT(1) NOT NULL DEFAULT 0,
    ADD COLUMN retirement_due_date DATE NULL",
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ── 5. finance_settings ────────────────────────────────────────────────
INSERT INTO finance_settings (setting_key, setting_value, description) VALUES
    ('retirement_default_days',   '14', 'Default days from receipt confirmation before retirement becomes overdue.'),
    ('receipt_auto_confirm_days', '5',  'Days after payment before receipt is auto-confirmed if the requester never confirms.')
ON DUPLICATE KEY UPDATE description = VALUES(description);

-- ── 6. Perms ───────────────────────────────────────────────────────────
-- Lpos.confirmReceiptAj: any operational role (they raise + retire cash requests)
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'Lpos', 'confirmReceiptAj', 0, NOW()
FROM user_groups g
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 = 'Lpos' AND p.action_allowed = 'confirmReceiptAj');

-- Retirements: new endpoints — cross-cutting receipts + per-line attachments + line-receipt link + downloads
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 'addReceiptAj' AS ac UNION ALL SELECT 'updateReceiptAj' UNION ALL SELECT 'removeReceiptAj'
             UNION ALL SELECT 'addLineAttachmentAj' UNION ALL SELECT 'removeLineAttachmentAj'
             UNION ALL SELECT 'setLineReceiptAj'
             UNION ALL SELECT 'receiptDownload' UNION ALL SELECT 'receiptFileDownload' UNION ALL SELECT 'lineAttachmentDownload') 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 = 'Retirements' AND p.action_allowed = actions.ac);
