`src/enrich/apply_exa_websites.ts`

61 lines
// Apply Exa-discovered websites to the businesses table.
//
// Reads a JSON array (file or stdin) of `[{ id, name, website, source }, ...]`,
// validates each URL (must be http/https, must NOT be yelp/facebook/yellowpages
// /bbb/manta/google.com — those are aggregators not primary websites),
// then UPDATEs businesses.website where currently null.
//
// Run:
//   npx tsx src/enrich/apply_exa_websites.ts /tmp/exa_results.json
//   curl ... | npx tsx src/enrich/apply_exa_websites.ts -

import * as fs from 'node:fs';
import { pool, query } from '../../db/pool.ts';

const AGGREGATORS = /^(www\.)?(yelp|facebook|instagram|twitter|x|linkedin|yellowpages|bbb|manta|trustpilot|opentable|grubhub|doordash|ubereats|google|maps\.google|tripadvisor|nextdoor|whitepages|spokeo)\.(com|net|org)$/i;

function normalize(raw: string): string | null {
  if (!raw) return null;
  let u = raw.trim();
  if (!u) return null;
  if (!/^https?:\/\//i.test(u)) u = 'https://' + u;
  let parsed: URL;
  try { parsed = new URL(u); } catch { return null; }
  if (AGGREGATORS.test(parsed.host)) return null;
  // Strip query strings and fragments — keep path so brand chain pages survive
  return parsed.protocol + '//' + parsed.host + parsed.pathname.replace(/\/+$/, '');
}

async function main() {
  const path = process.argv[2];
  if (!path) {
    console.error('usage: apply_exa_websites.ts <file.json | ->');
    process.exit(1);
  }
  const raw = path === '-' ? fs.readFileSync(0, 'utf8') : fs.readFileSync(path, 'utf8');
  // Tolerate the agent wrapping the JSON in markdown fences or commentary.
  const m = raw.match(/\[[\s\S]*\]/);
  if (!m) { console.error('no JSON array found in input'); process.exit(1); }
  const rows: Array<{ id: number; name: string; website: string | null; source?: string }> = JSON.parse(m[0]);
  console.log(`[apply_exa] ${rows.length} candidates`);

  let updated = 0, skipped_null = 0, skipped_invalid = 0, skipped_existing = 0;
  for (const r of rows) {
    if (!r.website) { skipped_null++; continue; }
    const norm = normalize(r.website);
    if (!norm) { skipped_invalid++; console.log(`  ✕ ${r.id} ${r.name} → ${r.website} (rejected)`); continue; }

    const existing = await query<{ website: string | null }>(`SELECT website FROM businesses WHERE id = $1`, [r.id]);
    if (!existing.rowCount) { console.log(`  ? ${r.id} not found`); continue; }
    if (existing.rows[0].website) { skipped_existing++; continue; }

    await query(`UPDATE businesses SET website = $1, last_seen_at = NOW() WHERE id = $2`, [norm, r.id]);
    updated++;
    console.log(`  ✓ ${r.id} ${r.name.slice(0, 40).padEnd(40)} → ${norm} [${r.source || '?'}]`);
  }
  console.log(`[apply_exa] done · ${updated} updated · ${skipped_null} null · ${skipped_invalid} invalid · ${skipped_existing} already had website`);
  await pool.end();
}

main().catch(e => { console.error(e); process.exit(1); });