<?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.mmc.iuav.user.dao.MessageDao">

    <delete id="removeUserMessageLog">
        delete from user_message_log
        where id = #{id}
    </delete>


    <select id="selectSystemMessageById" resultType="com.mmc.iuav.user.entity.SystemMessageDO">
        select
            id, subject, content, type, create_time
        from message
        where  id = #{id} and is_deleted = 0
    </select>
    <select id="selectAllSystemMessage" resultType="com.mmc.iuav.user.entity.SystemMessageDO">
        select
            id, subject, content, type, create_time
        from message
        where type = 0 and is_deleted = 0
        order by create_time desc
            LIMIT #{pageNo},#{pageSize}
    </select>
    <select id="selectReceiverMessage" resultType="java.lang.Integer">
        select count(*)
        from receiver_message
        where user_id = #{userId} and message_id = #{messageId}
    </select>
    <select id="selectReceiverMessageByDel" resultType="java.lang.Integer">
        select count(*)
        from receiver_message
        where user_id = #{userId} and message_id = #{messageId} and is_deleted = 1
    </select>
    <select id="countSystemMessage" resultType="java.lang.Integer">
        SELECT
            count(*)
        FROM message
        where is_deleted = 0
    </select>
    <select id="selectUserMessage" resultType="com.mmc.iuav.user.entity.UserMessageDO">
