← back to Norma

app/api/community/route.ts

162 lines

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

/**
 * GET /api/community
 *
 * Returns community posts (Reddit, Discord) with computed heat scores.
 * Heat score = score + (num_comments * 2) + petition mentions + news overlap
 *
 * Query params:
 *   sort: 'heat' | 'date' | 'score' (default: heat)
 *   subreddit: filter by subreddit
 *   sentiment: filter by sentiment
 *   limit: number of results (default 50, max 200)
 *   offset: pagination offset
 */
export async function GET(request: NextRequest) {
  const auth = requireRole(request, 'admin', 'staff');
  if (auth instanceof NextResponse) return auth;

  try {
    const url = new URL(request.url);
    const sort = url.searchParams.get('sort') || 'heat';
    const subreddit = url.searchParams.get('subreddit');
    const sentiment = url.searchParams.get('sentiment');
    const search = url.searchParams.get('q');
    const limit = Math.min(parseInt(url.searchParams.get('limit') || '50'), 200);
    const offset = Math.max(parseInt(url.searchParams.get('offset') || '0'), 0);

    const conditions: string[] = [];
    const values: (string | number)[] = [];
    let paramIdx = 1;

    if (subreddit) {
      conditions.push(`cp.subreddit = $${paramIdx}`);
      values.push(subreddit);
      paramIdx++;
    }

    if (sentiment) {
      conditions.push(`cp.sentiment = $${paramIdx}`);
      values.push(sentiment);
      paramIdx++;
    }

    if (search) {
      conditions.push(`(cp.title ILIKE $${paramIdx} OR cp.body ILIKE $${paramIdx})`);
      values.push(`%${search}%`);
      paramIdx++;
    }

    const whereClause = conditions.length > 0
      ? 'AND ' + conditions.join(' AND ')
      : '';

    // Heat score formula: base score + comment weight + recency boost
    const heatFormula = `
      COALESCE(cp.score, 0) +
      COALESCE(cp.num_comments, 0) * 2 +
      CASE WHEN cp.mentions_servicer IS NOT NULL AND cp.mentions_servicer != '' THEN 15 ELSE 0 END +
      CASE WHEN cp.mentions_program IS NOT NULL AND cp.mentions_program != '' THEN 10 ELSE 0 END +
      CASE WHEN cp.debt_amount IS NOT NULL THEN 5 ELSE 0 END +
      CASE WHEN cp.is_alumni = true THEN 20 ELSE 0 END +
      CASE WHEN cp.posted_at > NOW() - INTERVAL '7 days' THEN 30
           WHEN cp.posted_at > NOW() - INTERVAL '30 days' THEN 15
           ELSE 0 END
    `;

    let orderClause: string;
    switch (sort) {
      case 'date':
        orderClause = 'cp.posted_at DESC NULLS LAST';
        break;
      case 'score':
        orderClause = 'cp.score DESC NULLS LAST';
        break;
      case 'heat':
      default:
        orderClause = `(${heatFormula}) DESC`;
        break;
    }

    values.push(limit);
    values.push(offset);

    const sql = `
      SELECT
        cp.*,
        (${heatFormula}) AS heat_score
      FROM community_posts cp
      WHERE cp.platform = 'reddit'
      ${whereClause}
      ORDER BY ${orderClause}
      LIMIT $${paramIdx} OFFSET $${paramIdx + 1}
    `;

    const countSql = `
      SELECT COUNT(*) as total
      FROM community_posts cp
      WHERE cp.platform = 'reddit'
      ${whereClause}
    `;

    const [dataResult, countResult, subredditStats, sentimentStats] = await Promise.all([
      query(sql, values),
      query(countSql, values.slice(0, -2)),
      query(`
        SELECT subreddit, COUNT(*) as cnt, ROUND(AVG(score)) as avg_score
        FROM community_posts
        WHERE platform = 'reddit' AND subreddit IS NOT NULL
        GROUP BY subreddit
        ORDER BY cnt DESC
        LIMIT 15
      `),
      query(`
        SELECT sentiment, COUNT(*) as cnt
        FROM community_posts
        WHERE platform = 'reddit' AND sentiment IS NOT NULL
        GROUP BY sentiment
        ORDER BY cnt DESC
      `),
    ]);

    return NextResponse.json({
      posts: dataResult.rows,
      total: parseInt(countResult.rows[0]?.total ?? '0', 10),
      limit,
      offset,
      subreddits: subredditStats.rows,
      sentiments: sentimentStats.rows,
    });
  } catch (err) {
    console.error('[community] GET error:', (err as Error).message);
    return NextResponse.json({ error: 'Failed to fetch community data' }, { status: 500 });
  }
}

/**
 * POST /api/community
 *
 * Trigger a manual community data refresh (same as ingest endpoint but smaller).
 */
export async function POST(request: NextRequest) {
  const auth = requireRole(request, 'admin', 'staff');
  if (auth instanceof NextResponse) return auth;

  try {
    // Proxy to the ingest endpoint
    const res = await fetch(`http://127.0.0.1:7400/api/ingest/community`, {
      method: 'POST',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify({ sources: ['reddit', 'reddit_hot'] }),
    });
    const data = await res.json();
    return NextResponse.json(data);
  } catch (err) {
    console.error('[community] POST error:', (err as Error).message);
    return NextResponse.json({ error: 'Failed to refresh community data' }, { status: 500 });
  }
}