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

    <!-- 基础结果映射 -->
    <resultMap id="BaseResultMap" type="org.xyzh.common.dto.study.TbTaskUser">
        <id column="id" property="id" jdbcType="VARCHAR"/>
        <result column="task_id" property="taskID" jdbcType="VARCHAR"/>
        <result column="user_id" property="userID" jdbcType="VARCHAR"/>
        <result column="dept_id" property="deptID" jdbcType="VARCHAR"/>
        <result column="status" property="status" jdbcType="INTEGER"/>
        <result column="progress" property="progress" jdbcType="DECIMAL"/>
        <result column="complete_time" property="completeTime" jdbcType="TIMESTAMP"/>
        <result column="creator" property="creator" jdbcType="VARCHAR"/>
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
        <result column="update_time" property="updateTime" jdbcType="TIMESTAMP"/>
    </resultMap>

    <resultMap id="TaskItemResultMap" type="org.xyzh.common.vo.TaskItemVO">
        <id column="id" property="id" jdbcType="VARCHAR"/>
        <result column="task_id" property="taskID" jdbcType="VARCHAR"/>
        <result column="course_id" property="courseID" jdbcType="VARCHAR"/>
        <result column="course_name" property="courseName" jdbcType="VARCHAR"/>
        <result column="resource_id" property="resourceID" jdbcType="VARCHAR"/>
        <result column="resource_name" property="resourceName" jdbcType="VARCHAR"/>
        <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="status" property="status" jdbcType="INTEGER"/>
        <result column="progress" property="progress" jdbcType="DECIMAL"/>
        <result column="complete_time" property="completeTime" jdbcType="TIMESTAMP"/>
        <result column="required" property="required" jdbcType="BOOLEAN"/>
        <result column="order_num" property="orderNum" jdbcType="INTEGER"/>
        <result column="creator" property="creator" jdbcType="VARCHAR"/>
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
    </resultMap>

    <!-- 基础字段 -->
    <sql id="Base_Column_List">
        id, task_id, user_id, dept_id, status, progress, complete_time, 
        creator, create_time, update_time
    </sql>

    <!-- 通用条件 -->
    <sql id="Where_Clause">
        <where>
            <if test="taskID != null and taskID != ''">
                AND task_id = #{taskID}
            </if>
            <if test="userID != null and userID != ''">
                AND user_id = #{userID}
            </if>
            <if test="deptID != null and deptID != ''">
                AND dept_id = #{deptID}
            </if>
            <if test="status != null">
                AND status = #{status}
            </if>
        </where>
    </sql>

    <!-- selectTaskUsers -->
    <select id="selectTaskUsers" resultMap="BaseResultMap">
        SELECT 
        <include refid="Base_Column_List"/>
        FROM tb_task_user
        <include refid="Where_Clause"/>
        ORDER BY create_time DESC
    </select>

    <!-- 根据关联ID查询关联信息 -->
    <select id="selectByRelationId" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_task_user
        WHERE id = #{relationId}
    </select>

    <!-- 根据任务ID查询用户关联列表 -->
    <select id="selectByTaskId" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_task_user
        WHERE task_id = #{taskId}
        ORDER BY create_time DESC
    </select>

    <select id="selectTaskItemByTaskId" resultMap="TaskItemResultMap">
        SELECT
            ttu.id,
            ttu.task_id,
            ttu.user_id,
            ttu.status,
            ttu.progress,
            ttu.complete_time,
            ttu.creator,
            ttu.create_time,
            tu.username,
            ttu.dept_id,
            d.name as dept_name,
            d.parent_id
        FROM tb_task_user ttu
        INNER JOIN tb_sys_user tu ON ttu.user_id = tu.id AND tu.deleted = 0
        LEFT JOIN tb_sys_user_dept_role udr ON tu.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 ttu.task_id = #{taskId} AND ttu.deleted = 0
        ORDER BY ttu.create_time ASC
    </select>

    <select id="selectUserTaskItem" resultMap="TaskItemResultMap">
        SELECT
            ttu.id,
            ttu.task_id,
            ttu.user_id,
            ttu.status,
            ttu.progress,
            ttu.complete_time,
            ttu.creator,
            ttu.create_time,
            tu.username,
            ttu.dept_id,
            d.name as dept_name,
            d.parent_id
        FROM tb_task_user ttu
        INNER JOIN tb_sys_user tu ON ttu.user_id = tu.id AND tu.deleted = 0
        LEFT JOIN tb_sys_dept d ON ttu.dept_id = d.dept_id AND d.deleted = 0
        WHERE ttu.task_id = #{taskId} AND ttu.user_id = #{userId} AND ttu.deleted = 0
        ORDER BY ttu.create_time ASC
    </select>

    <!-- 根据用户ID查询任务关联列表 -->
    <select id="selectByUserId" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_task_user
        WHERE user_id = #{userId}
        ORDER BY create_time DESC
    </select>

    <!-- 根据任务ID和用户ID查询关联信息 -->
    <select id="selectByTaskIdAndUserId" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_task_user
        WHERE task_id = #{taskId} AND user_id = #{userId}
    </select>

    <!-- 根据状态查询任务用户关联列表 -->
    <select id="selectByStatus" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_task_user
        WHERE status = #{status}
        ORDER BY create_time DESC
    </select>

    <!-- 根据用户ID和状态查询任务关联列表 -->
    <select id="selectByUserIdAndStatus" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_task_user
        WHERE user_id = #{userId} AND status = #{status}
        ORDER BY create_time DESC
    </select>

    <!-- 检查任务用户关联是否存在 -->
    <select id="countByTaskIdAndUserId" resultType="int">
        SELECT COUNT(1)
        FROM tb_task_user
        WHERE task_id = #{taskId} AND user_id = #{userId}
        <if test="excludeId != null and excludeId != ''">
            AND id != #{excludeId}
        </if>
    </select>

    <!-- 插入任务用户关联 -->
    <insert id="insertTaskUser" parameterType="org.xyzh.common.dto.study.TbTaskUser">
        INSERT INTO tb_task_user (
            id, task_id, user_id, dept_id,creator, status, progress, create_time
        ) VALUES (
            #{id}, #{taskID}, #{userID}, #{deptID}, #{creator}, 0, 0, #{createTime}
        )
    </insert>

    <!-- 更新任务用户关联 -->
    <update id="updateTaskUser" parameterType="org.xyzh.common.dto.study.TbTaskUser">
        UPDATE tb_task_user
        <set>
            <if test="status != null">
                status = #{status},
            </if>
            <if test="progress != null">
                progress = #{progress},
            </if>
            <if test="completeTime != null">
                complete_time = #{completeTime},
            </if>
            <if test="updater != null">
                updater = #{updater},
            </if>
            update_time = NOW()
        </set>
        WHERE task_id = #{taskID} AND user_id = #{userID} AND deleted = 0
    </update>

    <!-- 删除任务用户关联 -->
    <delete id="deleteTaskUser" parameterType="org.xyzh.common.dto.study.TbTaskUser">
        DELETE FROM tb_task_user
        WHERE task_id = #{taskID}
    </delete>

    <!-- 批量插入任务用户关联 -->
    <insert id="batchInsertTaskUsers" parameterType="java.util.List">
        INSERT INTO tb_task_user (
            id, task_id, user_id, dept_id, status, progress, complete_time,
            creator, create_time
        ) VALUES
        <foreach collection="taskUserList" item="item" separator=",">
            (
                #{item.id}, #{item.taskID}, #{item.userID}, #{item.deptID}, 0, 
                0, #{item.completeTime}, #{item.creator}, #{item.createTime}
            )
        </foreach>
    </insert>

    <!-- 批量删除任务用户关联 -->
    <delete id="batchDeleteTaskUsers">
        DELETE FROM tb_task_user
        WHERE (task_id, user_id) IN
        <foreach collection="userIds" item="userID" open="(" separator="," close=")">
            (#{taskId}, #{userID})
        </foreach>
    </delete>

    <!-- 根据任务ID批量删除用户关联 -->
    <delete id="deleteByTaskId">
        DELETE FROM tb_task_user
        WHERE task_id = #{taskId}
    </delete>

    <!-- 根据用户ID批量删除任务关联 -->
    <delete id="deleteByUserId">
        DELETE FROM tb_task_user
        WHERE user_id = #{userId}
    </delete>

    <!-- 分页查询任务用户关联 -->
    <select id="selectTaskUsersPage" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_task_user
        <include refid="Where_Clause" />
        ORDER BY create_time DESC
        LIMIT #{pageParam.pageSize} OFFSET #{pageParam.offset}
    </select>

    <!-- 统计任务用户关联总数 -->
    <select id="countTaskUsers" resultType="long">
        SELECT COUNT(1)
        FROM tb_task_user
        <include refid="Where_Clause" />
    </select>

    <!-- 获取任务学习时长分布数据 -->
    <select id="getStudyDurationDistribution" resultType="map">
        SELECT
            CASE
                WHEN total_duration &lt; 600 THEN '0-10分钟'
                WHEN total_duration &lt; 1800 THEN '10-30分钟'
                WHEN total_duration &lt; 3600 THEN '30-60分钟'
                WHEN total_duration &lt; 7200 THEN '1-2小时'
                WHEN total_duration &lt; 14400 THEN '2-4小时'
                ELSE '4小时以上'
            END AS durationRange,
            COUNT(*) AS userCount
        FROM (
            SELECT
                lh.user_id,
                COALESCE(SUM(lh.duration), 0) AS total_duration
            FROM tb_task_user tu
            LEFT JOIN tb_learning_history lh ON tu.user_id = lh.user_id AND tu.task_id = lh.task_id AND lh.deleted = 0
            WHERE tu.task_id = #{taskId} AND tu.deleted = 0
            GROUP BY lh.user_id
        ) AS user_durations
        GROUP BY durationRange
        ORDER BY MIN(total_duration)
    </select>

    <!-- 获取任务学习进度分布数据 -->
    <select id="getStudyProgressDistribution" resultType="map">
        SELECT
            CASE
                WHEN progress &lt; 10 THEN '0-10%'
                WHEN progress &lt; 20 THEN '10-20%'
                WHEN progress &lt; 30 THEN '20-30%'
                WHEN progress &lt; 40 THEN '30-40%'
                WHEN progress &lt; 50 THEN '40-50%'
                WHEN progress &lt; 60 THEN '50-60%'
                WHEN progress &lt; 70 THEN '60-70%'
                WHEN progress &lt; 80 THEN '70-80%'
                WHEN progress &lt; 90 THEN '80-90%'
                WHEN progress &lt; 100 THEN '90-100%'
                ELSE '100%'
            END AS progressRange,
            COUNT(*) AS userCount
        FROM tb_task_user
        WHERE task_id = #{taskId} AND deleted = 0
        GROUP BY progressRange
        ORDER BY MIN(progress)
    </select>

    <!-- 获取任务完成时间排行榜（前10名） -->
    <select id="getCompletionTimeRanking" resultType="map">
        SELECT
            tu.user_id AS userId,
            u.username AS username,
            tu.complete_time AS completeTime,
            TIMESTAMPDIFF(SECOND, tu.create_time, tu.complete_time) AS completionDuration
        FROM tb_task_user tu
        INNER JOIN tb_sys_user u ON tu.user_id = u.id AND u.deleted = 0
        WHERE tu.task_id = #{taskId}
            AND tu.status = 2
            AND tu.complete_time IS NOT NULL
            AND tu.deleted = 0
        ORDER BY tu.complete_time ASC
        LIMIT 10
    </select>

    <!-- 获取任务学习时长排行榜（前10名） -->
    <select id="getStudyDurationRanking" resultType="map">
        SELECT
            lh.user_id AS userId,
            u.username AS username,
            COALESCE(SUM(lh.duration), 0) AS totalDuration
        FROM tb_task_user tu
        INNER JOIN tb_sys_user u ON tu.user_id = u.id AND u.deleted = 0
        LEFT JOIN tb_learning_history lh ON tu.user_id = lh.user_id AND tu.task_id = lh.task_id AND lh.deleted = 0
        WHERE tu.task_id = #{taskId} AND tu.deleted = 0
        GROUP BY lh.user_id, u.username
        ORDER BY totalDuration DESC
        LIMIT 10
    </select>

</mapper>
