<?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.message.mapper.MessageMapper">

    <!-- Result Map -->
    <resultMap id="BaseResultMap" type="org.xyzh.common.dto.message.TbSysMessage">
        <id column="id" property="id" jdbcType="VARCHAR"/>
        <result column="message_id" property="messageID" jdbcType="VARCHAR"/>
        <result column="title" property="title" jdbcType="VARCHAR"/>
        <result column="content" property="content" jdbcType="VARCHAR"/>
        <result column="message_type" property="messageType" jdbcType="VARCHAR"/>
        <result column="priority" property="priority" jdbcType="VARCHAR"/>
        <result column="sender_id" property="senderID" jdbcType="VARCHAR"/>
        <result column="sender_name" property="senderName" jdbcType="VARCHAR"/>
        <result column="sender_dept_id" property="senderDeptID" jdbcType="VARCHAR"/>
        <result column="sender_dept_name" property="senderDeptName" jdbcType="VARCHAR"/>
        <result column="send_mode" property="sendMode" jdbcType="VARCHAR"/>
        <result column="scheduled_time" property="scheduledTime" jdbcType="TIMESTAMP"/>
        <result column="actual_send_time" property="actualSendTime" jdbcType="TIMESTAMP"/>
        <result column="status" property="status" jdbcType="VARCHAR"/>
        <result column="target_user_count" property="targetUserCount" jdbcType="INTEGER"/>
        <result column="sent_count" property="sentCount" jdbcType="INTEGER"/>
        <result column="success_count" property="successCount" jdbcType="INTEGER"/>
        <result column="failed_count" property="failedCount" jdbcType="INTEGER"/>
        <result column="read_count" property="readCount" jdbcType="INTEGER"/>
        <result column="retry_count" property="retryCount" jdbcType="INTEGER"/>
        <result column="max_retry_count" property="maxRetryCount" jdbcType="INTEGER"/>
        <result column="last_error" property="lastError" jdbcType="VARCHAR"/>
        <result column="creator" property="creator" jdbcType="VARCHAR"/>
        <result column="updater" property="updater" jdbcType="VARCHAR"/>
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
        <result column="update_time" property="updateTime" jdbcType="TIMESTAMP"/>
        <result column="delete_time" property="deleteTime" jdbcType="TIMESTAMP"/>
        <result column="deleted" property="deleted" jdbcType="BOOLEAN"/>
    </resultMap>

    <resultMap id="MessageVOMap" type="org.xyzh.common.dto.message.MessageVO">
        <id column="id" property="id" jdbcType="VARCHAR"/>
        <result column="message_id" property="messageID" jdbcType="VARCHAR"/>
        <result column="title" property="title" jdbcType="VARCHAR"/>
        <result column="content" property="content" jdbcType="VARCHAR"/>
        <result column="message_type" property="messageType" jdbcType="VARCHAR"/>
        <result column="priority" property="priority" jdbcType="VARCHAR"/>
        <result column="sender_id" property="senderID" jdbcType="VARCHAR"/>
        <result column="sender_name" property="senderName" jdbcType="VARCHAR"/>
        <result column="sender_dept_id" property="senderDeptID" jdbcType="VARCHAR"/>
        <result column="sender_dept_name" property="senderDeptName" jdbcType="VARCHAR"/>
        <result column="send_mode" property="sendMode" jdbcType="VARCHAR"/>
        <result column="scheduled_time" property="scheduledTime" jdbcType="TIMESTAMP"/>
        <result column="actual_send_time" property="actualSendTime" jdbcType="TIMESTAMP"/>
        <result column="status" property="status" jdbcType="VARCHAR"/>
        <result column="target_user_count" property="targetUserCount" jdbcType="INTEGER"/>
        <result column="sent_count" property="sentCount" jdbcType="INTEGER"/>
        <result column="success_count" property="successCount" jdbcType="INTEGER"/>
        <result column="failed_count" property="failedCount" jdbcType="INTEGER"/>
        <result column="read_count" property="readCount" jdbcType="INTEGER"/>
        <result column="retry_count" property="retryCount" jdbcType="INTEGER"/>
        <result column="max_retry_count" property="maxRetryCount" jdbcType="INTEGER"/>
        <result column="last_error" property="lastError" jdbcType="VARCHAR"/>
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
        <result column="update_time" property="updateTime" jdbcType="TIMESTAMP"/>
    </resultMap>

    <!-- 分页查询消息列表（带权限过滤） -->
    <select id="selectMessagePage" resultMap="MessageVOMap">
        SELECT
            m.*
        FROM tb_sys_message m
        WHERE m.deleted = 0
        <if test="filter != null">
            <if test="filter.title != null and filter.title != ''">
                AND m.title LIKE CONCAT('%', #{filter.title}, '%')
            </if>
            <if test="filter.messageType != null and filter.messageType != ''">
                AND m.message_type = #{filter.messageType}
            </if>
            <if test="filter.status != null and filter.status != ''">
                AND m.status = #{filter.status}
            </if>
            <if test="filter.sendMode != null and filter.sendMode != ''">
                AND m.send_mode = #{filter.sendMode}
            </if>
            <if test="filter.priority != null and filter.priority != ''">
                AND m.priority = #{filter.priority}
            </if>
        </if>
        <!-- 权限过滤：只能查看自己部门及子部门的消息 -->
        AND (
            m.sender_dept_id = #{currentUserDeptID}
            OR m.sender_dept_id IN (
                WITH RECURSIVE dept_tree AS (
                    SELECT dept_id FROM tb_sys_dept
                    WHERE dept_id = #{currentUserDeptID} AND deleted = 0
                    UNION ALL
                    SELECT d.dept_id FROM tb_sys_dept d
                    INNER JOIN dept_tree dt ON d.parent_id = dt.dept_id
                    WHERE d.deleted = 0
                )
                SELECT dept_id FROM dept_tree
            )
        )
        ORDER BY m.create_time DESC
    </select>

    <!-- 查询消息详情 -->
    <select id="selectMessageDetail" resultMap="MessageVOMap">
        SELECT *
        FROM tb_sys_message
        WHERE message_id = #{messageID}
        AND deleted = 0
    </select>

    <!-- 查询待发送的定时消息 -->
    <select id="selectPendingScheduledMessages" resultMap="BaseResultMap">
        SELECT *
        FROM tb_sys_message
        WHERE status = 'pending'
        AND send_mode = 'scheduled'
        AND scheduled_time <![CDATA[ <= ]]> #{currentTime}
        AND deleted = 0
        ORDER BY scheduled_time ASC
        LIMIT 100
    </select>

    <!-- CAS更新消息状态 -->
    <update id="compareAndSetStatus">
        UPDATE tb_sys_message
        SET status = #{newStatus},
            update_time = NOW()
        WHERE message_id = #{messageID}
        AND status = #{expectedStatus}
        AND deleted = 0
    </update>

    <!-- 更新消息统计信息 -->
    <update id="updateStatistics">
        UPDATE tb_sys_message
        SET sent_count = #{sentCount},
            success_count = #{successCount},
            failed_count = #{failedCount},
            update_time = NOW()
        WHERE message_id = #{messageID}
        AND deleted = 0
    </update>

    <!-- 更新已读数量 -->
    <update id="incrementReadCount">
        UPDATE tb_sys_message
        SET read_count = read_count + 1,
            update_time = NOW()
        WHERE message_id = #{messageID}
        AND deleted = 0
    </update>

    <!-- 根据消息ID查询消息 -->
    <select id="selectMessageById" resultMap="BaseResultMap">
        SELECT *
        FROM tb_sys_message
        WHERE message_id = #{messageID}
        AND deleted = 0
    </select>

    <!-- 插入消息 -->
    <insert id="insertMessage">
        INSERT INTO tb_sys_message
        (id, message_id, title, content, message_type, priority, sender_id, sender_dept_id,
         send_mode, scheduled_time, status, target_user_count, retry_count, max_retry_count,
         creator, create_time, update_time, deleted)
        VALUES
        (#{message.id}, #{message.messageID}, #{message.title}, #{message.content},
         #{message.messageType}, #{message.priority}, #{message.senderID}, #{message.senderDeptID},
         #{message.sendMode}, #{message.scheduledTime}, #{message.status}, #{message.targetUserCount},
         #{message.retryCount}, #{message.maxRetryCount}, #{message.creator}, #{message.createTime},
         #{message.updateTime}, #{message.deleted})
    </insert>

    <!-- 更新消息 -->
    <update id="updateMessage">
        UPDATE tb_sys_message
        <set>
            <if test="message.title != null and message.title != ''">
                title = #{message.title},
            </if>
            <if test="message.content != null">
                content = #{message.content},
            </if>
            <if test="message.messageType != null and message.messageType != ''">
                message_type = #{message.messageType},
            </if>
            <if test="message.priority != null and message.priority != ''">
                priority = #{message.priority},
            </if>
            <if test="message.sendMode != null and message.sendMode != ''">
                send_mode = #{message.sendMode},
            </if>
            <if test="message.scheduledTime != null">
                scheduled_time = #{message.scheduledTime},
            </if>
            <if test="message.actualSendTime != null">
                actual_send_time = #{message.actualSendTime},
            </if>
            <if test="message.status != null and message.status != ''">
                status = #{message.status},
            </if>
            <if test="message.targetUserCount != null">
                target_user_count = #{message.targetUserCount},
            </if>
            <if test="message.retryCount != null">
                retry_count = #{message.retryCount},
            </if>
            <if test="message.maxRetryCount != null">
                max_retry_count = #{message.maxRetryCount},
            </if>
            <if test="message.lastError != null">
                last_error = #{message.lastError},
            </if>
            <if test="message.updater != null and message.updater != ''">
                updater = #{message.updater},
            </if>
            <if test="message.updateTime != null">
                update_time = #{message.updateTime},
            </if>
        </set>
        WHERE message_id = #{message.messageID}
        AND deleted = 0
    </update>

    <!-- 删除消息（逻辑删除） -->
    <update id="deleteMessage">
        UPDATE tb_sys_message
        SET deleted = 1,
            delete_time = NOW(),
            update_time = NOW()
        WHERE message_id = #{messageID}
        AND deleted = 0
    </update>

    <!-- 统计消息总数（带权限过滤） -->
    <select id="countMessage" resultType="java.lang.Integer">
        SELECT COUNT(*)
        FROM tb_sys_message m
        WHERE m.deleted = 0
        <if test="filter != null">
            <if test="filter.title != null and filter.title != ''">
                AND m.title LIKE CONCAT('%', #{filter.title}, '%')
            </if>
            <if test="filter.messageType != null and filter.messageType != ''">
                AND m.message_type = #{filter.messageType}
            </if>
            <if test="filter.status != null and filter.status != ''">
                AND m.status = #{filter.status}
            </if>
            <if test="filter.sendMode != null and filter.sendMode != ''">
                AND m.send_mode = #{filter.sendMode}
            </if>
            <if test="filter.priority != null and filter.priority != ''">
                AND m.priority = #{filter.priority}
            </if>
        </if>
        <!-- 权限过滤：只能查看自己部门及子部门的消息 -->
        AND (
            m.sender_dept_id = #{currentUserDeptID}
            OR m.sender_dept_id IN (
                WITH RECURSIVE dept_tree AS (
                    SELECT dept_id FROM tb_sys_dept
                    WHERE dept_id = #{currentUserDeptID} AND deleted = 0
                    UNION ALL
                    SELECT d.dept_id FROM tb_sys_dept d
                    INNER JOIN dept_tree dt ON d.parent_id = dt.dept_id
                    WHERE d.deleted = 0
                )
                SELECT dept_id FROM dept_tree
            )
        )
    </select>

</mapper>
