import { NextApiRequest, NextApiResponse } from 'next';
import mysql from 'mysql2/promise';
import { getVariantKindLabel, isVariantAvailable, type VariantKind } from '../../../lib/variantStock';
import { getDefaultProductVariant, hasValidOfferPrice, productVariantUrl } from '../../../lib/productOffer';

interface ProductVariant {
  id: number;
  sku: string;
  volume: string;
  volumeMl: number;
  currentPrice: number;
  kind: VariantKind;
  stock: number;
}

interface ProductForFeed {
  id: number;
  slug: string;
  name: string;
  description: string;
  brandName: string;
  primaryImage: string;
  stock: number;
  variants: ProductVariant[];
}

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();

    const query = `
      SELECT
        p.id AS product_id,
        p.name AS product_name,
        p.slug AS product_slug,
        p.description AS product_description,
        b.name AS brand_name,
        img.url AS image_url,
        pv.id AS variant_id,
        pv.sku AS variant_sku,
        pv.label,
        pv.volume_ml,
        pv.kind,
        pv.stock AS variant_stock,
        pr.price,
        COALESCE(ps.stock, 0) AS stock
      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
      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();

    // Process all product variants for feed
    const productsMap = new Map<number, ProductForFeed>();
    const allVariants: Array<ProductForFeed & { variantId: number; variantSku: string; volumeMl: number }> = [];

    for (const row of rows as any[]) {
      // Omit incomplete offers instead of advertising a fabricated zero price.
      if (!row.variant_id || !Number.isFinite(Number(row.volume_ml)) || Number(row.volume_ml) <= 0
        || !hasValidOfferPrice({ currentPrice: Number(row.price) })) continue;

      if (!productsMap.has(row.product_id)) {
        productsMap.set(row.product_id, {
          id: row.product_id,
          slug: row.product_slug,
          name: row.product_name,
          description: row.product_description || '',
          brandName: row.brand_name || 'Unknown Brand',
          primaryImage: row.image_url || '/images/placeholder.webp',
          stock: Number(row.stock) || 0,
          variants: [],
        });
      }

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

        // Add to allVariants for feed (each variant as separate item)
        allVariants.push({
          ...product,
          variantId: row.variant_id,
          variantSku: row.variant_sku || `VYS-${row.variant_id}`,
          volumeMl: Number(row.volume_ml) || 0,
        });
      }
    }

    // Generate XML feed
    let xml = '<?xml version="1.0" encoding="UTF-8"?>\n';
    xml += '<rss version="2.0" xmlns:g="http://base.google.com/ns/1.0">\n';
    xml += '  <channel>\n';
    xml += '    <title>VYSTŘÍKEJ TO</title>\n';
    xml += '    <link>https://vystrikejto.cz</link>\n';
    xml += '    <description>Parfémy a vůně - VYSTŘÍKEJ TO</description>\n';

    // Preserve existing base IDs, but describe one concrete, purchasable variant.
    for (const product of productsMap.values()) {
      const representative = getDefaultProductVariant(product.variants, product.stock);
      if (!representative) continue;
      const productUrl = productVariantUrl(product.slug, representative);
      const imageUrl = product.primaryImage.startsWith('http')
        ? product.primaryImage
        : `https://files.vystrikejto.cz${product.primaryImage}`;

      // Use product ID as unique ID for the base item
      const uniqueId = `${product.id}`;

      const isInStock = isVariantAvailable(representative, product.stock);

      xml += '    <item>\n';
      xml += `      <g:id>${uniqueId}</g:id>\n`;
      xml += `      <g:item_group_id>${product.id}</g:item_group_id>\n`;
      xml += `      <title>${escapeXml(`${product.brandName} ${product.name} ${getVariantKindLabel(representative.kind)} ${representative.volume}`)}</title>\n`;
      xml += `      <description>${escapeXml(product.description)}</description>\n`;
      xml += `      <g:link>${escapeXml(productUrl)}</g:link>\n`;
      xml += `      <g:image_link>${escapeXml(imageUrl)}</g:image_link>\n`;
      xml += `      <g:availability>${isInStock ? 'in_stock' : 'out_of_stock'}</g:availability>\n`;
      xml += `      <g:price>${representative.currentPrice.toFixed(2)} CZK</g:price>\n`;
      xml += `      <g:unit_pricing_measure>${representative.volumeMl} ml</g:unit_pricing_measure>\n`;
      xml += '      <g:unit_pricing_base_measure>100 ml</g:unit_pricing_base_measure>\n';
      xml += `      <g:brand>${escapeXml(product.brandName)}</g:brand>\n`;
      xml += `      <g:google_product_category>479</g:google_product_category>`;
      xml += `      <g:condition>new</g:condition>\n`;
      xml += '    </item>\n';
    }

    // Add each variant as a separate item
    for (const variant of allVariants) {
      const variantObj = variant.variants.find(v => v.id === variant.variantId);
      if (!variantObj) continue;
      const productUrl = productVariantUrl(variant.slug, variantObj);
      const imageUrl = variant.primaryImage.startsWith('http')
        ? variant.primaryImage
        : `https://files.vystrikejto.cz${variant.primaryImage}`;

      // Create unique ID for each variant
      const uniqueId = `variant-${variant.variantId}`;
      const price = variantObj.currentPrice;

      // Check if THIS VARIANT has enough stock (variant volume_ml must be <= product stock ml)
      const isInStock = isVariantAvailable(variantObj, variant.stock);

      xml += '    <item>\n';
      xml += `      <g:id>${uniqueId}</g:id>\n`;
      xml += `      <g:item_group_id>${variant.id}</g:item_group_id>\n`;
      xml += `      <title>${escapeXml(`${variant.brandName} ${variant.name} ${getVariantKindLabel(variantObj.kind)} ${variant.volumeMl} ml`)}</title>\n`;
      xml += `      <description>${escapeXml(variant.description)}</description>\n`;
      xml += `      <g:link>${escapeXml(productUrl)}</g:link>\n`;
      xml += `      <g:image_link>${escapeXml(imageUrl)}</g:image_link>\n`;
      xml += `      <g:availability>${isInStock ? 'in_stock' : 'out_of_stock'}</g:availability>\n`;
      xml += `      <g:price>${price.toFixed(2)} CZK</g:price>\n`;
      xml += `      <g:unit_pricing_measure>${variant.volumeMl} ml</g:unit_pricing_measure>\n`;
      xml += '      <g:unit_pricing_base_measure>100 ml</g:unit_pricing_base_measure>\n';
      xml += `      <g:brand>${escapeXml(variant.brandName)}</g:brand>\n`;
      xml += `      <g:google_product_category>479</g:google_product_category>`;
      xml += `      <g:condition>new</g:condition>\n`;
      xml += '    </item>\n';
    }

    xml += '  </channel>\n';
    xml += '</rss>\n';

    // Set proper headers for XML feed
    res.setHeader('Content-Type', 'application/xml; charset=utf-8');
    res.setHeader('Cache-Control', 'no-cache, no-store, must-revalidate');
    res.status(200).send(xml);
  } catch (error) {
    res.setHeader('Content-Type', 'application/xml; charset=utf-8');
    res.status(500).send(
      '<?xml version="1.0" encoding="UTF-8"?><rss version="2.0"><channel><title>Error</title></channel></rss>'
    );
  }
}

// Helper function to escape XML special characters
function escapeXml(str: string): string {
  return str
    .replace(/&/g, '&amp;')
    .replace(/</g, '&lt;')
    .replace(/>/g, '&gt;')
    .replace(/"/g, '&quot;')
    .replace(/'/g, '&apos;');
}