Skip to content

Your favorite shift management application!

7.5 KB
Raw Blame
import { NextResponse } from "next/server";
import { db } from "@/lib/db";
import {
  shifts,
  externalSyncs,
  shiftPresets,
  calendars,
} from "@/lib/db/schema";
import { eq, and, gte, lte, or, isNull } from "drizzle-orm";
import { getSessionUser } from "@/lib/auth/sessions";
import { hasCapability } from "@/lib/auth/permissions";
import { formatDateToLocal, parseLocalDate } from "@/lib/date-utils";
import { withShiftSegments } from "@/lib/shift-time-ranges";
import { toTimeRanges, sumRangeDurations } from "@/lib/time-ranges";
import {
  startOfWeek,
  endOfWeek,
  startOfMonth,
  endOfMonth,
  startOfYear,
  endOfYear,
} from "date-fns";

// GET shift statistics for a calendar
export async function GET(request: Request) {
  try {
    const { searchParams } = new URL(request.url);
    const calendarId = searchParams.get("calendarId");
    const period = searchParams.get("period") || "month"; // week, month, year
    const date = searchParams.get("date"); // reference date for the period

    if (!calendarId) {
      return NextResponse.json(
        { error: "Calendar ID is required" },
        { status: 400 }
      );
    }

    const user = await getSessionUser(request.headers);

    // Fetch calendar
    const [calendar] = await db
      .select()
      .from(calendars)
      .where(eq(calendars.id, calendarId));

    if (!calendar) {
      return NextResponse.json(
        { error: "Calendar not found" },
        { status: 404 }
      );
    }

    // Check read permission (works for both authenticated users and guests)
    const hasAccess = await hasCapability(user?.id, calendarId, "viewStats");
    if (!hasAccess) {
      return NextResponse.json(
        { error: "Insufficient permissions" },
        { status: 403 }
      );
    }

    let referenceDate = new Date();
    if (date) {
      try {
        referenceDate = parseLocalDate(date);
      } catch {
        return NextResponse.json(
          { error: "Invalid date format" },
          { status: 400 }
        );
      }
    }

    let startDate: Date;
    let endDate: Date;

    // Determine date range based on period
    switch (period) {
      case "week":
        startDate = startOfWeek(referenceDate, { weekStartsOn: 1 }); // Monday
        endDate = endOfWeek(referenceDate, { weekStartsOn: 1 });
        break;
      case "year":
        startDate = startOfYear(referenceDate);
        endDate = endOfYear(referenceDate);
        break;
      case "month":
      default:
        startDate = startOfMonth(referenceDate);
        endDate = endOfMonth(referenceDate);
        break;
    }

    // Fetch shifts for the period, excluding shifts from iCloud syncs or presets that are hidden from stats
    const result = await db
      .select({
        id: shifts.id,
        title: shifts.title,
        date: shifts.date,
        startTime: shifts.startTime,
        endTime: shifts.endTime,
        isAllDay: shifts.isAllDay,
      })
      .from(shifts)
      .leftJoin(externalSyncs, eq(shifts.externalSyncId, externalSyncs.id))
      .leftJoin(shiftPresets, eq(shifts.presetId, shiftPresets.id))
      .where(
        and(
          eq(shifts.calendarId, calendarId),
          gte(shifts.date, startDate),
          lte(shifts.date, endDate),
          // Exclude shifts from iCloud syncs that are hidden or hidden from stats
          or(
            isNull(shifts.externalSyncId),
            and(
              eq(externalSyncs.isHidden, false),
              eq(externalSyncs.hideFromStats, false)
            )
          ),
          // Exclude shifts from presets that are hidden from stats
          or(isNull(shifts.presetId), eq(shiftPresets.hideFromStats, false))
        )
      );

    const resultWithSegments = calendar.splitShiftsEnabled
      ? await withShiftSegments(result)
      : result;

    // Group by title and calculate stats
    const statsMap = new Map<string, { count: number; totalMinutes: number }>();
    const dailyStats = new Map<
      string,
      { count: number; totalMinutes: number }
    >();
    let minDuration = Infinity;
    let maxDuration = 0;
    let nonAllDayShiftCount = 0; // Track shifts with duration for accurate average

    resultWithSegments.forEach((shift) => {
      const existing = statsMap.get(shift.title) || {
        count: 0,
        totalMinutes: 0,
      };
      existing.count++;
      const duration = shift.isAllDay ? 0 : sumRangeDurations(toTimeRanges(shift));
      existing.totalMinutes += duration;
      statsMap.set(shift.title, existing);

      // Count non-all-day shifts for average calculation
      if (!shift.isAllDay) {
        nonAllDayShiftCount++;
      }

      // Track min/max duration (exclude all-day shifts)
      if (!shift.isAllDay && duration > 0) {
        minDuration = Math.min(minDuration, duration);
        maxDuration = Math.max(maxDuration, duration);
      }

      // Daily breakdown for trend analysis
      const shiftDate = shift.date;
      if (shiftDate) {
        try {
          // Handle both Date objects and Unix timestamps (in seconds for SQLite)
          const date =
            shiftDate instanceof Date
              ? shiftDate
              : typeof shiftDate === "number"
              ? new Date(shiftDate * 1000) // Convert seconds to milliseconds
              : new Date(shiftDate);

          if (!isNaN(date.getTime())) {
            const dateKey = formatDateToLocal(date);
            const dailyExisting = dailyStats.get(dateKey) || {
              count: 0,
              totalMinutes: 0,
            };
            dailyExisting.count++;
            dailyExisting.totalMinutes += duration;
            dailyStats.set(dateKey, dailyExisting);
          }
        } catch {
          // Skip invalid dates
        }
      }
    });

    // Transform result to object format
    const stats = Array.from(statsMap.entries()).reduce(
      (acc, [title, data]) => {
        acc[title] = {
          count: data.count,
          totalMinutes: data.totalMinutes,
        };
        return acc;
      },
      {} as Record<string, { count: number; totalMinutes: number }>
    );

    // Calculate total duration and averages
    const totalMinutes = Object.values(stats).reduce(
      (sum, data) => sum + data.totalMinutes,
      0
    );

    const totalShifts = resultWithSegments.length;
    const avgMinutesPerShift =
      nonAllDayShiftCount > 0 ? totalMinutes / nonAllDayShiftCount : 0;

    // Calculate days with shifts for accurate daily average
    const daysWithShifts = dailyStats.size;
    const avgShiftsPerDay =
      daysWithShifts > 0 ? totalShifts / daysWithShifts : 0;
    const avgMinutesPerDay =
      daysWithShifts > 0 ? totalMinutes / daysWithShifts : 0;

    // Convert daily stats to array for trend visualization
    const trendData = Array.from(dailyStats.entries())
      .map(([date, data]) => ({
        date,
        count: data.count,
        totalMinutes: data.totalMinutes,
      }))
      .sort((a, b) => a.date.localeCompare(b.date));

    return NextResponse.json({
      period,
      startDate: formatDateToLocal(startDate),
      endDate: formatDateToLocal(endDate),
      stats,
      totalMinutes,
      totalShifts,
      avgMinutesPerShift: Math.round(avgMinutesPerShift),
      avgShiftsPerDay: Math.round(avgShiftsPerDay * 10) / 10,
      avgMinutesPerDay: Math.round(avgMinutesPerDay),
      minDuration: minDuration === Infinity ? 0 : minDuration,
      maxDuration,
      daysWithShifts,
      trendData,
    });
  } catch (error) {
    console.error("Failed to fetch shift statistics:", error);
    return NextResponse.json(
      { error: "Failed to fetch shift statistics" },
      { status: 500 }
    );
  }
}