`scripts/weekly-ingest.sh`

101 lines
#!/usr/bin/env bash
#
# Weekly LA County Eats ingest cron.
#
# Runs Mondays 04:00 PT via launchd com.steve.lacountyeats-weekly.plist on Mac
# Studio 2 (LOCAL job — touches local PG, then dumps + restores to Kamatera,
# then emails Steve via George localhost:9850).
#
# Steps:
#   1. Pull latest LA County DPH inventory CSV from ArcGIS
#   2. Capture pre-pull facility count
#   3. Run ingest-inventory.ts (idempotent UPSERT keyed on FACILITY_ID)
#   4. Diff: how many NEW facilities (first_seen_at > last week)?
#   5. Diff: how many UPDATED facilities (last_updated_at > last week)?
#   6. dump+restore facility tables to Kamatera
#   7. Trigger cuisine enrichment of any new facilities (background, ~1.2s each)
#   8. Email Steve with the week's diff via George
#   9. Standing-rule alert: if failure rate > 10%, mark email subject 'WARNING'
#
# Inspections + violations were removed from the public site 2026-05-08
# (Steve directive: "remove all the health code details, render + drop DB").
# scripts/ingest-inspections.ts is retained for archival but no longer fires.
#
# Logs go to ~/Projects/restaurant-directory/logs/weekly-YYYYMMDD.log
set -euo pipefail

cd /Users/macstudio3/Projects/restaurant-directory
mkdir -p logs
LOG="logs/weekly-$(date +%Y%m%d).log"
exec > >(tee -a "$LOG") 2>&1

echo "──────────────────────────────────────────────────────────────────"
echo "[weekly] starting at $(date -Iseconds)"
echo "──────────────────────────────────────────────────────────────────"

DB=restaurant_professional_directory
GEORGE_URL="${GEORGE_URL:-http://localhost:9850}"
GEORGE_AUTH="${GEORGE_AUTH:-admin:DWSecure2024!}"
STEVE_EMAIL="steveabramsdesigns@gmail.com"

# Snapshot pre-state
PRE_FACILITIES=$(psql -At -d $DB -c "SELECT COUNT(*) FROM facility")
WEEK_AGO=$(date -v-7d -Iseconds)
echo "[weekly] pre-pull: $PRE_FACILITIES facilities"

# Force a fresh CSV download by removing the cache (>24h check is in script)
rm -f data/la-eh-inventory.csv

# Run inventory ingest; capture exit code
INV_OK=true
./node_modules/.bin/tsx scripts/ingest-inventory.ts || INV_OK=false

POST_FACILITIES=$(psql -At -d $DB -c "SELECT COUNT(*) FROM facility")
NEW_FACILITIES=$(psql -At -d $DB -c "SELECT COUNT(*) FROM facility WHERE first_seen_at > '$WEEK_AGO'")
UPDATED=$(psql -At -d $DB -c "SELECT COUNT(*) FROM facility WHERE last_updated_at > '$WEEK_AGO' AND first_seen_at <= '$WEEK_AGO'")

echo "[weekly] post-pull: $POST_FACILITIES facilities · $NEW_FACILITIES NEW this week · $UPDATED UPDATED"

# Sync new/updated rows to Kamatera (full table dump+restore — ~30 MB, acceptable weekly)
echo "[weekly] syncing to Kamatera…"
SYNC_OK=true
pg_dump -d $DB --data-only --no-owner --disable-triggers \
  -t facility -t facility_enrichment -t ingest_run 2>&1 \
  | ssh root@45.61.58.125 "sudo -u postgres psql -d $DB -v ON_ERROR_STOP=1 -q -c 'TRUNCATE facility CASCADE;' && sudo -u postgres psql -d $DB -v ON_ERROR_STOP=1 -q" \
  || SYNC_OK=false

# Background-enrich any new facilities (no wait — cuisine job is long-running)
if [ "$NEW_FACILITIES" -gt 0 ] && [ "$INV_OK" = true ]; then
  echo "[weekly] backgrounding cuisine enrichment for new facilities…"
  nohup ./node_modules/.bin/tsx scripts/enrich-cuisine.ts > "logs/cuisine-after-weekly-$(date +%Y%m%d).log" 2>&1 &
fi

# Build the email body
SUBJECT="LA County Eats — weekly ingest: $NEW_FACILITIES new, $UPDATED updated"
WARN=""
if [ "$INV_OK" = false ] || [ "$SYNC_OK" = false ]; then
  SUBJECT="WARNING — $SUBJECT"
  WARN="<p style='color:#b94a48;'><strong>WARNING:</strong> ingest=$INV_OK / kamatera-sync=$SYNC_OK</p>"
fi

BODY="<h2>LA County Eats — weekly ingest</h2>
<p>$(date '+%A %B %-d, %Y at %H:%M %Z')</p>
$WARN
<table cellspacing='0' cellpadding='6' border='1' style='border-collapse:collapse;font-family:sans-serif;'>
  <tr><th align='left'>Pre-pull facilities</th><td>$PRE_FACILITIES</td></tr>
  <tr><th align='left'>Post-pull facilities</th><td>$POST_FACILITIES</td></tr>
  <tr><th align='left'>NEW this week</th><td><strong>$NEW_FACILITIES</strong></td></tr>
  <tr><th align='left'>UPDATED this week</th><td>$UPDATED</td></tr>
</table>
<p>Site: <a href='https://lacountyeats.com/'>lacountyeats.com</a></p>
<p style='color:#888;font-size:12px;'>Logged at $LOG</p>"

# Send via George (account=info per project_george_account_param.md)
curl -s -X POST "${GEORGE_URL}/api/send?account=info" \
  -H "Authorization: Basic $(printf '%s' "$GEORGE_AUTH" | base64)" \
  -H "Content-Type: application/json" \
  -d "$(jq -n --arg to "$STEVE_EMAIL" --arg subject "$SUBJECT" --arg body "$BODY" '{to:$to, subject:$subject, body:$body}')" \
  > /dev/null && echo "[weekly] email sent to $STEVE_EMAIL" || echo "[weekly] email FAILED"

echo "[weekly] DONE at $(date -Iseconds)"