import { asc, eq } from "drizzle-orm";
import type {
  ProductFiltersResponse,
  FrameShapeWithMedia,
  BrandWithMedia,
  ProductTypeWithMedia,
  ProductMedia,
} from "../@types/products.js";
import { db } from "../db/index.js";
import {
  ageGroups,
  brands,
  collections,
  faceShapes,
  frameColors,
  frameShapes,
  genders,
  lensFeatures,
  mediaAssets,
  occasions,
  priceRanges,
  productTypes,
  sizes,
} from "../db/schema.js";

const activeLookup = async (table: any) => {
  return db
    .select({ id: table.id, name: table.name, slug: table.slug })
    .from(table)
    .where(eq(table.isActive, true))
    .orderBy(asc(table.name));
};

export const getProductFilters = async (): Promise<ProductFiltersResponse> => {
  const [
    brandRows,
    productTypeRows,
    collectionRows,
    occasionRows,
    frameShapeRows,
    faceShapeRows,
    genderRows,
    frameColorRows,
    lensFeatureRows,
    ageGroupRows,
    sizeRows,
    priceRangeRows,
  ] = await Promise.all([
    db
      .select({ id: brands.id, name: brands.name, slug: brands.slug })
      .from(brands)
      .where(eq(brands.isActive, true))
      .orderBy(asc(brands.name)),
    activeLookup(productTypes),
    activeLookup(collections),
    activeLookup(occasions),
    activeLookup(frameShapes),
    activeLookup(faceShapes),
    activeLookup(genders),
    db
      .select({ id: frameColors.id, name: frameColors.name, slug: frameColors.slug, hexCode: frameColors.hexCode })
      .from(frameColors)
      .where(eq(frameColors.isActive, true))
      .orderBy(asc(frameColors.name)),
    activeLookup(lensFeatures),
    db
      .select({ id: ageGroups.id, name: ageGroups.name, slug: ageGroups.slug, minAge: ageGroups.minAge, maxAge: ageGroups.maxAge })
      .from(ageGroups)
      .where(eq(ageGroups.isActive, true))
      .orderBy(asc(ageGroups.sortOrder), asc(ageGroups.name)),
    activeLookup(sizes),
    db
      .select({ id: priceRanges.id, name: priceRanges.name, slug: priceRanges.slug, minPrice: priceRanges.minPrice, maxPrice: priceRanges.maxPrice })
      .from(priceRanges)
      .where(eq(priceRanges.isActive, true))
      .orderBy(asc(priceRanges.sortOrder), asc(priceRanges.minPrice)),
  ]);

  // Fetch media for brands
  const brandMediaRows = await db
    .select({
      ownerId: mediaAssets.ownerId,
      id: mediaAssets.id,
      url: mediaAssets.url,
      altText: mediaAssets.altText,
      isPrimary: mediaAssets.isPrimary,
      sortOrder: mediaAssets.sortOrder,
    })
    .from(mediaAssets)
    .where(eq(mediaAssets.ownerType, "brand"))
    .orderBy(asc(mediaAssets.sortOrder));

  const mediaByBrandId = new Map<number, ProductMedia[]>();
  for (const row of brandMediaRows) {
    const existing = mediaByBrandId.get(row.ownerId) || [];
    existing.push({
      id: row.id,
      url: row.url,
      altText: row.altText,
      isPrimary: row.isPrimary,
      sortOrder: row.sortOrder,
    });
    mediaByBrandId.set(row.ownerId, existing);
  }

  const brandsWithMedia: BrandWithMedia[] = brandRows.map((b) => ({
    id: b.id,
    name: b.name,
    slug: b.slug,
    media: mediaByBrandId.get(b.id) || [],
  }));

  // Fetch media for frame shapes
  const frameShapeMediaRows = await db
    .select({
      ownerId: mediaAssets.ownerId,
      id: mediaAssets.id,
      url: mediaAssets.url,
      altText: mediaAssets.altText,
      isPrimary: mediaAssets.isPrimary,
      sortOrder: mediaAssets.sortOrder,
    })
    .from(mediaAssets)
    .where(eq(mediaAssets.ownerType, "frame_shape"))
    .orderBy(asc(mediaAssets.sortOrder));

  const mediaByShapeId = new Map<number, ProductMedia[]>();
  for (const row of frameShapeMediaRows) {
    const existing = mediaByShapeId.get(row.ownerId) || [];
    existing.push({
      id: row.id,
      url: row.url,
      altText: row.altText,
      isPrimary: row.isPrimary,
      sortOrder: row.sortOrder,
    });
    mediaByShapeId.set(row.ownerId, existing);
  }

  const frameShapesWithMedia: FrameShapeWithMedia[] = frameShapeRows.map((fs) => ({
    id: fs.id,
    name: fs.name,
    slug: fs.slug,
    media: mediaByShapeId.get(fs.id) || [],
  }));

  // Fetch media for product types
  const productTypeMediaRows = await db
    .select({
      ownerId: mediaAssets.ownerId,
      id: mediaAssets.id,
      url: mediaAssets.url,
      altText: mediaAssets.altText,
      isPrimary: mediaAssets.isPrimary,
      sortOrder: mediaAssets.sortOrder,
    })
    .from(mediaAssets)
    .where(eq(mediaAssets.ownerType, "product_type"))
    .orderBy(asc(mediaAssets.sortOrder));

  const mediaByProductTypeId = new Map<number, ProductMedia[]>();
  for (const row of productTypeMediaRows) {
    const existing = mediaByProductTypeId.get(row.ownerId) || [];
    existing.push({
      id: row.id,
      url: row.url,
      altText: row.altText,
      isPrimary: row.isPrimary,
      sortOrder: row.sortOrder,
    });
    mediaByProductTypeId.set(row.ownerId, existing);
  }

  const productTypesWithMedia: ProductTypeWithMedia[] = productTypeRows.map((pt) => ({
    id: pt.id,
    name: pt.name,
    slug: pt.slug,
    media: mediaByProductTypeId.get(pt.id) || [],
  }));

  return {
    brands: brandsWithMedia,
    productTypes: productTypesWithMedia,
    collections: collectionRows,
    occasions: occasionRows,
    frameShapes: frameShapesWithMedia,
    faceShapes: faceShapeRows,
    genders: genderRows,
    frameColors: frameColorRows,
    lensFeatures: lensFeatureRows,
    ageGroups: ageGroupRows,
    sizes: sizeRows,
    priceRanges: priceRangeRows,
    sortOptions: [
      { value: "newest", label: "Newest" },
      { value: "oldest", label: "Oldest" },
      { value: "price_asc", label: "Price: Low to High" },
      { value: "price_desc", label: "Price: High to Low" },
      { value: "rating_desc", label: "Top Rated" },
      { value: "popular", label: "Most Popular" },
      { value: "name_asc", label: "Name: A to Z" },
    ],
  };
};
