import {
  and,
  asc,
  count,
  desc,
  eq,
  gte,
  gt,
  inArray,
  like,
  lte,
  or,
  sql,
} from "drizzle-orm";
import type { SQL } from "drizzle-orm";
import type {
  CreateProductInput,
  ProductListQueryInput,
  UpdateProductInput,
} from "../@types/productRequests.js";
import type {
  PaginatedProducts,
  ProductDetail,
  ProductListItem,
  ProductLookup,
  ProductMedia,
} from "../@types/products.js";
import { db } from "../db/index.js";
import {
  ageGroups,
  brands,
  collections,
  faceShapes,
  frameColors,
  frameShapes,
  genders,
  lensFeatures,
  mediaAssets,
  occasions,
  productFaceShapes,
  productGenders,
  productLensFeatures,
  productOccasions,
  products,
  productTypes,
  sizes,
} from "../db/schema.js";
import {
  calculateSalePrice,
  slugify,
  toProductDetail,
  toProductListItem,
} from "./productSerializers.js";

const productSelect = {
  product: {
    id: products.id,
    sku: products.sku,
    name: products.name,
    slug: products.slug,
    description: products.description,
    basePrice: products.basePrice,
    discountPercent: products.discountPercent,
    salePrice: products.salePrice,
    stockQuantity: products.stockQuantity,
    status: products.status,
    isFeatured: products.isFeatured,
    averageRating: products.averageRating,
    ratingCount: products.ratingCount,
    createdAt: products.createdAt,
    updatedAt: products.updatedAt,
  },
  brand: {
    id: brands.id,
    name: brands.name,
    slug: brands.slug,
  },
  productType: {
    id: productTypes.id,
    name: productTypes.name,
    slug: productTypes.slug,
  },
  collection: {
    id: collections.id,
    name: collections.name,
    slug: collections.slug,
  },
  frameShape: {
    id: frameShapes.id,
    name: frameShapes.name,
    slug: frameShapes.slug,
  },
  frameColor: {
    id: frameColors.id,
    name: frameColors.name,
    slug: frameColors.slug,
    hexCode: frameColors.hexCode,
  },
  ageGroup: {
    id: ageGroups.id,
    name: ageGroups.name,
    slug: ageGroups.slug,
  },
  size: {
    id: sizes.id,
    name: sizes.name,
    slug: sizes.slug,
  },
};

const productBaseQuery = () =>
  db
    .select(productSelect)
    .from(products)
    .leftJoin(brands, eq(products.brandId, brands.id))
    .leftJoin(productTypes, eq(products.productTypeId, productTypes.id))
    .leftJoin(collections, eq(products.collectionId, collections.id))
    .leftJoin(frameShapes, eq(products.frameShapeId, frameShapes.id))
    .leftJoin(frameColors, eq(products.frameColorId, frameColors.id))
    .leftJoin(ageGroups, eq(products.ageGroupId, ageGroups.id))
    .leftJoin(sizes, eq(products.sizeId, sizes.id));

const productCountQuery = () =>
  db
    .select({ total: count() })
    .from(products)
    .leftJoin(brands, eq(products.brandId, brands.id))
    .leftJoin(productTypes, eq(products.productTypeId, productTypes.id))
    .leftJoin(collections, eq(products.collectionId, collections.id))
    .leftJoin(frameShapes, eq(products.frameShapeId, frameShapes.id))
    .leftJoin(frameColors, eq(products.frameColorId, frameColors.id))
    .leftJoin(ageGroups, eq(products.ageGroupId, ageGroups.id))
    .leftJoin(sizes, eq(products.sizeId, sizes.id));

const getIdsBySlugs = async (table: any, slugs?: string[]) => {
  if (!slugs || slugs.length === 0) return undefined;
  const rows = await db
    .select({ id: table.id })
    .from(table)
    .where(inArray(table.slug, slugs));
  return rows.map((row: { id: number }) => row.id);
};

const existsRelation = (
  relationTable: any,
  productIdColumn: any,
  relationIdColumn: any,
  ids?: number[],
) => {
  if (!ids || ids.length === 0) return undefined;
  return sql`exists (select 1 from ${relationTable} where ${productIdColumn} = ${products.id} and ${inArray(relationIdColumn, ids)})`;
};

