`scripts/enrich-productions-wikidata.ts`

150 lines
#!/usr/bin/env -S npx tsx
/**
 * Wikidata enrichment for film_production rows.
 *
 * For each production missing wikidata_qid / imdb_id / blurb, queries the
 * Wikidata SPARQL endpoint to find the matching item (filtered by P31 to
 * a film/TV-series subclass), then pulls IMDb ID (P345), Wikipedia URL,
 * and falls back to a Wikipedia summary for the blurb.
 *
 * Tier-C source. Updates only fill empty fields.
 *
 * Usage:
 *   npx tsx scripts/enrich-productions-wikidata.ts
 *   npx tsx scripts/enrich-productions-wikidata.ts --slug mulholland-echoes
 *   npx tsx scripts/enrich-productions-wikidata.ts --dry
 */
import { Pool } from 'pg';

const pool = new Pool({
  host: process.env.PGHOST ?? '/tmp',
  database: process.env.PGDATABASE ?? 'stayclaim',
  user: process.env.PGUSER ?? process.env.USER,
  max: 4,
});

type WdRow = {
  item: { value: string };
  itemLabel: { value: string };
  imdb?: { value: string };
  wiki?: { value: string };
};

async function querySparql(name: string): Promise<WdRow | null> {
  const sparql = `
SELECT ?item ?itemLabel ?imdb ?wiki WHERE {
  ?item rdfs:label "${name.replace(/"/g, '\\"')}"@en.
  VALUES ?type { wd:Q11424 wd:Q5398426 wd:Q24856 wd:Q24862 wd:Q21191270 }
  ?item wdt:P31 ?type.
  OPTIONAL { ?item wdt:P345 ?imdb. }
  OPTIONAL {
    ?wiki schema:about ?item;
          schema:isPartOf <https://en.wikipedia.org/>.
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
LIMIT 3
`;
  const url = 'https://query.wikidata.org/sparql?format=json&query=' + encodeURIComponent(sparql);
  const res = await fetch(url, {
    headers: {
      Accept: 'application/sparql-results+json',
      'User-Agent': 'pastdoor/0.1 (https://pastdoor.com)',
    },
  });
  if (!res.ok) throw new Error(`Wikidata ${res.status}`);
  const j = (await res.json()) as { results: { bindings: WdRow[] } };
  return j.results.bindings[0] ?? null;
}

async function fetchWikiSummary(title: string): Promise<string | null> {
  const url = `https://en.wikipedia.org/api/rest_v1/page/summary/${encodeURIComponent(title)}`;
  const res = await fetch(url, {
    headers: { 'User-Agent': 'pastdoor/0.1', Accept: 'application/json' },
  });
  if (!res.ok) return null;
  const j = (await res.json()) as { extract?: string };
  if (!j.extract) return null;
  return j.extract.split(/(?<=\.)\s+/).slice(0, 2).join(' ').slice(0, 280);
}

function pageTitleFromUrl(url: string): string | null {
  try {
    const u = new URL(url);
    if (!u.hostname.endsWith('wikipedia.org')) return null;
    const m = u.pathname.match(/^\/wiki\/(.+)$/);
    return m ? decodeURIComponent(m[1]!) : null;
  } catch {
    return null;
  }
}

async function main() {
  const args = process.argv.slice(2);
  const slugArg = args.find((a, i) => args[i - 1] === '--slug');
  const dry = args.includes('--dry');

  const conds: string[] = [];
  const params: unknown[] = [];
  if (slugArg) {
    params.push(slugArg);
    conds.push(`slug = $${params.length}`);
  }
  const sql = `SELECT id, slug, title, year, wikidata_qid, imdb_id, blurb
               FROM film_production
               ${conds.length ? 'WHERE ' + conds.join(' AND ') : ''}
               ORDER BY title`;
  const { rows } = await pool.query(sql, params);

  let updated = 0;
  let nohit = 0;
  for (const p of rows) {
    let wd;
    try {
      wd = await querySparql(p.title);
    } catch (e) {
      console.warn(`[${p.slug}] sparql err: ${(e as Error).message}`);
    }
    const updates: Record<string, unknown> = {};
    if (!p.wikidata_qid && wd?.item?.value) {
      const m = wd.item.value.match(/\/(Q\d+)$/);
      if (m) updates.wikidata_qid = m[1];
    }
    if (!p.imdb_id && wd?.imdb?.value) updates.imdb_id = wd.imdb.value;
    if (!p.blurb && wd?.wiki?.value) {
      const title = pageTitleFromUrl(wd.wiki.value);
      if (title) {
        const summary = await fetchWikiSummary(title);
        if (summary) updates.blurb = summary;
      }
    }

    if (Object.keys(updates).length === 0) {
      console.log(`[${p.slug}] nothing to add`);
      if (!wd) nohit++;
      await new Promise(r => setTimeout(r, 600));
      continue;
    }
    if (dry) {
      console.log(`[${p.slug}] DRY would set`, updates);
    } else {
      const setParts: string[] = [];
      const vals: unknown[] = [];
      for (const [k, v] of Object.entries(updates)) {
        vals.push(v);
        setParts.push(`${k} = $${vals.length}`);
      }
      vals.push(p.id);
      await pool.query(`UPDATE film_production SET ${setParts.join(', ')} WHERE id = $${vals.length}`, vals);
      console.log(`[${p.slug}] updated`, updates);
      updated++;
    }
    await new Promise(r => setTimeout(r, 600));
  }

  console.log(JSON.stringify({ checked: rows.length, updated, no_match: nohit, dry }, null, 2));
  await pool.end();
}

main().catch(e => { console.error(e); process.exit(1); });