`scripts/seed-episode-cast.mjs`

79 lines
// Seed episode_guest_cast from TVmaze (factual, no API key) for every episode
// that carries a tvmaze external id. Idempotent: clears + reinserts per episode.
// Also prints an aggregate of recurring guest people (episode counts) so we can
// pick the real recurring cast to add to the Cast tab.
//
//   node scripts/seed-episode-cast.mjs
//
import { readFileSync } from "node:fs";
import { Pool } from "pg";

// --- load DATABASE_URL from apps/web/.env.local ---
const env = readFileSync(new URL("../apps/web/.env.local", import.meta.url), "utf8");
const DB = env.match(/^DATABASE_URL=(.+)$/m)?.[1]?.replace(/^["']|["']$/g, "");
if (!DB) throw new Error("DATABASE_URL not found in apps/web/.env.local");
const pool = new Pool({ connectionString: DB, max: 4 });

const sleep = (ms) => new Promise((r) => setTimeout(r, ms));

async function tvmaze(url, tries = 3) {
  for (let i = 0; i < tries; i++) {
    const res = await fetch(url);
    if (res.status === 429) { await sleep(1500); continue; }
    if (!res.ok) throw new Error(`${res.status} ${url}`);
    return res.json();
  }
  throw new Error(`retries exhausted ${url}`);
}

const { rows: eps } = await pool.query(
  `select id, season_number, episode_number, external_ids
     from episodes order by season_number, episode_number`
);
console.log(`episodes in DB: ${eps.length}`);

const agg = new Map(); // person -> { count, character }
let seeded = 0;

for (const ep of eps) {
  const tv = ep.external_ids?.tvmaze;
  if (!tv) { console.log(`  S${ep.season_number}E${ep.episode_number} — no tvmaze id, skip`); continue; }
  let data;
  try {
    data = await tvmaze(`https://api.tvmaze.com/episodes/${tv}?embed=guestcast`);
  } catch (e) {
    console.log(`  S${ep.season_number}E${ep.episode_number} — fetch failed: ${e.message}`);
    continue;
  }
  const guests = (data._embedded?.guestcast ?? []).map((c, i) => ({
    name: c.person?.name ?? null,
    character: c.character?.name ?? null,
    ord: i,
  })).filter((g) => g.name);

  await pool.query(`delete from episode_guest_cast where episode_id = $1`, [ep.id]);
  for (const g of guests) {
    await pool.query(
      `insert into episode_guest_cast (episode_id, person_name, character_name, ord)
       values ($1,$2,$3,$4)`,
      [ep.id, g.name, g.character, g.ord]
    );
    const k = g.name;
    const cur = agg.get(k) ?? { count: 0, character: g.character };
    cur.count++;
    agg.set(k, cur);
  }
  seeded += guests.length;
  process.stdout.write(`\r  seeded S${ep.season_number}E${ep.episode_number} (+${guests.length})     `);
  await sleep(140); // be polite to TVmaze
}

console.log(`\n\ntotal guest-cast rows: ${seeded}`);
console.log(`\n=== recurring guests (>=3 episodes) — candidates for the Cast tab ===`);
[...agg.entries()]
  .filter(([, v]) => v.count >= 3)
  .sort((a, b) => b[1].count - a[1].count)
  .forEach(([name, v]) => console.log(`${String(v.count).padStart(3)}  ${name}  —  ${v.character ?? "?"}`));

await pool.end();