`db/ingest-news.mjs`

104 lines
// Aggregates 90210 news as an INDEX of links — headline + source + date +
// canonical URL only. NO article bodies are stored or reproduced (copyright);
// each item links out to the publisher. Source = free Google News RSS (no key).
// "All news" is honestly bounded by what the free index surfaces per query.
// Run: node db/ingest-news.mjs
import pg from "pg";

const DATABASE_URL =
  process.env.DATABASE_URL ?? "postgresql://localhost/nineoh_guide?host=/tmp";
const pool = new pg.Pool({ connectionString: DATABASE_URL });

const show = (await pool.query(`select id from shows where canonical_title='90210'`)).rows[0];
const showId = show?.id ?? null;

// On-topic queries spanning the original series + the 2008 reboot + cast.
const QUERIES = [
  '"Beverly Hills, 90210"',
  '"90210" CW reboot',
  '"90210" reunion',
  '"90210" cast',
  '"AnnaLynne McCord" 90210',
  '"Shenae Grimes" 90210',
  '"Jennie Garth" 90210',
  '"Tori Spelling" 90210',
  '"Jason Priestley" 90210',
  '"90210" reboot news',
];

function decode(s) {
  return (s || "")
    .replace(/<!\[CDATA\[(.*?)\]\]>/gs, "$1")
    .replace(/&amp;/g, "&")
    .replace(/&#39;/g, "'")
    .replace(/&quot;/g, '"')
    .replace(/&lt;/g, "<")
    .replace(/&gt;/g, ">")
    .trim();
}

function parseItems(xml) {
  const items = [];
  const re = /<item>(.*?)<\/item>/gs;
  let m;
  while ((m = re.exec(xml))) {
    const block = m[1];
    const title = decode((block.match(/<title>(.*?)<\/title>/s) || [])[1]);
    const link = decode((block.match(/<link>(.*?)<\/link>/s) || [])[1]);
    const pub = decode((block.match(/<pubDate>(.*?)<\/pubDate>/s) || [])[1]);
    const source = decode((block.match(/<source[^>]*>(.*?)<\/source>/s) || [])[1]);
    if (!title || !link) continue;
    // Google News titles are "Headline - Publisher" — split off the publisher.
    let headline = title;
    let publisher = source;
    const dash = title.lastIndexOf(" - ");
    if (dash > 20 && !source) {
      headline = title.slice(0, dash);
      publisher = title.slice(dash + 3);
    }
    items.push({ headline, link, pub, publisher });
  }
  return items;
}

let seen = 0,
  inserted = 0;
for (const q of QUERIES) {
  const url = `https://news.google.com/rss/search?q=${encodeURIComponent(q)}&hl=en-US&gl=US&ceid=US:en`;
  let xml;
  try {
    const r = await fetch(url, { headers: { "User-Agent": "nineoh-guide/0.1 (fan project)" } });
    xml = await r.text();
  } catch (e) {
    console.warn(`query failed: ${q} — ${e.message}`);
    continue;
  }
  const items = parseItems(xml);
  for (const it of items) {
    seen++;
    const when = it.pub ? new Date(it.pub) : null;
    const res = await pool.query(
      `insert into news_items (headline, canonical_url, publisher_name, published_at,
                               topic_tags, show_ids, snippet_status, source_query)
       values ($1,$2,$3,$4,$5,$6,'indexed',$7)
       on conflict (canonical_url) do nothing`,
      [
        it.headline,
        it.link,
        it.publisher || null,
        when && !isNaN(when) ? when.toISOString() : null,
        ["90210"],
        showId ? [showId] : [],
        q,
      ]
    );
    inserted += res.rowCount;
  }
  console.log(`"${q}" → ${items.length} items`);
}

const total = await pool.query(`select count(*)::int n from news_items`);
console.log(`\nseen ${seen}, newly inserted ${inserted}, total in index: ${total.rows[0].n}`);
console.log("Headlines + source + link only — no article bodies stored (copyright).");
await pool.end();