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

interface ProductRow {
  product_id: number;
  product_name: string;
  product_slug: string;
  product_description: string;
  brand_name: string;
  category_name: string;
  image_url: string;
  variant_id: number;
  sku: string;
  label: string;
  volume_ml: number;
  kind: 'SAMPLE' | 'BOTTLE' | 'OTHER';
  variant_stock: number;
  price: number;
  stock: number;
  perfume_type_id: number | null;
  perfume_type_name: string | null;
}

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 const config = {
  api: {
    responseLimit: '50mb',
  },
};

function escapeXml(str: string | null | undefined): string {
  if (!str) return '';
  return String(str)
    .replace(/&/g, '&')
    .replace(/</g, '&lt;')
    .replace(/>/g, '&gt;')
    .replace(/"/g, '&quot;')
    .replace(/'/g, '&apos;');
}

function determineGender(categoryName: string | null): string {
  if (!categoryName) return 'unisex';

  const normalized = String(categoryName).toLowerCase().trim();

  if (normalized.includes('pánsk') || normalized.includes('panske')) {
    return 'pánská';
  }
  if (normalized.includes('dámsk') || normalized.includes('damske')) {
    return 'dámská';
  }
  if (normalized.includes('unisex')) {
    return 'unisex';
  }

  return 'unisex';
}

function translatePerfumeType(typeName: string | null): string {
  if (!typeName) return '';

  const normalized = String(typeName).toLowerCase().trim();

  if (normalized.includes('eau de toilette')) {
    return 'toaletní voda';
  }
  if (normalized.includes('eau de parfum')) {
    return 'parfémovaná voda';
  }
  if (normalized.includes('parfum')) {
    return 'parfém';
  }
  if (normalized.includes('extrait de parfum') ) {
    return 'parfémový extrakt';
  }

  return typeName;
}

function getProductUrl(slug: string, sku: string): string {
  const domain = process.env.NEXT_PUBLIC_SITE_URL || 'https://vystrikejto.cz';
  return `${domain}/product/${slug}?variant=${encodeURIComponent(sku)}`;
}

function buildXmlItem(variantId: number, sku: string, productName: string, brandName: string, slug: string, description: string, imageUrl: string, price: number, category: string | null, volumeMl: number, label: string, perfumeTypeName: string | null, kind: ProductRow['kind']): string {
  if (!price || price <= 0) {
    return '';
  }

  const gender = determineGender(category);
  const volumeLabel = label || `${volumeMl} ml`;
  const translatedPerfumeType = translatePerfumeType(perfumeTypeName);
  const perfumeTypeLabel = translatedPerfumeType ? ` ${translatedPerfumeType}` : '';

  // Build product name for feed
  const productTitle = escapeXml(`${brandName} ${productName} ${gender}${perfumeTypeLabel} ${volumeLabel}`);
  const variantType = kind === 'BOTTLE' ? 'celý flakon' : kind === 'OTHER' ? 'varianta' : 'vzorek';
  
  return `
  <SHOPITEM>
    <ITEM_ID>${variantId}</ITEM_ID>
    <PRODUCT>${productTitle} ${variantType}</PRODUCT>
    <PRODUCTNAME>${productTitle} ${variantType}</PRODUCTNAME>
    <DESCRIPTION>${escapeXml(description || '')}</DESCRIPTION>
    <URL>${escapeXml(getProductUrl(slug, sku))}</URL>
    <IMGURL>${escapeXml(imageUrl)}</IMGURL>
    <PRICE_VAT>${price}</PRICE_VAT>
    <MANUFACTURER>${escapeXml(brandName)}</MANUFACTURER>
    <CATEGORYTEXT>Kosmetika a parfémy | Parfémy</CATEGORYTEXT>
    <DELIVERY_DATE>2</DELIVERY_DATE>
    <PARAM>
      <PARAM_NAME>Objem</PARAM_NAME>
      <VAL>${volumeMl} ml</VAL>
    </PARAM>
  </SHOPITEM>`;
}

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,
        (SELECT c.name FROM categories c
         JOIN product_category pc ON c.id = pc.category_id
         WHERE pc.product_id = p.id
         LIMIT 1) AS category_name,
        img.url AS image_url,
        pv.id AS variant_id,
        pv.sku,
        pv.label,
        pv.volume_ml,
        pv.kind,
        pv.stock AS variant_stock,
        pr.price,
        COALESCE(ps.stock, 0) AS stock,
        pt.id AS perfume_type_id,
        pt.name AS perfume_type_name
      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 perfume_type pt
        ON pt.id = p.perfume_type_id
      WHERE p.type = 'perfume'
        AND p.is_active = 1
        AND (
          (pv.kind = 'SAMPLE' AND COALESCE(ps.stock, 0) >= pv.volume_ml)
          OR (pv.kind <> 'SAMPLE' AND pv.stock > 0)
        )
      ORDER BY p.id, pv.volume_ml
    `;

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

    const productList = rows as ProductRow[];

    let xmlContent = '<?xml version="1.0" encoding="utf-8"?>\n<SHOP>\n';

    for (const product of productList) {
      const item = buildXmlItem(
        product.variant_id,
        product.sku || `VYS-${product.variant_id}`,
        product.product_name,
        product.brand_name,
        product.product_slug,
        product.product_description,
        product.image_url,
        product.price,
        product.category_name,
        product.volume_ml,
        product.label,
        product.perfume_type_name,
        product.kind
      );
      if (item) {
        xmlContent += item;
      }
    }

    xmlContent += '\n</SHOP>';

    return res.status(200).setHeader('Content-Type', 'application/xml; charset=utf-8').send(xmlContent);

  } catch (error) {
    return res.status(500).setHeader('Content-Type', 'application/xml; charset=utf-8').send(
      '<?xml version="1.0" encoding="utf-8"?><SHOP><ERROR>Chyba při generování feedu</ERROR></SHOP>'
    );
  }
}