import { router, protectedProcedure } from "@/src/trpc/trpc-index"
import { z } from "zod";
import { ApiError } from "@/src/lib/api-error"
import {
  sendOrderPackagedNotification,
  sendOrderDeliveredNotification,
} from "@/src/lib/notif-job";
import { publishCancellation } from "@/src/lib/post-order-handler"
import { getMultipleUserNegativityScores } from "@/src/stores/user-negativity-store"
import {
  updateOrderNotes as updateOrderNotesInDb,
  getOrderDetails as getOrderDetailsInDb,
  updateOrderPackaged as updateOrderPackagedInDb,
  updateOrderDelivered as updateOrderDeliveredInDb,
  updateOrderItemPackaging as updateOrderItemPackagingInDb,
  removeDeliveryCharge as removeDeliveryChargeInDb,
  getSlotOrders as getSlotOrdersInDb,
  updateAddressCoords as updateAddressCoordsInDb,
  getAllOrders as getAllOrdersInDb,
  rebalanceSlots as rebalanceSlotsInDb,
  cancelOrder as cancelOrderInDb,
  deleteOrderById as deleteOrderByIdInDb,
} from '@/src/dbService'
import type {
  AdminCancelOrderResult,
  AdminGetAllOrdersResult,
  AdminGetSlotOrdersResult,
  AdminOrderBasicResult,
  AdminOrderDetails,
  AdminOrderItemPackagingResult,
  AdminOrderMessageResult,
  AdminOrderRow,
  AdminOrderUpdateResult,
  AdminRebalanceSlotsResult,
} from "@packages/shared"

const updateOrderNotesSchema = z.object({
  orderId: z.number(),
  adminNotes: z.string(),
});

const getOrderDetailsSchema = z.object({
  orderId: z.number(),
});

const updatePackagedSchema = z.object({
  orderId: z.string(),
  isPackaged: z.boolean(),
});

const updateDeliveredSchema = z.object({
  orderId: z.string(),
  isDelivered: z.boolean(),
});

const updateOrderItemPackagingSchema = z.object({
  orderItemId: z.number(),
  isPackaged: z.boolean().optional(),
  isPackageVerified: z.boolean().optional(),
});

const getSlotOrdersSchema = z.object({
  slotId: z.string(),
});

const getAllOrdersSchema = z.object({
  cursor: z.number().optional(),
  limit: z.number().default(20),
  slotId: z.number().optional().nullable(),
  packagedFilter: z
    .enum(["all", "packaged", "not_packaged"])
    .optional()
    .default("all"),
  deliveredFilter: z
    .enum(["all", "delivered", "not_delivered"])
    .optional()
    .default("all"),
  cancellationFilter: z
    .enum(["all", "cancelled", "not_cancelled"])
    .optional()
    .default("all"),
  flashDeliveryFilter: z
    .enum(["all", "flash", "regular"])
    .optional()
    .default("all"),
});

