# Stock Dashboard

Laravel app reading inventory data that lives in an Oracle database owned by
another team.

## Inventory database

Two connections are defined in [config/database.php](config/database.php):

- `oracle` — the live inventory DB. **STRICTLY READ-ONLY.** Never write,
  update, insert, or delete against this connection. It's owned by another
  team and shared with other systems.
- `local` — a dev-only mirror of the Oracle schema (sqlite or mysql,
  controlled by `DB_LOCAL_DRIVER`). Safe to read and write.

Inventory-facing code (`StockService`, related models/queries) should use
[config('inventory.connection')](config/inventory.php) rather than hardcoding
a connection name. Switch which one is active with:

```bash
php artisan stock:switch oracle
php artisan stock:switch local
```

This rewrites `INVENTORY_CONNECTION` in `.env`. `oracle` isn't reachable from
most dev environments — use `local` unless you specifically need to hit live
data.

### Schema qualification

`csponline` (the connecting Oracle user) does **not** own the inventory
tables — confirmed 2026-09-06: no synonym exists, and the tables live under
the `CSP` or `RTH` schema instead. Every Oracle query must schema-qualify its
table names (e.g. `CSP.ITEM_MST`, not `ITEM_MST`). `CSP` matches the
connecting username and is what this app uses; `RTH` is a parallel schema
with identical table names that also exists in the same database but is not
used here — don't query it by mistake.

`StockService::qualify()` / `StockService::table()` apply the schema prefix
automatically when the active connection is `oracle`, using
`config('inventory.oracle_schema')`.

### Posted status

Confirmed against live data (2026-09-06): `STATUS = 2` means "posted" across
every `*_MST` table. This is exposed as `config('inventory.posted_status')`
/ `StockService::postedStatus()`.

## Auth

Login (`POST /api/login`) always authenticates against the `oracle`
connection directly — not `INVENTORY_CONNECTION` — since the `local` mirror
has no user data. It matches `CSP.UM_USERS` by trimmed `EMP#` and
`USER_STATUS = 'A'`, and compares `U_PASS` as **plain text** (confirmed
2026-09-22 — that table has no hashing).

On success, identity is mirrored into a local `employees` table
(`oracle_user_id`, `employee_id`, `role`) keyed off Oracle's `USER_ID`, and a
Sanctum token (`Laravel\Sanctum\HasApiTokens` on `App\Models\Employee`) is
issued against that local record — see
[app/Http/Controllers/Api/AuthController.php](app/Http/Controllers/Api/AuthController.php).
`POST /api/logout` and `GET /api/user` require `auth:sanctum`.

`employees.role` isn't set by login — nothing currently assigns it. It must
be set manually (e.g. via tinker) before an employee can pull a checklist,
one of `in_charge`, `caretaker`, `sweeper`, `security_guard`.

## Sanctuary operations — daily checklists

Source material lives outside the repo, at `~/Downloads/Al Azeem Fountaion/`
(role folders: Sanctuary, Security Guard, Sweeper, Care Taker, Incharge,
Support Department) — Word docs with the paper checklists, SOPs, JDs/KPIs,
and employment contracts this schema was seeded from.

Schema (all prefixed `al_azeem_`, per the user's naming request), mobile-API
oriented — built for the 4 operational roles' *daily checklists* only. The
weekly Admin/HR/CCTV/IAD scorecards and contract records described in the
source docs are **not** built — deferred, see below.

- `al_azeem_checklist_templates` — one row per role (`role` is the enum:
  `in_charge` / `caretaker` / `sweeper` / `security_guard`), holds the
  `evidence_options` staff can attach (`["whatsapp","salesforce"]` for
  Caretaker/Sweeper, `["physical_log","verified"]` for In-Charge/Security
  Guard, matching the paper forms) and the two sign-off labels.
- `al_azeem_checklist_sections` → `al_azeem_checklist_items` — the template
  content itself (time label + task text), seeded verbatim from the English
  checklist docs by [database/seeders/ChecklistSeeder.php](database/seeders/ChecklistSeeder.php)
  (re-runnable — matches by role/sort_order, doesn't duplicate).
- `al_azeem_checklist_submissions` — one per (template, employee, day);
  status flows `in_progress` → `submitted` → `verified`.
- `al_azeem_checklist_item_completions` — one per (submission, item); holds
  `is_done`, `evidence`, `note`, `completed_at`.

API (`Api\ChecklistController`, all `auth:sanctum`):
`GET /api/checklists/today`, `PUT /api/checklists/items/{item}`,
`POST /api/checklists/submit`, `GET /api/checklists/submissions[?status=]`,
`GET /api/checklists/submissions/{id}`,
`POST /api/checklists/submissions/{id}/verify`. Full request/response docs
for the mobile app: [docs/api.md](docs/api.md).

**Known gap — no role hierarchy / RBAC.** The paper checklists specify who
verifies whom (In-Charge verifies Caretaker/Sweeper; Admin HOD verifies
In-Charge/Security Guard — see each template's `reports_to`), but
`employees.role` only has the 4 operational roles seeded — there's no
`admin_hod` role or supervisor-mapping table yet. `verify()` currently only
blocks self-verification; any other authenticated employee can verify
anyone's submission. Tighten this before relying on it for real sign-off.

**Not built yet** (deferred when this was scoped — see conversation, not
requested): weekly scorecards (Admin/HR/CCTV/IAD), employment contract
records, gate passes (IGP/OGP), cat health/TNVR records.

### Oracle handoff (pending — not run)

The user asked (2026-09-23) for these 5 tables to live in Oracle instead of
local SQLite/MySQL. A privilege check found the app's `csponline` Oracle
account — documented and used elsewhere as strictly read-only — actually
holds near-DBA session privileges (`CREATE/ALTER/DROP ANY TABLE`,
`CREATE/DROP USER`, `GRANT ANY PRIVILEGE`, etc.) via a granted role, far
beyond what a read-only integration account should have. That's a real
finding for whoever administers that Oracle instance, independent of this
feature.

Given that, we did **not** create these tables in Oracle. Instead,
[docs/oracle/al_azeem_checklists_schema.sql](docs/oracle/al_azeem_checklists_schema.sql)
is a self-contained, reviewed handoff script (DDL for all 5 tables +
reference-data seed INSERTs, generated from `ChecklistSeeder.php` so it
can't drift from what's already seeded locally) for the team that owns
that Oracle instance to run themselves, with least-privilege GRANT
statements templated at the bottom. Confirmed target: Oracle Database 10g
Release 2 (10.2.0.1.0), which is why it uses SEQUENCE+TRIGGER instead of
IDENTITY columns and keeps every identifier ≤30 bytes.

Once that team runs it and confirms the schema, the app's checklist code
(`ChecklistController`, the 5 Eloquent models) still needs to be pointed at
Oracle instead of local storage — not done yet, this is schema-only.

**Gotcha:** `submission_date` uses Eloquent's `date` cast, which stores with
a `00:00:00` time component. Looking it up with a bare `Y-m-d` string (e.g.
inside `firstOrCreate`'s own array-based lookup) silently never matches —
use `whereDate()` for any query against it, as `ChecklistController::todaysSubmission()`
does. This broke `GET /api/checklists/today` on its second call per day
until fixed (confirmed 2026-09-22).