import { Injectable, NotFoundException, BadRequestException, Logger } from '@nestjs/common';
import { EntityManager } from '@mikro-orm/postgresql';
import { Order } from '../entities/order.entity';
import { OrderItem } from '../entities/order-item.entity';
import { User } from '../../users/entities/user.entity';
import { Restaurant } from '../../restaurants/entities/restaurant.entity';
import { Food } from '../../foods/entities/food.entity';
import { CartService } from '../../cart/providers/cart.service';
import { OrderStatus, OrderUserAddress, OrderCarAddress } from '../interface/order.interface';
import { PaymentMethodEnum, PaymentStatusEnum } from '../../payments/interface/payment';
import { Cart } from '../../cart/interfaces/cart.interface';
import { PaymentMethod } from '../../payments/entities/payment-method.entity';
import { PaymentsService } from '../../payments/services/payments.service';
import { DeliveryFeeTypeEnum, DeliveryMethodEnum } from '../../delivery/interface/delivery';
import { Delivery } from '../../delivery/entities/delivery.entity';
import { OrderRepository } from '../repositories/order.repository';
import { FindOrdersDto } from '../dto/find-orders.dto';
import { PaginatedResult } from 'src/common/interfaces/pagination.interface';
import { Payment } from 'src/modules/payments/entities/payment.entity';
import { InventoryService } from 'src/modules/inventory/inventory.service';
import { BulkReserveFoodDto } from 'src/modules/inventory/dto/bulk-reserve-food.dto';
import { StatusTransitionRef } from '../interface/order.interface';
import { EventEmitter2 } from '@nestjs/event-emitter';
import { OrderCreatedEvent, OrderStatusChangedEvent } from '../events/order.events';
import { OrderMessage } from 'src/common/enums/message.enum';
import { AdminCreateOrderDto } from '../dto/admin-create-order.dto';
import { normalizePhone } from 'src/modules/utils/phone.util';
type OrderItemData = { food: Food; quantity: number; unitPrice: number; discount: number };

type ValidatedCartForOrder = {
  user: User;
  restaurant: Restaurant;
  delivery: Delivery;
  userAddress: OrderUserAddress | null;
  carAddress: OrderCarAddress | null;
  paymentMethod: PaymentMethod;
  orderItemsData: OrderItemData[];
};

@Injectable()
export class OrdersService {
  private readonly logger = new Logger(OrdersService.name);

  private static readonly STATUS_TRANSITIONS: Record<OrderStatus, readonly OrderStatus[]> = {
    [OrderStatus.PENDING_PAYMENT]: [OrderStatus.PAID, OrderStatus.CANCELED, OrderStatus.PREPARING],
    [OrderStatus.PAID]: [OrderStatus.PREPARING, OrderStatus.CANCELED],
    [OrderStatus.PREPARING]: [OrderStatus.DELIVERED_TO_RECEPTIONIST, OrderStatus.DELIVERED_TO_WAITER, OrderStatus.SHIPPED, OrderStatus.CANCELED],
    [OrderStatus.DELIVERED_TO_WAITER]: [OrderStatus.COMPLETED, OrderStatus.CANCELED],
    [OrderStatus.DELIVERED_TO_RECEPTIONIST]: [OrderStatus.COMPLETED, OrderStatus.CANCELED],
    [OrderStatus.SHIPPED]: [OrderStatus.COMPLETED, OrderStatus.CANCELED],
    [OrderStatus.COMPLETED]: [OrderStatus.CANCELED],
    [OrderStatus.CANCELED]: [],
  };

  constructor(
    private readonly em: EntityManager,
    private readonly cartService: CartService,
    private readonly orderRepository: OrderRepository,
    private readonly paymentsService: PaymentsService,
    private readonly inventoryService: InventoryService,
    private readonly eventEmitter: EventEmitter2,
  ) { }

