← back to Norma
app/api/social/scoreboard/route.ts
195 lines
import { NextRequest, NextResponse } from 'next/server';
import { query } from '@/lib/db';
import { requireRole } from '@/lib/require-role';
/**
* GET /api/social/scoreboard?period=week&limit=20
*
* Returns a ranked leaderboard of competitor accounts.
*
* Strategy:
* 1. Check social_scoreboard for a cached row computed today.
* 2. If found, serve from cache (with enriched competitor + top_post data).
* 3. If not, compute live from social_competitor_posts, persist to
* social_scoreboard (upsert), then return the fresh results.
*
* Ranking metric: total_engagement (sum of likes + comments*2 + shares*3)
* over the selected period window.
*/
type LiveRow = {
competitor_id: string;
handle: string;
display_name: string | null;
avatar_url: string | null;
platform: string;
category: string;
expertise_area: string | null;
total_posts: number;
total_engagement: number;
avg_engagement: string;
breaking_news_count: number;
top_post_id: string | null;
rank?: number;
};
type TopPost = {
id: string;
body: string;
published_at: string;
likes: number;
shares: number;
comments: number;
};
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 period = searchParams.get('period') || 'week';
const limit = Math.min(parseInt(searchParams.get('limit') || '20', 10), 100);
const validPeriods: Record<string, string> = {
day: '1 day',
week: '7 days',
month: '30 days',
};
if (!validPeriods[period]) {
return NextResponse.json({ error: 'period must be day, week, or month' }, { status: 400 });
}
// ── 1. Check for a cached scoreboard computed today ──────────────────
const cached = await query(
`SELECT
s.*,
c.handle AS handle,
c.display_name AS display_name,
c.avatar_url AS avatar_url,
c.platform AS platform,
c.category AS category,
c.expertise_area AS expertise_area,
tp.body AS top_post_body,
tp.published_at AS top_post_published_at,
tp.likes AS top_post_likes,
tp.shares AS top_post_shares,
tp.comments AS top_post_comments
FROM social_scoreboard s
JOIN social_competitors c ON c.id = s.competitor_id
LEFT JOIN social_competitor_posts tp ON tp.id = s.top_post_id
WHERE s.period = $1
AND s.computed_date = CURRENT_DATE
ORDER BY s.rank ASC
LIMIT $2`,
[period, limit]
);
if (cached.rows.length > 0) {
return NextResponse.json({
scoreboard: cached.rows,
count: cached.rows.length,
period,
cached: true,
computed_at: cached.rows[0].computed_at,
});
}
// ── 2. Compute live from social_competitor_posts ──────────────────────
const interval = validPeriods[period];
const live = await query<LiveRow>(
`SELECT
c.id AS competitor_id,
c.handle,
c.display_name,
c.avatar_url,
c.platform,
c.category,
c.expertise_area,
COUNT(p.id)::int AS total_posts,
COALESCE(SUM(p.likes + p.comments * 2 + p.shares * 3), 0)::int
AS total_engagement,
COALESCE(AVG(p.engagement_score), 0) AS avg_engagement,
COUNT(p.id) FILTER (WHERE p.is_breaking_news = true)::int
AS breaking_news_count,
(SELECT id FROM social_competitor_posts
WHERE competitor_id = c.id
AND published_at >= NOW() - ($1)::interval
ORDER BY engagement_score DESC
LIMIT 1) AS top_post_id
FROM social_competitors c
LEFT JOIN social_competitor_posts p
ON p.competitor_id = c.id
AND p.published_at >= NOW() - ($1)::interval
WHERE c.is_active = true
GROUP BY c.id, c.handle, c.display_name, c.avatar_url, c.platform, c.category, c.expertise_area
ORDER BY total_engagement DESC
LIMIT $2`,
[interval, limit]
);
if (live.rows.length === 0) {
return NextResponse.json({ scoreboard: [], count: 0, period, cached: false });
}
const liveRows = live.rows;
// ── 3. Persist to scoreboard cache (upsert) ───────────────────────────
for (let i = 0; i < liveRows.length; i++) {
const row = liveRows[i];
const rank = i + 1;
await query(
`INSERT INTO social_scoreboard
(competitor_id, period, total_posts, total_engagement, avg_engagement,
breaking_news_count, top_post_id, rank, computed_date, computed_at)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, CURRENT_DATE, NOW())
ON CONFLICT (competitor_id, period, computed_date) DO UPDATE SET
total_posts = EXCLUDED.total_posts,
total_engagement = EXCLUDED.total_engagement,
avg_engagement = EXCLUDED.avg_engagement,
breaking_news_count = EXCLUDED.breaking_news_count,
top_post_id = EXCLUDED.top_post_id,
rank = EXCLUDED.rank,
computed_at = NOW()`,
[row.competitor_id, period, row.total_posts, row.total_engagement,
row.avg_engagement, row.breaking_news_count, row.top_post_id ?? null, rank]
);
liveRows[i].rank = rank;
}
// ── 4. Enrich with top post body ──────────────────────────────────────
const topPostIds = liveRows
.map((r) => r.top_post_id)
.filter((id): id is string => id !== null);
const topPostMap = new Map<string, TopPost>();
if (topPostIds.length > 0) {
const postRows = await query<TopPost>(
`SELECT id, body, published_at, likes, shares, comments
FROM social_competitor_posts
WHERE id = ANY($1)`,
[topPostIds]
);
for (const p of postRows.rows) {
topPostMap.set(p.id, p);
}
}
const enriched = liveRows.map((r) => ({
...r,
top_post_body: r.top_post_id ? (topPostMap.get(r.top_post_id)?.body ?? null) : null,
top_post_published_at: r.top_post_id ? (topPostMap.get(r.top_post_id)?.published_at ?? null) : null,
top_post_likes: r.top_post_id ? (topPostMap.get(r.top_post_id)?.likes ?? null) : null,
top_post_shares: r.top_post_id ? (topPostMap.get(r.top_post_id)?.shares ?? null) : null,
top_post_comments: r.top_post_id ? (topPostMap.get(r.top_post_id)?.comments ?? null) : null,
}));
return NextResponse.json({
scoreboard: enriched,
count: enriched.length,
period,
cached: false,
computed_at: new Date().toISOString(),
});
}