app-padel/apps/web/app/api/dashboard/stats/route.ts

import { NextRequest, NextResponse } from 'next/server';
import { getServerSession } from 'next-auth';
import { authOptions } from '@/lib/auth';
import { db } from '@/lib/db';

// GET /api/dashboard/stats - Get dashboard statistics
export async function GET(request: NextRequest) {
  try {
    const session = await getServerSession(authOptions);

    if (!session?.user) {
      return NextResponse.json(
        { error: 'Unauthorized' },
        { status: 401 }
      );
    }

    const { searchParams } = new URL(request.url);
    const siteId = searchParams.get('siteId') || session.user.siteId;
    const dateParam = searchParams.get('date');

    // Default to today
    const targetDate = dateParam ? new Date(dateParam) : new Date();
    const startOfDay = new Date(targetDate);
    startOfDay.setHours(0, 0, 0, 0);
    const endOfDay = new Date(targetDate);
    endOfDay.setHours(23, 59, 59, 999);

    // Build where clause for site filtering
    interface SiteFilter {
      organizationId: string;
      id?: string;
    }

    const siteFilter: SiteFilter = {
      organizationId: session.user.organizationId,
    };

    if (siteId) {
      siteFilter.id = siteId;
    }

    // Get sites matching the filter
    const sites = await db.site.findMany({
      where: siteFilter,
      select: { id: true, openTime: true, closeTime: true },
    });

    const siteIds = sites.map(s => s.id);

    // 1. Today's bookings count
    const todayBookings = await db.booking.count({
      where: {
        siteId: { in: siteIds },
        startTime: {
          gte: startOfDay,
          lte: endOfDay,
        },
        status: {
          in: ['PENDING', 'CONFIRMED', 'COMPLETED'],
        },
      },
    });

    // 2. Today's revenue (from sales + booking payments)
    // Get sales from today
    const todaySales = await db.sale.aggregate({
      where: {
        createdBy: {
          organizationId: session.user.organizationId,
        },
        createdAt: {
          gte: startOfDay,
          lte: endOfDay,
        },
        ...(siteId
          ? {
              cashRegister: {
                siteId,
              },
            }
          : {}),
      },
      _sum: {
        total: true,
      },
    });

    // Get booking payments from today
    const todayBookingPayments = await db.booking.aggregate({
      where: {
        siteId: { in: siteIds },
        startTime: {
          gte: startOfDay,
          lte: endOfDay,
        },
        status: 'COMPLETED',
      },
      _sum: {
        paidAmount: true,
      },
    });

    const salesTotal = Number(todaySales._sum.total || 0);
    const bookingPaymentsTotal = Number(todayBookingPayments._sum.paidAmount || 0);
    const todayRevenue = salesTotal + bookingPaymentsTotal;

    // 3. Calculate occupancy rate
    // Get all courts for the sites
    const courts = await db.court.findMany({
      where: {
        siteId: { in: siteIds },
        isActive: true,
        status: 'AVAILABLE',
      },
      include: {
        site: {
          select: {
            openTime: true,
            closeTime: true,
          },
        },
      },
    });

    // Calculate total available hours per court for the day
    let totalAvailableSlots = 0;
    let bookedSlots = 0;

    for (const court of courts) {
      // Parse open/close times (format: "HH:MM")
      const openTime = court.site.openTime || '08:00';
      const closeTime = court.site.closeTime || '22:00';

      const [openHour] = openTime.split(':').map(Number);
      const [closeHour] = closeTime.split(':').map(Number);

      // Each slot is 1 hour
      const availableHours = closeHour - openHour;
      totalAvailableSlots += availableHours;

      // Count booked hours for this court today
      const courtBookings = await db.booking.findMany({
        where: {
          courtId: court.id,
          startTime: {
            gte: startOfDay,
            lte: endOfDay,
          },
          status: {
            in: ['PENDING', 'CONFIRMED', 'COMPLETED'],
          },
        },
        select: {
          startTime: true,
          endTime: true,
        },
      });

      // Calculate booked hours
      for (const booking of courtBookings) {
        const durationMs = booking.endTime.getTime() - booking.startTime.getTime();
        const durationHours = durationMs / (1000 * 60 * 60);
        bookedSlots += durationHours;
      }
    }

    const occupancyRate = totalAvailableSlots > 0
      ? Math.round((bookedSlots / totalAvailableSlots) * 100)
      : 0;

    // 4. Active memberships count
    const now = new Date();
    const activeMembers = await db.membership.count({
      where: {
        status: 'ACTIVE',
        endDate: {
          gte: now,
        },
        plan: {
          organizationId: session.user.organizationId,
        },
      },
    });

    // 5. Pending bookings (awaiting payment/confirmation)
    const pendingBookings = await db.booking.count({
      where: {
        siteId: { in: siteIds },
        status: 'PENDING',
        startTime: {
          gte: startOfDay,
        },
      },
    });

    // 6. Upcoming tournaments (next 30 days)
    const thirtyDaysFromNow = new Date();
    thirtyDaysFromNow.setDate(thirtyDaysFromNow.getDate() + 30);

    const upcomingTournaments = await db.tournament.count({
      where: {
        organizationId: session.user.organizationId,
        ...(siteId ? { siteId } : {}),
        startDate: {
          gte: now,
          lte: thirtyDaysFromNow,
        },
        status: {
          in: ['DRAFT', 'REGISTRATION_OPEN', 'REGISTRATION_CLOSED', 'IN_PROGRESS'],
        },
      },
    });

    // Get court occupancy details for chart
    const courtOccupancy = await Promise.all(
      courts.map(async (court) => {
        const openTime = court.site.openTime || '08:00';
        const closeTime = court.site.closeTime || '22:00';

        const [openHour] = openTime.split(':').map(Number);
        const [closeHour] = closeTime.split(':').map(Number);
        const availableHours = closeHour - openHour;

        const courtBookings = await db.booking.findMany({
          where: {
            courtId: court.id,
            startTime: {
              gte: startOfDay,
              lte: endOfDay,
            },
            status: {
              in: ['PENDING', 'CONFIRMED', 'COMPLETED'],
            },
          },
          select: {
            startTime: true,
            endTime: true,
          },
        });

        let bookedHours = 0;
        for (const booking of courtBookings) {
          const durationMs = booking.endTime.getTime() - booking.startTime.getTime();
          bookedHours += durationMs / (1000 * 60 * 60);
        }

        return {
          courtId: court.id,
          courtName: court.name,
          availableHours,
          bookedHours: Math.round(bookedHours * 10) / 10,
          occupancyPercent: availableHours > 0
            ? Math.round((bookedHours / availableHours) * 100)
            : 0,
        };
      })
    );

    // Get recent bookings for the day
    const recentBookings = await db.booking.findMany({
      where: {
        siteId: { in: siteIds },
        startTime: {
          gte: startOfDay,
          lte: endOfDay,
        },
      },
      include: {
        court: {
          select: {
            id: true,
            name: true,
          },
        },
        client: {
          select: {
            id: true,
            firstName: true,
            lastName: true,
          },
        },
      },
      orderBy: {
        startTime: 'asc',
      },
      take: 10,
    });

    return NextResponse.json({
      stats: {
        todayBookings,
        todayRevenue,
        occupancyRate,
        activeMembers,
        pendingBookings,
        upcomingTournaments,
      },
      courtOccupancy,
      recentBookings: recentBookings.map((booking) => ({
        id: booking.id,
        startTime: booking.startTime,
        endTime: booking.endTime,
        status: booking.status,
        court: booking.court,
        client: booking.client
          ? {
              id: booking.client.id,
              name: `${booking.client.firstName} ${booking.client.lastName}`,
            }
          : null,
      })),
      date: targetDate.toISOString().split('T')[0],
    });
  } catch (error) {
    console.error('Error fetching dashboard stats:', error);
    return NextResponse.json(
      { error: 'Error fetching dashboard statistics' },
      { status: 500 }
    );
  }
}