const buildWhereConditions = async (
  query: ProductListQueryInput,
  includeAllStatuses: boolean,
) => {
  const conditions: SQL[] = [];

  if (includeAllStatuses) {
    if (query.status?.length)
      conditions.push(inArray(products.status, query.status));
  } else {
    conditions.push(eq(products.status, "active"));
  }

  if (query.q) {
    const pattern = `%${query.q}%`;
    conditions.push(
      or(
        like(products.name, pattern),
        like(products.sku, pattern),
        like(products.description, pattern),
      )!,
    );
  }

  if (query.brand?.length) conditions.push(inArray(brands.slug, query.brand));
  if (query.type?.length)
    conditions.push(inArray(productTypes.slug, query.type));
  if (query.collection?.length)
    conditions.push(inArray(collections.slug, query.collection));
  if (query.frameShape?.length)
    conditions.push(inArray(frameShapes.slug, query.frameShape));
  if (query.color?.length)
    conditions.push(inArray(frameColors.slug, query.color));
  if (query.ageGroup?.length)
    conditions.push(inArray(ageGroups.slug, query.ageGroup));
  if (query.size?.length) conditions.push(inArray(sizes.slug, query.size));
  if (query.minPrice !== undefined)
    conditions.push(gte(products.salePrice, query.minPrice));
  if (query.maxPrice !== undefined)
    conditions.push(lte(products.salePrice, query.maxPrice));
  if (query.rating !== undefined)
    conditions.push(gte(products.averageRating, query.rating.toFixed(2)));
  if (query.inStock === true) conditions.push(gt(products.stockQuantity, 0));
  if (query.inStock === false) conditions.push(eq(products.stockQuantity, 0));
  if (query.featured !== undefined)
    conditions.push(eq(products.isFeatured, query.featured));

  const [genderIds, faceShapeIds, lensFeatureIds, occasionIds] =
    await Promise.all([
      getIdsBySlugs(genders, query.gender),
      getIdsBySlugs(faceShapes, query.faceShape),
      getIdsBySlugs(lensFeatures, query.lens),
      getIdsBySlugs(occasions, query.occasion),
    ]);

  if (query.gender?.length && genderIds?.length === 0)
    conditions.push(sql`false`);
  if (query.faceShape?.length && faceShapeIds?.length === 0)
    conditions.push(sql`false`);
  if (query.lens?.length && lensFeatureIds?.length === 0)
    conditions.push(sql`false`);
  if (query.occasion?.length && occasionIds?.length === 0)
    conditions.push(sql`false`);

  const genderCondition = existsRelation(
    productGenders,
    productGenders.productId,
    productGenders.genderId,
    genderIds,
  );
  const faceShapeCondition = existsRelation(
    productFaceShapes,
    productFaceShapes.productId,
    productFaceShapes.faceShapeId,
    faceShapeIds,
  );
  const lensCondition = existsRelation(
    productLensFeatures,
    productLensFeatures.productId,
    productLensFeatures.lensFeatureId,
    lensFeatureIds,
  );
  const occasionCondition = existsRelation(
    productOccasions,
    productOccasions.productId,
    productOccasions.occasionId,
    occasionIds,
  );

  if (genderCondition) conditions.push(genderCondition);
  if (faceShapeCondition) conditions.push(faceShapeCondition);
  if (lensCondition) conditions.push(lensCondition);
  if (occasionCondition) conditions.push(occasionCondition);

  return conditions.length ? and(...conditions) : undefined;
};

const getOrderBy = (sort: ProductListQueryInput["sort"]) => {
  switch (sort) {
    case "oldest":
      return [asc(products.createdAt), asc(products.id)];
    case "price_asc":
      return [asc(products.salePrice), desc(products.id)];
    case "price_desc":
      return [desc(products.salePrice), desc(products.id)];
    case "rating_desc":
      return [
        desc(products.averageRating),
        desc(products.ratingCount),
        desc(products.id),
      ];
    case "popular":
      return [
        desc(products.ratingCount),
        desc(products.averageRating),
        desc(products.id),
      ];
    case "name_asc":
      return [asc(products.name), asc(products.id)];
    case "newest":
    default:
      return [desc(products.createdAt), desc(products.id)];
  }
};

