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

    <!-- 基础结果映射 -->
    <resultMap id="BaseResultMap" type="org.xyzh.common.dto.study.TbLearningHistory">
        <id column="id" property="id" jdbcType="VARCHAR"/>
        <result column="user_id" property="userID" jdbcType="VARCHAR"/>
        <result column="history_id" property="historyID" 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="task_id" property="taskID" jdbcType="VARCHAR"/>
        <result column="start_time" property="startTime" jdbcType="TIMESTAMP"/>
        <result column="end_time" property="endTime" jdbcType="TIMESTAMP"/>
        <result column="duration" property="duration" jdbcType="INTEGER"/>
        <result column="start_progress" property="startProgress" jdbcType="DECIMAL"/>
        <result column="end_progress" property="endProgress" jdbcType="DECIMAL"/>
        <result column="device_type" property="deviceType" jdbcType="VARCHAR"/>
        <result column="ip_address" property="ipAddress" jdbcType="VARCHAR"/>
        <result column="creator" property="creator" jdbcType="VARCHAR"/>
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
        <result column="deleted" property="deleted" jdbcType="BOOLEAN"/>
    </resultMap>

    <!-- VO结果映射（带关联信息） -->
    <resultMap id="VOResultMap" type="org.xyzh.common.vo.LearningHistoryVO">
        <id column="id" property="id" jdbcType="VARCHAR"/>
        <result column="user_id" property="userID" jdbcType="VARCHAR"/>
        <result column="user_name" property="userName" jdbcType="VARCHAR"/>
        <result column="history_id" property="historyID" jdbcType="VARCHAR"/>
        <result column="resource_type" property="resourceType" jdbcType="INTEGER"/>
        <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="node_id" property="nodeID" jdbcType="VARCHAR"/>
        <result column="node_name" property="nodeName" jdbcType="VARCHAR"/>
        <result column="task_id" property="taskID" jdbcType="VARCHAR"/>
        <result column="task_name" property="taskName" jdbcType="VARCHAR"/>
        <result column="start_time" property="startTime" jdbcType="TIMESTAMP"/>
        <result column="end_time" property="endTime" jdbcType="TIMESTAMP"/>
        <result column="duration" property="duration" jdbcType="INTEGER"/>
        <result column="start_progress" property="startProgress" jdbcType="DECIMAL"/>
        <result column="end_progress" property="endProgress" jdbcType="DECIMAL"/>
        <result column="device_type" property="deviceType" jdbcType="VARCHAR"/>
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
    </resultMap>

    <!-- 基础字段 -->
    <sql id="Base_Column_List">
        id, user_id, history_id, resource_type, resource_id, course_id, chapter_id, node_id, task_id,
        start_time, end_time, duration, start_progress, end_progress, device_type, ip_address,
        creator, create_time, deleted
    </sql>

    <!-- 通用条件 -->
    <sql id="Where_Clause">
        <where>
            deleted = 0
            <if test="userID != null and userID != ''">
                AND user_id = #{userID}
            </if>
            <if test="historyID != null and historyID != ''">
                AND history_id = #{historyID}
            </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="taskID != null and taskID != ''">
                AND task_id = #{taskID}
            </if>
            <if test="deviceType != null and deviceType != ''">
                AND device_type = #{deviceType}
            </if>
        </where>
    </sql>

    <!-- 插入学习历史 -->
    <insert id="insertLearningHistory">
        INSERT INTO tb_learning_history (
            id, user_id, history_id, resource_type, resource_id, course_id, chapter_id, node_id, task_id,
            start_time, end_time, duration, start_progress, end_progress, device_type, ip_address,
            creator, create_time, deleted
        ) VALUES (
            #{id}, #{userID}, #{historyID}, #{resourceType}, #{resourceID}, #{courseID}, #{chapterID}, #{nodeID}, #{taskID},
            #{startTime}, #{endTime}, #{duration}, #{startProgress}, #{endProgress}, #{deviceType}, #{ipAddress},
            #{creator}, #{createTime}, 0
        )
    </insert>

    <!-- 更新学习历史 -->
    <update id="updateLearningHistory">
        UPDATE tb_learning_history
        <set>
            <if test="endTime != null">
                end_time = #{endTime},
            </if>
            <if test="duration != null">
                duration = #{duration},
            </if>
            <if test="startProgress != null">
                start_progress = #{startProgress},
            </if>
            <if test="endProgress != null">
                end_progress = #{endProgress},
            </if>
            <if test="deviceType != null">
                device_type = #{deviceType},
            </if>
            <if test="ipAddress != null">
                ip_address = #{ipAddress},
            </if>
        </set>
        WHERE history_id = #{historyID} AND deleted = 0
    </update>

    <!-- 批量插入学习历史 -->
    <insert id="batchInsertLearningHistories">
        INSERT INTO tb_learning_history (
            id, user_id, history_id, resource_type, resource_id, course_id, chapter_id, node_id, task_id,
            start_time, end_time, duration, start_progress, end_progress, device_type, ip_address,
            creator, create_time, deleted
        ) VALUES
        <foreach collection="historyList" item="item" separator=",">
            (
                #{item.id}, #{item.userID}, #{item.historyID}, #{item.resourceType}, #{item.resourceID}, 
                #{item.courseID}, #{item.chapterID}, #{item.nodeID}, #{item.taskID},
                #{item.startTime}, #{item.endTime}, #{item.duration}, #{item.startProgress}, #{item.endProgress}, 
                #{item.deviceType}, #{item.ipAddress}, #{item.creator}, #{item.createTime}, 0
            )
        </foreach>
    </insert>

    <!-- 根据ID查询 -->
    <select id="selectById" resultMap="BaseResultMap">
        SELECT <include refid="Base_Column_List"/>
        FROM tb_learning_history
        WHERE id = #{id} AND deleted = 0
    </select>

    <!-- 根据用户ID查询 -->
    <select id="selectByUserId" resultMap="BaseResultMap">
        SELECT <include refid="Base_Column_List"/>
        FROM tb_learning_history
        WHERE user_id = #{userId} AND deleted = 0
        ORDER BY start_time DESC, create_time DESC
    </select>

    <!-- 根据条件查询学习历史列表 -->
    <select id="selectLearningHistories" resultMap="BaseResultMap">
        SELECT <include refid="Base_Column_List"/>
        FROM tb_learning_history
        <include refid="Where_Clause"/>
        ORDER BY start_time DESC, create_time DESC
    </select>

    <!-- 根据条件查询学习历史VO列表（带关联信息） -->
    <select id="selectLearningHistoriesWithDetails" resultMap="VOResultMap">
        SELECT 
            lh.id,
            lh.user_id,
            u.username as user_name,
            lh.history_id,
            lh.resource_type,
            lh.resource_id,
            CASE 
                WHEN lh.resource_type = 1 THEN r.title
                WHEN lh.resource_type = 2 THEN c.name
                WHEN lh.resource_type = 3 THEN ch.name
                WHEN lh.resource_type = 4 THEN n.name
            END as resource_title,
            lh.course_id,
            c.name as course_name,
            lh.chapter_id,
            ch.name as chapter_name,
            lh.node_id,
            n.name as node_name,
            lh.task_id,
            t.name as task_name,
            lh.start_time,
            lh.end_time,
            lh.duration,
            lh.start_progress,
            lh.end_progress,
            lh.device_type,
            lh.create_time
        FROM tb_learning_history lh
        LEFT JOIN tb_sys_user u ON lh.user_id = u.id
        LEFT JOIN tb_resource r ON lh.resource_type = 1 AND lh.resource_id = r.resource_id
        LEFT JOIN tb_course c ON lh.course_id = c.course_id
        LEFT JOIN tb_course_chapter ch ON lh.chapter_id = ch.chapter_id
        LEFT JOIN tb_course_node n ON lh.node_id = n.node_id
        LEFT JOIN tb_learning_task t ON lh.task_id = t.task_id
        <include refid="Where_Clause"/>
        ORDER BY lh.start_time DESC, lh.create_time DESC
    </select>

    <!-- 分页查询学习历史 -->
    <select id="selectLearningHistoriesPage" resultMap="BaseResultMap">
        SELECT <include refid="Base_Column_List"/>
        FROM tb_learning_history
        <include refid="Where_Clause"/>
        ORDER BY start_time DESC, create_time DESC
        LIMIT #{pageParam.offset}, #{pageParam.pageSize}
    </select>

    <!-- 统计学习历史总数 -->
    <select id="countLearningHistories" resultType="long">
        SELECT COUNT(*)
        FROM tb_learning_history
        <include refid="Where_Clause"/>
    </select>

    <!-- 根据时间段查询学习历史 -->
    <select id="selectByUserIdAndTimeRange" resultMap="BaseResultMap">
        SELECT <include refid="Base_Column_List"/>
        FROM tb_learning_history
        WHERE user_id = #{userId}
          AND start_time BETWEEN #{startTime} AND #{endTime}
          AND deleted = 0
        ORDER BY start_time DESC
    </select>

    <!-- 根据时间段和资源类型查询学习历史 -->
    <select id="selectByUserIdAndResourceTypeAndTimeRange" resultMap="BaseResultMap">
        SELECT <include refid="Base_Column_List"/>
        FROM tb_learning_history
        WHERE user_id = #{userId}
          AND resource_type = #{resourceType}
          AND start_time BETWEEN #{startTime} AND #{endTime}
          AND deleted = 0
        ORDER BY start_time DESC
    </select>

    <!-- 统计用户某时间段的学习时长 -->
    <select id="selectLearningStatisticsByTimeRange" resultType="map">
        SELECT 
            SUM(duration) as totalDuration,
            COUNT(*) as learnCount,
            COUNT(DISTINCT DATE(start_time)) as learnDays,
            COUNT(DISTINCT CASE WHEN resource_type = 1 THEN resource_id END) as resourceCount,
            COUNT(DISTINCT CASE WHEN resource_type = 2 THEN course_id END) as courseCount
        FROM tb_learning_history
        WHERE user_id = #{userId}
          AND start_time BETWEEN #{startTime} AND #{endTime}
          AND deleted = 0
    </select>

    <!-- 统计用户某时间段各资源类型的学习时长 -->
    <select id="selectLearningStatisticsByResourceType" resultType="map">
        SELECT 
            resource_type as resourceType,
            SUM(duration) as totalDuration,
            COUNT(*) as learnCount,
            COUNT(DISTINCT resource_id) as resourceCount
        FROM tb_learning_history
        WHERE user_id = #{userId}
          AND start_time BETWEEN #{startTime} AND #{endTime}
          AND deleted = 0
        GROUP BY resource_type
        ORDER BY resource_type
    </select>

    <!-- 获取用户学习的资源列表（按时间段） -->
    <select id="selectLearnedResourcesByTimeRange" resultType="map">
        SELECT 
            resource_type as resourceType,
            resource_id as resourceId,
            course_id as courseId,
            chapter_id as chapterId,
            node_id as nodeId,
            SUM(duration) as totalDuration,
            COUNT(*) as learnCount,
            MAX(start_time) as lastLearnTime
        FROM tb_learning_history
        WHERE user_id = #{userId}
          AND start_time BETWEEN #{startTime} AND #{endTime}
          AND deleted = 0
        GROUP BY resource_type, resource_id, course_id, chapter_id, node_id
        ORDER BY lastLearnTime DESC
    </select>

    <!-- 删除学习历史（软删除） -->
    <update id="deleteLearningHistory">
        UPDATE tb_learning_history
        SET deleted = 1, delete_time = NOW()
        WHERE id = #{id}
    </update>

    <!-- 批量删除学习历史（软删除） -->
    <update id="batchDeleteLearningHistories">
        UPDATE tb_learning_history
        SET deleted = 1, delete_time = NOW()
        WHERE id IN
        <foreach collection="ids" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>

    <!-- 根据会话ID查询学习历史 -->
    <select id="selectByhistoryID" resultMap="BaseResultMap">
        SELECT <include refid="Base_Column_List"/>
        FROM tb_learning_history
        WHERE history_id = #{historyID} AND deleted = 0
        ORDER BY start_time DESC
    </select>

    <!-- 获取用户最近的学习历史 -->
    <select id="selectRecentByUserId" resultMap="BaseResultMap">
        SELECT <include refid="Base_Column_List"/>
        FROM tb_learning_history
        WHERE user_id = #{userId} AND deleted = 0
        ORDER BY start_time DESC, create_time DESC
        LIMIT #{limit}
    </select>

</mapper>
