Skip to content

Your favorite shift management application!

8.9 KB
Raw Blame
import { NextRequest, NextResponse } from "next/server";
import { db } from "@/lib/db";
import {
  calendars,
  user,
  shifts,
  calendarNotes,
  shiftPresets,
  calendarShares,
  calendarAccessTokens,
  calendarPermissionBundles,
  externalSyncs,
} from "@/lib/db/schema";
import { and, asc, count, desc, eq, or, sql, type SQL } from "drizzle-orm";
import {
  alias,
  type SQLiteColumn,
  type SQLiteTable,
} from "drizzle-orm/sqlite-core";
import { isAdmin } from "@/lib/auth/admin";
import {
  getValidatedAdminUser,
  isErrorResponse,
} from "@/lib/auth/admin-helpers";
import {
  CALENDAR_CONTENT_FILTERS,
  CALENDAR_OWNER_FILTERS,
  CALENDAR_SORT_FIELDS,
  SORT_ORDERS,
  clampPage,
  containsPattern,
  parsePaging,
  pickParam,
  type CalendarListCounts,
} from "@/lib/admin-list";

// A calendar is orphaned when it has no owner row, whether owner_id is null or dangling
const orphaned = sql`${user.id} is null`;
const hasUserShares = sql`exists (select 1 from ${calendarShares} where ${calendarShares.calendarId} = ${calendars.id})`;
const hasTokens = sql`exists (select 1 from ${calendarAccessTokens} where ${calendarAccessTokens.calendarId} = ${calendars.id})`;
const hasSyncs = sql`exists (select 1 from ${externalSyncs} where ${externalSyncs.calendarId} = ${calendars.id})`;
const isShared = sql`(${hasUserShares} or ${hasTokens})`;

// The admin panel shows the guest bundle's actual name/seedKey instead of
// collapsing it to a read/write tier. The join also doubles as the "has
// guest access" check for sorting/filtering — an orphaned guestBundleId
// (pointing at a deleted bundle) produces a null row here, which correctly
// counts as no guest access, same as getUserAccessibleCalendars() elsewhere.
const guestBundleTable = alias(calendarPermissionBundles, "guest_bundle");

/** Timestamps defaulted by SQLite are "YYYY-MM-DD HH:MM:SS" text in UTC; app-written ones are unix seconds. */
function toDate(value: unknown): Date {
  if (typeof value === "number") return new Date(value * 1000);
  if (typeof value === "string") {
    if (/^\d+$/.test(value)) return new Date(Number(value) * 1000);
    const utc = new Date(`${value.replace(" ", "T")}Z`);
    return isNaN(utc.getTime()) ? new Date(value) : utc;
  }
  return new Date();
}

/** Per-row count as a correlated subquery, so one page is one statement. */
function countFor(table: SQLiteTable, column: SQLiteColumn) {
  return sql<number>`(select count(*) from ${table} where ${column} = ${calendars.id})`;
}

/**
 * Admin Calendar Management API
 *
 * GET /api/admin/calendars
 * One page of calendars plus instance-wide counts. Orphaned calendars always come first.
 *
 * Query Parameters:
 * - search: Calendar name, owner name or owner email contains (case-insensitive)
 * - content: all | shared | synced (default: all; shared = user shares or share links)
 * - owner: all | orphaned | with-owner (default: all)
 * - sort: name | createdAt | owner | shiftsCount | sharesCount | externalSyncsCount | guestBundle (default: createdAt)
 * - order: asc | desc (default: desc)
 * - page: 1-based page, clamped to the last page (default: 1)
 * - limit: Page size (default: 25, max: 100)
 *
 * Response: { items, total, counts, page, limit } — see lib/admin-list.ts
 *
 * Permission: Admin or Superadmin only
 */
