spb/wp-spboucher-ai

1import { sql } from "@/db";2import { PageTitle } from "@/components/admin-ui";3export default async function Analytics() {4  const [top, searches, referrers, byDay] = await Promise.all([5    sql`SELECT p.identifier, p.title, p.view_count::int AS views, p.download_count::int AS downloads, (SELECT count(*)::int FROM events e WHERE e.paper_id = p.id AND e.kind = 'view' AND NOT e.is_bot AND e.day > current_date - 7) AS views_7d FROM papers p WHERE p.status = 'published' ORDER BY p.view_count DESC, p.download_count DESC LIMIT 25`,6    sql`SELECT query, mode, count(*)::int AS n, avg(results)::int AS avg_results, avg(took_ms)::int AS ms FROM search_log WHERE at > now() - interval '90 days' GROUP BY query, mode ORDER BY n DESC LIMIT 40`,7    sql`SELECT referrer_host, count(*)::int AS n FROM events WHERE NOT is_bot AND referrer_host IS NOT NULL AND day > current_date - 30 GROUP BY referrer_host ORDER BY n DESC LIMIT 20`,8    sql`SELECT day::text, count(*) FILTER (WHERE kind = 'view')::int AS views, count(*) FILTER (WHERE kind = 'download')::int AS downloads, count(*) FILTER (WHERE is_bot)::int AS bots FROM events WHERE day > current_date - 30 GROUP BY day ORDER BY day DESC`,9  ]);10  return (<div><PageTitle title="Analytics" /><p className="meta mb-4">Views = distinct daily visitors per paper page (sha256 of IP + user agent + daily salt, not reversible; bots excluded). Downloads = manuscript requests. Raw events are kept 400 days; daily aggregates are kept indefinitely. No cookies, no third-party analytics.</p>11    <div className="grid xl:grid-cols-2 gap-6">12      <section className="card p-4"><h2 className="text-lg mb-2">Most viewed papers (all time)</h2><table className="data"><thead><tr><th>ID</th><th>Title</th><th>Views</th><th>7 d</th><th>Downloads</th></tr></thead><tbody>{top.map((p) => <tr key={p.identifier}><td className="font-mono text-navy">{p.identifier}</td><td className="text-fg-2">{p.title}</td><td>{p.views}</td><td>{p.views_7d}</td><td>{p.downloads}</td></tr>)}</tbody></table></section>13      <section className="card p-4"><h2 className="text-lg mb-2">Daily events (raw, last 30 days)</h2><table className="data"><thead><tr><th>Day</th><th>Views</th><th>Downloads</th><th>Bot hits</th></tr></thead><tbody>{byDay.map((d) => <tr key={d.day}><td>{d.day}</td><td>{d.views}</td><td>{d.downloads}</td><td className="text-fg-3">{d.bots}</td></tr>)}</tbody></table></section>14      <section className="card p-4"><h2 className="text-lg mb-2">Searches (90 days)</h2><table className="data"><thead><tr><th>Query</th><th>Mode</th><th>Count</th><th>Avg results</th><th>ms</th></tr></thead><tbody>{searches.map((s, i) => <tr key={i}><td>{s.query}</td><td>{s.mode}</td><td>{s.n}</td><td>{s.avg_results}</td><td>{s.ms}</td></tr>)}</tbody></table></section>15      <section className="card p-4"><h2 className="text-lg mb-2">Referrers (30 days)</h2><table className="data"><thead><tr><th>Host</th><th>Visits</th></tr></thead><tbody>{referrers.map((r) => <tr key={r.referrer_host}><td>{r.referrer_host}</td><td>{r.n}</td></tr>)}</tbody></table></section>16    </div></div>);17}18