# Migration Mapping — PrestaShop → Laravel

**Source:** `ppsanet_ps91.sql` (prefix `tpnif_`)
**Target:** Laravel 11 (`laravel-proposal/app`)
**Produced:** Phase 1 Discovery — 2026-09-09

---

## Quick-reference entity table

| PS Table(s) | Laravel Table | Rows (approx) | Status |
|---|---|---|---|
| `tpnif_customer` | `users` | ~6,100 | Ready (already partially done) |
| `tpnif_address` | `user_addresses` | ~6,246 | Ready |
| `tpnif_product` + `_lang` + `tpnif_manufacturer` | `local_products` | ~1,165 | Ready |
| `tpnif_orders` | `orders` | ~17,246 | Ready |
| `tpnif_order_detail` | `order_items` | ~41,093 | Ready (dual product lookup) |
| `tpnif_order_invoice` | `order_invoices` | ~17,045 | Ready |
| `tpnif_order_history` + `tpnif_order_state` | `order_events` | ~85,699 | Ready |
| `tpnif_customer_message` + `tpnif_customer_thread` | `order_messages` | ~11,160 msgs / ~7,697 threads | Partial (order-linked threads only) |
| `tpnif_order_slip` | `order_credits` | ~164 | Ready |
| `tpnif_order_return` + `_detail` | `order_returns` + `order_return_items` | ~0 (table built) | Ready |
| `tpnif_emailsubscription` + newsletter customers | `newsletter_subscribers` | ~0 PS rows + newsletter customers | Ready |
| `tpnif_product_comment` | `product_reviews` | 0 | Empty — nothing to migrate |
| `tpnif_wishlist` + `tpnif_wishlist_product` | `wishlists` | 0 | Empty — nothing to migrate |
| `tpnif_cart` + `tpnif_cart_product` | `cart_items` | historical | SKIP — see Gaps |

---

## New columns required (not yet in schema)

Every migrated model needs a `legacy_ps_id` column for idempotency and audit. These must be added via migration before Phase 3 begins:

| Table | Column | Type |
|---|---|---|
| `users` | `legacy_ps_id` | `unsignedBigInteger, nullable, unique` |
| `user_addresses` | `legacy_ps_id` | `unsignedBigInteger, nullable, unique` |
| `orders` | `legacy_ps_id` | `unsignedBigInteger, nullable, unique` |
| `order_items` | `legacy_ps_id` | `unsignedBigInteger, nullable` |
| `order_invoices` | `legacy_ps_id` | `unsignedBigInteger, nullable, unique` |
| `order_events` | `legacy_ps_id` | `unsignedBigInteger, nullable, unique` |
| `order_credits` | `legacy_ps_id` | `unsignedBigInteger, nullable, unique` |
| `order_messages` | `legacy_ps_id` | `unsignedBigInteger, nullable` |

Also needed on `orders`:

| Table | Column | Type | Notes |
|---|---|---|---|
| `orders` | `ps_reference` | `string(32), nullable` | PS `reference` field (e.g. BDWQCNZ) has no Laravel equivalent |

---

## Entity Maps

### 1. Customers → `users`

**Filter:** `deleted = 0 AND is_guest = 0 AND active = 1`
**Skip emails:** `anonymous@psgdpr.com`, `pub@prestashop.com`
**Idempotency:** check `users.legacy_ps_id = tpnif_customer.id_customer` before inserting

| PS Column | Laravel Column | Transform |
|---|---|---|
| `id_customer` | `legacy_ps_id` | Direct |
| `firstname` + `' '` + `lastname` | `name` | Concatenate, trim |
| `email` | `email` | `strtolower(trim())` |
| `passwd` | `password` | Replace with `Hash::make('12345')` |
| `birthday` | `birthdate` | `'0000-00-00'` → NULL |
| `newsletter` | `newsletter_subscribed` | Cast to bool |
| `company` (non-empty) | `company_name`, `is_business_account = true` | `trim()`, empty → NULL |
| `date_add` | `created_at` | Direct |
| `date_upd` | `updated_at` | Direct |
| *(fixed)* | `email_verified_at` | `= now()` — treat as already verified |

