# Legacy MySQL Backup → PostgreSQL `org_id` Migration

## Why this migration matters

The attached MariaDB dump (`backup.sql`) represents the previous single-tenant-ish layout that keyed almost every business object off an integer `company_id`. The current platform consolidates tenants, branches, and specialist units into a single hierarchy driven by the `orgs` table (UUID primary keys). To land the legacy data safely:

* Every `company_id`, `tenant_id`, and `branch_id` value must resolve to a concrete `org_id` (and, where relevant, a `branch_id` row that points back to that `org_id`).
* Fact tables (finance, HR, cases, procurement, payroll, tax) must carry `org_id` so that sync, authorization, reporting, and search policies operate correctly.
* Application code and tests need to stop assuming integer tenant identifiers.

This note summarizes the gaps and proposes the concrete steps required to migrate the dump into the UUID-based schema described in `TAFDB.pgsql`.

---

## Step 1 — Stage the legacy data

1. **Restore the dump into a disposable MariaDB instance** (Docker or local):

  ```bash
  docker run --name taf-mariadb \
    -e MYSQL_ROOT_PASSWORD=tafroot \
    -e MYSQL_DATABASE=taf_legacy \
    -p 3307:3306 -d mariadb:11

  mysql -h 127.0.0.1 -P 3307 -u root -p taf_legacy < backup.sql
  ```

2. **Bulk-load the legacy schema into PostgreSQL using `pgloader`.** Point the loader at the running MariaDB container and a scratch Postgres database (`taf_legacy_stage`) so that we keep the raw structure intact without touching the primary migration database during retries.

  ```bash
  docker exec -i postgres psql -U taf postgres -c "CREATE DATABASE taf_legacy_stage;"

  docker run --rm --network host \
    -v "$(pwd)/sql/migrations:/migrations" \
    dimitri/pgloader:latest \
    pgloader /migrations/pgloader_legacy.load
  ```

  The loader script (`sql/migrations/pgloader_legacy.load`) now mirrors every table into the `taf_legacy` schema, skips index creation (`create no indexes` avoids pgloader’s MariaDB index parser bug), and preserves sequences so downstream inserts don’t collide with legacy keys.

3. **Copy the staging schema into the canonical migration database** once a clean snapshot exists:

  ```bash
  docker exec -i postgres pg_dump -U taf -n taf_legacy taf_legacy_stage > /tmp/taf_legacy.sql
  docker exec -i postgres psql -U taf taf_org_migration -c "DROP SCHEMA IF EXISTS taf_legacy CASCADE;"
  docker exec -i postgres psql -U taf taf_org_migration < /tmp/taf_legacy.sql
  ```

    Spot-check a few counts (`SELECT count(*) FROM taf_legacy.users;` should return `241`) before moving on so that downstream scripts can trust the staging baseline.

   4. **Materialize Biosecurity tenant + user identities in the canonical schema.** The helper script below seeds the Biosecurity Authority of Fiji org (UUID v7), generates UUIDs for every legacy user, restores password hashes/emails, and associates every account with the new tenant.

    ```bash
    docker exec -i postgres psql -U taf taf_org_migration < sql/migrations/20251005_biosecurity_user_import.sql
    ```

    After the script finishes you should see `241` rows in `public.users` and the same number of rows in `public.user_org_memberships`, all pointing at the Biosecurity org ID `0199b208-8943-7ca3-ba31-781efbe25058`.

   5. **Replay all legacy time-clock punches into the UUID schema.** This script reuses the user/org maps, converts GPS coordinates to PostGIS points, and tags each record with the originating legacy key for auditability.

     ```bash
     docker exec -i postgres psql -U taf taf_org_migration < sql/migrations/20251005_time_clock_import.sql
     ```

     Validation checklist:

  * `SELECT COUNT(*) FROM taf_legacy.time_clocks;` should match `SELECT COUNT(*) FROM public.time_clocks;` (expect `2,381` rows for the supplied dump).
     * `SELECT DISTINCT branch_id FROM public.time_clocks WHERE branch_id IS NOT NULL;` should return the same branch UUIDs created via `legacy.location_branch_map`.
     * Random spot-checks of `source->>'legacy_clock_id'` should correlate with the original integer keys for debug traceability.

     > ✅ Treat the `taf_legacy` schema as read-only. All transformation queries should project from these raw tables into temporary views/CTEs or dedicated mapping tables in the target schema.

