# Features — bi_tool_backend_phase6 (SAWIS BI backend)

Evidence date: 2026-10-19.

Source: branch `phase6` at `717166ec` (2026-08-07).

Status words:

- **Current** — routed or otherwise present on this commit and not marked removed.
- **Completed** — delivered in a changelog or spec and still present.
- **In development** — code exists and the project's own plan still calls the area unfinished.
- **Planned** — described as next work, not found as finished behaviour.
- **Deprecated / leftover** — still in the tree but documented as redundant, or present only as starter-kit text.

There is no separate frontend in this repository. "Feature" here means a backend capability.

## Current — authentication and access

| Feature | Status | Where |
|---|---|---|
| `POST /loginAuth` with email/password or AES-256-CBC `payload` | Current | `LoginController`, `UserLoginModel`, `docs/general/LOGIN_ENCRYPTED_PAYLOAD_FRONTEND.md` |
| 5 failures / 15 minutes / IP lockout (HTTP 429) | Current, completed 2026-02-02 | `UserLoginModel` |
| Token session on `userjwt`, sliding `Start_date` | Current | `AuthFilter`, `AdminFilter`, `DomesticAuth` |
| Named-user idle windows (120 min and 90 min) | Current | `AuthFilter` |
| Role gate for Phase 5 admin routes (roles 1, 2, 4) | Current | `AdminFilter`, `DomesticAuth::ADMIN_FILTER_ROLES` |
| Domestic role gates (roles 0, 2, 3, 4 as in the PRD) | Current | `DomesticAuth` |
| Producer access junction plus CSV mirror | Current, changed 2026-08-07 | `domestic_user_producer_access`, `DomesticUserModel` |
| Local auth bypass when `REACT_APP_USE_AUTH_HEADERS` is false | Current | `AuthFilter`, `AdminFilter` |
| Global CORS, origin `*` | Current | `app/Filters/Cors.php` |
| First-login domestic help text from env or file | Current | `DOMESTIC_FIRST_LOGIN_HELP_TEXT`, `DOMESTIC_FIRST_LOGIN_HELP_FILE` |

User registration and "menu management with auto create controller" are listed in the root README and are **not** routed. Treat them as **leftover** starter-kit text.

## Current — export analytics

| Feature | Status | Where |
|---|---|---|
| Public filter list | Current | `GET /getFilters` |
| Authenticated filter list and country groups | Current | `GET /auth/getFilters`, `GET /auth/getCountryGroup` |
| Top 25, main chart, cultivar chart, year-over-year, total litres, exports | Current | `ChartDataApi` public POSTs |
| Public table APIs (market, exports, cultivar, volume, TOP 25) | Current | `TableDataApi` |
| Authenticated copies of those tables | Current | `AuthTableDataApi` under `/auth/tables/` |
| RSA exports report | Current | `POST /auth/reports/getRSAExportsReport` |
| Multi-value filters (scalar or array) | Current | Live doc; model `ApplyFilters` / `applyFilters` |
| Price range data and saved filter history | Current | `priceRangeController` under `/auth/` |
| API dashboard and saved layout | Current | `ApiDashboardController` |
| Audit dashboard (events, errors, ensure-table) | Current | `AuditDashboardController` under `/auth/audit-dashboard` |
| Site health page | Current | `GET /auth/site-health` |
| Email test send | Current | `GET/POST /auth/email-test` |

## Current — vineyard status and grape production (Phase 4 area)

| Feature | Status | Where |
|---|---|---|
| Vineyard filters and report list | Current | `VineyardStatus` |
| Vineyard metric POSTs (hectares through trellising) | Current | `Routes.php` |
| Vineyard combination test URL | Current | `GET /vineyardStatus/testReports/(:any)/(:any)` |
| Phase 4 import test URL | Current | `GET /vineyardStatus/test_url_phase_4` |
| Grape production filters, total production, production per region and cultivar | Current | `GrapeProduction` |
| Grape combination test URL | Current | `GET /grapeProduction/testReports/(:any)/(:any)` |
| `quickTest` and `testAllFilters` described in `docs/phase-4/GRAPE_PRODUCTION_TESTING_README.md` | **Not routed** on this commit | Doc only |

## Current — data sync

