# IC³ Navigation & Systems Database Structure

## Recommended Architecture

```
ic3_system_master (PK: system_id)
    ↓ (system_id FK)
ic3_nav_system (Bridge Table - Many-to-Many)
    ↓ (layer_id FK)
ic3_nav_layer (PK: id) - Global, not per system
    ├── id: 1, code: P1, label: "Data & Field Intelligence"
    ├── id: 2, code: P2, label: "Information with Intelligence"
    ├── id: 3, code: P3, label: "Analytics & Network Intelligence"
    ├── id: 4, code: P4, label: "AI Models & Prediction"
    ├── id: 5, code: P5, label: "Operations & Automation"
    └── id: 6, code: P6, label: "Citizen, Reporting & Governance"

ic3_nav_group (FK: nav_system_id → ic3_nav_system.id)
    ↓ (group_id FK)
ic3_nav_item (FK: group_id → ic3_nav_group.id)
    ↓ (item_id FK)
ic3_nav_role_access (FK: item_id → ic3_nav_item.id)
```

## API Endpoints

### GET `/api/scope/ic3-systems`
Loads all active system tabs from the database.

**Query (handlers_scope.go:183-188):**
```sql
SELECT system_id, system_name, system_category, COALESCE(system_label, '')
FROM ic3_system_master
WHERE is_active = true AND (system_type = 'DOMAIN' OR system_type IS NULL)
ORDER BY system_id
```

**Response:**
```json
[
  {
    "system_id": "SYS-001",
    "system_name": "24/7 Water Supply",
    "system_category": "Source & Supply",
    "system_label": "24/7"
  },
  {
    "system_id": "SYS-002",
    "system_name": "Realtime Operations",
    "system_category": "Digital Backbone",
    "system_label": "Realtime"
  },
  // ... more systems
]
```

## Table Schemas

### 1. ic3_system_master
- **system_id** (PK): SYS-001, SYS-002, etc.
- **system_name**: Full name (24/7 Water Supply, Realtime Operations, etc.)
- **system_category**: Category grouping (Source & Supply, Digital Backbone, etc.)
- **system_label**: Short label for UI (24/7, Realtime, WTP, etc.)
- **system_type**: DOMAIN or other types
- **is_active**: Boolean flag for filtering

### 2. ic3_nav_system (Bridge Table - RECOMMENDED)
**Purpose:** Many-to-Many junction between ic3_system_master and ic3_nav_layer

- **id** (PK): Auto-increment
- **system_id** (FK): References ic3_system_master
- **layer_id** (FK): References ic3_nav_layer
- **is_active**: Boolean
- **UNIQUE**: (system_id, layer_id)

**This allows:**
- Each system to have specific layers enabled
- Each layer to be shared across multiple systems
- Easy filtering: "Which layers does SYS-001 have?"

### 4. ic3_nav_layer
- **id** (PK): 1-6
- **layer_no**: 1-6 (matches id)
- **code**: P1, P2, P3, P4, P5, P6
- **label**: Display label for the layer/pillar
- **description**: What the layer does

### 5. ic3_nav_group
- **id** (PK): Auto-increment
- **nav_system_id** (FK): References ic3_nav_system.id *(currently system_id directly)*
- **code**: Unique identifier (e.g., "live_data_streams")
- **label**: Display name (e.g., "Live Data Streams")
- **icon**: Icon identifier
- **sort_order**: Display order
- **is_active**: Active/inactive flag
- **UNIQUE**: (nav_system_id, code)

### 6. ic3_nav_item
- **id** (PK): Auto-increment
- **group_id** (FK): References ic3_nav_group
- **code**: Unique identifier (e.g., "scada_telemetry")
- **label**: Display label (e.g., "SCADA / Telemetry Streams")
- **route**: Frontend route/path
- **icon**: Icon identifier
- **sort_order**: Display order
- **status**: 'live', 'coming_soon', or 'beta'
- **is_active**: Active/inactive flag
- **UNIQUE**: (group_id, code)

### 7. ic3_nav_role_access
- **id** (PK): Auto-increment
- **item_id** (FK): References ic3_nav_item
- **role_code**: SUPER_ADMIN, OPERATOR, VIEWER, CITIZEN, etc.
- **can_view**: Boolean
- **can_act**: Boolean
- **UNIQUE**: (item_id, role_code)

## Current vs Recommended Implementation

### ❌ Current Implementation
```
ic3_system_master ─FK(system_id)─→ ic3_nav_group ←FK(layer_id)─ ic3_nav_layer
```

**Issue:** ic3_nav_group serves double duty as both the system-layer junction AND the start of menu hierarchy. This couples the many-to-many relationship with the menu structure.

### ✅ Recommended Implementation
```
ic3_system_master ─FK(system_id)─→ ic3_nav_system ←FK(layer_id)─ ic3_nav_layer
                                           ↓ FK(nav_system_id)
                                    ic3_nav_group → ic3_nav_item
```

**Benefits:**
- **Separation of concerns:** Junction table separate from menu hierarchy
- **Flexibility:** Can enable/disable layers per system independently
- **Clarity:** Clear many-to-many relationship
- **Scalability:** Easier to add system-layer specific settings in future

## Data Flow

1. **Frontend loads systems** → `GET /api/scope/ic3-systems`
   - Returns systems from ic3_system_master (shown as tabs at top)

2. **User clicks a system** → Frontend stores system_id in navStore

3. **Frontend loads menu for that system** → `GET /api/scope/ic3-menu` (or similar)
   - Could query ic3_nav_group and ic3_nav_item filtered by system_id

4. **Role-based filtering** → Check ic3_nav_role_access
   - Show/hide menu items based on user's role

## Current Implementation Status

✅ **ic3_system_master** - Populated with all systems
✅ **ic3_nav_layer** - Populated with P1-P6 pillars
✅ **ic3_nav_group** - Populated with menu groups per system/layer
✅ **ic3_nav_item** - Populated with menu items
✅ **ic3_nav_role_access** - Implemented for access control

## Migrations

- **v5_1_nav_menu.sql** - Creates table structure
- **v5_1_nav_seed_24x7.sql** - Seeds 24/7 system menu
- **v5_2_nav_seed_all_systems.sql** - Seeds all systems' menus

## Suggested Migration: Add ic3_nav_system Bridge Table

```sql
-- Create ic3_nav_system bridge table (recommended)
CREATE TABLE IF NOT EXISTS ic3_nav_system (
    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,
    is_active   BOOLEAN NOT NULL DEFAULT TRUE,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (system_id, layer_id)
);

-- Migrate existing data from ic3_nav_group
INSERT INTO ic3_nav_system (system_id, layer_id, is_active)
SELECT DISTINCT system_id, layer_id, TRUE
FROM ic3_nav_group
ON CONFLICT (system_id, layer_id) DO NOTHING;

-- Add index for faster queries
CREATE INDEX IF NOT EXISTS idx_nav_system_system_layer 
ON ic3_nav_system(system_id, layer_id);

-- Update ic3_nav_group to reference ic3_nav_system instead of direct system_id
-- (This would require a migration of the foreign key relationship)
```