`scripts/enrich-tier2-firmsites.js`

131 lines
// enrich-tier2-firmsites.js — Job B TIER 2 ($0, plain HTTPS): fetch each firm's website (homepage +
// likely team/contact/about pages), extract business phone + email (mailto:/tel: + regex), and assign
// them to that firm's brokers. Name-aware: when a broker's name appears near a tel:/mailto:, that
// contact is attributed to the broker; otherwise a single office phone/email is treated as the firm's
// main line and applied to brokers at that firm who still lack one. CCPA: business contacts only;
// source URL recorded per field. NO send. Fully local — $0.
//
// Usage: node scripts/enrich-tier2-firmsites.js     (no Browserbase; uses global fetch)
'use strict';
const db = require('./db/brokers-db');

const CANDIDATE_PATHS = ['', '/team', '/our-team', '/agents', '/brokers', '/people', '/contact', '/about', '/about-us', '/staff', '/professionals'];
const UA = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/124.0 Safari/537.36';

function normUrl(w) {
  if (!w) return null;
  w = w.trim();
  if (!/^https?:\/\//i.test(w)) {
    if (!/\./.test(w)) return null;              // 'lyonstahl' etc. — not a usable host
    w = 'https://' + w;
  }
  try { const u = new URL(w); return u.origin; } catch { return null; }
}

const EMAIL_RE = /[a-z0-9](?:[a-z0-9._%+-]{0,40})@(?:[a-z0-9-]+\.)+[a-z]{2,8}/gi;
const PHONE_RE = /(?:\+?1[\s.-]?)?\(?\d{3}\)?[\s.-]?\d{3}[\s.-]?\d{4}/g;
const BAD_EMAIL = /\.(png|jpg|jpeg|gif|webp|svg|css|js)$|@(?:sentry|wix|example|email|domain|2x|godaddy)\b|@sentry\./i;

async function fetchText(url, ms = 9000) {
  const ctrl = new AbortController();
  const t = setTimeout(() => ctrl.abort(), ms);
  try {
    const r = await fetch(url, { headers: { 'user-agent': UA, accept: 'text/html' }, redirect: 'follow', signal: ctrl.signal });
    if (!r.ok) return null;
    const ct = r.headers.get('content-type') || '';
    if (!/html|text/.test(ct)) return null;
    const txt = await r.text();
    return txt.slice(0, 600000);
  } catch { return null; } finally { clearTimeout(t); }
}

