<?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
        FROM (
                 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} ,#{messageQO.receiverUserId}) AND receiver_user_id IN (#{senderUserId} ,#{messageQO.receiverUserId})
                 ORDER BY create_time desc
                     LIMIT #{messageQO.pageNo},#{messageQO.pageSize}
             ) AS subquery
        ORDER BY create_time ASC;
    </select>
    <select id="countUserMessage" resultType="java.lang.Integer">
        select count(*)
        from user_message
        where is_deleted = 0 and sender_user_id in(#{senderUserId} ,#{receiverUserId}) and receiver_user_id in(#{senderUserId} ,#{receiverUserId})
    </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} 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})
        order by create_time desc
            LIMIT 1
    </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>
        </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>
        </trim>
    </insert>
    <insert id="insertUserMessageLog">
        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>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="senderUserId != null">#{senderUserId,jdbcType=VARCHAR},</if>
            <if test="receiverUserId != null">#{receiverUserId,jdbcType=VARCHAR},</if>
            <if test="messageCount != null">#{messageCount,jdbcType=VARCHAR},</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,</if>
            <if test="content != null">content</if>
        </set>
        <where>
            id = #{id}
        </where>
    </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>

</mapper>
