`scripts/backfill-firm-phones-from-cre.mjs`

83 lines
#!/usr/bin/env node
// backfill-firm-phones-from-cre.mjs — TK-10687 Tier-1 (accurate, instant, $0, no web).
//
// The commercialrealestate `cre` DB already holds vetted broker contact data (from Crexi/Redfin).
// The strongest FIRM-level signal there: a phone SHARED by 2+ brokers at the same firm = the office
// switchboard (not a personal cell). We aggregate that office line per firm and promote it into the
// usre.firm registry (COALESCE-only, provenance 'cre-backfill'), so commercial firms — exactly the
// ones RENTV / CRCP care about — get an accurate main line without any web scraping.
//
// Usage: node scripts/backfill-firm-phones-from-cre.mjs            (dry-run)
//        node scripts/backfill-firm-phones-from-cre.mjs --apply    (write to usre.firm)
'use strict';
import pg from 'pg';

const USRE = process.env.DATABASE_URL || 'postgresql:///usre?host=/tmp';
const CRE = process.env.CRE_DATABASE_URL || 'postgresql:///cre?host=/tmp';
const APPLY = process.argv.includes('--apply');
const _mi = process.argv.indexOf('--min-share');
const MIN_SHARE = _mi > -1 ? Number(process.argv[_mi + 1]) : 2;   // ≥N brokers on the number = office line
// Mega-brokerages: their corporate line must come from their OWN site (web tier), never from one
// agent's cre record — else thousands of their brokers would show a single agent's number.
const MEGA = /(exp realty|compass|coldwell|keller williams|re\/?max|berkshire|century 21|sotheby|douglas elliman|redfin|opendoor|weichert|realty one|ehome)/i;

const canon = p => { const d = String(p || '').replace(/\D/g, '').replace(/^1(?=\d{10}$)/, ''); return d.length === 10 ? `(${d.slice(0,3)}) ${d.slice(3,6)}-${d.slice(6)}` : null; };
const norm = s => String(s || '').toLowerCase().replace(/\b(inc|llc|corp|corporation|co|company|the|and|&|ltd|lp|group)\b/g, ' ').replace(/[^a-z0-9]+/g, ' ').trim();
const TOLLFREE = /^\(?(?:800|833|844|855|866|877|888)/;

async function main() {
  const cre = new pg.Pool({ connectionString: CRE });
  const usre = new pg.Pool({ connectionString: USRE });

  // Raw (firm, broker phone) pairs — one row per broker so we can count SHARED lines after
  // normalizing away format variants ("(310) 529-2052" vs "310-529-2052").
  const rows = (await cre.query(`
    SELECT f.name AS firm, b.phone
      FROM broker b JOIN firm f ON f.id=b.firm_id
     WHERE coalesce(nullif(b.phone,''),'')<>'' AND b.is_test=false`)).rows;

  // count distinct brokers per (normalized firm, canonical phone); the office line = the shared one.
  const counts = new Map();   // key `${normFirm}|${canonPhone}` → {firm, phone, n}
  for (const r of rows) {
    if (MEGA.test(r.firm)) continue;                    // mega-brokerage corporate line ≠ one agent's record
    const p = canon(r.phone); if (!p || TOLLFREE.test(p)) continue;
    const nf = norm(r.firm); if (!nf) continue;
    const k = nf + '|' + p; const c = counts.get(k);
    if (c) c.n++; else counts.set(k, { firm: r.firm, nf, phone: p, n: 1 });
  }
  // per firm, keep the most-shared line that clears MIN_SHARE
  const byFirm = new Map();
  for (const v of counts.values()) {
    if (v.n < MIN_SHARE) continue;
    const cur = byFirm.get(v.nf);
    if (!cur || v.n > cur.n) byFirm.set(v.nf, v);
  }
  console.log(`cre office-line candidates (≥${MIN_SHARE} brokers share the canonical number, non-toll-free, non-mega): ${byFirm.size} firms`);

  // match to usre.firm (CA, missing phone) by normalized name
  const targets = (await usre.query(`SELECT id, name FROM firm WHERE license_state='CA' AND coalesce(nullif(phone,''),'')=''`)).rows;
  const idx = new Map(); for (const t of targets) idx.set(norm(t.name), t);

  let matched = 0;
  const client = APPLY ? await usre.connect() : null;
  for (const [k, v] of byFirm) {
    const t = idx.get(k); if (!t) continue;
    matched++;
    console.log(`  ${t.name} → 📞 ${v.phone}  (${v.n} brokers share it)`);
    if (APPLY) {
      await client.query(
        `UPDATE firm SET phone=COALESCE(NULLIF(phone,''),$2),
           phone_source=CASE WHEN COALESCE(NULLIF(phone,''),'')='' THEN 'cre-backfill' ELSE phone_source END,
           contact_attempts=contact_attempts+1, contact_enriched_at=now(),
           phone_status=CASE WHEN COALESCE(NULLIF(phone,''),'')='' THEN 'found' ELSE phone_status END
         WHERE id=$1`, [t.id, v.phone]);
      await client.query(`INSERT INTO firm_contacts (firm_id,kind,value,source_url) VALUES ($1,'phone',$2,'cre-backfill')
                            ON CONFLICT (firm_id,kind,value) DO NOTHING`, [t.id, v.phone]);
    }
  }
  if (client) client.release();
  console.log(`\nDONE: ${matched} usre CA firms matched an accurate cre office line · ${APPLY ? 'WRITTEN' : 'dry-run'} · $0`);
  await cre.end(); await usre.end();
}
main().catch(e => { console.error('FATAL', e); process.exit(1); });