`app/api/dashboard/route.ts`

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

export const dynamic = 'force-dynamic';

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

  try {
    // Query all 4 databases in parallel
    const [sdccData, grantData, pattyData, freddyData] = await Promise.all([
      // SDCC
      Promise.all([
        query('sdcc', 'SELECT COUNT(*) as count FROM drafts'),
        query('sdcc', 'SELECT COUNT(*) as count FROM news_items'),
        query('sdcc', 'SELECT COUNT(*) as count FROM petitions'),
        query('sdcc', 'SELECT COUNT(*) as count FROM collaborations'),
        query('sdcc', 'SELECT COUNT(*) as count FROM partners'),
        query('sdcc', 'SELECT COUNT(*) as count FROM old_leads'),
        query('sdcc', 'SELECT COUNT(*) as count FROM library_items'),
      ]),
      // Grant
      Promise.all([
        query('grant', 'SELECT COUNT(*) as count FROM grants'),
        query('grant', 'SELECT COUNT(*) as count FROM grant_proposals'),
        query('grant', 'SELECT COUNT(*) as count FROM news_items'),
        query('grant', 'SELECT COUNT(*) as count FROM collaborations'),
        query('grant', 'SELECT COALESCE(SUM(amount_awarded), 0) as total FROM grants WHERE amount_awarded IS NOT NULL'),
      ]),
      // Patty
      Promise.all([
        query('patty', 'SELECT COUNT(*) as count FROM petitions'),
        query('patty', 'SELECT COALESCE(SUM(signature_count), 0) as total FROM petitions'),
        query('patty', 'SELECT COUNT(*) as count FROM email_campaigns'),
        query('patty', 'SELECT COUNT(*) as count FROM email_subscribers'),
        query('patty', 'SELECT COUNT(*) as count FROM petition_templates'),
      ]),
      // Freddy
      Promise.all([
        query('freddy', 'SELECT COUNT(*) as count FROM causes'),
        query('freddy', 'SELECT COUNT(*) as count FROM organizations'),
        query('freddy', 'SELECT COUNT(*) as count FROM granters'),
        query('freddy', 'SELECT COUNT(*) as count FROM matches'),
        query('freddy', 'SELECT COUNT(*) as count FROM contacts'),
        query('freddy', 'SELECT COALESCE(SUM(commission_amount), 0) as total FROM revenue'),
      ]),
    ]);

    const sdcc = {
      drafts: parseInt(sdccData[0].rows[0].count),
      news: parseInt(sdccData[1].rows[0].count),
      petitions: parseInt(sdccData[2].rows[0].count),
      collaborations: parseInt(sdccData[3].rows[0].count),
      partners: parseInt(sdccData[4].rows[0].count),
      leads: parseInt(sdccData[5].rows[0].count),
      library: parseInt(sdccData[6].rows[0].count),
    };

    const grant = {
      grants: parseInt(grantData[0].rows[0].count),
      proposals: parseInt(grantData[1].rows[0].count),
      news: parseInt(grantData[2].rows[0].count),
      collaborations: parseInt(grantData[3].rows[0].count),
      total_funding: parseFloat(grantData[4].rows[0].total) || 0,
    };

    const patty = {
      petitions: parseInt(pattyData[0].rows[0].count),
      signatures: parseInt(pattyData[1].rows[0].total),
      campaigns: parseInt(pattyData[2].rows[0].count),
      subscribers: parseInt(pattyData[3].rows[0].count),
      templates: parseInt(pattyData[4].rows[0].count),
    };

    const freddy = {
      causes: parseInt(freddyData[0].rows[0].count),
      organizations: parseInt(freddyData[1].rows[0].count),
      granters: parseInt(freddyData[2].rows[0].count),
      matches: parseInt(freddyData[3].rows[0].count),
      contacts: parseInt(freddyData[4].rows[0].count),
      revenue_total: parseFloat(freddyData[5].rows[0].total) || 0,
    };

    const totalRecords =
      sdcc.drafts + sdcc.news + sdcc.petitions + sdcc.collaborations + sdcc.partners + sdcc.leads + sdcc.library +
      grant.grants + grant.proposals + grant.news + grant.collaborations +
      patty.petitions + patty.campaigns + patty.subscribers + patty.templates +
      freddy.causes + freddy.organizations + freddy.granters + freddy.matches + freddy.contacts;

    return NextResponse.json({
      sdcc,
      grant,
      patty,
      freddy,
      system: {
        apps_online: 4,
        total_records: totalRecords,
      },
    });
  } catch (err) {
    console.error('[dashboard] Error:', (err as Error).message);
    return NextResponse.json(
      { error: 'Failed to fetch dashboard data' },
      { status: 500 },
    );
  }
}