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

export const GET: APIRoute = async ({ url }) => {
  try {
    const view = url.searchParams.get('view') || 'overview';
    const days = parseInt(url.searchParams.get('days') || '30');

    if (view === 'overview') {
      const [allTime, thisMonth, today, pending] = await Promise.all([
        query(`SELECT COALESCE(SUM(commission), 0) as total_revenue, COUNT(*) as total_conversions,
               CASE WHEN COUNT(*) > 0 THEN ROUND(AVG(commission)::numeric, 2) ELSE 0 END as avg_commission
               FROM conversions WHERE status != 'rejected'`),
        query(`SELECT COALESCE(SUM(commission), 0) as revenue
               FROM conversions WHERE status != 'rejected' AND conversion_time >= date_trunc('month', CURRENT_DATE)`),
        query(`SELECT COALESCE(SUM(commission), 0) as revenue
               FROM conversions WHERE status != 'rejected' AND conversion_time >= CURRENT_DATE`),
        query(`SELECT COALESCE(SUM(commission), 0) as revenue
               FROM conversions WHERE status = 'pending'`),
      ]);
      return Response.json({
        total_revenue: allTime.rows[0]?.total_revenue || 0,
        revenue_month: thisMonth.rows[0]?.revenue || 0,
        revenue_today: today.rows[0]?.revenue || 0,
        avg_commission: allTime.rows[0]?.avg_commission || 0,
        total_conversions: allTime.rows[0]?.total_conversions || 0,
        pending_revenue: pending.rows[0]?.revenue || 0,
      });
    }

    if (view === 'timeseries') {
      const result = await query(`
        SELECT date, COALESCE(SUM(revenue), 0) as revenue, COALESCE(SUM(conversions), 0) as conversions
        FROM daily_stats WHERE dimension = 'total' AND dimension_id = 'all'
          AND date >= CURRENT_DATE - $1::int
        GROUP BY date ORDER BY date
      `, [days]);
      return Response.json({ data: result.rows });
    }

    if (view === 'by-shop') {
      const result = await query(`
        SELECT s.name as shop, COALESCE(SUM(c.commission), 0) as revenue, COUNT(*) as conversions
        FROM conversions c JOIN shops s ON c.shop_id = s.id::text::uuid
        WHERE c.status != 'rejected' AND c.conversion_time >= CURRENT_DATE - $1::int
        GROUP BY s.name ORDER BY revenue DESC LIMIT 20
      `, [days]);
      return Response.json({ data: result.rows });
    }

    if (view === 'by-product') {
      const result = await query(`
        SELECT p.name as product, p.brand, 
               COALESCE(SUM(ds_c.clicks), 0) as clicks,
               COUNT(c.id) as conversions,
               COALESCE(SUM(c.commission), 0) as revenue,
               CASE WHEN COALESCE(SUM(ds_c.clicks), 0) > 0 
                 THEN ROUND(COUNT(c.id)::numeric / SUM(ds_c.clicks) * 100, 2) ELSE 0 END as cr
        FROM conversions c
        JOIN products p ON c.product_id = p.id::text::uuid
        LEFT JOIN daily_stats ds_c ON ds_c.dimension = 'product' AND ds_c.dimension_id = p.id::text
          AND ds_c.date >= CURRENT_DATE - $1::int
        WHERE c.status != 'rejected' AND c.conversion_time >= CURRENT_DATE - $1::int
        GROUP BY p.id, p.name, p.brand
        ORDER BY revenue DESC LIMIT 20
      `, [days]);
      return Response.json({ data: result.rows });
    }

    if (view === 'by-source') {
      const result = await query(`
        SELECT dimension_id as source, COALESCE(SUM(revenue), 0) as revenue
        FROM daily_stats WHERE dimension = 'source' AND date >= CURRENT_DATE - $1::int
        GROUP BY dimension_id ORDER BY revenue DESC
      `, [days]);
      return Response.json({ data: result.rows });
    }

    if (view === 'by-network') {
      const result = await query(`
        SELECT network, COALESCE(SUM(commission), 0) as revenue, COUNT(*) as conversions
        FROM conversions WHERE status != 'rejected' AND conversion_time >= CURRENT_DATE - $1::int
        GROUP BY network ORDER BY revenue DESC
      `, [days]);
      return Response.json({ data: result.rows });
    }

    return Response.json({ error: 'Invalid view' }, { status: 400 });
  } catch (err: any) {
    return Response.json({ error: err.message || 'Query failed' }, { status: 500 });
  }
};
