import { type NextRequest, NextResponse } from "next/server"
import { sql } from "@/lib/db"

export async function GET(request: NextRequest) {
  try {
    // Get subscription revenue
    const subscriptionRevenue = await sql`
      SELECT 
        sp.name as plan,
        COUNT(us.id) as users,
        SUM(sp.price_monthly) as revenue
      FROM user_subscriptions us
      JOIN subscription_plans sp ON us.plan_id = sp.id
      WHERE us.status = 'active'
      GROUP BY sp.name, sp.price_monthly
      ORDER BY revenue DESC
    `

    // Get total revenue
    const totalRevenue = await sql`
      SELECT COALESCE(SUM(amount), 0) as total
      FROM payment_transactions
      WHERE status = 'completed'
    `

    // Get recent transactions
    const recentTransactions = await sql`
      SELECT 
        pt.id,
        u.name as user,
        sp.name as type,
        pt.amount,
        pt.created_at as date,
        pt.status
      FROM payment_transactions pt
      JOIN users u ON pt.user_id = u.id
      LEFT JOIN user_subscriptions us ON pt.user_id = us.user_id
      LEFT JOIN subscription_plans sp ON us.plan_id = sp.id
      ORDER BY pt.created_at DESC
      LIMIT 10
    `

    // Get monthly revenue data
    const monthlyRevenue = await sql`
      SELECT 
        TO_CHAR(created_at, 'Mon') as month,
        SUM(amount) as revenue
      FROM payment_transactions
      WHERE status = 'completed'
      AND created_at >= NOW() - INTERVAL '12 months'
      GROUP BY TO_CHAR(created_at, 'Mon'), DATE_TRUNC('month', created_at)
      ORDER BY DATE_TRUNC('month', created_at)
    `

    return NextResponse.json({
      totalRevenue: Number.parseInt(totalRevenue[0]?.total || "0"),
      subscriptionBreakdown: subscriptionRevenue,
      recentTransactions,
      monthlyRevenue,
    })
  } catch (error) {
    console.error("Error fetching revenue:", error)
    return NextResponse.json({ error: "Failed to fetch revenue" }, { status: 500 })
  }
}
