`app/api/geo/crosswalk/route.ts`

182 lines
/**
 * POST /api/geo/crosswalk
 *   Downloads the Census ZCTA-to-County relationship file and inserts every
 *   ZIP+county pair into the `zip_crosswalk` table.
 *   Uses ON CONFLICT DO NOTHING so re-running is safe.
 *
 * GET /api/geo/crosswalk
 *   Returns aggregate stats about the crosswalk data currently in the DB.
 */

import { NextRequest, NextResponse } from 'next/server';
import { query } from '@/lib/db';
import { requireRole } from '@/lib/require-role';
import { downloadCrosswalk, type CrosswalkRow } from '@/lib/geo-crosswalk';

/* ─── POST: Ingest crosswalk file into zip_crosswalk table ────────── */
export async function POST(request: NextRequest) {
  const auth = requireRole(request, 'admin', 'staff');
  if (auth instanceof NextResponse) return auth;
  const startTime = Date.now();

  // 1. Download and parse the Census file
  let rows: CrosswalkRow[];
  try {
    rows = await downloadCrosswalk();
  } catch (err) {
    // downloadCrosswalk() never throws — it returns [] on error —
    // but guard just in case.
    console.error('[geo/crosswalk] Unexpected download error:', (err as Error).message);
    return NextResponse.json(
      { success: false, error: 'Failed to download crosswalk file' },
      { status: 502 },
    );
  }

  if (rows.length === 0) {
    return NextResponse.json(
      {
        success: false,
        error:
          'Crosswalk file returned no records. The Census URL may be unavailable or the file format may have changed.',
      },
      { status: 502 },
    );
  }

  // 2. Batch-insert into zip_crosswalk
  let inserted = 0;
  let skipped  = 0;
  const errors: string[] = [];

  // Process in chunks of 500 to avoid overwhelming the connection pool.
  const CHUNK_SIZE = 500;

  for (let offset = 0; offset < rows.length; offset += CHUNK_SIZE) {
    const chunk = rows.slice(offset, offset + CHUNK_SIZE);

    for (const row of chunk) {
      try {
        const result = await query(
          `INSERT INTO zip_crosswalk (zip, county_fips, state_fips, state_abbr, county_name)
           VALUES ($1, $2, $3, $4, $5)
           ON CONFLICT (zip, county_fips) DO NOTHING`,
          [row.zip, row.countyFips, row.stateFips, row.stateAbbr, row.countyName],
        );

        // rowCount === 1 means a new row was inserted; 0 means ON CONFLICT hit.
        if ((result.rowCount ?? 0) > 0) {
          inserted++;
        } else {
          skipped++;
        }
      } catch (err) {
        const msg = `zip=${row.zip} county=${row.countyFips}: ${(err as Error).message}`;
        errors.push(msg);
        // Log the first 10 errors in detail; after that just count them.
        if (errors.length <= 10) {
          console.error('[geo/crosswalk] Insert error:', msg);
        }
      }
    }
  }

  const elapsed = ((Date.now() - startTime) / 1000).toFixed(1);
  const status  = errors.length === 0 ? 'success' : errors.length < rows.length ? 'partial' : 'failed';

  return NextResponse.json({
    success: status !== 'failed',
    status,
    elapsed_seconds: elapsed,
    total_parsed:  rows.length,
    inserted,
    skipped,
    error_count:   errors.length,
    // Return up to 20 sample errors so the caller can debug without flooding
    sample_errors: errors.slice(0, 20),
  });
}

/* ─── GET: Return crosswalk stats from the database ──────────────────── */
export async function GET(request: NextRequest) {
  const auth = requireRole(request, 'admin', 'staff');
  if (auth instanceof NextResponse) return auth;
  try {
    // Run all stat queries in parallel
    const [totalResult, stateResult, sampleResult, multiCountyResult] =
      await Promise.all([
        // Total row count and unique zip count
        query<{ total_rows: string; unique_zips: string; unique_counties: string; unique_states: string }>(
          `SELECT
             COUNT(*)                        AS total_rows,
             COUNT(DISTINCT zip)             AS unique_zips,
             COUNT(DISTINCT county_fips)     AS unique_counties,
             COUNT(DISTINCT state_fips)      AS unique_states
           FROM zip_crosswalk`,
        ),

        // Rows per state, ordered by volume
        query<{ state_abbr: string; state_fips: string; zip_count: string; county_count: string }>(
          `SELECT
             state_abbr,
             state_fips,
             COUNT(DISTINCT zip)         AS zip_count,
             COUNT(DISTINCT county_fips) AS county_count
           FROM zip_crosswalk
           GROUP BY state_abbr, state_fips
           ORDER BY zip_count DESC`,
        ),

        // 5 sample rows for quick sanity check
        query<{
          zip: string;
          county_fips: string;
          county_name: string;
          state_abbr: string;
        }>(
          `SELECT zip, county_fips, county_name, state_abbr
           FROM zip_crosswalk
           ORDER BY zip
           LIMIT 5`,
        ),

        // How many ZIPs span more than one county
        query<{ multi_county_zips: string }>(
          `SELECT COUNT(*) AS multi_county_zips
           FROM (
             SELECT zip
             FROM zip_crosswalk
             GROUP BY zip
             HAVING COUNT(*) > 1
           ) t`,
        ),
      ]);

    const totals       = totalResult.rows[0];
    const multiCounty  = multiCountyResult.rows[0];

    return NextResponse.json({
      totals: {
        total_rows:      Number(totals?.total_rows      ?? 0),
        unique_zips:     Number(totals?.unique_zips     ?? 0),
        unique_counties: Number(totals?.unique_counties ?? 0),
        unique_states:   Number(totals?.unique_states   ?? 0),
        multi_county_zips: Number(multiCounty?.multi_county_zips ?? 0),
      },
      by_state: stateResult.rows.map((r) => ({
        state_abbr:   r.state_abbr,
        state_fips:   r.state_fips,
        zip_count:    Number(r.zip_count),
        county_count: Number(r.county_count),
      })),
      sample_rows: sampleResult.rows,
    });
  } catch (err) {
    console.error('[geo/crosswalk] GET error:', (err as Error).message);
    return NextResponse.json(
      { error: 'Failed to fetch crosswalk stats' },
      { status: 500 },
    );
  }
}