`scripts/fetch-ncua-credit-unions.js`

279 lines
#!/usr/bin/env node
// fetch-ncua-credit-unions.js — CRCP "Credit Unions" builder for the lending-intelligence layer.
//
// Pulls REAL public data only ($0, unauthenticated) from the official NCUA Quarterly Call Report
// bulk data file. Unlike FDIC, NCUA has no clean REST API — the canonical public route is the
// quarterly ZIP of pipe/comma-delimited Call Report tables published at:
//   https://ncua.gov/analysis/credit-union-corporate-call-report-data/quarterly-data
//   file: /files/publications/analysis/call-report-data-YYYY-MM.zip   (verified reachable 2026-07-31)
//
// From that one official ZIP we read three tables:
//   • "Credit Union Branch Information.txt" — every physical office, with PhysicalAddressCountyName2.
//         This is how we scope to Los Angeles County (STATE=CA, county="Los Angeles").
//   • FOICU.txt   — credit-union master (charter #, name, HQ city/state, year opened, MDI flag).
//   • FS220.txt   — financial statement (ACCT_010 = TOTAL ASSETS, ACCT_083 = number of current members).
//
// The deliverable (data/ncua-credit-unions.json) is a DIRECTORY OF CREDIT UNIONS with an office/branch
// in LA County — each row joined to its authoritative NCUA master facts + total-assets/members from the
// same quarter's Call Report, plus its LA-County branch count.
//
// Every row is source-cited: source="NCUA", source_url=official per-CU deep link on the NCUA
// Research-a-Credit-Union locator, identifier=NCUA charter #, retrieved_at=now. No personal data —
// institutions only. Idempotent + re-runnable. Logs real source URLs + row counts. $0, public, no key.
//
// If the bulk ZIP genuinely cannot be reached/parsed, writes an HONEST manual_review_only stub
// (meta explains why + deep-links the official lookup) with an EMPTY array — never invents data.
//
// Run: node scripts/fetch-ncua-credit-unions.js

const fs = require('fs');
const path = require('path');
const os = require('os');
const { execFileSync } = require('child_process');

const ROOT = path.join(__dirname, '..');
const OUT = path.join(ROOT, 'data', 'ncua-credit-unions.json');

// ---- real public source (NCUA Quarterly Call Report bulk data, verified reachable 2026-07-31, $0) ----
const FILES_BASE = 'https://ncua.gov/files/publications/analysis';
const HUMAN = 'https://ncua.gov/analysis/credit-union-corporate-call-report-data/quarterly-data';
// NCUA "Research a Credit Union" public locator — per-CU deep link by charter number.
const LOCATOR = 'https://mapping.ncua.gov/ResearchCreditUnion';
const STATE = 'CA';
const COUNTY = 'Los Angeles';

// Quarter candidates, newest-first — the current year's Q1/Q2/Q3/Q4 then the prior year's Q4.
// (The most recent published quarter wins; we probe until one downloads.)
function quarterCandidates() {
  const now = new Date();
  const y = now.getUTCFullYear();
  const q = ['12', '09', '06', '03'];
  const cands = [];
  for (const yr of [y, y - 1]) for (const m of q) cands.push(`${yr}-${m}`);
  return cands;
}

function log(...a) { console.log('[ncua-credit-unions]', ...a); }

// Download a URL to a local file via curl (-L follows redirects, hard timeout). Public, $0.
function download(url, dest) {
  execFileSync('curl', ['-sL', '--fail', '--max-time', '200', '-o', dest, url], {
    stdio: ['ignore', 'ignore', 'ignore'], maxBuffer: 8 * 1024 * 1024,
  });
}

// Minimal CSV parser (RFC-4180-ish: quoted fields, embedded commas, doubled quotes). The NCUA
// txt tables are comma-delimited with double-quoted string fields.
function parseCSV(text) {
  const rows = [];
  let row = [], field = '', inq = false;
  for (let i = 0; i < text.length; i++) {
    const c = text[i];
    if (inq) {
      if (c === '"') { if (text[i + 1] === '"') { field += '"'; i++; } else inq = false; }
      else field += c;
    } else {
      if (c === '"') inq = true;
      else if (c === ',') { row.push(field); field = ''; }
      else if (c === '\r') { /* skip */ }
      else if (c === '\n') { row.push(field); rows.push(row); row = []; field = ''; }
      else field += c;
    }
  }
  if (field.length || row.length) { row.push(field); rows.push(row); }
  return rows;
}

