-- Slice 8Z-q · FM Verification enhancements for the Contract-signing tab
--
-- Ports the useful bits of the legacy hqdspera vOneTenderFMVerify page:
--   1. Fills 3 small schema gaps in tender_contract_terms
--        · payment_net_days      (structured net days, not just free-text payment_terms)
--        · pg_validity_months    (PG expiry — same shape as apg_validity_months)
--        · advance_conditions    (Tanzania tenders often include a triggering clause)
--   2. Adds tender_contract_bonds — one row per bond/receipt (APG, PG, retention bond,
--      insurance cert, TRA tax clearance…) with status + validity + document upload.
--      This is the SINGLE biggest FM decision gap — expiring PGs kill Tanzania contracts.
--   3. Adds tender_fm_verification_attachments — FM's own document category with a
--      structured subtype enum. Approve is gated on the 3 mandatory subtypes being present.
--   4. Grants perms to the new endpoints.

/* ── 1 · tender_contract_terms — add 3 columns ──────────────────────── */
ALTER TABLE tender_contract_terms
  ADD COLUMN payment_net_days     INT UNSIGNED NULL AFTER payment_basis,
  ADD COLUMN pg_validity_months   INT UNSIGNED NULL AFTER pg_bank,
  ADD COLUMN advance_conditions   TEXT         NULL AFTER apg_validity_months;

