`src/enrich/firm_website_discovery.ts`

226 lines
/**
 * Firm website discovery via free HTML search — Brave Search primary,
 * DuckDuckGo html endpoint fallback. No API keys, $0.
 *
 * For firms with no firm_site row yet (biggest by agent_count first), query
 * "<name> <city> <state> real estate", take the first organic result whose
 * domain isn't an aggregator/portal/MLS-board (preferring one whose domain
 * echoes a firm-name token), and store it in firm_site (discovery_method =
 * engine that answered). Misses are recorded too (url NULL,
 * crawl_status='no_url') so reruns advance down the list.
 *
 * Run:  npm run discover:firms                # default 200
 *       tsx src/enrich/firm_website_discovery.ts -- --limit=500
 *
 * Polite: 2.5-4s jittered gap per query, 10s timeout, real-browser UA.
 * Both engines bot-gate: Brave 429s, DDG serves an HTTP-202 "anomaly" page
 * (which must NOT be recorded as a legitimate no-result miss — that bug
 * poisoned the first run). Escalating backoff, abort after 3 consecutive
 * throttled firms.
 */
import 'dotenv/config';
import { pool, query } from '../../db/pool.ts';

const UA = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/126.0.0.0 Safari/537.36';
const TIMEOUT_MS = 10_000;

const BLOCK = new Set([
  // Listing aggregators / portals
  'zillow.com', 'realtor.com', 'redfin.com', 'homes.com', 'trulia.com',
  'apartments.com', 'homesnap.com', 'movoto.com', 'estately.com',
  'homelight.com', 'realtytrac.com', 'auction.com', 'point2homes.com',
  'coldwellbankerhomes.com', // brand-level portal, not the local firm's site
  'har.com', 'onekeymls.com', 'streeteasy.com', 'homefinder.com',
  'landwatch.com', 'land.com', 'loopnet.com', 'crexi.com',
  // Realtor-board / MLS office directories
  'pbbor.com', 'cpar.us', 'miamire.com', 'onehome.com', 'realtyna.com',
  // Social
  'facebook.com', 'instagram.com', 'linkedin.com', 'twitter.com', 'x.com',
  'youtube.com', 'tiktok.com', 'pinterest.com', 'reddit.com',
  // Reviews / directories / data brokers
  'yelp.com', 'bbb.org', 'yellowpages.com', 'whitepages.com', 'manta.com',
  'bizapedia.com', 'opencorporates.com', 'dnb.com', 'zoominfo.com',
  'crunchbase.com', 'glassdoor.com', 'indeed.com', 'mapquest.com',
  'wikipedia.org', 'wikidata.org', 'google.com', 'duckduckgo.com', 'brave.com',
  'ratemyagent.com', 'fastexpert.com', 'realtyrates.com',
]);

function host(u: string): string | null {
  try { return new URL(u).hostname.replace(/^www\./, '').toLowerCase(); } catch { return null; }
}

function blocked(h: string): boolean {
  for (const root of BLOCK) if (h === root || h.endsWith('.' + root)) return true;
  if (h.endsWith('.gov')) return true;
  if (h.includes('mls')) return true; // nystatemls.com, floridakeysmls.com, stellarmls.com, …
  return false;
}

const STOP = new Set(['the', 'and', 'for', 'inc', 'llc', 'llp', 'ltd', 'corp', 'co', 'of', 'real', 'estate', 'realty', 'group', 'company', 'properties', 'homes', 'brokerage', 'team']);

function nameTokens(name: string): string[] {
  return name.toLowerCase().replace(/[^a-z0-9 ]/g, ' ').split(/\s+/)
    .filter(t => t.length >= 4 && !STOP.has(t));
}

function domainMatchesName(h: string, name: string): boolean {
  const stem = h.split('.').slice(0, -1).join('').replace(/[^a-z0-9]/g, '');
  return nameTokens(name).some(t => stem.includes(t));
}

class ThrottleError extends Error {}

async function fetchHtml(url: string): Promise<string> {
  const res = await fetch(url, {
    headers: { 'User-Agent': UA, 'Accept': 'text/html,application/xhtml+xml', 'Accept-Language': 'en-US,en;q=0.9' },
    redirect: 'follow',
    signal: AbortSignal.timeout(TIMEOUT_MS),
  });
  if (res.status === 429 || res.status === 403 || res.status === 202) throw new ThrottleError(`http ${res.status}`);
  if (!res.ok) throw new Error(`http ${res.status}`);
  return res.text();
}

// Brave SERP: each organic hit is a block flagged data-type="web"; the first
// href inside the block is the target URL.
async function braveSearch(q: string): Promise<string[]> {
  const html = await fetchHtml('https://search.brave.com/search?q=' + encodeURIComponent(q));
  if (/verifying you are human|challenge-platform/i.test(html)) throw new ThrottleError('brave challenge');
  const out: string[] = [];
  for (const blk of html.split('data-type="web"').slice(1)) {
    const m = blk.match(/href="(https?:\/\/[^"]+)"/);
    if (m) out.push(m[1].replace(/&amp;/g, '&'));
  }
  return out.slice(0, 10);
}

