`scripts/enrich-contacts.js`

120 lines
// Contact deep-dive ($0 LOCAL). For every entity with a known website, MINE the site
// (home + /contact + /about) for the REAL LinkedIn profile URL, email, and direct phone.
// LinkedIn is captured from the entity's OWN site (authoritative + TOS-safe — we NEVER
// request linkedin.com, which blocks bots and forbids scraping). Writes linkedin / email /
// phone_web + contacts_enriched_at onto cslb_raw. Resumable (contacts_enriched_at stamp),
// shardable (--shard=i/N) for max-it fan-out. Phone is mostly already on CSLB.BusinessPhone;
// phone_web is a supplementary web-sourced direct line (never overwrites the canonical one).
//
// Usage: node scripts/enrich-contacts.js [--loop] [--batch=N] [--shard=i/N] [--all]
import { q, pool } from '../src/db.js';

const flags = new Set(process.argv.slice(2).filter(a => a.startsWith('--') && !a.includes('=')));
const kv = Object.fromEntries(process.argv.slice(2).filter(a => a.includes('=')).map(a => a.slice(2).split('=')));
const BATCH = Number(kv.batch || 40);
const LA_ONLY = !flags.has('--all');
const [SHARD_I, SHARD_N] = (kv.shard || '0/1').split('/').map(Number);
const SHARD = SHARD_N > 1 ? `AND (abs(hashtext(c."LicenseNo")) % ${SHARD_N}) = ${SHARD_I}` : '';
const sleep = ms => new Promise(r => setTimeout(r, ms));

// non-global tester (safe for .test); global extractor (safe for .match)
const HAS_LI = /linkedin\.com\/(?:company|in|pub|school)\//i;
const LI_RE = /https?:\/\/(?:[a-z]{2,3}\.)?linkedin\.com\/(?:company|in|pub|school)\/[A-Za-z0-9._~%\-]+/ig;
const EMAIL_RE = /[a-z0-9._%+\-]+@[a-z0-9.\-]+\.[a-z]{2,}/ig;
const PHONE_RE = /(?:\+?1[-.\s]?)?\(?\d{3}\)?[-.\s]?\d{3}[-.\s]?\d{4}/g;
const BAD_EMAIL = /(example|sentry|wixpress|godaddy|\.png|\.jpe?g|\.gif|@2x|domain\.com|email\.com|yourname|test@|no-?reply)/i;
const BAD_LI = /linkedin\.com\/(?:shareArticle|sharing|cws|feed|company\/setup|sales|learning|jobs)/i;

async function fetchText(url) {
  try {
    const res = await fetch(url, { headers: { 'User-Agent': 'Mozilla/5.0' }, signal: AbortSignal.timeout(8000), redirect: 'follow' });
    if (!res.ok) return '';
    if (!/html/.test(res.headers.get('content-type') || '')) return '';
    return (await res.text()).slice(0, 120000);
  } catch { return ''; }
}