| Feature | Status | Where |
|---|---|---|
| Stepped monthly export sync (backup/mapper, qa, dev, price ranges, industry) | Current | `GET /auth/runDataSync`, `docs/monthlySync/` |
| Full rebuild flag `full=1` | Documented | monthly sync README |
| Vineyard status sync | Current | `GET /auth/runDataSync_VineyardStatus` |
| Grape production sync | Current | `GET /auth/runDataSync_GrapeProduction` |
| Industry export helper | Current | `GET /sys/ExportDataTo__Data_export_dev_industry`, `GET /sys/createIndustryImport` (no auth filter on the route) |

Product codes included and land codes excluded are **Documented** in `docs/monthlySync/README.md` (product codes `1, 2, 3A–3D, 4–8, 9A–9D, 10, 11A, 11B, 36, 90`; excluded `LNDKDE` `56, 160, 179`).

## Current — Phase 5 levy mapper

| Feature | Status | Where |
|---|---|---|
| Queued DAF processing | Current | `POST /admin/mapper_processing_sheet_queue` |
| Direct step 1 and steps 2–4 | Current | same route, `step` parameter |
| Step 0 DAF header validation (non-blocking) | Completed 2026-06-04; CultName optional as of 2026-06-05 | `validateDafUploadExcel` |
| Final-sheet test on the main process row | Completed 2026-06-04 | `mapper_processing_final_sheet_test` |
| Step 2 progress poll | Completed 2026-03-16 | `GET /admin/mapper_processing_step2_progress` |
| Progress, stats, history, process thread, retry | Current | `AdminController` |
| Cancel one and cancel all | Current | `mapper_processing_cancel`, `mapper_processing_cancel_all` |
| Downloads: uploaded, debug, final, test upload | Current | `getAdminFiles/...` |
| Unknown cultivar / country / producer CRUD | Current | `/admin/unknown-*-matches` |
| Bulk unknown-lookup imports without `adminFilter` | Current | `mapper_import_unkown_*_lookup__import` |
| MCC designation lookup CRUD | Completed 2026-05-07 | `/admin/mcc-designation-lookup` |
| MySQL gone-away retry | Completed 2025-12-17 | README env vars; `AdminModel` |
| Outlook-safe HTML email shell | Completed 2026-06-04 | `sawis_email_html_helper.php` |
| Email log page and JSON | Completed 2026-02-25 | `GET /admin/email_logs` |
| NA corrections block step 3 and trigger reminder email | Completed 2026-04-09 and 2026-05-04 | changelog |
| Background `process:mapper-queue` | **Invoked, class missing** | `AdminController::triggerBackgroundProcessing` |
| Legacy extra levy export file | Removed 2025-12-17 | Changelog: only three Excel outputs (uploaded, debug, final) plus the later test upload |

`POST /admin/mapper_processing_sheet` (without `_queue`) is still routed. **Requires Confirmation** whether callers should use only the queue route.

## Current — Phase 6 domestic capture

In development as a product area: APIs are on `phase6`, and `BACKEND_NEXT_STEPS_PLAN.md` (2026-08-06) still treats lifecycle proof, historic promote, and report QA as unfinished. The 2026-08-07 commit added schema hardening after that plan.

| Feature | Status | Where |
|---|---|---|
| Open / list / get / update submission | Current | `DomesticCaptureController` |
| Saved totals v2 and legacy upgrade | Current | `PUT .../saved-totals`, phase 6 README |
| Line CRUD | Current | `.../data` |
| Reconciliation and previous-month comparison | Current | `.../reconciliation`, `.../previous-month` |
| Submit | Current | `POST .../submit` |
| Past submissions | Current | `GET .../submissions/past` |
| Help GET/POST and onboarding text | Current | `DomesticCaptureController`, revision note |
| Lookups (container, size, cultivar, price class, country, brackets, producer type, district, producers, context, periods) | Current | `DomesticLookupController` |
| My producer codes | Current | `GET /auth/domestic/me/producer-codes` |
| Admin dashboard, users, assignment, role, producers | Current | `DomesticAdminController` |
| Approve and unlock | Current | admin submission routes |
| Domestic audit list and detail | Current | `DomesticAdminController` audit routes |
| Report filters and `getDomesticReport` | Current, QA unfinished per Aug 2026 plan | `DomesticReportController` (public routes) |
| Create period headers | Current | `sys/domestic/create-period-submissions` |
| Cancel overdue on the 5th | Current | `sys/domestic/cancel-overdue-submissions` |
| Mark synced (BR-14) | Current | `sys/domestic/mark-synced` |
| Producer list verify and sync | Current | `sys/domestic/*` and admin variants |
| Schema SQL (16 domestic tables, period column, country, Gareth hardening) | Current | `docs/phase 6/*.sql` |
| Spec pack v1.7 HTML | Current document | `docs/phase 6/SAWIS_BI_Phase_6_SAWIS_7_Domestic_Capture_v1.7.html` |
| Spec v1.6 and archive v1.2–v1.4 | Retained history | `docs/phase 6/` and `archive/` |

