`src/enrich/firm_website_resolve.ts`

215 lines
/**
 * Firm→website resolver via the Google Places API — the reliable, STRUCTURED
 * alternative to the anti-bot-throttled Brave/DDG `discover` scraper.
 *
 * License-board firms (state DRE/TREC/DOS/DBPR/…) arrive with a name + license but
 * NO website. Places `searchText("<name> <city> <ST>")` returns `websiteUri`
 * directly, so this fills firm.website + firm_site without scraping any SERP HTML.
 *
 * RESUMABLE + ALL: each run takes the next BATCH firms still missing a site,
 * highest agent_count first, so successive runs progressively cover ALL ~213K
 * license-board firms. Quota-capped against the SAME places_quota monthly cap as
 * places-seed (one Google budget) so steady-state spend stays $0 (free tier).
 *
 * DRY by default; PLACES_SEED_LIVE=1 (or --live) makes real calls. Fail-soft:
 * always closes its ingest_runs row (no zombie 'running').
 *
 *   npm run resolve:firms                     # dry-run: plan + est cost
 *   npm run resolve:firms -- --live           # real calls, quota-capped
 *   USRE_RESOLVE_BATCH=250 npm run resolve:firms -- --live
 */
import 'dotenv/config';
import { appendFileSync, mkdirSync } from 'node:fs';
import { join } from 'node:path';
import { homedir } from 'node:os';
import { pool, query } from '../../db/pool.ts';
import { openRun, closeRun } from '../ingest/run.ts';

const SOURCE = 'firm_resolve';
const API = 'https://places.googleapis.com/v1/places:searchText';
const KEY = process.env.GOOGLE_PLACES_API_KEY || '';
const LIVE = process.env.PLACES_SEED_LIVE === '1' || process.argv.includes('--live');
const CAP = Number(process.env.PLACES_MONTHLY_CAP || 1500);
const BATCH = Number(process.env.USRE_RESOLVE_BATCH || 100);
const RATE_PER_CALL = 0.032; // Text-Search Pro list price, ledger-only (free-tier = $0)
// TK-10669: phone added to the mask — one call now yields website + phone, closing
// the firm.phone gap (0/215K populated) for the DRE-style contact card at $0 extra.
const FIELD_MASK = 'places.id,places.displayName,places.websiteUri,places.nationalPhoneNumber,places.formattedAddress';
// Optional asset-class scope (--asset=commercial|residential or USRE_RESOLVE_ASSET).
// RENTV is CRE-only, so the first sweep targets the ~4.1K commercial firms.
const ASSET_RAW = (process.argv.find(a => a.startsWith('--asset='))?.split('=')[1] || process.env.USRE_RESOLVE_ASSET || '').toLowerCase();
const ASSET = ['commercial', 'residential'].includes(ASSET_RAW) ? ASSET_RAW : '';
const ASSET_COND = ASSET ? `AND f.asset_class = '${ASSET}'` : '';

// Portal/aggregator hosts that are never a firm's OWN site.
const BLOCK = new Set([
  'zillow.com', 'realtor.com', 'redfin.com', 'trulia.com', 'homes.com', 'loopnet.com',
  'yelp.com', 'facebook.com', 'instagram.com', 'linkedin.com', 'indeed.com', 'google.com',
  'apartments.com', 'mapquest.com', 'bbb.org', 'yellowpages.com', 'crexi.com',
]);

function ym(): string {
  const d = new Date();
  return `${d.getUTCFullYear()}-${String(d.getUTCMonth() + 1).padStart(2, '0')}`;
}
function hostOf(u: string): string | null {
  try { return new URL(u).host.replace(/^www\./, '').toLowerCase(); } catch { return null; }
}
function nameEchoesHost(name: string, host: string): boolean {
  const tokens = name.toLowerCase().replace(/[^a-z0-9]+/g, ' ').split(' ').filter(t => t.length >= 4);
  const stem = host.split('.').slice(-2, -1)[0] || host;
  return tokens.some(t => stem.includes(t) || t.includes(stem));
}

