← back to Realestate Flyers
scripts/flyer-from-deal.mjs
123 lines
#!/usr/bin/env node
/**
* flyer-from-deal.mjs — RE flyer aggregation, increment 1b (TK-10708, re-flyers)
*
* Cody-gate top fix: PROVE the template->data path. Renders ONE real property-spotlight
* flyer from a usre recent_commercial_deals row, using the RENTV brand token system
* (lifted from rentv-bloom-flyers concept-1: palette + @page letter + print CSS).
*
* Compliance rails (re-props skill): PUBLIC-record facts only; NO re-hosted third-party
* photos/descriptions (the flyer carries a link-OUT block, not ripped listing assets);
* loan-officer angle for Frank. Local HTML only — renders NOTHING to a public host.
*
* Usage: node scripts/flyer-from-deal.mjs [--top] (default: highest-price usre deal)
* $0, read-only against the DB, writes one HTML file under public/flyers/. Idempotent.
* Undo: git revert the commit / delete the generated html.
*/
import { writeFileSync, mkdirSync } from 'node:fs';
import { join, dirname } from 'node:path';
import { fileURLToPath } from 'node:url';
import { execSync } from 'node:child_process';
const __dirname = dirname(fileURLToPath(import.meta.url));
const ROOT = join(__dirname, '..');
const OUTDIR = join(ROOT, 'public', 'flyers');
// --- pull one real deal row from usre ---------------------------------------
function topDeal() {
const sql = `SELECT json_build_object('sale_date',sale_date,'sale_price',sale_price,'ctype',ctype,'address',address,'city',city,'county_name',county_name,'sqft',sqft,'year_built',year_built,'doc_number',doc_number) FROM recent_commercial_deals ORDER BY sale_price DESC NULLS LAST LIMIT 1;`;
const raw = execSync(`psql -h /tmp usre -tAc "${sql.replace(/"/g, '\\"')}"`, { encoding: 'utf8', timeout: 8000 }).trim();
return JSON.parse(raw);
}
// --- loan-officer angle (Frank): rough acquisition-loan estimate ------------
function loanAngle(price) {
if (!price) return null;
const ltv = 0.6; // conservative CRE acquisition LTV
const loan = Math.round(price * ltv);
return { ltv, loan, refi_window: 'acquisition/perm debt likely placed at close — refi window ~5-7yr' };
}
const money = (n) => (n == null ? '—' : '$' + Number(n).toLocaleString('en-US'));
const titleCase = (s) => (s || '').replace(/\b\w/g, (c) => c.toUpperCase());
const esc = (s) => String(s ?? '').replace(/[&<>"]/g, (c) => ({ '&': '&', '<': '<', '>': '>', '"': '"' }[c]));
function render(deal) {
const loan = loanAngle(deal.sale_price);
const addr = titleCase(deal.address);
const listingSearch = `https://www.crexi.com/properties?searchTerm=${encodeURIComponent(deal.address + ' ' + (deal.city || ''))}`;
return `<!DOCTYPE html>
<html lang="en"><head>
<meta charset="UTF-8"><meta name="viewport" content="width=device-width,initial-scale=1">
<title>Property Spotlight — ${esc(addr)}, ${esc(deal.city)}</title>
<link rel="preconnect" href="https://fonts.googleapis.com">
<link href="https://fonts.googleapis.com/css2?family=Playfair+Display:wght@600;700;900&family=Inter:wght@300;400;500;600;700&display=swap" rel="stylesheet">
<style>
*,*::before,*::after{box-sizing:border-box;margin:0;padding:0}
:root{--blue:#16357A;--navy:#0E2350;--red:#D0202E;--gold:#E8A81C;--ink:#141414;--paper:#fff;--gray:#6B7280;--panel:#F4F6FB;--rule:#D9DFF0;--pw:816px;--ph:1056px}
html,body{width:var(--pw);min-height:var(--ph);background:#CBD5E0;font-family:'Inter',Helvetica,Arial,sans-serif;-webkit-print-color-adjust:exact;print-color-adjust:exact}
.page{width:var(--pw);height:var(--ph);background:var(--paper);position:relative;overflow:hidden;display:flex;flex-direction:column}
.top-bar{height:6px;background:linear-gradient(90deg,var(--navy),var(--blue) 40%,var(--gold))}
.head{padding:32px 44px 20px;display:flex;justify-content:space-between;align-items:flex-start}
.kicker{font-size:12px;letter-spacing:.18em;text-transform:uppercase;color:var(--gold);font-weight:700}
.brand{font-family:'Playfair Display',Georgia,serif;font-weight:900;font-size:22px;color:var(--blue)}
.brand small{display:block;font-family:Inter;font-weight:500;font-size:10px;letter-spacing:.06em;color:var(--gray);text-transform:uppercase}
h1{font-family:'Playfair Display',Georgia,serif;font-weight:700;font-size:40px;line-height:1.05;color:var(--ink);padding:0 44px}
.sub{padding:6px 44px 0;font-size:15px;color:var(--gray)}
.price-band{margin:26px 44px 0;background:var(--navy);color:#fff;border-radius:10px;padding:22px 28px;display:flex;justify-content:space-between;align-items:center}
.price-band .p{font-family:'Playfair Display',serif;font-weight:900;font-size:38px;color:var(--gold-lt,#F5C842)}
.price-band .lab{font-size:11px;letter-spacing:.16em;text-transform:uppercase;opacity:.85}
.facts{margin:26px 44px 0;display:grid;grid-template-columns:repeat(3,1fr);gap:14px}
.fact{background:var(--panel);border:1px solid var(--rule);border-radius:8px;padding:14px 16px}
.fact .k{font-size:10px;letter-spacing:.14em;text-transform:uppercase;color:var(--gray);font-weight:600}
.fact .v{font-size:18px;font-weight:700;color:var(--ink);margin-top:3px}
.loan{margin:26px 44px 0;border-left:4px solid var(--red);background:#FCF3F3;border-radius:0 8px 8px 0;padding:16px 20px}
.loan .t{font-size:11px;letter-spacing:.14em;text-transform:uppercase;color:var(--red);font-weight:700}
.loan .b{font-size:15px;color:var(--ink);margin-top:6px;line-height:1.5}
.linkout{margin:24px 44px 0;padding:16px 20px;border:1px dashed var(--rule);border-radius:8px}
.linkout .t{font-size:11px;letter-spacing:.14em;text-transform:uppercase;color:var(--blue);font-weight:700;margin-bottom:8px}
.linkout a{color:var(--blue);font-weight:600;text-decoration:none}
.foot{margin-top:auto;padding:20px 44px;border-top:1px solid var(--rule);font-size:11px;color:var(--gray);display:flex;justify-content:space-between}
@page{size:letter;margin:0}
@media print{body{background:#fff}}
</style></head>
<body><div class="page">
<div class="top-bar"></div>
<div class="head">
<div><div class="kicker">Property Spotlight · Closed Deal</div></div>
<div class="brand">RENTV<small>Cutting-edge CRE media coverage</small></div>
</div>
<h1>${esc(addr)}</h1>
<div class="sub">${esc(titleCase(deal.city))}, ${esc(deal.county_name)} · ${esc(titleCase(deal.ctype))}</div>
<div class="price-band">
<div><div class="lab">Recorded sale price</div><div class="p">${money(deal.sale_price)}</div></div>
<div style="text-align:right"><div class="lab">Sale date</div><div style="font-size:20px;font-weight:700">${esc(deal.sale_date)}</div></div>
</div>
<div class="facts">
<div class="fact"><div class="k">Property type</div><div class="v">${esc(titleCase(deal.ctype)) || '—'}</div></div>
<div class="fact"><div class="k">Building sqft</div><div class="v">${deal.sqft ? Number(deal.sqft).toLocaleString() : '—'}</div></div>
<div class="fact"><div class="k">Year built</div><div class="v">${esc(deal.year_built) || '—'}</div></div>
<div class="fact"><div class="k">County</div><div class="v">${esc(deal.county_name)}</div></div>
<div class="fact"><div class="k">Recorded doc #</div><div class="v">${esc(deal.doc_number) || '—'}</div></div>
<div class="fact"><div class="k">Source</div><div class="v">Public deed record</div></div>
</div>
${loan ? `<div class="loan"><div class="t">Loan-officer angle — for Frank</div><div class="b">Est. acquisition debt at ${(loan.ltv * 100)}% LTV ≈ <strong>${money(loan.loan)}</strong>. ${esc(loan.refi_window)}. A closed sale of this size is a live refinance/perm-debt lead.</div></div>` : ''}
<div class="linkout">
<div class="t">Verify & contact — link out, never re-hosted</div>
<div>🔗 Find the listing / broker: <a href="${esc(listingSearch)}">${esc(listingSearch)}</a></div>
<div style="margin-top:4px;color:var(--gray);font-size:12px">Deal facts sourced from public deed records. Broker/agent assets are linked, never copied (RENTV compliance rail).</div>
</div>
<div class="foot"><span>RENTV.com, Inc. · Real Estate News & Media</span><span>Generated by realestate-flyers · TK-10708</span></div>
</div></body></html>`;
}
// --- run --------------------------------------------------------------------
const deal = topDeal();
const slug = ('usre-' + (deal.address || 'deal') + '-' + (deal.city || '')).toLowerCase().replace(/[^a-z0-9]+/g, '-').replace(/^-|-$/g, '').slice(0, 60);
mkdirSync(OUTDIR, { recursive: true });
const outPath = join(OUTDIR, `${slug}.html`);
writeFileSync(outPath, render(deal));
console.log(`rendered property-spotlight flyer -> ${outPath.replace(process.env.HOME, '~')}`);
console.log(` deal: ${deal.address}, ${deal.city} — ${money(deal.sale_price)} (${deal.ctype})`);
console.log(` slug: ${slug}`);