`src/jobs/daily_standup_audio.ts`

108 lines
// Renders a ~60 second daily-standup audio summary of yesterday's pipeline activity.
// Uses macOS `say -v Samantha` + afconvert (AAC m4a) — same toolchain as feature audio.
// Output:  ~/Projects/ventura-corridor/data/audio/standup/standup-YYYY-MM-DD.m4a
//          + a /standup/<date> route on the server (separate route, see admin gate)
//
// Run manually:  npm run magazine:standup
// Cron-driven:   launchd at 7:50 AM (just before morning email)

import { execSync, spawnSync } from 'node:child_process';
import * as fs from 'node:fs';
import * as path from 'node:path';
import { Pool } from 'pg';

const pool = new Pool({ host: '/tmp', database: 'ventura_corridor' });

async function q<T = any>(sql: string, params: any[] = []): Promise<{ rows: T[] }> {
  const r = await pool.query(sql, params);
  return { rows: r.rows };
}

function fmtN(n: number) { return n.toLocaleString('en-US'); }

async function buildScript(): Promise<string> {
  const today = new Date().toISOString().slice(0, 10);
  const day = new Date().toLocaleDateString('en-US', { weekday: 'long', month: 'long', day: 'numeric' });

  // Daily stats
  const stats = (await q(`
    SELECT
      (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 magazine_features WHERE status = 'published') AS total_pub,
      (SELECT count(*) FROM magazine_features) AS total_features,
      (SELECT count(*) FROM businesses) AS total_biz,
      (SELECT COALESCE(SUM(taps), 0) FROM magazine_features) AS total_taps,
      (SELECT count(*) FROM reader_feedback WHERE received_at > NOW() - INTERVAL '24 hours') AS feedback_24h,
      (SELECT count(*) FROM reader_feedback WHERE NOT reviewed) AS feedback_unread,
      (SELECT count(*) FROM sponsor_inquiries WHERE received_at > NOW() - INTERVAL '24 hours') AS inq_24h,
      (SELECT count(*) FROM sponsor_inquiries WHERE status = 'new') AS inq_open
  `)).rows[0];

  // Top story by taps in last 24h
  const top = (await q<{ headline: string; taps: number; biz: string }>(`
    SELECT mf.headline, mf.taps, b.name AS biz
    FROM magazine_features mf JOIN businesses b ON b.id = mf.business_id
    WHERE mf.status = 'published' AND mf.published_at > NOW() - INTERVAL '7 days'
    ORDER BY mf.taps DESC NULLS LAST, mf.views DESC NULLS LAST LIMIT 1
  `)).rows[0];

  // Cover this month
  const cover = (await q<{ headline: string; biz: string }>(`
    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 = $1
  `, [today.slice(0, 7)])).rows[0];

  const lines: string[] = [];
  lines.push(`Good morning, Steve. It's ${day}, and here is the standup for The Corridor.`);
  lines.push(``);
  lines.push(`In the last 24 hours, the editorial assistant generated ${fmtN(Number(stats.gen_24h))} new feature${Number(stats.gen_24h) === 1 ? '' : 's'}, and you, or the auto-publisher, sent ${fmtN(Number(stats.pub_24h))} to the published shelf.`);
  lines.push(`The issue now stands at ${fmtN(Number(stats.total_pub))} published feature${Number(stats.total_pub) === 1 ? '' : 's'}, with ${fmtN(Number(stats.total_features))} drafts in the pipeline, drawn from a corpus of ${fmtN(Number(stats.total_biz))} businesses on the boulevard.`);
  if (top) lines.push(`The most-tapped story this week was "${top.headline}", a feature on ${top.biz}, with ${fmtN(Number(top.taps || 0))} reader tap${Number(top.taps) === 1 ? '' : 's'}.`);
  if (cover) lines.push(`On the cover this month: "${cover.headline}", on ${cover.biz}.`);
  if (Number(stats.feedback_24h) > 0) {
    lines.push(`Readers sent ${fmtN(Number(stats.feedback_24h))} note${Number(stats.feedback_24h) === 1 ? '' : 's'} to the desk in the last day. ${fmtN(Number(stats.feedback_unread))} ${Number(stats.feedback_unread) === 1 ? 'is' : 'are'} unread on the feedback page.`);
  }
  if (Number(stats.inq_24h) > 0) {
    lines.push(`Sponsorship: ${fmtN(Number(stats.inq_24h))} new inquir${Number(stats.inq_24h) === 1 ? 'y' : 'ies'} in the last 24 hours, ${fmtN(Number(stats.inq_open))} still open.`);
  }
  lines.push(``);
  lines.push(`Have a productive morning.`);
  return lines.join(' ');
}

async function main() {
  const today = new Date().toISOString().slice(0, 10);
  const dir = path.resolve(process.cwd(), 'data', 'audio', 'standup');
  fs.mkdirSync(dir, { recursive: true });

  const script = await buildScript();
  const txtPath = path.join(dir, `standup-${today}.txt`);
  const aiffPath = path.join(dir, `standup-${today}.aiff`);
  const m4aPath = path.join(dir, `standup-${today}.m4a`);
  fs.writeFileSync(txtPath, script);

  console.log(`[standup] script ${script.length} chars`);

  const say = spawnSync('say', ['-v', 'Samantha', '-r', '180', '-o', aiffPath, script], { stdio: 'inherit' });
  if (say.status !== 0) throw new Error('say failed');
  const conv = spawnSync('afconvert', ['-d', 'aac', '-b', '64000', '-f', 'm4af', aiffPath, m4aPath], { stdio: 'inherit' });
  if (conv.status !== 0) throw new Error('afconvert failed');
  const m4aSize = fs.statSync(m4aPath).size;
  fs.unlinkSync(aiffPath);

  console.log(`[standup] wrote ${m4aPath} (${(m4aSize/1024).toFixed(1)}KB)`);

  // Symlink "latest"
  const latest = path.join(dir, 'latest.m4a');
  try { fs.unlinkSync(latest); } catch {}
  fs.symlinkSync(`standup-${today}.m4a`, latest);

  await pool.end();
}

main().catch(e => { console.error('[standup]', e); process.exit(1); });