function logCost(calls: number, note: string): void {
  try {
    const dir = join(homedir(), '.claude');
    mkdirSync(dir, { recursive: true });
    const entry = {
      ts: new Date().toISOString(), skill: 'usre-firm-resolve', provider: 'google_places',
      units: calls, unit: 'searchText_call', rate: RATE_PER_CALL,
      cost: 0, list_cost_if_billed: +(calls * RATE_PER_CALL).toFixed(4),
      note: `${note} (free-tier, capped ${CAP}/mo)`,
    };
    appendFileSync(join(dir, 'cost-ledger.jsonl'), JSON.stringify(entry) + '\n');
  } catch { /* ledger best-effort */ }
}
async function callsUsed(): Promise<number> {
  const r = await query<{ calls_used: number }>(
    `SELECT calls_used FROM places_quota WHERE year_month = $1`, [ym()]);
  return r.rows[0]?.calls_used ?? 0;
}
async function bumpQuota(n: number): Promise<void> {
  await query(
    `INSERT INTO places_quota (year_month, calls_used) VALUES ($1,$2)
     ON CONFLICT (year_month) DO UPDATE
       SET calls_used = places_quota.calls_used + $2, updated_at = NOW()`,
    [ym(), n]);
}

async function searchText(q: string): Promise<Array<{ name: string; website?: string; phone?: string; address?: string }>> {
  const res = await fetch(API, {
    method: 'POST',
    headers: { 'Content-Type': 'application/json', 'X-Goog-Api-Key': KEY, 'X-Goog-FieldMask': FIELD_MASK },
    body: JSON.stringify({ textQuery: q, maxResultCount: 5 }),
  });
  if (!res.ok) throw new Error(`places searchText ${res.status}: ${(await res.text()).slice(0, 160)}`);
  const j: any = await res.json();
  return (j.places || []).map((p: any) => ({ name: p.displayName?.text || '', website: p.websiteUri, phone: p.nationalPhoneNumber, address: p.formattedAddress }));
}

/**
 * Best own-site pick: skip aggregators; prefer a host that echoes the firm name.
 * Returns `confident=true` only when the winning host echoes the name — Places
 * sometimes returns an agent's personal page or a franchise-brand portal for a
 * firm, so a non-echoing host is recorded as a LEAD (firm_site low_confidence)
 * WITHOUT overwriting firm.website. ~30% of hits are loose (audited 2026-07-30).
 */
function pickWebsite(hits: Array<{ name: string; website?: string; phone?: string; address?: string }>, firmName: string): { url: string; confident: boolean; phone?: string; address?: string } | null {
  const cands = hits
    .filter(h => !!h.website)
    .filter(h => { const host = hostOf(h.website!); return host && !BLOCK.has(host); });
  if (!cands.length) return null;
  const named = cands.find(h => { const host = hostOf(h.website!); return host && nameEchoesHost(firmName, host); });
  const winner = named || cands[0];
  const h = hostOf(winner.website!);
  if (!h) return null;
  // Phone + address only ride a name-echoing (confident) hit — never staple a loose
  // match's contact details onto the firm, same rule as firm.website.
  return { url: 'https://' + h, confident: !!named, phone: named ? winner.phone : undefined, address: named ? winner.address : undefined };
}

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

