# Site Roster WX Location Code Implementation Plan

> **For agentic workers:** REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (`- [ ]`) syntax for tracking.

**Goal:** Add a `locationCode` column (WX-format, e.g. `WX0101`) to the Site Roster tab and Excel export, derived from `locationInfo.locationID` in the MySQL view.

**Architecture:** The WX code is computed once in `v_location_employees` via a CASE expression — two legacy regional entries (locationID 0 and 9991) map to `WX0099`, all others use `CONCAT('WX', LPAD(locationID, 4, '0'))`. The API, UI table, and Excel export all read from the view; only the view and API need code changes for the column to flow through.

**Tech Stack:** MySQL (view ALTER), PHP 8 (mysqli), vanilla JS, PhpSpreadsheet (export — no change needed)

---

## File Map

| File | Change |
|------|--------|
| `v_location_employees` (MySQL view) | Add `locationCode` CASE column as position 2 |
| `public/api/search_site_list.php` | Add `locationCode` to `$COLUMNS` string and `$rows[]` array |
| `public/home.php` lines 648–668 | Add `<th>WX Code</th>` after `<th>Location</th>` |
| `public/home.php` lines 671–708 | Add `<td>locationCode</td>` after `locationNameFull` td in PHP loop |
| `public/home.php` lines 835, 860–894 | Bump `COLS` 22→23; add `locationCode` td in JS `renderRows()` |
| `public/assets/tools/site_export.php` | **No change** — uses `SELECT *`, inherits new column automatically |

---

## Task 1: ALTER the MySQL view to add `locationCode`

**Files:**
- Modify: `v_location_employees` (MySQL view — run on production DB)

- [ ] **Step 1: Get the current view definition**

Connect to the production MySQL instance and run:

```sql
SHOW CREATE VIEW v_location_employees\G
```

Copy the full `CREATE VIEW` statement from the output. You will need it in the next step.

- [ ] **Step 2: Identify the insert point**

In the SELECT clause of the view definition, find the line that selects `locationNameFull` (it will look something like `li.locationNameFull` or just `locationNameFull`). The new `locationCode` expression goes immediately after it.

- [ ] **Step 3: Build and run the CREATE OR REPLACE VIEW**

Take the definition from Step 1 and insert the following CASE block as the second selected expression (right after `locationNameFull`):

```sql
CASE
  WHEN li.locationID IN (0, 9991) THEN 'WX0099'
  ELSE CONCAT('WX', LPAD(li.locationID, 4, '0'))
END AS locationCode,
```

Then run:

```sql
CREATE OR REPLACE VIEW v_location_employees AS
  [your full modified SELECT here];
```

- [ ] **Step 4: Verify the column is present and correct**

```sql
SELECT locationNameFull, locationCode
FROM v_location_employees
ORDER BY locationNameFull
LIMIT 20;
```

Expected — spot-check these rows:

| locationNameFull | locationCode |
|------------------|--------------|
| 101 - Tomball    | WX0101       |
| 99 - Corporate   | WX0099       |
| 999 - Midwest    | WX0099       |
| 999 - Southwest  | WX0099       |
| 901 - Goose Creek | WX0901      |

- [ ] **Step 5: Verify export query still works**

```sql
SELECT COUNT(*) FROM v_location_employees;
```

Expected: same row count as before (no rows dropped by the change).

---

## Task 2: Update `search_site_list.php` to return `locationCode`

**Files:**
- Modify: `public/api/search_site_list.php`

- [ ] **Step 1: Add `locationCode` to the `$COLUMNS` string**

Open `public/api/search_site_list.php`. Find the `$COLUMNS` assignment (lines 31–63). Add `locationCode,` immediately after `locationNameFull,`:

```php
    $COLUMNS = "
    locationNameFull,
    locationCode,
    address,
    storeEmail,
    phoneNumber,

    GM_name,
    GM_email,
    GM_phone,

    SM_name,
    SM_email,
    SM_phone,

    adName,
    adEmail,
    adPhone,

    rdName,
    rdEmail,
    rdPhone,

    regionalFacilitiesManager,
    maintenanceLead,
    maintenanceTech,
    maintenanceTechEmail,

    opName,
    opNameEmail,

    market_partner,
    market_partner_email
  ";
```

- [ ] **Step 2: Add `locationCode` to the `$rows[]` output array**

Find the `while ($row = $res->fetch_assoc())` block (lines 119–154). Add the `locationCode` entry immediately after `locationNameFull`:

