# ktools4 — ground-up rebuild plan

_Copied in as the Phase 0 deliverable. Source of truth for phase/milestone status is `progress.php`; this document is the narrative version._

## Context

The current warehouse system (`ktools/`) has grown organically for a long time: ~85+ MySQL tables in `onnyfsmy_jon` with **zero foreign keys anywhere in the core schema**, four different columns used to identify "what SKU is this" (`sku`, `fnsku`, `asin`, sometimes `upc`), `box_inventory`/`sent_items`/`problem_items`/`fnsku_changes` all separately repeating the same box/dimension/prepper columns, two parallel misfit-tracking systems, typo'd permanent column names, and a 51-table gamification subsystem ("Warehouse Legends") that's bigger than the entire operational schema it sits on top of. Only that gamification layer has ever been under migration control (`whl_migrations.php`) — the 28 "core" tables were created by hand and never touched by any migration since.

Kris wants a ground-up rebuild — new database (`ktools4`, already created locally, currently empty), new admin panel, new mobile scanner site — that fixes the database design while preserving the exact day-to-day workflow preppers already know ("Zero Prepper Confusion," per `ktools4/dev/AGENT_COORDINATION.md`: same 6-card scan form, same 16-item chip nav, same admin sidebar sections, just cleaner underneath and better-looking). Before any real building starts, he wants three things in hand: a written plan, a browser-viewable build-progress tracker, and refined mockups of the admin panel direction he already likes.

Decisions Kris already made that this plan is built around:
1. **Full data migration at go-live** — legacy `onnyfsmy_jon` data gets transformed and imported into the new schema, not abandoned.
2. **Gamification is in scope now**, redesigned/normalized alongside everything else, not deferred.
3. **Build order: schema → scanner → admin.**
4. **Admin mockup**: refine the existing `ktools4/dev/admin-panel.html` direction rather than proposing alternatives.

One thing worth flagging even though it's outside this project's scope: `ktools/admin/assets/config.ini` has a live OpenAI API key sitting in cleartext alongside the DB credentials. Worth rotating that key independently of this rebuild's timeline.

## Directory layout

`ktools4/` stays flat at `C:\xampp\htdocs\krisdkent.net\ktools4\`, matching how `ktools/` and `products/poav4/` already sit as sibling folders under the same document root. Non-web-facing folders get a `.htaccess` deny instead of a `public/`-only webroot split:

```
ktools4/
├── index.php                 # scanner/public station entry (Phase 2)
├── admin/                    # admin panel (Phase 3), mirrors ktools/admin/ structure
├── api/                      # ajax/REST endpoints (scan save, sku lookup, GRTC proxy)
├── progress.php              # build-progress tracker — Phase 0, first thing built
├── assets/                   # css/js/images
│
├── app/                      # .htaccess: Deny from all
│   ├── config/
│   │   ├── config.php        # loads .env, defines constants — no secrets committed
│   │   ├── progress_data.php # progress tracker data source (Phase 0)
│   │   └── .env.example
│   ├── db/
│   │   ├── Database.php      # single canonical mysqli wrapper (retires db.php/db.main.php split)
│   │   └── migrations/
│   │       ├── MigrationRunner.php
│   │       ├── helpers.php           # ensureColumn()/ensureIndex()/ensureTable(), promoted out of game_quest.php
│   │       ├── schema/                # one file per change, YYYYMMDD_NNNN_name.php, chronological
│   │       └── seeds/                 # idempotent reference-data seeders, tracked separately
│   ├── domain/                # Inventory/, Skus/, Misfits/, Supplies/, Game/, Admin/
│   └── lib/                   # auth, permissions, csrf, GRTC client
│
├── bin/                       # .htaccess: Deny from all
│   ├── migrate.php            # CLI: status | up | --dry-run
│   └── import_data.php        # CLI twin of admin/import-data.php: preview | import | reset | grtc on|off
│
├── dev/                       # existing mockups/docs — kept as design reference
└── docs/
    ├── PLAN.md                 # this file
    ├── SCHEMA.md
    └── MIGRATIONS.md
