← back to Dw Yolo Loop

scripts/specs-crosswalk-probe/probe-pattern-crosswalk.sh

26 lines

#!/bin/sh
# probe-pattern-crosswalk (c33) — READ-ONLY feasibility test of bridging the
# specs SKU-namespace gap (c32) via pattern_name instead of exact SKU. Width &
# material are per-PATTERN (constant across colorways), so a pattern-level match
# suffices to assign specs. Verdict: PARTIAL — Clarke 288/1392 (21%), Coordonne
# 451/1320 (34%) live products covered; ceiling is structural (vendor-direct
# catalog is wallpaper-only CCW, can't cover the 1,028 distributor fabrics F#).
# READ-ONLY. $0 (local PG mirror).
PSQL=/opt/homebrew/opt/postgresql@14/bin/psql
DB="postgresql:///dw_unified?host=/tmp"
for v in "Clarke And Clarke:clarke_clarke_catalog" "Coordonné:coordonne_catalog"; do
  vendor="${v%%:*}"; cat="${v##*:}"
  echo "## $vendor vs $cat — pattern-name crosswalk coverage"
  $PSQL "$DB" -c "
    with cat as (
      select distinct lower(regexp_replace(
        case when color_name>'' then regexp_replace(pattern_name,color_name,'','i') else pattern_name end,
        '[^a-z0-9]','','gi')) p
      from $cat where width_inches is not null)
    select count(*) live_products,
      count(*) filter (where lower(regexp_replace(pattern_name,'[^a-z0-9]','','gi')) in (select p from cat)) covered_by_pattern_crosswalk
    from shopify_products where vendor='$vendor' and status='ACTIVE' and pattern_name>'';"
done
echo "## live Clarke product-type split (catalog is wallpaper-only)"
$PSQL "$DB" -c "select case when mfr_sku like 'F%' then 'fabric F#' when mfr_sku like 'W%' then 'wallpaper W#' else 'other' end typ, count(*) from shopify_products where vendor='Clarke And Clarke' and status='ACTIVE' group by 1 order by 2 desc;"