-- Slice 8Z-w · Service Templates Catalog
--
-- Global reference library of proven PM / Callout / Report templates used across
-- services projects. Modelled after sitetracker16 production templates (SITE-MONTHLY,
-- GEN-SVC-250, AC-QTR, etc.). Referenced from the PM → PC "instructions" card so
-- PM can pick from the catalog instead of writing template names from scratch.
--
-- This slice only seeds the METADATA (label · category · scope · cadence · description).
-- The full item schema (fields, dependencies, dropdown choices) will follow when PC/FS
-- actually use these to fill visit reports — for now, PM just references the slug.

CREATE TABLE IF NOT EXISTS service_templates_catalog (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(60) NOT NULL UNIQUE,
    label VARCHAR(160) NOT NULL,
    category ENUM(
        'PM_MONTHLY',
        'PM_QUARTERLY',
        'PM_SEMI_ANNUAL',
        'PM_ANNUAL',
        'PM_ASSET',
        'PM_FUELTEST',
        'CALLOUT',
        'CALLOUT_RESOLUTION',
        'REPORT'
    ) NOT NULL,
    scope ENUM('SITE','GEN','AC','BATTERY','UPS','SHELTER','GENERIC') NOT NULL DEFAULT 'GENERIC',
    cadence_note VARCHAR(80) NULL COMMENT 'e.g. "Every 30 days" · "Per 250 run-hours"',
    description TEXT NULL,
    tanzania_specific TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Flags templates with local regulator references (OSHA/WCF/NSSF/TRA)',
    item_count SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Number of check items in the full template (for reference)',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 100,
    created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/* ── Seed the 11 commonly-used templates (idempotent on slug) ──────── */

INSERT IGNORE INTO service_templates_catalog (slug, label, category, scope, cadence_note, description, tanzania_specific, item_count, sort_order) VALUES
-- Monthly PM (Vodacom-style tower fleet baseline)
('SITE-MONTHLY',
 'Monthly site PM',
 'PM_MONTHLY', 'SITE', 'Every 30 days',
 'Full site walk-around · perimeter + access + safety + power readings + photos + earthing + surge arrestors + AC incomer + shelter + alarm tests. 46 items across 10 sections.',
 1, 46, 10),

-- Generator services (250h + 500h + fuel-test)
('GEN-SVC-250',
 'Generator service · 250 run-hours',
 'PM_ASSET', 'GEN', 'Per 250 run-hours',
 'Oil + oil filter + fuel + air filter + coolant + belts. Test run: voltage / frequency / hours. 3 before/after photos. 11 items.',
 0, 11, 20),

('GEN-SVC-500',
 'Generator service · 500 run-hours',
 'PM_ASSET', 'GEN', 'Per 500 run-hours',
 'Extended service — valve clearance + injectors + coolant change + spark-plug review. 14 items (includes the 250h checks).',
 0, 14, 21),

('GEN-FUELTEST-QTR',
 'Generator fuel-test · quarterly',
 'PM_FUELTEST', 'GEN', 'Every 90 days',
 'Controlled 30-minute L/hr consumption test. 18 items across 6 sections (PRE_TEST · START · RUN · END · RESULT · RESTORE). FuelDesk reviewer required · reconciliation before close.',
 0, 18, 22),

-- Air-con quarterly
('AC-QTR',
 'Air-conditioner · quarterly PM',
 'PM_QUARTERLY', 'AC', 'Every 90 days',
 'Filter clean · coil clean · drain clear · fan check · performance (inlet / outlet / delta temps) · noise check. 8 items.',
 0, 8, 30),

-- Battery bank semi-annual
('BATT-SEMI',
 'Battery bank · semi-annual',
 'PM_SEMI_ANNUAL', 'BATTERY', 'Every 180 days',
 'Visual (3) · electrical measurements (4) · flooded-cell electrolyte top-up if applicable · optional load test · 1 photo. 10 items.',
 0, 10, 40),

-- Shelter annual
('SHELTER-ANNUAL',
 'Shelter · annual inspection',
 'PM_ANNUAL', 'SHELTER', 'Every 365 days',
 'Structural (roof / walls / door / floor) + systems (lighting / fire suppression / ventilation). 2 photos. 8 items.',
 0, 8, 50),

-- Emergency callout response (generic)
('CALLOUT-EMERGENCY',
 'Emergency callout response',
 'CALLOUT', 'GENERIC', 'On-demand · SLA-driven',
 'Issue category (POWER/GEN/BATTERY/COOLING/COMMS/ENV) · description · actions taken · resolution status · response time (min) · 2 photos · GPS. Ties to SLA response_target_hrs.',
 0, 8, 60),

-- Callout resolution templates (3 most-hit patterns from sitetracker16)
('CALLOUT-RES-GEN-FUEL',
 'Callout resolution · genset out of fuel',
 'CALLOUT_RESOLUTION', 'GEN', 'On-demand',
 'Root cause: "Fuel consumption higher than planned". Action: refueled + restarted. Status: FIXED. Follow-up: consumption trend review.',
 0, 5, 70),

('CALLOUT-RES-BATT-LOW',
 'Callout resolution · battery bank low voltage',
 'CALLOUT_RESOLUTION', 'BATTERY', 'On-demand',
 'Root cause: Grid outage exceeded battery autonomy. Action: gen started + charge cycle initiated. Status: MONITORING until float voltage restored.',
 0, 5, 71),

('CALLOUT-RES-AC-CLEAN',
 'Callout resolution · AC weak cooling',
 'CALLOUT_RESOLUTION', 'AC', 'On-demand',
 'Root cause: Filter maintenance overdue. Action: cleaned filters + wiped evaporator + verified delta-T. Status: FIXED.',
 0, 5, 72);

/* ── Perms — read-only, all senior roles ──────────────────────────── */
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'Projects', 'templatesCatalogAj', 0, NOW()
FROM user_groups g
WHERE g.user_group_name IN ('Administrator','Project Manager','Projects Coordinator','Executive','Finance Manager','Accountant','CRM','Field Supervisor')
  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='templatesCatalogAj');
