shopdb/docs/archive/INFRASTRUCTURE_SIMPLIFIED_FINAL.md

# Infrastructure - Simplified Final Design

**Date:** 2025-10-23
**Scope:** Only cameras track IDF relationships

---

## Simplified Schema

### IDFs (Intermediate Distribution Frames)
```sql
idfs:
  - idfid INT(11) PK
  - idfname VARCHAR(100)
  - description VARCHAR(255)
  - maptop, mapleft INT(11)
  - isactive BIT(1)
```
**Standalone** - Just a reference table for camera locations

### Cameras (Only device type with IDF relationship)
```sql
cameras:
  - cameraid INT(11) PK
  - modelid INT(11) → models → vendors
  - idfid INT(11) → idfs.idfid  (already exists!)
  - serialnumber VARCHAR(100)
  - macaddress VARCHAR(17)  (already exists!)
  - ipaddress VARCHAR(45)
  - description VARCHAR(255)
  - maptop, mapleft INT(11)
  - isactive BIT(1)
```

### Switches (No IDF)
```sql
switches:
  - switchid INT(11) PK
  - modelid INT(11) → models → vendors
  - serialnumber VARCHAR(100)
  - ipaddress VARCHAR(45)
  - description VARCHAR(255)
  - maptop, mapleft INT(11)
  - isactive BIT(1)
```

### Servers (No IDF)
```sql
servers:
  - serverid INT(11) PK
  - modelid INT(11) → models → vendors
  - serialnumber VARCHAR(100)
  - ipaddress VARCHAR(45)
  - description VARCHAR(255)
  - maptop, mapleft INT(11)
  - isactive BIT(1)
```

---

## Migration Needed

**Just run:** `add_infrastructure_vendor_model_support.sql`

This adds `modelid` to servers/switches/cameras (if not already present).

**No additional migrations needed!** Cameras already have `idfid` and `macaddress`.

---

## Edit Pages - Which Are Unique?

| Device | Unique Fields | Needs Custom Page? |
|--------|---------------|-------------------|
| **IDF** | idfname (no model/vendor) |  YES - different structure |
| **Camera** | idfid dropdown, macaddress |  YES - has IDF + MAC |
| **Server** | Standard fields only |  NO - same as switch |
| **Switch** | Standard fields only |  NO - same as server |

### Optimization: Combine Server/Switch Edit

Since servers and switches have **identical fields**, we can use:
- **1 generic edit page** for servers + switches
- **1 custom edit page** for cameras (has IDF + MAC)
- **1 custom edit page** for IDFs (no model/vendor)

---

## Page Architecture (5 Files Total!)

```
network_devices.asp                           → Unified list with tabs
network_device_detail_idf.asp?id=5           → IDF detail/edit (no model)
network_device_detail_generic.asp?type=server&id=3  → Server/Switch edit
network_device_detail_camera.asp?id=1        → Camera edit (IDF + MAC)
add_network_device.asp?type=server           → Add form with type selector
save_network_device.asp                       → Universal save
```

**Wait, that's 6 files. Can we simplify more?**

Actually, let's use **4 files** by combining add into detail:

```
network_devices.asp                     → List with tabs
device_idf.asp?id=5                    → IDF add/edit
device_generic.asp?type=server&id=3    → Server/Switch add/edit
device_camera.asp?id=1                 → Camera add/edit (IDF + MAC)
```

Each detail page handles both **add (id=0)** and **edit (id>0)**.

---

## File 1: network_devices.asp (List)

### Features
- Tabs: All | IDFs | Servers | Switches | Cameras
- Unified table showing all devices
- Click device → route to appropriate detail page

### Routing
```vbscript
Select Case rs("device_type")
    Case "IDF"
        detailUrl = "device_idf.asp?id=" & rs("device_id")
    Case "Server"
        detailUrl = "device_generic.asp?type=server&id=" & rs("device_id")
    Case "Switch"
        detailUrl = "device_generic.asp?type=switch&id=" & rs("device_id")
    Case "Camera"
        detailUrl = "device_camera.asp?id=" & rs("device_id")
End Select
```

---

## File 2: device_idf.asp (IDF Add/Edit)

### Fields
- **idfname** (text input, required)
- **description** (textarea)
- **maptop, mapleft** (optional coordinates)

### No dropdowns
IDFs are just locations with names. No model, no vendor, no parent.

### Save endpoint
Posts to `save_network_device.asp` with `type=idf`

---

## File 3: device_generic.asp (Server/Switch Add/Edit)

### Type-aware
Uses `?type=server` or `?type=switch` parameter

### Fields (Same for both!)
- **Model dropdown** (modelid → shows vendor + model)
- **Serial number** (text)
- **IP address** (text, validated)
- **Description** (textarea)
- **maptop, mapleft** (optional coordinates)

### Dynamic labels
```vbscript
Dim deviceType, displayName
deviceType = Request.QueryString("type")

If deviceType = "server" Then
    displayName = "Server"
ElseIf deviceType = "switch" Then
    displayName = "Switch"
Else
    Response.Redirect("network_devices.asp")
End If
%>

<h2><%If deviceId = 0 Then Response.Write("Add") Else Response.Write("Edit")%> <%=displayName%></h2>
```

