<?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, member_count, chat_type, fk_appid, creator, name, attributes,
        system_flag,muted, is_forbid_add_friend as forbid_add_friend,
        is_forbid_send_pic as forbid_send_pic, is_forbid_send_link as forbid_send_link
    </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.ConversationVo">
        select * from (SELECT imConversation.id as id,
        imConversation.create_time,
        imConversation.update_time,
        imConversation.`name`,
        imConversation.chat_type,
        imConversation.member_count,
        imConversation.attributes as attribute,
        imConversation.system_flag,
        imConversation.muted as muted,
        imConversation.is_forbid_add_friend as forbid_add_friend,
        imConversation.is_forbid_send_pic as forbid_send_pic,
        imConversation.is_forbid_send_link as forbid_send_link,
        imConversation.head_portrait,
        imConversation.is_encrypt isEncrypt,
        im_client.client_id AS creator,
        imConversationMembers.is_top as top,
        imConversationMembers.is_do_not_disturb as do_not_disturb,
        (SELECT COUNT(im_inbox.id)
        FROM im_inbox
        WHERE im_inbox.fk_conversation_id = imConversation.id
        AND im_inbox.receiver = #{currentClientId}
        AND im_inbox.read_msg_status = 0) AS msg_not_read_count,
        (SELECT COUNT(*)
        FROM im_inbox INNER JOIN im_message ON im_inbox.fk_msg_id = im_message.id
        WHERE im_inbox.fk_conversation_id = imConversation.id
        AND im_inbox.receiver = #{currentClientId}
        AND im_inbox.read_msg_status = 0
        AND (FIND_IN_SET(#{clientId},im_message.`at`) > 0 or FIND_IN_SET('-1',im_message.`at`) > 0)) AS beAtCount,
        (
        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 ON im_client.id = imConversation.creator
        WHERE imConversationMembers.fk_client_id = #{currentClientId}
        AND imConversationMembers.display_status = 1
        <if test="conversationId != null">
            AND imConversation.id = #{conversationId}
        </if>
        GROUP BY imConversation.id) t
        order by t.update_time desc

    </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>

    <select id="getRepetitionConversationSingle" resultType="com.wecloud.im.entity.ImConversation">
        -- 查询重复的单聊会话第一条
        select con.*
        from im_conversation con
                 inner join im_conversation_members mem1
                            on con.id = mem1.fk_conversation_id and mem1.fk_client_id = #{clientId1}
                 inner join im_conversation_members mem2
                            on con.id = mem2.fk_conversation_id and mem2.fk_client_id = #{clientId2}
        where con.chat_type in (1, 5, 6)
          and con.member_count = 2
          and con.is_encrypt = #{isEncrypt}
        order by con.id asc
        LIMIT 1
    </select>

    <select id="getRepetitionConversationAttributes" resultType="java.lang.Long">
        SELECT *
        FROM im_conversation
        WHERE id IN (
            SELECT im_conversation_members.fk_conversation_id
            FROM im_conversation_members
                     INNER JOIN (SELECT *
                                 FROM im_conversation_members
                                 WHERE im_conversation_members.fk_client_id = #{clientId2}) AS im_conversation_members2
                                ON im_conversation_members.fk_conversation_id =
                                   im_conversation_members2.fk_conversation_id
            WHERE im_conversation_members.fk_client_id = #{clientId1}
        )
          AND attributes = CAST(#{attributes} AS json)
        LIMIT 1
    </select>

    <update id="addMemberCount">
        update im_conversation
        set member_count = member_count + #{addCount}
        where fk_appid = #{appId}
          and id = #{conversationId}
    </update>

    <update id="updateMemberCount">
        update im_conversation
        set chat_type = 3
        where fk_appid = #{appId}
          and id = #{conversationId}
    </update>

    <select id="getConversationBySenderAndReceivers" resultType="com.wecloud.im.entity.ImConversation">
        SELECT
        c.id,
        c.create_time,
        c.update_time,
        c.last_message,
        c.member_count,
        c.chat_type,
        c.fk_appid,
        c.creator,
        c.name,
        c.attributes,
        c.system_flag,
        c.muted,
        c.is_forbid_add_friend as forbid_add_friend,
        c.is_forbid_send_pic as forbid_send_pic,
        c.is_forbid_send_link as forbid_send_link
        FROM
        im_conversation c
        LEFT JOIN im_conversation_members cm ON c.id = cm.fk_conversation_id
        WHERE
        c.chat_type = 1
        AND (
        (
        c.creator = #{senderImClientId} AND cm.fk_client_id IN
        <foreach collection="receiverImClientIds" item="id" index="index" open="(" close=")"
                 separator=",">
            #{id}
        </foreach>
        )
        OR (
        c.creator IN
        <foreach collection="receiverImClientIds" item="id" index="index" open="(" close=")"
                 separator=",">
            #{id}
        </foreach>
        AND cm.fk_client_id = #{senderImClientId}
        )
        )

    </select>

</mapper>
