local ModelBaseMysql = class("ModelBaseMysql")
  ModelBaseMysql.key = "key"
  ModelBaseMysql.schema = {}
  
  local string_format = string.format
  local table_insert = table.insert
  local table_unpack = table.unpack
  local assert = assert
  local next = next
  local ipairs = ipairs
  local pairs = pairs
  local tostring = tostring
  local tonumber = tonumber
  local mysqlproxy = mysqlproxy
  
  local function filterProperties(properties, filter)
      for i, field in ipairs(filter) do
          properties[field] = nil
      end
  end
  
  function ModelBaseMysql:ctor(properties)
      self.cacheFields = {} --缓存字段 不更新数据库的字段
  
      self[self.class.key .. "_"] = properties[self.class.key] --数据库key
      properties[self.class.key] = nil
  
      if not self:isValidKey() then
          print(string_format("%s [%s:key] should be give in new(ctor)", tostring(self), self.class.__cname))
          return
      end
  
      if type(properties) ~= "table" then properties = {} end
      self:loadProperties(properties)  --缺少的域将设置默认值
      self:getPriKey()
  end
  

  --[[--
  
  返回对象的 ID 值。
  
  **Returns:**
  
  -   ID 值
  
  ]]
  function ModelBaseMysql:getKey()
      local id = self[self.class.key .. "_"]
      assert(id ~= nil, string_format("%s [%s:getKey()] Invalid key", tostring(self), self.class.__cname))
      return id
  end
  
  function ModelBaseMysql:getPriKey()
      for k, v in pairs(self.class.schema) do
          local objType, def, keyType, length = table_unpack(v)

          if keyType == "pri" or keyType == "pri_auto" then

              self.pri_key = k

              break

          end
      end
  end
  
  function ModelBaseMysql:load(properties)
      if not self:isValidKey() then
          print(string_format("%s [%s:id] should be set before load", tostring(self), self.class.__cname))
          return false
      end

      local load = false

      if not properties then

          properties = mysqlproxy:query(string_format("SELECT * from `%s` where `%s` = %s;", self.class.__cname, self.pri_key, self:getKey()))

          load = true

      end
      if not next(properties) then return false end
  

      local data = load and properties[1] or properties
  
      self:loadProperties(data)

  
      self:onLoad()
  
      return true
  end
  
  --创建model对应的redis数据, 必须已经设置了ID
  function ModelBaseMysql:create()
      if not self:isValidKey() then
          print(string_format("%s [%s:key] should be set before create", tostring(self), self.class.__cname))
          return nil
      end
  
      self:save()
      self:onCreate()
  
      return self
  end
  
  -- save 忽略 缓存配置
  function ModelBaseMysql:save()
      local redisProperties = self:getProperties()
  
      local params = {}
      for fieldName, value in pairs(redisProperties) do
          local propname = fieldName .. "_"
          if self.class.schema[fieldName][1] == "table" then
              if not next(self[propname]) then 
                  params[fieldName] = "NULL"
              else

                  local result = mysqlproxy:quote_sql_str(MsgPack.pack(self[propname]))
                  --params[fieldName] = "'" .. MsgPack.pack(self[propname]) .. "'"
                  params[fieldName] = result

              end
          elseif self.class.schema[fieldName][1] == "string" then

              local result = mysqlproxy:quote_sql_str(self[propname])
              --params[fieldName] = "'" .. self[propname] .. "'"
              params[fieldName] = result

          else
              params[fieldName] = self[propname]
          end
      end
      if next(params) then
          -- insert update

          local sql = "INSERT INTO `%s` (%s) VALUES (%s) ON DUPLICATE KEY UPDATE %s;"

          local tbName = self.class.__cname
          local key_list = ""
          local value_list = ""
          local update_list = ""
          for k, v in pairs(params) do
              if key_list ~= "" then
                 key_list = key_list .. ","
              end
              if value_list ~= "" then
                 value_list = value_list .. ","
              end
              if update_list ~= "" then
                 update_list = update_list .. ","
              end
  

              key_list = key_list .. "`" .. k .. "`"

              value_list = value_list .. v

              update_list = update_list .. "`" .. k .. "`=" .. v

          end
          sql = string_format(sql, tbName, key_list, value_list, update_list)

          local res = mysqlproxy:query(sql)

          if res["errno"] then
              skynet.error(sql)

              skynet.error(res["err"])

          end

      end
  end
  
  --[[--
  
  确定对象是否设置了有效的 key。
  
  ]]
  function ModelBaseMysql:isValidKey()
      local propname = self.class.key .. "_"
      local key = self[propname]
      return type(key) == "string" and key ~= ""
  end
  
  --[[--
  
  加载对象的属性进内存。
  NOTE: 如果properties缺少schema中的域, 将用默认值来填充
  
  **Parameters:**
  
  -   properties: 包含属性值的数组
  
  ]]
  function ModelBaseMysql:loadProperties(properties)
      assert(type(properties) == "table", "Invalid properties")
      for field, schema in pairs(self.class.schema) do
          local typ, def = table_unpack(schema)
          local propname = field .. "_"
  
          if typ == "table" and type(properties[field]) == "string" then
              properties[field] = MsgPack.unpack(properties[field])
          end
  
          local val = properties[field] or def
          if val ~= nil then
              if typ == "number" then val = tonumber(val) end
              assert(type(val) == typ,
                 string_format("%s [%s:loadProperties()] Type mismatch, %s expected %s, actual is %s",
                                   tostring(self), self.class.__cname, field, typ, type(val)))
              self[propname] = val
          end
      end
  end
  
  --[[--
  
  取得对象的属性值。
  
  **Parameters:**
  
  -   fields: 要取得哪些属性的值，如果未指定该参数，则返回 fields 中设定的属性
  -   filter: 要从结果中过滤掉哪些属性，如果未指定则不过滤
  
  **Returns:**
  
  -   包含属性值的数组
  
  ]]
  function ModelBaseMysql:getProperties(fields, filter)
      local schema = self.class.schema
      if type(fields) ~= "table" then fields = table.keys(self.class.schema) end
  
      local properties = {}
      for i, field in ipairs(fields) do
          local propname = field .. "_"
          local typ = schema[field][1]
          local val = self[propname]
          assert(type(val) == typ,
                 string_format("%s [%s:getProperties()] Type mismatch, %s expected %s, actual is %s",
                                   tostring(self), self.class.__cname, field, typ, type(val)))
          properties[field] = val
      end
  
      if type(filter) == "table" then
          filterProperties(properties, filter)
      end
  
      return properties
  end
  
  function ModelBaseMysql:getProperty(property)
      if type(property) ~= "string" then return nil end
      if not self.class.schema[property] then return nil end
      return self:getProperties({property})[property]
  end
  

  function ModelBaseMysql:setProperty(property, value, forceSave)

      if not self.class.schema[property] then
          print(string_format("%s [%s:setProperty()] Invalid property : %s",
              tostring(self), self.class.__cname, property))
          return
      end
  
      local typ, def = table_unpack(self.class.schema[property])
      local propname = property .. "_"
  
      if typ == "number" then value = tonumber(value) end
      if typ == "table" and not value then
          value = self[propname] -- table 可以用自己的缓冲
      end
      assert(type(value) == typ,
         string_format("%s [%s:setProperties()] Type mismatch, %s expected %s, actual is %s",
           tostring(self), self.class.__cname, property, typ, type(value)))

      if typ == "number" or typ == "string" then
          if self[propname] == value then
              return
          end
      end
      self[propname] = value
      --self:save()
      self.cacheFields[property] = self[propname]
      if forceSave then
          self:update()
      end

  end
  

  function ModelBaseMysql:setProperties(fields, forceSave)

      for property, value in pairs(fields) do
           if not self.class.schema[property] then
              print(string_format("%s [%s:setProperty()] Invalid property : %s",
                  tostring(self), self.class.__cname, property))
          else
              local typ, def = table_unpack(self.class.schema[property])
              local propname = property .. "_"
              if typ == "number" then value = tonumber(value) end
              if typ == "table" and not value then
                  value = self[propname] -- table 可以用自己的缓冲
              end
              assert(type(value) == typ,
                 string_format("%s [%s:setProperties()] Type mismatch, %s expected %s, actual is %s",
                   tostring(self), self.class.__cname, property, typ, type(value)))

              if typ == "number" or typ == "string" then

                  if self[propname] ~= value then
                      self[propname] = value
                      self.cacheFields[property] = self[propname]

                  end

              end

          end
      end

      if forceSave then
          self:update()
      end
      --self:save()

  end
  

  function ModelBaseMysql:incrProperty(property, value, forceSave)

      if not self.class.schema[property] then
          print(string_format("%s [%s:setProperty()] Invalid property : %s",
              tostring(self), self.class.__cname, property))
          return
      end
  
      local typ, def = table_unpack(self.class.schema[property])
      local propname = property .. "_"
  

      if typ == "table" or typ == "string" then return end

      if typ == "number" then value = tonumber(value) end
  

      self:setProperty(property, self[propname] + value, forceSave)

  end
  
  function ModelBaseMysql:onLoad()
  end
  
  function ModelBaseMysql:onCreate()
  end
  

  function ModelBaseMysql:checkKeyExists(key)
      local res = mysqlproxy:query(string_format("SELECT * FROM `%s` WHERE `%s` = %s", self.class.__cname, self.pri_key, key))
      return next(res)
  end
  

  function ModelBaseMysql:checkTableSchema()
      -- 1.检测是否表存在
      local typeMap = {

          number = {"int", 0},

          string = {"varchar", "", 128},

          table = {"blob", "NULL"},
          pri = {"bigint", 0},

      }
      local tbName = self.class.__cname
      local create_sql = [[
  		CREATE TABLE IF NOT EXISTS `%s` (
              %s

              PRIMARY KEY (`%s`)%s

  			) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
      ]]

      local index_tpl_str = [[,INDEX `%s_Index` (`%s`)]]
      

      local alter_sql = [[
  		ALTER TABLE `%s` ADD COLUMN (
              %s
  		) ;
      ]]
      local field_tpl_str = "`%s` %s%s DEFAULT %s"

      local auto_increment_str = "`%s` %s NOT NULL AUTO_INCREMENT"

      local field_str = ""
  

      local res = mysqlproxy:query("desc `".. tbName .. "`;")

      local keyList = {}

      if res["err"] then -- 表不存在
          local schema = {}
          for k, v in pairs(self.class.schema) do
              local keyType = v[3]

              if keyType == "pri" or keyType == "pri_auto" then

                  self.pri_key = k
                  table_insert(schema, 1, {k, v})
              else

                  if keyType == "index" then
                      table_insert(keyList, k)
                  end

                  table_insert(schema, {k, v})
              end
          end
          for _, tbl in ipairs(schema) do
              local k, v = tbl[1], tbl[2]
              local objType, def, keyType, length = table_unpack(v)

              local isAutoPriKey = false

              assert(typeMap[objType], string_format("schema invalid type, %s, %s", tbName, k))

              -- 主键使用bigint存储
              if keyType == "pri" then objType = "pri" end

              if keyType == "pri" or keyType == "pri_auto" then
                  objType = "pri"
                  if keyType == "pri_auto" then
                      isAutoPriKey = true
                  end
              end

  
              local info = typeMap[objType]
              local suffix = ""
              local fieldType = info[1]
              if objType == "table" or not def or def == "" then def = info[2] end
              if type(def) == "string" and def ~= "NULL" then def = "'" .. def .. "'" end
              if info[3] and not length then length = info[3] end
              -- 设置字段长度
              if info[3] then suffix = string.format("(%d)", length) end
              -- 很长的string使用blob
              if  keyType == "blob" then 
                  fieldType = keyType
                  suffix = ""
                  def = "NULL"
              end
  

              if not isAutoPriKey then
                  field_str = field_str .. string.format(field_tpl_str..",", k, fieldType, suffix, def)
              else
                  field_str = field_str .. string.format(auto_increment_str..",", k, fieldType)
              end

          end
  
          assert(self.pri_key, string_format("table not include primary key, [%s]", tbName))

          local index_key_str = ""
          for _, k in ipairs(keyList) do
              index_key_str = index_key_str .. string_format(index_tpl_str, k, k)
          end

          -- 创建表格

          print(string_format(create_sql, tbName, field_str, self.pri_key, index_key_str))

          mysqlproxy:query(string_format(create_sql, tbName, field_str, self.pri_key, index_key_str))

      else    -- 检测是否有添加新字段
          local addCol = {}
          local curCols = {}
          for _, col in ipairs(res) do
              curCols[col["Field"]] = 1
          end
          for k, v in pairs(self.class.schema) do
              local objType, def, keyType, length = table_unpack(v)

              if not curCols[k] then
                  print(string_format("table [%s] add new column [%s]", tbName, k))
                  assert(typeMap[objType], string_format("schema invalid type, [%s], [%s]", tbName, k))
  
                  local info = typeMap[objType]
                  local suffix = ""
                  local fieldType = info[1]
                  if objType == "table" or not def or def == "" then def = info[2] end
                  if type(def) == "string" and def ~= "NULL" then def = "'" .. def .. "'" end
                  if info[3] and not length then length = info[3] end
                  -- 设置字段长度
                  if info[3] then suffix = string.format("(%d)", length) end
                  -- 很长的string使用blob
                  if  keyType == "blob" then 
                      fieldType = keyType
                      suffix = ""
                      def = "NULL"
                  end
                  local sep = ","
                  if field_str == "" then
                      sep = ""
                  end
                  field_str = field_str .. string.format(sep..field_tpl_str, k, fieldType, suffix, def)
              end
          end
          -- 添加新列
          if field_str ~= "" then
              mysqlproxy:query(string_format(alter_sql, tbName, field_str))
          end
      end
  
  end
  

  function  ModelBaseMysql:loadFields(fields)
      if not self:isValidKey() then
          print(string_format("%s [%s:id] should be set before load", tostring(self), self.class.__cname))
          return
      end
      if not next(fields) then
          return
      end
      local final = {}
      for _, v in ipairs(fields) do
          table.insert(final, '`'..v..'`')
      end
      local field_list = table.concat(final, ",")
      local res = mysqlproxy:query(string_format("SELECT %s from `%s` where `%s` = %s;", field_list, self.class.__cname, self.pri_key, self:getKey()))
      if res["errno"] then
          return
      end
  
      return res[1]
  end
  
  function ModelBaseMysql:update()
      if next(self.cacheFields) then
          self:updateFields(self.cacheFields)
          self.cacheFields = {}
      end
  end
  
  function ModelBaseMysql:updateFields(fields)
      local params = {}
      for field, value in pairs(fields) do
          if self.class.schema[field][1] == "table" then
              if next(value) then 
                  local result = mysqlproxy:quote_sql_str(MsgPack.pack(value))
                  params[field] = result
              end
          elseif self.class.schema[field][1] == "string" then
              local result = mysqlproxy:quote_sql_str(value)
              params[field] = result
          else
              params[field] = value 
          end
      end
      if next(params) then
          local sql = "UPDATE `%s` SET %s WHERE `%s` = %s;"
          local tbName = self.class.__cname
          local tmp = {}
          for k, v in pairs(params) do
              table.insert(tmp, '`' .. k .. '` = ' .. v)
          end
          sql = string_format(sql, tbName, table.concat(tmp, ","), self.pri_key, self:getKey())
          local res = mysqlproxy:query(sql)
          if res["errno"] then
              skynet.error(sql)

              skynet.error(res["err"])

              return false
          end
      end
      return true
  end
  

  return ModelBaseMysql