`scripts/build-map-points.js`

45 lines
// build-map-points.js — refresh data/map-points.json: keep the commercial (CRE) points as-is and
// rebuild the residential (SFR + condo) points from the cre DB WITH an rstatus lifecycle field
// (Active / In Escrow / Sold / Withdrawn) so the map can color residential by status.
'use strict';
const fs = require('fs');
const path = require('path');
const { Pool } = require('pg');
const ROOT = path.join(__dirname, '..');
const OUT = path.join(ROOT, 'data', 'map-points.json');
const pool = new Pool({ host: '/tmp', port: 5432, database: 'cre', user: process.env.USER || 'stevestudio2' });

const rstatus = r => r.status === 'off_market'
  ? (r.disposition === 'sold' ? 'Sold' : 'Withdrawn')
  : (/pending|contingent/i.test(r.market_status || '') ? 'In Escrow' : 'Active');

(async () => {
  // 1) keep existing non-residential (commercial) points
  let commercial = [];
  try {
    const prev = JSON.parse(fs.readFileSync(OUT, 'utf8'));
    const arr = Array.isArray(prev) ? prev : (prev.points || []);
    commercial = arr.filter(p => p.type !== 'SFR' && p.type !== 'Condo');
  } catch (_) {}

  // 2) rebuild residential from DB (only geocoded rows)
  const res = [];
  for (const [tbl, type] of [['sfr', 'SFR'], ['condo', 'Condo']]) {
    const rows = (await pool.query(
      `SELECT id, address, city, zip, price, lat, lng, status, market_status, disposition, days_on_market, source
         FROM ${tbl} WHERE lat IS NOT NULL AND lng IS NOT NULL AND price > 0`)).rows;
    for (const r of rows) res.push({
      id: 'res-' + r.id, kind: 'residential', type, lat: +r.lat, lng: +r.lng,
      address: r.address, city: r.city, zip: r.zip, price: +r.price,
      rstatus: rstatus(r), dom: r.days_on_market, source: r.source,
    });
  }

  const points = commercial.concat(res);
  // map.html reads `data.points` — keep the {points:[...]} object shape.
  fs.writeFileSync(OUT, JSON.stringify({ built_at: new Date().toISOString(), points }));
  const byStatus = res.reduce((a, p) => (a[p.rstatus] = (a[p.rstatus] || 0) + 1, a), {});
  console.log(JSON.stringify({ commercial: commercial.length, residential: res.length, byStatus }));
  await pool.end();
})().catch(e => { console.error(e.message); process.exit(1); });