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

export async function GET() {
  try {
    // Get total users
    const usersCount = await sql`SELECT COUNT(*) as count FROM users`

    // Get total tracks
    const tracksCount = await sql`SELECT COUNT(*) as count FROM tracks WHERE status = 'published'`

    // Get total artists
    const artistsCount = await sql`SELECT COUNT(*) as count FROM artists`

    // Get total streams (sum of all track streams)
    const streamsSum = await sql`SELECT COALESCE(SUM(total_streams), 0) as total FROM tracks`

    // Get premium subscribers
    const premiumCount = await sql`
      SELECT COUNT(*) as count FROM users 
      WHERE subscription_type != 'free'
    `

    // Get monthly revenue estimate
    const revenueResult = await sql`
      SELECT COALESCE(SUM(sp.price_monthly), 0) as total
      FROM user_subscriptions us
      JOIN subscription_plans sp ON us.plan_id = sp.id
      WHERE us.status = 'active'
    `

    // Get new users this month
    const newUsersThisMonth = await sql`
      SELECT COUNT(*) as count FROM users
      WHERE created_at >= DATE_TRUNC('month', CURRENT_DATE)
    `

    // Get new tracks this month
    const newTracksThisMonth = await sql`
      SELECT COUNT(*) as count FROM tracks
      WHERE created_at >= DATE_TRUNC('month', CURRENT_DATE)
    `

    return NextResponse.json({
      totalUsers: Number.parseInt(usersCount[0].count),
      totalTracks: Number.parseInt(tracksCount[0].count),
      totalArtists: Number.parseInt(artistsCount[0].count),
      totalStreams: Number.parseInt(streamsSum[0].total),
      premiumUsers: Number.parseInt(premiumCount[0].count),
      monthlyRevenue: Number.parseInt(revenueResult[0].total),
      newUsersThisMonth: Number.parseInt(newUsersThisMonth[0].count),
      newTracksThisMonth: Number.parseInt(newTracksThisMonth[0].count),
    })
  } catch (error) {
    console.error("Dashboard stats error:", error)
    return NextResponse.json({ error: "Erreur lors de la récupération des statistiques" }, { status: 500 })
  }
}
