# Migration Gaps — PrestaShop → Laravel

**Source:** `ppsanet_ps91.sql` (prefix `tpnif_`)
**Produced:** Phase 1 Discovery — 2026-09-09

This document logs every PS entity that cannot be fully migrated, the reason, and a recommendation. No migration code will be written until the user confirms MIGRATION_MAPPING.md and this file.

---

## Summary

| # | PS Entity | Issue | Recommendation |
|---|---|---|---|
| G1 | `tpnif_cart` / `tpnif_cart_product` | No `carts` table in Laravel; historical data | **SKIP** |
| G2 | `tpnif_customer_thread` (id_order = 0) | No target table for general enquiries | **SKIP** |
| G3 | `tpnif_order_message` / `*_lang` | PS message templates, not actual messages | **SKIP** |
| G4 | `tpnif_order_carrier` | No separate carrier table; partially absorbed into `orders` | **PARTIAL** |
| G5 | `tpnif_order_return` / `tpnif_order_return_detail` | Returns/RMA feature not built in Laravel | **SKIP or BUILD** |
| G6 | `tpnif_emailsubscription` | Separate module list, not linked to users | **REVIEW** |
| G7 | PS employee → Laravel user | No mapping between `tpnif_employee` and `users` | **FLAG** (data loss) |
| G8 | `order_items.turn14_product_id` | Many PS products not in Turn14 catalogue | **FLAG** (nullable FK) |
| G9 | `orders.ps_reference` | No column in current `orders` schema | **NEW COLUMN NEEDED** |

---

## G1 — Abandoned Carts (`tpnif_cart` / `tpnif_cart_product`)

**Columns:**
- `tpnif_cart`: id_cart, id_customer, id_address_delivery, id_currency, id_carrier, date_add, date_upd, checkout_session_data (serialized PHP or JSON)
- `tpnif_cart_product`: id_cart, id_product, id_product_attribute, quantity, date_add

**Row count:** ~5,500+ carts (most linked to placed orders)

**Problem:** The Laravel app has a `cart_items` table that uses `session_id` and `user_id` — there is no separate `carts` table. PS carts are historical (most are linked to placed orders already migrated as `orders`). Migrating PS carts would require inventing a session ID with no corresponding active browser session.

**Recommendation:** **SKIP.** Historical carts add no live-store value. Active carts in PS no longer exist (the store was decommissioned). Users will rebuild their own carts naturally.

---

## G2 — General Enquiry Threads (`tpnif_customer_thread` where `id_order = 0`)

**Row count:** 7,697 total threads; unknown proportion have `id_order = 0`

**Problem:** `tpnif_customer_thread` has two types:
1. **Order-linked** (`id_order > 0`): messages tied to a specific order → mapped to `order_messages`
2. **General enquiries** (`id_order = 0`): product questions, quoting requests, random contact-form submissions → no target table in Laravel

Many `id_order = 0` threads also have `id_customer = 0` (from anonymous contact-form submissions with email only).

**Recommendation:** **SKIP general enquiry threads.** Only order-linked threads (`id_order > 0`) are migrated to `order_messages`. The history of general enquiries has no operational value in the new system.

---

## G3 — PS Message Templates (`tpnif_order_message` / `tpnif_order_message_lang`)

**Columns:**
- `tpnif_order_message`: id_order_message, date_add
- `tpnif_order_message_lang`: id_order_message, id_lang, name, message

**Row count:** 26 template records

**Problem:** These are canned response templates used by PS staff when replying to customers — not actual messages. The Laravel app has a `message_templates` table for the same purpose, but the PS templates contain outdated text referencing Devils Parts / old banking details.

**Recommendation:** **SKIP.** The message_templates table in Laravel has its own content. Importing stale PS templates would pollute it.

---

## G4 — Order Carrier (`tpnif_order_carrier`) — Partial

**Columns:** id_order_carrier, id_order, id_carrier, id_order_invoice, weight, shipping_cost_tax_excl, shipping_cost_tax_incl, tracking_number, date_add

**Row count:** ~17,000+ (one per order)

**Problem:** Laravel has no separate carrier table. Carrier information is embedded on `orders`:
- `orders.shipping_carrier` — carrier name (PS has `id_carrier`, a FK to `tpnif_carrier` — not a name)
- `orders.waybill_number` — tracking number

The `tpnif_order_carrier.id_carrier` is an integer FK to `tpnif_carrier`, not a plain string. The carrier name requires a JOIN.

