`scripts/backfill-openclaw-llm.js`

113 lines
#!/usr/bin/env node
// backfill-openclaw-llm.js — OPTION 3 (Steve 2026-08-19): real listings direct from broker FIRM
// sites via openclaw real-Chrome (defeats 403/JS) + local qwen3:14b extraction ($0). Firm-site
// listing GRIDS carry address+price but NOT the per-listing agent, so these are attributed at the
// FIRM level (role='firm-listing', linked to every broker at that firm) and shown on agent profiles
// as a clearly-labeled "Firm listings" section — never silently claimed as that one agent's book.
// Reversible: everything tagged source='broker-site-oc'. Dry-run default; --apply to write.
//
// Usage: node scripts/backfill-openclaw-llm.js [--limit N] [--apply]
const { execFileSync } = require('child_process');
const db = require('./db/brokers-db');
const OC = process.env.HOME + '/.npm-global/bin/openclaw';
const OLLAMA = 'http://127.0.0.1:11434/api/generate';
const LIMIT = +(process.argv.find(a => a.startsWith('--limit='))?.split('=')[1]) || 5;
const OFFSET = +(process.argv.find(a => a.startsWith('--offset='))?.split('=')[1]) || 0;
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 domainOf = u => { try { return new URL(/^https?:/i.test(u) ? u : 'https://' + u).hostname.replace(/^www\./, ''); } catch { return u; } };

const sleep = ms => new Promise(r => setTimeout(r, ms));
function oc(args, timeout = 45000) { try { return execFileSync(OC, args, { encoding: 'utf8', timeout, stdio: ['ignore', 'pipe', 'ignore'] }); } catch (e) { return null; } }
function ocEval(fn) { const out = oc(['browser', 'evaluate', '--fn', fn]); if (!out) return null; try { return JSON.parse(out); } catch { return out; } }

async function llmExtract(text) {
  // feed the price-dense window (skip nav/cookie header) in <=7k chunks
  const firstPrice = text.search(/\$[0-9]{3,}(,[0-9]{3})+/);
  const body = firstPrice > 0 ? text.slice(Math.max(0, firstPrice - 200)) : text;
  const out = [];
  for (let i = 0; i < body.length && i < 21000; i += 7000) {
    const chunk = body.slice(i, i + 7000);
    if (!/\$[0-9]{3,}/.test(chunk)) continue;
    const prompt = `From this real-estate broker listings text, extract each property listing. Return ONLY a JSON array (no prose/markdown), items {"address":string,"price":number,"type":string_or_null}. Skip anything without a real street address AND a price.\n\nTEXT:\n${chunk}`;
    try {
      const r = await fetch(OLLAMA, { method: 'POST', body: JSON.stringify({ model: 'qwen3:14b', prompt, stream: false, options: { temperature: 0 } }) }).then(x => x.json());
      const raw = (r.response || '').replace(/<think>[\s\S]*?<\/think>/g, '').replace(/```json|```/g, '').trim();
      const m = raw.match(/\[[\s\S]*\]/); if (!m) continue;
      const arr = JSON.parse(m[0]);
      for (const it of arr) {
        const addr = String(it.address || '').trim();
        const price = +String(it.price).replace(/[^0-9.]/g, '');
        const houseNo = (addr.match(/^\d+/) || ['0'])[0];
        if (addr.length >= 8 && price >= 100000 && price <= 500000000 && !/^0+$/.test(houseNo) && !/\b000\b/.test(addr))
          out.push({ address: addr, price, type: it.type || 'Commercial' });
      }
    } catch (e) { /* skip chunk */ }
  }
  // dedupe by address
  const seen = new Set(); return out.filter(l => { const k = l.address.toLowerCase(); return !seen.has(k) && seen.add(k); });
}

