<?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="org.xyzh.ai.mapper.AiConversationMapper">

    <!-- 基础结果映射 -->
    <resultMap id="BaseResultMap" type="org.xyzh.common.dto.ai.TbAiConversation">
        <id column="id" property="id" jdbcType="VARCHAR"/>
        <result column="user_id" property="userID" jdbcType="VARCHAR"/>
        <result column="agent_id" property="agentID" jdbcType="VARCHAR"/>
        <result column="title" property="title" jdbcType="VARCHAR"/>
        <result column="summary" property="summary" jdbcType="VARCHAR"/>
        <result column="dify_conversation_id" property="difyConversationId" jdbcType="VARCHAR"/>
        <result column="status" property="status" jdbcType="INTEGER"/>
        <result column="is_favorite" property="isFavorite" jdbcType="BOOLEAN"/>
        <result column="is_pinned" property="isPinned" jdbcType="BOOLEAN"/>
        <result column="message_count" property="messageCount" jdbcType="INTEGER"/>
        <result column="total_tokens" property="totalTokens" jdbcType="INTEGER"/>
        <result column="last_message_time" property="lastMessageTime" jdbcType="TIMESTAMP"/>
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
        <result column="update_time" property="updateTime" jdbcType="TIMESTAMP"/>
    </resultMap>

    <!-- 基础字段 -->
    <sql id="Base_Column_List">
        id, user_id, agent_id, title, summary, dify_conversation_id, status, 
        is_favorite, is_pinned, message_count, total_tokens, last_message_time, 
        create_time, update_time
    </sql>

    <!-- 通用条件 -->
    <sql id="Where_Clause">
        <where>
            <if test="userID != null and userID != ''">
                AND user_id = #{userID}
            </if>
            <if test="agentID != null and agentID != ''">
                AND agent_id = #{agentID}
            </if>
            <if test="title != null and title != ''">
                AND title LIKE CONCAT('%', #{title}, '%')
            </if>
            <if test="difyConversationId != null and difyConversationId != ''">
                AND dify_conversation_id = #{difyConversationId}
            </if>
            <if test="status != null">
                AND status = #{status}
            </if>
            <if test="isFavorite != null">
                AND is_favorite = #{isFavorite}
            </if>
            <if test="isPinned != null">
                AND is_pinned = #{isPinned}
            </if>
        </where>
    </sql>

    <!-- 插入会话 -->
    <insert id="insertConversation" parameterType="org.xyzh.common.dto.ai.TbAiConversation">
        INSERT INTO tb_ai_conversation (
            id, user_id, agent_id, title, summary, dify_conversation_id,
            status, is_favorite, is_pinned, message_count, total_tokens,
            last_message_time, create_time, update_time, deleted
        ) VALUES (
            #{ID}, #{userID}, #{agentID}, #{title}, #{summary}, #{difyConversationId},
            #{status}, #{isFavorite}, #{isPinned}, #{messageCount}, #{totalTokens},
            #{lastMessageTime}, #{createTime}, #{updateTime}, #{deleted}
        )
    </insert>

    <!-- 更新会话（动态更新非null字段） -->
    <update id="updateConversation" parameterType="org.xyzh.common.dto.ai.TbAiConversation">
        UPDATE tb_ai_conversation
        <set>
            <if test="title != null">title = #{title},</if>
            <if test="summary != null">summary = #{summary},</if>
            <if test="difyConversationId != null">dify_conversation_id = #{difyConversationId},</if>
            <if test="status != null">status = #{status},</if>
            <if test="isFavorite != null">is_favorite = #{isFavorite},</if>
            <if test="isPinned != null">is_pinned = #{isPinned},</if>
            <if test="messageCount != null">message_count = #{messageCount},</if>
            <if test="totalTokens != null">total_tokens = #{totalTokens},</if>
            <if test="lastMessageTime != null">last_message_time = #{lastMessageTime},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
        </set>
        WHERE id = #{ID} AND deleted = 0
    </update>

    <!-- 逻辑删除会话 -->
    <update id="deleteConversation" parameterType="org.xyzh.common.dto.ai.TbAiConversation">
        DELETE FROM tb_ai_conversation WHERE id = #{ID}
    </update>

    <!-- 根据ID查询会话 -->
    <select id="selectConversationById" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM tb_ai_conversation
        WHERE id = #{conversationId} AND deleted = 0
    </select>

    <!-- 根据用户ID查询会话列表 -->
    <select id="selectConversationsByUserId" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM tb_ai_conversation
        WHERE user_id = #{userId}
        <if test="agentId != null and agentId != ''">
            AND agent_id = #{agentId}
        </if>
        AND deleted = 0
        ORDER BY is_pinned DESC, last_message_time DESC, create_time DESC
    </select>

    <!-- 统计用户的会话数量 -->
    <select id="countUserConversations" resultType="java.lang.Long">
        SELECT COUNT(1)
        FROM tb_ai_conversation
        WHERE user_id = #{userId}
          AND deleted = 0
    </select>

    <!-- 分页查询用户会话（支持关键词、日期范围、收藏筛选） -->
    <select id="selectUserConversationsPage" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM tb_ai_conversation
        WHERE user_id = #{userId}
        <if test="agentId != null and agentId != ''">
            AND agent_id = #{agentId}
        </if>
        <if test="keyword != null and keyword != ''">
            AND (title LIKE CONCAT('%', #{keyword}, '%') OR summary LIKE CONCAT('%', #{keyword}, '%'))
        </if>
        <if test="isFavorite != null">
            AND is_favorite = #{isFavorite}
        </if>
        <if test="startDate != null">
            AND create_time &gt;= #{startDate}
        </if>
        <if test="endDate != null">
            AND create_time &lt;= #{endDate}
        </if>
        AND deleted = 0
        ORDER BY is_pinned DESC, last_message_time DESC, create_time DESC
        LIMIT #{pageParam.offset}, #{pageParam.pageSize}
    </select>

    <!-- 统计查询条件下的会话数量 -->
    <select id="countUserConversationsWithFilter" resultType="java.lang.Long">
        SELECT COUNT(1)
        FROM tb_ai_conversation
        WHERE user_id = #{userId}
        <if test="agentId != null and agentId != ''">
            AND agent_id = #{agentId}
        </if>
        <if test="keyword != null and keyword != ''">
            AND (title LIKE CONCAT('%', #{keyword}, '%') OR summary LIKE CONCAT('%', #{keyword}, '%'))
        </if>
        <if test="isFavorite != null">
            AND is_favorite = #{isFavorite}
        </if>
        <if test="startDate != null">
            AND create_time &gt;= #{startDate}
        </if>
        <if test="endDate != null">
            AND create_time &lt;= #{endDate}
        </if>
        AND deleted = 0
    </select>

    <!-- 搜索会话（标题和摘要全文搜索） -->
    <select id="searchConversationsByKeyword" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM tb_ai_conversation
        WHERE user_id = #{userId}
          AND (title LIKE CONCAT('%', #{keyword}, '%') OR summary LIKE CONCAT('%', #{keyword}, '%'))
          AND deleted = 0
        ORDER BY last_message_time DESC, create_time DESC
        LIMIT #{pageParam.offset}, #{pageParam.pageSize}
    </select>

    <!-- 统计搜索结果数量 -->
    <select id="countSearchConversations" resultType="java.lang.Long">
        SELECT COUNT(1)
        FROM tb_ai_conversation
        WHERE user_id = #{userId}
          AND (title LIKE CONCAT('%', #{keyword}, '%') OR summary LIKE CONCAT('%', #{keyword}, '%'))
          AND deleted = 0
    </select>

    <!-- 批量更新会话状态 -->
    <update id="batchUpdateConversations">
        UPDATE tb_ai_conversation
        SET deleted = #{deleted},
            delete_time = NOW()
        WHERE id IN
        <foreach collection="ids" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>

    <!-- 查询用户最近的会话 -->
    <select id="selectRecentConversations" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM tb_ai_conversation
        WHERE user_id = #{userId}
          AND deleted = 0
        ORDER BY last_message_time DESC, create_time DESC
        LIMIT #{limit}
    </select>

    <!-- 查询热门会话（按消息数排序） -->
    <select id="selectPopularConversations" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM tb_ai_conversation
        WHERE user_id = #{userId}
          AND deleted = 0
        ORDER BY message_count DESC, total_tokens DESC
        LIMIT #{limit}
    </select>

    <!-- 查询过期会话ID列表 -->
    <select id="selectExpiredConversationIds" resultType="java.lang.String">
        SELECT id
        FROM tb_ai_conversation
        WHERE user_id = #{userId}
          AND create_time &lt; #{beforeDate}
          AND deleted = 0
    </select>

    <!-- selectAiConversations -->
    <select id="selectAiConversations" resultMap="BaseResultMap">
        SELECT 
        <include refid="Base_Column_List"/>
        FROM tb_ai_conversation
        <include refid="Where_Clause"/>
        AND deleted = 0
        ORDER BY last_message_time DESC, create_time DESC
    </select>

</mapper>
