`scripts/morning-report.js`

90 lines
// morning-report.js — build the daily "Top 10 SFV CRE Opportunities" email body from ranked.json.
// Prioritizes ACTIVE + affordable + verified deals by composite. Writes HTML to data/morning-report.html
// and prints it to stdout. Pure local, no deps. The 6am job re-runs analyze.js first, then this.
const fs = require('fs');
const path = require('path');
const ROOT = path.join(__dirname, '..');
const CREFin = require('../public/finance.js');
const EX = require('./report-extras.js'); // chart + table-of-info + suggested data points (2026-06-26)

const { meta, assumptions, ranked } = JSON.parse(fs.readFileSync(path.join(ROOT, 'data', 'ranked.json'), 'utf8'));
const A = assumptions || { budget: 400000, downPct: 25, ratePct: 6.75, amortYears: 30, closingPct: 2.5 };

// recompute at the canonical 25%-down scenario, then rank "opportunities": active + affordable first
const rows = ranked.map(p => {
  const f = CREFin.finance(p, A);
  const fs2 = CREFin.financeScore(p, f);
  return { ...p, finance: f, composite: CREFin.composite(p, f, fs2.score, p.qwenScore) };
});
const isActive = p => !/Under Contract|Pending|OUT OF BUDGET/i.test(p.status || '');
rows.sort((a, b) => {
  const aw = (isActive(a) ? 2 : 0) + (a.finance.affordable ? 1 : 0);
  const bw = (isActive(b) ? 2 : 0) + (b.finance.affordable ? 1 : 0);
  if (aw !== bw) return bw - aw;
  return b.composite - a.composite;
});
const top = rows.slice(0, 10);

const today = process.env.REPORT_DATE || new Date().toISOString().slice(0, 10);
const fmt = n => n == null ? '—' : '$' + Math.round(n).toLocaleString();
const row = (p, i) => {
  const f = p.finance;
  const ver = p.verified === true ? '✓ verified' : (p.cap_rate != null ? 'broker cap' : 'no income data');
  return `<tr>
    <td style="padding:6px 8px;font-weight:700">${i + 1}</td>
    <td style="padding:6px 8px"><a href="${p.source}">${p.address}</a><br><span style="color:#666;font-size:12px">${p.city}, CA · ${p.type} · ${p.units}u · built ${p.year_built || '—'}</span></td>
    <td style="padding:6px 8px;text-align:right">${fmt(p.price)}</td>
    <td style="padding:6px 8px;text-align:right">${p.cap_rate != null ? p.cap_rate + '%' : '—'}<br><span style="font-size:11px;color:#888">${ver}</span></td>
    <td style="padding:6px 8px;text-align:right">${f.coc != null ? f.coc + '%' : '—'}</td>
    <td style="padding:6px 8px;text-align:right">${f.dscr ?? '—'}</td>
    <td style="padding:6px 8px;text-align:right">${fmt(f.cashNeeded)}<br><span style="font-size:11px;color:${f.affordable ? '#178a3a' : '#b00'}">${f.affordable ? 'in budget' : 'over'}</span></td>
    <td style="padding:6px 8px;text-align:center;font-weight:700">${p.composite}</td>
  </tr>`;
};

