`scripts/confirm-broker-ca-dre-pass3.js`

109 lines
#!/usr/bin/env node
/*
 * confirm-broker-ca-dre-pass3.js — THIRD PASS: disambiguate the residue by LISTING CITY.
 *
 * Pass 2 left ~219 brokers "ambiguous": surname+nickname matched MORE THAN ONE CA DRE person,
 * so identity (and thus a provable CA license) couldn't be pinned by name alone. But many of
 * these brokers HAVE listings in specific LA-area cities, and the DRE name-search result rows
 * carry each licensee's CITY. So we disambiguate: confirm only when a DRE candidate's city
 * matches one of the broker's own listing cities — a real geographic cross-signal.
 *
 * Target : broker WHERE state IS NULL AND pass3_at IS NULL AND has >=1 listing/condo city.
 * On a single first-name + city match -> state='CA', dre_match='ca-by-city', dre_license.
 * Always stamps pass3_at. $0 local, resumable.
 *
 * Usage: node scripts/confirm-broker-ca-dre-pass3.js [--limit N] [--delay MS]
 */
'use strict';
const { parseSearch } = require('./fetch-dre-licenses');
const { pool } = require('./db/brokers-db');

const BASE = 'https://www2.dre.ca.gov/PublicASP/pplinfo.asp';
const UA = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/126 Safari/537.36';
const arg = (k, d) => { const i = process.argv.indexOf(k); return i > -1 ? (process.argv[i + 1] ?? true) : d; };
const LIMIT = +arg('--limit', 0) || Infinity;
const DELAY = +arg('--delay', 1200);
const sleep = ms => new Promise(r => setTimeout(r, ms));
const jitter = () => DELAY + Math.floor(Math.random() * 500);

const NICK = { bob: 'robert', rob: 'robert', bill: 'william', will: 'william', jim: 'james', mike: 'michael',
  tom: 'thomas', rick: 'richard', dave: 'david', dan: 'daniel', danny: 'daniel', ken: 'kenneth', chris: 'christopher',
  chuck: 'charles', charlie: 'charles', ed: 'edward', joe: 'joseph', tony: 'anthony', nick: 'nicholas', matt: 'matthew',
  greg: 'gregory', jeff: 'jeffrey', andy: 'andrew', ben: 'benjamin', sam: 'samuel', steve: 'steven', pat: 'patrick',
  ron: 'ronald', don: 'donald', doug: 'douglas', fred: 'frederick', jack: 'john', larry: 'lawrence', pete: 'peter',
  phil: 'phillip', ray: 'raymond', russ: 'russell', walt: 'walter', jenny: 'jennifer', jen: 'jennifer', liz: 'elizabeth',
  sue: 'susan', kathy: 'katherine', kate: 'katherine', patty: 'patricia', debbie: 'deborah', deb: 'deborah',
  cindy: 'cynthia', sandy: 'sandra', vicky: 'victoria', tina: 'christina', angie: 'angela', terri: 'teresa', terry: 'teresa' };
const norm = s => (s || '').toLowerCase().replace(/[^a-z\- ]/g, '').replace(/\s+/g, ' ').trim();
function split(raw) {
  const n = norm(raw).replace(/\b(jr|sr|ii|iii|iv)\b/g, '').trim();
  const p = n.split(' ').filter(Boolean);
  if (p.length < 2) return null;
  const first = p[0], last = p[p.length - 1];
  return first.length >= 2 && last.length >= 2 ? { first, last } : null;
}
function firstMatches(q, c) {
  if (!q || !c) return false;
  if (q === c) return true;
  const a = NICK[q], b = NICK[c];
  if (a && (a === c || c.startsWith(a))) return true;
  if (b && (b === q || q.startsWith(b))) return true;
  if (q.length >= 3 && c.startsWith(q)) return true;
  if (c.length >= 3 && q.startsWith(c)) return true;
  return false;
}
async function searchSurname(last) {
  const body = new URLSearchParams({ LICENSEE_NAME: `${last},`, LICENSE_ID: '', CITY_STATE: '', B2: 'Find', h_nextstep: '' });
  const r = await fetch(`${BASE}?start=1`, { method: 'POST', headers: { 'User-Agent': UA, 'Content-Type': 'application/x-www-form-urlencoded' }, body });
  return r.ok ? r.text() : '';
}

async function main() {
  await pool.query(`ALTER TABLE broker ADD COLUMN IF NOT EXISTS pass3_at timestamptz`);
  // residue brokers with at least one listing OR condo city
  const { rows } = await pool.query(`
    SELECT b.id, b.name,
           array_remove(array_agg(DISTINCT lower(trim(c.city))), NULL) AS cities
      FROM broker b
      LEFT JOIN broker_listing bl ON bl.broker_id=b.id LEFT JOIN listing  l ON l.id=bl.listing_id
      LEFT JOIN broker_condo   bc ON bc.broker_id=b.id LEFT JOIN condo    cc ON cc.id=bc.condo_id
      , LATERAL (SELECT l.city UNION ALL SELECT cc.city) c(city)
     WHERE b.state IS NULL AND b.pass3_at IS NULL AND coalesce(b.name,'')<>''
     GROUP BY b.id, b.name
    HAVING array_length(array_remove(array_agg(DISTINCT lower(trim(c.city))), NULL),1) >= 1`);
  const todo = rows.slice(0, LIMIT === Infinity ? rows.length : LIMIT);
  console.log(`residue brokers WITH a listing city: ${rows.length}${todo.length < rows.length ? ` (this run: ${todo.length})` : ''}`);

  let done = 0, recovered = 0, noCityMatch = 0, errors = 0;
  for (const b of todo) {
    const nm = split(b.name);
    const cities = new Set((b.cities || []).map(norm).filter(Boolean));
    let state = null, dre_license = null, dre_match = null;
    if (nm && cities.size) {
      try {
        const cands = parseSearch(await searchSurname(nm.last));
        const hits = cands.filter(c => {
          const parts = String(c.name).split(',');
          const cLast = norm(parts[0]);
          const cFirst = norm(parts[1] || '').split(' ')[0] || '';
          return cLast === nm.last && firstMatches(nm.first, cFirst) && cities.has(norm(c.city));
        });
        const people = new Map(hits.map(h => [norm(h.name), h]));
        if (people.size === 1) { const h = [...people.values()][0]; state = 'CA'; dre_license = h.license; dre_match = 'ca-by-city'; recovered++; }
        else noCityMatch++;
      } catch (e) { errors++; }
    }
    await pool.query(
      `UPDATE broker SET state=COALESCE($2,state), dre_license=COALESCE($3,dre_license), dre_match=COALESCE($4,dre_match), pass3_at=now() WHERE id=$1`,
      [b.id, state, dre_license, dre_match]);
    done++;
    process.stdout.write(`\r  pass3 ${done}/${todo.length}  recovered ${recovered}  no-city-match ${noCityMatch}  err ${errors}   `);
    await sleep(jitter());
  }
  process.stdout.write('\n');
  console.log(`✔ pass3 done. recovered(CA by city) ${recovered} | no-city-match ${noCityMatch} | err ${errors} | cost: $0 (local)`);
  await pool.end();
}

if (require.main === module) main().catch(e => { console.error(e); process.exit(1); });