`scripts/enrich-sfr-pools.js`

153 lines
#!/usr/bin/env node
// enrich-sfr-pools.js — pool enrichment for the SFV $700k–$1.2M active SFR set.
//
// WHY: the Redfin gis-csv feed we ingest into cre.sfr carries NO pool/amenity data (price/beds/baths/
// sqft only). Pool status lives ONLY on each listing's Redfin DETAIL page, in an embedded structured
// amenity blob:  {"amenityName":"Pool Features","referenceName":"POOL_FEATURES","amenityValues":[...]}
//
// TRANSPORT: plain curl/fetch is soft-blocked by Redfin after ~8 rapid hits (200s go empty). So we drive
// ONE warmed real-Chrome session (playwright-core + the installed Chrome app + the AutomationControlled
// evasion flag) — the SAME transport fetch-sfr-redfin.js uses to beat Redfin's anti-bot. Run it with the
// browserbase skill's node_modules on NODE_PATH (that's where playwright-core lives):
//   NODE_PATH=$HOME/.claude/skills/browserbase/node_modules node scripts/enrich-sfr-pools.js
//
// We read the STRUCTURED POOL_FEATURES field (NOT free-text "swimming pool", which false-positives on
// "community pool" / reviews) → has_pool = true / false / null(unknown: detail page had no such field).
// Caches to cre.sfr_pool + data/sfr-pools.json (prod snapshot). Idempotent + resumable: only (re)checks
// rows not seen within FRESH_DAYS, so it fills the band incrementally across scheduled runs.
'use strict';
const fs = require('fs');
const path = require('path');
const { Pool } = require('pg');
const { chromium } = require('playwright-core');

const ROOT = path.join(__dirname, '..');
const CHROME_PATH = process.env.CHROME_PATH || '/Applications/Google Chrome.app/Contents/MacOS/Google Chrome';
const pool = new Pool({ host: '/tmp', port: 5432, database: 'cre', user: process.env.USER || 'stevestudio2' });

const SFV_CITIES = ['Encino','Tarzana','Woodland Hills','Sherman Oaks','Van Nuys','North Hollywood',
  'Studio City','Reseda','Northridge','Granada Hills','Canoga Park','Winnetka','West Hills','Chatsworth',
  'Panorama City','Sun Valley','Valley Village','Valley Glen','North Hills','Porter Ranch','Arleta',
  'Pacoima','Sylmar','Mission Hills','Lake Balboa','Toluca Lake','Sunland','Tujunga','Shadow Hills'];
const PRICE_MIN = +(process.env.PRICE_MIN || 700000);
const PRICE_MAX = +(process.env.PRICE_MAX || 1200000);
const CAP = +(process.env.CAP || 1500);
const FRESH_DAYS = +(process.env.FRESH_DAYS || 21);
const sleep = ms => new Promise(r => setTimeout(r, ms));

// Parse the structured POOL_FEATURES amenity. true=real pool value, false=explicit None, null=field absent.
function classifyPool(html) {
  const m = html.match(/"amenityName\\?":\\?"Pool Features\\?"[^}]*?"amenityValues\\?":\\?\[([^\]]*)\]/i)
         || html.match(/POOL_FEATURES\\?"[^}]*?"amenityValues\\?":\\?\[([^\]]*)\]/i);
  if (!m) return { hasPool: null, features: null };
  const raw = m[1].replace(/\\"/g, '"');
  const vals = (raw.match(/"([^"]+)"/g) || []).map(s => s.replace(/"/g, '').trim()).filter(Boolean);
  const features = vals.join(', ') || null;
  const joined = vals.join(' ').toLowerCase();
  const positive = /(in ground|above ground|private|community|association|lap|infinity|heated|gunite|fenced|salt|solar|pool\/spa|pool spa)/.test(joined);
  const negative = vals.length === 0 || (/\bnone\b/.test(joined) && !positive) || /^no\b/.test(joined.trim());
  return { hasPool: positive ? true : (negative ? false : (vals.length ? true : null)), features };
}

