`scripts/backfill-la-neighborhoods.js`

235 lines
#!/usr/bin/env node
// Backfill `businesses.neighborhood` for BBCB shops sitting under the
// placeholder neighborhood='LOS ANGELES' (which is really a county tag, not a
// neighborhood). This is the difference between a directory that says "LA" on
// 12K rows and a directory that says "Silver Lake / Echo Park / K-Town / …"
// — i.e. the difference between a useless directory and a useful one.
//
// Source of truth: USPS ZIP code → LA neighborhood mapping. The City of LA
// formally adopted "Mapping LA" neighborhood boundaries (Times, 2009),
// but for ZIP-level routing we use the simpler postal mapping. Where a ZIP
// straddles two neighborhoods we pick the larger / more iconic one and rely
// on a future address-based pass to refine.
//
// For ZIPs that are NOT inside the City of LA (e.g. Beverly Hills, Torrance,
// Pasadena, Glendale) we set neighborhood = the city name, because those
// users will look for "salons in Beverly Hills" not "salons in Beverly Hills
// neighborhood of LA County".
//
//   node scripts/backfill-la-neighborhoods.js          # full backfill
//   DRY=1 node scripts/backfill-la-neighborhoods.js    # report-only
//   LIMIT=500 node scripts/backfill-la-neighborhoods.js # cap
//
// Idempotent — running it twice is a no-op.

import 'dotenv/config';
import { query, one } from '../src/lib/db.js';

const DRY = process.env.DRY === '1';
const LIMIT = process.env.LIMIT ? parseInt(process.env.LIMIT, 10) : null;

function ts() { return new Date().toISOString().replace('T', ' ').slice(0, 19); }
function log(m) { console.log(`[${ts()}] ${m}`); }

