`tk11041-innovations-reconcile/rescrape/backfill/apply-backfill.sql`

28 lines
-- TK-11041 — Innovations shopify_product_id backfill (GATED; DO NOT run without Steve's go)
-- DTD verdict A (2026-09-12, 4/4): pre-stage the match; this WRITE stays gated.
-- Scope: the 39 reprice cohort (WHL/Whistler 12 EXCLUDED per DTD verdict B hold).
-- Match proven: 51/51 exact 1:1 on mfr_sku, 0 no-match, 0 ambiguous (see match-51.tsv).
-- Effect: populates innovations_catalog.shopify_product_id + on_shopify from the live-store
--         mirror. Pure IDENTITY linkage — NO price, NO product status, NO Shopify write.
-- Prestate (verified 2026-09-12): all target rows shopify_product_id='' / NULL, on_shopify=f.
BEGIN;
UPDATE innovations_catalog ic
   SET shopify_product_id = sp.shopify_id,
       on_shopify = true
  FROM shopify_products sp
 WHERE upper(ic.mfr_sku) = upper(sp.mfr_sku)
   AND sp.vendor = 'Phillipe Romano'
   AND sp.status = 'ACTIVE'
   AND NOT coalesce(sp.has_product_variant, false)
   AND sp.supplier_name ~* 'innov'
   AND split_part(sp.mfr_sku, '-', 1) <> 'WHL';
-- EXPECT: UPDATE 39   (abort/ROLLBACK if the count is not exactly 39)
-- Verify before COMMIT:
--   SELECT count(*) FROM innovations_catalog ic JOIN shopify_products sp
--     ON upper(ic.mfr_sku)=upper(sp.mfr_sku)
--    WHERE sp.vendor='Phillipe Romano' AND sp.status='ACTIVE'
--      AND NOT coalesce(sp.has_product_variant,false) AND sp.supplier_name ~* 'innov'
--      AND split_part(sp.mfr_sku,'-',1) <> 'WHL'
--      AND ic.shopify_product_id = sp.shopify_id;   -- EXPECT 39
COMMIT;