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

// GET /api/admin/ringtone-api/export - Export statistics and reports
export async function GET(request: Request) {
  try {
    const { searchParams } = new URL(request.url)
    const format = searchParams.get("format") || "json" // json, csv, excel
    const reportType = searchParams.get("type") || "summary" // summary, downloads, revenue, operators
    const startDate = searchParams.get("start_date")
    const endDate = searchParams.get("end_date")

    let data: any = {}

    if (reportType === "summary") {
      // Summary statistics
      const stats = await sql`
        SELECT 
          COUNT(DISTINCT eak.id) as total_operators,
          COUNT(DISTINCT rd.id) as total_downloads,
          SUM(rd.price) as total_revenue,
          COUNT(DISTINCT rd.track_id) as unique_tracks_downloaded,
          COUNT(DISTINCT rd.customer_phone) as unique_customers
        FROM external_api_keys eak
        LEFT JOIN ringtone_downloads rd ON rd.api_key_id = eak.id
        ${startDate ? sql`WHERE rd.created_at >= ${startDate}` : sql``}
        ${startDate && endDate ? sql`AND rd.created_at <= ${endDate}` : sql``}
      `

      data = stats[0]
    } else if (reportType === "downloads") {
      // Downloads by track
      data = await sql`
        SELECT 
          t.id,
          t.title,
          t.artist_name,
          COUNT(rd.id) as download_count,
          SUM(rd.price) as revenue,
          COUNT(DISTINCT rd.api_key_id) as operator_count
        FROM tracks t
        JOIN ringtone_downloads rd ON rd.track_id = t.id
        ${startDate ? sql`WHERE rd.created_at >= ${startDate}` : sql``}
        ${startDate && endDate ? sql`AND rd.created_at <= ${endDate}` : sql``}
        GROUP BY t.id, t.title, t.artist_name
        ORDER BY download_count DESC
        LIMIT 100
      `
    } else if (reportType === "revenue") {
      // Revenue by operator
      data = await sql`
        SELECT 
          eak.company,
          eak.name as api_name,
          COUNT(rd.id) as downloads,
          SUM(rd.price) as revenue,
          AVG(rd.price) as avg_price,
          MIN(rd.created_at) as first_download,
          MAX(rd.created_at) as last_download
        FROM external_api_keys eak
        JOIN ringtone_downloads rd ON rd.api_key_id = eak.id
        ${startDate ? sql`WHERE rd.created_at >= ${startDate}` : sql``}
        ${startDate && endDate ? sql`AND rd.created_at <= ${endDate}` : sql``}
        GROUP BY eak.id, eak.company, eak.name
        ORDER BY revenue DESC
      `
    } else if (reportType === "operators") {
      // Operator details
      data = await sql`
        SELECT 
          eak.*,
          COUNT(DISTINCT rd.id) as total_downloads,
          SUM(rd.price) as total_revenue,
          COUNT(DISTINCT rd.track_id) as unique_tracks,
          MAX(rd.created_at) as last_download
        FROM external_api_keys eak
        LEFT JOIN ringtone_downloads rd ON rd.api_key_id = eak.id
        GROUP BY eak.id
        ORDER BY total_revenue DESC
      `
    }

    if (format === "csv") {
      // Convert to CSV
      if (Array.isArray(data) && data.length > 0) {
        const headers = Object.keys(data[0])
        const rows = data.map((row: any) => headers.map((h) => JSON.stringify(row[h] || "")).join(","))
        const csv = [headers.join(","), ...rows].join("\n")

        return new NextResponse(csv, {
          headers: {
            "Content-Type": "text/csv",
            "Content-Disposition": `attachment; filename="africanmusic-${reportType}-${Date.now()}.csv"`,
          },
        })
      }
    }

    // Default JSON response
    return NextResponse.json({
      success: true,
      report_type: reportType,
      generated_at: new Date().toISOString(),
      period: {
        start: startDate,
        end: endDate,
      },
      data,
    })
  } catch (error) {
    console.error("Error exporting report:", error)
    return NextResponse.json({ error: "Failed to export report" }, { status: 500 })
  }
}