**Notes:**
- `id_gender` has no field in Laravel (not captured)
- `siret`, `ape`, `website` — no equivalent — omit
- `title` (Mr/Mrs) — already in `users.title` added in `add_profile_fields`; can map `id_gender: 1 = 'Mr', 2 = 'Mrs'`

---

### 2. Addresses → `user_addresses`

**Filter:** `deleted = 0 AND active = 1 AND id_customer > 0`
**Idempotency:** check `user_addresses.legacy_ps_id = tpnif_address.id_address`

| PS Column | Laravel Column | Transform |
|---|---|---|
| `id_address` | `legacy_ps_id` | Direct |
| `id_customer` | `user_id` | Lookup `users.legacy_ps_id` → `users.id` |
| `alias` | `label` | Default to `'Home'` if blank |
| `firstname` | `first_name` | trim |
| `lastname` | `last_name` | trim |
| `phone_mobile` OR `phone` | `phone` | Prefer mobile; fall back to `phone` |
| `company` | `company` | trim |
| `address1` | `address_line1` | Direct |
| `address2` | `address_line2` | NULL if empty |
| `city` | `city` | Direct |
| `postcode` | `postal_code` | Direct |
| `id_country` (= 30 = South Africa) | `country` | Hard-code `'South Africa'` |
| *(first address per customer)* | `is_default` | `1` for first, `0` for rest |

**Notes:**
- `id_state` maps to PS states; `province` column in Laravel is nullable — leave NULL
- `other`, `vat_number`, `dni` — no equivalents — omit

---

### 3. Orders → `orders`

**Filter:** none (migrate all PS orders)
**Idempotency:** check `orders.legacy_ps_id = tpnif_orders.id_order`
**Join required:** `tpnif_address` on `id_address_delivery` for shipping fields

| PS Column | Laravel Column | Transform |
|---|---|---|
| `id_order` | `legacy_ps_id` | Direct |
| `id_customer` | `user_id` | Lookup `users.legacy_ps_id`; NULL if customer not migrated |
| `current_state` | `status` | See **Order State Map** below |
| `reference` | `ps_reference` | New column — preserve PS reference string |
| `payment` | `payment_method` | Direct (e.g. "Bank Wire", "PayFast") |
| `total_paid_tax_incl` | `total_incl` | Direct |
| `total_paid_tax_incl − total_paid_tax_excl` | `vat_amount` | Computed |
| `total_paid_tax_excl − total_shipping_tax_excl` | `subtotal_excl` | Computed |
| `total_shipping_tax_incl` | `shipping_cost` | Direct |
| JOIN addr: `firstname + ' ' + lastname` | `shipping_name` | |
| JOIN addr: `address1` + optional `address2` | `shipping_address` | |
| JOIN addr: `city` | `shipping_city` | |
| JOIN addr: `postcode` | `shipping_postal_code` | |
| JOIN addr: `phone_mobile OR phone` | `shipping_phone` | |
| `id_customer` → `tpnif_customer.email` | `shipping_email` | |
| `gift` | `gift_wrapped` | Cast to bool |
| `gift_message` | `order_notes` | Prefix with "Gift message: " if non-empty |
| `date_add` | `created_at` | Direct |
| `date_upd` | `updated_at` | Direct |

**Notes:**
- `shipping_carrier`, `shipping_service` — sourced from `tpnif_order_carrier` (see §9 below)
- `waybill_number` — sourced from `tpnif_order_carrier.tracking_number` if non-empty
- `billing_*` fields — PS stores billing address in `id_address_invoice`; can be populated from that join if needed
- `is_export`, `cart_weight_kg` — no PS equivalent, leave default

---

### 4. Order State Map (`tpnif_order_state` → `orders.status`)

PS stores 39 order states. Laravel has 7 enum values: `pending`, `paid`, `processing`, `shipped`, `delivered`, `cancelled`, `closed`.

