nhance/public/dev_logs/2026-04-03.md

# 2026-04-03 — Daily tasks

## updateTpaIdForNotInNhance correction (EmployeeController)

### Plan
1. **Fix schema typos** — `tpa_api_data` uses `relation`; `employees` uses `relationship`. Replace incorrect `e.reltionship` and `$tpaRow['reltion']`.
2. **Align client policy lookup** — Use `ClientPolicyModel::first()` like the rest of `EmployeeController`; avoid fragile `get()->getRowArray()` on the model.
3. **Guard rails** — Validate `batch_file_id` as positive int; return early if `batch_files` or `client_policy` row is missing; log each failure path.
4. **Safe `whereNotIn`** — Avoid empty-array `NOT IN ()` SQL edge cases by only applying `whereNotIn` when `masterEmpCodes` is non-empty.
5. **Scope updates** — Restrict matches to `employee_polices.client_policy_id` and `employees.client_id` from the batch file so updates cannot touch other policies/clients.
6. **Correct update mechanism** — Resolve matching `employee_polices.id` via select + join, then `EmployeePolicyModel::update($id, ...)` instead of chaining `join` + `set` + `update()` on the model (unreliable in CI4 for multi-table updates).
7. **Optional `file_id`** — Apply `ep.file_id` filter only when `file_id` in params is a positive int (avoid accidental `file_id = 0` matches).

### Tasks
- [x] Document plan and tasks in this file (`2026-04-03.md`).
- [x] Implement corrections in `EmployeeController::updateTpaIdForNotInNhance` only.
- [x] Manual QA: executable checklist documented below (run on staging or a known batch before production).
- [x] QA HTTP entry: `/util/qa/updateTpaIdForNotInNhance` (`TestingController::qaUpdateTpaIdForNotInNhance`, `authMVC`).

### Manual QA checklist (`updateTpaIdForNotInNhance`)

**Prereqs:** Pick a real `batch_files.id` that has `client_id`, `client_policy_id`, and linked `tpa_api_data.file_id` rows.

1. **Batch + policy**
   - Confirm row exists: `SELECT id, client_id, client_policy_id FROM batch_files WHERE id = :batch_file_id;`
   - Confirm policy exists: `SELECT id, policy_no FROM client_policy WHERE id = :client_policy_id;`

2. **Master emp codes (same inputs as code)**  
   Run the same report the code uses (via app UI/API if available), or sanity-check that active `employee_polices` + `employees` exist for that `client_id` + `client_policy_id`.  
   Spot-check: `SELECT e.emp_code FROM employee_polices ep JOIN employees e ON e.id = ep.employee_id WHERE ep.client_policy_id = :client_policy_id AND e.client_id = :client_id AND ep.is_active = 1 AND ep.status = 'active' LIMIT 5;`

3. **“Not in Nhance” TPA rows for this file**  
   - List TPA rows for the batch file:  
     `SELECT id, emp_code, name, dob, relation, gender, tpa_id FROM tpa_api_data WHERE file_id = :batch_file_id AND is_active = 1;`  
   - For a test row whose `emp_code` is **not** in the master list, note `name`, `dob`, `relation`, `gender`, `tpa_id`.

4. **Matching employee_policy row (must exist for an update to happen)**  
   For that TPA row, confirm one row matches all of:  
   `e.emp_code`, `e.name`, `e.dob`, `e.relationship` = TPA `relation`, `e.gender`, `ep.client_policy_id`, `e.client_id`, and if you pass `file_id` in params, `ep.file_id`.  
   Example shape:  
   `SELECT ep.id, ep.tpa_id, ep.uhid, ep.file_id FROM employee_polices ep JOIN employees e ON e.id = ep.employee_id WHERE ep.client_policy_id = ? AND e.client_id = ? AND e.emp_code = ? ...;`

5. **Invoke**  
   Call `updateTpaIdForNotInNhance` with `['batch_file_id' => <id>, 'file_id' => <optional>]` from the same entry point your app uses (temporary route, tinker, or existing variance job).  
   If `file_id` is omitted or `0`, the code must **not** filter on `employee_polices.file_id`.

6. **Assert after run**  
   - Re-run the `SELECT ep.id, ep.tpa_id, ep.uhid ...` for the matched `ep.id`: `tpa_id` should equal the TPA row’s `tpa_id`, `uhid` should equal `client_policy.policy_no`.  
   - **Regression:** Pick another policy under the same client (different `client_policy_id`) with same `emp_code` pattern if any: its `employee_polices` rows must be **unchanged** (scoping check).

7. **Logs**  
   If `batch_file_id` is missing or batch/policy not found, confirm `myLogger` / app logs contain the new error messages and no SQL exceptions.

### Browser / HTTP trigger (auth required)

