import crypto from "crypto";
import {
  and,
  asc,
  count,
  desc,
  eq,
  gte,
  inArray,
  like,
  lte,
  or,
  sql,
} from "drizzle-orm";
import type { SQL } from "drizzle-orm";
import type {
  AdminOrderListQueryInput,
  CreateOrderInput,
  CustomerOrderListQueryInput,
  UpdateOrderInput,
} from "../@types/orderRequests.js";
import type { AdminOrderDetail, AdminOrderListItem, PaginatedOrders, PublicOrder, PublicOrderItem } from "../@types/orders.js";
import { db } from "../db/index.js";
import { customers, orderItems, orders, products, staffUsers } from "../db/schema.js";
import { toAdminOrderDetail, toAdminOrderListItem, toPublicOrder } from "./orderSerializers.js";

const LOW_STOCK_THRESHOLD = 5;

const generateOrderNumber = () => {
  const random = crypto.randomBytes(3).toString("hex").toUpperCase();
  return `ORD-${Date.now()}-${random}`;
};

const mergeOrderItems = (items: CreateOrderInput["items"]) => {
  const map = new Map<number, { quantity: number; lensChoice: string; prescriptionUrl: string | null }>();
  for (const item of items) {
    const existing = map.get(item.productId);
    if (existing) {
      existing.quantity += item.quantity;
    } else {
      map.set(item.productId, {
        quantity: item.quantity,
        lensChoice: item.lensChoice ?? "none",
        prescriptionUrl: item.prescriptionUrl ?? null,
      });
    }
  }
  return [...map.entries()].map(([productId, data]) => ({ productId, ...data }));
};

const getOrderItemsByOrderIds = async (orderIds: number[]) => {
  if (!orderIds.length) return new Map<number, PublicOrderItem[]>();

  const rows = await db
    .select({
      orderId: orderItems.orderId,
      id: orderItems.id,
      productId: orderItems.productId,
      productName: orderItems.productName,
      sku: orderItems.sku,
      quantity: orderItems.quantity,
      unitPrice: orderItems.unitPrice,
      discountAmount: orderItems.discountAmount,
      lineTotal: orderItems.lineTotal,
      lensChoice: orderItems.lensChoice,
      prescriptionUrl: orderItems.prescriptionUrl,
    })
    .from(orderItems)
    .where(inArray(orderItems.orderId, orderIds))
    .orderBy(asc(orderItems.id));

  const map = new Map<number, PublicOrderItem[]>();
  for (const row of rows) {
    const list = map.get(row.orderId) ?? [];
    list.push({
      id: row.id,
      productId: row.productId,
      productName: row.productName,
      sku: row.sku,
      quantity: row.quantity,
      unitPrice: row.unitPrice,
      discountAmount: row.discountAmount,
      lineTotal: row.lineTotal,
      lensChoice: row.lensChoice,
      prescriptionUrl: row.prescriptionUrl,
    });
    map.set(row.orderId, list);
  }
  return map;
};

const getItemCountsByOrderIds = async (orderIds: number[]) => {
  if (!orderIds.length) return new Map<number, number>();
  const rows = await db
    .select({ orderId: orderItems.orderId, itemCount: sql<number>`coalesce(sum(${orderItems.quantity}), 0)` })
    .from(orderItems)
    .where(inArray(orderItems.orderId, orderIds))
    .groupBy(orderItems.orderId);

  return new Map(rows.map((row) => [row.orderId, Number(row.itemCount)]));
};

