`scripts/residential-report.js`

80 lines
// residential-report.js — build the LA County RESIDENTIAL market pulse email (SFR + condo) from the
// cre DB: Just Listed / In Escrow / Just Sold / Fell Out of Escrow + by-city breakdown. Prints HTML to
// stdout. Local + free. Window (days) via REPORT_DAYS (default 7).
'use strict';
const { Pool } = require('pg');
const pool = new Pool({ host: '/tmp', port: 5432, database: 'cre', user: process.env.USER || 'stevestudio2' });
const DAYS = +(process.env.REPORT_DAYS || 7);
const today = process.env.REPORT_DATE || new Date().toLocaleDateString('en-CA', { timeZone: 'America/Los_Angeles' });
const money = n => n == null ? '—' : '$' + Math.round(+n).toLocaleString();
const esc = s => (s == null ? '' : String(s)).replace(/&/g, '&amp;').replace(/</g, '&lt;');

// UNION SFR + condo into one residential set with a type label.
const UNI = `(
  SELECT 'SFR' t, address, city, zip, price, beds, baths, sqft, status, market_status, prev_market_status,
         days_on_market, listed_date, disposition, sold_price, sold_date, off_market_at, source, prev_price, price_changed_at FROM sfr
  UNION ALL
  SELECT 'Condo' t, address, city, zip, price, beds, baths, sqft, status, market_status, prev_market_status,
         days_on_market, listed_date, disposition, sold_price, sold_date, off_market_at, source, prev_price, price_changed_at FROM condo
)`;

