<?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.m2pool.chat.mapper.ChatMessageMapper">


    <select id="findRecentlyMessage" resultType="com.m2pool.chat.dto.ChatMessageDto">
        SELECT id,
               type,
               send_email as sendEmail,
               content,
               create_time as createTime,
               room_id as roomId,
               case when send_email = #{email} then 1
                   else 0
               end as isSelf
        FROM chat_message
        <where>
            room_id = #{roomId}
            <if test="id != null">
                AND id <![CDATA[ < ]]> #{id}
            </if>
        </where>
        ORDER BY id DESC
        LIMIT #{pageNum}
    </select>
    <select id="findUnReadNums" resultType="java.util.Map">
        SELECT send_email as userEmail,count(*) as unReadNums
        FROM chat_message
        WHERE send_email IN
              <foreach collection="userEmails" item="userEmail" open="(" separator="," close=")">
                  #{userEmail}
              </foreach>
              AND is_read = false
        GROUP BY send_email
    </select>
    <select id="findRoomIdsByCustomerEmail" resultType="java.lang.Long">
        select room_id from chat_message where send_email = #{userEmail}  group by room_id
        UNION
        select room_id from chat_message_history where send_email = #{userEmail}  group by room_id
        UNION
        select id as room_id from chat_room where user_two_email = #{userEmail}
    </select>

</mapper>
