`scripts/ingest-weho-film-permits.ts`

175 lines
/**
 * ingest-weho-film-permits.ts
 *
 * Pull every WeHo film permit (~1,821 since 2008) from Socrata and upsert into
 * pastdoor's listing + film_production + filming_location tables.
 *
 * Source: https://data.weho.org/resource/xd5y-dzvf.json
 *   - date, production, production_company, category, permit_no, address, location {lat,lng}
 *
 * Ingest pipeline:
 *   - Each WeHo address becomes (or matches) a listing (slug = slugified address + WH suffix)
 *   - Each unique production becomes a film_production row
 *   - Each permit becomes a filming_location row (linking production ↔ listing)
 *
 * Idempotent — safe to run repeatedly. Uses ON CONFLICT for listing.source/source_id and
 * filming_location.permit_number.
 *
 * Tier: A (gov data, City of West Hollywood official record).
 */
import { Pool } from 'pg';

const SOCRATA = 'https://data.weho.org/resource/xd5y-dzvf.json';

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,
});

type WehoRow = {
  date?: string;
  production?: string;
  production_company?: string;
  category?: string;
  permit_no?: string;
  address?: string;
  location?: { type: string; coordinates: [number, number] }; // [lng, lat]
};

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

function classifyKind(category?: string): string {
  const c = (category ?? '').toLowerCase();
  if (c.includes('feature')) return 'film';
  if (c.includes('tv') || c.includes('series')) return 'tv';
  if (c.includes('commercial')) return 'commercial';
  if (c.includes('web')) return 'web';
  if (c.includes('still') || c.includes('photo')) return 'photo';
  if (c.includes('music')) return 'music_video';
  if (c.includes('doc')) return 'documentary';
  if (c.includes('student')) return 'student';
  return 'other';
}

async function fetchAll(): Promise<WehoRow[]> {
  const all: WehoRow[] = [];
  const PAGE = 1000;
  let offset = 0;
  while (true) {
    const res = await fetch(`${SOCRATA}?$limit=${PAGE}&$offset=${offset}&$order=date%20DESC`);
    if (!res.ok) throw new Error(`Socrata ${res.status}`);
    const rows = (await res.json()) as WehoRow[];
    all.push(...rows);
    if (rows.length < PAGE) break;
    offset += PAGE;
  }
  return all;
}

async function upsertListing(row: WehoRow): Promise<string | null> {
  if (!row.address || !row.permit_no) return null;
  const lat = row.location?.coordinates?.[1] ?? null;
  const lng = row.location?.coordinates?.[0] ?? null;
  // Slug includes coords-derived suffix to avoid collisions on similar addresses
  const baseSlug = slugify(row.address) + '-wh';
  const sourceId = `weho-${row.address.replace(/\s+/g, '-').toLowerCase()}`;
  const result = await pool.query<{ id: string }>(
    `INSERT INTO listing (slug, source, source_id, title, address_line1, city, state, country, latitude, longitude, is_public, tier)
     VALUES ($1,'weho_film_permit',$2,$3,$4,'West Hollywood','CA','US',$5,$6,true,'free')
     ON CONFLICT (source, source_id) DO UPDATE SET latitude=COALESCE(listing.latitude,EXCLUDED.latitude), longitude=COALESCE(listing.longitude,EXCLUDED.longitude), updated_at=now()
     RETURNING id`,
    [baseSlug, sourceId, row.address, row.address, lat, lng]
  );
  return result.rows[0]?.id ?? null;
}

async function upsertProduction(row: WehoRow): Promise<string | null> {
  if (!row.production) return null;
  const slug = slugify(row.production) || `production-${row.permit_no}`;
  const kind = classifyKind(row.category);
  const year = row.date ? new Date(row.date).getFullYear() : null;
  const blurb = row.production_company ? `Production company: ${row.production_company}` : null;
  const result = await pool.query<{ id: string }>(
    `INSERT INTO film_production (slug, title, kind, year, blurb, source_tier, source_label)
     VALUES ($1,$2,$3,$4,$5,'A','WeHo Film Permit')
     ON CONFLICT (slug) DO UPDATE SET title=EXCLUDED.title, kind=COALESCE(film_production.kind,EXCLUDED.kind)
     RETURNING id`,
    [slug, row.production, kind, year, blurb]
  );
  return result.rows[0]?.id ?? null;
}

async function upsertFilmingLocation(productionId: string, listingId: string, row: WehoRow): Promise<boolean> {
  if (!row.permit_no) return false;
  const dateStr = row.date ? new Date(row.date).toISOString().slice(0, 10) : null;
  const r = await pool.query(
    `INSERT INTO filming_location (production_id, listing_id, permit_number, role, shoot_date_from, source_tier, source_label, public_visible)
     VALUES ($1,$2,$3,$4,$5,'A','WeHo Film Permit',true)
     ON CONFLICT (production_id, listing_id, permit_number) DO NOTHING`,
    [productionId, listingId, row.permit_no, row.category ?? null, dateStr]
  );
  return (r.rowCount ?? 0) > 0;
}

async function main() {
  console.log(`Fetching all WeHo film permits from ${SOCRATA} ...`);
  const rows = await fetchAll();
  console.log(`  → ${rows.length} rows`);

  let listingsTouched = 0;
  let productionsTouched = 0;
  let filmingLocationsInserted = 0;
  let skipped = 0;

  for (const [i, row] of rows.entries()) {
    if (!row.address || !row.permit_no || !row.production) {
      skipped++;
      continue;
    }
    try {
      const listingId = await upsertListing(row);
      if (!listingId) { skipped++; continue; }
      listingsTouched++;
      const productionId = await upsertProduction(row);
      if (!productionId) { skipped++; continue; }
      productionsTouched++;
      if (await upsertFilmingLocation(productionId, listingId, row)) {
        filmingLocationsInserted++;
      }
    } catch (e) {
      console.error(`Row ${i} (${row.permit_no}): ${(e as Error).message}`);
      skipped++;
    }
    if ((i + 1) % 100 === 0) {
      console.log(`  ${i + 1}/${rows.length} processed`);
    }
  }

  console.log('');
  console.log({
    rows: rows.length,
    listings_touched: listingsTouched,
    productions_touched: productionsTouched,
    filming_locations_inserted: filmingLocationsInserted,
    skipped,
  });
  await pool.end();
}

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