local QBCore = exports['qb-core']:GetCoreObject()

-- Aktive DJ-Booths
local activeDJBooths = {}

-- Database Setup
CreateThread(function()
    MySQL.query([[
        CREATE TABLE IF NOT EXISTS dj_playlists (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            owner VARCHAR(50) NOT NULL,
            description TEXT DEFAULT NULL,
            is_public TINYINT(1) DEFAULT 0,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        )
    ]])
    
    MySQL.query([[
        CREATE TABLE IF NOT EXISTS dj_playlist_songs (
            id INT AUTO_INCREMENT PRIMARY KEY,
            playlist_id INT NOT NULL,
            title VARCHAR(255) NOT NULL,
            artist VARCHAR(255) DEFAULT NULL,
            url TEXT NOT NULL,
            converted_url TEXT DEFAULT NULL,
            duration INT DEFAULT NULL,
            position INT DEFAULT 0,
            song_type VARCHAR(20) DEFAULT 'direct',
            thumbnail TEXT DEFAULT NULL,
            added_by VARCHAR(50) DEFAULT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (playlist_id) REFERENCES dj_playlists(id) ON DELETE CASCADE
        )
    ]])
    
    MySQL.query([[
        CREATE TABLE IF NOT EXISTS dj_url_cache (
            id INT AUTO_INCREMENT PRIMARY KEY,
            original_url VARCHAR(500) NOT NULL,
            converted_url TEXT NOT NULL,
            video_id VARCHAR(50) DEFAULT NULL,
            title VARCHAR(255) DEFAULT NULL,
            duration INT DEFAULT NULL,
            thumbnail TEXT DEFAULT NULL,
            expires_at TIMESTAMP NOT NULL,
            hit_count INT DEFAULT 1,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            last_accessed TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY (original_url(255))
        )
    ]])
    
    MySQL.query([[
        CREATE TABLE IF NOT EXISTS dj_session_history (
            id INT AUTO_INCREMENT PRIMARY KEY,
            dj_citizenid VARCHAR(50) NOT NULL,
            dj_name VARCHAR(100) NOT NULL,
            booth_name VARCHAR(100) NOT NULL,
            song_title VARCHAR(255) NOT NULL,
            song_url TEXT NOT NULL,
            song_type VARCHAR(20) DEFAULT 'direct',
            volume INT DEFAULT 50,
            duration_played INT DEFAULT NULL,
            session_start TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            session_end TIMESTAMP NULL DEFAULT NULL,
            listeners_count INT DEFAULT 0
        )
    ]])
end)

-- YouTube URL Funktionen
local function extractYouTubeVideoId(url)
    -- YouTube URL Patterns
    local patterns = {
        "youtube%.com/watch%?v=([%w%-_]+)",
        "youtube%.com/watch%?.*&v=([%w%-_]+)",
        "youtu%.be/([%w%-_]+)",
        "youtube%.com/embed/([%w%-_]+)"
    }
    
    for _, pattern in ipairs(patterns) do
        local videoId = string.match(url, pattern)
        if videoId then
            return videoId
        end
    end
    
    return nil
end

local function cleanYouTubeUrl(url)
    local videoId = extractYouTubeVideoId(url)
    if not videoId then
        return nil
    end
    
    -- Erstelle saubere YouTube URL ohne Playlist-Parameter
    return "https://www.youtube.com/watch?v=" .. videoId
end

-- Events
RegisterServerEvent('dj:server:playMusic')
AddEventHandler('dj:server:playMusic', function(data)
    local src = source
    local Player = QBCore.Functions.GetPlayer(src)
    if not Player then return end
    
    -- Speichere den aktuellen DJ-Status
    activeDJBooths[data.booth.name] = {
        url = data.url,
        title = data.title,
        volume = data.volume,
        range = data.range,
        dj = {
            id = Player.PlayerData.citizenid,
            name = Player.PlayerData.charinfo.firstname .. ' ' .. Player.PlayerData.charinfo.lastname
        },
        coords = data.booth.coords
    }
    
    -- Sende an alle Spieler
    TriggerClientEvent('dj:client:playMusic', -1, data)
    
    -- Log für Admin
    print(('[DJ System] %s spielt Musik ab: %s | Booth: %s'):format(
        GetPlayerName(src), 
        data.title,
        data.booth.name
    ))
    
    -- Speichere in Datenbank
    MySQL.insert('INSERT INTO dj_session_history (dj_citizenid, dj_name, booth_name, song_title, song_url, song_type, volume, session_start) VALUES (?, ?, ?, ?, ?, ?, ?, NOW())', {
        Player.PlayerData.citizenid,
        Player.PlayerData.charinfo.firstname .. ' ' .. Player.PlayerData.charinfo.lastname,
        data.booth.name,
        data.title,
        data.url,
        string.match(data.url, "youtube") and 'youtube' or 'direct',
        data.volume
    })
end)

