`app/api/pipeline/stats/route.ts`

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

/**
 * GET /api/pipeline/stats
 * Returns aggregate statistics for the petition pipeline:
 *   - by_status: count per status
 *   - by_platform: count per platform
 *   - total: overall count
 *   - recent_24h: items created in the last 24 hours
 *   - avg_confidence: average AI confidence score
 */
export async function GET(request: NextRequest) {
  const auth = requireRole(request, 'admin', 'staff');
  if (auth instanceof NextResponse) return auth;

  const orgId = auth.role === 'admin' ? getOrgId(request) : auth.orgId;

  try {
    // Run all stat queries in parallel
    const [statusResult, platformResult, totalResult, recentResult, confidenceResult] =
      await Promise.all([
        query(
          `SELECT status, COUNT(*)::int AS count
           FROM petition_pipeline
           WHERE ($1::uuid IS NULL OR org_id = $1::uuid)
           GROUP BY status
           ORDER BY count DESC`,
          [orgId]
        ),
        query(
          `SELECT platform, COUNT(*)::int AS count
           FROM petition_pipeline
           WHERE ($1::uuid IS NULL OR org_id = $1::uuid)
           GROUP BY platform
           ORDER BY count DESC`,
          [orgId]
        ),
        query(
          `SELECT COUNT(*)::int AS total FROM petition_pipeline
           WHERE ($1::uuid IS NULL OR org_id = $1::uuid)`,
          [orgId]
        ),
        query(
          `SELECT COUNT(*)::int AS count
           FROM petition_pipeline
           WHERE created_at >= NOW() - INTERVAL '24 hours'
             AND ($1::uuid IS NULL OR org_id = $1::uuid)`,
          [orgId]
        ),
        query(
          `SELECT
             ROUND(AVG(ai_confidence)::numeric, 3) AS avg_confidence,
             COUNT(*)::int AS ai_generated
           FROM petition_pipeline
           WHERE ai_confidence IS NOT NULL
             AND ($1::uuid IS NULL OR org_id = $1::uuid)`,
          [orgId]
        ),
      ]);

    // Convert status rows to an object for easier frontend consumption
    const byStatus: Record<string, number> = {};
    for (const row of statusResult.rows) {
      byStatus[row.status] = row.count;
    }

    const byPlatform: Record<string, number> = {};
    for (const row of platformResult.rows) {
      byPlatform[row.platform] = row.count;
    }

    return NextResponse.json({
      by_status: byStatus,
      by_platform: byPlatform,
      total: totalResult.rows[0]?.total ?? 0,
      recent_24h: recentResult.rows[0]?.count ?? 0,
      ai: {
        avg_confidence: confidenceResult.rows[0]?.avg_confidence
          ? parseFloat(confidenceResult.rows[0].avg_confidence)
          : null,
        total_generated: confidenceResult.rows[0]?.ai_generated ?? 0,
      },
    });
  } catch (err) {
    console.error('[api/pipeline/stats] GET error:', (err as Error).message);
    return NextResponse.json({ error: 'Failed to fetch pipeline stats' }, { status: 500 });
  }
}