import { and, asc, eq, like, count } from "drizzle-orm";
import type {
  CreateFrameShapeInput,
  UpdateFrameShapeInput,
  FrameShapeAdminListItem,
} from "../@types/frameShapeRequests.js";
import { db } from "../db/index.js";
import { frameShapes, mediaAssets, products } from "../db/schema.js";
import { toFrameShapeListItem, slugifyFrameShape } from "./frameShapeSerializers.js";

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

export const createFrameShape = async (
  input: CreateFrameShapeInput,
  staffId: number,
): Promise<FrameShapeAdminListItem> => {
  const finalSlug = input.slug ?? slugifyFrameShape(input.name);

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

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

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

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

export const updateFrameShape = async (
  id: number,
  input: UpdateFrameShapeInput,
  staffId: number,
): Promise<FrameShapeAdminListItem> => {
  const existing = await getFrameShapeById(id);
  if (!existing) {
    throw new Error("FRAME_SHAPE_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(frameShapes)
      .set(updateData)
      .where(eq(frameShapes.id, id));
  }

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

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

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

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