  async checkout(userId: string, restaurantId: string) {
    const cart = await this.cartService.findOneOrFail(userId, restaurantId);
    const validated = await this.validateCartForOrder(userId, restaurantId, cart);

    const order = await this.em.transactional(async em => {
      const order = em.create(Order, {
        user: validated.user,
        restaurant: validated.restaurant,
        deliveryMethod: validated.delivery,
        userAddress: validated.userAddress,
        carAddress: validated.carAddress,
        paymentMethod: validated.paymentMethod,
        couponDiscount: cart.couponDiscount || 0,
        couponDetail: cart.coupon,
        itemsDiscount: cart.itemsDiscount || 0,
        totalDiscount: cart.totalDiscount || 0,
        subTotal: cart.subTotal || 0,
        tax: cart.tax || 0,
        deliveryFee: cart.deliveryFee || 0,
        total: cart.total || 0,
        totalItems: cart.totalItems || 0,
        description: cart.description,
        tableNumber: cart.tableNumber,
        status: OrderStatus.PENDING_PAYMENT,
        history: [{ status: OrderStatus.PENDING_PAYMENT, changedAt: new Date() }],
      });

      em.persist(order);

      for (const itemData of validated.orderItemsData) {
        const { food, quantity, unitPrice, discount } = itemData;

        this.assertFoodHasSufficientStock(food, quantity);

        const totalPrice = (unitPrice - discount) * quantity;

        const orderItem = em.create(OrderItem, {
          order,
          food,
          quantity,
          unitPrice,
          discount,
          totalPrice,
        });

        em.persist(orderItem);
      }

      const payment = em.create(Payment, {
        order,
        amount: order.total,
        status: PaymentStatusEnum.Pending,
        method: order.paymentMethod.method,
        gateway: order.paymentMethod.gateway ?? null,
      });

      em.persist(payment);
      // reserve stock based on payment method.
      const bulkReserveFoodDto: BulkReserveFoodDto = {
        items: validated.orderItemsData.map(item => ({
          foodId: item.food.id,
          quantity: item.quantity,
        })),
      };
      await this.inventoryService.deductFromInventory(em, bulkReserveFoodDto);
      await em.flush();
      this.logger.debug(`Order ${order.id} created for user ${userId} (restaurant ${restaurantId})`);
      return order;
    });

    await this.cartService.clearCart(userId, restaurantId);

    const { paymentUrl } = await this.paymentsService.payOrder(order.id);
    this.eventEmitter.emit(
      OrderCreatedEvent.name,
      new OrderCreatedEvent(order.id, restaurantId, String(order?.orderNumber) || '', order.total),
    );

    return { paymentUrl, order };
  }