function pickLinkedin(html) {
  const hits = [...new Set((html.match(LI_RE) || []).map(u => u.replace(/\/$/, '')))].filter(u => !BAD_LI.test(u));
  if (!hits.length) return null;
  return hits.find(u => /\/company\//i.test(u)) || hits[0]; // prefer the company page
}
function pickEmail(html, site) {
  const host = site.replace(/^https?:\/\//, '').replace(/^www\./, '').split('/')[0];
  const emails = [...new Set((html.match(EMAIL_RE) || []).map(e => e.toLowerCase()))].filter(e => !BAD_EMAIL.test(e));
  if (!emails.length) return null;
  return emails.find(e => e.endsWith('@' + host)) || emails[0]; // prefer their own domain
}
// famous web-template default numbers (baked into thousands of themes) — never a real lead
const BAD_PHONE = new Set(['2147483647', '1234567890', '8000000000', '1231231234', '9876543210']);
function validNANP(d) {                                 // real North-American number, not a random digit run
  if (!d || d.length !== 10) return false;
  if (/^(\d)\1{9}$/.test(d)) return false;              // 3333333333 placeholder
  if (BAD_PHONE.has(d) || /^\d{3}555\d{4}$/.test(d)) return false; // template default / 555 fictional
  return /^[2-9]\d\d[2-9]\d{6}$/.test(d);               // area + exchange must start 2-9
}
function pickPhone(html) {
  const tel = html.match(/tel:\+?([0-9\-.\s()]{7,})/i); // tel: link is the strongest signal
  if (tel) { const d = tel[1].replace(/[^0-9]/g, '').slice(-10); if (validNANP(d)) return d; }
  for (const raw of (html.match(PHONE_RE) || [])) {
    const d = raw.replace(/[^0-9]/g, '');
    const t = (d.length === 11 && d[0] === '1') ? d.slice(1) : d;
    if (validNANP(t)) return t;
  }
  return null;
}
const fmtPhone = d => d && d.length === 10 ? `(${d.slice(0,3)}) ${d.slice(3,6)}-${d.slice(6)}` : (d || '');

async function enrichOne(c) {
  const out = { linkedin: null, email: null, phone: null };
  const site = c.website;
  let html = await fetchText(site);
  if (html && !HAS_LI.test(html)) {              // no LI on home → check a contact/about page
    const base = site.replace(/\/$/, '');
    for (const p of ['/contact', '/contact-us', '/about']) {
      const extra = await fetchText(base + p);
      if (extra) html += ' ' + extra;
      if (HAS_LI.test(html)) break;
    }
  }
  if (html) { out.linkedin = pickLinkedin(html); out.email = pickEmail(html, site); out.phone = pickPhone(html); }
  await q(`UPDATE cslb_raw SET linkedin=$2::text, email=COALESCE($3::text,email), phone_web=$4::text,
             linkedin_source=CASE WHEN $2::text IS NOT NULL THEN 'site' ELSE linkedin_source END,
             contacts_enriched_at=now() WHERE "LicenseNo"=$1`,
    [c.LicenseNo, out.linkedin, out.email, out.phone]);
  return out;
}

async function pick() {
  const la = LA_ONLY ? `AND c."County"='Los Angeles'` : '';
  return (await q(`SELECT c."LicenseNo", c."BusinessName", c.website
    FROM cslb_raw c
    WHERE c."PrimaryStatus"='CLEAR' AND c.website IS NOT NULL AND c.contacts_enriched_at IS NULL ${la} ${SHARD}
    ORDER BY c."LicenseNo" LIMIT ${BATCH}`)).rows;
}

async function main() {
  let done = 0, li = 0, em = 0, ph = 0;
  for (;;) {
    const batch = await pick();
    if (!batch.length) { console.log(`\n✔ site-mining pass complete (${LA_ONLY ? 'LA' : 'all'} entities with a website)`); break; }
    for (const c of batch) {
      let f;
      try { f = await enrichOne(c); }
      catch (e) {                                     // one bad row must never kill the worker
        try { await q(`UPDATE cslb_raw SET contacts_enriched_at=now() WHERE "LicenseNo"=$1`, [c.LicenseNo]); } catch {}
        continue;
      }
      done++; if (f.linkedin) li++; if (f.email) em++; if (f.phone) ph++;
      if (f.linkedin || f.email || f.phone)
        console.log(`  [${done}] ${(c.BusinessName || '').slice(0, 30).padEnd(30)} ${f.linkedin ? 'in✓' : '   '} ${f.email ? '@✓' : '  '} ${fmtPhone(f.phone)}`);
      await sleep(120);
    }
    const rem = Number((await q(`SELECT count(*) c FROM cslb_raw WHERE "PrimaryStatus"='CLEAR' AND website IS NOT NULL AND contacts_enriched_at IS NULL ${LA_ONLY ? `AND "County"='Los Angeles'` : ''}`)).rows[0].c);
    console.log(`— ${done} processed · ${li} linkedin · ${em} email · ${ph} phone · ~${rem} with-site left — $0`);
    if (!flags.has('--loop')) break;
  }
  console.log(`\nTotal: ${done} processed, ${li} linkedin, ${em} email, ${ph} phone. $0 (site-mining, local).`);
}
main().catch(e => { console.error('enrich-contacts error:', e.message); process.exitCode = 1; }).finally(() => pool.end());