// DDG html endpoint: <a class="result__a" href="/l/?uddg=<encoded>">.
async function ddgSearch(q: string): Promise<string[]> {
  const html = await fetchHtml('https://html.duckduckgo.com/html/?q=' + encodeURIComponent(q));
  if (/anomaly-modal|anomaly\.js|challenge-form/i.test(html)) throw new ThrottleError('ddg anomaly page');
  const out: string[] = [];
  const tagRe = /<a[^>]+class="[^"]*result__a[^"]*"[^>]*>/g;
  let m: RegExpExecArray | null;
  while ((m = tagRe.exec(html)) !== null) {
    const hrefM = m[0].match(/href="([^"]+)"/);
    if (!hrefM) continue;
    let abs = hrefM[1].replace(/&amp;/g, '&');
    try {
      const u = new URL(abs, 'https://duckduckgo.com');
      const uddg = u.searchParams.get('uddg');
      if (uddg) abs = decodeURIComponent(uddg);
    } catch {}
    if (/^https?:\/\//i.test(abs)) out.push(abs);
  }
  return out.slice(0, 10);
}

async function searchWeb(q: string): Promise<{ urls: string[]; engine: string }> {
  try {
    return { urls: await braveSearch(q), engine: 'brave' };
  } catch (e) {
    if (!(e instanceof ThrottleError)) throw e;
  }
  return { urls: await ddgSearch(q), engine: 'ddg' };
}

function pickWinner(urls: string[], firmName: string): string | null {
  const credible = urls.filter(u => { const h = host(u); return h && !blocked(h); });
  if (!credible.length) return null;
  // Prefer a result whose domain echoes the firm name; else first credible.
  const named = credible.find(u => domainMatchesName(host(u)!, firmName));
  const winner = named || credible[0];
  return 'https://' + host(winner);
}

interface FirmRow { id: number; name: string; hq_city: string | null; license_state: string }

async function main() {
  const argLimit = process.argv.find(a => a.startsWith('--limit='));
  const limit = argLimit ? parseInt(argLimit.split('=')[1], 10) : 200;

  const run = await query<{ id: number }>(
    `INSERT INTO ingest_runs (source, notes) VALUES ('firm_discovery', $1) RETURNING id`,
    [`brave+ddg website discovery, batch limit ${limit}`],
  );
  const runId = run.rows[0].id;

  const r = await query<FirmRow>(`
    SELECT f.id, f.name, f.hq_city, f.license_state
      FROM firm f
     WHERE f.agent_count IS NOT NULL
       AND NOT EXISTS (SELECT 1 FROM firm_site s WHERE s.firm_id = f.id)
     ORDER BY f.agent_count DESC, f.id
     LIMIT $1
  `, [limit]);
  const queue = r.rows;
  console.log(`[discover] run #${runId} · ${queue.length} firms · jittered 2.5-4s/query`);

  let ok = 0, miss = 0, err = 0, consecThrottle = 0;
  for (let i = 0; i < queue.length; i++) {
    const f = queue[i];
    const q = `${f.name} ${f.hq_city || ''} ${f.license_state} real estate`.replace(/\s+/g, ' ').trim();
    let result: { urls: string[]; engine: string } | null = null;
    // Up to 3 attempts with escalating backoff on throttle; a firm is only
    // recorded as a miss when an engine actually returned a results page.
    for (let attempt = 0; attempt < 3 && result === null; attempt++) {
      try {
        result = await searchWeb(q);
        consecThrottle = 0;
      } catch (e: any) {
        if (e instanceof ThrottleError) {
          const wait = [30_000, 90_000, 180_000][attempt];
          console.log(`  [throttle] ${e.message} — backing off ${wait / 1000}s (attempt ${attempt + 1}/3)`);
          await new Promise(res => setTimeout(res, wait));
        } else {
          console.log(`  [err] ${f.name.slice(0, 40)}: ${(e.message || e).slice(0, 80)}`);
          break; // transport error — skip this firm, don't record
        }
      }
    }
    if (result === null) {
      err++;
      consecThrottle++;
      console.log(`  [${i + 1}/${queue.length}] ⚠ ${f.name.slice(0, 44)} (search failed, not recorded)`);
      if (consecThrottle >= 3) {
        console.log('[discover] 3 consecutive throttled firms — aborting run');
        break;
      }
    } else {
      const winner = pickWinner(result.urls, f.name);
      if (winner) {
        ok++;
        await query(
          `INSERT INTO firm_site (firm_id, url, discovery_method) VALUES ($1, $2, $3)
           ON CONFLICT (firm_id) DO NOTHING`,
          [f.id, winner, result.engine],
        );
        console.log(`  [${i + 1}/${queue.length}] ✓ ${f.name.padEnd(44).slice(0, 44)} → ${winner}`);
      } else {
        miss++;
        await query(
          `INSERT INTO firm_site (firm_id, url, discovery_method, crawl_status) VALUES ($1, NULL, $2, 'no_url')
           ON CONFLICT (firm_id) DO NOTHING`,
          [f.id, result.engine],
        );
        console.log(`  [${i + 1}/${queue.length}] · ${f.name.slice(0, 44)} (no credible result)`);
      }
    }
    await new Promise(res => setTimeout(res, 2500 + Math.random() * 1500));
  }

  await query(
    `UPDATE ingest_runs SET finished_at = NOW(), status = 'ok', rows_upserted = $2, rows_skipped = $3,
            notes = notes || $4 WHERE id = $1`,
    [runId, ok, miss, ` · ${ok} found, ${miss} no-result, ${err} search-errors`],
  );
  console.log(`[discover] done · ${ok} found · ${miss} no-result · ${err} errors`);
  await pool.end();
}

main().catch(async (e) => {
  console.error('[discover]', e);
  try { await pool.end(); } catch {}
  process.exit(1);
});