`crawl/crexi-broker-enrich.js`

63 lines
'use strict';
// Enrich national (crexi) brokers with a real contact PATH: profile_url (crexi.com/brokers/<id>, $0)
// + brokerage website + title from api.crexi.com/brokers/<id> (cheap in-page batch). Crexi does NOT
// expose broker phone/email, so we never fabricate those — we link to the broker's real Crexi profile.
const fs = require('fs');
const path = require('path');
const { chromium } = require('playwright-core');
const Browserbase = require('@browserbasehq/sdk').default || require('@browserbasehq/sdk');
const { Pool } = require('pg');
const pool = new Pool({ host: '/tmp', database: 'crunified' });
const envTxt = fs.readFileSync(path.join(process.env.HOME, '.claude/skills/browserbase/.env'), 'utf8');
const API_KEY = (envTxt.match(/BROWSERBASE_API_KEY=(.*)/) || [])[1]?.trim();
const PROJECT = (envTxt.match(/BROWSERBASE_PROJECT_ID=(.*)/) || [])[1]?.trim();

(async () => {
  const t0 = Date.now();
  // 1) $0: profile_url for every crexi broker with an id
  await pool.query(`UPDATE brokers SET profile_url = 'https://www.crexi.com/brokers/' || crexi_id
    WHERE source='crexi' AND crexi_id IS NOT NULL AND crexi_id <> '' AND profile_url IS NULL`);
  const { rows: targets } = await pool.query(
    `SELECT crexi_id FROM brokers WHERE source='crexi' AND crexi_id IS NOT NULL AND crexi_id<>'' AND (website IS NULL OR website='') ORDER BY crexi_id`);
  console.log(`profile_url set for all crexi brokers; ${targets.length} to enrich with website/title`);
  if (!targets.length) { await pool.end(); return; }

  const bb = new Browserbase({ apiKey: API_KEY });
  const session = await bb.sessions.create({ projectId: PROJECT, browserSettings: { solveCaptchas: true, viewport: { width: 1440, height: 1000 } } });
  const browser = await chromium.connectOverCDP(session.connectUrl);
  const ctx = browser.contexts()[0]; const page = ctx.pages()[0] || await ctx.newPage();
  page.setDefaultTimeout(45000);
  await page.goto('https://www.crexi.com/brokers/806', { waitUntil: 'domcontentloaded', timeout: 35000 }).catch(()=>{});
  await page.waitForTimeout(4000);

  const ids = targets.map(t => t.crexi_id);
  let updated = 0, withSite = 0;
  for (let i = 0; i < ids.length; i += 30) {
    if ((Date.now() - t0) / 60000 > 30) { console.log('\n⏹ cap 30min'); break; }
    const batch = ids.slice(i, i + 30);
    const out = await page.evaluate(async (batch) => {
      const r = [];
      for (const id of batch) {
        try { const resp = await fetch(`https://api.crexi.com/brokers/${id}`, { headers: {} });
          if (resp.ok) { const j = await resp.json(); r.push({ id, website: (j.brokerage && j.brokerage.website) || j.website || null, title: j.title || j.jobTitle || null }); } }
        catch {}
      }
      return r;
    }, batch);
    for (const b of out) {
      if (!b.website && !b.title) continue;
      const res = await pool.query(
        `UPDATE brokers SET website=COALESCE(NULLIF($2,''), website), specialties=COALESCE(specialties,$3)
         WHERE crexi_id=$1 AND source='crexi'`, [b.id, b.website || '', b.title || null]);
      if (res.rowCount) { updated++; if (b.website) withSite++; }
    }
    process.stdout.write(`  ${Math.min(i + 30, ids.length)}/${ids.length}\r`);
  }
  await browser.close().catch(()=>{});
  const mins = (Date.now() - t0) / 60000;
  const [{ pc, wc }] = (await pool.query(`SELECT count(*) FILTER (WHERE profile_url IS NOT NULL) pc, count(*) FILTER (WHERE website IS NOT NULL) wc FROM brokers WHERE source='crexi'`)).rows;
  console.log(`\n=== DONE · ${mins.toFixed(1)} min · $${(mins*0.15).toFixed(2)} ===`);
  console.log(`crexi brokers: ${pc} now have a profile_url (contact path), ${wc} have a website. This run updated ${updated} (${withSite} websites).`);
  await pool.end();
})();