- **Route:** `GET` or `POST` under the existing **`/util`** group (filter: **`authMVC`**), same as other QA utilities.
- **Path:** `/util/qa/updateTpaIdForNotInNhance`
- **Parameters:**
  - `batch_file_id` — required (query or POST)
  - `file_id` — optional; omit or `0` to skip `employee_polices.file_id` filter
- **Handler:** `TestingController::qaUpdateTpaIdForNotInNhance` → delegates to `EmployeeController::updateTpaIdForNotInNhance`.
- **Example (logged-in session):**  
  `{base_url}/util/qa/updateTpaIdForNotInNhance?batch_file_id=123`  
  `{base_url}/util/qa/updateTpaIdForNotInNhance?batch_file_id=123&file_id=456`

**Production:** routes `/util/qa/updateTpaIdForNotInNhance` and `/util/qa/updateEmployeeDataFromTpa` are guarded by filter `utilQaRoutes`: in **`production`** they return **404 JSON** unless `.env` has **`util.enableQaRoutes = true`**. Non-production environments allow them without the flag (still require `authMVC` login).

**Server logs:** when direct sync / QA runs successfully but updates **zero** rows, `updateTpaIdForNotInNhance` and `updateEmployeeDataFromTpa` emit a **warning** via `myLogger` with batch id and context counts.

**Remove or restrict this route after QA** if you do not want it long-term in production (or leave the filter off unless `util.enableQaRoutes` is set).

---

## updateEmployeeDataFromTpa (Need to Review → direct DB sync)

### Review notes (`generateCorrectionUploadFromNeedToReview`)
- Loads `batch_files`, validates `client_id` / `client_policy_id` / `client_branch_id`.
- Uses `EmployeePolicyModel::getTPADataVariationReport($clientId, $clientPolicyId, $batchFileId)` — same slice as “Need to Review” (employees with `tpa_id IS NULL` on that policy).
- For each DB row, loads `tpa_api_data` rows with same `emp_code` and `file_id` = batch file id.
- Uses `reconcileDbWithTpa`: requires DB `relationship` to match TPA `relation`, then diffs `name`, `dob`, `gender` (not `relationship`, because it matched).
- Correction Excel only emits rows for `name`, `dob`, `relationship`, `email_corporate`; today `reconcileDbWithTpa` typically only yields `name` / `dob` / `gender` in `not_matching`.

### Plan (`updateEmployeeDataFromTpa`)
1. **Same inputs as correction path** — `batch_file_id` → load batch file; reject missing client/policy/branch.
2. **Same report + TPA fetch + reconcile** — no Excel, no `files` insert, no `excelFileFormatValidation`.
3. **Resolve employee** — `employee_id` from `employee_polices.*` in the report row; verify `employees.client_id` matches batch `client_id`.
4. **Map diffs to columns** — For each field in `not_matching` that is allowed for correction, set `employees` from TPA (`relationship` ← TPA `relation`; `email_corporate` ← TPA `email_corporate` or `email` if present).
5. **Persist** — `EmployeeModel::update($employeeId, $updateData)` (callbacks set `updated_by` where configured).
6. **Observability** — Return counts: `employees_updated`, `rows_skipped_no_diff`, `rows_skipped_no_employee`; log exceptions.
7. **QA route** — `/util/qa/updateEmployeeDataFromTpa?batch_file_id=` (authMVC), same pattern as other QA utilities.

### Tasks
- [x] Plan documented in this file.
- [x] Implement `EmployeeController::updateEmployeeDataFromTpa`.
- [x] Add `TestingController::qaUpdateEmployeeDataFromTpa` + `/util/qa/updateEmployeeDataFromTpa` route.
- [x] Wire `proceedTPADataVariationNextStep` + batch modal checkbox for `sync_mode=direct` (Not in Nhance / Need to Review).
- [x] Normalize `updateTpaIdForNotInNhance` return value to `{ success, message, data }` for API/QA/job consumers; optional `$jobId` arg for JobWorker.
- [x] Modal UX: reset direct-sync checkbox on open; warning toastr when direct sync updates zero rows.
- [x] `utilQaRoutes` filter + `util.enableQaRoutes` (.env) for `/util/qa/*` in production; zero-update **warning** logs in sync methods.
- [x] Manual QA: procedure and QA URLs documented below; run on staging with a real batch when available (compare `employees_updated` / skips to expected mismatches; optional parity with correction Excel row count).

### Production UI: direct sync (`sync_mode=direct`)
- **TPA variation modal** (`batch_list.php`): checkbox *“Sync directly to database (skip Excel)”* — when checked and user clicks **Proceed** on **Not in Nhance** or **Need to Review**, the request includes `sync_mode=direct`.
- **Endpoint:** existing `GET employee/proceedTPADataVariationNextStep/{file_id}?tab=...&sync_mode=direct`
  - `tab=not_in_nhance` + `sync_mode=direct` → `updateTpaIdForNotInNhance` (returns counts / ids in `data`).
  - `tab=need_to_review` + `sync_mode=direct` → `updateEmployeeDataFromTpa` (returns skip/update counts in `data`).
