--#region rewards config
local donatorGroup = "Donator"
local dailyTPassAmount = 2
local dailyDetePassAmount = 2
local rewardCooldown = 24 -- in hours
--#endregion

--#region mysql config
local mysql_hostname = '127.0.0.1'
local mysql_username = 'gmod'
local mysql_password = 'AcMkz15orXkEHR73'
local mysql_database = 'gmod'
local mysql_port = 3306
--#endregion

--#region Connect to DB
require('mysqloo')
local db = mysqloo.connect(mysql_hostname, mysql_username, mysql_password, mysql_database, mysql_port)
function db:onConnected()
    print("[DailyRewards] Successfully connected to database.")
end
function db:onConnectionFailed(err)
    print("[DailyRewards] Connection to database failed: " .. err)
end
db:connect()
--#endregion

--#region Handle MySQLError
local function handleMySQLError(query,err,sql)
    if db:status() ~= mysqloo.DATABASE_CONNECTED then
        db:connect()
        db:wait()
    if db:status() ~= mysqloo.DATABASE_CONNECTED then
        ErrorNoHalt("Re-connection to database server failed.")
        return
        end
   end
    query:start()
end
--#endregion

--#region Create table if it does not exist yet
local cmd = "CREATE TABLE IF NOT EXISTS `dailyrewards` (`steamid` VARCHAR(20) NOT NULL, `lastlogin` INT(32) NOT NULL, `tPass` INT(32) NOT NULL DEFAULT " .. dailyTPassAmount .. ", `detePass` INT(32) NOT NULL DEFAULT " .. dailyDetePassAmount .. ",  PRIMARY KEY (`steamid`))"
local query = db:query(cmd)
function query:onError(err, sql)
    handleMySQLError(query,err,sql)
end
query:start()
--#endregion

--#region Function to format unix timestamp
local function formatTime(currTime)
    hours = string.format("%02.f", math.floor(currTime/3600));
    mins = string.format("%02.f", math.floor(currTime/60 - (hours*60)));
    secs = string.format("%02.f", math.floor(currTime - hours*3600 - mins *60));
    return hours..":"..mins..":"..secs
end
--#endregion

--#region Handle Playercommand
hook.Add("PlayerSay", "SP_DailyRewards_HandlePlayerCommand",
    function(ply,text)
        if ply:IsUserGroup(donatorGroup) or ply:IsUserGroup("Developer") then -- TODO: REMOVE DEV
            if string.lower(text) == "!traitor" or string.lower(text) == "!t" or string.lower(text) == "!detective" or string.lower(text) == "!dete" then
                local cmd5 = "SELECT * FROM dailyrewards WHERE steamid = '%s'"
                local query5 = db:query(string.format(cmd5,ply:SteamID()))

                function query5:onSuccess(data)
                    if #data > 0 then
                        local row = data[1]
                        local tmp
                        if string.sub(string.lower(text),1,2) == "!t" then tmp = "tPass;Traitor;ROLE_TRAITOR" else tmp = "detePass;Detective;ROLE_DETECTIVE" end
                        if row[string.Split(tmp,";")[1]] > 0 then -- does the player still have a pass left?
                            local cmd6 = "UPDATE dailyrewards SET %s='%d' WHERE steamid = '%s'"
                            local query6 = db:query(string.format(cmd6,string.Split(tmp,";")[1], row[string.Split(tmp,";")[1]]-1, ply:SteamID()))

                            function query6:onError(err,sql)
                                handleMySQLError(query6,err,sql)
                            end
                            query6:start()
                            ply:PrintMessage(HUD_PRINTTALK, "[GEHEIM] Du wirst in der nächsten Runde " .. string.Split(tmp,";")[2] .. ". Dir bleiben " .. row[string.Split(tmp,";")[1]]-1 .. " " ..  string.Split(tmp,";")[2] .. " Pässe." )
                            ply:SetRole(string.Split(tmp,";")[3]) --ROLE_TRAITOR/ROLE_DETECTIVE
                        else
                            if row.lastlogin + rewardCooldown *3600-os.time() <= 0 then
                                local cmd7 = "UPDATE dailyrewards SET lastlogin='%d', tPass='%d', detePass='%d'"
                                local query7 = db:query(string.format(cmd7, os.time(), dailyTPassAmount, dailyDetePassAmount))

                                function query7:onError(err,sql)
                                    handleMySQLError(query7,err,sql)
                                end
                                query7:start()
                            else
                                ply:PrintMessage(HUD_PRINTTALK, "Du kannst in " .. formatTime((row.lastlogin + rewardCooldown *3600)-os.time()))
                            end
                        end
                    else
                        ply:PrintMessage(HUD_PRINTTALK, "Das hat nicht geklappt. Wende dich bitte an einen Admin oder einen Developer.")
                    end
                end
                function query5:onError(err,sql)
                    handleMySQLError(query5,err,sql)
                end
                query5:start()
            end
        end
    end
)
--#endregion

--#region Handle new player
hook.Add("PlayerInitialSpawn", "SP_DailyRewards_HandleNewPlayer",
    function(ply)
        if ply:IsUserGroup(donatorGroup) or ply:IsUserGroup("Developer") then -- TODO: REMOVE DEV
            local cmd2 = "SELECT * FROM dailyrewards WHERE steamid = '%s'" -- Try to select player's info from mysql database
            local query2 = db:query(string.format(cmd2,ply:SteamID()))

            function query2:onSuccess(data)
                if #data <= 0 or data == nil then
                    local cmd3 = "INSERT INTO dailyrewards VALUES('%s','%d','%d','%d')" -- create player profile in database
                    local query3 = db:query(string.format(cmd3, ply:SteamID(), os.time(),dailyTPassAmount,dailyDetePassAmount))

                    function query3:onError(err,sql)
                        handleMySQLError(query3,err,sql)
                    end
                    query3:start()
                end
            end

            function query2:onError(err,sql)
                handleMySQLError(query2,err,sql)
            end
            query2:start()
        end
    end
)
--#endregion

--#region Handle leaving player
hook.Add("PlayerDisconnected", "SP_DailyRewards_HandleLeavingPlayer",
    function(ply)
        if ply:IsUserGroup(donatorGroup) or ply:IsUserGroup("Developer") then -- TODO: REMOVE DEV
            local cmd4 = "UPDATE dailyrewards SET lastlogin='%d' WHERE steamid = '%s'"
            local query4 = db:query(string.format(cmd4,os.time(),ply:SteamID()))

            function query4:onError(err,sql)
                handleMySQLError(query4,err,sql)
            end
            query4:start()
        end
    end
)
--#endregion