`app/api/intelligence/power-scores/route.ts`

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

const ALLOWED_SORT_COLUMNS = new Set([
  'total_score',
  'connection_density',
  'financial_muscle',
  'advocacy_record',
  'rank',
]);

/**
 * GET /api/intelligence/power-scores
 * Query params:
 *   ?type=       — filter by entity_type
 *   ?state=      — filter by state (matched inside score_breakdown JSONB)
 *   ?min_score=  — minimum total_score (inclusive)
 *   ?sort=       — total_score | connection_density | financial_muscle | advocacy_record | rank
 *   ?limit=      — rows to return (default 50, max 200)
 *   ?q=          — search by entity_name (ILIKE)
 * Returns: { scores: [...] }
 */
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');
    const state = searchParams.get('state');
    const minScoreRaw = searchParams.get('min_score');
    const sortParam = searchParams.get('sort') || 'total_score';
    const q = searchParams.get('q');

    let limit = parseInt(searchParams.get('limit') || '50', 10);
    if (isNaN(limit) || limit < 1) limit = 50;
    if (limit > 200) limit = 200;

    // Validate sort column against allowlist to prevent SQL injection
    const sortColumn = ALLOWED_SORT_COLUMNS.has(sortParam) ? sortParam : 'total_score';
    // rank sorts ASC (lower rank = better); all score columns sort DESC
    const sortDirection = sortColumn === 'rank' ? 'ASC' : 'DESC';

    const conditions: string[] = [];
    const values: unknown[] = [];
    let paramIndex = 1;

    if (type) {
      conditions.push(`entity_type = $${paramIndex}`);
      values.push(type);
      paramIndex++;
    }

    if (state) {
      conditions.push(`score_breakdown->>'state' = $${paramIndex}`);
      values.push(state);
      paramIndex++;
    }

    if (minScoreRaw !== null) {
      const minScore = parseFloat(minScoreRaw);
      if (!isNaN(minScore)) {
        conditions.push(`total_score >= $${paramIndex}`);
        values.push(minScore);
        paramIndex++;
      }
    }

    if (q) {
      conditions.push(`entity_name ILIKE $${paramIndex}`);
      values.push(`%${q}%`);
      paramIndex++;
    }

    const whereClause = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';

    const result = await query(
      `SELECT
         entity_type,
         entity_id,
         entity_name,
         connection_density,
         financial_muscle,
         advocacy_record,
         total_score,
         rank,
         top_connections,
         score_breakdown,
         computed_at
       FROM power_scores
       ${whereClause}
       ORDER BY ${sortColumn} ${sortDirection} NULLS LAST
       LIMIT $${paramIndex}`,
      [...values, limit],
    );

    return NextResponse.json({ scores: result.rows });
  } catch (err) {
    console.error('[api/intelligence/power-scores] GET error:', (err as Error).message);
    return NextResponse.json({ error: 'Failed to fetch power scores' }, { status: 500 });
  }
}