export async function GET(request: NextRequest) {
  try {
    const currentUser = await getValidatedAdminUser(request);
    if (isErrorResponse(currentUser)) return currentUser;

    if (!isAdmin(currentUser)) {
      return NextResponse.json(
        { error: "Admin access required" },
        { status: 403 }
      );
    }

    const { searchParams } = request.nextUrl;
    const search = (searchParams.get("search") ?? "").trim();
    const content = pickParam(searchParams.get("content"), CALENDAR_CONTENT_FILTERS, "all");
    const owner = pickParam(searchParams.get("owner"), CALENDAR_OWNER_FILTERS, "all");
    const sort = pickParam(searchParams.get("sort"), CALENDAR_SORT_FIELDS, "createdAt");
    const order = pickParam(searchParams.get("order"), SORT_ORDERS, "desc");
    const paging = parsePaging(searchParams);

    const conditions: SQL[] = [];

    if (owner === "orphaned") conditions.push(orphaned);
    if (owner === "with-owner") conditions.push(sql`not ${orphaned}`);
    if (content === "shared") conditions.push(isShared);
    if (content === "synced") conditions.push(hasSyncs);

    if (search) {
      const pattern = containsPattern(search);
      conditions.push(
        or(
          sql`lower(${calendars.name}) like ${pattern} escape '\\'`,
          sql`lower(${user.name}) like ${pattern} escape '\\'`,
          sql`lower(${user.email}) like ${pattern} escape '\\'`
        )!
      );
    }

    const where = conditions.length > 0 ? and(...conditions) : undefined;

    const sortExpressions: Record<typeof sort, SQL> = {
      name: sql`lower(${calendars.name})`,
      createdAt: sql`${calendars.createdAt}`,
      owner: sql`lower(coalesce(${user.name}, ${user.email}, ''))`,
      shiftsCount: sql`(select count(*) from ${shifts} where ${shifts.calendarId} = ${calendars.id})`,
      sharesCount: sql`((select count(*) from ${calendarShares} where ${calendarShares.calendarId} = ${calendars.id}) + (select count(*) from ${calendarAccessTokens} where ${calendarAccessTokens.calendarId} = ${calendars.id}))`,
      externalSyncsCount: sql`(select count(*) from ${externalSyncs} where ${externalSyncs.calendarId} = ${calendars.id})`,
      guestBundle: sql`case when ${guestBundleTable.id} is null then 0 else 1 end`,
    };
    const direction = order === "asc" ? asc : desc;

    const [[{ total }], [countRow], [shiftsRow]] = await Promise.all([
      db
        .select({ total: count() })
        .from(calendars)
        .leftJoin(user, eq(calendars.ownerId, user.id))
        .where(where),
      db
        .select({
          total: count(),
          orphaned: sql<number>`coalesce(sum(${orphaned}), 0)`,
          shared: sql<number>`coalesce(sum(${isShared}), 0)`,
          synced: sql<number>`coalesce(sum(${hasSyncs}), 0)`,
        })
        .from(calendars)
        .leftJoin(user, eq(calendars.ownerId, user.id)),
      db.select({ total: count() }).from(shifts),
    ]);

    const { page, offset } = clampPage(paging.page, paging.limit, total);

    const rows = await db
      .select({
        id: calendars.id,
        name: calendars.name,
        color: calendars.color,
        ownerId: calendars.ownerId,
        guestBundleRowId: guestBundleTable.id,
        guestBundleName: guestBundleTable.name,
        guestBundleSeedKey: guestBundleTable.seedKey,
        // Raw values: rows created with the SQL default hold text, not unix seconds
        createdAt: sql<unknown>`${calendars.createdAt}`,
        updatedAt: sql<unknown>`${calendars.updatedAt}`,
        ownerUserId: user.id,
        ownerName: user.name,
        ownerEmail: user.email,
        ownerImage: user.image,
        shiftsCount: countFor(shifts, shifts.calendarId),
        notesCount: countFor(calendarNotes, calendarNotes.calendarId),
        presetsCount: countFor(shiftPresets, shiftPresets.calendarId),
        userSharesCount: countFor(calendarShares, calendarShares.calendarId),
        tokenSharesCount: countFor(calendarAccessTokens, calendarAccessTokens.calendarId),
        externalSyncsCount: countFor(externalSyncs, externalSyncs.calendarId),
      })
      .from(calendars)
      .leftJoin(user, eq(calendars.ownerId, user.id))
      .leftJoin(guestBundleTable, eq(calendars.guestBundleId, guestBundleTable.id))
      .where(where)
      .orderBy(
        asc(sql`case when ${orphaned} then 0 else 1 end`),
        direction(sortExpressions[sort]),
        direction(calendars.id)
      )
      .limit(paging.limit)
      .offset(offset);

    const items = rows.map((row) => ({
      id: row.id,
      name: row.name,
      color: row.color,
      ownerId: row.ownerId,
      owner: row.ownerUserId
        ? { name: row.ownerName, email: row.ownerEmail, image: row.ownerImage }
        : null,
      guestBundle: row.guestBundleRowId
        ? { id: row.guestBundleRowId, name: row.guestBundleName!, seedKey: row.guestBundleSeedKey }
        : null,
      createdAt: toDate(row.createdAt),
      updatedAt: toDate(row.updatedAt),
      shiftsCount: Number(row.shiftsCount),
      notesCount: Number(row.notesCount),
      presetsCount: Number(row.presetsCount),
      sharesCount: Number(row.userSharesCount) + Number(row.tokenSharesCount),
      externalSyncsCount: Number(row.externalSyncsCount),
    }));

    const counts: CalendarListCounts = {
      total: countRow.total,
      orphaned: Number(countRow.orphaned),
      shared: Number(countRow.shared),
      synced: Number(countRow.synced),
      shifts: shiftsRow.total,
    };

    return NextResponse.json({
      items,
      total,
      counts,
      page,
      limit: paging.limit,
    });
  } catch (error) {
    console.error("[Admin Calendars API] Error:", error);
    return NextResponse.json(
      { error: "Failed to fetch calendars" },
      { status: 500 }
    );
  }
}