Dashboard de Métricas em Tempo Real

// src/http/routes/get-month-revenue.ts
import { and, eq, gte, sql, sum, lte } from "drizzle-orm";
import { db } from "@/application/infra/db/connection";
import { orders } from "@/application/infra/db/schema";
import { auth } from "../auth";
import { UnauthorizedError } from "../errors/unauthorized-error";
import dayjs from "dayjs";

export const getMonthRevenue = auth.get(
  "/metrics/month-revenue",
  async ({ getCurrentUser }) => {
    const { restaurantId } = await getCurrentUser();
    if (!restaurantId) {
      throw new UnauthorizedError();
    }
    
    // Configurar período de análise
    const today = dayjs();
    const lastMonth = today.subtract(1, "month");
    const startOfLastMonth = lastMonth.startOf("month");

    // Query com agrupamento por mês/ano
    const monthRevenue = await db
      .select({
        monthWithYear: sql<string>`TO_CHAR(${orders.createdAt}, 'YYYY-MM')`,
        total: sum(orders.priceInCents).mapWith(Number)
      })
      .from(orders)
      .where(
        and(
          eq(orders.restaurantId, restaurantId),
          gte(orders.createdAt, startOfLastMonth.toDate()),
          lte(orders.createdAt, today.toDate()),
        ),
      )
      .groupBy(sql`TO_CHAR(${orders.createdAt}, 'YYYY-MM')`);
    
    // Calcular percentual de crescimento
    const diffFromLastMonth =
      currentMonthRevenue && lastMonthRevenue
        ? (currentMonthRevenue.total * 100) / lastMonthRevenue.total
        : null;
    
    return {
      currentMonthRevenue,
      lastMonthRevenue,
      diffFromLastMonth: diffFromLastMonth
        ? Number((diffFromLastMonth - 100).toFixed(2))
        : 0
    };

export default
  },
);

// src/http/routes/get-popular-products.ts
import { sum, eq, desc, type SQLWrapper } from "drizzle-orm";
import { db } from "@/application/infra/db/connection";
import { orderItems, orders, products } from "@/application/infra/db/schema";
import { auth } from "@/http/auth";
import { UnauthorizedError } from "@/http/errors/unauthorized-error";

export const getPopularProducts = auth.get(
  "/metrics/popular-products",
  async ({ getCurrentUser }) => {
    const { restaurantId } = await getCurrentUser();
    if (!restaurantId) {
      throw new UnauthorizedError();
    }
    
    // Query com múltiplos JOINs
    const popularProducts = await db
      .select({
        productName: products.name,
        amount: sum(orderItems.quantity).mapWith(Number)
      })
      .from(orderItems)
      .leftJoin(orders, eq(orders.id, orderItems.orderId))
      .leftJoin(products, eq(products.id, orderItems.productId))
      .where(eq(orders.restaurantId, restaurantId))
      .groupBy(products.name)
      .orderBy((fields: { amount: SQLWrapper }) => {
        return desc(fields.amount);
      })
      .limit(50);

    return popularProducts;
  },
);