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

141 lines
/**
 * Trending Petitions API
 * GET /api/petitions/trending — cross-platform trending petition feed
 *
 * Aggregates from: UK Parliament, OpenPetition EU, Decidim, GovTrack, MoveOn
 * Supports filtering by platform, category, climber status, min signatures
 */

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

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

  const url = req.nextUrl;
  const platform = url.searchParams.get('platform');
  const category = url.searchParams.get('category');
  const isClimber = url.searchParams.get('is_climber');
  const minSignatures = parseInt(url.searchParams.get('min_signatures') || '0');
  const search = url.searchParams.get('q');
  const limit = Math.min(parseInt(url.searchParams.get('limit') || '30'), 100);
  const offset = parseInt(url.searchParams.get('offset') || '0');
  const sortBy = url.searchParams.get('sort') || 'signatures'; // signatures, growth, recent
  const orgFilterMode = url.searchParams.get('org') === 'true' || url.searchParams.get('norma') === 'true';

  // Load org-specific focus categories from brand data
  const brand = await getBrand();
  const ORG_CATEGORIES = brand.focusAreas.length > 0
    ? brand.focusAreas
    : [
        'education', 'Education', 'debt_cancellation', 'consumer_protection',
        'repayment', 'collections', 'healthcare', 'Healthcare', 'Health and Well-Being',
        'government', 'Government and Politics', 'social_security', 'housing',
        'poverty', 'labor', 'Child and Family Well-Being', 'public_services',
      ];

  try {
    const conditions = [];
    const values: unknown[] = [];
    let idx = 1;

    // Server-side org filter: only show categories matching org focus areas
    if (orgFilterMode && !platform && !category) {
      // Build dynamic ILIKE clauses from brand search keywords
      const searchKeywords = brand.searchKeywords.length > 0
        ? brand.searchKeywords.slice(0, 6)
        : ['student', 'debt', 'loan', 'education', 'tuition', 'forgive'];
      const ilikeClauses = searchKeywords.map(kw => `title ILIKE '%${kw.replace(/'/g, "''")}%'`).join(' OR ');
      conditions.push(`(category = ANY($${idx++}) OR ${ilikeClauses})`);
      values.push(ORG_CATEGORIES);
    }

    if (platform) {
      conditions.push(`platform = $${idx++}`);
      values.push(platform);
    }
    if (category) {
      conditions.push(`category = $${idx++}`);
      values.push(category);
    }
    if (isClimber === 'true') {
      conditions.push(`is_climber = true`);
    }
    if (minSignatures > 0) {
      conditions.push(`signature_count >= $${idx++}`);
      values.push(minSignatures);
    }
    if (search) {
      conditions.push(`(title ILIKE $${idx} OR description ILIKE $${idx})`);
      values.push(`%${search}%`);
      idx++;
    }

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

    // Sort order
    let orderBy = 'signature_count DESC NULLS LAST';
    if (sortBy === 'growth') orderBy = 'growth_rate DESC NULLS LAST';
    if (sortBy === 'recent') orderBy = 'scraped_at DESC';

    const { rows: petitions } = await query(
      `SELECT id, platform, petition_id, title, description, target,
              signature_count, signature_goal, growth_rate, growth_pct,
              rank, category, url, creator, created_date,
              is_climber, scraped_at
       FROM trending_petitions
       ${where}
       ORDER BY ${orderBy}
       LIMIT $${idx++} OFFSET $${idx++}`,
      [...values, limit, offset],
    );

    // Total count
    const { rows: countRows } = await query(
      `SELECT COUNT(*) AS total FROM trending_petitions ${where}`,
      values,
    );

    // Platform breakdown
    const { rows: platforms } = await query(
      `SELECT platform, COUNT(*) AS count, SUM(signature_count) AS total_signatures,
              COUNT(CASE WHEN is_climber THEN 1 END) AS climbers
       FROM trending_petitions
       GROUP BY platform
       ORDER BY total_signatures DESC NULLS LAST`,
    );

    // Category breakdown
    const { rows: categories } = await query(
      `SELECT category, COUNT(*) AS count
       FROM trending_petitions
       WHERE category IS NOT NULL
       GROUP BY category
       ORDER BY count DESC`,
    );

    return NextResponse.json({
      petitions,
      total: Number(countRows[0]?.total || 0),
      platforms: platforms.map((p) => ({
        platform: p.platform,
        count: Number(p.count),
        totalSignatures: Number(p.total_signatures || 0),
        climbers: Number(p.climbers),
      })),
      categories: categories.map((c) => ({
        category: c.category,
        count: Number(c.count),
      })),
      limit,
      offset,
    });
  } catch (err) {
    console.error('[trending] GET error:', (err as Error).message);
    return NextResponse.json({ error: 'Failed to fetch trending petitions' }, { status: 500 });
  }
}