// ZIP5 → neighborhood (or city for non-LA-proper zips).
// City of LA neighborhoods are Title Case; standalone cities are also Title Case.
// Sources: USPS ZIP boundaries × LA Times Mapping LA × common postal usage.
const ZIP_TO_HOOD = {
  // ===== CITY OF LOS ANGELES =====
  // Downtown / DTLA
  '90012':'Downtown LA', '90013':'Downtown LA', '90014':'Downtown LA',
  '90015':'Downtown LA', '90017':'Downtown LA', '90021':'Downtown LA',
  '90071':'Downtown LA',
  // Koreatown / Mid-Wilshire / Westlake
  '90004':'Koreatown', '90005':'Koreatown', '90010':'Koreatown',
  '90020':'Koreatown', '90006':'Koreatown', '90019':'Mid-Wilshire',
  '90036':'Mid-Wilshire', '90057':'Westlake',
  // Hollywood / East Hollywood / Los Feliz / Silver Lake / Echo Park
  '90028':'Hollywood', '90038':'Hollywood', '90068':'Hollywood Hills',
  '90027':'Los Feliz', '90029':'East Hollywood',
  '90039':'Silver Lake', '90026':'Echo Park', '90065':'Highland Park',
  '90042':'Highland Park', '90041':'Eagle Rock', '90031':'Lincoln Heights',
  '90032':'El Sereno', '90033':'Boyle Heights', '90063':'East LA',
  // West LA / Westside
  '90024':'Westwood', '90025':'West LA', '90049':'Brentwood',
  '90064':'Cheviot Hills', '90034':'Palms', '90035':'Beverlywood',
  '90048':'Beverly Grove', '90069':'West Hollywood',
  '90046':'Hollywood Hills West', '90077':'Bel-Air',
  '90272':'Pacific Palisades', '90291':'Venice',
  '90293':'Playa del Rey', '90094':'Playa Vista',
  '90066':'Mar Vista',
  // South LA
  '90001':'South LA', '90002':'South LA', '90003':'South LA',
  '90007':'University Park', '90008':'Crenshaw',
  '90011':'South LA', '90016':'West Adams', '90018':'Jefferson Park',
  '90037':'South LA', '90043':'Hyde Park', '90044':'South LA',
  '90047':'South LA', '90056':'Ladera Heights', '90059':'Watts',
  '90061':'South LA', '90062':'South LA',
  // San Fernando Valley (City of LA portions)
  '91303':'Canoga Park', '91304':'Canoga Park', '91306':'Winnetka',
  '91307':'West Hills', '91311':'Chatsworth', '91316':'Encino',
  '91324':'Northridge', '91325':'Northridge', '91326':'Porter Ranch',
  '91330':'Northridge', '91335':'Reseda', '91340':'San Fernando',
  '91342':'Sylmar', '91343':'North Hills', '91344':'Granada Hills',
  '91345':'Mission Hills', '91352':'Sun Valley', '91356':'Tarzana',
  '91364':'Woodland Hills', '91367':'Woodland Hills',
  '91401':'Van Nuys', '91402':'Panorama City', '91403':'Sherman Oaks',
  '91405':'Van Nuys', '91406':'Van Nuys', '91411':'Sherman Oaks',
  '91423':'Sherman Oaks', '91436':'Encino',
  '91504':'Burbank', '91505':'Burbank', '91506':'Burbank',
  '91601':'North Hollywood', '91602':'Toluca Lake', '91604':'Studio City',
  '91605':'North Hollywood', '91606':'Valley Glen', '91607':'Valley Village',
  // Harbor / Wilmington / San Pedro
  '90731':'San Pedro', '90732':'San Pedro', '90744':'Wilmington',
  '90710':'Harbor City', '90717':'Lomita',

  // ===== STANDALONE LA COUNTY CITIES (non-LA proper) =====
  // Beverly Hills
  '90209':'Beverly Hills', '90210':'Beverly Hills',
  '90211':'Beverly Hills', '90212':'Beverly Hills',
  // Santa Monica
  '90401':'Santa Monica', '90402':'Santa Monica', '90403':'Santa Monica',
  '90404':'Santa Monica', '90405':'Santa Monica',
  // Culver City
  '90230':'Culver City', '90231':'Culver City', '90232':'Culver City',
  // Inglewood / Hawthorne / Lennox
  '90301':'Inglewood', '90302':'Inglewood', '90303':'Inglewood',
  '90304':'Lennox', '90305':'Inglewood',
  '90250':'Hawthorne', '90251':'Hawthorne',
  '90260':'Lawndale', '90261':'Lawndale',
  '90247':'Gardena', '90248':'Gardena', '90249':'Gardena',
  '90274':'Rancho Palos Verdes', '90275':'Rancho Palos Verdes',
  '90277':'Redondo Beach', '90278':'Redondo Beach',
  '90245':'El Segundo', '90266':'Manhattan Beach', '90254':'Hermosa Beach',
  // South Bay
  '90501':'Torrance', '90502':'Torrance', '90503':'Torrance',
  '90504':'Torrance', '90505':'Torrance', '90506':'Torrance',
  '90507':'Torrance', '90508':'Torrance', '90509':'Torrance',
  '90510':'Torrance',
  // Long Beach
  '90802':'Long Beach', '90803':'Long Beach', '90804':'Long Beach',
  '90805':'Long Beach', '90806':'Long Beach', '90807':'Long Beach',
  '90808':'Long Beach', '90809':'Long Beach', '90810':'Long Beach',
  '90813':'Long Beach', '90814':'Long Beach', '90815':'Long Beach',
  // Southeast LA county
  '90201':'Bell', '90202':'Bell Gardens', '90220':'Compton',
  '90221':'Compton', '90222':'Compton', '90255':'Huntington Park',
  '90262':'Lynwood', '90270':'Maywood', '90280':'South Gate',
  '90040':'Commerce', '90022':'East LA', '90023':'East LA',
  '90640':'Montebello', '90660':'Pico Rivera', '90670':'Santa Fe Springs',
  '90706':'Bellflower', '90712':'Lakewood', '90713':'Lakewood',
  '90715':'Lakewood', '90723':'Paramount',
  // East San Gabriel Valley
  '91006':'Arcadia', '91007':'Arcadia', '91016':'Monrovia',
  '91024':'Sierra Madre', '91030':'South Pasadena',
  '91101':'Pasadena', '91103':'Pasadena', '91104':'Pasadena',
  '91105':'Pasadena', '91106':'Pasadena', '91107':'Pasadena',
  '91108':'San Marino', '91123':'Pasadena', '91124':'Pasadena',
  '91125':'Pasadena', '91126':'Pasadena',
  '91201':'Glendale', '91202':'Glendale', '91203':'Glendale',
  '91204':'Glendale', '91205':'Glendale', '91206':'Glendale',
  '91207':'Glendale', '91208':'Glendale', '91214':'La Crescenta',
  '91501':'Burbank', '91502':'Burbank', '91503':'Burbank',
  '91706':'Baldwin Park', '91710':'Chino', '91711':'Claremont',
  '91722':'Covina', '91723':'Covina', '91724':'Covina',
  '91731':'El Monte', '91732':'El Monte', '91733':'South El Monte',
  '91740':'Glendora', '91741':'Glendora', '91744':'La Puente',
  '91745':'Hacienda Heights', '91746':'La Puente', '91748':'Rowland Heights',
  '91750':'La Verne', '91754':'Monterey Park', '91755':'Monterey Park',
  '91765':'Diamond Bar', '91766':'Pomona', '91767':'Pomona',
  '91768':'Pomona', '91770':'Rosemead', '91773':'San Dimas',
  '91775':'San Gabriel', '91776':'San Gabriel', '91780':'Temple City',
  '91789':'Walnut', '91790':'West Covina', '91791':'West Covina',
  '91792':'West Covina', '91801':'Alhambra', '91803':'Alhambra',
  // Santa Clarita Valley
  '91350':'Santa Clarita', '91351':'Canyon Country',
  '91354':'Valencia', '91355':'Valencia', '91381':'Stevenson Ranch',
  '91384':'Castaic', '91387':'Canyon Country', '91390':'Santa Clarita',
  // Antelope Valley
  '93534':'Lancaster', '93535':'Lancaster', '93536':'Lancaster',
  '93550':'Palmdale', '93551':'Palmdale', '93552':'Palmdale',

  // ===== second-pass coverage (top unmapped zips after first run) =====
  '90240':'Downey', '90241':'Downey', '90242':'Downey',
  '90601':'Whittier', '90602':'Whittier', '90603':'Whittier',
  '90604':'Whittier', '90605':'Whittier', '90606':'Whittier',
  '90638':'La Mirada', '90650':'Norwalk',
  '90701':'Artesia', '90703':'Cerritos', '90716':'Hawaiian Gardens',
  '90745':'Carson', '90746':'Carson', '90747':'Carson', '90749':'Long Beach',
  '90045':'Westchester', // Westchester / LAX
  '90265':'Malibu', '90290':'Topanga', '90292':'Marina del Rey',
  '91001':'Altadena', '91010':'Duarte', '91011':'La Cañada Flintridge',
  '91020':'Montrose', '91040':'Sunland', '91042':'Tujunga',
  '91301':'Agoura Hills', '91302':'Calabasas',
  '91321':'Newhall', '91331':'Pacoima', '91346':'Mission Hills',
  '91702':'Azusa',
  '91747':'Hacienda Heights',
  '91755':'Monterey Park', '91756':'Monterey Park',
  '91802':'Alhambra',
  // miscellaneous valley stragglers
  '91357':'Tarzana', '91365':'Woodland Hills', '91371':'West Hills',
  '91408':'Van Nuys', '91410':'Van Nuys', '91414':'Van Nuys',
  '91431':'Sherman Oaks', '91482':'Van Nuys',
  '91804':'Alhambra',
};

