`src/ingest/hud_fmr.ts`

83 lines
/**
 * HUD Fair Market Rents — FY2026 county-level, source 'hud_fmr'.
 * File: huduser.gov/portal/datasets/fmr/fmr2026/FY26_FMRs_revised.xlsx (XLSX-ONLY — HUD stopped
 * publishing a CSV variant after FY2024; parsed with the `xlsx` package, choice recorded in
 * SOURCES.md). huduser.gov bot-challenges the default fetch UA (202 + html), so we send a
 * browser UA. Period = FY2026 start = 2025-10-01.
 * fips column is 10-digit: county FIPS(5) + '99999' for whole-county rows; New England rows are
 * per-town (real sub-codes) — aggregated to county by pop2023-weighted mean.
 * Metrics: fmr_0br .. fmr_4br.
 */
import XLSX from 'xlsx';
import { openRun, closeRun, download } from './run.ts';
import { loadRegionMaps, upsertMetricRows, type MetricRow } from './crosswalk.ts';
import { pool } from '../../db/pool.ts';

const FMR_URL = 'https://www.huduser.gov/portal/datasets/fmr/fmr2026/FY26_FMRs_revised.xlsx';
const FY_PERIOD = '2025-10-01'; // FY2026 starts Oct 1 2025
const UA = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/126.0 Safari/537.36';

interface FmrRow {
  fips: string | number | null;
  pop2023: number | null;
  fmr_0: number | null; fmr_1: number | null; fmr_2: number | null; fmr_3: number | null; fmr_4: number | null;
}

async function main() {
  const runId = await openRun('hud_fmr', FMR_URL);
  try {
    const { path, sha256 } = await download(FMR_URL, 'FY26_FMRs_revised.xlsx', {
      reuseCached: true, headers: { 'User-Agent': UA },
    });
    const wb = XLSX.readFile(path);
    const rows = XLSX.utils.sheet_to_json<FmrRow>(wb.Sheets[wb.SheetNames[0]], { defval: null });
    if (!rows.length || !('fips' in rows[0]) || !('fmr_0' in rows[0])) {
      throw new Error('HUD FMR format drift: expected columns fips + fmr_0..fmr_4');
    }

    // aggregate per 5-digit county FIPS (pop-weighted across New England town rows)
    const byCounty = new Map<string, { wsum: number[]; pop: number; n: number }>();
    for (const r of rows) {
      const fips10 = String(r.fips ?? '').padStart(10, '0');
      const county = fips10.slice(0, 5);
      if (!/^\d{5}$/.test(county)) continue;
      const vals = [r.fmr_0, r.fmr_1, r.fmr_2, r.fmr_3, r.fmr_4].map(Number);
      if (vals.some(v => !Number.isFinite(v))) continue;
      const w = Math.max(Number(r.pop2023) || 0, 1);
      let agg = byCounty.get(county);
      if (!agg) { agg = { wsum: [0, 0, 0, 0, 0], pop: 0, n: 0 }; byCounty.set(county, agg); }
      for (let i = 0; i < 5; i++) agg.wsum[i] += vals[i] * w;
      agg.pop += w;
      agg.n++;
    }

    const maps = await loadRegionMaps();
    const metricRows: MetricRow[] = [];
    let skipped = 0;
    for (const [fips, agg] of byCounty) {
      const region = maps.byFips.get(fips);
      if (!region) { skipped++; continue; }
      for (let i = 0; i < 5; i++) {
        metricRows.push({
          regionId: region.id, source: 'hud_fmr', metric: `fmr_${i}br`,
          period: FY_PERIOD, value: Math.round(agg.wsum[i] / agg.pop),
        });
      }
    }
    const upserted = await upsertMetricRows(metricRows, runId);
    if (upserted === 0) throw new Error('0 metric rows parsed — HUD FMR format drift');
    await closeRun(runId, 'ok', {
      upserted, skipped, fileHash: sha256,
      notes: `FY2026 revised, period ${FY_PERIOD}; ${byCounty.size} counties (${rows.length} source rows incl. NE towns)`,
    });
    console.log(`hud_fmr: ${byCounty.size} counties, ${upserted} metric rows, ${skipped} unmatched`);
  } catch (e: any) {
    await closeRun(runId, 'failed', { notes: String(e.message || e) });
    throw e;
  } finally {
    await pool.end();
  }
}

main().catch((e) => { console.error(e); process.exit(1); });