RegisterServerEvent('dj:server:stopMusic')
AddEventHandler('dj:server:stopMusic', function(boothName)
    local src = source
    
    -- Entferne DJ-Status
    if activeDJBooths[boothName] then
        -- Aktualisiere Session-Ende in der Datenbank
        local djData = activeDJBooths[boothName]
        if djData and djData.dj then
            MySQL.update('UPDATE dj_session_history SET session_end = NOW() WHERE dj_citizenid = ? AND booth_name = ? AND session_end IS NULL', {
                djData.dj.id,
                boothName
            })
        end
        
        activeDJBooths[boothName] = nil
    end
    
    -- Sende an alle Spieler
    TriggerClientEvent('dj:client:stopMusic', -1, boothName)
    
    print(('[DJ System] %s hat die Musik gestoppt in: %s'):format(GetPlayerName(src), boothName))
end)

RegisterServerEvent('dj:server:setVolume')
AddEventHandler('dj:server:setVolume', function(data)
    local src = source
    
    -- Aktualisiere DJ-Status
    if activeDJBooths[data.booth.name] then
        activeDJBooths[data.booth.name].volume = data.volume
        activeDJBooths[data.booth.name].range = data.range
    end
    
    -- Sende an alle Spieler
    TriggerClientEvent('dj:client:setVolume', -1, data)
    
    print(('[DJ System] %s hat die Lautstärke auf %d%% gesetzt in: %s'):format(
        GetPlayerName(src), 
        data.volume,
        data.booth.name
    ))
end)

-- Playlist Management
RegisterServerEvent('dj:server:getPlaylists')
AddEventHandler('dj:server:getPlaylists', function()
    local src = source
    local Player = QBCore.Functions.GetPlayer(src)
    if not Player then return end
    
    MySQL.query('SELECT * FROM dj_playlists WHERE owner = ? OR is_public = 1', {Player.PlayerData.citizenid}, function(playlists)
        local playlistData = {}
        
        for _, playlist in pairs(playlists) do
            MySQL.query('SELECT * FROM dj_playlist_songs WHERE playlist_id = ? ORDER BY position', {playlist.id}, function(songs)
                table.insert(playlistData, {
                    id = playlist.id,
                    name = playlist.name,
                    description = playlist.description,
                    isPublic = playlist.is_public == 1,
                    isOwner = playlist.owner == Player.PlayerData.citizenid,
                    songs = songs
                })
                
                if #playlistData == #playlists then
                    TriggerClientEvent('dj:client:updatePlaylists', src, playlistData)
                end
            end)
        end
        
        if #playlists == 0 then
            TriggerClientEvent('dj:client:updatePlaylists', src, {})
        end
    end)
end)

RegisterServerEvent('dj:server:createPlaylist')
AddEventHandler('dj:server:createPlaylist', function(name, description, isPublic)
    local src = source
    local Player = QBCore.Functions.GetPlayer(src)
    if not Player then return end
    
    MySQL.insert('INSERT INTO dj_playlists (name, owner, description, is_public) VALUES (?, ?, ?, ?)', {
        name,
        Player.PlayerData.citizenid,
        description or '',
        isPublic and 1 or 0
    }, function(id)
        if id then
            TriggerClientEvent('QBCore:Notify', src, 'Playlist "' .. name .. '" wurde erstellt!', 'success')
            TriggerEvent('dj:server:getPlaylists', src)
        else
            TriggerClientEvent('QBCore:Notify', src, 'Fehler beim Erstellen der Playlist!', 'error')
        end
    end)
end)

