<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.api.app.dao.community.AppGambitDao">
    <select id="list" parameterType="java.lang.Integer" resultType="com.api.vo.app.AppGambitThemeVo">
        select a.* from (
            select sgt.id,sgt.gambit_id,sgt.content,sgt.likes,sgt.is_comment,sgt.is_public,
                   sgt.create_id,sgt.create_date,sgt.modify_id,sgt.modify_date,sgt.is_delete,
                   IFNULL(sgt.views,0) as views,sg.summary as gambit_title,ur.nick_name as create_name
            from user_resident as ur,sys_gambit_theme as sgt
            LEFT JOIN sys_gambit as sg on sgt.gambit_id = sg.id and sg.is_delete = 1
            where ur.id = sgt.create_id and sgt.is_delete = 1
        ) as a where a.is_public = 1 or a.create_id = #{id}
        order by a.create_date DESC
    </select>

    <select id="findCommentByThemeId" parameterType="java.lang.Integer" resultType="com.api.vo.app.AppGambitThemeCommentVo">
        select sgtc1.*,ur1.nick_name as create_name,ur2.nick_name as parent_name
        from user_resident as ur1 , sys_gambit_theme_comment as sgtc1
        LEFT JOIN sys_gambit_theme_comment as sgtc2 on sgtc1.parent_id = sgtc2.id
        LEFT JOIN user_resident as ur2 on sgtc2.create_id = ur2.id
        where sgtc1.gambit_theme_id = #{ThemeId} and sgtc1.create_id = ur1.id and sgtc1.is_delete = 1
        order by sgtc1.create_date DESC
    </select>

    <select id="listGambit" resultType="com.api.vo.app.AppGambitVo">
        select sg2.*,(case c.activity_num is null when true then 0 else c.activity_num end) as activity_num from sys_gambit as sg2 LEFT JOIN (
            select a.id,(a.sum2+b.count2) as activity_num from (
                select sg.id,(case SUM(sgt.likes) is null when true then 0 else SUM(sgt.likes) end) as sum2 from sys_gambit_theme as sgt,sys_gambit as sg where sgt.gambit_id = sg.id and sgt.is_delete = 1 group by sg.id
            ) as a LEFT JOIN (
                select sg.id,(case count(*) is null when true then 0 else count(*) end) as count2 from sys_gambit_theme_comment as sgtc,sys_gambit as sg where sgtc.gambit_id = sg.id  and sgtc.is_delete = 1  group by sg.id
            ) as b on a.id = b.id
        ) as c on c.id = sg2.id where sg2.is_public = 1 and sg2.is_delete = 1 and sg2.is_rating = 1 ORDER BY c.activity_num DESC
    </select>

    <select id="sumLikeNum" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select SUM(likes) from sys_gambit_theme where gambit_id = #{id} and is_delete = 1 group by id
    </select>

    <select id="sumThemeNum" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select count(*) from sys_gambit_theme where gambit_id = #{id} and is_delete = 1
    </select>


    <select id="sumCommentNum" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select count(*) from sys_gambit_theme_comment where gambit_id = #{gambitId} and is_delete = 1
    </select>

    <select id="myTidings" parameterType="java.lang.Integer" resultType="com.api.vo.app.AppMyTidingsVo">
        select * from sys_gambit_theme where create_id = #{id} and is_delete =1 order by create_date DESC
    </select>

    <select id="findLikeNames" parameterType="java.lang.Integer" resultType="com.api.vo.app.IdAndName">
        select ur.id,ur.nick_name as name from sys_gambit_theme_like as sgtl,user_resident as ur
        where sgtl.create_id = ur.id and  gambit_theme_id = #{id}
    </select>

    <select id="GambitThemeDetail" parameterType="java.lang.Integer" resultType="com.api.vo.app.AppGambitThemeVo">
        select sgt.id,sgt.gambit_id,sgt.content,sgt.likes,sgt.is_comment,sgt.is_public,
               sgt.create_id,sgt.create_date,sgt.modify_id,sgt.modify_date,sgt.is_delete,
               IFNULL(sgt.views,0) as views,sg.summary as gambit_title,ur.nick_name as create_name
        from user_resident as ur,sys_gambit_theme as sgt
        LEFT JOIN sys_gambit as sg on sgt.gambit_id = sg.id and sg.is_delete = 1
        where sgt.create_id = ur.id and sgt.is_delete = 1 and sgt.id = #{themeId}
    </select>

    <select id="findGambitIdByThemeId" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select gambit_id from sys_gambit_theme where id = #{themeId}
    </select>

    <select id="findThemeLikeByIds" parameterType="com.api.model.app.UserIdAndThemeId" resultType="com.api.model.app.AppGambitThemeLike">
        select * from sys_gambit_theme_like where gambit_theme_id = #{themeId} and create_id = #{id}
    </select>

    <insert id="insertThemeLike" parameterType="com.api.model.app.AppGambitThemeLike">
        insert into sys_gambit_theme_like(
            gambit_id,
            gambit_theme_id,
            create_id,
            create_date
        )values (
            #{gambitId},
            #{gambitThemeId},
            #{createId},
            #{createDate}
        )
    </insert>

    <delete id="deleteThemeLike" parameterType="java.lang.Integer">
        delete from sys_gambit_theme_like where id = #{id}
    </delete>

    <update id="incrLikesByTheme" parameterType="java.lang.Integer">
        update sys_gambit_theme set likes = likes + 1 where id = #{themeId}
    </update>

    <update id="decrLikesByTheme" parameterType="java.lang.Integer">
        update sys_gambit_theme set likes = likes - 1 where id = #{themeId}
    </update>

    <update id="falseDelete" parameterType="com.api.model.app.UserIdAndThemeId" >
        update sys_gambit_theme set is_delete = 0 where id = #{themeId} and create_id = #{id}
    </update>

    <insert id="comment" parameterType="com.api.model.app.AppGambitThemeComment">
        insert into sys_gambit_theme_comment(
            parent_id,
            gambit_id,
            gambit_theme_id,
            content,
            create_id,
            create_date,
            is_delete
        )values (
            #{parentId},
            #{gambitId},
            #{gambitThemeId},
            #{content},
            #{createId},
            #{createDate},
            #{isDelete}
        )
    </insert>

    <insert id="writePost" parameterType="com.api.model.app.AppGambitTheme">
        insert into sys_gambit_theme(
            gambit_id,
            content,
            likes,
            is_comment,
            is_public,
            create_id,
            create_date,
            is_delete
        )values (
            #{gambitId},
            #{content},
            #{likes},
            #{isComment},
            #{isPublic},
            #{createId},
            #{createDate},
            #{isDelete}
        )
        <selectKey keyProperty="id" resultType="java.lang.Integer">
            select LAST_INSERT_ID() AS id
        </selectKey>
    </insert>

    <select id="listByGambitId" parameterType="com.api.model.app.UserIdAndGambitId" resultType="com.api.vo.app.AppGambitThemeVo">
        select a.* from (
            select sgt.id,sgt.gambit_id,sgt.content,sgt.likes,sgt.is_comment,sgt.is_public,
                   sgt.create_id,sgt.create_date,sgt.modify_id,sgt.modify_date,sgt.is_delete,
                   IFNULL(sgt.views,0) as views,sg.summary as gambit_title,ur.nick_name as create_name
            from user_resident as ur,sys_gambit_theme as sgt
            LEFT JOIN sys_gambit as sg on sgt.gambit_id = sg.id and sg.is_delete = 1
            where ur.id = sgt.create_id and sgt.is_delete = 1 and sgt.gambit_id = #{gambitId}
            order by sgt.create_date DESC
        ) as a where a.is_public = 1 or a.create_id = #{id}
    </select>

    <select id="listByGambitIdByNews" parameterType="com.api.model.app.UserIdAndGambitId" resultType="com.api.vo.app.AppGambitThemeVo">
        select a.* from (
            select sgt.id,sgt.gambit_id,sgt.content,sgt.likes,sgt.is_comment,sgt.is_public,
                   sgt.create_id,sgt.create_date,sgt.modify_id,sgt.modify_date,sgt.is_delete,
                   IFNULL(sgt.views,0) as views,sg.summary as gambit_title,ur.nick_name as create_name
            from user_resident as ur,sys_gambit_theme as sgt
                                         LEFT JOIN sys_gambit as sg on sgt.gambit_id = sg.id and sg.is_delete = 1
            where ur.id = sgt.create_id and sgt.is_delete = 1 and sgt.gambit_id = #{gambitId}
            order by sgt.create_date DESC
        ) as a where a.is_public = 1 or a.create_id = #{id}
        order by a.create_date desc
    </select>

    <select id="listByGambitIdByHot" parameterType="com.api.model.app.UserIdAndGambitId" resultType="com.api.vo.app.AppGambitThemeVo">
        select a.* from (
                            select sgt.id,sgt.gambit_id,sgt.content,sgt.likes,sgt.is_comment,sgt.is_public,
                                   sgt.create_id,sgt.create_date,sgt.modify_id,sgt.modify_date,sgt.is_delete,
                                   IFNULL(sgt.views,0) as views,sg.summary as gambit_title,ur.nick_name as create_name
                            from user_resident as ur,sys_gambit_theme as sgt
                                                         LEFT JOIN sys_gambit as sg on sgt.gambit_id = sg.id and sg.is_delete = 1
                            where ur.id = sgt.create_id and sgt.is_delete = 1 and sgt.gambit_id = #{gambitId}
                            order by sgt.create_date DESC
                        ) as a where a.is_public = 1 or a.create_id = #{id}
        order by a.views desc
    </select>



    <select id="findActivityGambit" resultType="com.api.vo.app.IdAndName">
        select c.id,sg2.summary as name from (
            select a.id,(a.sum2+b.count2) as activity_num from (
                select sg.id,(case SUM(sgt.likes) is null when true then 0 else SUM(sgt.likes) end) as sum2 from sys_gambit_theme as sgt,sys_gambit as sg where sgt.gambit_id = sg.id and sgt.is_delete = 1 group by sg.id
            ) as a LEFT JOIN (
                select sg.id,(case count(*) is null when true then 0 else count(*) end) as count2 from sys_gambit_theme_comment as sgtc,sys_gambit as sg where sgtc.gambit_id = sg.id  and sgtc.is_delete = 1  group by sg.id
            ) as b on a.id = b.id
        ) as c,sys_gambit as sg2 where c.id = sg2.id ORDER BY c.activity_num DESC LIMIT 4
    </select>

    <select id="findCreateIdByThemeId" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select create_id from sys_gambit_theme where id = #{themeId}
    </select>

    <select id="findCreateIdById" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select create_id from sys_gambit_theme_comment where id = #{gambitThemeId}
    </select>
    <select id="findSwitchById" parameterType="java.lang.Integer" resultType="com.api.model.system.SysFunctionSwitch">
        select * from sys_function_switch where id = #{id}
    </select>


</mapper>