-- ============================================================
-- SEED: Navigation for SYS-014 (Solution Scenarios)
-- Adds nav groups and items for layers 301 (Scenario Management)
-- and 302 (Analysis & Impact Assessment)
-- ============================================================

-- Ensure layers 301 and 302 exist with proper definitions
-- If they don't exist, create them
INSERT INTO ic3_nav_layer (id, layer_no, code, label)
VALUES
    (301, 7, 'SS-P1', 'Scenario Management & Monitoring'),
    (302, 8, 'SS-P2', 'Analysis & Impact Assessment')
ON CONFLICT (id) DO UPDATE SET
    code = EXCLUDED.code,
    label = EXCLUDED.label;

-- Ensure SYS-014 is linked to these layers in ic3_system_nav
INSERT INTO ic3_system_nav (system_id, layer_id, status)
VALUES
    ('SYS-014', 301, 'active'),
    ('SYS-014', 302, 'active')
ON CONFLICT (system_id, layer_id) DO NOTHING;

DO $$
DECLARE
    p1 INT := 301;  -- Layer 301: Scenario Management & Monitoring
    p2 INT := 302;  -- Layer 302: Analysis & Impact Assessment
    g INT;
BEGIN
    -- Clean up existing nav groups/items (keep bridge entries intact)
    DELETE FROM ic3_nav_item WHERE group_id IN (SELECT id FROM ic3_nav_group WHERE system_id = 'SYS-014');
    DELETE FROM ic3_nav_group WHERE system_id = 'SYS-014';

    -- P1: Scenario Management & Monitoring
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order)
    VALUES ('SYS-014',p1,'scenario_management','Scenario 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,'active_scenarios','Active Scenarios','/ic3/scenarios/p1/active',1,'active'),
        (g,'scenario_library','Scenario Library','/ic3/scenarios/p1/library',2,'active'),
        (g,'scenario_status','Scenario Status & Performance','/ic3/scenarios/p1/status',3,'active')
        ON CONFLICT DO NOTHING;
    END IF;

    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order)
    VALUES ('SYS-014',p1,'real_time_monitoring','Real-Time 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,'scenario_metrics','Scenario Metrics Dashboard','/ic3/scenarios/p1/metrics',1,'active'),
        (g,'alert_events','Alerts & Events','/ic3/scenarios/p1/alerts',2,'active'),
        (g,'performance_tracking','Performance Tracking','/ic3/scenarios/p1/performance',3,'active')
        ON CONFLICT DO NOTHING;
    END IF;

    -- P2: Analysis & Impact Assessment
    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order)
    VALUES ('SYS-014',p2,'impact_analysis','Impact Analysis',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,'cost_benefit','Cost-Benefit Analysis','/ic3/scenarios/p2/cost-benefit',1,'active'),
        (g,'system_impact','System Impact Assessment','/ic3/scenarios/p2/impact',2,'active'),
        (g,'risk_analysis','Risk Analysis','/ic3/scenarios/p2/risk',3,'active')
        ON CONFLICT DO NOTHING;
    END IF;

    INSERT INTO ic3_nav_group(system_id,layer_id,code,label,sort_order)
    VALUES ('SYS-014',p2,'scenario_comparison','Scenario Comparison',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,'compare_scenarios','Compare Scenarios','/ic3/scenarios/p2/compare',1,'active'),
        (g,'what_if_analysis','What-If Analysis','/ic3/scenarios/p2/what-if',2,'active'),
        (g,'outcome_modeling','Outcome Modeling','/ic3/scenarios/p2/modeling',3,'active')
        ON CONFLICT DO NOTHING;
    END IF;

    RAISE NOTICE 'Successfully seeded SYS-014 (Solution Scenarios) navigation with 2 pillars';
END $$;