RegisterServerEvent('dj:server:addSongToPlaylist')
AddEventHandler('dj:server:addSongToPlaylist', function(playlistId, song)
    local src = source
    local Player = QBCore.Functions.GetPlayer(src)
    if not Player then return end
    
    -- Prüfe ob Playlist dem Spieler gehört
    MySQL.query('SELECT * FROM dj_playlists WHERE id = ? AND (owner = ? OR is_public = 1)', {
        playlistId,
        Player.PlayerData.citizenid
    }, function(result)
        if result[1] then
            -- Bereinige YouTube URL
            local songUrl = song.url
            if string.match(songUrl, "youtube") then
                local cleanUrl = cleanYouTubeUrl(songUrl)
                if cleanUrl then
                    songUrl = cleanUrl
                end
            end
            
            -- Hole höchste Position
            MySQL.query('SELECT MAX(position) as max_pos FROM dj_playlist_songs WHERE playlist_id = ?', {playlistId}, function(posResult)
                local position = 1
                if posResult[1] and posResult[1].max_pos then
                    position = posResult[1].max_pos + 1
                end
                
                -- Füge Song hinzu
                MySQL.insert('INSERT INTO dj_playlist_songs (playlist_id, title, artist, url, song_type, position, added_by) VALUES (?, ?, ?, ?, ?, ?, ?)', {
                    playlistId,
                    song.title,
                    song.artist or '',
                    songUrl,
                    string.match(songUrl, "youtube") and 'youtube' or 'direct',
                    position,
                    Player.PlayerData.citizenid
                }, function(id)
                    if id then
                        TriggerClientEvent('QBCore:Notify', src, 'Song wurde zur Playlist hinzugefügt!', 'success')
                        TriggerEvent('dj:server:getPlaylists', src)
                    else
                        TriggerClientEvent('QBCore:Notify', src, 'Fehler beim Hinzufügen des Songs!', 'error')
                    end
                end)
            end)
        else
            TriggerClientEvent('QBCore:Notify', src, 'Du hast keine Berechtigung für diese Playlist!', 'error')
        end
    end)
end)

RegisterServerEvent('dj:server:removeSongFromPlaylist')
AddEventHandler('dj:server:removeSongFromPlaylist', function(playlistId, songId)
    local src = source
    local Player = QBCore.Functions.GetPlayer(src)
    if not Player then return end
    
    -- Prüfe ob Playlist dem Spieler gehört
    MySQL.query('SELECT * FROM dj_playlists WHERE id = ? AND owner = ?', {
        playlistId,
        Player.PlayerData.citizenid
    }, function(result)
        if result[1] then
            MySQL.query('DELETE FROM dj_playlist_songs WHERE id = ? AND playlist_id = ?', {
                songId,
                playlistId
            }, function(affectedRows)
                if affectedRows > 0 then
                    TriggerClientEvent('QBCore:Notify', src, 'Song wurde aus der Playlist entfernt!', 'success')
                    TriggerEvent('dj:server:getPlaylists', src)
                else
                    TriggerClientEvent('QBCore:Notify', src, 'Fehler beim Entfernen des Songs!', 'error')
                end
            end)
        else
            TriggerClientEvent('QBCore:Notify', src, 'Du hast keine Berechtigung für diese Playlist!', 'error')
        end
    end)
end)

RegisterServerEvent('dj:server:deletePlaylist')
AddEventHandler('dj:server:deletePlaylist', function(playlistId)
    local src = source
    local Player = QBCore.Functions.GetPlayer(src)
    if not Player then return end
    
    MySQL.query('DELETE FROM dj_playlists WHERE id = ? AND owner = ?', {
        playlistId,
        Player.PlayerData.citizenid
    }, function(affectedRows)
        if affectedRows > 0 then
            TriggerClientEvent('QBCore:Notify', src, 'Playlist wurde gelöscht!', 'success')
            TriggerEvent('dj:server:getPlaylists', src)
        else
            TriggerClientEvent('QBCore:Notify', src, 'Fehler beim Löschen der Playlist!', 'error')
        end
    end)
end)

