package main

import (
	"context"
	"fmt"
	"log"

	"github.com/jackc/pgx/v5/pgxpool"
)

type Migration struct {
	Version     int
	Description string
	SQL         string
}

// migrations are applied in order; once applied they are never re-run.
var migrations = []Migration{
	{
		Version:     1,
		Description: "IC3 schema v3 - 9 tables, converted from SQLite",
		SQL: `
DROP TABLE IF EXISTS telemetry CASCADE;

CREATE TABLE IF NOT EXISTS telemetry (
    id               BIGSERIAL PRIMARY KEY,
    record_id        TEXT UNIQUE,
    timestamp_utc    TIMESTAMPTZ NOT NULL,
    received_at_utc  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    domain_code      TEXT NOT NULL DEFAULT '',
    system_id        TEXT,
    asset_id         TEXT NOT NULL,
    source_system    TEXT,
    collector_id     TEXT,
    sequence_no      INTEGER,
    quality_code     TEXT NOT NULL DEFAULT 'GOOD',
    alarm_state      TEXT NOT NULL DEFAULT 'NORMAL',
    location_path    TEXT,
    country_code     TEXT,
    state_code       TEXT,
    district_code    TEXT,
    taluk_code       TEXT,
    city_code        TEXT,
    ward_code        TEXT,
    zone_id          TEXT,
    dma_id           TEXT,
    pressure_zone_id TEXT,
    site_id          TEXT,
    latitude         DOUBLE PRECISION,
    longitude        DOUBLE PRECISION,
    elevation_m      DOUBLE PRECISION,
    stream_topic     TEXT,
    priority         TEXT,
    payload          JSONB,
    created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_tel_ts      ON telemetry(timestamp_utc DESC);
CREATE INDEX IF NOT EXISTS idx_tel_domain  ON telemetry(domain_code, timestamp_utc DESC);
CREATE INDEX IF NOT EXISTS idx_tel_system  ON telemetry(system_id,   timestamp_utc DESC);
CREATE INDEX IF NOT EXISTS idx_tel_asset   ON telemetry(asset_id,    timestamp_utc DESC);
CREATE INDEX IF NOT EXISTS idx_tel_qual    ON telemetry(quality_code);
CREATE INDEX IF NOT EXISTS idx_tel_alarm   ON telemetry(alarm_state)   WHERE alarm_state <> 'NORMAL';
CREATE INDEX IF NOT EXISTS idx_tel_dma     ON telemetry(dma_id, timestamp_utc DESC);
CREATE INDEX IF NOT EXISTS idx_tel_created ON telemetry(created_at DESC);

CREATE TABLE IF NOT EXISTS asset_latest (
    asset_id      TEXT PRIMARY KEY,
    system_id     TEXT,
    domain_code   TEXT,
    timestamp_utc TIMESTAMPTZ NOT NULL,
    quality_code  TEXT,
    alarm_state   TEXT,
    dma_id        TEXT,
    site_id       TEXT,
    stream_topic  TEXT,
    payload       JSONB,
    updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS assets (
    asset_id         TEXT PRIMARY KEY,
    system_id        TEXT,
    domain_code      TEXT,
    site_id          TEXT,
    dma_id           TEXT,
    pressure_zone_id TEXT,
    zone_id          TEXT,
    location_path    TEXT,
    latitude         DOUBLE PRECISION,
    longitude        DOUBLE PRECISION,
    elevation_m      DOUBLE PRECISION,
    first_seen       TIMESTAMPTZ,
    last_seen        TIMESTAMPTZ,
    last_quality     TEXT,
    last_alarm       TEXT,
    total_records    INTEGER DEFAULT 0
);

CREATE TABLE IF NOT EXISTS alarm_events (
    id              BIGSERIAL PRIMARY KEY,
    record_id       TEXT UNIQUE,
    timestamp_utc   TIMESTAMPTZ NOT NULL,
    asset_id        TEXT NOT NULL,
    domain_code     TEXT,
    system_id       TEXT,
    dma_id          TEXT,
    from_state      TEXT NOT NULL DEFAULT 'NORMAL',
    to_state        TEXT NOT NULL,
    param_key       TEXT,
    trigger_value   DOUBLE PRECISION,
    threshold_value DOUBLE PRECISION,
    quality_code    TEXT,
    acknowledged_by TEXT,
    acknowledged_at TIMESTAMPTZ,
    resolved_at     TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_alarm_asset  ON alarm_events(asset_id,    timestamp_utc DESC);
CREATE INDEX IF NOT EXISTS idx_alarm_domain ON alarm_events(domain_code, timestamp_utc DESC);
CREATE INDEX IF NOT EXISTS idx_alarm_open   ON alarm_events(to_state)    WHERE resolved_at IS NULL;

CREATE TABLE IF NOT EXISTS gis_features (
    id            BIGSERIAL PRIMARY KEY,
    record_id     TEXT NOT NULL,
    timestamp_utc TIMESTAMPTZ NOT NULL,
    received_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    domain_code   TEXT,
    system_id     TEXT,
    asset_id      TEXT NOT NULL,
    collection    TEXT NOT NULL,
    feature_type  TEXT,
    quality_code  TEXT,
    alarm_state   TEXT,
    geojson       JSONB,
    stream_topic  TEXT,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(collection, asset_id)
);
CREATE INDEX IF NOT EXISTS idx_gis_col    ON gis_features(collection);
CREATE INDEX IF NOT EXISTS idx_gis_domain ON gis_features(domain_code);

CREATE TABLE IF NOT EXISTS gis_history (
    id             BIGSERIAL PRIMARY KEY,
    record_id      TEXT NOT NULL,
    timestamp_utc  TIMESTAMPTZ NOT NULL,
    collection     TEXT NOT NULL,
    asset_id       TEXT NOT NULL,
    change_type    TEXT,
    geojson_before JSONB,
    geojson_after  JSONB,
    created_at     TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_gis_hist ON gis_history(asset_id, timestamp_utc DESC);

CREATE TABLE IF NOT EXISTS cctv_metadata (
    id               BIGSERIAL PRIMARY KEY,
    record_id        TEXT UNIQUE,
    timestamp_utc    TIMESTAMPTZ NOT NULL,
    asset_id         TEXT UNIQUE NOT NULL,
    stream_url       TEXT,
    stream_status    TEXT,
    protocol         TEXT,
    device_online    BOOLEAN DEFAULT TRUE,
    motion_detected  BOOLEAN DEFAULT FALSE,
    intrusion_alarm  BOOLEAN DEFAULT FALSE,
    door_state       TEXT    DEFAULT 'CLOSED',
    nvr_storage_pct  DOUBLE PRECISION DEFAULT 0,
    recording_active BOOLEAN DEFAULT TRUE,
    quality_code     TEXT,
    alarm_state      TEXT,
    domain_code      TEXT,
    system_id        TEXT,
    stream_topic     TEXT,
    created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS integration_events (
    id            BIGSERIAL PRIMARY KEY,
    record_id     TEXT UNIQUE,
    timestamp_utc TIMESTAMPTZ NOT NULL,
    asset_id      TEXT NOT NULL,
    source_system TEXT,
    event_type    TEXT,
    event_payload JSONB,
    domain_code   TEXT,
    system_id     TEXT,
    stream_topic  TEXT,
    quality_code  TEXT,
    processed     BOOLEAN DEFAULT FALSE,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_evt_type   ON integration_events(event_type,  timestamp_utc DESC);
CREATE INDEX IF NOT EXISTS idx_evt_domain ON integration_events(domain_code, timestamp_utc DESC);
CREATE INDEX IF NOT EXISTS idx_evt_proc   ON integration_events(processed)   WHERE processed = FALSE;
`,
	},
	{
		Version:     2,
		Description: "Users table for DB-backed authentication",
		SQL: `
CREATE TABLE IF NOT EXISTS users (
    id            BIGSERIAL PRIMARY KEY,
    username      TEXT UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    role          TEXT NOT NULL DEFAULT 'viewer',
    status        TEXT NOT NULL DEFAULT 'active',
    full_name     TEXT,
    email         TEXT,
    last_login    TIMESTAMPTZ,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
`,
	},
	{
		// Key design decisions:
		//   id (BIGSERIAL) = IC3-internal PK used by all child table FKs.
		//   traceid        = CMMS API numeric ID; NULL for report-only rows until CMMS API sync.
		//   report_asset_id = text Asset ID from the report ("Delhi Cantt Naraina_Trans_10");
		//                     UNIQUE — used as the upsert conflict key on bulk import.
		Version:     3,
		Description: "CMMS Asset Master schema - 5 tables mapping Asset ID Report + CMMS API 94-field spec",
		SQL: `
-- ===========================================================================
-- cmms_asset_master
-- Covers: Asset_ID_Report (2959 rows x 19 cols)
--       + CMMS API /asset/detail/{id} sections: data (42 fields) + assetIDDetails (26)
-- ===========================================================================
CREATE TABLE IF NOT EXISTS cmms_asset_master (
    id                        BIGSERIAL PRIMARY KEY,

    -- IC3 cross-reference (set after Realtime-CMMS auto/manual match)
    ic3_asset_id              TEXT,

    -- CMMS API identity
    traceid                   INTEGER UNIQUE,   -- NULL until CMMS API syncs; then set to CMMS traceid
    serialnumber              TEXT,             -- e.g. "35 Block_BP_02"
    barcode                   TEXT,             -- "MOD135794/SER575962"
    qrcode                    TEXT,

    -- Asset ID Report fields
    report_seq_no             INTEGER,
    report_asset_id           TEXT UNIQUE,      -- upsert key on bulk import; e.g. "Delhi Cantt Naraina_Trans_10"

    -- Hierarchy
    customer_name             TEXT,
    customer_id               INTEGER,
    location_name             TEXT,
    location_id               INTEGER,
    building_name             TEXT,
    building_id               INTEGER,
    floor_name                TEXT,
    department_name           TEXT,
    department_id             INTEGER,

    -- Classification
    category_name             TEXT,
    cat_id                    INTEGER,
    sub_category              TEXT,
    subcategory_id            INTEGER,
    asset_name                TEXT,
    asset_model               TEXT,
    equipment_model_name      TEXT,
    equipment_description     TEXT,
    equipments_model_id       INTEGER,
    model_id                  INTEGER,

    -- Manufacturer / vendor
    manufacturer_name         TEXT,
    manufacturer_id           INTEGER,
    service_provider_name     TEXT,
    in_charge_person          TEXT,
    selectmachineowner        INTEGER,

    -- Status & criticality
    status                    INTEGER NOT NULL DEFAULT 1,   -- 1=Active 0=Inactive
    reason_for_inactive       TEXT,
    asset_live_status         INTEGER NOT NULL DEFAULT 1,
    criticality               TEXT,             -- "High"/"Medium"/"Low" (data section text)
    criticality_type          INTEGER,          -- 1=High, 2=Med, 3=Low (assetIDDetails integer)
    mapping_status            INTEGER DEFAULT 0,
    is_main_asset             INTEGER DEFAULT 1,
    iot_device_mapped         INTEGER DEFAULT 0,   -- 0 = no live Realtime link (critical gap)

    -- Technical spec
    capacity_rating           TEXT,

    -- PM / WO counters from report
    schedule_count_configured INTEGER DEFAULT 0,
    schedule_count_initiated  INTEGER DEFAULT 0,
    schedule_assigned         TEXT,
    schedule_configured       INTEGER DEFAULT 0,
    schedule_initiate         INTEGER DEFAULT 0,
    ticketcount               INTEGER DEFAULT 0,

    -- Purchase / financial
    purchase_number           TEXT,
    po_number                 TEXT,
    purchase_date             DATE,
    purchase_value            NUMERIC(14,2),
    asset_lifespan            INTEGER,
    depreciation_type         TEXT,
    depreciation_percentage   NUMERIC(6,3),
    units_produced            NUMERIC(16,3),

    -- Contracts
    contract_name             TEXT,
    contract_number           TEXT,
    contract_start_date       DATE,
    contract_end_date         DATE,

    -- Geo
    latitude                  DOUBLE PRECISION,
    longitude                 DOUBLE PRECISION,

    -- Key dates
    installation_date         DATE,
    year_of_manufacturing     INTEGER,
    traceability_updated_on   DATE,

    -- Sync tracking
    last_synced_at            TIMESTAMPTZ,
    sync_version              INTEGER NOT NULL DEFAULT 1,

    created_at                TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at                TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_cmms_master_ic3        ON cmms_asset_master(ic3_asset_id)      WHERE ic3_asset_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_cmms_master_serial     ON cmms_asset_master(serialnumber)       WHERE serialnumber IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_cmms_master_customer   ON cmms_asset_master(customer_name);
CREATE INDEX IF NOT EXISTS idx_cmms_master_category   ON cmms_asset_master(category_name, sub_category);
CREATE INDEX IF NOT EXISTS idx_cmms_master_status     ON cmms_asset_master(status, asset_live_status);
CREATE INDEX IF NOT EXISTS idx_cmms_master_location   ON cmms_asset_master(location_id, building_id);
CREATE INDEX IF NOT EXISTS idx_cmms_master_contract   ON cmms_asset_master(contract_end_date)  WHERE contract_end_date IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_cmms_master_iot        ON cmms_asset_master(iot_device_mapped);
CREATE INDEX IF NOT EXISTS idx_cmms_master_criticality ON cmms_asset_master(criticality_type);

-- ===========================================================================
-- cmms_asset_performance
-- CMMS API /asset/detail/{id} -> performance section (11 fields)
-- Durations stored as seconds (BIGINT) for easy arithmetic; display layer converts.
-- ===========================================================================
CREATE TABLE IF NOT EXISTS cmms_asset_performance (
    id                            BIGSERIAL PRIMARY KEY,
    asset_id                      BIGINT NOT NULL REFERENCES cmms_asset_master(id) ON DELETE CASCADE,
    equipments_name               TEXT,
    equipment_model_name          TEXT,
    criticality_type_label        TEXT,
    total_ticket                  INTEGER DEFAULT 0,
    mttr_seconds                  BIGINT,
    mtbf_seconds                  BIGINT,
    availability_pct              NUMERIC(6,3),
    total_breakdown_seconds       BIGINT,
    total_schedule_breakdown_sec  BIGINT,
    total_actual_seconds          BIGINT,
    asset_created_date            TIMESTAMPTZ,
    synced_at                     TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_cmms_perf_asset ON cmms_asset_performance(asset_id, synced_at DESC);

-- ===========================================================================
-- cmms_pm_schedule
-- CMMS API -> last_schedule section (7 fields per asset)
-- next_schedule_date drives the IC3 PM alert engine.
-- ===========================================================================
CREATE TABLE IF NOT EXISTS cmms_pm_schedule (
    id                                BIGSERIAL PRIMARY KEY,
    asset_id                          BIGINT NOT NULL REFERENCES cmms_asset_master(id) ON DELETE CASCADE,
    schedule_id                       INTEGER,
    maintenance_name                  TEXT,
    schedule_date                     TIMESTAMPTZ,
    schedule_reference_id             TEXT,
    schedule_completed_date           TIMESTAMPTZ,
    next_schedule_date                TIMESTAMPTZ,
    fk_equipment_schedule_mapping_id  INTEGER,
    pm_tat_days                       NUMERIC(8,2),    -- (completed - planned) in days
    is_overdue                        BOOLEAN DEFAULT FALSE,
    synced_at                         TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_cmms_pm_asset   ON cmms_pm_schedule(asset_id, schedule_date DESC);
CREATE INDEX IF NOT EXISTS idx_cmms_pm_next    ON cmms_pm_schedule(next_schedule_date) WHERE next_schedule_date IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_cmms_pm_overdue ON cmms_pm_schedule(is_overdue) WHERE is_overdue = TRUE;

-- ===========================================================================
-- cmms_work_order_summary
-- CMMS API -> workorder_transactional section
-- ===========================================================================
CREATE TABLE IF NOT EXISTS cmms_work_order_summary (
    id                          BIGSERIAL PRIMARY KEY,
    asset_id                    BIGINT NOT NULL REFERENCES cmms_asset_master(id) ON DELETE CASCADE,
    total_wo_count              INTEGER DEFAULT 0,
    total_pm_count              INTEGER DEFAULT 0,
    total_breakdown_count       INTEGER DEFAULT 0,
    total_wo_cost               NUMERIC(14,2) DEFAULT 0,
    wo_count_last_year          INTEGER DEFAULT 0,
    pm_count_last_year          INTEGER DEFAULT 0,
    breakdown_count_last_year   INTEGER DEFAULT 0,
    pm_compliance_pct           NUMERIC(6,3),
    synced_at                   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_cmms_wo_asset ON cmms_work_order_summary(asset_id, synced_at DESC);

-- ===========================================================================
-- asset_cmms_mapping
-- IC3 SCADA asset_id <-> CMMS cmms_asset_master.id cross-reference.
-- Populated by auto-match (serialnumber = ic3_asset_id) then manual override.
-- ===========================================================================
CREATE TABLE IF NOT EXISTS asset_cmms_mapping (
    ic3_asset_id      TEXT   NOT NULL,
    cmms_id           BIGINT NOT NULL REFERENCES cmms_asset_master(id) ON DELETE CASCADE,
    cmms_serialnumber TEXT,
    match_confidence  TEXT   NOT NULL DEFAULT 'auto',   -- 'auto' | 'manual' | 'fuzzy'
    is_active         BOOLEAN NOT NULL DEFAULT TRUE,
    matched_by        TEXT,
    matched_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (ic3_asset_id, cmms_id)
);
CREATE INDEX IF NOT EXISTS idx_acm_ic3  ON asset_cmms_mapping(ic3_asset_id);
CREATE INDEX IF NOT EXISTS idx_acm_cmms ON asset_cmms_mapping(cmms_id);

-- ===========================================================================
-- cmms_sync_log  - audit trail for every CMMS import/API pull
-- ===========================================================================
CREATE TABLE IF NOT EXISTS cmms_sync_log (
    id              BIGSERIAL PRIMARY KEY,
    cmms_id         BIGINT,
    sync_type       TEXT NOT NULL,   -- 'bulk_import' | 'api_full' | 'api_delta' | 'api_single'
    status          TEXT NOT NULL,   -- 'success' | 'error' | 'partial'
    assets_synced   INTEGER DEFAULT 0,
    response_code   INTEGER,
    error_msg       TEXT,
    duration_ms     INTEGER,
    synced_at       TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_cmms_sync_at     ON cmms_sync_log(synced_at DESC);
CREATE INDEX IF NOT EXISTS idx_cmms_sync_status ON cmms_sync_log(status) WHERE status <> 'success';
`,
	},
	{
		Version:     4,
		Description: "CMMS views - asset health, PM overdue, IC3-CMMS linked, data quality",
		SQL: `
-- ===========================================================================
-- v_cmms_asset_health
-- One row per asset with latest performance + PM + WO summary.
-- Primary feed for the IC3 dashboard asset health tile and CMMS panel.
-- ===========================================================================
CREATE OR REPLACE VIEW v_cmms_asset_health AS
SELECT
    m.id                      AS cmms_id,
    m.traceid,
    m.ic3_asset_id,
    m.serialnumber,
    m.report_asset_id,
    m.customer_name,
    m.location_name,
    m.building_name,
    m.floor_name,
    m.department_name,
    m.category_name,
    m.sub_category,
    m.asset_name,
    m.equipment_model_name,
    m.manufacturer_name,
    m.in_charge_person,
    m.criticality,
    m.criticality_type,
    m.status,
    m.asset_live_status,
    m.iot_device_mapped,
    m.latitude,
    m.longitude,
    m.installation_date,
    m.contract_end_date,
    CASE
        WHEN m.contract_end_date IS NOT NULL
         AND m.contract_end_date < NOW() + INTERVAL '30 days'
         AND m.contract_end_date > NOW()
        THEN TRUE ELSE FALSE
    END AS contract_expiring_soon,
    CASE WHEN m.contract_end_date < NOW() THEN TRUE ELSE FALSE END AS contract_expired,

    -- Performance KPIs (latest sync)
    p.availability_pct,
    p.total_ticket,
    ROUND(p.mttr_seconds   / 3600.0, 2) AS mttr_hours,
    ROUND(p.mtbf_seconds   / 3600.0, 2) AS mtbf_hours,
    ROUND(p.total_actual_seconds / 3600.0, 2) AS total_run_hours,
    p.criticality_type_label,

    -- PM schedule (latest sync)
    s.maintenance_name                     AS last_pm_name,
    s.schedule_date                        AS last_pm_planned,
    s.schedule_completed_date              AS last_pm_completed,
    s.next_schedule_date,
    s.pm_tat_days,
    s.is_overdue                           AS pm_overdue,
    EXTRACT(DAY FROM (s.next_schedule_date - NOW()))::INTEGER AS days_until_next_pm,

    -- Work order summary (latest sync)
    w.total_wo_count,
    w.total_pm_count,
    w.total_breakdown_count,
    w.pm_compliance_pct,
    w.wo_count_last_year,
    w.breakdown_count_last_year,

    m.last_synced_at

FROM cmms_asset_master m
LEFT JOIN LATERAL (
    SELECT * FROM cmms_asset_performance WHERE asset_id = m.id ORDER BY synced_at DESC LIMIT 1
) p ON TRUE
LEFT JOIN LATERAL (
    SELECT * FROM cmms_pm_schedule WHERE asset_id = m.id ORDER BY synced_at DESC LIMIT 1
) s ON TRUE
LEFT JOIN LATERAL (
    SELECT * FROM cmms_work_order_summary WHERE asset_id = m.id ORDER BY synced_at DESC LIMIT 1
) w ON TRUE;

-- ===========================================================================
-- v_cmms_pm_overdue
-- Assets where next PM is overdue or due within 7 days - feeds IC3 alerts.
-- ===========================================================================
CREATE OR REPLACE VIEW v_cmms_pm_overdue AS
SELECT
    m.id         AS cmms_id,
    m.traceid,
    m.ic3_asset_id,
    m.serialnumber,
    m.customer_name,
    m.location_name,
    m.building_name,
    m.criticality,
    m.in_charge_person,
    s.maintenance_name,
    s.next_schedule_date,
    s.schedule_reference_id,
    EXTRACT(DAY FROM (NOW() - s.next_schedule_date))::INTEGER AS overdue_days,
    CASE
        WHEN s.next_schedule_date < NOW()                        THEN 'OVERDUE'
        WHEN s.next_schedule_date < NOW() + INTERVAL '7 days'   THEN 'DUE_SOON'
        ELSE 'UPCOMING'
    END AS pm_urgency
FROM cmms_asset_master m
JOIN cmms_pm_schedule s ON s.asset_id = m.id
WHERE s.next_schedule_date < NOW() + INTERVAL '7 days'
  AND m.status = 1
ORDER BY s.next_schedule_date ASC;

-- ===========================================================================
-- v_ic3_cmms_linked
-- SCADA assets joined to their CMMS record; shows unmatched rows for audit.
-- ===========================================================================
CREATE OR REPLACE VIEW v_ic3_cmms_linked AS
SELECT
    a.asset_id                 AS ic3_asset_id,
    a.domain_code,
    a.site_id,
    a.dma_id,
    a.last_seen,
    a.last_quality,
    a.last_alarm,
    a.total_records,
    m.id                       AS cmms_id,
    m.traceid                  AS cmms_traceid,
    m.serialnumber             AS cmms_serialnumber,
    m.customer_name            AS cmms_customer,
    m.category_name            AS cmms_category,
    m.sub_category             AS cmms_sub_category,
    m.criticality              AS cmms_criticality,
    m.iot_device_mapped,
    m.status                   AS cmms_status,
    lm.match_confidence,
    CASE WHEN m.id IS NULL THEN FALSE ELSE TRUE END AS is_cmms_linked
FROM assets a
LEFT JOIN asset_cmms_mapping lm ON lm.ic3_asset_id = a.asset_id AND lm.is_active = TRUE
LEFT JOIN cmms_asset_master  m  ON m.id = lm.cmms_id;

-- ===========================================================================
-- v_cmms_data_quality
-- Per-asset completeness score across 9 critical fields (P1 + P2 gaps).
-- Mirrors the "Data Quality Summary" sheet in the CMMS API tag list xlsx.
-- ===========================================================================
CREATE OR REPLACE VIEW v_cmms_data_quality AS
SELECT
    id            AS cmms_id,
    traceid,
    serialnumber,
    report_asset_id,
    customer_name,
    category_name,

    -- P1 gaps (block IC3 features)
    CASE WHEN iot_device_mapped = 0      THEN 1 ELSE 0 END AS gap_no_iot_link,
    CASE WHEN installation_date IS NULL  THEN 1 ELSE 0 END AS gap_no_install_date,
    CASE WHEN latitude IS NULL           THEN 1 ELSE 0 END AS gap_no_gps,
    CASE WHEN contract_name IS NULL      THEN 1 ELSE 0 END AS gap_no_contract,

    -- P2 gaps (degrade analytics)
    CASE WHEN purchase_date IS NULL      THEN 1 ELSE 0 END AS gap_no_purchase_date,
    CASE WHEN purchase_value IS NULL     THEN 1 ELSE 0 END AS gap_no_purchase_value,
    CASE WHEN asset_lifespan IS NULL     THEN 1 ELSE 0 END AS gap_no_lifespan,
    CASE WHEN depreciation_type IS NULL  THEN 1 ELSE 0 END AS gap_no_depreciation,
    CASE WHEN service_provider_name IS NULL THEN 1 ELSE 0 END AS gap_no_vendor,

    -- Overall completeness % across the 9 tracked fields
    ROUND((
        CASE WHEN iot_device_mapped <> 0        THEN 1 ELSE 0 END +
        CASE WHEN installation_date IS NOT NULL  THEN 1 ELSE 0 END +
        CASE WHEN latitude IS NOT NULL           THEN 1 ELSE 0 END +
        CASE WHEN contract_name IS NOT NULL      THEN 1 ELSE 0 END +
        CASE WHEN purchase_date IS NOT NULL      THEN 1 ELSE 0 END +
        CASE WHEN purchase_value IS NOT NULL     THEN 1 ELSE 0 END +
        CASE WHEN asset_lifespan IS NOT NULL     THEN 1 ELSE 0 END +
        CASE WHEN depreciation_type IS NOT NULL  THEN 1 ELSE 0 END +
        CASE WHEN service_provider_name IS NOT NULL THEN 1 ELSE 0 END
    ) * 100.0 / 9.0, 1) AS data_completeness_pct,

    last_synced_at
FROM cmms_asset_master
WHERE status = 1;
`,
	},
	{
		// All 49 tables from IC3_Complete_Database_Schema_Architecture_v4.0.docx
		// Sections 4-21: Master Data, GIS, MDM, CMMS, CRM, ERP, NRW, Hydraulic,
		//                Digital Twin, AI, API Integration, Data Warehouse, Reporting
		Version:     5,
		Description: "IC3 v4.0 full schema - 49 tables across all 20 domain modules",
		SQL: `
-- uuid-ossp for gen_random_uuid() on PostgreSQL < 13; safe no-op on 13+
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- ===========================================================================
-- SECTION 4: Master Data & GIS
-- ===========================================================================

-- ic3_system_master — sub-system registry (no upstream deps)
CREATE TABLE IF NOT EXISTS ic3_system_master (
    system_id           VARCHAR(50)  PRIMARY KEY,  -- e.g. SYS-Realtime-01
    system_name         VARCHAR(150) NOT NULL,
    system_category     VARCHAR(80)  NOT NULL,     -- Realtime|GIS|CMMS|CRM|MDM|AI|ERP|HDM|DT|AMI|IOT|CCTV
    protocol            VARCHAR(80),               -- REST|OPC-UA|MQTT|WFS|WebSocket|SFTP|MODBUS|LORA
    api_base_url        VARCHAR(255),
    api_auth_type       VARCHAR(50),               -- BEARER|BASIC|APIKEY|OAUTH2
    is_active           BOOLEAN     NOT NULL DEFAULT TRUE,
    vendor_name         VARCHAR(150),
    contact_email       VARCHAR(150),
    last_heartbeat      TIMESTAMPTZ,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ic3_location_master — location hierarchy (self-referencing)
CREATE TABLE IF NOT EXISTS ic3_location_master (
    location_id         VARCHAR(120) PRIMARY KEY,  -- LOC-DMA012-001
    location_name       VARCHAR(200) NOT NULL,
    location_type       VARCHAR(50)  NOT NULL,     -- DMA|ZONE|DISTRICT|WARD|CITY|STATE|SITE|BUILDING|STATION
    parent_location_id  VARCHAR(120) REFERENCES ic3_location_master(location_id),
    dma_id              VARCHAR(50),
    zone_id             VARCHAR(50),
    district            VARCHAR(100),
    city                VARCHAR(100),
    state               VARCHAR(100),
    country             VARCHAR(50)  DEFAULT 'India',
    pincode             VARCHAR(20),
    latitude            DOUBLE PRECISION,
    longitude           DOUBLE PRECISION,
    area_sqkm           NUMERIC(12,4),
    population_served   INTEGER,
    is_active           BOOLEAN     NOT NULL DEFAULT TRUE,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_loc_parent  ON ic3_location_master(parent_location_id);
CREATE INDEX IF NOT EXISTS idx_loc_dma     ON ic3_location_master(dma_id);
CREATE INDEX IF NOT EXISTS idx_loc_type    ON ic3_location_master(location_type);

-- ic3_asset_master — canonical asset registry (single source of truth)
CREATE TABLE IF NOT EXISTS ic3_asset_master (
    asset_id            VARCHAR(120) PRIMARY KEY,  -- AST-PUMP-0001
    asset_name          VARCHAR(200) NOT NULL,
    asset_type          VARCHAR(80)  NOT NULL,     -- PUMP|VALVE|METER|SENSOR|PIPELINE|TANK|RESERVOIR|MOTOR|PANEL|CCTV
    asset_category      VARCHAR(100),              -- Electrical|Mechanical|Instrument|Civil
    asset_sub_category  VARCHAR(100),
    criticality         VARCHAR(30)  NOT NULL DEFAULT 'Non-Critical', -- Critical|High|Non-Critical
    location_id         VARCHAR(120) REFERENCES ic3_location_master(location_id),
    system_id           VARCHAR(50)  REFERENCES ic3_system_master(system_id),
    dma_id              VARCHAR(50),
    zone_id             VARCHAR(50),
    site_id             VARCHAR(100),
    latitude            DOUBLE PRECISION,
    longitude           DOUBLE PRECISION,
    elevation_m         NUMERIC(10,3),
    manufacturer        VARCHAR(150),
    model_no            VARCHAR(150),
    serial_no           VARCHAR(150),
    capacity_rating     VARCHAR(100),
    design_life_years   INTEGER,
    install_date        DATE,
    year_of_manufacture INTEGER,
    commission_date     DATE,
    decommission_date   DATE,
    asset_status        VARCHAR(40)  NOT NULL DEFAULT 'Active', -- Active|Inactive|Under_Maintenance|Decommissioned
    ownership_type      VARCHAR(50)  DEFAULT 'Utility',         -- Utility|Contract|DBO|PPP
    in_charge_person    VARCHAR(150),
    department          VARCHAR(100),
    cmms_id             INTEGER,     -- FK→cmms_asset_master.traceid (soft link)
    scada_tag           VARCHAR(150),
    purchase_value      NUMERIC(14,2),
    current_value       NUMERIC(14,2),
    depreciation_method VARCHAR(50),
    parent_asset_id     VARCHAR(120) REFERENCES ic3_asset_master(asset_id),
    is_main_asset       BOOLEAN      NOT NULL DEFAULT TRUE,
    data_source         VARCHAR(50)  DEFAULT 'CMMS',            -- CMMS|Realtime|MANUAL|IMPORT
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_iam_type       ON ic3_asset_master(asset_type);
CREATE INDEX IF NOT EXISTS idx_iam_dma        ON ic3_asset_master(dma_id);
CREATE INDEX IF NOT EXISTS idx_iam_status     ON ic3_asset_master(asset_status);
CREATE INDEX IF NOT EXISTS idx_iam_criticality ON ic3_asset_master(criticality);
CREATE INDEX IF NOT EXISTS idx_iam_location   ON ic3_asset_master(location_id);
CREATE INDEX IF NOT EXISTS idx_iam_system     ON ic3_asset_master(system_id);

-- ic3_parameter_master — engineering parameter catalogue
CREATE TABLE IF NOT EXISTS ic3_parameter_master (
    parameter_id        VARCHAR(100) PRIMARY KEY,  -- PARAM-FLOW-M3H
    parameter_code      VARCHAR(80)  UNIQUE NOT NULL,
    parameter_name      VARCHAR(150) NOT NULL,
    parameter_group     VARCHAR(100) NOT NULL,     -- FLOW|PRESSURE|LEVEL|QUALITY|ENERGY|STATUS|VIBRATION|TEMPERATURE|DOSE|TURBIDITY|CHLORINE
    unit                VARCHAR(40),               -- m3/h, bar, m, NTU, mg/L, kWh
    data_type           VARCHAR(20)  DEFAULT 'FLOAT', -- FLOAT|INTEGER|BOOLEAN|TEXT|ENUM
    min_range           NUMERIC(18,6),
    max_range           NUMERIC(18,6),
    alarm_low           NUMERIC(18,6),
    alarm_high          NUMERIC(18,6),
    warning_low         NUMERIC(18,6),
    warning_high        NUMERIC(18,6),
    is_active           BOOLEAN      NOT NULL DEFAULT TRUE,
    description         TEXT,
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_param_group ON ic3_parameter_master(parameter_group);

-- ic3_tag_master — SCADA/IoT tag definitions
CREATE TABLE IF NOT EXISTS ic3_tag_master (
    tag_id              VARCHAR(150) PRIMARY KEY,
    tag_name            VARCHAR(200) NOT NULL,
    asset_id            VARCHAR(120) REFERENCES ic3_asset_master(asset_id),
    system_id           VARCHAR(50)  REFERENCES ic3_system_master(system_id),
    parameter_id        VARCHAR(100) REFERENCES ic3_parameter_master(parameter_id),
    dma_id              VARCHAR(50),
    tag_type            VARCHAR(50)  NOT NULL,     -- ANALOG|DIGITAL|CALCULATED|VIRTUAL
    source_tag_name     VARCHAR(200),              -- Native tag name in source Realtime
    scan_rate_sec       INTEGER      DEFAULT 10,
    engineering_unit    VARCHAR(40),
    scaling_factor      NUMERIC(10,6) DEFAULT 1.0,
    scaling_offset      NUMERIC(10,6) DEFAULT 0.0,
    deadband_pct        NUMERIC(6,3)  DEFAULT 0.1,
    is_active           BOOLEAN       NOT NULL DEFAULT TRUE,
    is_historized       BOOLEAN       NOT NULL DEFAULT TRUE,
    stream_topic        VARCHAR(200),
    created_at          TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ   NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_tag_asset  ON ic3_tag_master(asset_id);
CREATE INDEX IF NOT EXISTS idx_tag_system ON ic3_tag_master(system_id);
CREATE INDEX IF NOT EXISTS idx_tag_dma    ON ic3_tag_master(dma_id);

-- ic3_event_alarm — full event/alarm log (v4 version with proper schema)
CREATE TABLE IF NOT EXISTS ic3_event_alarm (
    event_id            UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    tag_id              VARCHAR(150) REFERENCES ic3_tag_master(tag_id),
    asset_id            VARCHAR(120) REFERENCES ic3_asset_master(asset_id),
    dma_id              VARCHAR(50),
    system_id           VARCHAR(50)  REFERENCES ic3_system_master(system_id),
    event_time          TIMESTAMPTZ  NOT NULL,
    event_type          VARCHAR(80)  NOT NULL,     -- ALARM|EVENT|STATE_CHANGE|COMMAND|INTERLOCK
    alarm_class         VARCHAR(50),               -- PROCESS|DEVICE|COMMS|OPERATOR
    priority            VARCHAR(20)  NOT NULL DEFAULT 'MEDIUM', -- CRITICAL|HIGH|MEDIUM|LOW
    alarm_state         VARCHAR(40)  NOT NULL,     -- ACTIVE|ACKNOWLEDGED|CLEARED|SHELVED
    from_value          NUMERIC(18,6),
    to_value            NUMERIC(18,6),
    threshold_value     NUMERIC(18,6),
    unit                VARCHAR(40),
    alarm_message       TEXT,
    acknowledged_by     VARCHAR(120),
    acknowledged_at     TIMESTAMPTZ,
    cleared_at          TIMESTAMPTZ,
    response_time_sec   INTEGER,
    work_order_id       VARCHAR(120),
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_iev_asset   ON ic3_event_alarm(asset_id, event_time DESC);
CREATE INDEX IF NOT EXISTS idx_iev_dma     ON ic3_event_alarm(dma_id, event_time DESC);
CREATE INDEX IF NOT EXISTS idx_iev_state   ON ic3_event_alarm(alarm_state) WHERE alarm_state NOT IN ('CLEARED');
CREATE INDEX IF NOT EXISTS idx_iev_time    ON ic3_event_alarm(event_time DESC);

-- gis_network_node — network nodes (pumps, valves, junctions)
CREATE TABLE IF NOT EXISTS gis_network_node (
    node_id             VARCHAR(120) PRIMARY KEY,
    node_type           VARCHAR(80)  NOT NULL,     -- JUNCTION|VALVE|PUMP|RESERVOIR|TANK|METER|HYDRANT|PRV|BPT|AIR_VALVE|WASHOUT
    asset_id            VARCHAR(120) REFERENCES ic3_asset_master(asset_id),
    dma_id              VARCHAR(50)  NOT NULL,
    pressure_zone_id    VARCHAR(50),
    elevation_m         NUMERIC(10,3),
    design_pressure_bar NUMERIC(8,4),
    min_pressure_bar    NUMERIC(8,4),
    max_pressure_bar    NUMERIC(8,4),
    latitude            DOUBLE PRECISION,
    longitude           DOUBLE PRECISION,
    node_status         VARCHAR(40)  NOT NULL DEFAULT 'Active',
    scada_node_id       VARCHAR(150),
    install_date        DATE,
    notes               TEXT,
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_gnn_dma    ON gis_network_node(dma_id);
CREATE INDEX IF NOT EXISTS idx_gnn_type   ON gis_network_node(node_type);
CREATE INDEX IF NOT EXISTS idx_gnn_asset  ON gis_network_node(asset_id);

-- gis_network_link — pipe segments
CREATE TABLE IF NOT EXISTS gis_network_link (
    link_id             VARCHAR(120) PRIMARY KEY,
    from_node_id        VARCHAR(120) REFERENCES gis_network_node(node_id),
    to_node_id          VARCHAR(120) REFERENCES gis_network_node(node_id),
    asset_id            VARCHAR(120) REFERENCES ic3_asset_master(asset_id),
    dma_id              VARCHAR(50)  NOT NULL,
    pipe_material       VARCHAR(50),               -- DI|HDPE|PVC|CI|AC|MS|GI|STEEL|CONC
    diameter_mm         INTEGER,
    length_m            NUMERIC(12,3),
    install_year        INTEGER,
    roughness_coeff     NUMERIC(8,4),
    max_velocity_ms     NUMERIC(8,4),
    operating_pressure_bar NUMERIC(8,4),
    pipe_status         VARCHAR(40)  NOT NULL DEFAULT 'Active', -- Active|Abandoned|Replaced|Planned
    condition_grade     VARCHAR(10),               -- A|B|C|D|E (A=new, E=critical)
    last_inspection     DATE,
    notes               TEXT,
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_gnl_dma      ON gis_network_link(dma_id);
CREATE INDEX IF NOT EXISTS idx_gnl_from     ON gis_network_link(from_node_id);
CREATE INDEX IF NOT EXISTS idx_gnl_to       ON gis_network_link(to_node_id);
CREATE INDEX IF NOT EXISTS idx_gnl_material ON gis_network_link(pipe_material);

-- gis_dma_boundary — DMA boundary polygons
CREATE TABLE IF NOT EXISTS gis_dma_boundary (
    dma_id              VARCHAR(50)  PRIMARY KEY,
    dma_name            VARCHAR(200) NOT NULL,
    dma_type            VARCHAR(50)  NOT NULL DEFAULT 'OPERATIONAL', -- OPERATIONAL|BULK|SUB-DMA
    parent_dma_id       VARCHAR(50)  REFERENCES gis_dma_boundary(dma_id),
    zone_id             VARCHAR(50),
    area_sqkm           NUMERIC(12,4),
    design_population   INTEGER,
    connections         INTEGER,
    target_nrw_pct      NUMERIC(6,3) DEFAULT 20.0,
    target_pressure_bar NUMERIC(8,4),
    supply_hours_per_day NUMERIC(5,2) DEFAULT 24.0,
    inlet_node_id       VARCHAR(120),
    boundary_geom       TEXT,  -- GeoJSON polygon stored as text (PostGIS optional)
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_dma_zone ON gis_dma_boundary(zone_id);

-- gis_topology_event — network change events
CREATE TABLE IF NOT EXISTS gis_topology_event (
    event_id            UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    event_type          VARCHAR(80)  NOT NULL,     -- ISOLATION|DMA_REVISION|VALVE_MODE|NEW_CONNECTION|DECOMMISSION|BURST_ISOLATION
    affected_asset_id   VARCHAR(120) REFERENCES ic3_asset_master(asset_id),
    affected_link_id    VARCHAR(120) REFERENCES gis_network_link(link_id),
    affected_dma_id     VARCHAR(50),
    event_description   TEXT,
    event_time          TIMESTAMPTZ  NOT NULL,
    restore_time        TIMESTAMPTZ,
    operator_id         VARCHAR(120),
    work_order_id       VARCHAR(120),
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_gte_asset ON gis_topology_event(affected_asset_id, event_time DESC);
CREATE INDEX IF NOT EXISTS idx_gte_dma   ON gis_topology_event(affected_dma_id, event_time DESC);
CREATE INDEX IF NOT EXISTS idx_gte_open  ON gis_topology_event(restore_time) WHERE restore_time IS NULL;

-- ===========================================================================
-- SECTION 6: MDM / HES (Smart Meter & AMI)
-- ===========================================================================

CREATE TABLE IF NOT EXISTS mdm_meter_master (
    meter_id            VARCHAR(120) PRIMARY KEY,  -- MTR-DMA012-00421
    asset_id            VARCHAR(120) REFERENCES ic3_asset_master(asset_id),
    customer_id         VARCHAR(120),              -- FK to crm_customer_master (soft)
    location_id         VARCHAR(120) REFERENCES ic3_location_master(location_id),
    dma_id              VARCHAR(50)  NOT NULL,
    meter_type          VARCHAR(80),               -- AMI|MECHANICAL|BULK|PREPAID|DISTRICT|INDUSTRIAL
    meter_size_mm       INTEGER,
    serial_no           VARCHAR(100),
    manufacturer        VARCHAR(100),
    model_no            VARCHAR(100),
    communication_type  VARCHAR(50),               -- LoRaWAN|NB-IoT|RF_MESH|PLC|GPRS|WALK-BY
    install_date        DATE,
    last_read_time      TIMESTAMPTZ,
    meter_status        VARCHAR(40)  NOT NULL DEFAULT 'Active', -- Active|Inactive|Faulty|Replaced
    latitude            DOUBLE PRECISION,
    longitude           DOUBLE PRECISION,
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_mdm_mm_dma    ON mdm_meter_master(dma_id);
CREATE INDEX IF NOT EXISTS idx_mdm_mm_type   ON mdm_meter_master(meter_type);
CREATE INDEX IF NOT EXISTS idx_mdm_mm_status ON mdm_meter_master(meter_status);

CREATE TABLE IF NOT EXISTS mdm_meter_reading (
    reading_id          BIGSERIAL    PRIMARY KEY,
    meter_id            VARCHAR(120) NOT NULL REFERENCES mdm_meter_master(meter_id),
    read_time           TIMESTAMPTZ  NOT NULL,
    read_type           VARCHAR(40)  NOT NULL DEFAULT 'AUTO', -- AUTO|MANUAL|ESTIMATED|ADJUSTED
    forward_reading_m3  NUMERIC(16,4),
    reverse_reading_m3  NUMERIC(16,4),
    net_reading_m3      NUMERIC(16,4),
    interval_consumption_m3 NUMERIC(16,4),
    flow_rate_m3h       NUMERIC(10,4),
    battery_voltage     NUMERIC(6,3),
    signal_strength_dbm INTEGER,
    tamper_flag         BOOLEAN      DEFAULT FALSE,
    quality_code        VARCHAR(20)  DEFAULT 'GOOD',
    received_at         TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_mdm_mr_meter ON mdm_meter_reading(meter_id, read_time DESC);
CREATE INDEX IF NOT EXISTS idx_mdm_mr_time  ON mdm_meter_reading(read_time DESC);

CREATE TABLE IF NOT EXISTS mdm_ami_event (
    ami_event_id        UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    meter_id            VARCHAR(120) NOT NULL REFERENCES mdm_meter_master(meter_id),
    event_time          TIMESTAMPTZ  NOT NULL,
    event_code          VARCHAR(50)  NOT NULL,     -- TAMPER|EMPTY_PIPE|BATTERY_LOW|BACKFLOW|NETWORK_JOIN|POWER_FAIL
    event_description   TEXT,
    severity            VARCHAR(20)  NOT NULL DEFAULT 'INFO', -- CRITICAL|HIGH|MEDIUM|LOW|INFO
    is_acknowledged     BOOLEAN      DEFAULT FALSE,
    ack_by              VARCHAR(100),
    ack_at              TIMESTAMPTZ,
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_ami_meter ON mdm_ami_event(meter_id, event_time DESC);
CREATE INDEX IF NOT EXISTS idx_ami_code  ON mdm_ami_event(event_code);
CREATE INDEX IF NOT EXISTS idx_ami_ack   ON mdm_ami_event(is_acknowledged) WHERE is_acknowledged = FALSE;

-- ===========================================================================
-- SECTION 7: CMMS Integration
-- ===========================================================================

CREATE TABLE IF NOT EXISTS cmms_work_order (
    work_order_id       VARCHAR(120) PRIMARY KEY,  -- WO-2026-001234
    asset_id            VARCHAR(120) REFERENCES ic3_asset_master(asset_id),
    dma_id              VARCHAR(50),
    wo_type             VARCHAR(80)  NOT NULL,     -- BREAKDOWN|PREVENTIVE|CORRECTIVE|INSPECTION|EMERGENCY|SHUTDOWN
    wo_status           VARCHAR(40)  NOT NULL DEFAULT 'OPEN', -- OPEN|IN_PROGRESS|COMPLETED|CANCELLED|ON_HOLD
    priority            VARCHAR(20)  NOT NULL DEFAULT 'MEDIUM', -- CRITICAL|HIGH|MEDIUM|LOW
    title               VARCHAR(300) NOT NULL,
    description         TEXT,
    raised_by           VARCHAR(120),
    assigned_to         VARCHAR(120),
    department          VARCHAR(100),
    planned_start       TIMESTAMPTZ,
    planned_end         TIMESTAMPTZ,
    actual_start        TIMESTAMPTZ,
    actual_end          TIMESTAMPTZ,
    breakdown_start     TIMESTAMPTZ,
    total_cost          NUMERIC(14,2),
    labour_cost         NUMERIC(14,2),
    parts_cost          NUMERIC(14,2),
    contractor_cost     NUMERIC(14,2),
    downtime_hours      NUMERIC(8,3),
    closure_notes       TEXT,
    service_request_id  VARCHAR(120),
    cmms_traceid        INTEGER,       -- CMMS API traceid cross-reference
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_cwo_asset   ON cmms_work_order(asset_id, planned_start DESC);
CREATE INDEX IF NOT EXISTS idx_cwo_status  ON cmms_work_order(wo_status);
CREATE INDEX IF NOT EXISTS idx_cwo_type    ON cmms_work_order(wo_type);
CREATE INDEX IF NOT EXISTS idx_cwo_dma     ON cmms_work_order(dma_id);

CREATE TABLE IF NOT EXISTS cmms_inspection_record (
    inspection_id       UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    asset_id            VARCHAR(120) NOT NULL REFERENCES ic3_asset_master(asset_id),
    work_order_id       VARCHAR(120) REFERENCES cmms_work_order(work_order_id),
    inspection_type     VARCHAR(80)  NOT NULL,     -- VISUAL|VIBRATION|THERMAL|ELECTRICAL|HYDRAULIC|OIL_SAMPLE
    inspection_date     TIMESTAMPTZ  NOT NULL,
    inspector_id        VARCHAR(120),
    overall_condition   VARCHAR(20)  NOT NULL,     -- GOOD|FAIR|POOR|CRITICAL
    findings            TEXT,
    recommendations     TEXT,
    next_inspection     DATE,
    checklist_data      JSONB,        -- structured checklist responses
    photos_attached     BOOLEAN      DEFAULT FALSE,
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_cir_asset ON cmms_inspection_record(asset_id, inspection_date DESC);

CREATE TABLE IF NOT EXISTS cmms_spare_parts (
    part_id             UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    work_order_id       VARCHAR(120) REFERENCES cmms_work_order(work_order_id),
    asset_id            VARCHAR(120) REFERENCES ic3_asset_master(asset_id),
    part_number         VARCHAR(100),
    part_name           VARCHAR(200) NOT NULL,
    manufacturer        VARCHAR(150),
    quantity_used       NUMERIC(10,3),
    unit_of_measure     VARCHAR(20)  DEFAULT 'Nos',
    unit_cost           NUMERIC(12,2),
    total_cost          NUMERIC(14,2),
    store_location      VARCHAR(100),
    po_reference        VARCHAR(120),
    used_at             TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_csp_wo    ON cmms_spare_parts(work_order_id);
CREATE INDEX IF NOT EXISTS idx_csp_asset ON cmms_spare_parts(asset_id);

-- ===========================================================================
-- SECTION 8: CRM Integration
-- ===========================================================================

CREATE TABLE IF NOT EXISTS crm_customer_master (
    customer_id         VARCHAR(120) PRIMARY KEY,  -- CUST-2026-087654
    customer_name       VARCHAR(200) NOT NULL,
    customer_type       VARCHAR(50)  NOT NULL DEFAULT 'Domestic', -- Domestic|Commercial|Industrial|Bulk|Government
    location_id         VARCHAR(120) REFERENCES ic3_location_master(location_id),
    dma_id              VARCHAR(50)  NOT NULL,
    zone_id             VARCHAR(50),
    address             TEXT,
    pincode             VARCHAR(20),
    mobile              VARCHAR(20),
    email               VARCHAR(150),
    aadhaar_no          VARCHAR(20),
    account_no          VARCHAR(60)  UNIQUE,
    connection_date     DATE,
    customer_status     VARCHAR(30)  NOT NULL DEFAULT 'Active', -- Active|Inactive|Disconnected|Reconnected
    consent_sms         BOOLEAN      DEFAULT TRUE,
    consent_email       BOOLEAN      DEFAULT FALSE,
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_crm_cm_dma    ON crm_customer_master(dma_id);
CREATE INDEX IF NOT EXISTS idx_crm_cm_type   ON crm_customer_master(customer_type);
CREATE INDEX IF NOT EXISTS idx_crm_cm_status ON crm_customer_master(customer_status);

CREATE TABLE IF NOT EXISTS crm_service_connection (
    connection_id       VARCHAR(120) PRIMARY KEY,  -- CONN-{CUST}-{SEQ}
    customer_id         VARCHAR(120) NOT NULL REFERENCES crm_customer_master(customer_id),
    meter_id            VARCHAR(120) REFERENCES mdm_meter_master(meter_id),
    asset_id            VARCHAR(120) REFERENCES ic3_asset_master(asset_id),
    dma_id              VARCHAR(50)  NOT NULL,
    connection_type     VARCHAR(50)  NOT NULL DEFAULT 'Domestic',
    pipe_size_mm        INTEGER,
    connection_status   VARCHAR(30)  NOT NULL DEFAULT 'Active',
    connection_date     DATE,
    disconnection_date  DATE,
    last_meter_read_m3  NUMERIC(16,4),
    avg_daily_demand_m3 NUMERIC(10,4),
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_crm_sc_customer ON crm_service_connection(customer_id);
CREATE INDEX IF NOT EXISTS idx_crm_sc_dma      ON crm_service_connection(dma_id);

CREATE TABLE IF NOT EXISTS crm_service_request (
    service_request_id  VARCHAR(120) PRIMARY KEY,  -- SR-2026-054321
    customer_id         VARCHAR(120) NOT NULL REFERENCES crm_customer_master(customer_id),
    meter_id            VARCHAR(120) REFERENCES mdm_meter_master(meter_id),
    asset_id            VARCHAR(120) REFERENCES ic3_asset_master(asset_id),
    dma_id              VARCHAR(50)  NOT NULL,
    complaint_category  VARCHAR(100) NOT NULL,     -- NO_SUPPLY|LOW_PRESSURE|WATER_QUALITY|BILLING|LEAK|METER_FAULT|PIPE_BURST|CONTAMINATION|OTHER
    complaint_description TEXT        NOT NULL,
    priority            VARCHAR(30)  NOT NULL DEFAULT 'P3-ROUTINE', -- P1-CRITICAL|P2-URGENT|P3-ROUTINE|P4-INFO
    sr_status           VARCHAR(40)  NOT NULL DEFAULT 'OPEN', -- OPEN|ASSIGNED|IN_PROGRESS|RESOLVED|CLOSED|REOPENED
    raised_channel      VARCHAR(50)  DEFAULT 'PORTAL',        -- PORTAL|MOBILE|IVR|HELPDESK|FIELD
    raised_at           TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    assigned_to         VARCHAR(120),
    assigned_at         TIMESTAMPTZ,
    resolved_at         TIMESTAMPTZ,
    closed_at           TIMESTAMPTZ,
    resolution_notes    TEXT,
    sla_target_hours    NUMERIC(8,2),
    sla_breached        BOOLEAN      DEFAULT FALSE,
    rating              INTEGER      CHECK (rating BETWEEN 1 AND 5),
    work_order_id       VARCHAR(120) REFERENCES cmms_work_order(work_order_id),
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_crm_sr_customer ON crm_service_request(customer_id, raised_at DESC);
CREATE INDEX IF NOT EXISTS idx_crm_sr_status   ON crm_service_request(sr_status);
CREATE INDEX IF NOT EXISTS idx_crm_sr_category ON crm_service_request(complaint_category);
CREATE INDEX IF NOT EXISTS idx_crm_sr_dma      ON crm_service_request(dma_id, raised_at DESC);

CREATE TABLE IF NOT EXISTS crm_notification_log (
    notification_id     UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    customer_id         VARCHAR(120) REFERENCES crm_customer_master(customer_id),
    service_request_id  VARCHAR(120) REFERENCES crm_service_request(service_request_id),
    notification_type   VARCHAR(50)  NOT NULL,     -- SMS|EMAIL|IVR|PUSH|WHATSAPP
    template_code       VARCHAR(100),
    message_body        TEXT,
    recipient_contact   VARCHAR(150),
    sent_at             TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    delivery_status     VARCHAR(20)  NOT NULL DEFAULT 'SENT', -- SENT|DELIVERED|FAILED|PENDING
    provider_message_id VARCHAR(200),
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_crm_nl_customer ON crm_notification_log(customer_id, sent_at DESC);
CREATE INDEX IF NOT EXISTS idx_crm_nl_status   ON crm_notification_log(delivery_status) WHERE delivery_status = 'FAILED';

-- ===========================================================================
-- SECTION 9: ERP / Finance
-- ===========================================================================

CREATE TABLE IF NOT EXISTS erp_cost_centre (
    cost_centre_id      VARCHAR(120) PRIMARY KEY,
    cost_centre_name    VARCHAR(200) NOT NULL,
    cost_centre_type    VARCHAR(80),               -- CAPEX|OPEX|MAINTENANCE|ADMIN
    department          VARCHAR(100),
    budget_year         INTEGER,
    annual_budget       NUMERIC(18,2),
    spent_to_date       NUMERIC(18,2) DEFAULT 0,
    is_active           BOOLEAN       NOT NULL DEFAULT TRUE,
    created_at          TIMESTAMPTZ   NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS erp_purchase_order (
    po_id               VARCHAR(120) PRIMARY KEY,
    work_order_id       VARCHAR(120) REFERENCES cmms_work_order(work_order_id),
    asset_id            VARCHAR(120) REFERENCES ic3_asset_master(asset_id),
    cost_centre_id      VARCHAR(120) REFERENCES erp_cost_centre(cost_centre_id),
    vendor_id           VARCHAR(120),
    vendor_name         VARCHAR(200),
    po_date             DATE         NOT NULL,
    po_amount           NUMERIC(18,2) NOT NULL,
    currency_code       VARCHAR(10)  DEFAULT 'INR',
    po_status           VARCHAR(40)  NOT NULL DEFAULT 'OPEN', -- OPEN|APPROVED|DISPATCHED|RECEIVED|CLOSED|CANCELLED
    delivery_date       DATE,
    received_date       DATE,
    invoice_no          VARCHAR(120),
    invoice_date        DATE,
    payment_date        DATE,
    payment_amount      NUMERIC(18,2),
    gst_amount          NUMERIC(14,2),
    notes               TEXT,
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_erp_po_asset  ON erp_purchase_order(asset_id);
CREATE INDEX IF NOT EXISTS idx_erp_po_status ON erp_purchase_order(po_status);

CREATE TABLE IF NOT EXISTS erp_asset_depreciation (
    depreciation_id     UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    asset_id            VARCHAR(120) NOT NULL REFERENCES ic3_asset_master(asset_id),
    cost_centre_id      VARCHAR(120) REFERENCES erp_cost_centre(cost_centre_id),
    financial_year      VARCHAR(10)  NOT NULL,     -- e.g. 2025-26
    opening_value       NUMERIC(14,2),
    depreciation_rate   NUMERIC(6,3),
    depreciation_method VARCHAR(50)  DEFAULT 'SLM', -- SLM|WDV
    depreciation_amount NUMERIC(14,2),
    closing_value       NUMERIC(14,2),
    accumulated_depreciation NUMERIC(14,2),
    useful_life_remaining INTEGER,
    notes               TEXT,
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_ead_asset ON erp_asset_depreciation(asset_id);
CREATE INDEX IF NOT EXISTS idx_ead_year  ON erp_asset_depreciation(financial_year);

-- ===========================================================================
-- SECTION 10: NRW Calculation Engine
-- ===========================================================================

CREATE TABLE IF NOT EXISTS nrw_dma_water_balance (
    balance_id          UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    dma_id              VARCHAR(50)  NOT NULL,
    balance_date        DATE         NOT NULL,
    balance_period      VARCHAR(20)  NOT NULL DEFAULT 'DAILY', -- DAILY|WEEKLY|MONTHLY
    system_input_m3     NUMERIC(16,4),   -- total inlet flow
    authorized_consumption_m3 NUMERIC(16,4),
    billed_consumption_m3     NUMERIC(16,4),
    unbilled_authorized_m3    NUMERIC(16,4),
    apparent_losses_m3        NUMERIC(16,4),  -- commercial losses
    real_losses_m3            NUMERIC(16,4),  -- physical losses
    nrw_volume_m3             NUMERIC(16,4),
    nrw_pct                   NUMERIC(6,3),
    ili                       NUMERIC(10,4),  -- Infrastructure Leakage Index
    mnf_m3h                   NUMERIC(12,4),  -- Minimum Night Flow
    pressure_avg_bar          NUMERIC(8,4),
    calculated_at             TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (dma_id, balance_date, balance_period)
);
CREATE INDEX IF NOT EXISTS idx_nrw_bal_dma  ON nrw_dma_water_balance(dma_id, balance_date DESC);
CREATE INDEX IF NOT EXISTS idx_nrw_bal_date ON nrw_dma_water_balance(balance_date DESC);

CREATE TABLE IF NOT EXISTS nrw_burst_leak_heatmap (
    heatmap_id          UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    dma_id              VARCHAR(50)  NOT NULL,
    asset_id            VARCHAR(120) REFERENCES ic3_asset_master(asset_id),
    pipe_id             VARCHAR(120) REFERENCES gis_network_link(link_id),
    event_type          VARCHAR(50)  NOT NULL,     -- BURST|LEAK|BLOCKAGE|PRESSURE_ANOMALY|MNF_SPIKE
    risk_weight         NUMERIC(8,4),
    water_loss_litres   NUMERIC(18,4),
    repair_duration_hours NUMERIC(10,3),
    latitude            DOUBLE PRECISION,
    longitude           DOUBLE PRECISION,
    event_start         TIMESTAMPTZ,
    event_end           TIMESTAMPTZ,
    is_resolved         BOOLEAN      NOT NULL DEFAULT FALSE,
    work_order_id       VARCHAR(120) REFERENCES cmms_work_order(work_order_id),
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_nrw_blh_dma    ON nrw_burst_leak_heatmap(dma_id, event_start DESC);
CREATE INDEX IF NOT EXISTS idx_nrw_blh_open   ON nrw_burst_leak_heatmap(is_resolved) WHERE is_resolved = FALSE;

-- ===========================================================================
-- SECTION 11: Hydraulic Model
-- ===========================================================================

CREATE TABLE IF NOT EXISTS hm_scenario (
    scenario_id         UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    scenario_name       VARCHAR(200) NOT NULL,
    scenario_type       VARCHAR(80)  NOT NULL,     -- CALIBRATION|DESIGN|EMERGENCY|WHAT_IF|DEMAND_FORECAST|LEAKAGE_ANALYSIS
    model_version       VARCHAR(50),
    dma_id              VARCHAR(50),
    trigger_type        VARCHAR(50)  DEFAULT 'ON_DEMAND', -- SCHEDULED|ON_DEMAND|ALARM_TRIGGERED|AI_TRIGGERED
    scenario_status     VARCHAR(40)  NOT NULL DEFAULT 'PENDING', -- PENDING|RUNNING|COMPLETED|FAILED|ARCHIVED
    created_by          VARCHAR(100) NOT NULL,
    run_start           TIMESTAMPTZ,
    run_end             TIMESTAMPTZ,
    duration_seconds    INTEGER,
    node_count          INTEGER,
    pipe_count          INTEGER,
    notes               TEXT,
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_hm_scen_status ON hm_scenario(scenario_status);
CREATE INDEX IF NOT EXISTS idx_hm_scen_dma    ON hm_scenario(dma_id);

CREATE TABLE IF NOT EXISTS hm_calibration_log (
    calibration_id      UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    scenario_id         UUID         NOT NULL REFERENCES hm_scenario(scenario_id),
    calibration_date    TIMESTAMPTZ  NOT NULL,
    calibrated_by       VARCHAR(100),
    calibration_method  VARCHAR(80),               -- MANUAL|GA|PARTICLE_SWARM|GRADIENT
    roughness_adj_factor NUMERIC(8,4),
    demand_multiplier   NUMERIC(8,4),
    r_squared           NUMERIC(6,4),              -- Model fit statistic
    max_pressure_error  NUMERIC(8,4),
    avg_pressure_error  NUMERIC(8,4),
    calibration_status  VARCHAR(30)  NOT NULL DEFAULT 'PENDING',
    notes               TEXT,
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_hm_cal_scenario ON hm_calibration_log(scenario_id);

CREATE TABLE IF NOT EXISTS hm_demand_pattern (
    pattern_id          UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    asset_id            VARCHAR(120) REFERENCES ic3_asset_master(asset_id),
    dma_id              VARCHAR(50),
    pattern_type        VARCHAR(50)  NOT NULL,     -- HOURLY|DAILY|SEASONAL|WEEKDAY|WEEKEND
    pattern_name        VARCHAR(150),
    day_type            VARCHAR(20),               -- WEEKDAY|SATURDAY|SUNDAY|HOLIDAY
    hour_of_day         INTEGER      CHECK (hour_of_day BETWEEN 0 AND 23),
    demand_factor       NUMERIC(8,4) NOT NULL,     -- Multiplier relative to average
    base_demand_m3h     NUMERIC(12,4),
    effective_from      DATE,
    effective_to        DATE,
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_hm_dp_dma  ON hm_demand_pattern(dma_id);
CREATE INDEX IF NOT EXISTS idx_hm_dp_type ON hm_demand_pattern(pattern_type);

CREATE TABLE IF NOT EXISTS hm_result (
    result_id           UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    scenario_id         UUID         NOT NULL REFERENCES hm_scenario(scenario_id),
    result_type         VARCHAR(20)  NOT NULL,     -- NODE|LINK
    asset_id            VARCHAR(120) REFERENCES ic3_asset_master(asset_id),
    node_id             VARCHAR(120) REFERENCES gis_network_node(node_id),
    link_id             VARCHAR(120) REFERENCES gis_network_link(link_id),
    sim_time            TIMESTAMPTZ  NOT NULL,
    pressure_bar        NUMERIC(12,4),
    head_m              NUMERIC(12,4),
    flow_m3h            NUMERIC(12,4),
    velocity_ms         NUMERIC(10,4),
    headloss_per_km     NUMERIC(10,4),
    demand_m3h          NUMERIC(12,4),
    quality_mg_l        NUMERIC(12,6),
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_hm_result_scenario ON hm_result(scenario_id, sim_time);
CREATE INDEX IF NOT EXISTS idx_hm_result_asset    ON hm_result(asset_id);

-- ===========================================================================
-- SECTION 12: Digital Twin
-- ===========================================================================

CREATE TABLE IF NOT EXISTS dt_object (
    twin_object_id      VARCHAR(150) PRIMARY KEY,  -- DT-AST-PUMP-0001
    asset_id            VARCHAR(120) NOT NULL REFERENCES ic3_asset_master(asset_id),
    twin_name           VARCHAR(200) NOT NULL,
    twin_type           VARCHAR(80)  NOT NULL,     -- PUMP|VALVE|PIPE|RESERVOIR|METER|DMA|WTP
    model_schema_version VARCHAR(20) DEFAULT 'v1.0',
    live_pressure_bar   NUMERIC(12,4),
    live_flow_m3h       NUMERIC(12,4),
    live_state          VARCHAR(80),               -- RUNNING|STOPPED|STANDBY|FAULT
    live_health_score   NUMERIC(5,2),
    last_live_update    TIMESTAMPTZ,
    twin_properties     JSONB,        -- full live property bag
    is_active           BOOLEAN      NOT NULL DEFAULT TRUE,
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_dt_obj_asset ON dt_object(asset_id);
CREATE INDEX IF NOT EXISTS idx_dt_obj_type  ON dt_object(twin_type);

CREATE TABLE IF NOT EXISTS dt_simulation_state (
    sim_state_id        UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    twin_object_id      VARCHAR(150) NOT NULL REFERENCES dt_object(twin_object_id),
    scenario_id         UUID         REFERENCES hm_scenario(scenario_id),
    sim_time            TIMESTAMPTZ  NOT NULL,
    sim_pressure_bar    NUMERIC(18,4),
    sim_flow_m3h        NUMERIC(18,4),
    sim_state           VARCHAR(80),
    delta_pressure      NUMERIC(18,4),  -- sim - live (model validation)
    delta_flow          NUMERIC(18,4),
    notes               TEXT,
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_dt_ss_twin ON dt_simulation_state(twin_object_id, sim_time DESC);

CREATE TABLE IF NOT EXISTS dt_state_history (
    history_id          BIGSERIAL    PRIMARY KEY,
    twin_object_id      VARCHAR(150) NOT NULL REFERENCES dt_object(twin_object_id),
    state_time          TIMESTAMPTZ  NOT NULL,
    live_state          VARCHAR(80),
    pressure_bar        NUMERIC(12,4),
    flow_m3h            NUMERIC(12,4),
    health_score        NUMERIC(5,2),
    alarm_state         VARCHAR(40)  DEFAULT 'NORMAL',
    properties_snapshot JSONB,
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_dt_sh_twin ON dt_state_history(twin_object_id, state_time DESC);

-- ===========================================================================
-- SECTION 13: AI / Analytics
-- ===========================================================================

CREATE TABLE IF NOT EXISTS ai_model_registry (
    model_id            VARCHAR(100) PRIMARY KEY,
    model_name          VARCHAR(200) NOT NULL,
    model_version       VARCHAR(50)  NOT NULL,
    model_type          VARCHAR(80)  NOT NULL,     -- ANOMALY_DETECTION|FAILURE_PREDICTION|LEAK_DETECTION|DEMAND_FORECAST|NRW_PREDICTION|WATER_QUALITY|ENERGY_OPTIMISATION
    framework           VARCHAR(50),               -- scikit-learn|PyTorch|TensorFlow|XGBoost|ONNX
    target_asset_types  TEXT,
    feature_count       INTEGER,
    training_data_start DATE,
    training_data_end   DATE,
    accuracy_score      NUMERIC(6,4),
    f1_score            NUMERIC(6,4),
    deployment_status   VARCHAR(40)  NOT NULL DEFAULT 'DEVELOPMENT', -- DEVELOPMENT|STAGING|PRODUCTION|RETIRED
    endpoint_url        VARCHAR(255),
    model_path          VARCHAR(500),
    deployed_at         TIMESTAMPTZ,
    deployed_by         VARCHAR(100),
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_ai_mr_type   ON ai_model_registry(model_type);
CREATE INDEX IF NOT EXISTS idx_ai_mr_status ON ai_model_registry(deployment_status);

CREATE TABLE IF NOT EXISTS ai_prediction_result (
    prediction_id       UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    model_id            VARCHAR(100) NOT NULL REFERENCES ai_model_registry(model_id),
    asset_id            VARCHAR(120) REFERENCES ic3_asset_master(asset_id),
    dma_id              VARCHAR(50),
    prediction_time     TIMESTAMPTZ  NOT NULL,
    prediction_type     VARCHAR(80)  NOT NULL,     -- matches model_type
    predicted_value     NUMERIC(18,6),
    confidence_score    NUMERIC(6,4),
    prediction_horizon_h INTEGER,                  -- hours ahead predicted
    is_anomaly          BOOLEAN      DEFAULT FALSE,
    anomaly_score       NUMERIC(8,4),
    alert_raised        BOOLEAN      DEFAULT FALSE,
    work_order_created  VARCHAR(120) REFERENCES cmms_work_order(work_order_id),
    prediction_payload  JSONB,
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_ai_pr_asset  ON ai_prediction_result(asset_id, prediction_time DESC);
CREATE INDEX IF NOT EXISTS idx_ai_pr_model  ON ai_prediction_result(model_id, prediction_time DESC);
CREATE INDEX IF NOT EXISTS idx_ai_pr_anomaly ON ai_prediction_result(is_anomaly) WHERE is_anomaly = TRUE;

CREATE TABLE IF NOT EXISTS ai_feature_store (
    feature_id          BIGSERIAL    PRIMARY KEY,
    asset_id            VARCHAR(120) NOT NULL REFERENCES ic3_asset_master(asset_id),
    feature_time        TIMESTAMPTZ  NOT NULL,
    feature_set_name    VARCHAR(100) NOT NULL,     -- e.g. "pump_health_v2"
    features            JSONB        NOT NULL,
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_ai_fs_asset ON ai_feature_store(asset_id, feature_time DESC);
CREATE INDEX IF NOT EXISTS idx_ai_fs_set   ON ai_feature_store(feature_set_name, feature_time DESC);

CREATE TABLE IF NOT EXISTS ai_asset_health_score (
    health_id           UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    asset_id            VARCHAR(120) NOT NULL REFERENCES ic3_asset_master(asset_id),
    model_id            VARCHAR(100) NOT NULL REFERENCES ai_model_registry(model_id),
    scored_at           TIMESTAMPTZ  NOT NULL,
    health_score        NUMERIC(5,2) NOT NULL,     -- 0 (critical) to 100 (excellent)
    risk_level          VARCHAR(30)  NOT NULL,     -- LOW|MEDIUM|HIGH|CRITICAL
    remaining_useful_life_days INTEGER,
    failure_probability_7d    NUMERIC(8,4),
    failure_probability_30d   NUMERIC(8,4),
    top_risk_factors    TEXT[],
    recommended_action  TEXT,
    alert_raised        BOOLEAN      DEFAULT FALSE,
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_ai_ahs_asset  ON ai_asset_health_score(asset_id, scored_at DESC);
CREATE INDEX IF NOT EXISTS idx_ai_ahs_risk   ON ai_asset_health_score(risk_level);

-- ===========================================================================
-- SECTION 14: API Integration
-- ===========================================================================

CREATE TABLE IF NOT EXISTS int_external_id_mapping (
    mapping_id          UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    system_id           VARCHAR(50)  REFERENCES ic3_system_master(system_id),
    entity_type         VARCHAR(80)  NOT NULL,     -- ASSET|TAG|CUSTOMER|METER|WORK_ORDER|DMA|LOCATION
    ic3_entity_id       VARCHAR(150) NOT NULL,
    external_entity_id  VARCHAR(150) NOT NULL,
    external_entity_name VARCHAR(300),
    sync_direction      VARCHAR(20)  DEFAULT 'BIDIRECTIONAL', -- INBOUND|OUTBOUND|BIDIRECTIONAL
    is_active           BOOLEAN      NOT NULL DEFAULT TRUE,
    last_synced_at      TIMESTAMPTZ,
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    UNIQUE (system_id, entity_type, external_entity_id)
);
CREATE INDEX IF NOT EXISTS idx_int_eim_ic3    ON int_external_id_mapping(ic3_entity_id, entity_type);
CREATE INDEX IF NOT EXISTS idx_int_eim_ext    ON int_external_id_mapping(system_id, external_entity_id);

CREATE TABLE IF NOT EXISTS int_api_exchange_log (
    log_id              UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    system_id           VARCHAR(50)  REFERENCES ic3_system_master(system_id),
    direction           VARCHAR(10)  NOT NULL,     -- INBOUND|OUTBOUND
    endpoint            VARCHAR(500) NOT NULL,
    method              VARCHAR(10)  NOT NULL,
    request_payload     JSONB,
    response_code       INTEGER,
    response_payload    JSONB,
    duration_ms         INTEGER,
    error_message       TEXT,
    entity_type         VARCHAR(80),
    entity_id           VARCHAR(150),
    user_id             VARCHAR(100),
    ip_address          VARCHAR(45),
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_int_ael_system ON int_api_exchange_log(system_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_int_ael_error  ON int_api_exchange_log(response_code) WHERE response_code >= 400;

CREATE TABLE IF NOT EXISTS int_data_quality_rule (
    rule_id             UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    rule_name           VARCHAR(200) NOT NULL,
    system_id           VARCHAR(50)  REFERENCES ic3_system_master(system_id),
    entity_type         VARCHAR(80)  NOT NULL,
    field_name          VARCHAR(150) NOT NULL,
    rule_type           VARCHAR(50)  NOT NULL,     -- NOT_NULL|RANGE|REGEX|ENUM|UNIQUE|REFERENTIAL
    rule_definition     JSONB        NOT NULL,     -- {"min":0,"max":100} or {"pattern":"^AST-"}
    severity            VARCHAR(20)  NOT NULL DEFAULT 'ERROR', -- ERROR|WARNING|INFO
    is_active           BOOLEAN      NOT NULL DEFAULT TRUE,
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_int_dqr_system ON int_data_quality_rule(system_id, entity_type);

CREATE TABLE IF NOT EXISTS int_dq_quarantine (
    quarantine_id       UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    rule_id             UUID         REFERENCES int_data_quality_rule(rule_id),
    system_id           VARCHAR(50),
    entity_type         VARCHAR(80)  NOT NULL,
    raw_payload         JSONB        NOT NULL,
    failed_field        VARCHAR(150),
    failure_reason      TEXT,
    received_at         TIMESTAMPTZ  NOT NULL,
    reviewed            BOOLEAN      NOT NULL DEFAULT FALSE,
    reviewed_by         VARCHAR(100),
    reviewed_at         TIMESTAMPTZ,
    resolution          VARCHAR(50),              -- ACCEPTED|REJECTED|CORRECTED
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_int_dq_rule     ON int_dq_quarantine(rule_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_int_dq_reviewed ON int_dq_quarantine(reviewed) WHERE reviewed = FALSE;

CREATE TABLE IF NOT EXISTS int_webhook_queue (
    queue_id            UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    event_type          VARCHAR(100) NOT NULL,     -- ALARM_RAISED|WO_COMPLETED|PREDICTION_READY|SR_RESOLVED|NRW_ALERT
    payload             JSONB        NOT NULL,
    target_system_id    VARCHAR(50)  REFERENCES ic3_system_master(system_id),
    retry_count         INTEGER      DEFAULT 0,
    status              VARCHAR(40)  NOT NULL DEFAULT 'PENDING', -- PENDING|DELIVERED|FAILED|EXPIRED
    last_attempt_at     TIMESTAMPTZ,
    expires_at          TIMESTAMPTZ,
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_int_wq_status  ON int_webhook_queue(status) WHERE status IN ('PENDING','FAILED');
CREATE INDEX IF NOT EXISTS idx_int_wq_expires ON int_webhook_queue(expires_at) WHERE status = 'PENDING';

-- ===========================================================================
-- SECTION 15: Data Warehouse Dimensions & Facts
-- ===========================================================================

CREATE TABLE IF NOT EXISTS dw_dim_asset (
    asset_sk            BIGSERIAL    PRIMARY KEY,  -- surrogate key
    asset_id            VARCHAR(120) NOT NULL,     -- natural key from ic3_asset_master
    asset_name          VARCHAR(200),
    asset_type          VARCHAR(80),
    asset_category      VARCHAR(100),
    criticality         VARCHAR(30),
    dma_id              VARCHAR(50),
    zone_id             VARCHAR(50),
    site_name           VARCHAR(150),
    manufacturer        VARCHAR(150),
    install_date        DATE,
    design_life_years   INTEGER,
    scd_start_date      DATE         NOT NULL DEFAULT CURRENT_DATE,
    scd_end_date        DATE,
    is_current          BOOLEAN      NOT NULL DEFAULT TRUE
);
CREATE INDEX IF NOT EXISTS idx_dw_da_asset   ON dw_dim_asset(asset_id);
CREATE INDEX IF NOT EXISTS idx_dw_da_current ON dw_dim_asset(is_current) WHERE is_current = TRUE;

CREATE TABLE IF NOT EXISTS dw_dim_date (
    date_sk             INTEGER      PRIMARY KEY,  -- YYYYMMDD
    calendar_date       DATE         NOT NULL UNIQUE,
    year                INTEGER,
    quarter             INTEGER,
    month               INTEGER,
    month_name          VARCHAR(20),
    week_of_year        INTEGER,
    day_of_week         INTEGER,     -- 1=Monday…7=Sunday
    day_name            VARCHAR(20),
    is_weekend          BOOLEAN,
    financial_year      VARCHAR(10)  -- e.g. 2025-26
);

CREATE TABLE IF NOT EXISTS dw_dim_time (
    time_sk             INTEGER      PRIMARY KEY,  -- HHMMSS
    hour                INTEGER,
    minute              INTEGER,
    quarter_hour        INTEGER,     -- 0-95 (every 15 min)
    shift               VARCHAR(20)  -- MORNING|AFTERNOON|EVENING|NIGHT
);

CREATE TABLE IF NOT EXISTS dw_fact_telemetry_hourly (
    fact_id             BIGSERIAL    PRIMARY KEY,
    date_sk             INTEGER      NOT NULL REFERENCES dw_dim_date(date_sk),
    time_sk             INTEGER      NOT NULL REFERENCES dw_dim_time(time_sk),
    asset_sk            BIGINT       NOT NULL REFERENCES dw_dim_asset(asset_sk),
    asset_id            VARCHAR(120),
    dma_id              VARCHAR(50),
    parameter_code      VARCHAR(80),
    avg_value           NUMERIC(18,6),
    min_value           NUMERIC(18,6),
    max_value           NUMERIC(18,6),
    reading_count       INTEGER,
    good_reading_count  INTEGER,
    alarm_minutes       INTEGER      DEFAULT 0,
    availability_pct    NUMERIC(6,3)
);
CREATE INDEX IF NOT EXISTS idx_dw_fth_date  ON dw_fact_telemetry_hourly(date_sk, asset_sk);
CREATE INDEX IF NOT EXISTS idx_dw_fth_dma   ON dw_fact_telemetry_hourly(dma_id, date_sk);

CREATE TABLE IF NOT EXISTS dw_fact_nrw_daily (
    fact_id             BIGSERIAL    PRIMARY KEY,
    date_sk             INTEGER      NOT NULL REFERENCES dw_dim_date(date_sk),
    dma_id              VARCHAR(50)  NOT NULL,
    system_input_m3     NUMERIC(16,4),
    nrw_volume_m3       NUMERIC(16,4),
    nrw_pct             NUMERIC(6,3),
    real_losses_m3      NUMERIC(16,4),
    apparent_losses_m3  NUMERIC(16,4),
    billed_m3           NUMERIC(16,4),
    mnf_m3h             NUMERIC(12,4),
    pressure_avg_bar    NUMERIC(8,4),
    burst_count         INTEGER      DEFAULT 0,
    UNIQUE (date_sk, dma_id)
);
CREATE INDEX IF NOT EXISTS idx_dw_fnd_date ON dw_fact_nrw_daily(date_sk);
CREATE INDEX IF NOT EXISTS idx_dw_fnd_dma  ON dw_fact_nrw_daily(dma_id, date_sk);

CREATE TABLE IF NOT EXISTS dw_fact_work_order (
    fact_id             BIGSERIAL    PRIMARY KEY,
    date_sk             INTEGER      NOT NULL REFERENCES dw_dim_date(date_sk),
    asset_sk            BIGINT       NOT NULL REFERENCES dw_dim_asset(asset_sk),
    work_order_id       VARCHAR(120),
    asset_id            VARCHAR(120),
    dma_id              VARCHAR(50),
    wo_type             VARCHAR(80),
    priority            VARCHAR(20),
    open_to_close_hours NUMERIC(10,3),
    breakdown_hours     NUMERIC(10,3),
    total_cost          NUMERIC(14,2),
    is_sla_met          BOOLEAN,
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_dw_fwo_date  ON dw_fact_work_order(date_sk, asset_sk);
CREATE INDEX IF NOT EXISTS idx_dw_fwo_dma   ON dw_fact_work_order(dma_id);

-- ===========================================================================
-- SECTION 21: Management Reporting / KPI
-- ===========================================================================

CREATE TABLE IF NOT EXISTS rpt_kpi_definition (
    kpi_id              VARCHAR(100) PRIMARY KEY,  -- KPI-NRW-01, KPI-PUMP-01
    kpi_name            VARCHAR(200) NOT NULL,
    kpi_category        VARCHAR(100) NOT NULL,     -- NRW|PUMP|QUALITY|CUSTOMER|ENERGY|MAINTENANCE|COMPLIANCE|SUPPLY
    calculation_sql     TEXT,
    unit                VARCHAR(40),               -- %, m3/day, kWh/ML, hours
    target_value        NUMERIC(18,4),
    green_threshold     NUMERIC(18,4),
    amber_threshold     NUMERIC(18,4),
    red_threshold       NUMERIC(18,4),
    frequency           VARCHAR(30)  DEFAULT 'DAILY', -- DAILY|WEEKLY|MONTHLY|QUARTERLY
    is_active           BOOLEAN      NOT NULL DEFAULT TRUE,
    description         TEXT,
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS rpt_kpi_snapshot (
    snapshot_id         UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    kpi_id              VARCHAR(100) NOT NULL REFERENCES rpt_kpi_definition(kpi_id),
    dma_id              VARCHAR(50),
    snapshot_date       DATE         NOT NULL,
    actual_value        NUMERIC(18,4),
    target_value        NUMERIC(18,4),
    variance            NUMERIC(18,4),
    variance_pct        NUMERIC(8,4),
    rag_status          VARCHAR(10)  NOT NULL DEFAULT 'GREEN', -- RED|AMBER|GREEN
    trend               VARCHAR(20),               -- IMPROVING|STABLE|DETERIORATING
    notes               TEXT,
    calculated_at       TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    UNIQUE (kpi_id, dma_id, snapshot_date)
);
CREATE INDEX IF NOT EXISTS idx_rpt_ks_kpi  ON rpt_kpi_snapshot(kpi_id, snapshot_date DESC);
CREATE INDEX IF NOT EXISTS idx_rpt_ks_dma  ON rpt_kpi_snapshot(dma_id, snapshot_date DESC);
CREATE INDEX IF NOT EXISTS idx_rpt_ks_rag  ON rpt_kpi_snapshot(rag_status) WHERE rag_status IN ('RED','AMBER');
`,
	},
	{
		// Consolidation migration:
		//   1. Fix dirty category data in cmms_asset_master (3 spellings of Instrumentation)
		//   2. Seed ic3_location_master from 10 unique locations
		//   3. Seed ic3_asset_master (THE canonical table) from cmms_asset_master
		//   4. Link cmms_asset_master.ic3_asset_id back to ic3_asset_master.asset_id
		//
		// After this migration the ownership model is:
		//   ic3_asset_master   = canonical identity (single source of truth)
		//   ic3_location_master = location names / hierarchy
		//   cmms_asset_master  = CMMS API sync cache (linked via ic3_asset_id FK)
		//   assets             = SCADA live telemetry cache (auto-written by poller)
		Version:     6,
		Description: "Consolidate: seed ic3_asset_master + ic3_location_master from cmms import; clean category data",
		SQL: `
-- ===========================================================================
-- STEP 1: Normalise dirty category data in cmms_asset_master
-- Fixes: "INSTRUMENATION" (typo), "INSTRUMENTATION" → canonical "Instrument"
-- ===========================================================================
UPDATE cmms_asset_master
SET category_name = 'Instrument'
WHERE UPPER(TRIM(category_name)) IN ('INSTRUMENTATION','INSTRUMENATION','INSTRUMENENTATION');

-- "PRESSURE GAUGE" was wrongly set as category; move to sub_category
UPDATE cmms_asset_master
SET category_name = 'Instrument',
    sub_category  = COALESCE(NULLIF(TRIM(sub_category),''), 'Pressure Gauge')
WHERE UPPER(TRIM(category_name)) = 'PRESSURE GAUGE';

-- "Sample Category" → mark as uncategorised
UPDATE cmms_asset_master
SET category_name = 'Uncategorised'
WHERE TRIM(category_name) = 'Sample Category';

-- ===========================================================================
-- STEP 2: Seed ic3_location_master
-- One row per unique (customer_name, location_name) pair.
-- location_id format: LOC-<customer_code>-<seq> using customer initials.
-- ===========================================================================
INSERT INTO ic3_location_master (
    location_id,
    location_name,
    location_type,
    city,
    created_at
)
SELECT DISTINCT ON (location_name)
    'LOC-' || UPPER(REGEXP_REPLACE(COALESCE(customer_name,'UNK'), '[^A-Za-z0-9]', '', 'g'))
             || '-' || LPAD(ROW_NUMBER() OVER (ORDER BY location_name)::TEXT, 4, '0'),
    location_name,
    CASE
        WHEN location_name ILIKE '%WTP%'   THEN 'SITE'
        WHEN location_name ILIKE '%BPS%'   THEN 'STATION'
        WHEN location_name ILIKE '%UGR%'   THEN 'STATION'
        WHEN location_name ILIKE '%ZONE%'  THEN 'ZONE'
        WHEN location_name ILIKE '%DMA%'   THEN 'DMA'
        ELSE 'STATION'
    END AS location_type,
    'New Delhi' AS city,
    NOW()
FROM cmms_asset_master
WHERE location_name IS NOT NULL AND TRIM(location_name) <> ''
ON CONFLICT (location_id) DO NOTHING;

-- ===========================================================================
-- STEP 3: Seed ic3_asset_master from cmms_asset_master
-- asset_id = report_asset_id (already unique per asset in the report)
-- location_id = matched from ic3_location_master by location_name
-- ===========================================================================
INSERT INTO ic3_asset_master (
    asset_id,
    asset_name,
    asset_type,
    asset_category,
    asset_sub_category,
    criticality,
    location_id,
    site_id,
    manufacturer,
    model_no,
    serial_no,
    capacity_rating,
    purchase_value,
    depreciation_method,
    asset_status,
    in_charge_person,
    department,
    cmms_id,
    data_source,
    created_at,
    updated_at
)
SELECT
    c.report_asset_id                                          AS asset_id,
    COALESCE(NULLIF(TRIM(c.asset_name),''),
             NULLIF(TRIM(c.serialnumber),''),
             c.report_asset_id)                                AS asset_name,
    COALESCE(NULLIF(TRIM(c.sub_category),''),
             NULLIF(TRIM(c.category_name),''), 'Unknown')      AS asset_type,
    COALESCE(NULLIF(TRIM(c.category_name),''), 'Unknown')      AS asset_category,
    NULLIF(TRIM(c.sub_category),'')                            AS asset_sub_category,
    CASE
        WHEN c.criticality ILIKE 'critical%' THEN 'Critical'
        WHEN c.criticality ILIKE 'high%'     THEN 'Critical'
        WHEN c.criticality ILIKE 'medium%'   THEN 'High'
        ELSE 'Non-Critical'
    END                                                        AS criticality,
    loc.location_id,
    NULLIF(TRIM(c.floor_name),'')                              AS site_id,
    NULLIF(TRIM(c.manufacturer_name),'')                       AS manufacturer,
    NULLIF(TRIM(c.asset_model),'')                             AS model_no,
    NULLIF(TRIM(c.serialnumber),'')                            AS serial_no,
    NULLIF(TRIM(c.capacity_rating),'')                         AS capacity_rating,
    c.purchase_value,
    NULLIF(TRIM(c.depreciation_type),'')                       AS depreciation_method,
    CASE WHEN c.status = 1 THEN 'Active' ELSE 'Inactive' END   AS asset_status,
    NULLIF(TRIM(c.in_charge_person),'')                        AS in_charge_person,
    NULLIF(TRIM(c.department_name),'')                         AS department,
    c.traceid                                                   AS cmms_id,
    'CMMS'                                                     AS data_source,
    c.created_at,
    c.updated_at
FROM cmms_asset_master c
LEFT JOIN ic3_location_master loc ON loc.location_name = c.location_name
WHERE c.report_asset_id IS NOT NULL
ON CONFLICT (asset_id) DO UPDATE SET
    asset_name       = EXCLUDED.asset_name,
    asset_category   = EXCLUDED.asset_category,
    asset_sub_category = EXCLUDED.asset_sub_category,
    criticality      = EXCLUDED.criticality,
    location_id      = EXCLUDED.location_id,
    site_id          = EXCLUDED.site_id,
    manufacturer     = EXCLUDED.manufacturer,
    model_no         = EXCLUDED.model_no,
    serial_no        = EXCLUDED.serial_no,
    asset_status     = EXCLUDED.asset_status,
    cmms_id          = EXCLUDED.cmms_id,
    updated_at       = NOW();

-- ===========================================================================
-- STEP 4: Write ic3_asset_id back into cmms_asset_master (the link)
-- ===========================================================================
UPDATE cmms_asset_master c
SET ic3_asset_id = c.report_asset_id
WHERE c.report_asset_id IS NOT NULL
  AND c.ic3_asset_id IS NULL;
`,
	},
	{
		// Drop cmms_asset_master. ic3_asset_master becomes the ONE asset table.
		// Steps:
		//   1. Add all remaining CMMS-specific columns to ic3_asset_master
		//   2. Backfill those columns from cmms_asset_master
		//   3. Migrate child-table FKs (performance, pm_schedule, wo_summary)
		//      from  asset_id BIGINT → cmms_asset_master(id)
		//      to    asset_id TEXT   → ic3_asset_master(asset_id)
		//   4. Drop asset_cmms_mapping and cmms_asset_master
		//   5. Recreate views pointing at ic3_asset_master
		Version:     7,
		Description: "Consolidate: merge cmms_asset_master into ic3_asset_master; drop redundant table",
		SQL: `
-- Migration 7: Add missing CMMS columns to ic3_asset_master
-- Note: Data consolidation was already done in migration 6
ALTER TABLE ic3_asset_master
    ADD COLUMN IF NOT EXISTS traceid                  INTEGER UNIQUE,
    ADD COLUMN IF NOT EXISTS barcode                  TEXT,
    ADD COLUMN IF NOT EXISTS qrcode                   TEXT,
    ADD COLUMN IF NOT EXISTS report_seq_no            INTEGER,
    ADD COLUMN IF NOT EXISTS customer_name            TEXT,
    ADD COLUMN IF NOT EXISTS customer_id              INTEGER,
    ADD COLUMN IF NOT EXISTS building_name            TEXT,
    ADD COLUMN IF NOT EXISTS location_name            TEXT,
    ADD COLUMN IF NOT EXISTS department_id            INTEGER,
    ADD COLUMN IF NOT EXISTS schedule_count_configured INTEGER DEFAULT 0,
    ADD COLUMN IF NOT EXISTS schedule_count_initiated  INTEGER DEFAULT 0,
    ADD COLUMN IF NOT EXISTS schedule_assigned         TEXT,
    ADD COLUMN IF NOT EXISTS schedule_configured       INTEGER DEFAULT 0,
    ADD COLUMN IF NOT EXISTS schedule_initiate         INTEGER DEFAULT 0,
    ADD COLUMN IF NOT EXISTS ticketcount               INTEGER DEFAULT 0,
    ADD COLUMN IF NOT EXISTS iot_device_mapped         INTEGER DEFAULT 0,
    ADD COLUMN IF NOT EXISTS mapping_status            INTEGER DEFAULT 0,
    ADD COLUMN IF NOT EXISTS asset_live_status         INTEGER NOT NULL DEFAULT 1,
    ADD COLUMN IF NOT EXISTS reason_for_inactive       TEXT,
    ADD COLUMN IF NOT EXISTS contract_name             TEXT,
    ADD COLUMN IF NOT EXISTS contract_number           TEXT,
    ADD COLUMN IF NOT EXISTS contract_start_date       DATE,
    ADD COLUMN IF NOT EXISTS contract_end_date         DATE,
    ADD COLUMN IF NOT EXISTS units_produced            NUMERIC(16,3),
    ADD COLUMN IF NOT EXISTS po_number                 TEXT,
    ADD COLUMN IF NOT EXISTS purchase_number           TEXT,
    ADD COLUMN IF NOT EXISTS purchase_date             DATE,
    ADD COLUMN IF NOT EXISTS selectmachineowner        INTEGER,
    ADD COLUMN IF NOT EXISTS traceability_updated_on   DATE,
    ADD COLUMN IF NOT EXISTS last_synced_at            TIMESTAMPTZ,
    ADD COLUMN IF NOT EXISTS sync_version              INTEGER DEFAULT 1,
    ADD COLUMN IF NOT EXISTS criticality_type          INTEGER,
    ADD COLUMN IF NOT EXISTS equipments_model_id       INTEGER,
    ADD COLUMN IF NOT EXISTS model_id_cmms             INTEGER,
    ADD COLUMN IF NOT EXISTS manufacturer_id           INTEGER,
    ADD COLUMN IF NOT EXISTS cat_id                    INTEGER,
    ADD COLUMN IF NOT EXISTS subcategory_id            INTEGER;

CREATE INDEX IF NOT EXISTS idx_iam_traceid       ON ic3_asset_master(traceid) WHERE traceid IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_iam_customer      ON ic3_asset_master(customer_name);
CREATE INDEX IF NOT EXISTS idx_iam_iot           ON ic3_asset_master(iot_device_mapped);
CREATE INDEX IF NOT EXISTS idx_iam_contract_end  ON ic3_asset_master(contract_end_date) WHERE contract_end_date IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_iam_live_status   ON ic3_asset_master(asset_live_status);

-- Recreate views pointing at ic3_asset_master
CREATE OR REPLACE VIEW v_cmms_asset_health AS
SELECT
    m.asset_id,
    m.traceid,
    m.customer_name,
    m.location_name,
    m.building_name,
    m.site_id          AS floor_name,
    m.department,
    m.asset_category   AS category_name,
    m.asset_sub_category AS sub_category,
    m.asset_name,
    m.model_no         AS equipment_model_name,
    m.manufacturer,
    m.in_charge_person,
    m.criticality,
    m.criticality_type,
    m.asset_status     AS status,
    m.asset_live_status,
    m.iot_device_mapped,
    m.latitude,
    m.longitude,
    m.install_date     AS installation_date,
    m.contract_end_date,
    CASE WHEN m.contract_end_date IS NOT NULL
          AND m.contract_end_date < NOW() + INTERVAL '30 days'
          AND m.contract_end_date > NOW()
         THEN TRUE ELSE FALSE END  AS contract_expiring_soon,
    CASE WHEN m.contract_end_date < NOW() THEN TRUE ELSE FALSE END AS contract_expired,
    p.availability_pct,
    p.total_ticket,
    ROUND(p.mttr_seconds   / 3600.0, 2) AS mttr_hours,
    ROUND(p.mtbf_seconds   / 3600.0, 2) AS mtbf_hours,
    ROUND(p.total_actual_seconds / 3600.0, 2) AS total_run_hours,
    s.maintenance_name        AS last_pm_name,
    s.schedule_date           AS last_pm_planned,
    s.schedule_completed_date AS last_pm_completed,
    s.next_schedule_date,
    s.pm_tat_days,
    s.is_overdue              AS pm_overdue,
    EXTRACT(DAY FROM (s.next_schedule_date - NOW()))::INTEGER AS days_until_next_pm,
    w.total_wo_count,
    w.total_pm_count,
    w.total_breakdown_count,
    w.pm_compliance_pct,
    w.wo_count_last_year,
    w.breakdown_count_last_year,
    m.last_synced_at
FROM ic3_asset_master m
LEFT JOIN LATERAL (
    SELECT * FROM cmms_asset_performance WHERE asset_id = m.asset_id ORDER BY synced_at DESC LIMIT 1
) p ON TRUE
LEFT JOIN LATERAL (
    SELECT * FROM cmms_pm_schedule WHERE asset_id = m.asset_id ORDER BY synced_at DESC LIMIT 1
) s ON TRUE
LEFT JOIN LATERAL (
    SELECT * FROM cmms_work_order_summary WHERE asset_id = m.asset_id ORDER BY synced_at DESC LIMIT 1
) w ON TRUE
WHERE m.data_source IN ('CMMS','IMPORT') OR m.traceid IS NOT NULL OR m.schedule_count_configured > 0;

CREATE OR REPLACE VIEW v_cmms_pm_overdue AS
SELECT
    m.asset_id,
    m.traceid,
    m.customer_name,
    m.location_name,
    m.building_name,
    m.criticality,
    m.in_charge_person,
    s.maintenance_name,
    s.next_schedule_date,
    s.schedule_reference_id,
    EXTRACT(DAY FROM (NOW() - s.next_schedule_date))::INTEGER AS overdue_days,
    CASE
        WHEN s.next_schedule_date < NOW()                       THEN 'OVERDUE'
        WHEN s.next_schedule_date < NOW() + INTERVAL '7 days'  THEN 'DUE_SOON'
        ELSE 'UPCOMING'
    END AS pm_urgency
FROM ic3_asset_master m
JOIN cmms_pm_schedule s ON s.asset_id = m.asset_id
WHERE s.next_schedule_date < NOW() + INTERVAL '7 days'
  AND m.asset_status = 'Active'
ORDER BY s.next_schedule_date ASC;

CREATE OR REPLACE VIEW v_ic3_scada_linked AS
SELECT
    a.asset_id                  AS scada_asset_id,
    a.domain_code,
    a.site_id,
    a.dma_id,
    a.last_seen,
    a.last_quality,
    a.last_alarm,
    a.total_records,
    m.asset_id                  AS ic3_asset_id,
    m.traceid,
    m.customer_name,
    m.asset_category,
    m.asset_sub_category,
    m.criticality,
    m.iot_device_mapped,
    m.asset_status,
    CASE WHEN m.asset_id IS NULL THEN FALSE ELSE TRUE END AS is_master_linked
FROM assets a
LEFT JOIN ic3_asset_master m ON m.asset_id = a.asset_id;

CREATE OR REPLACE VIEW v_cmms_data_quality AS
SELECT
    asset_id,
    traceid,
    asset_name,
    customer_name,
    asset_category,
    CASE WHEN iot_device_mapped = 0     THEN 1 ELSE 0 END AS gap_no_iot_link,
    CASE WHEN install_date IS NULL      THEN 1 ELSE 0 END AS gap_no_install_date,
    CASE WHEN latitude IS NULL          THEN 1 ELSE 0 END AS gap_no_gps,
    CASE WHEN contract_name IS NULL     THEN 1 ELSE 0 END AS gap_no_contract,
    CASE WHEN purchase_date IS NULL     THEN 1 ELSE 0 END AS gap_no_purchase_date,
    CASE WHEN purchase_value IS NULL    THEN 1 ELSE 0 END AS gap_no_purchase_value,
    CASE WHEN design_life_years IS NULL THEN 1 ELSE 0 END AS gap_no_lifespan,
    CASE WHEN depreciation_method IS NULL THEN 1 ELSE 0 END AS gap_no_depreciation,
    CASE WHEN manufacturer IS NULL      THEN 1 ELSE 0 END AS gap_no_vendor,
    ROUND((
        CASE WHEN iot_device_mapped <> 0    THEN 1 ELSE 0 END +
        CASE WHEN install_date IS NOT NULL  THEN 1 ELSE 0 END +
        CASE WHEN latitude IS NOT NULL      THEN 1 ELSE 0 END +
        CASE WHEN contract_name IS NOT NULL THEN 1 ELSE 0 END +
        CASE WHEN purchase_date IS NOT NULL THEN 1 ELSE 0 END +
        CASE WHEN purchase_value IS NOT NULL THEN 1 ELSE 0 END +
        CASE WHEN design_life_years IS NOT NULL THEN 1 ELSE 0 END +
        CASE WHEN depreciation_method IS NOT NULL THEN 1 ELSE 0 END +
        CASE WHEN manufacturer IS NOT NULL  THEN 1 ELSE 0 END
    ) * 100.0 / 9.0, 1) AS data_completeness_pct,
    last_synced_at
FROM ic3_asset_master
WHERE asset_status = 'Active';
`,
	},
	{
		Version:     8,
		Description: "CMMS normalized schema v1 — 26 relational tables (tbl_country → tbl_monitoring)",
		SQL: `
-- ═══════════════════════════════════════════════════════════════════════════════
-- LOCATION MASTER  (01–09)
-- ═══════════════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS tbl_country (
    country_id    SERIAL PRIMARY KEY,
    country_code  VARCHAR(3)  NOT NULL UNIQUE,
    country_name  VARCHAR(100) NOT NULL,
    status        SMALLINT    NOT NULL DEFAULT 1,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS tbl_state (
    state_id    SERIAL PRIMARY KEY,
    country_id  INT  NOT NULL REFERENCES tbl_country(country_id),
    state_code  VARCHAR(10)  NOT NULL,
    state_name  VARCHAR(100) NOT NULL,
    status      SMALLINT NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS tbl_city (
    city_id     SERIAL PRIMARY KEY,
    state_id    INT NOT NULL REFERENCES tbl_state(state_id),
    city_name   VARCHAR(100) NOT NULL,
    city_code   VARCHAR(20),
    latitude    DECIMAL(10,7),
    longitude   DECIMAL(10,7),
    status      SMALLINT NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS tbl_dma (
    dma_id        SERIAL PRIMARY KEY,
    city_id       INT NOT NULL REFERENCES tbl_city(city_id),
    dma_code      VARCHAR(30) NOT NULL UNIQUE,
    dma_name      VARCHAR(150) NOT NULL,
    dma_type      VARCHAR(50),
    area_sqkm     DECIMAL(10,3),
    population    INT,
    status        SMALLINT NOT NULL DEFAULT 1,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS tbl_site (
    site_id           SERIAL PRIMARY KEY,
    dma_id            INT NOT NULL REFERENCES tbl_dma(dma_id),
    city_id           INT NOT NULL REFERENCES tbl_city(city_id),
    customer_id       INT,
    site_code         VARCHAR(30) NOT NULL UNIQUE,
    site_name         VARCHAR(200) NOT NULL,
    site_type         VARCHAR(30) NOT NULL DEFAULT 'Other',
    om_package_code   VARCHAR(50),
    address_line1     VARCHAR(200),
    postal_code       VARCHAR(15),
    site_latitude     DECIMAL(10,7),
    site_longitude    DECIMAL(10,7),
    nearest_landmark  VARCHAR(200),
    status            SMALLINT NOT NULL DEFAULT 1,
    created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at        TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS tbl_zone (
    zone_id             SERIAL PRIMARY KEY,
    site_id             INT NOT NULL REFERENCES tbl_site(site_id),
    zone_code           VARCHAR(30),
    zone_name           VARCHAR(150) NOT NULL,
    zone_type           VARCHAR(50),
    complaint_zone_id   VARCHAR(30),
    ic3_map_label       VARCHAR(150),
    zone_lat            DECIMAL(10,7),
    zone_lon            DECIMAL(10,7),
    status              SMALLINT NOT NULL DEFAULT 1
);
CREATE INDEX IF NOT EXISTS idx_tbl_zone_site ON tbl_zone(site_id);
CREATE INDEX IF NOT EXISTS idx_tbl_zone_name ON tbl_zone(zone_name);

CREATE TABLE IF NOT EXISTS tbl_building (
    building_id    SERIAL PRIMARY KEY,
    zone_id        INT NOT NULL REFERENCES tbl_zone(zone_id),
    building_code  VARCHAR(30),
    building_name  VARCHAR(150) NOT NULL,
    building_type  VARCHAR(50),
    status         SMALLINT NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS tbl_floor (
    floor_id      SERIAL PRIMARY KEY,
    building_id   INT NOT NULL REFERENCES tbl_building(building_id),
    floor_code    VARCHAR(20),
    floor_name    VARCHAR(100) NOT NULL,
    floor_level   INT DEFAULT 0,
    floor_map_url VARCHAR(500),
    status        SMALLINT NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS tbl_department (
    department_id    SERIAL PRIMARY KEY,
    floor_id         INT NOT NULL REFERENCES tbl_floor(floor_id),
    department_code  VARCHAR(30),
    department_name  VARCHAR(150) NOT NULL,
    status           SMALLINT NOT NULL DEFAULT 1
);

-- ═══════════════════════════════════════════════════════════════════════════════
-- ASSET MASTER  (10–15)
-- ═══════════════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS tbl_asset_group (
    group_id    SERIAL PRIMARY KEY,
    group_code  VARCHAR(30) NOT NULL UNIQUE,
    group_name  VARCHAR(150) NOT NULL,
    description TEXT,
    status      SMALLINT NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS tbl_asset_category (
    cat_id      SERIAL PRIMARY KEY,
    group_id    INT NOT NULL REFERENCES tbl_asset_group(group_id),
    cat_code    VARCHAR(30) NOT NULL UNIQUE,
    cat_name    VARCHAR(100) NOT NULL,
    cat_status  SMALLINT NOT NULL DEFAULT 1
);
CREATE INDEX IF NOT EXISTS idx_tbl_cat_name ON tbl_asset_category(cat_name);

CREATE TABLE IF NOT EXISTS tbl_asset_subcategory (
    subcategory_id     SERIAL PRIMARY KEY,
    cat_id             INT NOT NULL REFERENCES tbl_asset_category(cat_id),
    subcategory_code   VARCHAR(30) NOT NULL,
    subcategory_name   VARCHAR(150) NOT NULL,
    subcategory_status SMALLINT NOT NULL DEFAULT 1,
    UNIQUE(cat_id, subcategory_code)
);
CREATE INDEX IF NOT EXISTS idx_tbl_subcat_name ON tbl_asset_subcategory(subcategory_name);

CREATE TABLE IF NOT EXISTS tbl_asset_type (
    asset_type_id  SERIAL PRIMARY KEY,
    subcategory_id INT NOT NULL REFERENCES tbl_asset_subcategory(subcategory_id),
    type_code      VARCHAR(30) NOT NULL,
    type_name      VARCHAR(200) NOT NULL,
    is_movable     SMALLINT NOT NULL DEFAULT 0,
    is_measuring   SMALLINT NOT NULL DEFAULT 0,
    status         SMALLINT NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS tbl_manufacturer (
    manufacturer_id      SERIAL PRIMARY KEY,
    manufacturer_code    VARCHAR(30) NOT NULL UNIQUE,
    manufacturer_name    VARCHAR(200) NOT NULL,
    manufacturer_aliases VARCHAR(500),
    country_id           INT REFERENCES tbl_country(country_id),
    manufacturer_status  SMALLINT NOT NULL DEFAULT 1
);
CREATE INDEX IF NOT EXISTS idx_tbl_mfr_name ON tbl_manufacturer(manufacturer_name);

CREATE TABLE IF NOT EXISTS tbl_equipment_model (
    model_id                    SERIAL PRIMARY KEY,
    asset_type_id               INT NOT NULL REFERENCES tbl_asset_type(asset_type_id),
    manufacturer_id             INT NOT NULL REFERENCES tbl_manufacturer(manufacturer_id),
    model_code                  VARCHAR(50) NOT NULL,
    model_name                  VARCHAR(200) NOT NULL,
    equipment_description       TEXT,
    capacity_rating             VARCHAR(100),
    basic_warranty_duration     INT,
    basic_warranty_period       VARCHAR(20),
    is_measuring_equipment      SMALLINT NOT NULL DEFAULT 0,
    model_status                SMALLINT NOT NULL DEFAULT 1
);

-- ═══════════════════════════════════════════════════════════════════════════════
-- CUSTOMER MASTER  (16)
-- ═══════════════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS tbl_customer (
    customer_id         SERIAL PRIMARY KEY,
    customer_code       VARCHAR(30) NOT NULL UNIQUE,
    customer_name       VARCHAR(200) NOT NULL,
    om_package_code     VARCHAR(50),
    contact_person      VARCHAR(150),
    contact_number      VARCHAR(30),
    contact_email       VARCHAR(150),
    city_id             INT REFERENCES tbl_city(city_id),
    customer_latitude   DECIMAL(10,7),
    customer_longitude  DECIMAL(10,7),
    customer_status     SMALLINT NOT NULL DEFAULT 1,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ═══════════════════════════════════════════════════════════════════════════════
-- CORE ASSET  (17–18)
-- ═══════════════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS tbl_asset (
    id                    SERIAL PRIMARY KEY,
    traceid               INT UNIQUE,
    serialnumber          VARCHAR(100) NOT NULL,
    asset_reference_number VARCHAR(100),
    barcode               VARCHAR(100),
    qrcode                VARCHAR(100),
    ble_id                VARCHAR(100),
    rfidcode              VARCHAR(100),
    model_id              INT REFERENCES tbl_equipment_model(model_id),
    customer_id           INT REFERENCES tbl_customer(customer_id),
    department_id         INT REFERENCES tbl_department(department_id),
    fk_company_id         INT NOT NULL DEFAULT 1,
    criticality           VARCHAR(20) NOT NULL DEFAULT 'Non-Critical',
    criticality_type      SMALLINT,
    asset_group_id        INT REFERENCES tbl_asset_group(group_id),
    installation_date     DATE,
    year_of_manufacturing INT,
    purchase_date         DATE,
    purchase_value        DECIMAL(15,2),
    po_number             VARCHAR(100),
    status                SMALLINT NOT NULL DEFAULT 1,
    asset_live_status     SMALLINT NOT NULL DEFAULT 1,
    is_main_asset         SMALLINT NOT NULL DEFAULT 1,
    iot_device_mapped     SMALLINT NOT NULL DEFAULT 0,
    approval_required     SMALLINT NOT NULL DEFAULT 0,
    workorder_required    SMALLINT NOT NULL DEFAULT 0,
    schedule_configured   SMALLINT,
    in_charge_person      VARCHAR(150),
    asset_created_date    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    -- denormalized for convenience (from ic3_asset_master)
    asset_name            VARCHAR(200),
    asset_category        VARCHAR(100),
    asset_sub_category    VARCHAR(150),
    manufacturer_name     VARCHAR(200),
    location_name         VARCHAR(200),
    building_name         VARCHAR(200),
    customer_name         VARCHAR(200),
    report_asset_id       VARCHAR(200) UNIQUE,
    created_at            TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at            TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_tbl_asset_customer  ON tbl_asset(customer_id);
CREATE INDEX IF NOT EXISTS idx_tbl_asset_dept      ON tbl_asset(department_id);
CREATE INDEX IF NOT EXISTS idx_tbl_asset_serial    ON tbl_asset(serialnumber);
CREATE INDEX IF NOT EXISTS idx_tbl_asset_traceid   ON tbl_asset(traceid) WHERE traceid IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_tbl_asset_cat       ON tbl_asset(asset_category, asset_sub_category);
CREATE INDEX IF NOT EXISTS idx_tbl_asset_location  ON tbl_asset(location_name);

CREATE TABLE IF NOT EXISTS tbl_asset_location (
    asset_location_id  SERIAL PRIMARY KEY,
    asset_id           INT NOT NULL REFERENCES tbl_asset(id) ON DELETE CASCADE,
    department_id      INT REFERENCES tbl_department(department_id),
    floor_id           INT REFERENCES tbl_floor(floor_id),
    building_id        INT REFERENCES tbl_building(building_id),
    zone_id            INT REFERENCES tbl_zone(zone_id),
    site_id            INT REFERENCES tbl_site(site_id),
    location_description VARCHAR(300),
    latitude           DECIMAL(10,7),
    longitude          DECIMAL(10,7),
    altitude_m         DECIMAL(8,3),
    gps_accuracy_m     DECIMAL(6,2),
    coordinate_system  VARCHAR(20) DEFAULT 'WGS84',
    tracking_source    VARCHAR(30) DEFAULT 'Manual',
    gis_map_layer      VARCHAR(20) DEFAULT 'outdoor',
    UNIQUE(asset_id)
);
CREATE INDEX IF NOT EXISTS idx_tbl_aloc_asset ON tbl_asset_location(asset_id);
CREATE INDEX IF NOT EXISTS idx_tbl_aloc_zone  ON tbl_asset_location(zone_id);
CREATE INDEX IF NOT EXISTS idx_tbl_aloc_site  ON tbl_asset_location(site_id);

-- ═══════════════════════════════════════════════════════════════════════════════
-- CONTRACT  (19)
-- ═══════════════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS tbl_contract (
    contract_id          SERIAL PRIMARY KEY,
    asset_id             INT NOT NULL REFERENCES tbl_asset(id),
    customer_id          INT NOT NULL REFERENCES tbl_customer(customer_id),
    contract_name        VARCHAR(200),
    contract_number      VARCHAR(100),
    contract_type        VARCHAR(50),
    service_provider_name VARCHAR(200),
    start_date           DATE,
    end_date             DATE,
    contract_status      SMALLINT NOT NULL DEFAULT 1,
    created_at           TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_tbl_contract_asset ON tbl_contract(asset_id);

-- ═══════════════════════════════════════════════════════════════════════════════
-- IoT / SCADA  (20–21)
-- ═══════════════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS tbl_iot_device (
    device_id            SERIAL PRIMARY KEY,
    device_reference_id  VARCHAR(100) NOT NULL UNIQUE,
    device_ip            VARCHAR(45),
    device_mac           VARCHAR(20),
    rtu_device_id        VARCHAR(100),
    protocol             VARCHAR(30),
    signal_strength      DECIMAL(5,2),
    last_tracking_time   TIMESTAMPTZ,
    status               SMALLINT NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS tbl_scada_tag (
    tag_id               SERIAL PRIMARY KEY,
    asset_id             INT NOT NULL REFERENCES tbl_asset(id),
    device_id            INT REFERENCES tbl_iot_device(device_id),
    scada_tag_id         VARCHAR(100) NOT NULL,
    mqtt_topic           VARCHAR(300),
    ic3_tile_id          VARCHAR(100),
    ic3_section          VARCHAR(50),
    ic3_alert_priority   VARCHAR(5),
    api_permission       VARCHAR(10) DEFAULT 'Read',
    data_quality         VARCHAR(20) DEFAULT 'Good',
    om_agency            VARCHAR(100),
    status               SMALLINT NOT NULL DEFAULT 1,
    UNIQUE(asset_id, scada_tag_id)
);
CREATE INDEX IF NOT EXISTS idx_tbl_tag_asset ON tbl_scada_tag(asset_id);

-- ═══════════════════════════════════════════════════════════════════════════════
-- GIS SPATIAL  (22)
-- ═══════════════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS tbl_gis_point (
    gis_id             SERIAL PRIMARY KEY,
    asset_location_id  INT REFERENCES tbl_asset_location(asset_location_id),
    asset_id           INT NOT NULL REFERENCES tbl_asset(id),
    plus_code          VARCHAR(30),
    map_provider       VARCHAR(30) DEFAULT 'Google Maps',
    external_map_link  VARCHAR(500),
    geofence_name      VARCHAR(150),
    created_at         TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(asset_id)
);
CREATE INDEX IF NOT EXISTS idx_tbl_gis_asset ON tbl_gis_point(asset_id);

-- ═══════════════════════════════════════════════════════════════════════════════
-- TRANSACTIONAL  (23–26)
-- ═══════════════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS tbl_performance (
    perf_id                      SERIAL PRIMARY KEY,
    asset_id                     INT NOT NULL REFERENCES tbl_asset(id),
    period_from                  DATE NOT NULL,
    period_to                    DATE NOT NULL,
    total_ticket                 INT,
    mttr                         DECIMAL(10,2),
    mtbf                         DECIMAL(10,2),
    availability                 DECIMAL(5,2),
    total_ticket_breakdown_hrs   DECIMAL(10,2),
    total_actual_hrs             VARCHAR(20),
    created_at                   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_tbl_perf_asset ON tbl_performance(asset_id);

CREATE TABLE IF NOT EXISTS tbl_wo_transactional (
    wo_trans_id         SERIAL PRIMARY KEY,
    asset_id            INT NOT NULL REFERENCES tbl_asset(id),
    summary_type        VARCHAR(20) NOT NULL DEFAULT 'upto',
    work_estimate_count INT,
    work_estimate_cost  DECIMAL(15,2),
    schedule_count      INT,
    schedule_cost       DECIMAL(15,2),
    ticket_count        INT,
    ticket_cost         DECIMAL(15,2),
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_tbl_wo_asset ON tbl_wo_transactional(asset_id);

CREATE TABLE IF NOT EXISTS tbl_pm_schedule (
    schedule_id          SERIAL PRIMARY KEY,
    asset_id             INT NOT NULL REFERENCES tbl_asset(id),
    schedule_name        VARCHAR(200) NOT NULL,
    schedule_type        VARCHAR(50) NOT NULL DEFAULT 'Preventive',
    scheduled_date       DATE NOT NULL,
    completion_date      DATE,
    schedule_status      VARCHAR(30) NOT NULL DEFAULT 'Scheduled',
    assigned_technician  VARCHAR(150),
    estimated_duration   DECIMAL(6,2),
    actual_duration      DECIMAL(6,2),
    schedule_cost        DECIMAL(15,2),
    next_schedule_date   DATE,
    created_at           TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_tbl_pm_asset ON tbl_pm_schedule(asset_id);
CREATE INDEX IF NOT EXISTS idx_tbl_pm_next  ON tbl_pm_schedule(next_schedule_date) WHERE next_schedule_date IS NOT NULL;

CREATE TABLE IF NOT EXISTS tbl_monitoring (
    monitoring_id  BIGSERIAL PRIMARY KEY,
    asset_id       INT NOT NULL REFERENCES tbl_asset(id),
    tag_id         INT REFERENCES tbl_scada_tag(tag_id),
    parameter      VARCHAR(100) NOT NULL,
    value          DECIMAL(15,4),
    unit           VARCHAR(30),
    timestamp      TIMESTAMPTZ NOT NULL,
    threshold_high DECIMAL(15,4),
    threshold_low  DECIMAL(15,4),
    alarm_status   VARCHAR(20) DEFAULT 'Normal',
    data_quality   VARCHAR(20) DEFAULT 'Good'
);
CREATE INDEX IF NOT EXISTS idx_tbl_mon_asset ON tbl_monitoring(asset_id, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_tbl_mon_alarm ON tbl_monitoring(alarm_status) WHERE alarm_status <> 'Normal';

-- ═══════════════════════════════════════════════════════════════════════════════
-- GIS VIEW — for map display (fallback chain: asset GPS → zone GPS → site GPS)
-- ═══════════════════════════════════════════════════════════════════════════════
CREATE OR REPLACE VIEW v_gis_assets AS
SELECT
    a.id                                          AS asset_id,
    a.traceid,
    a.serialnumber,
    a.report_asset_id,
    a.asset_name,
    a.asset_category,
    a.asset_sub_category,
    a.manufacturer_name,
    a.criticality,
    a.asset_live_status,
    a.iot_device_mapped,
    a.location_name,
    a.building_name,
    a.customer_name,
    a.status                                       AS asset_status,
    al.asset_location_id,
    -- GPS fallback chain: asset-level → zone-level → site-level
    COALESCE(al.latitude,  z.zone_lat,  s.site_latitude)   AS gis_lat,
    COALESCE(al.longitude, z.zone_lon,  s.site_longitude)  AS gis_lng,
    al.latitude                                    AS asset_lat,
    al.longitude                                   AS asset_lng,
    z.zone_name,
    z.zone_lat,
    z.zone_lon,
    z.ic3_map_label,
    s.site_name,
    s.site_type,
    s.site_latitude,
    s.site_longitude,
    c.customer_name                                AS customer_package,
    c.om_package_code
FROM tbl_asset a
LEFT JOIN tbl_asset_location al ON al.asset_id = a.id
LEFT JOIN tbl_zone            z  ON z.zone_id  = al.zone_id
LEFT JOIN tbl_site            s  ON s.site_id  = al.site_id
LEFT JOIN tbl_customer        c  ON c.customer_id = a.customer_id
WHERE a.status = 1;
`,
	},
	{
		Version:     9,
		Description: "Seed reference data + populate tbl_asset / tbl_asset_location from ic3_asset_master",
		SQL: `
-- ═══════════════════════════════════════════════════════════════════════════════
-- REFERENCE DATA — India → Delhi → DJB sites
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT INTO tbl_country (country_code, country_name, status) VALUES ('IN', 'India', 1)
    ON CONFLICT (country_code) DO NOTHING;

INSERT INTO tbl_state (country_id, state_code, state_name, status)
    SELECT country_id, 'DL', 'Delhi', 1 FROM tbl_country WHERE country_code='IN'
    ON CONFLICT DO NOTHING;

INSERT INTO tbl_city (state_id, city_name, city_code, latitude, longitude, status)
    SELECT state_id, 'Delhi', 'DEL', 28.6139, 77.2090, 1 FROM tbl_state WHERE state_code='DL'
    ON CONFLICT DO NOTHING;

-- DMA zones
INSERT INTO tbl_dma (city_id, dma_code, dma_name, dma_type, status)
SELECT city_id, v.code, v.name, v.dtype, 1
FROM tbl_city, (VALUES
    ('NARAINA-DC',  'Naraina D.Cantt Zone',          'UGR_Zone'),
    ('SHASTRI-NGR', 'Shastri Nagar Zone',             'UGR_Zone'),
    ('PATEL-RD',    'Patel Road BPS Zone',            'BPS_Zone'),
    ('CHAND-WTP',   'Chandrawal WTP Zone',            'WTP'),
    ('WAZ-WTP',     'Wazirabad WTP Zone',             'WTP'),
    ('BHIKAJI-BPS', 'Bhikaji Cama Palace BPS Zone',  'BPS_Zone'),
    ('JANTA-PK',    'Jantapark UGR Zone',             'UGR_Zone'),
    ('THANSINGH',   'Thansingh Nagar UGR Zone',       'UGR_Zone'),
    ('RAJ-NGR',     'New Rajender Nagar Zone',        'UGR_Zone')
) AS v(code,name,dtype)
WHERE city_code = 'DEL'
ON CONFLICT (dma_code) DO NOTHING;

-- Customers / packages
INSERT INTO tbl_customer (customer_code, customer_name, om_package_code, city_id, customer_status)
SELECT v.code, v.name, v.pkg, city_id, 1
FROM tbl_city, (VALUES
    ('DJB-KHYALA',  'DJB Khyala',     'DJB_Khyala'),
    ('DJB-PKG02',   'DJB_Package 02', 'DJB_Package 02'),
    ('DJB-PKG03',   'DJB_Package 03', 'DJB_Package 03'),
    ('DJB-PKG04',   'DJB_Package 04', 'DJB_Package 04')
) AS v(code,name,pkg)
WHERE city_code = 'DEL'
ON CONFLICT (customer_code) DO NOTHING;

-- Sites (with real GPS coordinates for GIS map)
INSERT INTO tbl_site (dma_id, city_id, customer_id, site_code, site_name, site_type,
                      om_package_code, site_latitude, site_longitude, status)
SELECT d.dma_id, ci.city_id, cu.customer_id, v.code, v.name, v.stype, v.pkg,
       v.lat::DECIMAL(10,7), v.lng::DECIMAL(10,7), 1
FROM tbl_city ci
JOIN tbl_dma d ON d.city_id = ci.city_id
JOIN tbl_customer cu ON cu.city_id = ci.city_id
, (VALUES
    ('NARAINA-DC',  'NARAINA-UGR',  'Naraina D.Cantt UGR',        'UGR', 'DJB_Khyala',    '28.5893','77.1383'),
    ('THANSINGH',   'THANS-UGR',    'Thansingh Nagar UGR',         'UGR', 'DJB_Khyala',    '28.6500','77.1890'),
    ('PATEL-RD',    'PATEL-BPS',    'Patel Road Online BPS',       'BPS', 'DJB_Package 02','28.6558','77.1686'),
    ('SHASTRI-NGR', 'SHASTRI-UGR',  'Shastri Nagar UGR',           'UGR', 'DJB_Package 02','28.6749','77.1781'),
    ('RAJ-NGR',     'RAJNAGAR-UGR', 'New Rajender Nagar R Block',  'UGR', 'DJB_Package 02','28.6499','77.1849'),
    ('JANTA-PK',    'JANTA-UGR',    'Jantapark UGR',               'UGR', 'DJB_Package 02','28.6407','77.1686'),
    ('CHAND-WTP',   'CHAND-WTP',    'DJB Chandrawal WTP',          'WTP', 'DJB_Package 03','28.6929','77.2195'),
    ('WAZ-WTP',     'WAZ-WTP',      'DJB Wazirabad WTP',           'WTP', 'DJB_Package 03','28.7474','77.2209'),
    ('BHIKAJI-BPS', 'BHIKAJI-BPS',  'Bhikaji Cama Palace BPS',     'BPS', 'DJB_Package 04','28.5699','77.1876')
) AS v(dma_code, code, name, stype, pkg, lat, lng)
WHERE ci.city_code = 'DEL'
  AND d.dma_code   = v.dma_code
  AND cu.om_package_code = v.pkg
ON CONFLICT (site_code) DO NOTHING;

-- Zones (one zone per site location name — GIS pin points)
INSERT INTO tbl_zone (site_id, zone_code, zone_name, zone_type, ic3_map_label, zone_lat, zone_lon, status)
SELECT s.site_id, v.zcode, v.zname, v.ztype, v.label,
       v.lat::DECIMAL(10,7), v.lng::DECIMAL(10,7), 1
FROM tbl_site s
, (VALUES
    ('NARAINA-UGR',  'NARAINA-DC-Z',   'Naraina D.Cantt UGR_Zone',        'UGR_Zone', 'Naraina D.Cantt',      '28.5893','77.1383'),
    ('THANS-UGR',    'THANS-NGR-Z',    'Thansingh Nagar UGR_Zone',        'UGR_Zone', 'Thansingh Nagar',      '28.6500','77.1890'),
    ('PATEL-BPS',    'PATEL-BPS-Z',    'Patel Road Online BPS_Zone',      'BPS_Zone', 'Patel Road BPS',       '28.6558','77.1686'),
    ('SHASTRI-UGR',  'SHASTRI-NGR-Z',  'Shastri Nagar UGR_Zone',          'UGR_Zone', 'Shastri Nagar',        '28.6749','77.1781'),
    ('RAJNAGAR-UGR', 'RAJNAGAR-Z',     'New Rajender Nagar R Block_Zone', 'UGR_Zone', 'New Rajender Nagar',   '28.6499','77.1849'),
    ('JANTA-UGR',    'JANTA-PK-Z',     'Jantapark UGR_Zone',              'UGR_Zone', 'Jantapark UGR',        '28.6407','77.1686'),
    ('CHAND-WTP',    'CHAND-WTP-Z',    'CHANDRAWAL WTP',                  'WTP',      'Chandrawal WTP',       '28.6929','77.2195'),
    ('WAZ-WTP',      'WAZ-WTP-Z',      'WAZIRABAD WTP',                   'WTP',      'Wazirabad WTP',        '28.7474','77.2209'),
    ('BHIKAJI-BPS',  'BHIKAJI-BPS-Z',  'BHIKAJI CAMA PALACE BPS',        'BPS_Zone', 'Bhikaji Cama BPS',     '28.5699','77.1876')
) AS v(site_code, zcode, zname, ztype, label, lat, lng)
WHERE s.site_code = v.site_code
ON CONFLICT DO NOTHING;

-- Default buildings / floors / departments per zone (minimum structure for FK chain)
INSERT INTO tbl_building (zone_id, building_code, building_name, building_type, status)
SELECT z.zone_id, z.zone_code || '-BLD', z.zone_name, z.zone_type, 1
FROM tbl_zone z
ON CONFLICT DO NOTHING;

INSERT INTO tbl_floor (building_id, floor_code, floor_name, floor_level, status)
SELECT b.building_id, b.building_code || '-G', 'Ground Floor', 0, 1
FROM tbl_building b
ON CONFLICT DO NOTHING;

INSERT INTO tbl_department (floor_id, department_code, department_name, status)
SELECT f.floor_id, f.floor_code || '-OPS', 'Operations', 1
FROM tbl_floor f
ON CONFLICT DO NOTHING;

-- Asset groups
INSERT INTO tbl_asset_group (group_code, group_name, status) VALUES
    ('ELEC',     'Electrical Equipment',    1),
    ('MECH',     'Mechanical Equipment',    1),
    ('INST',     'Instrumentation',         1),
    ('CIVIL',    'Civil Structure',         1),
    ('Realtime',    'Realtime / Control',         1),
    ('GENERAL',  'General',                 1)
ON CONFLICT (group_code) DO NOTHING;

-- Asset categories (normalised from report)
INSERT INTO tbl_asset_category (group_id, cat_code, cat_name, cat_status)
SELECT g.group_id, v.code, v.name, 1
FROM tbl_asset_group g, (VALUES
    ('ELEC',  'ELEC',  'Electrical'),
    ('MECH',  'MECH',  'Mechanical'),
    ('INST',  'INST',  'Instrumentation'),
    ('CIVIL', 'CIVIL', 'Civil'),
    ('Realtime', 'Realtime', 'Realtime'),
    ('GENERAL','GEN',  'General')
) AS v(grp, code, name)
WHERE g.group_code = v.grp
ON CONFLICT (cat_code) DO NOTHING;

-- Common manufacturers from the report
INSERT INTO tbl_manufacturer (manufacturer_code, manufacturer_name, manufacturer_status)
SELECT UPPER(LEFT(REGEXP_REPLACE(m.name, '[^A-Za-z0-9]', '', 'g'), 29)), m.name, 1
FROM (VALUES
    ('Kirloskar'), ('KBL'), ('Mather+Platte'), ('Advance Engineering Works Pvt Ltd'),
    ('MORRIS'), ('DOMAIN'), ('Cummins'), ('Krohne Marshall'), ('NA'), ('ABB'),
    ('L&T'), ('Siemens'), ('Grundfos'), ('ITT'), ('Sulzer'), ('Wilo'),
    ('Danfoss'), ('Atlas Copco'), ('Prominent'), ('JWIL'), ('Unknown')
) AS m(name)
ON CONFLICT (manufacturer_code) DO NOTHING;

-- Default subcategory (needed as FK for tbl_asset_type)
INSERT INTO tbl_asset_subcategory (cat_id, subcategory_code, subcategory_name, subcategory_status)
SELECT c.cat_id, v.code, v.name, 1
FROM tbl_asset_category c, (VALUES
    ('ELEC', 'LT-PANEL',   'LT Panel'),
    ('ELEC', 'HT-PANEL',   'HT Panel'),
    ('ELEC', 'MOTOR',      'LT Motors'),
    ('ELEC', 'DG-PANEL',   'DG Panel'),
    ('ELEC', 'VFD',        'VFD'),
    ('ELEC', 'ACB',        'Air Circuit Breaker'),
    ('MECH', 'PUMP-BOOST', 'Booster Pump'),
    ('MECH', 'PUMP-DW',    'Dewatering Pump'),
    ('MECH', 'NRV',        'NRV'),
    ('MECH', 'SV',         'Sluice Valve'),
    ('MECH', 'PUMP-TUBE',  'Tubewell Pump'),
    ('INST', 'FLOW-METER', 'Flow Meter'),
    ('INST', 'LEVEL-SEN',  'Level Sensor'),
    ('INST', 'PRESS-TX',   'Pressure Transmitter'),
    ('INST', 'PRESS-GAUGE','Pressure Gauge'),
    ('CIVIL','CIVIL-GEN',  'Civil General'),
    ('Realtime','PLC',        'PLC Panel'),
    ('GEN',  'GEN-ASSET',  'General Asset')
) AS v(cat, code, name)
WHERE c.cat_code = v.cat
ON CONFLICT (cat_id, subcategory_code) DO NOTHING;

-- ═══════════════════════════════════════════════════════════════════════════════
-- POPULATE tbl_asset FROM ic3_asset_master (2957 records)
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT INTO tbl_asset (
    traceid, serialnumber, asset_reference_number, barcode, qrcode,
    customer_id, fk_company_id, criticality, criticality_type,
    status, asset_live_status, is_main_asset, iot_device_mapped,
    schedule_configured, in_charge_person,
    asset_name, asset_category, asset_sub_category, manufacturer_name,
    location_name, building_name, customer_name, report_asset_id,
    purchase_value, po_number, installation_date, year_of_manufacturing,
    asset_created_date, created_at, updated_at
)
SELECT
    i.traceid,
    COALESCE(i.serial_no, i.asset_id, i.asset_name, 'UNKNOWN'),
    i.asset_id,
    i.barcode,
    i.qrcode,
    cu.customer_id,
    1,
    COALESCE(i.criticality, 'Non-Critical'),
    i.criticality_type,
    CASE WHEN i.asset_status = 'Active' THEN 1 ELSE 0 END,
    COALESCE(i.asset_live_status, 1),
    COALESCE(i.is_main_asset::INT, 1),
    COALESCE(i.iot_device_mapped, 0),
    i.schedule_configured::SMALLINT,
    i.in_charge_person,
    i.asset_name,
    i.asset_category,
    i.asset_sub_category,
    i.manufacturer,
    i.location_name,
    i.building_name,
    i.customer_name,
    i.asset_id,
    i.purchase_value,
    i.po_number,
    i.install_date,
    i.year_of_manufacture,
    COALESCE(i.created_at, NOW()),
    NOW(),
    NOW()
FROM ic3_asset_master i
LEFT JOIN tbl_customer cu ON cu.customer_name = i.customer_name
WHERE i.asset_id IS NOT NULL
ON CONFLICT (report_asset_id) DO NOTHING;

-- ═══════════════════════════════════════════════════════════════════════════════
-- POPULATE tbl_asset_location — link each asset to its zone + inherit zone GPS
-- GPS fallback: zone GPS used until real GPS survey data is available
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT INTO tbl_asset_location (
    asset_id, zone_id, site_id, department_id,
    latitude, longitude, tracking_source, gis_map_layer
)
SELECT
    a.id,
    z.zone_id,
    z.site_id,
    d.department_id,
    NULL,   -- individual GPS: NULL until survey conducted (zone GPS used via v_gis_assets view)
    NULL,
    'Zone_Centroid',
    'outdoor'
FROM tbl_asset a
JOIN tbl_zone z ON z.zone_name = a.location_name
LEFT JOIN tbl_building b  ON b.zone_id = z.zone_id
LEFT JOIN tbl_floor fl    ON fl.building_id = b.building_id
LEFT JOIN tbl_department d ON d.floor_id = fl.floor_id
WHERE a.location_name IS NOT NULL
  AND a.location_name <> '-'
  AND NOT EXISTS (
      SELECT 1 FROM tbl_asset_location al WHERE al.asset_id = a.id
  )
ON CONFLICT (asset_id) DO NOTHING;

-- Also handle 'Patel Road Online BPS' → 'Patel Road Online BPS_Zone' (name variant)
UPDATE tbl_asset_location al
SET zone_id = z.zone_id, site_id = z.site_id
FROM tbl_asset a, tbl_zone z
WHERE al.asset_id = a.id
  AND a.location_name = 'Patel Road Online BPS'
  AND z.zone_name = 'Patel Road Online BPS_Zone';
`,
	},
	{
		Version:     9,
		Description: "AI Anomaly Detection tables",
		SQL: `
-- ═══════════════════════════════════════════════════════════════════════════════
-- IC3 ANOMALY DETECTION SCHEMA
-- ═══════════════════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS ic3_anomalies (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  asset_id VARCHAR(100) NOT NULL,
  asset_name VARCHAR(255),
  asset_type VARCHAR(50),
  location_gps POINT,
  location_zone VARCHAR(100),
  location_dma VARCHAR(100),
  detected_at TIMESTAMP NOT NULL DEFAULT NOW(),
  severity DECIMAL(3,1) NOT NULL,
  confidence DECIMAL(5,2) NOT NULL,
  measurement_current DECIMAL(10,3),
  measurement_baseline DECIMAL(10,3),
  measurement_deviation DECIMAL(5,2),
  measurement_unit VARCHAR(20),
  probable_causes JSONB,
  status VARCHAR(50) NOT NULL DEFAULT 'active',
  work_order_id UUID,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_anomalies_asset_id ON ic3_anomalies(asset_id);
CREATE INDEX IF NOT EXISTS idx_anomalies_status ON ic3_anomalies(status);
CREATE INDEX IF NOT EXISTS idx_anomalies_severity ON ic3_anomalies(severity DESC);
CREATE INDEX IF NOT EXISTS idx_anomalies_detected_at ON ic3_anomalies(detected_at DESC);
CREATE INDEX IF NOT EXISTS idx_anomalies_confidence ON ic3_anomalies(confidence DESC);
CREATE INDEX IF NOT EXISTS idx_anomalies_asset_type ON ic3_anomalies(asset_type);

CREATE TABLE IF NOT EXISTS ic3_anomaly_audit_trail (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  anomaly_id UUID NOT NULL,
  action VARCHAR(255) NOT NULL,
  actor_user_id UUID,
  actor_name VARCHAR(255),
  actor_role VARCHAR(50),
  details TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  FOREIGN KEY (anomaly_id) REFERENCES ic3_anomalies(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_audit_trail_anomaly_id ON ic3_anomaly_audit_trail(anomaly_id);
CREATE INDEX IF NOT EXISTS idx_audit_trail_created_at ON ic3_anomaly_audit_trail(created_at DESC);

CREATE TABLE IF NOT EXISTS ic3_alert_rules (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  sensor_type VARCHAR(100) NOT NULL UNIQUE,
  threshold_upper DECIMAL(10,3),
  threshold_lower DECIMAL(10,3),
  percentage_deviation DECIMAL(5,2),
  severity VARCHAR(50) NOT NULL,
  enabled BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  updated_by UUID
);

CREATE INDEX IF NOT EXISTS idx_alert_rules_sensor_type ON ic3_alert_rules(sensor_type);
CREATE INDEX IF NOT EXISTS idx_alert_rules_severity ON ic3_alert_rules(severity);

CREATE TABLE IF NOT EXISTS ic3_sensor_health (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  sensor_id VARCHAR(100) NOT NULL UNIQUE,
  sensor_name VARCHAR(255),
  trust_score DECIMAL(5,2) NOT NULL DEFAULT 100,
  status VARCHAR(50) NOT NULL DEFAULT 'healthy',
  last_calibration TIMESTAMP,
  anomaly_count_7d INT DEFAULT 0,
  false_positive_rate DECIMAL(5,2) DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_sensor_health_trust_score ON ic3_sensor_health(trust_score);
CREATE INDEX IF NOT EXISTS idx_sensor_health_status ON ic3_sensor_health(status);

CREATE TABLE IF NOT EXISTS ic3_anomaly_stats (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  stat_date DATE NOT NULL UNIQUE,
  total_anomalies INT DEFAULT 0,
  active_anomalies INT DEFAULT 0,
  resolved_anomalies INT DEFAULT 0,
  critical_count INT DEFAULT 0,
  high_count INT DEFAULT 0,
  medium_count INT DEFAULT 0,
  low_count INT DEFAULT 0,
  avg_confidence DECIMAL(5,2),
  mttr_minutes DECIMAL(8,2),
  false_pos_rate DECIMAL(5,2),
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_anomaly_stats_date ON ic3_anomaly_stats(stat_date DESC);
`,
	},
	{
		Version:     10,
		Description: "ic3_system_master: add system_parent_id + seed project type hierarchy",
		SQL: `
ALTER TABLE ic3_system_master
ADD COLUMN IF NOT EXISTS system_parent_id VARCHAR(50) REFERENCES ic3_system_master(system_id);

INSERT INTO ic3_system_master (system_id, system_name, system_category, is_active) VALUES
  ('CAT-WST', 'Water Supply & Treatment', 'CATEGORY', true),
  ('CAT-WW',  'Wastewater & Sanitation',  'CATEGORY', true),
  ('CAT-RR',  'Reuse & Resource',         'CATEGORY', true),
  ('CAT-IRR', 'Irrigation & Agri-Water',  'CATEGORY', true),
  ('CAT-CS',  'Cross-scheme',             'CATEGORY', true)
ON CONFLICT (system_id) DO NOTHING;

INSERT INTO ic3_system_master (system_id, system_name, system_category, system_parent_id, is_active) VALUES
  ('SYS-WTP',  'WTP — Water Treatment',        'PROJECT', 'CAT-WST', true),
  ('SYS-DSP',  'DSP — Desalination',           'PROJECT', 'CAT-WST', true),
  ('SYS-BWT',  'Bulk Water Transmission',       'PROJECT', 'CAT-WST', true),
  ('SYS-24P',  '24x7 Pressurised Supply',       'PROJECT', 'CAT-WST', true),
  ('SYS-RWS',  'Rural Water Supply (JJM)',       'PROJECT', 'CAT-WST', true),
  ('SYS-STP',  'STP — Sewage Treatment',        'PROJECT', 'CAT-WW',  true),
  ('SYS-WWTP', 'WWTP — Wastewater',             'PROJECT', 'CAT-WW',  true),
  ('SYS-ETP',  'ETP / CETP — Effluent',         'PROJECT', 'CAT-WW',  true),
  ('SYS-FSM',  'FSM — Septage',                 'PROJECT', 'CAT-WW',  true),
  ('SYS-STD',  'Stormwater & Drainage',          'PROJECT', 'CAT-WW',  true),
  ('SYS-WRR',  'Water Reuse & Recycling',        'PROJECT', 'CAT-RR',  true),
  ('SYS-SRC',  'Source & Catchment',             'PROJECT', 'CAT-RR',  true),
  ('SYS-IRR',  'Irrigation — Rural',             'PROJECT', 'CAT-IRR', true),
  ('SYS-LFT',  'Lift Irrigation',                'PROJECT', 'CAT-IRR', true),
  ('SYS-MDI',  'Micro / Drip Irrigation',        'PROJECT', 'CAT-IRR', true),
  ('SYS-NRW',  'NRW & Smart Metering Programme', 'PROJECT', 'CAT-CS',  true)
ON CONFLICT (system_id) DO NOTHING;
`,
	},
	{
		Version:     11,
		Description: "Create ic3_city table for city master data",
		SQL: `
CREATE TABLE IF NOT EXISTS ic3_city (
    city_id         VARCHAR(50)  PRIMARY KEY,
    city_name       VARCHAR(200) NOT NULL,
    state_code      VARCHAR(10),
    country_code    VARCHAR(10),
    is_active       BOOLEAN      NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_city_active ON ic3_city(is_active);

ALTER TABLE ic3_location_master
ADD COLUMN IF NOT EXISTS city_id VARCHAR(50) REFERENCES ic3_city(city_id);
`,
	},
	{
		Version:     12,
		Description: "Seed Delhi into ic3_city and backfill city_id on all ic3_location_master rows",
		SQL: `
INSERT INTO ic3_city (city_id, city_name, state_code, country_code, created_at)
VALUES ('DEL', 'Delhi', 'DL', 'IN', NOW())
ON CONFLICT (city_id) DO NOTHING;

UPDATE ic3_location_master
SET    city_id    = 'DEL',
       updated_at = NOW()
WHERE  city_id IS NULL;
`,
	},
	{
		Version:     13,
		Description: "Seed domain systems into ic3_system_master",
		SQL: `
DELETE FROM ic3_system_master;

INSERT INTO ic3_system_master (system_id, system_name, system_category, system_type, is_active, created_at, updated_at) VALUES
('24-7', '24/7', 'DOMAIN', 'DOMAIN', TRUE, NOW(), NOW()),
('realtime', 'Realtime', 'DOMAIN', 'DOMAIN', TRUE, NOW(), NOW()),
('water-quality', 'Water Quality', 'DOMAIN', 'DOMAIN', TRUE, NOW(), NOW()),
('wtp', 'WTP', 'DOMAIN', 'DOMAIN', TRUE, NOW(), NOW()),
('wwtp', 'WWTP', 'DOMAIN', 'DOMAIN', TRUE, NOW(), NOW()),
('stp', 'STP', 'DOMAIN', 'DOMAIN', TRUE, NOW(), NOW()),
('gis', 'GIS', 'DOMAIN', 'DOMAIN', TRUE, NOW(), NOW()),
('digital-twin', 'Digital Twin', 'DOMAIN', 'DOMAIN', TRUE, NOW(), NOW()),
('irrigation-agri', 'Irrigation & Agri Water', 'DOMAIN', 'DOMAIN', TRUE, NOW(), NOW()),
('rws', 'RWS', 'DOMAIN', 'DOMAIN', TRUE, NOW(), NOW()),
('gw-sw', 'GW & SW', 'DOMAIN', 'DOMAIN', TRUE, NOW(), NOW()),
('nrw', 'NRW', 'DOMAIN', 'DOMAIN', TRUE, NOW(), NOW());
`,
	},
	{
		Version:     14,
		Description: "Clear and reseed domain systems if needed",
		SQL: `
DELETE FROM ic3_system_master;

INSERT INTO ic3_system_master (system_id, system_name, system_category, system_type, is_active, created_at, updated_at) VALUES
('24-7', '24/7', 'DOMAIN', 'DOMAIN', TRUE, NOW(), NOW()),
('realtime', 'Realtime', 'DOMAIN', 'DOMAIN', TRUE, NOW(), NOW()),
('water-quality', 'Water Quality', 'DOMAIN', 'DOMAIN', TRUE, NOW(), NOW()),
('wtp', 'WTP', 'DOMAIN', 'DOMAIN', TRUE, NOW(), NOW()),
('wwtp', 'WWTP', 'DOMAIN', 'DOMAIN', TRUE, NOW(), NOW()),
('stp', 'STP', 'DOMAIN', 'DOMAIN', TRUE, NOW(), NOW()),
('gis', 'GIS', 'DOMAIN', 'DOMAIN', TRUE, NOW(), NOW()),
('digital-twin', 'Digital Twin', 'DOMAIN', 'DOMAIN', TRUE, NOW(), NOW()),
('irrigation-agri', 'Irrigation & Agri Water', 'DOMAIN', 'DOMAIN', TRUE, NOW(), NOW()),
('rws', 'RWS', 'DOMAIN', 'DOMAIN', TRUE, NOW(), NOW()),
('gw-sw', 'GW & SW', 'DOMAIN', 'DOMAIN', TRUE, NOW(), NOW()),
('nrw', 'NRW', 'DOMAIN', 'DOMAIN', TRUE, NOW(), NOW());
`,
	},
	{
		Version:     15,
		Description: "Force reseed domain systems",
		SQL: `DELETE FROM ic3_system_master;
INSERT INTO ic3_system_master (system_id, system_name, system_category, system_type, is_active, created_at, updated_at) VALUES ('24-7', '24/7', 'DOMAIN', 'DOMAIN', TRUE, NOW(), NOW()), ('realtime', 'Realtime', 'DOMAIN', 'DOMAIN', TRUE, NOW(), NOW()), ('water-quality', 'Water Quality', 'DOMAIN', 'DOMAIN', TRUE, NOW(), NOW()), ('wtp', 'WTP', 'DOMAIN', 'DOMAIN', TRUE, NOW(), NOW()), ('wwtp', 'WWTP', 'DOMAIN', 'DOMAIN', TRUE, NOW(), NOW()), ('stp', 'STP', 'DOMAIN', 'DOMAIN', TRUE, NOW(), NOW()), ('gis', 'GIS', 'DOMAIN', 'DOMAIN', TRUE, NOW(), NOW()), ('digital-twin', 'Digital Twin', 'DOMAIN', 'DOMAIN', TRUE, NOW(), NOW()), ('irrigation-agri', 'Irrigation & Agri Water', 'DOMAIN', 'DOMAIN', TRUE, NOW(), NOW()), ('rws', 'RWS', 'DOMAIN', 'DOMAIN', TRUE, NOW(), NOW()), ('gw-sw', 'GW & SW', 'DOMAIN', 'DOMAIN', TRUE, NOW(), NOW()), ('nrw', 'NRW', 'DOMAIN', 'DOMAIN', TRUE, NOW(), NOW());`,
	},
	{
		Version:     16,
		Description: "IC3 Nav Menu Tables",
		SQL: `
-- 1. Intelligence layers (P1–P6) — global, not per system
CREATE TABLE IF NOT EXISTS ic3_nav_layer (
    id          SERIAL PRIMARY KEY,
    layer_no    SMALLINT NOT NULL CHECK (layer_no BETWEEN 1 AND 6),
    code        VARCHAR(10) NOT NULL UNIQUE,
    label       VARCHAR(100) NOT NULL,
    description TEXT,
    UNIQUE (layer_no)
);

-- Seed the 6 pillars
INSERT INTO ic3_nav_layer (layer_no, code, label, description) VALUES
(1, 'P1', 'Data & Field Intelligence',         'Raw acquisition — assets, IoT/Realtime, field data'),
(2, 'P2', 'Information with Intelligence',     'Contextualised dashboards, metering, water balance'),
(3, 'P3', 'Analytics & Network Intelligence',  'Hydraulics, NRW, network health, SLA analytics'),
(4, 'P4', 'AI Models & Prediction',            'Forecasting, predictive maintenance, anomaly detection'),
(5, 'P5', 'Operations & Automation',           'Scheduling, work orders, alarms, control logic'),
(6, 'P6', 'Citizen, Reporting & Governance',   'Citizen care, statutory reports, admin & trust')
ON CONFLICT (layer_no) DO NOTHING;

-- 2. Menu groups — one system × one layer → N groups
CREATE TABLE IF NOT EXISTS ic3_nav_group (
    id          SERIAL PRIMARY KEY,
    system_id   VARCHAR(50) NOT NULL REFERENCES ic3_system_master(system_id) ON DELETE CASCADE,
    layer_id    INT NOT NULL REFERENCES ic3_nav_layer(id) ON DELETE CASCADE,
    code        VARCHAR(60) NOT NULL,
    label       VARCHAR(120) NOT NULL,
    icon        VARCHAR(60),
    sort_order  INT NOT NULL DEFAULT 0,
    is_active   BOOLEAN NOT NULL DEFAULT TRUE,
    UNIQUE (system_id, layer_id, code)
);

-- 3. Menu items — leaf nodes, each group → N items
CREATE TABLE IF NOT EXISTS ic3_nav_item (
    id          SERIAL PRIMARY KEY,
    group_id    INT NOT NULL REFERENCES ic3_nav_group(id) ON DELETE CASCADE,
    code        VARCHAR(80) NOT NULL,
    label       VARCHAR(120) NOT NULL,
    route       VARCHAR(255),
    icon        VARCHAR(60),
    sort_order  INT NOT NULL DEFAULT 0,
    status      VARCHAR(20) NOT NULL DEFAULT 'live'
                    CHECK (status IN ('live', 'coming_soon', 'beta')),
    is_active   BOOLEAN NOT NULL DEFAULT TRUE,
    UNIQUE (group_id, code)
);

-- 4. Role access — which roles can see / act on each item
CREATE TABLE IF NOT EXISTS ic3_nav_role_access (
    id          SERIAL PRIMARY KEY,
    item_id     INT NOT NULL REFERENCES ic3_nav_item(id) ON DELETE CASCADE,
    role_code   VARCHAR(40) NOT NULL,
    can_view    BOOLEAN NOT NULL DEFAULT TRUE,
    can_act     BOOLEAN NOT NULL DEFAULT FALSE,
    UNIQUE (item_id, role_code)
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_nav_group_system_layer ON ic3_nav_group(system_id, layer_id);
CREATE INDEX IF NOT EXISTS idx_nav_item_group         ON ic3_nav_item(group_id);
CREATE INDEX IF NOT EXISTS idx_nav_role_item          ON ic3_nav_role_access(item_id);
`,
	},
	{
		Version:     17,
		Description: "Seed 24/7 Water Supply nav menu (P1-P3 data)",
		SQL: `
DO $$
DECLARE
    p1 INT; p2 INT; p3 INT; p4 INT; p5 INT; p6 INT;
    g1 INT; g2 INT; g3 INT; g4 INT;
BEGIN
    SELECT id INTO p1 FROM ic3_nav_layer WHERE code='P1';
    SELECT id INTO p2 FROM ic3_nav_layer WHERE code='P2';
    SELECT id INTO p3 FROM ic3_nav_layer WHERE code='P3';
    SELECT id INTO p4 FROM ic3_nav_layer WHERE code='P4';
    SELECT id INTO p5 FROM ic3_nav_layer WHERE code='P5';
    SELECT id INTO p6 FROM ic3_nav_layer WHERE code='P6';

    -- ── P1: Data & Field Intelligence ──────────────────────
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order)
        VALUES ('SYS-001',p1,'acquisition_connectors','Acquisition & Connectors',1)
        ON CONFLICT DO NOTHING RETURNING id INTO g1;
    IF g1 IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g1,'field_instrument_status', 'Field Instrument Status',  '/ic3/24x7/p1/field-status',  1,'live'),
        (g1,'live_telemetry_explorer', 'Live Telemetry Explorer',  '/ic3/24x7/p1/telemetry',     2,'live'),
        (g1,'iot_smart_meter_stream',  'IoT & Smart Meter Stream', '/ic3/24x7/p1/iot-stream',    3,'coming_soon'),
        (g1,'manual_lab_data_entry',   'Manual & Lab Data Entry',  '/ic3/24x7/p1/manual-entry',  4,'live')
        ON CONFLICT DO NOTHING;
    END IF;

    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order)
        VALUES ('SYS-001',p1,'network_assets','Network Assets & Inventory',2)
        ON CONFLICT DO NOTHING RETURNING id INTO g2;
    IF g2 IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g2,'pipe_network_registry',  'Pipe Network Registry',    '/ic3/24x7/p1/pipe-registry', 1,'coming_soon'),
        (g2,'valves_hydrants',        'Valves & Hydrants',        '/ic3/24x7/p1/valves',        2,'coming_soon'),
        (g2,'reservoirs',             'Reservoirs / OHT / GLR',   '/ic3/24x7/p1/reservoirs',    3,'coming_soon'),
        (g2,'pumping_stations',       'Pumping Stations',         '/ic3/24x7/p1/pumping',       4,'coming_soon'),
        (g2,'dma_registry',           'DMA Registry',             '/ic3/24x7/p1/dma',           5,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- ── P2: Information with Intelligence ──────────────────
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order)
        VALUES ('SYS-001',p2,'supply_monitoring','Supply Monitoring Dashboards',1)
        ON CONFLICT DO NOTHING RETURNING id INTO g3;
    IF g3 IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g3,'dma_supply_status',      'DMA Supply Status',        '/ic3/24x7/p2/dma-status',    1,'coming_soon'),
        (g3,'zone_supply_schedule',   'Zone-wise Supply Schedule', '/ic3/24x7/p2/schedule',      2,'coming_soon'),
        (g3,'reservoir_level_dash',   'Reservoir Level Dashboard', '/ic3/24x7/p2/reservoir',     3,'coming_soon'),
        (g3,'pumping_status',         'Pumping Status Overview',  '/ic3/24x7/p2/pumping-status', 4,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- ── P3: Analytics & Network Intelligence ────────────────
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order)
        VALUES ('SYS-001',p3,'nrw_analytics','Non-Revenue Water (NRW)',1)
        ON CONFLICT DO NOTHING RETURNING id INTO g4;
    IF g4 IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g4,'real_apparent_losses',   'Real vs Apparent Losses',  '/ic3/24x7/p3/losses',        1,'coming_soon'),
        (g4,'mnf_analysis',           'Min Night Flow (MNF)',     '/ic3/24x7/p3/mnf',           2,'coming_soon'),
        (g4,'leakage_hotspots',       'Leakage Hotspots',        '/ic3/24x7/p3/leakage',       3,'coming_soon'),
        (g4,'dma_loss_ranking',       'DMA Loss Ranking',        '/ic3/24x7/p3/dma-loss',      4,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

END $$;
`,
	},
	{
		Version:     18,
		Description: "Seed SYS-008 Digital Twin nav menu (all 6 layers, 24 groups, 80 items)",
		SQL: `
-- Ensure SYS-008 exists in system master (Digital Twin Platform)
INSERT INTO ic3_system_master (system_id, system_name, system_category, system_type, is_active, created_at, updated_at)
VALUES ('SYS-008', 'Digital Twin Platform', 'DT', 'DIGITAL_TWIN', TRUE, NOW(), NOW())
ON CONFLICT (system_id) DO NOTHING;

-- Wipe and reseed SYS-008 nav data
DELETE FROM ic3_nav_item  WHERE group_id IN (SELECT id FROM ic3_nav_group WHERE system_id = 'SYS-008');
DELETE FROM ic3_nav_group WHERE system_id = 'SYS-008';

DO $$
DECLARE
    p1 INT; p2 INT; p3 INT; p4 INT; p5 INT; p6 INT;
    g INT;
BEGIN
    SELECT id INTO p1 FROM ic3_nav_layer WHERE code='P1';
    SELECT id INTO p2 FROM ic3_nav_layer WHERE code='P2';
    SELECT id INTO p3 FROM ic3_nav_layer WHERE code='P3';
    SELECT id INTO p4 FROM ic3_nav_layer WHERE code='P4';
    SELECT id INTO p5 FROM ic3_nav_layer WHERE code='P5';
    SELECT id INTO p6 FROM ic3_nav_layer WHERE code='P6';

    -- ━━━━━━━ P1: Data & Field Intelligence ━━━━━━━
    -- G1: Network Model
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order)
        VALUES ('SYS-008',p1,'network_model','Network Model',1)
        ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'pipe_network_model',         'Pipe Network Model',           '/ic3/twin/p1/pipe-model',        1,'coming_soon'),
        (g,'nodes_junctions',            'Nodes & Junctions',            '/ic3/twin/p1/nodes',             2,'coming_soon'),
        (g,'tanks_reservoirs_model',     'Tanks / Reservoirs Model',     '/ic3/twin/p1/tanks-model',       3,'coming_soon'),
        (g,'pumps_valves_model',         'Pumps & Valves Model',         '/ic3/twin/p1/pumps-valves',      4,'coming_soon'),
        (g,'asset_attributes_metadata',  'Asset Attributes & Metadata',  '/ic3/twin/p1/asset-metadata',    5,'coming_soon'),
        (g,'model_versioning',           'Model Versioning & Snapshots', '/ic3/twin/p1/versioning',        6,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G2: Live Data Binding
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order)
        VALUES ('SYS-008',p1,'live_data_binding','Live Data Binding',2)
        ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'realtime_telemetry_binding', 'Realtime / Telemetry Binding',             '/ic3/twin/p1/realtime-binding', 1,'coming_soon'),
        (g,'sensor_asset_mapping',    'Sensor-to-Asset Mapping',               '/ic3/twin/p1/sensor-mapping',2,'coming_soon'),
        (g,'iot_live_state',          'IoT Live State Feed',                   '/ic3/twin/p1/iot-feed',      3,'coming_soon'),
        (g,'boundary_conditions',     'Boundary Conditions (Demand / Supply)', '/ic3/twin/p1/boundaries',    4,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G3: Geospatial & 3D Base
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order)
        VALUES ('SYS-008',p1,'geospatial_3d','Geospatial & 3D Base',3)
        ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'gis_layer_integration', 'GIS Layer Integration',          '/ic3/twin/p1/gis-layers', 1,'coming_soon'),
        (g,'terrain_dem',           'Terrain / DEM',                   '/ic3/twin/p1/terrain',    2,'coming_soon'),
        (g,'3d_asset_geometry',     '3D Asset Geometry / BIM',         '/ic3/twin/p1/3d-geometry',3,'coming_soon'),
        (g,'network_topology',      'Network Topology & Connectivity', '/ic3/twin/p1/topology',   4,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G4: Model Calibration Data
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order)
        VALUES ('SYS-008',p1,'model_calibration','Model Calibration Data',4)
        ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'field_measurement_import', 'Field Measurement Import',  '/ic3/twin/p1/field-measurements',1,'coming_soon'),
        (g,'roughness_friction_data',  'Roughness / Friction Data', '/ic3/twin/p1/friction-data',     2,'coming_soon'),
        (g,'demand_allocation',        'Demand Allocation Data',    '/ic3/twin/p1/demand-allocation', 3,'coming_soon'),
        (g,'calibration_datasets',     'Calibration Datasets',      '/ic3/twin/p1/calibration-data',  4,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- ━━━━━━━ P2: Information with Intelligence ━━━━━━━
    -- G1: Live Twin Dashboard
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order)
        VALUES ('SYS-008',p2,'live_twin_dashboard','Live Twin Dashboard',1)
        ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'realtime_network_state', 'Real-time Network State',  '/ic3/twin/p2/network-state',     1,'coming_soon'),
        (g,'pressure_flow_heatmap',  'Pressure / Flow Heatmap',  '/ic3/twin/p2/heatmap',           2,'coming_soon'),
        (g,'tank_levels_live',       'Tank Levels (Live)',        '/ic3/twin/p2/tank-levels',       3,'coming_soon'),
        (g,'pump_valve_status',      'Pump / Valve Status',       '/ic3/twin/p2/pump-valve-status', 4,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G2: 3D / Map Visualization
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order)
        VALUES ('SYS-008',p2,'3d_map_visualization','3D / Map Visualization',2)
        ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'3d_network_viewer',     '3D Network Viewer',       '/ic3/twin/p2/3d-viewer',      1,'coming_soon'),
        (g,'gis_map_overlay',       'GIS Map Overlay',         '/ic3/twin/p2/map-overlay',    2,'coming_soon'),
        (g,'layer_toggles_filters', 'Layer Toggles & Filters', '/ic3/twin/p2/layer-filters',  3,'coming_soon'),
        (g,'cross_section_views',   'Cross-Section Views',     '/ic3/twin/p2/cross-sections', 4,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G3: Network State Explorer
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order)
        VALUES ('SYS-008',p2,'network_state_explorer','Network State Explorer',3)
        ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'node_link_inspector',  'Node / Link Inspector',              '/ic3/twin/p2/node-inspector',  1,'coming_soon'),
        (g,'time_slider_playback', 'Time-Slider Playback',               '/ic3/twin/p2/time-playback',   2,'coming_soon'),
        (g,'state_comparison',     'State Comparison (Now vs Baseline)', '/ic3/twin/p2/state-comparison',3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G4: Twin Health & Sync
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order)
        VALUES ('SYS-008',p2,'twin_health_sync','Twin Health & Sync',4)
        ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'model_reality_deviation', 'Model-vs-Reality Deviation', '/ic3/twin/p2/deviation',   1,'coming_soon'),
        (g,'data_freshness_latency',  'Data Freshness / Latency',   '/ic3/twin/p2/freshness',   2,'coming_soon'),
        (g,'sync_status',             'Sync Status',                '/ic3/twin/p2/sync-status', 3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- ━━━━━━━ P3: Analytics & Network Intelligence ━━━━━━━
    -- G1: Hydraulic Simulation
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order)
        VALUES ('SYS-008',p3,'hydraulic_simulation','Hydraulic Simulation',1)
        ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'steady_state_simulation',    'Steady-State Simulation',             '/ic3/twin/p3/steady-state',    1,'coming_soon'),
        (g,'extended_period_simulation', 'Extended-Period Simulation (EPS)',    '/ic3/twin/p3/eps',             2,'coming_soon'),
        (g,'water_age_quality',          'Water Age / Quality Simulation',      '/ic3/twin/p3/water-quality',   3,'coming_soon'),
        (g,'energy_pumping_analysis',    'Energy & Pumping Analysis',           '/ic3/twin/p3/energy-analysis', 4,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G2: Scenario & What-If Analysis
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order)
        VALUES ('SYS-008',p3,'scenario_whatif','Scenario & What-If Analysis',2)
        ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'scenario_builder',       'Scenario Builder',                 '/ic3/twin/p3/scenario-builder',1,'coming_soon'),
        (g,'demand_growth_scenarios','Demand-Growth Scenarios',          '/ic3/twin/p3/demand-growth',   2,'coming_soon'),
        (g,'pipe_break_scenarios',   'Pipe Break / Isolation Scenarios', '/ic3/twin/p3/pipe-break',      3,'coming_soon'),
        (g,'scenario_comparison',    'Scenario Comparison',              '/ic3/twin/p3/scenario-compare',4,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G3: Performance Diagnostics
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order)
        VALUES ('SYS-008',p3,'performance_diagnostics','Performance Diagnostics',3)
        ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'pressure_deficiency_zones',   'Pressure-Deficiency Zones',         '/ic3/twin/p3/pressure-zones',    1,'coming_soon'),
        (g,'low_negative_pressure',       'Low / Negative Pressure Detection', '/ic3/twin/p3/negative-pressure', 2,'coming_soon'),
        (g,'velocity_headloss',           'Velocity & Headloss Analysis',      '/ic3/twin/p3/velocity-analysis', 3,'coming_soon'),
        (g,'critical_asset_identification','Critical Asset Identification',     '/ic3/twin/p3/critical-assets',   4,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G4: Calibration & Validation
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order)
        VALUES ('SYS-008',p3,'calibration_validation','Calibration & Validation',4)
        ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'calibration_analysis',   'Calibration Analysis',   '/ic3/twin/p3/calibration-analysis',1,'coming_soon'),
        (g,'model_accuracy_metrics', 'Model Accuracy Metrics', '/ic3/twin/p3/accuracy-metrics',    2,'coming_soon'),
        (g,'sensor_reconciliation',  'Sensor Reconciliation',  '/ic3/twin/p3/sensor-reconciliation',3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- ━━━━━━━ P4: AI Models & Prediction ━━━━━━━
    -- G1: Predictive Simulation
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order)
        VALUES ('SYS-008',p4,'predictive_simulation','Predictive Simulation',1)
        ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'forecast_driven_simulation','Forecast-Driven Simulation',    '/ic3/twin/p4/forecast-sim',       1,'coming_soon'),
        (g,'demand_based_prediction',   'Demand-Based State Prediction', '/ic3/twin/p4/demand-prediction',  2,'coming_soon'),
        (g,'realtime_predictive_twin',  'Real-time Predictive Twin',     '/ic3/twin/p4/realtime-predictor', 3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G2: Anomaly & Event Detection
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order)
        VALUES ('SYS-008',p4,'anomaly_event_detection','Anomaly & Event Detection',2)
        ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'burst_localization',      'Model-Based Burst Localization', '/ic3/twin/p4/burst-location',    1,'coming_soon'),
        (g,'anomaly_detection_twin',  'Anomaly Detection on Twin',      '/ic3/twin/p4/anomaly-detection', 2,'coming_soon'),
        (g,'contamination_spread',    'Contamination Spread Modeling',  '/ic3/twin/p4/contamination',     3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G3: Optimization & Recommendations
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order)
        VALUES ('SYS-008',p4,'optimization_recommendations','Optimization & Recommendations',3)
        ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'pump_schedule_optimization','Pump-Scheduling Optimization','/ic3/twin/p4/pump-optimization',  1,'coming_soon'),
        (g,'pressure_optimization',     'Pressure Optimization',       '/ic3/twin/p4/pressure-optimization',2,'coming_soon'),
        (g,'valve_setting_optimization','Valve-Setting Optimization',  '/ic3/twin/p4/valve-optimization', 3,'coming_soon'),
        (g,'energy_cost_optimization',  'Energy-Cost Optimization',    '/ic3/twin/p4/energy-optimization',4,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G4: AI-Assisted Calibration
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order)
        VALUES ('SYS-008',p4,'ai_assisted_calibration','AI-Assisted Calibration',4)
        ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'auto_calibration_ml',    'Auto-Calibration (ML)',  '/ic3/twin/p4/auto-calibration',1,'coming_soon'),
        (g,'demand_pattern_learning','Demand Pattern Learning','/ic3/twin/p4/demand-learning', 2,'coming_soon'),
        (g,'surrogate_ml_models',    'Surrogate / ML Models',  '/ic3/twin/p4/ml-models',       3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- ━━━━━━━ P5: Operations & Automation ━━━━━━━
    -- G1: Operational Decision Support
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order)
        VALUES ('SYS-008',p5,'operational_decision','Operational Decision Support',1)
        ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'operator_advisory',              'Operator Advisory',                 '/ic3/twin/p5/advisory',              1,'coming_soon'),
        (g,'realtime_control_recommendations','Real-time Control Recommendations','/ic3/twin/p5/control-recommendations',2,'coming_soon'),
        (g,'isolation_valve_planning',       'Isolation / Valve Planning',        '/ic3/twin/p5/isolation-planning',    3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G2: Scenario Execution & Dispatch
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order)
        VALUES ('SYS-008',p5,'scenario_execution','Scenario Execution & Dispatch',2)
        ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'approved_scenario_rollout','Approved Scenario Rollout','/ic3/twin/p5/scenario-rollout',1,'coming_soon'),
        (g,'work_order_scenario',      'Work Order from Scenario', '/ic3/twin/p5/work-order',      2,'coming_soon'),
        (g,'field_crew_guidance',      'Field Crew Guidance',      '/ic3/twin/p5/crew-guidance',   3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G3: Twin-Driven Alarms
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order)
        VALUES ('SYS-008',p5,'twin_driven_alarms','Twin-Driven Alarms',3)
        ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'predicted_condition_alarms',  'Predicted-Condition Alarms',   '/ic3/twin/p5/predicted-alarms',1,'coming_soon'),
        (g,'threshold_breach_simulation', 'Threshold-Breach Simulation',  '/ic3/twin/p5/threshold-breach',2,'coming_soon'),
        (g,'escalation_workflow',         'Escalation Workflow',          '/ic3/twin/p5/escalation',      3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G4: Automation & Sync Control
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order)
        VALUES ('SYS-008',p5,'automation_sync_control','Automation & Sync Control',4)
        ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'auto_model_update_rules',  'Auto Model-Update Rules',      '/ic3/twin/p5/update-rules',1,'coming_soon'),
        (g,'closed_loop_control_twin', 'Closed-Loop Control via Twin', '/ic3/twin/p5/closed-loop', 2,'coming_soon'),
        (g,'simulation_scheduling',    'Simulation Scheduling',        '/ic3/twin/p5/sim-scheduling',3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- ━━━━━━━ P6: Citizen, Reporting & Governance ━━━━━━━
    -- G1: Stakeholder Visualization
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order)
        VALUES ('SYS-008',p6,'stakeholder_visualization','Stakeholder Visualization',1)
        ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'public_stakeholder_views',    'Public / Stakeholder Twin Views','/ic3/twin/p6/stakeholder-views',1,'coming_soon'),
        (g,'outage_impact_communication', 'Outage & Impact Communication', '/ic3/twin/p6/outage-impact',     2,'coming_soon'),
        (g,'planning_visualization',      'Planning Visualization',        '/ic3/twin/p6/planning-visual',   3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G2: Reporting & Outcomes
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order)
        VALUES ('SYS-008',p6,'reporting_outcomes','Reporting & Outcomes',2)
        ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'simulation_reports',          'Simulation Reports',           '/ic3/twin/p6/sim-reports',       1,'coming_soon'),
        (g,'scenario_comparison_reports', 'Scenario-Comparison Reports',  '/ic3/twin/p6/scenario-reports',  2,'coming_soon'),
        (g,'model_performance_reports',   'Model-Performance Reports',    '/ic3/twin/p6/performance-reports',3,'coming_soon'),
        (g,'planning_investment_reports', 'Planning & Investment Reports', '/ic3/twin/p6/planning-reports',  4,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G3: Administration & Trust
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order)
        VALUES ('SYS-008',p6,'administration_trust','Administration & Trust',3)
        ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'model_access_permissions', 'Model Access & Permissions', '/ic3/twin/p6/access-permissions',1,'coming_soon'),
        (g,'version_control_audit',    'Version Control & Audit',    '/ic3/twin/p6/version-control',   2,'coming_soon'),
        (g,'model_governance_lineage', 'Model Governance / Lineage', '/ic3/twin/p6/governance',        3,'coming_soon'),
        (g,'configuration_standards',  'Configuration & Standards',  '/ic3/twin/p6/config-standards',  4,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

END $$;
`,
	},
	{
		Version:     19,
		Description: "Seed 12 Solution Scenarios + CMS/CMMS platforms into ic3_system_master",
		SQL: `
-- 12 Solution Scenario entries (system_type=DOMAIN so they appear in SystemsBar;
-- system_category=SOLUTION_SCENARIO distinguishes them from water-domain systems)
INSERT INTO ic3_system_master (system_id, system_name, system_category, system_type, is_active, created_at, updated_at) VALUES
('SS-01', 'Operations & Live Monitoring',        'SOLUTION_SCENARIO', 'DOMAIN', TRUE, NOW(), NOW()),
('SS-02', 'Performance, SLA & Reliability',      'SOLUTION_SCENARIO', 'DOMAIN', TRUE, NOW(), NOW()),
('SS-03', 'Asset & Infrastructure Management',   'SOLUTION_SCENARIO', 'DOMAIN', TRUE, NOW(), NOW()),
('SS-04', 'Maintenance & Work Order Management', 'SOLUTION_SCENARIO', 'DOMAIN', TRUE, NOW(), NOW()),
('SS-05', 'GIS, Location & Field Operations',    'SOLUTION_SCENARIO', 'DOMAIN', TRUE, NOW(), NOW()),
('SS-06', 'Consumer & Customer Management',      'SOLUTION_SCENARIO', 'DOMAIN', TRUE, NOW(), NOW()),
('SS-07', 'Finance, Cost & Procurement',         'SOLUTION_SCENARIO', 'DOMAIN', TRUE, NOW(), NOW()),
('SS-08', 'Contract & Warranty Management',      'SOLUTION_SCENARIO', 'DOMAIN', TRUE, NOW(), NOW()),
('SS-09', 'IoT & Real-Time Intelligence',        'SOLUTION_SCENARIO', 'DOMAIN', TRUE, NOW(), NOW()),
('SS-10', 'Analytics & Business Reporting',      'SOLUTION_SCENARIO', 'DOMAIN', TRUE, NOW(), NOW()),
('SS-11', 'Compliance, Audit & Escalation',      'SOLUTION_SCENARIO', 'DOMAIN', TRUE, NOW(), NOW()),
('SS-12', 'Data Quality & Governance',           'SOLUTION_SCENARIO', 'DOMAIN', TRUE, NOW(), NOW())
ON CONFLICT (system_id) DO UPDATE
  SET system_name = EXCLUDED.system_name,
      system_category = EXCLUDED.system_category,
      system_type = EXCLUDED.system_type,
      updated_at = NOW();

-- 2 Platform sub-layer entries (CMS and CMMS) — children of each scenario via convention
INSERT INTO ic3_system_master (system_id, system_name, system_category, system_type, is_active, created_at, updated_at) VALUES
('PLAT-CMS',  'CMS — Complaint Management',  'PLATFORM', 'PLATFORM', TRUE, NOW(), NOW()),
('PLAT-CMMS', 'CMMS — Maintenance Mgmt',     'PLATFORM', 'PLATFORM', TRUE, NOW(), NOW())
ON CONFLICT (system_id) DO UPDATE
  SET system_name = EXCLUDED.system_name,
      updated_at = NOW();
`,
	},
	{
		Version:     20,
		Description: "Create ic3_system_nav — system-to-nav-layer mapping table",
		SQL: `
-- Extend layer_no constraint to support layers beyond P6
ALTER TABLE ic3_nav_layer DROP CONSTRAINT IF EXISTS ic3_nav_layer_layer_no_check;
ALTER TABLE ic3_nav_layer ADD CONSTRAINT ic3_nav_layer_layer_no_check CHECK (layer_no >= 1 AND layer_no <= 99);

-- Add CMS and CMMS as navigation layers 7 and 8
INSERT INTO ic3_nav_layer (layer_no, code, label, description) VALUES
(7, 'CMS',  'CMS - Complaint Management', 'Complaint management, SLA tracking, citizen grievance resolution'),
(8, 'CMMS', 'CMMS - Maintenance Mgmt',    'Maintenance management, work orders, asset servicing and repair')
ON CONFLICT (code) DO NOTHING;

-- Bridge table: maps each system to one or more navigation layers
CREATE TABLE IF NOT EXISTS ic3_system_nav (
    id          SERIAL PRIMARY KEY,
    system_id   VARCHAR(50)  NOT NULL REFERENCES ic3_system_master(system_id) ON DELETE CASCADE,
    layer_id    INT          NOT NULL REFERENCES ic3_nav_layer(id)            ON DELETE CASCADE,
    status      VARCHAR(20)  NOT NULL DEFAULT 'active'
                    CHECK (status IN ('active', 'inactive')),
    created_by  VARCHAR(100),
    created_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    UNIQUE (system_id, layer_id)
);

CREATE INDEX IF NOT EXISTS idx_system_nav_system  ON ic3_system_nav(system_id);
CREATE INDEX IF NOT EXISTS idx_system_nav_layer   ON ic3_system_nav(layer_id);
CREATE INDEX IF NOT EXISTS idx_system_nav_status  ON ic3_system_nav(status);
`,
	},
}

