import { db } from '../db/db_index';
import { orders, orderItems, orderStatus, users, addresses, refunds, complaints, payments } from '../db/schema';
import { eq, and, gte, lt, desc, inArray, sql } from 'drizzle-orm';

export async function updateOrderNotes(orderId: number, adminNotes: string | null): Promise<any> {
  const [result] = await db
    .update(orders)
    .set({ adminNotes })
    .where(eq(orders.id, orderId))
    .returning();
  return result;
}

export async function getOrderWithDetails(orderId: number): Promise<any | null> {
  return await db.query.orders.findFirst({
    where: eq(orders.id, orderId),
    with: {
      orderItems: {
        with: {
          product: {
            with: {
              unit: true,
            },
          },
        },
      },
      user: true,
      address: true,
      orderStatus: true,
      slot: true,
      payments: true,
      refunds: true,
    },
  });
}

export async function getFullOrder(orderId: number): Promise<any | null> {
  return await db.query.orders.findFirst({
    where: eq(orders.id, orderId),
    with: {
      orderItems: {
        with: {
          product: {
            with: {
              unit: true,
            },
          },
        },
      },
      user: {
        with: {
          userDetails: true,
        },
      },
      address: true,
      orderStatus: true,
      slot: true,
      payments: true,
      refunds: true,
      complaints: true,
    },
  });
}

export async function getOrderDetails(orderId: number): Promise<any | null> {
  return await db.query.orders.findFirst({
    where: eq(orders.id, orderId),
    with: {
      orderItems: {
        with: {
          product: {
            with: {
              unit: true,
            },
          },
        },
      },
      user: true,
      address: true,
      orderStatus: true,
      slot: true,
      payments: true,
      refunds: true,
      complaints: true,
    },
  });
}

export async function getAllOrders(
  limit: number,
  cursor?: number,
  slotId?: number | null,
  filters?: any
): Promise<{ orders: any[]; hasMore: boolean }> {
  let whereConditions = [];

  if (cursor) {
    whereConditions.push(lt(orders.id, cursor));
  }

  if (slotId) {
    whereConditions.push(eq(orders.slotId, slotId));
  }

  // Add filter conditions
  if (filters) {
    if (filters.packagedFilter === 'packaged') {
      whereConditions.push(eq(orders.isPackaged, true));
    } else if (filters.packagedFilter === 'not_packaged') {
      whereConditions.push(eq(orders.isPackaged, false));
    }

    if (filters.deliveredFilter === 'delivered') {
      whereConditions.push(eq(orders.isDelivered, true));
    } else if (filters.deliveredFilter === 'not_delivered') {
      whereConditions.push(eq(orders.isDelivered, false));
    }

    if (filters.flashDeliveryFilter === 'flash') {
      whereConditions.push(eq(orders.isFlashDelivery, true));
    } else if (filters.flashDeliveryFilter === 'regular') {
      whereConditions.push(eq(orders.isFlashDelivery, false));
    }
  }

  const ordersList = await db.query.orders.findMany({
    where: whereConditions.length > 0 ? and(...whereConditions) : undefined,
    with: {
      orderItems: {
        with: {
          product: true,
        },
      },
      user: true,
      orderStatus: true,
      slot: true,
    },
    orderBy: desc(orders.id),
    limit: limit + 1,
  });

  const hasMore = ordersList.length > limit;
  return { orders: hasMore ? ordersList.slice(0, limit) : ordersList, hasMore };
}

export async function getOrdersBySlotId(slotId: number): Promise<any[]> {
  return await db.query.orders.findMany({
    where: eq(orders.slotId, slotId),
    with: {
      orderItems: {
        with: {
          product: true,
        },
      },
      user: true,
      orderStatus: true,
      address: true,
    },
    orderBy: desc(orders.createdAt),
  });
}

export async function updateOrderPackaged(orderId: number, isPackaged: boolean): Promise<any> {
  const [result] = await db
    .update(orders)
    .set({ isPackaged })
    .where(eq(orders.id, orderId))
    .returning();
  return result;
}

export async function updateOrderDelivered(orderId: number, isDelivered: boolean): Promise<any> {
  const [result] = await db
    .update(orders)
    .set({ isDelivered })
    .where(eq(orders.id, orderId))
    .returning();
  return result;
}

export async function updateOrderItemPackaging(
  orderItemId: number,
  isPackaged: boolean,
  isPackageVerified: boolean
): Promise<void> {
  await db.update(orderItems)
    .set({ is_packaged: isPackaged, is_package_verified: isPackageVerified })
    .where(eq(orderItems.id, orderItemId));
}

export async function updateAddressCoords(addressId: number, lat: number, lng: number): Promise<void> {
  await db.update(addresses)
    .set({ lat, lng })
    .where(eq(addresses.id, addressId));
}

export async function getOrderStatus(orderId: number): Promise<any | null> {
  return await db.query.orderStatus.findFirst({
    where: eq(orderStatus.orderId, orderId),
  });
}

export async function cancelOrder(orderId: number, reason: string): Promise<any> {
  return await db.transaction(async (tx) => {
    // Update order status
    const [order] = await tx.update(orders)
      .set({ isCancelled: true, cancellationReason: reason })
      .where(eq(orders.id, orderId))
      .returning();

    // Create order status entry
    await tx.insert(orderStatus).values({
      orderId,
      isCancelled: true,
      cancelReason: reason,
    });

    return order;
  });
}

export async function getTodaysOrders(slotId?: number): Promise<any[]> {
  const today = new Date();
  today.setHours(0, 0, 0, 0);
  const tomorrow = new Date(today);
  tomorrow.setDate(tomorrow.getDate() + 1);

  let whereConditions = [
    gte(orders.createdAt, today),
    lt(orders.createdAt, tomorrow),
  ];

  if (slotId) {
    whereConditions.push(eq(orders.slotId, slotId));
  }

  return await db.query.orders.findMany({
    where: and(...whereConditions),
    with: {
      orderItems: {
        with: {
          product: true,
        },
      },
      user: true,
      orderStatus: true,
    },
    orderBy: desc(orders.createdAt),
  });
}

export async function removeDeliveryCharge(orderId: number): Promise<any> {
  const [result] = await db
    .update(orders)
    .set({ deliveryCharge: '0' })
    .where(eq(orders.id, orderId))
    .returning();
  return result;
}