**Recommendation:** **PARTIAL IMPORT.** During order migration, JOIN `tpnif_order_carrier` + `tpnif_carrier_lang` to get the carrier name and populate `orders.shipping_carrier` and `orders.waybill_number` where `tracking_number` is non-empty.

---

## G5 — Returns / RMA (`tpnif_order_return` / `tpnif_order_return_detail`)

**Columns:**
- `tpnif_order_return`: id_order_return, id_customer, id_order, state, question, date_add, date_upd
- `tpnif_order_return_detail`: id_order_return, id_order_detail, id_customization, product_quantity

**Row count:** ~0 rows (PS returns table exists but is empty in this dump)

**Resolution:** ✅ **BUILT** — `order_returns` + `order_return_items` tables created in Laravel (`2026_09_09_100003_create_order_returns_table.php`). Models `OrderReturn` and `OrderReturnItem` written. Migration command `ppsa:migrate-returns` ready.

PS return states mapped:
- 1 (Waiting for confirmation) → `pending`
- 2 (Waiting for package) → `awaiting_return`
- 3 (Package received) → `received`
- 4 (Return denied) → `denied`
- 5 (Return completed) → `completed`

---

## G6 — Email Subscription List (`tpnif_emailsubscription`)

**Columns:** id_emailsubscription, id_shop, id_shop_group, email, newsletter_date_add, http_referer, ip_registration_newsletter, active

**Row count:** ~0 rows in PS dump (table structure present but no data exported)

**Resolution:** ✅ **IMPORTING** — `ppsa:migrate-newsletter` command written. Two sources:
1. `tpnif_emailsubscription` (active = 1) → `newsletter_subscribers`
2. `tpnif_customer` (newsletter = 1) → `newsletter_subscribers` (de-duped by email)

Skips emails already in `newsletter_subscribers`. Skips duplicate emails.

---

## G7 — Employee → User FK (data loss flag)

**Affects:** `order_events.created_by`, `order_messages.sender_user_id`

**Problem:** PS has `tpnif_employee` (staff logins) and `tpnif_customer` (customers) as separate tables. The Laravel app has a single `users` table. There is no mapping between PS employee IDs and Laravel user IDs.

- `tpnif_order_history.id_employee`: `0` means system/customer triggered; `> 0` means a specific staff member set the status
- `tpnif_customer_message.id_employee`: `0` means customer sent; `> 0` means staff replied

**Impact:** All `created_by` / `sender_user_id` fields that reference a PS employee will be `NULL` after migration. Historical order status changes and message replies from staff will lose attribution.

**Recommendation:** Accept the data loss. The alternative is to create Laravel users for every PS employee, which is outside scope. The event/message content is still preserved — only the author attribution is lost.

---

## G8 — Order Items Product Resolution (Turn14 + Local Products)

**Resolution:** ✅ **RESOLVED** — The system supports two product sources (Turn14 and local products) that don't overlap. `order_items` has both `turn14_product_id` (nullable) and `local_product_id` (nullable FK to `local_products`).

During `ppsa:migrate-order-items`, each order item's `product_reference` (part number) is resolved via a dual lookup:
1. **Turn14 first:** `new902_turn14_product` WHERE `part_number = product_reference`
2. **Local product fallback:** `local_products` WHERE `sku = product_reference`
3. **Neither found:** both IDs remain NULL — product name and part number still preserved as text

PS products are first migrated to `local_products` via `ppsa:migrate-products` (Phase 3, runs before order items), so all PS-era products are represented as local products and will be found by step 2.

**Prerequisite:** `ppsa:migrate-products` must run before `ppsa:migrate-order-items`.

---

## G9 — Missing `ps_reference` Column on `orders`

**Resolution:** ✅ **ADDED** — Migration `2026_09_09_100002_add_ps_reference_to_orders.php` adds `orders.ps_reference VARCHAR(32) NULL`. The `ppsa:migrate-orders` command populates this from `tpnif_orders.reference`.

---

## Relationships that cannot be fully preserved

| Relationship | Source | Target | Issue |
|---|---|---|---|
| `order_events.created_by` | PS employee ID | `users.id` | Employee table has no Laravel equivalent → NULL |
| `order_messages.sender_user_id` (staff replies) | PS employee ID | `users.id` | Same → NULL |
| `order_items.turn14_product_id` | PS `product_reference` | `turn14_product.id` | Many pre-Turn14 items → NULL |
| `order_messages.order_id` | PS `id_order = 0` threads | `orders.id` | General enquiries skipped |
| `order_messages.sender_user_id` (anonymous) | Thread with `id_customer = 0` | `users.id` | Anonymous contact form → NULL |

