← back to Norma

app/api/social/analytics/dashboard/route.ts

145 lines

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

export const dynamic = 'force-dynamic';

/**
 * GET /api/social/analytics/dashboard
 * Query params:
 *   days     number  default 30
 *   platform string  default 'all'
 *
 * Returns { trends, top_posts, heatmap, summary }
 */
export async function GET(request: NextRequest) {
  try {
    const { searchParams } = new URL(request.url);
    const daysParam = parseInt(searchParams.get('days') ?? '30', 10);
    const days = Number.isFinite(daysParam) && daysParam > 0 ? Math.min(daysParam, 365) : 30;
    const platform = (searchParams.get('platform') ?? 'all').toLowerCase();

    const platformFilter = platform !== 'all';

    // Trends: daily aggregation across platforms (optionally filtered)
    const trendsSql = `
      SELECT
        date::text AS date,
        SUM(followers)::bigint        AS total_followers,
        SUM(total_likes)::bigint      AS total_likes,
        SUM(total_comments)::bigint   AS total_comments,
        SUM(total_shares)::bigint     AS total_shares,
        SUM(total_reach)::bigint      AS total_reach,
        AVG(engagement_rate)::float   AS engagement_rate
      FROM social_analytics
      WHERE date >= (CURRENT_DATE - ($1::int - 1))
        ${platformFilter ? 'AND platform = $2' : ''}
      GROUP BY date
      ORDER BY date ASC
    `;
    const trendsParams: unknown[] = platformFilter ? [days, platform] : [days];
    const trendsResult = await query(trendsSql, trendsParams);

    // Top posts: top 5 by likes+comments+shares from metrics JSONB
    const topPostsSql = `
      SELECT
        p.id,
        p.body,
        p.platform,
        p.published_at,
        p.media_urls,
        p.hashtags,
        p.link_url,
        COALESCE(p.likes, 0)    AS likes,
        COALESCE(p.comments, 0) AS comments,
        COALESCE(p.shares, 0)   AS shares,
        COALESCE(p.views, 0)    AS views,
        (COALESCE(p.likes, 0) + COALESCE(p.comments, 0) + COALESCE(p.shares, 0)) AS engagement,
        a.account_name,
        a.display_name
      FROM social_posts p
      LEFT JOIN social_accounts a ON a.id = p.account_id
      WHERE p.status = 'published'
        AND p.published_at IS NOT NULL
        AND p.published_at >= (NOW() - ($1 || ' days')::interval)
        ${platformFilter ? 'AND p.platform = $2' : ''}
      ORDER BY engagement DESC
      LIMIT 5
    `;
    const topPostsParams: unknown[] = platformFilter
      ? [String(days), platform]
      : [String(days)];
    const topPostsResult = await query(topPostsSql, topPostsParams);

    // Heatmap: 7 days of week x 24 hours
    const heatmapSql = `
      SELECT
        EXTRACT(DOW  FROM published_at)::int AS day_of_week,
        EXTRACT(HOUR FROM published_at)::int AS hour,
        AVG(COALESCE(likes, 0) + COALESCE(comments, 0) + COALESCE(shares, 0))::float AS avg_engagement,
        COUNT(*)::int AS post_count
      FROM social_posts
      WHERE status = 'published'
        AND published_at IS NOT NULL
        AND published_at >= (NOW() - ($1 || ' days')::interval)
        ${platformFilter ? 'AND platform = $2' : ''}
      GROUP BY day_of_week, hour
      ORDER BY day_of_week, hour
    `;
    const heatmapParams: unknown[] = platformFilter
      ? [String(days), platform]
      : [String(days)];
    const heatmapResult = await query(heatmapSql, heatmapParams);

    // Summary
    const summarySql = `
      SELECT
        COUNT(*)::int AS total_posts,
        COALESCE(SUM(COALESCE(likes, 0) + COALESCE(comments, 0) + COALESCE(shares, 0)), 0)::bigint AS total_engagement,
        COALESCE(AVG(NULLIF(
          CASE WHEN COALESCE(reach, 0) > 0
               THEN (COALESCE(likes, 0) + COALESCE(comments, 0) + COALESCE(shares, 0))::float / reach * 100.0
               ELSE 0 END, 0)), 0)::float AS avg_engagement_rate
      FROM social_posts
      WHERE status = 'published'
        AND published_at IS NOT NULL
        AND published_at >= (NOW() - ($1 || ' days')::interval)
        ${platformFilter ? 'AND platform = $2' : ''}
    `;
    const summaryParams: unknown[] = platformFilter
      ? [String(days), platform]
      : [String(days)];
    const summaryResult = await query(summarySql, summaryParams);

    const growthSql = `
      SELECT COALESCE(SUM(followers_delta), 0)::bigint AS follower_growth
      FROM social_analytics
      WHERE date >= (CURRENT_DATE - ($1::int - 1))
        ${platformFilter ? 'AND platform = $2' : ''}
    `;
    const growthParams: unknown[] = platformFilter ? [days, platform] : [days];
    const growthResult = await query(growthSql, growthParams);

    const summaryRow = summaryResult.rows[0] ?? {};
    const growthRow = growthResult.rows[0] ?? {};

    return NextResponse.json({
      trends: trendsResult.rows,
      top_posts: topPostsResult.rows,
      heatmap: heatmapResult.rows,
      summary: {
        total_posts: Number(summaryRow.total_posts ?? 0),
        total_engagement: Number(summaryRow.total_engagement ?? 0),
        avg_engagement_rate: Number(summaryRow.avg_engagement_rate ?? 0),
        follower_growth: Number(growthRow.follower_growth ?? 0),
      },
      range: { days, platform },
    });
  } catch (err) {
    console.error('[analytics/dashboard] error:', (err as Error).message);
    return NextResponse.json(
      { error: 'Failed to load analytics dashboard' },
      { status: 500 },
    );
  }
}