`app/api/geo/report-card/route.ts`

149 lines
/**
 * GET /api/geo/report-card?zip=12345
 *
 * Returns a comprehensive "report card" for a single zip code:
 *   - zip_profiles row (composite scores)
 *   - Associated geo_hotspots
 *   - Top CFPB complaints for that zip
 *   - Nearby schools (same state) from college_scorecard
 *   - Community posts (matched by state)
 *   - HEND resources mentioning that state
 *
 * Requires authentication.
 */

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

export async function GET(request: NextRequest) {
  const auth = requireRole(request, 'admin', 'staff');
  if (auth instanceof NextResponse) return auth;

  const { searchParams } = new URL(request.url);
  const zip = searchParams.get('zip');

  if (!zip || !/^\d{5}$/.test(zip)) {
    return NextResponse.json(
      { error: 'Valid 5-digit zip parameter is required' },
      { status: 400 },
    );
  }

  try {
    // ── Fetch zip profile ──
    const { rows: profileRows } = await query(
      `SELECT * FROM zip_profiles WHERE zip = $1`,
      [zip],
    );

    const profile = profileRows[0] || null;

    // Get state for related queries
    const stateAbbr = profile?.state || null;

    // If no profile exists, still try to gather raw data
    // ── Run all related queries in parallel ──
    const [hotspotsRes, complaintsRes, schoolsRes, communityRes, hendRes] = await Promise.all([
      // Hotspots for this zip
      query(
        `SELECT id, hotspot_type, z_score, rank, severity, contributing_factors, detected_at
         FROM geo_hotspots
         WHERE zip = $1
         ORDER BY z_score DESC`,
        [zip],
      ),

      // CFPB complaints for this zip
      query(
        `SELECT complaint_id, date_received, product, sub_product, issue, sub_issue,
                company, company_response, state, consumer_disputed, submitted_via,
                complaint_narrative, tags
         FROM cfpb_complaints
         WHERE zip_code = $1
         ORDER BY date_received DESC
         LIMIT 20`,
        [zip],
      ),

      // Schools in the same state
      stateAbbr
        ? query(
            `SELECT school_name, city, state, zip, ownership, tuition_in_state,
                    tuition_out_state, avg_net_price, pell_grant_rate, federal_loan_rate,
                    median_debt, completion_rate, median_earnings_6yr, enrollment
             FROM college_scorecard
             WHERE state = $1
             ORDER BY median_debt DESC NULLS LAST
             LIMIT 10`,
            [stateAbbr],
          )
        : Promise.resolve({ rows: [] }),

      // Community posts for the state (matched via body/title text search or sentiment)
      stateAbbr
        ? query(
            `SELECT id, platform, subreddit, author, title, body, url, score,
                    num_comments, posted_at, sentiment, debt_amount, school_mentioned, tags
             FROM community_posts
             ORDER BY posted_at DESC NULLS LAST
             LIMIT 10`,
          )
        : Promise.resolve({ rows: [] }),

      // HEND resources mentioning this state
      stateAbbr
        ? query(
            `SELECT id, title, url, source_type, published_date, author, organization,
                    summary, key_findings, states_mentioned, policy_topics
             FROM hend_resources
             WHERE states_mentioned @> ARRAY[$1]
             ORDER BY published_date DESC NULLS LAST
             LIMIT 10`,
            [stateAbbr],
          )
        : Promise.resolve({ rows: [] }),
    ]);

    // ── Compute letter grade from distress score ──
    const distressScore = Number(profile?.debt_distress_score ?? 0);
    let grade = 'N/A';
    if (profile) {
      if (distressScore >= 80) grade = 'F';
      else if (distressScore >= 60) grade = 'D';
      else if (distressScore >= 40) grade = 'C';
      else if (distressScore >= 20) grade = 'B';
      else grade = 'A';
    }

    return NextResponse.json({
      zip,
      grade,
      profile,
      hotspots: hotspotsRes.rows,
      complaints: {
        count: complaintsRes.rows.length,
        items: complaintsRes.rows,
      },
      schools: {
        count: schoolsRes.rows.length,
        items: schoolsRes.rows,
      },
      community_posts: {
        count: communityRes.rows.length,
        items: communityRes.rows,
      },
      hend_resources: {
        count: hendRes.rows.length,
        items: hendRes.rows,
      },
    });
  } catch (err) {
    console.error('[geo/report-card] Error:', (err as Error).message);
    return NextResponse.json(
      { error: `Failed to generate report card: ${(err as Error).message}` },
      { status: 500 },
    );
  }
}