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

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id
        , create_time, update_time, last_message, fk_appid, creator, name, attributes, system
    </sql>

    <select id="getImConversationById" resultType="com.wecloud.im.param.ImConversationQueryVo">
        select
        <include refid="Base_Column_List"/>
        from im_conversation where id = #{id}
    </select>

    <select id="getImConversationPageList" parameterType="com.wecloud.im.param.ImConversationPageParam"
            resultType="com.wecloud.im.param.ImConversationQueryVo">
        select
        <include refid="Base_Column_List"/>
        from im_conversation
    </select>
    <select id="getMyImConversationListAndMsgCount" resultType="com.wecloud.im.vo.MyConversationListVo">
        SELECT imConversation.id,
               imConversation.create_time,
               imConversation.`name`,
               imConversation.attributes     as attribute,
               imConversation.system,
               im_client.client_id           AS creator,
               (SELECT COUNT(im_inbox.id)
                FROM im_inbox
                WHERE im_inbox.fk_conversation_id = imConversation.id
                  AND im_inbox.receiver = #{clientId}
                  AND im_inbox.read_msg = 0) AS msg_not_read_count,
               (
                   SELECT GROUP_CONCAT(im_client.client_id)
                   FROM im_conversation_members AS im_conversation_members
                            INNER JOIN im_client AS im_client ON im_client.id = im_conversation_members.fk_client_id
                   WHERE im_conversation_members.fk_conversation_id = imConversation.id
               )                             AS members
        FROM im_conversation_members AS imConversationMembers
                 INNER JOIN im_conversation AS imConversation
                            ON imConversation.id = imConversationMembers.fk_conversation_id
                 INNER JOIN im_client AS im_client ON im_client.id = imConversation.creator
        WHERE imConversationMembers.fk_client_id = #{clientId}
        GROUP BY imConversation.id
    </select>

    <select id="getMyImConversationList" resultType="com.wecloud.im.entity.ImConversation">
        SELECT imConversation.*
        FROM im_conversation_members AS imConversationMembers
                 INNER JOIN im_conversation imConversation
                            ON imConversation.id = imConversationMembers.fk_conversation_id
        WHERE imConversationMembers.fk_client_id = #{clientId}
    </select>

</mapper>
