import { q as query } from '../../../chunks/db_Du3AmLCY.mjs';
export { renderers } from '../../../renderers.mjs';

const GET = 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 = [days];
    if (brand) params.push(`%${brand}%`);
    const sortMap = {
      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) {
    return Response.json({ error: err.message || "Query failed" }, { status: 500 });
  }
};

const _page = /*#__PURE__*/Object.freeze(/*#__PURE__*/Object.defineProperty({
  __proto__: null,
  GET
}, Symbol.toStringTag, { value: 'Module' }));

const page = () => _page;

export { page };
