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

export const GET: APIRoute = async ({ url }) => {
  try {
    const limit = parseInt(url.searchParams.get('limit') || '20');

    const result = await query(`
      SELECT p.id, p.name, p.slug, p.brand, p.image_url, p.cutout_image_url,
             COALESCE(s.total_clicks, 0) as clicks_7d,
             (SELECT COUNT(*) FROM product_offers o WHERE o.product_id = p.id AND o.in_stock = true) as offer_count
      FROM products p
      LEFT JOIN (
        SELECT dimension_id, SUM(clicks) as total_clicks
        FROM daily_stats
        WHERE dimension = 'product' AND date >= CURRENT_DATE - 7
        GROUP BY dimension_id
      ) s ON s.dimension_id = p.id::text
      WHERE p.is_sneaker = true
      ORDER BY COALESCE(s.total_clicks, 0) DESC, 
               (SELECT COUNT(*) FROM product_offers o WHERE o.product_id = p.id AND o.in_stock = true) DESC
      LIMIT $1
    `, [limit]);

    return new Response(JSON.stringify({ products: result.rows }), {
      headers: { 'Content-Type': 'application/json' },
    });
  } catch (err) {
    console.error('Trending error:', err);
    return new Response(JSON.stringify({ products: [] }), {
      headers: { 'Content-Type': 'application/json' },
    });
  }
};
