import { and, asc, count, eq, like } from "drizzle-orm";
import type {
  CreateProductTypeInput,
  ProductTypeAdminListItem,
  UpdateProductTypeInput,
} from "../@types/productTypeRequests.js";
import { db } from "../db/index.js";
import { mediaAssets, products, productTypes } from "../db/schema.js";
import {
  slugifyProductType,
  toProductTypeListItem,
} from "./productTypeSerializers.js";

const insertMedia = async (
  tx: any,
  ownerTypeId: number,
  mediaItems: CreateProductTypeInput["media"],
  uploadedByStaffId: number,
) => {
  if (!mediaItems?.length) return;

  await tx.insert(mediaAssets).values(
    mediaItems.map((m) => ({
      ownerType: "product_type" as const,
      ownerId: ownerTypeId,
      url: m.url,
      storageKey: m.storageKey || null,
      fileName: m.fileName || null,
      mimeType: m.mimeType || null,
      sizeBytes: m.sizeBytes || null,
      altText: m.altText || null,
      sortOrder: m.sortOrder ?? 0,
      isPrimary: m.isPrimary ?? false,
      uploadedByStaffId,
    })),
  );
};

const replaceMedia = async (
  tx: any,
  ownerTypeId: number,
  mediaItems: CreateProductTypeInput["media"],
  uploadedByStaffId: number,
) => {
  await tx
    .delete(mediaAssets)
    .where(
      and(
        eq(mediaAssets.ownerType, "product_type"),
        eq(mediaAssets.ownerId, ownerTypeId),
      ),
    );

  if (mediaItems?.length) {
    await insertMedia(tx, ownerTypeId, mediaItems, uploadedByStaffId);
  }
};

const fetchProductTypeMedia = async (productTypeId: number) => {
  return db
    .select()
    .from(mediaAssets)
    .where(
      and(
        eq(mediaAssets.ownerType, "product_type"),
        eq(mediaAssets.ownerId, productTypeId),
      ),
    )
    .orderBy(asc(mediaAssets.sortOrder));
};

const fetchProductCount = async (productTypeId: number) => {
  const result = await db
    .select({ count: count() })
    .from(products)
    .where(eq(products.productTypeId, productTypeId));
  return result[0]?.count || 0;
};

export const listProductTypes = async (
  query: { q?: string; page: number; perPage: number; includeInactive?: boolean },
): Promise<{
  items: ProductTypeAdminListItem[];
  total: number;
  page: number;
  perPage: number;
  totalPages: number;
}> => {
  const { q, page, perPage, includeInactive } = query;

  const conditions = [];
  if (!includeInactive) {
    conditions.push(eq(productTypes.isActive, true));
  }
  if (q) {
    conditions.push(like(productTypes.name, `%${q}%`));
  }

  const whereClause = conditions.length ? and(...conditions) : undefined;

  const [dataRows, countResult] = await Promise.all([
    db
      .select()
      .from(productTypes)
      .where(whereClause)
      .orderBy(asc(productTypes.name))
      .limit(perPage)
      .offset((page - 1) * perPage),
    db
      .select({ count: count() })
      .from(productTypes)
      .where(whereClause),
  ]);

  const total = countResult[0]?.count || 0;
  const totalPages = Math.ceil(total / perPage);

  const items = await Promise.all(
    dataRows.map(async (row) => {
      const [mediaRows, productCount] = await Promise.all([
        fetchProductTypeMedia(row.id),
        fetchProductCount(row.id),
      ]);
      return toProductTypeListItem(row, mediaRows, productCount);
    }),
  );

  return { items, total, page, perPage, totalPages };
};

export const getProductTypeById = async (
  id: number,
): Promise<ProductTypeAdminListItem | null> => {
  const rows = await db
    .select()
    .from(productTypes)
    .where(eq(productTypes.id, id))
    .limit(1);

  const row = rows[0];
  if (!row) return null;

  const [mediaRows, productCount] = await Promise.all([
    fetchProductTypeMedia(row.id),
    fetchProductCount(row.id),
  ]);

  return toProductTypeListItem(row, mediaRows, productCount);
};

export const getProductTypeBySlug = async (
  slug: string,
): Promise<ProductTypeAdminListItem | null> => {
  const rows = await db
    .select()
    .from(productTypes)
    .where(eq(productTypes.slug, slug))
    .limit(1);

  const row = rows[0];
  if (!row) return null;

  const [mediaRows, productCount] = await Promise.all([
    fetchProductTypeMedia(row.id),
    fetchProductCount(row.id),
  ]);

  return toProductTypeListItem(row, mediaRows, productCount);
};

export const createProductType = async (
  input: CreateProductTypeInput,
  staffId: number,
): Promise<ProductTypeAdminListItem> => {
  const finalSlug = input.slug ?? slugifyProductType(input.name);

  await db.transaction(async (tx) => {
    await tx.insert(productTypes).values({
      name: input.name,
      slug: finalSlug,
    });

    const created = await tx
      .select({ id: productTypes.id })
      .from(productTypes)
      .where(eq(productTypes.slug, finalSlug))
      .limit(1);

    if (created[0] && input.media?.length) {
      await insertMedia(tx, created[0].id, input.media, staffId);
    }
  });

  const result = await getProductTypeBySlug(finalSlug);
  return result!;
};

export const updateProductType = async (
  id: number,
  input: UpdateProductTypeInput,
  staffId: number,
): Promise<ProductTypeAdminListItem> => {
  const existing = await getProductTypeById(id);
  if (!existing) {
    throw new Error("PRODUCT_TYPE_NOT_FOUND");
  }

  const updateData: Record<string, any> = {};
  if (input.name !== undefined) updateData.name = input.name;
  if (input.slug !== undefined) updateData.slug = input.slug;
  if (input.isActive !== undefined) updateData.isActive = input.isActive;

  if (Object.keys(updateData).length > 0) {
    await db
      .update(productTypes)
      .set(updateData)
      .where(eq(productTypes.id, id));
  }

  if (input.media !== undefined) {
    await db.transaction(async (tx) => {
      await replaceMedia(tx, id, input.media || [], staffId);
    });
  }

  return (await getProductTypeById(id))!;
};

export const deleteProductType = async (id: number): Promise<void> => {
  const existing = await getProductTypeById(id);
  if (!existing) {
    throw new Error("PRODUCT_TYPE_NOT_FOUND");
  }

  await db
    .update(productTypes)
    .set({ isActive: false })
    .where(eq(productTypes.id, id));
};
