`app/api/geo/trends/route.ts`

206 lines
/**
 * GET /api/geo/trends
 *
 * Returns time-series aggregated data from cfpb_complaints (by date_received)
 * and community_posts (by posted_at), grouped by day.
 *
 * Query parameters:
 *   ?range=30d    — Time range: 7d, 30d, 90d, 180d, 365d, all (default: 30d)
 *   ?state=CA     — Filter by state abbreviation (optional)
 *
 * Includes basic anomaly detection: if any day's count exceeds
 * mean + 2*stddev, it is flagged as an anomaly.
 *
 * Requires authentication.
 */

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

const VALID_RANGES: Record<string, number | null> = {
  '7d': 7,
  '30d': 30,
  '90d': 90,
  '180d': 180,
  '365d': 365,
  'all': null,
};

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

  const { searchParams } = new URL(request.url);
  const range = searchParams.get('range') || '30d';
  const state = searchParams.get('state');

  if (!(range in VALID_RANGES)) {
    return NextResponse.json(
      { error: `Invalid range. Must be one of: ${Object.keys(VALID_RANGES).join(', ')}` },
      { status: 400 },
    );
  }

  const days = VALID_RANGES[range];

  try {
    // ── Build CFPB complaints time series ──
    const cfpbConditions: string[] = [];
    const cfpbValues: unknown[] = [];
    let cfpbParamIdx = 1;

    if (days !== null) {
      cfpbConditions.push(`date_received >= CURRENT_DATE - $${cfpbParamIdx++}::integer`);
      cfpbValues.push(days);
    }

    if (state) {
      cfpbConditions.push(`state = $${cfpbParamIdx++}`);
      cfpbValues.push(state.toUpperCase());
    }

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

    const { rows: cfpbDaily } = await query<{
      day: string;
      complaint_count: string;
    }>(
      `SELECT
         date_received AS day,
         COUNT(*) AS complaint_count
       FROM cfpb_complaints
       ${cfpbWhere}
       GROUP BY date_received
       ORDER BY date_received`,
      cfpbValues,
    );

    // ── Build community posts time series ──
    const commConditions: string[] = [];
    const commValues: unknown[] = [];
    let commParamIdx = 1;

    if (days !== null) {
      commConditions.push(`posted_at >= CURRENT_DATE - $${commParamIdx++}::integer`);
      commValues.push(days);
    }

    // community_posts doesn't have a state column directly, so we skip state filter for it
    // unless we can text-match. For now, we return all community posts in the range.

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

    const { rows: communityDaily } = await query<{
      day: string;
      post_count: string;
      frustrated_count: string;
    }>(
      `SELECT
         DATE(posted_at) AS day,
         COUNT(*) AS post_count,
         COUNT(*) FILTER (WHERE sentiment = 'frustrated') AS frustrated_count
       FROM community_posts
       ${commWhere}
       GROUP BY DATE(posted_at)
       ORDER BY DATE(posted_at)`,
      commValues,
    );

    // ── Anomaly detection for CFPB complaints ──
    const cfpbCounts = cfpbDaily.map((r) => Number(r.complaint_count));
    const cfpbAnomalies = detectAnomalies(cfpbCounts);

    const cfpbSeries = cfpbDaily.map((row, i) => ({
      date: row.day,
      count: Number(row.complaint_count),
      is_anomaly: cfpbAnomalies.anomalyIndices.has(i),
    }));

    // ── Anomaly detection for community posts ──
    const commCounts = communityDaily.map((r) => Number(r.post_count));
    const commAnomalies = detectAnomalies(commCounts);

    const commSeries = communityDaily.map((row, i) => ({
      date: row.day,
      count: Number(row.post_count),
      frustrated_count: Number(row.frustrated_count),
      is_anomaly: commAnomalies.anomalyIndices.has(i),
    }));

    return NextResponse.json({
      filters: { range, state },
      cfpb_complaints: {
        series: cfpbSeries,
        stats: {
          total_days: cfpbSeries.length,
          total_count: cfpbCounts.reduce((a, b) => a + b, 0),
          mean: cfpbAnomalies.mean,
          stddev: cfpbAnomalies.stddev,
          threshold: cfpbAnomalies.threshold,
          anomaly_count: cfpbAnomalies.anomalyIndices.size,
        },
      },
      community_posts: {
        series: commSeries,
        stats: {
          total_days: commSeries.length,
          total_count: commCounts.reduce((a, b) => a + b, 0),
          mean: commAnomalies.mean,
          stddev: commAnomalies.stddev,
          threshold: commAnomalies.threshold,
          anomaly_count: commAnomalies.anomalyIndices.size,
        },
      },
    });
  } catch (err) {
    console.error('[geo/trends] Error:', (err as Error).message);
    return NextResponse.json(
      { error: `Failed to fetch trends: ${(err as Error).message}` },
      { status: 500 },
    );
  }
}

/* ─── Anomaly detection helper ─────────────────────────────────────────── */

interface AnomalyResult {
  mean: number;
  stddev: number;
  threshold: number;
  anomalyIndices: Set<number>;
}

function detectAnomalies(values: number[]): AnomalyResult {
  if (values.length === 0) {
    return { mean: 0, stddev: 0, threshold: 0, anomalyIndices: new Set() };
  }

  const sum = values.reduce((a, b) => a + b, 0);
  const mean = sum / values.length;

  const squaredDiffs = values.map((v) => (v - mean) ** 2);
  const variance = squaredDiffs.reduce((a, b) => a + b, 0) / values.length;
  const stddev = Math.sqrt(variance);

  const threshold = mean + 2 * stddev;

  const anomalyIndices = new Set<number>();
  for (let i = 0; i < values.length; i++) {
    if (values[i] > threshold) {
      anomalyIndices.add(i);
    }
  }

  return {
    mean: Math.round(mean * 100) / 100,
    stddev: Math.round(stddev * 100) / 100,
    threshold: Math.round(threshold * 100) / 100,
    anomalyIndices,
  };
}