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

export async function GET(request: NextRequest) {
  try {
    const adminId = request.headers.get("x-user-id")
    const url = new URL(request.url)
    const format = url.searchParams.get("format") || "json" // json, csv, excel
    const type = url.searchParams.get("type") || "sales" // sales, streams, artists, revenue

    // Verify admin role
    const admin = await sql`SELECT role FROM users WHERE id = ${adminId}`
    if (!admin[0] || admin[0].role !== "admin") {
      return NextResponse.json({ error: "Non autorisé" }, { status: 403 })
    }

    let data: any = {}
    let filename = ""

    switch (type) {
      case "sales":
        data = await sql`
          SELECT 
            p.id,
            p.created_at as date,
            u.name as user_name,
            u.email as user_email,
            p.purchase_type,
            CASE 
              WHEN p.purchase_type = 'track' THEN t.title
              WHEN p.purchase_type = 'album' THEN a.title
            END as item_name,
            CASE 
              WHEN p.purchase_type = 'track' THEN art1.name
              WHEN p.purchase_type = 'album' THEN art2.name
            END as artist_name,
            p.price_fcfa,
            p.status,
            p.payment_method
          FROM purchases p
          JOIN users u ON p.user_id = u.id
          LEFT JOIN tracks t ON p.purchase_type = 'track' AND p.item_id = t.id
          LEFT JOIN albums a ON p.purchase_type = 'album' AND p.item_id = a.id
          LEFT JOIN artists art1 ON t.artist_id = art1.id
          LEFT JOIN artists art2 ON a.artist_id = art2.id
          ORDER BY p.created_at DESC
          LIMIT 1000
        `
        filename = "ventes"
        break

      case "streams":
        data = await sql`
          SELECT 
            t.title as track_title,
            art.name as artist_name,
            t.total_streams,
            COUNT(DISTINCT lh.user_id) as unique_listeners,
            COUNT(lh.id) as total_plays
          FROM tracks t
          JOIN artists art ON t.artist_id = art.id
          LEFT JOIN listening_history lh ON t.id = lh.track_id
          GROUP BY t.id, t.title, art.name, t.total_streams
          ORDER BY t.total_streams DESC
          LIMIT 1000
        `
        filename = "streams"
        break

      case "artists":
        data = await sql`
          SELECT 
            a.name,
            a.is_verified,
            a.total_streams,
            a.total_followers,
            a.monthly_listeners,
            COUNT(DISTINCT t.id) as total_tracks,
            COUNT(DISTINCT alb.id) as total_albums,
            COALESCE(SUM(ae.earnings_amount), 0) as total_earnings
          FROM artists a
          LEFT JOIN tracks t ON a.id = t.artist_id
          LEFT JOIN albums alb ON a.id = alb.artist_id
          LEFT JOIN artist_earnings ae ON a.id = ae.artist_id
          GROUP BY a.id, a.name, a.is_verified, a.total_streams, a.total_followers, a.monthly_listeners
          ORDER BY a.total_streams DESC
          LIMIT 1000
        `
        filename = "artistes"
        break

      case "revenue":
        data = await sql`
          SELECT 
            DATE_TRUNC('day', pt.created_at) as date,
            COUNT(*) as transactions,
            SUM(pt.amount) as revenue,
            COUNT(DISTINCT pt.user_id) as unique_users
          FROM payment_transactions pt
          WHERE pt.status = 'completed'
          GROUP BY DATE_TRUNC('day', pt.created_at)
          ORDER BY date DESC
          LIMIT 365
        `
        filename = "revenus"
        break

      default:
        return NextResponse.json({ error: "Type invalide" }, { status: 400 })
    }

    if (format === "csv") {
      // Convert to CSV
      const csv = convertToCSV(data)
      return new NextResponse(csv, {
        headers: {
          "Content-Type": "text/csv",
          "Content-Disposition": `attachment; filename="${filename}_${new Date().toISOString().split("T")[0]}.csv"`,
        },
      })
    }

    // Return JSON by default
    return NextResponse.json({ data, count: data.length })
  } catch (error: any) {
    console.error("Error generating export:", error)
    return NextResponse.json({ error: "Erreur serveur" }, { status: 500 })
  }
}

function convertToCSV(data: any[]): string {
  if (data.length === 0) return ""

  const headers = Object.keys(data[0])
  const rows = data.map((row) =>
    headers
      .map((header) => {
        const value = row[header]
        if (value === null || value === undefined) return ""
        if (typeof value === "string" && value.includes(",")) return `"${value}"`
        return value
      })
      .join(","),
  )

  return [headers.join(","), ...rows].join("\n")
}
