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

23 lines
#!/bin/sh
# dup-image-scan (c47) — READ-ONLY. ACTIVE products sharing the EXACT same primary
# image_url across distinct handles could be a wrong-image/copy-paste error (shopper
# sees the wrong picture). VERDICT: clean — 27 shared-image groups / 56 products, but
# ALL are same-PATTERN variants: 20 colorways (same pattern, diff color) + 7 substrate
# variants (e.g. Simpatico Paper vs Print, Sporting Life vs Velvet). The shared photo
# is the correct pattern image across that pattern's color/material variants (vendor
# gave one photo/pattern) — NOT a wrong-image defect. $0 local mirror.
PSQL=/opt/homebrew/opt/postgresql@14/bin/psql
DB="postgresql:///dw_unified?host=/tmp"
$PSQL "$DB" -c "
with i as (select image_url, count(distinct handle) h,
  count(distinct split_part(trim(title),' - ',1)) bp
  from shopify_products where status='ACTIVE' and image_url<>'' group by image_url having count(distinct handle)>1)
select count(*) shared_groups, sum(h) products,
  count(*) filter (where bp=1) colorway_groups,
  count(*) filter (where bp>1) cross_label_groups
from i;"
echo '-- cross-label groups (manually confirmed = substrate variants of same pattern, not wrong-image) --'
$PSQL "$DB" -c "
with i as (select image_url, count(distinct handle) h, count(distinct split_part(trim(title),' - ',1)) bp, string_agg(distinct split_part(trim(title),' - ',1),' | ') pats, string_agg(distinct vendor,',') v from shopify_products where status='ACTIVE' and image_url<>'' group by image_url having count(distinct handle)>1)
select v vendor, left(pats,55) patterns from i where bp>1 order by h desc;"