shopdb/docs/archive/INFRASTRUCTURE_SUPPORT_IMPLEMENTATION.md

# Infrastructure Vendor/Model Support - Implementation Guide

**Date:** 2025-10-23
**Status:** Ready for Implementation
**Scope:** Add vendor/model tracking for servers, switches, and cameras

---

## Executive Summary

**Goal:** Extend the existing vendor/model system (currently used for PCs, Printers, and Machines) to also support infrastructure devices (Servers, Switches, Cameras).

**Decision:**  **Vendor types ABANDONED** - Keeping the simple vendors table as-is. No boolean flag refactoring needed.

### What We're Building

| Feature | Status | Impact |
|---------|--------|--------|
| Add `modelid` to servers/switches/cameras |  Script ready | Database schema |
| Create `vw_network_devices` view |  Script ready | Unified infrastructure query |
| Create server CRUD pages |  New development | 4 files |
| Create switch CRUD pages |  New development | 4 files |
| Create camera CRUD pages |  New development | 4 files |
| Update navigation |  New development | Menu items |
| Update network map |  Optional | Display vendor/model |

**Total New Files:** 12 ASP pages + nav updates
**Total Modified Files:** ~2-3 (navigation, possibly network_map.asp)
**Estimated Time:** 16-24 hours

---

## Part 1: Database Schema Changes

### Migration Script
**File:** `/home/camp/projects/windows/shopdb/sql/add_infrastructure_vendor_model_support.sql`

### What It Does

1. **Adds `modelid` column to infrastructure tables:**
   ```sql
   servers.modelid   → models.modelnumberid (FK)
   switches.modelid  → models.modelnumberid (FK)
   cameras.modelid   → models.modelnumberid (FK)
   ```

2. **Creates unified view for infrastructure:**
   ```sql
   CREATE VIEW vw_network_devices AS
   SELECT 'Server' AS device_type, serverid, modelid, modelnumber, vendor, ...
   FROM servers LEFT JOIN models LEFT JOIN vendors
   UNION ALL
   SELECT 'Switch' AS device_type, switchid, modelid, modelnumber, vendor, ...
   FROM switches LEFT JOIN models LEFT JOIN vendors
   UNION ALL
   SELECT 'Camera' AS device_type, cameraid, modelid, modelnumber, vendor, ...
   FROM cameras LEFT JOIN models LEFT JOIN vendors
   ```

### Tables After Migration

**servers table:**
```
serverid INT(11) PK AUTO_INCREMENT
modelid INT(11) FK → models.modelnumberid  ← NEW!
serialnumber VARCHAR(100)
ipaddress VARCHAR(15)
description VARCHAR(255)
maptop INT(11)
mapleft INT(11)
isactive BIT(1)
```

**switches table:**
```
switchid INT(11) PK AUTO_INCREMENT
modelid INT(11) FK → models.modelnumberid  ← NEW!
serialnumber VARCHAR(100)
ipaddress VARCHAR(15)
description VARCHAR(255)
maptop INT(11)
mapleft INT(11)
isactive BIT(1)
```

**cameras table:**
```
cameraid INT(11) PK AUTO_INCREMENT
modelid INT(11) FK → models.modelnumberid  ← NEW!
serialnumber VARCHAR(100)
ipaddress VARCHAR(15)
description VARCHAR(255)
maptop INT(11)
mapleft INT(11)
isactive BIT(1)
```

---

## Part 2: Required New Pages

### Server Management Pages (4 files)

#### 1. displayservers.asp - Server List View
**Purpose:** Display all servers in a searchable table
**Similar to:** displayprinters.asp, displaymachines.asp

**Key Features:**
- Sortable table with columns: ID, Model, Vendor, Serial, IP, Description, Status
- Search/filter functionality
- "Add New Server" button
- Click row → displayserver.asp (detail page)

**SQL Query:**
```sql
SELECT s.serverid, s.serialnumber, s.ipaddress, s.description, s.isactive,
       m.modelnumber, v.vendor
FROM servers s
LEFT JOIN models m ON s.modelid = m.modelnumberid
LEFT JOIN vendors v ON m.vendorid = v.vendorid
WHERE s.isactive = 1
ORDER BY s.serverid DESC
```

#### 2. displayserver.asp - Server Detail with Inline Edit
**Purpose:** Show server details with inline edit form
**Similar to:** displayprinter.asp, displaymachine.asp

**Key Features:**
- Display mode: Show all server info with Edit button
- Edit mode: Inline form to update server
- Model/Vendor dropdown selection
- Save button → saveserver_direct.asp
- Delete/deactivate functionality

**SQL Query (Display):**
```sql
SELECT s.*, m.modelnumber, v.vendor, v.vendorid
FROM servers s
LEFT JOIN models m ON s.modelid = m.modelnumberid
LEFT JOIN vendors v ON m.vendorid = v.vendorid
WHERE s.serverid = ?
```

