`src/ingest/fhfa_hpi.ts`

152 lines
/**
 * FHFA House Price Index (HPI_master.csv) -> metric 'hpi', quarterly, metro + state.
 * URL DRIFT (probed 2026-07-21): the old /DataTools/Downloads/Documents/HPI/HPI_master.csv
 * 404s; current path is /hpi/download/monthly/hpi_master.csv (serves the full master file).
 * Flavor choice: purchase-only quarterly covers only 100 MSAs vs 410 for all-transactions,
 * so we use hpi_type='traditional', hpi_flavor='all-transactions', frequency='quarterly'.
 * Metro match: place_id IS the CBSA code -> region.cbsa_code. State match: 2-letter place_id.
 * Last 12 quarters only; period = quarter-end date; value = index_nsa (SA blank for many MSAs).
 */
import { readFileSync } from 'node:fs';
import { pool } from '../../db/pool.ts';
import { openRun, closeRun, download } from './run.ts';
import { loadRegionMaps, loadOverrides, buildMetroMatcher, upsertMetricRows, upsertSourceRegionMap, UnmatchedCollector, type MetricRow } from './crosswalk.ts';

const SOURCE = 'fhfa';
const URL = 'https://www.fhfa.gov/hpi/download/monthly/hpi_master.csv';
const QUARTERS_KEPT = 12;

function parseCsvLine(line: string): string[] {
  const out: string[] = [];
  let cur = '', inQ = false;
  for (let i = 0; i < line.length; i++) {
    const c = line[i];
    if (inQ) {
      if (c === '"') { if (line[i + 1] === '"') { cur += '"'; i++; } else inQ = false; }
      else cur += c;
    } else if (c === '"') inQ = true;
    else if (c === ',') { out.push(cur); cur = ''; }
    else cur += c;
  }
  out.push(cur);
  return out;
}

const QUARTER_END: Record<string, string> = { '1': '03-31', '2': '06-30', '3': '09-30', '4': '12-31' };