### Save endpoint
Posts to `save_network_device.asp` with `type=server` or `type=switch`

---

## File 4: device_camera.asp (Camera Add/Edit)

### Fields (Camera-specific!)
- **Model dropdown** (modelid → shows vendor + model)
- **IDF dropdown** (idfid → required!)
- **Serial number** (text)
- **MAC address** (text, pattern validation)
- **IP address** (text, validated)
- **Description** (textarea)
- **maptop, mapleft** (optional coordinates)

### IDF Dropdown
```vbscript
<div class="form-group">
    <label>IDF Location <span class="text-danger">*</span></label>
    <select name="idfid" required class="form-control">
        <option value="">-- Select IDF --</option>
        <%
        strSQL = "SELECT idfid, idfname FROM idfs WHERE isactive = 1 ORDER BY idfname"
        Set rsIDFs = objConn.Execute(strSQL)
        Do While Not rsIDFs.EOF
        %>
            <option value="<%=rsIDFs("idfid")%>"
                    <%If Not IsNull(rs("idfid")) And rs("idfid") = rsIDFs("idfid") Then Response.Write("selected")%>>
                <%=Server.HTMLEncode(rsIDFs("idfname"))%>
            </option>
        <%
            rsIDFs.MoveNext
        Loop
        %>
    </select>
    <small class="form-text text-muted">
        Which IDF does this camera connect to?
    </small>
</div>
```

### MAC Address Field
```vbscript
<div class="form-group">
    <label>MAC Address</label>
    <input type="text" name="macaddress" class="form-control"
           pattern="^([0-9A-Fa-f]{2}[:-]){5}([0-9A-Fa-f]{2})$"
           placeholder="00:11:22:33:44:55">
</div>
```

### Save endpoint
Posts to `save_network_device.asp` with `type=camera`

---

## File 5: save_network_device.asp (Universal Save)

### Routes by type parameter
```vbscript
<%
Dim deviceType, deviceId
deviceType = Request.Form("type")
deviceId = GetSafeInteger("FORM", "id", 0, 0, 999999)

Select Case deviceType
    Case "idf"
        tableName = "idfs"
        idField = "idfid"
        ' Fields: idfname, description, maptop, mapleft
        ' No modelid!

    Case "server"
        tableName = "servers"
        idField = "serverid"
        ' Fields: modelid, serialnumber, ipaddress, description, maptop, mapleft
        ' No idfid!

    Case "switch"
        tableName = "switches"
        idField = "switchid"
        ' Fields: modelid, serialnumber, ipaddress, description, maptop, mapleft
        ' No idfid!

    Case "camera"
        tableName = "cameras"
        idField = "cameraid"
        ' Fields: modelid, idfid, serialnumber, macaddress, ipaddress, description, maptop, mapleft
        ' Has idfid and macaddress!
End Select

' Build INSERT or UPDATE query based on deviceId
If deviceId = 0 Then
    ' INSERT logic...
Else
    ' UPDATE logic...
End If
%>
```

---

## Add Flow (From network_devices.asp)

### "Add Device" Button
Shows modal or redirects to selection page:

```
[Add IDF]     → device_idf.asp?id=0
[Add Server]  → device_generic.asp?type=server&id=0
[Add Switch]  → device_generic.asp?type=switch&id=0
[Add Camera]  → device_camera.asp?id=0
```

Or use the existing approach with type selector in add_network_device.asp.

---

## Summary

### Field Comparison Table

| Field | IDF | Server | Switch | Camera |
|-------|-----|--------|--------|--------|
| idfname |  |  |  |  |
| modelid |  |  |  |  |
| idfid (parent) |  |  |  |  |
| macaddress |  |  |  |  |
| serialnumber |  |  |  |  |
| ipaddress |  |  |  |  |
| description |  |  |  |  |
| maptop, mapleft |  |  |  |  |

### Pages Needed

| Page | Handles | Reason |
|------|---------|--------|
| network_devices.asp | List all | Unified view |
| device_idf.asp | IDF add/edit | Different structure (no model) |
| device_generic.asp | Server + Switch add/edit | Identical fields |
| device_camera.asp | Camera add/edit | Unique fields (IDF + MAC) |
| save_network_device.asp | All saves | Universal endpoint |

**Total: 5 files** (or 6 if you separate add from edit)

---

## Navigation

```html
<li class="nav-header">INFRASTRUCTURE</li>
<li>
    <a href="network_devices.asp">
        <i class="zmdi zmdi-devices"></i> Network Devices
    </a>
</li>
<li>
    <a href="network_map.asp">
        <i class="zmdi zmdi-map"></i> Network Map
    </a>
</li>
```

---

## Migration Script

**Just run:** `/home/camp/projects/windows/shopdb/sql/add_infrastructure_vendor_model_support.sql`

**What it does:**
- Adds `modelid` to servers/switches/cameras (if not already present)
- Creates foreign keys to models table
- Creates `vw_network_devices` view

**What we DON'T need:**
-  Add `idfid` to switches (not tracking)
-  Add `idfid` to servers (not tracking)
-  Cameras already have `idfid` and `macaddress`

---

## Ready to Build!

**Total:** 5 ASP files
**Estimated Time:** 8-12 hours
**Complexity:** Medium (simpler than original plan!)

Next step: Run migration, then create the 5 files.