`db/ingest-guestcast.mjs`

60 lines
// Per-episode cast detail: pulls the guest/recurring cast for every episode from
// TVmaze (factual — actor + character names) into episode_guest_cast. The app user
// owns its DB, so it can CREATE this table itself (no superuser DDL needed).
// Throttled to respect TVmaze's rate limit. Run: node db/ingest-guestcast.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 sleep = (ms) => new Promise((r) => setTimeout(r, ms));

await pool.query(`
  create table if not exists episode_guest_cast (
    id serial primary key,
    episode_id uuid not null references episodes(id) on delete cascade,
    person_name text not null,
    character_name text,
    ord int default 0
  )`);
await pool.query(
  `create index if not exists idx_egc_ep on episode_guest_cast(episode_id)`
);

const eps = (
  await pool.query(
    `select id, season_number, episode_number, external_ids->>'tvmaze' as tvmaze
       from episodes where external_ids ? 'tvmaze' order by season_number, episode_number`
  )
).rows;

let done = 0,
  rows = 0;
for (const ep of eps) {
  await sleep(380); // TVmaze rate limit (>=20 req / 10s)
  try {
    const r = await fetch(
      `https://api.tvmaze.com/episodes/${ep.tvmaze}?embed=guestcast`,
      { headers: { "User-Agent": "nineoh-guide/0.1 (fan project)" } }
    );
    const j = await r.json();
    const guests = j._embedded?.guestcast ?? [];
    await pool.query(`delete from episode_guest_cast where episode_id=$1`, [ep.id]);
    let i = 0;
    for (const g of guests) {
      const name = g.person?.name;
      if (!name) continue;
      await pool.query(
        `insert into episode_guest_cast (episode_id, person_name, character_name, ord) values ($1,$2,$3,$4)`,
        [ep.id, name, g.character?.name ?? null, i++]
      );
      rows++;
    }
    done++;
    if (done % 20 === 0) console.log(`  ${done}/${eps.length} episodes, ${rows} guest credits`);
  } catch (e) {
    console.warn(`S${ep.season_number}E${ep.episode_number} failed: ${e.message}`);
  }
}
console.log(`done: ${done}/${eps.length} episodes, ${rows} guest credits total`);
await pool.end();