`src/lib/la-permits.ts`

189 lines
/**
 * LA City permits adapter — Socrata `pi9x-tg5x` (Building and Safety).
 *
 * Limitation: This is the City of LA dataset only. Beverly Hills, Santa
 * Monica, Culver City, West Hollywood, Pasadena etc. are separate
 * jurisdictions with their own portals; each needs its own adapter.
 *
 * APN format here is digits-only (no hyphens) — `4343024017` not `4343-024-017`.
 * Cache in `la_permit` table.
 */
import { pool } from './db';

const SOCRATA_BASE = 'https://data.lacity.org/resource/pi9x-tg5x.json';
const UA = 'pastdoor/1.0 (https://wholivedthere.com)';
const CACHE_TTL_DAYS = 7; // permits update more often than parcel data

export type LAPermit = {
  permit_nbr: string;
  apn: string;
  primary_address: string | null;
  zip_code: string | null;
  permit_type: string | null;
  permit_subtype: string | null;
  permit_group: string | null;
  status: string | null;
  issue_date: string | null;     // ISO date
  status_date: string | null;
  valuation: number | null;
  use_code: string | null;
  use_description: string | null;
  jurisdiction: string;
};

function apnDigits(apn: string): string {
  // Strict digits-only — Socrata APN field is a 10-digit string. Anything else
  // (whitespace, hyphens, '&', quotes, SoQL metachars) is dropped before the
  // value is interpolated into the request URL.
  return String(apn).replace(/\D/g, '');
}

async function fetchJson(url: string): Promise<unknown> {
  // 5s hard timeout — without AbortSignal a hung Socrata endpoint blocks the
  // server-side caller indefinitely. Mirrors la-arcgis pattern.
  const r = await fetch(url, {
    headers: { 'User-Agent': UA, Accept: 'application/json' },
    signal: AbortSignal.timeout(5_000),
  });
  if (!r.ok) throw new Error(`socrata ${r.status}`);
  return r.json();
}