SELECT id, img_url, content, sender_user_id, receiver_user_id, create_time, back_user_id
              FROM user_message
              where is_deleted = 0
                and sender_user_id in (#{senderUserId}, #{messageQO.receiverUserId})
                and receiver_user_id in (#{senderUserId}, #{messageQO.receiverUserId})
                and
                case
                when #{messageQO.backUserId} is not null then back_user_id = #{messageQO.backUserId}
                  else back_user_id is null
                end
ORDER BY create_time desc
                  LIMIT #{messageQO.pageNo}
                  , #{messageQO.pageSize}
    </select>
    <select id="countUserMessage" resultType="java.lang.Integer">
        select count(*)
        from user_message
        where is_deleted = 0 and sender_user_id in(#{senderUserId} ,#{messageQO.receiverUserId}) and receiver_user_id in(#{senderUserId} ,#{messageQO.receiverUserId})
          and
            case
                when #{messageQO.backUserId} is not null then back_user_id = #{messageQO.backUserId}
                else back_user_id is null
                end
    </select>
    <select id="selectUserListBySenderUserId" resultType="java.lang.Integer">
        select
            distinct receiver_user_id
        from user_message
        where is_deleted = 0 and sender_user_id = #{senderUserId}
    </select>
    <select id="selectUserListByReceiverUserId" resultType="java.lang.Integer">
        select
            distinct sender_user_id
        from user_message
        where is_deleted = 0 and receiver_user_id = #{senderUserId}
    </select>
    <select id="isUserMessageLog" resultType="java.lang.Integer">
        select id
        from user_message_log
        where sender_user_id = #{senderUserId}
          and receiver_user_id = #{receiverUserId}
          and case
                  when #{backUserId} is not null then back_user_id = #{backUserId}
                  else back_user_id is null
            end
            limit 1
    </select>
    <select id="selectUserList" resultType="com.mmc.iuav.user.entity.UserMessageLogDO">
        select *
        from user_message_log
        where sender_user_id = #{senderUserId}
    </select>
    <select id="selectUserMessageById" resultType="com.mmc.iuav.user.entity.UserMessageDO">
        select
            id, img_url, content, sender_user_id,receiver_user_id, create_time
        from user_message
        where is_deleted = 0 and id = #{id}
    </select>
    <select id="selectEndUserMessage" resultType="com.mmc.iuav.user.entity.UserMessageDO">
        select
            id, img_url, content, sender_user_id,receiver_user_id, create_time
        from user_message
        where is_deleted = 0 and sender_user_id in(#{senderUserId} ,#{receiverUserId}) and receiver_user_id in(#{senderUserId} ,#{receiverUserId})
        and
            case
                when #{backUserId} is not null then back_user_id = #{backUserId}
                else back_user_id is null
                end
        order by create_time desc
            LIMIT 1
    </select>
    <select id="selectUserMessageTimeType" resultType="com.mmc.iuav.user.model.vo.MessageTimeVO">
        select *
        from user_message_time_type
        where id = #{id}
    </select>
    <select id="listTimeType" resultType="com.mmc.iuav.user.model.vo.MessageTimeVO">
        select *
        from user_message_time_type
    </select>

    <select id="getUserMessageNum" resultType="java.lang.Integer">
        SELECT COUNT(DISTINCT back_user_id)
        FROM user_message
        WHERE DATE(create_time) = CURDATE() AND back_user_id IS NOT NULL
    </select>
    <select id="getUserAndUserMessageNum" resultType="java.lang.Integer">
        SELECT
            COUNT(*) AS count_distinct_pairs
        FROM
            ( SELECT DISTINCT sender_user_id, receiver_user_id FROM user_message WHERE DATE( create_time ) = CURDATE() AND back_user_id IS NULL ) AS distinct_pairs
    </select>

    <insert id="insertSystemMessage">
        insert into message
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="subject != null">subject,</if>
            <if test="content != null">content,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="subject != null">#{subject,jdbcType=VARCHAR},</if>
            <if test="content != null">#{content,jdbcType=VARCHAR},</if>
        </trim>
    </insert>
    <insert id="insertReceiverMessage">
        insert into receiver_message
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="userId != null">user_id,</if>
            <if test="messageId != null">message_id,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="userId != null">#{userId},</if>
            <if test="messageId != null">#{messageId},</if>
        </trim>
    </insert>
    <insert id="insertUserMessage">
        insert into user_message
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="senderUserId != null">sender_user_id,</if>
            <if test="receiverUserId != null">receiver_user_id,</if>
            <if test="content != null">content,</if>
            <if test="imgUrl != null">img_url,</if>
            <if test="backUserId != null">back_user_id,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="senderUserId != null">#{senderUserId,jdbcType=VARCHAR},</if>
            <if test="receiverUserId != null">#{receiverUserId,jdbcType=VARCHAR},</if>
            <if test="content != null">#{content,jdbcType=VARCHAR},</if>
            <if test="imgUrl != null">#{imgUrl,jdbcType=VARCHAR},</if>
            <if test="backUserId != null">#{backUserId,jdbcType=VARCHAR},</if>
        </trim>
    </insert>

    <insert id="insertTimeType">
        insert into user_message_time_type
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="money != null">money,</if>
            <if test="points != null">points,</if>
            <if test="chatDuration != null">chat_duration,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="money != null">#{money},</if>
            <if test="points != null">#{points},</if>
            <if test="chatDuration != null">#{chatDuration},</if>
        </trim>
    </insert>
    <insert id="insertUserMessageLog" parameterType="com.mmc.iuav.user.entity.UserMessageLogDO">
        insert into user_message_log
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="senderUserId != null">sender_user_id,</if>
            <if test="receiverUserId != null">receiver_user_id,</if>
            <if test="messageCount != null">message_count,</if>
            <if test="backUserId != null">back_user_id,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="senderUserId != null">#{senderUserId,jdbcType=INTEGER},</if>
            <if test="receiverUserId != null">#{receiverUserId,jdbcType=INTEGER},</if>
            <if test="messageCount != null">#{messageCount,jdbcType=INTEGER},</if>
            <if test="backUserId != null">#{backUserId,jdbcType=INTEGER},</if>
        </trim>
    </insert>

    <update id="removeMessage">
        update message
        set is_deleted = 1, deleted_time = NOW()
        where   id = #{id,jdbcType=INTEGER}
    </update>
    <update id="removeReceiverMessage">
        update receiver_message
        set is_deleted = 1, deleted_time = NOW()
        where   user_id = #{userId} and message_id = #{messageId}
    </update>
    <update id="removeReceiverMessageByMessageId">
        update receiver_message
        set is_deleted = 1, deleted_time = NOW()
        where message_id = #{messageId}
    </update>
    <update id="updateSystemMessage">
        UPDATE message
        <set>
            <if test="subject != null">
                subject = #{subject},
            </if>
            <if test="content != null">
                content = #{content}
            </if>
        </set>
        WHERE id = #{id}
    </update>
    <update id="removeUserMessage">
        update user_message
        set is_deleted = 1, deleted_time = NOW()
        where   id = #{id,jdbcType=INTEGER}
    </update>
    <update id="updateUserMessageLog">
        update user_message_log
        set
            message_count = message_count + 1,
            new_time = NOW()
        where id = #{id}
    </update>
    <update id="updateUserMessageCount">
        update user_message_log
        set
            message_count = 0
        where id = #{id}
    </update>
    <update id="removeUserMessageCount">
        update user_message_log
        set
            message_count = message_count - 1
        where id = #{id}
    </update>
    <update id="updateTimeType">
        update user_message_time_type
        <set>
            <if test="money != null">
                money = #{money},
            </if>
            <if test="points != null">
                points = #{points},
            </if>
            <if test="chatDuration != null">
                chat_duration = #{chatDuration}
            </if>
        </set>
        where id = #{id}
    </update>

</mapper>
