-- ============================================================
-- ADD ic3_nav_system BRIDGE TABLE
-- Many-to-Many: ic3_system_master ↔ ic3_nav_layer
-- ============================================================

-- Create bridge table to connect systems with layers
CREATE TABLE IF NOT EXISTS ic3_system_nav (
    id          SERIAL PRIMARY KEY,
    system_id   VARCHAR(50) NOT NULL REFERENCES ic3_system_master(system_id) ON DELETE CASCADE,
    layer_id    INT NOT NULL REFERENCES ic3_nav_layer(id) ON DELETE CASCADE,
    status      VARCHAR(20) NOT NULL DEFAULT 'active',
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (system_id, layer_id)
);

-- Populate bridge table from existing ic3_nav_group data
INSERT INTO ic3_system_nav (system_id, layer_id, status, created_at)
SELECT DISTINCT system_id, layer_id, 'active', CURRENT_TIMESTAMP
FROM ic3_nav_group
ON CONFLICT (system_id, layer_id) DO NOTHING;

-- Add index for faster queries by system
CREATE INDEX IF NOT EXISTS idx_system_nav_system_id ON ic3_system_nav(system_id);
CREATE INDEX IF NOT EXISTS idx_system_nav_layer_id ON ic3_system_nav(layer_id);
CREATE INDEX IF NOT EXISTS idx_system_nav_active ON ic3_system_nav(system_id, status);

-- Add comment
COMMENT ON TABLE ic3_system_nav IS 'Bridge table: Many-to-Many relationship between systems and layers (pillars)';
COMMENT ON COLUMN ic3_system_nav.system_id IS 'Reference to ic3_system_master - the system/domain';
COMMENT ON COLUMN ic3_system_nav.layer_id IS 'Reference to ic3_nav_layer - the pillar (P1-P6)';

-- Update ic3_nav_group to use nav_system_id instead of direct system_id
-- (This is optional - can be done separately to minimize changes)
-- ALTER TABLE ic3_nav_group ADD COLUMN nav_system_id INT REFERENCES ic3_nav_system(id) ON DELETE CASCADE;
-- UPDATE ic3_nav_group g SET nav_system_id = ns.id
--   FROM ic3_nav_system ns
--   WHERE g.system_id = ns.system_id AND g.layer_id = ns.layer_id;
-- ALTER TABLE ic3_nav_group DROP CONSTRAINT IF EXISTS ic3_nav_group_system_id_fkey;
-- ALTER TABLE ic3_nav_group DROP COLUMN system_id;
-- ALTER TABLE ic3_nav_group DROP COLUMN layer_id;
