require "shared.init"
require "utils.init"
require "GlobalVar"
require "RedisKeys"
require "ProtocolCode"
require "skynet.manager"
require "utils.MysqlUtil"

skynet = require "skynet"

redisproxy = require("shared.redisproxy")
mysqlproxy = require "shared.mysqlproxy"

SendPacket = function ( ... ) end

local function initSeaportTask()
	local keys = {[SEAPORT_TRADE_TASK_1] = "seaport_task_1", [SEAPORT_TRADE_TASK_2] = "seaport_task_2"}

	for key, tb_name in pairs(keys) do
		local res = mysqlproxy:query(string.format("SELECT `id`,`value` FROM %s;", tb_name))
		for _, v in pairs(res) do
			redisproxy:hset(key, v.id, v.value)
		end
	end
end

local function initRedisDb( ... )
	local function initAutoIncrementUid(tbName, keyName, fieldName)
		if not fieldName then fieldName = "value" end
		local mysqlVal = getDbCfgVal(tbName, keyName, fieldName)
		if not mysqlVal then 
			skynet.error(string.format("get db cfg fail, table %s, key %s, field %s", tbName, keyName, fieldName))
			return
		end
		local redisVal = tonum(redisproxy:hget(tbName, keyName))
		if redisVal < mysqlVal or redisVal == 0 then
			redisproxy:hset(tbName, keyName, mysqlVal)
		end
	end

	initAutoIncrementUid("autoincrement_set", "role")
	initAutoIncrementUid("autoincrement_set", "union")
	initAutoIncrementUid("autoincrement_set", "order")
	initAutoIncrementUid("autoincrement_set", "email")
	initAutoIncrementUid("autoincrement_set", "emailTimestamp")
	initAutoIncrementUid("autoincrement_set", "delay_email")
	initAutoIncrementUid("autoincrement_set", "stopcreate")
	initAutoIncrementUid("autoincrement_set", "maintain")
	initAutoIncrementUid("autoincrement_set", "seaportTime0")
	initAutoIncrementUid("autoincrement_set", "seaportTime1")
	initAutoIncrementUid("autoincrement_set", "seaportTime2")

	redisproxy:hsetnx("adv_season", "idx", 0)
	redisproxy:hsetnx("adv_season", "chapter", globalCsv.adv_endless_default_chapter)
	redisproxy:hsetnx("adv_season", "overTime", 0)
end

