`refresh.sh`

40 lines
#!/bin/bash
# Daily Sanderson staging refresh. Phase A (retail, $0, robust) always runs; Phase B (trade portal, auth) best-effort.
# NO live Shopify writes. Idempotent upserts into dw_unified staging tables.
export PATH="/opt/homebrew/opt/postgresql@14/bin:/opt/homebrew/bin:/usr/bin:/bin:/usr/sbin:/sbin:$PATH"
ROOT="$(cd "$(dirname "$0")" && pwd)"; DATA="$ROOT/data"; SC="$ROOT/scripts"
LOG="$DATA/refresh.log"; cd "$DATA"
ts(){ date +%FT%T; }
echo "$(ts) ===== refresh start =====" >> "$LOG"
PGc(){ psql -h /tmp -d dw_unified -q -v ON_ERROR_STOP=1 "$@"; }

# --- Phase A: retail feed-first ($0) ---
bash "$SC/harvest_retail.sh" >> "$LOG" 2>&1 || echo "$(ts) retail harvest WARN" >> "$LOG"
python3 "$SC/extract.py" 0 all_prod_urls.txt rows.csv >> "$LOG" 2>&1 || echo "$(ts) extract WARN" >> "$LOG"
[ -s rows.csv ] && PGc -f "$SC/load.sql" >> "$LOG" 2>&1 || echo "$(ts) retail load skipped" >> "$LOG"

# --- Phase A2: incremental palette for new SKUs only ($0 local) ---
psql -h /tmp -d dw_unified -tAc "copy (select mfr_sku,image_url from sanderson_catalog where ai_colors is null and image_url is not null) to stdout with (format csv, header true)" > palette_input.csv 2>>"$LOG"
python3 "$SC/palette.py" >> "$LOG" 2>&1 || true
[ -s palette_updates.sql ] && PGc -f palette_updates.sql >> "$LOG" 2>&1 || true

# --- Phase B: trade portal (auth, best-effort) ---
TRADE=skipped
if node "$SC/harvest_trade.js" >> "$LOG" 2>&1; then
  python3 "$SC/parse_trade.py" >> "$LOG" 2>&1 \
    && PGc -f "$SC/load_trade.sql" >> "$LOG" 2>&1 \
    && PGc -f "$SC/reconcile.sql" >> "$LOG" 2>&1 && TRADE=ok || TRADE=parse_or_load_fail
fi
if [ -f "$DATA/NEEDS_REAUTH" ]; then
  TRADE=needs_reauth
  echo "$(ts) ALERT: trade portal session expired -> run scripts/reauth.sh" >> "$LOG"
  # best-effort CNCP parking-lot alert (non-fatal)
  curl -s -m 5 -X POST http://127.0.0.1:3333/api/parking-lot -H 'Content-Type: application/json' \
    -d '{"title":"Sanderson trade-portal re-auth needed","note":"Daily cadence could not reach the SDG trade portal; run ~/Projects/sanderson-onboard/scripts/reauth.sh","project":"sanderson-onboard"}' >/dev/null 2>&1 || true
fi

# --- freshness marker ---
psql -h /tmp -d dw_unified -tAc "select json_build_object('ts',now(),'retail_rows',(select count(*) from sanderson_catalog),'trade_rows',(select count(*) from sanderson_trade_catalog),'trade_publishable',(select count(*) from sanderson_trade_catalog where publishable),'trade_status','$TRADE')" > "$DATA/last_run.json" 2>>"$LOG"
echo "$(ts) ===== refresh done trade=$TRADE =====" >> "$LOG"
cat "$DATA/last_run.json"