import type { APIRoute } from 'astro';
import { query } from '../../../lib/feeds/db.js';

/** GET /api/feeds/google-shopping.xml — Google Merchant Center product feed */
export const GET: APIRoute = async () => {
  try {
    const result = await query(
      `SELECT
         p.id, p.name, p.slug, p.brand, p.ean, p.gtin,
         p.brand_normalized, p.model_normalized,
         p.cutout_image_url,
         o.price, o.original_price, o.currency, o.product_url, o.image_url, o.in_stock,
         p.colorway_normalized as color
       FROM product_offers o
       JOIN products p ON o.product_id = p.id
       WHERE o.in_stock = true
         AND p.is_sneaker = true
         AND o.image_url IS NOT NULL AND o.image_url != ''
         AND o.price > 0
       ORDER BY o.price ASC`
    );

    // Deduplicate: one entry per product (lowest price offer)
    const seen = new Set<number>();
    const items: string[] = [];

    for (const row of result.rows) {
      if (seen.has(row.id)) continue;
      seen.add(row.id);

      const imageUrl = row.cutout_image_url || row.image_url;
      const productLink = `https://sneakerpicks.nl/sneaker/${row.slug || row.id}`;

      items.push(`    <item>
      <g:id>${escXml(String(row.id))}</g:id>
      <title>${escXml(row.name)}</title>
      <link>${escXml(productLink)}</link>
      <g:image_link>${escXml(imageUrl)}</g:image_link>
      <g:price>${row.price} ${row.currency || 'EUR'}</g:price>
      ${row.original_price ? `<g:sale_price>${row.price} ${row.currency || 'EUR'}</g:sale_price>` : ''}
      <g:brand>${escXml(row.brand || '')}</g:brand>
      ${row.ean ? `<g:gtin>${escXml(row.ean)}</g:gtin>` : ''}
      <g:condition>new</g:condition>
      <g:availability>${row.in_stock ? 'in_stock' : 'out_of_stock'}</g:availability>
      <g:google_product_category>Apparel &amp; Accessories &gt; Shoes &gt; Athletic Shoes</g:google_product_category>
      <g:product_type>Apparel &amp; Accessories &gt; Shoes</g:product_type>
      ${row.color ? `<g:color>${escXml(row.color)}</g:color>` : ''}
      <g:shipping>
        <g:country>NL</g:country>
        <g:service>Standard</g:service>
        <g:price>0.00 EUR</g:price>
      </g:shipping>
    </item>`);
    }

    const xml = `<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0" xmlns:g="http://base.google.com/ns/1.0">
  <channel>
    <title>SneakerPicks.nl - Sneaker Price Comparison</title>
    <link>https://sneakerpicks.nl</link>
    <description>Best sneaker prices from Dutch retailers</description>
${items.join('\n')}
  </channel>
</rss>`;

    return new Response(xml, {
      status: 200,
      headers: {
        'Content-Type': 'application/xml; charset=utf-8',
        'Cache-Control': 'public, max-age=3600',
      },
    });
  } catch (err: any) {
    return new Response(`<!-- Error: ${err.message} -->`, {
      status: 500,
      headers: { 'Content-Type': 'application/xml' },
    });
  }
};

function escXml(s: string): string {
  return s
    .replace(/&/g, '&amp;')
    .replace(/</g, '&lt;')
    .replace(/>/g, '&gt;')
    .replace(/"/g, '&quot;')
    .replace(/'/g, '&apos;');
}
