`build-supplement.sql`

32 lines
-- TK-11376 SUPPLEMENT — products excluded from plan.tsv only because shopify_products.dw_sku
-- (the MIRROR COLUMN) is null, but whose dw_sku IS present in a metafield. Metafield is truth.
-- Deliberately SEPARATE from plan.tsv so no already-applied target title can shift.
WITH dupgroups AS (
  SELECT vendor,title FROM shopify_products WHERE status='ACTIVE' AND title IS NOT NULL
  GROUP BY 1,2 HAVING count(*)>1
),
dupdw AS (  -- same duplicate-identity exclusion as the main plan
  SELECT dw_sku FROM shopify_products WHERE status='ACTIVE' AND dw_sku IS NOT NULL AND dw_sku<>''
  GROUP BY 1 HAVING count(*)>1
),
aff AS (
  SELECT sp.shopify_id, sp.handle, sp.vendor, sp.title AS old_title,
    coalesce(nullif(trim(sp.metafields->'dwc'->'dw_sku'->>'value'),''),
             nullif(trim(sp.metafields->'global'->'dw_sku'->>'value'),''),
             nullif(trim(sp.metafields->'custom'->'dw_sku'->>'value'),'')) AS dw
  FROM shopify_products sp JOIN dupgroups d ON d.vendor=sp.vendor AND d.title=sp.title
  WHERE sp.status='ACTIVE'
    AND nullif(trim(sp.dw_sku),'') IS NULL              -- mirror column empty = was excluded
    AND (sp.dw_sku IS NULL OR sp.dw_sku NOT IN (SELECT dw_sku FROM dupdw))
),
ok AS (SELECT * FROM aff WHERE dw IS NOT NULL
       -- skip if the sku is already written in the title (would be redundant)
       AND position(lower(dw) in lower(old_title))=0)
SELECT shopify_id, handle, vendor, old_title,
  CASE WHEN position(' | ' in old_title)>0
       THEN substring(old_title from 1 for position(' | ' in old_title)-1)||' - '||dw
            ||substring(old_title from position(' | ' in old_title))
       ELSE old_title||' - '||dw END AS new_title,
  'dw_sku_metafield' AS source
FROM ok ORDER BY vendor, old_title;