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

78 lines
#!/usr/bin/env node
/*
 * confirm-broker-ca-dre.js — positively confirm which unknown-geography brokers in
 * cre.broker are CALIFORNIA by cross-referencing the CA DRE public license lookup.
 *
 * WHY: cre.broker (Crexi + Redfin, national) has 1,882 brokers with NO office_addr, so
 * their state is unknown. CA DRE only licenses CA real-estate agents/brokers, so a DRE
 * name-match is positive proof the broker is California. This turns "unknown" -> CA where
 * confirmable; a non-match stays unknown (DRE cannot prove non-CA — could be a name
 * variation or an out-of-state broker), and is stamped so we never re-check it.
 *
 * Reuses the vetted parsers from fetch-dre-licenses.js (toDreName / parseSearch / pick).
 * Only the NAME-SEARCH step is needed (existence), so no detail GET — ~2x faster.
 *
 * Source : CA DRE public lookup  https://www2.dre.ca.gov/PublicASP/pplinfo.asp  ($0, no captcha)
 * Target : cre.broker  (adds columns: state already exists; dre_license, dre_match, dre_checked_at)
 * Resumable: processes only rows WHERE state IS NULL AND dre_checked_at IS NULL.
 *
 * Usage: node scripts/confirm-broker-ca-dre.js [--limit N] [--delay MS]
 */
'use strict';
const { toDreName, parseSearch, pick } = 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);

async function post(name) {
  const body = new URLSearchParams({ LICENSEE_NAME: name, 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 dre_license text;
    ALTER TABLE broker ADD COLUMN IF NOT EXISTS dre_match text;
    ALTER TABLE broker ADD COLUMN IF NOT EXISTS dre_checked_at timestamptz;`);

  const { rows } = await pool.query(
    `SELECT id, name FROM broker
      WHERE state IS NULL AND dre_checked_at IS NULL AND coalesce(name,'') <> ''
      ORDER BY id`);
  const todo = rows.slice(0, LIMIT === Infinity ? rows.length : LIMIT);
  console.log(`unknown-geography brokers to check: ${rows.length}${todo.length < rows.length ? ` (this run: ${todo.length})` : ''}`);

  let done = 0, ca = 0, verified = 0, probable = 0, still = 0, errors = 0;
  for (const b of todo) {
    const dreName = toDreName(b.name);
    let dre_match = 'unmatched', dre_license = null, state = null;
    try {
      const cands = parseSearch(await post(dreName));
      const { match, tier } = pick(cands, dreName);
      if (match) { dre_match = tier; dre_license = match.license; state = 'CA'; ca++; tier === 'verified' ? verified++ : probable++; }
      else { still++; }
    } catch (e) { dre_match = 'error'; errors++; }
    // state set only on a DRE hit; a miss leaves state NULL but stamps dre_checked_at (no re-check).
    await pool.query(
      `UPDATE broker SET state = COALESCE($2, state), dre_license = $3, dre_match = $4, dre_checked_at = now() WHERE id = $1`,
      [b.id, state, dre_license, dre_match]);
    done++;
    process.stdout.write(`\r  checked ${done}/${todo.length}  CA-confirmed ${ca} (v${verified}/p${probable})  still-unknown ${still}  err ${errors}   `);
    await sleep(jitter());
  }
  process.stdout.write('\n');
  console.log(`✔ done. CA-confirmed ${ca} | still-unknown ${still} | errors ${errors} | cost: $0 (local)`);
  await pool.end();
}

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