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

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

    if (view === 'overview') {
      const [emailStats, watchlistStats] = await Promise.all([
        query(`
          SELECT COUNT(*) as total_sent,
            COUNT(DISTINCT user_id) as unique_users,
            COUNT(*) FILTER (WHERE email_type = 'price_alert') as price_alerts,
            COUNT(*) FILTER (WHERE email_type = 'weekly_watchlist') as weekly_digests,
            COUNT(*) FILTER (WHERE email_type = 'verification') as verifications
          FROM email_log
        `),
        query(`
          SELECT COUNT(*) as total_items, COUNT(DISTINCT user_id) as unique_users,
            CASE WHEN COUNT(DISTINCT user_id) > 0 THEN ROUND(COUNT(*)::numeric / COUNT(DISTINCT user_id), 1) ELSE 0 END as avg_per_user
          FROM watchlist
        `),
      ]);
      return Response.json({
        emails: emailStats.rows[0],
        watchlist: watchlistStats.rows[0],
      });
    }

    if (view === 'recent') {
      const page = parseInt(url.searchParams.get('page') || '1');
      const limit = 25;
      const result = await query(`
        SELECT el.created_at, el.email_type, u.email, p.name as product_name, el.resend_id
        FROM email_log el
        LEFT JOIN users u ON el.user_id = u.id
        LEFT JOIN products p ON el.product_id = p.id
        ORDER BY el.created_at DESC
        LIMIT $1 OFFSET $2
      `, [limit, (page - 1) * limit]);
      return Response.json({ data: result.rows });
    }

    if (view === 'most-watched') {
      const result = await query(`
        SELECT p.name, p.brand, p.image_url, COUNT(*) as watchers
        FROM watchlist w JOIN products p ON w.product_id = p.id
        GROUP BY p.id, p.name, p.brand, p.image_url
        ORDER BY watchers DESC LIMIT 20
      `);
      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 });
  }
};
