`db/ingest-original.mjs`

150 lines
// Ingest the ORIGINAL "Beverly Hills, 90210" (1990–2000) as a SECOND show,
// alongside the existing 2008 reboot. FACTUAL episode metadata only (title,
// season, episode number, air date) from the free TVmaze API — NO synopses
// (those stay original editorial). Names-only cast; photos are added later by
// ingest-wikimedia-images.mjs which accepts ONLY commercial-free licenses.
// Run: node db/ingest-original.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 1990 ORIGINAL (not the 2008 reboot).
const searchRes = await fetch(
  "https://api.tvmaze.com/search/shows?q=beverly%20hills%2090210"
);
const results = await searchRes.json();
const pick =
  results
    .map((r) => r.show)
    .find(
      (s) =>
        /beverly hills,? 90210/i.test(s.name) &&
        (s.premiered ?? "").startsWith("1990")
    ) ?? results.map((r) => r.show).find((s) => (s.premiered ?? "").startsWith("1990"));

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

// Seed the ORIGINAL show row (idempotent) — distinct canonical_title from the reboot.
const CANON = "beverly-hills-90210";
await pool.query(
  `insert into shows (canonical_title, display_title, franchise_title, description_original, disclaimer_text)
   select $1,$2,$3,$4,$5
   where not exists (select 1 from shows where canonical_title = $1)`,
  [
    CANON,
    "Beverly Hills, 90210 (1990)",
    "Beverly Hills, 90210 franchise",
    "A fan-made companion to the original 1990–2000 teen drama set in the 90210 ZIP code. Unofficial and not affiliated with the producers, network, or cast.",
    "Unofficial, fan-made. Not affiliated with or endorsed by the series' producers, network, or cast.",
  ]
);
const showId = (
  await pool.query(`select id from shows where canonical_title=$1`, [CANON])
).rows[0].id;
console.log(`original show_id = ${showId}`);

// 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 ?? []).filter((e) => e.season && e.number);

// 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 original 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(`ORIGINAL: seasons ${seasonNums.length}, episodes upserted ${inserted}`);

// Names-only main cast via the embedded cast endpoint (factual credits; no photos here).
try {
  const castRes = await fetch(`https://api.tvmaze.com/shows/${pick.id}/cast`);
  const castArr = await castRes.json();
  let castN = 0;
  for (let i = 0; i < castArr.length; i++) {
    const person = castArr[i]?.person, character = castArr[i]?.character;
    if (!person?.name) continue;
    // Upsert person by name (no external_ids column; names are facts).
    let personId = (
      await pool.query(
        `insert into cast_people (name, publicity_risk_level)
           select $1, 'editorial-only'
           where not exists (select 1 from cast_people where name = $1)
           returning id`,
        [person.name]
      )
    ).rows[0]?.id;
    if (!personId) {
      personId = (await pool.query(`select id from cast_people where name=$1`, [person.name])).rows[0].id;
    }
    const pRow = { id: personId };
    let charId = null;
    if (character?.name) {
      charId = (
        await pool.query(
          `insert into characters (name, show_id)
             select $1, $2
             where not exists (select 1 from characters where name=$1 and show_id=$2)
             returning id`,
          [character.name, showId]
        )
      ).rows[0]?.id;
      if (!charId) {
        charId = (await pool.query(`select id from characters where name=$1 and show_id=$2`, [character.name, showId])).rows[0].id;
      }
    }
    await pool.query(
      `insert into credits (show_id, person_id, character_id, credit_type, billing_order)
       values ($1,$2,$3,'main-cast',$4)
       on conflict do nothing`,
      [showId, pRow.id, charId, i + 1]
    );
    castN++;
  }
  console.log(`ORIGINAL: main-cast credits ${castN}`);
} catch (e) {
  console.log("cast ingest skipped:", String(e).slice(0, 80));
}

console.log("Done. Recaps/bios intentionally NULL (original editorial); photos via ingest-wikimedia-images (commercial-free only).");
await pool.end();