<?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_new 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_new
    </select>
    <select id="getOfflineListByClientAndConversation" resultType="com.wecloud.im.vo.OfflineMsgDto">
        SELECT im_message_new.id                   AS msgId,
               im_message_new.create_time,
               im_message_new.withdraw_time,
               im_message_new.update_date,
               `im_client`.client_id           AS sender,
               im_message_new.content,
               im_message_new.withdraw,
               im_message_new.`event`,
               im_message_new.system_flag,
               im_message_new.`at`,
               im_message_new.send_status,
               im_message_new.fk_conversation_id,
               im_message_new.`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_new im_message_new ON im_message_new.id = im_inbox.fk_msg_id
                 INNER JOIN `im_client` ON `im_client`.id = `im_message_new`.sender
        WHERE im_inbox.fk_conversation_id = #{conversationId}
          AND im_inbox.receiver = #{clientId}
          AND im_inbox.receiver_msg_status = 0
          AND im_message_new.is_delete = 1
          AND im_message_new.withdraw = 0
    </select>
    <select id="getHistoryMsgConversationId" resultType="com.wecloud.im.vo.OfflineMsgDto">
        SELECT im_message_new.id AS msgId,
        im_message_new.pre_message_id AS preMessageId,
        im_message_new.create_time,
        im_message_new.withdraw_time,
        im_message_new.update_date,
        `im_client`.client_id AS sender,
        im_message_new.content,
        im_message_new.withdraw,
        im_message_new.`event`,
        im_message_new.system_flag,
        im_message_new.`at`,
        im_message_new.send_status,
        im_message_new.`msg_type` AS 'type',
        im_message_new.fk_conversation_id as conversationId
        FROM `im_message_new`
        INNER JOIN `im_client` ON `im_client`.id = `im_message_new`.sender
        WHERE fk_conversation_id = #{param.conversationId}
        and (im_message_new.`event`=0 || (im_message_new.`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_new.id > #{param.msgIdStart}
        </if>
        <if test="param.msgIdEnd != null">
            AND im_message_new.id <![CDATA[ < ]]> #{param.msgIdEnd}
        </if>

        ORDER BY `im_message_new`.`id` DESC
    </select>


    <select id="getHistoryMsgConversationIdNew" resultType="com.wecloud.im.vo.OfflineMsgDto">
        SELECT im_message_new.id AS msgId,
        im_message_new.create_time,
        im_message_new.withdraw_time,
        im_message_new.update_date,
        `im_client`.client_id AS sender,
        im_message_new.content,
        im_message_new.withdraw,
        im_message_new.`event`,
        im_message_new.system_flag,
        im_message_new.`at`,
        im_message_new.send_status,
        im_message_new.`msg_type` AS 'type',
        im_message_new.fk_conversation_id as conversationId
        FROM `im_message_new`
        INNER JOIN `im_client` ON `im_client`.id = `im_message_new`.sender
        WHERE fk_conversation_id = #{param.conversationId}
        and (im_message_new.`event`=0 || (im_message_new.`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_new.id > #{param.msgIdStart}
        </if>
        <if test="param.getLast != null and param.getLast == 1">
            AND im_message_new.id <![CDATA[ < ]]> #{param.msgIdEnd}
        </if>
        <if test="param.getLast != null and param.getLast == 0">
            AND im_message_new.id <![CDATA[ <= ]]> #{param.msgIdEnd}
        </if>
        <if test="param.getLast == null">
            AND im_message_new.id <![CDATA[ < ]]> #{param.msgIdEnd}
        </if>

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


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

        SELECT im_message_new.id         AS msgId,
               im_message_new.create_time,
               im_message_new.withdraw_time,
               im_message_new.update_date,
               `im_client`.client_id AS sender,
               im_message_new.content,
               im_message_new.withdraw,
               im_message_new.`event`,
               im_message_new.system_flag,
               im_message_new.`at`,
               im_message_new.`msg_type` AS 'type',
               im_message_new.send_status,
               im_message_new.fk_conversation_id
        FROM im_inbox
                 INNER JOIN im_message_new ON im_message_new.id = im_inbox.fk_msg_id
                 INNER JOIN im_client AS im_client ON im_client.id = im_message_new.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_new.id         AS msgId,
               im_message_new.create_time,
               im_message_new.withdraw_time,
               im_message_new.update_date,
               `im_client`.client_id AS sender,
               im_message_new.content,
               im_message_new.withdraw,
               im_message_new.`event`,
               im_message_new.system_flag,
               im_message_new.`at`,
               im_message_new.send_status,
               im_message_new.`msg_type` AS 'type',
               im_message_new.fk_conversation_id,
               im_message_new.pre_message_id AS 'preMessageId'
        FROM im_message_new
                 INNER JOIN im_client AS im_client ON im_client.id = im_message_new.sender
        WHERE im_message_new.fk_conversation_id = #{conversationId}
          and (im_message_new.`event` = 0 || (im_message_new.`event` = 1 and sender != #{currentFkClientId} and
                                          (receivers is null ||
                                           (receivers != null and FIND_IN_SET(#{currentFkClientId}, receivers)))))

        ORDER BY im_message_new.id 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>
