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

export async function GET(request: NextRequest, { params }: { params: Promise<{ id: string }> }) {
  try {
    const { id } = await params

    const users = await sql`
      SELECT 
        u.id, u.email, u.name, u.avatar_url, u.role, u.subscription_type,
        u.subscription_expires_at, u.is_verified, u.is_active, 
        u.language, u.country,
        u.created_at, u.updated_at, u.last_login_at,
        COALESCE((SELECT COUNT(*) FROM listening_history WHERE user_id = u.id), 0) as total_streams,
        COALESCE((SELECT COUNT(*) FROM liked_tracks WHERE user_id = u.id), 0) as liked_count,
        COALESCE((SELECT COUNT(*) FROM downloaded_tracks WHERE user_id = u.id), 0) as downloads_count,
        COALESCE((SELECT COUNT(*) FROM playlists WHERE user_id = u.id), 0) as playlists_count
      FROM users u
      WHERE u.id = ${id}
    `

    if (users.length === 0) {
      return NextResponse.json({ error: "Utilisateur non trouvé" }, { status: 404 })
    }

    const user = users[0]

    const playlists = await sql`
      SELECT p.id, p.name, p.is_public,
        COALESCE((SELECT COUNT(*) FROM playlist_tracks WHERE playlist_id = p.id), 0) as tracks,
        COALESCE((SELECT COUNT(*) FROM playlist_followers WHERE playlist_id = p.id), 0) as followers
      FROM playlists p
      WHERE p.user_id = ${id}
      ORDER BY p.created_at DESC
      LIMIT 5
    `

    const recentTracks = await sql`
      SELECT t.title, a.name as artist, lh.played_at
      FROM listening_history lh
      JOIN tracks t ON lh.track_id = t.id
      JOIN artists a ON t.artist_id = a.id
      WHERE lh.user_id = ${id}
      ORDER BY lh.played_at DESC
      LIMIT 5
    `

    const transactions = await sql`
      SELECT id, amount, currency, status, payment_method, created_at
      FROM payment_transactions
      WHERE user_id = ${id}
      ORDER BY created_at DESC
      LIMIT 10
    `

    const devices = await sql`
      SELECT id, device_info, ip_address, created_at, expires_at
      FROM user_sessions
      WHERE user_id = ${id}
      ORDER BY created_at DESC
      LIMIT 5
    `

    const formattedUser = {
      id: user.id,
      name: user.name || "Utilisateur",
      email: user.email,
      avatar: user.avatar_url,
      phone: "Non renseigné",
      birthDate: "Non renseigné",
      country: user.country || "Non renseigné",
      city: "Non renseigné",
      language: user.language || "fr",
      bio: "",
      status: user.is_active ? "active" : "inactive",
      plan: user.subscription_type || "free",
      planExpiry: user.subscription_expires_at
        ? new Date(user.subscription_expires_at).toLocaleDateString("fr-FR")
        : "N/A",
      joined: new Date(user.created_at).toLocaleDateString("fr-FR"),
      lastActive: user.last_login_at ? new Date(user.last_login_at).toLocaleDateString("fr-FR") : "Jamais",
      streams: Number(user.total_streams) || 0,
      totalListeningTime: `${Math.floor((Number(user.total_streams) * 3.5) / 60)}h`,
      downloadsUsed: Number(user.downloads_count) || 0,
      downloadsLimit: user.subscription_type === "premium" ? 10000 : 100,
      likedCount: Number(user.liked_count) || 0,
      playlists: playlists.map((p: any) => ({
        name: p.name,
        tracks: Number(p.tracks),
        followers: Number(p.followers),
      })),
      recentTracks: recentTracks.map((t: any) => ({
        title: t.title,
        artist: t.artist,
        playedAt: new Date(t.played_at).toLocaleDateString("fr-FR"),
      })),
      transactions: transactions.map((t: any) => ({
        id: t.id.slice(0, 8).toUpperCase(),
        date: new Date(t.created_at).toLocaleDateString("fr-FR"),
        type: "Abonnement",
        amount: `${t.amount} ${t.currency || "FCFA"}`,
        status: t.status,
      })),
      devices:
        devices.length > 0
          ? devices.map((d: any) => ({
              name: d.device_info || "Appareil inconnu",
              type: "desktop",
              lastUsed: d.created_at ? new Date(d.created_at).toLocaleDateString("fr-FR") : "N/A",
            }))
          : [{ name: "Navigateur Web", type: "desktop", lastUsed: "Maintenant" }],
      activityLog: [
        {
          action: "Connexion",
          date: user.last_login_at ? new Date(user.last_login_at).toLocaleDateString("fr-FR") : "N/A",
          ip: "N/A",
        },
        {
          action: "Inscription",
          date: new Date(user.created_at).toLocaleDateString("fr-FR"),
          details: "Création du compte",
        },
      ],
    }

    return NextResponse.json({ data: formattedUser })
  } catch (error) {
    console.error("Get user error:", error)
    return NextResponse.json({ error: "Erreur lors de la récupération de l'utilisateur" }, { status: 500 })
  }
}