## Planned

| Item | Evidence | Notes |
|---|---|---|
| Promote historic rows from `domestic_sawis_7_data_imported` into working data | `BACKEND_NEXT_STEPS_PLAN.md` step A | Not described as already run |
| Re-runnable modern sample-data script for local/QA | Same plan | Not found as a committed script in the file list reviewed |
| Windows Task Scheduler / cron command list | Same plan, step C | Not in the README beyond the HTTP routes |
| Protect `sys/domestic/*` with a token or IP allow-list | Same plan | Routes are still open |
| Q1 cutoff day agreed with SAWIS | Same plan | Cancel job currently uses the 5th in code |
| Deeper TestSprite value assertions and multipart DAF fixtures | Live doc section 6 (recommendations) | Recommendations, not implemented requirements |
| OpenAPI document | Live doc recommendation | Not in the tree |
| Real foreign keys, status lookup, split of `userjwt` sessions | Plan step F, still deferred there | Not implemented as those items |
| React Phase 6 UI | Plan: "not started" | Out of this repository |

## Recently changed (visible on this commit and in changelog)

The git clone used for onboarding has only the tip commit, so "recent" is taken from `CHANGELOG.md` plus the tip message.

| When | Change |
|---|---|
| 2026-08-07 | Tip commit: Gareth review schema hardening. SQL adds junction access, audit columns, working `TOTAAL DECIMAL(18,2)`, working `JJJJMM VARCHAR(6)`. PHP reads and writes the junction |
| 2026-07 (doc) | Saved totals v2, country on the submission header, help endpoints, first-login text (`REVISION_saved_totals_v2_and_onboarding.md`) |
| 2026-06-05 | Final-sheet step 2 no longer fails because `AdminModel::$table` was unset. DAF validation reads only a sample and no longer treats empty `CultName` as blocking |
| 2026-06-04 | Step 0 DAF validation, test upload on the main process row, Outlook-safe email, `notifySawisPipelineError` |
| 2026-05-07 | MCC designation lookup CRUD |
| 2026-05-06 | Serial numbers that look like `dd/mm/yyyy` are not rewritten as US dates |
| 2026-04-22 | Step 4 email goes to the requesting admin; `ettiene@overdrive.co.za` is CC. Logged-in admin email preferred over a posted address |
| 2026-02-02 | Encrypted login payload, login rate limit, background spawn path fix, PhpSpreadsheet file cache |

`CHANGELOG.md` has no entries after 2026-06-05. Phase 6 work after that date is **not** reflected in the changelog. **Discrepancy.**

## Deprecated, removed, or unused in the tree

| Item | Evidence |
|---|---|
| ChatGPT page | `ChatGPT` controller and `chatgpt_helper.php` exist. No route in `Routes.php`. Filter comments still mention `chatGPT/*` as a commented-out exception |
| AWS SDK | Required by Composer. No usage found under `app/` |
| `Phase6Testing/` | Referenced by Composer autoload and PHPUnit. Directory absent |
| CodeIgniter migrations for email log and test-upload columns | Named in changelog and live doc. `app/Database/Migrations/` is empty |
| Commented routes in `Routes.php` | Price-range setter, several `ExportDataTo__*` routes, and an SMTP test POST are commented out |
| Extra levy export beyond uploaded, debug, and final | Changelog 2025-12-17 says the legacy extra export was removed. Test-upload storage was added later as a fourth file on the same process row |

## Sample files present but not parsed

These binaries are in the repo. Their contents were not opened, so they are not a feature specification:

- `ExampleOfProcessEndFile.xlsx`
- `SEPTEMBER 2025 end of upload.xlsx`
- `September 2025 for Silicon.xlsx`
- `docs/phase 6/BI Tool Domestic June 26 (4).xlsx`
- `docs/businessRules_step2_mapper.xlsx`