`onboarding/sangetsu-lilycolor/dedup.sql`

23 lines
\set ON_ERROR_STOP on
CREATE TEMP TABLE staged(source text, mfr_sku text, norm text);
\copy staged FROM '/tmp/staged-skus.tsv'
CREATE TEMP TABLE reg_norm AS
  SELECT DISTINCT upper(regexp_replace(mfr_sku,'[^A-Za-z0-9]','','g')) n, dw_sku, vendor_name
  FROM dw_sku_registry WHERE mfr_sku IS NOT NULL AND mfr_sku<>'';
CREATE TEMP TABLE shop_norm AS
  SELECT DISTINCT upper(regexp_replace(coalesce(NULLIF(mfr_sku,''),NULLIF(sku,''),variant_sku),'[^A-Za-z0-9]','','g')) n, dw_sku, vendor
  FROM shopify_products;
CREATE INDEX ON reg_norm(n);
CREATE INDEX ON shop_norm(n);
ANALYZE reg_norm; ANALYZE shop_norm;
\echo '=== SUMMARY (staged vs existing) ==='
SELECT s.source,
  count(*) AS staged,
  count(*) FILTER (WHERE EXISTS(SELECT 1 FROM reg_norm r WHERE r.n=s.norm)) AS in_registry,
  count(*) FILTER (WHERE EXISTS(SELECT 1 FROM shop_norm sh WHERE sh.n=s.norm)) AS in_shopify,
  count(*) FILTER (WHERE NOT EXISTS(SELECT 1 FROM reg_norm r WHERE r.n=s.norm)
                   AND NOT EXISTS(SELECT 1 FROM shop_norm sh WHERE sh.n=s.norm)) AS net_new
FROM staged s GROUP BY s.source ORDER BY s.source;
-- detail of every match -> file for flagging
\copy (SELECT s.source, s.mfr_sku, s.norm, (SELECT r.dw_sku FROM reg_norm r WHERE r.n=s.norm LIMIT 1) reg_dw_sku, (SELECT r.vendor_name FROM reg_norm r WHERE r.n=s.norm LIMIT 1) reg_vendor, (SELECT sh.dw_sku FROM shop_norm sh WHERE sh.n=s.norm LIMIT 1) shop_dw_sku, (SELECT sh.vendor FROM shop_norm sh WHERE sh.n=s.norm LIMIT 1) shop_vendor FROM staged s WHERE EXISTS(SELECT 1 FROM reg_norm r WHERE r.n=s.norm) OR EXISTS(SELECT 1 FROM shop_norm sh WHERE sh.n=s.norm)) TO '/tmp/dedup-matches.tsv'