--白名单
local M = {}
local mysqldbx = require "mysqldbx"
local tools = require "tools"
local skynet = require "skynet"
local cjson = require "cjson"
function M.get_all_black_books()
    local sql = "SELECT * FROM whitelist_books "
    local res = mysqldbx.query(sql)
    return true,res
end
function M.add_book(msg_body)
    local isok ,key =  tools.checkData({"product_id","product_name"},msg_body)
    if not isok then
        return false,string.format("缺少字段： %s.", key)
    end
    local sql = string.format("SELECT * FROM whitelist_books WHERE product_id = '%s' LIMIT 1", msg_body.product_id)
    local isok,res;
    res = mysqldbx.query(sql)
    if #res > 0 then
        return false ,"product_id :"..msg_body.product_id.." 已存在！"
    end
    sql = string.format("INSERT INTO `whitelist_books` (product_id,product_name)  VALUES ('%s','%s')",msg_body.product_id,msg_body.product_name)
    mysqldbx.query(sql)
    skynet.send("backmgr","lua","on_recv",nil,"ws_push_msg",cjson.encode({cmd="updateBlackBooks"}))
    return true, {}
end

function M.delete_book(msg_body)
    local isok ,key =  tools.checkData({"product_id"},msg_body)
    if not isok then
        return false,string.format("缺少字段： %s.", key)
    end
    local sql = string.format("DELETE FROM whitelist_books WHERE product_id = '%s' ",msg_body.product_id)
    mysqldbx.query(sql)
    skynet.send("backmgr","lua","on_recv",nil,"ws_push_msg",cjson.encode({cmd="updateBlackBooks"}))
    return true, {}
end

function M.is_black_book(msg_body)
    local isok ,key =  tools.checkData({"product_id"},msg_body)
    if not isok then
        return false,string.format("缺少字段： %s.", key)
    end
    local sql = string.format("SELECT * FROM whitelist_books WHERE product_id = '%s' LIMIT 1", msg_body.product_id)
    local isok,res;
    res = mysqldbx.query(sql)
    return true, {is_black_book = #res > 0}
end

function M.who_at_in_black(msg_body)
    local isok ,key =  tools.checkData({"id_list"},msg_body)
    if not isok then
        return false,string.format("缺少字段： %s.", key)
    end
    local idString = table.concat(msg_body.id_list, ",")
    local sql = string.format("SELECT * FROM whitelist_books WHERE  product_id IN (%s)",idString)
    local isok,res;
    res = mysqldbx.query(sql)
    return true,res
end

function M.search_book_list(msg_body)
    local isok ,key =  tools.checkData({"page_size","page_number","product_id","product_name"},msg_body)
    if not isok then
        return false,string.format("缺少字段： %s.", key)
    end
    local page_size = msg_body.page_size
    local page_number = msg_body.page_number
    local offset = (page_number - 1) * page_size
    local isFirst = false
    local product_param = ""

    if msg_body.product_id~="" then
        isFirst = true
        product_param =string.format(" product_id = '%s' ",msg_body.product_id)
    end
    local product_name_param = ""
    if msg_body.product_name~="" then
        if isFirst ==true then
            product_name_param = string.format("AND product_name = '%s' ",msg_body.product_name)
        else
            isFirst = true
            product_name_param = string.format(" product_name = '%s' ",msg_body.product_name)
        end
    end

    local param = product_param..product_name_param

    if param ~= "" then
        param = " WHERE "..param
    end

    local sql = "SELECT * FROM whitelist_books "..param.."ORDER BY id DESC"..string.format(" LIMIT %d OFFSET %d ",page_size, offset)
   
    local list = mysqldbx.query(sql)

    sql = "SELECT   COUNT(*) AS total  FROM whitelist_books "..param

    local total = mysqldbx.query(sql)

    return true,list,total[1].total
end

return M