`app/api/ingest/grants-gov/route.ts`

213 lines
import { NextRequest, NextResponse } from 'next/server';
import { query } from '@/lib/db';
import { requireRole } from '@/lib/require-role';
import {
  searchGrantsGov,
  searchFederalRegister,
  searchUSAspendingAwards,
  type GrantsGovOpportunity,
} from '@/lib/grants-gov';

/**
 * POST /api/ingest/grants-gov
 *
 * Ingests education-related grants from:
 *  1. Grants.gov REST API (open federal opportunities)
 *  2. FederalRegister.gov (policy notices)
 *  3. USAspending (historical award data)
 *
 * Upserts into federal_grants and federal_grant_history tables.
 */
export async function POST(request: NextRequest) {
  const auth = requireRole(request, 'admin', 'staff');
  if (auth instanceof NextResponse) return auth;

  const stats = { grantsGov: 0, federalRegister: 0, usaspending: 0, errors: [] as string[] };

  // ── 1. Grants.gov ───────────────────────────────────────────────────────
  try {
    const searchTerms = [
      'education student debt',
      'student loan forgiveness',
      'higher education financial aid',
      'college scholarship grant',
      'student assistance program',
    ];

    const allOpps = new Map<string, GrantsGovOpportunity>();
    for (const term of searchTerms) {
      const opps = await searchGrantsGov(term, 50);
      for (const opp of opps) {
        if (opp.id && !allOpps.has(opp.id)) {
          allOpps.set(opp.id, opp);
        }
      }
    }

    for (const opp of allOpps.values()) {
      try {
        await query(
          `INSERT INTO federal_grants (
            source_system, opportunity_id, opportunity_number, title, agency,
            description, award_floor, award_ceiling, posted_date, close_date,
            funding_category, official_url, tags, raw_data, fetched_at
          ) VALUES (
            'grants_gov', $1, $2, $3, $4,
            $5, $6, $7, $8, $9,
            $10, $11, $12, $13, NOW()
          )
          ON CONFLICT (opportunity_id) DO UPDATE SET
            title = EXCLUDED.title,
            agency = EXCLUDED.agency,
            description = EXCLUDED.description,
            award_floor = EXCLUDED.award_floor,
            award_ceiling = EXCLUDED.award_ceiling,
            close_date = EXCLUDED.close_date,
            raw_data = EXCLUDED.raw_data,
            updated_at = NOW()`,
          [
            opp.id,
            opp.number,
            opp.title,
            opp.agency,
            opp.synopsis?.synopsisDesc || opp.category?.description || '',
            opp.awardFloor || null,
            opp.awardCeiling || null,
            opp.openDate || null,
            opp.closeDate || null,
            opp.category?.description || null,
            'https://www.grants.gov/search-results-detail/' + opp.id,
            ['education', 'federal', opp.agency?.toLowerCase() || ''].filter(Boolean),
            JSON.stringify(opp),
          ],
        );
        stats.grantsGov++;
      } catch (err) {
        stats.errors.push('grants_gov_insert: ' + (err as Error).message);
      }
    }
  } catch (err) {
    stats.errors.push('grants_gov_search: ' + (err as Error).message);
  }

  // ── 2. Federal Register ─────────────────────────────────────────────────
  try {
    const docs = await searchFederalRegister('education grant student loan', 50);

    for (const doc of docs) {
      try {
        const oppId = 'fr_' + doc.document_number;
        await query(
          `INSERT INTO federal_grants (
            source_system, opportunity_id, title, agency, description,
            posted_date, official_url, tags, raw_data, fetched_at
          ) VALUES (
            'federal_register', $1, $2, $3, $4,
            $5, $6, $7, $8, NOW()
          )
          ON CONFLICT (opportunity_id) DO UPDATE SET
            title = EXCLUDED.title,
            description = EXCLUDED.description,
            raw_data = EXCLUDED.raw_data,
            updated_at = NOW()`,
          [
            oppId,
            doc.title,
            doc.agencies?.[0]?.name || 'Federal Register',
            doc.abstract || '',
            doc.publication_date || null,
            doc.html_url,
            ['federal-register', 'policy', 'education'],
            JSON.stringify(doc),
          ],
        );
        stats.federalRegister++;
      } catch (err) {
        stats.errors.push('fed_register_insert: ' + (err as Error).message);
      }
    }
  } catch (err) {
    stats.errors.push('fed_register_search: ' + (err as Error).message);
  }

  // ── 3. USAspending Award History ────────────────────────────────────────
  try {
    const awards = await searchUSAspendingAwards(
      ['education', 'student loan', 'student debt', 'higher education'],
      100,
    );

    for (const award of awards) {
      try {
        await query(
          `INSERT INTO federal_grant_history (
            assistance_listing_number, recipient_name, award_amount,
            awarding_agency, start_date, end_date, cfda_number,
            source_system, raw_data, fetched_at
          ) VALUES ($1, $2, $3, $4, $5, $6, $7, 'usaspending', $8, NOW())`,
          [
            award['CFDA Number'] || null,
            award['Recipient Name'] || null,
            award['Award Amount'] || null,
            award['Awarding Agency'] || null,
            award['Start Date'] || null,
            award['End Date'] || null,
            award['CFDA Number'] || null,
            JSON.stringify(award),
          ],
        );
        stats.usaspending++;
      } catch (err) {
        stats.errors.push('usaspending_insert: ' + (err as Error).message);
      }
    }
  } catch (err) {
    stats.errors.push('usaspending_search: ' + (err as Error).message);
  }

  return NextResponse.json({
    success: true,
    stats,
    message: 'Ingested ' + stats.grantsGov + ' Grants.gov + ' + stats.federalRegister + ' Federal Register + ' + stats.usaspending + ' USAspending records',
  });
}

/* ─── GET: Return ingested federal grants ────────────────────────────────── */
export async function GET(request: NextRequest) {
  const auth = requireRole(request, 'admin', 'staff');
  if (auth instanceof NextResponse) return auth;

  try {
    const { rows } = await query(`
      SELECT id, source_system, opportunity_id, opportunity_number, title, agency,
             description, award_floor, award_ceiling, posted_date, close_date,
             funding_category, official_url, tags, fetched_at
      FROM federal_grants
      WHERE is_education_related = true
      ORDER BY
        CASE WHEN close_date IS NOT NULL AND close_date >= CURRENT_DATE THEN 0 ELSE 1 END,
        close_date ASC NULLS LAST,
        posted_date DESC NULLS LAST
      LIMIT 200
    `);

    const { rows: awardStats } = await query(`
      SELECT
        COUNT(*) as total_awards,
        COALESCE(SUM(award_amount), 0) as total_amount,
        COALESCE(AVG(award_amount), 0) as avg_amount,
        COALESCE(MAX(award_amount), 0) as max_amount
      FROM federal_grant_history
    `);

    return NextResponse.json({
      grants: rows,
      total: rows.length,
      awardHistory: awardStats[0] || null,
    });
  } catch (err) {
    console.error('[ingest/grants-gov] GET error:', (err as Error).message);
    return NextResponse.json({ error: 'Failed to fetch federal grants' }, { status: 500 });
  }
}