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

    <!-- 基础结果映射 -->
    <resultMap id="BaseResultMap" type="org.xyzh.common.dto.study.TbLearningRecord">
        <id column="id" property="id" jdbcType="VARCHAR"/>
        <result column="user_id" property="userID" jdbcType="VARCHAR"/>
        <result column="task_id" property="taskID" jdbcType="VARCHAR"/>
        <result column="resource_type" property="resourceType" jdbcType="INTEGER"/>
        <result column="resource_id" property="resourceID" jdbcType="VARCHAR"/>
        <result column="course_id" property="courseID" jdbcType="VARCHAR"/>
        <result column="chapter_id" property="chapterID" jdbcType="VARCHAR"/>
        <result column="node_id" property="nodeID" jdbcType="VARCHAR"/>
        <result column="duration" property="duration" jdbcType="INTEGER"/>
        <result column="progress" property="progress" jdbcType="DECIMAL"/>
        <result column="is_complete" property="isComplete" jdbcType="BOOLEAN"/>
        <result column="complete_time" property="completeTime" jdbcType="TIMESTAMP"/>
        <result column="last_learn_time" property="lastLearnTime" jdbcType="TIMESTAMP"/>
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
        <result column="update_time" property="updateTime" jdbcType="TIMESTAMP"/>
    </resultMap>

    <!-- 基础字段 -->
    <sql id="Base_Column_List">
        id, user_id, task_id, resource_type, resource_id, course_id, chapter_id, node_id, duration, progress, 
        is_complete, complete_time, last_learn_time, create_time, update_time
    </sql>

    <!-- 通用条件 -->
    <sql id="Where_Clause">
        <where>
            <if test="userID != null and userID != ''">
                AND user_id = #{userID}
            </if>
            <if test="taskID != null and taskID != ''">
                AND task_id = #{taskID}
            </if>
            <if test="resourceType != null">
                AND resource_type = #{resourceType}
            </if>
            <if test="resourceID != null and resourceID != ''">
                AND resource_id = #{resourceID}
            </if>
            <if test="courseID != null and courseID != ''">
                AND course_id = #{courseID}
            </if>
            <if test="chapterID != null and chapterID != ''">
                AND chapter_id = #{chapterID}
            </if>
            <if test="nodeID != null and nodeID != ''">
                AND node_id = #{nodeID}
            </if>
            <if test="isComplete != null">
                AND is_complete = #{isComplete}
            </if>
        </where>
    </sql>

    <!-- selectLearningRecords -->
    <select id="selectLearningRecords" resultMap="BaseResultMap">
        SELECT 
        <include refid="Base_Column_List"/>
        FROM tb_learning_record
        <include refid="Where_Clause"/>
        ORDER BY last_learn_time DESC, create_time DESC
    </select>

    <!-- 根据记录ID查询记录信息 -->
    <select id="selectByRecordId" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_learning_record
        WHERE id = #{recordId}
    </select>

    <!-- 根据用户ID查询学习记录列表 -->
    <select id="selectByUserId" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_learning_record
        WHERE user_id = #{userId}
        ORDER BY last_learn_time DESC, create_time DESC
    </select>

    <!-- 根据课程ID查询学习记录列表 -->
    <select id="selectByCourseId" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_learning_record
        WHERE resource_id = #{courseId}
        ORDER BY last_learn_time DESC, create_time DESC
    </select>

    <!-- 根据用户ID和课程ID查询学习记录 -->
    <select id="selectByUserIdAndCourseId" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_learning_record
        WHERE user_id = #{userId} AND course_id = #{courseId} AND resource_type = 2
    </select>

    <!-- 根据状态查询学习记录列表 -->
    <select id="selectByStatus" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_learning_record
        WHERE is_complete = #{status}
        ORDER BY last_learn_time DESC, create_time DESC
    </select>

    <!-- 查询用户学习统计信息 -->
    <select id="selectLearningStatistics" resultMap="BaseResultMap">
        SELECT
        user_id,
        COUNT(*) as total_records,
        SUM(duration) as total_duration,
        AVG(progress) as avg_progress,
        COUNT(CASE WHEN is_complete = 1 THEN 1 END) as completed_count
        FROM tb_learning_record
        WHERE user_id = #{userId}
        GROUP BY user_id
    </select>

    <!-- 插入学习记录 -->
    <insert id="insertLearningRecord" parameterType="org.xyzh.common.dto.study.TbLearningRecord">
        INSERT INTO tb_learning_record (
            id, user_id, task_id, resource_type, resource_id, course_id, chapter_id, node_id, create_time
        ) VALUES (
            #{id}, #{userID}, #{taskID}, #{resourceType}, #{resourceID}, #{courseID}, #{chapterID}, #{nodeID}, #{createTime}
        )
    </insert>

    <!-- 更新学习记录 -->
    <update id="updateLearningRecord" parameterType="org.xyzh.common.dto.study.TbLearningRecord">
        UPDATE tb_learning_record
        <set>
            <if test="duration != null">
                duration = #{duration},
            </if>
            <if test="progress != null">
                progress = #{progress},
            </if>
            <if test="isComplete != null">
                is_complete = #{isComplete},
            </if>
            <if test="completeTime != null">
                complete_time = #{completeTime},
            </if>
            <if test="lastLearnTime != null">
                last_learn_time = #{updateTime},
            </if>
            <if test="updater != null">
                updater = #{updater},
            </if>
            <if test="updateTime != null">
                update_time = #{updateTime},
            </if>
            <if test="startTime != null and endTime != null">
                create_time BETWEEN #{startTime} AND #{endTime}
            </if>
        </set>
        WHERE id = #{id}
    </update>

    <!-- 删除学习记录 -->
    <delete id="deleteLearningRecord" parameterType="org.xyzh.common.dto.study.TbLearningRecord">
        DELETE FROM tb_learning_record
        WHERE id = #{id}
    </delete>

    <!-- 批量插入学习记录 -->
    <insert id="batchInsertLearningRecords" parameterType="java.util.List">
        INSERT INTO tb_learning_record (
            id, user_id, resource_type, resource_id, task_id, course_id, chapter_id, node_id, create_time
        ) VALUES
        <foreach collection="learningRecordList" item="item" separator=",">
            (
                #{item.id}, #{item.userID}, #{item.resourceType}, #{item.resourceID}, #{item.taskID}, #{item.courseID}, #{item.chapterID}, #{item.nodeID}, #{item.createTime}
            )
        </foreach>
    </insert>

    <!-- 批量删除学习记录 -->
    <delete id="batchDeleteLearningRecords">
        DELETE FROM tb_learning_record
        WHERE id IN
        <foreach collection="ids" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <!-- LearningStatisticsDetailVO结果映射 -->
    <resultMap id="StatisticsDetailResultMap" type="org.xyzh.common.vo.LearningStatisticsDetailVO">
        <result column="stat_date" property="statDate" jdbcType="TIMESTAMP"/>
        <result column="resource_type" property="resourceType" jdbcType="INTEGER"/>
        <result column="resource_type_name" property="resourceTypeName" jdbcType="VARCHAR"/>
        <result column="resource_id" property="resourceID" jdbcType="VARCHAR"/>
        <result column="resource_title" property="resourceTitle" jdbcType="VARCHAR"/>
        <result column="course_id" property="courseID" jdbcType="VARCHAR"/>
        <result column="course_name" property="courseName" jdbcType="VARCHAR"/>
        <result column="chapter_id" property="chapterID" jdbcType="VARCHAR"/>
        <result column="chapter_name" property="chapterName" jdbcType="VARCHAR"/>
        <result column="total_duration" property="totalDuration" jdbcType="INTEGER"/>
        <result column="total_duration_formatted" property="totalDurationFormatted" jdbcType="VARCHAR"/>
        <result column="learn_count" property="learnCount" jdbcType="INTEGER"/>
        <result column="is_complete" property="isComplete" jdbcType="BOOLEAN"/>
        <result column="complete_time" property="completeTime" jdbcType="TIMESTAMP"/>
    </resultMap>

    <!-- 分页查询学习记录详情 -->
    <select id="selectLearningRecordsPage" resultMap="StatisticsDetailResultMap">
        SELECT
            COALESCE(lr.last_learn_time, lr.update_time, lr.create_time) AS stat_date,
            lr.resource_type AS resource_type,
            CASE lr.resource_type
                WHEN 1 THEN '文章'
                WHEN 2 THEN '课程'
                WHEN 3 THEN '章节'
                ELSE '未知'
            END AS resource_type_name,
            lr.resource_id AS resource_id,
            CASE 
                WHEN lr.resource_type = 1 THEN COALESCE(r.title, CONCAT('文章-', lr.resource_id))
                WHEN lr.resource_type = 2 THEN COALESCE(c.name, CONCAT('课程-', lr.course_id))
                WHEN lr.resource_type = 3 THEN COALESCE(ch.name, CONCAT('章节-', lr.chapter_id))
                ELSE '未知资源'
            END AS resource_title,
            COALESCE(lr.course_id, '') AS course_id,
            COALESCE(c.name, '') AS course_name,
            COALESCE(lr.chapter_id, '') AS chapter_id,
            COALESCE(ch.name, '') AS chapter_name,
            COALESCE(lr.duration, 0) AS total_duration,
            '' AS total_duration_formatted,
            1 AS learn_count,
            COALESCE(lr.is_complete, 0) AS is_complete,
            lr.complete_time AS complete_time
        FROM tb_learning_record lr
        LEFT JOIN tb_resource r ON lr.resource_type = 1 AND lr.resource_id = r.resource_id
        LEFT JOIN tb_course c ON lr.resource_type = 2 AND lr.course_id = c.course_id
        LEFT JOIN tb_course_chapter ch ON lr.resource_type = 3 AND lr.chapter_id = ch.chapter_id
        <where>
            <if test="filter.userID != null and filter.userID != ''">
                AND lr.user_id = #{filter.userID}
            </if>
            <if test="filter.resourceType != null">
                AND lr.resource_type = #{filter.resourceType}
            </if>
            <if test="filter.isComplete != null">
                AND lr.is_complete = #{filter.isComplete}
            </if>
        </where>
        ORDER BY lr.last_learn_time DESC, lr.create_time DESC
        LIMIT #{pageParam.pageSize} OFFSET #{pageParam.offset}
    </select>

    <!-- 统计学习记录总数 -->
    <select id="countLearningRecords" resultType="long">
        SELECT COUNT(1)
        FROM tb_learning_record lr
        <where>
            <if test="filter != null">
                <if test="filter.userID != null and filter.userID != ''">
                    AND lr.user_id = #{filter.userID}
                </if>
                <if test="filter.resourceType != null">
                    AND lr.resource_type = #{filter.resourceType}
                </if>
                <if test="filter.isComplete != null">
                    AND lr.is_complete = #{filter.isComplete}
                </if>
            </if>
        </where>
    </select>

    <!-- 获取本周课程和文章的总学习时长统计 -->
    <select id="getWeeklyStudyDurationByType" resultType="map">
        SELECT
            CASE
                WHEN lr.resource_type = 2 THEN '课程'
                WHEN lr.resource_type = 1 THEN '文章'
                ELSE '其他'
            END AS resourceType,
            COUNT(DISTINCT lr.user_id) AS userCount,
            COALESCE(SUM(lr.duration), 0) AS totalDuration,
            COUNT(*) AS studyCount
        FROM tb_learning_record lr
        INNER JOIN tb_sys_user u ON lr.user_id = u.id AND u.deleted = 0
        <if test="userDeptRoles != null and userDeptRoles.size() > 0">
        INNER JOIN tb_sys_user_dept_role udr ON u.id = udr.user_id AND udr.deleted = 0
        INNER JOIN tb_sys_dept user_dept ON udr.dept_id = user_dept.dept_id AND user_dept.deleted = 0
        INNER JOIN (
            SELECT dept_id, dept_path FROM tb_sys_dept WHERE deleted = 0 AND dept_id IN (
                <foreach collection="userDeptRoles" item="udr" separator=",">
                    #{udr.deptID}
                </foreach>
            )
        ) current_dept ON user_dept.dept_path LIKE CONCAT(current_dept.dept_path, '%')
        </if>
        WHERE lr.deleted = 0
            AND lr.update_time &gt;= DATE_SUB(CURDATE(), INTERVAL DAYOFWEEK(CURDATE())-1 DAY)
            AND lr.update_time &lt; DATE_ADD(DATE_SUB(CURDATE(), INTERVAL DAYOFWEEK(CURDATE())-1 DAY), INTERVAL 7 DAY)
        GROUP BY lr.resource_type
        ORDER BY totalDuration DESC
    </select>

    <!-- 获取本周学习时长排行榜 -->
    <select id="getWeeklyStudyDurationRanking" resultType="map">
        SELECT
            lr.user_id AS userId,
            u.username AS username,
            COALESCE(SUM(lr.duration), 0) AS totalDuration,
            COUNT(DISTINCT lr.resource_id) AS resourceCount,
            COUNT(*) AS studyCount
        FROM tb_learning_record lr
        INNER JOIN tb_sys_user u ON lr.user_id = u.id AND u.deleted = 0
        <if test="userDeptRoles != null and userDeptRoles.size() > 0">
        INNER JOIN tb_sys_user_dept_role udr ON u.id = udr.user_id AND udr.deleted = 0
        INNER JOIN tb_sys_dept user_dept ON udr.dept_id = user_dept.dept_id AND user_dept.deleted = 0
        INNER JOIN (
            SELECT dept_id, dept_path FROM tb_sys_dept WHERE deleted = 0 AND dept_id IN (
                <foreach collection="userDeptRoles" item="udr" separator=",">
                    #{udr.deptID}
                </foreach>
            )
        ) current_dept ON user_dept.dept_path LIKE CONCAT(current_dept.dept_path, '%')
        </if>
        WHERE lr.deleted = 0
            AND lr.update_time >= DATE_SUB(CURDATE(), INTERVAL DAYOFWEEK(CURDATE())-1 DAY)
            AND lr.update_time &lt; DATE_ADD(DATE_SUB(CURDATE(), INTERVAL DAYOFWEEK(CURDATE())-1 DAY), INTERVAL 7 DAY)
        GROUP BY lr.user_id, u.username
        ORDER BY totalDuration DESC
        LIMIT 10
    </select>

    <!-- 获取本周学习课程排行榜 -->
    <select id="getWeeklyCourseRanking" resultType="map">
        SELECT
            lr.resource_id AS resourceId,
            c.name AS resourceName,
            COUNT(DISTINCT lr.user_id) AS learnerCount,
            COALESCE(SUM(lr.duration), 0) AS totalDuration,
            COUNT(*) AS studyCount
        FROM tb_learning_record lr
        INNER JOIN tb_course c ON lr.resource_id = c.course_id AND c.deleted = 0
        INNER JOIN tb_sys_user u ON lr.user_id = u.id AND u.deleted = 0
        <if test="userDeptRoles != null and userDeptRoles.size() > 0">
        INNER JOIN tb_sys_user_dept_role udr ON u.id = udr.user_id AND udr.deleted = 0
        INNER JOIN tb_sys_dept user_dept ON udr.dept_id = user_dept.dept_id AND user_dept.deleted = 0
        INNER JOIN (
            SELECT dept_id, dept_path FROM tb_sys_dept WHERE deleted = 0 AND dept_id IN (
                <foreach collection="userDeptRoles" item="udr" separator=",">
                    #{udr.deptID}
                </foreach>
            )
        ) current_dept ON user_dept.dept_path LIKE CONCAT(current_dept.dept_path, '%')
        </if>
        WHERE lr.deleted = 0
            AND lr.resource_type = 2
            AND lr.update_time >= DATE_SUB(CURDATE(), INTERVAL DAYOFWEEK(CURDATE())-1 DAY)
            AND lr.update_time &lt; DATE_ADD(DATE_SUB(CURDATE(), INTERVAL DAYOFWEEK(CURDATE())-1 DAY), INTERVAL 7 DAY)
        GROUP BY lr.resource_id, c.name
        ORDER BY learnerCount DESC, totalDuration DESC
        LIMIT 10
    </select>

    <!-- 获取本周学习文章排行榜 -->
    <select id="getWeeklyArticleRanking" resultType="map">
        SELECT
            lr.resource_id AS resourceId,
            r.title AS resourceName,
            COUNT(DISTINCT lr.user_id) AS learnerCount,
            COALESCE(SUM(lr.duration), 0) AS totalDuration,
            COUNT(*) AS studyCount
        FROM tb_learning_record lr
        INNER JOIN tb_resource r ON lr.resource_id = r.resource_id AND r.deleted = 0
        INNER JOIN tb_sys_user u ON lr.user_id = u.id AND u.deleted = 0
        <if test="userDeptRoles != null and userDeptRoles.size() > 0">
        INNER JOIN tb_sys_user_dept_role udr ON u.id = udr.user_id AND udr.deleted = 0
        INNER JOIN tb_sys_dept user_dept ON udr.dept_id = user_dept.dept_id AND user_dept.deleted = 0
        INNER JOIN (
            SELECT dept_id, dept_path FROM tb_sys_dept WHERE deleted = 0 AND dept_id IN (
                <foreach collection="userDeptRoles" item="udr" separator=",">
                    #{udr.deptID}
                </foreach>
            )
        ) current_dept ON user_dept.dept_path LIKE CONCAT(current_dept.dept_path, '%')
        </if>
        WHERE lr.deleted = 0
            AND lr.resource_type = 1
            AND lr.update_time >= DATE_SUB(CURDATE(), INTERVAL DAYOFWEEK(CURDATE())-1 DAY)
            AND lr.update_time &lt; DATE_ADD(DATE_SUB(CURDATE(), INTERVAL DAYOFWEEK(CURDATE())-1 DAY), INTERVAL 7 DAY)
        GROUP BY lr.resource_id, r.title
        ORDER BY learnerCount DESC, totalDuration DESC
        LIMIT 10
    </select>

    <!-- 获取本周任务完成最多的排行榜 -->
    <select id="getWeeklyTaskCompletionRanking" resultType="map">
        SELECT
            tu.user_id AS userId,
            u.username AS username,
            COUNT(DISTINCT tu.task_id) AS completedTaskCount,
            COALESCE(SUM(lr.duration), 0) AS totalDuration,
            MAX(tu.complete_time) AS lastCompleteTime
        FROM tb_task_user tu
        INNER JOIN tb_sys_user u ON tu.user_id = u.id AND u.deleted = 0
        <if test="userDeptRoles != null and userDeptRoles.size() > 0">
        INNER JOIN tb_sys_user_dept_role udr ON u.id = udr.user_id AND udr.deleted = 0
        INNER JOIN tb_sys_dept user_dept ON udr.dept_id = user_dept.dept_id AND user_dept.deleted = 0
        INNER JOIN (
            SELECT dept_id, dept_path FROM tb_sys_dept WHERE deleted = 0 AND dept_id IN (
                <foreach collection="userDeptRoles" item="udr" separator=",">
                    #{udr.deptID}
                </foreach>
            )
        ) current_dept ON user_dept.dept_path LIKE CONCAT(current_dept.dept_path, '%')
        </if>
        LEFT JOIN tb_learning_record lr ON tu.user_id = lr.user_id AND tu.task_id = lr.task_id AND lr.deleted = 0
        WHERE tu.deleted = 0
            AND tu.status = 2
            AND tu.complete_time &gt;= DATE_SUB(CURDATE(), INTERVAL DAYOFWEEK(CURDATE())-1 DAY)
            AND tu.complete_time &lt; DATE_ADD(DATE_SUB(CURDATE(), INTERVAL DAYOFWEEK(CURDATE())-1 DAY), INTERVAL 7 DAY)
        GROUP BY tu.user_id, u.username
        ORDER BY completedTaskCount DESC, totalDuration DESC
        LIMIT 10
    </select>

    <!-- getTaskStaticByTag --> 

    <select id="getTaskStaticByTag">
        
    </select>

    <!-- 获取用户时间范围内每天的学习时长 -->
    <select id="getUserDailyDuration" resultType="java.util.Map">
        WITH RECURSIVE date_range AS (
            -- 生成起始日期
            SELECT DATE(#{startTime}) AS date
            UNION ALL
            -- 递归生成后续日期
            SELECT DATE_ADD(date, INTERVAL 1 DAY)
            FROM date_range
            WHERE date &lt; DATE(#{endTime})
        )
        SELECT 
            dr.date AS date,
            DATE_FORMAT(dr.date, '%Y-%m-%d') AS statDate,
            COALESCE(SUM(lr.duration), 0) AS duration,
            COALESCE(SUM(lr.duration), 0) AS totalDuration,
            COUNT(lr.id) AS count
        FROM date_range dr
        LEFT JOIN tb_learning_record lr 
            ON DATE(lr.update_time) = dr.date
            AND lr.user_id = #{userId}
        GROUP BY dr.date
        ORDER BY dr.date ASC
    </select>

</mapper>
