← back to Rentv Adintel
src/routes/pages.js
1554 lines
'use strict';
/**
* RENTV Advertiser Intelligence — page router (spec §24, §25, §26, §27).
* Server-rendered HTML strings. Direct DB reads for first paint.
* Every page degrades gracefully when tables are empty (DEMO DATA badge).
*/
const express = require('express');
const { query } = require('../../db');
const T = require('../../lib/types');
const scoring = require('../../lib/scoring');
const router = express.Router();
// ── Formatting helpers ────────────────────────────────────────────────────────
const esc = (s) => (s == null ? '' : String(s))
.replace(/&/g, '&').replace(/</g, '<').replace(/>/g, '>').replace(/"/g, '"');
const fmtDate = (s) => {
if (!s) return '—';
try {
const d = new Date(s);
if (isNaN(d)) return String(s);
return d.toLocaleString('en-US', { year: 'numeric', month: 'short', day: 'numeric', hour: 'numeric', minute: '2-digit' });
} catch (_) { return String(s); }
};
const fmtDateOnly = (s) => {
if (!s) return '—';
try { return new Date(s).toLocaleDateString('en-US', { year: 'numeric', month: 'short', day: 'numeric' }); }
catch (_) { return String(s); }
};
const STATUS_BADGE_CLASS = {
VERIFIED_ADVERTISER: 'badge-verified-advertiser',
VERIFIED_CONFERENCE_SPONSOR: 'badge-verified-sponsor',
VERIFIED_EXHIBITOR: 'badge-verified-exhibitor',
VERIFIED_MEDIA_PARTNER: 'badge-verified-media',
VERIFIED_CONTENT_PARTNER: 'badge-content-partner',
SPEAKER_OR_PANELIST_ONLY: 'badge-panelist',
PAST_ADVERTISER: 'badge-past-advertiser',
LIKELY_PROSPECT: 'badge-likely-prospect',
RESEARCH_NEEDED: 'badge-research-needed',
DISQUALIFIED: 'badge-disqualified',
};
function statusBadge(status) {
const cls = STATUS_BADGE_CLASS[status] || 'badge-research-needed';
const lbl = T.STATUS_LABELS[status] || status || '—';
return `<span class="badge ${cls}">${esc(lbl)}</span>`;
}
function scoreBadge(score) {
if (score == null) return '<span class="miss">—</span>';
const n = Number(score);
const cls = n >= 70 ? 'score-high' : n >= 40 ? 'score-mid' : 'score-low';
return `<span class="score-badge ${cls}">${n}</span>`;
}
function whenChip(ts) {
if (!ts) return '';
return `<span class="when-chip" title="${esc(new Date(ts).toISOString())}">Created ${esc(fmtDate(ts))}</span>`;
}
// ── Safe DB query (returns [] on missing table) ──────────────────────────────
async function safeQ(sql, params) {
try { return (await query(sql, params || [])).rows; }
catch (e) { if (e.code === '42P01') return []; throw e; }
}
// ── Shared HTML layout ────────────────────────────────────────────────────────
function layout(title, bodyHtml, opts) {
opts = opts || {};
const market = opts.market || 'ALL';
const verified = opts.verified ? '1' : '0';
return `<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>${esc(title)} — RENTV Advertiser Intelligence</title>
<link rel="stylesheet" href="/css/app.css">
</head>
<body>
<header class="site-header" role="banner">
<a class="brand" href="/" aria-label="RENTV Advertiser Intelligence home">
<span>RENTV</span> Advertiser Intel
</a>
<input
id="global-search"
class="header-search"
type="search"
placeholder="Search advertisers, contacts, conferences…"
aria-label="Search all advertisers and contacts"
value="${esc(opts.q || '')}">
<div class="switch-group" role="group" aria-label="Market filter">
<div class="market-switch">
<button data-market="ALL" class="${market === 'ALL' ? 'active' : ''}" aria-pressed="${market === 'ALL'}">All</button>
<button data-market="CA" class="${market === 'CA' ? 'active' : ''}" aria-pressed="${market === 'CA'}">California</button>
<button data-market="AZ" class="${market === 'AZ' ? 'active' : ''}" aria-pressed="${market === 'AZ'}">Arizona</button>
</div>
<label class="verified-switch ${verified === '1' ? 'active' : ''}" aria-label="Show verified only">
<input type="checkbox" ${verified === '1' ? 'checked' : ''} aria-checked="${verified === '1'}">
Verified Only
</label>
</div>
<div class="header-spacer"></div>
<button id="download-everything-btn" class="dl-btn" aria-label="Download everything as ZIP">
Download Everything
</button>
<span id="export-msg" style="display:none;font-size:14px;color:var(--warn);margin-left:8px"></span>
</header>
<nav aria-label="Main navigation" style="background:var(--surface);border-bottom:1px solid var(--border);padding:0 24px;display:flex;gap:0;overflow-x:auto">
${[
['/', 'Dashboard'],
['/advertisers', 'Advertisers'],
['/ads', 'Ads Gallery'],
['/conferences', 'Conferences'],
['/contacts', 'Contacts'],
['/prospects', 'Prospects'],
['/analytics', 'Analytics'],
['/search-intelligence', 'Search Intelligence'],
['/media-kit', 'Media Kit'],
['/sources', 'Sources'],
['/imports', 'Imports'],
['/review', 'Review'],
['/exports', 'Exports'],
['/settings', 'Settings'],
].map(([href, label]) => {
const active = opts.activeNav === href;
return `<a href="${esc(href)}" style="display:inline-flex;align-items:center;padding:10px 14px;font-size:15px;font-weight:${active ? '700' : '500'};color:${active ? 'var(--accent)' : 'var(--ink-muted)'};text-decoration:none;border-bottom:3px solid ${active ? 'var(--accent)' : 'transparent'};white-space:nowrap;min-height:44px" ${active ? 'aria-current="page"' : ''}>${esc(label)}</a>`;
}).join('')}
</nav>
<main id="main-content" role="main">
${bodyHtml}
</main>
<!-- Score drawer -->
<div id="score-drawer-overlay" class="drawer-overlay" role="dialog" aria-modal="true" aria-label="Show Me Why score drawer">
<div class="score-drawer" style="position:relative">
<button onclick="closeScoreDrawer()" class="btn btn-ghost" style="position:absolute;top:16px;right:16px" aria-label="Close score drawer">Close ✕</button>
<div id="score-drawer-body"></div>
</div>
</div>
<script src="/js/table.js"></script>
<script src="/js/app.js"></script>
</body>
</html>`;
}
// ── Demo banner helper ────────────────────────────────────────────────────────
const demoBanner = `<div class="demo-banner" role="status" aria-live="polite">
DEMO DATA — No live data imported yet. Run <code>npm run db:migrate && npm run db:seed</code> then connect GA4/GSC via <a href="/settings">Settings</a>.
</div>`;
// ── Page helpers ─────────────────────────────────────────────────────────────
function pageShell(leftRailHtml, contentHtml) {
return `<div class="page-shell">
<aside class="left-rail" aria-label="Filters and column controls">${leftRailHtml}</aside>
<div class="page-content">${contentHtml}</div>
</div>`;
}
function standardRail(extraHtml) {
return `
<div class="rail-section">
<h4>Column controls</h4>
<div class="field-toggles" id="field-toggles"></div>
<div style="margin-top:8px">
<button class="mini-btn" id="cols-all">All</button>
<button class="mini-btn" id="cols-none">None</button>
<button class="mini-btn" id="cols-reset" title="Reset to defaults">↻ Reset</button>
</div>
<p style="font-size:13px;color:var(--ink-faint);margin-top:8px;line-height:1.4">
Drag column header to reorder · click to sort · toggle above to show/hide.
</p>
</div>
${extraHtml || ''}`;
}
// ═══════════════════════════════════════════════════════════════════════════════
// GET / — Dashboard (§26)
// ═══════════════════════════════════════════════════════════════════════════════
router.get('/', async (req, res) => {
try {
const [summary, recentOrgs, upcomingEvents, audiences] = await Promise.all([
safeQ(`SELECT
(SELECT COUNT(*) FROM organizations) AS total_orgs,
(SELECT COUNT(*) FROM ad_sightings WHERE relationship_status = 'VERIFIED_ADVERTISER') AS verified_advertisers,
(SELECT COUNT(*) FROM ad_sightings WHERE relationship_status = 'VERIFIED_CONFERENCE_SPONSOR') AS verified_sponsors,
(SELECT COUNT(*) FROM organizations WHERE headquarters_state = 'CA') AS california_orgs,
(SELECT COUNT(*) FROM organizations WHERE headquarters_state = 'AZ') AS arizona_orgs,
(SELECT COUNT(*) FROM ad_sightings WHERE observed_at >= NOW() - INTERVAL '30 days') AS sightings_30d,
(SELECT COUNT(*) FROM ad_sightings WHERE observed_at >= NOW() - INTERVAL '7 days') AS sightings_7d,
(SELECT COUNT(*) FROM events) AS total_events,
(SELECT COUNT(*) FROM contact_points WHERE do_not_contact = false) AS total_contacts`),
safeQ(`SELECT o.id, o.display_name, o.last_seen_at, o.headquarters_state, o.created_at
FROM organizations o ORDER BY o.created_at DESC LIMIT 5`),
safeQ(`SELECT * FROM events WHERE start_date >= CURRENT_DATE ORDER BY start_date ASC LIMIT 5`),
safeQ(`SELECT * FROM rentv_audience_snapshots ORDER BY observed_at DESC LIMIT 4`),
]);
const s = summary[0] || {};
const isDemo = !s.total_orgs || s.total_orgs === '0';
const statCards = [
{ label: 'Verified Advertisers', value: s.verified_advertisers || 0, href: '/advertisers?verifiedOnly=1' },
{ label: 'Verified Sponsors', value: s.verified_sponsors || 0, href: '/advertisers?verifiedOnly=1' },
{ label: 'California Companies', value: s.california_orgs || 0, href: '/advertisers?market=CA' },
{ label: 'Arizona Companies', value: s.arizona_orgs || 0, href: '/advertisers?market=AZ' },
{ label: 'New Sightings (7d)', value: s.sightings_7d || 0, href: '/ads' },
{ label: 'New Sightings (30d)', value: s.sightings_30d || 0, href: '/ads' },
{ label: 'Total Contacts', value: s.total_contacts || 0, href: '/contacts' },
{ label: 'Total Events', value: s.total_events || 0, href: '/conferences' },
];
const statsHtml = statCards.map(c => `
<div class="stat-pill">
<span class="sp-label">${esc(c.label)}</span>
<span class="sp-value"><a href="${esc(c.href)}">${Number(c.value).toLocaleString()}</a></span>
</div>`).join('');
const recentHtml = recentOrgs.length
? recentOrgs.map(o => `<div style="display:flex;align-items:center;justify-content:space-between;padding:10px 0;border-bottom:1px solid var(--border);font-size:15px">
<a href="/advertisers/${esc(o.id)}">${esc(o.display_name)}</a>
<span class="miss" style="font-size:13px">${esc(o.headquarters_state || '')} · ${fmtDateOnly(o.last_seen_at)}</span>
</div>`).join('')
: '<p class="miss">No organizations yet.</p>';
const eventsHtml = upcomingEvents.length
? upcomingEvents.map(e => `<div style="display:flex;align-items:center;justify-content:space-between;padding:10px 0;border-bottom:1px solid var(--border);font-size:15px">
<a href="/conferences/${esc(e.id)}">${esc(e.name)}</a>
<span class="miss" style="font-size:13px">${fmtDateOnly(e.start_date)} · ${esc(e.city || '')}${e.state ? ', ' + esc(e.state) : ''}</span>
</div>`).join('')
: '<p class="miss">No upcoming events.</p>';
const audienceHtml = audiences.length
? audiences.map(a => `<div style="display:flex;justify-content:space-between;align-items:center;padding:10px 0;border-bottom:1px solid var(--border);font-size:15px">
<span>${esc(a.metric_label)}</span>
<span><strong>${esc(a.value_text || (a.value_numeric ? Number(a.value_numeric).toLocaleString() : '—'))}</strong>
<span class="miss" style="font-size:12px"> as of ${fmtDateOnly(a.observed_at)}</span></span>
</div>`).join('')
: '<p class="miss">No audience snapshots. Import from <a href="/imports">Imports</a>.</p>';
const body = `
<div class="page-content">
<h1 style="margin-bottom:20px">Dashboard</h1>
${isDemo ? demoBanner : ''}
<div class="stats-bar">${statsHtml}</div>
<div class="dash-grid">
<div class="dash-card">
<h3>Recently added companies</h3>
${recentHtml}
<p style="margin-top:10px"><a href="/advertisers" class="btn btn-outline" style="font-size:14px">View all advertisers</a></p>
</div>
<div class="dash-card">
<h3>Upcoming conferences</h3>
${eventsHtml}
<p style="margin-top:10px"><a href="/conferences" class="btn btn-outline" style="font-size:14px">View all conferences</a></p>
</div>
<div class="dash-card">
<h3>RENTV audience snapshots</h3>
${audienceHtml}
<p style="margin-top:10px"><a href="/media-kit" class="btn btn-outline" style="font-size:14px">View media kit</a></p>
</div>
<div class="dash-card">
<h3>Quick actions</h3>
<div style="display:flex;flex-direction:column;gap:10px;margin-top:8px">
<a href="/advertisers" class="btn btn-primary">View Advertisers</a>
<a href="/prospects" class="btn btn-outline">Top Prospects</a>
<a href="/review" class="btn btn-outline">Review Queue</a>
<a href="/imports" class="btn btn-outline">Import Data</a>
</div>
</div>
</div>
</div>`;
res.type('html').send(layout('Dashboard', body, { activeNav: '/' }));
} catch (e) {
console.error('[pages] /', e.message);
res.status(500).type('html').send(layout('Error', `<div class="page-content"><div class="alert alert-danger">Server error: ${esc(e.message)}</div></div>`));
}
});
// ═══════════════════════════════════════════════════════════════════════════════
// GET /advertisers — main table (§25)
// ═══════════════════════════════════════════════════════════════════════════════
router.get('/advertisers', async (req, res) => {
const market = ['CA', 'AZ', 'ALL'].includes(req.query.market) ? req.query.market : 'ALL';
const verifiedOnly = req.query.verifiedOnly === '1';
const q = req.query.q ? String(req.query.q).slice(0, 200) : '';
const category = req.query.category ? String(req.query.category).slice(0, 120) : '';
try {
const params = []; const conds = [];
function p(v) { params.push(v); return '$' + params.length; }
if (market !== 'ALL') conds.push(`o.headquarters_state = ${p(market)}`);
if (verifiedOnly) conds.push(`EXISTS (
SELECT 1 FROM ad_sightings s WHERE s.organization_id = o.id
AND s.relationship_status = ANY(${p(T.VERIFIED_STATUSES)}))`);
if (category) conds.push(`o.advertiser_categories @> ${p(JSON.stringify([category]))}::jsonb`);
if (q) {
const like = '%' + q.replace(/[%_]/g, '\\$&') + '%';
conds.push(`(o.display_name ILIKE ${p(like)} OR o.domain ILIKE ${p(like)})`);
}
const where = conds.length ? 'WHERE ' + conds.join(' AND ') : '';
const rows = await safeQ(`
SELECT
o.id, o.display_name, o.domain, o.headquarters_state, o.headquarters_city,
o.advertiser_categories, o.logo_asset_id, o.last_seen_at, o.created_at,
(SELECT s.relationship_status FROM ad_sightings s WHERE s.organization_id = o.id
ORDER BY CASE s.relationship_status
WHEN 'VERIFIED_ADVERTISER' THEN 1 WHEN 'VERIFIED_CONFERENCE_SPONSOR' THEN 2
WHEN 'VERIFIED_EXHIBITOR' THEN 3 WHEN 'VERIFIED_MEDIA_PARTNER' THEN 4
WHEN 'VERIFIED_CONTENT_PARTNER' THEN 5 WHEN 'PAST_ADVERTISER' THEN 6
WHEN 'LIKELY_PROSPECT' THEN 7 WHEN 'SPEAKER_OR_PANELIST_ONLY' THEN 8
WHEN 'RESEARCH_NEEDED' THEN 9 ELSE 10 END ASC LIMIT 1) AS top_status,
(SELECT COUNT(*) FROM ad_sightings s WHERE s.organization_id = o.id
AND s.verification_status = 'VERIFIED') AS verified_sightings,
(SELECT s.observed_at FROM ad_sightings s WHERE s.organization_id = o.id
ORDER BY s.observed_at DESC NULLS LAST LIMIT 1) AS last_sighting_at,
(SELECT COUNT(*) FROM event_relationships er WHERE er.organization_id = o.id) AS conf_activity,
(SELECT opp.score FROM opportunity_scores opp WHERE opp.organization_id = o.id
ORDER BY opp.computed_at DESC LIMIT 1) AS score,
(SELECT cp.value FROM contact_points cp WHERE cp.organization_id = o.id
AND cp.type = 'BUSINESS_PHONE' AND cp.do_not_contact = false LIMIT 1) AS best_phone,
(SELECT cp.value FROM contact_points cp WHERE cp.organization_id = o.id
AND cp.type = 'BUSINESS_EMAIL' AND cp.do_not_contact = false LIMIT 1) AS best_email,
(SELECT cp.value FROM contact_points cp WHERE cp.organization_id = o.id
AND cp.type = 'WEBSITE' LIMIT 1) AS website,
(SELECT cp.value FROM contact_points cp WHERE cp.organization_id = o.id
AND cp.type = 'LINKEDIN' LIMIT 1) AS linkedin_url,
(SELECT p.full_name FROM people p WHERE p.organization_id = o.id LIMIT 1) AS best_contact,
(SELECT s.source_page_url FROM ad_sightings s WHERE s.organization_id = o.id
ORDER BY s.observed_at DESC NULLS LAST LIMIT 1) AS latest_source
FROM organizations o ${where}
ORDER BY o.last_seen_at DESC NULLS LAST
LIMIT 500
`, params);
const isDemo = rows.length === 0;
// Category chips for rail
const catCounts = {};
rows.forEach(r => {
try {
const cats = Array.isArray(r.advertiser_categories) ? r.advertiser_categories : JSON.parse(r.advertiser_categories || '[]');
cats.forEach(c => { catCounts[c] = (catCounts[c] || 0) + 1; });
} catch (_) {}
});
const catChips = Object.entries(catCounts).sort((a, b) => b[1] - a[1]).slice(0, 16)
.map(([cat, n]) => `<span class="chip${category === cat ? ' active' : ''}" data-cat="${esc(cat)}">${esc(cat.slice(0, 40))}<span class="ct">${n}</span></span>`).join('');
const tableRows = rows.map(r => {
const thumb = r.logo_asset_id
? `<img class="org-thumb" src="/assets/${esc(r.logo_asset_id)}" alt="${esc(r.display_name)} logo" loading="lazy">`
: `<div class="org-thumb-placeholder" aria-hidden="true">🏢</div>`;
const cats = (() => { try { return JSON.parse(r.advertiser_categories || '[]'); } catch (_) { return []; } })();
const srcLink = r.latest_source
? `<a href="${esc(r.latest_source)}" target="_blank" rel="noopener noreferrer" title="${esc(r.latest_source)}">Source ↗</a>`
: '<span class="miss">—</span>';
return `<tr data-id="${esc(r.id)}" class="adv-row">
<td style="width:52px">${thumb}</td>
<td><a href="/advertisers/${esc(r.id)}">${esc(r.display_name)}</a></td>
<td>${statusBadge(r.top_status)}</td>
<td style="max-width:180px;white-space:normal;font-size:13px">${esc(cats.slice(0, 2).join(', ') || '—')}</td>
<td>${esc(r.headquarters_state || '—')}</td>
<td>${fmtDateOnly(r.last_sighting_at)}</td>
<td class="num"><a href="/advertisers/${esc(r.id)}#ads">${Number(r.verified_sightings || 0).toLocaleString()}</a></td>
<td class="num"><a href="/advertisers/${esc(r.id)}#conferences">${Number(r.conf_activity || 0).toLocaleString()}</a></td>
<td>
${scoreBadge(r.score)}
${r.id ? `<button class="btn btn-ghost" style="font-size:12px;padding:4px 8px;min-height:32px;margin-left:6px" data-smw-org="${esc(r.id)}" data-smw-name="${esc(r.display_name)}">Why?</button>` : ''}
</td>
<td>${r.best_contact ? esc(r.best_contact) : '<span class="miss">—</span>'}</td>
<td>${r.best_phone ? `<a href="tel:${esc(r.best_phone)}">${esc(r.best_phone)}</a>` : '<span class="miss">—</span>'}</td>
<td>${r.best_email ? `<a href="mailto:${esc(r.best_email)}">${esc(r.best_email)}</a>` : '<span class="miss">—</span>'}</td>
<td>${r.website ? `<a href="${/^https?:/.test(r.website) ? esc(r.website) : 'https://' + esc(r.website)}" target="_blank" rel="noopener noreferrer">${esc(r.domain || r.website)} ↗</a>` : '<span class="miss">—</span>'}</td>
<td>${r.linkedin_url ? `<a href="${esc(r.linkedin_url)}" target="_blank" rel="noopener noreferrer">LinkedIn ↗</a>` : '<span class="miss">—</span>'}</td>
<td>${srcLink}</td>
</tr>`;
}).join('');
const rail = standardRail(`
<div class="rail-section">
<h4>Category</h4>
<div class="chips" id="cat-chips">${catChips}</div>
</div>
<div class="rail-section">
<h4>Status</h4>
<div class="chips" id="status-chips">
${T.VERIFIED_STATUSES.concat(['LIKELY_PROSPECT', 'RESEARCH_NEEDED']).map(s =>
`<span class="chip" data-status="${esc(s)}">${esc(T.STATUS_LABELS[s] || s)}</span>`
).join('')}
</div>
</div>`);
const content = `
<div style="display:flex;align-items:center;justify-content:space-between;flex-wrap:wrap;gap:12px;margin-bottom:16px">
<h1>Advertisers</h1>
<a href="/imports" class="btn btn-outline" style="font-size:15px">+ Import Data</a>
</div>
${isDemo ? demoBanner : ''}
<div class="tbl-controls">
<input class="tbl-search" id="tbl-search" type="search" placeholder="Search all fields (space = AND)…" value="${esc(q)}" aria-label="Search advertisers">
<select class="tbl-sort-sel" id="sort-sel" aria-label="Sort by"></select>
<span class="tbl-count" id="row-count">${rows.length.toLocaleString()} records</span>
<button class="btn btn-ghost" id="csv-btn" style="font-size:14px">Export CSV</button>
</div>
<div class="tbl-wrap">
<table class="adv-tbl" role="grid" aria-label="Advertisers table">
<thead id="main-thead">
<tr>
<th>Logo</th><th>Company</th><th>Status</th><th>Category</th>
<th>Market</th><th>Last Sighting</th><th>#Verified</th><th>Conf. Activity</th>
<th>Score</th><th>Best Contact</th><th>Phone</th><th>Email</th>
<th>Website</th><th>LinkedIn</th><th>Source</th>
</tr>
</thead>
<tbody id="main-tbody">
${tableRows || '<tr><td colspan="15" class="empty-state"><p>No advertisers match your filters.</p></td></tr>'}
</tbody>
</table>
</div>
<script>
(function(){
// Client-side search
var q='', statusF=new Set(), catF=new Set();
var allRows=Array.from(document.querySelectorAll('#main-tbody tr.adv-row'));
function filter(){
var terms=q.toLowerCase().split(/\s+/).filter(Boolean);
var shown=0;
allRows.forEach(function(tr){
var txt=tr.textContent.toLowerCase();
var st=tr.querySelector('.badge')?tr.querySelector('.badge').textContent.trim():'';
var pass=terms.every(function(t){return txt.includes(t);});
if(statusF.size && !Array.from(statusF).some(function(s){return st.includes(s);})) pass=false;
tr.style.display=pass?'':'none';
if(pass) shown++;
});
document.getElementById('row-count').textContent=shown+' of '+allRows.length+' records';
}
document.getElementById('tbl-search').addEventListener('input',function(e){q=e.target.value;filter();});
document.querySelectorAll('#status-chips .chip').forEach(function(c){
c.addEventListener('click',function(){
c.classList.toggle('active');
if(c.classList.contains('active')) statusF.add(c.textContent.trim());
else statusF.delete(c.textContent.trim());
filter();
});
});
document.querySelectorAll('#cat-chips .chip').forEach(function(c){
c.addEventListener('click',function(){
c.classList.toggle('active');
if(c.classList.contains('active')) catF.add(c.dataset.cat);
else catF.delete(c.dataset.cat);
filter();
});
});
// Sort select
var COLS=[
{k:'display_name',l:'Company',g:'Identity'},{k:'top_status',l:'Status',g:'Identity'},
{k:'headquarters_state',l:'Market',g:'Identity'},{k:'last_sighting_at',l:'Last Sighting',g:'Activity'},
{k:'verified_sightings',l:'# Verified',g:'Activity'},{k:'conf_activity',l:'Conf. Activity',g:'Activity'},
{k:'score',l:'Score',g:'Activity'}
];
var sel=document.getElementById('sort-sel');
COLS.forEach(function(c){var o=document.createElement('option');o.value=c.k;o.textContent=c.l;sel.appendChild(o);});
// CSV export
document.getElementById('csv-btn').addEventListener('click',function(){
var rows=allRows.filter(function(tr){return tr.style.display!=='none';});
var cols=['Company','Status','Category','Market','Last Sighting','#Verified','Conf','Score','Contact','Phone','Email','Website'];
var csv=cols.map(function(c){return '"'+c+'"';}).join(',')+'\n'
+rows.map(function(tr){
var tds=Array.from(tr.querySelectorAll('td'));
return [1,2,3,4,5,6,7,8,9,10,11,12].map(function(i){
var v=(tds[i]||{}).textContent||'';
return '"'+v.replace(/"/g,'""').trim()+'"';
}).join(',');
}).join('\n');
var a=document.createElement('a');
a.href=URL.createObjectURL(new Blob([csv],{type:'text/csv'}));
a.download='rentv-advertisers.csv';a.click();URL.revokeObjectURL(a.href);
});
})();
</script>`;
res.type('html').send(layout('Advertisers', pageShell(rail, content), { activeNav: '/advertisers', market, verified: verifiedOnly, q }));
} catch (e) {
console.error('[pages] /advertisers', e.message);
res.status(500).type('html').send(layout('Error', `<div class="page-content"><div class="alert alert-danger">Server error: ${esc(e.message)}</div></div>`));
}
});
// ═══════════════════════════════════════════════════════════════════════════════
// GET /advertisers/:id — org profile (§25 tabs)
// ═══════════════════════════════════════════════════════════════════════════════
router.get('/advertisers/:id', async (req, res) => {
const { id } = req.params;
if (!/^[0-9a-f-]{36}$/i.test(id)) return res.redirect('/advertisers');
try {
const [orgRows, sightings, events, contacts, scoreRows, evidenceRows, auditRows] = await Promise.all([
safeQ(`SELECT * FROM organizations WHERE id = $1`, [id]),
safeQ(`SELECT s.*, p.name AS pub_name, p.domain AS pub_domain
FROM ad_sightings s LEFT JOIN publications p ON p.id = s.publication_id
WHERE s.organization_id = $1 ORDER BY s.observed_at DESC NULLS LAST LIMIT 100`, [id]),
safeQ(`SELECT er.*, e.name AS event_name, e.start_date, e.end_date, e.city, e.state, e.official_url
FROM event_relationships er JOIN events e ON e.id = er.event_id
WHERE er.organization_id = $1 ORDER BY e.start_date DESC NULLS LAST LIMIT 50`, [id]),
safeQ(`SELECT cp.*, pe.full_name, pe.public_title, pe.linkedin_url AS person_li
FROM contact_points cp LEFT JOIN people pe ON pe.id = cp.person_id
WHERE cp.organization_id = $1 AND cp.do_not_contact = false
ORDER BY cp.confidence DESC LIMIT 50`, [id]),
safeQ(`SELECT * FROM opportunity_scores WHERE organization_id = $1 ORDER BY computed_at DESC LIMIT 1`, [id]),
safeQ(`SELECT er.* FROM evidence_records er WHERE er.id IN (
SELECT evidence_id FROM ad_sightings WHERE organization_id = $1 AND evidence_id IS NOT NULL
) LIMIT 30`, [id]),
safeQ(`SELECT * FROM audit_logs WHERE entity_id = $1 ORDER BY created_at DESC LIMIT 30`, [id]),
]);
if (!orgRows.length) return res.status(404).type('html').send(layout('Not Found', `<div class="page-content"><div class="alert alert-danger">Organization not found. <a href="/advertisers">Back to advertisers</a></div></div>`));
const org = orgRows[0];
const scoreRow = scoreRows[0];
let scoreExplain = null;
if (scoreRow && scoreRow.factors) {
try { scoreExplain = scoring.explainScore(scoreRow.factors); } catch (_) {}
}
const cats = (() => { try { return Array.isArray(org.advertiser_categories) ? org.advertiser_categories : JSON.parse(org.advertiser_categories || '[]'); } catch (_) { return []; } })();
const bestEmail = contacts.find(c => c.type === 'BUSINESS_EMAIL');
const bestPhone = contacts.find(c => c.type === 'BUSINESS_PHONE');
const website = contacts.find(c => c.type === 'WEBSITE');
const linkedin = contacts.find(c => c.type === 'LINKEDIN');
const FACTOR_LABELS = {
verifiedAdvertising: 'Verified advertising',
verifiedConferenceSpendSignal: 'Conference spend signal',
recency: 'Recency',
repeatActivity: 'Repeat activity',
californiaFit: 'California fit',
arizonaFit: 'Arizona fit',
categoryFit: 'Category fit',
rentvAudienceFit: 'RENTV audience fit',
contactCompleteness: 'Contact completeness',
evidenceQuality: 'Evidence quality',
};
const scoreHtml = scoreExplain
? `<div class="settings-card">
<h3>Opportunity Score: ${scoreExplain.score} / 100</h3>
<p style="font-size:14px;color:var(--ink-muted)">RENTV sales opportunity score — not an ad-spend estimate. Computed ${fmtDate(scoreRow.computed_at)}.</p>
${scoreExplain.factors.map(f => `
<div class="factor-row">
<span class="factor-name">${esc(FACTOR_LABELS[f.factor] || f.factor)}</span>
<div class="factor-bar-wrap"><div class="factor-bar" style="width:${f.value}%"></div></div>
<span class="factor-contrib">${f.value}</span>
</div>`).join('')}
</div>`
: `<p class="miss">No score computed yet. Run the scoring job.</p>`;
const sightingsHtml = sightings.length
? sightings.map(s => `
<div class="evidence-card">
<div class="ev-title">${statusBadge(s.relationship_status)} ${esc(s.headline || 'Ad sighting')}</div>
<div class="ev-meta">
<span>${fmtDate(s.observed_at)}</span>
${s.pub_name ? `<span>via <a href="/advertisers?q=${encodeURIComponent(s.pub_name)}">${esc(s.pub_name)}</a></span>` : ''}
${s.source_page_url ? `<span><a href="${esc(s.source_page_url)}" target="_blank" rel="noopener noreferrer">Source ↗</a></span>` : ''}
<span>Verification: ${esc(s.verification_status || '—')}</span>
<span>Confidence: ${s.confidence ? Math.round(s.confidence * 100) + '%' : '—'}</span>
</div>
${s.visible_copy ? `<div class="ev-excerpt">${esc(s.visible_copy)}</div>` : ''}
<div style="margin-top:8px;display:flex;gap:8px;flex-wrap:wrap">
${s.verification_status !== 'VERIFIED' ? `<button class="btn btn-outline" style="font-size:13px;min-height:36px" onclick="reviewAction('${esc(s.id)}','verify')">Mark Verified</button>` : ''}
${s.verification_status !== 'REJECTED' ? `<button class="btn btn-ghost" style="font-size:13px;min-height:36px" onclick="reviewAction('${esc(s.id)}','reject')">Reject</button>` : ''}
</div>
</div>`).join('')
: '<p class="miss">No ad sightings recorded yet.</p>';
const confSponsors = events.filter(e => e.relationship_status !== 'SPEAKER_OR_PANELIST_ONLY');
const panelists = events.filter(e => e.relationship_status === 'SPEAKER_OR_PANELIST_ONLY');
const confsHtml = events.length ? `
${confSponsors.length ? `<div class="section-divider">Sponsor / Exhibitor Roles (${confSponsors.length})</div>
${confSponsors.map(e => `<div class="evidence-card">
<div class="ev-title"><a href="/conferences/${esc(e.event_id)}">${esc(e.event_name)}</a></div>
<div class="ev-meta">
<span>${statusBadge(e.relationship_status)}</span>
<span>${fmtDateOnly(e.start_date)}${e.city ? ' · ' + esc(e.city) : ''}${e.state ? ', ' + esc(e.state) : ''}</span>
${e.sponsor_level ? `<span>Level: ${esc(e.sponsor_level)}</span>` : ''}
${e.official_url ? `<span><a href="${esc(e.official_url)}" target="_blank" rel="noopener noreferrer">Official ↗</a></span>` : ''}
</div>
</div>`).join('')}` : ''}
${panelists.length ? `<div class="section-divider">Speaker / Panelist Roles (${panelists.length})</div>
<div class="alert alert-info" style="margin-bottom:16px">These are speaking roles only — not verified sponsorships.</div>
${panelists.map(e => `<div class="evidence-card">
<div class="ev-title"><a href="/conferences/${esc(e.event_id)}">${esc(e.event_name)}</a></div>
<div class="ev-meta">
<span>${statusBadge(e.relationship_status)}</span>
<span>${fmtDateOnly(e.start_date)}${e.city ? ' · ' + esc(e.city) : ''}${e.state ? ', ' + esc(e.state) : ''}</span>
${e.session_title ? `<span>Session: ${esc(e.session_title)}</span>` : ''}
</div>
</div>`).join('')}` : ''}
` : '<p class="miss">No conference activity recorded.</p>';
const contactsHtml = contacts.length
? contacts.map(c => `
<div class="evidence-card">
<div class="ev-title">${esc(c.full_name || 'Organization contact')}${c.public_title ? ` <span class="miss" style="font-weight:400">· ${esc(c.public_title)}</span>` : ''}</div>
<div class="ev-meta">
<span>${esc(c.type.replace(/_/g, ' '))}</span>
<span>${c.type === 'BUSINESS_EMAIL' ? `<a href="mailto:${esc(c.value)}">${esc(c.value)}</a>` :
c.type === 'BUSINESS_PHONE' ? `<a href="tel:${esc(c.value)}">${esc(c.value)}</a>` :
c.type === 'WEBSITE' || c.type === 'LINKEDIN' ? `<a href="${esc(c.value)}" target="_blank" rel="noopener noreferrer">${esc(c.value)} ↗</a>` :
esc(c.value)}</span>
${c.confidence ? `<span>Confidence: ${Math.round(c.confidence * 100)}%</span>` : ''}
${c.verified_at ? `<span>Verified: ${fmtDateOnly(c.verified_at)}</span>` : ''}
</div>
${c.person_li ? `<div style="margin-top:6px"><a href="${esc(c.person_li)}" target="_blank" rel="noopener noreferrer" class="btn btn-ghost" style="font-size:13px;min-height:32px">Open LinkedIn ↗</a></div>` : ''}
</div>`).join('')
: '<p class="miss">No contacts recorded. <a href="/review">Add via review queue</a>.</p>';
const evidenceHtml = evidenceRows.length
? evidenceRows.map(e => `
<div class="evidence-card">
<div class="ev-title">${esc(e.source_title || e.evidence_type)}</div>
<div class="ev-meta">
<span class="badge badge-research-needed" style="font-size:11px">${esc(e.evidence_type)}</span>
${e.source_url ? `<span><a href="${esc(e.source_url)}" target="_blank" rel="noopener noreferrer">Source ↗</a></span>` : ''}
<span>Observed: ${fmtDateOnly(e.observed_at)}</span>
<span>Retrieved: ${fmtDate(e.retrieved_at)}</span>
<span>Confidence: ${Math.round((e.confidence || 0) * 100)}%</span>
<span>Export: ${e.export_allowed ? 'Allowed' : 'Internal only'}</span>
</div>
${e.excerpt ? `<div class="ev-excerpt">${esc(e.excerpt)}</div>` : ''}
</div>`).join('')
: '<p class="miss">No evidence records linked yet.</p>';
const auditHtml = auditRows.length
? auditRows.map(a => `
<div class="audit-row">
<div style="flex:0 0 180px">
<span class="when-chip" title="${esc(new Date(a.created_at).toISOString())}">${fmtDate(a.created_at)}</span>
</div>
<div>
<span class="audit-action">${esc(a.action)}</span>
<span class="audit-meta"> — by ${esc(a.actor || 'system')}</span>
</div>
</div>`).join('')
: '<p class="miss">No audit history.</p>';
const header = `
<div class="org-header">
<div class="org-thumb-placeholder" style="flex-shrink:0;width:64px;height:64px;font-size:28px" aria-hidden="true">🏢</div>
<div class="org-header-info">
<h1>${esc(org.display_name)}</h1>
<div style="display:flex;flex-wrap:wrap;gap:8px;align-items:center;margin-bottom:6px">
${statusBadge(sightings[0] && sightings[0].relationship_status)}
${scoreBadge(scoreRow && scoreRow.score)}
${org.headquarters_state ? `<span class="badge badge-panelist">${esc(org.headquarters_state)}</span>` : ''}
${cats.slice(0, 2).map(c => `<span class="badge badge-panelist" style="font-size:11px">${esc(c)}</span>`).join('')}
</div>
${org.description ? `<p style="color:var(--ink-muted);font-size:15px;margin:0">${esc(org.description)}</p>` : ''}
<div class="org-actions">
${website ? `<a href="${/^https?:/.test(website.value) ? esc(website.value) : 'https://' + esc(website.value)}" class="btn btn-outline" target="_blank" rel="noopener noreferrer">Website ↗</a>` : ''}
${linkedin ? `<a href="${esc(linkedin.value)}" class="btn btn-outline" target="_blank" rel="noopener noreferrer">LinkedIn ↗</a>` : ''}
${bestEmail ? `<a href="mailto:${esc(bestEmail.value)}" class="btn btn-outline">Email</a>` : ''}
${bestPhone ? `<a href="tel:${esc(bestPhone.value)}" class="btn btn-outline">Call</a>` : ''}
${scoreRow ? `<button class="btn btn-ghost" data-smw-org="${esc(id)}" data-smw-name="${esc(org.display_name)}">Show Me Why ⓘ</button>` : ''}
</div>
</div>
</div>`;
const tabs = [
['summary', 'Summary'],
['ads', 'Ads & Creatives'],
['conferences', 'Conference Sponsorships'],
['contacts', 'Contacts'],
['evidence', 'Sources & Evidence'],
['audit', 'Audit History'],
];
const tabBar = `<div class="tab-bar" role="tablist">
${tabs.map(([k, l]) => `<button class="tab-btn" role="tab" data-tab="${k}" aria-selected="false">${esc(l)}</button>`).join('')}
</div>`;
const summaryTab = `<div class="tab-panel" id="tab-summary">
<div class="settings-card">
<h3>Organization details</h3>
<table style="width:100%;border-collapse:collapse;font-size:15px">
${[
['Legal name', org.legal_name],
['Domain', org.domain ? `<a href="https://${esc(org.domain)}" target="_blank" rel="noopener noreferrer">${esc(org.domain)} ↗</a>` : '—'],
['Headquarters', [org.headquarters_city, org.headquarters_state].filter(Boolean).join(', ') || '—'],
['Status', org.active_status],
['First seen', fmtDateOnly(org.first_seen_at)],
['Last seen', fmtDate(org.last_seen_at)],
['Created', fmtDate(org.created_at)],
['Categories', cats.join(', ') || '—'],
].map(([k, v]) => `<tr><td style="padding:8px 0;color:var(--ink-muted);width:180px;border-bottom:1px solid var(--border)">${esc(k)}</td><td style="padding:8px 0;border-bottom:1px solid var(--border)">${v}</td></tr>`).join('')}
</table>
</div>
${scoreHtml}
</div>`;
const body = `<div class="page-content">
<p><a href="/advertisers">← All advertisers</a></p>
${header}
${tabBar}
${summaryTab}
<div class="tab-panel" id="tab-ads">${sightingsHtml}</div>
<div class="tab-panel" id="tab-conferences">${confsHtml}</div>
<div class="tab-panel" id="tab-contacts">${contactsHtml}</div>
<div class="tab-panel" id="tab-evidence">${evidenceHtml}</div>
<div class="tab-panel" id="tab-audit">${auditHtml}</div>
<script>
(function(){
var tabs=document.querySelectorAll('.tab-btn');
var panels=document.querySelectorAll('.tab-panel');
function show(k){
tabs.forEach(function(t){t.classList.toggle('active',t.dataset.tab===k);t.setAttribute('aria-selected',t.dataset.tab===k);});
panels.forEach(function(p){p.classList.toggle('active',p.id==='tab-'+k);});
try{localStorage.setItem('adv-tab-${esc(id)}',k);}catch(_){}
}
tabs.forEach(function(t){t.addEventListener('click',function(){show(t.dataset.tab);});});
var saved=localStorage.getItem('adv-tab-${esc(id)}');
show(saved&&document.getElementById('tab-'+saved)?saved:'summary');
function reviewAction(sid,action){
fetch('/api/v1/review/'+sid+'/'+action,{method:'POST',headers:{'Content-Type':'application/json'},body:'{}'})
.then(function(r){return r.json();})
.then(function(d){if(d.ok){location.reload();}else{alert(d.message||'Error');}})
.catch(function(e){alert(e.message);});
}
window.reviewAction=reviewAction;
})();
</script>
</div>`;
res.type('html').send(layout(org.display_name, body, { activeNav: '/advertisers' }));
} catch (e) {
console.error('[pages] /advertisers/:id', e.message);
res.status(500).type('html').send(layout('Error', `<div class="page-content"><div class="alert alert-danger">Server error: ${esc(e.message)}</div></div>`));
}
});
// ═══════════════════════════════════════════════════════════════════════════════
// GET /ads — visual ad gallery
// ═══════════════════════════════════════════════════════════════════════════════
router.get('/ads', async (req, res) => {
try {
const rows = await safeQ(`
SELECT s.id, s.relationship_status, s.headline, s.observed_at, s.source_page_url,
s.verification_status, s.confidence, s.created_at,
o.display_name AS org_name, o.id AS org_id,
p.name AS pub_name, ca.object_key AS thumb_key
FROM ad_sightings s
LEFT JOIN organizations o ON o.id = s.organization_id
LEFT JOIN publications p ON p.id = s.publication_id
LEFT JOIN creative_assets ca ON ca.id = s.thumbnail_asset_id
ORDER BY s.observed_at DESC NULLS LAST LIMIT 200`);
const isDemo = rows.length === 0;
const cards = rows.map(r => `
<div class="ad-card">
${r.thumb_key
? `<img src="/assets/${esc(r.thumb_key)}" alt="${esc(r.org_name || '')} ad thumbnail" loading="lazy">`
: `<div style="height:160px;display:flex;align-items:center;justify-content:center;background:var(--surface-alt);font-size:40px">🖼</div>`}
<div class="ad-card-body">
<div class="org-name"><a href="/advertisers/${esc(r.org_id)}">${esc(r.org_name || '—')}</a></div>
<div class="ad-meta">${statusBadge(r.relationship_status)} · ${fmtDateOnly(r.observed_at)}</div>
${r.headline ? `<p style="font-size:14px;color:var(--ink-muted);margin:4px 0 0">${esc(r.headline)}</p>` : ''}
</div>
<div class="ad-card-footer">
<a href="/ads/${esc(r.id)}" class="btn btn-outline" style="font-size:13px;min-height:36px">Details</a>
${r.source_page_url ? `<a href="${esc(r.source_page_url)}" class="btn btn-ghost" style="font-size:13px;min-height:36px" target="_blank" rel="noopener noreferrer">Source ↗</a>` : ''}
</div>
</div>`).join('');
const body = `<div class="page-content">
<div style="display:flex;align-items:center;justify-content:space-between;margin-bottom:16px;flex-wrap:wrap;gap:12px">
<h1>Ad Gallery</h1>
<div style="display:flex;align-items:center;gap:12px">
<label class="density-wrap">Density <input type="range" id="density-sl" min="1" max="10" step="1"></label>
</div>
</div>
${isDemo ? demoBanner : ''}
<div class="card-grid">${cards || '<p class="miss">No ad sightings yet. Import data or run a research job.</p>'}</div>
<script>
(function(){
var sl=document.getElementById('density-sl');
if(!sl) return;
var stored=parseInt(localStorage.getItem('ads:density'),10);
var val=(stored>=1&&stored<=10)?stored:5;
sl.value=val;
function apply(v){var px=Math.round(450-(Math.max(1,Math.min(10,v))-1)*(450-200)/9);document.documentElement.style.setProperty('--card-min',px+'px');}
apply(val);
sl.addEventListener('input',function(){apply(+sl.value);localStorage.setItem('ads:density',sl.value);});
})();
</script>
</div>`;
res.type('html').send(layout('Ad Gallery', body, { activeNav: '/ads' }));
} catch (e) {
res.status(500).type('html').send(layout('Error', `<div class="page-content"><div class="alert alert-danger">${esc(e.message)}</div></div>`));
}
});
// GET /ads/:id
router.get('/ads/:id', async (req, res) => {
if (!/^[0-9a-f-]{36}$/i.test(req.params.id)) return res.redirect('/ads');
try {
const rows = await safeQ(`
SELECT s.*, o.display_name AS org_name, o.id AS org_id, p.name AS pub_name,
er.source_url, er.source_title, er.excerpt, er.observed_at AS ev_observed_at,
er.confidence AS ev_confidence, er.export_allowed
FROM ad_sightings s
LEFT JOIN organizations o ON o.id = s.organization_id
LEFT JOIN publications p ON p.id = s.publication_id
LEFT JOIN evidence_records er ON er.id = s.evidence_id
WHERE s.id = $1`, [req.params.id]);
if (!rows.length) return res.status(404).type('html').send(layout('Not Found', `<div class="page-content"><p>Ad sighting not found. <a href="/ads">Back to gallery</a></p></div>`));
const s = rows[0];
const body = `<div class="page-content">
<p><a href="/ads">← Ad Gallery</a></p>
<h1>${esc(s.headline || 'Ad Sighting')}</h1>
<div style="display:flex;flex-wrap:wrap;gap:8px;margin-bottom:16px">
${statusBadge(s.relationship_status)}
<span class="badge badge-panelist">Verification: ${esc(s.verification_status || '—')}</span>
</div>
<div class="settings-card">
${[
['Company', s.org_id ? `<a href="/advertisers/${esc(s.org_id)}">${esc(s.org_name || '—')}</a>` : '—'],
['Publication', esc(s.pub_name || '—')],
['Observed', fmtDate(s.observed_at)],
['First observed', fmtDateOnly(s.first_observed_at)],
['Last observed', fmtDateOnly(s.last_observed_at)],
['Source URL', s.source_page_url ? `<a href="${esc(s.source_page_url)}" target="_blank" rel="noopener noreferrer">${esc(s.source_page_url)} ↗</a>` : '—'],
['Landing URL', s.landing_url ? `<a href="${esc(s.landing_url)}" target="_blank" rel="noopener noreferrer">${esc(s.landing_url)} ↗</a>` : '—'],
['Confidence', s.confidence ? Math.round(s.confidence * 100) + '%' : '—'],
['Created', fmtDate(s.created_at)],
].map(([k, v]) => `<div style="display:flex;gap:16px;padding:8px 0;border-bottom:1px solid var(--border);font-size:15px"><span style="color:var(--ink-muted);flex:0 0 160px">${esc(k)}</span><span>${v}</span></div>`).join('')}
</div>
${s.visible_copy ? `<div class="settings-card"><h3>Ad copy</h3><p style="font-style:italic">${esc(s.visible_copy)}</p></div>` : ''}
${s.excerpt ? `<div class="settings-card"><h3>Evidence excerpt</h3><blockquote class="ev-excerpt" style="margin:0">${esc(s.excerpt)}</blockquote></div>` : ''}
<div style="display:flex;gap:10px;margin-top:16px">
<button class="btn btn-outline" onclick="reviewAction('${esc(s.id)}','verify')">Mark Verified</button>
<button class="btn btn-ghost" onclick="reviewAction('${esc(s.id)}','reject')">Reject</button>
</div>
<script>function reviewAction(id,a){fetch('/api/v1/review/'+id+'/'+a,{method:'POST',headers:{'Content-Type':'application/json'},body:'{}'}).then(r=>r.json()).then(d=>{if(d.ok)location.reload();else alert(d.message||'Error');}).catch(e=>alert(e.message));}</script>
</div>`;
res.type('html').send(layout('Ad: ' + (s.headline || s.org_name || ''), body, { activeNav: '/ads' }));
} catch (e) {
res.status(500).type('html').send(layout('Error', `<div class="page-content"><div class="alert alert-danger">${esc(e.message)}</div></div>`));
}
});
// ═══════════════════════════════════════════════════════════════════════════════
// GET /conferences
// ═══════════════════════════════════════════════════════════════════════════════
router.get('/conferences', async (req, res) => {
try {
const rows = await safeQ(`
SELECT e.*,
(SELECT COUNT(*) FROM event_relationships er WHERE er.event_id = e.id
AND er.relationship_status != 'SPEAKER_OR_PANELIST_ONLY') AS sponsor_count,
(SELECT COUNT(*) FROM event_relationships er WHERE er.event_id = e.id
AND er.relationship_status = 'SPEAKER_OR_PANELIST_ONLY') AS panelist_count
FROM events e ORDER BY e.start_date DESC NULLS LAST LIMIT 200`);
const isDemo = rows.length === 0;
const tableRows = rows.map(r => `<tr>
<td><a href="/conferences/${esc(r.id)}">${esc(r.name)}</a></td>
<td>${fmtDateOnly(r.start_date)}</td>
<td>${esc(r.city || '—')}${r.state ? ', ' + esc(r.state) : ''}</td>
<td>${esc(r.event_type || '—')}</td>
<td class="num"><a href="/conferences/${esc(r.id)}">${Number(r.sponsor_count || 0).toLocaleString()}</a></td>
<td class="num">${Number(r.panelist_count || 0).toLocaleString()}</td>
<td>${r.official_url ? `<a href="${esc(r.official_url)}" target="_blank" rel="noopener noreferrer">Official ↗</a>` : '<span class="miss">—</span>'}</td>
</tr>`).join('');
const body = `<div class="page-content">
<h1>Conferences</h1>
${isDemo ? demoBanner : ''}
<div class="tbl-controls">
<input class="tbl-search" id="conf-search" type="search" placeholder="Search conferences…" aria-label="Search conferences">
<span class="tbl-count" id="conf-count">${rows.length} events</span>
</div>
<div class="tbl-wrap">
<table class="adv-tbl" id="conf-tbl" aria-label="Conferences">
<thead><tr><th>Name</th><th>Date</th><th>Location</th><th>Type</th><th>Sponsors</th><th>Panelists</th><th>Link</th></tr></thead>
<tbody>${tableRows || '<tr><td colspan="7"><p class="miss" style="padding:20px">No conferences recorded yet.</p></td></tr>'}</tbody>
</table>
</div>
<script>
(function(){
var s=document.getElementById('conf-search'),rows=Array.from(document.querySelectorAll('#conf-tbl tbody tr'));
if(!s)return;
s.addEventListener('input',function(){var q=s.value.toLowerCase().split(/\s+/).filter(Boolean),n=0;rows.forEach(function(tr){var pass=q.every(function(t){return tr.textContent.toLowerCase().includes(t);});tr.style.display=pass?'':'none';if(pass)n++;});document.getElementById('conf-count').textContent=n+' of '+rows.length+' events';});
})();
</script>
</div>`;
res.type('html').send(layout('Conferences', body, { activeNav: '/conferences' }));
} catch (e) {
res.status(500).type('html').send(layout('Error', `<div class="page-content"><div class="alert alert-danger">${esc(e.message)}</div></div>`));
}
});
// GET /conferences/:id
router.get('/conferences/:id', async (req, res) => {
if (!/^[0-9a-f-]{36}$/i.test(req.params.id)) return res.redirect('/conferences');
try {
const [evRows, rels] = await Promise.all([
safeQ(`SELECT * FROM events WHERE id = $1`, [req.params.id]),
safeQ(`SELECT er.*, o.display_name AS org_name, o.id AS org_id, o.domain,
o.advertiser_categories
FROM event_relationships er
JOIN organizations o ON o.id = er.organization_id
WHERE er.event_id = $1
ORDER BY CASE er.relationship_status
WHEN 'VERIFIED_CONFERENCE_SPONSOR' THEN 1 WHEN 'VERIFIED_EXHIBITOR' THEN 2
WHEN 'VERIFIED_MEDIA_PARTNER' THEN 3 WHEN 'VERIFIED_CONTENT_PARTNER' THEN 4
WHEN 'SPEAKER_OR_PANELIST_ONLY' THEN 5 ELSE 6 END, o.display_name`, [req.params.id]),
]);
if (!evRows.length) return res.status(404).type('html').send(layout('Not Found', `<div class="page-content"><p>Conference not found. <a href="/conferences">Back</a></p></div>`));
const ev = evRows[0];
const sponsors = rels.filter(r => r.relationship_status !== 'SPEAKER_OR_PANELIST_ONLY');
const panelists = rels.filter(r => r.relationship_status === 'SPEAKER_OR_PANELIST_ONLY');
const relCard = (r) => `<div class="evidence-card">
<div class="ev-title"><a href="/advertisers/${esc(r.org_id)}">${esc(r.org_name)}</a></div>
<div class="ev-meta">
${statusBadge(r.relationship_status)}
${r.sponsor_level ? `<span>Level: ${esc(r.sponsor_level)}</span>` : ''}
${r.session_title ? `<span>Session: ${esc(r.session_title)}</span>` : ''}
${r.panel_role ? `<span>Role: ${esc(r.panel_role)}</span>` : ''}
${r.domain ? `<span><a href="https://${esc(r.domain)}" target="_blank" rel="noopener noreferrer">${esc(r.domain)} ↗</a></span>` : ''}
<span>Confidence: ${Math.round((r.confidence || 0) * 100)}%</span>
</div>
</div>`;
const body = `<div class="page-content">
<p><a href="/conferences">← Conferences</a></p>
<h1>${esc(ev.name)}</h1>
<div class="settings-card" style="margin-bottom:24px">
${[
['Date', fmtDateOnly(ev.start_date) + (ev.end_date ? ' – ' + fmtDateOnly(ev.end_date) : '')],
['Location', [ev.venue, ev.city, ev.state].filter(Boolean).join(', ') || '—'],
['Type', ev.event_type || '—'],
['Official URL', ev.official_url ? `<a href="${esc(ev.official_url)}" target="_blank" rel="noopener noreferrer">${esc(ev.official_url)} ↗</a>` : '—'],
['Sponsor page', ev.sponsor_page_url ? `<a href="${esc(ev.sponsor_page_url)}" target="_blank" rel="noopener noreferrer">Sponsor page ↗</a>` : '—'],
].map(([k,v]) => `<div style="display:flex;gap:16px;padding:8px 0;border-bottom:1px solid var(--border);font-size:15px"><span style="color:var(--ink-muted);flex:0 0 140px">${esc(k)}</span><span>${v}</span></div>`).join('')}
</div>
<div class="section-divider">Sponsors, Exhibitors & Partners (${sponsors.length})</div>
${sponsors.length ? sponsors.map(relCard).join('') : '<p class="miss">No verified sponsors recorded. Panelists are listed separately below.</p>'}
<div class="section-divider" style="margin-top:28px">Speakers & Panelists (${panelists.length})</div>
${panelists.length
? `<div class="alert alert-info" style="margin-bottom:16px">Speaking roles only — not sponsors unless separately verified.</div>` + panelists.map(relCard).join('')
: '<p class="miss">No panelist records.</p>'}
</div>`;
res.type('html').send(layout(ev.name, body, { activeNav: '/conferences' }));
} catch (e) {
res.status(500).type('html').send(layout('Error', `<div class="page-content"><div class="alert alert-danger">${esc(e.message)}</div></div>`));
}
});
// ═══════════════════════════════════════════════════════════════════════════════
// GET /contacts
// ═══════════════════════════════════════════════════════════════════════════════
router.get('/contacts', async (req, res) => {
const q = req.query.q ? String(req.query.q).slice(0, 200) : '';
try {
const params = [];
function p(v) { params.push(v); return '$' + params.length; }
const conds = ['cp.do_not_contact = false'];
if (q) {
const like = '%' + q.replace(/[%_]/g, '\\$&') + '%';
conds.push(`(pe.full_name ILIKE ${p(like)} OR o.display_name ILIKE ${p(like)} OR cp.value ILIKE ${p(like)})`);
}
const rows = await safeQ(`
SELECT cp.id, cp.type, cp.value, cp.confidence, cp.verified_at, cp.created_at,
pe.full_name, pe.public_title, pe.linkedin_url AS person_li,
o.display_name AS org_name, o.id AS org_id
FROM contact_points cp
LEFT JOIN people pe ON pe.id = cp.person_id
LEFT JOIN organizations o ON o.id = cp.organization_id
WHERE ${conds.join(' AND ')}
ORDER BY cp.confidence DESC, cp.created_at DESC
LIMIT 500`, params);
const isDemo = rows.length === 0;
const tableRows = rows.map(r => {
const val = r.type === 'BUSINESS_EMAIL' ? `<a href="mailto:${esc(r.value)}">${esc(r.value)}</a>`
: r.type === 'BUSINESS_PHONE' ? `<a href="tel:${esc(r.value)}">${esc(r.value)}</a>`
: r.type === 'WEBSITE' || r.type === 'LINKEDIN' ? `<a href="${esc(r.value)}" target="_blank" rel="noopener noreferrer">${esc(r.value)} ↗</a>`
: esc(r.value);
return `<tr>
<td>${r.full_name ? esc(r.full_name) : '<span class="miss">—</span>'}</td>
<td>${r.public_title ? esc(r.public_title) : '<span class="miss">—</span>'}</td>
<td><a href="/advertisers/${esc(r.org_id)}">${esc(r.org_name || '—')}</a></td>
<td><span class="badge badge-panelist" style="font-size:11px">${esc(r.type.replace(/_/g, ' '))}</span></td>
<td>${val}</td>
<td>${r.confidence ? Math.round(r.confidence * 100) + '%' : '—'}</td>
<td>${fmtDateOnly(r.verified_at)}</td>
<td>${r.person_li ? `<a href="${esc(r.person_li)}" target="_blank" rel="noopener noreferrer">LinkedIn ↗</a>` : '<span class="miss">—</span>'}</td>
<td><span class="when-chip" title="${esc(r.created_at ? new Date(r.created_at).toISOString() : '')}">${fmtDate(r.created_at)}</span></td>
</tr>`;
}).join('');
const body = `<div class="page-content">
<h1>Contacts</h1>
${isDemo ? demoBanner : ''}
<div class="tbl-controls">
<input class="tbl-search" id="contact-search" type="search" placeholder="Search contacts…" value="${esc(q)}" aria-label="Search contacts">
<span class="tbl-count" id="contact-count">${rows.length} contacts</span>
</div>
<div class="tbl-wrap">
<table class="adv-tbl" id="contact-tbl" aria-label="Contacts">
<thead><tr><th>Name</th><th>Title</th><th>Company</th><th>Type</th><th>Value</th><th>Confidence</th><th>Verified</th><th>LinkedIn</th><th>Created</th></tr></thead>
<tbody>${tableRows || '<tr><td colspan="9"><p class="miss" style="padding:20px">No contacts yet.</p></td></tr>'}</tbody>
</table>
</div>
<script>
(function(){var s=document.getElementById('contact-search'),rows=Array.from(document.querySelectorAll('#contact-tbl tbody tr'));if(!s)return;s.addEventListener('input',function(){var q=s.value.toLowerCase().split(/\s+/).filter(Boolean),n=0;rows.forEach(function(tr){var pass=q.every(function(t){return tr.textContent.toLowerCase().includes(t);});tr.style.display=pass?'':'none';if(pass)n++;});document.getElementById('contact-count').textContent=n+' of '+rows.length+' contacts';});})();
</script>
</div>`;
res.type('html').send(layout('Contacts', body, { activeNav: '/contacts', q }));
} catch (e) {
res.status(500).type('html').send(layout('Error', `<div class="page-content"><div class="alert alert-danger">${esc(e.message)}</div></div>`));
}
});
// ═══════════════════════════════════════════════════════════════════════════════
// GET /prospects
// ═══════════════════════════════════════════════════════════════════════════════
router.get('/prospects', async (req, res) => {
const market = ['CA', 'AZ', 'ALL'].includes(req.query.market) ? req.query.market : 'ALL';
try {
const params = [T.VERIFIED_STATUSES];
const conds = [`NOT EXISTS (SELECT 1 FROM ad_sightings s WHERE s.organization_id = o.id AND s.relationship_status = ANY($1))`];
if (market !== 'ALL') { params.push(market); conds.push(`o.headquarters_state = $${params.length}`); }
const rows = await safeQ(`
SELECT o.id, o.display_name, o.domain, o.headquarters_state, o.headquarters_city,
o.advertiser_categories, o.last_seen_at, o.created_at,
opp.score, opp.computed_at AS score_at
FROM organizations o
LEFT JOIN opportunity_scores opp ON opp.organization_id = o.id
AND opp.computed_at = (SELECT MAX(x.computed_at) FROM opportunity_scores x WHERE x.organization_id = o.id)
WHERE ${conds.join(' AND ')}
ORDER BY opp.score DESC NULLS LAST, o.last_seen_at DESC NULLS LAST
LIMIT 200`, params);
const isDemo = rows.length === 0;
const tableRows = rows.map((r, i) => {
const cats = (() => { try { return Array.isArray(r.advertiser_categories) ? r.advertiser_categories : JSON.parse(r.advertiser_categories || '[]'); } catch (_) { return []; } })();
return `<tr>
<td class="num">${i + 1}</td>
<td><a href="/advertisers/${esc(r.id)}">${esc(r.display_name)}</a></td>
<td>${esc(r.headquarters_state || '—')}</td>
<td style="font-size:13px;max-width:200px;white-space:normal">${esc(cats.slice(0, 2).join(', ') || '—')}</td>
<td>${scoreBadge(r.score)}</td>
<td>${fmtDateOnly(r.score_at)}</td>
<td>${r.domain ? `<a href="https://${esc(r.domain)}" target="_blank" rel="noopener noreferrer">${esc(r.domain)} ↗</a>` : '<span class="miss">—</span>'}</td>
<td>${r.id ? `<button class="btn btn-ghost" style="font-size:12px;padding:4px 8px;min-height:32px" data-smw-org="${esc(r.id)}" data-smw-name="${esc(r.display_name)}">Why?</button>` : ''}</td>
</tr>`;
}).join('');
const body = `<div class="page-content">
<h1>Top Prospects</h1>
<p style="color:var(--ink-muted);font-size:15px">Companies ranked by RENTV advertising opportunity score — not verified advertisers yet.</p>
${isDemo ? demoBanner : ''}
<div class="tbl-wrap">
<table class="adv-tbl" aria-label="Prospects">
<thead><tr><th>#</th><th>Company</th><th>State</th><th>Category</th><th>Score</th><th>Scored</th><th>Website</th><th>Why?</th></tr></thead>
<tbody>${tableRows || '<tr><td colspan="8"><p class="miss" style="padding:20px">No prospects yet — run the scoring job.</p></td></tr>'}</tbody>
</table>
</div>
</div>`;
res.type('html').send(layout('Prospects', body, { activeNav: '/prospects', market }));
} catch (e) {
res.status(500).type('html').send(layout('Error', `<div class="page-content"><div class="alert alert-danger">${esc(e.message)}</div></div>`));
}
});
// ═══════════════════════════════════════════════════════════════════════════════
// GET /analytics
// ═══════════════════════════════════════════════════════════════════════════════
router.get('/analytics', async (req, res) => {
try {
const [ga4, conn] = await Promise.all([
safeQ(`SELECT * FROM ga4_daily_metrics ORDER BY metric_date DESC LIMIT 30`),
safeQ(`SELECT * FROM analytics_connections WHERE kind = 'GA4' LIMIT 1`),
]);
const isDemo = ga4.length === 0 || (ga4[0] && ga4[0].is_demo);
const connStatus = conn[0] ? conn[0].status : 'NOT_CONNECTED';
const tableRows = ga4.map(r => `<tr>
<td>${fmtDateOnly(r.metric_date)}</td>
<td class="num">${(r.sessions || 0).toLocaleString()}</td>
<td class="num">${(r.total_users || 0).toLocaleString()}</td>
<td class="num">${(r.new_users || 0).toLocaleString()}</td>
<td class="num">${(r.engaged_sessions || 0).toLocaleString()}</td>
<td class="num">${r.engagement_rate ? (r.engagement_rate * 100).toFixed(1) + '%' : '—'}</td>
<td class="num">${r.avg_engagement_time ? Number(r.avg_engagement_time).toFixed(1) + 's' : '—'}</td>
<td class="num">${(r.views || 0).toLocaleString()}</td>
</tr>`).join('');
const body = `<div class="page-content">
<h1>Analytics — GA4</h1>
<div class="alert alert-info" style="margin-bottom:16px">
Connection status: <strong>${esc(connStatus)}</strong>.
${connStatus !== 'CONNECTED' ? `<a href="/settings">Connect GA4 →</a>` : 'Import running.'}
</div>
${isDemo ? demoBanner : ''}
<div class="tbl-wrap">
<table class="adv-tbl" aria-label="GA4 daily metrics">
<thead><tr><th>Date</th><th>Sessions</th><th>Users</th><th>New Users</th><th>Engaged</th><th>Eng. Rate</th><th>Avg Time</th><th>Views</th></tr></thead>
<tbody>${tableRows || '<tr><td colspan="8"><p class="miss" style="padding:20px">No GA4 data. <a href="/imports">Import CSV</a> or <a href="/settings">connect API</a>.</p></td></tr>'}</tbody>
</table>
</div>
<p style="margin-top:16px"><a href="/search-intelligence" class="btn btn-outline">View Search Intelligence →</a></p>
</div>`;
res.type('html').send(layout('Analytics', body, { activeNav: '/analytics' }));
} catch (e) {
res.status(500).type('html').send(layout('Error', `<div class="page-content"><div class="alert alert-danger">${esc(e.message)}</div></div>`));
}
});
// ═══════════════════════════════════════════════════════════════════════════════
// GET /search-intelligence
// ═══════════════════════════════════════════════════════════════════════════════
router.get('/search-intelligence', async (req, res) => {
try {
const rows = await safeQ(`
SELECT query, SUM(clicks) AS clicks, SUM(impressions) AS impressions,
AVG(ctr) AS ctr, AVG(position) AS position, MAX(is_brand) AS is_brand
FROM gsc_query_metrics
WHERE metric_date >= CURRENT_DATE - 28
GROUP BY query ORDER BY impressions DESC NULLS LAST LIMIT 100`);
const isDemo = rows.length === 0;
const tableRows = rows.map(r => `<tr>
<td>${esc(r.query || '—')}</td>
<td class="num">${Number(r.impressions || 0).toLocaleString()}</td>
<td class="num">${Number(r.clicks || 0).toLocaleString()}</td>
<td class="num">${r.ctr ? (r.ctr * 100).toFixed(1) + '%' : '—'}</td>
<td class="num">${r.position ? Number(r.position).toFixed(1) : '—'}</td>
<td>${r.is_brand ? '<span class="badge badge-verified-media">Brand</span>' : '<span class="badge badge-panelist">Non-brand</span>'}</td>
</tr>`).join('');
const body = `<div class="page-content">
<h1>Search Intelligence — GSC</h1>
<p style="color:var(--ink-muted);font-size:15px">Organic search data from Google Search Console. Last 28 days.</p>
${isDemo ? demoBanner : ''}
<div class="tbl-controls">
<input class="tbl-search" id="gsc-search" type="search" placeholder="Search queries…" aria-label="Search queries">
<span class="tbl-count" id="gsc-count">${rows.length} queries</span>
</div>
<div class="tbl-wrap">
<table class="adv-tbl" id="gsc-tbl" aria-label="GSC queries">
<thead><tr><th>Query</th><th>Impressions</th><th>Clicks</th><th>CTR</th><th>Avg Position</th><th>Type</th></tr></thead>
<tbody>${tableRows || '<tr><td colspan="6"><p class="miss" style="padding:20px">No GSC data. <a href="/imports">Import CSV</a> or <a href="/settings">connect API</a>.</p></td></tr>'}</tbody>
</table>
</div>
<script>(function(){var s=document.getElementById('gsc-search'),rows=Array.from(document.querySelectorAll('#gsc-tbl tbody tr'));if(!s)return;s.addEventListener('input',function(){var q=s.value.toLowerCase().split(/\s+/).filter(Boolean),n=0;rows.forEach(function(tr){var pass=q.every(function(t){return tr.textContent.toLowerCase().includes(t);});tr.style.display=pass?'':'none';if(pass)n++;});document.getElementById('gsc-count').textContent=n+' of '+rows.length+' queries';});})();</script>
</div>`;
res.type('html').send(layout('Search Intelligence', body, { activeNav: '/search-intelligence' }));
} catch (e) {
res.status(500).type('html').send(layout('Error', `<div class="page-content"><div class="alert alert-danger">${esc(e.message)}</div></div>`));
}
});
// ═══════════════════════════════════════════════════════════════════════════════
// GET /media-kit (§27)
// ═══════════════════════════════════════════════════════════════════════════════
router.get('/media-kit', async (req, res) => {
try {
const [audiences, rates] = await Promise.all([
safeQ(`SELECT * FROM rentv_audience_snapshots ORDER BY observed_at DESC`),
safeQ(`SELECT * FROM rentv_rate_snapshots ORDER BY product_key, observed_at DESC`),
]);
const isDemo = audiences.length === 0 && rates.length === 0;
// Group audience snapshots by metric_key to show conflict warning when >1 value
const byKey = {};
audiences.forEach(a => { (byKey[a.metric_key] = byKey[a.metric_key] || []).push(a); });
const audienceHtml = Object.entries(byKey).map(([key, snaps]) => {
const latest = snaps[0];
const hasConflict = snaps.length > 1 &&
snaps.some(s => String(s.value_numeric || s.value_text) !== String(latest.value_numeric || latest.value_text));
return `
<div class="mk-metric">
<div class="mk-val">${esc(latest.value_text || (latest.value_numeric ? Number(latest.value_numeric).toLocaleString() : '—'))}</div>
<div class="mk-label">${esc(latest.metric_label)}</div>
<div class="mk-source">As of ${fmtDateOnly(latest.observed_at)} · ${esc(latest.source_key || 'manual entry')}</div>
${hasConflict ? `<div class="conflict-warning" style="margin-top:8px;font-size:12px">
Multiple values recorded. Showing most recent. History:
${snaps.map(s => `${esc(s.value_text || String(s.value_numeric))} (${fmtDateOnly(s.observed_at)})`).join(' / ')}
</div>` : ''}
</div>`;
}).join('');
// Group rates by product
const byProduct = {};
rates.forEach(r => { (byProduct[r.product_key] = byProduct[r.product_key] || []).push(r); });
const ratesHtml = Object.entries(byProduct).map(([pk, snaps]) => {
const latest = snaps[0];
const hasMultiple = snaps.length > 1;
return `<div class="settings-card" style="margin-bottom:12px">
<h3>${esc(latest.product_label)}</h3>
<div style="display:flex;gap:16px;flex-wrap:wrap;align-items:baseline">
<span style="font-size:28px;font-weight:900">$${latest.rate ? Number(latest.rate).toLocaleString() : '—'}</span>
<span style="color:var(--ink-muted);font-size:15px">${esc(latest.unit || '')}</span>
</div>
<div class="mk-source">Rate as of ${fmtDateOnly(latest.observed_at)} · ${esc(latest.source_key || 'manual')}</div>
${latest.package_notes ? `<p style="font-size:14px;color:var(--ink-muted);margin-top:8px">${esc(latest.package_notes)}</p>` : ''}
${hasMultiple ? `<details style="margin-top:10px"><summary style="cursor:pointer;font-size:13px;color:var(--accent)">Rate history (${snaps.length} snapshots)</summary>
<div style="margin-top:8px">${snaps.map(s => `<div style="font-size:13px;padding:4px 0;border-bottom:1px solid var(--border)">${fmtDateOnly(s.observed_at)}: <strong>$${s.rate ? Number(s.rate).toLocaleString() : '—'}</strong> ${esc(s.unit || '')} — ${esc(s.source_key || 'manual')}</div>`).join('')}</div>
</details>` : ''}
</div>`;
}).join('');
const body = `<div class="page-content">
<h1>Media Kit</h1>
<p style="color:var(--ink-muted);font-size:15px">Sourced, dated audience and rate data. All figures show their source and observation date. Conflicting snapshots are shown together — not merged.</p>
${isDemo ? demoBanner : ''}
<h2 style="margin-bottom:16px">Audience Snapshots</h2>
${audienceHtml || '<p class="miss">No audience data. <a href="/imports">Import or add manually</a>.</p>'}
<div class="mk-snapshot">${audienceHtml}</div>
<h2 style="margin:28px 0 16px">Rate Card</h2>
${ratesHtml || '<p class="miss">No rate snapshots. Seed data or import a rate card.</p>'}
<div style="margin-top:24px">
<button onclick="window.print()" class="btn btn-outline">Print Media Kit</button>
</div>
</div>`;
res.type('html').send(layout('Media Kit', body, { activeNav: '/media-kit' }));
} catch (e) {
res.status(500).type('html').send(layout('Error', `<div class="page-content"><div class="alert alert-danger">${esc(e.message)}</div></div>`));
}
});
// ═══════════════════════════════════════════════════════════════════════════════
// GET /sources
// ═══════════════════════════════════════════════════════════════════════════════
router.get('/sources', async (req, res) => {
try {
const rows = await safeQ(`
SELECT sp.*,
(SELECT COUNT(*) FROM sources s WHERE s.source_policy_id = sp.id) AS item_count
FROM source_policies sp ORDER BY sp.display_name`);
const isDemo = rows.length === 0;
const tableRows = rows.map(r => `<tr>
<td>${esc(r.display_name)}</td>
<td>${esc(r.source_key)}</td>
<td>${esc(r.access_method || '—')}</td>
<td>${r.enabled ? '<span class="badge badge-verified-advertiser">Enabled</span>' : '<span class="badge badge-disqualified">Disabled</span>'}</td>
<td>${r.allows_automated_access ? 'Yes' : 'No'}</td>
<td class="num"><a href="/sources">${Number(r.item_count || 0).toLocaleString()}</a></td>
<td>${r.base_url ? `<a href="${esc(r.base_url)}" target="_blank" rel="noopener noreferrer">${esc(r.base_url)} ↗</a>` : '<span class="miss">—</span>'}</td>
<td><span class="when-chip" title="${esc(r.created_at ? new Date(r.created_at).toISOString() : '')}">${fmtDate(r.created_at)}</span></td>
</tr>`).join('');
const body = `<div class="page-content">
<h1>Sources</h1>
${isDemo ? demoBanner : ''}
<div class="tbl-wrap">
<table class="adv-tbl" aria-label="Source policies">
<thead><tr><th>Name</th><th>Key</th><th>Access Method</th><th>Status</th><th>Automated?</th><th>Items</th><th>URL</th><th>Created</th></tr></thead>
<tbody>${tableRows || '<tr><td colspan="8"><p class="miss" style="padding:20px">No source policies yet. Run <code>npm run db:seed</code>.</p></td></tr>'}</tbody>
</table>
</div>
</div>`;
res.type('html').send(layout('Sources', body, { activeNav: '/sources' }));
} catch (e) {
res.status(500).type('html').send(layout('Error', `<div class="page-content"><div class="alert alert-danger">${esc(e.message)}</div></div>`));
}
});
// ═══════════════════════════════════════════════════════════════════════════════
// GET /imports
// ═══════════════════════════════════════════════════════════════════════════════
router.get('/imports', async (req, res) => {
try {
const runs = await safeQ(`SELECT * FROM analytics_import_runs ORDER BY started_at DESC LIMIT 20`);
const runsHtml = runs.map(r => `<div class="audit-row">
<div style="flex:0 0 180px"><span class="when-chip" title="${esc(r.started_at ? new Date(r.started_at).toISOString() : '')}">${fmtDate(r.started_at)}</span></div>
<div>
<span class="audit-action">${esc(r.kind)}</span>
<span class="badge ${r.status === 'DONE' ? 'badge-verified-advertiser' : r.status === 'ERROR' ? 'badge-disqualified' : 'badge-likely-prospect'}" style="margin-left:8px">${esc(r.status)}</span>
${r.row_count != null ? `<span class="audit-meta"> — ${Number(r.row_count).toLocaleString()} rows</span>` : ''}
${r.error ? `<span class="audit-meta" style="color:var(--danger)"> — ${esc(r.error)}</span>` : ''}
</div>
</div>`).join('');
const body = `<div class="page-content">
<h1>Imports</h1>
<p style="color:var(--ink-muted);font-size:15px">Upload CSV, XLSX, EML, PDF, or image files. Connect GA4, GSC, or Gmail via <a href="/settings">Settings</a>.</p>
<div class="dash-grid" style="margin-bottom:28px">
${[
['GA4 CSV', 'Upload a GA4 export CSV', '/imports#ga4'],
['Search Console CSV', 'Upload GSC export CSV', '/imports#gsc'],
['Advertiser Spreadsheet', 'Upload advertiser list CSV/XLSX', '/imports#advertisers'],
['Conference Sponsor List', 'Upload sponsor list CSV', '/imports#conferences'],
['Ad Image / PDF', 'Upload ad creative or email', '/imports#ad'],
].map(([title, desc, href]) => `
<div class="dash-card">
<h3>${esc(title)}</h3>
<p style="font-size:15px;color:var(--ink-muted)">${esc(desc)}</p>
<form method="POST" action="/api/v1/imports" enctype="multipart/form-data" style="margin-top:10px">
<input type="hidden" name="kind" value="${esc(title.toUpperCase().replace(/\s/g,'_'))}">
<input type="file" name="file" style="font-size:14px;margin-bottom:10px;display:block" accept=".csv,.xlsx,.eml,.pdf,.jpg,.png,.webp">
<button type="submit" class="btn btn-primary" style="font-size:15px">Upload ${esc(title)}</button>
</form>
</div>`).join('')}
</div>
<h2>Recent import runs</h2>
${runs.length ? runsHtml : '<p class="miss">No imports yet.</p>'}
</div>`;
res.type('html').send(layout('Imports', body, { activeNav: '/imports' }));
} catch (e) {
res.status(500).type('html').send(layout('Error', `<div class="page-content"><div class="alert alert-danger">${esc(e.message)}</div></div>`));
}
});
// ═══════════════════════════════════════════════════════════════════════════════
// GET /review
// ═══════════════════════════════════════════════════════════════════════════════
router.get('/review', async (req, res) => {
try {
const items = await safeQ(`
SELECT s.id, s.relationship_status, s.verification_status, s.observed_at,
s.source_page_url, s.headline, s.confidence, s.created_at,
o.display_name AS org_name, o.id AS org_id
FROM ad_sightings s
LEFT JOIN organizations o ON o.id = s.organization_id
WHERE s.verification_status = 'UNVERIFIED'
ORDER BY s.created_at DESC LIMIT 100`);
const isDemo = items.length === 0;
const cards = items.map(s => `
<div class="dash-card" style="margin-bottom:14px">
<div style="display:flex;align-items:flex-start;justify-content:space-between;gap:12px;flex-wrap:wrap">
<div>
${whenChip(s.created_at)}
<div style="margin-top:8px"><a href="/advertisers/${esc(s.org_id)}" style="font-size:18px;font-weight:700">${esc(s.org_name || '—')}</a></div>
<div style="margin-top:6px;display:flex;gap:8px;flex-wrap:wrap">
${statusBadge(s.relationship_status)}
<span class="badge badge-research-needed">Unverified</span>
${s.confidence ? `<span class="badge badge-panelist">Conf: ${Math.round(s.confidence * 100)}%</span>` : ''}
</div>
${s.headline ? `<p style="font-size:15px;margin:8px 0 0;color:var(--ink-muted)">${esc(s.headline)}</p>` : ''}
${s.source_page_url ? `<p style="margin:6px 0 0"><a href="${esc(s.source_page_url)}" target="_blank" rel="noopener noreferrer" style="font-size:14px">Source ↗</a> · ${fmtDateOnly(s.observed_at)}</p>` : ''}
</div>
<div style="display:flex;gap:8px;flex-wrap:wrap;align-items:center">
<button class="btn btn-outline" onclick="reviewAction('${esc(s.id)}','verify')" style="font-size:15px">Verify</button>
<button class="btn btn-ghost" onclick="reviewAction('${esc(s.id)}','reject')" style="font-size:15px">Reject</button>
<a href="/ads/${esc(s.id)}" class="btn btn-ghost" style="font-size:15px">Details</a>
</div>
</div>
</div>`).join('');
const body = `<div class="page-content">
<h1>Review Queue</h1>
<p style="color:var(--ink-muted);font-size:15px">Verify or reject unverified ad sightings. Do not mark a speaker/panelist as a sponsor without separate evidence.</p>
${isDemo ? demoBanner : ''}
${cards || '<div class="empty-state"><div class="em-icon">✓</div><p>All sightings reviewed. No items pending.</p></div>'}
<script>function reviewAction(id,a){fetch('/api/v1/review/'+id+'/'+a,{method:'POST',headers:{'Content-Type':'application/json'},body:'{}'}).then(r=>r.json()).then(d=>{if(d.ok)location.reload();else alert(d.message||'Error');}).catch(e=>alert(e.message));}</script>
</div>`;
res.type('html').send(layout('Review', body, { activeNav: '/review' }));
} catch (e) {
res.status(500).type('html').send(layout('Error', `<div class="page-content"><div class="alert alert-danger">${esc(e.message)}</div></div>`));
}
});
// ═══════════════════════════════════════════════════════════════════════════════
// GET /exports
// ═══════════════════════════════════════════════════════════════════════════════
router.get('/exports', async (req, res) => {
try {
const rows = await safeQ(`SELECT * FROM exports ORDER BY created_at DESC LIMIT 20`);
const tableRows = rows.map(r => `<tr>
<td><span class="when-chip" title="${esc(r.created_at ? new Date(r.created_at).toISOString() : '')}">${fmtDate(r.created_at)}</span></td>
<td>${esc(r.kind)}</td>
<td><span class="badge ${r.status === 'DONE' ? 'badge-verified-advertiser' : r.status === 'ERROR' ? 'badge-disqualified' : 'badge-likely-prospect'}">${esc(r.status)}</span></td>
<td>${r.status === 'DONE' && r.object_key ? `<a href="/assets/${esc(r.object_key)}" class="btn btn-outline" style="font-size:13px;min-height:36px">Download</a>` : '<span class="miss">—</span>'}</td>
</tr>`).join('');
const body = `<div class="page-content">
<h1>Exports</h1>
<div class="alert alert-info" style="margin-bottom:20px">Use <strong>Download Everything</strong> in the header to create a full ZIP export. Exports run as background jobs.</div>
<div style="margin-bottom:24px">
<button id="start-export-btn" class="btn btn-primary" style="font-size:16px" onclick="startExportPage()">Create New Export</button>
<span id="export-status-msg" style="margin-left:12px;font-size:15px;color:var(--ink-muted)"></span>
</div>
<h2>Export history</h2>
${rows.length ? `<div class="tbl-wrap"><table class="adv-tbl"><thead><tr><th>Created</th><th>Kind</th><th>Status</th><th>Download</th></tr></thead><tbody>${tableRows}</tbody></table></div>`
: '<p class="miss">No exports yet.</p>'}
<script>
function startExportPage(){
var btn=document.getElementById('start-export-btn');
var msg=document.getElementById('export-status-msg');
btn.disabled=true; btn.textContent='Creating…'; msg.textContent='';
fetch('/api/v1/exports',{method:'POST',headers:{'Content-Type':'application/json'},body:JSON.stringify({kind:'DOWNLOAD_EVERYTHING'})})
.then(function(r){return r.json();})
.then(function(d){msg.textContent=d.message||'Export queued — refresh this page to check status.';btn.textContent='Create New Export';btn.disabled=false;})
.catch(function(e){msg.textContent='Error: '+e.message;btn.textContent='Create New Export';btn.disabled=false;});
}
</script>
</div>`;
res.type('html').send(layout('Exports', body, { activeNav: '/exports' }));
} catch (e) {
res.status(500).type('html').send(layout('Error', `<div class="page-content"><div class="alert alert-danger">${esc(e.message)}</div></div>`));
}
});
// ═══════════════════════════════════════════════════════════════════════════════
// GET /settings
// ═══════════════════════════════════════════════════════════════════════════════
router.get('/settings', async (req, res) => {
try {
const conns = await safeQ(`SELECT * FROM analytics_connections ORDER BY kind`);
const connHtml = ['GA4', 'GSC', 'GOOGLE_ADS'].map(kind => {
const c = conns.find(x => x.kind === kind);
return `<div class="settings-card">
<h3>${esc(kind)}</h3>
<div style="display:flex;align-items:center;gap:12px;flex-wrap:wrap">
<span class="badge ${c && c.status === 'CONNECTED' ? 'badge-verified-advertiser' : 'badge-research-needed'}">${c ? esc(c.status) : 'NOT_CONNECTED'}</span>
${c && c.last_import_at ? `<span class="miss" style="font-size:14px">Last import: ${fmtDate(c.last_import_at)}</span>` : ''}
</div>
<p style="font-size:14px;color:var(--ink-muted);margin-top:8px">Configure via environment variables in <code>.env</code>. See <code>docs/ANALYTICS_CONNECTORS.md</code>.</p>
<form method="POST" action="/api/v1/research/jobs" style="margin-top:10px">
<input type="hidden" name="type" value="${esc(kind + '_IMPORT')}">
<button type="submit" class="btn btn-outline" style="font-size:14px">Trigger ${esc(kind)} import</button>
</form>
</div>`;
}).join('');
const body = `<div class="page-content">
<h1>Settings</h1>
<h2 style="margin:0 0 16px">Analytics Connections</h2>
${connHtml}
<h2 style="margin:24px 0 16px">Research Jobs</h2>
<div class="settings-card">
<h3>Run research jobs</h3>
<div style="display:flex;flex-wrap:wrap;gap:10px;margin-top:10px">
${['california', 'arizona', 'conferences'].map(j => `
<form method="POST" action="/api/v1/research/jobs">
<input type="hidden" name="type" value="${j}">
<button type="submit" class="btn btn-outline" style="font-size:14px">Research: ${j.charAt(0).toUpperCase() + j.slice(1)}</button>
</form>`).join('')}
</div>
<p style="font-size:14px;color:var(--ink-muted);margin-top:10px">Jobs run according to source policies. Check <a href="/admin/jobs">Admin → Jobs</a> for status.</p>
</div>
<h2 style="margin:24px 0 16px">Score Weights</h2>
<div class="settings-card">
<p style="font-size:15px;color:var(--ink-muted)">Default weights are configured in <code>lib/scoring.js</code>. Admin weight overrides stored in <code>opportunity_scores.factors</code>. See documentation for details.</p>
</div>
</div>`;
res.type('html').send(layout('Settings', body, { activeNav: '/settings' }));
} catch (e) {
res.status(500).type('html').send(layout('Error', `<div class="page-content"><div class="alert alert-danger">${esc(e.message)}</div></div>`));
}
});
// ═══════════════════════════════════════════════════════════════════════════════
// GET /admin/jobs
// ═══════════════════════════════════════════════════════════════════════════════
router.get('/admin/jobs', async (req, res) => {
try {
const runs = await safeQ(`SELECT * FROM ingestion_runs ORDER BY started_at DESC LIMIT 50`);
const isDemo = runs.length === 0;
const cards = runs.map(r => `
<div class="dash-card" style="margin-bottom:12px">
<div style="display:flex;align-items:flex-start;justify-content:space-between;gap:12px;flex-wrap:wrap">
<div>
${whenChip(r.started_at)}
<div style="margin-top:8px;font-size:18px;font-weight:700">${esc(r.source_key || 'General')}</div>
<div style="margin-top:6px;display:flex;gap:8px;flex-wrap:wrap">
<span class="badge ${r.status === 'DONE' ? 'badge-verified-advertiser' : r.status === 'ERROR' ? 'badge-disqualified' : r.status === 'RUNNING' ? 'badge-verified-media' : 'badge-panelist'}">${esc(r.status)}</span>
${r.dry_run ? '<span class="badge badge-content-partner">Dry run</span>' : ''}
</div>
${r.error ? `<p style="font-size:14px;color:var(--danger);margin:6px 0 0">${esc(r.error)}</p>` : ''}
${r.finished_at ? `<p style="font-size:13px;color:var(--ink-faint);margin:4px 0 0">Finished: ${fmtDate(r.finished_at)}</p>` : ''}
</div>
</div>
</div>`).join('');
const body = `<div class="page-content">
<h1>Admin — Jobs</h1>
${isDemo ? demoBanner : ''}
${cards || '<div class="empty-state"><p>No job runs recorded yet.</p></div>'}
</div>`;
res.type('html').send(layout('Admin: Jobs', body, { activeNav: '/admin/jobs' }));
} catch (e) {
res.status(500).type('html').send(layout('Error', `<div class="page-content"><div class="alert alert-danger">${esc(e.message)}</div></div>`));
}
});
// ═══════════════════════════════════════════════════════════════════════════════
// GET /admin/audit
// ═══════════════════════════════════════════════════════════════════════════════
router.get('/admin/audit', async (req, res) => {
try {
const rows = await safeQ(`SELECT * FROM audit_logs ORDER BY created_at DESC LIMIT 200`);
const isDemo = rows.length === 0;
const rowsHtml = rows.map(r => `
<div class="audit-row">
<div style="flex:0 0 220px">${whenChip(r.created_at)}</div>
<div>
<span class="audit-action">${esc(r.action)}</span>
${r.entity_table ? `<span class="audit-meta"> — ${esc(r.entity_table)}</span>` : ''}
${r.entity_id ? `<span class="audit-meta"> / ${esc(String(r.entity_id).slice(0,8))}…</span>` : ''}
<span class="audit-meta"> — by ${esc(r.actor || 'system')}</span>
</div>
</div>`).join('');
const body = `<div class="page-content">
<h1>Admin — Audit Log</h1>
${isDemo ? demoBanner : ''}
${rowsHtml || '<div class="empty-state"><p>No audit entries yet.</p></div>'}
</div>`;
res.type('html').send(layout('Admin: Audit', body, { activeNav: '/admin/audit' }));
} catch (e) {
res.status(500).type('html').send(layout('Error', `<div class="page-content"><div class="alert alert-danger">${esc(e.message)}</div></div>`));
}
});
module.exports = router;