`db/apply-bios.mjs`

52 lines
// Applies ORIGINAL cast bios from db/bios.json → cast_people.biography_original
// (bio_status='ai-draft'), and links each actor to their character via the credits
// table (episode_id NULL, credit_type='main-cast'). Run: node db/apply-bios.mjs
import pg from "pg";
import { readFileSync } from "node:fs";
import { fileURLToPath } from "node:url";
import { dirname, join } from "node:path";

const DATABASE_URL =
  process.env.DATABASE_URL ?? "postgresql://localhost/nineoh_guide?host=/tmp";
const pool = new pg.Pool({ connectionString: DATABASE_URL });
const here = dirname(fileURLToPath(import.meta.url));
const bios = JSON.parse(readFileSync(join(here, "bios.json"), "utf8"));

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

let bioN = 0, linkN = 0;
for (const [name, entry] of Object.entries(bios)) {
  if (name.startsWith("_")) continue;
  const person = (await pool.query(`select id from cast_people where name=$1`, [name])).rows[0];
  if (!person) { console.warn(`no cast_people row for ${name} — skipped`); continue; }

  const b = await pool.query(
    `update cast_people set biography_original=$1, bio_status='ai-draft' where id=$2`,
    [entry.bio, person.id]
  );
  bioN += b.rowCount;

  // Link person -> character (show-level main-cast credit; episode_id NULL).
  const ch = (
    await pool.query(`select id from characters where name=$1 and show_id=$2`, [entry.character, show.id])
  ).rows[0];
  if (ch) {
    const link = await pool.query(
      `insert into credits (person_id, character_id, credit_type)
         select $1,$2,'main-cast'
         where not exists (
           select 1 from credits where person_id=$1 and character_id=$2 and credit_type='main-cast'
         )`,
      [person.id, ch.id]
    );
    linkN += link.rowCount;
  }
}

console.log(`bios written: ${bioN}, main-cast links created: ${linkN}`);
const t = await pool.query(`select bio_status, count(*) from cast_people group by bio_status order by 1`);
console.table(t.rows);
console.log("All bios ai-draft — review before publish.");
await pool.end();