/* ─────────────────────────────────────────────────────────────────────────────
   Slice 8Z-z-D · Spare-part requests + POC/PM/Procurement approval chain
   Two new tables + SPARE_REQUEST_APPROVAL fin_chain seed + perms.
   ───────────────────────────────────────────────────────────────────────────── */

/* ── Spare requests ─────────────────────────────────────────────────────────── */
CREATE TABLE IF NOT EXISTS spare_requests (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    request_no VARCHAR(30) NOT NULL,
    contract_id INT UNSIGNED NOT NULL,
    site_id INT UNSIGNED NOT NULL,
    callout_id INT UNSIGNED NULL COMMENT 'Optional link to originating callout',
    urgency ENUM('EMERGENCY','URGENT','ROUTINE') NOT NULL DEFAULT 'ROUTINE',
    who_pays ENUM('CLIENT','INTERNAL','MIXED','TBD') NOT NULL DEFAULT 'TBD',
    status ENUM('DRAFT','AWAITING_POC','POC_APPROVED','POC_DECLINED',
                'AWAITING_PM','PM_APPROVED','PM_DECLINED',
                'WITH_PROCUREMENT','STOCK_ISSUED','SOURCING','ORDERED',
                'DELIVERED','INSTALLED','CLOSED','CANCELLED')
        NOT NULL DEFAULT 'DRAFT',
    procurement_decision ENUM('FROM_STOCK','PROCURE','EMERGENCY_CASH') NULL,
    poc_id INT UNSIGNED NULL COMMENT 'Which site POC gave the nod',
    poc_approved_on TIMESTAMP NULL,
    poc_confirmation_method ENUM('EMAIL','WHATSAPP','PHONE','ONSITE_SIGNATURE','OTHER') NULL,
    poc_attachment_path VARCHAR(500) NULL,
    linked_lpo_id INT UNSIGNED NULL COMMENT 'When procurement decides PROCURE',
    linked_cash_request_id INT UNSIGNED NULL COMMENT 'When EMERGENCY_CASH used',
    reason VARCHAR(500) NULL,
    notes TEXT NULL,
    created_by INT UNSIGNED NULL,
    created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    edited_by INT UNSIGNED NULL,
    edited_on TIMESTAMP NULL,
    deleted TINYINT(1) NOT NULL DEFAULT 0,
    UNIQUE KEY uq_request_no (request_no),
    KEY idx_contract (contract_id, deleted),
    KEY idx_site (site_id, deleted),
    KEY idx_status (status, deleted),
    KEY idx_callout (callout_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/* ── Spare request line items ──────────────────────────────────────────────── */
CREATE TABLE IF NOT EXISTS spare_request_items (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    request_id INT UNSIGNED NOT NULL,
    part_id INT UNSIGNED NULL COMMENT 'FK spare_parts.c0d; NULL when free-text (part not yet in catalogue)',
    part_name_free VARCHAR(200) NULL COMMENT 'Free-text name when part_id is null',
    qty DECIMAL(12,4) NOT NULL DEFAULT 1,
    uom VARCHAR(16) NULL,
    unit_cost_estimate DECIMAL(14,2) NULL,
    who_pays_line ENUM('CLIENT','INTERNAL') NULL COMMENT 'Per-line override; NULL inherits header',
    notes VARCHAR(500) NULL,
    deleted TINYINT(1) NOT NULL DEFAULT 0,
    KEY idx_request (request_id, deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/* ── SPARE_REQUEST_APPROVAL chain (3 steps: PC/POC → PM → Procurement) ─────── */
INSERT INTO fin_chain_defs (chain_key, title, description, status, created_by)
SELECT 'SPARE_REQUEST_APPROVAL',
       'Spare Request Approval',
       'PC captures POC confirmation → PM approves → Procurement triages (stock / procure / emergency cash).',
       'ACTIVE', 0
WHERE NOT EXISTS (SELECT 1 FROM fin_chain_defs WHERE chain_key = 'SPARE_REQUEST_APPROVAL');

INSERT INTO fin_chain_steps (chain_id, step_no, step_key, step_label, role_required, is_terminal, description, sort_order, created_by)
SELECT d.c0d, 1, 'POC_CONFIRMATION', 'PC records POC confirmation',    'PROJECTS COORDINATOR', 0,
       'PC captures the client-side POC yes/no + method (email/WhatsApp/phone/on-site) + attachment.', 1, 0
FROM fin_chain_defs d
WHERE d.chain_key = 'SPARE_REQUEST_APPROVAL'
  AND NOT EXISTS (SELECT 1 FROM fin_chain_steps s WHERE s.chain_id = d.c0d AND s.step_key = 'POC_CONFIRMATION');

INSERT INTO fin_chain_steps (chain_id, step_no, step_key, step_label, role_required, is_terminal, description, sort_order, created_by)
SELECT d.c0d, 2, 'PM_APPROVAL', 'PM approves the request',             'PROJECT MANAGER', 0,
       'PM reviews scope + cost estimate + who-pays split. Terminal for INTERNAL small-value requests.', 2, 0
FROM fin_chain_defs d
WHERE d.chain_key = 'SPARE_REQUEST_APPROVAL'
  AND NOT EXISTS (SELECT 1 FROM fin_chain_steps s WHERE s.chain_id = d.c0d AND s.step_key = 'PM_APPROVAL');

INSERT INTO fin_chain_steps (chain_id, step_no, step_key, step_label, role_required, is_terminal, description, sort_order, created_by)
SELECT d.c0d, 3, 'PROCUREMENT_TRIAGE', 'Procurement decides source', 'PROCUREMENT', 1,
       'Procurement picks FROM_STOCK / PROCURE / EMERGENCY_CASH and creates the downstream artefact.', 3, 0
FROM fin_chain_defs d
WHERE d.chain_key = 'SPARE_REQUEST_APPROVAL'
  AND NOT EXISTS (SELECT 1 FROM fin_chain_steps s WHERE s.chain_id = d.c0d AND s.step_key = 'PROCUREMENT_TRIAGE');

/* ── Perms · Admin + PM + PC full; Procurement decide; Field Supervisor view ─ */
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'Projects', a.action_name, 0, NOW()
FROM user_groups g
CROSS JOIN (
    SELECT 'spareRequestSaveAj'      AS action_name UNION ALL
    SELECT 'spareRequestSubmitAj'                   UNION ALL
    SELECT 'spareRequestRemoveAj'                   UNION ALL
    SELECT 'spareRequestItemSaveAj'                 UNION ALL
    SELECT 'spareRequestItemRemoveAj'               UNION ALL
    SELECT 'spareRequestListAj'
) a
WHERE g.user_group_name IN ('Administrator','Project Manager','Projects Coordinator')
  AND NOT EXISTS (SELECT 1 FROM user_groups_perms p WHERE p.user_group_id=g.c0d AND p.controller_allowed='Projects' AND p.action_allowed=a.action_name);

INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'Projects', 'spareRequestListAj', 0, NOW()
FROM user_groups g
WHERE g.user_group_name IN ('Executive','Finance Manager','Accountant','Field Supervisor','Procurement','Stores')
  AND NOT EXISTS (SELECT 1 FROM user_groups_perms p WHERE p.user_group_id=g.c0d AND p.controller_allowed='Projects' AND p.action_allowed='spareRequestListAj');

/* Procurement gets the triage decision endpoint. */
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'Projects', 'spareRequestDecideAj', 0, NOW()
FROM user_groups g
WHERE g.user_group_name IN ('Administrator','Procurement','Project Manager')
  AND NOT EXISTS (SELECT 1 FROM user_groups_perms p WHERE p.user_group_id=g.c0d AND p.controller_allowed='Projects' AND p.action_allowed='spareRequestDecideAj');