export const createCustomerOrder = async (customerId: number, input: CreateOrderInput): Promise<PublicOrder> => {
  const [customer] = await db.select().from(customers).where(eq(customers.id, customerId)).limit(1);
  if (!customer) throw new Error("Customer not found.");

  const contactPhone = input.contactPhone ?? customer.phoneNumber;
  if (!contactPhone) throw new Error("Contact phone is required before placing an order.");

  const mergedItems = mergeOrderItems(input.items);
  const productIds = mergedItems.map((item) => item.productId);
  const productRows = await db.select().from(products).where(and(inArray(products.id, productIds), eq(products.status, "active")));
  const productMap = new Map(productRows.map((product) => [product.id, product]));

  if (productRows.length !== productIds.length) {
    throw new Error("One or more products are unavailable.");
  }

  const lineItems = mergedItems.map((item) => {
    const product = productMap.get(item.productId);
    if (!product) throw new Error("One or more products are unavailable.");
    if (product.stockQuantity < item.quantity) {
      throw new Error(`${product.name} has only ${product.stockQuantity} item(s) in stock.`);
    }

    const baseLineTotal = product.basePrice * item.quantity;
    const lineTotal = product.salePrice * item.quantity;
    const discountAmount = baseLineTotal - lineTotal;

    return {
      productId: product.id,
      productName: product.name,
      sku: product.sku,
      quantity: item.quantity,
      unitPrice: product.salePrice,
      discountAmount,
      lineTotal,
      lensChoice: item.lensChoice ?? "none",
      prescriptionUrl: item.prescriptionUrl ?? null,
    };
  });

  const subtotal = lineItems.reduce((sum, item) => sum + item.lineTotal + item.discountAmount, 0);
  const totalDiscount = lineItems.reduce((sum, item) => sum + item.discountAmount, 0);
  const totalAmount = lineItems.reduce((sum, item) => sum + item.lineTotal, 0);
  const orderNumber = generateOrderNumber();

  await db.transaction(async (tx) => {
    if (input.contactPhone && input.contactPhone !== customer.phoneNumber) {
      await tx.update(customers).set({ phoneNumber: input.contactPhone }).where(eq(customers.id, customer.id));
    }

    await tx.insert(orders).values({
      orderNumber,
      customerId: customer.id,
      status: "pending",
      fulfillmentMethod: input.fulfillmentMethod,
      contactPhone,
      deliveryAddress: input.fulfillmentMethod === "delivery" ? input.deliveryAddress : null,
      subtotal,
      totalDiscount,
      totalAmount,
      paymentMethod: input.paymentMethod,
      paymentStatus: "pending",
      customerNotes: input.customerNotes,
    });

    const [created] = await tx.select({ id: orders.id }).from(orders).where(eq(orders.orderNumber, orderNumber)).limit(1);
    if (!created) throw new Error("Order creation failed.");

    await tx.insert(orderItems).values(lineItems.map((item) => ({ orderId: created.id, ...item })));

    for (const item of lineItems) {
      await tx
        .update(products)
        .set({ stockQuantity: sql`${products.stockQuantity} - ${item.quantity}` })
        .where(eq(products.id, item.productId));
    }
  });

  const order = await getCustomerOrderByNumber(customerId, orderNumber);
  if (!order) throw new Error("Order creation failed.");
  return order;
};

const customerOrderWhere = (customerId: number, query: CustomerOrderListQueryInput) => {
  const conditions: SQL[] = [eq(orders.customerId, customerId)];
  if (query.status?.length) conditions.push(inArray(orders.status, query.status));
  if (query.paymentStatus?.length) conditions.push(inArray(orders.paymentStatus, query.paymentStatus));
  if (query.activeOnly) conditions.push(sql`${orders.status} not in ('completed', 'cancelled', 'refunded')`);
  return and(...conditions);
};

export const listCustomerOrders = async (
  customerId: number,
  query: CustomerOrderListQueryInput,
): Promise<PaginatedOrders<PublicOrder>> => {
  const where = customerOrderWhere(customerId, query);
  const offset = (query.page - 1) * query.perPage;
  const [countRows, rows] = await Promise.all([
    db.select({ total: count() }).from(orders).where(where),
    db.select().from(orders).where(where).orderBy(desc(orders.createdAt), desc(orders.id)).limit(query.perPage).offset(offset),
  ]);

  const itemsByOrderId = await getOrderItemsByOrderIds(rows.map((row) => row.id));
  const total = countRows[0]?.total ?? 0;
  const totalPages = total === 0 ? 0 : Math.ceil(total / query.perPage);

  return {
    items: rows.map((row) => toPublicOrder(row, itemsByOrderId.get(row.id) ?? [])),
    pagination: {
      page: query.page,
      perPage: query.perPage,
      total,
      totalPages,
      hasNextPage: query.page < totalPages,
      hasPreviousPage: query.page > 1,
    },
  };
};

export const getCustomerOrderById = async (customerId: number, orderId: number) => {
  const [order] = await db.select().from(orders).where(and(eq(orders.customerId, customerId), eq(orders.id, orderId))).limit(1);
  if (!order) return null;
  const items = await getOrderItemsByOrderIds([order.id]);
  return toPublicOrder(order, items.get(order.id) ?? []);
};