| PS id | PS Name | Laravel `status` |
|---|---|---|
| 1 | Awaiting check payment | `pending` |
| 2 | Payment Received | `paid` |
| 3 | Processing in progress | `processing` |
| 4 | Collected by Courier | `shipped` |
| 5 | Delivered | `delivered` |
| 6 | Canceled | `cancelled` |
| 7 | Refunded | `cancelled` |
| 8 | Payment error | `cancelled` |
| 9 | On backorder (paid) | `processing` |
| 10 | Awaiting Bank EFT payment | `pending` |
| 11 | Remote payment accepted | `paid` |
| 12 | On backorder (not paid) | `pending` |
| 13 | Awaiting Cash On Delivery | `pending` |
| 14 | Waiting for payment | `pending` |
| 15 | Partial refund | `closed` |
| 16 | Partial payment | `paid` |
| 17 | Authorized (to be captured) | `paid` |
| 18 | Refund Yoco Payment | `cancelled` |
| 19 | Refund Yoco Payment - Pending | `cancelled` |
| 20 | Refund Yoco Payment - Rejected | `cancelled` |
| 21 | Refund Yoco Payment - Error | `cancelled` |
| 22 | Part Payment Received | `paid` |
| 23 | In Query | `processing` |
| 24 | Partially Delivered | `shipped` |
| 25 | Collected in Store | `delivered` |
| 26 | Backorder placed on Supplier | `processing` |
| 27 | Awaiting Customer Collection | `shipped` |
| 28 | Quality Control | `processing` |
| 29 | Order Closed | `closed` |
| 30 | Waybill Generated | `shipped` |
| 31 | Issued to Preparation Dept | `processing` |
| 32 | Awaiting item out of Stock | `processing` |
| 33 | 0. Awaiting Weekly Order | `processing` |
| 34 | 1. Order Placed at US Warehouse | `processing` |
| 35 | 2. Product on Backorder from Supplier | `processing` |
| 36 | 3. Order Dispatched from US Warehouse | `shipped` |
| 37 | 4. Order being processed at SA Customs | `shipped` |
| 38 | 5. Order Clearance Delay | `shipped` |
| 39 | 6. Order Cleared SA Customs | `shipped` |
| *(unknown/unmapped)* | Fallback | `processing` |

---

### 5. Order Items → `order_items`

**Idempotency:** check `order_items.legacy_ps_id = tpnif_order_detail.id_order_detail`

| PS Column | Laravel Column | Transform |
|---|---|---|
| `id_order_detail` | `legacy_ps_id` | Direct |
| `id_order` | `order_id` | Lookup `orders.legacy_ps_id` |
| `product_name` | `product_name` | Direct |
| `product_reference` | `part_number` | Direct (PS `product_reference` IS the part number) |
| `product_reference` | `turn14_product_id` | Lookup `new902_turn14_product.part_number`; NULL if not found |
| `product_quantity` | `qty` | Direct |
| `unit_price_tax_excl` | `unit_price_excl` | Direct |
| `unit_price_tax_incl` | `unit_price_incl` | Direct |
| `total_price_tax_incl` | `line_total_incl` | Direct |
| *(from Turn14 lookup)* | `brand_name` | `turn14_brand.name` if product found; otherwise NULL |

**Important:** `turn14_product_id` will be NULL for many historical PS products that pre-date the Turn14 catalogue switch. This is expected — order history is preserved regardless.

---

### 6. Order Invoices → `order_invoices`

**Idempotency:** check `order_invoices.legacy_ps_id = tpnif_order_invoice.id_order_invoice`

| PS Column | Laravel Column | Transform |
|---|---|---|
| `id_order_invoice` | `legacy_ps_id` | Direct |
| `id_order` | `order_id` | Lookup `orders.legacy_ps_id` |
| `number` | `invoice_number` | Format as `'IN' + LPAD(number, 6, '0')` → `IN000873` |
| `date_add` | `invoice_date`, `created_at` | Direct |
| `note` | `notes` | NULL if empty |
| `NULL` | `generated_by` | No PS equivalent — leave NULL |

---

### 7. Order History → `order_events`

**Source:** `tpnif_order_history` JOIN `tpnif_order_state_lang` (lang 1)
**Idempotency:** check `order_events.legacy_ps_id = tpnif_order_history.id_order_history`