RegisterServerEvent('dj:server:updatePlaylist')
AddEventHandler('dj:server:updatePlaylist', function(playlistId, name, description, isPublic)
    local src = source
    local Player = QBCore.Functions.GetPlayer(src)
    if not Player then return end
    
    MySQL.update('UPDATE dj_playlists SET name = ?, description = ?, is_public = ? WHERE id = ? AND owner = ?', {
        name,
        description or '',
        isPublic and 1 or 0,
        playlistId,
        Player.PlayerData.citizenid
    }, function(affectedRows)
        if affectedRows > 0 then
            TriggerClientEvent('QBCore:Notify', src, 'Playlist wurde aktualisiert!', 'success')
            TriggerEvent('dj:server:getPlaylists', src)
        else
            TriggerClientEvent('QBCore:Notify', src, 'Fehler beim Aktualisieren der Playlist!', 'error')
        end
    end)
end)

-- Synchronisation
RegisterServerEvent('dj:server:requestActiveDJs')
AddEventHandler('dj:server:requestActiveDJs', function()
    local src = source
    TriggerClientEvent('dj:client:receiveActiveDJs', src, activeDJBooths)
end)

-- Wenn ein Spieler das Spiel verlässt und DJ war, stoppe die Musik
AddEventHandler('playerDropped', function(reason)
    local src = source
    local Player = QBCore.Functions.GetPlayer(src)
    if not Player then return end
    
    -- Prüfe ob Spieler DJ war
    for boothName, boothData in pairs(activeDJBooths) do
        if boothData.dj and boothData.dj.id == Player.PlayerData.citizenid then
            -- Spieler war DJ, stoppe Musik
            
            -- Aktualisiere Session-Ende in der Datenbank
            MySQL.update('UPDATE dj_session_history SET session_end = NOW() WHERE dj_citizenid = ? AND booth_name = ? AND session_end IS NULL', {
                boothData.dj.id,
                boothName
            })
            
            activeDJBooths[boothName] = nil
            TriggerClientEvent('dj:client:stopMusic', -1, boothName)
            
            print(('[DJ System] DJ %s hat das Spiel verlassen, Musik in %s gestoppt'):format(
                GetPlayerName(src),
                boothName
            ))
        end
    end
end)

-- Cache Cleanup (läuft alle 30 Minuten)
CreateThread(function()
    while true do
        Wait(1800000) -- 30 Minuten
        MySQL.query('DELETE FROM dj_url_cache WHERE expires_at < NOW()')
        print('[DJ System] Cache bereinigt')
    end
end)

-- Initialisiere Standard-Playlists
CreateThread(function()
    Wait(5000) -- Warte bis Datenbank bereit ist
    
    -- Prüfe ob Standard-Playlists existieren
    MySQL.query('SELECT * FROM dj_playlists WHERE owner = ?', {'system'}, function(result)
        if #result == 0 then
            print('[DJ System] Erstelle Standard-Playlists')
            
            -- Erstelle Standard-Playlists
            for _, playlist in pairs(Config.DefaultPlaylists) do
                MySQL.insert('INSERT INTO dj_playlists (name, owner, description, is_public) VALUES (?, ?, ?, ?)', {
                    playlist.name,
                    'system',
                    'Standard-Playlist',
                    1
                }, function(playlistId)
                    if playlistId then
                        -- Füge Songs hinzu
                        for position, song in ipairs(playlist.songs) do
                            MySQL.insert('INSERT INTO dj_playlist_songs (playlist_id, title, url, song_type, position, added_by) VALUES (?, ?, ?, ?, ?, ?)', {
                                playlistId,
                                song.title,
                                song.url,
                                string.match(song.url, "youtube") and 'youtube' or 'direct',
                                position,
                                'system'
                            })
                        end
                        
                        print('[DJ System] Standard-Playlist erstellt: ' .. playlist.name)
                    end
                end)
            end
        end
    end)
end)

-- QBCore Callbacks
QBCore.Functions.CreateCallback('dj:server:getActiveDJs', function(source, cb)
    cb(activeDJBooths)
end)

