`build-supplement2.sql`

31 lines
-- TK-11376 SUPPLEMENT 2 — products with NO dw_sku anywhere, but a CLEAN manufacturer_sku
-- (metafield preferred; must contain a digit, so junk like 'SANCAR'/'TRUE'/'Donghia' is rejected).
WITH dupgroups AS (SELECT vendor,title FROM shopify_products WHERE status='ACTIVE' AND title IS NOT NULL
                   GROUP BY 1,2 HAVING count(*)>1),
aff AS (
 SELECT sp.shopify_id, sp.handle, sp.vendor, sp.title AS old_title,
   coalesce(nullif(trim(sp.dw_sku),''), nullif(trim(sp.metafields->'dwc'->'dw_sku'->>'value'),''),
            nullif(trim(sp.metafields->'global'->'dw_sku'->>'value'),''),
            nullif(trim(sp.metafields->'custom'->'dw_sku'->>'value'),'')) dw,
   coalesce(nullif(trim(sp.metafields->'dwc'->'manufacturer_sku'->>'value'),''),
            nullif(trim(sp.metafields->'custom'->'manufacturer_sku'->>'value'),''),
            nullif(trim(sp.mfr_sku),'')) mfr
 FROM shopify_products sp JOIN dupgroups d ON d.vendor=sp.vendor AND d.title=sp.title
 WHERE sp.status='ACTIVE'),
ok AS (
 SELECT * FROM aff
 WHERE dw IS NULL                                   -- not covered by main plan or supplement 1
   AND mfr IS NOT NULL AND mfr ~ '[0-9]'            -- clean: must carry a digit
   AND position(lower(mfr) in lower(old_title))=0), -- not already in the title
-- a mfr_sku shared by >1 product in the same group cannot differentiate them
uniq AS (SELECT vendor, old_title, mfr, count(*) c FROM ok GROUP BY 1,2,3)
SELECT o.shopify_id, o.handle, o.vendor, o.old_title,
  CASE WHEN position(' | ' in o.old_title)>0
       THEN substring(o.old_title from 1 for position(' | ' in o.old_title)-1)||' - '||o.mfr
            ||substring(o.old_title from position(' | ' in o.old_title))
       ELSE o.old_title||' - '||o.mfr END AS new_title,
  'mfr_sku_clean' AS source
FROM ok o JOIN uniq u ON u.vendor=o.vendor AND u.old_title=o.old_title AND u.mfr=o.mfr
WHERE u.c=1
ORDER BY o.vendor, o.old_title;