`scripts/prewarm-la-parcel.mjs`

133 lines
#!/usr/bin/env node
/**
 * Bulk pre-warm la_parcel cache for every public listing in the DB.
 *
 * For each listing: build query string from address_line1 + city, hit ArcGIS,
 * upsert la_parcel. Throttled (300ms gap) to be polite to the public layer.
 *
 * Run from pastdoor cwd (so .env.production loads):
 *   cd /root/public-projects/pastdoor && node scripts/prewarm-la-parcel.mjs
 *
 * Idempotent — skips listings that already have a fresh la_parcel row (TTL 30d
 * via the same logic in lib/la-arcgis.ts).
 */
import pg from 'pg';

const ARCGIS_BASE = 'https://public.gis.lacounty.gov/public/rest/services/LACounty_Cache/LACounty_Parcel/MapServer/0/query';
const FIELDS = [
  'APN', 'SitusFullAddress', 'SitusHouseNo', 'SitusDirection', 'SitusStreet',
  'SitusUnit', 'SitusCity', 'SitusZIP',
  'UseCode', 'UseType', 'UseDescription',
  'YearBuilt1', 'EffectiveYear1',
  'Units1', 'Bedrooms1', 'Bathrooms1', 'SQFTmain1',
  'Roll_Year', 'Roll_LandValue', 'Roll_ImpValue', 'Roll_HomeOwnersExemp',
  'CENTER_LAT', 'CENTER_LON',
].join(',');
const UA = 'pastdoor/1.0-prewarm (https://wholivedthere.com)';

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL, max: 3 });

function normalize(addr) {
  return String(addr).toUpperCase()
    .replace(/[,]/g, ' ')
    .replace(/\b(NORTH|SOUTH|EAST|WEST)\b/g, m => m[0])
    .replace(/\b(STREET|AVENUE|BOULEVARD|DRIVE|ROAD|PLACE|TERRACE|COURT|LANE|WAY)\b/g,
      m => ({ STREET: 'ST', AVENUE: 'AVE', BOULEVARD: 'BLVD', DRIVE: 'DR', ROAD: 'RD', PLACE: 'PL', TERRACE: 'TER', COURT: 'CT', LANE: 'LN', WAY: 'WAY' }[m]))
    .replace(/\s+/g, ' ').trim();
}

function tokens(norm) {
  const parts = norm.split(' ');
  const numIdx = parts.findIndex(p => /^\d+$/.test(p));
  const houseNo = numIdx >= 0 ? parts[numIdx] : null;
  const cityHints = ['BEVERLY','HOLLYWOOD','LOS','WEST','SANTA','PASADENA','BURBANK','GLENDALE','CULVER','MALIBU','LONG'];
  const after = parts.slice(numIdx + 1);
  const cityIdx = after.findIndex(p => cityHints.includes(p));
  const street = (cityIdx >= 0 ? after.slice(0, cityIdx) : after).join(' ');
  return { houseNo, street };
}

async function fetchArcGis(where) {
  const url = `${ARCGIS_BASE}?where=${encodeURIComponent(where)}&outFields=${FIELDS}&f=json&resultRecordCount=1`;
  const r = await fetch(url, { headers: { 'User-Agent': UA, Accept: 'application/json' } });
  if (!r.ok) return [];
  const d = await r.json().catch(() => null);
  return d?.features ?? [];
}

async function lookup(addrInput) {
  const norm = normalize(addrInput);
  const { houseNo, street } = tokens(norm);
  if (!houseNo || !street) return null;
  const tries = [
    `SitusFullAddress LIKE '${houseNo} %${street}%'`,
    `SitusFullAddress LIKE '${houseNo} N ${street}%'`,
    `SitusFullAddress LIKE '${houseNo} S ${street}%'`,
    `SitusFullAddress LIKE '${houseNo} E ${street}%'`,
    `SitusFullAddress LIKE '${houseNo} W ${street}%'`,
  ];
  for (const where of tries) {
    const feats = await fetchArcGis(where);
    if (feats.length) return feats[0].attributes;
  }
  return null;
}

async function upsert(listingId, a) {
  const num = v => { const n = Number(v); return Number.isFinite(n) && n !== 0 ? n : null; };
  const land = num(a.Roll_LandValue);
  const imp  = num(a.Roll_ImpValue);
  await pool.query(
    `INSERT INTO la_parcel
      (apn, listing_id, situs_address, city, zip, use_code, use_type, use_description,
       year_built, effective_year, sqft_main, bedrooms, bathrooms, units,
       roll_year, land_value, improvement_value, total_value, homeowner_exempt,
       lat, lng, raw, fetched_at)
     VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22, now())
     ON CONFLICT (apn) DO UPDATE SET
       listing_id = COALESCE(EXCLUDED.listing_id, la_parcel.listing_id),
       situs_address = EXCLUDED.situs_address,
       year_built = EXCLUDED.year_built,
       sqft_main = EXCLUDED.sqft_main,
       roll_year = EXCLUDED.roll_year,
       land_value = EXCLUDED.land_value,
       improvement_value = EXCLUDED.improvement_value,
       total_value = EXCLUDED.total_value,
       fetched_at = now()`,
    [
      String(a.APN), listingId, a.SitusFullAddress, a.SitusCity?.trim() ?? null, a.SitusZIP?.trim() ?? null,
      a.UseCode ?? null, a.UseType ?? null, a.UseDescription ?? null,
      num(a.YearBuilt1), num(a.EffectiveYear1), num(a.SQFTmain1),
      num(a.Bedrooms1), num(a.Bathrooms1), num(a.Units1),
      num(a.Roll_Year), land, imp, (land ?? 0) + (imp ?? 0) || null, !!a.Roll_HomeOwnersExemp,
      num(a.CENTER_LAT), num(a.CENTER_LON), JSON.stringify(a),
    ]
  );
}

(async () => {
  const { rows } = await pool.query(
    `SELECT l.id, l.slug, l.address_line1, l.city, l.title
     FROM listing l
     LEFT JOIN la_parcel p ON p.listing_id = l.id AND p.fetched_at > now() - interval '30 days'
     WHERE l.is_public = true AND p.apn IS NULL
     ORDER BY l.updated_at DESC`
  );
  console.log(`pre-warming ${rows.length} listings`);
  let hits = 0, misses = 0, errors = 0;
  for (const l of rows) {
    const q = `${l.address_line1 ?? l.title} ${l.city ?? ''}`.trim();
    try {
      const a = await lookup(q);
      if (a) { await upsert(l.id, a); hits++; if (hits % 25 === 0) console.log(`  ${hits} hits / ${misses} misses ...`); }
      else { misses++; }
    } catch (e) {
      errors++;
      console.warn(`  ${l.slug}: ${e?.message ?? e}`);
    }
    await new Promise(r => setTimeout(r, 300));  // be polite
  }
  console.log(`done: ${hits} hits, ${misses} misses, ${errors} errors`);
  await pool.end();
})();