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

interface VoucherVariant {
  id: number;
  name: string;
  volume: string;
  currentPrice: number;
}

interface Voucher {
  id: number;
  name: string;
  slug: string;
  description: string;
  discount: number;
  primaryImage: string;
  variants: VoucherVariant[];
}

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

  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,
        img.url AS image_url,
        pv.id AS variant_id,
        pv.label,
        pv.volume_ml,
        pr.price
      FROM products p
      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
      WHERE p.type = 'voucher'
        AND p.is_active = 1
      ORDER BY p.id, pv.volume_ml
    `;

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

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

    for (const row of rows as any[]) {
      if (!vouchersMap.has(row.product_id)) {
        vouchersMap.set(row.product_id, {
          id: row.product_id,
          slug: row.product_slug,
          name: row.product_name,
          description: row.product_description || '',
          discount: 0,
          primaryImage: resolveImageUrl(row.image_url),
          variants: [],
        });
      }

      if (row.variant_id) {
        const voucher = vouchersMap.get(row.product_id)!;
        voucher.variants.push({
          id: row.variant_id,
          name: row.label || '',
          volume: row.volume_ml ? `${row.volume_ml} Kč` : '',
          currentPrice: Number(row.price) || 0,
        });
      }
    }

    const vouchers = Array.from(vouchersMap.values());
    res.status(200).json(vouchers);
  } catch (error: any) {
    console.error('Database error:', error);
    console.error('Error message:', error.message);
    res.status(500).json({ error: error.message || 'Failed to fetch vouchers' });
  }
}