#### 3. addserver.asp - Add New Server Form
**Purpose:** Form to add a new server
**Similar to:** addprinter.asp, addmachine.asp

**Key Features:**
- Model dropdown (filtered from models table)
- Vendor dropdown (auto-filled based on model or separate selector)
- Serial number input (text)
- IP address input (validated)
- Description textarea
- Map coordinates (optional, maptop/mapleft)
- Submit → saveserver_direct.asp

**Model Dropdown Query:**
```sql
SELECT m.modelnumberid, m.modelnumber, v.vendor
FROM models m
INNER JOIN vendors v ON m.vendorid = v.vendorid
WHERE m.isactive = 1
ORDER BY v.vendor, m.modelnumber
```

**Or separate vendor/model selection:**
```sql
-- Step 1: Select vendor
SELECT vendorid, vendor FROM vendors WHERE isactive = 1 ORDER BY vendor

-- Step 2: Select model (filtered by vendorid)
SELECT modelnumberid, modelnumber FROM models
WHERE vendorid = ? AND isactive = 1 ORDER BY modelnumber
```

#### 4. saveserver_direct.asp - Server Save Endpoint
**Purpose:** Backend processor to insert/update server
**Similar to:** saveprinter_direct.asp, savemachine_direct.asp

**Key Features:**
- Validate all inputs using validation.asp functions
- INSERT for new server
- UPDATE for existing server
- Return JSON response or redirect
- Error handling

**Insert Query:**
```sql
INSERT INTO servers (modelid, serialnumber, ipaddress, description, maptop, mapleft, isactive)
VALUES (?, ?, ?, ?, ?, ?, 1)
```

**Update Query:**
```sql
UPDATE servers
SET modelid = ?, serialnumber = ?, ipaddress = ?, description = ?,
    maptop = ?, mapleft = ?
WHERE serverid = ?
```

### Switch Management Pages (4 files)

Same structure as servers, just replace "server" with "switch":
- **displayswitches.asp** - Switch list
- **displayswitch.asp** - Switch detail with inline edit
- **addswitch.asp** - Add switch form
- **saveswitch_direct.asp** - Switch save endpoint

### Camera Management Pages (4 files)

Same structure, replace with "camera":
- **displaycameras.asp** - Camera list
- **displaycamera.asp** - Camera detail with inline edit
- **addcamera.asp** - Add camera form
- **savecamera_direct.asp** - Camera save endpoint

---

## Part 3: Navigation Updates

### Add Menu Items

**File to modify:** `includes/leftsidebar.asp` (or wherever main nav is)

**New menu section:**
```html
<!-- Infrastructure -->
<li class="nav-header">INFRASTRUCTURE</li>
<li><a href="displayservers.asp"><i class="zmdi zmdi-storage"></i> Servers</a></li>
<li><a href="displayswitches.asp"><i class="zmdi zmdi-device-hub"></i> Switches</a></li>
<li><a href="displaycameras.asp"><i class="zmdi zmdi-videocam"></i> Cameras</a></li>
```

Or add to existing "Network" or "Devices" section.

---

## Part 4: Optional Enhancements

### Update network_map.asp
If network_map.asp currently exists and displays network topology:
- Add server/switch/camera markers to the map
- Display vendor/model on hover/click
- Use vw_network_devices view for unified query

**Query for map:**
```sql
SELECT device_type, device_id, vendor, modelnumber,
       ipaddress, description, maptop, mapleft
FROM vw_network_devices
WHERE isactive = 1 AND maptop IS NOT NULL AND mapleft IS NOT NULL
```

---

## Part 5: Code Templates

