`scripts/condos-report.js`

136 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) || []);

// Overlay the persistent per-listing broker cache (data/condo-brokers.json) by id, so the email stays
// enriched even after a weekly refresh rewrites condos-redfin.json. Same source the viewer overlays.
try {
  const cb = JSON.parse(fs.readFileSync(path.join(ROOT, 'data', 'condo-brokers.json'), 'utf8'));
  for (const c of all) {
    const b = cb[c.id]; if (!b || !b.broker_name) continue;
    c.broker_name = b.broker_name;
    if (Array.isArray(b.agents) && b.agents.length) c.agents = b.agents;   // ALL license numbers on the listing
    if (b.firm_name) c.firm_name = b.firm_name;
    if (b.broker_dre) { c.broker_dre = b.broker_dre; c.dre_url = (b.dre && b.dre.dre_url) || `https://www2.dre.ca.gov/PublicASP/pplinfo.asp?License_id=${b.broker_dre}`; }
    if (b.agent_phone) c.agent_phone = b.agent_phone;
    if (b.office_phone) c.office_phone = b.office_phone;
    if (b.dre) { c.broker_dre_expiration = b.dre.dre_expiration; c.responsible_broker = b.dre.responsible_broker || c.responsible_broker; }
  }
} catch (_) { /* no broker cache yet — brokerLine falls back to a Redfin deep link */ }

// Unwarrantable = fha_expired only.
const expired = all.filter(c => c.warrantable_status === 'fha_expired');
// Date order (Steve 2026-08-03): newest-listed first — fewest days on market — so the freshest
// unwarrantable plays lead. DOM-less listings sort last; tie-break by lowest price.
const dom = c => (c.days_on_market != null && c.days_on_market !== '') ? +c.days_on_market : Infinity;
expired.sort((a, b) => dom(a) - dom(b) || (a.price || Infinity) - (b.price || Infinity));