async function main() {
  const used = await callsUsed();
  const budget = Math.max(0, CAP - used);
  const take = Math.min(BATCH, budget || BATCH);

  const r = await query<FirmRow>(`
    SELECT f.id, f.name, f.hq_city, f.license_state
      FROM firm f
     WHERE f.source <> 'google_places'
       AND f.agent_count IS NOT NULL
       AND (f.website IS NULL OR f.website = '')
       AND NOT EXISTS (SELECT 1 FROM firm_site s WHERE s.firm_id = f.id)
       ${ASSET_COND}
     ORDER BY f.agent_count DESC NULLS LAST, f.id
     LIMIT $1`, [take]);
  const queue = r.rows;

  const remaining = await query<{ n: number }>(`
    SELECT COUNT(*)::int AS n FROM firm f
     WHERE f.source <> 'google_places' AND f.agent_count IS NOT NULL
       AND (f.website IS NULL OR f.website = '')
       AND NOT EXISTS (SELECT 1 FROM firm_site s WHERE s.firm_id = f.id)
       ${ASSET_COND}`);
  console.log(`[resolve] quota ${used}/${CAP} (${budget} left) · batch ${queue.length} · ${remaining.rows[0].n} firms still unresolved${ASSET ? ` [asset=${ASSET}]` : ''} · live=${LIVE}`);

  if (!LIVE) {
    for (const f of queue.slice(0, 5)) {
      console.log(`   • "${`${f.name} ${f.hq_city || ''} ${f.license_state || ''}`.replace(/\s+/g, ' ').trim()}"`);
    }
    if (queue.length > 5) console.log(`   … +${queue.length - 5} more`);
    console.log(`[resolve] DRY-RUN — set PLACES_SEED_LIVE=1 (or --live). ${queue.length} calls, est $0 (free-tier; $${(queue.length * RATE_PER_CALL).toFixed(2)} if billed).`);
    await pool.end();
    return;
  }
  if (!KEY) { console.error('[resolve] live but GOOGLE_PLACES_API_KEY unset'); await pool.end(); process.exit(2); }
  if (budget <= 0) { console.log(`[resolve] monthly cap ${CAP} reached — HARD STOP, $0 spent`); await pool.end(); return; }

  const runId = await openRun(SOURCE, 'places-firm-resolve');
  let calls = 0, resolved = 0, phones = 0, lowConf = 0, noSite = 0, fatal: any = null;
  try {
    for (const f of queue) {
      if (calls >= budget) { console.log('[resolve] cap reached mid-run — stopping'); break; }
      const q = `${f.name} ${f.hq_city || ''} ${f.license_state || ''}`.replace(/\s+/g, ' ').trim();
      const hits = await searchText(q);
      calls++; await bumpQuota(1); logCost(1, `resolve "${f.name.slice(0, 40)}"`);
      const pick = pickWebsite(hits, f.name);
      if (pick && pick.confident) {
        resolved++;
        // COALESCE(NULLIF(...)) so we fill only empty fields — never clobber an
        // already-known website/phone with a fresh Places guess.
        await query(
          `UPDATE firm SET website        = COALESCE(NULLIF(website,''), $2),
                           phone          = COALESCE(NULLIF(phone,''),   $3),
                           street_address = COALESCE(NULLIF(street_address,''), $4),
                           address_source = CASE WHEN NULLIF(street_address,'') IS NULL AND $4 IS NOT NULL
                                                 THEN 'google_places_resolve' ELSE address_source END
             WHERE id = $1`, [f.id, pick.url, pick.phone || null, pick.address || null]);
        if (pick.phone) phones++;
        await query(
          `INSERT INTO firm_site (firm_id, url, discovery_method) VALUES ($1,$2,'google_places_resolve')
           ON CONFLICT (firm_id) DO NOTHING`, [f.id, pick.url]);
      } else if (pick) {
        // Loose match (host doesn't echo the firm name) — record the LEAD for
        // review/crawl but do NOT overwrite firm.website with a maybe-wrong site.
        lowConf++;
        await query(
          `INSERT INTO firm_site (firm_id, url, discovery_method, crawl_status)
           VALUES ($1,$2,'google_places_resolve','low_confidence') ON CONFLICT (firm_id) DO NOTHING`, [f.id, pick.url]);
      } else {
        noSite++;
        await query(
          `INSERT INTO firm_site (firm_id, url, discovery_method, crawl_status)
           VALUES ($1, NULL, 'google_places_resolve', 'no_url') ON CONFLICT (firm_id) DO NOTHING`, [f.id]);
      }
      await new Promise(res => setTimeout(res, 250)); // polite gap
    }
  } catch (e: any) {
    fatal = e;
    console.error(`[resolve] aborted, recording partial: ${String(e?.message || e).slice(0, 120)}`);
  }
  // Fail-soft: always close the run (never leave a zombie 'running').
  const status = calls > 0 ? 'ok' : 'failed';
  await closeRun(runId, status, {
    upserted: resolved, skipped: lowConf + noSite,
    notes: `${calls} Places calls, ${resolved} confident sites (${phones} w/phone), ${lowConf} low-confidence leads, ${noSite} no-site${ASSET ? ` [asset=${ASSET}]` : ''}${fatal ? ` · partial(${String(fatal?.message || fatal).slice(0, 40)})` : ''}`,
  });
  console.log(`[resolve] done: ${calls} calls · ${resolved} confident · ${lowConf} low-confidence · ${noSite} no-site · $0 (free-tier)${fatal ? ' (partial)' : ''}`);
  await pool.end();
}

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