`scripts/export-to-xlsx.js`

56 lines
// export-to-xlsx.js — write the full CRE dataset to a local multi-tab .xlsx (no Drive API / gcloud
// needed). Steve uploads it to Google Drive → it becomes an editable Google Sheet he owns.
// Output: /tmp/la-cre-data.xlsx. $0 local. Run: node scripts/export-to-xlsx.js
'use strict';
const XLSX = require('xlsx');
const { Pool } = require('pg');
const fs = require('fs');
const path = require('path');
const pool = new Pool({ host: '/tmp', port: 5432, database: 'cre', user: process.env.USER || 'stevestudio2' });
const ROOT = path.join(__dirname, '..');
const OUT = process.env.OUT || '/tmp/la-cre-data.xlsx';
const d = v => v ? String(v).slice(0, 10) : '';

(async () => {
  const q = s => pool.query(s).then(r => r.rows);
  const webCol = (await pool.query(`SELECT 1 FROM information_schema.columns WHERE table_name='broker' AND column_name='website'`)).rowCount ? ', b.website' : ', NULL website';

  const commercial = await q(`SELECT b.name "Broker", f.name "Firm",
      (SELECT count(*) FROM broker_listing bl WHERE bl.broker_id=b.id)::int "Listings", b.total_assets "Total Crexi Book",
      b.phone "Phone", b.email "Email" ${webCol.replace('b.website', 'b.website "Website"').replace('NULL website', 'NULL "Website"')}
      FROM broker b LEFT JOIN firm f ON f.id=b.firm_id WHERE b.agent_type='commercial' OR b.agent_type IS NULL ORDER BY 3 DESC NULLS LAST`);
  const agents = await q(`SELECT b.name "Agent", f.name "Firm",
      (SELECT count(*) FROM broker_condo bc WHERE bc.broker_id=b.id)::int "Condos", b.phone "Phone", b.email "Email",
      ${webCol.includes('b.website') ? 'b.website' : 'NULL'} "Website", b.license "License"
      FROM broker b LEFT JOIN firm f ON f.id=b.firm_id WHERE b.agent_type='residential' ORDER BY 3 DESC NULLS LAST`).catch(() => []);
  const firms = await q(`SELECT f.name "Firm", count(DISTINCT bl.listing_id)::int "Listings", count(DISTINCT b.id)::int "Brokers"
      FROM firm f JOIN broker b ON b.firm_id=f.id JOIN broker_listing bl ON bl.broker_id=b.id GROUP BY f.name ORDER BY 2 DESC`);
  const sales = await q(`SELECT address "Address", city "City", zip "ZIP", sold_price "Sold Price", sold_date "Sold Date",
      beds "Beds", baths "Baths", sqft "SqFt" FROM closed_sale WHERE sold_price>0 ORDER BY sold_date DESC NULLS LAST`).catch(() => []);
  sales.forEach(s => s['Sold Date'] = d(s['Sold Date']));

  let condos = [];
  try {
    condos = JSON.parse(fs.readFileSync(path.join(ROOT, 'data', 'fha-approved-condos.json'), 'utf8')).condos
      .filter(c => c.warrant_signal === 'fha_approved')
      .map(c => ({ Project: c.project_name, City: c.city, ZIP: c.zip, 'Status (proxy)': 'FHA-approved (proxy, not lender-verified)', Expiration: c.expiration_date }));
  } catch (_) {}

  const wb = XLSX.utils.book_new();
  const add = (name, rows) => XLSX.utils.book_append_sheet(wb, XLSX.utils.json_to_sheet(rows.length ? rows : [{ note: 'no rows' }]), name.slice(0, 31));
  add('Commercial Brokers', commercial);
  add('Residential Agents', agents);
  add('Firms', firms);
  add('Warrantable Condos (FHA)', condos);
  add('Closed Sales (Redfin ~5yr)', sales);
  XLSX.writeFile(wb, OUT);

  console.log(`wrote ${OUT}`);
  console.log(`  Commercial Brokers: ${commercial.length}`);
  console.log(`  Residential Agents: ${agents.length}`);
  console.log(`  Firms: ${firms.length}`);
  console.log(`  Warrantable Condos: ${condos.length}`);
  console.log(`  Closed Sales: ${sales.length}`);
  await pool.end();
})().catch(e => { console.error('ERR', e.message); process.exit(1); });