if not sql.TableExists("player_job_times") then
    sql.Query("CREATE TABLE player_job_times (steamid TEXT, job TEXT, time INTEGER, PRIMARY KEY (steamid, job))")
end

local trackedJobs = {
    ["Citizen"] = true,
    ["Hitman"] = true,
    ["Hacker"] = true
}

local function getPlayerJobTime(ply, job)
    return tonumber(sql.QueryValue("SELECT time FROM player_job_times WHERE steamid = '" .. ply:SteamID64() .. "' AND job = '" .. job .. "'")) or 0
end

local function setPlayerJobTime(ply, job, time)
    sql.Query("INSERT OR REPLACE INTO player_job_times (steamid, job, time) VALUES ('" .. ply:SteamID64() .. "', '" .. job .. "', " .. time .. ")")
end

local function addPlayerJobTime(ply, job, time)
    local currentTime = getPlayerJobTime(ply, job)
    setPlayerJobTime(ply, job, currentTime + time)
end

local function showJobTimesUI(ply)
    local jobTimes = {}

    for _, job in ipairs(RPExtraTeams) do
        jobTimes[job.name] = {time = 0, color = job.color}
    end

    for _, p in ipairs(player.GetAll()) do
        local steamid = p:SteamID64()

        for _, job in ipairs(RPExtraTeams) do
            local time = getPlayerJobTime(p, job.name)
            jobTimes[job.name].time = jobTimes[job.name].time + time
        end
    end

    local frame = vgui.Create("DFrame")
    frame:SetSize(ScrW() * 0.6, ScrH() * 0.6)
    frame:Center()
    frame:SetTitle("Job Times")
    frame:SetVisible(true)
    frame:SetDraggable(true)
    frame:ShowCloseButton(true)
    frame:MakePopup()

    local jobList = vgui.Create("DListView", frame)
    jobList:Dock(FILL)
    jobList:SetMultiSelect(false)
    jobList:AddColumn("Job")
    jobList:AddColumn("Time")
    jobList:AddColumn("Color")

    for _, job in ipairs(RPExtraTeams) do
        local jobTime = 0
        for _, p in ipairs(player.GetAll()) do
            local time = getPlayerJobTime(p, job.name)
            jobTime = jobTime + time
        end

        if jobTime > 0 then
            local niceTime = string.NiceTime(jobTime)
            jobList:AddLine(job.name, niceTime, tostring(job.color))
        end
    end
end


concommand.Add("show_job_times", showJobTimesUI)


hook.Add("PlayerDisconnected", "SaveJobTimes", function(ply)
    for job, _ in pairs(trackedJobs) do
        local currentTime = os.time()
        local lastTime = ply:GetNWInt("JobTime_" .. job, 0)
        local timeOnJob = currentTime - lastTime

        local totalTime = tonumber(sql.QueryValue("SELECT totaltime FROM player_jobtimes WHERE steamid = '".. ply:SteamID() .."' AND job = '".. job .."'")) or 0
        sql.Query("REPLACE INTO player_jobtimes (steamid, job, jobtime, totaltime) VALUES ('".. ply:SteamID() .."', '".. job .."', ".. lastTime ..", ".. (totalTime + timeOnJob) ..")")
    end
end)

hook.Add("PlayerChangedTeam", "SavePlayerJobTimes", function(ply, oldTeam, newTeam)
    local job = RPExtraTeams[newTeam].name
    local currentTime = os.time()
    local lastTime = ply:GetNWInt("JobTime_" .. job, currentTime)

    local totalTime = tonumber(sql.QueryValue("SELECT totaltime FROM player_jobtimes WHERE steamid = '".. ply:SteamID() .."' AND job = '".. job .."'")) or 0
    sql.Query("REPLACE INTO player_jobtimes (steamid, job, jobtime, totaltime) VALUES ('".. ply:SteamID() .."', '".. job .."', ".. lastTime ..", ".. totalTime ..")")

    ply:SetNWInt("JobTime_" .. job, currentTime)
end)

hook.Add("PlayerInitialSpawn", "TrackJobTimes", function(ply)
    for job, _ in pairs(trackedJobs) do
        local lastTime = tonumber(sql.QueryValue("SELECT jobtime FROM player_jobtimes WHERE steamid = '".. ply:SteamID() .."' AND job = '".. job .."'")) or 0
        ply:SetNWInt("JobTime_" .. job, lastTime)
    end
end)

hook.Add("PlayerChangedTeam", "TrackJobTimes", function(ply, oldTeam, newTeam)
    local job = RPExtraTeams[newTeam].name
    local currentTime = os.time()
    local lastTime = ply:GetNWInt("JobTime_" .. job, currentTime)

    ply:SetNWInt("JobTime_" .. job, currentTime)
end)