← back to Grant

app/api/dashboard/route.ts

118 lines

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

export async function GET(request: NextRequest) {
  const session = verifyAuthWithOrg(request);
  if (!session) return NextResponse.json({ error: 'Unauthorized' }, { status: 401 });

  try {
    const orgId = await resolveOrgId(session);
    if (!orgId) {
      return NextResponse.json({
        org_name: 'No Organization',
        grant_count: 0,
        total_funding: 0,
        news_count: 0,
        collab_count: 0,
        proposal_count: 0,
        upcoming_deadlines: [],
        recent_activity: [],
      });
    }
    const nameRes = await query('SELECT name FROM organizations WHERE id = $1', [orgId]);
    const orgName = nameRes.rows[0]?.name ?? 'Organization';

    // Run all queries in parallel
    const [
      grantCountRes,
      fundingRes,
      newsCountRes,
      collabCountRes,
      proposalCountRes,
      deadlinesRes,
      recentGrantsRes,
      recentCollabsRes,
      recentProposalsRes,
    ] = await Promise.all([
      query('SELECT COUNT(*)::int as count FROM grants WHERE org_id = $1', [orgId]),
      query('SELECT COALESCE(SUM(amount_awarded), 0)::numeric as total FROM grants WHERE org_id = $1 AND amount_awarded > 0', [orgId]),
      query('SELECT COUNT(*)::int as count FROM news_items WHERE org_id = $1', [orgId]),
      query('SELECT COUNT(*)::int as count FROM collaborations WHERE org_id = $1', [orgId]),
      query('SELECT COUNT(*)::int as count FROM grant_proposals WHERE org_id = $1', [orgId]),
      query(
        `SELECT id, title, funder, deadline, status, priority, ai_fit_score
         FROM grants
         WHERE org_id = $1 AND deadline > NOW()
         ORDER BY deadline ASC
         LIMIT 5`,
        [orgId],
      ),
      query(
        `SELECT id, title, funder, status, created_at, 'grant' as entity_type
         FROM grants WHERE org_id = $1
         ORDER BY created_at DESC LIMIT 5`,
        [orgId],
      ),
      query(
        `SELECT id, name, organization, collab_type, status, created_at, 'collaboration' as entity_type
         FROM collaborations WHERE org_id = $1
         ORDER BY created_at DESC LIMIT 5`,
        [orgId],
      ),
      query(
        `SELECT gp.id, gp.subject, gp.proposal_type, gp.status, gp.created_at, 'proposal' as entity_type, g.title as grant_title
         FROM grant_proposals gp
         JOIN grants g ON g.id = gp.grant_id
         WHERE gp.org_id = $1
         ORDER BY gp.created_at DESC LIMIT 5`,
        [orgId],
      ),
    ]);

    // Merge and sort recent activity
    const recentActivity = [
      ...recentGrantsRes.rows.map((r) => ({
        id: r.id,
        type: 'grant' as const,
        label: `Grant discovered: ${r.title}`,
        detail: r.funder,
        status: r.status,
        created_at: r.created_at,
      })),
      ...recentCollabsRes.rows.map((r) => ({
        id: r.id,
        type: 'collaboration' as const,
        label: `Collaborator: ${r.name}`,
        detail: r.organization || r.collab_type,
        status: r.status,
        created_at: r.created_at,
      })),
      ...recentProposalsRes.rows.map((r) => ({
        id: r.id,
        type: 'proposal' as const,
        label: `Proposal: ${r.subject}`,
        detail: r.grant_title,
        status: r.status,
        created_at: r.created_at,
      })),
    ]
      .sort((a, b) => new Date(b.created_at).getTime() - new Date(a.created_at).getTime())
      .slice(0, 8);

    return NextResponse.json({
      org_name: orgName,
      grant_count: grantCountRes.rows[0].count,
      total_funding: Number(fundingRes.rows[0].total),
      news_count: newsCountRes.rows[0].count,
      collab_count: collabCountRes.rows[0].count,
      proposal_count: proposalCountRes.rows[0].count,
      upcoming_deadlines: deadlinesRes.rows,
      recent_activity: recentActivity,
    });
  } catch (err) {
    console.error('[dashboard GET]', err);
    return NextResponse.json({ error: 'Failed to fetch dashboard' }, { status: 500 });
  }
}