`src/ingest/brokers/fl_dbpr.ts`

64 lines
/**
 * FL — DBPR free licensee extracts (www2.myfloridalicense.com/sto/file_download/extracts/,
 * refreshed ~daily, probed 2026-07-21). 14 regional files RE_rgn1..14.csv, headerless
 * DBPR standard layout (latin-1). Column map (0-based, verified against live rows):
 *  1 license-type group · 2 name · 3 dba · 4 rank ("SL Sales Associate", "BK Broker",
 *  "BL Broker Sales", "CQ RE Corp.", "PR RE Partnership", branch/instructor ranks)
 *  8 city · 9 state · 13 numeric license # · 14 primary status ("Current"/"Invol Inactive"…)
 *  15 secondary status ("Active"/"Inactive") · 19 prefixed license # (SL1234567)
 *  21 employer firm name · 22 employer numeric license #.
 * Firm source_id uses the NUMERIC license number so CQ firm rows and employer refs join.
 */
import { download } from '../run.ts';
import { parseCsvFile } from './csv.ts';
import type { BrokerRow, FetchedFile, StateAdapter } from './types.ts';

const BASE = 'https://www2.myfloridalicense.com/sto/file_download/extracts/';
const REGIONS = Array.from({ length: 14 }, (_, i) => `RE_rgn${i + 1}.csv`);

export const flDbpr: StateAdapter = {
  state: 'FL',
  source: 'fl_dbpr',
  url: BASE + 'RE_rgn1.csv',
  async fetch() {
    const out = [];
    for (const f of REGIONS) out.push(await download(BASE + f, `fl_dbpr_${f}`));
    return out;
  },
  *parse(files: FetchedFile[]): Generator<BrokerRow> {
    for (const file of files) {
      for (const r of parseCsvFile(file.path, 'latin1')) {
        if (r.length < 20) continue;
        const rank = (r[4] || '').trim();
        const prefix = rank.slice(0, 2).toUpperCase();
        const name = (r[2] || '').trim();
        const licNo = (r[19] || '').trim() || (r[13] || '').trim();
        if (!name || !licNo) continue;
        const status = [r[14], r[15]].map(s => (s || '').trim()).filter(Boolean).join(' / ') || null;
        const city = (r[8] || '').trim() || null;
        const st = (r[9] || '').trim().toUpperCase() || null;
        if (prefix === 'CQ' || prefix === 'PR') {
          yield {
            kind: 'firm', name, license_no: licNo,
            license_type: prefix === 'CQ' ? 'Real Estate Corporation' : 'Real Estate Partnership',
            license_status: status, city, state_code: st,
            // numeric license joins employer refs — engine keys firms on firm_license_no
            firm_license_no: (r[13] || '').trim() || null,
          };
        } else if (prefix === 'SL' || prefix === 'BK' || prefix === 'BL') {
          const empName = (r[21] || '').trim() || null;
          const empLic = (r[22] || '').trim() || null;
          yield {
            kind: 'broker', name, license_no: licNo,
            license_type: rank.slice(3).trim() || rank,
            license_status: status,
            firm_name: empName, firm_license_no: empName ? empLic : null,
            city, state_code: st,
          };
        }
        // branch offices / instructors / additional locations skipped by design
      }
    }
  },
};