`scripts/ingest-wikipedia-named-houses.ts`

142 lines
/**
 * ingest-wikipedia-named-houses.ts
 *
 * Pulls Wikipedia category "Houses in Beverly Hills, California" + sister
 * categories (Bel Air, Hollywood Hills, Holmby Hills, Brentwood, Hancock Park)
 * to seed named historical estates (Pickfair, Greystone, Falcon Lair, etc.)
 * into the archive.
 *
 * For each article: fetch summary (title, description, extract, lat/lon, URL),
 * then upsert as a `listing` row + a `place_event` row tagged with kind='named_estate'
 * pointing back to the actual Wikipedia article (drill-down per the
 * feedback_drill_down_archive_urls memory rule).
 *
 * Tier: B (Wikipedia is verified-aggregator, not primary archive).
 */
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,
  password: process.env.PGPASSWORD,
  port: parseInt(process.env.PGPORT ?? '5432', 10),
  max: 4,
});

const CATEGORIES = [
  'Houses in Beverly Hills, California',
  'National Register of Historic Places in Los Angeles County, California',
  'Houses on the National Register of Historic Places in California',
  'Historic American Buildings Survey in California',
  'Houses in Pasadena, California',
  'Houses in Santa Monica, California',
  'Tourist attractions in Los Angeles',
  'Buildings and structures in Beverly Hills, California',
  'Historic districts in Los Angeles',
];

async function fetchCategoryMembers(catTitle: string): Promise<string[]> {
  const url = `https://en.wikipedia.org/w/api.php?action=query&list=categorymembers&cmtitle=${encodeURIComponent('Category:' + catTitle)}&cmlimit=200&format=json`;
  const res = await fetch(url, { headers: { 'User-Agent': 'stayclaim-ingest/1.0 (+https://wholivedthere.com)' } });
  if (!res.ok) return [];
  const j = await res.json() as { query?: { categorymembers?: { title: string; ns: number }[] } };
  return (j.query?.categorymembers ?? []).filter(m => m.ns === 0).map(m => m.title);
}

async function fetchSummary(title: string) {
  const url = `https://en.wikipedia.org/api/rest_v1/page/summary/${encodeURIComponent(title.replace(/ /g, '_'))}`;
  const res = await fetch(url, { headers: { 'User-Agent': 'stayclaim-ingest/1.0 (+https://wholivedthere.com)' } });
  if (!res.ok) return null;
  const j = await res.json() as any;
  return {
    title: j.title as string,
    description: j.description as string | undefined,
    extract: j.extract as string | undefined,
    coords: j.coordinates as { lat: number; lon: number } | undefined,
    url: j.content_urls?.desktop?.page as string | undefined,
    pageId: j.pageid as number | undefined,
  };
}

function slugify(s: string): string {
  return s.toLowerCase().replace(/[^\w\s-]/g, '').replace(/\s+/g, '-').replace(/-+/g, '-').replace(/^-|-$/g, '').slice(0, 90);
}

async function ensureListing(name: string, summary: any, city: string): Promise<string | null> {
  if (!summary.coords) return null;
  const slug = `${slugify(name)}-house-wp`;
  const r = await pool.query<{ id: string }>(
    `INSERT INTO listing
       (slug, source, source_id, title, address_line1, city, state, country, latitude, longitude, is_public, tier, headline, description)
     VALUES ($1,'wikipedia_house',$2,$3,$4,$5,'CA','US',$6,$7,true,'free',$8,$9)
     ON CONFLICT (slug) DO UPDATE SET
       latitude = COALESCE(listing.latitude, EXCLUDED.latitude),
       longitude = COALESCE(listing.longitude, EXCLUDED.longitude),
       headline = COALESCE(listing.headline, EXCLUDED.headline),
       description = COALESCE(listing.description, EXCLUDED.description),
       updated_at = now()
     RETURNING id`,
    [
      slug,
      `wikipedia:${summary.pageId ?? slug}`,
      name,                                        // title — the named estate
      `${name} (named estate)`,                    // address_line1 — placeholder; usually the article body has actual address
      city,
      summary.coords.lat,
      summary.coords.lon,
      summary.description ?? null,
      summary.extract ?? null,
    ]
  );
  return r.rows[0]?.id ?? null;
}

async function ensureEvent(listingId: string, name: string, summary: any) {
  await pool.query(
    `INSERT INTO place_event
       (listing_id, kind, summary, source_tier, source_label, source_url, source_id, public_visible, confidence)
     VALUES ($1,'named_estate',$2,'B','Wikipedia',$3,$4,true,0.9)
     ON CONFLICT DO NOTHING`,
    [
      listingId,
      `${name}${summary.description ? ` — ${summary.description}` : ''}.${summary.extract ? ' ' + summary.extract.slice(0, 250) : ''}`,
      summary.url ?? `https://en.wikipedia.org/wiki/${encodeURIComponent(name.replace(/ /g, '_'))}`,
      `wp_estate:${summary.pageId ?? slugify(name)}`,
    ]
  );
}

async function main() {
  let totalProcessed = 0, totalIngested = 0, totalSkipped = 0;
  for (const cat of CATEGORIES) {
    const titles = await fetchCategoryMembers(cat);
    console.log(`\n=== Category: ${cat} (${titles.length} articles) ===`);
    const cityHint = cat.replace(/^Houses in /, '').replace(/^Mansions in /, '').replace(/, .*$/, '').replace(', Los Angeles', '').trim();
    for (const title of titles) {
      const summary = await fetchSummary(title);
      if (!summary) { totalSkipped++; continue; }
      if (!summary.coords) {
        console.log(`  - ${title}: no coords, skipping`);
        totalSkipped++;
        continue;
      }
      const lid = await ensureListing(title, summary, cityHint);
      if (lid) {
        await ensureEvent(lid, title, summary);
        totalIngested++;
        console.log(`  ✓ ${title} (${summary.coords.lat.toFixed(4)}, ${summary.coords.lon.toFixed(4)})`);
      } else {
        totalSkipped++;
      }
      totalProcessed++;
      // Polite Wikipedia rate (1 req/sec sustained)
      await new Promise(r => setTimeout(r, 250));
    }
  }
  console.log(`\n✓ named houses: ${totalIngested} ingested, ${totalSkipped} skipped, ${totalProcessed} processed`);
  await pool.end();
}

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