/* ─────────────────────────────────────────────────────────────────────────────
   Slice 8Z-z-B · Equipment blueprints + per-asset spec link
   Blueprint = per-contract equipment spec ("standard genset config for this
   Vodacom deployment"). PC uses it to prefill Add-equipment forms, to bulk-
   apply "1 GEN + 2 AC per site" across selected sites, and as an Excel
   shortcut column (spec_code → auto-inherit).
   ───────────────────────────────────────────────────────────────────────────── */

CREATE TABLE IF NOT EXISTS service_contract_asset_specs (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    contract_id     INT UNSIGNED NOT NULL,
    spec_code       VARCHAR(40) NOT NULL COMMENT 'PC-freeform (e.g. VDC-GEN-40)',
    spec_name       VARCHAR(160) NOT NULL,
    asset_type_id   INT UNSIGNED NOT NULL,
    make            VARCHAR(120) NULL,
    model           VARCHAR(120) NULL,
    size_value      INT UNSIGNED NULL,
    size_unit       VARCHAR(16)  NULL,
    default_warranty_months  SMALLINT UNSIGNED NULL,
    default_hours_run_seed   INT UNSIGNED NULL,
    meta_defaults_json TEXT NULL COMMENT 'Extra keys pre-populated on new assets meta_json',
    linked_pm_template_id INT UNSIGNED NULL COMMENT 'Optional auto-schedule hook',
    notes           VARCHAR(500) NULL,
    is_active       TINYINT(1) NOT NULL DEFAULT 1,
    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_contract_code (contract_id, spec_code),
    KEY idx_contract_type (contract_id, asset_type_id, deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/* Link each installed asset back to the blueprint it was created from (nullable). */
ALTER TABLE service_contract_assets
    ADD COLUMN spec_id INT UNSIGNED NULL,
    ADD KEY idx_spec (spec_id);

/* ── Perms · Admin + PM + PC full · view for the rest ────────────────────────── */
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 'assetSpecSaveAj'   AS action_name UNION ALL
    SELECT 'assetSpecRemoveAj'                UNION ALL
    SELECT 'assetSpecApplyAj'                 UNION ALL
    SELECT 'assetsImportAj'
) 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);