async function pageText() { // scroll to trigger lazy-loaded listing grids, then read
  for (let i = 0; i < 3; i++) { oc(['browser', 'evaluate', '--fn', '() => window.scrollTo(0, document.body.scrollHeight)']); await sleep(1200); }
  const txt = ocEval(`() => document.body.innerText`);
  return typeof txt === 'string' ? txt : (txt && (txt.result || txt.value)) || '';
}
async function scrapeDomain(url) {
  const base = /^https?:/i.test(url) ? url : 'https://' + url;
  if (!oc(['browser', 'navigate', base])) return { err: 'nav-fail' };
  // discover the listings page link on the homepage
  const links = ocEval(`() => [...document.querySelectorAll('a')].map(a=>({t:(a.textContent||'').trim().slice(0,40),h:a.href})).filter(l=>/listing|propert|for.?sale|inventory|available|our.?deals/i.test(l.t+' '+l.h)).slice(0,10)`);
  const cand = (Array.isArray(links) ? links : []).map(l => l.h).filter(Boolean);
  const listUrl = cand.find(h => /listing|propert|for-?sale|inventory/i.test(h)) || null;
  const origin = (() => { try { return new URL(base).origin; } catch { return base.replace(/\/$/, ''); } })();
  // try: discovered link + common subpaths + homepage; keep the best-yielding page
  const tryUrls = [...new Set([listUrl, origin + '/listings', origin + '/properties', origin + '/for-sale', origin + '/inventory', base].filter(Boolean))].slice(0, 4);
  let best = { listings: [], listUrl: base };
  for (const u of tryUrls) {
    if (u !== base && !oc(['browser', 'navigate', u])) continue;
    const text = await pageText();
    if (!text || text.length < 400) continue;
    const listings = await llmExtract(text);
    if (listings.length > best.listings.length) best = { listings, listUrl: u };
    if (best.listings.length >= 6) break; // good enough
  }
  return best.listings.length ? best : { err: 'no-listings' };
}

(async () => {
  // top SHARED firm domains (most agents per domain = highest yield/effort)
  const rows = (await db.pool.query(
    `SELECT b.firm_id, f.name AS firm, count(*) AS agents,
            (array_agg(b.website ORDER BY length(b.website)))[1] AS website,
            array_agg(b.id) AS broker_ids
       FROM broker b JOIN firm f ON f.id=b.firm_id
      WHERE b.website IS NOT NULL AND b.website !~* 'crexi' AND b.website !~* $1
      GROUP BY b.firm_id, f.name HAVING count(*) >= 2
      ORDER BY count(*) DESC LIMIT $2 OFFSET $3`, [BIG.source, LIMIT, OFFSET])).rows;
  console.log(`\n== Option-3 openclaw+LLM backfill · ${rows.length} firm domains · ${APPLY ? 'APPLY' : 'DRY-RUN'} ==\n`);
  let totFound = 0, wrote = 0;
  for (const r of rows) {
    process.stdout.write(`  ${r.firm} (${r.agents} agents) — ${domainOf(r.website)} … `);
    const res = await scrapeDomain(r.website);
    if (res.err) { console.log(`[${res.err}]`); continue; }
    totFound += res.listings.length;
    console.log(`${res.listings.length} firm listing(s)`);
    res.listings.slice(0, 3).forEach(l => console.log(`       $${l.price.toLocaleString()}  ${l.address}`));
    if (APPLY && res.listings.length) {
      for (const l of res.listings) {
        // FIRM-level rows in the dedicated direct-listing store (firm_name, no broker_id) — the
        // agent-profile endpoint joins these by the agent's firm and shows them labeled "Firm listings".
        const ins = await db.pool.query(
          `INSERT INTO broker_direct_listing (firm_name,address,price,type,role,source)
           VALUES ($1,$2,$3,$4,'firm-listing','broker-site-oc') ON CONFLICT DO NOTHING RETURNING id`,
          [r.firm, l.address, l.price, l.type]).catch(() => ({ rows: [] }));
        if (ins.rows[0]?.id) wrote++;
      }
    }
  }
  console.log(`\n== ${rows.length} firms · ${totFound} listings extracted · ${APPLY ? wrote + ' written (source=broker-site-oc, role=firm-listing, reversible)' : 'DRY-RUN'} ==`);
  console.log(APPLY ? "Undo: DELETE FROM broker_listing WHERE listing_id IN (SELECT id FROM listing WHERE source='broker-site-oc'); DELETE FROM listing WHERE source='broker-site-oc';" : 'Re-run with --apply to write.');
  process.exit(0);
})();