<?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} ))
        <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="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}
        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>