export const getCustomerOrderByNumber = async (customerId: number, orderNumber: string) => {
  const [order] = await db.select().from(orders).where(and(eq(orders.customerId, customerId), eq(orders.orderNumber, orderNumber))).limit(1);
  if (!order) return null;
  const items = await getOrderItemsByOrderIds([order.id]);
  return toPublicOrder(order, items.get(order.id) ?? []);
};

const adminOrderWhere = (query: AdminOrderListQueryInput) => {
  const conditions: SQL[] = [];
  if (query.status?.length) conditions.push(inArray(orders.status, query.status));
  if (query.paymentStatus?.length) conditions.push(inArray(orders.paymentStatus, query.paymentStatus));
  if (query.fulfillmentMethod?.length) conditions.push(inArray(orders.fulfillmentMethod, query.fulfillmentMethod));
  if (query.from) conditions.push(gte(orders.createdAt, query.from));
  if (query.to) conditions.push(lte(orders.createdAt, query.to));
  if (query.q) {
    const pattern = `%${query.q}%`;
    conditions.push(
      or(
        like(orders.orderNumber, pattern),
        like(orders.contactPhone, pattern),
        like(customers.email, pattern),
        like(customers.name, pattern),
      )!,
    );
  }
  return conditions.length ? and(...conditions) : undefined;
};

const adminOrderSort = (sort: AdminOrderListQueryInput["sort"]) => {
  switch (sort) {
    case "oldest":
      return [asc(orders.createdAt), asc(orders.id)];
    case "amount_desc":
      return [desc(orders.totalAmount), desc(orders.id)];
    case "amount_asc":
      return [asc(orders.totalAmount), asc(orders.id)];
    case "newest":
    default:
      return [desc(orders.createdAt), desc(orders.id)];
  }
};

const adminOrderSelect = {
  order: orders,
  customer: {
    id: customers.id,
    email: customers.email,
    name: customers.name,
    phoneNumber: customers.phoneNumber,
  },
  staff: {
    id: staffUsers.id,
    name: staffUsers.name,
    email: staffUsers.email,
  },
};

const getAdminOrderRows = async (query: AdminOrderListQueryInput) => {
  const where = adminOrderWhere(query);
  return db
    .select(adminOrderSelect)
    .from(orders)
    .innerJoin(customers, eq(orders.customerId, customers.id))
    .leftJoin(staffUsers, eq(orders.managedByStaffId, staffUsers.id))
    .where(where)
    .orderBy(...adminOrderSort(query.sort))
    .limit(query.perPage)
    .offset((query.page - 1) * query.perPage);
};

export const listAdminOrders = async (query: AdminOrderListQueryInput): Promise<PaginatedOrders<AdminOrderListItem>> => {
  const where = adminOrderWhere(query);
  const [countRows, rows] = await Promise.all([
    db.select({ total: count() }).from(orders).innerJoin(customers, eq(orders.customerId, customers.id)).where(where),
    getAdminOrderRows(query),
  ]);

  const itemCounts = await getItemCountsByOrderIds(rows.map((row) => row.order.id));
  const items = rows.map((row) =>
    toAdminOrderListItem(
      row.order,
      row.customer,
      row.staff?.id ? row.staff : null,
      itemCounts.get(row.order.id) ?? 0,
    ),
  );

  const total = countRows[0]?.total ?? 0;
  const totalPages = total === 0 ? 0 : Math.ceil(total / query.perPage);

  return {
    items,
    pagination: {
      page: query.page,
      perPage: query.perPage,
      total,
      totalPages,
      hasNextPage: query.page < totalPages,
      hasPreviousPage: query.page > 1,
    },
  };
};

export const getAdminOrderById = async (orderId: number): Promise<AdminOrderDetail | null> => {
  const [row] = await db
    .select(adminOrderSelect)
    .from(orders)
    .innerJoin(customers, eq(orders.customerId, customers.id))
    .leftJoin(staffUsers, eq(orders.managedByStaffId, staffUsers.id))
    .where(eq(orders.id, orderId))
    .limit(1);

  if (!row) return null;

  const [itemsByOrderId, itemCounts] = await Promise.all([
    getOrderItemsByOrderIds([row.order.id]),
    getItemCountsByOrderIds([row.order.id]),
  ]);

  const listItem = toAdminOrderListItem(
    row.order,
    row.customer,
    row.staff?.id ? row.staff : null,
    itemCounts.get(row.order.id) ?? 0,
  );

  return toAdminOrderDetail(listItem, itemsByOrderId.get(row.order.id) ?? [], row.order.staffNotes);
};

