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 metric = url.searchParams.get('metric') || 'all';
    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, SUM(ds.clicks) as total_clicks,
               SUM(ds.conversions) as total_conversions, SUM(ds.revenue) as total_revenue
        FROM daily_stats ds
        WHERE ds.dimension = 'product' AND ds.date >= CURRENT_DATE - $1::int
        GROUP BY ds.dimension_id
        ORDER BY total_clicks DESC
        LIMIT 20
      `, [days]);
      return new Response(JSON.stringify({ data: result.rows }), {
        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 result = await query(`
        SELECT
          COALESCE(SUM(clicks), 0) as total_clicks,
          COALESCE(SUM(conversions), 0) as total_conversions,
          COALESCE(SUM(revenue), 0) as total_revenue,
          CASE WHEN SUM(clicks) > 0 THEN ROUND(SUM(conversions)::numeric / SUM(clicks) * 100, 2) ELSE 0 END as ctr
        FROM daily_stats
        WHERE dimension = 'total' AND date >= CURRENT_DATE - $1::int
      `, [days]);
      return new Response(JSON.stringify(result.rows[0] || {}), {
        headers: { 'Content-Type': 'application/json' },
      });
    }

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