local _M = {}
  local tarr2tab = table.array2Table
  
  local function loadEmails(roleId)
  	local emails = {}

  	local res = mysqlproxy:query(string.format("SELECT * FROM `Email` WHERE `roleId` = %s", roleId))
  	for _, data in ipairs(res) do
  		local email = require("models.Email").new({key = string.format("%d",data.id), id=data.id})
  		if email:load(data) then

  			table.insert(emails, email)
  		end
  	end
  	return emails
  end
  

  local function delExpireEmails(roleId)

  	--local sql = [[
  	--	DELETE FROM Email
  	--	WHERE id IN (
  	--		SELECT id FROM (
  	--		  SELECT id
  	--		  FROM Email WHERE roleId=%s
  	--		  ORDER BY createtime DESC, id desc
  	--		  LIMIT %s, 100000
  	--		) a
  	--	);
  	--]]
      local sql = "SELECT id FROM Email WHERE roleId=%s ORDER BY createtime DESC, id desc LIMIT %s, 100000"

  	sql = string.format(sql, roleId, EMAIL_LIMIT)

  	local res = mysqlproxy:query(sql)
      local tmp = {}
      for _, v in ipairs(res) do
          table.insert(tmp, v.id)
      end
      mysqlproxy:query(string.format("DELETE FROM Email WHERE id IN (%s)", table.concat(tmp, ",")))

  end
  

  function _M.listRpc(agent, data)
  	local role = agent.role
  	local roleId = role:getProperty("id")
  	local now = skynet.timex()
  	local result = {}
  	local mid = role:getProperty("sid")
  
  	local globalEmail = redisproxy:hget("autoincrement_set", "email")
  	globalEmail = tonum(globalEmail)
  	local emailSync = role:getProperty("emailSync")
  	if globalEmail > emailSync then
  		role:setProperty("emailSync", globalEmail)
  		emailSync = math.max(emailSync + 1, globalEmail - EMAIL_LIMIT + 1)
  		local result = redisproxy:pipelining(function (red)
  			for id = emailSync, globalEmail do
  				red:hgetall(string.format("globalEmail:%s", id))		
  			end
  		end)
  		local count = 1
  		for _, data in ipairs(result) do
  			local email = tarr2tab(data)

  			-- 0 需要判斷創角時間小於郵件創建時間 1 只需要在時間段內登陸即可領取
  			local delayType = tonum(email.delayType)
  			local flag = false
  			if delayType == 1 then
  				flag = skynet.timex() > tonum(email.createtime)
  			else 
  				flag = tonum(email.createtime) > role:getProperty("ctime")
  			end
  			if flag and ( not email.mid or tonum(email.mid) == mid ) 

  				and ( not email.endtime or tonum(email.endtime) > now )then

  				local time = math.max(tonum(email.timestamp, 0) , tonum(email.createtime))

  				mysqlproxy:insertEmail({

  					roleId = roleId, 
  					emailId = 0,
  					createtime = time,
  					title = email.title, 

  					stitle = email.stitle, 

  					content = email.content, 
  					attachments = email.attachments
  				})

  				role:mylog("mail_action", {desc = "get_global", key1 = email.title, key2 = email.attachments})

  			end
  		end
  	end

  	delExpireEmails(roleId)

  
  	local emails = loadEmails(roleId)
  	for _, email in ipairs(emails) do
  		table.insert(result, email:data())
  	end
  

  	SendPacket(actionCodes.Email_listRpc, MsgPack.pack({list = result}))

  	return true
  end
  
  local function getEmailAttachments( email )
  	if email:getProperty("status") == 2 then
  		return ""
  	end
  	local attachments = email:getProperty("attachments")
  	if attachments:len() == 0 then
  		local rewardPms = email:getProperty("rewardPms")
  		local emailData = csvdb["emailCsv"][email:getProperty("emailId")]
  		if emailData then

  			if next(rewardPms) then
  				attachments = emailData.attachment:format(table.unpack(rewardPms))
  			else
  				attachments = emailData.attachment
  			end

  		end
  	end
  	return attachments
  end
  
  function _M.drawAllAttachRpc(agent, data)
  	local role = agent.role
  	local roleId = role:getProperty("id")
  

  	local reward, change = {}

  	local ids = {}
  
  	local emails = loadEmails(roleId)

  	for _, email in ipairs(emails) do
  		local attachments = getEmailAttachments(email)
  		if attachments ~= "" then

  			email:setProperty("status", 2, true)

  			email:log(role, 2)

  			ids[email:getProperty("id")] = 1

  			for key, v in pairs(attachments:toNumMap()) do
  				reward[key] = (reward[key] or 0) + v

  			end

  			role:mylog("mail_action", {desc = "draw_attach", int1 = email:getProperty("emailId"), key1 = email:getProperty("title"), key2 = attachments})

  		end

  	end

  	if role:checkRuneFullyByReward(reward) then return 1 end
  

  	reward, change = role:award(reward, {log = {desc = "draw_attach"}})
  	SendPacket(actionCodes.Email_drawAllAttachRpc, MsgPack.pack({ids = ids, reward = reward, change = change}))

  	return true
  end
  
  function _M.drawAttachRpc(agent, data)
  	local role = agent.role
  	local roleId = role:getProperty("id")
  	local msg = MsgPack.unpack(data)
  	local id = msg.id
  

  	local email = require("models.Email").new({key = string.format("%d", id), id = id})

  	email.owner = role

  	if not email:load() then return end
  
  	local attachments = getEmailAttachments(email)
  	if attachments == "" then return end
  

  	local reward, change = {}
  	if type(attachments) == "string" then
  		for key, v in pairs(attachments:toNumMap()) do
  			reward[key] = (reward[key] or 0) + v
  		end
  	else
  		reward = attachments
  	end

  	if role:checkRuneFullyByReward(reward) then return 1 end

  	reward, change = role:award(reward, {log = {desc = "draw_attach", int1 = id}})
  

  	email:setProperty("status", 2, true)

  	email:log(role, 2)

  	SendPacket(actionCodes.Email_drawAttachRpc, MsgPack.pack({reward = reward, change = change}))

  	role:mylog("mail_action", {desc = "draw_attach", int1 = email:getProperty("emailId"), key1 = email:getProperty("title"), key2 = attachments})

  	return true
  end
  
  function _M.checkRpc(agent, data)
  	local role = agent.role
  	local roleId = role:getProperty("id")
  
  	local msg = MsgPack.unpack(data)
  	local id = msg.id
  

  	local email = require("models.Email").new({key = string.format("%d", id), id = id})

  	if not email:load() then return end

  	email:setProperty("status", 1, true)

  	email:log(role, 1)

  	role:mylog("mail_action", {desc = "check_mail", int1 = id})

  
  	SendPacket(actionCodes.Email_checkRpc, '')
  	return true
  end
  
  function _M.delRpc(agent, data)
  	local role = agent.role
  	local roleId = role:getProperty("id")

  	local result = {}

  	local tmp = {}
  	local emails = loadEmails(roleId)
  	for _, email in ipairs(emails) do
  		local attachments = getEmailAttachments(email)
  
  		if email:getProperty("status") == 2 or (attachments == "" and email:getProperty("status") == 1) then
  			result[email:getProperty("id")] = 1
  			email:log(role, 3)
  			table.insert(tmp, email:getProperty("id"))

  		end

  	end
  
  	mysqlproxy:query(string.format("DELETE FROM `Email` WHERE `id` in (%s)", table.concat(tmp, ",")))
  

  	for delId, _ in ipairs(result) do
  		role:mylog("mail_action", {desc = "del_mail", int1 = delId})
  	end

  	SendPacket(actionCodes.Email_delRpc, MsgPack.pack({result = result}))
  	return true
  end
  
  return _M