/* ── 2 · tender_contract_bonds — one row per bond / receipt ─────────── */
CREATE TABLE IF NOT EXISTS tender_contract_bonds (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tender_id INT UNSIGNED NOT NULL,

    bond_type ENUM(
        'CLIENT_ADVANCE',      -- cash advance received from the client
        'APG',                 -- Advance Payment Guarantee we issue to the client
        'PERFORMANCE_BOND',    -- Performance bond we issue to the client
        'RETENTION_BOND',      -- Retention bond (releases retention early)
        'INSURANCE_CERT',      -- CAR / Workers Comp / All-risk insurance certificate
        'TAX_CLEARANCE',       -- TRA tax clearance certificate (Tanzania)
        'OTHER_GUARANTEE'
    ) NOT NULL,

    status ENUM('PENDING','RECEIVED','NA','EXPIRED') NOT NULL DEFAULT 'PENDING'
        COMMENT 'PENDING = awaited · RECEIVED = in-hand · NA = not required · EXPIRED = past validity',

    reference_no VARCHAR(120) NULL COMMENT 'e.g. bond no, cheque no, cert no',
    issuer VARCHAR(180) NULL     COMMENT 'e.g. CRDB Bank · AAR Insurance · Vodacom · TRA',
    amount DECIMAL(20,2) NULL,
    currency VARCHAR(10) NOT NULL DEFAULT 'TZS',
    received_date DATE NULL,
    validity_until DATE NULL COMMENT 'when the bond / cert expires — drives renewal alerts',

    document_path VARCHAR(500) NULL,
    document_original_filename VARCHAR(255) NULL,
    document_mime VARCHAR(120) NULL,
    document_size_bytes INT UNSIGNED NULL,
    remarks TEXT NULL,

    deleted TINYINT(1) NOT NULL DEFAULT 0,
    created_by INT UNSIGNED NULL,
    created_by_name VARCHAR(160) NULL,
    created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    edited_by INT UNSIGNED NULL,
    edited_on TIMESTAMP NULL,

    KEY tender_idx (tender_id, deleted, bond_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/* ── 3 · tender_fm_verification_attachments — FM-owned docs ─────────── */
CREATE TABLE IF NOT EXISTS tender_fm_verification_attachments (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tender_id INT UNSIGNED NOT NULL,

    subtype ENUM(
        'COVER_MEMO',              -- MANDATORY
        'CASH_FLOW',               -- MANDATORY
        'PROFITABILITY_MODEL',     -- MANDATORY
        'BANK_FACILITY_LETTER',
        'CREDIT_ASSESSMENT',
        'FX_HEDGING_MEMO',
        'INSURANCE_CHECKLIST',
        'RELATED_PARTY_DISCLOSURE',
        'OTHER'
    ) NOT NULL DEFAULT 'OTHER',

    label VARCHAR(200) NULL,
    original_filename VARCHAR(255) NOT NULL,
    stored_path VARCHAR(500) NOT NULL,
    mime_type VARCHAR(120) NULL,
    size_bytes INT UNSIGNED NOT NULL DEFAULT 0,
    notes VARCHAR(500) NULL,

    deleted TINYINT(1) NOT NULL DEFAULT 0,
    uploaded_by INT UNSIGNED NULL,
    uploaded_by_name VARCHAR(160) NULL,
    uploaded_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    KEY tender_idx (tender_id, deleted, subtype)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/* ── 4 · Perms ──────────────────────────────────────────────────────── */

-- Read: all senior roles that see the contract tab
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'Tenders', 'bondsListAj', 0, NOW()
FROM user_groups g
WHERE g.user_group_name IN ('Administrator','Finance Manager','Executive','Projects Coordinator','Project Manager','Accountant','CRM')
  AND NOT EXISTS (SELECT 1 FROM user_groups_perms p WHERE p.user_group_id = g.c0d AND p.controller_allowed = 'Tenders' AND p.action_allowed = 'bondsListAj');

INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'Tenders', 'bondDocDownload', 0, NOW()
FROM user_groups g
WHERE g.user_group_name IN ('Administrator','Finance Manager','Executive','Projects Coordinator','Project Manager','Accountant','CRM')
  AND NOT EXISTS (SELECT 1 FROM user_groups_perms p WHERE p.user_group_id = g.c0d AND p.controller_allowed = 'Tenders' AND p.action_allowed = 'bondDocDownload');

INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'Tenders', 'fmAttachListAj', 0, NOW()
FROM user_groups g
WHERE g.user_group_name IN ('Administrator','Finance Manager','Executive','Projects Coordinator','Project Manager','Accountant','CRM')
  AND NOT EXISTS (SELECT 1 FROM user_groups_perms p WHERE p.user_group_id = g.c0d AND p.controller_allowed = 'Tenders' AND p.action_allowed = 'fmAttachListAj');

INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'Tenders', 'fmAttachDownload', 0, NOW()
FROM user_groups g
WHERE g.user_group_name IN ('Administrator','Finance Manager','Executive','Projects Coordinator','Project Manager','Accountant','CRM')
  AND NOT EXISTS (SELECT 1 FROM user_groups_perms p WHERE p.user_group_id = g.c0d AND p.controller_allowed = 'Tenders' AND p.action_allowed = 'fmAttachDownload');

-- Write: FM + Admin only
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'Tenders', 'bondSaveAj', 0, NOW()
FROM user_groups g
WHERE g.user_group_name IN ('Administrator','Finance Manager')
  AND NOT EXISTS (SELECT 1 FROM user_groups_perms p WHERE p.user_group_id = g.c0d AND p.controller_allowed = 'Tenders' AND p.action_allowed = 'bondSaveAj');

INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'Tenders', 'bondRemoveAj', 0, NOW()
FROM user_groups g
WHERE g.user_group_name IN ('Administrator','Finance Manager')
  AND NOT EXISTS (SELECT 1 FROM user_groups_perms p WHERE p.user_group_id = g.c0d AND p.controller_allowed = 'Tenders' AND p.action_allowed = 'bondRemoveAj');

INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'Tenders', 'fmAttachUploadAj', 0, NOW()
FROM user_groups g
WHERE g.user_group_name IN ('Administrator','Finance Manager')
  AND NOT EXISTS (SELECT 1 FROM user_groups_perms p WHERE p.user_group_id = g.c0d AND p.controller_allowed = 'Tenders' AND p.action_allowed = 'fmAttachUploadAj');

INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'Tenders', 'fmAttachRemoveAj', 0, NOW()
FROM user_groups g
WHERE g.user_group_name IN ('Administrator','Finance Manager')
  AND NOT EXISTS (SELECT 1 FROM user_groups_perms p WHERE p.user_group_id = g.c0d AND p.controller_allowed = 'Tenders' AND p.action_allowed = 'fmAttachRemoveAj');
