<?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.study.mapper.LearningTaskMapper">

    <!-- 基础结果映射 -->
    <resultMap id="BaseResultMap" type="org.xyzh.common.dto.study.TbLearningTask">
        <id column="id" property="id" jdbcType="VARCHAR"/>
        <result column="task_id" property="taskID" jdbcType="VARCHAR"/>
        <result column="name" property="name" jdbcType="VARCHAR"/>
        <result column="description" property="description" jdbcType="LONGVARCHAR"/>
        <result column="start_time" property="startTime" jdbcType="TIMESTAMP"/>
        <result column="end_time" property="endTime" jdbcType="TIMESTAMP"/>
        <result column="status" property="status" jdbcType="INTEGER"/>
        <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>

    <!-- TaskItemVO 结果映射 -->
    <resultMap id="TaskItemResultMap" type="org.xyzh.common.vo.TaskItemVO" extends="BaseResultMap">
        <result column="user_id" property="userID" jdbcType="VARCHAR"/>
        <result column="username" property="username" jdbcType="VARCHAR"/>
        <result column="dept_id" property="deptID" jdbcType="VARCHAR"/>
        <result column="dept_name" property="deptName" jdbcType="VARCHAR"/>
        <result column="parent_id" property="parentID" jdbcType="VARCHAR"/>
        <result column="user_status" property="status" jdbcType="INTEGER"/>
        <result column="progress" property="progress" jdbcType="DECIMAL"/>
        <result column="complete_time" property="completeTime" jdbcType="TIMESTAMP"/>
    </resultMap>

    <!-- 基础字段 -->
    <sql id="Base_Column_List">
        id, task_id, name, description, start_time, end_time, status, 
        creator, updater, create_time, update_time, delete_time, deleted
    </sql>

    <!-- 通用条件 -->
    <sql id="Where_Clause">
        <where>
            deleted = 0
            <if test="taskID != null and taskID != ''">
                AND task_id = #{taskID}
            </if>
            <if test="name != null and name != ''">
                AND name LIKE CONCAT('%', #{name}, '%')
            </if>
            <if test="status != null">
                AND status = #{status}
            </if>
        </where>
    </sql>

    <sql id="Filter_Clause">
        <where>
            deleted = 0
            <if test="filter.taskID != null and filter.taskID != ''">
                AND task_id = #{taskID}
            </if>
            <if test="filter.name != null and filter.name != ''">
                AND name LIKE CONCAT('%', #{filter.name}, '%')
            </if>
            <if test="filter.status != null">
                AND status = #{filter.status}
            </if>
        </where>
    </sql>

    <!-- 权限过滤条件（基于dept_path的高效继承） -->
    <sql id="Permission_Filter">
        INNER JOIN tb_resource_permission rp ON t.task_id = rp.resource_id 
            AND rp.resource_type = 3 
            AND rp.deleted = 0
            AND rp.can_read = 1
            AND (
                -- 全局权限：所有用户可访问
                (rp.dept_id IS NULL AND rp.role_id IS NULL)
                <if test="userDeptRoles != null and userDeptRoles.size() > 0">
                    OR EXISTS (
                        SELECT 1 
                        FROM (
                            <foreach collection="userDeptRoles" item="udr" separator=" UNION ALL ">
                                SELECT #{udr.deptID} AS dept_id, #{udr.deptPath} AS dept_path, #{udr.roleID} AS role_id
                            </foreach>
                        ) user_roles
                        LEFT JOIN tb_sys_dept perm_dept ON perm_dept.dept_id = rp.dept_id AND perm_dept.deleted = 0
                        WHERE 
                           -- 部门级权限：当前部门或父部门（通过dept_path判断继承关系）
                           (rp.role_id IS NULL AND rp.dept_id IS NOT NULL 
                               AND user_roles.dept_path LIKE CONCAT(perm_dept.dept_path, '%'))
                           -- 角色级权限：跨部门的角色权限
                           OR (rp.dept_id IS NULL AND rp.role_id = user_roles.role_id)
                           -- 精确权限：特定部门的特定角色
                           OR (rp.dept_id = user_roles.dept_id AND rp.role_id = user_roles.role_id)
                    )
                </if>
            )
    </sql>

    <!-- selectLearningTasks - 添加权限过滤 -->
    <select id="selectLearningTasks" resultMap="BaseResultMap">
        SELECT DISTINCT t.*
        FROM tb_learning_task t
        <include refid="Permission_Filter"/>
        WHERE t.deleted = 0
            <if test="filter.taskID != null and filter.taskID != ''">
                AND t.task_id = #{filter.taskID}
            </if>
            <if test="filter.name != null and filter.name != ''">
                AND t.name LIKE CONCAT('%', #{filter.name}, '%')
            </if>
            <if test="filter.status != null">
                AND t.status = #{filter.status}
            </if>
        ORDER BY t.create_time DESC
    </select>

    <!-- 根据任务ID查询任务信息 -->
    <select id="selectByTaskId" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_learning_task
        WHERE task_id = #{taskId} AND deleted = 0
    </select>

    <!-- 根据用户ID查询学习任务列表 -->
    <select id="selectByUserId" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_learning_task
        WHERE deleted = 0
        ORDER BY create_time DESC
    </select>

    <!-- 根据任务名称查询任务 -->
    <select id="selectByName" resultMap="BaseResultMap">
        SELECT DISTINCT t.*
        FROM tb_learning_task t
        <include refid="Permission_Filter"/>
        WHERE t.name = #{name} AND t.deleted = 0
    </select>

    <!-- 根据状态查询学习任务列表 -->
    <select id="selectByStatus" resultMap="BaseResultMap">
        SELECT DISTINCT t.*
        FROM tb_learning_task t
        <include refid="Permission_Filter"/>
        WHERE t.status = #{status} AND t.deleted = 0
        ORDER BY t.create_time DESC
    </select>

    <!-- 根据优先级查询学习任务列表 -->
    <select id="selectByPriority" resultMap="BaseResultMap">
        SELECT DISTINCT t.*
        FROM tb_learning_task t
        <include refid="Permission_Filter"/>
        WHERE t.deleted = 0
        ORDER BY t.create_time DESC
    </select>

    <!-- 查询用户待完成任务列表 -->
    <select id="selectPendingTasksByUserId" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_learning_task
        LEFT JOIN tb_task_user tu ON t.task_id = tu.task_id
        WHERE t.status = 0 AND t.deleted = 0
        AND tu.user_id = #{userId}
        ORDER BY create_time DESC
    </select>

    <!-- 查询用户已完成任务列表 -->
    <select id="selectCompletedTasksByUserId" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_learning_task
        LEFT JOIN tb_task_user tu ON t.task_id = tu.task_id
        WHERE t.status = 1 AND t.deleted = 0
        AND tu.user_id = #{userId}
        ORDER BY t.create_time DESC
    </select>

    <!-- 检查任务名称是否存在 -->
    <select id="countByName" resultType="int">
        SELECT COUNT(DISTINCT t.id)
        FROM tb_learning_task t
        <include refid="Permission_Filter"/>
        WHERE t.name = #{name} AND t.deleted = 0
        <if test="excludeId != null and excludeId != ''">
            AND t.id != #{excludeId}
        </if>
    </select>

    <!-- 插入学习任务 -->
    <insert id="insertLearningTask" parameterType="org.xyzh.common.dto.study.TbLearningTask">
        INSERT INTO tb_learning_task (
            id, task_id, name, description, start_time, end_time, status,
            creator,  create_time
        ) VALUES (
            #{id}, #{taskID}, #{name}, #{description}, #{startTime}, #{endTime}, #{status},
            #{creator}, #{createTime}
        )
    </insert>

    <!-- 更新学习任务 -->
    <update id="updateLearningTask" parameterType="org.xyzh.common.dto.study.TbLearningTask">
        UPDATE tb_learning_task
        <set>
            <if test="taskID != null and taskID != ''">
                task_id = #{taskID},
            </if>
            <if test="name != null and name != ''">
                name = #{name},
            </if>
            <if test="description != null and description != ''">
                description = #{description},
            </if>
            <if test="startTime != null">
                start_time = #{startTime},
            </if>
            <if test="endTime != null">
                end_time = #{endTime},
            </if>
            <if test="status != null">
                status = #{status},
            </if>
            <if test="updater != null and updater != ''">
                updater = #{updater},
            </if>
            <if test="updateTime != null">
                update_time = #{updateTime},
            </if>
            <if test="deleteTime != null">
                delete_time = #{deleteTime},
            </if>
            <if test="deleted != null">
                deleted = #{deleted},
            </if>
        </set>
        WHERE task_id = #{taskID}
    </update>

    <!-- 删除学习任务 -->
    <delete id="deleteLearningTask" parameterType="org.xyzh.common.dto.study.TbLearningTask">
        DELETE FROM tb_learning_task
        WHERE task_id = #{taskID}
    </delete>

    <!-- 批量插入学习任务 -->
    <insert id="batchInsertLearningTasks" parameterType="java.util.List">
        INSERT INTO tb_learning_task (
            id, task_id, name, description, start_time, end_time, status,
            creator, create_time
        ) VALUES
        <foreach collection="learningTaskList" item="item" separator=",">
            (
                #{item.id}, #{item.taskID}, #{item.name}, #{item.description}, #{item.startTime}, 
                #{item.endTime}, #{item.status}, #{item.creator}, #{item.createTime}
            )
        </foreach>
    </insert>

    <!-- 批量删除学习任务 -->
    <delete id="batchDeleteLearningTasks">
        DELETE FROM tb_learning_task
        WHERE task_id IN
        <foreach collection="ids" item="taskID" open="(" separator="," close=")">
            #{taskID}
        </foreach>
    </delete>

    <!-- 分页查询学习任务 -->
    <!-- selectLearningTasksPage - 添加权限过滤 -->
    <select id="selectLearningTasksPage" resultMap="BaseResultMap">
        SELECT DISTINCT t.*
        FROM tb_learning_task t
        <include refid="Permission_Filter"/>
        WHERE t.deleted = 0
            <if test="filter != null">
                <if test="filter.taskID != null and filter.taskID != ''">
                    AND t.task_id = #{filter.taskID}
                </if>
                <if test="filter.name != null and filter.name != ''">
                    AND t.name LIKE CONCAT('%', #{filter.name}, '%')
                </if>
                <if test="filter.status != null">
                    AND t.status = #{filter.status}
                </if>
            </if>
        ORDER BY t.create_time DESC
        LIMIT #{pageParam.pageSize} OFFSET #{pageParam.offset}
    </select>

    <!-- selectUserLearningTasksPage - 添加权限过滤 -->
    <select id="selectUserLearningTasksPage" resultMap="TaskItemResultMap">
        SELECT DISTINCT
            tlt.id, tlt.task_id, tlt.name, tlt.description, tlt.start_time, tlt.end_time, 
            tlt.creator, tlt.updater, tlt.create_time, tlt.update_time,
            ttu.user_id, ttu.status AS user_status, ttu.progress, ttu.complete_time,
            ttu.dept_id, d.name AS dept_name, d.parent_id
        FROM tb_task_user ttu
        INNER JOIN tb_learning_task tlt ON ttu.task_id = tlt.task_id
        LEFT JOIN tb_sys_dept d ON ttu.dept_id = d.dept_id AND d.deleted = 0
        INNER JOIN tb_resource_permission rp ON tlt.task_id = rp.resource_id 
            AND rp.resource_type = 3 
            AND rp.deleted = 0
            AND rp.can_read = 1
            AND (
                (rp.dept_id IS NULL AND rp.role_id IS NULL)
                <if test="userDeptRoles != null and userDeptRoles.size() > 0">
                    OR EXISTS (
                        SELECT 1 
                        FROM (
                            <foreach collection="userDeptRoles" item="udr" separator=" UNION ALL ">
                                SELECT #{udr.deptID} AS dept_id, #{udr.roleID} AS role_id
                            </foreach>
                        ) user_roles
                        WHERE (rp.dept_id = user_roles.dept_id AND rp.role_id IS NULL)
                           OR (rp.role_id = user_roles.role_id AND rp.dept_id IS NULL)
                           OR (rp.dept_id = user_roles.dept_id AND rp.role_id = user_roles.role_id)
                    )
                </if>
            )
        WHERE ttu.user_id = #{filter.userID}
        AND tlt.deleted = 0
        AND ttu.deleted = 0
        <if test="filter.status != null">
            AND ttu.status = #{filter.status}
        </if>
        ORDER BY tlt.create_time DESC
        LIMIT #{pageParam.pageSize} OFFSET #{pageParam.offset}
    </select>

    <!-- 统计学习任务总数 - 添加权限过滤 -->
    <select id="countLearningTasks" resultType="long">
        SELECT COUNT(DISTINCT t.id)
        FROM tb_learning_task t
        <include refid="Permission_Filter"/>
        WHERE t.deleted = 0
            <if test="filter != null">
                <if test="filter.taskID != null and filter.taskID != ''">
                    AND t.task_id = #{filter.taskID}
                </if>
                <if test="filter.name != null and filter.name != ''">
                    AND t.name LIKE CONCAT('%', #{filter.name}, '%')
                </if>
                <if test="filter.status != null">
                    AND t.status = #{filter.status}
                </if>
            </if>
    </select>

    <!-- getTaskStaticByTag：按标签统计当前用户在时间范围内任务的平均完成进度 --> 

    <select id="getTaskStaticByTag" resultType="java.util.Map">
        SELECT 
            tt.name        AS tagName,
            AVG(tu.progress) AS avgProgress
        FROM tb_learning_task t
        LEFT JOIN tb_task_user tu ON t.task_id = tu.task_id 
        LEFT JOIN tb_learning_task_tag tlt ON t.task_id = tlt.task_id
        LEFT JOIN tb_tag tt ON tlt.tag_id = tt.tag_id
        <include refid="Permission_Filter"/>
        WHERE t.deleted = 0
          AND tu.deleted = 0
          AND tu.user_id = #{userId}
          AND tu.progress IS NOT NULL
          AND t.create_time BETWEEN #{filter.startTime} AND #{filter.endTime}
        GROUP BY tt.tag_id, tt.name
    </select>
</mapper>
