`scripts/ingest-gov-fl-cities.js`

59 lines
#!/usr/bin/env node
/*
 * ingest-gov-fl-cities.js — load FL luxury CITIES (Palm Beach, Naples, Miami Beach) city-level
 * from the FL DBPR CSV into gov_licensed_agent, one metro per city. Same file/columns as
 * ingest-gov-fl.js but filtered by CITY (field 7) instead of county. Expects /tmp/fl_realestate.csv.
 */
'use strict';
const fs = require('fs');
const readline = require('readline');
const { pool } = require('./db/brokers-db');
const FILE = '/tmp/fl_realestate.csv';
const CITY_METRO = { 'PALM BEACH': 'palm-beach', 'NAPLES': 'naples', 'MIAMI BEACH': 'miami-beach' };

function parseLine(line) { const out = []; const re = /"((?:[^"]|"")*)"/g; let m; while ((m = re.exec(line))) out.push(m[1].replace(/""/g, '"')); return out; }
const flDate = s => { const M = { JAN:'01',FEB:'02',MAR:'03',APR:'04',MAY:'05',JUN:'06',JUL:'07',AUG:'08',SEP:'09',OCT:'10',NOV:'11',DEC:'12' };
  const m = /^(\d{2})-([A-Z]{3})-(\d{2})$/.exec((s||'').trim()); if (!m) return null; const yr = +m[3] > 50 ? '19'+m[3] : '20'+m[3]; return `${yr}-${M[m[2]]||'01'}-${m[1]}`; };

async function flush(batch) {
  if (!batch.length) return 0;
  const seen = new Set(); const vals = []; const ph = [];
  for (const r of batch) {
    const key = r.lic + '|' + r.firm; if (seen.has(key)) continue; seen.add(key);
    const b = vals.length;
    ph.push(`('FL',$${b+1},$${b+2},$${b+3},$${b+4},$${b+5},$${b+6},$${b+7},$${b+8},$${b+9},$${b+10},'fl-dbpr')`);
    vals.push(r.metro, r.name, r.firm, r.lic, r.type, r.city, r.addr, r.zip, r.county, r.exp);
  }
  if (!ph.length) return 0;
  await pool.query(
    `INSERT INTO gov_licensed_agent (state,metro,name,firm,license_number,license_type,city,addr,zip,county,expiration,source)
     VALUES ${ph.join(',')}
     ON CONFLICT (source,license_number,firm) DO UPDATE SET license_type=EXCLUDED.license_type, expiration=EXCLUDED.expiration, city=EXCLUDED.city, fetched_at=now()`, vals);
  return ph.length;
}

async function main() {
  if (!fs.existsSync(FILE)) { console.error(`missing ${FILE}`); process.exit(1); }
  const cityRe = new RegExp('"(' + Object.keys(CITY_METRO).join('|') + ')"', 'i');
  const rl = readline.createInterface({ input: fs.createReadStream(FILE), crlfDelay: Infinity });
  let loaded = 0, batch = [];
  for await (const line of rl) {
    if (!cityRe.test(line)) continue;
    const f = parseLine(line); if (f.length < 17) continue;
    const city = (f[7] || '').trim().toUpperCase();
    const metro = CITY_METRO[city]; if (!metro) continue;
    if (!/active/i.test((f[13] || '').trim())) continue;
    const rank = (f[3] || '').trim();
    if (!/(broker|sales)/i.test(rank) || /corp|officer|school|instructor/i.test(rank)) continue;
    batch.push({ metro, name: (f[1]||'').trim(), firm: (f[19]||'').trim(), lic: (f[11]||'').trim(), type: rank,
      city, addr: (f[4]||'').trim(), zip: (f[9]||'').trim(), county: (f[10]||'').trim(), exp: flDate(f[16]) });
    if (batch.length >= 500) { loaded += await flush(batch); batch = []; }
  }
  loaded += await flush(batch);
  const { rows } = await pool.query(`SELECT metro, count(*) n FROM gov_licensed_agent WHERE metro = ANY($1) GROUP BY metro ORDER BY n DESC`, [Object.values(CITY_METRO)]);
  rows.forEach(r => console.log(`  ${r.metro.padEnd(14)} ${r.n}`));
  console.log(`✔ FL cities loaded ${loaded}. source: FL DBPR. cost: $0`);
  await pool.end();
}
if (require.main === module) main().catch(e => { console.error(e); process.exit(1); });