export const orderRouter = router({
  updateNotes: protectedProcedure
    .input(updateOrderNotesSchema)
    .mutation(async ({ input }): Promise<AdminOrderRow> => {
      const { orderId, adminNotes } = input;

      const result = await updateOrderNotesInDb(orderId, adminNotes || null)

      /*
      // Old implementation - direct DB query:
      const result = await db
        .update(orders)
        .set({
          adminNotes: adminNotes || null,
        })
        .where(eq(orders.id, orderId))
        .returning();

      if (result.length === 0) {
        throw new Error("Order not found");
      }
      */

      if (!result) {
        throw new Error("Order not found")
      }

      return result as AdminOrderRow;
    }),

  getOrderDetails: protectedProcedure
    .input(getOrderDetailsSchema)
    .query(async ({ input }): Promise<AdminOrderDetails> => {
      const { orderId } = input;

      const orderDetails = await getOrderDetailsInDb(orderId)

      /*
      // Old implementation - direct DB queries:
      // Single optimized query with all relations
      const orderData = await db.query.orders.findFirst({
        where: eq(orders.id, orderId),
        with: {
          user: true,
          address: true,
          slot: true,
          orderItems: {
            with: {
              product: {
                with: {
                  unit: true,
                },
              },
            },
          },
          payment: true,
          paymentInfo: true,
          orderStatus: true,
          refunds: true,
        },
      });

      if (!orderData) {
        throw new Error("Order not found");
      }

      // Get coupon usage for this specific order using new orderId field
      const couponUsageData = await db.query.couponUsage.findMany({
        where: eq(couponUsage.orderId, orderData.id),
        with: {
          coupon: true,
        },
      });

      let couponData = null;
      if (couponUsageData.length > 0) {
        // Calculate total discount from multiple coupons
        let totalDiscountAmount = 0;
        const orderTotal = parseFloat(orderData.totalAmount.toString());

        for (const usage of couponUsageData) {
          let discountAmount = 0;

          if (usage.coupon.discountPercent) {
            discountAmount =
              (orderTotal *
                parseFloat(usage.coupon.discountPercent.toString())) /
              100;
          } else if (usage.coupon.flatDiscount) {
            discountAmount = parseFloat(usage.coupon.flatDiscount.toString());
          }

          // Apply max value limit if set
          if (
            usage.coupon.maxValue &&
            discountAmount > parseFloat(usage.coupon.maxValue.toString())
          ) {
            discountAmount = parseFloat(usage.coupon.maxValue.toString());
          }

          totalDiscountAmount += discountAmount;
        }

        couponData = {
          couponCode: couponUsageData
            .map((u) => u.coupon.couponCode)
            .join(", "),
          couponDescription: `${couponUsageData.length} coupons applied`,
          discountAmount: totalDiscountAmount,
        };
      }

      // Status determination from included relation
      const statusRecord = orderData.orderStatus?.[0];
      let status: "pending" | "delivered" | "cancelled" = "pending";
      if (statusRecord?.isCancelled) {
        status = "cancelled";
      } else if (statusRecord?.isDelivered) {
        status = "delivered";
      }

      // Always include refund data (will be null/undefined if not cancelled)
      const refund = orderData.refunds?.[0];

      return {
        id: orderData.id,
        readableId: orderData.id,
        userId: orderData.user.id,
        customerName: `${orderData.user.name}`,
        customerEmail: orderData.user.email,
        customerMobile: orderData.user.mobile,
        address: {
          name: orderData.address.name,
          line1: orderData.address.addressLine1,
          line2: orderData.address.addressLine2,
          city: orderData.address.city,
          state: orderData.address.state,
          pincode: orderData.address.pincode,
          phone: orderData.address.phone,
        },
        slotInfo: orderData.slot
          ? {
              time: orderData.slot.deliveryTime.toISOString(),
              sequence: orderData.slot.deliverySequence,
            }
          : null,
        isCod: orderData.isCod,
        isOnlinePayment: orderData.isOnlinePayment,
        totalAmount: parseFloat(orderData.totalAmount?.toString() || '0') - parseFloat(orderData.deliveryCharge?.toString() || '0'),
        deliveryCharge: parseFloat(orderData.deliveryCharge?.toString() || '0'),
        adminNotes: orderData.adminNotes,
        userNotes: orderData.userNotes,
        createdAt: orderData.createdAt,
        status,
        isPackaged: statusRecord?.isPackaged || false,
        isDelivered: statusRecord?.isDelivered || false,
        items: orderData.orderItems.map((item) => ({
          id: item.id,
          name: item.product.name,
          quantity: item.quantity,
          productSize: item.product.productQuantity,
          price: item.price,
          unit: item.product.unit?.shortNotation,
          amount:
            parseFloat(item.price.toString()) *
            parseFloat(item.quantity || "0"),
          isPackaged: item.is_packaged,
          isPackageVerified: item.is_package_verified,
        })),
        payment: orderData.payment
          ? {
              status: orderData.payment.status,
              gateway: orderData.payment.gateway,
              merchantOrderId: orderData.payment.merchantOrderId,
            }
          : null,
        paymentInfo: orderData.paymentInfo
          ? {
              status: orderData.paymentInfo.status,
              gateway: orderData.paymentInfo.gateway,
              merchantOrderId: orderData.paymentInfo.merchantOrderId,
            }
          : null,
        // Cancellation details (always included, null if not cancelled)
        cancelReason: statusRecord?.cancelReason || null,
        cancellationReviewed: statusRecord?.cancellationReviewed || false,
        isRefundDone: refund?.refundStatus === "processed" || false,
        refundStatus: refund?.refundStatus as RefundStatus,
        refundAmount: refund?.refundAmount
          ? parseFloat(refund.refundAmount.toString())
          : null,
        // Coupon information
        couponData: couponData,
        couponCode: couponData?.couponCode || null,
        couponDescription: couponData?.couponDescription || null,
        discountAmount: couponData?.discountAmount || null,
        orderStatus: statusRecord,
        refundRecord: refund,
        isFlashDelivery: orderData.isFlashDelivery,
      };
      */

      if (!orderDetails) {
        throw new Error('Order not found')
      }

      return orderDetails
    }),

  updatePackaged: protectedProcedure
    .input(updatePackagedSchema)
    .mutation(async ({ input }): Promise<AdminOrderUpdateResult> => {
      const { orderId, isPackaged } = input;

      const result = await updateOrderPackagedInDb(orderId, isPackaged)

      /*
      // Old implementation - direct DB queries:
      // Update all order items to the specified packaged state
      await db
        .update(orderItems)
        .set({ is_packaged: isPackaged })
        .where(eq(orderItems.orderId, parseInt(orderId)));

      // Also update the order status table for backward compatibility
      if (!isPackaged) {
        await db
          .update(orderStatus)
          .set({ isPackaged, isDelivered: false })
          .where(eq(orderStatus.orderId, parseInt(orderId)));
      } else {
        await db
          .update(orderStatus)
          .set({ isPackaged })
          .where(eq(orderStatus.orderId, parseInt(orderId)));
      }

      const order = await db.query.orders.findFirst({
        where: eq(orders.id, parseInt(orderId)),
      });
      if (order) await sendOrderPackagedNotification(order.userId, orderId);

      return { success: true };
      */

      if (result.userId) await sendOrderPackagedNotification(result.userId, orderId)

      return { success: true, userId: result.userId }
    }),

  updateDelivered: protectedProcedure
    .input(updateDeliveredSchema)
    .mutation(async ({ input }): Promise<AdminOrderUpdateResult> => {
      const { orderId, isDelivered } = input;

      const result = await updateOrderDeliveredInDb(orderId, isDelivered)

      /*
      // Old implementation - direct DB queries:
      await db
        .update(orderStatus)
        .set({ isDelivered })
        .where(eq(orderStatus.orderId, parseInt(orderId)));

      const order = await db.query.orders.findFirst({
        where: eq(orders.id, parseInt(orderId)),
      });
      if (order) await sendOrderDeliveredNotification(order.userId, orderId);

      return { success: true };
      */

      if (result.userId) await sendOrderDeliveredNotification(result.userId, orderId)

      return { success: true, userId: result.userId }
    }),

  updateOrderItemPackaging: protectedProcedure
    .input(updateOrderItemPackagingSchema)
    .mutation(async ({ input }): Promise<AdminOrderItemPackagingResult> => {
      const { orderItemId, isPackaged, isPackageVerified } = input;

      const result = await updateOrderItemPackagingInDb(orderItemId, isPackaged, isPackageVerified)

      /*
      // Old implementation - direct DB queries:
      // Validate that orderItem exists
      const orderItem = await db.query.orderItems.findFirst({
        where: eq(orderItems.id, orderItemId),
      });

      if (!orderItem) {
        throw new ApiError("Order item not found", 404);
      }

      // Build update object with only provided fields
      const updateData: any = {};
      if (isPackaged !== undefined) {
        updateData.is_packaged = isPackaged;
      }
      if (isPackageVerified !== undefined) {
        updateData.is_package_verified = isPackageVerified;
      }

      // Update the order item
      await db
        .update(orderItems)
        .set(updateData)
        .where(eq(orderItems.id, orderItemId));

      return { success: true };
      */

      if (!result.updated) {
        throw new ApiError('Order item not found', 404)
      }

      return result
    }),

  removeDeliveryCharge: protectedProcedure
    .input(z.object({ orderId: z.number() }))
    .mutation(async ({ input }): Promise<AdminOrderMessageResult> => {
      const { orderId } = input;

      const result = await removeDeliveryChargeInDb(orderId)

      /*
      // Old implementation - direct DB queries:
      const order = await db.query.orders.findFirst({
        where: eq(orders.id, orderId),
      });

      if (!order) {
        throw new Error('Order not found');
      }

      const currentDeliveryCharge = parseFloat(order.deliveryCharge?.toString() || '0');
      const currentTotalAmount = parseFloat(order.totalAmount?.toString() || '0');
      const newTotalAmount = currentTotalAmount - currentDeliveryCharge;

      await db
        .update(orders)
        .set({ 
          deliveryCharge: '0',
          totalAmount: newTotalAmount.toString()
        })
        .where(eq(orders.id, orderId));

      return { success: true, message: 'Delivery charge removed' };
      */

      if (!result) {
        throw new Error('Order not found')
      }

      return result
    }),

  getSlotOrders: protectedProcedure
    .input(getSlotOrdersSchema)
    .query(async ({ input }): Promise<AdminGetSlotOrdersResult> => {
      const { slotId } = input;

      const result = await getSlotOrdersInDb(slotId)

      /*
      // Old implementation - direct DB queries:
      const slotOrders = await db.query.orders.findMany({
        where: eq(orders.slotId, parseInt(slotId)),
        with: {
          user: true,
          address: true,
          slot: true,
          orderItems: {
            with: {
              product: {
                with: {
                  unit: true,
                },
              },
            },
          },
          orderStatus: true,
        },
      });

      const filteredOrders = slotOrders.filter((order) => {
        const statusRecord = order.orderStatus[0];
        return (
          order.isCod ||
          (statusRecord && statusRecord.paymentStatus === "success")
        );
      });

      const formattedOrders = filteredOrders.map((order) => {
        const statusRecord = order.orderStatus[0]; // assuming one status per order
        let status: "pending" | "delivered" | "cancelled" = "pending";
        if (statusRecord?.isCancelled) {
          status = "cancelled";
        } else if (statusRecord?.isDelivered) {
          status = "delivered";
        }

        const items = order.orderItems.map((item) => ({
          id: item.id,
          name: item.product.name,
          quantity: parseFloat(item.quantity),
          price: parseFloat(item.price.toString()),
          amount: parseFloat(item.quantity) * parseFloat(item.price.toString()),
          unit: item.product.unit?.shortNotation || "",
          isPackaged: item.is_packaged,
          isPackageVerified: item.is_package_verified,
        }));

        return {
          id: order.id,
          readableId: order.id,
          customerName: order.user.name,
          address: `${order.address.addressLine1}${
            order.address.addressLine2 ? `, ${order.address.addressLine2}` : ""
          }, ${order.address.city}, ${order.address.state} - ${
            order.address.pincode
          }, Phone: ${order.address.phone}`,
           addressId: order.addressId,
           latitude: order.address.adminLatitude ?? order.address.latitude,
           longitude: order.address.adminLongitude ?? order.address.longitude,
          totalAmount: parseFloat(order.totalAmount),
          items,
          deliveryTime: order.slot?.deliveryTime.toISOString() || null,
          status,
          isPackaged:
            order.orderItems.every((item) => item.is_packaged) || false,
          isDelivered: statusRecord?.isDelivered || false,
          isCod: order.isCod,
          paymentMode: order.isCod ? "COD" : "Online",
          paymentStatus: statusRecord?.paymentStatus || "pending",
          slotId: order.slotId,
          adminNotes: order.adminNotes,
          userNotes: order.userNotes,
        };
      });

      return { success: true, data: formattedOrders };
      */

      return result
    }),

  updateAddressCoords: protectedProcedure
    .input(
      z.object({
        addressId: z.number(),
        latitude: z.number(),
        longitude: z.number(),
      })
    )
    .mutation(async ({ input }): Promise<AdminOrderBasicResult> => {
      const { addressId, latitude, longitude } = input;

      const result = await updateAddressCoordsInDb(addressId, latitude, longitude)

      /*
      // Old implementation - direct DB queries:
      const result = await db
        .update(addresses)
        .set({
          adminLatitude: latitude,
          adminLongitude: longitude,
        })
        .where(eq(addresses.id, addressId))
        .returning();

      if (result.length === 0) {
        throw new ApiError("Address not found", 404);
      }

      return { success: true };
      */

      if (!result.success) {
        throw new ApiError('Address not found', 404)
      }

      return result
    }),

  getAll: protectedProcedure
    .input(getAllOrdersSchema)
    .query(async ({ input }): Promise<AdminGetAllOrdersResult | undefined> => {
      try {
        const result = await getAllOrdersInDb(input)
        const userIds = [...new Set(result.orders.map((order) => order.userId))]
        const negativityScores = await getMultipleUserNegativityScores(userIds)

        const orders = result.orders.map((order) => {
          const { userId, userNegativityScore, ...rest } = order
          return {
            ...rest,
            userNegativityScore: negativityScores[userId] || 0,
          }
        })

        /*
        // Old implementation - direct DB queries:
        const {
          cursor,
          limit,
          slotId,
          packagedFilter,
          deliveredFilter,
          cancellationFilter,
          flashDeliveryFilter,
        } = input;

        let whereCondition: SQL<unknown> | undefined = eq(orders.id, orders.id); // always true
        if (cursor) {
          whereCondition = and(whereCondition, lt(orders.id, cursor));
        }
        if (slotId) {
          whereCondition = and(whereCondition, eq(orders.slotId, slotId));
        }
        if (packagedFilter === "packaged") {
          whereCondition = and(
            whereCondition,
            eq(orderStatus.isPackaged, true)
          );
        } else if (packagedFilter === "not_packaged") {
          whereCondition = and(
            whereCondition,
            eq(orderStatus.isPackaged, false)
          );
        }
        if (deliveredFilter === "delivered") {
          whereCondition = and(
            whereCondition,
            eq(orderStatus.isDelivered, true)
          );
        } else if (deliveredFilter === "not_delivered") {
          whereCondition = and(
            whereCondition,
            eq(orderStatus.isDelivered, false)
          );
        }
        if (cancellationFilter === "cancelled") {
          whereCondition = and(
            whereCondition,
            eq(orderStatus.isCancelled, true)
          );
        } else if (cancellationFilter === "not_cancelled") {
          whereCondition = and(
            whereCondition,
            eq(orderStatus.isCancelled, false)
          );
        }
        if (flashDeliveryFilter === "flash") {
          whereCondition = and(
            whereCondition,
            eq(orders.isFlashDelivery, true)
          );
        } else if (flashDeliveryFilter === "regular") {
          whereCondition = and(
            whereCondition,
            eq(orders.isFlashDelivery, false)
          );
        }

        const allOrders = await db.query.orders.findMany({
          where: whereCondition,
          orderBy: desc(orders.createdAt),
          limit: limit + 1, // fetch one extra to check if there's more
          with: {
            user: true,
            address: true,
            slot: true,
            orderItems: {
              with: {
                product: {
                  with: {
                    unit: true,
                  },
                },
              },
            },
            orderStatus: true,
          },
        });

        const hasMore = allOrders.length > limit;
        const ordersToReturn = hasMore ? allOrders.slice(0, limit) : allOrders;

        const userIds = [...new Set(ordersToReturn.map(o => o.userId))];
        const negativityScores = await getMultipleUserNegativityScores(userIds);

        const filteredOrders = ordersToReturn.filter((order) => {
          const statusRecord = order.orderStatus[0];
          return (
            order.isCod ||
            (statusRecord && statusRecord.paymentStatus === "success")
          );
        });

        const formattedOrders = filteredOrders.map((order) => {
          const statusRecord = order.orderStatus[0];
          let status: "pending" | "delivered" | "cancelled" = "pending";
          if (statusRecord?.isCancelled) {
            status = "cancelled";
          } else if (statusRecord?.isDelivered) {
            status = "delivered";
          }

          const items = order.orderItems
            .map((item) => ({
              id: item.id,
              name: item.product.name,
              quantity: parseFloat(item.quantity),
              price: parseFloat(item.price.toString()),
              amount:
                parseFloat(item.quantity) * parseFloat(item.price.toString()),
              unit: item.product.unit?.shortNotation || "",
              productSize: item.product.productQuantity,
              isPackaged: item.is_packaged,
              isPackageVerified: item.is_package_verified,
            }))
            .sort((first, second) => first.id - second.id);
           dayjs.extend(utc);
           return {
               id: order.id,
               orderId: order.id.toString(),
               readableId: order.id,
              customerName: order.user.name,
             customerMobile: order.user.mobile,
             address: `${order.address.addressLine1}${
               order.address.addressLine2
                 ? `, ${order.address.addressLine2}`
                 : ""
             }, ${order.address.city}, ${order.address.state} - ${
               order.address.pincode
             }, Phone: ${order.address.phone}`,
              addressId: order.addressId,
              latitude: order.address.adminLatitude ?? order.address.latitude,
              longitude: order.address.adminLongitude ?? order.address.longitude,
             totalAmount: parseFloat(order.totalAmount),
             deliveryCharge: parseFloat(order.deliveryCharge || "0"),
             items,
             createdAt: order.createdAt,
             //  deliveryTime: order.slot ? dayjs.utc(order.slot.deliveryTime).format('ddd, MMM D • h:mm A') : 'Not scheduled',
           deliveryTime: order.slot?.deliveryTime.toISOString() || null,
             status,
             isPackaged:
               order.orderItems.every((item) => item.is_packaged) || false,
             isDelivered: statusRecord?.isDelivered || false,
             isCod: order.isCod,
             isFlashDelivery: order.isFlashDelivery,
             userNotes: order.userNotes,
             adminNotes: order.adminNotes,
             userNegativityScore: negativityScores[order.userId] || 0,
           };
        });
        
        return {
          orders: formattedOrders,
          nextCursor: hasMore
            ? ordersToReturn[ordersToReturn.length - 1].id
            : undefined,
        };
        */

        return {
          orders,
          nextCursor: result.nextCursor,
        }
      } catch (e) {
        console.log({ e });
      }
    }),

  rebalanceSlots: protectedProcedure
    .input(z.object({ slotIds: z.array(z.number()).min(1).max(50) }))
    .mutation(async ({ input }): Promise<AdminRebalanceSlotsResult> => {
      const slotIds = input.slotIds;

      const result = await rebalanceSlotsInDb(slotIds)

      /*
      // Old implementation - direct DB queries:
      const ordersList = await db.query.orders.findMany({
        where: inArray(orders.slotId, slotIds),
        with: {
          orderItems: {
            with: {
              product: true
            }
          },
          couponUsages: {
            with: {
              coupon: true
            }
          },
        }
      });

      const processedOrdersData = ordersList.map((order) => {

        let newTotal = order.orderItems.reduce((acc,item) => {
          const latestPrice = +item.product.price;
          const amount = (latestPrice * Number(item.quantity));
          return acc+amount;
        },0)

        order.orderItems.forEach(item => {
          item.price = item.product.price;
          item.discountedPrice = item.product.price
        })

        const coupon = order.couponUsages[0]?.coupon;

        let discount = 0;
        if(coupon && !coupon.isInvalidated && (!coupon.validTill || new Date(coupon.validTill) > new Date())) {
          const proportion = Number(order.orderGroupProportion || 1);
          if(coupon.discountPercent) {
            const maxDiscount = Number(coupon.maxValue || Infinity) * proportion;
            discount = Math.min((newTotal * parseFloat(coupon.discountPercent)) / 100, maxDiscount);
          }
          else {
            discount = Number(coupon.flatDiscount) * proportion;
          }
        }
        newTotal -= discount

        const { couponUsages, orderItems: orderItemsRaw, ...rest} = order;
        const updatedOrderItems = orderItemsRaw.map(item => {
          const { product, ...rawOrderItem } = item;
          return rawOrderItem;
        })
        return {order: rest, updatedOrderItems, newTotal }
      })

      const updatedOrderIds: number[] = [];
      await db.transaction(async (tx) => {
        for (const { order, updatedOrderItems, newTotal } of processedOrdersData) {
          await tx.update(orders).set({ totalAmount: newTotal.toString() }).where(eq(orders.id, order.id));
          updatedOrderIds.push(order.id);

          for (const item of updatedOrderItems) {
            await tx.update(orderItems).set({
              price: item.price,
              discountedPrice: item.discountedPrice
            }).where(eq(orderItems.id, item.id));
          }
        }
      });

      return { success: true, updatedOrders: updatedOrderIds, message: `Rebalanced ${updatedOrderIds.length} orders.` };
      */

      return result
    }),

  cancelOrder: protectedProcedure
    .input(z.object({
      orderId: z.number(),
      reason: z.string().min(1, "Cancellation reason is required"),
    }))
    .mutation(async ({ input }): Promise<AdminCancelOrderResult> => {
      const { orderId, reason } = input;

      const result = await cancelOrderInDb(orderId, reason)

      /*
      // Old implementation - direct DB queries:
      const order = await db.query.orders.findFirst({
        where: eq(orders.id, orderId),
        with: {
          orderStatus: true,
        },
      });

      if (!order) {
        throw new ApiError("Order not found", 404);
      }

      const status = order.orderStatus[0];
      if (!status) {
        throw new ApiError("Order status not found", 400);
      }

      if (status.isCancelled) {
        throw new ApiError("Order is already cancelled", 400);
      }

      if (status.isDelivered) {
        throw new ApiError("Cannot cancel delivered order", 400);
      }

      const result = await db.transaction(async (tx) => {
        await tx
          .update(orderStatus)
          .set({
            isCancelled: true,
            isCancelledByAdmin: true,
            cancelReason: reason,
            cancellationAdminNotes: reason,
            cancellationReviewed: true,
            cancellationReviewedAt: new Date(),
          })
          .where(eq(orderStatus.id, status.id));

        const refundStatus = order.isCod ? "na" : "pending";

        await tx.insert(refunds).values({
          orderId: order.id,
          refundStatus,
        });

        return { orderId: order.id, userId: order.userId };
      });

      // Publish to Redis for Telegram notification
      await publishCancellation(result.orderId, 'admin', reason);

      return { success: true, message: "Order cancelled successfully" };
      */

      if (!result.success) {
        if (result.error === 'order_not_found') {
          throw new ApiError(result.message, 404)
        }
        if (result.error === 'status_not_found') {
          throw new ApiError(result.message, 400)
        }
        if (result.error === 'already_cancelled') {
          throw new ApiError(result.message, 400)
        }
        if (result.error === 'already_delivered') {
          throw new ApiError(result.message, 400)
        }

        throw new ApiError(result.message, 400)
      }

      if (result.orderId) {
        await publishCancellation(result.orderId, 'admin', reason)
      }

      return { success: true, message: result.message }
    }),
});

