← back to Norma

app/api/analytics/charts/route.ts

175 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/analytics/charts ──────────────────────────────────────────────
// Aggregates chart data from journalists, petitions, donations, topics, etc.

let queryErrors = 0;

function safeQuery(label: string, sql: string, params?: unknown[]) {
  return query(sql, params).catch((err) => {
    console.error(`[analytics/charts] ${label} failed:`, err.message);
    queryErrors++;
    return { rows: [] };
  });
}

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

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

  try {
    const [
      journalistsByOutlet,
      topReportersByArticles,
      articlesByMonth,
      topicFrequency,
      petitionsByPlatform,
      petitionsByCategory,
      topPetitions,
      donationsBySource,
      donationsByMonth,
      sentimentBreakdown,
      orgsByType,
      beatDistribution,
    ] = await Promise.all([
      // 1. Journalists by outlet (bar chart)
      safeQuery('journalistsByOutlet', `
        SELECT outlet, COUNT(*)::int as count
        FROM journalists WHERE is_active = true
        GROUP BY outlet ORDER BY count DESC LIMIT 12
      `),

      // 2. Top reporters by article count (horizontal bar)
      safeQuery('topReportersByArticles', `
        SELECT j.name, j.outlet, COUNT(ja.id)::int as article_count
        FROM journalists j
        LEFT JOIN journalist_articles ja ON ja.journalist_id = j.id
        WHERE j.is_active = true
        GROUP BY j.id, j.name, j.outlet
        ORDER BY article_count DESC LIMIT 10
      `),

      // 3. Articles published by month (area chart)
      safeQuery('articlesByMonth', `
        SELECT
          TO_CHAR(published_date, 'YYYY-MM') as month,
          COUNT(*)::int as count
        FROM journalist_articles
        WHERE published_date IS NOT NULL
          AND published_date >= NOW() - INTERVAL '12 months'
        GROUP BY TO_CHAR(published_date, 'YYYY-MM')
        ORDER BY month ASC
      `),

      // 4. Most covered topics (bar chart) — 12-month window
      safeQuery('topicFrequency', `
        SELECT unnest_topic as topic, COUNT(*)::int as count
        FROM journalist_articles,
             LATERAL unnest(topics) AS unnest_topic
        WHERE unnest_topic IS NOT NULL
          AND published_date >= NOW() - INTERVAL '12 months'
        GROUP BY unnest_topic
        ORDER BY count DESC LIMIT 15
      `),

      // 5. Petitions by platform (pie chart)
      safeQuery('petitionsByPlatform', `
        SELECT platform, COUNT(*)::int as count,
               COALESCE(SUM(signature_count), 0)::int as total_signatures
        FROM petitions
        WHERE ($1::text IS NULL OR org_id = $1)
        GROUP BY platform ORDER BY count DESC
      `, [orgId]),

      // 6. Petitions by category (bar chart)
      safeQuery('petitionsByCategory', `
        SELECT category, COUNT(*)::int as count,
               COALESCE(SUM(signature_count), 0)::int as total_signatures
        FROM petitions
        WHERE category IS NOT NULL AND ($1::text IS NULL OR org_id = $1)
        GROUP BY category ORDER BY count DESC LIMIT 10
      `, [orgId]),

      // 7. Top petitions by signatures (horizontal bar)
      safeQuery('topPetitions', `
        SELECT title, platform, COALESCE(signature_count, 0)::int as signature_count
        FROM petitions
        WHERE ($1::text IS NULL OR org_id = $1)
        ORDER BY signature_count DESC NULLS LAST
        LIMIT 8
      `, [orgId]),

      // 8. Donations by source (pie chart)
      safeQuery('donationsBySource', `
        SELECT source, COUNT(*)::int as count,
               COALESCE(SUM(amount), 0)::numeric as total_amount
        FROM donations
        WHERE ($1::text IS NULL OR org_id = $1)
        GROUP BY source ORDER BY total_amount DESC
      `, [orgId]),

      // 9. Donations over time (area chart)
      safeQuery('donationsByMonth', `
        SELECT
          TO_CHAR(donated_at, 'YYYY-MM') as month,
          COUNT(*)::int as count,
          COALESCE(SUM(amount), 0)::numeric as total_amount
        FROM donations
        WHERE donated_at >= NOW() - INTERVAL '12 months'
          AND ($1::text IS NULL OR org_id = $1)
        GROUP BY TO_CHAR(donated_at, 'YYYY-MM')
        ORDER BY month ASC
      `, [orgId]),

      // 10. Article sentiment breakdown (pie chart)
      safeQuery('sentimentBreakdown', `
        SELECT sentiment, COUNT(*)::int as count
        FROM journalist_articles
        WHERE sentiment IS NOT NULL
        GROUP BY sentiment ORDER BY count DESC
      `),

      // 11. Organizations by type (pie chart)
      safeQuery('orgsByType', `
        SELECT type, COUNT(*)::int as count
        FROM organizations
        WHERE type IS NOT NULL
        GROUP BY type ORDER BY count DESC LIMIT 8
      `),

      // 12. Journalist beat distribution (pie chart)
      safeQuery('beatDistribution', `
        SELECT beat, COUNT(*)::int as count
        FROM journalists
        WHERE beat IS NOT NULL AND is_active = true
        GROUP BY beat ORDER BY count DESC LIMIT 10
      `),
    ]);

    return NextResponse.json({
      journalistsByOutlet: journalistsByOutlet.rows,
      topReportersByArticles: topReportersByArticles.rows,
      articlesByMonth: articlesByMonth.rows,
      topicFrequency: topicFrequency.rows,
      petitionsByPlatform: petitionsByPlatform.rows,
      petitionsByCategory: petitionsByCategory.rows,
      topPetitions: topPetitions.rows,
      donationsBySource: donationsBySource.rows,
      donationsByMonth: donationsByMonth.rows,
      sentimentBreakdown: sentimentBreakdown.rows,
      orgsByType: orgsByType.rows,
      beatDistribution: beatDistribution.rows,
      _queryErrors: queryErrors,
    });
  } catch (err) {
    console.error('[analytics/charts] Error:', (err as Error).message);
    return NextResponse.json({ error: (err as Error).message }, { status: 500 });
  }
}