`app/api/scraper/petitions/route.ts`

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

/**
 * GET /api/scraper/petitions
 * Returns trending petitions from the database.
 *
 * Query params:
 *   ?platform=change_org     — filter by platform (change_org, moveon, care2)
 *   &climbers_only=true      — only return petitions marked as climbers
 *   &category=education      — filter by category
 *   &limit=50                — max results (default 50, max 500)
 */
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 platform = searchParams.get('platform');
    const climbersOnly = searchParams.get('climbers_only');
    const category = searchParams.get('category');
    const limitParam = searchParams.get('limit');

    const limit = Math.min(Math.max(parseInt(limitParam || '50', 10) || 50, 1), 500);

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

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

    if (climbersOnly === 'true') {
      conditions.push(`is_climber = true`);
    }

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

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

    const result = await query(
      `SELECT
         *,
         CASE
           WHEN growth_rate > 0 AND signature_count > 0 THEN
             ROUND(growth_rate * 100.0 / GREATEST(signature_count, 1), 2)
           ELSE 0
         END AS computed_growth_velocity,
         EXTRACT(EPOCH FROM (NOW() - COALESCE(created_date, scraped_at))) / 86400 AS days_since_creation
       FROM trending_petitions
       ${whereClause}
       ORDER BY
         is_climber DESC,
         signature_count DESC NULLS LAST
       LIMIT $${paramIndex}`,
      [...values, limit],
    );

    // Summary stats
    const statsResult = await query(
      `SELECT
         COUNT(*) AS total_petitions,
         COUNT(*) FILTER (WHERE is_climber = true) AS climber_count,
         COUNT(DISTINCT platform) AS platform_count,
         AVG(signature_count) FILTER (WHERE signature_count > 0) AS avg_signatures,
         MAX(signature_count) AS max_signatures,
         AVG(growth_rate) FILTER (WHERE growth_rate > 0) AS avg_growth_rate
       FROM trending_petitions`,
    );

    return NextResponse.json({
      petitions: result.rows,
      stats: statsResult.rows[0] ?? null,
      count: result.rows.length,
    });
  } catch (err) {
    console.error('[api/scraper/petitions] GET error:', (err as Error).message);
    return NextResponse.json({ error: 'Failed to fetch trending petitions' }, { status: 500 });
  }
}