# Site Directory WX Location Display 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 WX-formatted location display to both the Site Roster and Company Directory tabs — via a new `locationNameWX` view column and a `wx_location_code` computed JOIN column — replacing the plain numeric location display in both tabs.

**Architecture:** `locationNameWX` is added to `v_location_employees` using the already-present `locationCode` column as the WX prefix. Company Directory gets a `LEFT JOIN locationInfo` in both `home.php` and `search_directory.php` to compute `wx_location_code` using the canonical CASE expression. All consumers (PHP render, JS renderRows, search APIs) are updated to use the new column names. Sorting and search filtering are updated to match displayed values.

**Tech Stack:** MySQL (view ALTER), PHP 8.x, vanilla JS (no framework)

---

## File Map

| File | Change |
|------|--------|
| `v_location_employees` (DB view) | Add `locationNameWX` column |
| `public/api/search_site_list.php` | Add `locationNameWX` to SELECT, response, search filter, ORDER BY |
| `public/home.php` lines 38–45 | Add LEFT JOIN + `wx_location_code` to Tab 1 query |
| `public/home.php` line 619 | Swap `locationCode` → `wx_location_code` in Tab 1 PHP render |
| `public/home.php` line 674 | Swap `locationNameFull` → `locationNameWX` in Tab 2 PHP render |
| `public/home.php` line 780 | Swap `locationCode` → `wx_location_code` in Tab 1 JS renderRows |
| `public/home.php` line 864 | Swap `locationNameFull` → `locationNameWX` in Tab 2 JS renderRows |
| `public/api/search_directory.php` | Add LEFT JOIN + `wx_location_code` to all queries; update search filter |

---

## Task 1: ALTER v_location_employees — add locationNameWX

**Files:**
- Modify: `v_location_employees` (MySQL view — run via MySQL client or MCP)

- [ ] **Step 1: Confirm current view definition**

Run in MySQL:
```sql
SHOW CREATE VIEW v_location_employees\G
```
Copy the current CREATE VIEW statement — you'll need it for the ALTER.

- [ ] **Step 2: Verify locationCode and locationNameFull are present**

```sql
SELECT locationNameFull, locationCode
FROM v_location_employees
LIMIT 5;
```
Expected: rows like `"101 - Tomball"` and `"WX0101"`. If either column is missing, stop — the view is not in the expected state.

- [ ] **Step 3: ALTER the view to add locationNameWX**

Add `locationNameWX` as the column immediately after `locationCode` in the SELECT list of the view. The expression uses `locationCode` (already computed with WX prefix) and strips the numeric prefix from `locationNameFull` using `LOCATE`:

```sql
CONCAT(locationCode, SUBSTRING(locationNameFull, LOCATE(' - ', locationNameFull))) AS locationNameWX,
```

In context — this line goes directly after the `locationCode` expression in the view's SELECT. The full ALTER VIEW must regenerate the entire view body with this column inserted. Use the output from Step 1 as the base, inserting the new line after `locationCode`.

- [ ] **Step 4: Verify the new column**

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

Expected output examples:

| locationNameFull | locationCode | locationNameWX |
|------------------|--------------|----------------|
| 101 - Tomball | WX0101 | WX0101 - Tomball |
| 99 - Corporate | WX0099 | WX0099 - Corporate |
| 999 - Southwest | WX0099 | WX0099 - Southwest |

- [ ] **Step 5: Commit**

No code file changes in this task — document the view change in git via a comment in the migration notes or simply note it in the commit message.

```bash
git commit --allow-empty -m "db: add locationNameWX to v_location_employees view"
```

---

## Task 2: Update search_site_list.php — add locationNameWX

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

- [ ] **Step 1: Add locationNameWX to $COLUMNS**

Line 31–64. Add `locationNameWX,` immediately after `locationNameFull,`:

```php
$COLUMNS = "
    locationNameFull,
    locationNameWX,
    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: Swap search filter to locationNameWX**

Line 76–85. Replace `locationNameFull LIKE ?` with `locationNameWX LIKE ?` in the WHERE clause:

```php
$where = "WHERE
      locationNameWX LIKE ?
      OR GM_name LIKE ?
      OR SM_name LIKE ?
      OR adName LIKE ?
      OR rdName LIKE ?
      OR maintenanceLead LIKE ?
      OR maintenanceTech LIKE ?
      OR opName LIKE ?
      OR market_partner LIKE ?";