QBCore.Functions.CreateCallback('dj:server:getPlaylists', function(source, cb)
    local Player = QBCore.Functions.GetPlayer(source)
    if not Player then return cb({}) end
    
    MySQL.query('SELECT * FROM dj_playlists WHERE owner = ? OR is_public = 1', {Player.PlayerData.citizenid}, function(playlists)
        local playlistData = {}
        local remaining = #playlists
        
        if remaining == 0 then
            return cb({})
        end
        
        for _, playlist in pairs(playlists) do
            MySQL.query('SELECT * FROM dj_playlist_songs WHERE playlist_id = ? ORDER BY position', {playlist.id}, function(songs)
                table.insert(playlistData, {
                    id = playlist.id,
                    name = playlist.name,
                    description = playlist.description,
                    isPublic = playlist.is_public == 1,
                    isOwner = playlist.owner == Player.PlayerData.citizenid,
                    songs = songs
                })
                
                remaining = remaining - 1
                if remaining == 0 then
                    cb(playlistData)
                end
            end)
        end
    end)
end)

QBCore.Functions.CreateCallback('dj:server:getSessionHistory', function(source, cb, limit)
    local Player = QBCore.Functions.GetPlayer(source)
    if not Player then return cb({}) end
    
    limit = limit or 50
    
    MySQL.query('SELECT * FROM dj_session_history WHERE dj_citizenid = ? ORDER BY session_start DESC LIMIT ?', {
        Player.PlayerData.citizenid,
        limit
    }, function(history)
        cb(history)
    end)
end)

-- Admin Commands
QBCore.Commands.Add('djstop', 'Stoppe alle DJ-Booths (Admin)', {}, false, function(source, args)
    local Player = QBCore.Functions.GetPlayer(source)
    if Player.PlayerData.job.name ~= 'admin' and Player.PlayerData.job.name ~= 'police' then
        TriggerClientEvent('QBCore:Notify', source, 'Du hast keine Berechtigung für diesen Befehl!', 'error')
        return
    end
    
    for boothName, _ in pairs(activeDJBooths) do
        -- Aktualisiere Session-Ende in der Datenbank
        local djData = activeDJBooths[boothName]
        if djData and djData.dj then
            MySQL.update('UPDATE dj_session_history SET session_end = NOW() WHERE dj_citizenid = ? AND booth_name = ? AND session_end IS NULL', {
                djData.dj.id,
                boothName
            })
        end
        
        activeDJBooths[boothName] = nil
        TriggerClientEvent('dj:client:stopMusic', -1, boothName)
    end
    
    TriggerClientEvent('QBCore:Notify', source, 'Alle DJ-Booths wurden gestoppt!', 'success')
    print('[DJ System] Admin ' .. GetPlayerName(source) .. ' hat alle DJ-Booths gestoppt')
end)

QBCore.Commands.Add('djstatus', 'Zeige Status aller DJ-Booths (Admin)', {}, false, function(source, args)
    local Player = QBCore.Functions.GetPlayer(source)
    if Player.PlayerData.job.name ~= 'admin' and Player.PlayerData.job.name ~= 'police' then
        TriggerClientEvent('QBCore:Notify', source, 'Du hast keine Berechtigung für diesen Befehl!', 'error')
        return
    end
    
    local status = {}
    for boothName, boothData in pairs(activeDJBooths) do
        table.insert(status, {
            booth = boothName,
            song = boothData.title,
            dj = boothData.dj.name,
            volume = boothData.volume
        })
    end
    
    if #status == 0 then
        TriggerClientEvent('QBCore:Notify', source, 'Keine aktiven DJ-Booths!', 'info')
    else
        for _, booth in ipairs(status) do
            TriggerClientEvent('QBCore:Notify', source, booth.booth .. ': ' .. booth.song .. ' (DJ: ' .. booth.dj .. ', Vol: ' .. booth.volume .. '%)', 'info')
        end
    end
end)

-- Exports
exports('GetActiveDJs', function()
    return activeDJBooths
end)

exports('StopBooth', function(boothName)
    if activeDJBooths[boothName] then
        -- Aktualisiere Session-Ende in der Datenbank
        local djData = activeDJBooths[boothName]
        if djData and djData.dj then
            MySQL.update('UPDATE dj_session_history SET session_end = NOW() WHERE dj_citizenid = ? AND booth_name = ? AND session_end IS NULL', {
                djData.dj.id,
                boothName
            })
        end
        
        activeDJBooths[boothName] = nil
        TriggerClientEvent('dj:client:stopMusic', -1, boothName)
        return true
    end
    return false
end)

-- Initialisierung
print('[DJ System] Server gestartet')