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

    <select id="findRoomList" resultType="com.m2pool.chat.dto.ChatRoomDto">
        SELECT
            id,
            user_one_email AS userEmail,
            flag,
            CASE WHEN last_user_send_time >= last_customer_send_time
            THEN last_user_send_time ELSE last_customer_send_time END AS lastUserSendTime,
            service_read_num AS clientReadNum
        FROM
            chat_room
        <where>
            del = false
            <choose>
                <when test="ids != null and ids.size() > 0">
                    AND id IN
                    <foreach item="id" index="index" collection="ids"
                             open="(" separator="," close=")">
                        #{id}
                    </foreach>
                </when>
                <when test="sendDateTime != null">
                    AND last_user_send_time <![CDATA[ <= ]]>  #{sendDateTime}
                </when>
                <otherwise>
                    AND last_user_send_time <![CDATA[ <= ]]>  NOW()
                </otherwise>
            </choose>
        </where>
        ORDER BY
            flag DESC,
            GREATEST( last_user_send_time, last_customer_send_time ) DESC
        LIMIT 20
    </select>
    <select id="findRoomByUserEmail" resultType="com.m2pool.chat.dto.ChatRoomDto">
        SELECT
            id,client_read_num AS clientReadNum ,user_one_email AS selfEmail,user_two_email AS userEmail
        FROM
            chat_room
        WHERE
            user_one_email = #{userEmail}
    </select>
    <insert id="insetOrUpdateRoom">
        INSERT INTO chat_room(user_one_email,user_two_email)
        VALUES (#{room.userOneEmail},#{room.userTwoEmail})
        ON DUPLICATE KEY UPDATE
        user_one_email = #{room.userOneEmail},
        user_two_email = #{room.userTwoEmail}
    </insert>

</mapper>