// Lot size from the structured "Lot Size" amenity (display value) + a numeric lotSize field (sqft).
function extractLot(html) {
  let display = null, sqft = null;
  const am = html.match(/"amenityName\\?":\\?"Lot Size\\?"[^}]*?"amenityValues\\?":\\?\[\\?"([^"\\]{1,40})/i);
  if (am) display = am[1].replace(/\\u002c/gi, ',').trim();
  const num = html.match(/"lotSize\\?":\s*(\d{3,9})/i);
  if (num) sqft = +num[1];
  // derive sqft from a "X square feet"/"X Sq. Ft." display if the numeric field was absent
  if (sqft == null && display) { const m = display.replace(/,/g, '').match(/(\d{3,9})\s*(sq|square)/i); if (m) sqft = +m[1]; }
  // acres display → sqft
  if (sqft == null && display) { const a = display.replace(/,/g, '').match(/([\d.]+)\s*acre/i); if (a) sqft = Math.round(parseFloat(a[1]) * 43560); }
  if (!display && sqft != null) display = sqft.toLocaleString() + ' sq ft';
  return { lot_size: display, lot_sqft: sqft };
}

// Primary LISTING BROKER + agent from the MLS record Redfin surfaces (the primary listing broker who
// holds the listing — what Steve wants over aggregator attribution). Structured JSON fields.
function extractBroker(html) {
  const b = html.match(/"listingBrokerName\\?":\s*\\?"([^"\\]{2,80})/i) || html.match(/"brokerName\\?":\s*\\?"([^"\\]{2,80})/i);
  const a = html.match(/"listingAgentName\\?":\s*\\?"([^"\\]{2,60})/i) || html.match(/"agentName\\?":\s*\\?"([^"\\]{2,60})/i);
  const clean = s => s ? s.replace(/\\u0026/g,'&').replace(/\\u002c/g,',').trim() : null;
  return { broker: clean(b && b[1]), agent: clean(a && a[1]) };
}

