-- Slice 8S-10 · Tender Service Spec
--
-- Captures the "as bid" answers to the 10 Services CRM-checklist questions upfront on the
-- tender. When the tender is WON → converted → answers pre-populate a DRAFT service_contract
-- so PC never re-types what CRM already collected during bid preparation.
--
-- One row per Services tender. Goods/Works tenders never touch this table.

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

    -- Sites & equipment (bid-time expectations)
    no_of_sites SMALLINT UNSIGNED NULL,
    sites_notes TEXT NULL COMMENT 'e.g. "45 BTS across Dar/Coast/Morogoro"',
    equipment_json LONGTEXT NULL COMMENT 'JSON: [{type:"GEN", size:"60-100kVA", count:45}, ...]',

    -- PM structure
    pm_cadence ENUM('MONTHLY','QUARTERLY','SEMI_ANNUAL','ANNUAL','ADHOC') NULL,
    pm_payment_structure ENUM('MONTHLY_RETAINER','PER_EVENT','BUNDLED_WITH_CALLOUTS') NULL,

    -- Callout structure
    has_callouts TINYINT(1) NOT NULL DEFAULT 1,
    callout_payment_structure ENUM('PER_CALLOUT_FEE','T_AND_M','BUNDLED','FREE_QUOTA_PLUS_FEE') NULL,
    callout_fee DECIMAL(20,2) NULL,
    emergency_multiplier DECIMAL(5,2) NULL DEFAULT 2.00,
    free_callouts_per_month SMALLINT UNSIGNED NULL DEFAULT 0,

    -- SLA (defaults reflect what a typical Tanzania tender specifies)
    sla_emergency_hrs SMALLINT UNSIGNED NULL,
    sla_urgent_hrs SMALLINT UNSIGNED NULL,
    sla_routine_hrs SMALLINT UNSIGNED NULL,

    -- Spare policy + service pack
    spare_policy ENUM('COMPANY','CLIENT','CONSIGNMENT','FIXED_PACK') NULL,
    spare_policy_notes TEXT NULL,
    client_supplied_pack TINYINT(1) NOT NULL DEFAULT 0,
    service_pack_notes TEXT NULL COMMENT 'The list of parts the client agreed to pay for per interval',

    -- Invoicing + payment
    invoicing_cadence ENUM('MONTHLY_RETAINER','PER_EVENT','MILESTONE','CONSUMPTION') NULL,
    payment_terms_days SMALLINT UNSIGNED NULL DEFAULT 30,

    -- Pricing model
    labour_rate_per_hr DECIMAL(20,2) NULL,
    mileage_rate_per_km DECIMAL(20,2) NULL,
    parts_markup_pct DECIMAL(5,2) NULL DEFAULT 25.00,

    -- Adhoc parts SOP (the operational process for replacement requests)
    part_replacement_sop_agreed TINYINT(1) NOT NULL DEFAULT 0,
    part_replacement_notes TEXT NULL,

    -- Free-form
    general_notes TEXT NULL,

    -- Audit
    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,

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

-- Perms — Admin + CRM (matches CRM checklist ownership).
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'Tenders', 'saveServiceSpecAj', 0, NOW()
FROM user_groups g
WHERE g.user_group_name IN ('Administrator','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 = 'saveServiceSpecAj');

-- Read-only for PC/PM/Exec/Accountant/FM so they can see the bid answers.
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'Tenders', 'serviceSpecViewAj', 0, NOW()
FROM user_groups g
WHERE g.user_group_name IN ('Administrator','CRM','Projects Coordinator','Project Manager','Executive','Accountant','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 = 'serviceSpecViewAj');