| PS Column | Laravel Column | Transform |
|---|---|---|
| `id_order_history` | `legacy_ps_id` | Direct |
| `id_order` | `order_id` | Lookup `orders.legacy_ps_id` |
| `'status_change'` | `event_type` | Fixed value |
| state_lang.`name` | `description` | e.g. "Delivered", "Awaiting Bank EFT payment" |
| `id_employee` | `created_by` | Always NULL — no employee→user mapping |
| `{}` | `metadata` | Empty JSON `{}` |
| `date_add` | `created_at` | Direct |

---

### 8. Customer Messages → `order_messages`

**Source:** `tpnif_customer_message` JOIN `tpnif_customer_thread`
**Filter:** `tpnif_customer_thread.id_order > 0` (order-linked threads only — see Gaps for general enquiries)
**Idempotency:** check `order_messages.legacy_ps_id = tpnif_customer_message.id_customer_message`

| PS Column | Laravel Column | Transform |
|---|---|---|
| `id_customer_message` | `legacy_ps_id` | Direct |
| thread.`id_order` | `order_id` | Lookup `orders.legacy_ps_id` |
| `id_employee = 0` → thread.`id_customer` | `sender_user_id` | Lookup `users.legacy_ps_id`; NULL if employee or unknown |
| `'Imported from PS'` | `subject` | Fixed string |
| `message` | `body` | Direct |
| `["email"]` | `channels` | JSON array — assume email |
| `private` | `is_private_note` | Cast to bool |
| `private = 0` | `is_visible_to_customer` | `!private` |
| `false` | `sent_via_email` etc. | Historical — all false |
| `date_add` | `created_at`, `sent_at` | Direct |

**Notes:**
- Threads with `id_order = 0` (general enquiries) are skipped — see Gaps
- `id_employee > 0` messages: sender is staff — `sender_user_id = NULL` (employee→user mapping not available)
- `is_private_note = true` where `private = 1`

---

### 9. Order Carrier → `orders` (supplement)

**Not a separate entity** — data absorbed into `orders` table during order migration.

| PS Column | Laravel Column | Notes |
|---|---|---|
| `tracking_number` | `waybill_number` | Only if orders.waybill_number is empty |
| `shipping_cost_tax_incl` | *(already from tpnif_orders)* | Redundant |

---

### 10. Credit Slips → `order_credits`

**Idempotency:** check `order_credits.legacy_ps_id = tpnif_order_slip.id_order_slip`

| PS Column | Laravel Column | Transform |
|---|---|---|
| `id_order_slip` | `legacy_ps_id` | Direct |
| `id_order` | `order_id` | Lookup `orders.legacy_ps_id` |
| `id_customer` | `user_id` | Lookup `users.legacy_ps_id` |
| `amount` | `amount` | Direct (ZAR) |
| `'refund'` | `type` | Fixed — all PS slips are refunds |
| `'Imported from PrestaShop'` | `reason` | Fixed |
| `NULL` | `notes` | No PS equivalent |
| `NULL` | `reference` | No PS equivalent |
| `date_add` | `created_at` | Direct |
| `date_upd` | `updated_at` | Direct |

---

### 11. PS Products → `local_products`

**Source:** `tpnif_product p` JOIN `tpnif_product_lang pl` (lang=1, shop=1) LEFT JOIN `tpnif_manufacturer m`
**Filter:** `p.active = 1`
**Idempotency:** check `local_products.ps_product_id = p.id_product`

| PS Column | Laravel Column | Transform |
|---|---|---|
| `p.id_product` | `ps_product_id` | Direct (existing column) |
| `p.reference` (or `'PS{id_product}'` if blank) | `sku` | Fallback generated |
| `p.reference` | `part_number` | Same as sku |
| `pl.name` | `name` | Direct |
| `pl.description` | `description` | `strip_tags()` — remove HTML |
| `m.name` (or `'Performance SA'` if no manufacturer) | `brand` | LEFT JOIN lookup |
| `p.price * 1.15` | `price_incl` | PS price is tax-excl; apply 15% ZAR VAT |
| `p.weight` | `weight_kg` | Direct (PS stores weight in kg) |
| `p.active` | `active` | Cast to bool |
| `NULL` | `images` | Images not migrated — admin adds later |
| `NULL` | `category`, `subcategory` | Not mapped — admin assigns later |

