<?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.frequency.follow.mapper.FollowMapper">

    <resultMap type="com.ruoyi.frequency.follow.entity.Follow" id="FollowResult">
        <result property="id"    column="id"    />
        <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="delFlag"    column="del_flag"    />
        <result property="userId"    column="user_id"    />
        <result property="followerUserId"    column="follower_user_id"    />
        <result property="userType"    column="user_type"    />
        <result property="followerUserType"    column="follower_user_type"    />
        <result property="orderNum"    column="orderNum"    />
        <result property="orderAmount"    column="orderAmount"    />
    </resultMap>

    <sql id="selectFollowVo">
        SELECT
            t.id,
            t.create_by,
            t.create_time,
            t.update_by,
            t.update_time,
            t.del_flag,
            t.user_id,
            t.follower_user_id,
            t.follower_user_type,
            t.user_type,
            case t.user_type
                when 1
                    then (select count(1) from t_order o where o.customer_id = t.user_id and o.status >= 4)
                when 2
                    then (select count(1) from t_order o where o.store_id = t.user_id and o.status >= 4)
                else 0 end orderNum,
            case t.user_type
                when 1
                    then ifnull((select sum(o.amount) from t_order o where o.customer_id = t.user_id and o.status >= 4),0)
                when 2
                    then ifnull((select sum(o.amount) from t_order o where o.store_id = t.user_id and o.status >= 4),0)
                else 0 end orderAmount
        FROM
            t_follow AS t
                LEFT JOIN t_store s on t.user_id = s.id and t.user_type = 2
                LEFT JOIN t_customer c on t.user_id = c.id and t.user_type = 1
                LEFT JOIN t_sale sa on t.user_id = sa.id and t.user_type = 3
                LEFT JOIN t_enterprise e on t.user_id = e.id and t.user_type = 4
                LEFT JOIN t_store es on e.id = es.bind_enterprise_id and t.user_type = 4
    </sql>

    <select id="selectFollowList" parameterType="com.ruoyi.frequency.follow.entity.Follow" resultMap="FollowResult">
        <include refid="selectFollowVo"/>
        <where>
            <if test="userId != null "> and t.user_id = #{userId}</if>
            <if test="followerUserId != null "> and t.follower_user_id = #{followerUserId}</if>
            <if test="userType != null "> and t.user_type = #{userType}</if>
            <if test="followerUserType != null "> and t.follower_user_type = #{followerUserType}</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
                sa.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
                sa.mobile like concat('%', #{userInfo.mobile}, '%')
                or
                es.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>
        </where>
        order by t.create_time desc
    </select>

    <select id="selectFollowById" parameterType="Long" resultMap="FollowResult">
        <include refid="selectFollowVo"/>
        where t.id = #{id}
    </select>

    <insert id="insertFollow" parameterType="com.ruoyi.frequency.follow.entity.Follow">
        insert into t_follow
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">id,</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="delFlag != null">del_flag,</if>
            <if test="userId != null">user_id,</if>
            <if test="followerUserId != null">follower_user_id,</if>
            <if test="userType != null">user_type,</if>
            <if test="followerUserType != null">follower_user_type,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">#{id},</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="delFlag != null">#{delFlag},</if>
            <if test="userId != null">#{userId},</if>
            <if test="followerUserId != null">#{followerUserId},</if>
            <if test="userType != null">#{userType},</if>
            <if test="followerUserType != null">#{followerUserType},</if>
        </trim>
    </insert>

    <update id="updateFollow" parameterType="com.ruoyi.frequency.follow.entity.Follow">
        update t_follow
        <trim prefix="SET" suffixOverrides=",">
            <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="delFlag != null">del_flag = #{delFlag},</if>
            <if test="userId != null">user_id = #{userId},</if>
            <if test="followerUserId != null">follower_user_id = #{followerUserId},</if>
            <if test="userType != null">user_type = #{userType},</if>
            <if test="followerUserType != null">follower_user_type = #{followerUserType},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteFollowById" parameterType="Long">
        delete from t_follow where id = #{id}
    </delete>

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

    <select id="followerPageBySelf" resultType="com.ruoyi.controller.resp.FollowPageResp">
        SELECT
        t.id ,
        t.update_time createTime,
        t.follower_user_id userId,
        t.follower_user_type userType
        FROM
        t_follow t
        LEFT JOIN t_customer c on t.follower_user_id = c.id and t.follower_user_type = 1
        LEFT JOIN t_store s on t.follower_user_id = s.id and t.follower_user_type = 2
        LEFT JOIN (
        SELECT
        p.*
        FROM
        t_post p
        LEFT JOIN ( SELECT MAX( create_time ) create_time, user_id FROM t_post GROUP BY user_id ) q on
        p.user_id = q.user_id
        WHERE
        p.del_flag = 1
        and p.create_time = q.create_time

        ) pp on pp.user_id = t.follower_user_id
        WHERE
        t.user_id = #{userVo.userId}
        AND t.user_type = #{userVo.userType}
        AND t.del_flag=1
        <if test="req.keyword!=null and req.keyword!=''">
            AND (
            c.nickname LIKE CONCAT('%',#{req.keyword},'%')
            or
            s.nickname LIKE CONCAT('%',#{req.keyword},'%')
            )
        </if>
<!--        <if test="req.blockUserList!=null and req.blockUserList.size() > 0">-->
<!--            AND (-->
<!--            (t.follower_user_type = 1 and t.follower_user_id not in-->
<!--            <foreach collection="req.blockUserList" item="userVo" open="(" close=")" separator=",">-->
<!--                IF(#{userVo.userType} = 1,#{userVo.userId},'')-->
<!--            </foreach>)-->
<!--            or-->
<!--            (t.follower_user_type = 2 and t.follower_user_id not in-->
<!--            <foreach collection="req.blockUserList" item="userVo" open="(" close=")" separator=",">-->
<!--                IF(#{userVo.userType} = 2,#{userVo.userId},'')-->
<!--            </foreach>)-->
<!--            or-->
<!--            (t.follower_user_type = 4 and t.follower_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.follower_user_type = 1 and t.follower_user_id not in-->
<!--            <foreach collection="req.shieldUserList" item="userVo" open="(" close=")" separator=",">-->
<!--                IF(#{userVo.userType} = 1,#{userVo.userId},'')-->
<!--            </foreach>)-->
<!--            or-->
<!--            (t.follower_user_type = 2 and t.follower_user_id not in-->
<!--            <foreach collection="req.shieldUserList" item="userVo" open="(" close=")" separator=",">-->
<!--                IF(#{userVo.userType} = 2,#{userVo.userId},'')-->
<!--            </foreach>)-->
<!--            or-->
<!--            (t.follower_user_type = 4 and t.follower_user_id not in-->
<!--            <foreach collection="req.shieldUserList" item="userVo" open="(" close=")" separator=",">-->
<!--                IF(#{userVo.userType} = 4,#{userVo.userId},'')-->
<!--            </foreach>)-->
<!--            )-->
<!--        </if>-->

        <choose>
            <when test="req.sortType != null and req.sortType != ''">
                <if test="req.sortType == 1 ">
                    ORDER BY
                    pp.create_time desc
                </if>
            </when>
            <otherwise>
                ORDER BY
                t.update_time DESC
            </otherwise>
        </choose>

    </select>

    <select id="notFollowerPageBySelf" resultType="com.ruoyi.controller.resp.FollowPageResp">
        SELECT
        *
        FROM
        (
        SELECT
        t.id userId,
        1 userType,
        t.create_time
        FROM
        t_customer t
        LEFT JOIN t_follow f ON f.user_id = #{userVo.userId}
        AND f.user_type = #{userVo.userType}
        AND f.follower_user_id = t.id
        AND f.follower_user_type = 1
        AND f.del_flag = 1
        WHERE
        t.del_flag = 1
        AND f.id IS NULL
        AND t.id !=  #{userVo.userId}
        <if test="req.keyword!=null and req.keyword!=''">
            AND (
            t.nickname LIKE CONCAT('%',#{req.keyword},'%')
            or
            t.name LIKE CONCAT('%',#{req.keyword},'%')
            )
        </if>
        UNION ALL
        SELECT
        t.id userId,
        2 userType,
        t.create_time
        FROM
        t_store t
        LEFT JOIN t_follow f ON f.user_id = #{userVo.userId}
        AND f.user_type = #{userVo.userType}
        AND f.follower_user_id = t.id
        AND f.follower_user_type = 2
        AND f.del_flag = 1
        WHERE
        t.del_flag = 1
        AND f.id IS NULL
        AND t.id !=  #{userVo.userId}
        <if test="req.keyword!=null and req.keyword!=''">
            AND (
            t.nickname LIKE CONCAT('%',#{req.keyword},'%')
            or
            t.name LIKE CONCAT('%',#{req.keyword},'%')
            )
        </if>
        ) tt
        LEFT JOIN (
        SELECT
        p.*
        FROM
        t_post p
        LEFT JOIN ( SELECT MAX( trends_time ) trends_time, user_id FROM t_post GROUP BY user_id ) q on
        p.user_id = q.user_id
        WHERE
        p.del_flag = 1
        and p.trends_time = q.trends_time
        ) pp on pp.user_id = tt.userId
        WHERE 1=1
        <if test="req.blockUserList!=null and req.blockUserList.size() > 0">
            AND (
            (tt.userType = 1 and tt.userId not in
            <foreach collection="req.blockUserList" item="userVo" open="(" close=")" separator=",">
                IF(#{userVo.userType} = 1,#{userVo.userId},'')
            </foreach>)
            or
            (tt.userType = 2 and tt.userId not in
            <foreach collection="req.blockUserList" item="userVo" open="(" close=")" separator=",">
                IF(#{userVo.userType} = 2,#{userVo.userId},'')
            </foreach>)
            or
            (tt.userType = 4 and tt.userId 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 (
            (tt.userType = 1 and tt.userId not in
            <foreach collection="req.shieldUserList" item="userVo" open="(" close=")" separator=",">
                IF(#{userVo.userType} = 1,#{userVo.userId},'')
            </foreach>)
            or
            (tt.userType = 2 and tt.userId not in
            <foreach collection="req.shieldUserList" item="userVo" open="(" close=")" separator=",">
                IF(#{userVo.userType} = 2,#{userVo.userId},'')
            </foreach>)
            or
            (tt.userType = 4 and tt.userId not in
            <foreach collection="req.shieldUserList" item="userVo" open="(" close=")" separator=",">
                IF(#{userVo.userType} = 4,#{userVo.userId},'')
            </foreach>)
            )
        </if>
        <choose>
            <when test="req.sortType != null and req.sortType != ''">
                <if test="req.sortType == 1 ">
                    ORDER BY
                    pp.create_time desc
                </if>
                <if test="req.sortType == 2 ">
                    ORDER BY
                    pp.trends_time desc
                </if>
            </when>
            <otherwise>
                ORDER BY
                tt.create_time DESC
            </otherwise>
        </choose>
    </select>


    <select id="fansPageBySelf" resultType="com.ruoyi.controller.resp.FollowPageResp">
        SELECT
        t.id ,
        t.update_time createTime,
        t.user_id userId,
        t.user_type userType
        FROM
        t_follow t
        LEFT JOIN t_customer c on t.user_id = c.id and t.user_type = 1
        LEFT JOIN t_store s on t.user_id = s.id and t.user_type = 2
        WHERE
        t.follower_user_id = #{userVo.userId}
        AND t.follower_user_type = #{userVo.userType}
        AND t.del_flag=1
        <if test="req.keyword!=null and req.keyword!=''">
            AND (
            c.nickname LIKE CONCAT('%',#{req.keyword},'%')
            or
            s.nickname LIKE CONCAT('%',#{req.keyword},'%')
            )
        </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>-->
        ORDER BY
        t.update_time DESC
    </select>
</mapper>