`app/api/ingest/charity-navigator/route.ts`

130 lines
import { NextRequest, NextResponse } from 'next/server';
import { query } from '@/lib/db';
import { requireRole } from '@/lib/require-role';
import { getOrgId } from '@/lib/orgId';
import {
  searchOrganizations,
  type CharityNavOrg,
} from '@/lib/charity-navigator';

/**
 * POST /api/ingest/charity-navigator
 *
 * Ingests organization ratings from Charity Navigator.
 * Body: { keywords?: string[] }
 *
 * Upserts into charity_ratings via ON CONFLICT (ein) 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 ?? [
      'education',
      'community development',
      'human services',
    ];

    // De-duplicate organizations across keyword searches
    const allOrgs = new Map<string, CharityNavOrg>();

    for (const keyword of keywords) {
      try {
        const orgs = await searchOrganizations(keyword, 50);
        for (const org of orgs) {
          if (org.ein && !allOrgs.has(org.ein)) {
            allOrgs.set(org.ein, org);
          }
        }
      } catch (err) {
        stats.errors.push(`search_"${keyword}": ${(err as Error).message}`);
      }
    }

    // Upsert each organization
    for (const org of allOrgs.values()) {
      try {
        await query(
          `INSERT INTO charity_ratings (
            ein, org_name, overall_rating, financial_rating,
            accountability_rating, category, cause, tagline,
            charity_navigator_url, raw_json, org_id
          ) VALUES (
            $1, $2, $3, $4,
            $5, $6, $7, $8,
            $9, $10, $11
          )
          ON CONFLICT (ein) DO UPDATE SET
            org_name = EXCLUDED.org_name,
            overall_rating = EXCLUDED.overall_rating,
            financial_rating = EXCLUDED.financial_rating,
            accountability_rating = EXCLUDED.accountability_rating,
            category = EXCLUDED.category,
            cause = EXCLUDED.cause,
            tagline = EXCLUDED.tagline,
            charity_navigator_url = EXCLUDED.charity_navigator_url,
            raw_json = EXCLUDED.raw_json,
            updated_at = NOW()`,
          [
            org.ein,
            org.orgName,
            org.overallRating,
            org.financialRating,
            org.accountabilityRating,
            org.category,
            org.cause,
            org.tagline,
            org.charityNavigatorUrl,
            JSON.stringify(org.rawJson),
            orgId,
          ],
        );
        stats.ingested++;
      } catch (err) {
        stats.errors.push(`insert_${org.ein}: ${(err as Error).message}`);
      }
    }
  } catch (err) {
    stats.errors.push('charity_navigator_ingest: ' + (err as Error).message);
  }

  return NextResponse.json({
    success: true,
    ingested: stats.ingested,
    errors: stats.errors,
    message: `Ingested ${stats.ingested} Charity Navigator organizations`,
  });
}

/* ─── GET: Return ingested Charity Navigator ratings ─────────────────────── */
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, ein, org_name, overall_rating, financial_rating,
             accountability_rating, category, cause, tagline,
             charity_navigator_url, total_revenue, total_expenses,
             program_expense_ratio, admin_expense_ratio,
             fundraising_expense_ratio, created_at, updated_at
      FROM charity_ratings
      ORDER BY overall_rating DESC NULLS LAST, org_name ASC
      LIMIT 200
    `);

    return NextResponse.json({
      ratings: rows,
      total: rows.length,
    });
  } catch (err) {
    console.error('[ingest/charity-navigator] GET error:', (err as Error).message);
    return NextResponse.json({ error: 'Failed to fetch Charity Navigator ratings' }, { status: 500 });
  }
}