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

    <!-- 基础结果映射 -->
    <resultMap id="BaseResultMap" type="org.xyzh.common.dto.study.TbLearningStatisticsDetail">
        <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="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="total_duration" property="totalDuration" jdbcType="INTEGER"/>
        <result column="learn_count" property="learnCount" jdbcType="INTEGER"/>
        <result column="is_complete" property="isComplete" jdbcType="BOOLEAN"/>
        <result column="complete_time" property="completeTime" jdbcType="TIMESTAMP"/>
        <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="deleted" property="deleted" jdbcType="BOOLEAN"/>
    </resultMap>

    <!-- VO结果映射（带关联信息） -->
    <resultMap id="VOResultMap" type="org.xyzh.common.vo.LearningStatisticsDetailVO">
        <result column="stat_date" property="statDate" jdbcType="DATE"/>
        <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="total_duration" property="totalDuration" jdbcType="INTEGER"/>
        <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>

    <!-- 基础字段 -->
    <sql id="Base_Column_List">
        id, user_id, stat_date, resource_type, resource_id, course_id, chapter_id,
        total_duration, learn_count, is_complete, complete_time,
        creator, updater, create_time, update_time, deleted
    </sql>

    <!-- 通用条件 -->
    <sql id="Where_Clause">
        <where>
            deleted = 0
            <if test="userID != null and userID != ''">
                AND user_id = #{userID}
            </if>
            <if test="statDate != null">
                AND stat_date = #{statDate}
            </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="isComplete != null">
                AND is_complete = #{isComplete}
            </if>
        </where>
    </sql>

    <!-- 插入统计明细 -->
    <insert id="insertStatisticsDetail">
        INSERT INTO tb_learning_statistics_detail (
            id, user_id, stat_date, resource_type, resource_id, course_id, chapter_id,
            total_duration, learn_count, is_complete, complete_time,
            creator, create_time, deleted
        ) VALUES (
            #{id}, #{userID}, #{statDate}, #{resourceType}, #{resourceID}, #{courseID}, #{chapterID},
            #{totalDuration}, #{learnCount}, #{isComplete}, #{completeTime},
            #{creator}, #{createTime}, 0
        )
    </insert>

    <!-- 更新统计明细 -->
    <update id="updateStatisticsDetail">
        UPDATE tb_learning_statistics_detail
        <set>
            <if test="totalDuration != null">
                total_duration = #{totalDuration},
            </if>
            <if test="learnCount != null">
                learn_count = #{learnCount},
            </if>
            <if test="isComplete != null">
                is_complete = #{isComplete},
            </if>
            <if test="completeTime != null">
                complete_time = #{completeTime},
            </if>
            <if test="updater != null">
                updater = #{updater},
            </if>
            update_time = NOW()
        </set>
        WHERE id = #{id}
    </update>

    <!-- 插入或更新统计明细（ON DUPLICATE KEY UPDATE） -->
    <insert id="insertOrUpdateStatisticsDetail">
        INSERT INTO tb_learning_statistics_detail (
            id, user_id, stat_date, resource_type, resource_id, course_id, chapter_id,
            total_duration, learn_count, is_complete, complete_time,
            creator, create_time, deleted
        ) VALUES (
            #{id}, #{userID}, #{statDate}, #{resourceType}, #{resourceID}, #{courseID}, #{chapterID},
            #{totalDuration}, #{learnCount}, #{isComplete}, #{completeTime},
            #{creator}, NOW(), 0
        )
        ON DUPLICATE KEY UPDATE
            total_duration = total_duration + #{totalDuration},
            learn_count = learn_count + #{learnCount},
            is_complete = #{isComplete},
            complete_time = #{completeTime},
            updater = #{updater},
            update_time = NOW()
    </insert>

    <!-- 根据条件查询统计明细 -->
    <select id="selectStatisticsDetails" resultMap="BaseResultMap">
        SELECT <include refid="Base_Column_List"/>
        FROM tb_learning_statistics_detail
        <include refid="Where_Clause"/>
        ORDER BY stat_date DESC, create_time DESC
    </select>

    <!-- 根据用户ID和日期查询统计明细 -->
    <select id="selectByUserIdAndDate" resultMap="BaseResultMap">
        SELECT <include refid="Base_Column_List"/>
        FROM tb_learning_statistics_detail
        WHERE user_id = #{userId}
          AND stat_date = #{statDate}
          AND deleted = 0
        ORDER BY resource_type, total_duration DESC
    </select>

    <!-- 根据用户ID和日期范围查询统计明细 -->
    <select id="selectByUserIdAndDateRange" resultMap="BaseResultMap">
        SELECT <include refid="Base_Column_List"/>
        FROM tb_learning_statistics_detail
        WHERE user_id = #{userId}
          AND stat_date BETWEEN #{startDate} AND #{endDate}
          AND deleted = 0
        ORDER BY stat_date DESC, resource_type, total_duration DESC
    </select>

    <!-- 根据用户ID和日期范围查询统计明细VO（带关联信息） -->
    <select id="selectStatisticsDetailsWithInfo" resultMap="VOResultMap">
        SELECT 
            sd.stat_date,
            sd.resource_type,
            sd.resource_id,
            CASE 
                WHEN sd.resource_type = 1 THEN r.title
                WHEN sd.resource_type = 2 THEN c.name
                WHEN sd.resource_type = 3 THEN ch.name
            END as resource_title,
            sd.course_id,
            c.name as course_name,
            sd.chapter_id,
            ch.name as chapter_name,
            sd.total_duration,
            sd.learn_count,
            sd.is_complete,
            sd.complete_time
        FROM tb_learning_statistics_detail sd
        LEFT JOIN tb_resource r ON sd.resource_type = 1 AND sd.resource_id = r.resource_id
        LEFT JOIN tb_course c ON sd.course_id = c.course_id
        LEFT JOIN tb_course_chapter ch ON sd.chapter_id = ch.chapter_id
        WHERE sd.user_id = #{userId}
          AND sd.stat_date BETWEEN #{startDate} AND #{endDate}
          AND sd.deleted = 0
        ORDER BY sd.stat_date DESC, sd.total_duration DESC
    </select>

    <!-- 根据用户ID、资源类型和日期查询统计明细 -->
    <select id="selectByUserIdAndResourceTypeAndDate" resultMap="BaseResultMap">
        SELECT <include refid="Base_Column_List"/>
        FROM tb_learning_statistics_detail
        WHERE user_id = #{userId}
          AND resource_type = #{resourceType}
          AND resource_id = #{resourceId}
          AND stat_date = #{statDate}
          AND deleted = 0
        LIMIT 1
    </select>

    <!-- 删除统计明细（软删除） -->
    <update id="deleteStatisticsDetail">
        UPDATE tb_learning_statistics_detail
        SET deleted = 1, delete_time = NOW()
        WHERE id = #{id}
    </update>

    <!-- 批量删除统计明细（软删除） -->
    <update id="batchDeleteStatisticsDetails">
        UPDATE tb_learning_statistics_detail
        SET deleted = 1, delete_time = NOW()
        WHERE id IN
        <foreach collection="ids" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>

    <!-- 按日期汇总用户学习统计 -->
    <select id="selectDailySummary" resultMap="BaseResultMap">
        SELECT 
            NULL as id,
            user_id,
            stat_date,
            NULL as resource_type,
            NULL as resource_id,
            NULL as course_id,
            NULL as chapter_id,
            SUM(total_duration) as total_duration,
            SUM(learn_count) as learn_count,
            NULL as is_complete,
            NULL as complete_time,
            NULL as creator,
            NULL as updater,
            MIN(create_time) as create_time,
            MAX(update_time) as update_time,
            0 as deleted
        FROM tb_learning_statistics_detail
        WHERE user_id = #{userId}
          AND stat_date BETWEEN #{startDate} AND #{endDate}
          AND deleted = 0
        GROUP BY user_id, stat_date
        ORDER BY stat_date DESC
    </select>

</mapper>