  async createOrderForUserByPhone(restaurantId: string, dto: AdminCreateOrderDto): Promise<Order> {
    const normalizedPhone = normalizePhone(dto.userPhone);
    let user = await this.em.findOne(User, { phone: normalizedPhone });
    if (!user) {
      user = this.em.create(User, {
        phone: normalizedPhone,
        firstName: dto.firstName?.trim() || normalizedPhone,
        lastName: dto.lastName?.trim(),
      });
      await this.em.persistAndFlush(user);
      this.logger.debug(`Auto-created user ${user.id} with phone ${normalizedPhone} via admin order`);
    }

    const [restaurant, delivery] = await Promise.all([
      this.getRestaurantOrFail(restaurantId),
      this.getDeliveryOrFail(dto.deliveryMethodId),
    ]);

    const paymentMethod = await this.getPaymentMethodOrFail(dto.paymentMethodId, restaurantId);
    this.assertPaymentMethodEnabled(paymentMethod);

    this.assertAdminDeliveryRequirements(dto, delivery);

    const orderItemsData = await this.buildOrderItemsDataFromDto(dto.items, restaurantId);

    const subTotal = orderItemsData.reduce((sum, item) => sum + item.unitPrice * item.quantity, 0);
    const itemsDiscount = orderItemsData.reduce((sum, item) => sum + item.discount * item.quantity, 0);
    const deliveryFee = delivery.deliveryFeeType === DeliveryFeeTypeEnum.FIXED ? Number(delivery.deliveryFee) : 0;
    const totalDiscount = itemsDiscount;
    const total = subTotal - totalDiscount + deliveryFee;
    const totalItems = orderItemsData.reduce((sum, item) => sum + item.quantity, 0);

    const order = await this.em.transactional(async em => {
      const order = em.create(Order, {
        user,
        restaurant,
        deliveryMethod: delivery,
        userAddress: delivery.method === DeliveryMethodEnum.DeliveryCourier ? (dto.userAddress ?? null) : null,
        carAddress: delivery.method === DeliveryMethodEnum.DeliveryCar ? (dto.carAddress ?? null) : null,
        paymentMethod,
        couponDiscount: 0,
        couponDetail: null,
        itemsDiscount,
        totalDiscount,
        subTotal,
        tax: 0,
        deliveryFee,
        total,
        totalItems,
        description: dto.description,
        tableNumber: dto.tableNumber,
        status: OrderStatus.PENDING_PAYMENT,
        history: [{ status: OrderStatus.PENDING_PAYMENT, changedAt: new Date() }],
      });

      em.persist(order);

      for (const itemData of orderItemsData) {
        const { food, quantity, unitPrice, discount } = itemData;
        this.assertFoodHasSufficientStock(food, quantity);
        const totalPrice = (unitPrice - discount) * quantity;
        const orderItem = em.create(OrderItem, { order, food, quantity, unitPrice, discount, totalPrice });
        em.persist(orderItem);
      }

      const payment = em.create(Payment, {
        order,
        amount: order.total,
        status: PaymentStatusEnum.Pending,
        method: order.paymentMethod.method,
        gateway: order.paymentMethod.gateway ?? null,
      });
      em.persist(payment);

      const bulkReserveFoodDto: BulkReserveFoodDto = {
        items: orderItemsData.map(item => ({ foodId: item.food.id, quantity: item.quantity })),
      };
      await this.inventoryService.deductFromInventory(em, bulkReserveFoodDto);
      await em.flush();

      this.logger.debug(`Admin created order ${order.id} for user ${user.id} (phone: ${normalizedPhone}, restaurant: ${restaurantId})`);
      return order;
    });

    this.eventEmitter.emit(
      OrderCreatedEvent.name,
      new OrderCreatedEvent(order.id, restaurantId, String(order?.orderNumber) || '', order.total),
    );

    return order;
  }

  /**
   * Validates cart and prepares all required data for order creation
   */
  private async validateCartForOrder(userId: string, restaurantId: string, cart: Cart): Promise<ValidatedCartForOrder> {
    this.assertCartHasItems(cart);
    this.assertCartHasDeliveryMethod(cart);
    this.assertCartHasPaymentMethod(cart);

    const [user, restaurant, delivery] = await Promise.all([
      this.getUserOrFail(userId),
      this.getRestaurantOrFail(restaurantId),
      this.getDeliveryOrFail(cart.deliveryMethodId!),
    ]);

    this.assertMeetsMinOrderForDelivery(cart, delivery);
    this.assertDeliveryMethodRequirements(cart, delivery);

    const paymentMethod = await this.getPaymentMethodOrFail(cart.paymentMethodId!, restaurantId);
    this.assertPaymentMethodEnabled(paymentMethod);

    const orderItemsData = await this.buildOrderItemsData(cart, restaurantId);

    return {
      user,
      restaurant,
      delivery,
      paymentMethod,
      userAddress: delivery.method === DeliveryMethodEnum.DeliveryCourier ? (cart?.userAddress ?? null) : null,
      carAddress: delivery.method === DeliveryMethodEnum.DeliveryCar ? (cart?.carAddress ?? null) : null,
      orderItemsData,
    };
  }

  async findAllForUser(restId: string, dto: FindOrdersDto, userId: string): Promise<PaginatedResult<Order>> {
    const result = await this.orderRepository.findAllPaginated(restId, {
      page: dto.page,
      limit: dto.limit,
      statuses: dto.statuses,
      paymentStatus: dto.paymentStatus,
      search: dto.search,
      startDate: dto.startDate,
      endDate: dto.endDate,
      orderBy: dto.orderBy,
      order: dto.order,
      userId,
    });

    return result;
  }

