-- Slice 8Z-y · Services Scope Configuration on PM's project view
--
-- Restructures the PM → PC card into a structured coverage matrix:
--   Y-A · confirm scope from bid          — 4 cols on service_contracts
--   Y-B · template bindings per equipment — new service_contract_template_bindings
--   Y-C · spare part schedule / packs     — new service_contract_packs + _pack_items
--   Y-E · additional PC instructions      — reuses existing c_projects.pm_svc_* cols
--
-- Y-C uses per-contract tables (not the global service_packs library) so PM can start
-- fresh without a populated parts catalog. Item labels are free-text — normalising to
-- spare_parts.sku is a future concern once the parts catalog is populated.

/* ── Y-A · scope confirmation cols on service_contracts ────────────── */
ALTER TABLE service_contracts
  ADD COLUMN sites_target_confirmed        INT UNSIGNED NULL AFTER pm_spec_confirmed_by_name,
  ADD COLUMN equipment_scope_confirmed_json LONGTEXT   NULL AFTER sites_target_confirmed,
  ADD COLUMN scope_confirmed_on            TIMESTAMP    NULL AFTER equipment_scope_confirmed_json,
  ADD COLUMN scope_confirmed_by            INT UNSIGNED NULL AFTER scope_confirmed_on,
  ADD COLUMN scope_confirmed_by_name       VARCHAR(160) NULL AFTER scope_confirmed_by;

/* ── Y-B · template bindings per equipment class ───────────────────── */
CREATE TABLE IF NOT EXISTS service_contract_template_bindings (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    contract_id INT UNSIGNED NOT NULL,
    equipment_class VARCHAR(120) NOT NULL COMMENT 'e.g. "GEN 20-40kVA", "AC 18000BTU", "Shelter"',
    equipment_qty INT UNSIGNED NULL COMMENT 'Snapshot of unit count at binding time',
    template_id INT UNSIGNED NOT NULL,
    cadence_override_days INT UNSIGNED NULL COMMENT 'Overrides template default cadence if set',
    notes VARCHAR(500) NULL,
    sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 100,
    created_by INT UNSIGNED NULL,
    created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    edited_by INT UNSIGNED NULL,
    edited_on TIMESTAMP NULL,
    KEY contract_idx (contract_id, equipment_class),
    KEY template_idx (template_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/* ── Y-C · Spare part schedule (per-contract packs + items) ────────── */
CREATE TABLE IF NOT EXISTS service_contract_packs (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    contract_id INT UNSIGNED NOT NULL,
    code VARCHAR(60) NULL,
    name VARCHAR(191) NOT NULL,
    supply_model ENUM('CLIENT','COMPANY','CONSIGNMENT','FIXED_PACK') NOT NULL DEFAULT 'CLIENT'
        COMMENT 'Who supplies these parts · overrides contract-level spare_policy per-pack',
    linked_template_slug VARCHAR(60) NULL COMMENT 'Optional link to service_templates_catalog.slug',
    notes TEXT NULL,
    sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 100,
    deleted TINYINT(1) NOT NULL DEFAULT 0,
    created_by INT UNSIGNED NULL,
    created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    edited_by INT UNSIGNED NULL,
    edited_on TIMESTAMP NULL,
    KEY contract_idx (contract_id, deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS service_contract_pack_items (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    pack_id INT UNSIGNED NOT NULL,
    interval_label VARCHAR(60) NULL COMMENT 'e.g. "Per visit", "Every 250h", "Quarterly"',
    item_name VARCHAR(240) NOT NULL,
    qty DECIMAL(12,4) NOT NULL DEFAULT 1.0000,
    uom VARCHAR(24) NULL COMMENT 'ea · L · kg · m · pack',
    notes VARCHAR(500) NULL,
    sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 100,
    deleted TINYINT(1) NOT NULL DEFAULT 0,
    KEY pack_idx (pack_id, deleted)
) 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, 'Projects', a.action_name, 0, NOW()
FROM user_groups g
CROSS JOIN (
    SELECT 'saveScopeConfirmAj'      AS action_name UNION ALL
    SELECT 'bindingSaveAj'                          UNION ALL
    SELECT 'bindingRemoveAj'                        UNION ALL
    SELECT 'bindingSuggestDefaultsAj'               UNION ALL
    SELECT 'packSaveAj'                             UNION ALL
    SELECT 'packRemoveAj'                           UNION ALL
    SELECT 'packItemSaveAj'                         UNION ALL
    SELECT 'packItemRemoveAj'                       UNION ALL
    SELECT 'svcScopeSnapshotAj'
) 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);
