<?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.wecloud.im.mapper.ImMessageMapper">

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id
        , create_time, withdraw_time, update_date, fk_appid, sender, content, withdraw, event, msg_type, system_flag, at, send_status, fk_conversation_id
    </sql>

    <select id="getImMessageById" resultType="com.wecloud.im.param.ImMessageQueryVo">
        select
        <include refid="Base_Column_List"/>
        from im_message where id = #{id}
    </select>

    <select id="getImMessagePageList" parameterType="com.wecloud.im.param.ImMessagePageParam"
            resultType="com.wecloud.im.param.ImMessageQueryVo">
        select
        <include refid="Base_Column_List"/>
        from im_message
    </select>
    <select id="getOfflineListByClientAndConversation" resultType="com.wecloud.im.vo.OfflineMsgDto">
        SELECT im_message.id                   AS msgId,
               im_message.create_time,
               im_message.withdraw_time,
               im_message.update_date,
               `im_client`.client_id           AS sender,
               im_message.content,
               im_message.withdraw,
               im_message.`event`,
               im_message.system_flag,
               im_message.`at`,
               im_message.send_status,
               im_message.fk_conversation_id,
               im_message.`msg_type`           AS 'type',
               (SELECT COUNT(id)
                FROM im_inbox
                WHERE fk_msg_id = msgId
                  AND read_msg_status = 0)     AS not_read_count,
               (SELECT COUNT(id)
                FROM im_inbox
                WHERE fk_msg_id = msgId
                  AND receiver_msg_status = 0) AS not_receiver_count

        FROM im_inbox
                 INNER JOIN im_message im_message ON im_message.id = im_inbox.fk_msg_id
                 INNER JOIN `im_client` ON `im_client`.id = `im_message`.sender
        WHERE im_inbox.fk_conversation_id = #{conversationId}
          AND im_inbox.receiver = #{clientId}
          AND im_inbox.receiver_msg_status = 0
          AND im_message.is_delete = 1
          AND im_message.withdraw = 0
    </select>
    <select id="getHistoryMsgConversationId" resultType="com.wecloud.im.vo.OfflineMsgDto">
        SELECT im_message.id AS msgId,
        im_message.create_time,
        im_message.withdraw_time,
        im_message.update_date,
        `im_client`.client_id AS sender,
        im_message.content,
        im_message.withdraw,
        im_message.`event`,
        im_message.system_flag,
        im_message.`at`,
        im_message.send_status,
        im_message.`msg_type` AS 'type',
        im_message.fk_conversation_id as conversationId,
        (SELECT COUNT(id) FROM im_inbox WHERE fk_msg_id = msgId AND read_msg_status = 0) AS not_read_count,
        (SELECT COUNT(id)
        FROM im_inbox
        WHERE fk_msg_id = msgId
        AND receiver_msg_status = 0) AS not_receiver_count
        FROM `im_message`
        INNER JOIN `im_client` ON `im_client`.id = `im_message`.sender
        WHERE fk_conversation_id = #{param.conversationId} and im_message.is_delete = 1 and im_message.withdraw = 0
        and (im_message.`event`=0 || (im_message.`event`=1 and sender != #{param.currentFkClientId} and (receivers is
        null || (receivers !=null and FIND_IN_SET(#{param.currentFkClientId}, receivers))) ))
        <if test="param.msgIdStart != null">
            AND im_message.id > #{param.msgIdStart}
        </if>
        <if test="param.msgIdEnd != null">
            AND im_message.id <![CDATA[ < ]]> #{param.msgIdEnd}
        </if>

        ORDER BY `im_message`.`create_time` DESC
    </select>


    <select id="getHistoryMsgConversationIdNew" resultType="com.wecloud.im.vo.OfflineMsgDto">
        SELECT im_message.id AS msgId,
        im_message.create_time,
        im_message.withdraw_time,
        im_message.update_date,
        `im_client`.client_id AS sender,
        im_message.content,
        im_message.withdraw,
        im_message.`event`,
        im_message.system_flag,
        im_message.`at`,
        im_message.send_status,
        im_message.`msg_type` AS 'type',
        im_message.fk_conversation_id as conversationId,
        (SELECT COUNT(id) FROM im_inbox WHERE fk_msg_id = msgId AND read_msg_status = 0) AS not_read_count,
        (SELECT COUNT(id)
        FROM im_inbox
        WHERE fk_msg_id = msgId
        AND receiver_msg_status = 0) AS not_receiver_count
        FROM `im_message`
        INNER JOIN `im_client` ON `im_client`.id = `im_message`.sender
        WHERE fk_conversation_id = #{param.conversationId} and im_message.is_delete = 1 and im_message.withdraw = 0
        and (im_message.`event`=0 || (im_message.`event`=1 and sender != #{param.currentFkClientId} and (receivers is
        null || (receivers !=null and FIND_IN_SET(#{param.currentFkClientId}, receivers))) ))
        <if test="param.msgIdStart != null">
            AND im_message.id > #{param.msgIdStart}
        </if>
        <if test="param.getLast != null and param.getLast == 1">
            AND im_message.id <![CDATA[ < ]]> #{param.msgIdEnd}
        </if>
        <if test="param.getLast != null and param.getLast == 0">
            AND im_message.id <![CDATA[ <= ]]> #{param.msgIdEnd}
        </if>
        <if test="param.getLast == null">
            AND im_message.id <![CDATA[ < ]]> #{param.msgIdEnd}
        </if>

        ORDER BY `im_message`.`create_time` DESC
    </select>


    <select id="getReceivedLastMsgByConversationId" resultType="com.wecloud.im.vo.OfflineMsgDto">

        SELECT im_message.id         AS msgId,
               im_message.create_time,
               im_message.withdraw_time,
               im_message.update_date,
               `im_client`.client_id AS sender,
               im_message.content,
               im_message.withdraw,
               im_message.`event`,
               im_message.system_flag,
               im_message.`at`,
               im_message.`msg_type` AS 'type',
               im_message.send_status,
               im_message.fk_conversation_id
        FROM im_inbox
                 INNER JOIN im_message ON im_message.id = im_inbox.fk_msg_id
                 INNER JOIN im_client AS im_client ON im_client.id = im_message.sender
        WHERE im_inbox.receiver = #{clientId}
          AND im_inbox.fk_conversation_id = #{conversationId}
        ORDER BY create_time DESC
        LIMIT 1
    </select>

    <select id="getLastMsgByConversationId" resultType="com.wecloud.im.vo.OfflineMsgDto">
        SELECT im_message.id         AS msgId,
               im_message.create_time,
               im_message.withdraw_time,
               im_message.update_date,
               `im_client`.client_id AS sender,
               im_message.content,
               im_message.withdraw,
               im_message.`event`,
               im_message.system_flag,
               im_message.`at`,
               im_message.send_status,
               im_message.`msg_type` AS 'type',
               im_message.fk_conversation_id
        FROM im_message
                 INNER JOIN im_client AS im_client ON im_client.id = im_message.sender
        WHERE im_message.fk_conversation_id = #{conversationId}
          and (im_message.`event` = 0 || (im_message.`event` = 1 and sender != #{currentFkClientId} and
                                          (receivers is null ||
                                           (receivers != null and FIND_IN_SET(#{currentFkClientId}, receivers)))))

        ORDER BY create_time DESC
        LIMIT 1
    </select>

    <select id="getReaders" resultType="com.wecloud.im.vo.ReaderVo">
        SELECT client.id             as id,
               client.head_portrait  as headPortrait,
               client.nickname       as nickname,
               client.client_id      as clientId,
               inbox.read_msg_status as readMsgStatus
        FROM `im_inbox` inbox
                 LEFT JOIN im_client client on inbox.receiver = client.id
        WHERE inbox.fk_conversation_id = #{param.conversationId}
          and inbox.fk_msg_id = #{param.msgId}
          and inbox.receiver != #{currentClientId}
    </select>

</mapper>
