<?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">


    <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>
    <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>
    <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>

</mapper>