const getMediaByProductIds = async (productIds: number[]) => {
  if (productIds.length === 0) return new Map<number, ProductMedia[]>();

  const rows = await db
    .select({
      ownerId: mediaAssets.ownerId,
      id: mediaAssets.id,
      url: mediaAssets.url,
      altText: mediaAssets.altText,
      isPrimary: mediaAssets.isPrimary,
      sortOrder: mediaAssets.sortOrder,
    })
    .from(mediaAssets)
    .where(
      and(
        eq(mediaAssets.ownerType, "product"),
        inArray(mediaAssets.ownerId, productIds),
      ),
    )
    .orderBy(
      desc(mediaAssets.isPrimary),
      asc(mediaAssets.sortOrder),
      asc(mediaAssets.id),
    );

  const map = new Map<number, ProductMedia[]>();
  for (const row of rows) {
    const entry = {
      id: row.id,
      url: row.url,
      altText: row.altText,
      isPrimary: row.isPrimary,
      sortOrder: row.sortOrder,
    };
    const existing = map.get(row.ownerId);
    if (existing) {
      existing.push(entry);
    } else {
      map.set(row.ownerId, [entry]);
    }
  }
  return map;
};

export const listProducts = async (
  query: ProductListQueryInput,
  options: { includeAllStatuses?: boolean } = {},
): Promise<PaginatedProducts> => {
  const includeAllStatuses = options.includeAllStatuses === true;
  const where = await buildWhereConditions(query, includeAllStatuses);
  const offset = (query.page - 1) * query.perPage;

  const [countRows, productRows] = await Promise.all([
    productCountQuery().where(where),
    productBaseQuery()
      .where(where)
      .orderBy(...getOrderBy(query.sort))
      .limit(query.perPage)
      .offset(offset),
  ]);

  const total = countRows[0]?.total ?? 0;
  const mediaMap = await getMediaByProductIds(
    productRows.map((row) => row.product.id),
  );
  const items = productRows.map((row) => {
    const productMedia = mediaMap.get(row.product.id) ?? [];
    return toProductListItem({
      ...row,
      primaryMedia: productMedia.find((m) => m.isPrimary) ?? productMedia[0] ?? null,
      media: productMedia,
    });
  });

  const totalPages = total === 0 ? 0 : Math.ceil(total / query.perPage);

  return {
    items,
    pagination: {
      page: query.page,
      perPage: query.perPage,
      total,
      totalPages,
      hasNextPage: query.page < totalPages,
      hasPreviousPage: query.page > 1,
    },
  };
};

const findProductRowBy = async (where: SQL) => {
  const [row] = await productBaseQuery().where(where).limit(1);
  if (!row) return null;

  const mediaMap = await getMediaByProductIds([row.product.id]);
  const productMedia = mediaMap.get(row.product.id) ?? [];
  return {
    ...row,
    primaryMedia: productMedia.find((m) => m.isPrimary) ?? productMedia[0] ?? null,
    media: productMedia,
  };
};

const getProductRelations = async (productId: number) => {
  const [media, genderRows, faceShapeRows, lensFeatureRows, occasionRows] =
    await Promise.all([
      db
        .select({
          id: mediaAssets.id,
          url: mediaAssets.url,
          altText: mediaAssets.altText,
          isPrimary: mediaAssets.isPrimary,
          sortOrder: mediaAssets.sortOrder,
        })
        .from(mediaAssets)
        .where(
          and(
            eq(mediaAssets.ownerType, "product"),
            eq(mediaAssets.ownerId, productId),
          ),
        )
        .orderBy(
          desc(mediaAssets.isPrimary),
          asc(mediaAssets.sortOrder),
          asc(mediaAssets.id),
        ),
      db
        .select({ id: genders.id, name: genders.name, slug: genders.slug })
        .from(productGenders)
        .innerJoin(genders, eq(productGenders.genderId, genders.id))
        .where(eq(productGenders.productId, productId))
        .orderBy(asc(genders.name)),
      db
        .select({
          id: faceShapes.id,
          name: faceShapes.name,
          slug: faceShapes.slug,
        })
        .from(productFaceShapes)
        .innerJoin(faceShapes, eq(productFaceShapes.faceShapeId, faceShapes.id))
        .where(eq(productFaceShapes.productId, productId))
        .orderBy(asc(faceShapes.name)),
      db
        .select({
          id: lensFeatures.id,
          name: lensFeatures.name,
          slug: lensFeatures.slug,
        })
        .from(productLensFeatures)
        .innerJoin(
          lensFeatures,
          eq(productLensFeatures.lensFeatureId, lensFeatures.id),
        )
        .where(eq(productLensFeatures.productId, productId))
        .orderBy(asc(lensFeatures.name)),
      db
        .select({
          id: occasions.id,
          name: occasions.name,
          slug: occasions.slug,
        })
        .from(productOccasions)
        .innerJoin(occasions, eq(productOccasions.occasionId, occasions.id))
        .where(eq(productOccasions.productId, productId))
        .orderBy(asc(occasions.name)),
    ]);

  return {
    media,
    genders: genderRows,
    faceShapes: faceShapeRows,
    lensFeatures: lensFeatureRows,
    occasions: occasionRows,
  };
};