### Template 1: Infrastructure List Page (displayservers.asp)
```vbscript
<!--#include file="./includes/sql.asp"-->
<!--#include file="./includes/header.asp"-->
<!--#include file="./includes/leftsidebar.asp"-->
<!--#include file="./includes/topbarheader.asp"-->

<%
' Fetch all servers with model/vendor
Dim strSQL, rs
strSQL = "SELECT s.serverid, s.serialnumber, s.ipaddress, s.description, s.isactive, " & _
         "m.modelnumber, v.vendor " & _
         "FROM servers s " & _
         "LEFT JOIN models m ON s.modelid = m.modelnumberid " & _
         "LEFT JOIN vendors v ON m.vendorid = v.vendorid " & _
         "WHERE s.isactive = 1 " & _
         "ORDER BY s.serverid DESC"
Set rs = objConn.Execute(strSQL)
%>

<div class="content-wrapper">
    <h2>Servers <a href="addserver.asp" class="btn btn-primary">Add Server</a></h2>

    <table class="table table-striped">
        <thead>
            <tr>
                <th>ID</th>
                <th>Vendor</th>
                <th>Model</th>
                <th>Serial Number</th>
                <th>IP Address</th>
                <th>Description</th>
                <th>Actions</th>
            </tr>
        </thead>
        <tbody>
        <% Do While Not rs.EOF %>
            <tr>
                <td><%=rs("serverid")%></td>
                <td><%=Server.HTMLEncode(rs("vendor") & "")%></td>
                <td><%=Server.HTMLEncode(rs("modelnumber") & "")%></td>
                <td><%=Server.HTMLEncode(rs("serialnumber") & "")%></td>
                <td><%=Server.HTMLEncode(rs("ipaddress") & "")%></td>
                <td><%=Server.HTMLEncode(rs("description") & "")%></td>
                <td><a href="displayserver.asp?serverid=<%=rs("serverid")%>">View</a></td>
            </tr>
        <%
            rs.MoveNext
        Loop
        rs.Close
        Set rs = Nothing
        %>
        </tbody>
    </table>
</div>

<!--#include file="./includes/footer.asp"-->
```

### Template 2: Add Infrastructure Device Form (addserver.asp)
```vbscript
<!--#include file="./includes/sql.asp"-->
<!--#include file="./includes/header.asp"-->
<!--#include file="./includes/leftsidebar.asp"-->
<!--#include file="./includes/topbarheader.asp"-->

<div class="content-wrapper">
    <h2>Add Server</h2>

    <form method="post" action="saveserver_direct.asp">
        <div class="form-group">
            <label>Model</label>
            <select name="modelid" required class="form-control">
                <option value="">-- Select Model --</option>
                <%
                Dim strSQL, rsModels
                strSQL = "SELECT m.modelnumberid, m.modelnumber, v.vendor " & _
                         "FROM models m " & _
                         "INNER JOIN vendors v ON m.vendorid = v.vendorid " & _
                         "WHERE m.isactive = 1 " & _
                         "ORDER BY v.vendor, m.modelnumber"
                Set rsModels = objConn.Execute(strSQL)
                Do While Not rsModels.EOF
                %>
                    <option value="<%=rsModels("modelnumberid")%>">
                        <%=Server.HTMLEncode(rsModels("vendor") & " - " & rsModels("modelnumber"))%>
                    </option>
                <%
                    rsModels.MoveNext
                Loop
                rsModels.Close
                Set rsModels = Nothing
                %>
            </select>
        </div>

        <div class="form-group">
            <label>Serial Number</label>
            <input type="text" name="serialnumber" class="form-control" maxlength="100">
        </div>

        <div class="form-group">
            <label>IP Address</label>
            <input type="text" name="ipaddress" class="form-control" maxlength="15"
                   pattern="^[0-9\.]+$" placeholder="192.168.1.100">
        </div>

        <div class="form-group">
            <label>Description</label>
            <textarea name="description" class="form-control" rows="3"></textarea>
        </div>

        <button type="submit" class="btn btn-success">Save Server</button>
        <a href="displayservers.asp" class="btn btn-secondary">Cancel</a>
    </form>
</div>

<!--#include file="./includes/footer.asp"-->
```

### Template 3: Save Infrastructure Device (saveserver_direct.asp)
```vbscript
<!--#include file="./includes/sql.asp"-->
<!--#include file="./includes/error_handler.asp"-->
<!--#include file="./includes/validation.asp"-->
<!--#include file="./includes/db_helpers.asp"-->

<%
' Validate inputs
Dim modelid, serialnumber, ipaddress, description
modelid = GetSafeInteger("FORM", "modelid", 0, 1, 999999)
serialnumber = GetSafeString("FORM", "serialnumber", "", 0, 100, "^[A-Za-z0-9\-]+$")
ipaddress = GetSafeString("FORM", "ipaddress", "", 0, 15, "^[0-9\.]+$")
description = GetSafeString("FORM", "description", "", 0, 255, "")

' Validate required fields
If modelid = 0 Then
    Response.Write("Error: Model is required")
    Response.End
End If

' Insert server
Dim strSQL
strSQL = "INSERT INTO servers (modelid, serialnumber, ipaddress, description, isactive) " & _
         "VALUES (?, ?, ?, ?, 1)"

Set rs = ExecuteParameterizedQuery(objConn, strSQL, Array(modelid, serialnumber, ipaddress, description))

Call CleanupResources()

' Redirect to list
Response.Redirect("displayservers.asp")
%>
```

---

## Part 6: Implementation Checklist

### Phase 1: Database Migration
- [ ] Review `add_infrastructure_vendor_model_support.sql`
- [ ] Backup database
- [ ] Run migration on test database
- [ ] Verify `modelid` columns added to servers/switches/cameras
- [ ] Verify foreign keys created
- [ ] Verify `vw_network_devices` view created
- [ ] Test view returns correct data

