`scripts/build-agent/nightly-email.sh`

71 lines
#!/bin/bash
# Lawyer Build Agent — nightly digest (8 PM PT) → steve@designerwallcoverings.com via George.
set -euo pipefail

PROJ="/Users/macstudio3/Projects/lawyer-directory-builder"
LOG="$PROJ/logs/build-agent/email.log"
SNAP="$PROJ/logs/build-agent/snapshot.json"
mkdir -p "$(dirname "$LOG")"
ts() { date -u +%Y-%m-%dT%H:%M:%SZ; }

[[ -f "$SNAP" ]] || { echo "[$(ts)] no snapshot — skipping email" >> "$LOG"; exit 0; }

PSQL="psql -d lawyer_professional_directory -tA"

# Stats from snapshot + DB
TOTAL=$($PSQL -c "SELECT COUNT(*) FROM site_mockups")
UNIQ=$($PSQL -c "SELECT COUNT(*) FROM site_mockups WHERE variant IN ('x1','x2','x3')")
COMPLETE=$($PSQL -c "SELECT COUNT(*) FROM (SELECT organization_id FROM site_mockups WHERE variant IN ('x1','x2','x3') GROUP BY organization_id HAVING COUNT(DISTINCT variant)=3) z")
LAST24=$($PSQL -c "SELECT COUNT(*) FROM site_mockups WHERE variant IN ('x1','x2','x3') AND generated_at > NOW() - INTERVAL '24 hours'")
LEADS_24=$($PSQL -c "SELECT COUNT(*) FROM leads WHERE source='pitch_page_reply' AND created_at > NOW() - INTERVAL '24 hours'")
PITCH_VIEWS_24=$($PSQL -c "SELECT COUNT(*) FROM pitch_views WHERE viewed_at > NOW() - INTERVAL '24 hours'")
RECENT_FIRMS=$($PSQL <<'SQL'
SELECT string_agg('• ' || o.name || ' (#' || o.id || ')', E'\n')
FROM (
  SELECT DISTINCT ON (organization_id) organization_id, generated_at
  FROM site_mockups
  WHERE variant IN ('x1','x2','x3')
    AND generated_at > NOW() - INTERVAL '24 hours'
  ORDER BY organization_id, generated_at DESC
  LIMIT 8
) m
JOIN organizations o ON o.id = m.organization_id
SQL
)

# HTML body
read -r -d '' BODY <<EOF
<html><body style="font-family:-apple-system,system-ui,sans-serif;color:#1a1814;max-width:640px">
<h2 style="font-family:Georgia,serif;font-weight:400;color:#a82a1f;border-bottom:1px solid #ddd;padding-bottom:8px">Lawyer Directory — overnight build digest</h2>
<table cellpadding="6" cellspacing="0" style="border-collapse:collapse;width:100%;font-size:14px">
  <tr><td style="color:#7a6e5a">Mockups in DB</td><td style="text-align:right"><b>$TOTAL</b></td></tr>
  <tr><td style="color:#7a6e5a">Unique (x1/x2/x3)</td><td style="text-align:right"><b>$UNIQ</b></td></tr>
  <tr><td style="color:#7a6e5a">Firms with all 3 unique variants</td><td style="text-align:right"><b>$COMPLETE</b></td></tr>
  <tr><td style="color:#7a6e5a">Generated in last 24h</td><td style="text-align:right"><b>$LAST24</b></td></tr>
  <tr style="border-top:1px solid #eee"><td style="color:#7a6e5a">Pitch-page views (24h)</td><td style="text-align:right"><b>$PITCH_VIEWS_24</b></td></tr>
  <tr><td style="color:#7a6e5a">Pitch-page replies (24h)</td><td style="text-align:right"><b>$LEADS_24</b></td></tr>
</table>
<h3 style="margin-top:24px;font-family:Georgia,serif;font-weight:400">Recent firms</h3>
<pre style="background:#f5f1e8;padding:12px;border-radius:4px;font-family:Menlo,Monaco,monospace;font-size:12px;white-space:pre-wrap">${RECENT_FIRMS:-(none)}</pre>
<p style="font-size:12px;color:#7a6e5a;margin-top:24px;border-top:1px solid #eee;padding-top:12px">
  Live gallery: <a href="http://192.168.1.53:9701/mockups">http://192.168.1.53:9701/mockups</a><br/>
  Per-firm pitch: <a href="http://192.168.1.53:9701/p/5">http://192.168.1.53:9701/p/&lt;firm_id&gt;</a><br/>
  Watchdog log: $PROJ/logs/build-agent/watchdog.log
</p>
</body></html>
EOF

# Subject (RFC 2047 encoded)
SUBJECT="=?UTF-8?B?$(printf '%s' "Lawyer Directory · $LAST24 mockups today · $COMPLETE firms complete" | base64)?="

# Send via George — credentials from env (audit 2026-05-04: removed inline literal)
: "${GEORGE_AUTH_USERNAME:=admin}"
: "${GEORGE_AUTH_PASSWORD:?GEORGE_AUTH_PASSWORD env var required}"
RESP=$(curl -s -u "${GEORGE_AUTH_USERNAME}:${GEORGE_AUTH_PASSWORD}" \
  -X POST "http://localhost:9850/api/send?account=info" \
  -H "Content-Type: application/json" \
  -d "$(jq -n --arg t "steve@designerwallcoverings.com" --arg s "$SUBJECT" --arg b "$BODY" \
        '{to: $t, subject: $s, body: $b, html: true}')" 2>&1 | tr -d '\n' | head -c 500)

echo "[$(ts)] sent: total=$TOTAL uniq=$UNIQ complete=$COMPLETE 24h=$LAST24 → $RESP" >> "$LOG"