export async function PATCH(request: NextRequest, { params }: { params: Promise<{ id: string }> }) {
  try {
    const { id } = await params
    const updates = await request.json()

    // Build individual update queries based on what fields are provided
    const allowedFields = [
      "name",
      "avatar_url",
      "role",
      "subscription_type",
      "is_verified",
      "is_active",
      "language",
      "country",
    ]

    // Filter to only allowed fields that exist in the request
    const fieldsToUpdate = Object.keys(updates).filter((key) => allowedFields.includes(key))

    if (fieldsToUpdate.length === 0) {
      return NextResponse.json({ error: "Aucun champ valide à mettre à jour" }, { status: 400 })
    }

    // Execute update for each field using proper tagged template syntax
    let result

    if (fieldsToUpdate.includes("name") && fieldsToUpdate.includes("avatar_url")) {
      result = await sql`
        UPDATE users
        SET name = ${updates.name}, avatar_url = ${updates.avatar_url}, updated_at = CURRENT_TIMESTAMP
        WHERE id = ${id}
        RETURNING id, email, name, avatar_url, role, subscription_type, is_active
      `
    } else if (fieldsToUpdate.includes("name")) {
      result = await sql`
        UPDATE users
        SET name = ${updates.name}, updated_at = CURRENT_TIMESTAMP
        WHERE id = ${id}
        RETURNING id, email, name, avatar_url, role, subscription_type, is_active
      `
    } else if (fieldsToUpdate.includes("avatar_url")) {
      result = await sql`
        UPDATE users
        SET avatar_url = ${updates.avatar_url}, updated_at = CURRENT_TIMESTAMP
        WHERE id = ${id}
        RETURNING id, email, name, avatar_url, role, subscription_type, is_active
      `
    } else if (fieldsToUpdate.includes("is_active")) {
      result = await sql`
        UPDATE users
        SET is_active = ${updates.is_active}, updated_at = CURRENT_TIMESTAMP
        WHERE id = ${id}
        RETURNING id, email, name, avatar_url, role, subscription_type, is_active
      `
    } else if (fieldsToUpdate.includes("role")) {
      result = await sql`
        UPDATE users
        SET role = ${updates.role}, updated_at = CURRENT_TIMESTAMP
        WHERE id = ${id}
        RETURNING id, email, name, avatar_url, role, subscription_type, is_active
      `
    } else if (fieldsToUpdate.includes("subscription_type")) {
      result = await sql`
        UPDATE users
        SET subscription_type = ${updates.subscription_type}, updated_at = CURRENT_TIMESTAMP
        WHERE id = ${id}
        RETURNING id, email, name, avatar_url, role, subscription_type, is_active
      `
    } else if (fieldsToUpdate.includes("language")) {
      result = await sql`
        UPDATE users
        SET language = ${updates.language}, updated_at = CURRENT_TIMESTAMP
        WHERE id = ${id}
        RETURNING id, email, name, avatar_url, role, subscription_type, is_active
      `
    } else if (fieldsToUpdate.includes("country")) {
      result = await sql`
        UPDATE users
        SET country = ${updates.country}, updated_at = CURRENT_TIMESTAMP
        WHERE id = ${id}
        RETURNING id, email, name, avatar_url, role, subscription_type, is_active
      `
    } else {
      // Generic update for any combination
      result = await sql`
        UPDATE users
        SET updated_at = CURRENT_TIMESTAMP
        WHERE id = ${id}
        RETURNING id, email, name, avatar_url, role, subscription_type, is_active
      `
    }

    if (result.length === 0) {
      return NextResponse.json({ error: "Utilisateur non trouvé" }, { status: 404 })
    }

    return NextResponse.json(result[0])
  } catch (error) {
    console.error("Update user error:", error)
    return NextResponse.json({ error: "Erreur lors de la mise à jour" }, { status: 500 })
  }
}

export async function DELETE(request: NextRequest, { params }: { params: Promise<{ id: string }> }) {
  try {
    const { id } = await params

    await sql`DELETE FROM users WHERE id = ${id}`

    return NextResponse.json({ success: true })
  } catch (error) {
    console.error("Delete user error:", error)
    return NextResponse.json({ error: "Erreur lors de la suppression" }, { status: 500 })
  }
}
