import { and, asc, eq, like, count } from "drizzle-orm";
import type {
  CreateBrandInput,
  UpdateBrandInput,
  BrandAdminListItem,
} from "../@types/brandRequests.js";
import { db } from "../db/index.js";
import { brands, mediaAssets, products } from "../db/schema.js";
import { toBrandListItem, slugifyBrand } from "./brandSerializers.js";

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

  await tx.insert(mediaAssets).values(
    mediaItems.map((m) => ({
      ownerType: "brand" 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: CreateBrandInput["media"],
  uploadedByStaffId: number,
) => {
  await tx
    .delete(mediaAssets)
    .where(
      and(
        eq(mediaAssets.ownerType, "brand"),
        eq(mediaAssets.ownerId, ownerTypeId),
      ),
    );

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

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

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

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

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

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

  const [dataRows, countResult] = await Promise.all([
    db
      .select()
      .from(brands)
      .where(whereClause)
      .orderBy(asc(brands.name))
      .limit(perPage)
      .offset((page - 1) * perPage),
    db
      .select({ count: count() })
      .from(brands)
      .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([
        fetchBrandMedia(row.id),
        fetchProductCount(row.id),
      ]);
      return toBrandListItem(row, mediaRows, productCount);
    }),
  );

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

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

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

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

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

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

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

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

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

export const createBrand = async (
  input: CreateBrandInput,
  staffId: number,
): Promise<BrandAdminListItem> => {
  const finalSlug = input.slug ?? slugifyBrand(input.name);

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

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

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

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

export const updateBrand = async (
  id: number,
  input: UpdateBrandInput,
  staffId: number,
): Promise<BrandAdminListItem> => {
  const existing = await getBrandById(id);
  if (!existing) {
    throw new Error("BRAND_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(brands)
      .set(updateData)
      .where(eq(brands.id, id));
  }

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

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

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

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