`scripts/sources/wholivedthere.js`

102 lines
// sources/wholivedthere.js — READ-ONLY bridge from the CRE app to the WhoLivedThere / stayclaim /
// pastdoor archive (local Postgres DB `pastdoor`). Given a listing's street address, returns the
// property-history facts the archive holds: LA County assessor parcel (year built, units, use type,
// assessed land/improvement/total value), building-permit history (alterations + valuations),
// curated place events, and film-shoot history ("filmed here").
//
// HONEST SCOPE: pastdoor is a CURATED archive, not a full county parcel DB (la_parcel ~252,
// la_permit ~25, place_event ~71, filming_location ~1528 but keyed to featured listings). So most
// commercial CRE addresses will return NO match — that's expected. When it DOES hit, the assessor
// value + permit history are genuinely useful underwriting cross-checks. The bridge never throws into
// the request path: any DB error returns { available:false } so the CRE app degrades gracefully.
//
// Connection: local Unix socket (/tmp) as the OS user, matching how psql reaches pastdoor (peer auth,
// no password). Override with WLT_PG* env if the DB moves. Read-only by intent — only SELECTs here.

'use strict';
const { Pool } = require('pg');

const pool = new Pool({
  host: process.env.WLT_PGHOST || '/tmp',
  port: +(process.env.WLT_PGPORT || 5432),
  database: process.env.WLT_PGDATABASE || 'pastdoor',
  user: process.env.WLT_PGUSER || process.env.USER || 'stevestudio2',
  max: 4, idleTimeoutMillis: 10000, connectionTimeoutMillis: 4000,
  // statement_timeout guards against a runaway query blocking the CRE viewer.
  statement_timeout: 4000
});
pool.on('error', () => {}); // never crash the host process on an idle-client error

// Split a street address into a leading house number + the first significant street-name word, so we
// can match "13350 Victory Blvd" against assessor strings like "13350 VICTORY BLVD VAN NUYS CA 91401".
function addrParts(address) {
  const s = String(address || '').trim();
  const num = (s.match(/^\s*(\d+)/) || [])[1] || '';
  const name = (s.replace(/^\s*\d+\s*/, '')
    .replace(/\b(n|s|e|w|north|south|east|west)\b/gi, ' ')
    .match(/[a-z]{3,}/i) || [])[0] || '';
  return { num, name };
}

let warned = false;

// lookupAddress: best-effort property-history enrichment for one address. Returns:
//   { available:true, matched:true, parcel, permits[], events[], filming[] }  on a hit
//   { available:true, matched:false }                                          when the archive has nothing
//   { available:false, reason }                                                when the DB can't be reached
async function lookupAddress(address, city /*, zip */) {
  const { num, name } = addrParts(address);
  if (!num || !name) return { available: true, matched: false };
  let client;
  try {
    client = await pool.connect();
    // Parcel: number-prefix AND street-name contained. City is a soft bonus (assessor city strings
    // are abbreviated, e.g. "W HOLLYWOOD CA"), so we don't hard-filter on it.
    const parcelQ = await client.query(
      `select apn, situs_address, city, zip, year_built, effective_year, sqft_main, units, use_type,
              use_description, land_value, improvement_value, total_value, roll_year, listing_id
         from la_parcel
        where situs_address ilike $1 and situs_address ilike $2
        order by (city ilike $3) desc, roll_year desc nulls last
        limit 1`,
      [num + '%', '%' + name + '%', '%' + String(city || '') + '%']
    );
    const parcel = parcelQ.rows[0] || null;

    const permitsQ = await client.query(
      `select permit_nbr, primary_address, permit_type, permit_subtype, status, issue_date, valuation, use_description
         from la_permit
        where primary_address ilike $1 and primary_address ilike $2
        order by issue_date desc nulls last
        limit 8`,
      [num + '%', '%' + name + '%']
    );

    let events = [], filming = [];
    if (parcel && parcel.listing_id) {
      const ev = await client.query(
        `select kind, summary, valid_from, source_label
           from place_event
          where listing_id = $1 and coalesce(public_visible, true) = true
          order by valid_from desc nulls last limit 10`, [parcel.listing_id]);
      events = ev.rows;
      const fl = await client.query(
        `select fl.role, fl.scene_note, fl.shoot_date_from, p.title, p.year, p.kind
           from filming_location fl left join film_production p on p.id = fl.production_id
          where fl.listing_id = $1 and coalesce(fl.public_visible, true) = true
          order by fl.shoot_date_from desc nulls last limit 10`, [parcel.listing_id]);
      filming = fl.rows;
    }

    const matched = !!(parcel || permitsQ.rows.length);
    return { available: true, matched, parcel, permits: permitsQ.rows, events, filming };
  } catch (e) {
    if (!warned) { console.warn('[wholivedthere] DB unavailable:', e.message); warned = true; }
    return { available: false, reason: e.message };
  } finally {
    if (client) client.release();
  }
}

module.exports = { lookupAddress, addrParts, pool };