---

## Step 2 — Build an `org` hierarchy from `companies`

The current schema expects **every business row to have an `org_id`**. Follow these steps:

1. **Seed tenants/roots** — each legacy company becomes a top-level org:

   ```sql
   WITH src AS (
     SELECT c.id   AS legacy_company_id,
            c.name AS company_name
       FROM legacy.companies c
   ), prepared AS (
     SELECT uuid_generate_v7() AS org_id,
            legacy_company_id,
            company_name
       FROM src
   )
   INSERT INTO orgs (org_id, parent_org_id, name, kind)
   SELECT org_id,
          NULL,
          company_name,
          'tenant'
     FROM prepared;

   CREATE TEMP TABLE legacy_company_org_map AS
   SELECT legacy_company_id, org_id
     FROM prepared;
   ```

   The `orgs_set_root_path` trigger will populate `root_org_id`/`path`. Persist the mapping (`legacy_company_org_map`) because later transformations will rely on it.

2. **Create branches** — if the legacy dump contains subordinate structures (e.g., `departments`, `offices`, `warehouses`) that were previously scoped by `company_id`, promote them to `branches` that reference the parent `org_id`.

   ```sql
   WITH prepared AS (
     SELECT uuid_generate_v7() AS branch_id,
            d.id               AS legacy_branch_id,
            m.org_id,
            d.name             AS branch_name
       FROM legacy.departments d
       JOIN legacy_company_org_map m ON m.legacy_company_id = d.company_id
   )
   INSERT INTO branches (branch_id, org_id, name, settings)
   SELECT branch_id,
          org_id,
          branch_name,
          jsonb_build_object('legacy_department_id', legacy_branch_id)
     FROM prepared;

   CREATE TEMP TABLE legacy_branch_map AS
   SELECT legacy_branch_id, branch_id, org_id
     FROM prepared;
   ```

   > 🔁 Repeat the pattern for any other sub-structures (stores, cost centres, plants) that previously hung off `company_id`.

3. **Map users and employees to org memberships** using the new join table `user_org_memberships` so authorization and dashboards work:

   ```sql
   INSERT INTO user_org_memberships (membership_id, user_id, org_id, role)
   SELECT uuid_generate_v7(), u.user_id, m.org_id,
          CASE WHEN u.is_superadmin THEN 'admin' ELSE 'member' END
     FROM users u
     JOIN legacy.users lu     ON lower(lu.email) = lower(u.email)
     JOIN legacy_company_org_map m ON m.legacy_company_id = lu.company_id
   ON CONFLICT (user_id, org_id) DO NOTHING;
   ```

---

## Step 3 — Transform core domains

Use fresh `uuid_generate_v7()` values for every target table **and persist the mapping for each legacy primary key**. Create helper tables (temporary during the run or permanent if you need auditing) such as `legacy.gl_journal_entry_map(legacy_id int, journal_entry_id uuid)` so child tables can reuse the generated IDs safely across multiple scripts.

### 3.1 Finance (GL, AP/AR, Banks)