// Read a member of the downloaded ZIP to a string via the system `unzip -p` (present on macOS/Linux).
function unzipMember(zipPath, member) {
  return execFileSync('unzip', ['-p', zipPath, member], {
    encoding: 'utf8', maxBuffer: 256 * 1024 * 1024,
  });
}

function atomicWrite(file, obj) {
  fs.writeFileSync(file + '.tmp', JSON.stringify(obj));
  fs.renameSync(file + '.tmp', file);
}

// Parse an NCUA numeric that may be blank/"" — returns a Number or null.
function num(v) {
  if (v == null) return null;
  const s = String(v).trim();
  if (!s) return null;
  const n = Number(s);
  return Number.isFinite(n) ? n : null;
}

// The NCUA CYCLE_DATE / dates look like "3/31/2026 0:00:00" — keep the date part only.
function datePart(v) {
  if (!v) return null;
  const s = String(v).trim();
  const m = s.match(/^(\d{1,2}\/\d{1,2}\/\d{4})/);
  return m ? m[1] : (s || null);
}

// Write the honest empty stub (used only if the official bulk route can't be reached/parsed).
function writeStub(reason, retrieved_at, quarter) {
  const meta = {
    source: 'NCUA',
    source_url: HUMAN,
    source_home: HUMAN,
    access_method: 'manual_review_only',
    personal_data: false,
    scope: `Credit unions with a physical office in ${COUNTY} County, ${STATE}`,
    note: `Automated bulk route unavailable (${reason}). No data fabricated. `
        + `Look up credit unions manually at the official NCUA locator: ${LOCATOR} `
        + `(or download the quarterly Call Report ZIP at ${HUMAN}).`,
    quarter: quarter || null,
    count: 0,
    retrieved_at,
  };
  atomicWrite(OUT, { meta, credit_unions: [] });
  log('WROTE MANUAL-REVIEW STUB (empty array):', reason);
}

