import { BUNDLES_ENABLED } from '@/lib/features';
import type { NextApiRequest, NextApiResponse } from 'next';
import mysql from 'mysql2/promise';

interface BundleProduct {
  id: number;
  variantId: number;
  slug: string;
  name: string;
  brandName: string;
  image: string;
  volume: string;
  volumeMl: number;
  price: number;
  productStock: number;
}

interface BundleVariant {
  id: number;
  name: string;
  currentPrice: number;
  originalPrice: number;
  volumeMl?: number;
  products?: BundleProduct[];
}

interface Bundle {
  id: number;
  slug: string;
  name: string;
  image: string;
  products: BundleProduct[];
  currentPrice: number;
  originalPrice: number;
  bundleProductId?: number;
  bundleVariantId?: number;
  variants?: BundleVariant[];
}

interface ErrorResponse {
  error: string;
}

const pool = mysql.createPool({
  host: process.env.DB_HOST || 'localhost',
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || '',
  database: process.env.DB_NAME || 'perfume_shop',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
});

export default async function handler(
  req: NextApiRequest,
  res: NextApiResponse<Bundle[] | ErrorResponse>
) {
  if (!BUNDLES_ENABLED) {
    return res.status(404).json({ error: 'Not found' });
  }

  if (req.method !== 'GET') {
    return res.status(405).json({ error: 'Method not allowed' });
  }

  try {
    const connection = await pool.getConnection();

    // Get all bundles with their bundle variants
    const bundlesQuery = `
      SELECT DISTINCT
        b.id AS bundle_id,
        b.product_id AS bundle_product_id,
        bpv.id AS bundle_variant_id,
        bpv.label AS bundle_variant_name,
        bpv.volume_ml AS bundle_volume_ml,
        bp.name AS bundle_name,
        bp.slug AS bundle_slug,
        bundle_pr.price AS bundle_price
      FROM bundles b
      LEFT JOIN products bp ON bp.id = b.product_id
      LEFT JOIN product_variants bpv ON bpv.product_id = b.product_id AND bpv.is_active = 1
      LEFT JOIN prices bundle_pr ON bundle_pr.variant_id = bpv.id AND bundle_pr.valid_to IS NULL
      ORDER BY b.id, bpv.id
    `;

    // Get all products in bundles with ALL their variants and prices
    const productsQuery = `
      SELECT
        bi.bundle_id,
        p.id AS product_id,
        p.name AS product_name,
        p.slug AS product_slug,
        br.name AS brand_name,
        img.url AS product_image,
        ps.stock AS product_stock,
        pv.id AS variant_id,
        pv.label AS variant_label,
        pv.volume_ml,
        pr.price
      FROM bundle_items bi
      JOIN products p ON p.id = (SELECT product_id FROM product_variants WHERE id = bi.product_variant_id)
      JOIN brands br ON br.id = p.brand_id
      JOIN product_variants pv ON pv.product_id = p.id AND pv.is_active = 1
      LEFT JOIN product_images img ON img.product_id = p.id AND img.is_primary = 1
      LEFT JOIN product_stock ps ON ps.product_id = p.id
      JOIN prices pr ON pr.variant_id = pv.id AND pr.valid_to IS NULL
      ORDER BY bi.bundle_id, p.id, pv.volume_ml
    `;

    const [bundleRows] = await connection.query(bundlesQuery);
    const [productRows] = await connection.query(productsQuery);
    connection.release();

    // Map all products by bundle_id -> product_id -> [variants with prices]
    const bundleProductVariantsMap = new Map<number, Map<number, any[]>>();
    const bundleImageMap = new Map<number, string>();

    for (const row of productRows as any[]) {
      if (!bundleProductVariantsMap.has(row.bundle_id)) {
        bundleProductVariantsMap.set(row.bundle_id, new Map());
      }
      const productsMap = bundleProductVariantsMap.get(row.bundle_id)!;

      if (!productsMap.has(row.product_id)) {
        productsMap.set(row.product_id, []);
        // Store first image
        if (row.product_image && !bundleImageMap.has(row.bundle_id)) {
          bundleImageMap.set(row.bundle_id, row.product_image);
        }
      }

      productsMap.get(row.product_id)!.push(row);
    }

    // Build bundles with variants
    const bundlesMap = new Map<number, Bundle>();
    const bundleVariantsMap = new Map<string, BundleVariant>();

    for (const bundleRow of bundleRows as any[]) {
      if (!bundlesMap.has(bundleRow.bundle_id)) {
        bundlesMap.set(bundleRow.bundle_id, {
          id: bundleRow.bundle_id,
          slug: bundleRow.bundle_slug || `sada-${bundleRow.bundle_id}`,
          name: bundleRow.bundle_name || `Výhodná sada č. ${bundleRow.bundle_id}`,
          image: bundleImageMap.get(bundleRow.bundle_id) || '/images/placeholder.webp',
          products: [],
          currentPrice: 0,
          originalPrice: 0,
          bundleProductId: bundleRow.bundle_product_id,
          bundleVariantId: bundleRow.bundle_variant_id,
          variants: [],
        });
      }

      if (bundleRow.bundle_variant_id) {
        const variantKey = `${bundleRow.bundle_id}_${bundleRow.bundle_variant_id}`;
        if (!bundleVariantsMap.has(variantKey)) {
          const variant: BundleVariant = {
            id: bundleRow.bundle_variant_id,
            name: bundleRow.bundle_variant_name || `${bundleRow.bundle_volume_ml}ml`,
            currentPrice: Number(bundleRow.bundle_price) || 0,
            originalPrice: 0,
            volumeMl: Number(bundleRow.bundle_volume_ml) || 0,
            products: [],
          };
          bundleVariantsMap.set(variantKey, variant);
        }
      }
    }

    // Assign products to each variant
    for (const [bundleId, productsMap] of bundleProductVariantsMap) {
      const bundle = bundlesMap.get(bundleId);
      if (!bundle) continue;

      // For each variant, find products with matching volume
      for (const variant of bundleVariantsMap.values()) {
        if (!variant.id) continue;

        const variantKey = `${bundleId}_${variant.id}`;
        const storedVariant = bundleVariantsMap.get(variantKey);
        if (!storedVariant) continue;

        let totalPrice = 0;
        storedVariant.products = [];

        for (const [productId, productVariants] of productsMap) {
          // Find variant with matching volume_ml
          const matchingVariant = productVariants.find(
            (pv: any) => pv.volume_ml === variant.volumeMl
          );

          if (matchingVariant) {
            storedVariant.products!.push({
              id: productId,
              variantId: matchingVariant.variant_id,
              slug: matchingVariant.product_slug,
              name: matchingVariant.product_name,
              brandName: matchingVariant.brand_name,
              image: matchingVariant.product_image || '/images/placeholder.webp',
              volume: `${matchingVariant.volume_ml} ml`,
              volumeMl: matchingVariant.volume_ml,
              price: Number(matchingVariant.price) || 0,
              productStock: Number(matchingVariant.product_stock) || 0,
            });
            totalPrice += Number(matchingVariant.price) || 0;
          }
        }

        storedVariant.originalPrice = totalPrice;
      }
    }

    // Attach variants to bundles
    const bundles = Array.from(bundlesMap.values());
    for (const bundle of bundles) {
      const variants = Array.from(bundleVariantsMap.values()).filter(
        v => v.id && (bundleRows as any[]).some((br: any) =>
          br.bundle_id === bundle.id && br.bundle_variant_id === v.id
        )
      );

      if (variants.length > 0) {
        variants.sort((a, b) => a.currentPrice - b.currentPrice);
        bundle.variants = variants;
        bundle.currentPrice = variants[0].currentPrice;
        bundle.originalPrice = variants[0].originalPrice;
        bundle.products = variants[0].products || [];
      }
    }

    res.status(200).json(bundles);
  } catch (error: any) {
    res.status(500).json({ error: error.message || 'Failed to fetch bundles' });
  }
}