import { and, asc, desc, eq, like, count } from "drizzle-orm";
import type {
  CreateClientReviewInput,
  UpdateClientReviewInput,
  ClientReviewAdminListItem,
} from "../@types/clientReviewRequests.js";
import { db } from "../db/index.js";
import { clientReviews, mediaAssets } from "../db/schema.js";
import { toClientReviewListItem } from "./clientReviewSerializers.js";

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

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

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

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

export const getActiveClientReviews = async (): Promise<ClientReviewAdminListItem[]> => {
  const rows = await db
    .select()
    .from(clientReviews)
    .where(eq(clientReviews.isActive, true))
    .orderBy(asc(clientReviews.sortOrder), desc(clientReviews.createdAt));

  const items = await Promise.all(
    rows.map(async (row) => {
      const mediaRows = await fetchReviewMedia(row.id);
      return toClientReviewListItem(row, mediaRows);
    }),
  );

  return items;
};

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

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

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

  const [dataRows, countResult] = await Promise.all([
    db
      .select()
      .from(clientReviews)
      .where(whereClause)
      .orderBy(asc(clientReviews.sortOrder), desc(clientReviews.createdAt))
      .limit(perPage)
      .offset((page - 1) * perPage),
    db
      .select({ count: count() })
      .from(clientReviews)
      .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 = await fetchReviewMedia(row.id);
      return toClientReviewListItem(row, mediaRows);
    }),
  );

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

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

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

  const mediaRows = await fetchReviewMedia(row.id);
  return toClientReviewListItem(row, mediaRows);
};

export const createClientReview = async (
  input: CreateClientReviewInput,
  staffId: number,
): Promise<ClientReviewAdminListItem> => {
  const result = await db.transaction(async (tx) => {
    await tx.insert(clientReviews).values({
      clientName: input.clientName,
      reviewText: input.reviewText,
      postUrl: input.postUrl ?? null,
      starRating: input.starRating ?? 5,
      sortOrder: input.sortOrder ?? 0,
    });

    const inserted = await tx
      .select({ id: clientReviews.id })
      .from(clientReviews)
      .orderBy(desc(clientReviews.id))
      .limit(1);

    const createdId = inserted[0]?.id;
    if (!createdId) throw new Error("CLIENT_REVIEW_CREATE_FAILED");

    if (input.media?.length) {
      await insertMedia(tx, createdId, input.media, staffId);
    }

    return createdId;
  });

  return (await getClientReviewById(result))!;
};

export const updateClientReview = async (
  id: number,
  input: UpdateClientReviewInput,
  staffId: number,
): Promise<ClientReviewAdminListItem> => {
  const existing = await getClientReviewById(id);
  if (!existing) {
    throw new Error("CLIENT_REVIEW_NOT_FOUND");
  }

  const updateData: Record<string, any> = {};
  if (input.clientName !== undefined) updateData.clientName = input.clientName;
  if (input.reviewText !== undefined) updateData.reviewText = input.reviewText;
  if (input.postUrl !== undefined) updateData.postUrl = input.postUrl;
  if (input.starRating !== undefined) updateData.starRating = input.starRating;
  if (input.sortOrder !== undefined) updateData.sortOrder = input.sortOrder;
  if (input.isActive !== undefined) updateData.isActive = input.isActive;

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

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

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

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

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