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
  
  -- startCache 和 endCache 在恰当的时候*配对使用*  嵌套使用多次增加引用计数 直到引用计数为0 写入
  function ModelBaseMysql:startCache( ... )
      for _, field in ipairs({ ... }) do
          if self.class.schema[field] then
              self.cacheFields[field] = (self.cacheFields[field] or 0) + 1
          end
      end
  end
  
  --减少缓存引用计数 为时写入, 无参数 强制刷新所有缓存
  function ModelBaseMysql:endCache( ... )
      local args = { ... }
      local params = {}
  
      local function doOneCache(field)
          local propname = field .. "_"
          table_insert(params, field)
          if self.class.schema[field][1] == "table" then
              table_insert(params, MsgPack.pack(self[propname]))
          else
              table_insert(params, self[propname])
          end
      end
  
      if not next(args) then
          for field, _ in pairs(self.cacheFields) do
              doOneCache(field)
          end
          self.cacheFields = {}
      else
          for _, field in ipairs(args) do
              if self.cacheFields[field] then
                  self.cacheFields[field] = self.cacheFields[field] - 1
                  if self.cacheFields[field] <= 0 then
                      self.cacheFields[field] = nil
                      doOneCache(field)
                  end
              end
          end
      end
  
      if next(params) then
          redisproxy:hmset(self:getKey(), table_unpack(params))
      end
  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" then
              self.pri_key = k
          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
  
      if not properties then
          print(string_format("SELECT * from %s where `%s` = %s;", self.class.__cname, self.pri_key, self:getKey()))
          properties = mysqlproxy:query(string_format("SELECT * from %s where `%s` = %s;", self.class.__cname, self.pri_key, self:getKey()))
      end
      if not next(properties) then return false end
  
      self:loadProperties(properties[1])
  
      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
                  params[fieldName] = "'" .. MsgPack.pack(self[propname]) .. "'"
              end
          elseif self.class.schema[fieldName][1] == "string" then
              params[fieldName] = "'" .. self[propname] .. "'"
          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)
          mysqlproxy:query(sql)
      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)
      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)))
      self[propname] = value
  
      if not self.cacheFields[property] then
          -- table 使用msgpack
          if typ == "table" then
              value = MsgPack.pack(value)
          end
          --redisproxy:hset(self:getKey(), property, value)
          self:save()
      end
  end
  
  function ModelBaseMysql:setProperties(fields)
      local result = {}
      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)))
              self[propname] = value
  
              if not self.cacheFields[property] then
                  table_insert(result, property)
                  if typ == "table" then
                      table_insert(result, MsgPack.pack(self[propname]))
                  else
                      table_insert(result, self[propname])
                  end
              end
          end
      end
      if next(result) then
          --redisproxy:hmset(self:getKey(), table_unpack(result))
          self:save()
      end
  end
  
  function ModelBaseMysql:incrProperty(property, value)
      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" then return end
      if typ == "number" then value = tonumber(value) 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)))
      self[propname] = self[propname] + value
  
      if not self.cacheFields[property] then
          --return redisproxy:hincrby(self:getKey(), property, value)
          self:save()
      end
  end
  
  function ModelBaseMysql:onLoad()
  end
  
  function ModelBaseMysql:onCreate()
  end
  
  function ModelBaseMysql:checkTableSchema()
      -- 1.检测是否表存在
      local typeMap = {
          number = {"int", 0, 10},
          string = {"varchar", "", 128},
          table = {"blob", "NULL"}
      }
      local tbName = self.class.__cname
      local create_sql = [[
  		CREATE TABLE IF NOT EXISTS `%s` (
              %s
  			PRIMARY KEY (`%s`)
  			) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
      ]]
      local alter_sql = [[
  		ALTER TABLE `%s` ADD COLUMN (
              %s
  		) ;
      ]]
      local field_tpl_str = "`%s` %s%s DEFAULT %s"
      local field_str = ""
  
      local res = mysqlproxy:query("desc ".. tbName .. ";")
      if res["err"] then -- 表不存在
          local schema = {}
          for k, v in pairs(self.class.schema) do
              local keyType = v[3]
              if keyType == "pri" then
                  self.pri_key = k
                  table_insert(schema, 1, {k, v})
              else
                  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)
              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
  
              field_str = field_str .. string.format(field_tpl_str..",", k, fieldType, suffix, def)
          end
  
          assert(self.pri_key, string_format("table not include primary key, [%s]", tbName))
          -- 创建表格
          mysqlproxy:query(string_format(create_sql, tbName, field_str, self.pri_key))
      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 keyType == "pri" then
                  self.pri_key = k
              end
              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
  
  return ModelBaseMysql