import { BUNDLES_ENABLED } from '@/lib/features';
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,
});

const DOMAIN = 'https://vystrikejto.cz';

// Static pages with their priority and changefreq
const STATIC_PAGES = [
  { url: '/', priority: 1.0, changefreq: 'daily' },
  ...(BUNDLES_ENABLED ? [{ url: '/vyhodne-sady', priority: 0.9, changefreq: 'weekly' }] : []),
  { url: '/vyprodej', priority: 0.9, changefreq: 'weekly' },
  { url: '/brands', priority: 0.8, changefreq: 'weekly' },
  { url: '/darkove-poukazy', priority: 0.7, changefreq: 'monthly' },
  { url: '/about', priority: 0.7, changefreq: 'yearly' },
  { url: '/faq', priority: 0.7, changefreq: 'yearly' },
  { url: '/reviews', priority: 0.6, changefreq: 'monthly' },
  { url: '/terms', priority: 0.5, changefreq: 'yearly' },
  { url: '/privacy', priority: 0.5, changefreq: 'yearly' },
  { url: '/rights', priority: 0.5, changefreq: 'yearly' },
];

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

    // Get all active products with their variants and slugs
    const productsQuery = `
      SELECT 
        p.id, 
        p.slug, 
        p.updated_at
      FROM products p
      WHERE p.type = 'perfume'
        AND p.is_active = 1
      ORDER BY p.id DESC
    `;

    const brandsQuery = `
      SELECT id, slug, updated_at
      FROM brands
      WHERE is_active = 1
      ORDER BY name ASC
    `;

    const categoriesQuery = `
      SELECT slug, name
      FROM categories
      WHERE active = 1
      ORDER BY sort_order ASC
    `;

    const bundlesQuery = `
      SELECT p.slug, p.updated_at
      FROM products p
      JOIN bundles b ON b.product_id = p.id
      WHERE p.type = 'bundle'
        AND p.is_active = 1
      ORDER BY p.id DESC
    `;

    const [products] = await connection.query(productsQuery);
    const [brands] = await connection.query(brandsQuery);
    const [categories] = await connection.query(categoriesQuery);
    const [bundles] = BUNDLES_ENABLED ? await connection.query(bundlesQuery) : [[]];

    connection.release();

    // Generate XML sitemap
    let xml = '<?xml version="1.0" encoding="UTF-8"?>\n';
    xml += '<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">\n';

    // Add static pages
    for (const page of STATIC_PAGES) {
      xml += '  <url>\n';
      xml += `    <loc>${DOMAIN}${page.url}</loc>\n`;
      xml += `    <changefreq>${page.changefreq}</changefreq>\n`;
      xml += `    <priority>${page.priority.toFixed(1)}</priority>\n`;
      xml += '  </url>\n';
    }

    for (const product of products as any[]) {
      const lastmod = product.updated_at
        ? new Date(product.updated_at).toISOString().split('T')[0]
        : new Date().toISOString().split('T')[0];

      xml += '  <url>\n';
      xml += `    <loc>${DOMAIN}/product/${product.slug}</loc>\n`;
      xml += `    <lastmod>${lastmod}</lastmod>\n`;
      xml += `    <changefreq>weekly</changefreq>\n`;
      xml += `    <priority>0.8</priority>\n`;
      xml += '  </url>\n';
    }

    for (const bundle of bundles as any[]) {
      const lastmod = bundle.updated_at
        ? new Date(bundle.updated_at).toISOString().split('T')[0]
        : new Date().toISOString().split('T')[0];

      xml += '  <url>\n';
      xml += `    <loc>${DOMAIN}/vyhodne-sady/${bundle.slug}</loc>\n`;
      xml += `    <lastmod>${lastmod}</lastmod>\n`;
      xml += `    <changefreq>weekly</changefreq>\n`;
      xml += `    <priority>0.8</priority>\n`;
      xml += '  </url>\n';
    }

    // Add brand pages
    for (const brand of brands as any[]) {
      const lastmod = brand.updated_at
        ? new Date(brand.updated_at).toISOString().split('T')[0]
        : new Date().toISOString().split('T')[0];

      xml += '  <url>\n';
      xml += `    <loc>${DOMAIN}/brands/${brand.slug}</loc>\n`;
      xml += `    <lastmod>${lastmod}</lastmod>\n`;
      xml += `    <changefreq>weekly</changefreq>\n`;
      xml += `    <priority>0.7</priority>\n`;
      xml += '  </url>\n';
    }

    for (const category of categories as any[]) {
      xml += '  <url>\n';
      xml += `    <loc>${DOMAIN}/parfemy/${category.slug}</loc>\n`;
      xml += `    <changefreq>weekly</changefreq>\n`;
      xml += `    <priority>0.7</priority>\n`;
      xml += '  </url>\n';
    }

    xml += '</urlset>';

    // Set proper headers for XML sitemap
    res.setHeader('Content-Type', 'application/xml; charset=utf-8');
    res.setHeader('Cache-Control', 'public, s-maxage=3600, stale-while-revalidate=86400');
    res.status(200).send(xml);
  } catch (error) {
    console.error('Sitemap generation error:', error);
    res.setHeader('Content-Type', 'application/xml; charset=utf-8');
    res.status(500).send(
      '<?xml version="1.0" encoding="UTF-8"?><urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"></urlset>'
    );
  }
}