← back to Norma

app/api/datasource/stats/route.ts

80 lines

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

// ─── GET /api/datasource/stats ────────────────────────────────────────────────

async function safeQuery<T extends Record<string, unknown>>(
  sql: string,
  params?: unknown[],
): Promise<T[]> {
  try {
    const { rows } = await query<T>(sql, params);
    return rows;
  } catch (err: unknown) {
    const msg = (err as Error).message ?? '';
    if (msg.includes('does not exist') || msg.includes('undefined_table')) {
      return [];
    }
    throw err;
  }
}

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

  try {
    // APIs: total + by_status breakdown
    const apiStatusRows = await safeQuery<{ status: string; count: string }>(
      `SELECT COALESCE(status, 'unknown') AS status, COUNT(*) AS count
       FROM discovered_apis
       GROUP BY status`,
    );

    const apiTotal = apiStatusRows.reduce((sum, r) => sum + parseInt(r.count, 10), 0);
    const apiByStatus: Record<string, number> = {};
    for (const r of apiStatusRows) {
      apiByStatus[r.status] = parseInt(r.count, 10);
    }

    // Headlines: total + today's count
    const headlineTotalRows = await safeQuery<{ count: string }>(
      `SELECT COUNT(*) AS count FROM newspaper_frontpages`,
    );
    const headlineTodayRows = await safeQuery<{ count: string }>(
      `SELECT COUNT(*) AS count
       FROM newspaper_frontpages
       WHERE scraped_at >= CURRENT_DATE`,
    );

    // Statements: total + last_24h
    const stmtTotalRows = await safeQuery<{ count: string }>(
      `SELECT COUNT(*) AS count FROM nonprofit_statements`,
    );
    const stmtLast24hRows = await safeQuery<{ count: string }>(
      `SELECT COUNT(*) AS count
       FROM nonprofit_statements
       WHERE created_at >= NOW() - INTERVAL '24 hours'`,
    );

    return NextResponse.json({
      apis: {
        total: apiTotal,
        by_status: apiByStatus,
      },
      headlines: {
        total: parseInt(headlineTotalRows[0]?.count ?? '0', 10),
        today: parseInt(headlineTodayRows[0]?.count ?? '0', 10),
      },
      statements: {
        total: parseInt(stmtTotalRows[0]?.count ?? '0', 10),
        last_24h: parseInt(stmtLast24hRows[0]?.count ?? '0', 10),
      },
    });
  } catch (err: unknown) {
    console.error('[api/datasource/stats] Error:', (err as Error).message);
    return NextResponse.json({ error: (err as Error).message }, { status: 500 });
  }
}