  async findAllForAdmin(restId: string, dto: FindOrdersDto): Promise<PaginatedResult<Order>> {
    const result = await this.orderRepository.findAllPaginated(restId, {
      page: dto.page,
      limit: dto.limit,
      statuses: dto.statuses,
      paymentStatus: dto.paymentStatus,
      search: dto.search,
      startDate: dto.startDate,
      endDate: dto.endDate,
      orderBy: dto.orderBy,
      order: dto.order,
      excludeOnlinePendingPayment: true,
    });

    return result;
  }

  async findOne(id: string, restId: string): Promise<Order> {
    const order = await this.orderRepository.findOne(
      { id, restaurant: { id: restId } },
      {
        populate: [
          'user',
          'restaurant',
          'deliveryMethod',
          'userAddress',
          'carAddress',
          'paymentMethod',
          'payments',
          'items',
          'items.food',
        ],
      },
    );
    if (!order) {
      throw new NotFoundException(OrderMessage.NOT_FOUND);
    }
    return order;
  }



  async changeOrderStatus(
    orderId: string,
    restId: string,
    toStatus: OrderStatus,
    ref: StatusTransitionRef,
    desc?: string,
  ): Promise<Order> {
    const order = await this.getOrderOrFail(orderId, restId);

    // Store previous status before changing it
    const previousStatus = order.status;

    this.assertStatusTransitionAllowed(order, toStatus, ref);

    order.status = toStatus;
    order.history.push({ status: toStatus, changedAt: new Date(), desc: desc || null });
    await this.em.persistAndFlush(order);

    this.eventEmitter.emit(
      OrderStatusChangedEvent.name,
      new OrderStatusChangedEvent(
        orderId,
        order.user?.id || '',
        String(order?.orderNumber) || '',
        restId,
        previousStatus,
        toStatus,
        ref,
      ),
    );
    return order;
  }
  /*               Helpers              */
  private assertStatusTransitionAllowed(order: Order, to: OrderStatus, ref: StatusTransitionRef) {
    const paymentMethod = order.paymentMethod?.method;
    if (!paymentMethod) {
      throw new BadRequestException(OrderMessage.PAYMENT_METHOD_MISSING);
    }

    if (!this.canTransition(order.status, to, paymentMethod, ref, order.deliveryMethod.method)) {
      throw new BadRequestException(OrderMessage.INVALID_STATUS_TRANSITION);
    }
  }

  private canTransition(from: OrderStatus, to: OrderStatus, paymentMethod: PaymentMethodEnum, ref: 'user' | 'admin', deliveryMethod: DeliveryMethodEnum) {
    if (!OrdersService.STATUS_TRANSITIONS[from]?.includes(to)) return false;

    if (to === OrderStatus.CANCELED) {
      // only allow orders with status of PENDING_PAYMENT and PAID are allowed to be canceled by user
      if (ref === 'user' && ![OrderStatus.PENDING_PAYMENT, OrderStatus.PAID].includes(from)) {
        return false;
      } else if (ref === 'admin') {
        return true;
      }
    }
    // only allow orders with status of PENDING_PAYMENT and payment
    // method of cash are allowed to move to  CONFIRMED directly
    if (
      from == OrderStatus.PENDING_PAYMENT &&
      to == OrderStatus.PREPARING &&
      paymentMethod !== PaymentMethodEnum.Cash
    ) {
      return false;
    }

    /** 
    * Only allow orders with status of PREPARING to be shipped if the delivery method is DeliveryCourier
    */
    if (
      from == OrderStatus.PREPARING &&
      to == OrderStatus.SHIPPED &&
      deliveryMethod !== DeliveryMethodEnum.DeliveryCourier
    ) {
      return false;
    }
    if (
      from == OrderStatus.PREPARING &&
      to == OrderStatus.DELIVERED_TO_WAITER &&
      deliveryMethod !== DeliveryMethodEnum.DineIn &&
      deliveryMethod !== DeliveryMethodEnum.DeliveryCar
    ) {
      return false;
    }
    if (
      from == OrderStatus.PREPARING &&
      to == OrderStatus.DELIVERED_TO_RECEPTIONIST &&
      deliveryMethod !== DeliveryMethodEnum.CustomerPickup
    ) {
      return false;
    }

    if (paymentMethod === PaymentMethodEnum.Online) {
      if (to === OrderStatus.PREPARING && from !== OrderStatus.PAID) return false;
    }

    return true;
  }

