`scripts/backfill-broker-listings.js`

96 lines
#!/usr/bin/env node
// backfill-broker-listings.js — find listings DIRECTLY from brokers' own websites (Steve 2026-08-19),
// NEVER from crexi/aggregators. Enriches our broker graph (broker_listing -> listing) with real,
// direct-from-source listings so the agent-profile pages show more of an agent's actual book.
//
// STRATEGY (honest, per the pilot): broker "websites" are heterogeneous —
//   - plain-fetchable boutique sites (cremgroupre) -> curl works
//   - 403 bot-blocked (lyonstahl) -> need openclaw real-Chrome (Phase 2, not this pilot)
//   - JS-rendered (strandsrealty) -> need a browser (Phase 2)
// This pilot does the PLAIN-FETCH subset + a heuristic price/address extractor and runs DRY by
// default (prints what it WOULD insert). --apply writes to the DB, tagging rows source='broker-site'
// so the whole backfill is reversible (DELETE FROM listing WHERE source='broker-site').
//
// Usage: node scripts/backfill-broker-listings.js [--limit N] [--apply]
const db = require('./db/brokers-db');
const LIMIT = +(process.argv.find(a => a.startsWith('--limit='))?.split('=')[1]) || 8;
const APPLY = process.argv.includes('--apply');
const BIG = /cbre|kw\.com|yourkwoffice|kellerwilliams|marcusmillichap|kidder|coldwell|compass|remax|century21|colliers|jll|cushman|berkshire/i;

const UA = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120 Safari/537.36';
const _domCache = new Map(); // fetch each domain ONCE (many agents share a firm site — don't hammer)
const domainOf = u => { try { return new URL(/^https?:/i.test(u) ? u : 'https://' + u).hostname.replace(/^www\./, ''); } catch { return u; } };
async function fetchSite(url) {
  const dom = domainOf(url);
  if (_domCache.has(dom)) return _domCache.get(dom);
  const r = await _fetchSite(url); _domCache.set(dom, r); return r;
}
async function _fetchSite(url) {
  try {
    if (!/^https?:\/\//i.test(url)) url = 'https://' + url.replace(/^\/+/, ''); // scheme-less sites (www.x.com)
    const ctrl = new AbortController(); const t = setTimeout(() => ctrl.abort(), 15000);
    const r = await fetch(url, { headers: { 'User-Agent': UA, Accept: 'text/html' }, redirect: 'follow', signal: ctrl.signal });
    clearTimeout(t);
    if (!r.ok) return { ok: false, status: r.status };
    return { ok: true, status: r.status, html: await r.text() };
  } catch (e) { return { ok: false, status: 'ERR', err: String(e.message).slice(0, 40) }; }
}

// Heuristic listing extractor: find price tokens, grab a nearby street-address-looking phrase.
const strip = h => h.replace(/<script[\s\S]*?<\/script>/gi, ' ').replace(/<style[\s\S]*?<\/style>/gi, ' ').replace(/<[^>]+>/g, ' ').replace(/&nbsp;/g, ' ').replace(/\s+/g, ' ');
const ADDR = /\b\d{2,6}\s+(?:[NSEW]\.?\s+)?[A-Z][A-Za-z0-9.'-]+(?:\s+[A-Z][A-Za-z0-9.'-]+){0,3}\s+(?:St|Street|Ave|Avenue|Blvd|Boulevard|Rd|Road|Dr|Drive|Ln|Lane|Way|Pl|Place|Ct|Court|Hwy|Highway)\b/g;
function extractListings(html) {
  const text = strip(html);
  const out = [];
  const addrs = text.match(ADDR) || [];
  for (const a of [...new Set(addrs)].slice(0, 40)) {
    const i = text.indexOf(a);
    const window = text.slice(Math.max(0, i - 120), i + 180);
    const pm = window.match(/\$\s?([0-9]{3,}(?:,[0-9]{3})+)/);
    const price = pm ? +pm[1].replace(/,/g, '') : null;
    const addr = a.trim();
    // reject garbage: leading-zero / placeholder house numbers ("000 Street ..."), too-short
    const houseNo = (addr.match(/^\d+/) || ['0'])[0];
    const bad = /^0+$/.test(houseNo) || /^0+\s/.test(addr) || /\b000\b/.test(addr) || addr.length < 8;
    if (price && price >= 100000 && price <= 500000000 && !bad) out.push({ address: addr, price });
  }
  // dedupe by address
  const seen = new Set(); return out.filter(l => !seen.has(l.address.toLowerCase()) && seen.add(l.address.toLowerCase()));
}

(async () => {
  const brokers = (await db.pool.query(
    `SELECT id, name, website FROM broker WHERE website IS NOT NULL AND website !~* 'crexi' AND website !~* $1 LIMIT $2`,
    [BIG.source, LIMIT])).rows;
  // Which domains are shared by >1 broker? Their listings are FIRM-level, not one agent's —
  // don't attribute those to a single agent (the misattribution trap). Only unique domains get linked.
  const shareRows = (await db.pool.query(
    `SELECT website FROM broker WHERE website IS NOT NULL AND website !~* 'crexi' AND website !~* $1`, [BIG.source])).rows;
  const domCount = {}; for (const r of shareRows) { const d = domainOf(r.website); domCount[d] = (domCount[d] || 0) + 1; }
  const isShared = url => (domCount[domainOf(url)] || 0) > 1;
  console.log(`\n== Backfill pilot: ${brokers.length} boutique broker sites · ${APPLY ? 'APPLY (writing)' : 'DRY-RUN'} ==\n`);
  let totalFound = 0, wrote = 0, blocked = 0;
  for (const b of brokers) {
    const r = await fetchSite(b.website);
    if (!r.ok) { console.log(`  ✗ ${b.name} — ${b.website} [${r.status}${r.err ? ' ' + r.err : ''}] (Phase-2 openclaw)`); blocked++; continue; }
    const listings = extractListings(r.html);
    totalFound += listings.length;
    const shared = isShared(b.website);
    console.log(`  ${listings.length ? '✓' : '·'} ${b.name} — ${b.website} → ${listings.length} listing(s)${shared && listings.length ? ' [SHARED firm site — not attributed]' : ''}`);
    listings.slice(0, 4).forEach(l => console.log(`       $${l.price.toLocaleString()}  ${l.address}`));
    if (APPLY && listings.length && !shared) {  // only attribute UNIQUE-domain (personal-site) listings to the agent
      for (const l of listings) {
        // personal-site listing -> dedicated direct store, attributed to this agent (unique index dedups)
        const ins = await db.pool.query(
          `INSERT INTO broker_direct_listing (broker_id, agent_name, address, price, type, role, source)
           VALUES ($1, $2, $3, $4, 'Commercial', 'agent', 'broker-site') ON CONFLICT DO NOTHING RETURNING id`,
          [b.id, b.name, l.address, l.price]).catch(() => ({ rows: [] }));
        if (ins.rows[0]?.id) wrote++;
      }
    }
  }
  console.log(`\n== ${brokers.length} sites · ${totalFound} listings extracted · ${blocked} blocked (need openclaw) · ${APPLY ? wrote + ' written (source=broker-site, reversible)' : 'DRY-RUN (no writes)'} ==`);
  console.log(APPLY ? 'Undo: DELETE FROM broker_listing WHERE listing_id IN (SELECT id FROM listing WHERE source=\'broker-site\'); DELETE FROM listing WHERE source=\'broker-site\';' : 'Re-run with --apply to write.');
  process.exit(0);
})();