`scripts/import-crcp.js`

57 lines
'use strict';
// Import CRCP real data → CRUnifiedDB: 2,537 brokers + 3,105 for-sale listings (honestly typed
// as listing_kind='sale', is_sublease=false), geo-matched from map-points by address. $0.
const fs = require('fs');
const { Pool } = require('pg');
const pool = new Pool({ host: '/tmp', database: 'crunified' });
const CR = process.env.HOME + '/Projects/commercialrealestate/data/';
const j = (f) => JSON.parse(fs.readFileSync(CR + f, 'utf8'));
const norm = (s) => (s || '').toLowerCase().replace(/[^a-z0-9]/g, '');
const SPACE = { 'Office': 'office', 'Retail': 'retail', 'Retail/NNN': 'retail', 'Mixed-use': 'flex', 'Multifamily': 'multifamily' };

(async () => {
  const brokers = j('brokers-snapshot.json').brokers;
  const listings = j('listings.json').listings;
  const points = j('map-points.json').points;

  // address+city → {lat,lng} from map-points (any kind) for geo matching
  const geo = new Map();
  for (const p of points) if (p.lat && p.lng) { const k = norm(p.address) + '|' + norm(p.city); if (!geo.has(k)) geo.set(k, { lat: p.lat, lng: p.lng }); }

  // --- brokers ---
  let b = 0;
  for (const r of brokers) {
    await pool.query(`INSERT INTO brokers (id,name,firm,agent_type,phone,email,website,linkedin,office_addr,total_assets,specialties)
      VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11) ON CONFLICT (id) DO UPDATE SET
      name=EXCLUDED.name, firm=EXCLUDED.firm, phone=EXCLUDED.phone, email=EXCLUDED.email, website=EXCLUDED.website`,
      [r.id, r.name, r.firm, r.agent_type, r.phone, r.email, r.website, r.linkedin, r.office_addr, r.total_assets,
       Array.isArray(r.specialties) ? r.specialties.join(', ') : r.specialties]);
    b++;
  }

  // --- listings (for-sale base) ---
  let li = 0, withGeo = 0;
  for (const r of listings) {
    const g = geo.get(norm(r.address) + '|' + norm(r.city));
    if (g) withGeo++;
    await pool.query(`INSERT INTO listings
      (source_label, external_id, listing_kind, is_sublease, title, address, city, state, zip, lat, lng,
       space_type, asset_class, size_sf, units, cap_rate, year_built, price_amount, price_unit, status, source_url, raw, last_seen)
      VALUES ('crcp',$1,'sale',false,$2,$3,$4,'CA',$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,'sale price',$15,$16,$17, now())
      ON CONFLICT (source_label, external_id) WHERE source_label IS NOT NULL DO UPDATE SET price_amount=EXCLUDED.price_amount, cap_rate=EXCLUDED.cap_rate,
        lat=COALESCE(EXCLUDED.lat, listings.lat), lng=COALESCE(EXCLUDED.lng, listings.lng), last_seen=now()`,
      [String(r.id), (r.type ? r.type + ' — ' : '') + r.address, r.address, r.city, r.zip,
       g ? g.lat : null, g ? g.lng : null, SPACE[r.type] || 'other', r.type, r.sqft || null, r.units || null,
       r.cap_rate || null, r.year_built || null, r.price || null,
       (r.status || 'Active'), r.source, JSON.stringify({ cap_rate: r.cap_rate, upside: r.upside_note, rent_control: r.rent_control })]);
    li++;
  }

  const [{ n: total }] = (await pool.query(`SELECT COUNT(*)::int n FROM listings`)).rows;
  const [{ n: subs }] = (await pool.query(`SELECT COUNT(*)::int n FROM listings WHERE is_sublease`)).rows;
  console.log(`brokers: ${b} imported`);
  console.log(`CRCP listings: ${li} imported (${withGeo} geo-matched from map-points)`);
  console.log(`CRUnifiedDB now: ${total} listings total (${subs} sublease, ${total - subs} sale)`);
  await pool.end();
})();