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 SCADA-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 SCADA 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-SCADA-01
    system_name         VARCHAR(150) NOT NULL,
    system_category     VARCHAR(80)  NOT NULL,     -- SCADA|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|SCADA|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 SCADA
    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),
    ('SCADA',    'SCADA / 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'),
    ('SCADA', 'SCADA', 'SCADA'),
    ('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'),
    ('SCADA','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);
`,
	},
}

// 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)
	}
	return nil
}