| Legacy table | Key columns | Target table | Transformations |
|--------------|-------------|--------------|-----------------|
| `GL_Journal_Entries` | `id`, `company_id`, `timestamp`, `created_by` | `gl_journal_entries` (UUID PK) | Generate a v7 `journal_entry_id` per row and log it in `legacy.gl_journal_entry_map`. Map `company_id` → `org_id` via `legacy_company_org_map`. Join on users by email to resolve `created_by` → `users.user_id`. Cast `timestamp` to `timestamptz`. |
| `gl_transactions` | `journal_entry_id`, `company_id`, `type`, `amount` | `gl_transactions` (UUID PK) | Pull `journal_entry_id` from the mapping, generate a new v7 `gl_transaction_id`, store it in `legacy.gl_transaction_map`, and map `company_id` to `org_id`. Convert decimal precision with `numeric(18,6)`. |
| `ap_transactions`, `ar_transactions`, `bank_transactions` | `gl_transaction_id` | Same table names (`*_transactions`) | Generate v7 UUIDs, join to `legacy.gl_transaction_map` for the parent key, and keep an auxiliary map for any downstream references. |

Example load statement:

```sql
CREATE TABLE IF NOT EXISTS legacy.gl_journal_entry_map (
  legacy_id int PRIMARY KEY,
  journal_entry_id uuid NOT NULL
);

WITH src AS (
  SELECT je.id                      AS legacy_id,
         uuid_generate_v7()         AS journal_entry_id,
         je.reference,
         je.memo,
         je.timestamp AT TIME ZONE 'UTC' AS occurred_at,
         u.user_id                     AS created_by,
         map.org_id,
         je.updatedAt
    FROM legacy.GL_Journal_Entries je
    JOIN legacy_company_org_map map ON map.legacy_company_id = je.company_id
    LEFT JOIN users u ON lower(u.full_name) = lower(je.created_by) -- adjust per actual match key
), ins AS (
  INSERT INTO gl_journal_entries (journal_entry_id, org_id, reference, memo, occurred_at, created_by, updatedAt)
  SELECT journal_entry_id, org_id, reference, memo, occurred_at, created_by, updatedAt
    FROM src
  RETURNING journal_entry_id
)
INSERT INTO legacy.gl_journal_entry_map (legacy_id, journal_entry_id)
SELECT src.legacy_id, src.journal_entry_id
  FROM src
ON CONFLICT (legacy_id) DO UPDATE SET journal_entry_id = excluded.journal_entry_id;
```

> ⚠️  Verify the target finance tables in `TAFDB.pgsql`. If a table is missing, add its definition before loading.

### 3.2 HR (employees, compensation, attendance)

1. Derive `person_id`/`user_id` bindings:
  * Use email/username to map `legacy.employees` → `users.user_id`.
  * Generate a new v7 `employee_id` for each legacy employee and store the relationship in `legacy.employee_map` for downstream tables.

2. Populate dependent tables (`compensation`, `attendance`, `bank_detail_changes`) using the new `employee_id` and `org_id` from the mapping.

| Legacy table | Target | Key notes |
|--------------|--------|-----------|
| `employees` | `employee_profiles` | Map `company_id` → `org_id`, convert status enums to new enumerations (`employment_status`). Store original integer in `meta -> 'legacy_employee_id'`. |
| `compensation` | `employee_compensation_history` | Ensure effective dates stored as `daterange` or `date`. |
| `bank_detail_changes` | `employee_bank_change_requests` | Convert status enums to lower-case text and store approver as UUID.

### 3.3 Case management, OHS & Appeals

The `appeals`, `ohs_jsa_*`, `ohs_action_*` models reference `tenant_id`/`branch_id`. Update load scripts to use `org_id` (and `branch_id` where a specific site is required).

```sql
WITH prepared AS (
  SELECT uuid_generate_v7() AS appeal_id,
         a.id               AS legacy_appeal_id,
         case_map.case_id,
         emp_map.employee_id,
         org_map.org_id,
         lower(a.status)::text AS status,
         a.reason,
         a.updatedAt AT TIME ZONE 'UTC' AS created_at
    FROM legacy.appeals a
    JOIN case_map        ON case_map.legacy_case_id = a.case_id
    JOIN emp_map         ON emp_map.legacy_employee_id = a.employee_id
    JOIN legacy_company_org_map org_map ON org_map.legacy_company_id = emp_map.legacy_company_id
)
INSERT INTO appeals (appeal_id, case_id, employee_id, org_id, status, reason, created_at)
SELECT appeal_id, case_id, employee_id, org_id, status, reason, created_at
  FROM prepared;

INSERT INTO legacy.appeal_map (legacy_id, appeal_id)
SELECT legacy_appeal_id, appeal_id FROM prepared
ON CONFLICT (legacy_id) DO NOTHING;
```

