`zoffany_mfr_recovery_dryrun.sql`

21 lines
-- DRY RUN: Zoffany mfr_sku recovery via handle→dw_sku→zoffany_catalog join
-- Ticket: TK-10677 (sub-work of TK-10794)
-- Recovers 24 ACTIVE Zoffany products with null mfr_sku via LOCAL JOIN (no scrape needed)
-- Reversible: undo = UPDATE shopify_products SET mfr_sku='' WHERE id IN (<these 24 ids>)
-- No externality: local dw_unified mirror write only (Shopify write stays gated per TK rules)
-- Blast radius: 24 rows

BEGIN;

-- Simulate the update (SELECT version)
SELECT sp.id, sp.title, sp.handle,
       upper(regexp_replace(sp.handle, '.*-(dwzf-\d+)$', '\1')) as extracted_dw_sku,
       zc.mfr_sku as new_mfr_sku
FROM shopify_products sp
JOIN zoffany_catalog zc ON zc.dw_sku = upper(regexp_replace(sp.handle, '.*-(dwzf-\d+)$', '\1'))
WHERE sp.vendor = 'Zoffany'
  AND sp.status = 'ACTIVE'
  AND (sp.mfr_sku IS NULL OR sp.mfr_sku = '');

ROLLBACK;