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

export async function GET() {
  try {
    let plans: any[] = []
    let subscribers: any[] = []

    try {
      plans = await sql`
        SELECT id, name, price_monthly, price_yearly, features, description
        FROM subscription_plans
        WHERE is_active = true
        ORDER BY price_monthly ASC
      `
    } catch (e) {
      console.log("Plans fetch error:", e)
    }

    try {
      subscribers = await sql`
        SELECT 
          u.id, u.name, u.email, u.avatar_url, u.subscription_type, u.is_active, u.created_at,
          us.current_period_start, us.current_period_end, us.status as sub_status,
          sp.name as plan_name, sp.price_monthly
        FROM users u
        LEFT JOIN user_subscriptions us ON u.id = us.user_id
        LEFT JOIN subscription_plans sp ON us.plan_id = sp.id
        WHERE u.subscription_type IS NOT NULL AND u.subscription_type != 'free'
        ORDER BY u.created_at DESC
      `
    } catch (e) {
      console.log("Subscribers fetch error:", e)
    }

    // Calculate stats
    const totalRevenue = subscribers
      .filter((s: any) => s.is_active)
      .reduce((acc: number, s: any) => {
        return acc + (s.price_monthly || 0)
      }, 0)

    const premiumCount = subscribers.filter((s: any) => s.subscription_type === "premium" && s.is_active).length
    const familyCount = subscribers.filter((s: any) => s.subscription_type === "family" && s.is_active).length
    const studentCount = subscribers.filter((s: any) => s.subscription_type === "student" && s.is_active).length

    return NextResponse.json({
      plans: plans.map((p: any) => ({
        id: p.id,
        name: p.name,
        price: p.price_monthly,
        priceYearly: p.price_yearly,
        description: p.description,
        color:
          p.name === "Premium" ? "purple" : p.name === "Famille" ? "blue" : p.name === "Etudiant" ? "cyan" : "zinc",
      })),
      subscribers: subscribers.map((s: any) => ({
        id: s.id,
        name: s.name,
        email: s.email,
        avatar: s.avatar_url || "/placeholder.svg?height=40&width=40",
        plan: s.plan_name || s.subscription_type || "free",
        status: s.sub_status || (s.is_active ? "active" : "cancelled"),
        startDate: s.current_period_start ? new Date(s.current_period_start).toISOString().split("T")[0] : null,
        nextBilling: s.current_period_end ? new Date(s.current_period_end).toISOString().split("T")[0] : null,
        amount: s.price_monthly || 0,
        autoRenew: s.is_active,
      })),
      stats: {
        totalRevenue,
        premiumCount,
        familyCount,
        studentCount,
      },
    })
  } catch (error) {
    console.error("Subscriptions error:", error)
    return NextResponse.json({
      plans: [],
      subscribers: [],
      stats: { totalRevenue: 0, premiumCount: 0, familyCount: 0, studentCount: 0 },
    })
  }
}
