`scripts/export-snapshot.js`

44 lines
'use strict';
// Export CRUnifiedDB → data/snapshot.json (deploy artifact). Serves the live site with zero
// load on the shared prod Postgres. Includes the CRCP full-CRE base + sublease inventory + brokers.
const fs = require('fs');
const path = require('path');
const { Pool } = require('pg');
const pool = new Pool({ host: process.env.PGHOST || '/tmp', database: process.env.PGDATABASE || 'crunified' });
const q = (s, p) => pool.query(s, p).then(r => r.rows);
const OUT = path.join(__dirname, '..', 'data', 'snapshot.json');

(async () => {
  const sponsors = await q(`
    SELECT s.*, COUNT(l.id) FILTER (WHERE l.status='active')::int AS listing_count
    FROM sponsors s LEFT JOIN listings l ON l.sponsor_id=s.id GROUP BY s.id ORDER BY s.role, s.name`);
  const listings = await q(`
    SELECT l.id, l.listing_kind, l.is_sublease, l.title, l.address, l.city, l.state, l.zip, l.lat, l.lng,
           l.space_type, l.asset_class, l.size_sf, l.units, l.cap_rate, l.year_built, l.price_amount, l.price_unit,
           l.term, l.source_url, l.image_url, l.source_label,
           s.slug AS sponsor_slug, s.name AS sponsor_name, s.logo_path
    FROM listings l LEFT JOIN sponsors s ON s.id=l.sponsor_id WHERE l.status IN ('active','Active') OR l.status IS NULL
    ORDER BY l.is_sublease DESC, l.id`);
  const brokers = await q(`SELECT id, name, firm, agent_type, phone, email, website, linkedin, office_addr, total_assets, specialties, state, profile_url, created_at FROM brokers ORDER BY total_assets DESC NULLS LAST, name`);
  const runs = await q(`SELECT s.slug, cr.status, cr.finished_at, cr.listings_found FROM crawl_runs cr JOIN sponsors s ON s.id=cr.sponsor_id ORDER BY cr.started_at DESC LIMIT 20`);

  const cnt = (arr, key) => arr.reduce((m, x) => { const k = x[key] || '—'; m[k] = (m[k] || 0) + 1; return m; }, {});
  const snapshot = {
    built_at: new Date().toISOString(),
    sponsors, brokers, listings, recent_runs: runs,
    map_points: listings.filter(l => l.lat != null && l.lng != null).map(l => ({
      id: l.id, title: l.title, address: l.address, city: l.city, lat: l.lat, lng: l.lng, listing_kind: l.listing_kind,
      space_type: l.space_type, asset_class: l.asset_class, size_sf: l.size_sf, price_amount: l.price_amount,
      price_unit: l.price_unit, cap_rate: l.cap_rate, source_url: l.source_url, sponsor_slug: l.sponsor_slug, sponsor_name: l.sponsor_name })),
    stats: {
      listings: listings.length, sponsors: sponsors.length, brokers: brokers.length,
      subleases: listings.filter(l => l.is_sublease).length,
      by_kind: Object.entries(cnt(listings, 'listing_kind')).map(([k, n]) => ({ kind: k, n })).sort((a, b) => b.n - a.n),
      by_type: Object.entries(cnt(listings.filter(l => l.space_type), 'space_type')).map(([space_type, n]) => ({ space_type, n })).sort((a, b) => b.n - a.n),
    },
  };
  fs.writeFileSync(OUT, JSON.stringify(snapshot));
  console.log(`snapshot → ${(fs.statSync(OUT).size / 1e6).toFixed(1)}MB · ${sponsors.length} sponsors, ${brokers.length} brokers, ${listings.length} listings, ${snapshot.map_points.length} map points`);
  await pool.end();
})();