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

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

/**
 * GET /api/scraper/trending
 * Returns trending topics from the database.
 *
 * Query params:
 *   ?source=reddit           — filter by source (reddit, google_trends)
 *   &category=education      — filter by category
 *   &limit=50                — max results (default 50, max 500)
 *   &batch=latest            — specific batch ID or 'latest' (default: latest)
 */
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 source = searchParams.get('source');
    const category = searchParams.get('category');
    const limitParam = searchParams.get('limit');
    const batch = searchParams.get('batch') || 'latest';

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

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

    // Resolve batch
    if (batch === 'latest') {
      conditions.push(`scrape_batch = (SELECT scrape_batch FROM trending_topics ORDER BY scraped_at DESC LIMIT 1)`);
    } else {
      conditions.push(`scrape_batch = $${paramIndex}`);
      values.push(batch);
      paramIndex++;
    }

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

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

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

    // Left join with petition_suggestions to show if a suggestion exists for that topic
    const result = await query(
      `SELECT
         t.*,
         ps.id AS suggestion_id,
         ps.title AS suggestion_title,
         ps.status AS suggestion_status,
         ps.urgency AS suggestion_urgency
       FROM trending_topics t
       LEFT JOIN petition_suggestions ps
         ON LOWER(ps.title) LIKE '%' || LOWER(SUBSTRING(t.topic FROM 1 FOR 40)) || '%'
         AND ps.status != 'rejected'
       ${whereClause}
       ORDER BY t.score DESC NULLS LAST
       LIMIT $${paramIndex}`,
      [...values, limit],
    );

    // Also get batch metadata
    const batchInfo = await query(
      `SELECT
         scrape_batch,
         COUNT(*) AS total_topics,
         MIN(scraped_at) AS batch_start,
         MAX(scraped_at) AS batch_end,
         COUNT(DISTINCT source) AS source_count,
         COUNT(DISTINCT category) FILTER (WHERE category IS NOT NULL) AS category_count
       FROM trending_topics
       WHERE scrape_batch = (
         SELECT scrape_batch FROM trending_topics ORDER BY scraped_at DESC LIMIT 1
       )
       GROUP BY scrape_batch`,
    );

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