<?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
    </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,
               (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}
        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>

</mapper>