export const getProductBySlug = async (
  slug: string,
  includeAllStatuses = false,
): Promise<ProductDetail | null> => {
  const statusCondition = includeAllStatuses
    ? undefined
    : eq(products.status, "active");
  const row = await findProductRowBy(
    and(
      eq(products.slug, slug),
      ...(statusCondition ? [statusCondition] : []),
    )!,
  );
  if (!row) return null;
  return toProductDetail(row, await getProductRelations(row.product.id));
};

export const getProductById = async (
  id: number,
): Promise<ProductDetail | null> => {
  const row = await findProductRowBy(eq(products.id, id));
  if (!row) return null;
  return toProductDetail(row, await getProductRelations(row.product.id));
};

const insertRelations = async (
  tx: any,
  productId: number,
  input: Pick<
    CreateProductInput,
    "genderIds" | "faceShapeIds" | "lensFeatureIds" | "occasionIds"
  >,
) => {
  if (input.genderIds.length) {
    await tx
      .insert(productGenders)
      .values(input.genderIds.map((genderId) => ({ productId, genderId })));
  }
  if (input.faceShapeIds.length) {
    await tx
      .insert(productFaceShapes)
      .values(
        input.faceShapeIds.map((faceShapeId) => ({ productId, faceShapeId })),
      );
  }
  if (input.lensFeatureIds.length) {
    await tx
      .insert(productLensFeatures)
      .values(
        input.lensFeatureIds.map((lensFeatureId) => ({
          productId,
          lensFeatureId,
        })),
      );
  }
  if (input.occasionIds.length) {
    await tx
      .insert(productOccasions)
      .values(
        input.occasionIds.map((occasionId) => ({ productId, occasionId })),
      );
  }
};

const insertMedia = async (
  tx: any,
  productId: number,
  media: CreateProductInput["media"],
  uploadedByStaffId: number,
) => {
  if (!media.length) return;
  const hasPrimary = media.some((item) => item.isPrimary);
  await tx.insert(mediaAssets).values(
    media.map((item, index) => ({
      ownerType: "product" as const,
      ownerId: productId,
      url: item.url,
      storageKey: item.storageKey,
      fileName: item.fileName,
      mimeType: item.mimeType,
      sizeBytes: item.sizeBytes,
      altText: item.altText,
      sortOrder: item.sortOrder,
      isPrimary: hasPrimary ? item.isPrimary : index === 0,
      uploadedByStaffId,
    })),
  );
};

export const createProduct = async (
  input: CreateProductInput,
  uploadedByStaffId: number,
) => {
  const finalSlug = input.slug ?? slugify(input.name);
  const finalSalePrice =
    input.salePrice ??
    calculateSalePrice(input.basePrice, input.discountPercent);

  await db.transaction(async (tx) => {
    await tx.insert(products).values({
      sku: input.sku,
      name: input.name,
      slug: finalSlug,
      description: input.description,
      brandId: input.brandId,
      productTypeId: input.productTypeId,
      collectionId: input.collectionId,
      frameShapeId: input.frameShapeId,
      frameColorId: input.frameColorId,
      ageGroupId: input.ageGroupId,
      sizeId: input.sizeId,
      basePrice: input.basePrice,
      discountPercent: input.discountPercent,
      salePrice: finalSalePrice,
      stockQuantity: input.stockQuantity,
      status: input.status,
      isFeatured: input.isFeatured,
    });

    const [created] = await tx
      .select({ id: products.id })
      .from(products)
      .where(eq(products.slug, finalSlug))
      .limit(1);
    if (!created) throw new Error("Product creation failed.");

    await insertRelations(tx, created.id, input);
    await insertMedia(tx, created.id, input.media, uploadedByStaffId);
  });

  const product = await getProductBySlug(finalSlug, true);
  if (!product) throw new Error("Product creation failed.");
  return product;
};

