`scripts/specs-reparse-sizing/size-specs-reparse.sh`

26 lines
#!/bin/sh
# size-specs-reparse — cycle-32 READ-ONLY sizing of the council #1 idea
# ("backfill specs from already-crawled data"). Answers: is the repped-vendor
# specs gap a cheap DB->metafield push, or is it blocked? Verdict: BLOCKED by a
# SKU-namespace mismatch — the per-vendor *_catalog tables (scraped vendor-direct,
# e.g. Clarke CCW#### SKUs) carry ~100% width but DON'T join to the live Shopify
# products (imported via distributor channels, e.g. Cowtan F#/W# .CAC.0 SKUs).
# READ-ONLY. $0 (local PG mirror).
PSQL=/opt/homebrew/opt/postgresql@14/bin/psql
DB="postgresql:///dw_unified?host=/tmp"
VENDORS="clarke_clarke_catalog gaston_daniela_catalog coordonne_catalog lee_jofa_catalog brunschwig_catalog osborne_catalog"
echo "## catalog spec coverage + active-product join (mfr_sku)"
for t in $VENDORS; do
  $PSQL "$DB" -t -c "
    select '$t' tbl,
      count(*) cat_rows,
      count(*) filter (where c.width_inches is not null) width_in_cat,
      count(*) filter (where c.material is not null and c.material<>'') mat_in_cat,
      count(*) filter (where s.id is not null) joins_active
    from $t c
    left join shopify_products s on upper(s.mfr_sku)=upper(c.mfr_sku) and s.status='ACTIVE';"
done
echo "## SKU namespace samples (mismatch proof)"
echo "shopify Clarke:"; $PSQL "$DB" -t -c "select mfr_sku from shopify_products where vendor='Clarke And Clarke' and status='ACTIVE' and mfr_sku is not null limit 4;"
echo "catalog Clarke:"; $PSQL "$DB" -t -c "select mfr_sku from clarke_clarke_catalog limit 4;"