---Functions for working the the mysqlite module.
-- Helps load players into their correct instance
--@server sv_setup.lua
--@alias sql

--Config for mysql must be in before mysql
local config = nrequire("config/sv_sql.lua")
--Adds the MySQLite global
nrequire("sv_mysqlite.lua")
local data = nrequire("config/sv_newplayer.lua")
local col = nrequire("config/colortheme.lua")
local q = nrequire("core/database/sv_queries.lua")
local log = nrequire("shared/log.lua")
local sql = {}

--Setup the database if it's not already
local setup_db = [[
CREATE TABLE IF NOT EXISTS playerdata(SteamID bigint primary key, PlayerData TEXT, MetaData TEXT)]]

local setup_mapstich = [[
CREATE TABLE IF NOT EXISTS maps(Server TEXT, Image BINARY, Connectors TEXT);
]]

local insert_server = [[
INSERT INTO maps(`Server`,`Connectors`) VALUES('%s','%s');
]]

local update_server = [[
UPDATE maps SET Connectors='%s' WHERE Server='%s';
]]

--Create a new player
local create_player_query = [[
INSERT INTO playerdata (`SteamID`,`PlayerData`,`MetaData`) VALUES(%.0f,'%s','%s')]]

--Get a player's data from the database
local fetch_player_query = [[
SELECT PlayerData, MetaData FROM playerdata WHERE SteamID=%.0f
]]

local save_player_query = [[
UPDATE playerdata SET MetaData='%s' PlayerData='%s' WHERE SteamID=%.0f
]]

local function q_fai(err,query)
	log.error(string.format("Error executing %s, error:%s",query,err))
end

local function connect()
	--print("Connecting to the database...")
	MySQLite.initialize(config)
	local omq = MySQLite.query
	MySQLite.query = function(...)
		local varargs = {...}
		log.debug(string.format(">>%s",varargs[1]))
		omq(unpack(varargs))
	end
