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

121 lines
#!/usr/bin/env node
/*
 * confirm-broker-ca-dre-pass2.js — SECOND PASS over the brokers pass-1 left unmatched.
 *
 * Pass 1 required an exact first-name match, so every NICKNAME missed (Dan≠Daniel,
 * Ken≠Kenneth, Nick≠Nicholas...). This pass searches CA DRE by SURNAME ONLY, then matches
 * the first name locally with nickname-expansion + prefix logic — but stays wrong-person-safe:
 * it only confirms when exactly ONE distinct person (by name) matches the relaxed rule.
 *
 * Target rows : broker WHERE state IS NULL AND dre_match='unmatched' AND pass2_at IS NULL,
 *               skipping obvious company/team names and too-short surnames.
 * On a confirmed hit : state='CA', dre_match='probable-fuzzy', dre_license=<id>.
 * Always stamps pass2_at so the row is never re-checked. $0 local, resumable.
 *
 * Usage: node scripts/confirm-broker-ca-dre-pass2.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);

// common US nickname -> formal first name (only the non-prefix ones; prefix cases handled separately)
const NICK = {
  bob: 'robert', rob: 'robert', bobby: 'robert', bill: 'william', will: 'william', billy: 'william',
  jim: 'james', jimmy: 'james', mike: 'michael', mickey: 'michael', tom: 'thomas', tommy: 'thomas',
  dick: 'richard', rick: 'richard', ricky: 'richard', dave: 'david', dan: 'daniel', danny: 'daniel',
  ken: 'kenneth', kenny: 'kenneth', chris: 'christopher', chuck: 'charles', charlie: 'charles',
  ed: 'edward', eddie: 'edward', ted: 'theodore', joe: 'joseph', joey: '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', gene: 'eugene', hank: 'henry', jack: 'john', larry: 'lawrence', marty: 'martin',
  pete: 'peter', phil: 'phillip', ray: 'raymond', russ: 'russell', stan: 'stanley', walt: 'walter',
  wes: 'wesley', jenny: 'jennifer', jen: 'jennifer', liz: 'elizabeth', beth: 'elizabeth', betty: 'elizabeth',
  sue: 'susan', kathy: 'katherine', cathy: 'catherine', kate: 'katherine', katie: 'katherine',
  patty: 'patricia', trish: 'patricia', debbie: 'deborah', deb: 'deborah', cindy: 'cynthia',
  sandy: 'sandra', mandy: 'amanda', val: 'valerie', vicky: 'victoria', tina: 'christina',
  angie: 'angela', terri: 'teresa', terry: 'teresa', becky: 'rebecca', peggy: 'margaret', meg: 'margaret',
};
const norm = s => (s || '').toLowerCase().replace(/[^a-z\- ]/g, '').replace(/\s+/g, ' ').trim();

// person name -> {first, last}; strips suffixes. Returns null for non-person shapes.
function split(raw) {
  let 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 last = p[p.length - 1];
  const first = p[0];
  if (last.length < 2 || first.length < 2) return null;
  return { first, last };
}
// does DRE candidate first name plausibly equal the query first name?
function firstMatches(qFirst, cFirst) {
  if (!qFirst || !cFirst) return false;
  if (qFirst === cFirst) return true;
  const a = NICK[qFirst], b = NICK[cFirst];
  if (a && (a === cFirst || cFirst.startsWith(a))) return true;   // dan -> daniel
  if (b && (b === qFirst || qFirst.startsWith(b))) return true;   // reverse
  if (qFirst.length >= 3 && cFirst.startsWith(qFirst)) return true; // deb -> deborah
  if (cFirst.length >= 3 && qFirst.startsWith(cFirst)) 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 pass2_at timestamptz`);
  const { rows } = await pool.query(`
    SELECT id, name FROM broker
     WHERE state IS NULL AND dre_match = 'unmatched' AND pass2_at IS NULL
       AND coalesce(name,'') <> ''
       AND name !~* '(team|group|realty|properties|associates|& |inc|llc|corp| co\\.| and )'
     ORDER BY id`);
  const todo = rows.slice(0, LIMIT === Infinity ? rows.length : LIMIT);
  console.log(`pass-1 unmatched, person-shaped: ${rows.length}${todo.length < rows.length ? ` (this run: ${todo.length})` : ''}`);

  let done = 0, recovered = 0, ambiguous = 0, none = 0, skipped = 0, errors = 0;
  for (const b of todo) {
    const nm = split(b.name);
    if (!nm) { await pool.query(`UPDATE broker SET pass2_at=now() WHERE id=$1`, [b.id]); skipped++; done++; continue; }
    let state = null, dre_license = null, dre_match = null;
    try {
      const cands = parseSearch(await searchSurname(nm.last));
      // candidates whose surname is exact and first name plausibly matches.
      // c.name is "LAST, FIRST MIDDLE" — split on the RAW comma first, then norm each part
      // (norm strips commas, so norming before the split would collapse the whole name).
      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);
      });
      const people = new Map(hits.map(h => [norm(h.name).replace(/\s+/g, ' '), h]));
      if (people.size === 1) { const h = [...people.values()][0]; state = 'CA'; dre_license = h.license; dre_match = 'probable-fuzzy'; recovered++; }
      else if (people.size > 1) { ambiguous++; }
      else { none++; }
    } 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), pass2_at=now() WHERE id=$1`,
      [b.id, state, dre_license, dre_match]);
    done++;
    process.stdout.write(`\r  pass2 ${done}/${todo.length}  recovered ${recovered}  ambiguous ${ambiguous}  no-match ${none}  err ${errors}   `);
    await sleep(jitter());
  }
  process.stdout.write('\n');
  console.log(`✔ pass2 done. recovered(CA) ${recovered} | ambiguous ${ambiguous} | no-match ${none} | skipped ${skipped} | err ${errors} | cost: $0 (local)`);
  await pool.end();
}

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