/** Cache-first lookup. Returns up to `limit` permits for an APN. */
export async function getPermitsForApn(apnInput: string, opts: { limit?: number } = {}): Promise<LAPermit[]> {
  const apn = apnDigits(apnInput);
  if (!apn) return [];
  const limit = Math.min(opts.limit ?? 50, 200);

  // Check cache freshness — if any permit for this APN was fetched within TTL, trust the cache.
  try {
    const fresh = await pool.query<{ exists: boolean }>(
      `SELECT EXISTS (SELECT 1 FROM la_permit WHERE apn = $1 AND fetched_at > now() - ($2 || ' days')::interval) AS exists`,
      [apn, CACHE_TTL_DAYS]
    );
    if (fresh.rows[0]?.exists) {
      const { rows } = await pool.query<LAPermit>(
        `SELECT permit_nbr, apn, primary_address, zip_code, permit_type, permit_subtype, permit_group,
                status, issue_date::text, status_date::text, valuation, use_code, use_description, jurisdiction
         FROM la_permit
         WHERE apn = $1
         ORDER BY issue_date DESC NULLS LAST
         LIMIT $2`,
        [apn, limit]
      );
      return rows;
    }
  } catch (e) {
    // Table may not exist yet — fall through to fetch.
    // Use pg error code 42P01 (undefined_table) primarily; fall back to message
    // match for non-pg errors.
    const code = (e as { code?: string } | null)?.code;
    if (code !== '42P01' && !(e instanceof Error && /relation .* does not exist/.test(e.message))) throw e;
  }

  // Fetch from Socrata. APN is digit-sanitized above; encodeURIComponent is
  // belt-and-braces.
  const url = `${SOCRATA_BASE}?apn=${encodeURIComponent(apn)}&$order=issue_date DESC&$limit=${encodeURIComponent(String(limit))}`;
  let data: unknown;
  try {
    data = await fetchJson(url);
  } catch (e) {
    // Transient network/timeout failure — fall back to the cache regardless of
    // TTL so callers don't see "no permits" when the truth is "lookup failed."
    console.warn(`la_permits fetch: ${e instanceof Error ? e.message : e}`);
    try {
      const { rows } = await pool.query<LAPermit>(
        `SELECT permit_nbr, apn, primary_address, zip_code, permit_type, permit_subtype, permit_group,
                status, issue_date::text, status_date::text, valuation, use_code, use_description, jurisdiction
         FROM la_permit
         WHERE apn = $1
         ORDER BY issue_date DESC NULLS LAST
         LIMIT $2`,
        [apn, limit]
      );
      return rows;
    } catch {
      return [];
    }
  }
  const rows = Array.isArray(data) ? data : [];
  if (!rows.length) return [];

  // Build permit_nbr → raw row map once so the cache-write loop doesn't
  // re-scan `rows` on every iteration (was O(n^2)).
  const rawByPermit = new Map<string, Record<string, unknown>>();
  for (const r of rows) {
    const x = r as Record<string, unknown>;
    const k = String(x.permit_nbr ?? '');
    if (k) rawByPermit.set(k, x);
  }

  const permits: LAPermit[] = rows.map(r => {
    const x = r as Record<string, unknown>;
    // Keep 0 as a real valuation (legitimate "no-cost" permits exist). Only
    // null/undefined/'' are missing. NaN is also missing.
    const valRaw = x.valuation;
    let valuation: number | null = null;
    if (valRaw !== null && valRaw !== undefined && valRaw !== '') {
      const n = Number(valRaw);
      valuation = Number.isFinite(n) ? n : null;
    }
    return {
      permit_nbr: String(x.permit_nbr ?? ''),
      apn,
      primary_address: (x.primary_address as string) ?? null,
      zip_code: (x.zip_code as string) ?? null,
      permit_type: (x.permit_type as string) ?? null,
      permit_subtype: (x.permit_sub_type as string) ?? null,
      permit_group: (x.permit_group as string) ?? null,
      status: (x.status as string) ?? null,
      issue_date: typeof x.issue_date === 'string' ? x.issue_date.slice(0, 10) : null,
      status_date: typeof x.status_date === 'string' ? x.status_date.slice(0, 10) : null,
      valuation,
      use_code: (x.use_code as string) ?? null,
      use_description: (x.use_desc as string) ?? null,
      jurisdiction: 'LA City',
    };
  }).filter(p => p.permit_nbr);

  // Best-effort cache write. Refresh all mutable columns in DO UPDATE so
  // upstream changes to address/type/use_description don't get pinned to
  // the first-seen value forever.
  try {
    for (const p of permits) {
      await pool.query(
        `INSERT INTO la_permit
          (permit_nbr, apn, primary_address, zip_code, permit_type, permit_subtype, permit_group,
           status, issue_date, status_date, valuation, use_code, use_description, jurisdiction, raw, fetched_at)
         VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9::date,$10::date,$11,$12,$13,$14,$15, now())
         ON CONFLICT (permit_nbr) DO UPDATE SET
           apn = EXCLUDED.apn,
           primary_address = EXCLUDED.primary_address,
           zip_code = EXCLUDED.zip_code,
           permit_type = EXCLUDED.permit_type,
           permit_subtype = EXCLUDED.permit_subtype,
           permit_group = EXCLUDED.permit_group,
           status = EXCLUDED.status,
           issue_date = EXCLUDED.issue_date,
           status_date = EXCLUDED.status_date,
           valuation = EXCLUDED.valuation,
           use_code = EXCLUDED.use_code,
           use_description = EXCLUDED.use_description,
           jurisdiction = EXCLUDED.jurisdiction,
           raw = EXCLUDED.raw,
           fetched_at = now()`,
        [p.permit_nbr, p.apn, p.primary_address, p.zip_code, p.permit_type, p.permit_subtype, p.permit_group,
         p.status, p.issue_date, p.status_date, p.valuation, p.use_code, p.use_description, p.jurisdiction,
         JSON.stringify(rawByPermit.get(p.permit_nbr) ?? {})]
      );
    }
  } catch (e) {
    const code = (e as { code?: string } | null)?.code;
    if (code !== '42P01' && !(e instanceof Error && /relation .* does not exist/.test(e.message))) {
      console.warn('la_permit upsert:', e instanceof Error ? e.message : e);
    }
  }

  return permits;
}