# Catalog Migration Rescue - Mapping & Reconciliation Report

## Field mapping

### Supplier A (`supplier-a.csv`) -> Shopify
| Supplier A column | Shopify column | Notes |
|---|---|---|
| SKU | Variant SKU | Preserved exactly |
| Title | Title | Canonical title (see matching rules) |
| Category | Type | |
| Size / Color / Pack / Capacity / Thickness / Scent | Option1/Option2 Name + Value | Only populated axes are used |
| Price | Variant Price | Plain decimal (`24.99`) |
| Stock | Variant Inventory Qty | |
| Image | Image Src | |
| Weight (g) | Variant Grams | |
| Description | Body (HTML) | Wrapped in `<p>...</p>` |

### Supplier B (`supplier-b.csv`) -> Shopify
| Supplier B column | Shopify column | Notes |
|---|---|---|
| Item Code | Variant SKU | Preserved exactly (e.g. `SKU-61007`) |
| Product Title | Title | Reworded titles matched by normalized key |
| Type | Type | |
| Variant Size / Color / Pack / Capacity / Thickness | Option1/Option2 Name + Value | `Gray` normalized to `Grey`; `1L` = `1000ml`; spaced units (`750 ml`) unified |
| Price | Variant Price | `$` prefix and `USD` suffix stripped |
| Inventory | Variant Inventory Qty | |
| Photo | Image Src | |
| Grams | Variant Grams | |
| *(no description column)* | Body (HTML) | Filled from another supplier when available, else left blank - never written for them |

### Supplier C (`supplier-c.csv`) -> Shopify
| Supplier C column | Shopify column | Notes |
|---|---|---|
| Product Code | Variant SKU | Preserved exactly (dotted format, e.g. `WB.750.BLU`) |
| Item | Title | ALL-CAPS and filler words (`Premium`) ignored for matching |
| Category | Type | |
| Options | Option1/Option2 Name + Value | Combined string (`Blue / 750ml`, `750ml - Blue`, `Blue, 500 ml`) split and each token assigned to its axis using the values learned from suppliers A/B for the same product |
| Cost | Variant Price | `USD` prefix stripped; treated as a price candidate and logged when it disagrees |
| On Hand | Variant Inventory Qty | |
| Picture | Image Src | |
| *(no weight column)* | Variant Grams | Filled from another supplier when available, else left blank - never estimated |
| Description | Body (HTML) | |

Handle, Vendor, Tags, Published and Status are structural Shopify fields
generated by the pipeline: Handle is the slug of the canonical title, Vendor
is the product line name (first word of the canonical title), Published =
TRUE, Status = active.

## Matching rules
* Product key = normalized title: lowercased, punctuation removed, filler
  words (`premium`, `new`, `deluxe`, `pro`, `best`, `quality`) dropped,
  plurals singularized (`Towels` = `Towel`, `Boxes` = `Box`), word order
  ignored. This is how `Aurora Stainless Steel Water Bottle`,
  `Stainless Steel Water Bottle - Aurora` and `AURORA Premium Stainless
  Steel Water Bottle` are recognized as one product.
* Variant key = the product key plus normalized variant attributes. Units
  are unified (`1L` = `1000ml` = `1 L`, `750ml` = `750 ml`, `20cm` = `20 cm`)
  and spelling is unified (`Gray` = `Grey`).

## Conflict-resolution rules
1. Every input row is preserved as a source record; nothing is edited in place.
2. For each matched variant, records are ranked by **completeness** (how many
   of SKU / price / image / stock / weight / description are populated).
   Ties break by supplier priority **A > B > C**, then by file order.
3. The winning record supplies the output values (its SKU is used verbatim).
   A field the winner lacks is filled from the next-best record that has it.
   **Values are only ever copied from a supplier file - never guessed,
   averaged or invented.**
4. Any field where suppliers disagree (both supply a value, values differ)
   is counted as a conflict, by type, below.
5. A variant is published only if, after merging, it has a price, at least
   one image, and a SKU that no other variant uses. Otherwise **all** of its
   source rows go to the exception log for a client decision.
6. Duplicate listings (the same supplier listing the same variant twice) are
   merged like any other record for that variant and counted below.

## Counts

| Measure | Count |
|---|---|
| Rows in supplier-a.csv | 69 |
| Rows in supplier-b.csv | 72 |
| Rows in supplier-c.csv | 59 |
| **Total input rows** | **200** |
| Unique products found (all suppliers) | 22 |
| Unique variants found | 93 |
| Duplicate listings merged (same supplier, same variant) | 3 |
| Input rows merged into the output catalog | 195 |
| Input rows sent to the exception log | 5 |
| Products in the Shopify file | 21 |
| Variants (rows) in the Shopify file | 88 |

## Conflicts found, by type

| Conflict type | Variant groups affected |
|---|---|
| SKU | 79 |
| image | 77 |
| price | 63 |
| stock quantity | 79 |
| title wording | 79 |
| weight | 15 |
| **Total** | **392** |

## Missing values filled from another supplier (copied, never invented)

| Field | Times filled |
|---|---|
| image | 1 |
| price | 1 |

## Exceptions, by reason

| Reason | Source rows held back |
|---|---|
| No image from any supplier | 1 |
| No price from any supplier | 2 |
| SKU collision - no unique SKU available | 2 |

Full detail per record: `exception-log.csv`.