`scripts/ingest-gov-co.js`

48 lines
#!/usr/bin/env node
/*
 * ingest-gov-co.js — load Aspen, Telluride, Vail licensed real-estate brokers from the
 * COLORADO open-data API into gov_licensed_agent. $0, authoritative. (Colorado issues a single
 * "Broker" real-estate license — no salesperson tier — so all agents are brokers.)
 *
 * Source: CO DORA / Division of Real Estate via data.colorado.gov dataset 4zse-6bnw
 *   SODA API https://data.colorado.gov/resource/4zse-6bnw.json
 * Usage: node scripts/ingest-gov-co.js
 */
'use strict';
const { pool } = require('./db/brokers-db');
const BASE = 'https://data.colorado.gov/resource/4zse-6bnw.json';
const CITY_METRO = { ASPEN: 'aspen', TELLURIDE: 'telluride', VAIL: 'vail' };
const coDate = s => { const m = /^(\d{2})\/(\d{2})\/(\d{4})$/.exec(s || ''); return m ? `${m[3]}-${m[1]}-${m[2]}` : null; };

async function main() {
  let loaded = 0;
  for (const [CITY, metro] of Object.entries(CITY_METRO)) {
    const where = `upper(city)='${CITY}' AND licensestatus='Active' AND licensetype like '%Broker%'`;
    const url = `${BASE}?%24where=${encodeURIComponent(where)}&%24limit=5000`;
    const r = await fetch(url, { headers: { 'User-Agent': 'crcp-gov-ingest' } });
    if (!r.ok) { console.error(`${metro}: 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.licenseprefix || '') + (x.licensenumber || '')).trim();
      const name = [x.firstname, x.middlename, x.lastname].filter(Boolean).join(' ').trim();
      const firm = (x.employer || '').trim();          // usually absent; kept for schema parity
      const key = lic + '|' + firm; if (seen.has(key)) continue; seen.add(key);
      const b = vals.length;
      ph.push(`('CO',$${b+1},$${b+2},$${b+3},$${b+4},$${b+5},$${b+6},NULL,$${b+7},NULL,$${b+8},'co-dora')`);
      vals.push(metro, name, firm, lic, (x.licensetype || '').trim(), (x.city || '').trim(), (x.zipcode || '').trim(), coDate(x.licenseexpirationdate));
    }
    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, expiration=EXCLUDED.expiration, city=EXCLUDED.city, fetched_at=now()`, vals);
      loaded += ph.length;
    }
    console.log(`  ${metro.padEnd(12)} ${ph.length}`);
  }
  console.log(`✔ CO loaded ${loaded}. source: CO DORA public data. cost: $0`);
  await pool.end();
}
if (require.main === module) main().catch(e => { console.error(e); process.exit(1); });