  private async getOrderOrFail(orderId: string, restId: string): Promise<Order> {
    const order = await this.em.findOne(
      Order,
      { id: orderId, restaurant: { id: restId } },
      { populate: ['paymentMethod', 'deliveryMethod', 'user'] },
    );
    if (!order) throw new NotFoundException(OrderMessage.NOT_FOUND);
    return order;
  }

  private assertCartHasItems(cart: Cart) {
    if (!cart.items || cart.items.length === 0) {
      throw new BadRequestException(OrderMessage.CART_EMPTY);
    }
  }

  private assertCartHasDeliveryMethod(cart: Cart) {
    if (!cart.deliveryMethodId) {
      throw new NotFoundException(OrderMessage.DELIVERY_METHOD_NOT_FOUND);
    }
  }

  private assertCartHasPaymentMethod(cart: Cart) {
    if (!cart.paymentMethodId) {
      throw new BadRequestException(OrderMessage.PAYMENT_METHOD_REQUIRED);
    }
  }

  private async getUserOrFail(userId: string): Promise<User> {
    const user = await this.em.findOne(User, { id: userId });
    if (!user) throw new NotFoundException(OrderMessage.USER_NOT_FOUND);
    return user;
  }

  private async getRestaurantOrFail(restaurantId: string): Promise<Restaurant> {
    const restaurant = await this.em.findOne(Restaurant, { id: restaurantId });
    if (!restaurant) throw new NotFoundException(OrderMessage.RESTAURANT_NOT_FOUND);
    return restaurant;
  }

  private async getDeliveryOrFail(deliveryId: string): Promise<Delivery> {
    const delivery = await this.em.findOne(Delivery, { id: deliveryId });
    if (!delivery) throw new NotFoundException(OrderMessage.DELIVERY_NOT_FOUND);
    return delivery;
  }

  private assertMeetsMinOrderForDelivery(cart: Cart, delivery: Delivery) {
    const minOrderPrice = Number(delivery.minOrderPrice) || 0;
    if (minOrderPrice > 0 && cart.total < minOrderPrice) {
      throw new BadRequestException(OrderMessage.MIN_ORDER_AMOUNT_NOT_MET);
    }
  }

  private assertDeliveryMethodRequirements(cart: Cart, delivery: Delivery) {
    if (delivery.method === DeliveryMethodEnum.DineIn) {
      if (!cart.tableNumber || cart.tableNumber.trim() === '') {
        throw new BadRequestException(OrderMessage.TABLE_NUMBER_REQUIRED);
      }
    }

    if (delivery.method === DeliveryMethodEnum.DeliveryCourier && !cart.userAddress) {
      throw new BadRequestException(OrderMessage.ADDRESS_REQUIRED);
    }

    if (delivery.method === DeliveryMethodEnum.DeliveryCar && !cart.carAddress) {
      throw new BadRequestException(OrderMessage.CAR_ADDRESS_REQUIRED);
    }
  }

  private async getPaymentMethodOrFail(paymentMethodId: string, restaurantId: string): Promise<PaymentMethod> {
    const paymentMethod = await this.em.findOne(
      PaymentMethod,
      {
        id: paymentMethodId,
        restaurant: { id: restaurantId },
      },
      { populate: ['restaurant'] },
    );

    if (!paymentMethod) {
      throw new NotFoundException(`Payment method with ID ${paymentMethodId} not found for restaurant ${restaurantId}`);
    }

    return paymentMethod;
  }

