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

    <!-- 基础结果映射 -->
    <resultMap id="BaseResultMap" type="org.xyzh.common.dto.study.TbLearningStatistics">
        <id column="id" property="id" jdbcType="VARCHAR"/>
        <result column="user_id" property="userID" jdbcType="VARCHAR"/>
        <result column="stat_date" property="statDate" jdbcType="DATE"/>
        <result column="total_duration" property="totalDuration" jdbcType="INTEGER"/>
        <result column="resource_count" property="resourceCount" jdbcType="INTEGER"/>
        <result column="course_count" property="courseCount" jdbcType="INTEGER"/>
        <result column="complete_count" property="completeCount" jdbcType="INTEGER"/>
        <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, stat_date, total_duration, resource_count, course_count, 
        complete_count, create_time, update_time
    </sql>

    <!-- 通用条件 -->
    <sql id="Where_Clause">
        <where>
            <if test="userID != null and userID != ''">
                AND user_id = #{userID}
            </if>
            <if test="statDate != null">
                AND stat_date = #{statDate}
            </if>
        </where>
    </sql>

    <!-- selectLearningStatistics -->
    <select id="selectLearningStatistics" resultMap="BaseResultMap">
        SELECT 
        <include refid="Base_Column_List"/>
        FROM tb_learning_statistics
        <include refid="Where_Clause"/>
        ORDER BY stat_date DESC
    </select>

    <!-- 根据统计ID查询统计信息 -->
    <select id="selectByStatisticsId" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_learning_statistics
        WHERE id = #{statisticsId}
    </select>

    <!-- 根据用户ID查询学习统计 -->
    <select id="selectByUserId" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_learning_statistics
        WHERE user_id = #{userId}
        ORDER BY stat_date DESC
    </select>

    <!-- 根据课程ID查询学习统计列表 -->
    <select id="selectByCourseId" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_learning_statistics
        ORDER BY stat_date DESC
    </select>

    <!-- 根据用户ID和课程ID查询学习统计 -->
    <select id="selectByUserIdAndCourseId" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_learning_statistics
        WHERE user_id = #{userId}
        ORDER BY stat_date DESC
    </select>

    <!-- 根据统计类型查询学习统计列表 -->
    <select id="selectByType" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_learning_statistics
        ORDER BY stat_date DESC
    </select>

    <!-- 查询用户学习排行榜 -->
    <select id="selectLearningRanking" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_learning_statistics
        ORDER BY total_duration DESC, complete_count DESC, stat_date DESC
        <if test="limit != null and limit > 0">
            LIMIT #{limit}
        </if>
    </select>

    <!-- 插入学习统计 -->
    <insert id="insertLearningStatistics" parameterType="org.xyzh.common.dto.study.TbLearningStatistics">
        INSERT INTO tb_learning_statistics (
            id, user_id, stat_date, total_duration, resource_count, course_count,
            complete_count, create_time, update_time
        ) VALUES (
            #{id}, #{userID}, #{statDate}, #{totalDuration}, #{resourceCount}, #{courseCount},
            #{completeCount}, #{createTime}, #{updateTime}
        )
    </insert>

    <!-- 更新学习统计 -->
    <update id="updateLearningStatistics" parameterType="org.xyzh.common.dto.study.TbLearningStatistics">
        UPDATE tb_learning_statistics
        <set>
            <if test="userID != null and userID != ''">
                user_id = #{userID},
            </if>
            <if test="statDate != null">
                stat_date = #{statDate},
            </if>
            <if test="totalDuration != null">
                total_duration = #{totalDuration},
            </if>
            <if test="resourceCount != null">
                resource_count = #{resourceCount},
            </if>
            <if test="courseCount != null">
                course_count = #{courseCount},
            </if>
            <if test="completeCount != null">
                complete_count = #{completeCount},
            </if>
            <if test="updateTime != null">
                update_time = #{updateTime},
            </if>
        </set>
        WHERE id = #{id}
    </update>

    <!-- 删除学习统计 -->
    <delete id="deleteLearningStatistics" parameterType="org.xyzh.common.dto.study.TbLearningStatistics">
        DELETE FROM tb_learning_statistics
        WHERE id = #{id}
    </delete>

    <!-- 批量插入学习统计 -->
    <insert id="batchInsertLearningStatistics" parameterType="java.util.List">
        INSERT INTO tb_learning_statistics (
            id, user_id, stat_date, total_duration, resource_count, course_count,
            complete_count, create_time, update_time
        ) VALUES
        <foreach collection="learningStatisticsList" item="item" separator=",">
            (
                #{item.id}, #{item.userID}, #{item.statDate}, #{item.totalDuration}, 
                #{item.resourceCount}, #{item.courseCount}, #{item.completeCount}, 
                #{item.createTime}, #{item.updateTime}
            )
        </foreach>
    </insert>

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

    <!-- 分页查询学习统计 -->
    <select id="selectLearningStatisticsPage" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_learning_statistics
        <include refid="Where_Clause" />
        ORDER BY stat_date DESC
        LIMIT #{pageParam.pageSize} OFFSET #{pageParam.offset}
    </select>

    <!-- 统计学习统计总数 -->
    <select id="countLearningStatistics" resultType="long">
        SELECT COUNT(1)
        FROM tb_learning_statistics
        <include refid="Where_Clause" />
    </select>

</mapper>