function main() {
  const retrieved_at = new Date().toISOString();
  const tmp = path.join(os.tmpdir(), `ncua-cr-${process.pid}.zip`);

  // 1) Download the most recent reachable quarterly Call Report ZIP.
  let quarter = null;
  for (const q of quarterCandidates()) {
    const url = `${FILES_BASE}/call-report-data-${q}.zip`;
    try {
      download(url, tmp);
      if (fs.existsSync(tmp) && fs.statSync(tmp).size > 100000) { quarter = q; log(`downloaded ${url}`); break; }
    } catch (_) { /* try older quarter */ }
  }
  if (!quarter) { writeStub('quarterly Call Report ZIP not reachable', retrieved_at, null); return; }

  // 2) Read the three tables out of the ZIP.
  let branchRows, foicuRows, fsRows;
  try {
    branchRows = parseCSV(unzipMember(tmp, 'Credit Union Branch Information.txt'));
    foicuRows = parseCSV(unzipMember(tmp, 'FOICU.txt'));
    fsRows = parseCSV(unzipMember(tmp, 'FS220.txt'));
  } catch (e) {
    try { fs.unlinkSync(tmp); } catch (_) {}
    writeStub(`ZIP member parse failed: ${e.message.split('\n')[0]}`, retrieved_at, quarter);
    return;
  }
  try { fs.unlinkSync(tmp); } catch (_) {}

  const idx = (hdr, name) => hdr.indexOf(name);

  // --- Branches: find CUs (by CU_NUMBER) with an LA-County office; count LA branches each. ---
  const bh = branchRows[0] || [];
  const bCU = idx(bh, 'CU_NUMBER');
  const bState = idx(bh, 'PhysicalAddressStateCode');
  const bCounty = idx(bh, 'PhysicalAddressCountyName2');
  const bCity = idx(bh, 'PhysicalAddressCity');
  if (bCU < 0 || bState < 0 || bCounty < 0) {
    writeStub('Branch Information table missing expected columns', retrieved_at, quarter);
    return;
  }
  const laBranchCount = {};          // CU_NUMBER -> LA-County office count
  const laCity = {};                 // CU_NUMBER -> a representative LA-County city
  let laBranchTotal = 0;
  for (let i = 1; i < branchRows.length; i++) {
    const r = branchRows[i];
    if (!r || r.length <= Math.max(bCU, bState, bCounty)) continue;
    const st = (r[bState] || '').trim();
    const cty = (r[bCounty] || '').trim();
    if (st === STATE && cty.toLowerCase() === COUNTY.toLowerCase()) {
      const cu = (r[bCU] || '').trim();
      if (!cu) continue;
      laBranchCount[cu] = (laBranchCount[cu] || 0) + 1;
      laBranchTotal++;
      if (!laCity[cu] && bCity >= 0 && (r[bCity] || '').trim()) laCity[cu] = (r[bCity] || '').trim();
    }
  }
  const laCUs = Object.keys(laBranchCount);
  log(`LA County offices: ${laBranchTotal} across ${laCUs.length} distinct credit unions`);
  if (!laCUs.length) { writeStub('no LA-County offices found in Branch Information table', retrieved_at, quarter); return; }

  // --- FOICU master keyed by CU_NUMBER. ---
  const fh = foicuRows[0] || [];
  const fCU = idx(fh, 'CU_NUMBER');
  const fName = idx(fh, 'CU_NAME');
  const fCity = idx(fh, 'CITY');
  const fState = idx(fh, 'STATE');
  const fZip = idx(fh, 'ZIP_CODE');
  const fStreet = idx(fh, 'STREET');
  const fYear = idx(fh, 'YEAR_OPENED');
  const fMDI = idx(fh, 'IsMDI');
  const fCharterState = idx(fh, 'CharterState');
  const master = {};
  for (let i = 1; i < foicuRows.length; i++) {
    const r = foicuRows[i];
    if (!r || fCU < 0 || r.length <= fCU) continue;
    master[(r[fCU] || '').trim()] = r;
  }

  // --- FS220 financials keyed by CU_NUMBER (ACCT_010 assets, ACCT_083 members). ---
  const sh = fsRows[0] || [];
  const sCU = idx(sh, 'CU_NUMBER');
  const sAssets = idx(sh, 'ACCT_010');
  const sMembers = idx(sh, 'ACCT_083');
  const fin = {};
  for (let i = 1; i < fsRows.length; i++) {
    const r = fsRows[i];
    if (!r || sCU < 0 || r.length <= sCU) continue;
    fin[(r[sCU] || '').trim()] = r;
  }
  log(`FS220 financial rows: ${Object.keys(fin).length}; FOICU master rows: ${Object.keys(master).length}`);

  // --- Build the credit-unions directory: one row per CU with an LA-County office. ---
  const credit_unions = laCUs.map((cu, i) => {
    const m = master[cu] || [];
    const f = fin[cu] || [];
    const get = (row, i2) => (i2 >= 0 && row && row.length > i2 ? row[i2] : null);
    const name = (get(m, fName) || '').trim() || `NCUA charter ${cu}`;
    const assets = sAssets >= 0 ? num(get(f, sAssets)) : null;   // TOTAL ASSETS ($ actual dollars)
    const members = sMembers >= 0 ? num(get(f, sMembers)) : null;
    return {
      id: i + 1,
      charter: cu,                                              // NCUA charter number (CU_NUMBER)
      name,
      members: members != null ? members : null,
      total_assets: assets != null ? assets : null,            // $ actual dollars (NOT thousands)
      la_branches: laBranchCount[cu] || 0,
      city: laCity[cu] || (get(m, fCity) || '').trim() || null,
      hq_city: (get(m, fCity) || '').trim() || null,
      hq_state: (get(m, fState) || '').trim() || null,
      charter_state: (get(m, fCharterState) || '').trim() || null,
      zip: (get(m, fZip) || '').trim() || null,
      street: (get(m, fStreet) || '').trim() || null,
      year_opened: get(m, fYear) ? num(get(m, fYear)) : null,
      mdi: fMDI >= 0 ? /^(1|true|yes)$/i.test((get(m, fMDI) || '').trim()) : null,  // minority depository institution
      // provenance (package §16 evidence-first)
      source: 'NCUA',
      source_url: `${LOCATOR}?ID=${encodeURIComponent(cu)}`,
      identifier: `NCUA charter ${cu}`,
      retrieved_at,
    };
  }).sort((a, b) => (b.la_branches - a.la_branches) || ((b.total_assets || 0) - (a.total_assets || 0)));

  const meta = {
    source: 'NCUA',
    source_url: HUMAN,
    source_home: HUMAN,
    access_method: 'official_bulk_file',
    dataset: 'NCUA Quarterly Call Report data',
    quarter,
    file: `${FILES_BASE}/call-report-data-${quarter}.zip`,
    scope: `Credit unions with a physical office in ${COUNTY} County, ${STATE} (scoped via the Call Report Branch Information county field)`,
    la_offices: laBranchTotal,
    personal_data: false,
    count: credit_unions.length,
    retrieved_at,
  };

  atomicWrite(OUT, { meta, credit_unions });
  log(`wrote ${credit_unions.length} credit unions → ${path.relative(ROOT, OUT)}`);
  log('source:', HUMAN, `(quarter ${quarter}, $0, official NCUA public bulk file)`);
}

main();