// runMigrations bootstraps schema_migrations then applies any pending versions.
func runMigrations(ctx context.Context, pool *pgxpool.Pool) error {
	if _, err := pool.Exec(ctx, `
		CREATE TABLE IF NOT EXISTS schema_migrations (
			version     INTEGER PRIMARY KEY,
			applied_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
			description TEXT
		)`); err != nil {
		return fmt.Errorf("create schema_migrations: %w", err)
	}

	for _, m := range migrations {
		var exists bool
		pool.QueryRow(ctx,
			`SELECT EXISTS(SELECT 1 FROM schema_migrations WHERE version=$1)`, m.Version,
		).Scan(&exists)
		if exists {
			log.Printf("Migration %d already applied -- skipping", m.Version)
			continue
		}

		log.Printf("Applying migration %d: %s", m.Version, m.Description)
		if _, err := pool.Exec(ctx, m.SQL); err != nil {
			return fmt.Errorf("migration %d failed: %w", m.Version, err)
		}
		pool.Exec(ctx,
			`INSERT INTO schema_migrations(version, description) VALUES($1,$2)`,
			m.Version, m.Description,
		)
		log.Printf("Migration %d applied successfully", m.Version)
	}

	// Apply navigation migrations
	log.Println("[NAV MIGRATIONS] Starting navigation migrations...")
	defer log.Println("[NAV MIGRATIONS] Navigation migrations complete")
	applyNavMigrations(ctx, pool)

	return nil
}