package main

import (
	"context"
	"fmt"
	"os"

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

func applyNavMigrations(ctx context.Context, pool *pgxpool.Pool) {

	// Migration 16: Create nav tables
	fmt.Println("Applying migration 16: IC3 Nav Menu Tables...")
	sql16 := `
-- 1. Intelligence layers (P1–P6 + SYS-014 special layers 301-302) — global, not per system
CREATE TABLE IF NOT EXISTS ic3_nav_layer (
    id          SERIAL PRIMARY KEY,
    layer_no    SMALLINT NOT NULL CHECK (layer_no BETWEEN 1 AND 999),
    code        VARCHAR(10) NOT NULL UNIQUE,
    label       VARCHAR(100) NOT NULL,
    description TEXT,
    UNIQUE (layer_no)
);

-- Seed the 6 pillars + SYS-014 special layers
INSERT INTO ic3_nav_layer (layer_no, code, label, description) VALUES
(1, 'P1', 'Data & Field Intelligence',         'Raw acquisition — assets, IoT/Realtime, field data'),
(2, 'P2', 'Information with Intelligence',     'Contextualised dashboards, metering, water balance'),
(3, 'P3', 'Analytics & Network Intelligence',  'Hydraulics, NRW, network health, SLA analytics'),
(4, 'P4', 'AI Models & Prediction',            'Forecasting, predictive maintenance, anomaly detection'),
(5, 'P5', 'Operations & Automation',           'Scheduling, work orders, alarms, control logic'),
(6, 'P6', 'Citizen, Reporting & Governance',   'Citizen care, statutory reports, admin & trust'),
(301, 'SYS014-L1', 'Systems & Architecture',   'The 13 water systems as integrated pillars'),
(302, 'SYS014-L2', 'Cross-System Analytics',   'Unified intelligence across all domains')
ON CONFLICT (layer_no) DO NOTHING;

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

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

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

-- Indexes
CREATE INDEX IF NOT EXISTS idx_nav_group_system_layer ON ic3_nav_group(system_id, layer_id);
CREATE INDEX IF NOT EXISTS idx_nav_item_group         ON ic3_nav_item(group_id);
CREATE INDEX IF NOT EXISTS idx_nav_role_item          ON ic3_nav_role_access(item_id);
`

	_, err := pool.Exec(ctx, sql16)
	if err != nil {
		fmt.Printf("✗ Migration 16 failed: %v\n", err)
		return
	}
	fmt.Println("✓ Migration 16 applied")

	// Migration 17: Seed all systems data
	fmt.Println("Seeding navigation menu data...")

	// Read the comprehensive seed file (for SYS-002 through SYS-013)
	seedData, err := os.ReadFile("H:/ic3/docs/v5_1_nav_seed_all_systems.sql")
	if err != nil {
		fmt.Printf("Warning: Comprehensive seed file not found\n")
	} else {
		_, _ = pool.Exec(ctx, string(seedData))
		fmt.Println("✓ Comprehensive seed data applied (SYS-002 through SYS-013)")
	}

	// Clear old SYS-001 data and seed with correct structure from document
	fmt.Println("Clearing old SYS-001 data...")
	_, _ = pool.Exec(ctx, `DELETE FROM ic3_nav_item WHERE group_id IN (SELECT id FROM ic3_nav_group WHERE system_id = 'SYS-001')`)
	_, _ = pool.Exec(ctx, `DELETE FROM ic3_nav_group WHERE system_id = 'SYS-001'`)

	fmt.Println("Seeding SYS-001 (24x7) with document structure...")
	seedSYS001 := `
DO $$
DECLARE
    p1 INT; p2 INT; p3 INT; p4 INT; p5 INT; p6 INT;
    g INT;
BEGIN
    SELECT id INTO p1 FROM ic3_nav_layer WHERE code='P1';
    SELECT id INTO p2 FROM ic3_nav_layer WHERE code='P2';
    SELECT id INTO p3 FROM ic3_nav_layer WHERE code='P3';
    SELECT id INTO p4 FROM ic3_nav_layer WHERE code='P4';
    SELECT id INTO p5 FROM ic3_nav_layer WHERE code='P5';
    SELECT id INTO p6 FROM ic3_nav_layer WHERE code='P6';

    -- ━━━━━━━ P1: Data & Field Intelligence ━━━━━━━
    -- G1: Network Assets & Inventory
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-001',p1,'network_assets','Network Assets & Inventory',1) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'pipe_network_registry','Pipe Network Registry','/ic3/24x7/p1/pipe-registry',1,'coming_soon'),
        (g,'valves_hydrants','Valves & Hydrants','/ic3/24x7/p1/valves',2,'coming_soon'),
        (g,'reservoirs','Reservoirs / OHT / GLR / Sumps','/ic3/24x7/p1/reservoirs',3,'coming_soon'),
        (g,'pumping_stations','Pumping Stations','/ic3/24x7/p1/pumping',4,'coming_soon'),
        (g,'bulk_consumer_meters','Bulk & Consumer Meters','/ic3/24x7/p1/meters',5,'coming_soon'),
        (g,'dma_registry','DMA Registry (District Metered Areas)','/ic3/24x7/p1/dma',6,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G2: Field Instrumentation / IoT
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-001',p1,'field_instrumentation','Field Instrumentation / IoT',2) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'flow_meters','Flow Meters (Bulk / Zone)','/ic3/24x7/p1/flow-meters',1,'coming_soon'),
        (g,'pressure_sensors','Pressure Sensors & Loggers','/ic3/24x7/p1/pressure-sensors',2,'coming_soon'),
        (g,'reservoir_sensors','Reservoir Level Sensors','/ic3/24x7/p1/reservoir-sensors',3,'coming_soon'),
        (g,'smart_meters','Smart Meters (AMR / AMI)','/ic3/24x7/p1/smart-meters',4,'coming_soon'),
        (g,'water_quality_probes','Inline Water-Quality Probes','/ic3/24x7/p1/wq-probes',5,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G3: Field Operations Data
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-001',p1,'field_operations','Field Operations Data',3) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'crew_tracking','Crew / Workforce Tracking','/ic3/24x7/p1/crew-tracking',1,'coming_soon'),
        (g,'field_surveys','Mobile Field Surveys','/ic3/24x7/p1/surveys',2,'coming_soon'),
        (g,'meter_reading','Meter-Reading Capture','/ic3/24x7/p1/meter-reading',3,'coming_soon'),
        (g,'asset_inspections','Asset Condition Inspections','/ic3/24x7/p1/inspections',4,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G4: Data Acquisition & Realtime
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-001',p1,'data_realtime','Data Acquisition & Realtime',4) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'realtime_telemetry','Realtime / Telemetry Feed','/ic3/24x7/p1/realtime',1,'coming_soon'),
        (g,'data_logger_sync','Data Logger Sync','/ic3/24x7/p1/logger-sync',2,'coming_soon'),
        (g,'manual_dcu_entry','Manual / DCU Entry','/ic3/24x7/p1/manual-entry',3,'coming_soon'),
        (g,'data_validation','Data Validation & Gap-Filling','/ic3/24x7/p1/validation',4,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

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

    -- G2: Consumption & Metering
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-001',p2,'consumption_metering','Consumption & Metering',2) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'consumption_profiles','Consumer Consumption Profiles','/ic3/24x7/p2/consumption',1,'coming_soon'),
        (g,'bulk_reconciliation','Bulk vs Consumer Reconciliation','/ic3/24x7/p2/reconciliation',2,'coming_soon'),
        (g,'meter_health','Meter Health & Coverage','/ic3/24x7/p2/meter-health',3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G3: Supply Equity & Continuity
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-001',p2,'supply_equity','Supply Equity & Continuity',3) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'supply_hours_247','Supply-Hours / 24×7 Compliance','/ic3/24x7/p2/supply-hours',1,'coming_soon'),
        (g,'pressure_adequacy','Pressure Adequacy Maps','/ic3/24x7/p2/pressure-maps',2,'coming_soon'),
        (g,'continuity_tracking','Continuous vs Intermittent Tracking','/ic3/24x7/p2/continuity',3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G4: Water Balance
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-001',p2,'water_balance','Water Balance',4) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'input_billed_consumed','Input vs Billed vs Consumed','/ic3/24x7/p2/input-billed',1,'coming_soon'),
        (g,'dma_water_balance','DMA Water Balance','/ic3/24x7/p2/dma-balance',2,'coming_soon'),
        (g,'storage_status','Storage Status','/ic3/24x7/p2/storage-status',3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- ━━━━━━━ P3: Analytics & Network Intelligence ━━━━━━━
    -- G1: Hydraulic Performance
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-001',p3,'hydraulic_performance','Hydraulic Performance',1) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'pressure_flow_analytics','Pressure–Flow Analytics','/ic3/24x7/p3/pressure-flow',1,'coming_soon'),
        (g,'hydraulic_analysis','Network Hydraulic Analysis','/ic3/24x7/p3/hydraulic-analysis',2,'coming_soon'),
        (g,'demand_patterns','Demand Pattern Analysis','/ic3/24x7/p3/demand-patterns',3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

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

    -- G3: Network Health
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-001',p3,'network_health','Network Health',3) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'burst_leak_frequency','Burst / Leak Frequency','/ic3/24x7/p3/burst-frequency',1,'coming_soon'),
        (g,'pipe_failure_analytics','Pipe Failure Analytics','/ic3/24x7/p3/pipe-failure',2,'coming_soon'),
        (g,'energy_efficiency','Pumping Energy Efficiency','/ic3/24x7/p3/energy-efficiency',3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G4: Service-Level Analytics
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-001',p3,'service_level_analytics','Service-Level Analytics',4) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'sla_compliance_trends','SLA Compliance Trends','/ic3/24x7/p3/sla-trends',1,'coming_soon'),
        (g,'equity_coverage_analytics','Equity & Coverage Analytics','/ic3/24x7/p3/equity-coverage',2,'coming_soon'),
        (g,'supply_reliability_index','Supply Reliability Index','/ic3/24x7/p3/reliability-index',3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- ━━━━━━━ P4: AI Models & Prediction ━━━━━━━
    -- G1: Demand Forecasting
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-001',p4,'demand_forecasting','Demand Forecasting',1) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'short_term_forecast','Short-term Demand Prediction','/ic3/24x7/p4/short-term',1,'coming_soon'),
        (g,'seasonal_peak_demand','Seasonal / Peak Demand','/ic3/24x7/p4/seasonal-peak',2,'coming_soon'),
        (g,'dma_level_forecast','DMA-level Forecast','/ic3/24x7/p4/dma-forecast',3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G2: Predictive Maintenance
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-001',p4,'predictive_maintenance','Predictive Maintenance',2) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'pipe_failure_prediction','Pipe Failure Prediction','/ic3/24x7/p4/pipe-failure',1,'coming_soon'),
        (g,'pump_failure_prediction','Pump Failure Prediction','/ic3/24x7/p4/pump-failure',2,'coming_soon'),
        (g,'asset_remaining_life','Asset Remaining-Life Estimation','/ic3/24x7/p4/remaining-life',3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G3: Leak & Anomaly Detection
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-001',p4,'leak_anomaly_detection','Leak & Anomaly Detection',3) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'ml_leak_detection','ML Leak Detection','/ic3/24x7/p4/ml-leak',1,'coming_soon'),
        (g,'burst_anomaly_alerts','Burst / Anomaly Alerts','/ic3/24x7/p4/burst-alerts',2,'coming_soon'),
        (g,'pressure_transient_detection','Pressure-Transient Detection','/ic3/24x7/p4/pressure-transient',3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G4: Optimization Models
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-001',p4,'optimization_models','Optimization Models',4) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'pumping_schedule_optimization','Pumping-Schedule Optimization','/ic3/24x7/p4/pump-schedule',1,'coming_soon'),
        (g,'pressure_optimization','Pressure-Management Optimization','/ic3/24x7/p4/pressure-optimization',2,'coming_soon'),
        (g,'energy_optimization','Energy Optimization','/ic3/24x7/p4/energy-optimization',3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- ━━━━━━━ P5: Operations & Automation ━━━━━━━
    -- G1: Supply Scheduling & Control
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-001',p5,'supply_scheduling','Supply Scheduling & Control',1) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'valve_operation_schedules','Valve Operation Schedules','/ic3/24x7/p5/valve-schedules',1,'coming_soon'),
        (g,'pump_control_automation','Pump Control / Automation','/ic3/24x7/p5/pump-control',2,'coming_soon'),
        (g,'pressure_management_prv','Pressure Management (PRV Control)','/ic3/24x7/p5/prv-control',3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G2: Work & Maintenance Management
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-001',p5,'work_maintenance','Work & Maintenance Management',2) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'work_orders','Work Orders','/ic3/24x7/p5/work-orders',1,'coming_soon'),
        (g,'maintenance_scheduling','Maintenance Scheduling','/ic3/24x7/p5/maintenance',2,'coming_soon'),
        (g,'crew_dispatch','Crew Dispatch','/ic3/24x7/p5/crew-dispatch',3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G3: Alarms & Incident Management
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-001',p5,'alarms_incidents','Alarms & Incident Management',3) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'alarm_console','Alarm Console','/ic3/24x7/p5/alarm-console',1,'coming_soon'),
        (g,'incident_workflow','Incident Workflow / Escalation','/ic3/24x7/p5/incident-workflow',2,'coming_soon'),
        (g,'sop_automation','SOP Automation','/ic3/24x7/p5/sop-automation',3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G4: Automation & Control Logic
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-001',p5,'automation_control','Automation & Control Logic',4) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'rule_engine_triggers','Rule Engine / Triggers','/ic3/24x7/p5/rule-engine',1,'coming_soon'),
        (g,'automated_alerts','Automated Alerts','/ic3/24x7/p5/automated-alerts',2,'coming_soon'),
        (g,'closed_loop_control','Closed-Loop Control','/ic3/24x7/p5/closed-loop',3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- ━━━━━━━ P6: Citizen, Reporting & Governance ━━━━━━━
    -- G1: Citizen Care
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-001',p6,'citizen_care','Citizen Care',1) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'complaint_grievance','Complaint / Grievance Management','/ic3/24x7/p6/complaints',1,'coming_soon'),
        (g,'service_requests','Service Requests','/ic3/24x7/p6/service-requests',2,'coming_soon'),
        (g,'citizen_app_portal','Citizen App / Portal','/ic3/24x7/p6/citizen-portal',3,'coming_soon'),
        (g,'notifications_comm','Notifications & Communication','/ic3/24x7/p6/notifications',4,'coming_soon'),
        (g,'feedback_satisfaction','Feedback & Satisfaction','/ic3/24x7/p6/feedback',5,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G2: Reporting & Outcomes
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-001',p6,'reporting_outcomes','Reporting & Outcomes',2) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'regulatory_statutory','Regulatory / Statutory Reports','/ic3/24x7/p6/regulatory',1,'coming_soon'),
        (g,'kpi_sla_reports','KPI & SLA Reports','/ic3/24x7/p6/kpi-sla',2,'coming_soon'),
        (g,'supply_equity_reports','Supply-Equity Reports','/ic3/24x7/p6/equity-reports',3,'coming_soon'),
        (g,'custom_report_builder','Custom Report Builder','/ic3/24x7/p6/custom-builder',4,'coming_soon'),
        (g,'scheduled_reports','Scheduled Reports','/ic3/24x7/p6/scheduled',5,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G3: Administration & Trust
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-001',p6,'administration_trust','Administration & Trust',3) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'user_role_management','User & Role Management','/ic3/24x7/p6/user-roles',1,'coming_soon'),
        (g,'access_control','Access Control / Permissions','/ic3/24x7/p6/access-control',2,'coming_soon'),
        (g,'audit_logs','Audit Logs / Data Lineage','/ic3/24x7/p6/audit-logs',3,'coming_soon'),
        (g,'configuration_master','Configuration / Master Data','/ic3/24x7/p6/config',4,'coming_soon'),
        (g,'data_governance','Data Governance & Security','/ic3/24x7/p6/governance',5,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

END $$;
`
	_, _ = pool.Exec(ctx, seedSYS001)

	// Clear old SYS-008 data and seed with correct structure
	fmt.Println("Clearing old SYS-008 data...")
	_, _ = pool.Exec(ctx, `DELETE FROM ic3_nav_item WHERE group_id IN (SELECT id FROM ic3_nav_group WHERE system_id = 'SYS-008')`)
	_, _ = pool.Exec(ctx, `DELETE FROM ic3_nav_group WHERE system_id = 'SYS-008'`)

	fmt.Println("Seeding SYS-008 (Digital Twin) with document structure...")
	seedSYS008 := `
DO $$
DECLARE
    p1 INT; p2 INT; p3 INT; p4 INT; p5 INT; p6 INT;
    g INT;
BEGIN
    SELECT id INTO p1 FROM ic3_nav_layer WHERE code='P1';
    SELECT id INTO p2 FROM ic3_nav_layer WHERE code='P2';
    SELECT id INTO p3 FROM ic3_nav_layer WHERE code='P3';
    SELECT id INTO p4 FROM ic3_nav_layer WHERE code='P4';
    SELECT id INTO p5 FROM ic3_nav_layer WHERE code='P5';
    SELECT id INTO p6 FROM ic3_nav_layer WHERE code='P6';

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

END $$;
`
	_, _ = pool.Exec(ctx, seedSYS008)

	// Clear old SYS-007 data and seed with correct structure
	fmt.Println("Clearing old SYS-007 data...")
	_, _ = pool.Exec(ctx, `DELETE FROM ic3_nav_item WHERE group_id IN (SELECT id FROM ic3_nav_group WHERE system_id = 'SYS-007')`)
	_, _ = pool.Exec(ctx, `DELETE FROM ic3_nav_group WHERE system_id = 'SYS-007'`)

	fmt.Println("Seeding SYS-007 (GIS System) with document structure...")
	seedSYS007 := `
DO $$
DECLARE
    p1 INT; p2 INT; p3 INT; p4 INT; p5 INT; p6 INT;
    g INT;
BEGIN
    SELECT id INTO p1 FROM ic3_nav_layer WHERE code='P1';
    SELECT id INTO p2 FROM ic3_nav_layer WHERE code='P2';
    SELECT id INTO p3 FROM ic3_nav_layer WHERE code='P3';
    SELECT id INTO p4 FROM ic3_nav_layer WHERE code='P4';
    SELECT id INTO p5 FROM ic3_nav_layer WHERE code='P5';
    SELECT id INTO p6 FROM ic3_nav_layer WHERE code='P6';

    -- ━━━━━━━ P1: Data & Field Intelligence (Geospatial foundation) ━━━━━━━
    -- G1: Base Maps & Layers
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-007',p1,'base_maps_layers','Base Maps & Layers',1) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'base_map_satellite','Base Map (Satellite / Street / Hybrid)','/ic3/gis/p1/base-maps',1,'coming_soon'),
        (g,'admin_boundaries','Administrative Boundaries (Ward / Zone / DMA)','/ic3/gis/p1/boundaries',2,'coming_soon'),
        (g,'land_use_parcel','Land Use / Parcel Layer','/ic3/gis/p1/parcel-layer',3,'coming_soon'),
        (g,'contour_terrain_dem','Contour / Terrain (DEM)','/ic3/gis/p1/terrain',4,'coming_soon'),
        (g,'imagery_orthophotos','Imagery / Drone Orthophotos','/ic3/gis/p1/imagery',5,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G2: Network Asset Layers
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-007',p1,'network_asset_layers','Network Asset Layers',2) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'pipeline_network','Pipeline Network Layer','/ic3/gis/p1/pipeline-network',1,'coming_soon'),
        (g,'nodes_junctions','Nodes / Junctions / Fittings','/ic3/gis/p1/nodes-junctions',2,'coming_soon'),
        (g,'valves_hydrants','Valves & Hydrants','/ic3/gis/p1/valves-hydrants',3,'coming_soon'),
        (g,'reservoirs_tanks','Reservoirs / Tanks / Pumping Stations','/ic3/gis/p1/reservoirs-tanks',4,'coming_soon'),
        (g,'service_connections','Service Connections','/ic3/gis/p1/service-connections',5,'coming_soon'),
        (g,'manholes_chambers','Manholes / Chambers','/ic3/gis/p1/manholes',6,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G3: Geospatial Data Management
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-007',p1,'geospatial_data_mgmt','Geospatial Data Management',3) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'feature_catalog','Feature Catalog / Geodatabase','/ic3/gis/p1/feature-catalog',1,'coming_soon'),
        (g,'coordinate_systems','Coordinate Systems / Projections','/ic3/gis/p1/coordinates',2,'coming_soon'),
        (g,'data_import','Data Import (Shapefile / KML / CAD / GeoJSON)','/ic3/gis/p1/data-import',3,'coming_soon'),
        (g,'topology_validation','Topology Rules & Validation','/ic3/gis/p1/topology',4,'coming_soon'),
        (g,'layer_versioning','Layer Versioning','/ic3/gis/p1/versioning',5,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G4: Field GIS / Survey
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-007',p1,'field_gis_survey','Field GIS / Survey',4) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'mobile_field_mapping','Mobile Field Mapping','/ic3/gis/p1/mobile-mapping',1,'coming_soon'),
        (g,'gps_asset_capture','GPS Asset Capture','/ic3/gis/p1/gps-capture',2,'coming_soon'),
        (g,'as_built_survey','As-Built Survey Import','/ic3/gis/p1/as-built',3,'coming_soon'),
        (g,'field_data_sync','Field Data Sync','/ic3/gis/p1/field-sync',4,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- ━━━━━━━ P2: Information with Intelligence (The map dashboard) ━━━━━━━
    -- G1: Interactive Map Dashboard
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-007',p2,'interactive_map_dashboard','Interactive Map Dashboard',1) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'network_map_viewer','Network Map Viewer','/ic3/gis/p2/map-viewer',1,'coming_soon'),
        (g,'layer_control_toggles','Layer Control / Toggles','/ic3/gis/p2/layer-control',2,'coming_soon'),
        (g,'search_locate','Search & Locate (Asset / Address)','/ic3/gis/p2/search',3,'coming_soon'),
        (g,'map_legend_symbology','Map Legend & Symbology','/ic3/gis/p2/legend',4,'coming_soon'),
        (g,'measure_draw_tools','Measure / Draw Tools','/ic3/gis/p2/measure-draw',5,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G2: Asset Map Views
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-007',p2,'asset_map_views','Asset Map Views',2) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'live_asset_status','Live Asset Status on Map','/ic3/gis/p2/asset-status',1,'coming_soon'),
        (g,'colour_coded_condition','Colour-coded Condition / Age','/ic3/gis/p2/condition-age',2,'coming_soon'),
        (g,'asset_popups','Asset Attribute Pop-ups','/ic3/gis/p2/popups',3,'coming_soon'),
        (g,'clustered_heatmap','Clustered / Heatmap Views','/ic3/gis/p2/heatmap',4,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G3: Thematic Maps
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-007',p2,'thematic_maps','Thematic Maps',3) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'pressure_flow_thematic','Pressure / Flow Thematic','/ic3/gis/p2/pressure-flow',1,'coming_soon'),
        (g,'supply_zone_dma','Supply Zone / DMA Maps','/ic3/gis/p2/supply-zones',2,'coming_soon'),
        (g,'consumer_density','Consumer Density Maps','/ic3/gis/p2/consumer-density',3,'coming_soon'),
        (g,'coverage_service_area','Coverage / Service-Area Maps','/ic3/gis/p2/coverage',4,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G4: Spatial Context Overlays
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-007',p2,'spatial_context_overlays','Spatial Context Overlays',4) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'complaints_incidents','Complaints / Incidents on Map','/ic3/gis/p2/complaints',1,'coming_soon'),
        (g,'work_orders_map','Work Orders on Map','/ic3/gis/p2/work-orders',2,'coming_soon'),
        (g,'sensor_locations','Sensor Locations','/ic3/gis/p2/sensors',3,'coming_soon'),
        (g,'nrw_loss_zones','NRW / Loss Zones Overlay','/ic3/gis/p2/nrw-zones',4,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- ━━━━━━━ P3: Analytics & Network Intelligence (Spatial analysis) ━━━━━━━
    -- G1: Network Tracing
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-007',p3,'network_tracing','Network Tracing',1) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'upstream_downstream','Upstream / Downstream Trace','/ic3/gis/p3/upstream-trace',1,'coming_soon'),
        (g,'isolation_valve_trace','Isolation / Valve Trace','/ic3/gis/p3/isolation-trace',2,'coming_soon'),
        (g,'connectivity_analysis','Connectivity Analysis','/ic3/gis/p3/connectivity',3,'coming_soon'),
        (g,'shortest_path_routing','Shortest-Path / Routing','/ic3/gis/p3/routing',4,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G2: Spatial Analysis
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-007',p3,'spatial_analysis','Spatial Analysis',2) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'buffer_proximity','Buffer / Proximity Analysis','/ic3/gis/p3/buffer-proximity',1,'coming_soon'),
        (g,'overlay_intersect','Overlay / Intersect Analysis','/ic3/gis/p3/overlay',2,'coming_soon'),
        (g,'density_hotspot','Density / Hotspot Analysis','/ic3/gis/p3/hotspot',3,'coming_soon'),
        (g,'catchment_service_area','Catchment / Service-Area Delineation','/ic3/gis/p3/catchment',4,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G3: Asset & Network Intelligence
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-007',p3,'asset_network_intelligence','Asset & Network Intelligence',3) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'asset_age_material','Asset Age / Material Mapping','/ic3/gis/p3/asset-age',1,'coming_soon'),
        (g,'pipe_criticality','Pipe Criticality Mapping','/ic3/gis/p3/criticality',2,'coming_soon'),
        (g,'gap_coverage_analysis','Gap / Coverage Analysis','/ic3/gis/p3/gap-coverage',3,'coming_soon'),
        (g,'network_density','Network Density Metrics','/ic3/gis/p3/density-metrics',4,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G4: Spatial Quality & Integrity
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-007',p3,'spatial_quality_integrity','Spatial Quality & Integrity',4) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'topology_error_detection','Topology Error Detection','/ic3/gis/p3/topology-errors',1,'coming_soon'),
        (g,'connectivity_gaps','Connectivity Gaps','/ic3/gis/p3/connectivity-gaps',2,'coming_soon'),
        (g,'attribute_completeness','Attribute Completeness','/ic3/gis/p3/completeness',3,'coming_soon'),
        (g,'geometry_validation','Geometry Validation','/ic3/gis/p3/geometry-validation',4,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- ━━━━━━━ P4: AI Models & Prediction (Geospatial AI) ━━━━━━━
    -- G1: Predictive Mapping
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-007',p4,'predictive_mapping','Predictive Mapping',1) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'leak_probability','Leak-Probability Mapping','/ic3/gis/p4/leak-probability',1,'coming_soon'),
        (g,'pipe_failure_risk','Pipe-Failure Risk Mapping','/ic3/gis/p4/failure-risk',2,'coming_soon'),
        (g,'demand_growth','Demand-Growth Mapping','/ic3/gis/p4/demand-growth',3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G2: Detection & Extraction
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-007',p4,'detection_extraction','Detection & Extraction',2) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'ai_feature_extraction','AI Feature Extraction (Imagery)','/ic3/gis/p4/feature-extraction',1,'coming_soon'),
        (g,'change_detection','Encroachment / Change Detection','/ic3/gis/p4/change-detection',2,'coming_soon'),
        (g,'automated_asset_detection','Automated Asset Detection','/ic3/gis/p4/asset-detection',3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G3: Spatial Optimization
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-007',p4,'spatial_optimization','Spatial Optimization',3) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'optimal_sensor_placement','Optimal Sensor Placement','/ic3/gis/p4/sensor-placement',1,'coming_soon'),
        (g,'network_expansion','Network Expansion Planning','/ic3/gis/p4/expansion-planning',2,'coming_soon'),
        (g,'routing_coverage','Routing / Coverage Optimization','/ic3/gis/p4/coverage-optimization',3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G4: Geospatial Forecasting
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-007',p4,'geospatial_forecasting','Geospatial Forecasting',4) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'spatial_demand_forecast','Spatial Demand Forecast','/ic3/gis/p4/demand-forecast',1,'coming_soon'),
        (g,'risk_zone_prediction','Risk-Zone Prediction','/ic3/gis/p4/risk-zones',2,'coming_soon'),
        (g,'spread_impact_modeling','Spread / Impact Modeling','/ic3/gis/p4/impact-modeling',3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- ━━━━━━━ P5: Operations & Automation (GIS-driven operations) ━━━━━━━
    -- G1: Map-Based Operations
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-007',p5,'map_based_operations','Map-Based Operations',1) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'incident_dispatch','Incident Location & Dispatch','/ic3/gis/p5/incident-dispatch',1,'coming_soon'),
        (g,'crew_tracking','Crew Tracking on Map','/ic3/gis/p5/crew-tracking',2,'coming_soon'),
        (g,'work_order_mapping','Work-Order Mapping','/ic3/gis/p5/work-order-map',3,'coming_soon'),
        (g,'isolation_planning','Map-Driven Isolation Planning','/ic3/gis/p5/isolation-planning',4,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G2: Field Workforce GIS
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-007',p5,'field_workforce_gis','Field Workforce GIS',2) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'field_navigation','Field Navigation / Routing','/ic3/gis/p5/field-navigation',1,'coming_soon'),
        (g,'mobile_work_assignment','Mobile Work Assignment','/ic3/gis/p5/work-assignment',2,'coming_soon'),
        (g,'live_field_location','Live Field Location','/ic3/gis/p5/live-location',3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G3: Planning & Design Tools
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-007',p5,'planning_design_tools','Planning & Design Tools',3) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'network_planning','Network Planning / Redlining','/ic3/gis/p5/planning-redline',1,'coming_soon'),
        (g,'design_markup','Design Markup & Annotation','/ic3/gis/p5/design-markup',2,'coming_soon'),
        (g,'scenario_mapping','Scenario Mapping','/ic3/gis/p5/scenario-mapping',3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G4: GIS Automation
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-007',p5,'gis_automation','GIS Automation',4) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'auto_map_updates','Auto Map Updates (Realtime / CMMS)','/ic3/gis/p5/auto-updates',1,'coming_soon'),
        (g,'geofencing_alerts','Geofencing & Alerts','/ic3/gis/p5/geofencing',2,'coming_soon'),
        (g,'scheduled_map_exports','Scheduled Map Exports','/ic3/gis/p5/map-exports',3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- ━━━━━━━ P6: Citizen, Reporting & Governance (Outward-facing) ━━━━━━━
    -- G1: Public / Citizen GIS
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-007',p6,'public_citizen_gis','Public / Citizen GIS',1) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'public_map_portal','Public Map Portal','/ic3/gis/p6/public-portal',1,'coming_soon'),
        (g,'outage_disruption_map','Outage / Disruption Map','/ic3/gis/p6/outage-map',2,'coming_soon'),
        (g,'complaint_location_capture','Complaint Location Capture','/ic3/gis/p6/complaint-capture',3,'coming_soon'),
        (g,'service_area_lookup','Service-Area Lookup','/ic3/gis/p6/service-lookup',4,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G2: Spatial Reporting
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-007',p6,'spatial_reporting','Spatial Reporting',2) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'map_based_reports','Map-Based Reports','/ic3/gis/p6/map-reports',1,'coming_soon'),
        (g,'spatial_asset_inventory','Spatial Asset-Inventory Reports','/ic3/gis/p6/asset-inventory',2,'coming_soon'),
        (g,'coverage_compliance_maps','Coverage / Compliance Maps','/ic3/gis/p6/compliance-maps',3,'coming_soon'),
        (g,'printable_map_atlas','Printable Map Atlas / Exports','/ic3/gis/p6/map-atlas',4,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G3: Administration & Trust
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-007',p6,'administration_trust','Administration & Trust',3) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'layer_access_permissions','Layer Access & Permissions','/ic3/gis/p6/access-permissions',1,'coming_soon'),
        (g,'geodatabase_admin','Geodatabase Administration','/ic3/gis/p6/geodatabase-admin',2,'coming_soon'),
        (g,'data_lineage_audit','Data Lineage / Audit','/ic3/gis/p6/data-lineage',3,'coming_soon'),
        (g,'standards_symbology','Standards & Symbology Config','/ic3/gis/p6/standards-config',4,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

END $$;
`
	_, _ = pool.Exec(ctx, seedSYS007)

	// Clear old SYS-011 data and seed with correct structure
	fmt.Println("Clearing old SYS-011 data...")
	_, _ = pool.Exec(ctx, `DELETE FROM ic3_nav_item WHERE group_id IN (SELECT id FROM ic3_nav_group WHERE system_id = 'SYS-011')`)
	_, _ = pool.Exec(ctx, `DELETE FROM ic3_nav_group WHERE system_id = 'SYS-011'`)

	fmt.Println("Seeding SYS-011 (Groundwater & Surface Water) with document structure...")
	seedSYS011 := `
DO $$
DECLARE
    p1 INT; p2 INT; p3 INT; p4 INT; p5 INT; p6 INT;
    g INT;
BEGIN
    SELECT id INTO p1 FROM ic3_nav_layer WHERE code='P1';
    SELECT id INTO p2 FROM ic3_nav_layer WHERE code='P2';
    SELECT id INTO p3 FROM ic3_nav_layer WHERE code='P3';
    SELECT id INTO p4 FROM ic3_nav_layer WHERE code='P4';
    SELECT id INTO p5 FROM ic3_nav_layer WHERE code='P5';
    SELECT id INTO p6 FROM ic3_nav_layer WHERE code='P6';

    -- ━━━━━━━ P1: Data & Field Intelligence (Source acquisition) ━━━━━━━
    -- G1: Source & Well Inventory
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-011',p1,'source_well_inventory','Source & Well Inventory',1) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'borewells_tubewells','Borewells / Tubewells Registry','/ic3/gwsw/p1/borewell-registry',1,'coming_soon'),
        (g,'open_wells_piezometers','Open Wells & Piezometers','/ic3/gwsw/p1/open-wells',2,'coming_soon'),
        (g,'surface_sources','Surface Sources (Rivers / Lakes / Reservoirs)','/ic3/gwsw/p1/surface-sources',3,'coming_soon'),
        (g,'raw_water_intake','Raw Water Intake Structures','/ic3/gwsw/p1/intake-structures',4,'coming_soon'),
        (g,'recharge_structures','Recharge Structures','/ic3/gwsw/p1/recharge-structures',5,'coming_soon'),
        (g,'aquifer_catchment','Aquifer / Catchment Registry','/ic3/gwsw/p1/aquifer-catchment',6,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G2: Field Instrumentation / IoT
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-011',p1,'field_instrumentation','Field Instrumentation / IoT',2) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'gw_level_sensors','Groundwater Level Sensors (DWLR)','/ic3/gwsw/p1/gw-sensors',1,'coming_soon'),
        (g,'borewell_flow_meters','Borewell Flow / Pump Meters','/ic3/gwsw/p1/flow-meters',2,'coming_soon'),
        (g,'surface_level_gauges','Surface Source Level Gauges','/ic3/gwsw/p1/level-gauges',3,'coming_soon'),
        (g,'raw_water_flow','Raw Water Flow Meters','/ic3/gwsw/p1/raw-water-flow',4,'coming_soon'),
        (g,'water_quality_probes','In-situ Water-Quality Probes','/ic3/gwsw/p1/wq-probes',5,'coming_soon'),
        (g,'rain_gauges','Rain Gauges','/ic3/gwsw/p1/rain-gauges',6,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G3: Field Operations Data
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-011',p1,'field_operations_data','Field Operations Data',3) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'water_level_dipping','Manual Water-Level Dipping','/ic3/gwsw/p1/water-level-dip',1,'coming_soon'),
        (g,'well_inspections','Well Inspection Surveys','/ic3/gwsw/p1/well-inspections',2,'coming_soon'),
        (g,'abstraction_logs','Abstraction Logbooks','/ic3/gwsw/p1/abstraction-logs',3,'coming_soon'),
        (g,'sample_collection','Sample Collection Logs','/ic3/gwsw/p1/sample-logs',4,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G4: Data Acquisition & Telemetry
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-011',p1,'data_acquisition_telemetry','Data Acquisition & Telemetry',4) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'realtime_telemetry','Realtime / Telemetry Feed','/ic3/gwsw/p1/realtime-feed',1,'coming_soon'),
        (g,'dwlr_logger_sync','DWLR / Logger Sync','/ic3/gwsw/p1/logger-sync',2,'coming_soon'),
        (g,'manual_dcu_entry','Manual / DCU Entry','/ic3/gwsw/p1/manual-entry',3,'coming_soon'),
        (g,'data_validation','Data Validation & Gap-Filling','/ic3/gwsw/p1/data-validation',4,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- ━━━━━━━ P2: Information with Intelligence (Contextualised views) ━━━━━━━
    -- G1: Source Monitoring Dashboards
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-011',p2,'source_monitoring_dashboards','Source Monitoring Dashboards',1) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'gw_level_dashboard','Groundwater Level Dashboard','/ic3/gwsw/p2/gw-dashboard',1,'coming_soon'),
        (g,'surface_level_storage','Surface Source Level / Storage','/ic3/gwsw/p2/surface-level',2,'coming_soon'),
        (g,'raw_water_availability','Raw Water Availability','/ic3/gwsw/p2/raw-water-avail',3,'coming_soon'),
        (g,'intake_status_overview','Intake Status Overview','/ic3/gwsw/p2/intake-status',4,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G2: Abstraction & Yield
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-011',p2,'abstraction_yield','Abstraction & Yield',2) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'wellwise_abstraction','Well-wise Abstraction','/ic3/gwsw/p2/well-abstraction',1,'coming_soon'),
        (g,'source_yield_profiles','Source Yield Profiles','/ic3/gwsw/p2/yield-profiles',2,'coming_soon'),
        (g,'pumping_energy','Pumping Hours / Energy','/ic3/gwsw/p2/pumping-energy',3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G3: Source Water Quality
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-011',p2,'source_water_quality','Source Water Quality',3) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'raw_water_quality','Raw Water Quality Status','/ic3/gwsw/p2/raw-quality',1,'coming_soon'),
        (g,'gw_quality_params','Groundwater Quality (TDS, Fluoride, Nitrate, Arsenic)','/ic3/gwsw/p2/gw-quality',2,'coming_soon'),
        (g,'contamination_indicators','Contamination Indicators','/ic3/gwsw/p2/contamination-indicators',3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G4: Recharge & Balance
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-011',p2,'recharge_balance','Recharge & Balance',4) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'recharge_status','Recharge Status','/ic3/gwsw/p2/recharge-status',1,'coming_soon'),
        (g,'gw_balance','Groundwater Balance (Recharge vs Abstraction)','/ic3/gwsw/p2/gw-balance',2,'coming_soon'),
        (g,'catchment_inflow','Catchment / Inflow Status','/ic3/gwsw/p2/catchment-inflow',3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- ━━━━━━━ P3: Analytics & Network Intelligence (Deeper analysis) ━━━━━━━
    -- G1: Groundwater Analytics
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-011',p3,'groundwater_analytics','Groundwater Analytics',1) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'water_table_trend','Water-Table Trend Analysis','/ic3/gwsw/p3/water-table-trend',1,'coming_soon'),
        (g,'seasonal_fluctuation','Seasonal Fluctuation Analysis','/ic3/gwsw/p3/seasonal-fluctuation',2,'coming_soon'),
        (g,'depletion_hotspots','Depletion / Over-Extraction Hotspots','/ic3/gwsw/p3/depletion-hotspots',3,'coming_soon'),
        (g,'aquifer_behaviour','Aquifer Behaviour Analysis','/ic3/gwsw/p3/aquifer-behaviour',4,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G2: Source Sustainability
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-011',p3,'source_sustainability','Source Sustainability',2) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'source_reliability','Source Reliability Index','/ic3/gwsw/p3/reliability-index',1,'coming_soon'),
        (g,'safe_yield_demand','Safe Yield vs Demand','/ic3/gwsw/p3/safe-yield',2,'coming_soon'),
        (g,'stress_scarcity','Stress / Scarcity Classification','/ic3/gwsw/p3/scarcity-class',3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G3: Quality Analytics
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-011',p3,'quality_analytics','Quality Analytics',3) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'contamination_trend','Contamination Trend Analysis','/ic3/gwsw/p3/contamination-trend',1,'coming_soon'),
        (g,'salinity_saline_ingress','Salinity / Saline-Ingress Tracking','/ic3/gwsw/p3/salinity-tracking',2,'coming_soon'),
        (g,'quality_hotspot','Quality Hotspot Mapping','/ic3/gwsw/p3/quality-hotspot',3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G4: Catchment & Hydrology
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-011',p3,'catchment_hydrology','Catchment & Hydrology',4) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'rainfall_recharge','Rainfall–Recharge Correlation','/ic3/gwsw/p3/rainfall-recharge',1,'coming_soon'),
        (g,'runoff_inflow','Runoff / Inflow Analysis','/ic3/gwsw/p3/runoff-inflow',2,'coming_soon'),
        (g,'drought_indicators','Drought Indicators','/ic3/gwsw/p3/drought-indicators',3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- ━━━━━━━ P4: AI Models & Prediction (Forecasting) ━━━━━━━
    -- G1: Resource Forecasting
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-011',p4,'resource_forecasting','Resource Forecasting',1) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'gw_level_prediction','Groundwater Level Prediction','/ic3/gwsw/p4/gw-prediction',1,'coming_soon'),
        (g,'surface_inflow_forecast','Surface Source Inflow Forecast','/ic3/gwsw/p4/inflow-forecast',2,'coming_soon'),
        (g,'seasonal_availability','Seasonal Availability Forecast','/ic3/gwsw/p4/seasonal-forecast',3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G2: Sustainability Modeling
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-011',p4,'sustainability_modeling','Sustainability Modeling',2) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'aquifer_depletion','Aquifer Depletion Modeling','/ic3/gwsw/p4/aquifer-depletion',1,'coming_soon'),
        (g,'safe_yield_prediction','Safe-Yield Prediction','/ic3/gwsw/p4/safe-yield-pred',2,'coming_soon'),
        (g,'recharge_estimation','Recharge Estimation Models','/ic3/gwsw/p4/recharge-estimation',3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G3: Quality & Risk Prediction
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-011',p4,'quality_risk_prediction','Quality & Risk Prediction',3) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'contamination_spread','Contamination Spread Prediction','/ic3/gwsw/p4/contamination-pred',1,'coming_soon'),
        (g,'saline_ingress_pred','Saline-Ingress Prediction','/ic3/gwsw/p4/saline-pred',2,'coming_soon'),
        (g,'source_failure_risk','Source Failure / Drying Risk','/ic3/gwsw/p4/failure-risk',3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G4: Optimization Models
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-011',p4,'optimization_models','Optimization Models',4) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'abstraction_scheduling','Abstraction-Scheduling Optimization','/ic3/gwsw/p4/abstraction-optim',1,'coming_soon'),
        (g,'source_mix_allocation','Source-Mix / Allocation Optimization','/ic3/gwsw/p4/source-mix-optim',2,'coming_soon'),
        (g,'pumping_energy_optim','Pumping Energy Optimization','/ic3/gwsw/p4/energy-optim',3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- ━━━━━━━ P5: Operations & Automation (Control and execution) ━━━━━━━
    -- G1: Abstraction Management
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-011',p5,'abstraction_management','Abstraction Management',1) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'well_operation_scheduling','Well Operation Scheduling','/ic3/gwsw/p5/well-scheduling',1,'coming_soon'),
        (g,'pump_control_automation','Pump Control / Automation','/ic3/gwsw/p5/pump-control',2,'coming_soon'),
        (g,'abstraction_limit_enforcement','Abstraction-Limit Enforcement','/ic3/gwsw/p5/abstraction-limits',3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G2: Source Switching & Allocation
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-011',p5,'source_switching_allocation','Source Switching & Allocation',2) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'source_mix_control','Source-Mix Control','/ic3/gwsw/p5/source-mix',1,'coming_soon'),
        (g,'intake_switching','Intake Switching','/ic3/gwsw/p5/intake-switching',2,'coming_soon'),
        (g,'allocation_rationing','Allocation / Rationing Plans','/ic3/gwsw/p5/rationing-plans',3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G3: Maintenance & Work Management
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-011',p5,'maintenance_work_mgmt','Maintenance & Work Management',3) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'well_pump_maintenance','Well / Pump Maintenance','/ic3/gwsw/p5/maintenance',1,'coming_soon'),
        (g,'work_orders','Work Orders','/ic3/gwsw/p5/work-orders',2,'coming_soon'),
        (g,'crew_dispatch','Crew Dispatch','/ic3/gwsw/p5/crew-dispatch',3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G4: Alarms & Automation
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-011',p5,'alarms_automation','Alarms & Automation',4) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'critical_level_alarms','Critical-Level Alarms','/ic3/gwsw/p5/critical-alarms',1,'coming_soon'),
        (g,'quality_breach_alarms','Quality-Breach Alarms','/ic3/gwsw/p5/quality-alarms',2,'coming_soon'),
        (g,'rule_engine_triggers','Rule Engine / Triggers','/ic3/gwsw/p5/rule-engine',3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- ━━━━━━━ P6: Citizen, Reporting & Governance (Outward-facing) ━━━━━━━
    -- G1: Stakeholder & Citizen Care
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-011',p6,'stakeholder_citizen_care','Stakeholder & Citizen Care',1) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'public_source_status','Public Source-Status Info','/ic3/gwsw/p6/source-status',1,'coming_soon'),
        (g,'community_recharge','Community Recharge Programs','/ic3/gwsw/p6/recharge-programs',2,'coming_soon'),
        (g,'grievance_requests','Grievance / Service Requests','/ic3/gwsw/p6/grievance',3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G2: Reporting & Compliance
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-011',p6,'reporting_compliance','Reporting & Compliance',2) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'regulatory_abstraction_reports','Regulatory Abstraction Reports (CGWA / NOC)','/ic3/gwsw/p6/regulatory-reports',1,'coming_soon'),
        (g,'gw_status_reports','Groundwater Status Reports','/ic3/gwsw/p6/gw-reports',2,'coming_soon'),
        (g,'source_quality_compliance','Source-Quality Compliance Reports','/ic3/gwsw/p6/compliance-reports',3,'coming_soon'),
        (g,'custom_scheduled_reports','Custom / Scheduled Reports','/ic3/gwsw/p6/custom-reports',4,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- G3: Administration & Trust
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-011',p6,'administration_trust','Administration & Trust',3) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'abstraction_licensing','Abstraction Licensing / NOC Register','/ic3/gwsw/p6/licensing',1,'coming_soon'),
        (g,'user_role_management','User & Role Management','/ic3/gwsw/p6/user-roles',2,'coming_soon'),
        (g,'audit_logs','Audit Logs / Data Lineage','/ic3/gwsw/p6/audit-logs',3,'coming_soon'),
        (g,'configuration_master','Configuration / Master Data','/ic3/gwsw/p6/config',4,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

END $$;
`
	_, _ = pool.Exec(ctx, seedSYS011)

	// Ensure SYS-011 exists in system master
	fmt.Println("Ensuring SYS-011 is registered in system master...")
	_, _ = pool.Exec(ctx, `
		INSERT INTO ic3_system_master (system_id, system_name, system_description, source_system, parent_system_id, is_active, created_at, updated_at)
		VALUES ('SYS-011', 'Groundwater & Surface Water', 'Source acquisition and monitoring — well & source inventory, field instrumentation, telemetry and field logs for groundwater and surface sources', 'INNOMAINT', 'SYS-001', true, NOW(), NOW())
		ON CONFLICT (system_id) DO UPDATE SET
			system_name = 'Groundwater & Surface Water',
			updated_at = NOW()
	`)

	// Clear old SYS-002 data and seed with correct structure
	fmt.Println("Clearing old SYS-002 data...")
	_, _ = pool.Exec(ctx, `DELETE FROM ic3_nav_item WHERE group_id IN (SELECT id FROM ic3_nav_group WHERE system_id = 'SYS-002')`)
	_, _ = pool.Exec(ctx, `DELETE FROM ic3_nav_group WHERE system_id = 'SYS-002'`)

	fmt.Println("Seeding SYS-002 (Real-time Operations) with document structure...")
	seedSYS002 := `
DO $$
DECLARE
    p1 INT; p2 INT; p3 INT; p4 INT; p5 INT; p6 INT;
    g INT;
BEGIN
    SELECT id INTO p1 FROM ic3_nav_layer WHERE code='P1';
    SELECT id INTO p2 FROM ic3_nav_layer WHERE code='P2';
    SELECT id INTO p3 FROM ic3_nav_layer WHERE code='P3';
    SELECT id INTO p4 FROM ic3_nav_layer WHERE code='P4';
    SELECT id INTO p5 FROM ic3_nav_layer WHERE code='P5';
    SELECT id INTO p6 FROM ic3_nav_layer WHERE code='P6';

    -- ━━━━━━━ P1: Data & Field Intelligence (Live-data foundation) ━━━━━━━
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-002',p1,'live_data_streams','Live Data Streams',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'scada_telemetry_streams','SCADA / Telemetry Streams','/ic3/realtime/p1/scada-streams',1,'coming_soon'),(g,'iot_sensor_streams','IoT Sensor Streams','/ic3/realtime/p1/iot-streams',2,'coming_soon'),(g,'smart_meter_streams','Smart-Meter Streams','/ic3/realtime/p1/meter-streams',3,'coming_soon'),(g,'third_party_feeds','Third-party Feeds (Weather etc.)','/ic3/realtime/p1/third-party',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-002',p1,'device_tag_mgmt','Device & Tag Management',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'device_registry','Device Registry','/ic3/realtime/p1/device-registry',1,'coming_soon'),(g,'tag_signal_catalog','Tag / Signal Catalog','/ic3/realtime/p1/tag-catalog',2,'coming_soon'),(g,'polling_sampling_config','Polling / Sampling Config','/ic3/realtime/p1/polling-config',3,'coming_soon'),(g,'edge_gateway_status','Edge Gateway Status','/ic3/realtime/p1/gateway-status',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-002',p1,'stream_health','Stream Health',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'connectivity_status','Connectivity Status','/ic3/realtime/p1/connectivity',1,'coming_soon'),(g,'data_latency_monitoring','Data-Latency Monitoring','/ic3/realtime/p1/latency',2,'coming_soon'),(g,'missing_data_detection','Missing-Data Detection','/ic3/realtime/p1/missing-data',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-002',p1,'data_pipeline','Data Pipeline',4) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'realtime_ingestion','Real-time Ingestion','/ic3/realtime/p1/ingestion',1,'coming_soon'),(g,'stream_validation','Stream Validation','/ic3/realtime/p1/validation',2,'coming_soon'),(g,'historian_timeseries','Historian / Time-series Store','/ic3/realtime/p1/historian',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;

    -- ━━━━━━━ P2: Information with Intelligence (The live picture) ━━━━━━━
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-002',p2,'live_ops_dashboard','Live Operations Dashboard',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'realtime_network_overview','Real-time Network Overview','/ic3/realtime/p2/network-overview',1,'coming_soon'),(g,'live_kpi_tiles','Live KPI Tiles','/ic3/realtime/p2/kpi-tiles',2,'coming_soon'),(g,'map_based_live_view','Map-based Live View','/ic3/realtime/p2/live-map',3,'coming_soon'),(g,'custom_live_dashboards','Custom Live Dashboards','/ic3/realtime/p2/custom-dashboards',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-002',p2,'live_parameter_views','Live Parameter Views',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'flow_pressure_live','Flow / Pressure (Live)','/ic3/realtime/p2/flow-pressure',1,'coming_soon'),(g,'level_quality_live','Level / Quality (Live)','/ic3/realtime/p2/level-quality',2,'coming_soon'),(g,'pump_asset_status','Pump / Asset Live Status','/ic3/realtime/p2/asset-status',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-002',p2,'event_alert_feed','Event & Alert Feed',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'live_alarm_feed','Live Alarm Feed','/ic3/realtime/p2/alarm-feed',1,'coming_soon'),(g,'event_timeline','Event Timeline','/ic3/realtime/p2/timeline',2,'coming_soon'),(g,'acknowledgement_status','Acknowledgement Status','/ic3/realtime/p2/ack-status',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-002',p2,'watchlists','Watchlists',4) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'critical_asset_watchlist','Critical-Asset Watchlist','/ic3/realtime/p2/critical-assets',1,'coming_soon'),(g,'threshold_monitors','Threshold Monitors','/ic3/realtime/p2/thresholds',2,'coming_soon') ON CONFLICT DO NOTHING; END IF;

    -- ━━━━━━━ P3: Analytics & Network Intelligence (Streaming analytics) ━━━━━━━
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-002',p3,'realtime_analytics','Real-time Analytics',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'streaming_aggregations','Streaming Aggregations','/ic3/realtime/p3/aggregations',1,'coming_soon'),(g,'live_trend_analysis','Live Trend Analysis','/ic3/realtime/p3/live-trends',2,'coming_soon'),(g,'rolling_kpis','Rolling KPIs','/ic3/realtime/p3/rolling-kpis',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-002',p3,'anomaly_pattern','Anomaly & Pattern',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'realtime_anomaly_detection','Real-time Anomaly Detection','/ic3/realtime/p3/anomaly-detection',1,'coming_soon'),(g,'spike_drift_detection','Spike / Drift Detection','/ic3/realtime/p3/spike-drift',2,'coming_soon'),(g,'cross_stream_correlation','Cross-Stream Correlation','/ic3/realtime/p3/correlation',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-002',p3,'event_analytics','Event Analytics',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'event_frequency_analysis','Event-Frequency Analysis','/ic3/realtime/p3/event-frequency',1,'coming_soon'),(g,'alarm_flood_analysis','Alarm-Flood Analysis','/ic3/realtime/p3/alarm-flood',2,'coming_soon'),(g,'root_cause_correlation','Root-Cause Correlation','/ic3/realtime/p3/root-cause',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-002',p3,'performance_analytics','Performance',4) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'sla_uptime_monitoring','SLA / Uptime Monitoring','/ic3/realtime/p3/sla-uptime',1,'coming_soon'),(g,'response_time_analytics','Response-Time Analytics','/ic3/realtime/p3/response-time',2,'coming_soon') ON CONFLICT DO NOTHING; END IF;

    -- ━━━━━━━ P4: AI Models & Prediction (Predictive operations) ━━━━━━━
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-002',p4,'realtime_prediction','Real-time Prediction',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'short_horizon_forecasting','Short-horizon Forecasting','/ic3/realtime/p4/short-horizon',1,'coming_soon'),(g,'live_demand_prediction','Live Demand Prediction','/ic3/realtime/p4/demand-prediction',2,'coming_soon'),(g,'next_value_estimation','Next-value Estimation','/ic3/realtime/p4/next-value',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-002',p4,'early_warning','Early Warning',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'predictive_alerts','Predictive Alerts','/ic3/realtime/p4/predictive-alerts',1,'coming_soon'),(g,'failure_early_warning','Failure Early-Warning','/ic3/realtime/p4/failure-warning',2,'coming_soon'),(g,'threshold_breach_prediction','Threshold-Breach Prediction','/ic3/realtime/p4/threshold-prediction',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-002',p4,'streaming_ml','Streaming ML',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'online_anomaly_models','Online Anomaly Models','/ic3/realtime/p4/online-models',1,'coming_soon'),(g,'adaptive_thresholds','Adaptive Thresholds','/ic3/realtime/p4/adaptive-thresholds',2,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-002',p4,'optimization_realtime','Optimization',4) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'realtime_setpoint_suggestions','Real-time Setpoint Suggestions','/ic3/realtime/p4/setpoint-suggestions',1,'coming_soon'),(g,'dynamic_prioritization','Dynamic Prioritization','/ic3/realtime/p4/prioritization',2,'coming_soon') ON CONFLICT DO NOTHING; END IF;

    -- ━━━━━━━ P5: Operations & Automation (Command & control) ━━━━━━━
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-002',p5,'command_control','Command & Control',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'ops_command_center','Operations Command Center','/ic3/realtime/p5/command-center',1,'coming_soon'),(g,'remote_control_actions','Remote Control Actions','/ic3/realtime/p5/remote-control',2,'coming_soon'),(g,'realtime_dispatch','Real-time Dispatch','/ic3/realtime/p5/dispatch',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-002',p5,'alarm_management','Alarm Management',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'alarm_console','Alarm Console','/ic3/realtime/p5/alarm-console',1,'coming_soon'),(g,'ack_escalation','Acknowledgement / Escalation','/ic3/realtime/p5/escalation',2,'coming_soon'),(g,'alarm_shelving','Alarm Shelving / Suppression','/ic3/realtime/p5/shelving',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-002',p5,'automated_response','Automated Response',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'rule_trigger_engine','Rule / Trigger Engine','/ic3/realtime/p5/rule-engine',1,'coming_soon'),(g,'automated_notifications','Automated Notifications','/ic3/realtime/p5/notifications',2,'coming_soon'),(g,'closed_loop_actions','Closed-loop Actions','/ic3/realtime/p5/closed-loop',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-002',p5,'incident_operations','Incident Operations',4) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'incident_console','Live Incident Console','/ic3/realtime/p5/incident-console',1,'coming_soon'),(g,'response_workflow','Response Workflow','/ic3/realtime/p5/response-workflow',2,'coming_soon'),(g,'shift_handover','Shift Handover','/ic3/realtime/p5/shift-handover',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;

    -- ━━━━━━━ P6: Citizen, Reporting & Governance (Outward-facing) ━━━━━━━
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-002',p6,'public_live_info','Public Live Info',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'public_status_page','Public Status Page','/ic3/realtime/p6/status-page',1,'coming_soon'),(g,'outage_live_updates','Outage Live Updates','/ic3/realtime/p6/outage-updates',2,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-002',p6,'reporting_outcomes','Reporting & Outcomes',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'realtime_report_snapshots','Real-time Report Snapshots','/ic3/realtime/p6/report-snapshots',1,'coming_soon'),(g,'event_alarm_reports','Event / Alarm Reports','/ic3/realtime/p6/event-reports',2,'coming_soon'),(g,'uptime_sla_reports','Uptime / SLA Reports','/ic3/realtime/p6/uptime-reports',3,'coming_soon'),(g,'custom_live_exports','Custom Live Exports','/ic3/realtime/p6/exports',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-002',p6,'administration_trust','Administration & Trust',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'alarm_threshold_config','Alarm / Threshold Configuration','/ic3/realtime/p6/config',1,'coming_soon'),(g,'user_role_management','User & Role Management','/ic3/realtime/p6/user-roles',2,'coming_soon'),(g,'audit_logs','Audit Logs / Data Lineage','/ic3/realtime/p6/audit-logs',3,'coming_soon'),(g,'dashboard_configuration','Dashboard Configuration','/ic3/realtime/p6/dashboard-config',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;

END $$;
`
	_, _ = pool.Exec(ctx, seedSYS002)

	// Clear old SYS-010 data and seed with correct structure
	fmt.Println("Clearing old SYS-010 data...")
	_, _ = pool.Exec(ctx, `DELETE FROM ic3_nav_item WHERE group_id IN (SELECT id FROM ic3_nav_group WHERE system_id = 'SYS-010')`)
	_, _ = pool.Exec(ctx, `DELETE FROM ic3_nav_group WHERE system_id = 'SYS-010'`)

	fmt.Println("Seeding SYS-010 (Rural Water Supply) with document structure...")
	seedSYS010 := `
DO $$
DECLARE
    p1 INT; p2 INT; p3 INT; p4 INT; p5 INT; p6 INT;
    g INT;
BEGIN
    SELECT id INTO p1 FROM ic3_nav_layer WHERE code='P1';
    SELECT id INTO p2 FROM ic3_nav_layer WHERE code='P2';
    SELECT id INTO p3 FROM ic3_nav_layer WHERE code='P3';
    SELECT id INTO p4 FROM ic3_nav_layer WHERE code='P4';
    SELECT id INTO p5 FROM ic3_nav_layer WHERE code='P5';
    SELECT id INTO p6 FROM ic3_nav_layer WHERE code='P6';

    -- ━━━━━━━ P1: Data & Field Intelligence (Scheme-data foundation) ━━━━━━━
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-010',p1,'scheme_asset_inventory','Scheme & Asset Inventory',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'village_schemes','Village Water-Supply Schemes','/ic3/rws/p1/schemes',1,'coming_soon'),(g,'source_works','Source Works (Borewells / Intake)','/ic3/rws/p1/source-works',2,'coming_soon'),(g,'ohsr_glsr_sumps','OHSR / GLSR / Sumps','/ic3/rws/p1/tanks',3,'coming_soon'),(g,'distribution_network','Village Distribution Network','/ic3/rws/p1/distribution',4,'coming_soon'),(g,'fhtc_connections','FHTC (Household Tap Connections)','/ic3/rws/p1/fhtc',5,'coming_soon'),(g,'standposts_handpumps','Public Standposts / Handpumps','/ic3/rws/p1/standposts',6,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-010',p1,'field_instrumentation_iot','Field Instrumentation / IoT',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'bulk_flow_meters','Scheme Bulk Flow Meters','/ic3/rws/p1/flow-meters',1,'coming_soon'),(g,'tank_level_sensors','Tank Level Sensors','/ic3/rws/p1/tank-sensors',2,'coming_soon'),(g,'household_meters','Household / Standpost Meters','/ic3/rws/p1/household-meters',3,'coming_soon'),(g,'pump_sensors','Pump-Run Sensors','/ic3/rws/p1/pump-sensors',4,'coming_soon'),(g,'quality_sensors','Chlorine / Quality Sensors','/ic3/rws/p1/quality-sensors',5,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-010',p1,'field_operations_data','Field Operations Data',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'operator_logs','Pump-Operator Logs','/ic3/rws/p1/operator-logs',1,'coming_soon'),(g,'village_survey','Village Survey Data','/ic3/rws/p1/survey-data',2,'coming_soon'),(g,'fhtc_verification','FHTC Verification','/ic3/rws/p1/fhtc-verify',3,'coming_soon'),(g,'asset_inspection','Asset Inspection','/ic3/rws/p1/inspections',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-010',p1,'data_acquisition_telemetry','Data Acquisition & Telemetry',4) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'iot_telemetry_feed','IoT / Telemetry Feed','/ic3/rws/p1/iot-feed',1,'coming_soon'),(g,'manual_mobile_entry','Manual / Mobile Entry','/ic3/rws/p1/mobile-entry',2,'coming_soon'),(g,'data_validation','Data Validation & Gap-Filling','/ic3/rws/p1/validation',3,'coming_soon'),(g,'offline_sync','Offline Sync','/ic3/rws/p1/offline-sync',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;

    -- ━━━━━━━ P2: Information with Intelligence (The rural picture) ━━━━━━━
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-010',p2,'scheme_monitoring_dashboard','Scheme Monitoring Dashboard',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'scheme_supply_status','Scheme-wise Supply Status','/ic3/rws/p2/scheme-status',1,'coming_soon'),(g,'village_coverage_dash','Village Coverage Dashboard','/ic3/rws/p2/coverage-dash',2,'coming_soon'),(g,'tank_pumping_status','Tank Level / Pumping Status','/ic3/rws/p2/tank-status',3,'coming_soon'),(g,'daily_supply_hours','Daily Supply Hours','/ic3/rws/p2/supply-hours',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-010',p2,'fhtc_coverage','FHTC & Coverage',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'fhtc_coverage_pct','FHTC Coverage (%)','/ic3/rws/p2/fhtc-coverage',1,'coming_soon'),(g,'connection_status','Household Connection Status','/ic3/rws/p2/connection-status',2,'coming_soon'),(g,'habitation_coverage','Habitation Coverage','/ic3/rws/p2/habitation',3,'coming_soon'),(g,'saturation_tracking','Saturation Tracking (JJM)','/ic3/rws/p2/jjm-saturation',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-010',p2,'service_levels','Service Levels',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'per_capita_supply','Per-Capita Supply (lpcd)','/ic3/rws/p2/per-capita',1,'coming_soon'),(g,'supply_regularity','Supply Regularity','/ic3/rws/p2/regularity',2,'coming_soon'),(g,'potability_status','Potability Status','/ic3/rws/p2/potability',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-010',p2,'scheme_functionality','Scheme Functionality',4) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'functionality_status','Functionality Status','/ic3/rws/p2/functionality',1,'coming_soon'),(g,'non_functional_schemes','Non-functional Schemes','/ic3/rws/p2/non-functional',2,'coming_soon'),(g,'source_sustainability','Source Sustainability Status','/ic3/rws/p2/source-sustainability',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;

    -- ━━━━━━━ P3: Analytics & Network Intelligence (Rural diagnostics) ━━━━━━━
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-010',p3,'coverage_equity_analytics','Coverage & Equity Analytics',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'coverage_gap_analysis','Coverage-Gap Analysis','/ic3/rws/p3/coverage-gap',1,'coming_soon'),(g,'habitation_equity','Habitation-wise Equity','/ic3/rws/p3/equity',2,'coming_soon'),(g,'underserved_mapping','Underserved-Village Mapping','/ic3/rws/p3/underserved',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-010',p3,'functionality_analytics','Functionality Analytics',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'downtime_analysis','Scheme-Downtime Analysis','/ic3/rws/p3/downtime',1,'coming_soon'),(g,'pump_performance','Pump Performance Analysis','/ic3/rws/p3/pump-performance',2,'coming_soon'),(g,'service_benchmarking','Service-Level Benchmarking','/ic3/rws/p3/benchmarking',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-010',p3,'source_sustainability_p3','Source Sustainability',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'yield_vs_demand','Source Yield vs Demand','/ic3/rws/p3/yield-demand',1,'coming_soon'),(g,'seasonal_stress','Seasonal-Stress Analysis','/ic3/rws/p3/seasonal-stress',2,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-010',p3,'quality_analytics_p3','Quality Analytics',4) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'quality_compliance','Quality-Compliance Trends','/ic3/rws/p3/quality-compliance',1,'coming_soon'),(g,'contamination_hotspots','Contamination Hotspots','/ic3/rws/p3/contamination',2,'coming_soon') ON CONFLICT DO NOTHING; END IF;

    -- ━━━━━━━ P4: AI Models & Prediction (Forecasting) ━━━━━━━
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-010',p4,'demand_supply_forecast','Demand & Supply Forecasting',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'village_demand_forecast','Village Demand Forecast','/ic3/rws/p4/demand-forecast',1,'coming_soon'),(g,'seasonal_supply_forecast','Seasonal Supply Forecast','/ic3/rws/p4/seasonal-forecast',2,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-010',p4,'functionality_prediction','Functionality Prediction',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'scheme_failure_pred','Scheme-Failure Prediction','/ic3/rws/p4/scheme-failure',1,'coming_soon'),(g,'pump_failure_pred','Pump-Failure Prediction','/ic3/rws/p4/pump-failure',2,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-010',p4,'sustainability_modeling','Sustainability Modeling',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'source_depletion_risk','Source-Depletion Risk','/ic3/rws/p4/depletion-risk',1,'coming_soon'),(g,'water_stress_pred','Water-Stress Prediction','/ic3/rws/p4/water-stress',2,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-010',p4,'optimization_rws','Optimization',4) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'supply_scheduling_optim','Supply-Scheduling Optimization','/ic3/rws/p4/scheduling-optim',1,'coming_soon'),(g,'tanker_deployment_optim','Tanker-Deployment Optimization','/ic3/rws/p4/tanker-optim',2,'coming_soon') ON CONFLICT DO NOTHING; END IF;

    -- ━━━━━━━ P5: Operations & Automation (Running schemes) ━━━━━━━
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-010',p5,'supply_operations','Supply Operations',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'pump_scheduling','Pump Scheduling','/ic3/rws/p5/pump-scheduling',1,'coming_soon'),(g,'tank_filling_control','Tank-Filling Control','/ic3/rws/p5/tank-filling',2,'coming_soon'),(g,'supply_hour_management','Supply-Hour Management','/ic3/rws/p5/supply-hours',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-010',p5,'maintenance_work_mgmt','Maintenance & Work Management',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'om_work_orders','O&M Work Orders','/ic3/rws/p5/work-orders',1,'coming_soon'),(g,'maintenance_schedules','Scheme Maintenance Schedules','/ic3/rws/p5/schedules',2,'coming_soon'),(g,'spare_asset_mgmt','Spare / Asset Management','/ic3/rws/p5/spare-asset',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-010',p5,'tanker_alternate_supply','Tanker / Alternate Supply',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'tanker_dispatch','Tanker Dispatch','/ic3/rws/p5/tanker-dispatch',1,'coming_soon'),(g,'emergency_supply','Emergency-Supply Planning','/ic3/rws/p5/emergency-supply',2,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-010',p5,'alarms_automation','Alarms & Automation',4) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'pump_level_alarms','Pump / Level Alarms','/ic3/rws/p5/pump-alarms',1,'coming_soon'),(g,'quality_breach_alarms','Quality-Breach Alarms','/ic3/rws/p5/quality-alarms',2,'coming_soon'),(g,'rule_engine_triggers','Rule Engine / Triggers','/ic3/rws/p5/rule-engine',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;

    -- ━━━━━━━ P6: Citizen, Reporting & Governance (Outward-facing) ━━━━━━━
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-010',p6,'community_citizen','Community & Citizen',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'village_grievance','Village Grievance / Requests','/ic3/rws/p6/grievance',1,'coming_soon'),(g,'vwsc_portal','VWSC / Community Portal','/ic3/rws/p6/portal',2,'coming_soon'),(g,'tariff_contribution','Tariff / Contribution Tracking','/ic3/rws/p6/tariff',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-010',p6,'reporting_outcomes','Reporting & Outcomes',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'jjm_fhtc_reports','JJM / FHTC Progress Reports','/ic3/rws/p6/jjm-reports',1,'coming_soon'),(g,'functionality_reports','Scheme-Functionality Reports','/ic3/rws/p6/functionality-reports',2,'coming_soon'),(g,'quality_compliance_reports','Quality-Compliance Reports','/ic3/rws/p6/compliance-reports',3,'coming_soon'),(g,'custom_reports','Custom / Scheduled Reports','/ic3/rws/p6/custom-reports',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-010',p6,'administration_trust','Administration & Trust',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'scheme_config','Scheme / Village Configuration','/ic3/rws/p6/config',1,'coming_soon'),(g,'user_role_management','User & Role Management','/ic3/rws/p6/user-roles',2,'coming_soon'),(g,'audit_logs','Audit Logs / Data Lineage','/ic3/rws/p6/audit-logs',3,'coming_soon'),(g,'master_data_standards','Master Data & Standards','/ic3/rws/p6/standards',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;

END $$;
`
	_, _ = pool.Exec(ctx, seedSYS010)

	// Clear old SYS-006 data and seed with correct structure
	fmt.Println("Clearing old SYS-006 data...")
	_, _ = pool.Exec(ctx, `DELETE FROM ic3_nav_item WHERE group_id IN (SELECT id FROM ic3_nav_group WHERE system_id = 'SYS-006')`)
	_, _ = pool.Exec(ctx, `DELETE FROM ic3_nav_group WHERE system_id = 'SYS-006'`)

	fmt.Println("Seeding SYS-006 (STP) with document structure...")
	seedSYS006 := `
DO $$
DECLARE
    p1 INT; p2 INT; p3 INT; p4 INT; p5 INT; p6 INT;
    g INT;
BEGIN
    SELECT id INTO p1 FROM ic3_nav_layer WHERE code='P1';
    SELECT id INTO p2 FROM ic3_nav_layer WHERE code='P2';
    SELECT id INTO p3 FROM ic3_nav_layer WHERE code='P3';
    SELECT id INTO p4 FROM ic3_nav_layer WHERE code='P4';
    SELECT id INTO p5 FROM ic3_nav_layer WHERE code='P5';
    SELECT id INTO p6 FROM ic3_nav_layer WHERE code='P6';

    -- P1: Data & Field Intelligence (Plant-data foundation)
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-006',p1,'plant_asset_registry','Plant Asset Registry',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'process_units','Process Units (Screen, Grit, Aeration, Clarifier, Digester)','/ic3/stp/p1/process-units',1,'coming_soon'),(g,'blowers_aerators','Blowers & Aerators','/ic3/stp/p1/blowers',2,'coming_soon'),(g,'pumps','Pumps (Influent / RAS / WAS)','/ic3/stp/p1/pumps',3,'coming_soon'),(g,'disinfection','Disinfection System','/ic3/stp/p1/disinfection',4,'coming_soon'),(g,'sludge_handling','Sludge-Handling Units','/ic3/stp/p1/sludge-handling',5,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-006',p1,'process_instrumentation','Process Instrumentation / IoT',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'flow_meters','Influent / Effluent Flow Meters','/ic3/stp/p1/flow-meters',1,'coming_soon'),(g,'do_sensors','Dissolved-Oxygen (DO) Sensors','/ic3/stp/p1/do-sensors',2,'coming_soon'),(g,'online_analyzers','Online Analyzers (BOD / COD / TSS / pH)','/ic3/stp/p1/analyzers',3,'coming_soon'),(g,'mlss_sludge_sensors','MLSS / Sludge Sensors','/ic3/stp/p1/mlss-sensors',4,'coming_soon'),(g,'level_pressure','Level / Pressure Sensors','/ic3/stp/p1/level-pressure',5,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-006',p1,'lab_sampling','Laboratory & Sampling',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'lab_test_results','Lab Test Results (BOD, COD, TSS)','/ic3/stp/p1/lab-results',1,'coming_soon'),(g,'sampling_registry','Sampling-Point Registry','/ic3/stp/p1/sampling-registry',2,'coming_soon'),(g,'sludge_tests','Sludge-Test Records','/ic3/stp/p1/sludge-tests',3,'coming_soon'),(g,'sampling_schedules','Sampling Schedules','/ic3/stp/p1/schedules',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-006',p1,'data_acquisition_scada','Data Acquisition & SCADA',4) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'scada_plc_feed','SCADA / PLC Feed','/ic3/stp/p1/scada-feed',1,'coming_soon'),(g,'manual_logsheet','Manual Logsheet Entry','/ic3/stp/p1/manual-entry',2,'coming_soon'),(g,'data_validation','Data Validation & Gap-Filling','/ic3/stp/p1/validation',3,'coming_soon'),(g,'historian_sync','Historian Sync','/ic3/stp/p1/historian',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;

    -- P2: Information with Intelligence (The plant picture)
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-006',p2,'plant_overview_dashboard','Plant Overview Dashboard',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'process_mimic','Process Mimic Diagram','/ic3/stp/p2/mimic',1,'coming_soon'),(g,'effluent_output','Treated-Effluent Output','/ic3/stp/p2/effluent',2,'coming_soon'),(g,'plant_status','Plant Status (Units Online)','/ic3/stp/p2/plant-status',3,'coming_soon'),(g,'load_vs_capacity','Load vs Capacity','/ic3/stp/p2/load-capacity',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-006',p2,'process_monitoring','Process Monitoring',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'aeration_do','Aeration / DO Status','/ic3/stp/p2/aeration',1,'coming_soon'),(g,'clarifier_perf','Clarifier Performance','/ic3/stp/p2/clarifier',2,'coming_soon'),(g,'sludge_status','Sludge (MLSS / SVI) Status','/ic3/stp/p2/sludge-status',3,'coming_soon'),(g,'disinfection_status','Disinfection Status','/ic3/stp/p2/disinfection-status',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-006',p2,'effluent_quality','Effluent Quality Monitoring',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'influent_vs_effluent','Influent vs Effluent (BOD / COD / TSS)','/ic3/stp/p2/quality-comparison',1,'coming_soon'),(g,'quality_trends','Online Quality Trends','/ic3/stp/p2/quality-trends',2,'coming_soon'),(g,'discharge_compliance','Discharge-Compliance Status','/ic3/stp/p2/compliance-status',3,'coming_soon'),(g,'quality_alerts','Quality Alerts','/ic3/stp/p2/quality-alerts',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-006',p2,'energy_consumables','Energy & Consumables',4) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'aeration_energy','Aeration Energy','/ic3/stp/p2/aeration-energy',1,'coming_soon'),(g,'chemical_polymer','Chemical / Polymer Use','/ic3/stp/p2/chemical-use',2,'coming_soon'),(g,'power_consumption','Power Consumption','/ic3/stp/p2/power',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;

    -- P3: Analytics & Network Intelligence (Plant diagnostics)
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-006',p3,'process_efficiency','Process Efficiency',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'treatment_efficiency','Treatment Efficiency (BOD / COD / TSS Removal)','/ic3/stp/p3/efficiency',1,'coming_soon'),(g,'aeration_efficiency','Aeration Efficiency','/ic3/stp/p3/aeration-eff',2,'coming_soon'),(g,'clarifier_efficiency','Clarifier / Settling Efficiency','/ic3/stp/p3/clarifier-eff',3,'coming_soon'),(g,'unit_benchmarking','Unit-Process Benchmarking','/ic3/stp/p3/benchmarking',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-006',p3,'sludge_analytics','Sludge Analytics',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'sludge_generation','Sludge-Generation Analysis','/ic3/stp/p3/sludge-gen',1,'coming_soon'),(g,'digester_perf','Digester Performance','/ic3/stp/p3/digester',2,'coming_soon'),(g,'biogas_yield','Biogas-Yield Analysis','/ic3/stp/p3/biogas',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-006',p3,'energy_analytics','Energy Analytics',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'specific_energy','Specific Energy (kWh/ML)','/ic3/stp/p3/specific-energy',1,'coming_soon'),(g,'aeration_energy_analysis','Aeration-Energy Analysis','/ic3/stp/p3/aeration-analysis',2,'coming_soon'),(g,'blower_efficiency','Blower Efficiency','/ic3/stp/p3/blower-eff',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-006',p3,'compliance_analytics','Compliance Analytics',4) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'discharge_compliance_trends','Discharge-Compliance Trends','/ic3/stp/p3/compliance-trends',1,'coming_soon'),(g,'exceedance_analysis','Exceedance Analysis','/ic3/stp/p3/exceedance',2,'coming_soon'),(g,'load_analysis','Load Analysis','/ic3/stp/p3/load-analysis',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;

    -- P4: AI Models & Prediction (Predictive treatment)
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-006',p4,'quality_prediction','Quality Prediction',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'effluent_quality_pred','Effluent-Quality Prediction','/ic3/stp/p4/quality-pred',1,'coming_soon'),(g,'influent_load_forecast','Influent-Load Forecast','/ic3/stp/p4/load-forecast',2,'coming_soon'),(g,'compliance_risk_pred','Compliance-Risk Prediction','/ic3/stp/p4/risk-pred',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-006',p4,'process_optimization_ai','Process Optimization (AI)',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'aeration_do_optim','Aeration / DO Optimization','/ic3/stp/p4/aeration-optim',1,'coming_soon'),(g,'chemical_dose_optim','Chemical-Dose Optimization','/ic3/stp/p4/chemical-optim',2,'coming_soon'),(g,'sludge_age_optim','Sludge-Age Optimization','/ic3/stp/p4/sludge-age-optim',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-006',p4,'predictive_maintenance','Predictive Maintenance',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'equipment_failure_pred','Equipment-Failure Prediction','/ic3/stp/p4/equipment-pred',1,'coming_soon'),(g,'blower_pump_health','Blower / Pump Health','/ic3/stp/p4/pump-health',2,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-006',p4,'energy_optimization','Energy Optimization',4) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'aeration_energy_optim','Aeration-Energy Optimization','/ic3/stp/p4/energy-optim',1,'coming_soon'),(g,'load_based_control','Load-based Process Control','/ic3/stp/p4/load-control',2,'coming_soon') ON CONFLICT DO NOTHING; END IF;

    -- P5: Operations & Automation (Running the plant)
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-006',p5,'process_control_automation','Process Control & Automation',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'do_aeration_control','DO / Aeration Control','/ic3/stp/p5/do-control',1,'coming_soon'),(g,'sludge_control','Sludge (RAS / WAS) Control','/ic3/stp/p5/sludge-control',2,'coming_soon'),(g,'dosing_control','Dosing-Setpoint Control','/ic3/stp/p5/dosing',3,'coming_soon'),(g,'auto_sequencing','Auto Sequencing','/ic3/stp/p5/sequencing',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-006',p5,'sludge_management','Sludge Management',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'sludge_wasting','Sludge-Wasting Schedule','/ic3/stp/p5/wasting',1,'coming_soon'),(g,'dewatering_ops','Dewatering Operations','/ic3/stp/p5/dewatering',2,'coming_soon'),(g,'biogas_digester','Biogas / Digester Operations','/ic3/stp/p5/biogas-ops',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-006',p5,'maintenance_work_mgmt','Maintenance & Work Management',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'preventive_maintenance','Preventive Maintenance','/ic3/stp/p5/maintenance',1,'coming_soon'),(g,'work_orders','Work Orders','/ic3/stp/p5/work-orders',2,'coming_soon'),(g,'analyzer_calibration','Analyzer Calibration Schedules','/ic3/stp/p5/calibration',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-006',p5,'alarms_incident_mgmt','Alarms & Incident Management',4) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'process_alarm_console','Process Alarm Console','/ic3/stp/p5/alarm-console',1,'coming_soon'),(g,'compliance_breach_response','Compliance-Breach Response','/ic3/stp/p5/compliance-response',2,'coming_soon'),(g,'sop_escalation','SOP / Escalation','/ic3/stp/p5/escalation',3,'coming_soon'),(g,'shift_logbook','Shift Logbook','/ic3/stp/p5/logbook',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;

    -- P6: Citizen, Reporting & Governance (Outward-facing)
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-006',p6,'stakeholder_communication','Stakeholder & Communication',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'discharge_disclosure','Discharge / Compliance Disclosure','/ic3/stp/p6/disclosure',1,'coming_soon'),(g,'reuse_info','Reuse-Availability Info','/ic3/stp/p6/reuse',2,'coming_soon'),(g,'grievance_inputs','Grievance Inputs','/ic3/stp/p6/grievance',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-006',p6,'reporting_compliance','Reporting & Compliance',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'statutory_reports','Statutory Discharge Reports (CPCB / SPCB)','/ic3/stp/p6/statutory',1,'coming_soon'),(g,'operation_reports','Production / Operation Reports','/ic3/stp/p6/operation',2,'coming_soon'),(g,'energy_chemical_reports','Energy / Chemical Reports','/ic3/stp/p6/energy-chemical',3,'coming_soon'),(g,'custom_reports','Custom / Scheduled Reports','/ic3/stp/p6/custom',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-006',p6,'administration_trust','Administration & Trust',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'plant_config','Plant / Process Configuration','/ic3/stp/p6/config',1,'coming_soon'),(g,'user_roles','User & Role Management (Operators / Shifts)','/ic3/stp/p6/user-roles',2,'coming_soon'),(g,'audit_logs','Audit Logs / Data Lineage','/ic3/stp/p6/audit',3,'coming_soon'),(g,'standards_master','Standards & Setpoint Master Data','/ic3/stp/p6/standards',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;

END $$;
`
	_, _ = pool.Exec(ctx, seedSYS006)

	// SYS-003: Water Quality
	fmt.Println("Seeding SYS-003 (Water Quality)...")
	seedSYS003 := `
DO $$
DECLARE
    p1 INT; p2 INT; p3 INT; p4 INT; p5 INT; p6 INT;
    g INT;
BEGIN
    SELECT id INTO p1 FROM ic3_nav_layer WHERE code='P1';
    SELECT id INTO p2 FROM ic3_nav_layer WHERE code='P2';
    SELECT id INTO p3 FROM ic3_nav_layer WHERE code='P3';
    SELECT id INTO p4 FROM ic3_nav_layer WHERE code='P4';
    SELECT id INTO p5 FROM ic3_nav_layer WHERE code='P5';
    SELECT id INTO p6 FROM ic3_nav_layer WHERE code='P6';
    DELETE FROM ic3_nav_item WHERE group_id IN (SELECT id FROM ic3_nav_group WHERE system_id = 'SYS-003');
    DELETE FROM ic3_nav_group WHERE system_id = 'SYS-003';

    -- P1: Data & Field Intelligence
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-003',p1,'sampling_monitoring_network','Sampling & Monitoring Network',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'sampling_point_registry','Sampling-Point Registry (Source / Plant / Network / Tap)','/ic3/wq/p1/sampling-registry',1,'coming_soon'),(g,'online_wq_sensors','Online WQ Sensor Network','/ic3/wq/p1/sensor-network',2,'coming_soon'),(g,'monitoring_stations','Monitoring-Station Inventory','/ic3/wq/p1/stations',3,'coming_soon'),(g,'sampling_plans','Sampling Plans / Schedules','/ic3/wq/p1/plans',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-003',p1,'laboratory_data','Laboratory Data',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'lab_test_entry','Lab Test-Result Entry','/ic3/wq/p1/lab-entry',1,'coming_soon'),(g,'test_method_catalog','Test-Method / Parameter Catalog','/ic3/wq/p1/catalog',2,'coming_soon'),(g,'lims_integration','LIMS Integration','/ic3/wq/p1/lims',3,'coming_soon'),(g,'chain_custody','Chain-of-Custody Records','/ic3/wq/p1/custody',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-003',p1,'field_testing_data','Field Testing Data',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'ftk_results','Field Test Kit (FTK) Results','/ic3/wq/p1/ftk',1,'coming_soon'),(g,'mobile_sampling','Mobile Sampling Data','/ic3/wq/p1/mobile-sampling',2,'coming_soon'),(g,'crowdsourced_tests','Citizen / Crowdsourced Tests','/ic3/wq/p1/crowdsourced',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-003',p1,'data_acquisition_validation','Data Acquisition & Validation',4) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'online_analyzer_feed','Online-Analyzer Feed','/ic3/wq/p1/analyzer-feed',1,'coming_soon'),(g,'manual_mobile_entry','Manual / Mobile Entry','/ic3/wq/p1/manual-entry',2,'coming_soon'),(g,'data_validation_qaqc','Data Validation & QA/QC','/ic3/wq/p1/validation',3,'coming_soon'),(g,'historian_sync','Historian Sync','/ic3/wq/p1/historian',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;

    -- P2: Information with Intelligence
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-003',p2,'wq_dashboard','Water Quality Dashboard',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'system_wide_status','System-wide WQ Status','/ic3/wq/p2/status',1,'coming_soon'),(g,'parameter_overview','Parameter-wise Overview','/ic3/wq/p2/parameters',2,'coming_soon'),(g,'compliance_heatmap','Compliance Heatmap','/ic3/wq/p2/heatmap',3,'coming_soon'),(g,'sampling_coverage','Sampling Coverage','/ic3/wq/p2/coverage',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-003',p2,'parameter_monitoring','Parameter Monitoring',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'physical_parameters','Physical (Turbidity, Colour, pH)','/ic3/wq/p2/physical',1,'coming_soon'),(g,'chemical_parameters','Chemical (Cl, Fluoride, Nitrate, Metals)','/ic3/wq/p2/chemical',2,'coming_soon'),(g,'microbiological','Microbiological (Coliform, E. coli)','/ic3/wq/p2/micro',3,'coming_soon'),(g,'online_trends','Online Parameter Trends','/ic3/wq/p2/trends',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-003',p2,'compliance_views','Compliance Views',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'bis_10500_compliance','BIS 10500 Compliance Status','/ic3/wq/p2/bis-compliance',1,'coming_soon'),(g,'exceedance_map','Exceedance Map','/ic3/wq/p2/exceedance',2,'coming_soon'),(g,'potability_status','Potability Status','/ic3/wq/p2/potability',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-003',p2,'quality_alerts','Quality Alerts',4) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'realtime_alerts','Real-time WQ Alerts','/ic3/wq/p2/alerts',1,'coming_soon'),(g,'contamination_indicators','Contamination Indicators','/ic3/wq/p2/indicators',2,'coming_soon'),(g,'boil_water_advisory','Boil-Water Advisory Status','/ic3/wq/p2/boil-water',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;

    -- P3: Analytics & Network Intelligence
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-003',p3,'compliance_analytics','Compliance Analytics',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'compliance_trends','Compliance-Rate Trends','/ic3/wq/p3/trends',1,'coming_soon'),(g,'exceedance_analysis','Parameter-Exceedance Analysis','/ic3/wq/p3/exceedance',2,'coming_soon'),(g,'standards_benchmarking','Standards Benchmarking','/ic3/wq/p3/benchmarking',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-003',p3,'spatial_source_analytics','Spatial & Source Analytics',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'hotspot_mapping','Contamination Hotspot Mapping','/ic3/wq/p3/hotspots',1,'coming_soon'),(g,'source_analysis','Source-wise Quality Analysis','/ic3/wq/p3/source-analysis',2,'coming_soon'),(g,'network_degradation','Network Quality Degradation','/ic3/wq/p3/degradation',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-003',p3,'trend_correlation','Trend & Correlation',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'seasonal_trends','Seasonal Quality Trends','/ic3/wq/p3/seasonal',1,'coming_soon'),(g,'parameter_correlation','Parameter Correlation','/ic3/wq/p3/correlation',2,'coming_soon'),(g,'chlorine_decay','Residual-Chlorine Decay Analysis','/ic3/wq/p3/chlorine-decay',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-003',p3,'risk_analytics','Risk Analytics',4) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'risk_index','Water-Quality Risk Index','/ic3/wq/p3/risk-index',1,'coming_soon'),(g,'vulnerability_analysis','Vulnerability Analysis','/ic3/wq/p3/vulnerability',2,'coming_soon') ON CONFLICT DO NOTHING; END IF;

    -- P4: AI Models & Prediction
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-003',p4,'quality_prediction','Quality Prediction',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'parameter_forecasting','Parameter Forecasting','/ic3/wq/p4/forecast',1,'coming_soon'),(g,'contamination_prediction','Contamination Prediction','/ic3/wq/p4/contamination-pred',2,'coming_soon'),(g,'chlorine_prediction','Residual-Chlorine Prediction','/ic3/wq/p4/chlorine-pred',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-003',p4,'event_detection','Event Detection',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'contamination_event','Contamination-Event Detection','/ic3/wq/p4/event-detection',1,'coming_soon'),(g,'anomaly_detection','WQ Anomaly Detection','/ic3/wq/p4/anomalies',2,'coming_soon'),(g,'outbreak_warning','Outbreak Early-Warning','/ic3/wq/p4/outbreak',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-003',p4,'spread_modeling','Spread Modeling',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'spread_model','Contaminant-Spread Modeling','/ic3/wq/p4/spread',1,'coming_soon'),(g,'source_identification','Source Identification (Back-tracking)','/ic3/wq/p4/source-id',2,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-003',p4,'optimization_wq','Optimization',4) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'sampling_location_optim','Sampling-Location Optimization','/ic3/wq/p4/sampling-optim',1,'coming_soon'),(g,'frequency_optim','Monitoring-Frequency Optimization','/ic3/wq/p4/frequency-optim',2,'coming_soon') ON CONFLICT DO NOTHING; END IF;

    -- P5: Operations & Automation
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-003',p5,'sampling_operations','Sampling Operations',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'sampling_execution','Sampling-Schedule Execution','/ic3/wq/p5/execution',1,'coming_soon'),(g,'field_assignment','Field-Sampling Assignment','/ic3/wq/p5/assignment',2,'coming_soon'),(g,'sample_tracking','Sample Tracking','/ic3/wq/p5/tracking',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-003',p5,'incident_response','Incident & Response',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'contamination_response','Contamination-Response Workflow','/ic3/wq/p5/response',1,'coming_soon'),(g,'boil_water_mgmt','Boil-Water Advisory Management','/ic3/wq/p5/boil-water-mgmt',2,'coming_soon'),(g,'flushing_remediation','Flushing / Remediation Orders','/ic3/wq/p5/remediation',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-003',p5,'qc_operations','Quality-Control Operations',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'resampling_workflow','Re-sampling Workflow','/ic3/wq/p5/resampling',1,'coming_soon'),(g,'corrective_action','Corrective-Action Tracking','/ic3/wq/p5/corrective-action',2,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-003',p5,'alarms_automation','Alarms & Automation',4) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'wq_breach_alarms','WQ-Breach Alarms','/ic3/wq/p5/breach-alarms',1,'coming_soon'),(g,'auto_escalation','Auto Escalation','/ic3/wq/p5/escalation',2,'coming_soon'),(g,'rule_engine','Rule Engine / Triggers','/ic3/wq/p5/rule-engine',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;

    -- P6: Citizen, Reporting & Governance
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-003',p6,'citizen_disclosure','Citizen & Disclosure',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'public_portal','Public Water-Quality Portal','/ic3/wq/p6/public-portal',1,'coming_soon'),(g,'quality_disclosure','Quality Disclosure / Reports','/ic3/wq/p6/disclosure',2,'coming_soon'),(g,'citizen_complaint','Citizen Complaint (Quality)','/ic3/wq/p6/complaint',3,'coming_soon'),(g,'boil_notifications','Boil-Water Notifications','/ic3/wq/p6/notifications',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-003',p6,'reporting_compliance','Reporting & Compliance',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'statutory_reports','Statutory WQ Reports (BIS / WHO / CPHEEO)','/ic3/wq/p6/statutory',1,'coming_soon'),(g,'compliance_dashboards','Compliance Dashboards','/ic3/wq/p6/dashboards',2,'coming_soon'),(g,'lab_nabl_reports','Lab / NABL Reports','/ic3/wq/p6/nabl',3,'coming_soon'),(g,'custom_reports','Custom / Scheduled Reports','/ic3/wq/p6/custom',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-003',p6,'administration_trust','Administration & Trust',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'parameter_config','Parameter / Standard Configuration','/ic3/wq/p6/parameter-config',1,'coming_soon'),(g,'sampling_plan_config','Sampling-Plan Configuration','/ic3/wq/p6/plan-config',2,'coming_soon'),(g,'user_roles','User & Role Management','/ic3/wq/p6/user-roles',3,'coming_soon'),(g,'audit_logs','Audit Logs / Data Lineage','/ic3/wq/p6/audit',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;

END $$;
`
	_, _ = pool.Exec(ctx, seedSYS003)

	// SYS-004: Water Treatment Plant
	fmt.Println("Seeding SYS-004 (WTP)...")
	seedSYS004 := `
DO $$
DECLARE
    p1 INT; p2 INT; p3 INT; p4 INT; p5 INT; p6 INT;
    g INT;
BEGIN
    SELECT id INTO p1 FROM ic3_nav_layer WHERE code='P1';
    SELECT id INTO p2 FROM ic3_nav_layer WHERE code='P2';
    SELECT id INTO p3 FROM ic3_nav_layer WHERE code='P3';
    SELECT id INTO p4 FROM ic3_nav_layer WHERE code='P4';
    SELECT id INTO p5 FROM ic3_nav_layer WHERE code='P5';
    SELECT id INTO p6 FROM ic3_nav_layer WHERE code='P6';
    DELETE FROM ic3_nav_item WHERE group_id IN (SELECT id FROM ic3_nav_group WHERE system_id = 'SYS-004');
    DELETE FROM ic3_nav_group WHERE system_id = 'SYS-004';

    -- P1: Data & Field Intelligence
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-004',p1,'plant_asset_registry','Plant Asset Registry',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'process_units','Process Units (Intake, Clariflocculator, Filters, CWR)','/ic3/wtp/p1/units',1,'coming_soon'),(g,'pumps_blowers','Pumps & Blowers','/ic3/wtp/p1/pumps',2,'coming_soon'),(g,'dosing_systems','Chemical Dosing Systems','/ic3/wtp/p1/dosing',3,'coming_soon'),(g,'valves_actuators','Valves & Actuators','/ic3/wtp/p1/valves',4,'coming_soon'),(g,'filter_media','Filter Beds / Media','/ic3/wtp/p1/filter-media',5,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-004',p1,'process_instrumentation','Process Instrumentation / IoT',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'flow_meters','Flow Meters (Raw / Treated)','/ic3/wtp/p1/flow-meters',1,'coming_soon'),(g,'level_sensors','Level Sensors (Tanks / Clarifiers / CWR)','/ic3/wtp/p1/level-sensors',2,'coming_soon'),(g,'online_analyzers','Online Analyzers (Turbidity, pH, Chlorine)','/ic3/wtp/p1/analyzers',3,'coming_soon'),(g,'pressure_headloss','Pressure / Headloss Sensors','/ic3/wtp/p1/pressure',4,'coming_soon'),(g,'dosing_sensors','Chemical-Dosing Sensors','/ic3/wtp/p1/dosing-sensors',5,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-004',p1,'lab_sampling','Laboratory & Sampling',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'lab_test_entry','Lab Test-Result Entry','/ic3/wtp/p1/lab-entry',1,'coming_soon'),(g,'sampling_registry','Sampling-Point Registry','/ic3/wtp/p1/sampling',2,'coming_soon'),(g,'jar_test_records','Jar-Test Records','/ic3/wtp/p1/jar-tests',3,'coming_soon'),(g,'sampling_schedules','Sampling Schedules','/ic3/wtp/p1/schedules',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-004',p1,'data_acquisition_scada','Data Acquisition & SCADA',4) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'scada_plc_feed','SCADA / PLC Feed','/ic3/wtp/p1/scada',1,'coming_soon'),(g,'manual_logsheet','Manual Logsheet Entry','/ic3/wtp/p1/manual',2,'coming_soon'),(g,'data_validation','Data Validation & Gap-Filling','/ic3/wtp/p1/validation',3,'coming_soon'),(g,'historian_sync','Historian Sync','/ic3/wtp/p1/historian',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;

    -- P2: Information with Intelligence
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-004',p2,'plant_overview','Plant Overview Dashboard',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'process_flow','Process Flow / Mimic Diagram','/ic3/wtp/p2/flow',1,'coming_soon'),(g,'treated_output','Treated-Water Output','/ic3/wtp/p2/output',2,'coming_soon'),(g,'plant_status','Plant Status (Units Online / Offline)','/ic3/wtp/p2/status',3,'coming_soon'),(g,'production_capacity','Production vs Capacity','/ic3/wtp/p2/capacity',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-004',p2,'process_monitoring','Process Monitoring',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'stage_parameters','Stage-wise Process Parameters','/ic3/wtp/p2/parameters',1,'coming_soon'),(g,'filter_status','Filter Run / Headloss Status','/ic3/wtp/p2/filter-status',2,'coming_soon'),(g,'clarifier_perf','Clarifier Performance','/ic3/wtp/p2/clarifier',3,'coming_soon'),(g,'chlorine_status','Disinfection Status (Residual Chlorine)','/ic3/wtp/p2/chlorine',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-004',p2,'wq_monitoring','Water Quality Monitoring',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'raw_vs_treated','Raw vs Treated Quality','/ic3/wtp/p2/quality-comparison',1,'coming_soon'),(g,'online_trends','Online Parameter Trends (Turbidity, pH, Cl)','/ic3/wtp/p2/trends',2,'coming_soon'),(g,'compliance_status','Compliance Status (BIS 10500)','/ic3/wtp/p2/compliance',3,'coming_soon'),(g,'quality_alerts','Quality Alerts','/ic3/wtp/p2/alerts',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-004',p2,'chemicals_consumables','Chemicals & Consumables',4) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'dosing_rates','Chemical Dosing Rates','/ic3/wtp/p2/dosing-rates',1,'coming_soon'),(g,'chemical_stock','Chemical Stock / Consumption','/ic3/wtp/p2/stock',2,'coming_soon'),(g,'dosage_vs_demand','Dosage vs Demand','/ic3/wtp/p2/dosage-demand',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;

    -- P3: Analytics & Network Intelligence
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-004',p3,'process_efficiency','Process Efficiency',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'treatment_efficiency','Treatment Efficiency (Turbidity Removal)','/ic3/wtp/p3/efficiency',1,'coming_soon'),(g,'filter_performance','Filter Performance Analysis','/ic3/wtp/p3/filter-perf',2,'coming_soon'),(g,'clarifier_efficiency','Clarifier / Sedimentation Efficiency','/ic3/wtp/p3/clarifier-eff',3,'coming_soon'),(g,'unit_benchmarking','Unit-Process Benchmarking','/ic3/wtp/p3/benchmarking',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-004',p3,'chemical_optimization','Chemical Optimization',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'coagulant_analysis','Coagulant-Dose Analysis','/ic3/wtp/p3/coagulant',1,'coming_soon'),(g,'chemical_cost','Chemical-Cost Analysis','/ic3/wtp/p3/cost-analysis',2,'coming_soon'),(g,'dose_response','Dose–Response Correlation','/ic3/wtp/p3/dose-response',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-004',p3,'energy_resource','Energy & Resource',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'specific_energy','Specific Energy Consumption (kWh/ML)','/ic3/wtp/p3/energy',1,'coming_soon'),(g,'pumping_efficiency','Pumping Efficiency','/ic3/wtp/p3/pumping-eff',2,'coming_soon'),(g,'water_recovery','Water Recovery / Plant Losses','/ic3/wtp/p3/recovery',3,'coming_soon'),(g,'backwash_analysis','Backwash-Water Analysis','/ic3/wtp/p3/backwash',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-004',p3,'compliance_quality','Compliance & Quality',4) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'compliance_trends','Quality-Compliance Trends','/ic3/wtp/p3/trends',1,'coming_soon'),(g,'exceedance_analysis','Exceedance Analysis','/ic3/wtp/p3/exceedance',2,'coming_soon'),(g,'sludge_analysis','Sludge-Generation Analysis','/ic3/wtp/p3/sludge',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;

    -- P4: AI Models & Prediction
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-004',p4,'quality_prediction','Quality Prediction',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'treated_quality_pred','Treated-Water Quality Prediction','/ic3/wtp/p4/quality-pred',1,'coming_soon'),(g,'raw_water_forecast','Raw-Water Quality Forecast (from Source)','/ic3/wtp/p4/raw-forecast',2,'coming_soon'),(g,'filter_run_pred','Filter-Run / Breakthrough Prediction','/ic3/wtp/p4/filter-pred',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-004',p4,'dosing_optimization','Dosing Optimization (AI)',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'ai_coagulant','AI Coagulant-Dose Optimization','/ic3/wtp/p4/coagulant-ai',1,'coming_soon'),(g,'chlorine_optimization','Chlorine-Dose Optimization','/ic3/wtp/p4/chlorine-optim',2,'coming_soon'),(g,'jar_test_sensor','Predictive Jar-Test / Soft Sensor','/ic3/wtp/p4/jar-test-ai',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-004',p4,'predictive_maintenance','Predictive Maintenance',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'equipment_failure_pred','Equipment-Failure Prediction','/ic3/wtp/p4/equipment-pred',1,'coming_soon'),(g,'membrane_fouling','Filter Media / Membrane Fouling Prediction','/ic3/wtp/p4/fouling-pred',2,'coming_soon'),(g,'pump_health','Pump-Health Prediction','/ic3/wtp/p4/pump-health',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-004',p4,'process_optimization','Process Optimization',4) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'energy_optimization','Energy Optimization','/ic3/wtp/p4/energy-optim',1,'coming_soon'),(g,'production_planning','Production Planning / Demand Match','/ic3/wtp/p4/planning',2,'coming_soon'),(g,'setpoint_optimization','Process Setpoint Optimization','/ic3/wtp/p4/setpoint-optim',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;

    -- P5: Operations & Automation
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-004',p5,'process_control','Process Control & Automation',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'setpoint_control','Setpoint Control (Dosing / Flow)','/ic3/wtp/p5/setpoint',1,'coming_soon'),(g,'backwash_automation','Filter Backwash Automation','/ic3/wtp/p5/backwash',2,'coming_soon'),(g,'clarifier_control','Clarifier / Desludging Control','/ic3/wtp/p5/clarifier-control',3,'coming_soon'),(g,'auto_sequencing','Auto-Sequencing / Start-Stop','/ic3/wtp/p5/sequencing',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-004',p5,'dosing_management','Chemical Dosing Management',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'dosing_setpoint','Dosing-Setpoint Management','/ic3/wtp/p5/dosing-setpoint',1,'coming_soon'),(g,'stock_replenishment','Stock-Replenishment Workflow','/ic3/wtp/p5/replenishment',2,'coming_soon'),(g,'dosing_pump_control','Dosing-Pump Control','/ic3/wtp/p5/pump-control',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-004',p5,'maintenance_mgmt','Maintenance & Work Management',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'preventive_maintenance','Preventive Maintenance Schedules','/ic3/wtp/p5/maintenance',1,'coming_soon'),(g,'work_orders','Work Orders','/ic3/wtp/p5/work-orders',2,'coming_soon'),(g,'filter_media_replacement','Filter-Media Replacement','/ic3/wtp/p5/filter-replacement',3,'coming_soon'),(g,'analyzer_calibration','Analyzer Calibration Schedules','/ic3/wtp/p5/calibration',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-004',p5,'alarms_incidents','Alarms & Incident Management',4) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'alarm_console','Process Alarm Console','/ic3/wtp/p5/alarm-console',1,'coming_soon'),(g,'quality_breach','Quality-Breach Response','/ic3/wtp/p5/breach-response',2,'coming_soon'),(g,'sop_escalation','SOP / Escalation Workflow','/ic3/wtp/p5/escalation',3,'coming_soon'),(g,'shift_logbook','Shift Logbook','/ic3/wtp/p5/logbook',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;

    -- P6: Citizen, Reporting & Governance
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-004',p6,'stakeholder_communication','Stakeholder & Communication',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'quality_disclosure','Water-Quality Public Disclosure','/ic3/wtp/p6/disclosure',1,'coming_soon'),(g,'production_status','Supply / Production Status','/ic3/wtp/p6/status',2,'coming_soon'),(g,'grievance_inputs','Grievance Inputs','/ic3/wtp/p6/grievance',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-004',p6,'reporting_compliance','Reporting & Compliance',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'statutory_reports','Statutory Quality Reports (BIS 10500 / CPHEEO)','/ic3/wtp/p6/statutory',1,'coming_soon'),(g,'operation_reports','Production & Daily-Operation Reports','/ic3/wtp/p6/operations',2,'coming_soon'),(g,'chemical_reports','Chemical-Consumption Reports','/ic3/wtp/p6/chemical',3,'coming_soon'),(g,'energy_reports','Energy Reports','/ic3/wtp/p6/energy',4,'coming_soon'),(g,'custom_reports','Custom / Scheduled Reports','/ic3/wtp/p6/custom',5,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-004',p6,'administration_trust','Administration & Trust',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'plant_config','Plant / Process Configuration','/ic3/wtp/p6/config',1,'coming_soon'),(g,'user_roles','User & Role Management (Operators / Shifts)','/ic3/wtp/p6/user-roles',2,'coming_soon'),(g,'audit_logs','Audit Logs / Data Lineage','/ic3/wtp/p6/audit',3,'coming_soon'),(g,'standards_master','Standards & Setpoint Master Data','/ic3/wtp/p6/standards',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;

END $$;
`
	_, _ = pool.Exec(ctx, seedSYS004)

	// SYS-005: Wastewater Treatment Plant
	fmt.Println("Seeding SYS-005 (WWTP)...")
	seedSYS005 := `
DO $$
DECLARE
    p1 INT; p2 INT; p3 INT; p4 INT; p5 INT; p6 INT;
    g INT;
BEGIN
    SELECT id INTO p1 FROM ic3_nav_layer WHERE code='P1';
    SELECT id INTO p2 FROM ic3_nav_layer WHERE code='P2';
    SELECT id INTO p3 FROM ic3_nav_layer WHERE code='P3';
    SELECT id INTO p4 FROM ic3_nav_layer WHERE code='P4';
    SELECT id INTO p5 FROM ic3_nav_layer WHERE code='P5';
    SELECT id INTO p6 FROM ic3_nav_layer WHERE code='P6';
    DELETE FROM ic3_nav_item WHERE group_id IN (SELECT id FROM ic3_nav_group WHERE system_id = 'SYS-005');
    DELETE FROM ic3_nav_group WHERE system_id = 'SYS-005';

    -- P1: Data & Field Intelligence
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-005',p1,'sewerage_network_inventory','Sewerage Network Inventory',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'sewer_network','Sewer Network (Gravity / Rising Mains)','/ic3/wwtp/p1/sewer-network',1,'coming_soon'),(g,'manholes_chambers','Manholes / Chambers','/ic3/wwtp/p1/manholes',2,'coming_soon'),(g,'lift_pumping_stations','Lift / Pumping Stations','/ic3/wwtp/p1/pumping-stations',3,'coming_soon'),(g,'treatment_nodes','Treatment Nodes (STPs)','/ic3/wwtp/p1/treatment-nodes',4,'coming_soon'),(g,'outfalls_discharge','Outfalls / Discharge Points','/ic3/wwtp/p1/outfalls',5,'coming_soon'),(g,'reuse_recycling','Reuse / Recycling Infrastructure','/ic3/wwtp/p1/reuse',6,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-005',p1,'field_instrumentation','Field Instrumentation / IoT',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'flow_meters','Flow Meters (Sewer / Pumping)','/ic3/wwtp/p1/flow-meters',1,'coming_soon'),(g,'level_sensors','Level Sensors (Wet Wells / Manholes)','/ic3/wwtp/p1/level-sensors',2,'coming_soon'),(g,'pump_run_sensors','Pump-Run Sensors','/ic3/wwtp/p1/pump-sensors',3,'coming_soon'),(g,'outfall_quality','Outfall Quality Sensors','/ic3/wwtp/p1/quality-sensors',4,'coming_soon'),(g,'overflow_sensors','Overflow Sensors','/ic3/wwtp/p1/overflow-sensors',5,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-005',p1,'field_operations_data','Field Operations Data',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'cctv_inspection','Manhole / Sewer Inspection (CCTV)','/ic3/wwtp/p1/cctv',1,'coming_soon'),(g,'desilting_logs','Desilting / Cleaning Logs','/ic3/wwtp/p1/desilting',2,'coming_soon'),(g,'blockage_reports','Blockage Reports','/ic3/wwtp/p1/blockage',3,'coming_soon'),(g,'field_survey','Field Survey Data','/ic3/wwtp/p1/survey',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-005',p1,'data_acquisition_scada','Data Acquisition & SCADA',4) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'scada_telemetry','SCADA / Telemetry Feed','/ic3/wwtp/p1/scada',1,'coming_soon'),(g,'manual_mobile_entry','Manual / Mobile Entry','/ic3/wwtp/p1/manual-entry',2,'coming_soon'),(g,'data_validation','Data Validation & Gap-Filling','/ic3/wwtp/p1/validation',3,'coming_soon'),(g,'historian_sync','Historian Sync','/ic3/wwtp/p1/historian',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;

    -- P2: Information with Intelligence
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-005',p2,'network_monitoring','Network Monitoring Dashboard',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'network_overview','Sewerage Network Overview','/ic3/wwtp/p2/network-overview',1,'coming_soon'),(g,'pumping_status','Pumping-Station Status','/ic3/wwtp/p2/pumping-status',2,'coming_soon'),(g,'flow_level_dashboard','Flow / Level Dashboard','/ic3/wwtp/p2/flow-level',3,'coming_soon'),(g,'treatment_capacity','Treatment-Capacity Utilization','/ic3/wwtp/p2/capacity',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-005',p2,'collection_performance','Collection Performance',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'inflow_profiles','Sewage Inflow Profiles','/ic3/wwtp/p2/inflow',1,'coming_soon'),(g,'pumping_performance','Pumping-Station Performance','/ic3/wwtp/p2/perf',2,'coming_soon'),(g,'overflow_status','Overflow / Spill Status','/ic3/wwtp/p2/overflow',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-005',p2,'reuse_recycling','Reuse & Recycling',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'reuse_volume','Treated-Water Reuse Volume','/ic3/wwtp/p2/reuse-volume',1,'coming_soon'),(g,'reuse_allocation','Reuse Allocation (Industry / Irrigation)','/ic3/wwtp/p2/allocation',2,'coming_soon'),(g,'recycling_status','Recycling Status','/ic3/wwtp/p2/recycling',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-005',p2,'compliance_quality','Compliance & Quality',4) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'outfall_quality','Outfall Quality Status','/ic3/wwtp/p2/quality',1,'coming_soon'),(g,'discharge_compliance','Discharge Compliance','/ic3/wwtp/p2/compliance',2,'coming_soon'),(g,'quality_alerts','Quality Alerts','/ic3/wwtp/p2/alerts',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;

    -- P3: Analytics & Network Intelligence
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-005',p3,'network_hydraulic','Network Hydraulic Analytics',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'sewer_flow','Sewer-Flow Analysis','/ic3/wwtp/p3/flow-analysis',1,'coming_soon'),(g,'capacity_surcharge','Capacity / Surcharge Analysis','/ic3/wwtp/p3/capacity-analysis',2,'coming_soon'),(g,'infiltration_inflow','Infiltration / Inflow (I&I) Analysis','/ic3/wwtp/p3/inflow-analysis',3,'coming_soon'),(g,'bottleneck_id','Bottleneck Identification','/ic3/wwtp/p3/bottleneck',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-005',p3,'collection_efficiency','Collection Efficiency',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'pumping_efficiency','Pumping Efficiency','/ic3/wwtp/p3/pump-efficiency',1,'coming_soon'),(g,'energy_analytics','Pumping-Energy Analytics','/ic3/wwtp/p3/energy-analytics',2,'coming_soon'),(g,'blockage_analysis','Blockage / Overflow Analysis','/ic3/wwtp/p3/blockage-analysis',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-005',p3,'reuse_analytics','Reuse Analytics',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'reuse_potential','Reuse-Potential Analysis','/ic3/wwtp/p3/reuse-potential',1,'coming_soon'),(g,'reuse_matching','Reuse vs Demand Matching','/ic3/wwtp/p3/demand-match',2,'coming_soon'),(g,'resource_recovery','Resource-Recovery Analytics','/ic3/wwtp/p3/recovery',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-005',p3,'compliance_analytics','Compliance Analytics',4) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'compliance_trends','Discharge-Compliance Trends','/ic3/wwtp/p3/compliance-trends',1,'coming_soon'),(g,'spill_compliance','Spill / Overflow Compliance','/ic3/wwtp/p3/spill-compliance',2,'coming_soon') ON CONFLICT DO NOTHING; END IF;

    -- P4: AI Models & Prediction
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-005',p4,'flow_load_forecasting','Flow & Load Forecasting',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'inflow_forecast','Sewage-Inflow Forecast','/ic3/wwtp/p4/inflow-forecast',1,'coming_soon'),(g,'weather_flow','Wet-Weather Flow Prediction','/ic3/wwtp/p4/weather-flow',2,'coming_soon'),(g,'load_forecast','Load Forecasting','/ic3/wwtp/p4/load-forecast',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-005',p4,'risk_event_prediction','Risk & Event Prediction',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'blockage_pred','Blockage / Overflow Prediction','/ic3/wwtp/p4/blockage-pred',1,'coming_soon'),(g,'pump_failure','Pump-Failure Prediction','/ic3/wwtp/p4/pump-failure',2,'coming_soon'),(g,'surcharge_risk','Surcharge-Risk Prediction','/ic3/wwtp/p4/surcharge-risk',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-005',p4,'reuse_optimization','Reuse Optimization',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'allocation_optim','Reuse-Allocation Optimization','/ic3/wwtp/p4/allocation-optim',1,'coming_soon'),(g,'treatment_matching','Treatment–Reuse Matching','/ic3/wwtp/p4/treatment-match',2,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-005',p4,'optimization','Optimization',4) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'energy_optim','Pumping-Energy Optimization','/ic3/wwtp/p4/energy-optim',1,'coming_soon'),(g,'network_optim','Network-Operation Optimization','/ic3/wwtp/p4/network-optim',2,'coming_soon') ON CONFLICT DO NOTHING; END IF;

    -- P5: Operations & Automation
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-005',p5,'network_operations','Network Operations',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'pumping_control','Pumping-Station Control','/ic3/wwtp/p5/pumping-control',1,'coming_soon'),(g,'flow_diversion','Flow Diversion / Balancing','/ic3/wwtp/p5/flow-diversion',2,'coming_soon'),(g,'overflow_mgmt','Overflow Management','/ic3/wwtp/p5/overflow-mgmt',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-005',p5,'maintenance_mgmt','Maintenance & Work Management',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'desilting_schedules','Desilting / Cleaning Schedules','/ic3/wwtp/p5/desilting-schedules',1,'coming_soon'),(g,'repair_orders','Sewer-Repair Work Orders','/ic3/wwtp/p5/repair-orders',2,'coming_soon'),(g,'cctv_planning','CCTV Inspection Planning','/ic3/wwtp/p5/cctv-planning',3,'coming_soon'),(g,'blockage_response','Blockage Response','/ic3/wwtp/p5/blockage-response',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-005',p5,'reuse_operations','Reuse Operations',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'reuse_scheduling','Reuse-Supply Scheduling','/ic3/wwtp/p5/reuse-scheduling',1,'coming_soon'),(g,'allocation_mgmt','Allocation Management','/ic3/wwtp/p5/allocation-mgmt',2,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-005',p5,'alarms_automation','Alarms & Automation',4) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'level_alarms','Overflow / Level Alarms','/ic3/wwtp/p5/level-alarms',1,'coming_soon'),(g,'quality_alarms','Pump / Quality Alarms','/ic3/wwtp/p5/quality-alarms',2,'coming_soon'),(g,'rule_engine','Rule Engine / Triggers','/ic3/wwtp/p5/rule-engine',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;

    -- P6: Citizen, Reporting & Governance
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-005',p6,'citizen_stakeholder','Citizen & Stakeholder',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'complaint_requests','Sewerage Complaint / Requests','/ic3/wwtp/p6/complaints',1,'coming_soon'),(g,'blockage_reporting','Overflow / Blockage Reporting','/ic3/wwtp/p6/blockage-report',2,'coming_soon'),(g,'reuse_portal','Reuse-Customer Portal','/ic3/wwtp/p6/reuse-portal',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-005',p6,'reporting_compliance','Reporting & Compliance',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'statutory_reports','Statutory Discharge Reports (CPCB / NGT)','/ic3/wwtp/p6/statutory',1,'coming_soon'),(g,'perf_reports','Network-Performance Reports','/ic3/wwtp/p6/performance',2,'coming_soon'),(g,'reuse_reports','Reuse / Recycling Reports','/ic3/wwtp/p6/reuse-reports',3,'coming_soon'),(g,'custom_reports','Custom / Scheduled Reports','/ic3/wwtp/p6/custom-reports',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-005',p6,'administration_trust','Administration & Trust',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'network_config','Network / Asset Configuration','/ic3/wwtp/p6/config',1,'coming_soon'),(g,'user_roles','User & Role Management','/ic3/wwtp/p6/user-roles',2,'coming_soon'),(g,'audit_logs','Audit Logs / Data Lineage','/ic3/wwtp/p6/audit',3,'coming_soon'),(g,'master_data','Master Data & Standards','/ic3/wwtp/p6/master-data',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;

END $$;
`
	_, _ = pool.Exec(ctx, seedSYS005)

	// SYS-013: Computerized Maintenance Management System
	fmt.Println("Seeding SYS-013 (CMMS)...")
	seedSYS013 := `
DO $$
DECLARE
    p1 INT; p2 INT; p3 INT; p4 INT; p5 INT; p6 INT;
    g INT;
BEGIN
    SELECT id INTO p1 FROM ic3_nav_layer WHERE code='P1';
    SELECT id INTO p2 FROM ic3_nav_layer WHERE code='P2';
    SELECT id INTO p3 FROM ic3_nav_layer WHERE code='P3';
    SELECT id INTO p4 FROM ic3_nav_layer WHERE code='P4';
    SELECT id INTO p5 FROM ic3_nav_layer WHERE code='P5';
    SELECT id INTO p6 FROM ic3_nav_layer WHERE code='P6';
    DELETE FROM ic3_nav_item WHERE group_id IN (SELECT id FROM ic3_nav_group WHERE system_id = 'SYS-013');
    DELETE FROM ic3_nav_group WHERE system_id = 'SYS-013';

    -- P1: Data & Field Intelligence
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-013',p1,'asset_registry','Asset Registry',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'asset_hierarchy','Asset Hierarchy / Master','/ic3/cmms/p1/asset-hierarchy',1,'coming_soon'),(g,'equipment_catalog','Equipment Catalog','/ic3/cmms/p1/equipment-catalog',2,'coming_soon'),(g,'asset_criticality','Asset Criticality / Classification','/ic3/cmms/p1/criticality',3,'coming_soon'),(g,'location_mapping','Location / Site Mapping','/ic3/cmms/p1/location-mapping',4,'coming_soon'),(g,'asset_documentation','Asset Documentation (Manuals / Warranty)','/ic3/cmms/p1/documentation',5,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-013',p1,'maintenance_data_capture','Maintenance Data Capture',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'meter_readings','Meter / Runtime Readings','/ic3/cmms/p1/meter-readings',1,'coming_soon'),(g,'condition_monitoring','Condition-Monitoring Data','/ic3/cmms/p1/condition-monitoring',2,'coming_soon'),(g,'asset_tag_scanning','QR / Asset-Tag Scanning','/ic3/cmms/p1/asset-tag',3,'coming_soon'),(g,'inspection_checklists','Inspection Checklists','/ic3/cmms/p1/checklists',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-013',p1,'spares_inventory','Spares & Inventory',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'spare_parts_catalog','Spare-Parts Catalog','/ic3/cmms/p1/spare-catalog',1,'coming_soon'),(g,'stock_records','Stock / Inventory Records','/ic3/cmms/p1/stock',2,'coming_soon'),(g,'vendor_registry','Supplier / Vendor Registry','/ic3/cmms/p1/vendor-registry',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-013',p1,'data_acquisition_integration','Data Acquisition & Integration',4) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'iot_sensor_integration','IoT / Sensor Integration','/ic3/cmms/p1/iot-integration',1,'coming_soon'),(g,'scada_fault_feed','SCADA Fault Feed','/ic3/cmms/p1/scada-feed',2,'coming_soon'),(g,'mobile_field_data','Mobile Field Data','/ic3/cmms/p1/mobile-data',3,'coming_soon'),(g,'data_validation','Data Validation','/ic3/cmms/p1/validation',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;

    -- P2: Information with Intelligence
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-013',p2,'maintenance_dashboard','Maintenance Dashboard',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'wo_status_overview','Work-Order Status Overview','/ic3/cmms/p2/wo-status',1,'coming_soon'),(g,'asset_health_dashboard','Asset-Health Dashboard','/ic3/cmms/p2/asset-health',2,'coming_soon'),(g,'maintenance_kpi','Maintenance KPI Tiles','/ic3/cmms/p2/kpi-tiles',3,'coming_soon'),(g,'backlog_overview','Backlog Overview','/ic3/cmms/p2/backlog',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-013',p2,'workorder_views','Work-Order Views',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'wo_status','Open / In-Progress / Closed WOs','/ic3/cmms/p2/wo-status-view',1,'coming_soon'),(g,'wo_by_priority','WO by Priority / Type','/ic3/cmms/p2/wo-priority',2,'coming_soon'),(g,'technician_assignment','Technician-Assignment View','/ic3/cmms/p2/technician-assignment',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-013',p2,'asset_status','Asset Status',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'uptime_downtime','Asset Uptime / Downtime','/ic3/cmms/p2/uptime',1,'coming_soon'),(g,'breakdown_vs_planned','Breakdown vs Planned','/ic3/cmms/p2/breakdown-planned',2,'coming_soon'),(g,'warranty_amc','Warranty / AMC Status','/ic3/cmms/p2/warranty-amc',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-013',p2,'inventory_status','Inventory Status',4) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'stock_levels','Stock Levels','/ic3/cmms/p2/stock-levels',1,'coming_soon'),(g,'reorder_status','Reorder Status','/ic3/cmms/p2/reorder',2,'coming_soon'),(g,'consumption_overview','Consumption Overview','/ic3/cmms/p2/consumption',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;

    -- P3: Analytics & Network Intelligence
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-013',p3,'maintenance_analytics','Maintenance Analytics',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'mtbf_mttr','MTBF / MTTR Analysis','/ic3/cmms/p3/mtbf-mttr',1,'coming_soon'),(g,'breakdown_trends','Breakdown-Trend Analysis','/ic3/cmms/p3/breakdown-trends',2,'coming_soon'),(g,'pm_compliance','PM-Compliance Analysis','/ic3/cmms/p3/pm-compliance',3,'coming_soon'),(g,'failure_mode','Failure-Mode Analysis','/ic3/cmms/p3/failure-mode',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-013',p3,'asset_performance','Asset Performance',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'asset_reliability','Asset-Reliability Analysis','/ic3/cmms/p3/reliability',1,'coming_soon'),(g,'availability_oee','Availability / OEE','/ic3/cmms/p3/oee',2,'coming_soon'),(g,'lifecycle_cost','Lifecycle-Cost Analysis','/ic3/cmms/p3/lifecycle-cost',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-013',p3,'cost_resource','Cost & Resource',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'maintenance_cost','Maintenance-Cost Analysis','/ic3/cmms/p3/cost-analysis',1,'coming_soon'),(g,'labour_utilization','Labour Utilization','/ic3/cmms/p3/labour',2,'coming_soon'),(g,'spare_consumption','Spare-Consumption Analysis','/ic3/cmms/p3/spare-consumption',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-013',p3,'backlog_workload','Backlog & Workload',4) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'backlog_analysis','Backlog Analysis','/ic3/cmms/p3/backlog-analysis',1,'coming_soon'),(g,'workload_balancing','Workload Balancing','/ic3/cmms/p3/workload-balancing',2,'coming_soon') ON CONFLICT DO NOTHING; END IF;

    -- P4: AI Models & Prediction
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-013',p4,'predictive_maintenance','Predictive Maintenance',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'failure_prediction','Failure Prediction','/ic3/cmms/p4/failure-pred',1,'coming_soon'),(g,'remaining_useful_life','Remaining Useful Life (RUL)','/ic3/cmms/p4/rul',2,'coming_soon'),(g,'condition_based_triggers','Condition-based Triggers','/ic3/cmms/p4/condition-triggers',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-013',p4,'anomaly_detection','Anomaly Detection',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'equipment_anomaly','Equipment-Anomaly Detection','/ic3/cmms/p4/equipment-anomaly',1,'coming_soon'),(g,'sensor_fault_detection','Sensor-based Fault Detection','/ic3/cmms/p4/sensor-fault',2,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-013',p4,'optimization','Optimization',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'pm_schedule_optim','PM-Schedule Optimization','/ic3/cmms/p4/pm-schedule-optim',1,'coming_soon'),(g,'spare_demand_forecast','Spare-Parts Demand Forecast','/ic3/cmms/p4/spare-forecast',2,'coming_soon'),(g,'tech_scheduling_optim','Technician-Scheduling Optimization','/ic3/cmms/p4/tech-scheduling',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-013',p4,'reliability_modeling','Reliability Modeling',4) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'risk_based_maintenance','Risk-based Maintenance','/ic3/cmms/p4/risk-maintenance',1,'coming_soon'),(g,'criticality_modeling','Criticality Modeling','/ic3/cmms/p4/criticality',2,'coming_soon') ON CONFLICT DO NOTHING; END IF;

    -- P5: Operations & Automation
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-013',p5,'workorder_management','Work-Order Management',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'wo_creation','WO Creation / Assignment','/ic3/cmms/p5/wo-creation',1,'coming_soon'),(g,'corrective_maintenance','Corrective Maintenance','/ic3/cmms/p5/corrective',2,'coming_soon'),(g,'wo_workflow','WO Workflow / Approvals','/ic3/cmms/p5/wo-workflow',3,'coming_soon'),(g,'mobile_work_execution','Mobile Work Execution','/ic3/cmms/p5/mobile-execution',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-013',p5,'preventive_maintenance','Preventive Maintenance',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'pm_scheduling','PM Scheduling','/ic3/cmms/p5/pm-scheduling',1,'coming_soon'),(g,'pm_calendar_triggers','PM Calendar / Triggers','/ic3/cmms/p5/pm-calendar',2,'coming_soon'),(g,'checklist_execution','Checklist Execution','/ic3/cmms/p5/checklist',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-013',p5,'inventory_procurement','Inventory & Procurement',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'spare_issue_return','Spare Issue / Return','/ic3/cmms/p5/spare-issue',1,'coming_soon'),(g,'auto_reorder','Auto Reorder / Purchase Requests','/ic3/cmms/p5/auto-reorder',2,'coming_soon'),(g,'vendor_management','Vendor Management','/ic3/cmms/p5/vendor-mgmt',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-013',p5,'automation_alerts','Automation & Alerts',4) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'breakdown_auto_wo','Breakdown Auto-WO (from SCADA)','/ic3/cmms/p5/auto-wo',1,'coming_soon'),(g,'escalation_workflow','Escalation Workflow','/ic3/cmms/p5/escalation',2,'coming_soon'),(g,'notification_engine','Notification Engine','/ic3/cmms/p5/notifications',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;

    -- P6: Citizen, Reporting & Governance
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-013',p6,'service_requests','Service Requests',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'internal_service_requests','Internal Service Requests','/ic3/cmms/p6/service-requests',1,'coming_soon'),(g,'complaint_to_wo','Complaint-to-WO Linkage','/ic3/cmms/p6/complaint-wo',2,'coming_soon'),(g,'request_tracking','Request Tracking','/ic3/cmms/p6/tracking',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-013',p6,'reporting_outcomes','Reporting & Outcomes',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'maintenance_perf_reports','Maintenance-Performance Reports','/ic3/cmms/p6/perf-reports',1,'coming_soon'),(g,'asset_reliability_reports','Asset / Reliability Reports','/ic3/cmms/p6/asset-reports',2,'coming_soon'),(g,'cost_budget_reports','Cost / Budget Reports','/ic3/cmms/p6/cost-reports',3,'coming_soon'),(g,'compliance_audit_reports','Compliance / Audit Reports','/ic3/cmms/p6/compliance-reports',4,'coming_soon'),(g,'custom_scheduled_reports','Custom / Scheduled Reports','/ic3/cmms/p6/custom-reports',5,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-013',p6,'administration_trust','Administration & Trust',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'asset_pm_config','Asset / PM Configuration','/ic3/cmms/p6/config',1,'coming_soon'),(g,'user_role_mgmt','User & Role Management (Technicians)','/ic3/cmms/p6/user-roles',2,'coming_soon'),(g,'audit_logs','Audit Logs / Data Lineage','/ic3/cmms/p6/audit',3,'coming_soon'),(g,'master_data_standards','Master Data & Standards','/ic3/cmms/p6/master-data',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;

END $$;
`
	_, _ = pool.Exec(ctx, seedSYS013)

	// Seed SYS-014 (Platform Master) with 2 special layers
	fmt.Println("Seeding SYS-014 (IC³ Platform Master) with special layers...")
	seedSYS014 := `
DO $$
DECLARE
    l1 INT; l2 INT;
    g INT;
BEGIN
    SELECT id INTO l1 FROM ic3_nav_layer WHERE code='SYS014-L1';
    SELECT id INTO l2 FROM ic3_nav_layer WHERE code='SYS014-L2';

    -- Layer 301: Systems & Architecture
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-014',l1,'systems_architecture','All 13 Water Systems',1) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'system_overview','System Overview','/ic3/platform/systems-overview',1,'coming_soon'),
        (g,'system_health','System Health Status','/ic3/platform/system-health',2,'coming_soon'),
        (g,'integrated_assets','Integrated Asset Registry','/ic3/platform/assets',3,'coming_soon'),
        (g,'system_config','System Configuration','/ic3/platform/config',4,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;

    -- Layer 302: Cross-System Analytics
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-014',l2,'cross_system','Cross-System Intelligence',1) ON CONFLICT DO NOTHING RETURNING id INTO g;
    IF g IS NOT NULL THEN
        INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES
        (g,'unified_analytics','Unified Analytics Dashboard','/ic3/platform/unified-analytics',1,'coming_soon'),
        (g,'cross_domain_insights','Cross-Domain Insights','/ic3/platform/cross-insights',2,'coming_soon'),
        (g,'platform_reports','Platform-wide Reports','/ic3/platform/reports',3,'coming_soon')
        ON CONFLICT DO NOTHING;
    END IF;
END $$;
`
	_, _ = pool.Exec(ctx, seedSYS014)
	fmt.Println("✓ SYS-014 seeding complete")

	// Check counts
	var layers, groups, items int
	pool.QueryRow(ctx, "SELECT COUNT(*) FROM ic3_nav_layer").Scan(&layers)
	pool.QueryRow(ctx, "SELECT COUNT(*) FROM ic3_nav_group").Scan(&groups)
	pool.QueryRow(ctx, "SELECT COUNT(*) FROM ic3_nav_item").Scan(&items)

	fmt.Printf("Database state:\n")
	fmt.Printf("  Layers: %d\n", layers)
	fmt.Printf("  Groups: %d\n", groups)
	fmt.Printf("  Items: %d\n\n", items)

	// SYS-009: Irrigation Management System
	fmt.Println("Seeding SYS-009 (Irrigation Management)...")
	seedSYS009 := `
DO $$
DECLARE
    p1 INT; p2 INT; p3 INT; p4 INT; p5 INT; p6 INT;
    g INT;
BEGIN
    SELECT id INTO p1 FROM ic3_nav_layer WHERE code='P1';
    SELECT id INTO p2 FROM ic3_nav_layer WHERE code='P2';
    SELECT id INTO p3 FROM ic3_nav_layer WHERE code='P3';
    SELECT id INTO p4 FROM ic3_nav_layer WHERE code='P4';
    SELECT id INTO p5 FROM ic3_nav_layer WHERE code='P5';
    SELECT id INTO p6 FROM ic3_nav_layer WHERE code='P6';
    DELETE FROM ic3_nav_item WHERE group_id IN (SELECT id FROM ic3_nav_group WHERE system_id = 'SYS-009');
    DELETE FROM ic3_nav_group WHERE system_id = 'SYS-009';

    -- P1: Data & Field Intelligence
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-009',p1,'command_area_inventory','Command Area & Network Inventory',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'canal_network','Canal Network (Main / Branch / Distributary / Minor)','/ic3/irrigation/p1/canal-network',1,'live'),(g,'outlets_field_channels','Outlets / Field Channels','/ic3/irrigation/p1/outlets',2,'live'),(g,'command_area_cca','Command Area / CCA (Culturable Command Area)','/ic3/irrigation/p1/cca',3,'live'),(g,'reservoirs_dams','Reservoirs / Dams / Barrages','/ic3/irrigation/p1/reservoirs',4,'live'),(g,'regulators_gates','Regulators / Gates / Structures','/ic3/irrigation/p1/structures',5,'live'),(g,'micro_irrigation','Micro-irrigation Assets (Drip / Sprinkler)','/ic3/irrigation/p1/micro-irrigation',6,'live') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-009',p1,'field_instrumentation','Field Instrumentation / IoT',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'canal_flow_meters','Canal Flow Meters','/ic3/irrigation/p1/flow-meters',1,'live'),(g,'level_sensors','Reservoir / Canal Level Sensors','/ic3/irrigation/p1/level-sensors',2,'live'),(g,'soil_moisture','Soil-Moisture Sensors','/ic3/irrigation/p1/soil-moisture',3,'live'),(g,'weather_stations','Weather Stations (Rain / ET)','/ic3/irrigation/p1/weather-stations',4,'live'),(g,'gate_position','Gate-Position Sensors','/ic3/irrigation/p1/gate-sensors',5,'live') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-009',p1,'agronomic_field_data','Agronomic & Field Data',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'crop_cropping_pattern','Crop / Cropping-Pattern Data','/ic3/irrigation/p1/cropping-pattern',1,'live'),(g,'land_parcel_registry','Land Parcel / Farmer Registry','/ic3/irrigation/p1/farmer-registry',2,'live'),(g,'irrigation_demand','Irrigation Demand Indents','/ic3/irrigation/p1/demand-indents',3,'live'),(g,'field_survey','Field Survey Data','/ic3/irrigation/p1/field-survey',4,'live') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-009',p1,'data_acquisition_telemetry','Data Acquisition & Telemetry',4) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'scada_telemetry','SCADA / Telemetry Feed','/ic3/irrigation/p1/scada-feed',1,'live'),(g,'remote_sensing','Remote Sensing / Satellite Data','/ic3/irrigation/p1/remote-sensing',2,'live'),(g,'manual_mobile_entry','Manual / Mobile Entry','/ic3/irrigation/p1/manual-entry',3,'live'),(g,'data_validation','Data Validation & Gap-Filling','/ic3/irrigation/p1/data-validation',4,'live') ON CONFLICT DO NOTHING; END IF;

    -- P2: Information with Intelligence
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-009',p2,'irrigation_dashboard','Irrigation Dashboard',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'command_area_overview','Command-Area Overview','/ic3/irrigation/p2/overview',1,'live'),(g,'canal_supply_status','Canal Supply Status','/ic3/irrigation/p2/supply-status',2,'live'),(g,'reservoir_storage','Reservoir Storage Status','/ic3/irrigation/p2/storage-status',3,'live'),(g,'water_delivery_indent','Water Delivery vs Indent','/ic3/irrigation/p2/delivery-vs-indent',4,'live') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-009',p2,'water_allocation','Water Allocation & Delivery',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'allocation_by_outlet','Allocation by Distributary / Outlet','/ic3/irrigation/p2/allocation-outlet',1,'live'),(g,'delivery_performance','Delivery Performance (Equity)','/ic3/irrigation/p2/delivery-equity',2,'live'),(g,'rotational_supply','Rotational Supply (Warabandi) Schedule','/ic3/irrigation/p2/warabandi-schedule',3,'live') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-009',p2,'crop_demand','Crop & Demand',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'crop_water_requirement','Crop Water Requirement','/ic3/irrigation/p2/water-requirement',1,'live'),(g,'cropping_pattern_area','Cropping Pattern / Area','/ic3/irrigation/p2/cropping-pattern',2,'live'),(g,'irrigation_demand_season','Irrigation Demand (Current Season)','/ic3/irrigation/p2/current-demand',3,'live') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-009',p2,'resource_status','Resource Status',4) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'reservoir_live_storage','Reservoir Live Storage','/ic3/irrigation/p2/live-storage',1,'live'),(g,'groundwater_status','Groundwater (Conjunctive) Status','/ic3/irrigation/p2/groundwater-status',2,'live'),(g,'soil_moisture_status','Soil-Moisture Status','/ic3/irrigation/p2/soil-moisture-status',3,'live') ON CONFLICT DO NOTHING; END IF;

    -- P3: Analytics & Network Intelligence
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-009',p3,'water_use_efficiency','Water-Use Efficiency',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'conveyance_efficiency','Conveyance Efficiency (Canal Losses)','/ic3/irrigation/p3/conveyance-efficiency',1,'live'),(g,'application_efficiency','Application Efficiency','/ic3/irrigation/p3/application-efficiency',2,'live'),(g,'water_productivity','Water Productivity (Crop per Drop)','/ic3/irrigation/p3/water-productivity',3,'live'),(g,'delivery_performance_ratio','Delivery Performance Ratio','/ic3/irrigation/p3/performance-ratio',4,'live') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-009',p3,'equity_adequacy','Equity & Adequacy',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'head_tail_equity','Head vs Tail Equity Analysis','/ic3/irrigation/p3/head-tail-equity',1,'live'),(g,'adequacy_reliability','Adequacy / Reliability Analysis','/ic3/irrigation/p3/adequacy-reliability',2,'live'),(g,'distribution_uniformity','Distribution Uniformity','/ic3/irrigation/p3/distribution-uniformity',3,'live') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-009',p3,'demand_supply','Demand-Supply Analytics',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'demand_vs_supply','Crop Demand vs Supply','/ic3/irrigation/p3/demand-vs-supply',1,'live'),(g,'deficit_surplus','Deficit / Surplus Analysis','/ic3/irrigation/p3/deficit-surplus',2,'live'),(g,'seasonal_balance','Seasonal Water Balance','/ic3/irrigation/p3/seasonal-balance',3,'live') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-009',p3,'resource_analytics','Resource Analytics',4) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'reservoir_inflow','Reservoir Inflow / Depletion','/ic3/irrigation/p3/reservoir-inflow',1,'live'),(g,'conjunctive_use','Conjunctive-Use Analysis','/ic3/irrigation/p3/conjunctive-use',2,'live'),(g,'canal_loss_hotspots','Canal-Loss Hotspots','/ic3/irrigation/p3/canal-loss-hotspots',3,'live') ON CONFLICT DO NOTHING; END IF;

    -- P4: AI Models & Prediction
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-009',p4,'demand_forecasting','Demand Forecasting',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'water_demand_prediction','Crop Water-Demand Prediction','/ic3/irrigation/p4/water-demand-prediction',1,'coming_soon'),(g,'et_based_forecast','ET-based Demand Forecast','/ic3/irrigation/p4/et-forecast',2,'coming_soon'),(g,'seasonal_forecast','Seasonal Irrigation Forecast','/ic3/irrigation/p4/seasonal-forecast',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-009',p4,'resource_forecasting','Resource Forecasting',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'inflow_forecast','Reservoir-Inflow Forecast','/ic3/irrigation/p4/inflow-forecast',1,'coming_soon'),(g,'availability_forecast','Water-Availability Forecast','/ic3/irrigation/p4/availability-forecast',2,'coming_soon'),(g,'drought_prediction','Drought / Stress Prediction','/ic3/irrigation/p4/drought-prediction',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-009',p4,'crop_yield_models','Crop & Yield Models',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'crop_stress_detection','Crop-Stress Detection (Remote Sensing)','/ic3/irrigation/p4/crop-stress',1,'coming_soon'),(g,'yield_prediction','Yield Prediction','/ic3/irrigation/p4/yield-prediction',2,'coming_soon'),(g,'cropping_optimization','Cropping-Pattern Optimization','/ic3/irrigation/p4/cropping-optimization',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-009',p4,'optimization_models','Optimization Models',4) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'irrigation_scheduling_opt','Irrigation-Scheduling Optimization','/ic3/irrigation/p4/scheduling-optimization',1,'coming_soon'),(g,'canal_operation_opt','Canal-Operation Optimization','/ic3/irrigation/p4/operation-optimization',2,'coming_soon'),(g,'allocation_optimization','Allocation Optimization (Equity)','/ic3/irrigation/p4/allocation-optimization',3,'coming_soon') ON CONFLICT DO NOTHING; END IF;

    -- P5: Operations & Automation
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-009',p5,'canal_operations','Canal Operations & Control',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'gate_control','Gate / Regulator Control','/ic3/irrigation/p5/gate-control',1,'live'),(g,'flow_regulation','Canal Flow Regulation','/ic3/irrigation/p5/flow-regulation',2,'live'),(g,'warabandi_management','Rotational Supply (Warabandi) Management','/ic3/irrigation/p5/warabandi-mgmt',3,'live'),(g,'water_release_scheduling','Water-Release Scheduling','/ic3/irrigation/p5/release-scheduling',4,'live') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-009',p5,'irrigation_scheduling','Irrigation Scheduling',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'field_level_scheduling','Field-level Scheduling','/ic3/irrigation/p5/field-scheduling',1,'live'),(g,'demand_based_delivery','Demand-based Delivery','/ic3/irrigation/p5/demand-delivery',2,'live'),(g,'micro_irrigation_automation','Micro-irrigation Automation','/ic3/irrigation/p5/micro-automation',3,'live') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-009',p5,'maintenance_management','Maintenance & Work Management',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'structure_maintenance','Canal / Structure Maintenance','/ic3/irrigation/p5/maintenance',1,'live'),(g,'desilting_schedules','Desilting Schedules','/ic3/irrigation/p5/desilting',2,'live'),(g,'work_orders','Work Orders','/ic3/irrigation/p5/work-orders',3,'live') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-009',p5,'alarms_automation','Alarms & Automation',4) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'level_breach_alarms','Level / Breach Alarms','/ic3/irrigation/p5/level-alarms',1,'live'),(g,'gate_fault_alarms','Gate-fault Alarms','/ic3/irrigation/p5/gate-alarms',2,'live'),(g,'rule_engine_triggers','Rule Engine / Triggers','/ic3/irrigation/p5/rule-engine',3,'live') ON CONFLICT DO NOTHING; END IF;

    -- P6: Citizen, Reporting & Governance
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-009',p6,'farmer_wua_engagement','Farmer & WUA Engagement',1) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'farmer_wua_portal','Farmer / WUA Portal','/ic3/irrigation/p6/farmer-portal',1,'coming_soon'),(g,'irrigation_indent','Irrigation Indent / Request','/ic3/irrigation/p6/indent-request',2,'coming_soon'),(g,'grievance_management','Grievance Management','/ic3/irrigation/p6/grievance',3,'coming_soon'),(g,'advisory_guidance','Advisory / Crop Guidance','/ic3/irrigation/p6/advisory',4,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-009',p6,'reporting_outcomes','Reporting & Outcomes',2) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'delivery_reports','Water-Delivery Reports','/ic3/irrigation/p6/delivery-reports',1,'live'),(g,'area_irrigated_reports','Area-Irrigated Reports','/ic3/irrigation/p6/area-irrigated',2,'live'),(g,'efficiency_reports','Water-Use Efficiency Reports','/ic3/irrigation/p6/efficiency-reports',3,'live'),(g,'billing_reports','Water Charges / Billing Reports','/ic3/irrigation/p6/billing-reports',4,'live'),(g,'custom_reports','Custom / Scheduled Reports','/ic3/irrigation/p6/custom-reports',5,'coming_soon') ON CONFLICT DO NOTHING; END IF;
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order) VALUES ('SYS-009',p6,'administration_trust','Administration & Trust',3) ON CONFLICT DO NOTHING RETURNING id INTO g; IF g IS NOT NULL THEN INSERT INTO ic3_nav_item(group_id,code,label,route,sort_order,status) VALUES (g,'configuration','Command-Area / Outlet Configuration','/ic3/irrigation/p6/configuration',1,'live'),(g,'registry_admin','WUA / Farmer Registry Admin','/ic3/irrigation/p6/registry-admin',2,'live'),(g,'user_role_management','User & Role Management','/ic3/irrigation/p6/user-management',3,'live'),(g,'audit_logs','Audit Logs / Data Lineage','/ic3/irrigation/p6/audit-logs',4,'live') ON CONFLICT DO NOTHING; END IF;

    INSERT INTO ic3_system_nav(system_id, layer_id, status, created_by)
    SELECT 'SYS-009', id, 'active', 'system' FROM ic3_nav_layer
    WHERE code IN ('P1','P2','P3','P4','P5','P6')
    ON CONFLICT DO NOTHING;

END $$;
`
	_, _ = pool.Exec(ctx, seedSYS009)
	fmt.Println("✓ SYS-009 (Irrigation Management) seeded!")

	fmt.Println("✓ Nav tables created and seeded!")

	// Update system labels
	updateSystemLabels(pool, ctx)
}

func updateSystemLabels(pool *pgxpool.Pool, ctx context.Context) {
	fmt.Println("Updating system labels...")

	// Add system_label column if it doesn't exist
	_, _ = pool.Exec(ctx, `ALTER TABLE ic3_system_master ADD COLUMN IF NOT EXISTS system_label VARCHAR(100)`)

	// Update existing systems
	updates := map[string]string{
		"SYS-001": "24/7",
		"SYS-002": "Realtime",
		"SYS-003": "Water Quality",
		"SYS-004": "Water Treatment Plant",
		"SYS-005": "Waste Water",
		"SYS-006": "Sewage Treatment Plant",
		"SYS-007": "Geographic Information System",
		"SYS-008": "Digital Twin system",
		"SYS-009": "Irrigation & Agri Water",
		"SYS-010": "Rural Water Supply",
		"SYS-011": "Ground Water & Source Water",
		"SYS-012": "NRW",
		"SYS-013": "Computerized Maintenance Management System",
		"SYS-014": "IC³ Platform Master",
	}

	for sysID, label := range updates {
		_, _ = pool.Exec(ctx, `UPDATE ic3_system_master SET system_label = $1 WHERE system_id = $2`, label, sysID)
	}

	// Insert missing systems (SYS-009, SYS-012, SYS-014)
	_, _ = pool.Exec(ctx, `INSERT INTO ic3_system_master (system_id, system_name, system_label, created_at)
		VALUES ($1, $2, $3, NOW()), ($4, $5, $6, NOW()), ($7, $8, $9, NOW())
		ON CONFLICT (system_id) DO NOTHING`,
		"SYS-009", "Irrigation & Agri Water", "Irrigation & Agri Water",
		"SYS-012", "NRW", "NRW",
		"SYS-014", "IC3 Platform Master", "IC³ Platform Master")

	fmt.Println("✓ System labels updated!")
}