```

## Migration framework (the "auto DB migration for go-live" piece)

Direct evolution of `whl_migrations.php`'s proven pattern (tracking table + idempotent apply functions), generalized to cover *every* table from day one instead of just the gamification layer, and split into one small file per change instead of one 7,900-line function.

- **`schema_migrations` table**: `migration_key` (filename minus `.php`), `description`, `checksum` (sha256 of file contents — detects drift on an already-applied file), `applied_at`, `applied_by`, `batch`. A parallel `schema_seeds` table tracks reference-data seeders with upsert semantics.
- **File contract**: each `app/db/migrations/schema/*.php` returns `['description' => ..., 'up' => function(mysqli $conn) { ... }]`. Idempotent (`CREATE TABLE IF NOT EXISTS`, guarded `ensureColumn()`), same safety pattern `game_quest.php` already uses today, just shared instead of copy-pasted.
- **`MigrationRunner::run($conn)`**: safety-checks `SELECT DATABASE()` against an expected-DB constant (exactly like `db.php` already does), `glob()`s the `schema/` folder in filename order, applies anything not yet in `schema_migrations`, then does the same for `seeds/`.
- **Dev and prod (Bluehost) alike**: `MigrationRunner::autoApply()` runs on every page bootstrap, same as `db.php` auto-running `whl_run_migrations()` today — uploading the files *is* the deploy, with no manual DB step (Kris's call, 2026-10-01). Cheap when nothing is pending (file names vs tracked keys); when something is, it applies under a MySQL `GET_LOCK` so simultaneous requests can't double-apply. A failure throws locally but only logs in production. Every migration therefore has to be idempotent and safe to run against the live DB mid-shift.
- `php bin/migrate.php status | up` still works over SSH as a manual check/fallback.
- **ETL is deliberately separate from schema migrations**: the legacy import (`app/lib/LegacyImporter.php`, run from Admin Tools → Import Data or `php bin/import_data.php import`) opens the legacy `onnyfsmy_jon` database as a READ ONLY snapshot and loads everything into ktools4 in one transaction. Reset empties ktools4 again, so the import is repeated with fresh data until cutover instead of being resumable. Importing turns GRTC off (the legacy app still owns the prep lists) until an admin turns it back on. It never runs on a routine deploy. See docs/IMPORT_DATA.md.

## Target schema (high-level entity list, not full DDL)

**Identity fix** — `skus` (one row per real product) + `sku_identifiers` (`sku_id`, `identifier_type` ∈ fnsku/asin/upc/walmart_upc/merchant_sku, `identifier_value`, unique per type+value). Every other table joins to `sku_id`, not to whichever text column happened to be handy.

**Order-type/remote-list modeling** — `order_types` (`fba`/`haz`/`awd`/`wfs`/`pprep`, each with its `identifier_type`) + `remote_lists` (named GRTC lists per order type). This directly encodes what was confirmed live against the real GRTC API: `wfs` items resolve by `walmart_upc`, `fba`/`haz`/`awd` resolve by `fnsku`, `pprep` has no remote list yet.

**Inventory lifecycle** — `inventory_events` (one row per prepped unit-group, `status` ∈ staged/sent/problem/cancelled) replaces `box_inventory` + `sent_items` + `problem_items` as three copies of the same columns. `relabel_events` succeeds `fnsku_changes`. `orders`/`shipments`/`shipment_pallets`/`problem_types` carried over with real FKs (fixing `asigned_shipment` → `assigned_shipment_id` along the way).

**Templates & supplies** — `box_templates`, `box_template_supplies`, `supply_types/items/requests/order_history/usage` carried over largely as-is, just with real constraints instead of the hand-maintained FK map in `data_optimization.php`.

**Misfits, unified** — `misfit_categories` + `misfits` + `misfit_batches`/`misfit_batch_items`/`misfit_inventory_log` replace the two parallel systems (`misfit_items` and `categorized_misfit_*`) with one.

**People** — `preppers` (display name) separate from `users` (admin logins), giving every gamification table one `prepper_id` FK instead of duplicating `prepped_by`/`normalized_name` text everywhere. `permissions_list`/`role_permissions`/`user_permissions` carried over as-is.

**Gamification, clustered (51 tables → roughly 35-38, not a table-by-table redesign)**:

| Cluster | What happens |
|---|---|
| Round/attack mechanics (~10 tables: stranded/problem-item/boss-monday/pallet-pirate/boss-loss/boss-counter/daily-medic/mind-reader/oops-specialist attacks) | Collapse into one `prep_game_round_mechanics` table with a `mechanic_type` discriminator + FKs to rounds/preppers — the single biggest table-count win available. |
| Scoring events & finds | `prep_game_events` stays central; merge `_search_skus`/`_damage_skus` into `prep_game_sku_finds` (`find_type` discriminator); `_hidden_skus`/`_productivity_skus` get `sku_id`/`remote_list_id` FKs instead of repeated fnsku/asin/list_name columns. |
| Definitions/content | Kept distinct (quests/goals/badges/achievements/roles/ranks genuinely differ), but the repeated `(xp_amount, item_code, item_quantity, badge_code)` reward shape gets factored into one `game_rewards` table referenced by FK instead of copy-pasted in five places. |
| Player progress/wallet | Kept as-is structurally (goal tracking, XP ledger, item wallet, transfers, boosts, awarded badges, undo log are legitimately different) — the fix is replacing every text name with `prepper_id` FK. |
| Seasonal/live-events, minigame | Kept as their own bounded clusters, get real FKs added. |
| Settings/misc | `_supply_catalog`/`_supply_missions` merge into the core `supply_items`/`supply_requests` (an `is_game_mission`/`xp_bonus` column) instead of a second supply system. |

## Phases

| # | Scope | Exit criteria |
|---|---|---|
| **0** | This plan; build `progress.php` v1 (file-backed, no DB needed yet); refine `admin-panel.html` — real collapsible sidebar accordion, theme-switcher scaffold, restore Help Guide/Live Help, mock up 2-3 more subpages (Orders, Warehouse Legends admin hub, Reports) in the same visual language. | Kris approves the plan and the refined mockup; `progress.php` loads locally. |
| **1** | `MigrationRunner` + helpers + `bin/migrate.php`; write schema migrations for every core entity above (no gamification yet) against the empty local `ktools4` DB; seed reference data (`order_types`, `permissions_list`, `problem_types`, `misfit_categories`, `supply_types`). | `php bin/migrate.php status` shows everything applied cleanly. |
| **2** | Scanner/public station: `app/domain/Inventory`, sku resolution via `sku_identifiers`, wire `public-clean.html`/`public-futuristic.html` into real pages (Save Record/Search/View first, then the rest of the 16-item nav). Wire `order_types`/`remote_lists` to the real GRTC API for all 5 order types. Warehouse Legends dock stays visually present but stubbed. | A prepper can complete a full scan-to-save cycle in ktools4 for every order type. |
| **3** | Admin panel: real shell (topbar, accordion sidebar, theme switcher) from the Phase-0 mockup; wire Dashboard + the mocked subpages to real data; fill remaining sidebar sections, daily-used ones first. | Admin can run day-to-day ops in ktools4 without the legacy app. |
| **4** | Gamification: migrate the clustered schema, all FK'd to `preppers`/`skus`/`order_types`; rebuild scoring, the floating dock, quest/leaderboard/boss pages, and the `prep_game2_*` admin suite as drill-downs. | Warehouse Legends fully playable and admin-manageable in ktools4. |
| **5** | Legacy import (`admin/import-data.php`, `bin/import_data.php`); row-count reconciliation and an orphan-check (modeled on `data_optimization.php`, pointed at the new schema); finalize prod DB name/cutover; move every secret (DB creds, GRTC key, the exposed OpenAI key) into env config and rotate them; run the cutover. | ktools4 is production; legacy app retired to read-only archive. |

## Verification

- `progress.php` opens locally at `http://localhost/krisdkent.net/ktools4/progress.php` and reflects real phase/milestone state.
- Refined `admin-panel.html` opens in a browser: sidebar sections actually collapse/expand, theme switcher visibly changes the palette, new subpages are reachable from the sidebar links.
- End of Phase 1: `php bin/migrate.php status` (run from `ktools4/`) shows every core migration and seed applied against the local `ktools4` database with no errors, and `SHOW TABLES` in that database matches the entity list above.
- End of Phase 2: a real scan-to-save round trip works against `ktools4` for all 5 order types, including a live GRTC removal call for `fba`/`haz`/`awd`/`wfs` (mirroring the order-type-aware fix already shipped for the legacy `ktools/test.api.php`).
- End of Phase 5: row counts reconcile between `onnyfsmy_jon` and `ktools4` per entity, and an orphan-check against the new schema comes back clean before the production cutover.