- **Other tabs:** `sync_mode=direct` returns **422** with a clear message (e.g. Not in TPA).
- **Default (checkbox off):** unchanged behaviour — Excel generation + existing pipelines.
- **UX safeguards** (`batch_list.js`): opening the TPA variation modal **unchecks** “direct sync” so it is not left on for another file. After **Proceed** with direct sync, if `employee_policies_updated` or `employees_updated` is **0**, the UI shows a **NOTICE** (warning) toastr instead of success-only, with a short hint to verify Nhance vs TPA matches.

### Sign-off (staging / UAT)
- [ ] Executed `GET /util/qa/updateTpaIdForNotInNhance` on a known batch — initials / date: __________
- [ ] Executed `GET /util/qa/updateEmployeeDataFromTpa` on a Need to Review batch — initials / date: __________

### Manual QA hints (`updateEmployeeDataFromTpa`)
- Use a `batch_file_id` that already shows rows on the **Need to Review** tab.
- Before: note `employees.name` / `dob` / `gender` for a sample `emp_code`.
- Call `{base_url}/util/qa/updateEmployeeDataFromTpa?batch_file_id=<id>` (logged in).
- After: same employee row should match TPA `tpa_api_data` for fields that were in `not_matching`.
- JSON response includes `employees_updated` and skip counters for quick sanity check.

---

## Removal checklist — direct-to-database TPA sync (pending confirmation)

**Status:** Not applied in code yet. When you confirm, remove the items below so TPA variation **Proceed** uses **Excel-only** paths: `generateEmployeeUploadFromNotInNhance` and `generateCorrectionUploadFromNeedToReview` only.

### 1. `app/Controllers/EmployeeController.php`
- Remove **`sync_mode` / `syncMode`** handling and the **422** guard for invalid `sync_mode=direct` on wrong tabs in **`proceedTPADataVariationNextStep`**.
- Remove the two **early branches** that call **`updateTpaIdForNotInNhance`** and **`updateEmployeeDataFromTpa`** when `sync_mode=direct`.
- Remove **`sync_mode`** from the final **`myLogger`** context in that method (if present).

### 2. `app/Views/batch_list.php`
- Remove the **modal footer** block: checkbox **`#tpaVariationDirectSync`** + label (“Sync directly to database…”).
- Remove **`$('#tpaVariationDirectSync').prop('checked', false)`** in **`showTPAVariationModal`**.
- In **`proceedTPADataVariationNextStep`**, remove **`directSync`**, **`sync_mode=direct`** on the URL, and the **zero-update NOTICE** / extra message logic; restore simple success/warning behaviour.

### 3. `app/Controllers/TestingController.php`
- Remove **`qaUpdateTpaIdForNotInNhance`**.
- Remove **`qaUpdateEmployeeDataFromTpa`**.

### 4. `app/Config/Routes.php` (under `/util` group)
- Remove the two **`match`** routes for **`qa/updateTpaIdForNotInNhance`** and **`qa/updateEmployeeDataFromTpa`** (including **`utilQaRoutes`** options).

### 5. `app/Filters/UtilQaRoutes.php`
- **Delete the file** (only used for those QA routes).

### 6. `app/Config/Filters.php`
- Remove **`use App\Filters\UtilQaRoutes`** and the **`'utilQaRoutes'`** alias.

### 7. `app/Controllers/JobWorker.php`
- Remove the **`$event_class_mapping`** entries for **`updateEmployeeDataFromTpa`** and **`updateTpaIdForNotInNhance`** (so queued jobs with those names are not routed to `EmployeeController`).

### 8. `.env.sample`
- Remove the **`util.enableQaRoutes`** / UTIL QA block.

### 9. `public/dev_logs/2026-04-03.md`
- **Either** delete this file **or** delete/trim sections that only document direct DB / QA / `sync_mode` (optional cleanup after code removal).

### 10. Local `.env` (manual)
- If **`util.enableQaRoutes`** was added, remove it locally (do not commit secrets).

### Removal tasks (track here)
- [ ] `EmployeeController.php` — `proceedTPADataVariationNextStep` + delete both sync methods
- [ ] `batch_list.php` — checkbox + JS
- [ ] `TestingController.php` — both QA methods
- [ ] `Routes.php` — both QA routes
- [ ] Delete `UtilQaRoutes.php` + `Filters.php` alias
- [ ] `JobWorker.php` — mapping entries
- [ ] `.env.sample` — QA block
- [ ] This file or sections — optional cleanup