**Notes:**
- Products are migrated before orders/order-items so the local product lookup works during item migration
- PS products that also exist in Turn14 (same `product_reference` / part number) will be found by Turn14 lookup first — the local_product entry is harmless but won't be linked from order_items

---

### 12. PS Returns → `order_returns` + `order_return_items`

**Source:** `tpnif_order_return` + `tpnif_order_return_detail`
**Row count:** ~0 (table exists in PS but is empty in this dump)
**Idempotency:** check `order_returns.legacy_ps_id = tpnif_order_return.id_order_return`

PS state → Laravel status:

| PS State | Name | Laravel `status` |
|---|---|---|
| 1 | Waiting for confirmation | `pending` |
| 2 | Waiting for package | `awaiting_return` |
| 3 | Package received | `received` |
| 4 | Return denied | `denied` |
| 5 | Return completed | `completed` |

| PS Column | Laravel Column | Notes |
|---|---|---|
| `id_order_return` | `legacy_ps_id` | |
| `id_order` → orders lookup | `order_id` | |
| `id_customer` → users lookup | `user_id` | |
| state map | `status` | |
| `question` | `reason` + `customer_notes` | Same text in both fields |
| `date_add` | `created_at` | |
| `date_upd` | `updated_at` | |

For `order_return_items`:
- `id_order_detail` → `order_item_id` via `order_items.legacy_ps_id` lookup
- `product_quantity` → `qty_returned`

---

### 13. Newsletter Subscribers → `newsletter_subscribers`

Two sources merged into one target, de-duped by email:

**Source 1:** `tpnif_emailsubscription` WHERE `active = 1`
- `email` → `email`
- `newsletter_date_add` → `subscribed_at`

**Source 2:** `tpnif_customer` WHERE `newsletter = 1 AND deleted = 0 AND is_guest = 0 AND active = 1`
- `email` → `email`
- `firstname + ' ' + lastname` → `name`
- `newsletter_date_add` → `subscribed_at` (or `now()` if null)

---

### 14. Product Reviews → `product_reviews`

**PS table:** `tpnif_product_comment`
**Row count:** **0** — nothing to migrate. Table exists in Laravel.

---

### 15. Wishlists → `wishlists`

**PS tables:** `tpnif_wishlist`, `tpnif_wishlist_product`
**Row count:** **0** — nothing to migrate. Table exists in Laravel.

---

## Relationship chains

```
users (legacy_ps_id ← tpnif_customer.id_customer)
  │
  ├── user_addresses (legacy_ps_id ← tpnif_address.id_address)
  │
  └── orders (legacy_ps_id ← tpnif_orders.id_order)
          │
          ├── order_items (legacy_ps_id ← tpnif_order_detail.id_order_detail)
          │       └── [turn14_product lookup via part_number — may be NULL]
          │
          ├── order_invoices (legacy_ps_id ← tpnif_order_invoice.id_order_invoice)
          │
          ├── order_events (legacy_ps_id ← tpnif_order_history.id_order_history)
          │
          ├── order_messages (legacy_ps_id ← tpnif_customer_message.id_customer_message)
          │       [only where tpnif_customer_thread.id_order > 0]
          │
          └── order_credits (legacy_ps_id ← tpnif_order_slip.id_order_slip)
```

---

## Data type / format differences

| Issue | PS format | Laravel handling |
|---|---|---|
| Invalid dates | `'0000-00-00'` / `'0000-00-00 00:00:00'` | → `NULL` |
| Currency | ZAR already (confirmed from data — amounts like R507, R2500) | No conversion needed |
| Prices | `decimal(20,6)` | Round to `decimal(10,2)` |
| Multi-language | `tpnif_*_lang` tables with `id_lang` | Extract `id_lang = 1` (English) only |
| Serialized PHP | `tpnif_cart.delivery_option` uses PHP `serialize()` or JSON | Not needed (carts skipped) |
| PS product IDs | Integer PK in `tpnif_product`; different from Turn14 product IDs | Use `product_reference` (part_number) for Turn14 lookup |
| Employee ↔ User | PS has separate `tpnif_employee` table with no Laravel equivalent | Set `created_by = NULL` for all employee-originated events |