`reconcile-staging/reconcile-image-less.sh`

131 lines
#!/bin/bash
# reconcile-image-less.sh — clears the PHANTOM Phase-3 enrichment backlog.
#
# ROOT CAUSE (2026-08-31, TK-phase3-phantom): AI enrichment requires an image
# (enrich-ai-tags.js:593 → `image_url IS NOT NULL`). Catalog rows with NO
# image_url can never be enriched, so their enrichment_tracking.phase3_ai_at
# stays NULL forever. The phase3-monitor counts phase3_ai_at IS NULL as
# "pending" (no image check), so ~4,236 un-enrichable image-less rows are
# phantom-counted every night → "NO drain overnight" false alarm.
#
# FIX: stamp those image-less pending rows with last_error='unenrichable-no-image'.
# The monitor's EXISTING filter (`last_error NOT LIKE 'unenrichable-%'`) then
# drops them, and the backlog reads its true (~17) count. This does NOT set
# phase3_ai_at (that would falsely claim enrichment ran) and does NOT block
# re-enrichment: enrich-ai-tags.js's selector ignores last_error, so if an image
# later arrives the row is re-picked and phase3_ai_at gets stamped normally.
#
# SELF-HEALING: a second pass CLEARS the stamp on any row that has since gained
# an image (Codex reversibility rider), so re-running is idempotent + correct.
#
# REVERSIBLE: --apply snapshots every affected (id, catalog_table) to a
# timestamped JSON BEFORE writing. Undo = restore-image-less.sh <snapshot>.
#
# Usage:
#   ./reconcile-image-less.sh            # DRY-RUN (default) — counts only, no writes
#   ./reconcile-image-less.sh --apply    # execute (snapshots first)
set -euo pipefail

APPLY=0
[ "${1:-}" = "--apply" ] && APPLY=1
PSQL="sudo -u postgres psql -d dw_unified -Atc"
SNAPDIR="$(dirname "$0")/reconcile-snapshots"
TS="$(date -u +%Y-%m-%dT%H-%M-%SZ)"
SNAP="$SNAPDIR/reconcile-$TS.json"
mkdir -p "$SNAPDIR"

echo "=== reconcile-image-less  (mode: $([ $APPLY = 1 ] && echo APPLY || echo DRY-RUN)) ==="

# Scope = the SAME actionable universe the phase3-monitor counts: parse
# PIPELINE_EXCLUDED_VENDORS live from full-monte-batch.js (single source of
# truth) and exclude those vendor_codes + spoonflower. This targets exactly the
# rows causing the monitor's false alarm and mislabels no rule-excluded vendor.
BATCHJS=/root/DW-Agents/full-monte/full-monte-batch.js
EXCL=$(sed -n '/PIPELINE_EXCLUDED_VENDORS = new Set(\[/,/\]);/p' "$BATCHJS" 2>/dev/null \
        | grep -oE "'[a-z0-9_]+'" | paste -sd, -)
[ -z "$EXCL" ] && EXCL="'cowtan_tout','colefax_fowler','phillip_jeffries'"
EXCL="$EXCL,'spoonflower'"
echo "excluding vendors: $EXCL"
echo

# The predicate for a phantom row, evaluated per catalog_table:
#   phase3_ai_at IS NULL                              (monitor counts it pending)
#   AND last_error IS NULL                            (untried — don't touch errored rows)
#   AND the mfr_sku has NO catalog row with an image  (truly un-enrichable)
#   AND NOT any catalog row for that mfr_sku HAS an image (multi-row sku guard)
build_where() { # $1 = catalog_table  (aliased et)
  cat <<SQL
et.catalog_table = '$1'
  AND et.phase3_ai_at IS NULL
  AND et.last_error IS NULL
  AND et.vendor_code NOT IN ($EXCL)
  AND NOT EXISTS (SELECT 1 FROM "$1" c
                  WHERE c.mfr_sku = et.mfr_sku
                    AND c.image_url IS NOT NULL AND c.image_url <> '')
SQL
}

