local mysql = require "resty.mysql"

-- 数据库配置
local st = require "settings"

local _M = { _version = "0.1" }

local mt = { __index = _M }


function _M.new(self, o)
    o = o or { _database = st.mysql.database }
    setmetatable(o, mt)
    return o
end

-- 获取数据库连接
function _M.get_conn(self, conn_info)
    conn_info = conn_info or {}
    local db, err = mysql:new()
    if not db then
        ngx.log(ngx.ERR, "failed to instantiate mysql: " .. err)
        return nil
    end
    -- 默认超时3000ms
    local timeout = conn_info.timeout or 3000
    db:set_timeout(timeout)
    local mysql_st = st.mysql
    local database = conn_info.database or self._database
    local host = mysql_st.host
    local port = mysql_st.port
    local user = mysql_st.user
    local password = mysql_st.password
    local ok, err, errcode, sqlstate = db:connect{
        host = host,
        port = port,
        database = database,
        user = user,
        password = password,
    }
    if not ok then
        ngx.log(ngx.ERR, "failed to connect: " .. err .. ": " .. errcode .. " " .. sqlstate)
        return nil
    end
    if db:get_reused_times() == 0 then
        -- 第一次使用，设置编码utf8，避免获取到的中文数据出现乱码
        db:query("SET NAMES utf8")
    end
    return db
end


-- 插入封装
-- table_name: 数据库表
-- item: 插入的内容
function _M.insert(self, table_name, item)
    local fields_item = {}
    local values_item = {}
    local index = 1
    for field, value in pairs(item) do
        fields_item[index] = "`" .. field .. "`"
        values_item[index] = ngx.quote_sql_str(value)
        index = index + 1
    end
    local sql = string.format("INSERT INTO %s (%s) VALUES (%s)", table_name,
                              table.concat(fields_item, ","),
                              table.concat(values_item, ","))
    local db = self:get_conn()
    local res, err, errcode, sqlstate = db:query(sql)
    if not res then
        ngx.log(ngx.ERR, "bad result: " .. err .. ": " .. errcode .. ": " .. sqlstate)
        return nil, err, errcode, sqlstate
    end
    db:set_keepalive(10000, 100)
    return res
end


-- 查询封装
-- table_name: 数据库表
-- query_table: 查询内容
function _M.find(self, table_name, query_table)
    local columns = query_table.columns or "*"
    local conditions = query_table.conditions or ""
    local bind = query_table.bind or {}
    local order = query_table.order or ""
    local limit = tonumber(query_table.limit) or -1
    local offset = tonumber(query_table.offset) or -1

    for k, v in pairs(bind) do
        local regex = ":" .. k .. ":"
        conditions, n, err = ngx.re.sub(conditions, regex, ngx.quote_sql_str(v))
        if not conditions then
            ngx.log(ngx.ERR, err)
            return nil
        end
    end

    local sql = string.format("SELECT %s FROM %s", columns, table_name)

    if #conditions ~= 0 then
        sql = string.format("%s WHERE %s", sql, conditions)
    end

    if #order ~= 0 then
        sql = string.format("%s %s", sql, order)
    end

    if offset > 0 then
        sql = string.format("%s OFFSET %s", sql, offset)
    end

    if limit > 0 then
        sql = string.format("%s LIMIT %s", sql, limit)
    end

    local db = self:get_conn()
    local res, err, errcode, sqlstate = db:query(sql)
    if not res then
        ngx.log(ngx.ERR, "bad result: " .. err .. ": " .. errcode .. ": " .. sqlstate)
        return nil, err, errcode, sqlstate
    end
    db:set_keepalive(10000, 100)
    return res
end