<?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.ruoyi.post.frequency.post.mapper.PostMapper">

    <resultMap type="com.ruoyi.post.frequency.post.entity.Post" id="PostResult">
        <result property="id"    column="id"    />
        <result property="delFlag"    column="del_flag"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="postTypeId"    column="post_type_id"    />
        <result property="userType"    column="user_type"    />
        <result property="userId"    column="user_id"    />
        <result property="title"    column="title"    />
        <result property="content"    column="content"    />
        <result property="coverImage"    column="cover_image"    />
        <result property="videoUrl"    column="video_url"    />
        <result property="shareNum"    column="share_num"    />
        <result property="likeNum"    column="like_num"    />
        <result property="commentNum"    column="comment_num"    />
        <result property="collectNum"    column="collect_num"    />
        <result property="browseNum"    column="browse_num"    />
        <result property="result"    column="result"    />
        <result property="reason"    column="reason"    />
        <result property="isRecommend"    column="isRecommend"    />
        <result property="postTypeName"    column="postTypeName"    />
        <result property="describes"    column="describes"    />
    </resultMap>

    <sql id="selectPostVo">
        SELECT
            t.id,
            t.del_flag,
            t.create_by,
            t.create_time,
            t.update_by,
            t.update_time,
            t.post_type_id,
            t.user_type,
            t.user_id,
            t.title,
            t.content,
            t.cover_image,
            t.video_url,
            t.share_num,
            t.like_num,
            t.comment_num,
            t.collect_num,
            t.browse_num,
            t.answer_num,
            t.result,
            t.reason,
            t.address,
            t2.`name` postTypeName,
            case when r.id is null then 1 else 2 end isRecommend,
            r.sort,
            t.describes
        FROM
            t_post AS t
                LEFT JOIN t_post_type AS t2 ON t.post_type_id = t2.id
                left join t_customer c on c.id = t.user_id and t.user_type = 1
                left join t_store s on s.id = t.user_id and t.user_type = 2
                left join t_recommend r on r.object_id = t.id and r.type = 2 and r.del_flag = 1
                left join t_enterprise e on e.id = t.user_id and t.user_type = 4
                left join t_store s2 on s2.bind_enterprise_id = e.id and t.user_type = 4
    </sql>

    <select id="selectPostList" parameterType="com.ruoyi.post.frequency.post.entity.Post" resultMap="PostResult">
        SELECT * from ( <include refid="selectPostVo"/>
        <where>
            and t.del_flag != 2
            <if test="postTypeId != null ">and t.post_type_id = #{postTypeId}</if>
            <if test="type != null ">and t.type = #{type}</if>
            <if test="userType != null ">and t.user_type = #{userType}</if>
            <if test="userId != null "> and t.user_id = #{userId}</if>
            <if test="title != null  and title != ''"> and t.title like concat('%',#{title},'%')</if>
            <if test="content != null  and content != ''"> and t.content = #{content}</if>
            <if test="coverImage != null  and coverImage != ''"> and t.cover_image = #{coverImage}</if>
            <if test="videoUrl != null  and videoUrl != ''"> and t.video_url = #{videoUrl}</if>
            <if test="shareNum != null "> and t.share_num = #{shareNum}</if>
            <if test="likeNum != null "> and t.like_num = #{likeNum}</if>
            <if test="commentNum != null "> and t.comment_num = #{commentNum}</if>
            <if test="collectNum != null "> and t.collect_num = #{collectNum}</if>
            <if test="browseNum != null ">and t.browse_num = #{browseNum}</if>
            <if test="result != null ">and t.result = #{result}</if>
            <if test="reason != null  and reason != ''">and t.reason = #{reason}</if>
            <if test="userInfo != null and userInfo.nickname!=null and userInfo.nickname!=''">
                AND (
                s.nickname LIKE CONCAT('%',#{userInfo.nickname},'%')
                or
                c.nickname LIKE CONCAT('%',#{userInfo.nickname},'%')
                or
                e.name LIKE CONCAT('%',#{userInfo.nickname},'%')
                )
            </if>
            <if test="userInfo != null and userInfo.mobile!=null and userInfo.mobile!=''">
                AND (
                s.mobile LIKE CONCAT('%',#{userInfo.mobile},'%')
                or
                c.mobile LIKE CONCAT('%',#{userInfo.mobile},'%')
                or
                s2.mobile LIKE CONCAT('%',#{userInfo.mobile},'%')
                )
            </if>
            <if test="beginTime != null ">
                and  t.create_time >= #{beginTime}

            </if>
            <if test="endTime != null ">
                and  t.create_time &lt; #{endTime}
            </if>

            <if test="enterpriseId != null  and enterpriseId != ''">and e.id = #{enterpriseId}</if>
        </where>
        )tt
        order by tt.isRecommend desc, tt.sort,tt.create_time desc
    </select>

    <select id="selectPostById" parameterType="Long" resultMap="PostResult">
        <include refid="selectPostVo"/>
        where t.id = #{id}
    </select>

    <insert id="insertPost" parameterType="com.ruoyi.post.frequency.post.entity.Post">
        insert into t_post
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">id,</if>
            <if test="delFlag != null">del_flag,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="postTypeId != null">post_type_id,</if>
            <if test="userType != null">user_type,</if>
            <if test="userId != null">user_id,</if>
            <if test="title != null">title,</if>
            <if test="content != null">content,</if>
            <if test="coverImage != null">cover_image,</if>
            <if test="videoUrl != null">video_url,</if>
            <if test="shareNum != null">share_num,</if>
            <if test="likeNum != null">like_num,</if>
            <if test="commentNum != null">comment_num,</if>
            <if test="collectNum != null">collect_num,</if>
            <if test="browseNum != null">browse_num,</if>
            <if test="result != null">result,</if>
            <if test="reason != null">reason,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">#{id},</if>
            <if test="delFlag != null">#{delFlag},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="postTypeId != null">#{postTypeId},</if>
            <if test="userType != null">#{userType},</if>
            <if test="userId != null">#{userId},</if>
            <if test="title != null">#{title},</if>
            <if test="content != null">#{content},</if>
            <if test="coverImage != null">#{coverImage},</if>
            <if test="videoUrl != null">#{videoUrl},</if>
            <if test="shareNum != null">#{shareNum},</if>
            <if test="likeNum != null">#{likeNum},</if>
            <if test="commentNum != null">#{commentNum},</if>
            <if test="collectNum != null">#{collectNum},</if>
            <if test="browseNum != null">#{browseNum},</if>
            <if test="result != null">#{result},</if>
            <if test="reason != null">#{reason},</if>
        </trim>
    </insert>

    <update id="updatePost" parameterType="com.ruoyi.post.frequency.post.entity.Post">
        update t_post
        <trim prefix="SET" suffixOverrides=",">
            <if test="delFlag != null">del_flag = #{delFlag},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="postTypeId != null">post_type_id = #{postTypeId},</if>
            <if test="userType != null">user_type = #{userType},</if>
            <if test="userId != null">user_id = #{userId},</if>
            <if test="title != null">title = #{title},</if>
            <if test="content != null">content = #{content},</if>
            <if test="coverImage != null">cover_image = #{coverImage},</if>
            <if test="videoUrl != null">video_url = #{videoUrl},</if>
            <if test="shareNum != null">share_num = share_num + #{shareNum},</if>
            <if test="likeNum != null">like_num = like_num + #{likeNum},</if>
            <if test="commentNum != null">comment_num = comment_num + #{commentNum},</if>
            <if test="collectNum != null">collect_num = collect_num + #{collectNum},</if>
            <if test="browseNum != null">browse_num = browse_num + #{browseNum},</if>
            <if test="result != null">result = #{result},</if>
            <if test="reason != null">reason = #{reason},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deletePostById" parameterType="Long">
        delete from t_post where id = #{id}
    </delete>

    <delete id="deletePostByIds" parameterType="String">
        delete from t_post where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <update id="updateNumPost">
        UPDATE t_post
        <if test="type == 1">
            SET share_num = share_num + #{num}
        </if>
        <if test="type == 2">
            SET like_num = like_num + #{num}
        </if>
        <if test="type == 3">
            SET comment_num = comment_num + #{num}
        </if>
        <if test="type == 4">
            SET collect_num = collect_num + #{num}
        </if>
        <if test="type == 5">
            SET browse_num = browse_num + #{num}
        </if>
        WHERE
        id = #{id}
        <if test="type == 1">
            AND (share_num + #{num}) >= 0
        </if>
        <if test="type == 2">
            AND (like_num + #{num}) >= 0
        </if>
        <if test="type == 3">
            AND (comment_num + #{num}) >= 0
        </if>
        <if test="type == 4">
            AND (collect_num + #{num}) >= 0
        </if>
        <if test="type == 5">
            AND (browse_num + #{num}) >= 0
        </if>
    </update>

    <select id="postPage" resultType="com.ruoyi.post.controller.resp.PostPageResp">
        SELECT
        *
        FROM
        (
        SELECT
        t.id,
        1 type,
        t.create_time,
        t.trends_time,
        t.user_id,
        t.user_type,
        t.title,
        t.content,
        t.cover_image,
        t.video_url,
        t.share_num,
        t.like_num,
        t.comment_num,
        t.collect_num,
        t.browse_num,
        NULL isBuy,
        NULL price,
        CASE
        WHEN (
        SELECT
        COUNT( 1 )
        FROM
        t_post_like fl
        WHERE
        fl.del_flag = 1
        AND fl.type = 1
        AND fl.user_id = #{userVo.userId}
        AND fl.user_type = #{userVo.userType}
        AND fl.object_id = t.id
        ) = 0 THEN
        1 ELSE 2
        END isLike,

        CASE
        WHEN (
        SELECT
        COUNT( 1 )
        FROM
        t_collect c
        WHERE
        c.del_flag = 1
        AND c.type = 2
        AND c.user_id = #{userVo.userId}
        AND c.user_type = #{userVo.userType}
        AND c.object_id = t.id
        ) = 0 THEN
        1 ELSE 2
        END isCollect,

        CASE
        WHEN #{userVo.userType} = t.user_type
        AND #{userVo.userId} = t.user_id THEN
        2 ELSE 1
        END isSelf,
        t.result,
        t.address,
        case when r.id is null then 1 else 2 end isRecommend,
        r.sort,
        t.comment_id,
        t.describes
        FROM
        t_post t
        left join t_customer c on c.id = t.user_id and t.user_type = 1
        left join t_store s on s.id = t.user_id and t.user_type = 2
        left join t_recommend r on r.object_id = t.id and r.type = 2 and r.del_flag = 1
        WHERE
        t.del_flag = 1
        AND t.result = 2
        <if test="req.keyword!=null and req.keyword!=''">
            AND (
            t.content LIKE CONCAT('%',#{req.keyword},'%')
            or
            s.nickname LIKE CONCAT('%',#{req.keyword},'%')
            or
            c.nickname LIKE CONCAT('%',#{req.keyword},'%')
            )
        </if>
        <if test="req.postTypeId!=null and req.postTypeId!=''">
            AND t.post_type_id = #{req.postTypeId}
        </if>
        <if test="req.type!=null and req.type!=''">
            <if test="req.type == 1">
                and (
                SELECT
                COUNT( 1 )
                FROM
                t_follow f
                WHERE
                f.del_flag = 1
                AND f.user_id = #{userVo.userId}
                AND f.user_type = #{userVo.userType}
                AND f.follower_user_id = t.user_id
                AND f.follower_user_type = t.user_type
                ) > 0
            </if>
        </if>

        <if test="req.blockUserList!=null and req.blockUserList.size() > 0">
            AND (
            (t.user_type = 1 and t.user_id not in
            <foreach collection="req.blockUserList" item="userVo" open="(" close=")" separator=",">
                IF(#{userVo.userType} = 1,#{userVo.userId},'')
            </foreach>)
            or
            (t.user_type = 2 and t.user_id not in
            <foreach collection="req.blockUserList" item="userVo" open="(" close=")" separator=",">
                IF(#{userVo.userType} = 2,#{userVo.userId},'')
            </foreach>)
            or
            (t.user_type = 4 and t.user_id not in
            <foreach collection="req.blockUserList" item="userVo" open="(" close=")" separator=",">
                IF(#{userVo.userType} = 4,#{userVo.userId},'')
            </foreach>)
            )
        </if>

        <if test="req.shieldUserList!=null and req.shieldUserList.size() > 0">
            AND (
            (t.user_type = 1 and t.user_id not in
            <foreach collection="req.shieldUserList" item="userVo" open="(" close=")" separator=",">
                IF(#{userVo.userType} = 1,#{userVo.userId},'')
            </foreach>)
            or
            (t.user_type = 2 and t.user_id not in
            <foreach collection="req.shieldUserList" item="userVo" open="(" close=")" separator=",">
                IF(#{userVo.userType} = 2,#{userVo.userId},'')
            </foreach>)
            or
            (t.user_type = 4 and t.user_id not in
            <foreach collection="req.shieldUserList" item="userVo" open="(" close=")" separator=",">
                IF(#{userVo.userType} = 4,#{userVo.userId},'')
            </foreach>)
            )
        </if>

        <if test="req.shieldPostIdList!=null and req.shieldPostIdList.size() > 0">
            AND t.id not in
            <foreach collection="req.shieldPostIdList" item="i" open="(" close=")" separator=",">
                #{i}
            </foreach>
        </if>

        <if test="req.userId!=null and req.userId != ''">
            AND t.user_id = #{req.userId}
        </if>

        <choose>
            <when test="req.postType!=null and req.postType != ''">
                AND t.type = #{req.postType}
            </when>
            <otherwise>
                AND t.type = 1
            </otherwise>
        </choose>

        <if test="req.userType!=null and req.userType != ''">
            AND t.user_type = #{req.userType}
        </if>
        ) tt
        <if test="req.orderSort==null or req.orderSort==''">
            order by tt.isRecommend desc, tt.sort,tt.trends_time desc
        </if>
        <if test="req.orderSort!=null and req.orderSort!=''">
            order by
            <if test="req.orderSort==1">
                tt.create_time desc
            </if>
            <if test="req.orderSort==2">
                tt.like_num desc
            </if>
            <if test="req.orderSort==3">
                tt.comment_num desc
            </if>
        </if>
    </select>


    <select id="postDetail" resultType="com.ruoyi.post.controller.resp.PostDetailResp">
        SELECT
            t.id,
            t.create_time,
            t.user_type,
            t.user_id,
            t.title,
            t.content,
            t.cover_image,
            t.video_url,
            t.share_num,
            t.like_num,
            t.comment_num,
            t.collect_num,
            t.browse_num,
            t.result,
            t.reason,
            t.address,
            CASE

                WHEN (
                         SELECT
                             COUNT( 1 )
                         FROM
                             t_post_like fl
                         WHERE
                             fl.del_flag = 1
                           AND fl.type = 1
                           AND fl.user_id = #{userVo.userId}
                           AND fl.user_type = #{userVo.userType}
                           AND fl.object_id = t.id
                     ) = 0 THEN
                    1 ELSE 2
                END isLike,
            CASE

                WHEN (
                         SELECT
                             COUNT( 1 )
                         FROM
                             t_collect c
                         WHERE
                             c.del_flag = 1
                           AND c.type = 2
                           AND c.user_id = #{userVo.userId}
                           AND c.user_type = #{userVo.userType}
                           AND c.object_id = t.id
                     ) = 0 THEN
                    1 ELSE 2
                END isCollect,
            CASE
                WHEN #{userVo.userType} = t.user_type
                    AND #{userVo.userId} = t.user_id THEN
                    2 ELSE 1
                END isSelf,
            t2.name postTypeName,
            t.describes
        FROM
            t_post t
                left JOIN t_post_type t2 ON t2.id = t.post_type_id
        WHERE
            t.id = #{id}
          AND t.del_flag = 1
    </select>

    <update id="updateBrowseRecord">
        insert into t_browse_record
            (id, create_time, update_time, type, user_type, user_id, object_id)
        values
            (#{id},#{updateTime},#{updateTime},#{type},#{userVo.userType}, #{userVo.userId}, #{objectId})
        ON DUPLICATE  KEY UPDATE
            update_time = #{updateTime};
    </update>

    <select id="statistics" parameterType="com.ruoyi.post.frequency.post.entity.Post" resultType="com.ruoyi.post.frequency.post.model.result.PostCount">
        SELECT
        COUNT(1) num,
        COUNT(
        DISTINCT t.user_id,
        t.user_type
        ) number,
        COUNT(DISTINCT c.id) customerNum,
        COUNT(DISTINCT s.id) storeNum,
        ifnull(sum(t.like_num), 0) likeNum,
        ifnull(sum(t.comment_num), 0) commentNum,
        ifnull(sum(t.collect_num), 0) collectNum,
        ifnull(sum(t.browse_num), 0) browseNum
        FROM
        t_post AS t
        LEFT JOIN t_customer c ON c.id = t.user_id
        AND t.user_type = 1
        LEFT JOIN t_store s ON s.id = t.user_id
        AND t.user_type = 2
        where
        t.id in
        <foreach item="id" collection="list" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>
</mapper>