async function main() {
  log(`mode=${DRY ? 'DRY' : 'WRITE'}${LIMIT ? ` limit=${LIMIT}` : ''}`);

  // Pull every BBCB shop tagged with the placeholder county. Truncate ZIP+4 to
  // the 5-digit prefix. Skip rows without a usable ZIP.
  const rows = await query(
    `SELECT id, zip
       FROM businesses
      WHERE source_data_json->>'source' = 'dca_bbcb'
        AND neighborhood IN ('LOS ANGELES')
        AND zip IS NOT NULL AND zip != ''
      ${LIMIT ? `LIMIT ${LIMIT}` : ''}`
  );

  log(`fetched ${rows.rows.length} candidate rows`);

  let matched = 0, skipped = 0, updated = 0;
  const hoodHits = {};

  for (const r of rows.rows) {
    const zip5 = (r.zip || '').slice(0, 5);
    const hood = ZIP_TO_HOOD[zip5];
    if (!hood) { skipped++; continue; }
    matched++;
    hoodHits[hood] = (hoodHits[hood] || 0) + 1;
    if (DRY) continue;
    // Update both `neighborhood` and a JSON breadcrumb so a future pass can
    // tell which mapping source set it.
    await query(
      `UPDATE businesses
          SET neighborhood = $1,
              source_data_json = jsonb_set(COALESCE(source_data_json, '{}'::jsonb), '{neighborhood_source}', '"zip-map"'),
              updated_at = now()
        WHERE id = $2`,
      [hood, r.id]
    );
    updated++;
    if (updated % 500 === 0) log(`  …${updated} updated`);
  }

  log(`MATCHED: ${matched} · SKIPPED (no zip-map entry): ${skipped} · UPDATED: ${updated}`);
  // Top hoods
  const top = Object.entries(hoodHits).sort((a,b)=>b[1]-a[1]).slice(0, 20);
  log(`TOP NEIGHBORHOODS BY HIT COUNT:`);
  for (const [h, n] of top) console.log(`    ${String(n).padStart(5)} · ${h}`);

  if (!DRY) {
    const tally = await one(
      `SELECT COUNT(*) FILTER (WHERE neighborhood='LOS ANGELES') AS still_generic,
              COUNT(DISTINCT neighborhood) AS distinct_neighborhoods
         FROM businesses
        WHERE source_data_json->>'source' = 'dca_bbcb'`
    );
    log(`AFTER: still_generic=${tally.still_generic} distinct_neighborhoods=${tally.distinct_neighborhoods}`);
  }
  process.exit(0);
}

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