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

    <!-- 基础结果映射 -->
    <resultMap id="BaseResultMap" type="org.xyzh.common.dto.ai.TbAiMessage">
        <id column="id" property="id" jdbcType="VARCHAR"/>
        <result column="conversation_id" property="conversationID" jdbcType="VARCHAR"/>
        <result column="user_id" property="userID" jdbcType="VARCHAR"/>
        <result column="agent_id" property="agentID" jdbcType="VARCHAR"/>
        <result column="role" property="role" jdbcType="VARCHAR"/>
        <result column="content" property="content" jdbcType="LONGVARCHAR"/>
        <result column="file_ids" property="fileIDs" jdbcType="VARCHAR"/>
        <result column="knowledge_ids" property="knowledgeIDs" jdbcType="VARCHAR"/>
        <result column="knowledge_refs" property="knowledgeRefs" jdbcType="LONGVARCHAR"/>
        <result column="token_count" property="tokenCount" jdbcType="INTEGER"/>
        <result column="dify_message_id" property="difyMessageId" jdbcType="VARCHAR"/>
        <result column="rating" property="rating" jdbcType="INTEGER"/>
        <result column="feedback" property="feedback" jdbcType="VARCHAR"/>
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
        <result column="update_time" property="updateTime" jdbcType="TIMESTAMP"/>
    </resultMap>

    <!-- 基础字段 -->
    <sql id="Base_Column_List">
        id, conversation_id, user_id, agent_id, role, content, file_ids, knowledge_ids, 
        knowledge_refs, token_count, dify_message_id, rating, feedback, 
        create_time, update_time
    </sql>

    <!-- 通用条件 -->
    <sql id="Where_Clause">
        <where>
            <if test="conversationID != null and conversationID != ''">
                AND conversation_id = #{conversationID}
            </if>
            <if test="userID != null and userID != ''">
                AND user_id = #{userID}
            </if>
            <if test="role != null and role != ''">
                AND role = #{role}
            </if>
        </where>
    </sql>

    <!-- 插入消息 -->
    <insert id="insertMessage" parameterType="org.xyzh.common.dto.ai.TbAiMessage">
        INSERT INTO tb_ai_message (
            id, conversation_id, user_id, agent_id, role, content,
            file_ids, knowledge_ids, knowledge_refs, token_count,
            dify_message_id, rating, feedback, create_time, update_time, deleted
        ) VALUES (
            #{ID}, #{conversationID}, #{userID}, #{agentID}, #{role}, #{content},
            #{fileIDs}, #{knowledgeIDs}, #{knowledgeRefs}, #{tokenCount},
            #{difyMessageId}, #{rating}, #{feedback}, #{createTime}, #{updateTime}, #{deleted}
        )
    </insert>

    <!-- 更新消息（动态更新非null字段） -->
    <update id="updateMessage" parameterType="org.xyzh.common.dto.ai.TbAiMessage">
        UPDATE tb_ai_message
        <set>
            <if test="content != null">content = #{content},</if>
            <if test="fileIDs != null">file_ids = #{fileIDs},</if>
            <if test="knowledgeIDs != null">knowledge_ids = #{knowledgeIDs},</if>
            <if test="knowledgeRefs != null">knowledge_refs = #{knowledgeRefs},</if>
            <if test="tokenCount != null">token_count = #{tokenCount},</if>
            <if test="difyMessageId != null">dify_message_id = #{difyMessageId},</if>
            <if test="rating != null">rating = #{rating},</if>
            <if test="feedback != null">feedback = #{feedback},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
        </set>
        WHERE id = #{ID} AND deleted = 0
    </update>

    <!-- 逻辑删除消息 -->
    <update id="deleteMessage" parameterType="org.xyzh.common.dto.ai.TbAiMessage">
        UPDATE tb_ai_message
        SET deleted = 1,
            delete_time = NOW()
        WHERE id = #{ID} AND deleted = 0
    </update>

    <!-- 根据ID查询消息 -->
    <select id="selectMessageById" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM tb_ai_message
        WHERE id = #{messageId} AND deleted = 0
    </select>

    <!-- 根据会话ID查询消息列表（按时间正序） -->
    <select id="selectMessagesByConversationId" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM tb_ai_message
        WHERE conversation_id = #{conversationId}
          AND deleted = 0
        ORDER BY create_time ASC
    </select>

    <!-- 统计会话的消息数量 -->
    <select id="countConversationMessages" resultType="java.lang.Long">
        SELECT COUNT(1)
        FROM tb_ai_message
        WHERE conversation_id = #{conversationId}
          AND deleted = 0
    </select>

    <!-- 查询会话的最后一条消息 -->
    <select id="selectLastMessage" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM tb_ai_message
        WHERE conversation_id = #{conversationId}
          AND deleted = 0
        ORDER BY create_time DESC
        LIMIT 1
    </select>

    <!-- 搜索消息内容（全文搜索） -->
    <select id="searchMessagesByContent" resultMap="BaseResultMap">
        SELECT m.*
        FROM tb_ai_message m
        INNER JOIN tb_ai_conversation c ON m.conversation_id = c.id
        WHERE c.user_id = #{userId}
          AND m.content LIKE CONCAT('%', #{keyword}, '%')
        <if test="conversationId != null and conversationId != ''">
            AND m.conversation_id = #{conversationId}
        </if>
          AND m.deleted = 0
          AND c.deleted = 0
        ORDER BY m.create_time DESC
        LIMIT #{pageParam.offset}, #{pageParam.pageSize}
    </select>

    <!-- 统计搜索消息数量 -->
    <select id="countSearchMessages" resultType="java.lang.Long">
        SELECT COUNT(1)
        FROM tb_ai_message m
        INNER JOIN tb_ai_conversation c ON m.conversation_id = c.id
        WHERE c.user_id = #{userId}
          AND m.content LIKE CONCAT('%', #{keyword}, '%')
        <if test="conversationId != null and conversationId != ''">
            AND m.conversation_id = #{conversationId}
        </if>
          AND m.deleted = 0
          AND c.deleted = 0
    </select>

    <!-- 统计会话的评分分布 -->
    <select id="countMessageRatings" resultType="java.util.HashMap">
        SELECT
            rating,
            COUNT(1) as count
        FROM tb_ai_message
        WHERE conversation_id = #{conversationId}
          AND rating IS NOT NULL
          AND deleted = 0
        GROUP BY rating
    </select>

    <!-- selectAiMessages -->
    <select id="selectAiMessages" resultMap="BaseResultMap">
        SELECT 
        <include refid="Base_Column_List"/>
        FROM tb_ai_message
        <include refid="Where_Clause"/>
        AND deleted = 0
        ORDER BY create_time ASC
    </select>

</mapper>
