import { productBadgeJoinSql } from '../../../lib/automaticNewBadge';
import type { NextApiRequest, NextApiResponse } from 'next';
import mysql from 'mysql2/promise';
import { pickProductImages, resolveImageUrl } from '../../../lib/images';

interface ProductVariant {
  id: number;
  name: string;
  volume: string;
  volumeMl: number;
  currentPrice: number;
  kind: 'SAMPLE' | 'BOTTLE' | 'OTHER';
  sku: string;
  stock: number;
}

interface ProductImage {
  url: string;
  isPrimary: boolean;
  alt: string | null;
}

interface BadgeItem {
  name: string;
  color?: string | null;
}

interface FragranceIngredient {
  id: number;
  name: string;
  slug: string;
  description: string;
  imageUrl: string;
}

interface ProductVariantShort {
  id: number;
  name: string;
  volume: string;
  volumeMl: number;
  currentPrice: number;
  kind: 'SAMPLE' | 'BOTTLE' | 'OTHER';
  sku: string;
  stock: number;
}

interface RelatedProduct {
  id: number;
  slug: string;
  name: string;
  brandName: string;
  primaryImage: string;
  stock: number;
  badges?: BadgeItem[];
  variants: ProductVariantShort[];
}

interface ProductResponse {
  id: number;
  slug: string;
  name: string;
  description: string;
  characteristic: string;
  origin: string;
  categories: string[];
  perfumeType: string;
  brandName: string;
  brandSlug: string;
  brandWebsite: string;
  topNotes: string;
  heartNotes: string;
  baseNotes: string;
  chemicals: string;
  primaryImage: string;
  compositionImage: string;
  allImages: ProductImage[];
  stock: number;
  badges?: BadgeItem[];
  variants: ProductVariant[];
  relatedProducts: RelatedProduct[];
  fragranceNotes: {
    top: FragranceIngredient[];
    heart: FragranceIngredient[];
    base: FragranceIngredient[];
  };
}

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<ProductResponse | ErrorResponse>
) {
  if (req.method !== 'GET') {
    return res.status(405).json({ error: 'Method not allowed' });
  }

  const { id } = req.query;

  if (!id || typeof id !== 'string') {
    return res.status(400).json({ error: 'Product slug is required' });
  }

  res.setHeader('Cache-Control', 'no-store');

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

    const query = `
      SELECT
        p.id AS product_id,
        p.name AS product_name,
        p.slug AS product_slug,
        p.description AS product_description,
        p.characteristic AS product_characteristic,
        p.top_notes AS product_top_notes,
        p.heart_notes AS product_heart_notes,
        p.base_notes AS product_base_notes,
        p.chemicals AS product_chemicals,
        o.name AS origin_name,
        pt.name AS perfume_type_name,
        b.id AS brand_id,
        b.name AS brand_name,
        b.slug AS brand_slug,
        b.website AS brand_website,
        pv.id AS variant_id,
        pv.label,
        pv.volume_ml,
        pv.kind,
        pv.sku,
        pv.stock AS variant_stock,
        pr.price,
        COALESCE(ps.stock, 0) AS stock,
        GROUP_CONCAT(DISTINCT CONCAT(pb.id, '|', pb.name, '|', COALESCE(pb.color, '')) SEPARATOR ';') AS badge_data
      FROM products p
      JOIN brands b ON b.id = p.brand_id
      LEFT JOIN origin o ON o.id = p.origin
      LEFT JOIN perfume_type pt ON pt.id = p.perfume_type_id
      JOIN product_variants pv
        ON pv.product_id = p.id
       AND pv.is_active = 1
      JOIN prices pr
        ON pr.variant_id = pv.id
       AND pr.valid_to IS NULL
      LEFT JOIN product_stock ps
        ON ps.product_id = p.id
      LEFT JOIN product_badge pbm
        ON pbm.product_id = p.id
      ${productBadgeJoinSql}
      WHERE p.slug = ?
        AND p.type = 'perfume'
        AND p.is_active = 1
      GROUP BY p.id, pv.id, pr.variant_id
      ORDER BY pv.volume_ml
    `;

    const [rows] = await connection.query(query, [id]);

    if (!Array.isArray(rows) || rows.length === 0) {
      connection.release();
      return res.status(404).json({ error: 'Product not found' });
    }

    const row = rows[0] as any;
    const [imageRows] = await connection.query(
      `SELECT url, is_primary, position, alt
       FROM product_images
       WHERE product_id = ?
       ORDER BY is_primary DESC, COALESCE(position, 9999) ASC, id ASC`,
      [row.product_id]
    );
    const [categoryRows] = await connection.query(
      `SELECT DISTINCT c.id, c.name, c.sort_order
       FROM product_category pc
       JOIN categories c ON c.id = pc.category_id
       WHERE pc.product_id = ?
       ORDER BY c.sort_order ASC, c.id ASC`,
      [row.product_id]
    );
    let ingredientRows: any[] = [];
    try {
      const [rowsWithIngredients] = await connection.query(
        `SELECT i.id, i.name, i.slug, i.description, i.image_url, pi.note, pi.sort_order
         FROM product_ingredients pi
         JOIN ingredients i ON i.id = pi.ingredient_id
         WHERE pi.product_id = ? AND i.active = 1
         ORDER BY FIELD(pi.note, 'TOP', 'HEART', 'BASE'), pi.sort_order ASC, i.name ASC`,
        [row.product_id]
      );
      ingredientRows = rowsWithIngredients as any[];
    } catch (ingredientError: any) {
      if (ingredientError?.code !== 'ER_NO_SUCH_TABLE') {
        connection.release();
        throw ingredientError;
      }
    }
    connection.release();

    const { primaryImage, compositionImage, allImages } = pickProductImages(imageRows as any[]);
    const categories = (categoryRows as Array<{ name: string | null }>)
      .map((category) => category.name)
      .filter((name): name is string => Boolean(name));
    const fragranceNotes: ProductResponse['fragranceNotes'] = { top: [], heart: [], base: [] };
    for (const ingredient of ingredientRows) {
      const note = ingredient.note === 'TOP' ? 'top' : ingredient.note === 'HEART' ? 'heart' : 'base';
      fragranceNotes[note].push({
        id: Number(ingredient.id),
        name: String(ingredient.name),
        slug: String(ingredient.slug || ''),
        description: String(ingredient.description || ''),
        imageUrl: String(ingredient.image_url || ''),
      });
    }
    // Parse and sort badges by ID, removing duplicates
    const badges: BadgeItem[] = [];
    if (row.badge_data) {
      const badgeEntries = row.badge_data.split(';').filter((b: string) => b.trim());
      const badgesMap = new Map<number, { id: number; name: string; color: string | null }>();

      for (const entry of badgeEntries) {
        const parts = entry.split('|');
        const id = Number(parts[0]);
        const name = parts[1];
        const color = parts[2];

        if (!isNaN(id) && name) {
          // Use Map to deduplicate by badge ID - only keep first occurrence
          if (!badgesMap.has(id)) {
            badgesMap.set(id, {
              id,
              name: name.trim(),
              color: color && color.trim() ? color.trim() : null,
            });
          }
        }
      }

      // Sort by ID and convert to array
      const badgesArray = Array.from(badgesMap.values());
      badgesArray.sort((a, b) => a.id - b.id);

      for (const badge of badgesArray) {
        badges.push({
          name: badge.name,
          color: badge.color,
        });
      }
    }

    const product: ProductResponse = {
      id: row.product_id,
      slug: row.product_slug,
      name: row.product_name,
      description: row.product_description || '',
      characteristic: row.product_characteristic || '',
      origin: row.origin_name || '',
      categories,
      perfumeType: row.perfume_type_name || '',
      brandName: row.brand_name || 'Unknown Brand',
      brandSlug: row.brand_slug || '',
      brandWebsite: row.brand_website || '',
      topNotes: row.product_top_notes || '',
      heartNotes: row.product_heart_notes || '',
      baseNotes: row.product_base_notes || '',
      chemicals: row.product_chemicals || '',
      primaryImage,
      compositionImage,
      allImages,
      stock: Number(row.stock) || 0,
      badges: badges.length > 0 ? badges : undefined,
      variants: [],
      relatedProducts: [],
      fragranceNotes,
    };

    const variantsSet = new Set<number>();
    for (const variant of rows as any[]) {
      if (variant.variant_id && !variantsSet.has(variant.variant_id)) {
        variantsSet.add(variant.variant_id);
        product.variants.push({
          id: variant.variant_id,
          name: variant.label || '',
          volume: variant.volume_ml ? `${variant.volume_ml} ml` : '',
          volumeMl: Number(variant.volume_ml) || 0,
          currentPrice: Number(variant.price) || 0,
          kind: variant.kind || 'SAMPLE',
          sku: variant.sku || `VYS-${variant.variant_id}`,
          stock: Number(variant.variant_stock) || 0,
        });
      }
    }

    // Fetch related products from the same brand
    const relatedQuery = `
      SELECT
        p.id AS product_id,
        p.name AS product_name,
        p.slug AS product_slug,
        b.name AS brand_name,
        img.url AS image_url,
        COALESCE(ps.stock, 0) AS stock,
        GROUP_CONCAT(DISTINCT CONCAT(pb.id, '|', pb.name, '|', COALESCE(pb.color, '')) SEPARATOR ';') AS badge_data
      FROM products p
      JOIN brands b ON b.id = p.brand_id
      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
      LEFT JOIN product_sort psort ON psort.product_id = p.id
      LEFT JOIN product_badge pbm ON pbm.product_id = p.id
      ${productBadgeJoinSql}
      WHERE b.id = ?
        AND p.id != ?
        AND p.type = 'perfume'
        AND p.is_active = 1
        AND EXISTS (
          SELECT 1 FROM product_variants available_variant
          WHERE available_variant.product_id = p.id
            AND available_variant.is_active = 1
            AND (
              (available_variant.kind = 'SAMPLE' AND COALESCE(ps.stock, 0) >= available_variant.volume_ml)
              OR (available_variant.kind <> 'SAMPLE' AND available_variant.stock > 0)
            )
        )
      GROUP BY p.id
      ORDER BY COALESCE(psort.s_id, 999999) ASC, p.id ASC
    `;

    const connection2 = await pool.getConnection();
    const [relatedRows] = await connection2.query(relatedQuery, [row.brand_id, row.product_id]);

    product.relatedProducts = [];
    for (const relProduct of relatedRows as any[]) {
      // Parse and sort badges for related product, removing duplicates
      const relatedBadges: BadgeItem[] = [];
      if (relProduct.badge_data) {
        const badgeEntries = relProduct.badge_data.split(';').filter((b: string) => b.trim());
        const badgesMap = new Map<number, { id: number; name: string; color: string | null }>();

        for (const entry of badgeEntries) {
          const parts = entry.split('|');
          const id = Number(parts[0]);
          const name = parts[1];
          const color = parts[2];

          if (!isNaN(id) && name) {
            // Use Map to deduplicate by badge ID - only keep first occurrence
            if (!badgesMap.has(id)) {
              badgesMap.set(id, {
                id,
                name: name.trim(),
                color: color && color.trim() ? color.trim() : null,
              });
            }
          }
        }

        // Sort by ID and convert to array
        const badgesArray = Array.from(badgesMap.values());
        badgesArray.sort((a, b) => a.id - b.id);

        for (const badge of badgesArray) {
          relatedBadges.push({
            name: badge.name,
            color: badge.color,
          });
        }
      }

      const variantQuery = `
        SELECT
          pv.id AS variant_id,
          pv.label,
          pv.volume_ml,
          pv.kind,
          pv.sku,
          pv.stock AS variant_stock,
          pr.price
        FROM product_variants pv
        JOIN prices pr ON pr.variant_id = pv.id AND pr.valid_to IS NULL
        WHERE pv.product_id = ? AND pv.is_active = 1
        ORDER BY pv.volume_ml
      `;

      const [variants] = await connection2.query(variantQuery, [relProduct.product_id]);

      product.relatedProducts.push({
        id: relProduct.product_id,
        slug: relProduct.product_slug,
        name: relProduct.product_name,
        brandName: relProduct.brand_name,
        primaryImage: resolveImageUrl(relProduct.image_url),
        stock: Number(relProduct.stock) || 0,
        badges: relatedBadges.length > 0 ? relatedBadges : undefined,
        variants: (variants as any[]).map((v: any) => ({
          id: v.variant_id,
          name: v.label || '',
          volume: v.volume_ml ? `${v.volume_ml} ml` : '',
          volumeMl: Number(v.volume_ml) || 0,
          currentPrice: Number(v.price) || 0,
          kind: v.kind || 'SAMPLE',
          sku: v.sku || `VYS-${v.variant_id}`,
          stock: Number(v.variant_stock) || 0,
        })),
      });
    }
    connection2.release();

    res.status(200).json(product);
  } catch (error) {
    res.status(500).json({ error: 'Failed to fetch product' });
  }
}