`scripts/dup-sku-scan/dup-sku-scan.sh`

14 lines
#!/bin/sh
# dup-sku-scan (c50) — READ-ONLY. Looked for distinct ACTIVE products sharing one
# mfr_sku (data-integrity dup). VERDICT after 2-layer skeptical decode: genuine
# duplicate-SKU defects ~0 — every "shared SKU" is colorways of one pattern sharing
# the vendor's pattern-level SKU (Scalamandre WP####, Nina Campbell NCW####). BUT
# the decode surfaced a real issue: 529 ACTIVE products carry a PLACEHOLDER/junk SKU
# (no-digit, shared): SANCAR 179 / TRUE 91 (boolean leak!) / YORK 57 / OSBORNE 56 /
# INNOVATIONS 52 / DONGHIA 16 + descriptive strings — not real SKUs. $0 local.
PSQL=/opt/homebrew/opt/postgresql@14/bin/psql
DB="postgresql:///dw_unified?host=/tmp"
echo "## placeholder/junk SKUs (no-digit mfr_sku shared >3 active products)"
$PSQL "$DB" -c "select upper(trim(mfr_sku)) sku, count(distinct handle) products, left(string_agg(distinct vendor,', '),60) vendors from shopify_products where status='ACTIVE' and mfr_sku<>'' and trim(mfr_sku) !~ '[0-9]' group by upper(trim(mfr_sku)) having count(distinct handle)>3 order by 2 desc limit 20;"
$PSQL "$DB" -c "with p as (select upper(trim(mfr_sku)) sku from shopify_products where status='ACTIVE' and mfr_sku<>'' and trim(mfr_sku) !~ '[0-9]' group by upper(trim(mfr_sku)) having count(distinct handle)>3) select count(*) placeholder_sku_products from shopify_products where status='ACTIVE' and upper(trim(mfr_sku)) in (select sku from p);"