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

export async function GET(request: NextRequest) {
  try {
    const { searchParams } = new URL(request.url)
    const { page, limit, offset } = getPaginationValues({
      page: Number.parseInt(searchParams.get("page") || "1"),
      limit: Number.parseInt(searchParams.get("limit") || "20"),
    })
    const status = searchParams.get("status")
    const planId = searchParams.get("plan_id")

    const whereConditions = []
    if (status) {
      whereConditions.push(`us.status = '${status}'`)
    }
    if (planId) {
      whereConditions.push(`us.plan_id = '${planId}'`)
    }

    const whereClause = whereConditions.length > 0 ? `WHERE ${whereConditions.join(" AND ")}` : ""

    const subscriptions = await sql`
      SELECT us.*, 
             u.name as user_name, u.email as user_email, u.avatar_url as user_avatar,
             sp.name as plan_name, sp.price_monthly as plan_price
      FROM user_subscriptions us
      JOIN users u ON us.user_id = u.id
      JOIN subscription_plans sp ON us.plan_id = sp.id
      ${sql.unsafe(whereClause)}
      ORDER BY us.created_at DESC
      LIMIT ${limit} OFFSET ${offset}
    `

    const countResult = await sql`
      SELECT COUNT(*) as total 
      FROM user_subscriptions us ${sql.unsafe(whereClause)}
    `
    const total = Number.parseInt(countResult[0].total)

    return NextResponse.json({
      data: subscriptions,
      total,
      page,
      limit,
      totalPages: Math.ceil(total / limit),
    })
  } catch (error) {
    console.error("Get subscriptions error:", error)
    return NextResponse.json({ error: "Erreur lors de la récupération des abonnements" }, { status: 500 })
  }
}
