const staffDateExpression = sql<Date>`date_trunc(${timeFrame}, ${CalendarEvent.startTimeUTC})`;
  // 1. Calculate staffing costs
  const staffingCosts = await ctx.db
    .select({
      date: staffDateExpression.as("date"),
      value: sum(
        sql`(${CalendarEvent.endTimeUTC}::timestamp - ${CalendarEvent.startTimeUTC}::timestamp) * ${ClinicStaffProfile.costPerHour} / 3600`,
      ).as("value"),
    })
    .from(Appointment)
    .innerJoin(CalendarEvent, eq(Appointment.calendarEventId, CalendarEvent.id))
    .innerJoin(
      ClinicStaffProfile,
      eq(Appointment.providerId, ClinicStaffProfile.userId),
    )
    .where(
      and(
        eq(Appointment.clinicId, clinicId),
        between(CalendarEvent.startTimeUTC, startDate, endDate),
        isNotNull(ClinicStaffProfile.costPerHour),
      ),
    )
    .groupBy(staffDateExpression);

const staffDateExpression = sql<Date>`date_trunc(${timeFrame}, ${CalendarEvent.startTimeUTC})`;
  // 1. Calculate staffing costs
  const staffingCosts = await ctx.db
    .select({
      date: staffDateExpression.as("date"),
      value: sum(
        sql`(${CalendarEvent.endTimeUTC}::timestamp - ${CalendarEvent.startTimeUTC}::timestamp) * ${ClinicStaffProfile.costPerHour} / 3600`,
      ).as("value"),
    })
    .from(Appointment)
    .innerJoin(CalendarEvent, eq(Appointment.calendarEventId, CalendarEvent.id))
    .innerJoin(
      ClinicStaffProfile,
      eq(Appointment.providerId, ClinicStaffProfile.userId),
    )
    .where(
      and(
        eq(Appointment.clinicId, clinicId),
        between(CalendarEvent.startTimeUTC, startDate, endDate),
        isNotNull(ClinicStaffProfile.costPerHour),
      ),
    )
    .groupBy(staffDateExpression);

select date_trunc($1, "calendar_event"."start_time_utc") as "date", sum(("calendar_event"."end_time_utc"::timestamp - "calendar_event"."start_time_utc"::timestamp) * "clinic_staff_profile"."cost_per_hour" / 3600) as "value" from "appointment" inner join "calendar_event" on "appointment"."calendar_event_id" = "calendar_event"."id" inner join "clinic_staff_profile" on "appointment"."provider_id" = "clinic_staff_profile"."user_id" where ("appointment"."clinic_id" = $2 and "calendar_event"."start_time_utc" between $3 and $4 and "clinic_staff_profile"."cost_per_hour" is not null) group by date_trunc($5, "calendar_event"."start_time_utc") -- params: ["day", "itt4i7h048d4xllh185k4txi", "2024-06-23T19:05:11.869Z", "2024-07-23T19:05:11.870Z", "day"]

select date_trunc($1, "calendar_event"."start_time_utc") as "date", sum(("calendar_event"."end_time_utc"::timestamp - "calendar_event"."start_time_utc"::timestamp) * "clinic_staff_profile"."cost_per_hour" / 3600) as "value" from "appointment" inner join "calendar_event" on "appointment"."calendar_event_id" = "calendar_event"."id" inner join "clinic_staff_profile" on "appointment"."provider_id" = "clinic_staff_profile"."user_id" where ("appointment"."clinic_id" = $2 and "calendar_event"."start_time_utc" between $3 and $4 and "clinic_staff_profile"."cost_per_hour" is not null) group by date_trunc($5, "calendar_event"."start_time_utc") -- params: ["day", "itt4i7h048d4xllh185k4txi", "2024-06-23T19:05:11.869Z", "2024-07-23T19:05:11.870Z", "day"]

const clinicRolesSQ = ctx.db
        .select({ id: ClinicRole.id })
        .from(ClinicRole)
        .innerJoin(ClinicUserRole, eq(ClinicRole.id, ClinicUserRole.roleId))
        .where(eq(ClinicUserRole.userId, ctx.session.user.id))
        .as("rolesSQ");

      const tasks = await ctx.db
        .select({
          count: sql<number>`count(DISTINCT Task.id) over()`.as("count"),
          task: Task,
        })
        .from(Task)
        .leftJoin(AssignedTask, eq(Task.id, AssignedTask.taskId))
        .where(
          and(
            eq(Task.clinicId, clinicId),
            input.completed
              ? isNotNull(Task.completedBy)
              : isNull(Task.completedBy),
            input.search
              ? or(
                  ilike(Task.name, `%${input.search}%`),
                  ilike(Task.description, `%${input.search}%`),
                )
              : undefined,
            or(
              eq(AssignedTask.userId, ctx.session.user.id),
              inArray(AssignedTask.roleId, clinicRolesSQ),
            ),
          ),
        )
        .orderBy(input.sortOrder === "desc" ? desc(sortBy) : sortBy)
        .limit(limit)
        .offset(offset)
        .execute();

const clinicRolesSQ = ctx.db
        .select({ id: ClinicRole.id })
        .from(ClinicRole)
        .innerJoin(ClinicUserRole, eq(ClinicRole.id, ClinicUserRole.roleId))
        .where(eq(ClinicUserRole.userId, ctx.session.user.id))
        .as("rolesSQ");

      const tasks = await ctx.db
        .select({
          count: sql<number>`count(DISTINCT Task.id) over()`.as("count"),
          task: Task,
        })
        .from(Task)
        .leftJoin(AssignedTask, eq(Task.id, AssignedTask.taskId))
        .where(
          and(
            eq(Task.clinicId, clinicId),
            input.completed
              ? isNotNull(Task.completedBy)
              : isNull(Task.completedBy),
            input.search
              ? or(
                  ilike(Task.name, `%${input.search}%`),
                  ilike(Task.description, `%${input.search}%`),
                )
              : undefined,
            or(
              eq(AssignedTask.userId, ctx.session.user.id),
              inArray(AssignedTask.roleId, clinicRolesSQ),
            ),
          ),
        )
        .orderBy(input.sortOrder === "desc" ? desc(sortBy) : sortBy)
        .limit(limit)
        .offset(offset)
        .execute();

select count(DISTINCT Task.id) over() as "count", "task"."id", "task"."name", "task"."description", "task"."due_date", "task"."clinic_id", "task"."appointment_id", "task"."created_by", "task"."completed_at", "task"."completed_by", "task"."created_at", "task"."updated_at" from "task" left join "assigned_task" on "task"."id" = "assigned_task"."task_id" where ("task"."clinic_id" = $1 and "task"."completed_by" is null and ("assigned_task"."user_id" = $2 or "assigned_task"."role_id" in $3)) order by "task"."created_at" limit $4 -- params: ["id1", "id2", {}, 150]

select count(DISTINCT Task.id) over() as "count", "task"."id", "task"."name", "task"."description", "task"."due_date", "task"."clinic_id", "task"."appointment_id", "task"."created_by", "task"."completed_at", "task"."completed_by", "task"."created_at", "task"."updated_at" from "task" left join "assigned_task" on "task"."id" = "assigned_task"."task_id" where ("task"."clinic_id" = $1 and "task"."completed_by" is null and ("assigned_task"."user_id" = $2 or "assigned_task"."role_id" in $3)) order by "task"."created_at" limit $4 -- params: ["id1", "id2", {}, 150]