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 type = url.searchParams.get('type') || 'timeseries';

    if (type === 'timeseries') {
      const result = await query(`
        SELECT date, clicks, conversions, revenue
        FROM daily_stats
        WHERE dimension = 'total' AND dimension_id = 'all'
          AND date >= CURRENT_DATE - $1::int
        ORDER BY date
      `, [days]);
      return new Response(JSON.stringify({ data: result.rows }), {
        headers: { 'Content-Type': 'application/json' },
      });
    }

    if (type === 'top-products') {
      const result = await query(`
        SELECT ds.dimension_id as product_id, p.name as product_name, p.slug,
               SUM(ds.clicks) as total_clicks, SUM(ds.conversions) as total_conversions,
               SUM(ds.revenue) as total_revenue
        FROM daily_stats ds
        LEFT JOIN products p ON p.id = ds.dimension_id::int
        WHERE ds.dimension = 'product' AND ds.date >= CURRENT_DATE - $1::int
        GROUP BY ds.dimension_id, p.name, p.slug
        ORDER BY total_clicks DESC
        LIMIT 20
      `, [days]);

      // Get pageviews per product
      const pvResult = await query(`
        SELECT product_id, COUNT(*) as views
        FROM page_views
        WHERE product_id IS NOT NULL AND created_at >= CURRENT_DATE - $1::int
        GROUP BY product_id
      `, [days]);

      const pvMap = new Map<number, number>();
      for (const row of pvResult.rows) {
        pvMap.set(Number(row.product_id), Number(row.views));
      }

      const data = result.rows.map(row => ({
        ...row,
        pageviews: pvMap.get(Number(row.product_id)) || 0,
        ctr: pvMap.get(Number(row.product_id))
          ? ((Number(row.total_clicks) / pvMap.get(Number(row.product_id))!) * 100).toFixed(2)
          : '0',
      }));

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

    if (type === 'top-shops') {
      const result = await query(`
        SELECT ds.dimension_id as shop_id, SUM(ds.clicks) as total_clicks,
               SUM(ds.revenue) as total_revenue
        FROM daily_stats ds
        WHERE ds.dimension = 'shop' AND ds.date >= CURRENT_DATE - $1::int
        GROUP BY ds.dimension_id
        ORDER BY total_revenue DESC
        LIMIT 20
      `, [days]);
      return new Response(JSON.stringify({ data: result.rows }), {
        headers: { 'Content-Type': 'application/json' },
      });
    }

    if (type === 'sources') {
      const result = await query(`
        SELECT ds.dimension_id as source, SUM(ds.clicks) as total_clicks
        FROM daily_stats ds
        WHERE ds.dimension = 'source' AND ds.date >= CURRENT_DATE - $1::int
        GROUP BY ds.dimension_id
        ORDER BY total_clicks DESC
      `, [days]);
      return new Response(JSON.stringify({ data: result.rows }), {
        headers: { 'Content-Type': 'application/json' },
      });
    }

    if (type === 'overview') {
      const statsResult = await query(`
        SELECT
          COALESCE(SUM(clicks), 0) as total_clicks,
          COALESCE(SUM(conversions), 0) as total_conversions,
          COALESCE(SUM(revenue), 0) as total_revenue
        FROM daily_stats
        WHERE dimension = 'total' AND date >= CURRENT_DATE - $1::int
      `, [days]);

      const pvResult = await query(`
        SELECT COUNT(*) as total_pageviews FROM page_views
        WHERE created_at >= CURRENT_DATE - $1::int
      `, [days]);

      const watchlistResult = await query(`
        SELECT COUNT(*) as total_watchlist FROM watchlist
        WHERE created_at >= CURRENT_DATE - $1::int
      `, [days]);

      const priceWatchResult = await query(`
        SELECT COUNT(*) as active_price_watches FROM watchlist
        WHERE target_price IS NOT NULL
      `, []);

      const stats = statsResult.rows[0] || { total_clicks: 0, total_conversions: 0, total_revenue: 0 };
      const totalPageviews = Number(pvResult.rows[0]?.total_pageviews || 0);
      const totalClicks = Number(stats.total_clicks);
      const ctr = totalPageviews > 0 ? ((totalClicks / totalPageviews) * 100).toFixed(2) : '0';

      return new Response(JSON.stringify({
        ...stats,
        total_pageviews: totalPageviews,
        ctr,
        total_watchlist: Number(watchlistResult.rows[0]?.total_watchlist || 0),
        active_price_watches: Number(priceWatchResult.rows[0]?.active_price_watches || 0),
      }), {
        headers: { 'Content-Type': 'application/json' },
      });
    }

    return new Response(JSON.stringify({ error: 'Invalid type' }), { status: 400 });
  } catch (err) {
    console.error('Analytics error:', err);
    return new Response(JSON.stringify({ data: [], error: 'Query failed' }), {
      headers: { 'Content-Type': 'application/json' },
    });
  }
};