// {"id": "order_Rhh00qJNdjUp8o", "notes": {"retry": "true", "customerOrderId": "14"}, "amount": 21000, "entity": "order", "status": "created", "receipt": "order_14_retry", "attempts": 0, "currency": "INR", "offer_id": null, "signature": "6df20655021f1d6841340f2a2ef2ef9378cb3d43495ab09e85f08aea1a851583", "amount_due": 21000, "created_at": 1763575791, "payment_id": "pay_Rhh15cLL28YM7j", "amount_paid": 0}

export async function deleteOrderById(orderId: number): Promise<void> {
  await deleteOrderByIdInDb(orderId)

  /*
  // Old implementation - direct DB queries:
  await db.transaction(async (tx) => {
    await tx.delete(orderItems).where(eq(orderItems.orderId, orderId));
    await tx.delete(orderStatus).where(eq(orderStatus.orderId, orderId));
    await tx.delete(payments).where(eq(payments.orderId, orderId));
    await tx.delete(refunds).where(eq(refunds.orderId, orderId));
    await tx.delete(couponUsage).where(eq(couponUsage.orderId, orderId));
    await tx.delete(complaints).where(eq(complaints.orderId, orderId));
    await tx.delete(orders).where(eq(orders.id, orderId));
  });
  */
}