---

## Data quality issues found in dump

| # | Issue | Location | Handling |
|---|---|---|---|
| DQ1 | `0000-00-00` / `0000-00-00 00:00:00` in date fields | `tpnif_customer.birthday`, `tpnif_order_detail.download_deadline`, various | → `NULL` |
| DQ2 | Orders where `total_paid_tax_excl` ≠ `total_paid_tax_incl − expected_vat` | `tpnif_order_invoice` (e.g. order 6: excl=755, incl=2705) | Accept as-is; these appear to be historical data entry quirks or partial refunds |
| DQ3 | Duplicate emails | `tpnif_customer` may have multiple accounts per email | Deduplicate: first active/non-deleted record wins |
| DQ4 | Customers with `id_customer` referenced in orders but `deleted = 1` | `tpnif_orders.id_customer` → deleted customers | `orders.user_id = NULL` for those orders |
| DQ5 | Order items with no `product_reference` (empty string) | `tpnif_order_detail.product_reference` | `part_number = ''`, `turn14_product_id = NULL` — preserve product_name |
| DQ6 | Threads with `id_customer = 0` (anonymous enquiries) | `tpnif_customer_thread` | Skip (no user to link) |
| DQ7 | `tpnif_customer_message` messages with no linked thread | Should not occur by FK, but verify | Log in MIGRATION_REPORT.md if found |

---

## PS tables confirmed empty (0 rows)

These tables exist in PS and have Laravel counterparts, but contain no data — nothing to migrate:

| PS Table | Laravel Table | Note |
|---|---|---|
| `tpnif_product_comment` | `product_reviews` | No reviews were entered in PS |
| `tpnif_wishlist` | `wishlists` | No wishlists were created in PS |
| `tpnif_wishlist_product` | `wishlists` | (sub-table, also empty) |

---

## Unmapped PS tables (no Laravel counterpart, low/no migration value)

378 tables exist in the PS dump. Below is a representative list of those explicitly excluded from migration scope. These are PS system/module tables with no equivalent functionality in Laravel:

| Table(s) | What it is | Decision |
|---|---|---|
| `tpnif_access` / `tpnif_authorization_role` | PS RBAC system | SKIP — Laravel has Spatie |
| `tpnif_alias` | URL aliases for SEO | SKIP — Laravel uses its own routing |
| `tpnif_appagebuilder_*` | Page builder module data | SKIP |
| `tpnif_attribute` / `tpnif_attribute_group` | PS product variants | SKIP — not used with Turn14 |
| `tpnif_btmegamenu_*` | Menu module | SKIP |
| `tpnif_carrier` / `tpnif_carrier_*` | PS carrier config | SKIP — Laravel uses TCG/Shiplogic |
| `tpnif_category` / `tpnif_category_*` | PS product categories | SKIP — Turn14 provides categories |
| `tpnif_cart_rule` / `tpnif_cart_rule_*` | Discount vouchers | SKIP — no equivalent in Laravel yet |
| `tpnif_configuration` | PS shop config vars | SKIP |
| `tpnif_connections` / `tpnif_connections_page` | Analytics tracking | SKIP |
| `tpnif_currency` / `tpnif_currency_shop` | Currency config | SKIP — ZAR only |
| `tpnif_cms` / `tpnif_cms_*` | CMS pages | SKIP — replaced by blog/static pages |
| `tpnif_employee` / `tpnif_employee_*` | PS admin users | SKIP — see G7 |
| `tpnif_feature` / `tpnif_feature_*` | PS product features | SKIP |
| `tpnif_product` / `tpnif_product_*` | PS product catalogue | SKIP — replaced by Turn14 |
| `tpnif_specific_price` | Price rules | SKIP |
| `tpnif_stock` / `tpnif_stock_available` | PS stock counts | SKIP — replaced by Turn14 sync |
| `tpnif_tax` / `tpnif_tax_rule*` | Tax config | SKIP |
| `tpnif_the_courier_guy_*` | Old TCG quote module | SKIP |
| `tpnif_translation` | Module translations | SKIP |
| `tpnif_statssearch` | Search analytics | SKIP |

---

## Prerequisites before Phase 3 can begin

1. Add `legacy_ps_id` columns (see MIGRATION_MAPPING.md §"New columns required")
2. Add `orders.ps_reference VARCHAR(32) NULL` (G9 above)
3. Confirm the PS dump is loaded and accessible from the Laravel `.env` DB connection (staging/local)
4. Confirm the `ppsa:import-ps-customers` command has been run (customers are prerequisite for all other entities)