  private assertPaymentMethodEnabled(paymentMethod: PaymentMethod) {
    if (!paymentMethod.enabled) {
      throw new BadRequestException(OrderMessage.PAYMENT_METHOD_NOT_ENABLED);
    }
  }

  private async buildOrderItemsData(cart: Cart, restaurantId: string): Promise<OrderItemData[]> {
    const orderItemsData: OrderItemData[] = [];

    for (const cartItem of cart.items) {
      const food = await this.em.findOne(Food, { id: cartItem.foodId }, { populate: ['restaurant', 'inventory'] });
      if (!food) throw new NotFoundException(OrderMessage.FOOD_NOT_FOUND);

      if (food.restaurant.id !== restaurantId) {
        throw new BadRequestException(OrderMessage.FOOD_NOT_BELONGS_TO_RESTAURANT);
      }

      this.assertFoodHasSufficientStock(food, cartItem.quantity);

      orderItemsData.push({
        food,
        quantity: cartItem.quantity,
        unitPrice: food.price || 0,
        discount: food.discount || 0,
      });
    }

    return orderItemsData;
  }

  private assertFoodHasSufficientStock(food: Food, quantity: number) {
    const availableStock = food.inventory?.availableStock ?? 0;
    if (availableStock < quantity) {
      throw new BadRequestException(OrderMessage.FOOD_NOT_FOUND);
    }
  }

  private async buildOrderItemsDataFromDto(
    items: { foodId: string; quantity: number }[],
    restaurantId: string,
  ): Promise<OrderItemData[]> {
    const orderItemsData: OrderItemData[] = [];

    for (const item of items) {
      const food = await this.em.findOne(Food, { id: item.foodId }, { populate: ['restaurant', 'inventory'] });
      if (!food) throw new NotFoundException(OrderMessage.FOOD_NOT_FOUND);

      if (food.restaurant.id !== restaurantId) {
        throw new BadRequestException(OrderMessage.FOOD_NOT_BELONGS_TO_RESTAURANT);
      }

      this.assertFoodHasSufficientStock(food, item.quantity);

      orderItemsData.push({
        food,
        quantity: item.quantity,
        unitPrice: food.price || 0,
        discount: food.discount || 0,
      });
    }

    return orderItemsData;
  }

  private assertAdminDeliveryRequirements(dto: AdminCreateOrderDto, delivery: Delivery) {
    if (delivery.method === DeliveryMethodEnum.DineIn) {
      if (!dto.tableNumber || dto.tableNumber.trim() === '') {
        throw new BadRequestException(OrderMessage.TABLE_NUMBER_REQUIRED);
      }
    }

    if (delivery.method === DeliveryMethodEnum.DeliveryCourier && !dto.userAddress) {
      throw new BadRequestException(OrderMessage.ADDRESS_REQUIRED);
    }

    if (delivery.method === DeliveryMethodEnum.DeliveryCar && !dto.carAddress) {
      throw new BadRequestException(OrderMessage.CAR_ADDRESS_REQUIRED);
    }
  }


  async getStats(restId: string) {
    return this.em.transactional(async em => {
      // 1. Total orders count (excluding pending and canceled)
      const totalOrders = await em.count(Order, {
        restaurant: { id: restId },
        status: {
          $in: [
            OrderStatus.PAID,
            OrderStatus.PREPARING,
            OrderStatus.DELIVERED_TO_WAITER,
            OrderStatus.DELIVERED_TO_RECEPTIONIST,
            OrderStatus.SHIPPED,
            OrderStatus.COMPLETED,
          ],
        },
      });

      // 2. Active foods count
      const activeFoods = await em.count(Food, {
        restaurant: { id: restId },
        isActive: true,
      });

      // 3. Total clients count (distinct users with orders for this restaurant)
      const clientsResult = await em.execute(
        `
        SELECT COUNT(DISTINCT o.user_id) as count
        FROM orders o
        WHERE o.restaurant_id = ?
        `,
        [restId],
      );
      const totalClients = Number(clientsResult[0]?.count || 0);

      // 4. Total revenue (sum of paid payments for orders of this restaurant)
      const revenueResult = await em.execute(
        `
        SELECT COALESCE(SUM(p.amount), 0)::numeric as total
        FROM payments p
        INNER JOIN orders o ON p.order_id = o.id
        WHERE o.restaurant_id = ? AND p.status = 'paid'
        `,
        [restId],
      );
      const totalRevenue = Number(revenueResult[0]?.total || 0);

      return {
        totalOrders,
        activeFoods,
        totalClients,
        totalRevenue,
      };
    });
  }

