`scripts/build-deed-history.js`

97 lines
// build-deed-history.js — Part (a) of the CRE deeds FREE-PASS.
// Extends the 13-record broker-history PILOT to ALL 1,407 listings using the FREE
// LA County assessor roll (assessor_parcel): attaches the last deed recording_date,
// AIN, and year_built to every listing via the same address match serve.js uses.
//
// FREE + LOCAL only (cre DB). No paid API. Broker (part b) stays null on the new
// records — deeds carry no agent name (Gov Code §6254.21); current-listing broker
// enrichment is the separate gated pass. The 13 pilot records (which already have
// current_broker + B-tier sale history) are MERGE-PRESERVED, never downgraded.
//
// Usage: node scripts/build-deed-history.js   (writes data/broker-history-full.json)

const fs = require('fs');
const path = require('path');
const { pool } = require('./db/brokers-db');

const ROOT = path.join(__dirname, '..');
const LISTINGS = path.join(ROOT, 'data', 'listings.json');
const PILOT = path.join(ROOT, 'data', 'broker-history-pilot.json');
const OUT = path.join(ROOT, 'data', 'broker-history-full.json');

// Same address parse serve.js /api/property-history uses (house number + first 3+ letter word).
const parse = (address) => ({
  num: (String(address).match(/^\s*(\d+)/) || [])[1] || '',
  word: (String(address).replace(/^\s*\d+\s*/, '').match(/[a-z]{3,}/i) || [])[0] || '',
});

(async () => {
  const listingsRaw = JSON.parse(fs.readFileSync(LISTINGS, 'utf8'));
  const listings = Array.isArray(listingsRaw) ? listingsRaw : (listingsRaw.listings || listingsRaw.properties || []);
  const pilot = JSON.parse(fs.readFileSync(PILOT, 'utf8'));
  const pilotById = new Map((pilot.properties || []).map((p) => [p.id, p]));

  let matched = 0, deed = 0, preserved = 0;
  const properties = [];

  for (const l of listings) {
    // Preserve the fully-enriched pilot record verbatim (has current_broker + tx history).
    if (pilotById.has(l.id)) { properties.push(pilotById.get(l.id)); preserved++; continue; }

    const { num, word } = parse(l.address);
    let row = null;
    if (num && word) {
      try {
        row = (await pool.query(
          `SELECT ain, year_built, units, recording_date
             FROM assessor_parcel WHERE situs_house_no=$1 AND situs_street ILIKE $2
             ORDER BY roll_year DESC NULLS LAST LIMIT 1`,
          [+num, '%' + word + '%'])).rows[0] || null;
      } catch (_) { /* table absent — degrade */ }
    }
    if (row) matched++;
    const deedDate = row && row.recording_date
      ? new Date(row.recording_date).toISOString().slice(0, 10) : null;
    if (deedDate) deed++;

    const tx = [];
    if (deedDate) tx.push({
      date: deedDate,
      event: 'Deed recorded (LA County assessor)',
      price: null,
      source: `LA County assessor_parcel, AIN ${row.ain}`,
      tier: 'A',
    });

    properties.push({
      id: l.id,
      address: l.address,
      city: l.city,
      ain: row ? String(row.ain) : null,
      assessor_year_built: (row && row.year_built) || l.year_built || null,
      assessor_units: (row && row.units) || l.units || null,
      last_deed_date: deedDate,
      sale_price_search: null,
      tx_history: tx,
      current_broker: null,           // part (b) — gated enrichment fills this later
      broker_flag: false,
    });
  }

  await pool.end();

  const full = {
    meta: {
      scope: `FULL — all ${listings.length} ranked listings. Deed date/AIN/year-built from the free LA County assessor roll (assessor_parcel). ${preserved} top records retain full broker + sale-history enrichment from the pilot; the rest are deed-only until the gated current-listing-broker pass runs.`,
      sources: (pilot.meta && pilot.meta.sources) || ['LA County Assessor (assessor_parcel — recording_date = last deed recording)'],
      broker_caveat: (pilot.meta && pilot.meta.broker_caveat) || 'Broker attribution requires the separate current-listing enrichment pass; deeds carry no agent name (Gov Code §6254.21).',
      counts: { listings: listings.length, assessor_matched: matched, with_deed_date: deed, pilot_preserved: preserved },
    },
    properties,
  };

  fs.writeFileSync(OUT, JSON.stringify(full, null, 1));
  console.log(`wrote ${OUT}`);
  console.log(`  listings=${listings.length}  assessor_matched=${matched}  with_deed_date=${deed}  pilot_preserved=${preserved}`);
})().catch((e) => { console.error('FAILED:', e.message); process.exit(1); });