`src/lib/la-arcgis.ts`

281 lines
/**
 * LA County GIS ArcGIS REST client — TS port of mcp-la-records/src/arcgis.mjs.
 *
 * Why duplicated: pastdoor is server-side and can call the public ArcGIS
 * endpoint directly without going through MCP/stdio. The MCP server stays
 * for agent + CLI use; this module is the in-app caller.
 *
 * Cached in `la_parcel` PG table — see migration 013_la_parcel_cache.sql.
 */
import { pool } from './db';

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 (https://wholivedthere.com)';
const CACHE_TTL_DAYS = 30;

export type LAParcel = {
  apn: string;
  listing_id: string | null;
  situs_address: string | null;
  city: string | null;
  zip: string | null;
  use_code: string | null;
  use_type: string | null;
  use_description: string | null;
  year_built: number | null;
  effective_year: number | null;
  sqft_main: number | null;
  bedrooms: number | null;
  bathrooms: number | null;
  units: number | null;
  roll_year: number | null;
  land_value: number | null;
  improvement_value: number | null;
  total_value: number | null;
  homeowner_exempt: boolean;
  lat: number | null;
  lng: number | null;
  fetched_at: Date | string;
};

function normalizeForSearch(addr: string): string {
  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' } as Record<string, string>)[m] ?? m
    )
    .replace(/\s+/g, ' ')
    .trim();
}

function tokens(normalized: string): { houseNo: string | null; street: string } {
  const parts = normalized.split(' ');
  const numIdx = parts.findIndex(p => /^\d+$/.test(p));
  const houseNo = numIdx >= 0 ? parts[numIdx] : null;
  const after = parts.slice(numIdx + 1);

  // Cities are matched as multi-word phrases scanning from the end of the
  // address — single-token hints (e.g., 'SANTA', 'WEST') falsely truncated
  // streets like 'SANTA MONICA BLVD' or 'W HOLLYWOOD BLVD' to empty. Phrases
  // use the post-normalize form, so 'WEST'→'W' and 'BOULEVARD'→'BLVD'.
  const cityPhrases: string[][] = [
    ['BEVERLY', 'HILLS'],
    ['W', 'HOLLYWOOD'],
    ['SANTA', 'MONICA'],
    ['LOS', 'ANGELES'],
    ['CULVER', 'CITY'],
    ['LONG', 'BEACH'],
    ['HOLLYWOOD'],
    ['PASADENA'],
    ['BURBANK'],
    ['GLENDALE'],
    ['MALIBU'],
  ];

  // Match the longest city phrase that is a suffix of `after`. Longest-first
  // so 'W HOLLYWOOD' wins over 'HOLLYWOOD' when both could match.
  const phrasesByLen = [...cityPhrases].sort((a, b) => b.length - a.length);
  let cityStart = -1;
  for (const phrase of phrasesByLen) {
    if (phrase.length > after.length) continue;
    const start = after.length - phrase.length;
    let match = true;
    for (let j = 0; j < phrase.length; j++) {
      if (after[start + j] !== phrase[j]) { match = false; break; }
    }
    if (match) { cityStart = start; break; }
  }

  const street = (cityStart >= 0 ? after.slice(0, cityStart) : after).join(' ');
  return { houseNo, street };
}

async function fetchArcGis(where: string): Promise<unknown[]> {
  const url = `${ARCGIS_BASE}?where=${encodeURIComponent(where)}&outFields=${FIELDS}&f=json&resultRecordCount=5`;
  // 5s hard timeout — without AbortSignal a hung ArcGIS endpoint blocks the
  // server-side caller indefinitely (and the loop runs up to 5 of these in series).
  const r = await fetch(url, {
    headers: { 'User-Agent': UA, Accept: 'application/json' },
    signal: AbortSignal.timeout(5_000),
  });
  if (!r.ok) throw new Error(`arcgis ${r.status}`);
  const data: unknown = await r.json();
  // eslint-disable-next-line @typescript-eslint/no-explicit-any
  return ((data as any)?.features ?? []) as unknown[];
}

function normalizeArcGisRow(a: Record<string, unknown>): Omit<LAParcel, 'listing_id' | 'fetched_at'> | null {
  if (!a.APN) return null;
  // Keep 0 as a real value — studios (Bedrooms1=0), non-residential parcels
  // (Units1=0), and zero-value rolls are legitimate data, not "missing".
  const num = (v: unknown) => {
    if (v === null || v === undefined || v === '') return null;
    const n = Number(v);
    return Number.isFinite(n) ? n : null;
  };
  const land = num(a.Roll_LandValue);
  const imp  = num(a.Roll_ImpValue);
  const totalValue = land === null && imp === null ? null : (land ?? 0) + (imp ?? 0);
  // ArcGIS returns the HOX field as a flag/dollar amount, often a string like
  // "N"/"Y"/"0"/"7000". `!!a.Roll_HomeOwnersExemp` is true for "N" too, so
  // explicitly coerce: numeric > 0 OR Y means exempted.
  const hox = a.Roll_HomeOwnersExemp;
  const hoxNum = Number(hox);
  const homeownerExempt = Number.isFinite(hoxNum)
    ? hoxNum > 0
    : typeof hox === 'string' && /^Y/i.test(hox.trim());
  return {
    apn: String(a.APN),
    situs_address: (a.SitusFullAddress as string) ?? null,
    city: (a.SitusCity as string)?.trim() ?? null,
    zip: (a.SitusZIP as string)?.trim() ?? null,
    use_code: (a.UseCode as string) ?? null,
    use_type: (a.UseType as string) ?? null,
    use_description: (a.UseDescription as string) ?? null,
    year_built: num(a.YearBuilt1),
    effective_year: num(a.EffectiveYear1),
    sqft_main: num(a.SQFTmain1),
    bedrooms: num(a.Bedrooms1),
    bathrooms: num(a.Bathrooms1),
    units: num(a.Units1),
    roll_year: num(a.Roll_Year),
    land_value: land,
    improvement_value: imp,
    total_value: totalValue,
    homeowner_exempt: homeownerExempt,
    lat: num(a.CENTER_LAT),
    lng: num(a.CENTER_LON),
  };
}

