-- Slice 8Z-s · Executive → PM Kickoff Brief
--
-- Captures the handoff information PM needs to start well:
--   · Kickoff essentials (Exec):   priority, target kickoff date, free-form instructions
--   · Guardrails       (Exec):     margin floor %, change-order threshold %
--   · Financial gates  (FM):       insurance, advance, TRA e-fiscal, WCF/NSSF
--   · Operational gates (Exec):    site visit, mobilization, long-lead, permits, OSHA safety plan
--   · Financial risks  (FM):       client payment history, FX exposure, WCF/NSSF risk
--   · Strategic risks  (Exec):     tight schedule, site access, scope ambiguity, PPRA reporting
--   · Reporting cadence (Exec):    weekly/bi-weekly/monthly + escalation contact
--
-- One row per tender. RBAC on section ownership is enforced in TenderExecKickoffBrief::save().

CREATE TABLE IF NOT EXISTS tender_exec_kickoff_brief (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tender_id INT UNSIGNED NOT NULL,

    /* ── A · Kickoff essentials (Exec) ────────────────────────────── */
    priority ENUM('LOW','NORMAL','HIGH') NOT NULL DEFAULT 'NORMAL',
    target_kickoff_date DATE NULL,
    instructions_to_pm TEXT NULL COMMENT 'Free-form mandate/briefing to the PM at kickoff',

    /* ── B · Guardrails (Exec) ────────────────────────────────────── */
    margin_floor_pct DECIMAL(5,2) NULL COMMENT 'Min margin below which PM must escalate',
    change_order_threshold_pct DECIMAL(5,2) NULL COMMENT 'Variance % of contract that triggers re-approval',

    /* ── C-FM · Financial pre-mobilization gates (FM) ─────────────── */
    gate_insurance_activated TINYINT(1) NOT NULL DEFAULT 0,
    gate_advance_requested   TINYINT(1) NOT NULL DEFAULT 0,
    gate_tra_efiscal_setup   TINYINT(1) NOT NULL DEFAULT 0,
    gate_wcf_nssf_verified   TINYINT(1) NOT NULL DEFAULT 0,

    /* ── C-Exec · Operational pre-mobilization gates (Exec) ───────── */
    gate_site_visit          TINYINT(1) NOT NULL DEFAULT 0,
    gate_mobilization        TINYINT(1) NOT NULL DEFAULT 0,
    gate_long_lead           TINYINT(1) NOT NULL DEFAULT 0,
    gate_permits             TINYINT(1) NOT NULL DEFAULT 0,
    gate_osha_safety_plan    TINYINT(1) NOT NULL DEFAULT 0,

    /* ── D-FM · Financial risk flags (FM) ─────────────────────────── */
    risk_client_payment      TINYINT(1) NOT NULL DEFAULT 0,
    risk_fx_exposure         TINYINT(1) NOT NULL DEFAULT 0,
    risk_wcf_nssf_compliance TINYINT(1) NOT NULL DEFAULT 0,

    /* ── D-Exec · Operational / strategic risk flags (Exec) ───────── */
    risk_tight_schedule      TINYINT(1) NOT NULL DEFAULT 0,
    risk_site_access         TINYINT(1) NOT NULL DEFAULT 0,
    risk_scope_ambiguity     TINYINT(1) NOT NULL DEFAULT 0,
    risk_ppra_reporting      TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Only meaningful when origin = TENDER_PUBLIC',

    /* ── E · Reporting cadence (Exec) ─────────────────────────────── */
    reporting_cadence ENUM('WEEKLY','BIWEEKLY','MONTHLY','ADHOC') NULL,
    escalation_contact_user_id INT UNSIGNED NULL,
    escalation_contact_name    VARCHAR(160) NULL,

    /* ── Audit — separate FM vs Exec edit stamps so PM can see who did what ── */
    created_by INT UNSIGNED NULL,
    created_by_name VARCHAR(160) NULL,
    created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fm_edited_by   INT UNSIGNED NULL,
    fm_edited_by_name VARCHAR(160) NULL,
    fm_edited_on   TIMESTAMP NULL,
    exec_edited_by INT UNSIGNED NULL,
    exec_edited_by_name VARCHAR(160) NULL,
    exec_edited_on TIMESTAMP NULL,

    UNIQUE KEY uq_tender (tender_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/* ── Perms ────────────────────────────────────────────────────────── */
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'Tenders', 'saveKickoffBriefAj', 0, NOW()
FROM user_groups g
WHERE g.user_group_name IN ('Administrator','Finance Manager','Executive')
  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 = 'saveKickoffBriefAj');

INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'Tenders', 'kickoffBriefViewAj', 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 = 'kickoffBriefViewAj');