async function main() {
  const runId = await openRun(SOURCE, URL);
  try {
    const { path, sha256 } = await download(URL, 'hpi_master.csv');
    const lines = readFileSync(path, 'utf8').split('\n');
    const header = parseCsvLine(lines[0].trim());
    const idx = (name: string) => {
      const i = header.indexOf(name);
      if (i < 0) throw new Error(`HPI header missing ${name}: ${header.join(',')}`);
      return i;
    };
    const iType = idx('hpi_type'), iFlavor = idx('hpi_flavor'), iFreq = idx('frequency'),
      iLevel = idx('level'), iName = idx('place_name'), iId = idx('place_id'),
      iYr = idx('yr'), iPeriod = idx('period'), iNsa = idx('index_nsa');

    type Parsed = { level: string; placeId: string; placeName: string; yr: number; q: string; value: number };
    const parsed: Parsed[] = [];
    let maxYr = 0;
    for (let n = 1; n < lines.length; n++) {
      const line = lines[n].trim();
      if (!line) continue;
      const c = parseCsvLine(line);
      if (c[iType] !== 'traditional' || c[iFlavor] !== 'all-transactions' || c[iFreq] !== 'quarterly') continue;
      const level = c[iLevel];
      if (level !== 'MSA' && level !== 'State') continue;
      const value = Number(c[iNsa]);
      if (!Number.isFinite(value)) continue;
      const yr = Number(c[iYr]);
      parsed.push({ level, placeId: c[iId], placeName: c[iName], yr, q: c[iPeriod], value });
      if (yr > maxYr) maxYr = yr;
    }
    if (parsed.length === 0) throw new Error('HPI parse produced 0 rows — format drift?');

    // keep last 12 quarters: quarter index yr*4 + q
    const latestQ = Math.max(...parsed.filter(p => p.yr === maxYr).map(p => Number(p.q)));
    const latestIdx = maxYr * 4 + latestQ;
    const cutoffIdx = latestIdx - (QUARTERS_KEPT - 1);

    const maps = await loadRegionMaps();
    const overrides = loadOverrides(SOURCE);
    const matchMetro = buildMetroMatcher(maps);
    const unmatched = new UnmatchedCollector();
    // regions covered by a DIRECT cbsa_code match — an MSAD fallback must never overwrite these
    const directRegionIds = new Set<number>();
    for (const p of parsed) {
      if (p.level !== 'MSA') continue;
      const direct = maps.byCbsa.get(p.placeId);
      if (direct) directRegionIds.add(direct.id);
    }
    /** "Los Angeles-Long Beach-Glendale, CA (MSAD)" -> parent CBSA via first city × each state token. */
    const matchMsad = (placeName: string) => {
      const name = placeName.replace(/\s*\(MSAD\)$/, '');
      const comma = name.lastIndexOf(',');
      if (comma < 0) return null;
      const firstCity = name.slice(0, comma).split(/[-/]/)[0].trim();
      for (const st of name.slice(comma + 1).trim().split('-')) {
        const m = matchMetro(`${firstCity}, ${st.trim()}`);
        if (m) return m;
      }
      return null;
    };
    const rows: MetricRow[] = [];
    const mapEntries: { source: string; sourceRegionId: string; regionId: number; matchMethod: string }[] = [];
    const unmatchedIds = new Set<string>();

    for (const p of parsed) {
      if (p.yr * 4 + Number(p.q) < cutoffIdx) continue;
      let region = p.level === 'MSA' ? maps.byCbsa.get(p.placeId) : maps.byStateCode.get(p.placeId);
      let method = p.level === 'MSA' ? 'cbsa' : 'state';
      if (!region && overrides[p.placeId]) {
        region = maps.byCanonicalKey.get(overrides[p.placeId]);
        method = 'override';
      }
      if (!region && p.level === 'MSA' && p.placeName.endsWith('(MSAD)')) {
        // FHFA lists divisions INSTEAD of the parent CBSA for the largest metros.
        // The PRIMARY division's first-named city equals the parent CBSA's first city
        // ("Los Angeles-Long Beach-Glendale, CA" -> CBSA 31080); secondary divisions
        // (Anaheim, Camden, Cambridge...) correctly fail the prefix match and stay skipped.
        // Never applied to a region already covered by a direct cbsa_code match.
        const m = matchMsad(p.placeName);
        if (m && !directRegionIds.has(m.region.id)) { region = m.region; method = 'msad_primary_city'; }
      }
      if (!region) {
        if (!unmatchedIds.has(p.placeId)) {
          unmatchedIds.add(p.placeId);
          unmatched.add(SOURCE, { level: p.level, place_id: p.placeId, place_name: p.placeName });
        }
        continue;
      }
      rows.push({
        regionId: region.id, source: SOURCE, metric: 'hpi',
        period: `${p.yr}-${QUARTER_END[p.q]}`, value: p.value,
      });
      mapEntries.push({ source: SOURCE, sourceRegionId: p.placeId, regionId: region.id, matchMethod: method });
    }
    if (rows.length === 0) throw new Error('HPI matched 0 regions — crosswalk drift?');

    const upserted = await upsertMetricRows(rows, runId);
    await upsertSourceRegionMap(mapEntries);
    unmatched.flush();
    await closeRun(runId, 'ok', {
      upserted, skipped: unmatchedIds.size, fileHash: sha256,
      notes: `flavor=all-transactions latest=${maxYr}Q${latestQ} quarters=${QUARTERS_KEPT} unmatched_places=${unmatchedIds.size}`,
    });
    console.log(`[fhfa] ok: ${upserted} rows (latest ${maxYr}Q${latestQ}), ${unmatchedIds.size} unmatched place_ids`);
  } catch (e) {
    await closeRun(runId, 'failed', { notes: String(e) });
    console.error('[fhfa] FAILED:', e);
    process.exitCode = 1;
  } finally {
    await pool.end();
  }
}

main();