# Implementation Plan: Promoted Columns + API Updates

> Status: **COMPLETE** ✔
> SQL migration file: `sql/optimize_collection_data.sql` — run this in phpMyAdmin on each environment.

---

## What We're Doing (in One Line)

Add `user_ref_id`, `owner_user_id`, `entity_ref_id`, `status_id`, `sort_order` as first-class columns to `collection_data`, then wire the API and models to read/write them.

---

## Phase 1 — Database (already created)

**File:** `sql/optimize_collection_data.sql`

- Adds 5 new columns to `collection_data`
- Adds 5 indexes
- Backfills existing data from JSON

**Run this first in phpMyAdmin before any code changes.**

---

## Phase 2 — Model Layer

### 2a. `CollectionDataMutation.php` — `add()` method

**Current:** Only writes `collection_id`, `parent_id`, `website_id`, `data`, `slug`.

**Change:** Also accept and write the 5 new columns from the incoming payload.

```
Payload fields to map:
  $payload->user_ref_id    → user_ref_id
  $payload->owner_user_id  → owner_user_id (fallback: created_by from auth)
  $payload->entity_ref_id  → entity_ref_id
  $payload->status_id      → status_id
  $payload->sort_order     → sort_order (default 0)
```

**What changes:** INSERT query gains 5 new columns and bound params.

---

### 2b. `CollectionDataMutation.php` — `update()` method

**Current:** Updates only `data` and `parent_id`.

**Change:** Also update the 5 new columns if present in the payload. Fields omitted from the payload are left unchanged (no overwrite with NULL).

**What changes:** UPDATE query becomes dynamic — only include a column in SET if the key exists in the payload.

---

### 2c. `CollectionDataQuery.php` — `buildOrderClause()` method

**Current:** Allowed sort columns whitelist = `['id', 'parent_id', 'created_at', 'updated_at']`. Everything else falls through to `JSON_EXTRACT`.

**Change:** Add `sort_order`, `status_id`, `user_ref_id`, `entity_ref_id`, `owner_user_id` to the allowed column whitelist so they sort as native columns (fast, indexed) instead of via JSON extraction.

**What changes:** One array addition in `buildOrderClause()`.

---

### 2d. `CollectionDataQuery.php` — `findMany()` method

**Current:** Accepts `filters` array (JSON field filters) and `sortBy`/`sortOrder`.

**Change:** Add optional native column filter params:

```
$user_ref_id    = null  → adds WHERE user_ref_id = ?
$owner_user_id  = null  → adds WHERE owner_user_id = ?
$entity_ref_id  = null  → adds WHERE entity_ref_id = ?
$status_id      = null  → adds WHERE status_id = ?
```

These go into the WHERE clause as direct column comparisons (not JSON_EXTRACT), so they use the indexes.

**What changes:** 4 new optional parameters + condition-building block.

---

### 2e. `CollectionDataQuery.php` — `find()` (CollectionSearchModel)

**Current:** `CollectionSearchModel` only supports JSON-field `filters`.

**Change:** Allow the search model to accept native column filters as top-level fields in the POST body:

```json
{
  "collection_id": "jobs",
  "website_id": "fieldforge",
  "user_ref_id": 2,
  "status_id": 1,
  "entity_ref_id": 66,
  "sortBy": "sort_order",
  "sortOrder": "ASC"
}
```

`CollectionSearchModel` will read these optional fields and add them as direct WHERE conditions (not inside `filters`).

**What changes:** `CollectionSearchModel` constructor gains 4 new optional properties. `find()` builds WHERE conditions for them.

---

## Phase 3 — API Endpoint Layer

### 3a. `api/collection-data/list.php`

**Current:** Accepts `collectionId`, `websiteId`, `parent_id`, `childrenCollection` as GET params.

**Change:** Accept 5 new optional GET params and pass them to `findMany()`:

```
?user_ref_id=2
?owner_user_id=1
?entity_ref_id=66
?status_id=1
?sortBy=sort_order
?sortOrder=ASC
```

**What changes:** Read new GET params → pass to `findMany()`.

---

### 3b. `api/collection-data/paged-list.php`

**Current:** Same structure as `list.php` based on the file read.

**Change:** Same as 3a — accept and forward the same new params.

---

### 3c. `api/collection-data/find.php` (POST body)

**Current:** POST body passes through to `CollectionSearchModel` which only handles `collection_id`, `website_id`, and `filters[]`.

**Change:** No endpoint file changes needed. The change is in `CollectionSearchModel` (2e above) — the endpoint already passes the raw body in.

---

### 3d. `api/collection-data/save.php`

**Current:** Passes the raw body directly to `add()` or `update()`.

**Change:** No endpoint file changes needed. The payload already flows through. The model changes (2a/2b) handle the new columns transparently.

---

### 3e. `api/collection-data/save-range.php`

**Current:** Loops and calls `add()`/`update()` per item.

**Change:** No endpoint file changes needed — same as 3d.

---

## Phase 4 — No New Endpoints Needed

The existing endpoints cover everything once the model layer is updated:

| Operation | How |
|---|---|
| Save with sort_order / status_id | `POST save.php` — include new fields in body |
| Update sort_order (reorder) | `POST save.php` with `id` + `sort_order` |
| List ordered by sort_order | `GET list.php?sortBy=sort_order` |
| Filter jobs by technician | `GET list.php?collectionId=jobs&user_ref_id=2` |
| Filter jobs by status | `GET list.php?collectionId=jobs&status_id=1` |
| Filter jobs for a machine | `GET list.php?collectionId=jobs&entity_ref_id=66` |
| Advanced search | `POST find.php` with `"user_ref_id": 2, "status_id": 1` |

---

## Summary of Files That Change

| File | Change |
|---|---|
| `models/CollectionDataMutation.php` | `add()` and `update()` write new columns |
| `models/CollectionDataQuery.php` | `findMany()` filters + `buildOrderClause()` whitelist + `CollectionSearchModel` native filters |
| `api/collection-data/list.php` | Forward new GET params |
| `api/collection-data/paged-list.php` | Forward new GET params |

**Files that do NOT change:**
- `api/collection-data/find.php`
- `api/collection-data/save.php`
- `api/collection-data/save-range.php`
- `models/Details.php`
- `models/CollectionLinkQuery.php`
- All other endpoints

---

## Order of Execution

1. Run `sql/optimize_collection_data.sql` in phpMyAdmin on each environment
2. Deploy `CollectionDataMutation.php` changes
3. Deploy `CollectionDataQuery.php` changes
4. Deploy `list.php` + `paged-list.php` changes
5. Test with existing data — backfill queries in the migration should have populated the new columns already

---

> Reply with **go ahead** when ready to implement.