const recap = (p, i) => {
  const f = p.finance;
  const ver = p.verified === true ? '✓ verified in-place' : (p.cap_rate != null ? (p.noi_seller ? 'seller/pro-forma' : 'broker cap') : 'no income data');
  const cap = p.cap_rate != null ? p.cap_rate + '%' + (p.cap_rate_projected ? ` (proj ${p.cap_rate_projected}%)` : '') : '—';
  const rr = p.rent_roll && p.rent_roll.length
    ? `<div style="font-size:12px;color:#444;margin:4px 0"><b>Rent roll:</b> ${p.rent_roll.map(u => `${u.type} $${u.rent}/mo${u.proforma ? ` (proj $${u.proforma})` : ''}${u.note ? ' — ' + u.note : ''}`).join(' · ')}</div>` : '';
  const risks = p.qwen && p.qwen.risks && p.qwen.risks.length
    ? `<div style="font-size:12px;color:#a15c00;margin:4px 0"><b>Risks:</b> ${p.qwen.risks.slice(0, 3).join('; ')}</div>` : '';
  return `<div style="border-top:1px solid #e2e2e2;padding:10px 0">
    <div style="font-size:14px"><b>#${i + 1}. <a href="${p.source}">${p.address}</a>, ${p.city}</b> &nbsp;<span style="color:#888;font-weight:400">score ${p.composite}</span></div>
    <div style="color:#666;font-size:12px">${p.type} · ${p.units} unit${p.units > 1 ? 's' : ''}${p.year_built ? ` · built ${p.year_built}` : ''} · ${fmt(p.price)} · cap ${cap} (${ver})</div>
    <div style="font-size:12px;color:#333;margin:3px 0">Cash to close <b>${fmt(f.cashNeeded)}</b> (${f.affordable ? 'in budget' : 'OVER budget'}) · CoC ${f.coc != null ? f.coc + '%' : 'n/a'} · DSCR ${f.dscr ?? 'n/a'} · est NOI ${f.noi ? fmt(f.noi) : 'n/d'}${f.grossYield ? ' · rent yield ' + f.grossYield + '% (rent÷price)' : ''}</div>
    ${p.qwen && p.qwen.thesis ? `<div style="font-size:13px;color:#222;margin:4px 0">${p.qwen.thesis}</div>` : ''}
    ${rr}${risks}
    <div style="font-size:12px;color:#555">${p.upside_note || ''}</div>
  </div>`;
};

const html = `<div style="font-family:-apple-system,Segoe UI,Arial,sans-serif;max-width:780px">
<h2 style="margin:0 0 2px">SFV Commercial Real Estate — Top 10 Opportunities</h2>
<div style="color:#666;font-size:13px">${today} · ${meta.market} · $${A.budget.toLocaleString()} down (${A.downPct}% / ${A.ratePct}% / ${A.amortYears}yr) · ${ranked.length} listings tracked</div>
<p style="font-size:13px;color:#444;margin:12px 0">Ranked by blended score (verified in-place economics + AI thesis), active &amp; in-budget deals first.
Cap rates are broker/MLS-stated — verify NOI in DD. Most SFV multifamily runs negative/breakeven leverage at today's rates; positive-leverage or seller-financed deals are the standouts.</p>
<table style="border-collapse:collapse;width:100%;font-size:13px;border:1px solid #ddd">
<tr style="background:#f4f4f4;text-align:left">
  <th style="padding:6px 8px">#</th><th style="padding:6px 8px">Property</th><th style="padding:6px 8px;text-align:right">Price</th>
  <th style="padding:6px 8px;text-align:right">Cap</th><th style="padding:6px 8px;text-align:right">CoC</th>
  <th style="padding:6px 8px;text-align:right">DSCR</th><th style="padding:6px 8px;text-align:right">Cash needed</th><th style="padding:6px 8px;text-align:center">Score</th>
</tr>
${top.map(row).join('\n')}
</table>
<h3 style="margin:18px 0 6px;font-size:15px">Detailed recap — each opportunity</h3>
${top.map(recap).join('\n')}

${EX.barChart(rows, { n: 10, title: 'Top SFV deals by blended score' })}

${EX.dataTable(rows, { title: 'Table of info — all tracked SFV listings' })}

${EX.moreDataPoints()}

<p style="color:#888;font-size:12px;margin-top:16px">Live viewer: http://127.0.0.1:9911 · generated by commercialrealestate/scripts/morning-report.js</p>
</div>`;

fs.writeFileSync(path.join(ROOT, 'data', 'morning-report.html'), html);
process.stdout.write(html);