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

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

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

  const { bundleId } = req.query;

  if (!bundleId || Array.isArray(bundleId)) {
    return res.status(400).json({ error: 'Invalid bundleId' });
  }

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

    const query = `
      SELECT
        p.id AS product_id,
        pv.id AS variant_id,
        p.name AS product_name,
        br.name AS brand_name,
        img.url AS product_image,
        pv.volume_ml,
        pv.label as volume,
        ps.stock AS product_stock
      FROM bundles b
      JOIN bundle_items bi ON bi.bundle_id = b.id
      JOIN product_variants pv ON pv.id = bi.product_variant_id
      JOIN products p ON p.id = pv.product_id
      JOIN brands br ON br.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
      WHERE b.product_id = ?
      ORDER BY bi.id
    `;

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

    const products: BundleProduct[] = (rows as any[]).map((row) => ({
      id: row.product_id,
      variantId: row.variant_id,
      name: row.product_name,
      brandName: row.brand_name,
      image: row.product_image || '/images/placeholder.webp',
      volume: row.volume || `${row.volume_ml} ml`,
      volumeMl: Number(row.volume_ml) || 0,
      productStock: Number(row.product_stock) || 0,
    }));

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