← back to Norma

app/api/ingest/sam-gov/route.ts

143 lines

import { NextRequest, NextResponse } from 'next/server';
import { query } from '@/lib/db';
import { requireRole } from '@/lib/require-role';
import { getOrgId } from '@/lib/orgId';
import {
  searchOpportunities,
  type SamOpportunity,
} from '@/lib/sam-gov';

/**
 * POST /api/ingest/sam-gov
 *
 * Ingests contract opportunities from SAM.gov.
 * Body: { keywords?: string[] }
 *
 * Upserts into sam_opportunities via ON CONFLICT (notice_id) DO UPDATE.
 */
export async function POST(request: NextRequest) {
  const auth = requireRole(request, 'admin', 'staff');
  if (auth instanceof NextResponse) return auth;

  const orgId = auth.role === 'admin' ? getOrgId(request) : auth.orgId;
  const stats = { ingested: 0, errors: [] as string[] };

  try {
    const body = await request.json().catch(() => ({}));
    const keywords: string[] = body.keywords ?? [
      'nonprofit grant',
      'community development',
      'education assistance',
    ];

    // De-duplicate opportunities across keyword searches
    const allOpps = new Map<string, SamOpportunity>();

    for (const keyword of keywords) {
      try {
        const opps = await searchOpportunities(keyword, 50);
        for (const opp of opps) {
          if (opp.noticeId && !allOpps.has(opp.noticeId)) {
            allOpps.set(opp.noticeId, opp);
          }
        }
      } catch (err) {
        stats.errors.push(`search_"${keyword}": ${(err as Error).message}`);
      }
    }

    // Upsert each opportunity
    for (const opp of allOpps.values()) {
      try {
        await query(
          `INSERT INTO sam_opportunities (
            notice_id, title, solicitation_number, department, sub_tier,
            office, posted_date, response_deadline, type,
            set_aside_description, naics_code, classification_code,
            description, url, raw_json, org_id
          ) VALUES (
            $1, $2, $3, $4, $5,
            $6, $7, $8, $9,
            $10, $11, $12,
            $13, $14, $15, $16
          )
          ON CONFLICT (notice_id) DO UPDATE SET
            title = EXCLUDED.title,
            solicitation_number = EXCLUDED.solicitation_number,
            department = EXCLUDED.department,
            sub_tier = EXCLUDED.sub_tier,
            office = EXCLUDED.office,
            response_deadline = EXCLUDED.response_deadline,
            type = EXCLUDED.type,
            set_aside_description = EXCLUDED.set_aside_description,
            naics_code = EXCLUDED.naics_code,
            classification_code = EXCLUDED.classification_code,
            description = EXCLUDED.description,
            url = EXCLUDED.url,
            raw_json = EXCLUDED.raw_json,
            updated_at = NOW()`,
          [
            opp.noticeId,
            opp.title,
            opp.solicitationNumber,
            opp.department,
            opp.subTier,
            opp.office,
            opp.postedDate || null,
            opp.responseDeadLine || null,
            opp.type,
            opp.setAsideDescription,
            opp.naicsCode,
            opp.classificationCode,
            opp.description,
            opp.uiLink,
            JSON.stringify(opp),
            orgId,
          ],
        );
        stats.ingested++;
      } catch (err) {
        stats.errors.push(`insert_${opp.noticeId}: ${(err as Error).message}`);
      }
    }
  } catch (err) {
    stats.errors.push('sam_gov_ingest: ' + (err as Error).message);
  }

  return NextResponse.json({
    success: true,
    ingested: stats.ingested,
    errors: stats.errors,
    message: `Ingested ${stats.ingested} SAM.gov opportunities`,
  });
}

/* ─── GET: Return ingested SAM.gov opportunities ─────────────────────────── */
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, notice_id, title, solicitation_number, department, sub_tier,
             office, posted_date, response_deadline, type,
             set_aside_description, naics_code, classification_code,
             description, url, created_at, updated_at
      FROM sam_opportunities
      ORDER BY
        CASE WHEN response_deadline IS NOT NULL AND response_deadline >= CURRENT_DATE THEN 0 ELSE 1 END,
        response_deadline ASC NULLS LAST,
        posted_date DESC NULLS LAST
      LIMIT 200
    `);

    return NextResponse.json({
      opportunities: rows,
      total: rows.length,
    });
  } catch (err) {
    console.error('[ingest/sam-gov] GET error:', (err as Error).message);
    return NextResponse.json({ error: 'Failed to fetch SAM.gov opportunities' }, { status: 500 });
  }
}