# Tables in scope = every catalog_table that has ≥1 untried phase3-null row,
# and whose catalog actually has an image_url column (skip if not).
TABLES=$($PSQL "SELECT DISTINCT catalog_table FROM enrichment_tracking
                WHERE phase3_ai_at IS NULL AND last_error IS NULL AND catalog_table IS NOT NULL
                ORDER BY 1")

TOTAL=0
CLEAR_TOTAL=0
SKIPPED=""
echo "[" > "$SNAP.tmp"
FIRST=1
for T in $TABLES; do
  # guard: table must have BOTH image_url AND mfr_sku columns (the join keys).
  # Tables lacking either can't be reconciled by this join → skip + report.
  COLS=$($PSQL "SELECT count(*) FROM information_schema.columns
                WHERE table_name='$T' AND column_name IN ('image_url','mfr_sku')" 2>/dev/null || echo 0)
  if [ "$COLS" != "2" ]; then
    echo "  skip $T (missing image_url and/or mfr_sku column)"
    SKIPPED="$SKIPPED $T"
    continue
  fi

  WH=$(build_where "$T")
  N=$($PSQL "SELECT count(*) FROM enrichment_tracking et WHERE $WH" 2>/dev/null || echo 0)
  # self-heal: rows previously stamped that now HAVE an image → clear
  C=$($PSQL "SELECT count(*) FROM enrichment_tracking et
             WHERE et.catalog_table='$T' AND et.last_error='unenrichable-no-image'
               AND EXISTS (SELECT 1 FROM \"$T\" c WHERE c.mfr_sku=et.mfr_sku
                           AND c.image_url IS NOT NULL AND c.image_url<>'')" 2>/dev/null || echo 0)
  [ "$N" = "0" ] && [ "$C" = "0" ] && continue
  printf "  %-34s stamp=%-6s clear=%s\n" "$T" "$N" "$C"
  TOTAL=$((TOTAL+N)); CLEAR_TOTAL=$((CLEAR_TOTAL+C))

  if [ $APPLY = 1 ]; then
    # snapshot the ids we are about to stamp (old last_error is always NULL here)
    IDS=$($PSQL "SELECT id FROM enrichment_tracking et WHERE $WH")
    for id in $IDS; do
      [ $FIRST = 1 ] && FIRST=0 || echo "," >> "$SNAP.tmp"
      printf '{"id":%s,"catalog_table":"%s","old_last_error":null}' "$id" "$T" >> "$SNAP.tmp"
    done
    # stamp
    $PSQL "UPDATE enrichment_tracking et SET last_error='unenrichable-no-image', updated_at=now() WHERE $WH" >/dev/null
    # self-heal clear
    $PSQL "UPDATE enrichment_tracking et SET last_error=NULL, updated_at=now()
           WHERE et.catalog_table='$T' AND et.last_error='unenrichable-no-image'
             AND EXISTS (SELECT 1 FROM \"$T\" c WHERE c.mfr_sku=et.mfr_sku
                         AND c.image_url IS NOT NULL AND c.image_url<>'')" >/dev/null
  fi
done
echo "]" >> "$SNAP.tmp"

echo "----------------------------------------------"
echo "TOTAL image-less phantom rows to stamp : $TOTAL"
echo "TOTAL stale stamps to clear (self-heal): $CLEAR_TOTAL"
[ -n "$SKIPPED" ] && echo "SKIPPED tables (no mfr_sku/image_url col — handle separately):$SKIPPED"
if [ $APPLY = 1 ]; then
  mv "$SNAP.tmp" "$SNAP"
  echo "APPLIED. Snapshot (undo map): $SNAP"
  echo "Undo: ./restore-image-less.sh $SNAP"
else
  rm -f "$SNAP.tmp"
  echo "DRY-RUN — no writes. Re-run with --apply to execute."
fi