export const updateAdminOrder = async (orderId: number, input: UpdateOrderInput, staffId: number) => {
  const [existing] = await db.select({ id: orders.id }).from(orders).where(eq(orders.id, orderId)).limit(1);
  if (!existing) return null;

  await db
    .update(orders)
    .set({
      ...(input.status !== undefined ? { status: input.status } : {}),
      ...(input.paymentStatus !== undefined ? { paymentStatus: input.paymentStatus } : {}),
      ...(input.staffNotes !== undefined ? { staffNotes: input.staffNotes } : {}),
      managedByStaffId: input.managedByStaffId === undefined ? staffId : input.managedByStaffId,
    })
    .where(eq(orders.id, orderId));

  return getAdminOrderById(orderId);
};

export const getDashboardSummary = async () => {
  const recentOrderQuery: AdminOrderListQueryInput = { page: 1, perPage: 8, sort: "newest" };

  const [
    totalOrdersRows,
    pendingOrdersRows,
    completedOrdersRows,
    customersRows,
    productsRows,
    activeProductsRows,
    lowStockRows,
    revenueRows,
    statusRows,
    paymentRows,
    recentOrders,
    lowStockProducts,
    topProducts,
  ] = await Promise.all([
    db.select({ total: count() }).from(orders),
    db.select({ total: count() }).from(orders).where(eq(orders.status, "pending")),
    db.select({ total: count() }).from(orders).where(eq(orders.status, "completed")),
    db.select({ total: count() }).from(customers),
    db.select({ total: count() }).from(products),
    db.select({ total: count() }).from(products).where(eq(products.status, "active")),
    db.select({ total: count() }).from(products).where(lte(products.stockQuantity, LOW_STOCK_THRESHOLD)),
    db.select({ revenue: sql<number>`coalesce(sum(${orders.totalAmount}), 0)` }).from(orders).where(eq(orders.status, "completed")),
    db.select({ status: orders.status, count: count() }).from(orders).groupBy(orders.status),
    db.select({ status: orders.paymentStatus, count: count() }).from(orders).groupBy(orders.paymentStatus),
    listAdminOrders(recentOrderQuery),
    db
      .select({
        id: products.id,
        sku: products.sku,
        name: products.name,
        slug: products.slug,
        stockQuantity: products.stockQuantity,
        status: products.status,
      })
      .from(products)
      .where(lte(products.stockQuantity, LOW_STOCK_THRESHOLD))
      .orderBy(asc(products.stockQuantity), asc(products.name))
      .limit(10),
    db
      .select({
        productId: orderItems.productId,
        productName: orderItems.productName,
        sku: orderItems.sku,
        quantitySold: sql<number>`coalesce(sum(${orderItems.quantity}), 0)`,
        revenue: sql<number>`coalesce(sum(${orderItems.lineTotal}), 0)`,
      })
      .from(orderItems)
      .groupBy(orderItems.productId, orderItems.productName, orderItems.sku)
      .orderBy(desc(sql`sum(${orderItems.quantity})`))
      .limit(10),
  ]);

  return {
    totals: {
      orders: totalOrdersRows[0]?.total ?? 0,
      pendingOrders: pendingOrdersRows[0]?.total ?? 0,
      completedOrders: completedOrdersRows[0]?.total ?? 0,
      customers: customersRows[0]?.total ?? 0,
      products: productsRows[0]?.total ?? 0,
      activeProducts: activeProductsRows[0]?.total ?? 0,
      lowStockProducts: lowStockRows[0]?.total ?? 0,
      revenue: Number(revenueRows[0]?.revenue ?? 0),
    },
    orderStatusBreakdown: statusRows.map((row) => ({ status: row.status, count: row.count })),
    paymentStatusBreakdown: paymentRows.map((row) => ({ status: row.status, count: row.count })),
    recentOrders: recentOrders.items,
    lowStockProducts,
    topProducts: topProducts.map((row) => ({
      productId: row.productId,
      productName: row.productName,
      sku: row.sku,
      quantitySold: Number(row.quantitySold),
      revenue: Number(row.revenue),
    })),
  };
};