const replaceRelation = async (
  tx: any,
  table: any,
  productIdColumn: any,
  relationIdColumnName: string,
  productId: number,
  ids?: number[],
) => {
  if (ids === undefined) return;
  await tx.delete(table).where(eq(productIdColumn, productId));
  if (ids.length === 0) return;
  await tx
    .insert(table)
    .values(
      ids.map((relationId) => ({
        productId,
        [relationIdColumnName]: relationId,
      })),
    );
};

export const updateProduct = async (
  productId: number,
  input: UpdateProductInput,
  uploadedByStaffId: number,
) => {
  const updateValues: Partial<typeof products.$inferInsert> = {};

  if (input.sku !== undefined) updateValues.sku = input.sku;
  if (input.name !== undefined) updateValues.name = input.name;
  if (input.slug !== undefined) updateValues.slug = input.slug;
  if (input.description !== undefined)
    updateValues.description = input.description;
  if (input.brandId !== undefined) updateValues.brandId = input.brandId;
  if (input.productTypeId !== undefined)
    updateValues.productTypeId = input.productTypeId;
  if (input.collectionId !== undefined)
    updateValues.collectionId = input.collectionId;
  if (input.frameShapeId !== undefined)
    updateValues.frameShapeId = input.frameShapeId;
  if (input.frameColorId !== undefined)
    updateValues.frameColorId = input.frameColorId;
  if (input.ageGroupId !== undefined)
    updateValues.ageGroupId = input.ageGroupId;
  if (input.sizeId !== undefined) updateValues.sizeId = input.sizeId;
  if (input.basePrice !== undefined) updateValues.basePrice = input.basePrice;
  if (input.discountPercent !== undefined)
    updateValues.discountPercent = input.discountPercent;
  if (input.stockQuantity !== undefined)
    updateValues.stockQuantity = input.stockQuantity;
  if (input.status !== undefined) updateValues.status = input.status;
  if (input.isFeatured !== undefined)
    updateValues.isFeatured = input.isFeatured;

  const current = await getProductById(productId);
  if (!current) return null;

  if (input.salePrice !== undefined) {
    updateValues.salePrice = input.salePrice;
  } else if (
    input.basePrice !== undefined ||
    input.discountPercent !== undefined
  ) {
    updateValues.salePrice = calculateSalePrice(
      input.basePrice ?? current.basePrice,
      input.discountPercent ?? current.discountPercent,
    );
  }

  await db.transaction(async (tx) => {
    if (Object.keys(updateValues).length > 0) {
      await tx
        .update(products)
        .set(updateValues)
        .where(eq(products.id, productId));
    }

    await replaceRelation(
      tx,
      productGenders,
      productGenders.productId,
      "genderId",
      productId,
      input.genderIds,
    );
    await replaceRelation(
      tx,
      productFaceShapes,
      productFaceShapes.productId,
      "faceShapeId",
      productId,
      input.faceShapeIds,
    );
    await replaceRelation(
      tx,
      productLensFeatures,
      productLensFeatures.productId,
      "lensFeatureId",
      productId,
      input.lensFeatureIds,
    );
    await replaceRelation(
      tx,
      productOccasions,
      productOccasions.productId,
      "occasionId",
      productId,
      input.occasionIds,
    );

    if (input.media !== undefined) {
      await tx
        .delete(mediaAssets)
        .where(
          and(
            eq(mediaAssets.ownerType, "product"),
            eq(mediaAssets.ownerId, productId),
          ),
        );
      await insertMedia(tx, productId, input.media, uploadedByStaffId);
    }
  });

  return getProductById(productId);
};

export const archiveProduct = async (productId: number) => {
  const existing = await getProductById(productId);
  if (!existing) return null;

  await db
    .update(products)
    .set({ status: "archived" })
    .where(eq(products.id, productId));
  return getProductById(productId);
};