end
hook.Add("DatabaseInitialized","setup_table",function()
	if not MySQLite.isMySQL() then
		log.warn("Database wasn't mysqloo, something is probably wrong!")
	end
	local setup_success = function(res,li)
		log.info("Set up playerdata table")
		--print("Set up connection to db")
	end
	--print("Setup query:",setup_db)
	MySQLite.query(setup_db,setup_success,q_fai)
	local q_suc = function(res,li)
		log.info("Sucessfully created maps table")
		local zall = {}
		for k,v in pairs(zones.List) do
			if v.class == "artery_serverchange" then
				zall[#zall + 1] = v
			end
		end
		local iinsert_suc = function(res,li)
			log.info("Successfully inserted new server to maps")
		end
		local iinsert_fai = function(sql,err)
			local qn = q.s_fmt(update_server,game.GetIPAddress(),util.TableToJSON(zall))
			local justprint = function(res,li)
				print("Successfully updated server pos and stuff")
			end
			MySQLite.query(qn,justprin,q_fai)
		end
		local q_s = q.s_fmt(insert_server,game.GetIPAddress(),util.TableToJSON(zall))
		MySQLite.query(q_s,iinsert_suc,q_fai)
	end
	MySQLite.query(setup_mapstich,q_suc,q_fai)
end)

hook.Add("Initialize","initalizedbconnection",function()
	connect()
end)

---Gets a player's data from the database.
-- Finds a player's data, returns nil if not found. Players are found off their steamid
--@tparam player ply The player to look for's data
--@treturn table|nil {PlayerData, MetaData}
function sql.GetPlayerData(ply)
	local s64 = ply:SteamID64()
	local q_str = q.s_fmt(fetch_player_query,s64)
	local q_suc = function(res,li)
		log.debug("Loading player, res is " .. tostring(res))
		if res == nil then
			log.debug("Creating new player")
			sql.CreatePlayerTable(ply)
		else
			assert(#res == 1,"Not unique!")
			local meta = res[1].MetaData
			local plyd = res[1].PlayerData
			local mtbl = util.JSONToTable(meta)
			if mtbl.lastserver ~= game.GetIPAddress() then
				--ply:ConCommand("connect " .. mtbl.lastserver)
				-- net.Start("art_sendtoserver")
				-- net.WriteString(mtbl.lastserver)
				-- net.Send(ply)
				--return nil
			end
			local _,_,x,y,z = string.find(mtbl.lastlocation,"([-%d%.]+) ([-%d%.]+) ([-%d%.]+)")
			local vec = {x,y,z}
			for k,v in pairs(vec) do vec[k] = tonumber(v) end
			ply:SetPos(Vector(unpack(vec)))
			xpcall(function()
				q.deserialize_player(ply,plyd)
			end,function(err)
				log.error("Failed to load:" .. ply:Nick() .. ":" .. err)
				log.error(debug.traceback())
			end)
		end
	end
	--print("doing query",q_str)
	MySQLite.query(q_str,q_suc,q_fai)
end

---Manually loads data for the caller.
-- Forefully loads a player's data.
--@concommand artery_loadplayer
concommand.Add("artery_loadplayer",function(ply,cmd,args)
	if not ply:IsAdmin() then return end
	sql.GetPlayerData(ply)
end)

---Creates a new row for the player in the database.
-- Creates a new row for a player, works off the configuration in sv_newplayer.lua
--@see sv_newplayer.lua
--@tparam player ply The player to create the new row for
function sql.CreatePlayerTable(ply)
	log.debug(string.format("Creating new player data for %q", ply:Nick()))
	--print("Createing player table....")
	local s64 = ply:SteamID64()
	log.debug("got steam id it was " .. tostring(s64))
	--print("steamid was", s64)
	local plytbl = data.newdata()
	log.debug("about to ask for new meta")
	local plymet = data.newmeta()
	log.debug("About to get plydata and plymeta")
	local plydata = util.TableToJSON(plytbl)
	local metdata = util.TableToJSON(plymet)
	local q_str = q.s_fmt(create_player_query,s64,plydata,metdata)
	log.debug("built q_str it is" .. q_str)
	local q_suc = function(res,li)
		log.debug("Inserted new player")
		--print("Inserted new player",ply)
		sql.GetPlayerData(ply)
	end
	log.debug("About to do query")
	--print("doing query", q_str)
	MySQLite.query(q_str,q_suc,q_fai)
end

---Sends a player to another server.
-- Usually when a player disconnects, their location & server are saved, this overrides that and sends them to another instance when they reconnect.
--@tparam player ply The player to send to a different instace
--@tparam string ls The server to send the player to
--@tparam vector3 ll The location to send the player to on that server
function sql.SendPlayerToInstance(ply,ls,ll)
	local s64 = ply:SteamID64()
	local plydata = q.serialize_player(ply)
	local plymeta = util.TableToJSON({
		lastserver = ls,
		lastlocation = ll
	})
	local q_str = q.s_fmt(save_player_query,plymeta,plydata,s64)
	local q_suc = function(res,li)
		--print("Successfully saved player data")
	end
	MySQLite.query(q_str,q_suc,q_fai)
end

---Do queries related to the player creation.
-- Create the caller's data, Reload the coller's data, or Send the caller to another instance
--@usage artery_DoQuery (create|get|send <server> <location>)
--@concommand artery_DoQuery
concommand.Add("artery_DoQuery",function(ply,cmd,args)
	if not ply:IsAdmin() then return end
	if args[1] == "create" then
		sql.CreatePlayerTable(ply)
	elseif args[1] == "get" then
		sql.GetPlayerData(ply)
	elseif args[1] == "send" then
		sql.SendPlayerToInstance(ply,args[2],args[3])
	else
		error("Command not understood:" .. args[1] .. "!")
	end
end)

--print("In sv_setup.lua, sql before returning is", sql)

return sql