const today = process.env.REPORT_DATE || new Date().toISOString().slice(0, 10);
const fmt = n => n == null ? '—' : '$' + Math.round(n).toLocaleString();
const esc = s => String(s == null ? '' : s).replace(/&/g, '&amp;').replace(/</g, '&lt;').replace(/>/g, '&gt;').replace(/"/g, '&quot;');

// 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>`;

// Per-listing broker/firm/all-info line. The DRE# hyperlinks to its full CA DRE record
// ("using dre#, always hook into the full list"). Falls back to a Redfin deep link when a
// listing's broker couldn't be captured, so every listing still routes somewhere.
const brokerLine = (c) => {
  const aph = c.agent_phone ? ` · 📱 <a href="tel:${String(c.agent_phone).replace(/[^0-9]/g,'')}">${esc(c.agent_phone)}</a>` : '';
  const oph = (c.office_phone && c.office_phone !== c.agent_phone) ? ` · ☎ ${esc(c.office_phone)}` : '';
  // Record ALL license numbers on the listing: one row per agent (primary + any co-listing agent),
  // each DRE# hyperlinked to its full CA DRE record. Falls back to the single primary broker until
  // the multi-agent re-scrape populates c.agents.
  const agents = (Array.isArray(c.agents) && c.agents.length)
    ? c.agents
    : ((c.broker_name || c.broker_dre) ? [{ name: c.broker_name, firm: c.firm_name, dre: c.broker_dre, dre_record: c.dre }] : []);
  if (agents.length) {
    const rows = agents.map((a, i) => {
      const dreUrl = (a.dre_record && a.dre_record.dre_url) || (a.dre ? `https://www2.dre.ca.gov/PublicASP/pplinfo.asp?License_id=${a.dre}` : '');
      const dre = a.dre ? ` · DRE# <a href="${dreUrl}">${esc(a.dre)}</a>` : '';
      const exp = (a.dre_record && a.dre_record.dre_expiration) ? ` · exp ${esc(a.dre_record.dre_expiration)}`
        : (i === 0 && c.broker_dre_expiration ? ` · exp ${esc(c.broker_dre_expiration)}` : '');
      const role = i === 0 ? '' : ' <span style="color:#888">(co-agent)</span>';
      return `${esc(a.name || '')}${a.firm ? (a.name ? ' · ' : '') + esc(a.firm) : ''}${dre}${exp}${role}`;
    }).join('<br>&nbsp;&nbsp;&nbsp;&nbsp;');
    return `<div style="font-size:12px;color:#1a5;margin:3px 0"><b>🧑‍💼 Listed by:</b> ${rows}${aph}${oph}</div>`;
  }
  return `<div style="font-size:12px;color:#888;margin:3px 0"><b>🧑‍💼 Listed by:</b> <a href="${c.source}">view listing broker on Redfin ↗</a></div>`;
};

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` : ''}${c.days_on_market != null ? ` · 🗓 ${c.days_on_market}d on mkt` : ''}</div>
  <div style="font-size:12px;color:#a15c00;margin:3px 0"><b>Why unwarrantable:</b> ${whyNote(c)}</div>
  ${brokerLine(c)}
  <div style="font-size:11px;color:#888">${(c.warrant_signals && c.warrant_signals.label) || 'FHA/VA-approval-based proxy, NOT lender-verified.'}</div>
  <div style="font-size:12px;color:#333;margin:6px 0 2px"><b>Notes:</b> <span style="display:inline-block;border-bottom:1px solid #bbb;min-width:75%;height:14px">&nbsp;</span></div>
</div>`;

// Co-branded broker / lender block (Steve 2026-08-03). Frank Thomas is the loan officer these
// FHA-lapsed plays are FOR — a DRE-licensed broker + NMLS loan officer at Arcstone Financial.
// Details are his own signature-block contact info. Every scheduled run now carries it.
const brokerBlock = `<div style="margin-top:22px;border:1px solid #e2e2e2;border-radius:6px;padding:16px 18px;background:#faf9f7">
  <div style="font-size:11px;letter-spacing:.08em;text-transform:uppercase;color:#a15c00;margin-bottom:6px">Financing on these plays — talk to a lender</div>
  <div style="font-size:16px;font-weight:700;color:#222">Frank Thomas</div>
  <div style="font-size:13px;color:#444">Arcstone Financial · Mortgage Banker</div>
  <div style="font-size:12px;color:#666;margin:2px 0 8px">NMLS# 344100 · DRE# 01706806 · Equal Housing Lender</div>
  <div style="font-size:13px;color:#333;line-height:1.7">
    📱 <a href="tel:+13238190828" style="color:#0a58ca;text-decoration:none">323-819-0828</a> (mobile) · Fax 323-694-6047<br>
    ✉️ <a href="mailto:frankt@arcstoneinc.com" style="color:#0a58ca;text-decoration:none">frankt@arcstoneinc.com</a><br>
    🌐 <a href="https://www.arcstoneinc.com" style="color:#0a58ca;text-decoration:none">www.arcstoneinc.com</a><br>
    📍 1917 Hillhurst Ave., Suite 203, Los Angeles, CA 90027
  </div>
  <div style="font-size:13px;margin-top:10px">
    <a href="https://www.arcstoneinc.com/loan-app/?siteId=2943098668&amp;lar=frankt&amp;workFlowId=76622" style="display:inline-block;background:#a15c00;color:#fff;text-decoration:none;padding:7px 14px;border-radius:4px;font-weight:600">Apply Now ↗</a>
    <a href="https://arcstoneinc.sharefile.com/share/getinfo/rd4022c8c6d24a469" style="display:inline-block;margin-left:8px;color:#0a58ca;text-decoration:none;padding:7px 4px">🔒 Secure Document Upload ↗</a>
  </div>
  <div style="font-size:12px;margin-top:8px;color:#666">
    <a href="https://www.facebook.com/Arcstoneloans" style="color:#0a58ca;text-decoration:none">Facebook</a> ·
    <a href="https://www.instagram.com/arcstonefinancial/" style="color:#0a58ca;text-decoration:none">Instagram</a>
  </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. Ordered newest-listed first (fewest days on market).</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')}
${brokerBlock}
<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);