-- Slice 8Z-x · Templates catalog · full item schemas + callout resolutions
--
-- Two new tables, mirrors sitetracker16 production storage:
--   · service_template_items — 158 rows of PM checklist fields
--   · service_callout_resolutions_catalog — 18 rows of proven closure patterns
--
-- Migration only creates the tables. Data seeded by scripts/seed_service_templates_items.php
-- so the item list stays in code (easier to review / diff / evolve).

CREATE TABLE IF NOT EXISTS service_template_items (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    template_id INT UNSIGNED NOT NULL,
    section VARCHAR(60) NOT NULL,
    sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 100,
    field_slug VARCHAR(80) NOT NULL,
    field_label VARCHAR(240) NOT NULL,
    field_type ENUM('CHECK','NUMBER','PHOTO','TEXT','LONG_TEXT','DATE','DROPDOWN','GPS','SCAN') NOT NULL,
    is_required TINYINT(1) NOT NULL DEFAULT 0,
    unit_or_note VARCHAR(60) NULL COMMENT 'V / Hz / L / kW / °C / hrs / % / …',
    config_json TEXT NULL COMMENT 'JSON: {choices:[...]} for DROPDOWN · {min:x,max:y} for NUMBER ranges',
    depends_on_slug VARCHAR(80) NULL COMMENT 'gate on sibling field_slug in the same template',
    depends_on_value VARCHAR(60) NULL COMMENT 'value the parent must equal (e.g. YES / NO)',
    UNIQUE KEY uq_tpl_slug (template_id, field_slug),
    KEY tpl_section_sort (template_id, section, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS service_callout_resolutions_catalog (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(60) NOT NULL UNIQUE,
    category ENUM('POWER','GEN','BATTERY','COOLING','COMMS','ENVIRONMENT','SECURITY','OTHER') NOT NULL,
    title VARCHAR(160) NOT NULL,
    root_cause VARCHAR(240) NULL,
    action_taken VARCHAR(240) NULL,
    fault_status ENUM('FIXED','MONITORING','PENDING_FOLLOWUP','PENDING_SPARES','ESCALATED') NOT NULL DEFAULT 'FIXED',
    sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 100,
    is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