/**
 * Cache-first lookup. Returns the cached row if fresh; otherwise hits ArcGIS,
 * stores, and returns. Returns null if no parcel matches.
 */
export async function getOrFetchParcelForAddress(
  addressInput: string,
  listingId: string | null
): Promise<LAParcel | null> {
  // 1. Cache hit by listing_id (fastest)
  if (listingId) {
    try {
      const { rows } = await pool.query<LAParcel>(
        `SELECT * FROM la_parcel WHERE listing_id = $1 AND fetched_at > now() - ($2 || ' days')::interval LIMIT 1`,
        [listingId, CACHE_TTL_DAYS]
      );
      if (rows[0]) return rows[0];
    } catch (e) {
      // Table may not exist yet on a fresh deploy — fall through to fetch.
      if (!(e instanceof Error && /relation .* does not exist/.test(e.message))) throw e;
    }
  }

  // 2. Hit ArcGIS
  const norm = normalizeForSearch(addressInput);
  const { houseNo, street } = tokens(norm);

  // Hard-allowlist tokens used in WHERE clauses. ArcGIS treats single quotes
  // as string delimiters and supports LIKE wildcards (% and _) — both are
  // meaningful and must not survive into a where clause built from user input.
  // Refuse to interpolate anything that doesn't match. Belt-and-braces: also
  // strip any character outside the allowlist before interpolation.
  const sanitizeNo     = (s: string) => /^\d{1,8}$/.test(s) ? s : '';
  const sanitizeStreet = (s: string) => s.replace(/[^A-Z0-9 ]/g, '').trim().slice(0, 60);
  const safeNo     = houseNo ? sanitizeNo(houseNo) : '';
  const safeStreet = street ? sanitizeStreet(street) : '';

  const tries: string[] = [];
  if (safeNo && safeStreet) {
    tries.push(`SitusFullAddress LIKE '${safeNo} %${safeStreet}%'`);
    tries.push(`SitusFullAddress LIKE '${safeNo} N ${safeStreet}%'`);
    tries.push(`SitusFullAddress LIKE '${safeNo} S ${safeStreet}%'`);
    tries.push(`SitusFullAddress LIKE '${safeNo} E ${safeStreet}%'`);
    tries.push(`SitusFullAddress LIKE '${safeNo} W ${safeStreet}%'`);
  }
  if (!safeNo && safeStreet) {
    tries.push(`SitusFullAddress LIKE '%${safeStreet}%'`);
  }
  if (tries.length === 0) {
    // Input was either empty or contained only disallowed chars (apostrophe-only
    // queries like O'Connor, ArcGIS wildcards like '%' or '_', SQL
    // metacharacters). Refuse the lookup.
    console.warn(`la-arcgis: refused unsafe input "${addressInput}"`);
    return null;
  }

  let raw: Record<string, unknown> | null = null;
  let anyCompleted = false;
  for (const where of tries) {
    let feats: unknown[];
    try {
      feats = await fetchArcGis(where);
      anyCompleted = true;
    } catch (e) {
      console.warn(`arcgis ${where}: ${e instanceof Error ? e.message : e}`);
      continue;
    }
    if (feats.length) {
      // eslint-disable-next-line @typescript-eslint/no-explicit-any
      raw = (feats[0] as any).attributes;
      break;
    }
  }
  // If every attempt threw (network/timeout), surface that to the caller as a
  // real error instead of "no parcel" — otherwise transient failures get
  // negative-cached as definitive misses.
  if (!raw && !anyCompleted) {
    throw new Error('arcgis: all attempts failed');
  }
  if (!raw) return null;

  const norm2 = normalizeArcGisRow(raw);
  if (!norm2) return null;

  // 3. Cache (best-effort — table may not exist yet)
  try {
    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,
         city = EXCLUDED.city, zip = EXCLUDED.zip,
         use_code = EXCLUDED.use_code, use_type = EXCLUDED.use_type, use_description = EXCLUDED.use_description,
         year_built = EXCLUDED.year_built, effective_year = EXCLUDED.effective_year,
         sqft_main = EXCLUDED.sqft_main, bedrooms = EXCLUDED.bedrooms, bathrooms = EXCLUDED.bathrooms, units = EXCLUDED.units,
         roll_year = EXCLUDED.roll_year, land_value = EXCLUDED.land_value, improvement_value = EXCLUDED.improvement_value,
         total_value = EXCLUDED.total_value, homeowner_exempt = EXCLUDED.homeowner_exempt,
         lat = EXCLUDED.lat, lng = EXCLUDED.lng, raw = EXCLUDED.raw, fetched_at = now()`,
      [norm2.apn, listingId, norm2.situs_address, norm2.city, norm2.zip,
       norm2.use_code, norm2.use_type, norm2.use_description,
       norm2.year_built, norm2.effective_year, norm2.sqft_main, norm2.bedrooms, norm2.bathrooms, norm2.units,
       norm2.roll_year, norm2.land_value, norm2.improvement_value, norm2.total_value, norm2.homeowner_exempt,
       norm2.lat, norm2.lng, JSON.stringify(raw)]
    );
  } catch (e) {
    if (!(e instanceof Error && /relation .* does not exist/.test(e.message))) {
      console.warn('la_parcel upsert:', e instanceof Error ? e.message : e);
    }
  }

  return { ...norm2, listing_id: listingId, fetched_at: new Date() };
}