`data/tk11050-item3/audit-distinct-sku-gap.sh`

50 lines
#!/usr/bin/env bash
# TK-11050 ITEM 3 — Kamatera->Mac2 *_catalog distinct-SKU gap audit (READ-ONLY).
# Run on Mac2 (Mac2 = local /tmp socket; Kamatera = ssh). Steve runs this outside auto-mode.
# HARD LESSON baked in: compare by DISTINCT mfr_sku SET, never by row count.
# Proves whether each "Kamatera-fuller" gap is REAL missing SKUs or a koroseal-class dupe artifact.
set -uo pipefail
KAM="root@45.61.58.125"
KPSQL="psql -h 127.0.0.1 -U dw_admin -d dw_unified -tA"
WORK=/tmp/tk11050-item3; mkdir -p "$WORK"
# Discover ALL *_catalog tables that exist on BOTH machines AND have an mfr_sku column (covers the 7 "tails" too).
psql -h /tmp -d dw_unified -tA -c "SELECT table_name FROM information_schema.columns WHERE table_schema='public' AND column_name='mfr_sku' AND table_name LIKE '%_catalog' ORDER BY table_name" > "$WORK/mac2_tables.txt"
if ! ssh -n "$KAM" "$KPSQL -v ON_ERROR_STOP=1 -c \"SELECT table_name FROM information_schema.columns WHERE table_schema='public' AND column_name='mfr_sku' AND table_name LIKE '%_catalog' ORDER BY table_name\"" > "$WORK/kam_tables.txt"; then
  echo "FATAL: Kamatera catalog-table enumeration failed; refusing to report an empty/zero diff." >&2
  exit 2
fi
[ -s "$WORK/kam_tables.txt" ] || { echo "FATAL: Kamatera returned no catalog tables; refusing false-success." >&2; exit 2; }
comm -12 "$WORK/mac2_tables.txt" "$WORK/kam_tables.txt" > "$WORK/both_all.txt"
# EXCLUSIONS (per TK-11050): competitor-intel, staff-only, excluded/dead vendor, item-2,
# item-4, known exception. Armani/Casa confirmed excluded by Steve 2026-09-01. Carlisle may be
# crawled/backfilled to staging but must stay offline (no Shopify onboarding/publish).
SKIP='^(armani_casa_catalog|connie_our_catalog|connie_competitor_catalog|schumacher_catalog|fentucci_catalog|fentucci_naturals_catalog|koroseal_catalog|anna_french_catalog|rebel_walls_catalog|rebelwalls_catalog|spoonflower_catalog)$'
grep -vE "$SKIP" "$WORK/both_all.txt" > "$WORK/both_tables.txt"
printf "%-32s %10s %10s %10s %10s %10s\n" table mac2_rows mac2_dsku kam_dsku KAMonly_REAL MAC2only
printf "%-32s %10s %10s %10s %10s %10s\n" "-----" "-----" "-----" "-----" "-----" "-----"
while read -r t; do
  [ -z "$t" ] && continue
  psql -h /tmp -d dw_unified -tA -c "SELECT distinct mfr_sku FROM $t WHERE coalesce(mfr_sku,'')<>''" 2>/dev/null | sort -u > "$WORK/mac2.$t"
  # -n is mandatory inside this while-loop: without it ssh consumes the loop's stdin and
  # silently audits only the first table while exiting 0 (the original false-success bug).
  if ! ssh -n "$KAM" "$KPSQL -v ON_ERROR_STOP=1 -c \"SELECT distinct mfr_sku FROM $t WHERE coalesce(mfr_sku,'')<>''\"" 2>"$WORK/kam.$t.err" | sort -u > "$WORK/kam.$t"; then
    echo "FATAL: Kamatera read failed for $t; refusing a partial audit." >&2
    cat "$WORK/kam.$t.err" >&2
    exit 2
  fi
  m2rows=$(psql -h /tmp -d dw_unified -tA -c "SELECT count(*) FROM $t" 2>/dev/null)
  m2d=$(wc -l < "$WORK/mac2.$t" | tr -d ' ')
  kd=$(wc -l < "$WORK/kam.$t" | tr -d ' ')
  comm -13 "$WORK/mac2.$t" "$WORK/kam.$t" > "$WORK/kamonly.$t"   # in Kamatera, NOT in Mac2 = REAL backfill set
  comm -23 "$WORK/mac2.$t" "$WORK/kam.$t" > "$WORK/mac2only.$t"
  ko=$(wc -l < "$WORK/kamonly.$t" | tr -d ' ')
  mo=$(wc -l < "$WORK/mac2only.$t" | tr -d ' ')
  # Only print rows where there's any divergence
  if [ "$ko" -gt 0 ] || [ "$mo" -gt 0 ]; then
    printf "%-32s %10s %10s %10s %10s %10s\n" "$t" "$m2rows" "$m2d" "$kd" "$ko" "$mo"
  fi
done < "$WORK/both_tables.txt"
echo
echo "REAL backfill sets (Kamatera-only distinct mfr_sku) are in $WORK/kamonly.<table>"
echo "Next: run backfill-one.sh <table> for any table with KAMonly_REAL > 0 (additive INSERT, Kamatera->Mac2)."