// Primary listing photo from og:image (falls back to the first cdn-redfin bigphoto).
function extractPhoto(html) {
  const og = html.match(/property=\\?"og:image\\?"[^>]*content=\\?"(https:\/\/[^"\\ >]{20,160})/i)
          || html.match(/content=\\?"(https:\/\/ssl\.cdn-redfin\.com\/photo\/[^"\\ >]{20,160})\\?"[^>]*property=\\?"og:image/i);
  if (og) return og[1].replace(/\\u002f/gi, '/');
  const cdn = html.match(/https:\/\/ssl\.cdn-redfin\.com\/photo\/[^"\\ >]{20,120}\.jpg/i);
  return cdn ? cdn[0] : null;
}

async function main() {
  await pool.query(`CREATE TABLE IF NOT EXISTS sfr_pool (
    id text PRIMARY KEY, has_pool boolean, pool_features text, http_status int,
    lot_size text, lot_sqft int, photo_url text,
    checked_at timestamptz NOT NULL DEFAULT now())`);
  await pool.query(`ALTER TABLE sfr_pool ADD COLUMN IF NOT EXISTS lot_size text,
    ADD COLUMN IF NOT EXISTS lot_sqft int, ADD COLUMN IF NOT EXISTS photo_url text`);

  const cities = SFV_CITIES.map(c => `'${c.replace(/'/g, "''")}'`).join(',');
  const { rows } = await pool.query(
    `SELECT s.id, s.address, s.city, s.source
       FROM sfr s LEFT JOIN sfr_pool p ON p.id=s.id
      WHERE s.status='active' AND s.price::int BETWEEN $1 AND $2 AND s.city IN (${cities})
        AND s.source IS NOT NULL
        AND (p.id IS NULL OR p.has_pool IS NULL OR p.photo_url IS NULL
             OR p.checked_at < now() - ($3 || ' days')::interval)
      ORDER BY (p.id IS NULL) DESC, (p.photo_url IS NULL) DESC, (p.has_pool IS NULL) DESC, s.price::int DESC
      LIMIT $4`, [PRICE_MIN, PRICE_MAX, FRESH_DAYS, CAP]);
  console.log(`[enrich-pools] real-Chrome; ${rows.length} listings to (re)check`);
  if (!rows.length) { await snapshot(); await pool.end(); return; }

  const browser = await chromium.launch({ executablePath: CHROME_PATH, args: ['--disable-blink-features=AutomationControlled'] });
  const ctx = await browser.newContext({ userAgent: 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/128.0.0.0 Safari/537.36', viewport: { width: 1440, height: 1000 } });
  const page = await ctx.newPage();
  // warm the session (get Redfin cookies before hammering detail pages)
  await page.goto('https://www.redfin.com/city/11203/CA/Los-Angeles', { waitUntil: 'domcontentloaded' }).catch(() => {});
  await page.waitForTimeout(2500);

  let done = 0, withPool = 0, without = 0, unknown = 0, failed = 0, blockedStreak = 0;
  for (const row of rows) {
    let hasPool = null, features = null, status = 0, lot = null, lotSqft = null, photo = null;
    try {
      const resp = await page.goto(row.source, { waitUntil: 'domcontentloaded', timeout: 25000 });
      status = resp ? resp.status() : 0;
      await page.waitForTimeout(400);
      const html = await page.content();
      if (html && html.length > 40000) {           // real listing page (>40KB); stripped blocks are tiny
        const c = classifyPool(html); hasPool = c.hasPool; features = c.features;
        const L = extractLot(html); lot = L.lot_size; lotSqft = L.lot_sqft;
        photo = extractPhoto(html); blockedStreak = 0;
      } else { blockedStreak++; }                    // soft-block / empty
    } catch (_) { failed++; blockedStreak++; }
    await pool.query(
      `INSERT INTO sfr_pool(id,has_pool,pool_features,http_status,lot_size,lot_sqft,photo_url,checked_at)
         VALUES($1,$2,$3,$4,$5,$6,$7,now())
       ON CONFLICT (id) DO UPDATE SET has_pool=$2, pool_features=$3, http_status=$4,
         lot_size=$5, lot_sqft=$6, photo_url=$7, checked_at=now()`,
      [row.id, hasPool, features, status || null, lot, lotSqft, photo]);
    hasPool === true ? withPool++ : hasPool === false ? without++ : unknown++;
    done++;
    if (done % 25 === 0) { console.log(`[enrich-pools] ${done}/${rows.length} pool:${withPool} no:${without} unk:${unknown} fail:${failed}`); await snapshot(); }
    // if Redfin starts soft-blocking (many empty pages in a row), back off hard then keep trying
    if (blockedStreak >= 6) { console.log(`[enrich-pools] soft-block streak ${blockedStreak} — backing off 60s`); await sleep(60000); blockedStreak = 0; }
    else await sleep(1200 + Math.floor(Math.random() * 1600)); // 1.2–2.8s between pages
  }
  await browser.close();
  await snapshot();
  console.log(`[enrich-pools] DONE checked:${done} withPool:${withPool} without:${without} unknown:${unknown} failed:${failed}`);
  await pool.end();
}

async function snapshot() {
  const snap = (await pool.query(`SELECT id, has_pool, pool_features, lot_size, lot_sqft, photo_url, checked_at FROM sfr_pool`)).rows;
  fs.mkdirSync(path.join(ROOT, 'data'), { recursive: true });
  fs.writeFileSync(path.join(ROOT, 'data', 'sfr-pools.json'),
    JSON.stringify({ updated_at: new Date().toISOString(), pools: snap }, null, 0));
}
main().catch(e => { console.error('[enrich-pools] FATAL', e); process.exit(1); });