`scripts/ingest-gov-ct.js`

49 lines
#!/usr/bin/env node
/*
 * ingest-gov-ct.js — load Greenwich, CT licensed real-estate brokers + salespersons from the
 * CONNECTICUT open-data API into gov_licensed_agent. $0, authoritative.
 *
 * Source: CT Dept. of Consumer Protection eLicense via data.ct.gov
 *   Brokers      fwpc-pgqj   ·   Salespersons  7y8e-cawe   (SODA API)
 * Usage: node scripts/ingest-gov-ct.js
 */
'use strict';
const { pool } = require('./db/brokers-db');
const DATASETS = [
  { id: 'fwpc-pgqj', type: 'Broker' },
  { id: '7y8e-cawe', type: 'Salesperson' },
];
const METRO = 'greenwich', CITY = 'GREENWICH';

async function main() {
  let loaded = 0;
  for (const ds of DATASETS) {
    const where = `upper(city)='${CITY}' AND active='1'`;
    const url = `https://data.ct.gov/resource/${ds.id}.json?%24where=${encodeURIComponent(where)}&%24limit=5000`;
    const r = await fetch(url, { headers: { 'User-Agent': 'crcp-gov-ingest' } });
    if (!r.ok) { console.error(`${ds.type}: SODA ${r.status}`); continue; }
    const rows = await r.json();
    const seen = new Set(); const vals = []; const ph = [];
    for (const x of rows) {
      const lic = (x.fullcredentialcode || x.credentialnumber || '').trim();
      const name = (x.name || '').trim();
      const firm = (x.dba || '').trim();
      const key = lic + '|' + firm; if (seen.has(key)) continue; seen.add(key);
      const b = vals.length;
      ph.push(`('CT','${METRO}',$${b+1},$${b+2},$${b+3},$${b+4},$${b+5},NULL,$${b+6},NULL,NULL,'ct-dcp')`);
      vals.push(name, firm, lic, (x.credential || ds.type).trim(), (x.city || '').trim(), (x.zip || '').trim());
    }
    if (ph.length) {
      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, city=EXCLUDED.city, fetched_at=now()`, vals);
      loaded += ph.length;
    }
    console.log(`  ${ds.type.padEnd(12)} ${ph.length}`);
  }
  console.log(`✔ greenwich loaded ${loaded}. source: CT DCP eLicense. cost: $0`);
  await pool.end();
}
if (require.main === module) main().catch(e => { console.error(e); process.exit(1); });