← back to Norma

app/api/datasource/route.ts

162 lines

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

// ─── GET /api/datasource ─────────────────────────────────────────────────────
// Query params: type=apis|headlines|statements, search=..., page=1, limit=20

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

  try {
    const { searchParams } = new URL(request.url);
    const type = searchParams.get('type') ?? 'apis';
    const search = searchParams.get('search') ?? '';
    const page = Math.max(1, parseInt(searchParams.get('page') ?? '1', 10) || 1);
    const limit = Math.min(100, Math.max(1, parseInt(searchParams.get('limit') ?? '20', 10) || 20));
    const offset = (page - 1) * limit;

    const searchPattern = search ? `%${search}%` : null;

    if (type === 'apis') {
      // discovered_apis table
      const baseWhere = searchPattern
        ? `WHERE (name ILIKE $1 OR description ILIKE $1 OR category ILIKE $1)`
        : `WHERE TRUE`;
      const params: unknown[] = searchPattern
        ? [searchPattern, limit, offset]
        : [limit, offset];
      const limitIdx = searchPattern ? 2 : 1;
      const offsetIdx = searchPattern ? 3 : 2;

      const { rows } = await query<{
        id: string;
        name: string;
        description: string | null;
        category: string | null;
        api_url: string | null;
        relevance_score: number | null;
        status: string | null;
        match_topics: unknown;
        last_tested_at: string | null;
        created_at: string;
      }>(
        `SELECT id, name, description, category, api_url, relevance_score,
                status, match_topics, last_tested_at, created_at
         FROM discovered_apis
         ${baseWhere}
         ORDER BY relevance_score DESC NULLS LAST, created_at DESC
         LIMIT $${limitIdx} OFFSET $${offsetIdx}`,
        params,
      );

      const { rows: countRows } = await query<{ count: string }>(
        `SELECT COUNT(*) AS count FROM discovered_apis ${baseWhere}`,
        searchPattern ? [searchPattern] : [],
      );

      return NextResponse.json({
        type: 'apis',
        items: rows,
        total: parseInt(countRows[0]?.count ?? '0', 10),
        page,
        limit,
      });
    }

    if (type === 'headlines') {
      // newspaper_frontpages table
      const baseWhere = searchPattern
        ? `WHERE (newspaper_name ILIKE $1 OR top_headline ILIKE $1 OR country ILIKE $1)`
        : `WHERE TRUE`;
      const params: unknown[] = searchPattern
        ? [searchPattern, limit, offset]
        : [limit, offset];
      const limitIdx = searchPattern ? 2 : 1;
      const offsetIdx = searchPattern ? 3 : 2;

      const { rows } = await query<{
        id: string;
        newspaper_name: string;
        country: string | null;
        top_headline: string | null;
        frontpage_url: string | null;
        relevance_score: number | null;
        scraped_date: string | null;
        created_at: string;
      }>(
        `SELECT id, newspaper_name, country, top_headline, frontpage_url, relevance_score, scraped_date, created_at
         FROM newspaper_frontpages
         ${baseWhere}
         ORDER BY relevance_score DESC NULLS LAST, scraped_date DESC NULLS LAST
         LIMIT $${limitIdx} OFFSET $${offsetIdx}`,
        params,
      );

      const { rows: countRows } = await query<{ count: string }>(
        `SELECT COUNT(*) AS count FROM newspaper_frontpages ${baseWhere}`,
        searchPattern ? [searchPattern] : [],
      );

      return NextResponse.json({
        type: 'headlines',
        items: rows,
        total: parseInt(countRows[0]?.count ?? '0', 10),
        page,
        limit,
      });
    }

    if (type === 'statements') {
      // nonprofit_statements table
      const baseWhere = searchPattern
        ? `WHERE (org_name ILIKE $1 OR statement_title ILIKE $1 OR sentiment ILIKE $1)`
        : `WHERE TRUE`;
      const params: unknown[] = searchPattern
        ? [searchPattern, limit, offset]
        : [limit, offset];
      const limitIdx = searchPattern ? 2 : 1;
      const offsetIdx = searchPattern ? 3 : 2;

      const { rows } = await query<{
        id: string;
        org_name: string;
        statement_title: string | null;
        sentiment: string | null;
        statement_body: string | null;
        source_url: string | null;
        relevance_score: number | null;
        statement_date: string | null;
        created_at: string;
      }>(
        `SELECT id, org_name, statement_title, sentiment, statement_body, source_url,
                relevance_score, statement_date, created_at
         FROM nonprofit_statements
         ${baseWhere}
         ORDER BY relevance_score DESC NULLS LAST, statement_date DESC NULLS LAST
         LIMIT $${limitIdx} OFFSET $${offsetIdx}`,
        params,
      );

      const { rows: countRows } = await query<{ count: string }>(
        `SELECT COUNT(*) AS count FROM nonprofit_statements ${baseWhere}`,
        searchPattern ? [searchPattern] : [],
      );

      return NextResponse.json({
        type: 'statements',
        items: rows,
        total: parseInt(countRows[0]?.count ?? '0', 10),
        page,
        limit,
      });
    }

    return NextResponse.json({ error: 'Invalid type. Use: apis, headlines, or statements.' }, { status: 400 });
  } catch (err: unknown) {
    console.error('[api/datasource] Error:', (err as Error).message);
    return NextResponse.json({ error: (err as Error).message }, { status: 500 });
  }
}