  async getFoodSalesPieChart(restId: string) {
    return this.em.transactional(async em => {
      // Use last 30 days instead of just last month to be more inclusive
      const now = new Date();
      const endDate = new Date(now);
      endDate.setHours(23, 59, 59, 999);
      const startDate = new Date(now);
      startDate.setDate(startDate.getDate() - 30);
      startDate.setHours(0, 0, 0, 0);

      // Calculate actual last month for the period display
      const firstDayOfLastMonth = new Date(now.getFullYear(), now.getMonth() - 1, 1);
      firstDayOfLastMonth.setHours(0, 0, 0, 0);
      const lastDayOfLastMonth = new Date(now.getFullYear(), now.getMonth(), 0, 23, 59, 59, 999);

      this.logger.debug(
        `Food sales pie chart query params: restId=${restId}, startDate=${startDate.toISOString()}, endDate=${endDate.toISOString()}`,
      );

      // Diagnostic query to check what orders exist
      const diagnosticResult = await em.execute(
        `
        SELECT 
          o.id,
          o.status,
          o.created_at,
          COUNT(oi.id) as item_count
        FROM orders o
        LEFT JOIN order_items oi ON oi.order_id = o.id
        WHERE o.restaurant_id = ?
        GROUP BY o.id, o.status, o.created_at
        ORDER BY o.created_at DESC
        LIMIT 10
        `,
        [restId],
      );
      this.logger.debug(`Diagnostic: Found ${diagnosticResult.length} recent orders for restaurant ${restId}`);
      if (diagnosticResult.length > 0) {
        this.logger.debug(`Sample order: status=${diagnosticResult[0].status}, created_at=${diagnosticResult[0].created_at}`);
      }

      // Query order items from orders in the date range
      // Only include completed orders (excluding canceled and pending)
      const result = await em.execute(
        `
        SELECT 
          f.id as food_id,
          f.title as food_title,
          COALESCE(SUM(oi.quantity), 0)::int as total_quantity,
          COALESCE(SUM(oi.total_price), 0)::numeric as total_revenue
        FROM order_items oi
        INNER JOIN orders o ON oi.order_id = o.id
        INNER JOIN foods f ON oi.food_id = f.id
        WHERE o.restaurant_id = ?
          AND o.created_at >= ?
          AND o.created_at <= ?
          AND o.status NOT IN ('pendingPayment', 'canceled')
        GROUP BY f.id, f.title
        ORDER BY total_revenue DESC
        LIMIT 5
        `,
        [restId, startDate, endDate],
      );

      this.logger.debug(`Food sales pie chart query returned ${result.length} rows`);

      // Calculate total revenue for percentage calculation
      const totalRevenue = result.reduce((sum: number, item: any) => {
        return sum + Number(item.total_revenue || 0);
      }, 0);

      // Format data for pie chart and calculate percentages
      const chartData = result.map((item: any) => ({
        foodId: item.food_id,
        foodTitle: item.food_title || 'Unknown',
        quantity: Number(item.total_quantity || 0),
        revenue: Number(item.total_revenue || 0),
        percentage: totalRevenue > 0 ? Number(((Number(item.total_revenue || 0) / totalRevenue) * 100).toFixed(2)) : 0,
      }));

      return {
        period: {
          startDate: firstDayOfLastMonth.toISOString(),
          endDate: lastDayOfLastMonth.toISOString(),
        },
        totalRevenue,
        data: chartData,
      };
    });
  }
}
