<?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="org.dromara.member.mapper.AppUserMessageMapper">

    <resultMap type="org.dromara.member.domain.AppUserMessage" id="AppUserMessageResult">
        <id property="id" column="id"/>
        <result property="userId" column="user_id"/>
        <result property="messageId" column="message_id"/>
        <result property="isRead" column="is_read"/>
        <result property="readTime" column="read_time"/>
        <result property="createTime" column="create_time"/>
    </resultMap>

    <resultMap type="org.dromara.member.domain.vo.AppUserMessageVo" id="AppUserMessageVoResult">
        <id property="id" column="id"/>
        <result property="userId" column="user_id"/>
        <result property="messageId" column="message_id"/>
        <result property="isRead" column="is_read"/>
        <result property="readTime" column="read_time"/>
        <result property="createTime" column="create_time"/>
    </resultMap>

    <sql id="selectAppUserMessageVo">
        select user_message_id, user_id, message_id, is_read, read_time, create_time
        from app_user_message
    </sql>

    <select id="selectAppUserMessageList" parameterType="AppUserMessage" resultMap="AppUserMessageVoResult">
        <include refid="selectAppUserMessageVo"/>
        <where>
            <if test="userId != null">and user_id = #{userId}</if>
            <if test="messageId != null">and message_id = #{messageId}</if>
            <if test="isRead != null">and is_read = #{isRead}</if>
            <if test="params.beginTime != null and params.beginTime != ''"><!-- 开始时间检索 -->
                and date_format(create_time,'%y%m%d') &gt;= date_format(#{params.beginTime},'%y%m%d')
            </if>
            <if test="params.endTime != null and params.endTime != ''"><!-- 结束时间检索 -->
                and date_format(create_time,'%y%m%d') &lt;= date_format(#{params.endTime},'%y%m%d')
            </if>
        </where>
        order by create_time desc
    </select>

    <select id="selectAppUserMessageByUserMessageId" parameterType="Long" resultMap="AppUserMessageVoResult">
        <include refid="selectAppUserMessageVo"/>
        where user_message_id = #{userMessageId}
    </select>

    <select id="selectAppUserMessageByUserId" parameterType="Long" resultMap="AppUserMessageVoResult">
        <include refid="selectAppUserMessageVo"/>
        where user_id = #{userId}
        order by create_time desc
    </select>

    <select id="selectUnreadMessagesByUserId" parameterType="Long" resultMap="AppUserMessageVoResult">
        <include refid="selectAppUserMessageVo"/>
        where user_id = #{userId} and is_read = 0
        order by create_time desc
    </select>

    <select id="countUnreadMessagesByUserId" parameterType="Long" resultType="int">
        select count(1)
        from app_user_message
        where user_id = #{userId}
          and is_read = 0
    </select>

    <select id="selectAppUserMessageByUserIdAndMessageId" resultMap="AppUserMessageVoResult">
        <include refid="selectAppUserMessageVo"/>
        where user_id = #{userId} and message_id = #{messageId}
    </select>

    <insert id="insertAppUserMessage" parameterType="AppUserMessage" useGeneratedKeys="true"
            keyProperty="userMessageId">
        insert into app_user_message
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="userId != null">user_id,</if>
            <if test="messageId != null">message_id,</if>
            <if test="isRead != null">is_read,</if>
            <if test="readTime != null">read_time,</if>
            create_time
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="userId != null">#{userId},</if>
            <if test="messageId != null">#{messageId},</if>
            <if test="isRead != null">#{isRead},</if>
            <if test="readTime != null">#{readTime},</if>
            sysdate()
        </trim>
    </insert>

    <insert id="batchInsertAppUserMessage" parameterType="java.util.List">
        insert into app_user_message(user_id, message_id, is_read, create_time)
        values
        <foreach collection="list" item="item" separator=",">
            (#{item.userId}, #{item.messageId}, #{item.isRead}, sysdate())
        </foreach>
    </insert>

    <update id="updateAppUserMessage" parameterType="AppUserMessage">
        update app_user_message
        <trim prefix="SET" suffixOverrides=",">
            <if test="userId != null">user_id = #{userId},</if>
            <if test="messageId != null">message_id = #{messageId},</if>
            <if test="isRead != null">is_read = #{isRead},</if>
            <if test="readTime != null">read_time = #{readTime},</if>
        </trim>
        where user_message_id = #{userMessageId}
    </update>

    <update id="markAsRead" parameterType="Long">
        update app_user_message
        set is_read   = 1,
            read_time = sysdate()
        where user_message_id = #{userMessageId}
    </update>

    <update id="markAllAsReadByUserId" parameterType="Long">
        update app_user_message
        set is_read   = 1,
            read_time = sysdate()
        where user_id = #{userId}
          and is_read = 0
    </update>

    <delete id="deleteAppUserMessageByUserMessageId" parameterType="Long">
        delete
        from app_user_message
        where user_message_id = #{userMessageId}
    </delete>

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

    <delete id="deleteAppUserMessageByUserId" parameterType="Long">
        delete
        from app_user_message
        where user_id = #{userId}
    </delete>

    <delete id="deleteAppUserMessageByMessageId" parameterType="Long">
        delete
        from app_user_message
        where message_id = #{messageId}
    </delete>

</mapper>