`src/ingest/wikipedia_lawyers.ts`

252 lines
/**
 * Wikipedia importer for individual LA lawyers (Category:Lawyers_from_Los_Angeles).
 *
 * 285 articles → professionals table. Extracts from each article's infobox:
 *   - full name, birth/death year, education, firm, notable cases, position
 * Skips judicial-only figures (already covered by Category:Judges).
 * Resolves firm name → organizations (creates link if firm exists in our DB).
 */
import 'dotenv/config';
import crypto from 'node:crypto';
import { fetch } from 'undici';
import { load } from 'cheerio';
import { pool, query, withTx } from '../db/pool.ts';

const SOURCE_NAME = 'Wikipedia (LA County lawyer biographies)';
const USER_AGENT = process.env.USER_AGENT || 'LawyerDirectoryBuilder/0.1 (research; contact: steveabramsdesigns@gmail.com)';

const CATEGORIES = [
  'Category:Lawyers_from_Los_Angeles',
  'Category:California_lawyers',
];

async function ensureSource(): Promise<number> {
  await query(`
    INSERT INTO sources (source_name, source_type, base_url, terms_notes, allowed_method, rate_limit_rps)
    VALUES ($1, 'api', 'https://en.wikipedia.org/api/rest_v1/page/html/',
      'Wikipedia REST API. CC-BY-SA. Polite-use 0.8 rps.', 'api', 0.8)
    ON CONFLICT (source_name) DO NOTHING
  `, [SOURCE_NAME]);
  const r = await query<{ id: number }>(`SELECT id FROM sources WHERE source_name = $1`, [SOURCE_NAME]);
  return r.rows[0].id;
}

async function startJob(sourceId: number, label: string) {
  const r = await query<{ id: number }>(`
    INSERT INTO scrape_jobs (source_id, job_label, status, started_at)
    VALUES ($1, $2, 'running', NOW()) RETURNING id
  `, [sourceId, label]);
  return r.rows[0].id;
}

async function finishJob(jobId: number, fields: Record<string, unknown>) {
  const sets: string[] = [];
  const params: unknown[] = [];
  let i = 1;
  for (const [k, v] of Object.entries(fields)) { sets.push(`${k} = $${i++}`); params.push(v); }
  sets.push(`finished_at = NOW()`);
  params.push(jobId);
  await query(`UPDATE scrape_jobs SET ${sets.join(', ')} WHERE id = $${i}`, params);
}

async function fetchJSON<T>(url: string): Promise<T> {
  const r = await fetch(url, { headers: { 'User-Agent': USER_AGENT, Accept: 'application/json' }, signal: AbortSignal.timeout(30000) });
  if (!r.ok) throw new Error(`${r.status} ${url}`);
  return await r.json() as T;
}
async function fetchHTML(url: string): Promise<string> {
  const r = await fetch(url, { headers: { 'User-Agent': USER_AGENT, Accept: 'text/html' }, signal: AbortSignal.timeout(30000) });
  if (!r.ok) throw new Error(`${r.status} ${url}`);
  return await r.text();
}

async function listCategoryMembers(cat: string): Promise<string[]> {
  let cont: string | undefined;
  const titles = new Set<string>();
  while (true) {
    const u = `https://en.wikipedia.org/w/api.php?action=query&list=categorymembers&cmtitle=${encodeURIComponent(cat)}&cmlimit=500&format=json${cont ? `&cmcontinue=${encodeURIComponent(cont)}` : ''}`;
    const j = await fetchJSON<{ query: { categorymembers: { title: string; ns: number }[] }; continue?: { cmcontinue: string } }>(u);
    for (const m of j.query.categorymembers) if (m.ns === 0) titles.add(m.title);
    if (!j.continue) break;
    cont = j.continue.cmcontinue;
  }
  return [...titles];
}

interface Bio {
  title: string;
  fullName: string;
  birthYear?: number;
  deathYear?: number;
  education?: string;
  firm?: string;
  position?: string;
  description?: string;
}

function parseYear(s: string): number | null {
  const m = s.match(/(\d{4})/); if (!m) return null;
  const y = parseInt(m[1], 10);
  return y >= 1700 && y <= new Date().getFullYear() ? y : null;
}

// Strip Wikipedia disambiguators: "Joaquin Avila (lawyer)" → "Joaquin Avila"
function cleanTitle(t: string): string {
  return t.replace(/\s*\([^)]*\)\s*$/, '').trim();
}

function splitName(full: string) {
  const parts = full.split(/\s+/);
  if (parts.length === 1) return { first: parts[0], last: '' };
  if (parts.length === 2) return { first: parts[0], last: parts[1] };
  // Treat last token as last name; everything before as first/middle.
  const last = parts[parts.length - 1];
  const first = parts[0];
  const middle = parts.slice(1, -1).join(' ');
  return { first, middle, last };
}

function extractInfobox(html: string, title: string): Bio {
  const $ = load(html);
  const bio: Bio = { title, fullName: cleanTitle(title) };

  const firstP = $('p').filter((_, p) => $(p).text().trim().length > 50).first().text().trim();
  if (firstP) bio.description = firstP.slice(0, 500);

  const $box = $('table.infobox, .infobox.vcard').first();
  if ($box.length === 0) return bio;

  $box.find('tr').each((_, tr) => {
    const $tr = $(tr);
    const label = $tr.find('th').first().text().toLowerCase().trim();
    const value = $tr.find('td').first().text().trim();
    if (!label || !value) return;

    if (/^born|^birth/.test(label)) { const y = parseYear(value); if (y) bio.birthYear = y; }
    else if (/^died|^death/.test(label)) { const y = parseYear(value); if (y) bio.deathYear = y; }
    else if (/^education|alma mater|college|school/.test(label)) bio.education = value.slice(0, 200);
    else if (/^firm|employer|practice/.test(label)) bio.firm = value.slice(0, 200);
    else if (/^position|^title|^office|known for/.test(label)) bio.position = value.slice(0, 200);
  });

  return bio;
}