(async () => {
  const q = (sql, a = []) => pool.query(sql, a).then(r => r.rows);
  const one = async (sql, a = []) => (await q(sql, a))[0] || {};

  const s = await one(`SELECT
    count(*) FILTER (WHERE status='active') active,
    count(*) FILTER (WHERE market_status IN ('Pending','Contingent')) in_escrow,
    count(*) FILTER (WHERE status='active' AND days_on_market IS NOT NULL AND days_on_market<=$1) just_listed,
    count(*) FILTER (WHERE disposition='sold' AND off_market_at > now()-($1||' days')::interval) just_sold,
    count(*) FILTER (WHERE status='off_market' AND disposition='withdrawn') withdrawn,
    count(*) FILTER (WHERE status='active' AND prev_price IS NOT NULL AND price < prev_price AND price_changed_at > now()-($1||' days')::interval) price_cuts
    FROM ${UNI} u`, [DAYS]);

  const justListed = await q(`SELECT t,address,city,price,beds,baths,days_on_market dom,source FROM ${UNI} u
    WHERE status='active' AND days_on_market IS NOT NULL AND days_on_market<=$1
    ORDER BY days_on_market ASC, price DESC LIMIT 20`, [DAYS]);
  const justSold = await q(`SELECT t,address,city,sold_price,sold_date FROM ${UNI} u
    WHERE disposition='sold' AND off_market_at > now()-($1||' days')::interval
    ORDER BY sold_date DESC NULLS LAST LIMIT 20`, [DAYS]);
  const escrowByCity = await q(`SELECT city, count(*) n FROM ${UNI} u
    WHERE market_status IN ('Pending','Contingent') GROUP BY city ORDER BY n DESC LIMIT 12`);
  const fellOut = await q(`SELECT t,address,city,price,beds,baths,source FROM ${UNI} u
    WHERE status='active' AND prev_market_status IN ('Pending','Contingent') AND market_status NOT IN ('Pending','Contingent')
    ORDER BY price DESC LIMIT 20`);
  const priceCuts = await q(`SELECT t,address,city,price,prev_price,source FROM ${UNI} u
    WHERE status='active' AND prev_price IS NOT NULL AND price < prev_price AND price_changed_at > now()-($1||' days')::interval
    ORDER BY (prev_price-price) DESC LIMIT 20`, [DAYS]);

  const tile = (label, val, color) => `<td style="padding:14px 16px;background:#161b22;border:1px solid #2a313c;border-radius:10px;text-align:center">
    <div style="font-size:26px;font-weight:700;color:${color}">${(+val).toLocaleString()}</div>
    <div style="font-size:11px;color:#8b949e;text-transform:uppercase;letter-spacing:.5px;margin-top:2px">${label}</div></td>`;
  const rowsTbl = (rows, cols) => rows.length ? `<table style="width:100%;border-collapse:collapse;font-size:13px">
    <tr style="color:#8b949e;text-align:left">${cols.map(c => `<th style="padding:6px 8px;border-bottom:1px solid #2a313c;font-size:11px;text-transform:uppercase">${c[0]}</th>`).join('')}</tr>
    ${rows.map(r => `<tr>${cols.map(c => `<td style="padding:6px 8px;border-bottom:1px solid #20262f">${c[1](r)}</td>`).join('')}</tr>`).join('')}
    </table>` : '<div style="color:#8b949e;font-size:13px;padding:8px">None in the last ' + DAYS + ' days.</div>';

  const html = `<!doctype html><html><body style="margin:0;background:#0e1116;color:#e6edf3;font-family:-apple-system,Helvetica,Arial,sans-serif;padding:22px">
  <div style="max-width:760px;margin:0 auto">
    <h1 style="font-size:20px;margin:0 0 3px">🏠 LA County Residential Pulse <span style="color:#3fb950">· ${today}</span></h1>
    <div style="color:#8b949e;font-size:12px;margin-bottom:16px">Redfin for-sale + in-escrow · last ${DAYS} days · <a href="https://crcp.agentabrams.com/mls.html" style="color:#58a6ff">open the MLS →</a></div>
    <table style="width:100%;border-spacing:8px;margin:-8px 0 18px"><tr>
      ${tile('Active', s.active, '#e6edf3')}${tile('In Escrow', s.in_escrow, '#d29922')}${tile('Just Listed', s.just_listed, '#3fb950')}${tile('Price Cuts', s.price_cuts, '#ffa600')}${tile('Just Sold', s.just_sold, '#58a6ff')}${tile('Withdrawn', s.withdrawn, '#f85149')}
    </tr></table>
    <h3 style="font-size:13px;text-transform:uppercase;color:#8b949e;margin:18px 0 6px">🆕 Just Listed (newest ${DAYS}d)</h3>
    ${rowsTbl(justListed, [['Address', r => `<a href="${esc(r.source)}" style="color:#58a6ff;text-decoration:none">${esc(r.address)}</a>`], ['City', r => esc(r.city)], ['Type', r => r.t], ['Price', r => money(r.price)], ['Bd/Ba', r => `${r.beds ?? '—'}/${r.baths ?? '—'}`], ['DOM', r => r.dom + 'd']])}
    <h3 style="font-size:13px;text-transform:uppercase;color:#8b949e;margin:20px 0 6px">✅ Just Sold (closed escrow, ${DAYS}d)</h3>
    ${rowsTbl(justSold, [['Address', r => esc(r.address)], ['City', r => esc(r.city)], ['Type', r => r.t], ['Sold', r => money(r.sold_price)], ['Date', r => r.sold_date ? String(r.sold_date).slice(0, 10) : '—']])}
    <h3 style="font-size:13px;text-transform:uppercase;color:#8b949e;margin:20px 0 6px">💸 Price Cuts (biggest, ${DAYS}d)</h3>
    ${rowsTbl(priceCuts, [['Address', r => `<a href="${esc(r.source)}" style="color:#58a6ff;text-decoration:none">${esc(r.address)}</a>`], ['City', r => esc(r.city)], ['Type', r => r.t], ['Was', r => money(r.prev_price)], ['Now', r => money(r.price)], ['Cut', r => `<span style="color:#ffa600">-${money(r.prev_price - r.price)}</span>`]])}
    <h3 style="font-size:13px;text-transform:uppercase;color:#8b949e;margin:20px 0 6px">↩️ Fell Out of Escrow (back on market)</h3>
    ${rowsTbl(fellOut, [['Address', r => `<a href="${esc(r.source)}" style="color:#58a6ff;text-decoration:none">${esc(r.address)}</a>`], ['City', r => esc(r.city)], ['Type', r => r.t], ['Price', r => money(r.price)], ['Bd/Ba', r => `${r.beds ?? '—'}/${r.baths ?? '—'}`]])}
    <h3 style="font-size:13px;text-transform:uppercase;color:#8b949e;margin:20px 0 6px">🤝 In Escrow by City</h3>
    ${rowsTbl(escrowByCity, [['City', r => esc(r.city)], ['Pending / Contingent', r => `<b>${r.n}</b>`]])}
    <div style="color:#8b949e;font-size:11px;margin-top:22px;border-top:1px solid #2a313c;padding-top:10px">
      Fell-Out accrues as listings transition Pending→Active across sweeps. Just-Sold cross-references recorded sales; escrow lag means more sales surface over the following weeks.</div>
  </div></body></html>`;
  process.stdout.write(html);
  await pool.end();
})().catch(e => { console.error(e.message); process.exit(1); });