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

    <!-- Result Map -->
    <resultMap id="BaseResultMap" type="org.xyzh.common.dto.message.TbSysMessageUser">
        <id column="id" property="id" jdbcType="VARCHAR"/>
        <result column="message_id" property="messageID" jdbcType="VARCHAR"/>
        <result column="user_id" property="userID" jdbcType="VARCHAR"/>
        <result column="send_method" property="sendMethod" jdbcType="VARCHAR"/>
        <result column="is_read" property="isRead" jdbcType="BOOLEAN"/>
        <result column="read_time" property="readTime" jdbcType="TIMESTAMP"/>
        <result column="send_status" property="sendStatus" jdbcType="VARCHAR"/>
        <result column="fail_reason" property="failReason" 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="MessageUserVOMap" type="org.xyzh.common.dto.message.MessageUserVO">
        <id column="id" property="id" jdbcType="VARCHAR"/>
        <result column="message_id" property="messageID" jdbcType="VARCHAR"/>
        <result column="user_id" property="userID" jdbcType="VARCHAR"/>
        <result column="username" property="username" jdbcType="VARCHAR"/>
        <result column="full_name" property="fullName" jdbcType="VARCHAR"/>
        <result column="dept_id" property="deptID" jdbcType="VARCHAR"/>
        <result column="name" property="deptName" jdbcType="VARCHAR"/>
        <result column="send_method" property="sendMethod" jdbcType="VARCHAR"/>
        <result column="is_read" property="isRead" jdbcType="BOOLEAN"/>
        <result column="read_time" property="readTime" jdbcType="TIMESTAMP"/>
        <result column="send_status" property="sendStatus" jdbcType="VARCHAR"/>
        <result column="fail_reason" property="failReason" jdbcType="VARCHAR"/>
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
        <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_name" property="senderName" jdbcType="VARCHAR"/>
        <result column="sender_dept_name" property="senderDeptName" jdbcType="VARCHAR"/>
        <result column="actual_send_time" property="actualSendTime" jdbcType="TIMESTAMP"/>
    </resultMap>

    <!-- 根据消息ID查询用户消息列表 -->
    <select id="selectByMessageID" resultMap="MessageUserVOMap">
        SELECT
            mu.*,
            u.username,
            ui.full_name as full_name,
            d.dept_id as dept_id,
            d.name as deptName
        FROM tb_sys_message_user mu
        LEFT JOIN tb_sys_user u ON mu.user_id = u.id
        LEFT JOIN tb_sys_user_info ui ON u.id = ui.user_id
        LEFT JOIN tb_sys_user_dept_role udr ON u.id = udr.user_id AND udr.deleted = 0
        LEFT JOIN tb_sys_dept d ON udr.dept_id = d.dept_id AND d.deleted = 0
        WHERE mu.message_id = #{messageID}
        AND mu.deleted = 0
        ORDER BY mu.create_time DESC
    </select>

    <!-- 批量插入用户消息 -->
    <insert id="batchInsert">
        INSERT INTO tb_sys_message_user
        (id, message_id, user_id, send_method, is_read, send_status, creator, create_time, update_time, deleted)
        VALUES
        <foreach collection="userMessages" item="item" separator=",">
            (#{item.id}, #{item.messageID}, #{item.userID}, #{item.sendMethod},
             0, 'pending', #{item.creator}, NOW(), NOW(), 0)
        </foreach>
    </insert>

    <!-- 分页查询当前用户的消息列表 -->
    <select id="selectMyMessages" resultMap="MessageUserVOMap">
        SELECT
            mu.*,
            m.title,
            m.content,
            m.message_type as message_type,
            m.priority,
            m.sender_name as sender_name,
            m.sender_dept_name as sender_dept_name,
            m.actual_send_time as actual_send_time
        FROM tb_sys_message_user mu
        INNER JOIN tb_sys_message m ON mu.message_id = m.message_id
        WHERE mu.user_id = #{userID}
        AND mu.deleted = 0
        AND m.deleted = 0
        <if test="filter != null">
            <if test="filter.isRead != null">
                AND mu.is_read = #{filter.isRead}
            </if>
            <if test="filter.sendMethod != null and filter.sendMethod != ''">
                AND mu.send_method = #{filter.sendMethod}
            </if>
        </if>
        ORDER BY m.actual_send_time DESC, mu.create_time DESC
    </select>

    <!-- 查询当前用户的消息详情 -->
    <select id="selectMyMessageDetail" resultMap="MessageUserVOMap">
        SELECT
            mu.*,
            m.title,
            m.content,
            m.message_type as message_type,
            m.priority,
            m.sender_name as sender_name,
            m.sender_dept_name as sender_dept_name,
            m.actual_send_time as actual_send_time
        FROM tb_sys_message_user mu
        INNER JOIN tb_sys_message m ON mu.message_id = m.message_id
        WHERE mu.user_id = #{userID}
        AND mu.message_id = #{messageID}
        AND mu.deleted = 0
        AND m.deleted = 0
    </select>

    <!-- 标记消息为已读 -->
    <update id="markAsRead">
        UPDATE tb_sys_message_user
        SET is_read = 1,
            send_status = 'sent',
            read_time = NOW(),
            update_time = NOW()
        WHERE user_id = #{userID}
        AND message_id = #{messageID}
        AND deleted = 0
    </update>

    <!-- 批量标记消息为已读 -->
    <update id="batchMarkAsRead">
        UPDATE tb_sys_message_user
        SET is_read = 1,
            read_time = NOW(),
            update_time = NOW()
        WHERE user_id = #{userID}
        AND message_id IN
        <foreach collection="messageIDs" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>
        AND deleted = 0
    </update>

    <!-- 查询未读消息数量 -->
    <select id="countUnread" resultType="java.lang.Integer">
        SELECT COUNT(*)
        FROM tb_sys_message_user
        WHERE user_id = #{userID}
        AND is_read = 0
        AND deleted = 0
    </select>

    <!-- 动态计算未读消息数量（基于 target 配置） -->
    <select id="countUnreadWithDynamicTargets" resultType="java.lang.Integer">
        SELECT COUNT(DISTINCT m.message_id)
        FROM tb_sys_message m
        INNER JOIN tb_sys_message_target mt ON m.message_id = mt.message_id AND mt.deleted = 0
        LEFT JOIN tb_sys_message_user mu ON m.message_id = mu.message_id AND mu.user_id = #{userID} AND mu.deleted = 0
        WHERE m.deleted = 0
        AND m.status IN ('sent', 'sending', 'failed')
        AND COALESCE(mu.is_read, 0) = 0
        AND (
            -- 用户类型目标：直接匹配
            (mt.target_type = 'user' AND mt.target_id = #{userID})
            OR
            -- 部门类型目标：用户所在部门是目标部门或其子部门
            (mt.target_type = 'dept' AND EXISTS (
                SELECT 1 FROM tb_sys_user_dept_role udr
                INNER JOIN tb_sys_dept d ON udr.dept_id = d.dept_id AND d.deleted = 0
                INNER JOIN tb_sys_dept target_dept ON target_dept.dept_id = mt.target_id AND target_dept.deleted = 0
                WHERE udr.user_id = #{userID}
                AND udr.deleted = 0
                AND (
                    d.dept_id = target_dept.dept_id
                    OR d.dept_path LIKE CONCAT(target_dept.dept_path, '%')
                )
            ))
            OR
            -- 角色类型目标：用户在指定部门范围内拥有该角色
            (mt.target_type = 'role' AND EXISTS (
                SELECT 1 FROM tb_sys_user_dept_role udr
                INNER JOIN tb_sys_dept d ON udr.dept_id = d.dept_id AND d.deleted = 0
                WHERE udr.user_id = #{userID}
                AND udr.deleted = 0
                AND udr.role_id = mt.target_id
                AND d.dept_path LIKE CONCAT(
                    (SELECT dept_path FROM tb_sys_dept WHERE dept_id = mt.scope_dept_id AND deleted = 0),
                    '%'
                )
            ))
        )
    </select>

    <!-- 更新用户消息的发送状态 -->
    <update id="updateSendStatus">
        UPDATE tb_sys_message_user
        SET send_status = #{sendStatus},
            <if test="failReason != null and failReason != ''">
                fail_reason = #{failReason},
            </if>
            update_time = NOW()
        WHERE id = #{id}
        AND deleted = 0
    </update>

    <!-- 查询待发送的用户消息列表 -->
    <select id="selectPendingUserMessages" resultMap="BaseResultMap">
        SELECT *
        FROM tb_sys_message_user
        WHERE deleted = 0
        <if test="filter != null">
            <if test="filter.messageID != null and filter.messageID != ''">
                AND message_id = #{filter.messageID}
            </if>
            <if test="filter.sendStatus != null and filter.sendStatus != ''">
                AND send_status = #{filter.sendStatus}
            </if>
            <if test="filter.deleted != null">
                AND deleted = #{filter.deleted}
            </if>
        </if>
        ORDER BY create_time ASC
    </select>

    <!-- 动态查询当前用户可见的消息列表（基于 target 配置计算） -->
    <select id="selectMyMessagesWithDynamicTargets" resultMap="MessageUserVOMap">
        SELECT
            m.message_id,
            m.title,
            m.content,
            m.message_type,
            m.priority,
            m.sender_name,
            m.sender_dept_name,
            m.actual_send_time,
            MAX(COALESCE(mu.is_read, 0)) as is_read,
            MAX(mu.read_time) as read_time,
            MAX(COALESCE(mu.send_status, 'pending')) as send_status,
            MAX(mu.id) as id,
            MAX(mu.user_id) as user_id,
            MAX(mu.send_method) as send_method,
            MAX(mu.fail_reason) as fail_reason,
            MAX(mu.create_time) as create_time
        FROM tb_sys_message m
        INNER JOIN tb_sys_message_target mt ON m.message_id = mt.message_id AND mt.deleted = 0
        LEFT JOIN tb_sys_message_user mu ON m.message_id = mu.message_id AND mu.user_id = #{userID} AND mu.deleted = 0
        WHERE m.deleted = 0
        AND m.status IN ('sent', 'sending', 'failed')
        AND (
            -- 用户类型目标：直接匹配
            (mt.target_type = 'user' AND mt.target_id = #{userID})
            OR
            -- 部门类型目标：用户所在部门是目标部门或其子部门
            (mt.target_type = 'dept' AND EXISTS (
                SELECT 1 FROM tb_sys_user_dept_role udr
                INNER JOIN tb_sys_dept d ON udr.dept_id = d.dept_id AND d.deleted = 0
                INNER JOIN tb_sys_dept target_dept ON target_dept.dept_id = mt.target_id AND target_dept.deleted = 0
                WHERE udr.user_id = #{userID}
                AND udr.deleted = 0
                AND (
                    d.dept_id = target_dept.dept_id
                    OR d.dept_path LIKE CONCAT(target_dept.dept_path, '%')
                )
            ))
            OR
            -- 角色类型目标：用户在指定部门范围内拥有该角色
            (mt.target_type = 'role' AND EXISTS (
                SELECT 1 FROM tb_sys_user_dept_role udr
                INNER JOIN tb_sys_dept d ON udr.dept_id = d.dept_id AND d.deleted = 0
                WHERE udr.user_id = #{userID}
                AND udr.deleted = 0
                AND udr.role_id = mt.target_id
                AND d.dept_path LIKE CONCAT(
                    (SELECT dept_path FROM tb_sys_dept WHERE dept_id = mt.scope_dept_id AND deleted = 0),
                    '%'
                )
            ))
        )
        <if test="filter != null">
            <if test="filter.isRead != null">
                AND COALESCE(mu.is_read, 0) = #{filter.isRead}
            </if>
            <if test="filter.sendMethod != null and filter.sendMethod != ''">
                AND mt.send_method = #{filter.sendMethod}
            </if>
            <if test="filter.sendStatus != null and filter.sendStatus != ''">
                AND mu.send_status = #{filter.sendStatus}
            </if>
            <if test="filter.priority != null and filter.priority != ''">
                AND m.priority = #{filter.priority}
            </if>
            <if test="filter.messageType != null and filter.messageType != ''">
                AND m.message_type = #{filter.messageType}
            </if>
            <if test="filter.title != null and filter.title != ''">
                AND m.title LIKE CONCAT('%', #{filter.title}, '%')
            </if>
        </if>
        GROUP BY m.message_id, m.title, m.content, m.message_type, m.priority,
                 m.sender_name, m.sender_dept_name, m.actual_send_time
        ORDER BY m.actual_send_time DESC, m.create_time DESC
    </select>

    <!-- 查询或创建用户消息记录 -->
    <select id="selectOrCreateUserMessage" resultMap="MessageUserVOMap">
        SELECT
            mu.*,
            m.title,
            m.content,
            m.message_type,
            m.priority,
            m.sender_name,
            m.sender_dept_name,
            m.actual_send_time
        FROM tb_sys_message m
        LEFT JOIN tb_sys_message_user mu ON m.message_id = mu.message_id
            AND mu.user_id = #{userID}
            AND mu.deleted = 0
        WHERE m.message_id = #{messageID}
        AND m.deleted = 0
    </select>

    <!-- 插入用户消息记录（如果不存在） -->
    <insert id="insertIfNotExists">
        INSERT INTO tb_sys_message_user
        (id, message_id, user_id, send_method, is_read, send_status, creator, create_time, update_time, deleted)
        SELECT
            #{userMessage.id},
            #{userMessage.messageID},
            #{userMessage.userID},
            #{userMessage.sendMethod},
            0,
            'pending',
            #{userMessage.creator},
            NOW(),
            NOW(),
            0
        FROM DUAL
        WHERE NOT EXISTS (
            SELECT 1 FROM tb_sys_message_user
            WHERE message_id = #{userMessage.messageID}
            AND user_id = #{userMessage.userID}
            AND deleted = 0
        )
    </insert>

</mapper>
