`zoffany_mfr_recovery_APPLY.sql`

24 lines
-- ⚠️ MIRROR-ONLY — NOT DURABLE (2026-08-24 TK-10677): shopify_products is Kamatera-canonical;
-- a Mac2 mirror write reverts on next sync. Use verified CSV + Kamatera+Shopify path in the pending-approval memo instead.

-- APPLY (not dry-run): Zoffany mfr_sku recovery — 24 products
-- Ticket: TK-10677 / TK-10794
-- Approved: <APPROVAL_DATE>
-- Reversible undo: UPDATE shopify_products SET mfr_sku='' WHERE vendor='Zoffany' AND status='ACTIVE' AND mfr_sku IN ('ZFOW312943','ZHIW312996','ZTOT312747','ZPHA312595','ZKEM312648','ZRHW312903','ZRHW312914','ZRHW312907','ZRHW312899','ZKEM312641','ZRHW312910','ZRHW312890','ZRHW312906','ZRHW312897','ZHIW312994','ZQUA310993','ZHIW313004','ZRHW312889','ZRHW312916','ZFOW312941','ZGUV08004','ZHIW312995','ZHIW313003','ZHIW312997')
-- NOTE: This is the dw_unified mirror write ONLY. Shopify write must follow separately (Steve-gated).

BEGIN;

UPDATE shopify_products sp
SET mfr_sku = zc.mfr_sku
FROM zoffany_catalog zc
WHERE zc.dw_sku = upper(regexp_replace(sp.handle, '.*-(dwzf-\d+)$', '\1'))
  AND sp.vendor = 'Zoffany'
  AND sp.status = 'ACTIVE'
  AND (sp.mfr_sku IS NULL OR sp.mfr_sku = '');

SELECT count(*) as rows_updated FROM shopify_products
WHERE vendor = 'Zoffany' AND status = 'ACTIVE' AND mfr_sku IS NOT NULL AND mfr_sku != '';

COMMIT;