`db/ingest-tvmaze.mjs`

85 lines
// Ingests FACTUAL episode metadata only (title, season, episode number, air date)
// from the free TVmaze API — NOT synopses (those stay original editorial work).
// TVmaze is credited via source_type='tvmaze' + external_ids.tvmaze. Rate limit is
// generous (>=20 req / 10s); this makes a single embedded call, so we're well under.
// Run: node db/ingest-tvmaze.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 });

// Find the 2008 CW series specifically (not the 1990 original).
const searchRes = await fetch("https://api.tvmaze.com/search/shows?q=90210");
const results = await searchRes.json();
const pick =
  results
    .map((r) => r.show)
    .find(
      (s) =>
        s.name === "90210" &&
        (s.premiered ?? "").startsWith("2008")
    ) ?? results[0]?.show;

if (!pick) {
  console.error("No TVmaze match for 90210");
  process.exit(1);
}
console.log(`TVmaze show: ${pick.name} (${pick.premiered}) id=${pick.id} — ${pick.network?.name ?? "?"}`);

const show = (await pool.query(`select id from shows where canonical_title='90210'`)).rows[0];
if (!show) {
  console.error("Run db/seed.mjs first to create the show row.");
  process.exit(1);
}
const showId = show.id;

// One embedded call returns every episode (factual fields only).
const epsRes = await fetch(`https://api.tvmaze.com/shows/${pick.id}?embed=episodes`);
const data = await epsRes.json();
const episodes = data._embedded?.episodes ?? [];

// Upsert seasons.
const seasonNums = [...new Set(episodes.map((e) => e.season))].sort((a, b) => a - b);
for (const n of seasonNums) {
  const count = episodes.filter((e) => e.season === n).length;
  await pool.query(
    `insert into seasons (show_id, season_number, episode_count, source_last_verified_at)
     values ($1,$2,$3, now())
     on conflict (show_id, season_number)
     do update set episode_count = excluded.episode_count, source_last_verified_at = now()`,
    [showId, n, count]
  );
}

// Upsert episodes — FACTUAL fields only; summaries left NULL for editorial recaps.
let inserted = 0;
for (const e of episodes) {
  const seasonRow = (
    await pool.query(`select id from seasons where show_id=$1 and season_number=$2`, [showId, e.season])
  ).rows[0];
  const res = await pool.query(
    `insert into episodes
       (show_id, season_id, season_number, episode_number, title, air_date,
        source_url, source_type, external_ids, last_verified_at)
     values ($1,$2,$3,$4,$5,$6,$7,'tvmaze',$8, now())
     on conflict (show_id, season_number, episode_number)
     do update set title = excluded.title, air_date = excluded.air_date, last_verified_at = now()`,
    [
      showId,
      seasonRow?.id ?? null,
      e.season,
      e.number,
      e.name,
      e.airdate || null,
      e.url || null,
      JSON.stringify({ tvmaze: String(e.id) }),
    ]
  );
  inserted += res.rowCount;
}

console.log(`seasons: ${seasonNums.length}, episodes upserted: ${inserted}`);
console.log("Recaps intentionally left NULL — original editorial recaps are a separate task.");
await pool.end();