--视频标题
local M = {}
local mysqldbx = require "mysqldbx"
local tools = require "tools"
local skynet = require "skynet"
local md5 =	require	"md5"


function M.add_video_title(msg_body)
    local isok ,key =  tools.checkData({"title_list"},msg_body)
    if not isok then
        return false,string.format("缺少字段： %s.", key)
    end
    for i = 1, #msg_body.title_list, 1 do
        local title = msg_body.title_list[i].title
        local md5_tag = msg_body.title_list[i].md5_tag
        local sql = string.format("SELECT * FROM video_titles WHERE md5_tag = '%s' LIMIT 1", md5_tag)
        local isok,res;
        res = mysqldbx.query(sql)
        if #res > 0 then

        else
            sql = string.format("INSERT INTO `video_titles` (title,md5_tag)  VALUES ('%s','%s')",title,md5_tag)
            mysqldbx.query(sql)
        end

    end
    return true, {}
end

function M.delete_video_title(msg_body)
    local isok ,key =  tools.checkData({"id_list"},msg_body)
    if not isok then
        return false,string.format("缺少字段： %s.", key)
    end
    for i = 1, #msg_body.id_list, 1 do
        local id = msg_body.id_list[i]
        local sql = string.format("DELETE FROM video_titles WHERE id = %d ",id)
        mysqldbx.query(sql)
    end
    return true, {}
end

function M.modify_video_title(msg_body)
    local isok ,key =  tools.checkData({"title","md5_tag","id"},msg_body)
    if not isok then
        return false,string.format("缺少字段： %s.", key)
    end
    local sql = string.format("UPDATE  video_titles SET title = '%s' , md5_tag = '%s' WHERE id = %d ",msg_body.title,msg_body.md5_tag,msg_body.id)
    mysqldbx.query(sql)
    return true, {}
end

function M.search_video_title(msg_body)
    local isok ,key =  tools.checkData({"page_size","page_number","content"},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 sql = "SELECT * FROM video_titles "..string.format(" WHERE  (title LIKE CONCAT( '%%%s%%')) ",msg_body.content)..string.format(" LIMIT %d OFFSET %d ",page_size, offset)
    local list = mysqldbx.query(sql)
    sql = "SELECT   COUNT(*) AS total  FROM video_titles "..string.format(" WHERE  (title LIKE CONCAT( '%%%s%%')) ",msg_body.content)
    local total = mysqldbx.query(sql)
    return true,list,total[1].total
end

function M.initAll()
    local sql = "SELECT * FROM video_titles "
    local list = mysqldbx.query(sql)
    for i = 1, #list, 1 do
        if (list[i].md5_tag==nil) then
            local id = list[i].id
            local md5_tag =md5.sumhexa(list[i].title)
            skynet.error("md5_tag:",md5_tag)
            sql = string.format("UPDATE  video_titles SET md5_tag = '%s' WHERE id = %d ",md5_tag,id)
            mysqldbx.query(sql)
        end
    end
    return true,{}
end
return M