```php
        $rows[] = [
            'locationNameFull'           => $row['locationNameFull'] ?? '',
            'locationCode'               => $row['locationCode']     ?? '',
            'address'                    => $row['address']          ?? '',
            'storeEmail'                 => $row['storeEmail']       ?? '',
            'phoneNumber'                => $row['phoneNumber']      ?? '',

            'GM_name'                    => $row['GM_name']          ?? '',
            'GM_email'                   => $row['GM_email']         ?? '',
            'GM_phone'                   => $row['GM_phone']         ?? '',

            'SM_name'                    => $row['SM_name']          ?? '',
            'SM_email'                   => $row['SM_email']         ?? '',
            'SM_phone'                   => $row['SM_phone']         ?? '',

            'adName'                     => $row['adName']           ?? '',
            'adEmail'                    => $row['adEmail']          ?? '',
            'adPhone'                    => $row['adPhone']          ?? '',

            'rdName'                     => $row['rdName']           ?? '',
            'rdEmail'                    => $row['rdEmail']          ?? '',
            'rdPhone'                    => $row['rdPhone']          ?? '',

            'regionalFacilitiesManager'  => $row['regionalFacilitiesManager']  ?? '',
            'maintenanceLead'            => $row['maintenanceLead']            ?? '',
            'maintenanceTech'            => $row['maintenanceTech']            ?? '',
            'maintenanceTechEmail'       => $row['maintenanceTechEmail']       ?? '',

            'opName'                     => $row['opName']           ?? '',
            'opNameEmail'                => $row['opNameEmail']       ?? '',

            'market_partner'             => $row['market_partner']        ?? '',
            'market_partner_email'       => $row['market_partner_email']  ?? '',
        ];
```

- [ ] **Step 3: Verify the API response locally**

Hit the endpoint in a browser or with curl:

```
http://whitewater-secure/public/api/search_site_list.php?q=Tomball
```

Expected JSON (abbreviated):

```json
{
  "ok": true,
  "results": [
    {
      "locationNameFull": "101 - Tomball",
      "locationCode": "WX0101",
      ...
    }
  ]
}
```

Confirm `locationCode` is present and correct before moving on.

---

## Task 3: Update `home.php` — Tab 2 `<thead>`

**Files:**
- Modify: `public/home.php` (~line 648)

- [ ] **Step 1: Add `<th>WX Code</th>` after `<th>Location</th>`**

Find the Site Roster `<thead>` block (around line 648). The current first two `<th>` cells are:

```html
<th>Location</th>
<th>Address</th>
```

Change to:

```html
<th>Location</th>
<th>WX Code</th>
<th>Address</th>
```

---

## Task 4: Update `home.php` — Tab 2 PHP `<tbody>` render loop

**Files:**
- Modify: `public/home.php` (~line 673)

- [ ] **Step 1: Add `locationCode` td after the `locationNameFull` td**

Find the PHP `foreach ($rows2 as $row2)` loop (around line 671). The current first two `<td>` cells are:

```php
<td><?= htmlspecialchars($row2['locationNameFull'] ?? '') ?></td>
<td><?= htmlspecialchars($row2['address']          ?? '') ?></td>
```

Change to:

```php
<td><?= htmlspecialchars($row2['locationNameFull'] ?? '') ?></td>
<td><?= htmlspecialchars($row2['locationCode']     ?? '') ?></td>
<td><?= htmlspecialchars($row2['address']          ?? '') ?></td>
```

---

## Task 5: Update `home.php` — Tab 2 JS `renderRows()`

**Files:**
- Modify: `public/home.php` (~lines 835, 862)

- [ ] **Step 1: Bump `COLS` from 22 to 23**

Find (around line 835):

```js
const COLS = 22;
```

Change to:

```js
const COLS = 23;
```

- [ ] **Step 2: Add `locationCode` td to the JS row template**

Find the `renderRows` map template (around line 862). The current first two `<td>` cells are:

```js
        <td>${escapeHtml(r.locationNameFull)}</td>
        <td>${escapeHtml(r.address)}</td>
```

Change to:

```js
        <td>${escapeHtml(r.locationNameFull)}</td>
        <td>${escapeHtml(r.locationCode)}</td>
        <td>${escapeHtml(r.address)}</td>
```

---

## Task 6: Smoke test end-to-end

No code changes — verification only.

- [ ] **Step 1: Load the intranet home page locally**

Open `http://whitewater-secure/public/home.php` and click the **Site Roster** tab.

Expected:
- A new **WX Code** column appears between Location and Address
- Values look like `WX0101`, `WX0402`, `WX0901` etc.
- "999 - Midwest" and "999 - Southwest" rows both show `WX0099`
- "99 - Corporate" row shows `WX0099`
- Column count in the table matches header (no misaligned cells)

- [ ] **Step 2: Test search**

Type `Tomball` in the Site Roster search box.

Expected: Row shows `WX0101` in the WX Code column.

- [ ] **Step 3: Test "No matches" path**

Type a nonsense string (e.g. `zzzzz`).

Expected: "No matches." spans all 23 columns cleanly (no layout break).

- [ ] **Step 4: Test the Excel export**

Click **Export All Sites**. Open the downloaded `.xlsx`.

Expected: `locationCode` column is present with correct WX values.

- [ ] **Step 5: Commit**

```bash
git add public/api/search_site_list.php public/home.php
git commit -m "feat: add WX location code column to Site Roster tab and export"
```

Note: The view ALTER was run directly on the DB — no file to stage for that change.