import { NextApiRequest, NextApiResponse } from 'next';
import mysql from 'mysql2/promise';

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

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

    // Query to get all active product variants with their stock
    const query = `
      SELECT
        p.id AS product_id,
        pv.volume_ml,
        pv.kind,
        pv.stock AS variant_stock,
        COALESCE(ps.stock, 0) AS sample_stock
      FROM products p
      JOIN product_variants pv
        ON pv.product_id = p.id
       AND pv.is_active = 1
      LEFT JOIN product_stock ps
        ON ps.product_id = p.id
      WHERE p.type = 'perfume'
        AND p.is_active = 1
      ORDER BY p.id DESC, pv.volume_ml
    `;

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

    // Generate XML feed
    let xml = '<?xml version="1.0" encoding="UTF-8"?>\n';
    xml += '<item_list>\n';

    // Add each variant as a separate item
    for (const row of rows as any[]) {
      const productId = Number(row.product_id);
      const volumeMl = Number(row.volume_ml);
      const stock = row.kind === 'SAMPLE'
        ? Math.floor((Number(row.sample_stock) || 0) / Math.max(1, volumeMl))
        : Number(row.variant_stock) || 0;

      // Only include if stock is greater than 0
      if (stock > 0 && volumeMl > 0) {
        if (stock > 0) {
          const stockQuantity = stock;

          // ITEM_ID format matches main feed: productId-volumeMl
          const itemId = `${productId}-${volumeMl}`;

          xml += '  <item id="' + itemId + '">\n';
          xml += '    <stock_quantity>' + stockQuantity + '</stock_quantity>\n';
          xml += '  </item>\n';
        }
      }
    }

    xml += '</item_list>\n';

    // Set proper headers for XML feed with explicit Content-Length
    const xmlBuffer = Buffer.from(xml, 'utf-8');
    res.setHeader('Content-Type', 'application/xml; charset=utf-8');
    res.setHeader('Content-Length', xmlBuffer.length);
    res.setHeader('Cache-Control', 'no-cache, no-store, must-revalidate');
    res.status(200).end(xmlBuffer);
  } catch (error) {
    console.error('Feed error:', error);
    const errorXml = '<?xml version="1.0" encoding="UTF-8"?><item_list></item_list>';
    const errorBuffer = Buffer.from(errorXml, 'utf-8');
    res.setHeader('Content-Type', 'application/xml; charset=utf-8');
    res.setHeader('Content-Length', errorBuffer.length);
    res.status(500).end(errorBuffer);
  }
}