async function upsertProfessional(bio: Bio, articleUrl: string, sourceId: number) {
  const { first, middle, last } = splitName(bio.fullName);
  if (!first || !last) return null;

  return await withTx(async (client) => {
    // Dedup: name match (case insensitive) — articles are unique enough by name.
    let profId: number | null = null;
    const r = await client.query<{ id: number }>(
      `SELECT id FROM professionals WHERE LOWER(full_name) = LOWER($1) LIMIT 1`,
      [bio.fullName]);
    if (r.rowCount) profId = r.rows[0].id;

    if (profId) {
      await client.query(`
        UPDATE professionals
        SET first_name = COALESCE(first_name, $2),
            last_name  = COALESCE(last_name,  $3),
            middle_name = COALESCE(middle_name, $4),
            graduation_year = COALESCE(graduation_year, $5),
            bio = COALESCE(bio, $6),
            confidence_tier = COALESCE(confidence_tier, 'medium'),
            updated_at = NOW()
        WHERE id = $1
      `, [profId, first, last, middle, bio.birthYear ? bio.birthYear + 25 : null, bio.description]);
    } else {
      const r2 = await client.query<{ id: number }>(`
        INSERT INTO professionals (full_name, first_name, middle_name, last_name, bio, source_confidence_score, confidence_tier)
        VALUES ($1, $2, $3, $4, $5, 0.45, 'medium')
        RETURNING id
      `, [bio.fullName, first, middle, last, bio.description]);
      profId = r2.rows[0].id;
    }

    // Education
    if (bio.education) {
      const exists = await client.query(
        `SELECT 1 FROM education WHERE professional_id = $1 AND school_name = $2 LIMIT 1`,
        [profId, bio.education]);
      if (exists.rowCount === 0) {
        await client.query(`
          INSERT INTO education (professional_id, school_name, source_url)
          VALUES ($1, $2, $3)
        `, [profId, bio.education, articleUrl]);
      }
    }

    // Firm linkage — try to find existing organization by name
    if (bio.firm) {
      const r3 = await client.query<{ id: number }>(
        `SELECT id FROM organizations WHERE LOWER(name) = LOWER($1) LIMIT 1`,
        [bio.firm]);
      if (r3.rowCount) {
        const orgId = r3.rows[0].id;
        const exists = await client.query(
          `SELECT 1 FROM professional_locations WHERE professional_id = $1 AND organization_id = $2 LIMIT 1`,
          [profId, orgId]);
        if (exists.rowCount === 0) {
          await client.query(`
            INSERT INTO professional_locations (professional_id, organization_id, role, source_url, last_verified_at)
            VALUES ($1, $2, $3, $4, NOW())
          `, [profId, orgId, bio.position || 'attorney', articleUrl]);
        }
      }
    }

    const rawJson = JSON.stringify(bio);
    const hash = crypto.createHash('sha256').update(rawJson + '|wiki-lawyer|' + bio.title + '|' + profId).digest('hex');
    await client.query(`
      INSERT INTO raw_records (source_id, source_url, entity_type, entity_id, raw_json, fetched_at, hash)
      VALUES ($1, $2, 'professional', $3, $4::jsonb, NOW(), $5)
      ON CONFLICT (source_id, hash) DO NOTHING
    `, [sourceId, articleUrl, profId, rawJson, hash]);

    return profId;
  });
}

async function main() {
  console.log('[wiki-lawyers] importing LA lawyer biographies…');
  const sourceId = await ensureSource();
  const jobId = await startJob(sourceId, 'wiki-lawyers:la');

  const titles = new Set<string>();
  for (const cat of CATEGORIES) {
    const ts = await listCategoryMembers(cat);
    ts.forEach(t => titles.add(t));
    await new Promise(r => setTimeout(r, 1000));
  }
  console.log(`[wiki-lawyers] ${titles.size} candidate articles`);

  let processed = 0, kept = 0, skipped = 0;
  for (const title of titles) {
    try {
      const articleUrl = `https://en.wikipedia.org/wiki/${encodeURIComponent(title.replace(/ /g, '_'))}`;
      const html = await fetchHTML(`https://en.wikipedia.org/api/rest_v1/page/html/${encodeURIComponent(title)}`);
      const bio = extractInfobox(html, title);
      const id = await upsertProfessional(bio, articleUrl, sourceId);
      if (id) kept++; else skipped++;
    } catch (e) {
      console.error(`[wiki-lawyers] err ${title}: ${(e as Error).message}`);
      skipped++;
    }
    processed++;
    if (processed % 25 === 0) console.log(`[wiki-lawyers] ${processed}/${titles.size} kept=${kept} skipped=${skipped}`);
    await new Promise(r => setTimeout(r, 700));     // ~1.4 rps polite
  }

  await finishJob(jobId, { status: 'completed', records_found: titles.size, records_inserted: kept, records_skipped: skipped });
  console.log(`[wiki-lawyers] done. articles=${titles.size} kept=${kept} skipped=${skipped}`);
  await pool.end();
}

main().catch(async (err) => {
  console.error('[wiki-lawyers] fatal:', err);
  try { await pool.end(); } catch {}
  process.exit(1);
});