`scripts/condos-report.js`

65 lines
// condos-report.js — build the "Unwarrantable Condos" email body from data/condos-redfin.json.
// Scope (Steve 2026-07-04): warrantable_status === 'fha_expired' ONLY — condos that WERE FHA-approved
// and whose approval has lapsed, i.e. genuinely lost financing eligibility = the clearest discount play.
// Deliberately EXCLUDES 'not_listed' (honest-labeling rule: not_listed = unknown, verify with lender,
// NOT confirmed unwarrantable). Pure local, no deps. Mirrors morning-report.js styling.
const fs = require('fs');
const path = require('path');
const ROOT = path.join(__dirname, '..');

const raw = JSON.parse(fs.readFileSync(path.join(ROOT, 'data', 'condos-redfin.json'), 'utf8'));
const all = Array.isArray(raw) ? raw : (raw.condos || raw.listings || raw.results || Object.values(raw).find(Array.isArray) || []);

// Unwarrantable = fha_expired only.
const expired = all.filter(c => c.warrantable_status === 'fha_expired');
// Rank: most accessible discount plays first (lowest price), tie-break lowest $/sqft.
const ppsf = c => (c.price && c.sqft) ? c.price / c.sqft : Infinity;
expired.sort((a, b) => (a.price || Infinity) - (b.price || Infinity) || ppsf(a) - ppsf(b));

const today = process.env.REPORT_DATE || new Date().toISOString().slice(0, 10);
const fmt = n => n == null ? '—' : '$' + Math.round(n).toLocaleString();

// Pull the honest HUD "why" note (project name + expiration date + concentration) from warrant_signals.
const whyNote = c => {
  const sig = (c.warrant_signals && c.warrant_signals.signals || []).find(s => s.kind === 'fha_list');
  return (sig && sig.note) || (c.warrant_source || 'FHA approval lapsed');
};

const row = (c, i) => `<tr>
  <td style="padding:6px 8px;font-weight:700">${i + 1}</td>
  <td style="padding:6px 8px"><a href="${c.source}">${c.address}</a><br><span style="color:#666;font-size:12px">${c.city}, CA · ${c.property_type || 'Condo'}${c.year_built ? ` · built ${c.year_built}` : ''}</span></td>
  <td style="padding:6px 8px;text-align:right">${fmt(c.price)}</td>
  <td style="padding:6px 8px;text-align:right">${c.sqft ? fmt(c.price / c.sqft) : '—'}</td>
  <td style="padding:6px 8px;text-align:center">${c.beds ?? '—'}/${c.baths ?? '—'}</td>
  <td style="padding:6px 8px;text-align:right">${c.hoa ? fmt(c.hoa) + '/mo' : '—'}</td>
  <td style="padding:6px 8px;font-size:11px;color:#a15c00">FHA lapsed</td>
</tr>`;

const recap = (c, i) => `<div style="border-top:1px solid #e2e2e2;padding:10px 0">
  <div style="font-size:14px"><b>#${i + 1}. <a href="${c.source}">${c.address}</a>, ${c.city}</b></div>
  <div style="color:#666;font-size:12px">${c.property_type || 'Condo'}${c.year_built ? ` · built ${c.year_built}` : ''} · ${fmt(c.price)}${c.sqft ? ` · ${fmt(c.price / c.sqft)}/sqft · ${c.sqft.toLocaleString()} sqft` : ''}${c.beds != null ? ` · ${c.beds}bd/${c.baths}ba` : ''}${c.hoa ? ` · HOA ${fmt(c.hoa)}/mo` : ''}</div>
  <div style="font-size:12px;color:#a15c00;margin:3px 0"><b>Why unwarrantable:</b> ${whyNote(c)}</div>
  <div style="font-size:11px;color:#888">${(c.warrant_signals && c.warrant_signals.label) || 'FHA/VA-approval-based proxy, NOT lender-verified.'}</div>
</div>`;

const html = `<div style="font-family:-apple-system,Segoe UI,Arial,sans-serif;max-width:780px">
<h2 style="margin:0 0 2px">Unwarrantable Condos — FHA-Lapsed Discount Plays</h2>
<div style="color:#666;font-size:13px">${today} · LA County · ${expired.length} condo${expired.length === 1 ? '' : 's'} with lapsed FHA approval · ${all.length} condos screened</div>
<p style="font-size:13px;color:#444;margin:12px 0">These condos were <b>previously FHA-approved</b> but the project's approval has <b>expired</b> — so FHA/VA (and often conventional) financing is unavailable until re-certified. That constrains the buyer pool to cash / portfolio lenders, which typically means a price discount and less competition. Ranked most-accessible first (lowest price).</p>
<p style="font-size:12px;color:#a15c00;margin:0 0 12px"><b>Honest labeling:</b> "unwarrantable" here is an <b>FHA-approval-based proxy</b> (HUD's public list), NOT a lender-verified Fannie/Freddie warrantability determination. Confirm project status + eligibility with a lender before acting. Excludes ${all.length - expired.length} condos that are FHA-approved or simply not on the list (unknown).</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">$/sqft</th><th style="padding:6px 8px;text-align:center">Bd/Ba</th>
  <th style="padding:6px 8px;text-align:right">HOA</th><th style="padding:6px 8px">Status</th>
</tr>
${expired.map(row).join('\n')}
</table>
<h3 style="margin:18px 0 6px;font-size:15px">Detailed recap — each condo</h3>
${expired.map(recap).join('\n')}
<p style="color:#888;font-size:12px;margin-top:16px">Live viewer: http://127.0.0.1:9911 · generated by commercialrealestate/scripts/condos-report.js</p>
</div>`;

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