import { productBadgeJoinSql } from '../../lib/automaticNewBadge';
import type { NextApiRequest, NextApiResponse } from 'next';
import mysql from 'mysql2/promise';
import { 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 Badge {
  name: string;
  color?: string | null;
}

interface ProductResponse {
  id: number;
  slug: string;
  name: string;
  description: string;
  brandName: string;
  primaryImage: string;
  stock: number;
  sortOrder: number;
  badges?: Badge[];
  variants: ProductVariant[];
}

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' });
  }

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

  try {
    const connection = await pool.getConnection();
    const searchQuery = req.query.q as string | undefined;

    let query = `
      SELECT
        p.id AS product_id,
        p.name AS product_name,
        p.slug AS product_slug,
        b.id AS brand_id,
        b.name AS brand_name,
        b.slug AS brand_slug,
        img.url AS image_url,
        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,
        COALESCE(psort.s_id, 999999) AS sort_order,
        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
      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_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 p.type = 'perfume'
        AND p.is_active = 1
        AND b.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)
            )
        )
        AND p.id NOT IN (
          SELECT id FROM (
            SELECT p2.id
            FROM products p2
            JOIN brands b2 ON b2.id = p2.brand_id
            LEFT JOIN product_sort psort2 ON psort2.product_id = p2.id
            WHERE p2.type = 'perfume' AND p2.is_active = 1 AND b2.is_active = 1
            ORDER BY COALESCE(psort2.s_id, 999999) ASC
            LIMIT 3
          ) AS top_products
        )
    `;

    const params: any[] = [];

    if (searchQuery && searchQuery.trim()) {
      const words = searchQuery.trim().split(/\s+/);
      for (const word of words) {
        const searchTerm = `%${word}%`;
        query += ` AND (p.name LIKE ? OR b.name LIKE ?)`;
        params.push(searchTerm, searchTerm);
      }
    }

    const stockFilter = req.query.stock as string | undefined;
    if (stockFilter === '1') {
      query += ` AND COALESCE(ps.stock, 0) = 1`;
    }

    query += ` GROUP BY p.id, pv.id, pr.variant_id`;

    const sortBy = req.query.sort as string | undefined;
    switch (sortBy) {
      case 'cheapest':
        query += ` ORDER BY (SELECT MIN(pr2.price) FROM product_variants pv2 JOIN prices pr2 ON pr2.variant_id = pv2.id WHERE pv2.product_id = p.id AND pv2.is_active = 1 AND pr2.valid_to IS NULL) ASC, p.id, pv.volume_ml`;
        break;
      case 'most_expensive':
        query += ` ORDER BY (SELECT MAX(pr2.price) FROM product_variants pv2 JOIN prices pr2 ON pr2.variant_id = pv2.id WHERE pv2.product_id = p.id AND pv2.is_active = 1 AND pr2.valid_to IS NULL) DESC, p.id, pv.volume_ml`;
        break;
      case 'newest':
      default:
        query += ` ORDER BY sort_order ASC, p.id ASC, pv.volume_ml`;
    }

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

    // Group variants by product
    const productsMap = new Map<number, ProductResponse>();

    for (const row of rows as any[]) {
      if (!productsMap.has(row.product_id)) {
        const badges: Badge[] = [];
        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,
            });
          }
        }

        productsMap.set(row.product_id, {
          id: row.product_id,
          slug: row.product_slug,
          name: row.product_name,
          description: '',
          brandName: row.brand_name || 'Unknown Brand',
          primaryImage: resolveImageUrl(row.image_url),
          stock: Number(row.stock) || 0,
          sortOrder: Number(row.sort_order) || 0,
          badges: badges.length > 0 ? badges : undefined,
          variants: [],
        });
      }

      if (row.variant_id) {
        const product = productsMap.get(row.product_id)!;
        product.variants.push({
          id: row.variant_id,
          name: row.label || '',
          volume: row.volume_ml ? `${row.volume_ml} ml` : '',
          volumeMl: Number(row.volume_ml) || 0,
          currentPrice: Number(row.price) || 0,
          kind: row.kind || 'SAMPLE',
          sku: row.sku || `VYS-${row.variant_id}`,
          stock: Number(row.variant_stock) || 0,
        });
      }
    }

    const products = Array.from(productsMap.values());
    res.status(200).json(products);
  } catch (error) {
    console.error('Database error:', error);
    res.status(500).json({ error: 'Failed to fetch products' });
  }
}