`scripts/enrich-linkedin-search.js`

110 lines
// Multi-source, MULTI-ENGINE LinkedIn-by-search ($0 LOCAL) for RE entities with no website.
// One polite stream drains each source pool in order (CSLB site-less → rentv_licensed_targets
// → …). For each entity we ROTATE across free search engines (DDG → Bing → Mojeek) so no
// single engine's rate limit can stall the whole stream — same host-diversity trick that beat
// the paid API. Query uses `site:linkedin.com`, so we regex linkedin.com/(company|in|pub) URLs
// straight out of whichever engine responds, and accept one only if a name token (>=4) is in
// the slug. We NEVER request linkedin.com itself. An engine that 403s/captchas is put on a
// 5-min cooldown; if ALL engines are cooling we wait, and the row is NOT stamped (retry later)
// so a rate-limit is never mistaken for "no profile".
//
// Usage: node scripts/enrich-linkedin-search.js [--loop] [--batch=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 || 30);
const LA_ONLY = !flags.has('--all');
const DELAY = Number(process.env.LI_DELAY_MS || 4000);
const sleep = ms => new Promise(r => setTimeout(r, ms));
const enc = encodeURIComponent;
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';
const LI_RE = /https?:\/\/(?:[a-z]{2,3}\.)?linkedin\.com\/(?:company|in|pub|school)\/[A-Za-z0-9._~%\-]+/ig;
const BAD_LI = /linkedin\.com\/(?:shareArticle|sharing|cws|feed|company\/setup|sales|learning|jobs|pulse|posts|directory)/i;
const CAPTCHA = /(unusual traffic|are you a (?:human|robot)|captcha|verify you are)/i;

const SOURCES = [
  { key: 'cslb', table: 'cslb_raw', id: '"LicenseNo"', name: '"BusinessName"', city: '"City"',
    where: `"PrimaryStatus"='CLEAR' AND website IS NULL${LA_ONLY ? ` AND "County"='Los Angeles'` : ''}` },
  { key: 'rentv', table: 'rentv_licensed_targets', id: 'id', name: 'entity_name', city: 'city',
    where: `(website IS NULL OR website='')` },
];

const ENGINES = [
  { name: 'ddg',    url: qy => `https://html.duckduckgo.com/html/?q=${enc(qy)}`, cool: 0 },
  { name: 'bing',   url: qy => `https://www.bing.com/search?q=${enc(qy)}`,       cool: 0 },
  { name: 'mojeek', url: qy => `https://www.mojeek.com/search?q=${enc(qy)}`,     cool: 0 },
];
let rr = 0;                                            // round-robin cursor

function slugMatches(name, url) {
  const slug = url.toLowerCase().replace(/[^a-z0-9]/g, '');
  const toks = String(name || '').toLowerCase()
    .replace(/\b(inc|llc|corp|co|ltd|the|construction|builders|building|group|company|dev|development|and|of|services|enterprises|realty|real|estate|properties|associates)\b/g, ' ')
    .replace(/[^a-z0-9 ]/g, ' ').split(/\s+/).filter(t => t.length >= 4);
  return toks.some(t => slug.includes(t));
}
function extractLinkedin(html, name) {
  const hits = [...new Set((html.match(LI_RE) || []).map(u => u.replace(/\/$/, '').replace(/\\/g, '')))]
    .filter(u => !BAD_LI.test(u));
  const ok = hits.filter(u => slugMatches(name, u));
  const pick = ok.find(u => /\/company\//i.test(u)) || ok[0];
  return pick ? pick.replace(/\?.*$/, '') : null;
}

// try engines in rotation; return {link} on a responding engine, or throw 'all-cooling'
async function findLinkedin(name, city) {
  const query = `${name} ${city || ''} CA site:linkedin.com`;
  const now = Date.now();
  for (let k = 0; k < ENGINES.length; k++) {
    const e = ENGINES[(rr + k) % ENGINES.length];
    if (e.cool > now) continue;                        // engine on cooldown — skip
    try {
      const res = await fetch(e.url(query), { headers: { 'User-Agent': UA, 'Accept-Language': 'en-US' }, signal: AbortSignal.timeout(9000) });
      if (res.status === 403 || res.status === 429) { e.cool = Date.now() + 300000; continue; }
      if (!res.ok) { e.cool = Date.now() + 60000; continue; }
      const html = await res.text();
      if (CAPTCHA.test(html.slice(0, 4000))) { e.cool = Date.now() + 300000; continue; }
      rr++;                                            // advance rotation only on a real response
      return { link: extractLinkedin(html, name), engine: e.name };
    } catch { e.cool = Date.now() + 60000; continue; }
  }
  throw new Error('all-cooling');
}

async function pick() {
  for (const s of SOURCES) {
    const rows = (await q(`SELECT ${s.id} AS id, ${s.name} AS name, ${s.city} AS city
      FROM ${s.table} WHERE ${s.where} AND contacts_enriched_at IS NULL
      ORDER BY ${s.id} LIMIT ${BATCH}`)).rows;
    if (rows.length) return { src: s, rows };
  }
  return { src: null, rows: [] };
}

async function main() {
  let done = 0, li = 0, curKey = '';
  for (;;) {
    const { src, rows } = await pick();
    if (!rows.length) { console.log('\n✔ multi-source LinkedIn search complete (all pools drained)'); break; }
    if (src.key !== curKey) { curKey = src.key; console.log(`\n▶ source: ${src.key} (${src.table})`); }
    for (const c of rows) {
      let r = null;
      try { r = await findLinkedin(c.name, c.city); }
      catch (e) {
        if (e.message === 'all-cooling') { console.log('  ⏸ all engines cooling — wait 90s (no stamp; retry)'); await sleep(90000); continue; }
      }
      const link = r ? r.link : null;
      await q(`UPDATE ${src.table} SET linkedin=COALESCE($2::text,linkedin),
                 linkedin_source=CASE WHEN $2::text IS NOT NULL THEN 'search' ELSE linkedin_source END,
                 contacts_enriched_at=now() WHERE ${src.id}=$1`, [c.id, link]);
      done++; if (link) { li++; console.log(`  [${done}] ${String(c.name).slice(0, 32).padEnd(32)} → ${link}  (${r.engine})`); }
      await sleep(DELAY);
    }
    console.log(`— ${curKey}: ${done} processed · ${li} linkedin — $0`);
    if (!flags.has('--loop')) break;
  }
  console.log(`\nTotal: ${done} processed, ${li} linkedin. $0 (multi-engine search, local).`);
}
main().catch(e => { console.error('li-search error:', e.message); process.exitCode = 1; }).finally(() => pool.end());