`src/jobs/morning_corpus_summary.ts`

106 lines
// Daily 9 AM corpus summary — one-line "where the corpus stands" email
// to Steve. Different from morning_feature_email (8:07): that surfaces a
// single feature; this one summarizes the full pipeline state.

import 'dotenv/config';
import { pool, query } from '../../db/pool.ts';

const TO = process.env.MORNING_TO || 'steveabramsdesigns@gmail.com';
const GEORGE = process.env.GEORGE_URL || 'http://localhost:9850/api/send';
const AUTH = 'Basic ' + Buffer.from('admin:DWSecure2024!').toString('base64');
const DRY = process.argv.includes('--dry-run');

const esc = (s: any) => String(s ?? '').replace(/[&<>"']/g, c => ({'&':'&amp;','<':'&lt;','>':'&gt;','"':'&quot;',"'":'&#39;'}[c]!));
const fmt = (n: any) => Number(n).toLocaleString('en-US');

async function main() {
  const stats = (await query(`
    SELECT
      (SELECT count(*) FROM businesses) AS biz,
      (SELECT count(*) FROM magazine_features) AS feat,
      (SELECT count(*) FROM magazine_features WHERE status='published') AS pub,
      (SELECT count(*) FROM magazine_features WHERE status='draft') AS draft,
      (SELECT count(*) FROM magazine_features WHERE generated_at > NOW() - INTERVAL '24 hours') AS gen_24h,
      (SELECT count(*) FROM magazine_features WHERE published_at > NOW() - INTERVAL '24 hours') AS pub_24h,
      (SELECT count(*) FROM reader_feedback WHERE NOT reviewed) AS fb_unread,
      (SELECT count(*) FROM sponsor_inquiries WHERE status='new') AS inq_open
  `)).rows[0];

  const dupes = (await query(`
    SELECT count(*) AS groups, sum(n) AS affected
    FROM (SELECT count(*) AS n FROM magazine_features WHERE headline IS NOT NULL GROUP BY headline HAVING count(*) > 1) g
  `)).rows[0];

  const top = (await query<any>(`
    SELECT mf.id, mf.headline, b.name FROM magazine_features mf JOIN businesses b ON b.id=mf.business_id
    WHERE mf.status='published' ORDER BY COALESCE(mf.taps,0) DESC, COALESCE(mf.views,0) DESC, mf.id DESC LIMIT 3
  `)).rows;

  const fresh = (await query<any>(`
    SELECT mf.headline, b.name FROM magazine_features mf JOIN businesses b ON b.id=mf.business_id
    WHERE mf.headline IS NOT NULL ORDER BY mf.generated_at DESC LIMIT 5
  `)).rows;

  const cover = (await query<any>(`
    SELECT mf.headline, b.name AS biz FROM magazine_issues i
    JOIN magazine_features mf ON mf.id=i.cover_feature_id
    JOIN businesses b ON b.id=mf.business_id
    WHERE i.issue_month = to_char(now(),'YYYY-MM')
  `)).rows[0];

  const covPct = stats.biz > 0 ? +(Number(stats.feat) / Number(stats.biz) * 100).toFixed(2) : 0;

  const html = `<div style="font-family:Georgia,serif;max-width:580px;color:#1a1815;background:#faf6ee;padding:32px">
<div style="font-size:9px;letter-spacing:.4em;text-transform:uppercase;color:#8a6d3b;font-family:Helvetica,sans-serif;font-weight:500">The Corridor desk · ${new Date().toLocaleDateString('en-US',{weekday:'long',month:'long',day:'numeric'})}</div>
<h1 style="font-family:Georgia,serif;font-style:italic;font-weight:500;font-size:30px;margin:14px 0 8px;line-height:1.05">Corpus is at <span style="color:#6a3a1a">${fmt(stats.feat)}</span> features.</h1>

<table style="border-collapse:collapse;font-size:14px;margin-top:18px">
  <tr><td style="padding:4px 14px 4px 0;color:#888;width:140px">Businesses</td><td><b>${fmt(stats.biz)}</b></td></tr>
  <tr><td style="padding:4px 14px 4px 0;color:#888">Features (total)</td><td><b>${fmt(stats.feat)}</b> · ${covPct}% coverage</td></tr>
  <tr><td style="padding:4px 14px 4px 0;color:#888">Published</td><td>${fmt(stats.pub)}</td></tr>
  <tr><td style="padding:4px 14px 4px 0;color:#888">Drafts pending review</td><td>${fmt(stats.draft)}</td></tr>
  <tr><td style="padding:4px 14px 4px 0;color:#888">Generated last 24h</td><td>${fmt(stats.gen_24h)}</td></tr>
  <tr><td style="padding:4px 14px 4px 0;color:#888">Published last 24h</td><td>${fmt(stats.pub_24h)}</td></tr>
  ${Number(dupes.groups) > 0 ? `<tr><td style="padding:4px 14px 4px 0;color:#888">Duplicate-headline groups</td><td>${dupes.groups} (${dupes.affected} features) — auto-dedupe runs 3:33 AM</td></tr>` : ''}
  ${Number(stats.fb_unread) > 0 ? `<tr><td style="padding:4px 14px 4px 0;color:#888">Unread feedback</td><td><b style="color:#b66565">${fmt(stats.fb_unread)}</b></td></tr>` : ''}
  ${Number(stats.inq_open) > 0 ? `<tr><td style="padding:4px 14px 4px 0;color:#888">Open sponsor inquiries</td><td><b style="color:#b66565">${fmt(stats.inq_open)}</b></td></tr>` : ''}
</table>

${cover ? `<div style="margin-top:24px;padding:16px 20px;background:rgba(184,153,104,0.10);border:1px solid #d8cdb8;border-left:3px solid #8a6d3b">
  <div style="font-size:9px;letter-spacing:.4em;text-transform:uppercase;color:#8a6d3b;font-weight:500;margin-bottom:6px">Cover this month</div>
  <div style="font-family:Georgia,serif;font-style:italic;font-size:18px">${esc(cover.headline)}</div>
  <div style="font-size:11px;color:#888;margin-top:4px">${esc(cover.biz)}</div>
</div>` : ''}

${top.length ? `<h3 style="font-family:Georgia,serif;font-style:italic;color:#6a3a1a;margin-top:24px;font-size:18px">Most-tapped published</h3>
<ol style="font-size:13px;line-height:1.6;font-family:Georgia,serif;font-style:italic;padding-left:24px">
  ${top.map(t => `<li><a href="http://127.0.0.1:9780/magazine/${t.id}" style="color:#1a1815">${esc(t.headline)}</a> <span style="color:#888;font-style:normal;font-size:11px">${esc(t.name)}</span></li>`).join('')}
</ol>` : ''}

${fresh.length ? `<h3 style="font-family:Georgia,serif;font-style:italic;color:#6a3a1a;margin-top:18px;font-size:18px">Latest gen</h3>
<ul style="font-size:13px;line-height:1.6;font-family:Georgia,serif;font-style:italic;padding-left:24px">
  ${fresh.map(f => `<li>${esc(f.headline)} <span style="color:#888;font-style:normal;font-size:11px">${esc(f.name)}</span></li>`).join('')}
</ul>` : ''}

<div style="margin-top:24px;padding-top:14px;border-top:1px solid #d8cdb8;font-size:11px;color:#6e6356;font-family:Helvetica,sans-serif">
  <a href="http://127.0.0.1:9780/today.html" style="color:#8a6d3b">today</a> ·
  <a href="http://127.0.0.1:9780/health.html" style="color:#8a6d3b">health</a> ·
  <a href="http://127.0.0.1:9780/coverage.html" style="color:#8a6d3b">coverage</a> ·
  <a href="http://127.0.0.1:9780/issue" style="color:#8a6d3b">issue</a>
</div>
</div>`;

  if (DRY) { process.stdout.write(html); await pool.end(); return; }

  const r = await fetch(GEORGE, {
    method: 'POST',
    headers: { 'Content-Type': 'application/json', Authorization: AUTH },
    body: JSON.stringify({ to: TO, subject: `🪶 The Corridor · ${stats.feat} features (${covPct}% coverage)`, body: html, isHtml: true, account: 'info' })
  });
  const out = await r.json().catch(() => ({}));
  console.log(`[corpus_summary] ${r.status} → ${TO} · messageId=${out.messageId || 'none'}`);
  await pool.end();
}

main().catch(e => { console.error(e); process.exit(1); });