`scripts/export-residential-snapshots.js`

48 lines
// export-residential-snapshots.js — dump BOTH residential snapshots (data/sfr-redfin.json +
// data/condos-redfin.json) from the cre DB AFTER mark-off-market.js has run, so the prod (no-DB)
// snapshots carry the lifecycle fields (status / off_market_at / disposition / sold_price / sold_date).
//
// Supersedes export-sfr-snapshot.js in the refresh flow (that one is SFR-only and pre-lifecycle).
// Includes every active listing + off-market listings pulled within the last OFFMKT_DAYS (so the
// "pulled / sold" view has recent history without the file growing unbounded).
'use strict';
const fs = require('fs');
const path = require('path');
const { Pool } = require('pg');

const ROOT = path.join(__dirname, '..');
const pool = new Pool({ host: '/tmp', port: 5432, database: 'cre', user: process.env.USER || 'stevestudio2' });
const OFFMKT_DAYS = +(process.env.OFFMKT_DAYS || 180);
const WHERE = `WHERE price > 0 AND (status='active' OR off_market_at > now() - interval '${OFFMKT_DAYS} days')`;

(async () => {
  // SFR
  const sfr = (await pool.query(`
    SELECT id, address, city, zip, price, beds, baths, sqft, year_built, firm_name, broker_name,
           source, lat, lng, status, off_market_at, disposition, sold_price, sold_date, last_seen, created_at,
           days_on_market, listed_date, market_status, prev_market_status, prev_price, price_changed_at
      FROM sfr ${WHERE} ORDER BY (status='active') DESC, created_at DESC, price DESC`)).rows;
  fs.writeFileSync(path.join(ROOT, 'data', 'sfr-redfin.json'), JSON.stringify({
    meta: { source: 'Redfin LA County single-family — feed-first (gis-csv), local Chrome',
            note: 'status=off_market means the listing left Redfin\'s active feed; disposition=sold cross-referenced against closed_sale, else withdrawn.',
            offmarket_window_days: OFFMKT_DAYS, fetched_at: new Date().toISOString(), count: sfr.length },
    sfr }, null, 2));

  // Condos (via condo_card view — carries warrantability + lifecycle columns)
  const condos = (await pool.query(`
    SELECT id, address, city, zip, price, hoa, beds, baths, sqft, year_built, project_name,
           warrantable_status, warrant_source, warrant_signals, broker_name, firm_name, source, created_at,
           status, off_market_at, disposition, sold_price, sold_date, last_seen,
           days_on_market, listed_date, market_status, prev_market_status, prev_price, price_changed_at
      FROM condo_card ${WHERE} ORDER BY (status='active') DESC, created_at DESC, price DESC`)).rows;
  fs.writeFileSync(path.join(ROOT, 'data', 'condos-redfin.json'), JSON.stringify({
    meta: { source: 'Redfin LA County condos — feed-first (gis-csv), local Chrome',
            offmarket_window_days: OFFMKT_DAYS, fetched_at: new Date().toISOString(), count: condos.length },
    condos }, null, 2));

  const byStatus = (rows) => rows.reduce((a, r) => (a[r.status] = (a[r.status] || 0) + 1, a), {});
  console.log(JSON.stringify({ sfr: { count: sfr.length, byStatus: byStatus(sfr) },
                               condos: { count: condos.length, byStatus: byStatus(condos) } }));
  await pool.end();
})().catch(e => { console.error(e.message); process.exit(1); });