### 3.4 Procurement & Inventory

* Convert `vendors` → `vendors` (UUID PK) with the org mapping.
* Load related objects (`vendor_emails`, `vendor_bank_accounts`, `products`, `inventory`, `inventory_txn`) by generating v7 UUIDs and recording mapping tables for each legacy identifier.
* Ensure quantity/price precision matches Postgres `numeric` definitions.

---

## Step 4 — Update PHP model metadata

Many models under `models/` still describe `tenant_id`/`branch_id` integer fields. Once the data lands with UUIDs:

1. Replace `tenant_id` / `branch_id` entries in model definitions with `org_id` and `branch_id` UUID metadata.
2. Add `uuid` validation rules and adjust controllers/services that build query filters.
3. Update PHPUnit fixtures (`api/tests/seed/*.sql` and `tests/phpunit/fixtures`) to include `org_id`.

> ✅  Tackle `models/GL_Journal_Entries.php`, `models/GLTransactions.php`, `models/ohs_*`, `models/appeals.php`, `models/training_enrollments.php`, and any others still referencing integers.

---

## Step 5 — Validation & QA

1. **Row counts** — for each migrated table, compare counts between legacy staging and final tables grouped by `org_id` to confirm completeness.

   ```sql
   SELECT org_id, count(*) FROM gl_journal_entries GROUP BY 1
   EXCEPT
   SELECT map.org_id, count(*) FROM legacy.GL_Journal_Entries je JOIN legacy_company_org_map map ON map.legacy_company_id = je.company_id GROUP BY 1;
   ```

2. **Foreign keys** — run `SELECT DISTINCT ... WHERE FK IS NULL` queries to catch mapping gaps.
3. **Quality gates** — execute `./test_setup.sh`, PHPUnit, and Jest suites to confirm new UUID data satisfies business logic.
4. **Smoke tests** — load Dashboard modules (finance, HR, OHS) to ensure ModuleLoader fetches succeed with `org_id` filters.

---

## Step 6 — Backfill automation (optional but recommended)

*Create a repeatable ETL script* (Python or PHP) that orchestrates:

1. Extract & load staging tables (`COPY ... FROM STDIN` or FDW).
2. Execute SQL files for each domain (`sql/migrations/finance_org_backfill.sql`, `sql/migrations/hr_org_backfill.sql`, etc.).
3. Emit a migration summary (counts, failed lookups, warnings).

This script should live under `scripts/etl/` and be accompanied by unit tests using a miniature dataset.

---

## Open items / decisions

| Topic | Decision Needed |
|-------|-----------------|
| Branch granularity | Confirm whether legacy departments/offices map one-to-one with `branches` or if we should collapse them into the parent `org`. |
| User identity resolution | Define the authoritative matching rule (email vs username) for linking legacy `created_by`/`employee_id` references to UUID users. |
| Histories & soft deletes | Decide whether to carry forward `updatedAt`/`isDeleted` flags directly or to leverage the new audit/event sourcing tables. |

---

## Next steps checklist

- [ ] Create the `legacy_company_org_map`, `legacy_branch_map`, and other mapping helpers in Postgres.
- [ ] Draft domain-specific SQL migration files and commit them under `sql/migrations/`.
- [ ] Update PHP model definitions to use `org_id`/UUIDs and add PHPUnit coverage for the migrated data.
- [ ] Run full test suite (`vendor/bin/phpunit`, `npm test`) against a database loaded with migrated data.
- [ ] Document operational runbook (how to re-run ETL) in `docs/runbooks/OrgMigration.md`.