`src/ingest/wikipedia_infobox.ts`

243 lines
/**
 * Wikipedia infobox enricher for LA-based law firms.
 *
 * Sources:
 *   - Category:Law_firms_based_in_Los_Angeles  (~21 articles)
 *   - Category:Law_firms_based_in_California   (~13 articles)
 * For each article, fetches REST HTML, parses .infobox table for:
 *   - Headquarters / Address
 *   - Founded
 *   - Lawyers / No. of lawyers / Attorneys
 *   - Revenue
 *   - Website
 * Upserts into organizations (matching by name first) — fills in attorney_count,
 * founded_year, website, address from infobox where missing.
 */
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 law firm articles)';
const USER_AGENT = process.env.USER_AGENT || 'LawyerDirectoryBuilder/0.1 (research; contact: steveabramsdesigns@gmail.com)';

const CATEGORIES = [
  'Category:Law_firms_based_in_Los_Angeles',
  'Category:Law_firms_based_in_California',
];

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.5 rps.', 'api', 0.5)
    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[]> {
  const url = `https://en.wikipedia.org/w/api.php?action=query&list=categorymembers&cmtitle=${encodeURIComponent(cat)}&cmlimit=500&format=json`;
  const j = await fetchJSON<{ query: { categorymembers: { title: string; ns: number }[] } }>(url);
  return j.query.categorymembers.filter(m => m.ns === 0).map(m => m.title);
}

interface Infobox {
  headquarters?: string;
  founded?: number;
  lawyers?: number;
  revenue?: string;
  website?: 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;
}

function parseInteger(s: string): number | null {
  // "Lawyers 3,300+" → 3300
  const m = s.replace(/,/g, '').match(/(\d{2,6})/);
  return m ? parseInt(m[1], 10) : null;
}

function extractInfobox(html: string): Infobox {
  const $ = load(html);
  const ib: Infobox = {};

  // First paragraph as description
  const firstP = $('p').filter((_, p) => $(p).text().trim().length > 50).first().text().trim();
  if (firstP) ib.description = firstP.slice(0, 600);

  // Find the table.infobox; many Wikipedia article HTMLs have a nested vcard
  const $box = $('table.infobox, .infobox.vcard').first();
  if ($box.length === 0) return ib;

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

    if (/^head\s?quarters/.test(label) || label === 'address') ib.headquarters = value;
    else if (/founded|established/.test(label)) {
      const y = parseYear(value); if (y) ib.founded = y;
    }
    else if (/lawyers|attorneys|no\. of (lawyers|attorneys)/.test(label)) {
      const n = parseInteger(value); if (n) ib.lawyers = n;
    }
    else if (/^revenue/.test(label)) ib.revenue = value;
    else if (/^website/.test(label)) {
      const a = $tr.find('td a').first().attr('href');
      if (a) ib.website = a;
    }
  });

  return ib;
}

function bandFromCount(n: number | undefined): string | null {
  if (!n) return null;
  if (n >= 500) return 'biglaw';
  if (n >= 100) return 'large';
  if (n >= 25) return 'medium';
  if (n >= 5) return 'small';
  return 'solo';
}

async function upsertFromArticle(title: string, sourceId: number) {
  const articleUrl = `https://en.wikipedia.org/wiki/${encodeURIComponent(title.replace(/ /g, '_'))}`;
  const restUrl = `https://en.wikipedia.org/api/rest_v1/page/html/${encodeURIComponent(title)}`;
  let html: string;
  try { html = await fetchHTML(restUrl); }
  catch (e) { console.error(`[wiki] fetch err ${title}: ${(e as Error).message}`); return null; }

  const ib = extractInfobox(html);
  const name = title;                                 // article title is the firm name

  return await withTx(async (client) => {
    let orgId: number | null = null;
    if (ib.website) {
      const r = await client.query<{ id: number }>(
        `SELECT id FROM organizations WHERE LOWER(website) = LOWER($1) LIMIT 1`, [ib.website]);
      if (r.rowCount) orgId = r.rows[0].id;
    }
    if (!orgId) {
      const r = await client.query<{ id: number }>(
        `SELECT id FROM organizations WHERE LOWER(name) = LOWER($1) AND county = 'Los Angeles' LIMIT 1`, [name]);
      if (r.rowCount) orgId = r.rows[0].id;
    }

    if (orgId) {
      await client.query(`
        UPDATE organizations
        SET website = COALESCE(website, $2),
            attorney_count = GREATEST(attorney_count, COALESCE($3, 0)),
            firm_size_band = COALESCE($4, firm_size_band),
            source_url = COALESCE(source_url, $5),
            updated_at = NOW()
        WHERE id = $1
      `, [orgId, ib.website, ib.lawyers, bandFromCount(ib.lawyers), articleUrl]);
    } else {
      const r = await client.query<{ id: number }>(`
        INSERT INTO organizations (
          name, type, address, city, county, website,
          attorney_count, firm_size_band, source_url
        ) VALUES (
          $1, 'law_firm', $2, 'Los Angeles', 'Los Angeles', $3,
          COALESCE($4::int, 0), $5, $6
        ) RETURNING id
      `, [name, ib.headquarters, ib.website, ib.lawyers, bandFromCount(ib.lawyers), articleUrl]);
      orgId = r.rows[0].id;
    }

    const rawJson = JSON.stringify({ title, infobox: ib, articleUrl });
    const hash = crypto.createHash('sha256').update(rawJson + '|wiki|' + title + '|' + orgId).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,'organization',$3,$4::jsonb,NOW(),$5)
      ON CONFLICT (source_id, hash) DO NOTHING
    `, [sourceId, articleUrl, orgId, rawJson, hash]);

    return orgId;
  });
}

async function main() {
  console.log('[wiki] enriching from Wikipedia LA-firm category articles…');
  const sourceId = await ensureSource();
  const jobId = await startJob(sourceId, 'wiki:la-firm-articles');

  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] ${titles.size} candidate articles`);

  let processed = 0, kept = 0, skipped = 0;
  for (const title of titles) {
    try {
      const id = await upsertFromArticle(title, sourceId);
      if (id) kept++; else skipped++;
    } catch (e) {
      console.error(`[wiki] err ${title}: ${(e as Error).message}`);
      skipped++;
    }
    processed++;
    await new Promise(r => setTimeout(r, 800));         // ~0.8 rps polite
  }

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

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