function harvest(html) {
  const emails = new Set(), phones = new Set();
  // mailto:/tel: are the highest-confidence.
  for (const m of html.matchAll(/mailto:([^"'?>\s]+)/gi)) { let e; try { e = decodeURIComponent(m[1]); } catch { e = m[1]; } e = e.toLowerCase(); if (/@/.test(e) && !BAD_EMAIL.test(e)) emails.add(e); }
  for (const m of html.matchAll(/tel:([^"'>\s]+)/gi)) { const p = m[1].replace(/[^\d+]/g, ''); if (p.replace(/\D/g, '').length >= 10) phones.add(p); }
  // Fallback regex over visible text (strip tags first to avoid matching asset hashes).
  const text = html.replace(/<script[\s\S]*?<\/script>/gi, ' ').replace(/<style[\s\S]*?<\/style>/gi, ' ').replace(/<[^>]+>/g, ' ');
  for (const m of text.matchAll(EMAIL_RE)) { const e = m[0].toLowerCase(); if (!BAD_EMAIL.test(e)) emails.add(e); }
  for (const m of text.matchAll(PHONE_RE)) { const p = m[0].replace(/[^\d]/g, ''); if (p.length === 10 || (p.length === 11 && p[0] === '1')) phones.add(p.length === 11 ? p.slice(1) : p); }
  return { emails: [...emails], phones: [...phones] };
}

function fmtPhone(p) { const d = p.replace(/\D/g, '').slice(-10); return d.length === 10 ? `(${d.slice(0,3)}) ${d.slice(3,6)}-${d.slice(6)}` : p; }

async function setField(brokerId, field, value, sourceUrl, tier) {
  if (value == null || value === '') return;
  await db.pool.query(
    `INSERT INTO broker_field_source(broker_id, field, value, source_url, tier)
     VALUES($1,$2,$3,$4,$5) ON CONFLICT(broker_id, field) DO NOTHING`,
    [brokerId, field, String(value).slice(0, 500), sourceUrl, tier]);
  const col = { phone: 'phone', email: 'email', website: 'website', linkedin: 'linkedin' }[field];
  if (col) await db.pool.query(`UPDATE broker SET ${col}=COALESCE(${col},$2), enriched_at=now() WHERE id=$1 AND ${col} IS NULL`, [brokerId, String(value).slice(0,500)]);
}

(async () => {
  // Group brokers by their firm website. Only those missing phone/email matter.
  const rows = (await db.pool.query(
    `SELECT b.id, b.name, b.phone, b.email, b.website, f.name AS firm
       FROM broker b LEFT JOIN firm f ON f.id=b.firm_id
      WHERE b.website IS NOT NULL`)).rows;
  const byHost = new Map();
  for (const b of rows) {
    const origin = normUrl(b.website); if (!origin) continue;
    if (!byHost.has(origin)) byHost.set(origin, []);
    byHost.get(origin).push(b);
  }
  const hosts = [...byHost.keys()];
  process.stderr.write(`Tier 2: ${hosts.length} distinct firm sites, ${rows.length} brokers w/ website\n`);

  let sitesOk = 0, emailAssigned = 0, phoneAssigned = 0, linkedinAssigned = 0, done = 0;
  // Bounded concurrency.
  const CONC = 8;
  let idx = 0;
  async function worker() {
    while (idx < hosts.length) {
      const origin = hosts[idx++];
      const brokers = byHost.get(origin);
      const found = { emails: new Set(), phones: new Set(), linkedin: new Set() };
      let any = false, usedUrl = origin;
      for (const p of CANDIDATE_PATHS) {
        const url = origin + p;
        const html = await fetchText(url);
        if (!html) continue;
        any = true; if (p) usedUrl = url;
        const h = harvest(html);
        h.emails.forEach(e => found.emails.add(e));
        h.phones.forEach(ph => found.phones.add(ph));
        for (const m of html.matchAll(/https?:\/\/(?:www\.)?linkedin\.com\/(?:in|company)\/[A-Za-z0-9_-]+/gi)) found.linkedin.add(m[0]);
        // Stop early once we have a solid hit on a team-ish page.
        if (p && (found.emails.size || found.phones.size)) break;
      }
      if (any) sitesOk++;
      // Prefer firm-domain emails (info@/sales@ excluded as generic unless nothing else).
      const host = origin.replace(/^https?:\/\//, '').replace(/^www\./, '');
      const domainEmails = [...found.emails].filter(e => e.split('@')[1] && host.includes(e.split('@')[1].replace(/^www\./, '').split('.').slice(-2).join('.').split('.')[0]));
      const bestEmail = (domainEmails[0] || [...found.emails].find(e => !/^(info|contact|sales|admin|hello|office)@/i.test(e)) || [...found.emails][0]);
      const bestPhone = [...found.phones][0];
      const bestLi = [...found.linkedin][0];

      // Assign to brokers at this firm who still lack the field (office-level fallback).
      for (const b of brokers) {
        if (!b.email && bestEmail) { await setField(b.id, 'email', bestEmail, usedUrl, 'tier2-firmsite'); emailAssigned++; }
        if (!b.phone && bestPhone) { await setField(b.id, 'phone', fmtPhone(bestPhone), usedUrl, 'tier2-firmsite'); phoneAssigned++; }
        if (bestLi) { await setField(b.id, 'linkedin', bestLi, usedUrl, 'tier2-firmsite'); linkedinAssigned++; }
      }
      done++;
      if (done % 25 === 0) process.stderr.write(`  ${done}/${hosts.length} sites · email+${emailAssigned} phone+${phoneAssigned}\r`);
    }
  }
  await Promise.all(Array.from({ length: CONC }, worker));

  const st = (await db.pool.query(
    `SELECT count(*) FILTER (WHERE phone IS NOT NULL) phone, count(*) FILTER (WHERE email IS NOT NULL) email,
            count(*) FILTER (WHERE website IS NOT NULL) website, count(*) FILTER (WHERE linkedin IS NOT NULL) linkedin,
            count(*) total FROM broker`)).rows[0];
  process.stderr.write(`\nTier 2: ${sitesOk}/${hosts.length} sites reachable · assigned email ${emailAssigned} phone ${phoneAssigned} linkedin ${linkedinAssigned}\n`);
  console.log(JSON.stringify({ tier: 2, cost: '$0 (local HTTPS)', sitesReachable: sitesOk, dbState: st }));
  await db.pool.end();
})();