import "server-only";
import { and, asc, desc, eq, sql } from "drizzle-orm";
import { db } from "@/lib/db/client";
import {
  storeCategories,
  storeProducts,
  storeOrders,
  storeOrderLines,
  type StoreOrder,
  type StoreOrderLine,
} from "@/lib/db/schema";

export type StoreCategorySummary = {
  id: string;
  slug: string;
  name: string;
  description: string;
  productCount: number;
};

export async function listPublishedCategoriesWithCounts(): Promise<
  StoreCategorySummary[]
> {
  const cats = await db
    .select()
    .from(storeCategories)
    .orderBy(asc(storeCategories.position));
  const prods = await db
    .select({ categoryId: storeProducts.categoryId, status: storeProducts.status })
    .from(storeProducts);
  return cats.map((c) => ({
    id: c.id,
    slug: c.slug,
    name: c.name,
    description: c.description,
    productCount: prods.filter(
      (p) => p.categoryId === c.id && p.status === "published",
    ).length,
  }));
}

export async function listPublishedProducts() {
  return db
    .select()
    .from(storeProducts)
    .where(eq(storeProducts.status, "published"))
    .orderBy(desc(storeProducts.publishedAt));
}

export async function getPublishedProductBySlug(slug: string) {
  const [row] = await db
    .select()
    .from(storeProducts)
    .where(and(eq(storeProducts.slug, slug), eq(storeProducts.status, "published")))
    .limit(1);
  return row ?? null;
}

export async function listProductsForAdmin() {
  return db.select().from(storeProducts).orderBy(desc(storeProducts.createdAt));
}

export async function listCategoriesForAdmin() {
  return db.select().from(storeCategories).orderBy(asc(storeCategories.position));
}

export async function getProductForAdmin(id: string) {
  const [row] = await db
    .select()
    .from(storeProducts)
    .where(eq(storeProducts.id, id))
    .limit(1);
  return row ?? null;
}

export function formatPrice(priceMinor: number, currency: string): string {
  const major = priceMinor / 100;
  try {
    return new Intl.NumberFormat("en-GB", {
      style: "currency",
      currency,
    }).format(major);
  } catch {
    return `${currency} ${major.toFixed(2)}`;
  }
}

// -------------------------------------------------------------------------
// Orders
// -------------------------------------------------------------------------

/** Sequential OG-00001 style number. Uses count(*)+1 for simplicity. */
export async function nextOrderNumber(): Promise<string> {
  const [row] = await db
    .select({ n: sql<number>`count(*)`.mapWith(Number) })
    .from(storeOrders);
  const seq = (row?.n ?? 0) + 1;
  return `OG-${String(seq).padStart(5, "0")}`;
}

export async function getOrderForUser(
  orderId: string,
  userId: string | null,
): Promise<{ order: StoreOrder; lines: StoreOrderLine[] } | null> {
  const [order] = await db
    .select()
    .from(storeOrders)
    .where(eq(storeOrders.id, orderId))
    .limit(1);
  if (!order) return null;
  // Either the signed-in user owns it OR admin (we guard admin separately).
  if (userId && order.userId && order.userId !== userId) {
    return null;
  }
  const lines = await db
    .select()
    .from(storeOrderLines)
    .where(eq(storeOrderLines.orderId, order.id))
    .orderBy(asc(storeOrderLines.position));
  return { order, lines };
}

export async function listOrdersForUser(userId: string): Promise<StoreOrder[]> {
  return db
    .select()
    .from(storeOrders)
    .where(eq(storeOrders.userId, userId))
    .orderBy(desc(storeOrders.createdAt));
}

export async function listOrdersForAdmin(statusFilter?: string) {
  const rows = await db
    .select()
    .from(storeOrders)
    .orderBy(desc(storeOrders.createdAt))
    .limit(500);
  if (!statusFilter || statusFilter === "all") return rows;
  return rows.filter((r) => r.status === statusFilter);
}

export async function getOrderForAdmin(
  orderId: string,
): Promise<{ order: StoreOrder; lines: StoreOrderLine[] } | null> {
  const [order] = await db
    .select()
    .from(storeOrders)
    .where(eq(storeOrders.id, orderId))
    .limit(1);
  if (!order) return null;
  const lines = await db
    .select()
    .from(storeOrderLines)
    .where(eq(storeOrderLines.orderId, order.id))
    .orderBy(asc(storeOrderLines.position));
  return { order, lines };
}

export async function countPendingOrders(): Promise<number> {
  const [row] = await db
    .select({ n: sql<number>`count(*)`.mapWith(Number) })
    .from(storeOrders)
    .where(eq(storeOrders.status, "pending_invoice"));
  return row?.n ?? 0;
}
