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

export const GET: APIRoute = async ({ url }) => {
  const q = url.searchParams.get('q')?.trim() || '';
  if (q.length < 2) {
    return new Response(JSON.stringify([]), { headers: { 'Content-Type': 'application/json' } });
  }

  const pattern = `%${q}%`;
  const result = await query(`
    SELECT p.id, p.name, p.slug, p.brand, p.colorway, p.image_url,
           MIN(po.price) as lowest_price,
           COUNT(DISTINCT COALESCE(f.merchant_name, f.name)) as shop_count
    FROM products p
    JOIN product_offers po ON po.product_id = p.id AND po.in_stock = true
    JOIN feeds f ON f.id = po.feed_id
    WHERE (p.name ILIKE $1 OR p.brand ILIKE $1 OR CONCAT(p.brand, ' ', p.name) ILIKE $1)
      AND p.is_sneaker = true AND p.image_url IS NOT NULL AND p.image_url != ''
    GROUP BY p.id
    HAVING MIN(po.price) > 0
    ORDER BY COUNT(po.id) DESC
    LIMIT 6
  `, [pattern]);

  const results = result.rows.map((r: any) => ({
    id: String(r.id),
    slug: r.slug,
    name: r.name,
    brand: r.brand,
    colorway: r.colorway || '',
    image: r.image_url,
    lowestPrice: Number(r.lowest_price),
    shopCount: Number(r.shop_count),
  }));

  return new Response(JSON.stringify(results), {
    headers: { 'Content-Type': 'application/json' },
  });
};