```

- [ ] **Step 3: Swap ORDER BY to locationNameWX**

Line 107. Replace `ORDER BY locationNameFull ASC` with:

```php
$sql = "SELECT {$COLUMNS} FROM {$TABLE} {$where}
      ORDER BY locationNameWX ASC
      LIMIT {$limit}";
```

- [ ] **Step 4: Add locationNameWX to $rows[] output**

Line 122–155. Add the new entry immediately after `'locationNameFull'`:

```php
$rows[] = [
    'locationNameFull'           => $row['locationNameFull'] ?? '',
    'locationNameWX'             => $row['locationNameWX']   ?? '',
    '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 5: Verify via browser**

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

Expected: JSON response with `locationNameWX` field present in results, e.g. `"locationNameWX":"WX0101 - Tomball"`. Results should be sorted by WX code (WX0099 first, WX0101, WX0102, etc.).

- [ ] **Step 6: Commit**

```bash
git add public/api/search_site_list.php
git commit -m "feat: add locationNameWX to search_site_list API — column, search filter, sort"
```

---

## Task 3: Update home.php Tab 2 — Site Roster display

**Files:**
- Modify: `public/home.php`

- [ ] **Step 1: Swap PHP render loop — locationNameFull → locationNameWX**

Line 674. Replace:

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

With:

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

- [ ] **Step 2: Swap JS renderRows() — locationNameFull → locationNameWX**

Line 864. Replace:

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

With:

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

- [ ] **Step 3: Verify in browser**

Load `http://whitewater-secure/public/home.php`, click **Site Roster** tab.
- Location column should show "WX0101 - Tomball" format
- WX Code column (second column) still shows "WX0101" — unchanged
- Type "WX0101" in the search box — row should appear
- Type "Tomball" in the search box — row should appear
- Type "101" in the search box — row should appear (LIKE '%101%' matches "WX0101")

- [ ] **Step 4: Commit**

```bash
git add public/home.php
git commit -m "feat: Site Roster location column — display locationNameWX (WX0101 - Tomball format)"
```

---

## Task 4: Update search_directory.php — add JOIN + wx_location_code

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

- [ ] **Step 1: Define CASE expression and JOIN variables**

After line 28 (`$q = trim(...)`), add:

```php
$CASE = "CASE
    WHEN li.locationID IN (0, 9991) THEN 'WX0099'
    WHEN li.locationID IS NOT NULL  THEN CONCAT('WX', LPAD(li.locationID, 4, '0'))
    ELSE CONCAT('WX', LPAD(ea.locationCode, 4, '0'))
  END";

$join = "LEFT JOIN locationInfo li ON ea.locationCode = li.locationID";
```

- [ ] **Step 2: Update $cols and $baseWhere to use ea. prefix**

Replace lines 30–32:

```php
$limit     = 50;
$baseWhere = "ea.status_adp != 'Terminated' AND ea.employeeName != 'AApple Jaxx' AND ea.jobTitle NOT IN ('Shift Leader','Team Leader')";
$cols = "ea.employeeID, ea.employeeName, {$CASE} AS wx_location_code, ea.jobTitle,
         COALESCE(ea.workEmail, ea.wwEmail, ea.personalEmail) AS workEmail,
         ea.phone_preferred, ea.status_adp";
```

- [ ] **Step 3: Update grand total query**

Replace line 35:

```php
$sqlGrand = "SELECT COUNT(*) AS grand_total FROM employee_adp ea WHERE $baseWhere";
```

- [ ] **Step 4: Update q='' list query to include JOIN**

Replace line 48:

```php
$sqlList  = "SELECT $cols FROM employee_adp ea $join WHERE $baseWhere ORDER BY ea.employeeName LIMIT ?";
```

- [ ] **Step 5: Update prefix search (len < 2) — count and list queries**

Replace lines 57–82 (the `if ($len < 2)` block):

```php
$sqlCount = "
    SELECT COUNT(*) AS total
    FROM employee_adp ea $join
    WHERE $baseWhere
      AND (
        ea.employeeName LIKE CONCAT(?, '%')
        OR ({$CASE}) LIKE CONCAT(?, '%')
      )
";
$stmtCount = $db->prepare($sqlCount);
$stmtCount->bind_param('ss', $q, $q);
$stmtCount->execute();
$total = (int)($stmtCount->get_result()->fetch_assoc()['total'] ?? 0);

$sqlList = "
    SELECT $cols
    FROM employee_adp ea $join
    WHERE $baseWhere
      AND (
        ea.employeeName LIKE CONCAT(?, '%')
        OR ({$CASE}) LIKE CONCAT(?, '%')
      )
    ORDER BY ea.employeeName
    LIMIT ?
";
$stmtList = $db->prepare($sqlList);
$stmtList->bind_param('ssi', $q, $q, $limit);
```

- [ ] **Step 6: Update contains search (len >= 2) — count and list queries**

Replace lines 88–115 (the `else` block):

```php
$like = "%{$q}%";

$sqlCount = "
    SELECT COUNT(*) AS total
    FROM employee_adp ea $join
    WHERE $baseWhere
      AND (
        ea.employeeName LIKE ?
        OR ({$CASE}) LIKE ?
      )
";
$stmtCount = $db->prepare($sqlCount);
$stmtCount->bind_param('ss', $like, $like);
$stmtCount->execute();
$total = (int)($stmtCount->get_result()->fetch_assoc()['total'] ?? 0);

$sqlList = "
    SELECT $cols
    FROM employee_adp ea $join
    WHERE $baseWhere
      AND (
        ea.employeeName LIKE ?
        OR ({$CASE}) LIKE ?
      )
    ORDER BY ea.employeeName
    LIMIT ?
";
$stmtList = $db->prepare($sqlList);
$stmtList->bind_param('ssi', $like, $like, $limit);
```

- [ ] **Step 7: Verify via browser**

Open: `http://whitewater-secure/public/api/search_directory.php?q=`

Expected: JSON response with `wx_location_code` field in results, e.g. `"wx_location_code":"WX0101"`.

Then test: `http://whitewater-secure/public/api/search_directory.php?q=WX0101`

Expected: returns employees at location 101.

- [ ] **Step 8: Commit**

```bash
git add public/api/search_directory.php
git commit -m "feat: Company Directory API — add wx_location_code via locationInfo JOIN"
```

---

## Task 5: Update home.php Tab 1 — Company Directory display

**Files:**
- Modify: `public/home.php`

- [ ] **Step 1: Update Tab 1 SQL query — add JOIN and wx_location_code**

Replace line 38:

```php
$sql = "SELECT ea.*, DATEDIFF(CURDATE(), ea.hireDate) AS tenure_in_days,
            CASE
                WHEN li.locationID IN (0, 9991) THEN 'WX0099'
                WHEN li.locationID IS NOT NULL   THEN CONCAT('WX', LPAD(li.locationID, 4, '0'))
                ELSE CONCAT('WX', LPAD(ea.locationCode, 4, '0'))
            END AS wx_location_code
        FROM employee_adp ea
        LEFT JOIN locationInfo li ON ea.locationCode = li.locationID
        WHERE ea.status_adp = 'Active'
        AND ea.employeeName != 'AApple Jaxx'
        AND ea.jobTitle NOT IN ('Shift Leader','Team Leader')";
```

- [ ] **Step 2: Swap PHP render loop — locationCode → wx_location_code**

Line 619. Replace:

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

With:

```php
<td><?= htmlspecialchars($row['wx_location_code'] ?? '') ?></td>
```

- [ ] **Step 3: Swap JS renderRows() — locationCode → wx_location_code**

Line 780. Replace:

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

With:

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

- [ ] **Step 4: Verify in browser**

Load `http://whitewater-secure/public/home.php`, stay on **Company Directory** tab (Tab 1, default).
- Location column should show "WX0101" format for store employees
- Corp employees should show "WX0099"
- Type "WX0101" in the search box — employees at location 101 appear
- Type "Smith" — employees named Smith appear regardless of location
- Type "WX" in the search box — all employees returned (WX matches every wx_location_code)

- [ ] **Step 5: Commit**

```bash
git add public/home.php
git commit -m "feat: Company Directory location column — display wx_location_code via locationInfo JOIN"
```

---

## Verification Checklist

After all tasks are complete:

- [ ] Site Roster Location column shows "WX0101 - Tomball" format
- [ ] Site Roster WX Code column still shows "WX0101" (unchanged)
- [ ] Site Roster search: "WX0101", "Tomball", and "101" all return Tomball
- [ ] Site Roster sorted by WX code ascending (WX0099 first)
- [ ] Company Directory Location column shows "WX0101" format
- [ ] Company Directory search: "WX0101" returns employees at location 101
- [ ] Corp/regional employees show "WX0099"
- [ ] Export (site_export.php) includes `locationNameWX` column automatically