← back to Lawyer Directory Builder
src/server/admin.ts
538 lines
/**
* Admin command center — Steve only (role='admin' OR tier='admin' on app_users).
*
* GET /admin — overview: users, ratings, threads, leads, mockups, pitches
* GET /admin/users — every user, tier filter, force-tier-change action
* GET /admin/ratings — every rating, hide/unhide
* GET /admin/threads — every thread + recent message preview
* GET /admin/leads — pitch-page replies
* GET /admin/pitches — every firm's /p/:id link with status
* POST /admin/users/:id/tier — force-tier-change (auth required)
* POST /admin/ratings/:id/hide — moderation (auth required)
*
* Tier check: requires req.user.role='admin' OR app_users.tier='admin'.
*/
import express from 'express';
import { query } from '../db/pool.ts';
import { refreshAggregate } from './community.ts';
const router = express.Router();
// Skip JSON parsing for /webhooks/* so Stripe raw-body handlers see a Buffer.
router.use((req, res, next) => {
if (req.path.startsWith('/webhooks/')) return next();
return express.json()(req, res, next);
});
async function isAdmin(req: express.Request): Promise<boolean> {
if (!req.user) return false;
if (req.user.role === 'admin') return true;
const r = await query<{ tier: string }>(`SELECT tier FROM app_users WHERE id = $1`, [req.user.id]);
return r.rows[0]?.tier === 'admin';
}
router.use('/admin', async (req, res, next) => {
if (!(await isAdmin(req))) {
return res.status(403).send(`<!doctype html><body style="font:14px system-ui;color:#f4f1ea;background:#0a0a0c;display:flex;align-items:center;justify-content:center;min-height:100vh">
<div style="text-align:center"><h2 style="font-family:Cormorant Garamond,Georgia,serif;color:#b89968">Admin only</h2>
<p>Log in as admin to see this. <a href="/login" style="color:#b89968">→ Login</a></p></div></body>`);
}
next();
});
const esc = (s: any) => String(s == null ? '' : s).replace(/[&<>"']/g, c => ({'&':'&','<':'<','>':'>','"':'"',"'":'''}[c]!));
const HEAD = `<!doctype html><html><head><meta charset="utf-8"><title>Admin · Lawyer Directory</title>
<link href="https://fonts.googleapis.com/css2?family=Cormorant+Garamond:wght@400;500&family=Inter:wght@300;400;500;600&display=swap" rel="stylesheet">
<style>
:root{--bg:#0a0a0c;--panel:#131316;--rule:#2a2724;--ink:#f4f1ea;--ink-mute:#8b857a;--metal:#b89968;--good:#10b981;--warn:#facc15;--bad:#ef4444}
*,*::before,*::after{box-sizing:border-box}
body{margin:0;font:14px/1.5 -apple-system,system-ui,sans-serif;background:var(--bg);color:var(--ink);-webkit-font-smoothing:antialiased}
header{padding:18px 28px;background:var(--panel);border-bottom:1px solid var(--rule);display:flex;align-items:baseline;gap:24px;flex-wrap:wrap}
h1{margin:0;font-family:Cormorant Garamond,Georgia,serif;font-weight:400;font-size:22px}
h1 .accent{color:var(--metal)}
nav{display:flex;gap:14px;color:var(--ink-mute);font-size:12px;letter-spacing:.1em;text-transform:uppercase;margin-left:auto}
nav a{color:var(--ink-mute);text-decoration:none}
nav a.active{color:var(--metal);border-bottom:1px solid var(--metal);padding-bottom:2px}
.wrap{padding:24px 28px;max-width:1280px;margin:0 auto}
.cards{display:grid;grid-template-columns:repeat(auto-fit,minmax(180px,1fr));gap:14px;margin-bottom:24px}
.card{background:var(--panel);border:1px solid var(--rule);border-radius:6px;padding:18px;display:flex;flex-direction:column;gap:4px}
.card .lbl{font-size:10px;letter-spacing:.18em;text-transform:uppercase;color:var(--ink-mute)}
.card .val{font-family:Cormorant Garamond,Georgia,serif;font-weight:400;font-size:32px;color:var(--metal)}
.card a{color:inherit;text-decoration:none}
table{width:100%;border-collapse:collapse;font-size:13px;margin-top:8px}
th{text-align:left;padding:10px 12px;background:var(--panel);border-bottom:1px solid var(--rule);font-weight:500;color:var(--ink-mute);text-transform:uppercase;font-size:11px;letter-spacing:.1em}
td{padding:8px 12px;border-bottom:1px solid var(--rule);vertical-align:top}
tr:hover td{background:var(--panel)}
.tag{display:inline-block;padding:1px 7px;border-radius:3px;background:#1c2237;color:var(--metal);font-size:11px}
.tag.client{background:#1c2237;color:var(--ink-mute)}
.tag.lawyer{background:#173b2a;color:var(--good)}
.tag.admin{background:#3b1515;color:var(--bad)}
button{background:var(--bg);border:1px solid var(--rule);color:var(--ink);padding:4px 10px;font:inherit;font-size:11px;border-radius:3px;cursor:pointer}
button:hover{border-color:var(--metal);color:var(--metal)}
.empty{padding:48px;text-align:center;color:var(--ink-mute)}
</style></head><body>`;
function frame(active: string, body: string) {
return HEAD + `
<header>
<h1>Admin <span class="accent">·</span> Lawyer Directory</h1>
<nav>
<a href="/admin" class="${active==='home'?'active':''}">Home</a>
<a href="/admin/users" class="${active==='users'?'active':''}">Users</a>
<a href="/admin/ratings" class="${active==='ratings'?'active':''}">Ratings</a>
<a href="/admin/threads" class="${active==='threads'?'active':''}">Threads</a>
<a href="/admin/leads" class="${active==='leads'?'active':''}">Leads</a>
<a href="/admin/pitches" class="${active==='pitches'?'active':''}">Pitches</a>
<a href="/admin/broadcast" class="${active==='broadcast'?'active':''}">Broadcast</a>
<a href="/admin/views" class="${active==='views'?'active':''}">Views</a>
<a href="/dashboard.html">↗ Dashboard</a>
</nav>
</header>
<div class="wrap">${body}</div>
</body></html>`;
}
router.get('/admin', async (_req, res) => {
const r = await query<{ k: string; v: string }>(`
SELECT 'users' AS k, COUNT(*)::text AS v FROM app_users
UNION ALL SELECT 'users_lawyer', COUNT(*)::text FROM app_users WHERE tier='lawyer'
UNION ALL SELECT 'users_paid', COUNT(*)::text FROM app_users WHERE paid_until > NOW()
UNION ALL SELECT 'ratings', COUNT(*)::text FROM ratings
UNION ALL SELECT 'ratings_user', COUNT(*)::text FROM ratings WHERE source='user'
UNION ALL SELECT 'threads', COUNT(*)::text FROM threads
UNION ALL SELECT 'messages', COUNT(*)::text FROM messages
UNION ALL SELECT 'leads', COUNT(*)::text FROM leads
UNION ALL SELECT 'mockups', COUNT(*)::text FROM site_mockups
UNION ALL SELECT 'mockups_llm', COUNT(*)::text FROM site_mockups WHERE variant='x'
UNION ALL SELECT 'firms', COUNT(*)::text FROM organizations WHERE type='law_firm'
UNION ALL SELECT 'attorneys', COUNT(*)::text FROM professionals
UNION ALL SELECT 'audits_low', COUNT(*)::text FROM site_audits WHERE marketing_score < 50
`);
const m: Record<string, number> = {};
for (const row of r.rows) m[row.k] = parseInt(row.v, 10);
const card = (lbl: string, val: number, href?: string) =>
`<div class="card"><div class="lbl">${esc(lbl)}</div><div class="val">${href?`<a href="${href}">`:''}${val.toLocaleString()}${href?'</a>':''}</div></div>`;
res.send(frame('home', `
<h2 style="font-family:Cormorant Garamond,Georgia,serif;font-weight:400;color:var(--metal);margin:0 0 16px">Overview</h2>
<div class="cards">
${card('Users', m.users, '/admin/users')}
${card('Lawyer tier', m.users_lawyer, '/admin/users')}
${card('Paid', m.users_paid)}
${card('Ratings', m.ratings, '/admin/ratings')}
${card('User reviews', m.ratings_user)}
${card('Threads', m.threads, '/admin/threads')}
${card('Messages', m.messages)}
${card('Leads', m.leads, '/admin/leads')}
${card('Mockups', m.mockups, '/mockups')}
${card('LLM mockups', m.mockups_llm, '/mockups?variant=x')}
${card('Low-audit firms', m.audits_low)}
</div>
<div style="margin-top:24px;color:var(--ink-mute);font-size:12px">
Live: <a href="/dashboard.html" style="color:var(--metal)">Dashboard</a> ·
<a href="/community" style="color:var(--metal)">Community</a> ·
<a href="/mockups" style="color:var(--metal)">Mockup gallery</a> ·
<a href="/qa-shots/" style="color:var(--metal)">QA shots</a>
</div>
`));
});
router.get('/admin/users', async (_req, res) => {
const r = await query<any>(`
SELECT u.id, u.email, u.full_name, u.tier, u.role, u.plan, u.bar_number, u.bar_verified_at,
u.claimed_professional_id, u.paid_until, u.created_at, u.last_login_at,
p.full_name AS pro_name
FROM app_users u
LEFT JOIN professionals p ON p.id = u.claimed_professional_id
ORDER BY u.created_at DESC LIMIT 200
`);
const rows = r.rows.map(u => `<tr>
<td>${u.id}</td>
<td>${esc(u.email)}</td>
<td>${esc(u.full_name||'')}</td>
<td><span class="tag ${esc(u.tier||'client')}">${esc(u.tier||'?')}</span></td>
<td>${esc(u.bar_number||'')} ${u.pro_name?`<a href="/attorneys/${u.claimed_professional_id}" style="color:var(--metal)">${esc(u.pro_name)}</a>`:''}</td>
<td>${u.paid_until?esc(String(u.paid_until).slice(0,10)):'—'}</td>
<td>${esc(String(u.created_at).slice(0,10))}</td>
<td>
<button onclick="setTier(${u.id},'client')">→ client</button>
<button onclick="setTier(${u.id},'lawyer')">→ lawyer</button>
<button onclick="setTier(${u.id},'admin')">→ admin</button>
</td>
</tr>`).join('');
res.send(frame('users', `
<h2 style="font-family:Cormorant Garamond,Georgia,serif;font-weight:400;color:var(--metal);margin:0 0 16px">Users (${r.rowCount})</h2>
<table>
<thead><tr><th>ID</th><th>Email</th><th>Name</th><th>Tier</th><th>Bar / Claim</th><th>Paid until</th><th>Joined</th><th>Actions</th></tr></thead>
<tbody>${rows || '<tr><td colspan="8" class="empty">No users yet — sign up at /signup</td></tr>'}</tbody>
</table>
<script>
async function setTier(id, tier) {
const r = await fetch('/admin/users/'+id+'/tier', { method:'POST', headers:{'Content-Type':'application/json'}, body: JSON.stringify({tier}) }).then(r=>r.json());
if (r.ok) location.reload(); else alert(JSON.stringify(r));
}
</script>
`));
});
router.post('/admin/users/:id/tier', async (req, res) => {
const id = parseInt(req.params.id, 10);
const tier = String(req.body?.tier || '');
if (!['client','lawyer','admin','guest'].includes(tier)) return res.status(400).json({ error: 'bad_tier' });
await query(`UPDATE app_users SET tier = $1 WHERE id = $2`, [tier, id]);
res.json({ ok: true, id, tier });
});
router.get('/admin/ratings', async (_req, res) => {
const r = await query<any>(`
SELECT r.id, r.source, r.stars, r.service_score, r.price_score, r.quality_score, r.comment, r.posted_at, r.hidden_at,
o.id AS org_id, o.name AS firm_name, p.id AS pro_id, p.full_name AS pro_name,
u.email AS reviewer_email
FROM ratings r
LEFT JOIN organizations o ON o.id = r.organization_id
LEFT JOIN professionals p ON p.id = r.professional_id
LEFT JOIN app_users u ON u.id = r.reviewer_user_id
ORDER BY r.posted_at DESC LIMIT 200
`);
const rows = r.rows.map(rt => `<tr>
<td>${rt.id}</td>
<td><span class="tag">${esc(rt.source)}</span></td>
<td style="color:${Number(rt.stars)>=4?'var(--good)':Number(rt.stars)>=3?'var(--warn)':'var(--bad)'}">★ ${Number(rt.stars).toFixed(1)}</td>
<td>${rt.firm_name?`<a href="/firms/${rt.org_id}" style="color:var(--metal)">${esc(rt.firm_name)}</a>`:rt.pro_name?`<a href="/attorneys/${rt.pro_id}" style="color:var(--metal)">${esc(rt.pro_name)}</a>`:'—'}</td>
<td>${esc(rt.reviewer_email||'')}</td>
<td style="max-width:380px;overflow:hidden;text-overflow:ellipsis">${esc((rt.comment||'').slice(0,100))}</td>
<td>${esc(String(rt.posted_at).slice(0,10))}</td>
<td>${rt.hidden_at?`<span style="color:var(--bad)">hidden</span>`:`<button onclick="hide(${rt.id})">hide</button>`}</td>
</tr>`).join('');
res.send(frame('ratings', `
<h2 style="font-family:Cormorant Garamond,Georgia,serif;font-weight:400;color:var(--metal);margin:0 0 16px">Ratings (${r.rowCount})</h2>
<table>
<thead><tr><th>ID</th><th>Source</th><th>Stars</th><th>Target</th><th>Reviewer</th><th>Comment</th><th>Posted</th><th>Action</th></tr></thead>
<tbody>${rows || '<tr><td colspan="8" class="empty">No ratings yet — submit at /firms/:id</td></tr>'}</tbody>
</table>
<script>
async function hide(id) {
const r = await fetch('/admin/ratings/'+id+'/hide', { method:'POST' }).then(r=>r.json());
if (r.ok) location.reload(); else alert(JSON.stringify(r));
}
</script>
`));
});
router.post('/admin/ratings/:id/hide', async (req, res) => {
const id = parseInt(req.params.id, 10);
// Look up the target so we can refresh its cached aggregate after hiding.
const t = await query<{ professional_id: number | null; organization_id: number | null }>(
`SELECT professional_id, organization_id FROM ratings WHERE id = $1`, [id]);
await query(`UPDATE ratings SET hidden_at = NOW(), hidden_reason = 'admin_hide' WHERE id = $1`, [id]);
const row = t.rows[0];
if (row?.professional_id) await refreshAggregate('professional', row.professional_id);
else if (row?.organization_id) await refreshAggregate('organization', row.organization_id);
res.json({ ok: true });
});
router.get('/admin/threads', async (_req, res) => {
const r = await query<any>(`
SELECT t.id, t.channel, t.topic, t.message_count, t.last_message_at, t.created_at,
o.name AS firm_name, t.organization_id,
(SELECT body FROM messages WHERE thread_id=t.id ORDER BY posted_at DESC LIMIT 1) AS last_msg
FROM threads t LEFT JOIN organizations o ON o.id = t.organization_id
ORDER BY t.last_message_at DESC LIMIT 200
`);
const rows = r.rows.map(t => `<tr>
<td>${t.id}</td>
<td><span class="tag">${esc(t.channel)}</span></td>
<td>${esc(t.topic||'(none)')}</td>
<td>${t.message_count}</td>
<td style="max-width:300px;overflow:hidden;text-overflow:ellipsis;color:var(--ink-mute)">${esc((t.last_msg||'').slice(0,80))}</td>
<td>${t.firm_name?`<a href="/firms/${t.organization_id}" style="color:var(--metal)">${esc(t.firm_name)}</a>`:''}</td>
<td>${esc(String(t.last_message_at).slice(0,16))}</td>
</tr>`).join('');
res.send(frame('threads', `
<h2 style="font-family:Cormorant Garamond,Georgia,serif;font-weight:400;color:var(--metal);margin:0 0 16px">Threads (${r.rowCount})</h2>
<table>
<thead><tr><th>ID</th><th>Channel</th><th>Topic</th><th>Msgs</th><th>Last</th><th>Firm</th><th>Last activity</th></tr></thead>
<tbody>${rows || '<tr><td colspan="7" class="empty">No threads yet — start one at /community</td></tr>'}</tbody>
</table>
`));
});
router.get('/admin/leads', async (_req, res) => {
const r = await query<any>(`
SELECT l.id, l.organization_id, l.source, l.contact_name, l.contact_email, l.message, l.received_at, l.status,
o.name AS firm_name
FROM leads l LEFT JOIN organizations o ON o.id = l.organization_id
ORDER BY l.received_at DESC LIMIT 200
`);
const rows = r.rows.map(l => `<tr>
<td>${l.id}</td>
<td><span class="tag">${esc(l.source||'')}</span></td>
<td>${l.firm_name?`<a href="/firms/${l.organization_id}" style="color:var(--metal)">${esc(l.firm_name)}</a>`:''}</td>
<td>${esc(l.contact_name||'')}<br><small style="color:var(--ink-mute)">${esc(l.contact_email||'')}</small></td>
<td style="max-width:380px;overflow:hidden;text-overflow:ellipsis">${esc((l.message||'').slice(0,200))}</td>
<td>${esc(String(l.received_at).slice(0,16))}</td>
<td><span class="tag">${esc(l.status||'')}</span></td>
</tr>`).join('');
res.send(frame('leads', `
<h2 style="font-family:Cormorant Garamond,Georgia,serif;font-weight:400;color:var(--metal);margin:0 0 16px">Leads (${r.rowCount})</h2>
<table>
<thead><tr><th>ID</th><th>Source</th><th>Firm</th><th>Contact</th><th>Message</th><th>Received</th><th>Status</th></tr></thead>
<tbody>${rows || '<tr><td colspan="7" class="empty">No leads yet — replies via /p/:firm_id come here</td></tr>'}</tbody>
</table>
`));
});
router.get('/admin/pitches', async (req, res) => {
const filter = String(req.query.filter || 'low_score');
let where: string;
if (filter === 'low_score') {
where = `EXISTS (SELECT 1 FROM site_audits a WHERE a.organization_id=o.id AND a.marketing_score IS NOT NULL AND a.marketing_score < 50)`;
} else if (filter === 'has_mockup_x') {
where = `EXISTS (SELECT 1 FROM site_mockups m WHERE m.organization_id=o.id AND m.variant='x')`;
} else if (filter === 'has_phone') {
where = `o.phone IS NOT NULL`;
} else {
where = `o.website IS NOT NULL`;
}
const r = await query<any>(`
SELECT o.id, o.name, o.website, o.phone, o.city,
(SELECT marketing_score FROM site_audits a WHERE a.organization_id=o.id AND a.status_code BETWEEN 200 AND 399 ORDER BY a.audited_at DESC LIMIT 1) AS audit_score,
(SELECT COUNT(*) FROM site_mockups m WHERE m.organization_id=o.id) AS mock_count,
(SELECT COUNT(*) FROM emails e WHERE e.organization_id=o.id) AS email_count
FROM organizations o
WHERE o.type='law_firm' AND ${where}
ORDER BY (SELECT marketing_score FROM site_audits a WHERE a.organization_id=o.id AND a.status_code BETWEEN 200 AND 399 ORDER BY a.audited_at DESC LIMIT 1) ASC NULLS LAST
LIMIT 200
`);
const rows = r.rows.map(f => {
const sc = f.audit_score == null ? null : Number(f.audit_score);
const c = sc == null ? 'var(--ink-mute)' : sc >= 70 ? 'var(--good)' : sc >= 50 ? 'var(--warn)' : 'var(--bad)';
return `<tr>
<td>${f.id}</td>
<td><a href="/firms/${f.id}" style="color:var(--metal)" target="_blank">${esc(f.name)}</a></td>
<td>${esc(f.city||'')}</td>
<td style="color:${c};font-weight:600">${sc==null?'—':sc}</td>
<td>${f.mock_count}</td>
<td>${f.email_count}</td>
<td>${esc(f.phone||'')}</td>
<td><a href="/p/${f.id}" target="_blank" style="color:var(--metal)">→ /p/${f.id}</a></td>
</tr>`;
}).join('');
res.send(frame('pitches', `
<h2 style="font-family:Cormorant Garamond,Georgia,serif;font-weight:400;color:var(--metal);margin:0 0 16px">Pitches (${r.rowCount})</h2>
<div style="margin-bottom:12px;color:var(--ink-mute);font-size:12px">
Filter:
<a href="?filter=low_score" style="color:${filter==='low_score'?'var(--metal)':'var(--ink-mute)'}">Low audit score</a> ·
<a href="?filter=has_mockup_x" style="color:${filter==='has_mockup_x'?'var(--metal)':'var(--ink-mute)'}">Has LLM mockup</a> ·
<a href="?filter=has_phone" style="color:${filter==='has_phone'?'var(--metal)':'var(--ink-mute)'}">Has phone</a> ·
<a href="?filter=all" style="color:${filter==='all'?'var(--metal)':'var(--ink-mute)'}">All with site</a>
</div>
<table>
<thead><tr><th>ID</th><th>Firm</th><th>City</th><th>Audit</th><th>Mocks</th><th>Emails</th><th>Phone</th><th>Pitch URL</th></tr></thead>
<tbody>${rows}</tbody>
</table>
`));
});
// ─── /admin/views — pitch-page open tracking ──────────────────────────────
router.get('/admin/views', async (_req, res) => {
const totals = await query<{ k: string; v: string }>(`
SELECT 'total' AS k, COUNT(*)::text AS v FROM pitch_views
UNION ALL SELECT 'unique_firms', COUNT(DISTINCT organization_id)::text FROM pitch_views
UNION ALL SELECT 'last_24h', COUNT(*)::text FROM pitch_views WHERE viewed_at > NOW() - INTERVAL '24 hours'
UNION ALL SELECT 'with_utm', COUNT(*)::text FROM pitch_views WHERE utm_source IS NOT NULL
`);
const t: Record<string, number> = {};
for (const row of totals.rows) t[row.k] = parseInt(row.v, 10);
const top = await query<any>(`
SELECT v.organization_id AS org_id, o.name AS firm_name,
COUNT(*)::int AS views,
MAX(v.viewed_at) AS last_view,
ARRAY_AGG(DISTINCT v.utm_source) FILTER (WHERE v.utm_source IS NOT NULL) AS sources
FROM pitch_views v JOIN organizations o ON o.id = v.organization_id
GROUP BY v.organization_id, o.name
ORDER BY views DESC, last_view DESC
LIMIT 100
`);
const recent = await query<any>(`
SELECT v.id, v.organization_id, o.name AS firm_name, v.visitor_ip, v.referrer, v.utm_source, v.viewed_at
FROM pitch_views v JOIN organizations o ON o.id = v.organization_id
ORDER BY v.viewed_at DESC LIMIT 50
`);
const card = (lbl: string, val: number) =>
`<div class="card"><div class="lbl">${esc(lbl)}</div><div class="val">${val.toLocaleString()}</div></div>`;
const topRows = top.rows.map(r => `<tr>
<td><a href="/firms/${r.org_id}" style="color:var(--metal)" target="_blank">${esc(r.firm_name)}</a></td>
<td>${r.views}</td>
<td>${esc(String(r.last_view).slice(0,16))}</td>
<td>${(r.sources || []).map((s: string) => `<span class="tag">${esc(s)}</span>`).join(' ')}</td>
<td><a href="/p/${r.org_id}" target="_blank" style="color:var(--metal)">→ /p/${r.org_id}</a></td>
</tr>`).join('');
const recentRows = recent.rows.map(r => `<tr>
<td>${esc(String(r.viewed_at).slice(0,19))}</td>
<td><a href="/p/${r.organization_id}" style="color:var(--metal)">${esc(r.firm_name)}</a></td>
<td><span class="tag">${esc(r.utm_source||'organic')}</span></td>
<td style="max-width:240px;overflow:hidden;text-overflow:ellipsis;color:var(--ink-mute)">${esc(r.referrer||'')}</td>
<td style="color:var(--ink-mute);font-family:monospace;font-size:11px">${esc(r.visitor_ip||'')}</td>
</tr>`).join('');
res.send(frame('views', `
<h2 style="font-family:Cormorant Garamond,Georgia,serif;font-weight:400;color:var(--metal);margin:0 0 16px">Pitch-page views</h2>
<div class="cards">
${card('Total views', t.total||0)}
${card('Unique firms', t.unique_firms||0)}
${card('Last 24h', t.last_24h||0)}
${card('With UTM', t.with_utm||0)}
</div>
<h3 style="font-family:Cormorant Garamond,Georgia,serif;font-weight:400;color:var(--metal);margin:24px 0 8px;font-size:18px">Top firms by views</h3>
<table>
<thead><tr><th>Firm</th><th>Views</th><th>Last view</th><th>UTM sources</th><th>Pitch URL</th></tr></thead>
<tbody>${topRows || '<tr><td colspan="5" class="empty">No views yet — share a /p/:id URL.</td></tr>'}</tbody>
</table>
<h3 style="font-family:Cormorant Garamond,Georgia,serif;font-weight:400;color:var(--metal);margin:24px 0 8px;font-size:18px">Recent views</h3>
<table>
<thead><tr><th>When</th><th>Firm</th><th>UTM</th><th>Referrer</th><th>IP</th></tr></thead>
<tbody>${recentRows || '<tr><td colspan="5" class="empty">—</td></tr>'}</tbody>
</table>
`));
});
// ─── /admin/broadcast — bulk pitch composer ────────────────────────────────
router.get('/admin/broadcast', async (req, res) => {
const filter = String(req.query.filter || 'low_score_with_email');
const limit = Math.min(parseInt((req.query.limit as string) || '100', 10), 1000);
const PUBLIC = process.env.PUBLIC_BASE_URL || 'http://localhost:9701';
let where: string;
if (filter === 'low_score_with_email') {
where = `EXISTS (SELECT 1 FROM site_audits a WHERE a.organization_id=o.id AND a.marketing_score IS NOT NULL AND a.marketing_score < 50)
AND EXISTS (SELECT 1 FROM emails e WHERE e.organization_id=o.id)`;
} else if (filter === 'has_mockup_with_email') {
where = `EXISTS (SELECT 1 FROM site_mockups m WHERE m.organization_id=o.id AND m.variant='x')
AND EXISTS (SELECT 1 FROM emails e WHERE e.organization_id=o.id)`;
} else if (filter === 'no_audit_no_email') {
where = `o.website IS NULL`;
} else {
where = `EXISTS (SELECT 1 FROM emails e WHERE e.organization_id=o.id)`;
}
const r = await query<any>(`
SELECT o.id, o.name, o.city, o.website,
(SELECT marketing_score FROM site_audits a WHERE a.organization_id=o.id AND a.status_code BETWEEN 200 AND 399 ORDER BY a.audited_at DESC LIMIT 1) AS audit_score,
(SELECT COUNT(*)::int FROM site_mockups m WHERE m.organization_id=o.id AND m.variant='x') AS has_x,
(SELECT email FROM emails e WHERE e.organization_id=o.id ORDER BY id ASC LIMIT 1) AS first_email,
(SELECT COUNT(*)::int FROM emails e WHERE e.organization_id=o.id) AS email_count
FROM organizations o
WHERE o.type='law_firm' AND ${where}
ORDER BY (SELECT marketing_score FROM site_audits a WHERE a.organization_id=o.id AND a.status_code BETWEEN 200 AND 399 ORDER BY a.audited_at DESC LIMIT 1) ASC NULLS LAST
LIMIT $1
`, [limit]);
const rows = r.rows.map(f => {
const sc = f.audit_score == null ? null : Number(f.audit_score);
const subj = `For ${f.name} — your website, side by side with what it could look like`;
const body =
`Hi,
I came across ${f.name}'s website${f.website ? ` (${f.website.replace(/^https?:\/\//,'')})` : ''} and built a redesign concept.
No call, no slide deck, no pitch. Just look:
${PUBLIC}/p/${f.id}
Your current site is on the left, the redesign on the right. If it's interesting, the page has a reply box that comes straight to me. If not, ignore this — no follow-ups.
— Steve Abrams
Entrepreneur, designer, small-business owner
hello@agentabrams.com`;
const mailto = `mailto:${f.first_email}?subject=${encodeURIComponent(subj)}&body=${encodeURIComponent(body)}`;
return `<tr>
<td>${f.id}</td>
<td><a href="/firms/${f.id}" style="color:var(--metal)" target="_blank">${esc(f.name)}</a></td>
<td>${esc(f.city||'')}</td>
<td style="color:${sc==null?'var(--ink-mute)':sc>=70?'var(--good)':sc>=50?'var(--warn)':'var(--bad)'};font-weight:600">${sc==null?'—':sc}</td>
<td>${f.has_x ? '<span style="color:var(--good)">✓</span>' : '—'}</td>
<td>${esc(f.first_email||'')}${f.email_count > 1 ? ` <span style="color:var(--ink-mute)">+${f.email_count - 1}</span>` : ''}</td>
<td>
<a href="${esc(mailto)}" target="_blank" style="color:var(--metal);text-decoration:none">✉ open</a> ·
<a href="/p/${f.id}" target="_blank" style="color:var(--metal);text-decoration:none">→ /p/${f.id}</a>
</td>
</tr>`;
}).join('');
// CSV button → calls /admin/broadcast.csv with the same filter
res.send(frame('broadcast', `
<h2 style="font-family:Cormorant Garamond,Georgia,serif;font-weight:400;color:var(--metal);margin:0 0 8px">Broadcast composer</h2>
<p style="color:var(--ink-mute);margin:0 0 16px;font-size:13px">
Generate per-firm pitch emails. Clicking <b>✉ open</b> opens your default mail client with the body prefilled.
<a href="/admin/broadcast.csv?filter=${esc(filter)}" style="color:var(--metal);margin-left:14px">⬇ Download CSV</a>
</p>
<div style="margin-bottom:16px;color:var(--ink-mute);font-size:12px">
Filter:
<a href="?filter=low_score_with_email" style="color:${filter==='low_score_with_email'?'var(--metal)':'var(--ink-mute)'}">Low audit + has email</a> ·
<a href="?filter=has_mockup_with_email" style="color:${filter==='has_mockup_with_email'?'var(--metal)':'var(--ink-mute)'}">Has LLM mockup + email</a> ·
<a href="?filter=any_with_email" style="color:${filter==='any_with_email'?'var(--metal)':'var(--ink-mute)'}">Any with email</a>
</div>
<table>
<thead><tr><th>ID</th><th>Firm</th><th>City</th><th>Audit</th><th>LLM mock</th><th>Email</th><th>Actions</th></tr></thead>
<tbody>${rows || '<tr><td colspan="7" class="empty">No matches.</td></tr>'}</tbody>
</table>
<p style="color:var(--ink-mute);font-size:12px;margin-top:18px">
Showing ${r.rowCount} of up to ${limit}.
Slice-B: bulk-send via PurelyMail SMTP coming once that password is wired into secrets.
</p>
`));
});
router.get('/admin/broadcast.csv', async (req, res) => {
const filter = String(req.query.filter || 'low_score_with_email');
const PUBLIC = process.env.PUBLIC_BASE_URL || 'http://localhost:9701';
let where: string;
if (filter === 'low_score_with_email') {
where = `EXISTS (SELECT 1 FROM site_audits a WHERE a.organization_id=o.id AND a.marketing_score IS NOT NULL AND a.marketing_score < 50)
AND EXISTS (SELECT 1 FROM emails e WHERE e.organization_id=o.id)`;
} else if (filter === 'has_mockup_with_email') {
where = `EXISTS (SELECT 1 FROM site_mockups m WHERE m.organization_id=o.id AND m.variant='x')
AND EXISTS (SELECT 1 FROM emails e WHERE e.organization_id=o.id)`;
} else {
where = `EXISTS (SELECT 1 FROM emails e WHERE e.organization_id=o.id)`;
}
const r = await query<any>(`
SELECT o.id, o.name, o.city, o.website,
(SELECT marketing_score FROM site_audits a WHERE a.organization_id=o.id AND a.status_code BETWEEN 200 AND 399 ORDER BY a.audited_at DESC LIMIT 1) AS audit_score,
(SELECT email FROM emails e WHERE e.organization_id=o.id ORDER BY id ASC LIMIT 1) AS first_email
FROM organizations o
WHERE o.type='law_firm' AND ${where}
ORDER BY (SELECT marketing_score FROM site_audits a WHERE a.organization_id=o.id AND a.status_code BETWEEN 200 AND 399 ORDER BY a.audited_at DESC LIMIT 1) ASC NULLS LAST
LIMIT 5000
`);
const csvEsc = (s: any) => {
const v = String(s == null ? '' : s);
return /[",\n]/.test(v) ? `"${v.replace(/"/g, '""')}"` : v;
};
const subjFor = (f: any) => `For ${f.name} — your website, side by side with what it could look like`;
const bodyFor = (f: any) => `Hi,\n\nI came across ${f.name}'s website${f.website ? ` (${f.website.replace(/^https?:\/\//,'')})` : ''} and built a redesign concept.\n\nNo call, no slide deck, no pitch. Just look:\n${PUBLIC}/p/${f.id}\n\nYour current site is on the left, the redesign on the right. If it's interesting, the page has a reply box that comes straight to me. If not, ignore this — no follow-ups.\n\n— Steve Abrams\n Entrepreneur, designer, small-business owner\n hello@agentabrams.com`;
const lines = ['firm_id,firm_name,city,recipient_email,audit_score,pitch_url,subject,body_text'];
for (const f of r.rows) {
lines.push([
f.id,
csvEsc(f.name),
csvEsc(f.city || ''),
csvEsc(f.first_email),
f.audit_score == null ? '' : f.audit_score,
`${PUBLIC}/p/${f.id}`,
csvEsc(subjFor(f)),
csvEsc(bodyFor(f)),
].join(','));
}
res.setHeader('Content-Type', 'text/csv; charset=utf-8');
res.setHeader('Content-Disposition', `attachment; filename="lawyer-pitch-batch-${filter}-${new Date().toISOString().slice(0,10)}.csv"`);
res.send(lines.join('\n'));
});
export default router;