`scripts/report-extras.js`

146 lines
// report-extras.js — shared, email-safe building blocks for the SFV CRE reports.
// Added 2026-06-26 per Steve's request: "add a graphics and make table of info, and
// suggest more data points.. all zillow info that they use" (reply to the 1pm report).
//
// Three exports, all returning HTML strings that render in Gmail:
//   derived(p)         — computes extra CRE/Zillow-style metrics from a listing+finance
//   barChart(rows,opt) — email-safe CSS bar chart (Gmail strips <svg>, so we use <div> bars)
//   dataTable(rows,opt)— rich "table of info" with the derived columns
//   moreDataPoints()   — the suggested additional Zillow-style data points to scrape next
//
// Pure, no deps, works under Node require. finance is already computed on each row by the
// caller (afternoon-report.js / morning-report.js recompute via finance.js first).
'use strict';

const fmt = n => (n == null || Number.isNaN(n)) ? '—' : '$' + Math.round(n).toLocaleString();
const esc = s => String(s == null ? '' : s).replace(/[&<>"]/g, c => ({ '&': '&amp;', '<': '&lt;', '>': '&gt;', '"': '&quot;' }[c]));

// ── derived metrics ──────────────────────────────────────────────────────────
// price-per-unit (price per door) is the bread-and-butter SFV multifamily metric and
// is ALWAYS computable. $/sqft + age + GRM fill in when the source has the inputs —
// the gaps are exactly what the "more data points" section argues we should scrape.
function derived(p) {
  const f = p.finance || {};
  const perUnit = p.units > 0 ? Math.round(p.price / p.units) : null;
  const perSqft = p.sqft > 0 ? Math.round(p.price / p.sqft) : null;
  const grossAnnual = f.grossAnnual || null;
  const grm = grossAnnual ? +(p.price / grossAnnual).toFixed(1) : null; // Gross Rent Multiplier
  const age = p.year_built ? (new Date().getFullYear() - p.year_built) : null;
  return { perUnit, perSqft, grm, age };
}

// color a value by economics: green = positive leverage, amber = affordable but flat/unknown,
// red = over budget. Mirrors the card/table coloring already used in the reports.
function econColor(p) {
  const f = p.finance || {};
  if (!f.affordable) return '#b00020';
  if (f.coc != null && f.coc > 0) return '#0a7d2c';
  return '#a15c00';
}

// ── email-safe CSS bar chart ─────────────────────────────────────────────────
// Horizontal bars of composite score for the top-N rows. Gmail renders <table> + a
// <div> with inline width%+background, but NOT <svg> — hence divs, not a real chart lib.
function barChart(rows, opt = {}) {
  const n = opt.n || 8;
  const title = opt.title || 'Top deals by blended score';
  const top = rows.slice().sort((a, b) => b.composite - a.composite).slice(0, n);
  if (!top.length) return '';
  const max = Math.max(100, ...top.map(p => p.composite || 0));
  const bars = top.map(p => {
    const pct = Math.max(2, Math.round((p.composite / max) * 100));
    const col = econColor(p);
    const f = p.finance || {};
    const coc = f.coc != null ? `${f.coc}% CoC` : 'CoC n/a';
    const label = `${esc(p.address)}, ${esc(p.city)}`;
    return `<tr>
      <td style="padding:3px 8px 3px 0;font-size:12px;color:#333;white-space:nowrap;max-width:230px;overflow:hidden;text-overflow:ellipsis">${label}</td>
      <td style="padding:3px 0;width:100%">
        <div style="background:#eee;border-radius:3px;width:100%">
          <div style="background:${col};width:${pct}%;height:15px;border-radius:3px"></div>
        </div>
      </td>
      <td style="padding:3px 0 3px 8px;font-size:12px;font-weight:700;color:${col};white-space:nowrap">${p.composite} <span style="font-weight:400;color:#777">· ${coc}</span></td>
    </tr>`;
  }).join('\n');
  return `<h3 style="margin:18px 0 4px;font-size:15px">📊 ${esc(title)}</h3>
  <div style="font-size:11px;color:#888;margin:0 0 4px">Bar = blended score (max ${max}). <span style="color:#0a7d2c">green</span>=positive leverage · <span style="color:#a15c00">amber</span>=in-budget, flat/unknown · <span style="color:#b00020">red</span>=over budget.</div>
  <table style="border-collapse:collapse;width:100%;table-layout:fixed">${bars}</table>`;
}

// ── rich "table of info" ─────────────────────────────────────────────────────
// Adds the derived columns ($/unit, $/sqft, GRM) on top of the core economics so the
// whole tracked set is scannable at a glance — the "make table of info" ask.
function dataTable(rows, opt = {}) {
  const n = opt.n || rows.length;
  const title = opt.title || 'All tracked listings';
  const sorted = rows.slice().sort((a, b) => b.composite - a.composite).slice(0, n);
  if (!sorted.length) return '';
  const th = (t, align = 'right') => `<th style="padding:6px 7px;text-align:${align};border-bottom:2px solid #ddd;white-space:nowrap">${t}</th>`;
  const td = (v, align = 'right', extra = '') => `<td style="padding:5px 7px;text-align:${align};${extra}">${v}</td>`;
  const head = `<tr style="background:#f4f4f4">
    ${th('#', 'center')}${th('Property', 'left')}${th('Price')}${th('$/Unit')}${th('$/SqFt')}${th('Cap')}${th('CoC')}${th('DSCR')}${th('NOI')}${th('GRM')}${th('Cash to close')}${th('Score', 'center')}</tr>`;
  const body = sorted.map((p, i) => {
    const f = p.finance || {};
    const d = derived(p);
    const col = econColor(p);
    const cap = p.cap_rate != null ? p.cap_rate + '%' : '—';
    const prop = `<a href="${esc(p.source)}" style="color:#1a4f8a;text-decoration:none">${esc(p.address)}</a><br><span style="color:#777;font-size:11px">${esc(p.city)} · ${esc(p.type)} · ${p.units}u${d.age != null ? ` · ${d.age}yr` : ''}</span>`;
    return `<tr style="border-bottom:1px solid #eee">
      ${td(i + 1, 'center', 'font-weight:700;color:#555')}
      ${td(prop, 'left')}
      ${td(fmt(p.price))}
      ${td(fmt(d.perUnit))}
      ${td(d.perSqft != null ? fmt(d.perSqft) : '—')}
      ${td(cap)}
      ${td(f.coc != null ? `<b style="color:${f.coc > 0 ? '#0a7d2c' : '#b00020'}">${f.coc}%</b>` : '—')}
      ${td(f.dscr != null ? f.dscr : '—')}
      ${td(f.noi ? fmt(f.noi) : 'n/d')}
      ${td(d.grm != null ? d.grm + '×' : '—')}
      ${td(fmt(f.cashNeeded) + `<br><span style="font-size:10px;color:${f.affordable ? '#178a3a' : '#b00'}">${f.affordable ? 'in budget' : 'over'}</span>`)}
      ${td(`<b style="color:${col}">${p.composite}</b>`, 'center')}
    </tr>`;
  }).join('\n');
  return `<h3 style="margin:20px 0 4px;font-size:15px">🧮 ${esc(title)} (${sorted.length})</h3>
  <div style="overflow-x:auto"><table style="border-collapse:collapse;width:100%;font-size:12px;border:1px solid #e2e2e2">
  ${head}
  ${body}
  </table></div>
  <div style="font-size:11px;color:#888;margin:4px 0">$/Unit = price ÷ doors · GRM = price ÷ gross annual rent (lower is cheaper) · $/SqFt blank where the source omits square footage — see suggested data points below.</div>`;
}

// ── suggested additional data points (the "all zillow info they use" ask) ─────
// Honest split: what we can compute today vs. what needs a richer source (Zillow/Redfin/
// LA RSO/county assessor). Scraping Zillow is a separate, gated effort — this is the spec
// for it, not an action taken here.
function moreDataPoints() {
  const have = [
    ['$/Unit (price per door)', 'computed now from price ÷ units — added to the table above'],
    ['GRM (gross rent multiplier)', 'computed when rent roll / gross is known — added above'],
    ['CoC, DSCR, NOI, cash-to-close', 'already modeled at 25% down via finance.js'],
  ];
  const need = [
    ['Square footage & $/SqFt', 'Zillow/Redfin/Crexi detail — most Crexi rows omit it (col is blank now)'],
    ['Year built / effective age', 'Zillow + county assessor — drives reno cost & insurability'],
    ['Beds/baths & unit mix', 'Zillow — needed for rent-comp and value-add modeling'],
    ['Days on market (DOM)', 'Zillow/Crexi — momentum & negotiating-leverage signal'],
    ['Price history / price cuts', 'Zillow — flags motivated sellers (the Canby cut caught one)'],
    ['Last sold price & date', 'Zillow/county — basis, hold period, appreciation'],
    ['Zestimate + Rent Zestimate', 'Zillow AVM — independent check on broker cap & pro-forma rent'],
    ['Annual property tax', 'county assessor — a real NOI line most broker caps ignore'],
    ['Rent control / RSO status', 'LA Housing Dept RSO list — currently just "verify" on every row'],
    ['Lot size & zoning (ADU/entitlement)', 'county/Zillow — value-add & development upside'],
    ['Walk/Transit Score + comps', 'Zillow/Walk Score — submarket desirability & $/unit benchmark'],
  ];
  const li = ([k, v]) => `<li style="margin:2px 0"><b>${esc(k)}</b> — <span style="color:#555">${esc(v)}</span></li>`;
  return `<h3 style="margin:20px 0 4px;font-size:15px">➕ Suggested additional data points</h3>
  <div style="font-size:13px;color:#222">
    <div style="margin:4px 0;color:#0a7d2c;font-weight:600">Now live in this report:</div>
    <ul style="margin:2px 0 8px;padding-left:18px;font-size:12px">${have.map(li).join('')}</ul>
    <div style="margin:4px 0;color:#a15c00;font-weight:600">Next — Zillow/county fields to add to the scraper (separate build, needs sign-off):</div>
    <ul style="margin:2px 0;padding-left:18px;font-size:12px">${need.map(li).join('')}</ul>
  </div>`;
}

module.exports = { derived, barChart, dataTable, moreDataPoints, econColor };