-- 初始化服务器数据库以及服务器信息表
local function initServerDatabase()
	local servId = skynet.getenv("servId")
	mysqlproxy:query(string.format("CREATE DATABASE IF NOT EXISTS server_%s DEFAULT CHARSET = utf8mb4 COLLATE utf8mb4_general_ci;", servId))
	mysqlproxy:query(string.format("use server_%s", servId))

	-- 服务器信息表 开服时间
	mysqlproxy:query [[
		CREATE TABLE IF NOT EXISTS `server_info` (
			`key` varchar(45) NOT NULL,
			`int_value` int(11) DEFAULT NULL,
			`str_value` varchar(128) DEFAULT NULL,
			PRIMARY KEY (`key`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
	]]

	local res = mysqlproxy:query("SELECT * FROM `server_info` where `key` = 'server_start';")
	if not next(res) then
		mysqlproxy:query(string.format("INSERT INTO `server_info`(`key`, `str_value`) VALUES('server_start', '%s');",
			os.date("%Y%m%d", skynet.timex())))
	end
end

local function initAutoIncreUidTable()
	mysqlproxy:query [[
		CREATE TABLE IF NOT EXISTS `autoincrement_set` (
			`key` varchar(45) NOT NULL,
			`value` int(11) DEFAULT NULL,
			PRIMARY KEY (`key`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
	]]
	local servId = tonumber(skynet.getenv("servId"))
	if servId then
		local tpl = "INSERT INTO `autoincrement_set`(`key`, `value`) values('%s', %d)"
		mysqlproxy:query(string.format(tpl, "role", servId * MAX_ROLE_NUM))
		mysqlproxy:query(string.format(tpl, "union", servId * MAX_ROLE_NUM))
		mysqlproxy:query(string.format(tpl, "order", 0))
		mysqlproxy:query(string.format(tpl, "email", 0))
		mysqlproxy:query(string.format(tpl, "emailTimestamp", 0))
		mysqlproxy:query(string.format(tpl, "delay_email", 0))
		mysqlproxy:query(string.format(tpl, "stopcreate", 0))
		mysqlproxy:query(string.format(tpl, "maintain", 0))
		mysqlproxy:query(string.format(tpl, "seaportTime0", 0))
		mysqlproxy:query(string.format(tpl, "seaportTime1", 0))
		mysqlproxy:query(string.format(tpl, "seaportTime2", 0))

        -- 记录当前更新版本
		mysqlproxy:query(string.format(tpl, "db_ver", 0))
	end
end

local function initSeaportTable()
	-- 海港贸易任务
	mysqlproxy:query [[
		CREATE TABLE IF NOT EXISTS `seaport_task_1` (
			`id` int NOT NULL,
			`value` int(11) DEFAULT 0,
			PRIMARY KEY (`id`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
	]]
	mysqlproxy:query [[
		CREATE TABLE IF NOT EXISTS `seaport_task_2` (
			`id` int NOT NULL,
			`value` int(11) DEFAULT 0,
			PRIMARY KEY (`id`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
	]]
end

local function initAdvSeasonTable()
	--mysqlproxy:query [[
	--	CREATE TABLE IF NOT EXISTS `adv_season` (
	--		`key` varchar(45) NOT NULL,
	--		`value` int(11) DEFAULT NULL,
	--		PRIMARY KEY (`key`)
	--		) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
	--]]
	--local servId = tonumber(skynet.getenv("servId"))
	--if servId then
	--	local tpl = "INSERT INTO `adv_season`(`key`, `value`) values('%s', %d)"

	--	mysqlproxy:query(string.format(tpl, "idx", 0))
	--	mysqlproxy:query(string.format(tpl, "chapter", globalCsv.adv_endless_default_chapter))
	--	mysqlproxy:query(string.format(tpl, "overTime", 0))
	--end
end

local function checkRoleTables()
	local list = {"Role", "Daily", "Activity", "Diner", "Store", "Hero", "Rune", "Order", "Email", "Friend", "Spark"}
	for _, name in ipairs(list) do
		local obj = require("models."..name).new({key = "key"})
		print("check table [" .. name .. "] begin.")
		obj:checkTableSchema()
		print("check table [" .. name .. "] end.")
	end
end

local function createMysqlSp()
	mysqlproxy:query "DROP PROCEDURE IF EXISTS `add_friends`"
	mysqlproxy:query [[
		CREATE PROCEDURE `add_friends`(IN role_id bigint, IN friend_id bigint, IN add_time int)
		BEGIN
			DECLARE t_error INTEGER DEFAULT 0;    
			DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;    
    
			START TRANSACTION;    
				INSERT INTO `Friend`(`roleid`,`fid`,`addTime`) VALUES(role_id, friend_id, add_time);       
				INSERT INTO `Friend`(`roleid`,`fid`,`addTime`) VALUES(friend_id, role_id, add_time);       
    
			IF t_error = 1 THEN    
				ROLLBACK;    
			ELSE    
				COMMIT;    
			END IF;    
			select t_error; 
		END
	]]

	mysqlproxy:query "DROP PROCEDURE IF EXISTS `del_friends`"
	mysqlproxy:query [[
		CREATE PROCEDURE `del_friends`(IN role_id bigint, IN friend_id bigint)
		BEGIN
			DECLARE t_error INTEGER DEFAULT 0;    
			DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;    
    
			START TRANSACTION;    
				DELETE FROM `Friend` WHERE `roleid` = role_id AND `fid` = friend_id;       
				DELETE FROM `Friend` WHERE `roleid` = friend_id AND `fid` = role_id;       
    
			IF t_error = 1 THEN    
				ROLLBACK;    
			ELSE    
				COMMIT;    
			END IF;    
			select t_error; 
		END
	]]
end


local steps = {
	[1] = {
		handler = initServerDatabase,
		desc = "initialize server database "
	},
	[2] = {
		handler = initAutoIncreUidTable,
		desc = "initialize auto_increment_uid table "
	},
	[3] = {
		handler = initAdvSeasonTable,
		desc = "initialize adv_season table "
	},
	[4] = {
		handler = checkRoleTables,
		desc = "check role tables "
	},
	[5] = {
		handler = createMysqlSp,
		desc = "create mysql store procedure "
	},
	[6] = {
		handler = initSeaportTable,
		desc = "initialize seaport table "
	},
}

local function loadAllUserInfo()
	local maxId = 0
	local sql = "SELECT `id`, `uid`, `name` FROM `Role` WHERE `id` > %d ORDER BY `id` LIMIT 1000;"
	while true do
		local res = mysqlproxy:query(string.format(sql, maxId))
		if not next(res) then
			return
		else
			for _, info in ipairs(res) do
				if info["id"] > maxId then
					maxId = info["id"]
				end
				redisproxy:pipelining(function (red)
					local dbName = string.upper(info["name"])
					red:set(string.format("user:%s", dbName), info["id"])
					red:set(string.format("uid:%s", info["uid"]), dbName)
				end)
			end
		end
	end
end

local function selectDb()
	local work_count = tonumber(skynet.getenv("thread"))
	for i = 1, work_count do
		local mysqld = skynet.localname(".mysql" .. i)

		local servId = skynet.getenv("servId")
		local ok, result = pcall(skynet.call, mysqld, "lua", "query", string.format("use server_%s", servId))
		if not ok then
			skynet.error("select db error", "\n", debug.traceback(coroutine.running(), nil))
			return
		end
	end
end

local function doUpdateSql()
    local updates = {
        [1] = {
            "ALTER TABLE `Email` ADD INDEX roleid_createtime_id(`roleId`,`createtime`,`id`),DROP INDEX roleId_Index",
            "ALTER TABLE `Role` ADD INDEX uid_Index(`uid`)",
        },
        [2] = {
            "ALTER TABLE `Rune` MODIFY COLUMN `refer` bigint;"
        },
        [3] = {
            "ALTER TABLE `Email` MODIFY COLUMN `content` VARCHAR(2048);"
        },
    }
	-- 更新记录表
    local res = mysqlproxy:query("SELECT `value` FROM `autoincrement_set` WHERE `key` = 'db_ver';")
    local ver = res[1].value
    for k, sqls in ipairs(updates) do
        if ver < k then
            skynet.error("do update sql, version:"..k)
            for _, sql in ipairs(sqls) do
                local r = mysqlproxy:query(sql)
                if r["errno"] then
                    skynet.error(string.format("%s, err:%s",sql, r["err"]))
                    return
                end
            end
            mysqlproxy:query(string.format("UPDATE `autoincrement_set` SET `value` = %d WHERE `key` = 'db_ver';", k))
        end
    end
end

skynet.start(function ()
	--local new = redisproxy:hsetnx("autoincrement_set", "server_start", os.date("%Y%m%d", skynet.timex())) == 1
	--if not new then
	--	print("server has been initialized...")
	--	skynet.exit()
	--	return
	--end
	csvdb = require "shared.csvdata"
	globalCsv = csvdb["GlobalDefineCsv"]

	for _, action in ipairs(steps) do
		print(action.desc .. "start ...")
		action.handler()
		print(action.desc .. "finished ...")
	end
	initRedisDb()
	initSeaportTask()		-- 海港任务数据初始化
	loadAllUserInfo()
    doUpdateSql()       -- 执行更新sql
	selectDb()
	skynet.exit()
end)