### Phase 2: Server Pages (Do This First)
- [ ] Create `displayservers.asp` (list view)
- [ ] Create `addserver.asp` (add form)
- [ ] Create `saveserver_direct.asp` (save endpoint)
- [ ] Create `displayserver.asp` (detail with inline edit)
- [ ] Test: Add new server
- [ ] Test: Edit existing server
- [ ] Test: View server list

### Phase 3: Switch Pages
- [ ] Create `displayswitches.asp` (list view)
- [ ] Create `addswitch.asp` (add form)
- [ ] Create `saveswitch_direct.asp` (save endpoint)
- [ ] Create `displayswitch.asp` (detail with inline edit)
- [ ] Test: Add/edit/view switches

### Phase 4: Camera Pages
- [ ] Create `displaycameras.asp` (list view)
- [ ] Create `addcamera.asp` (add form)
- [ ] Create `savecamera_direct.asp` (save endpoint)
- [ ] Create `displaycamera.asp` (detail with inline edit)
- [ ] Test: Add/edit/view cameras

### Phase 5: Navigation & Polish
- [ ] Add menu items to navigation
- [ ] Test all navigation links
- [ ] Update dashboard (optional - add infrastructure stats)
- [ ] Update search (optional - add infrastructure to search results)

### Phase 6: Optional Enhancements
- [ ] Update `network_map.asp` to show infrastructure devices
- [ ] Add infrastructure reports (count by vendor, etc.)
- [ ] Add bulk import for infrastructure (CSV upload)

### Phase 7: Documentation & Deployment
- [ ] Update user documentation
- [ ] Update technical documentation
- [ ] Test on production-like data
- [ ] Create deployment checklist
- [ ] Deploy to production

---

## Part 7: Testing Plan

### Unit Tests (Per Device Type)
- [ ] Can add device with valid model
- [ ] Can add device without model (modelid NULL)
- [ ] Can edit device and change model
- [ ] Can delete/deactivate device
- [ ] Form validation works (IP format, required fields)
- [ ] SQL injection prevention (parameterized queries)

### Integration Tests
- [ ] Device appears in list immediately after creation
- [ ] Device detail page shows vendor/model info correctly
- [ ] Model dropdown only shows active models
- [ ] Vendor name displays correctly (from model FK)
- [ ] Map coordinates save/display correctly

### Data Integrity Tests
- [ ] Foreign keys enforce referential integrity
- [ ] Deleting a model doesn't break device (ON DELETE SET NULL)
- [ ] View `vw_network_devices` returns all device types
- [ ] NULL model handling (device with no model assigned)

---

## Part 8: Rollback Plan

If issues arise:
1. Migration script is **non-destructive** - only adds columns, doesn't modify existing data
2. Can drop columns: `ALTER TABLE servers DROP COLUMN modelid`
3. Can drop view: `DROP VIEW vw_network_devices`
4. New ASP pages can be deleted without affecting existing functionality
5. Navigation changes can be reverted

**Risk Level:** LOW - This is pure additive functionality, no changes to existing code.

---

## Part 9: Time Estimates

| Task | Time | Notes |
|------|------|-------|
| Database migration | 30 min | Run script + verify |
| Server pages (4 files) | 4-6 hours | First set, establish pattern |
| Switch pages (4 files) | 2-3 hours | Copy/modify from servers |
| Camera pages (4 files) | 2-3 hours | Copy/modify from servers |
| Navigation updates | 30 min | Add menu items |
| Testing | 3-4 hours | Full testing cycle |
| Documentation | 1-2 hours | User guide updates |
| **Total** | **13-19 hours** | ~2-3 days of work |

---

## Part 10: Success Criteria

 **Database:**
- All 3 tables have modelid column with FK to models
- vw_network_devices view returns data from all 3 tables

 **Functionality:**
- Can add/edit/view/delete servers, switches, cameras
- Vendor/model information displays correctly
- Forms validate inputs properly
- No SQL errors

 **User Experience:**
- Navigation easy to find
- Forms intuitive (like printer/machine forms)
- List views show relevant info at a glance

 **Code Quality:**
- Follows existing coding standards (STANDARDS.md)
- Uses parameterized queries (no SQL injection)
- Proper error handling
- Consistent with printer/machine patterns

---

## Next Steps

1. **Get approval** on this simplified approach
2. **Run database migration** on test environment
3. **Start with server pages** - establish the pattern
4. **Copy/adapt for switches and cameras** - reuse code
5. **Test thoroughly**
6. **Document and deploy**

---

**Document Status:** Ready for Implementation
**Last Updated:** 2025-10-23
**Approved By:** _[Pending]_