`scripts/refresh-live-catalog.sh`

40 lines
#!/bin/bash
# Idempotent refresh of the LIVE catalog: re-runs the CJ ingests directly against
# the production DB (over an SSH tunnel) so products upsert in place by their
# (network, external_id) key — refreshing prices/stock AND swapping in TRACKED
# affiliate links the moment each CJ advertiser gets approved. No duplicates, no
# data loss, no truncate. Safe to run nightly.
#
# Run manually:   bash scripts/refresh-live-catalog.sh
# Or via launchd (see com.steve.ids-catalog-refresh.plist).
set -euo pipefail
KAM=root@45.61.58.125
PROJ="$HOME/Projects/interiordesignershowroom"
TPORT=15432
cd "$PROJ"

echo "[$(date '+%H:%M:%S')] refresh-live-catalog starting"

# 1) pull the live DB role password out of the live .env DATABASE_URL (never printed)
LIVE_URL=$(ssh "$KAM" "grep -m1 '^DATABASE_URL=' /root/Projects/interiordesignershowroom/.env | cut -d= -f2-")
LIVE_PW=$(printf '%s' "$LIVE_URL" | sed -E 's#.*://[^:]+:([^@]+)@.*#\1#')
if [ -z "$LIVE_PW" ]; then echo "could not resolve live DB password"; exit 1; fi

# 2) open an SSH tunnel to the live Postgres (localhost:5432 on Kamatera)
ssh -f -N -o ExitOnForwardFailure=yes -L ${TPORT}:127.0.0.1:5432 "$KAM"
TUNNEL_PID=$(pgrep -f "ssh -f -N -o ExitOnForwardFailure=yes -L ${TPORT}:127.0.0.1:5432 $KAM" | head -1)
trap '[ -n "${TUNNEL_PID:-}" ] && kill "$TUNNEL_PID" 2>/dev/null || true' EXIT
sleep 2

# 3) run the ingests straight against LIVE (upsert by natural key -> tracked links update)
export DATABASE_URL="postgresql://idshowroom:${LIVE_PW}@127.0.0.1:${TPORT}/idshowroom"
node scripts/ingest-cj-catalog.js --per=70
node scripts/ingest-brands.js
node scripts/ingest-samplize.js

# 4) restart the live app + report tracked-link coverage
ssh "$KAM" "cd /root/Projects/interiordesignershowroom && pm2 restart interiordesignershowroom" >/dev/null 2>&1 || true
TRACKED=$(PGPASSWORD="$LIVE_PW" psql "host=127.0.0.1 port=${TPORT} user=idshowroom dbname=idshowroom" -tAc \
  "SELECT count(*) FILTER (WHERE affiliate_url ~ 'cj.com|dpbolvw|anrdoezrs|kqzyfj|tkqlhce|jdoqocy')||'/'||count(*) FROM products" 2>/dev/null || echo '?')
echo "[$(date '+%H:%M:%S')] done. tracked-link coverage: ${TRACKED}"