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

export const GET: APIRoute = async ({ url }) => {
  try {
    const days = parseInt(url.searchParams.get('days') || '30');
    const brand = url.searchParams.get('brand') || '';
    const sort = url.searchParams.get('sort') || 'score';
    const order = url.searchParams.get('order') === 'asc' ? 'ASC' : 'DESC';
    const page = parseInt(url.searchParams.get('page') || '1');
    const limit = parseInt(url.searchParams.get('limit') || '50');
    const offset = (page - 1) * limit;

    const brandFilter = brand ? `AND p.brand ILIKE $2` : '';
    const params: any[] = [days];
    if (brand) params.push(`%${brand}%`);

    const sortMap: Record<string, string> = {
      score: 'score', pageviews: 'pageviews', clicks: 'clicks',
      watchlist: 'watchlist_adds', conversions: 'conversions', revenue: 'revenue', ctr: 'ctr',
    };
    const sortCol = sortMap[sort] || 'score';

    const result = await query(`
      WITH product_metrics AS (
        SELECT p.id, p.name, p.brand,
          COALESCE(pv.views, 0) as pageviews,
          COALESCE(ds.clicks, 0) as clicks,
          COALESCE(w.cnt, 0) as watchlist_adds,
          COALESCE(wa.cnt, 0) as active_alerts,
          COALESCE(cv.conversions, 0) as conversions,
          COALESCE(cv.revenue, 0) as revenue,
          CASE WHEN COALESCE(pv.views, 0) > 0 
            THEN ROUND(COALESCE(ds.clicks, 0)::numeric / pv.views * 100, 2) ELSE 0 END as ctr,
          (COALESCE(pv.views, 0) * 1 + COALESCE(ds.clicks, 0) * 5 + COALESCE(w.cnt, 0) * 10 
           + COALESCE(cv.conversions, 0) * 50 + COALESCE(cv.revenue, 0) * 2) as score
        FROM products p
        LEFT JOIN (
          SELECT product_id, COUNT(*) as views FROM page_views
          WHERE created_at >= CURRENT_DATE - $1::int GROUP BY product_id
        ) pv ON pv.product_id = p.id
        LEFT JOIN (
          SELECT dimension_id, SUM(clicks) as clicks FROM daily_stats
          WHERE dimension = 'product' AND date >= CURRENT_DATE - $1::int GROUP BY dimension_id
        ) ds ON ds.dimension_id = p.id::text
        LEFT JOIN (
          SELECT product_id, COUNT(*) as cnt FROM watchlist GROUP BY product_id
        ) w ON w.product_id = p.id
        LEFT JOIN (
          SELECT product_id, COUNT(*) as cnt FROM watchlist WHERE notify_price_drop = true GROUP BY product_id
        ) wa ON wa.product_id = p.id
        LEFT JOIN (
          SELECT product_id, COUNT(*) as conversions, COALESCE(SUM(commission), 0) as revenue
          FROM conversions WHERE status != 'rejected' AND conversion_time >= CURRENT_DATE - $1::int
          GROUP BY product_id
        ) cv ON cv.product_id = p.id::text::uuid
        WHERE p.is_sneaker = true ${brandFilter}
      )
      SELECT * FROM product_metrics ORDER BY ${sortCol} ${order} LIMIT ${limit} OFFSET ${offset}
    `, params);

    const countResult = await query(`
      SELECT COUNT(*) as total FROM products WHERE is_sneaker = true ${brand ? `AND brand ILIKE $2` : ''}
    `, params);

    return Response.json({
      data: result.rows,
      total: parseInt(countResult.rows[0]?.total || '0'),
      page, limit,
    });
  } catch (err: any) {
    return Response.json({ error: err.message || 'Query failed' }, { status: 500 });
  }
};
