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

    <!-- 基础结果映射 -->
    <resultMap id="BaseResultMap" type="org.xyzh.common.dto.study.TbCourseNode">
        <id column="id" property="id" jdbcType="VARCHAR"/>
        <result column="node_id" property="nodeID" jdbcType="VARCHAR"/>
        <result column="chapter_id" property="chapterID" jdbcType="VARCHAR"/>
        <result column="name" property="name" jdbcType="VARCHAR"/>
        <result column="content" property="content" jdbcType="LONGVARCHAR"/>
        <result column="node_type" property="nodeType" jdbcType="INTEGER"/>
        <result column="resource_id" property="resourceID" jdbcType="VARCHAR"/>
        <result column="video_url" property="videoUrl" jdbcType="VARCHAR"/>
        <result column="duration" property="duration" jdbcType="INTEGER"/>
        <result column="order_num" property="orderNum" jdbcType="INTEGER"/>
        <result column="is_required" property="isRequired" jdbcType="INTEGER"/>
        <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="delete_time" property="deleteTime" jdbcType="TIMESTAMP"/>
        <result column="deleted" property="deleted" jdbcType="BOOLEAN"/>
    </resultMap>

    <resultMap id="CourseItemResultMap" type="org.xyzh.common.vo.CourseItemVO">
        <id column="id" property="id" jdbcType="VARCHAR"/>
        <result column="node_id" property="nodeID" jdbcType="VARCHAR"/>
        <result column="chapter_id" property="chapterID" jdbcType="VARCHAR"/>
        <result column="name" property="name" jdbcType="VARCHAR"/>
        <result column="content" property="content" jdbcType="LONGVARCHAR"/>
        <result column="node_type" property="nodeType" jdbcType="INTEGER"/>
        <result column="resource_id" property="resourceID" jdbcType="VARCHAR"/>
        <result column="video_url" property="videoUrl" jdbcType="VARCHAR"/>
        <result column="duration" property="duration" jdbcType="INTEGER"/>
        <result column="order_num" property="orderNum" jdbcType="INTEGER"/>
        <result column="is_required" property="isRequired" jdbcType="INTEGER"/>
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
        <!-- 学习记录相关字段 -->
        <result column="progress" property="progress" jdbcType="DECIMAL"/>
        <result column="is_complete" property="isComplete" jdbcType="BOOLEAN"/>
        <result column="learning_duration" property="learningDuration" jdbcType="INTEGER"/>
        <result column="last_learn_time" property="lastLearnTime" jdbcType="TIMESTAMP"/>
        <result column="complete_time" property="completeTime" jdbcType="TIMESTAMP"/>
    </resultMap>

    <!-- 基础字段 -->
    <sql id="Base_Column_List">
        id, node_id, chapter_id, name, content, node_type, resource_id, video_url, duration, order_num, is_required,
        creator, updater, create_time, update_time, delete_time, deleted
    </sql>

    <!-- 通用条件 -->
    <sql id="Where_Clause">
        <where>
            deleted = 0
            <if test="nodeID != null and nodeID != ''">
                AND node_id = #{nodeID}
            </if>
            <if test="chapterID != null and chapterID != ''">
                AND chapter_id = #{chapterID}
            </if>
            <if test="name != null and name != ''">
                AND name LIKE CONCAT('%', #{name}, '%')
            </if>
            <if test="nodeType != null">
                AND node_type = #{nodeType}
            </if>
            <if test="isRequired != null">
                AND is_required = #{isRequired}
            </if>
        </where>
    </sql>

    <!-- selectCourseNodes -->
    <select id="selectCourseNodes" resultMap="BaseResultMap">
        SELECT 
        <include refid="Base_Column_List"/>
        FROM tb_course_node
        <include refid="Where_Clause"/>
        ORDER BY order_num ASC, create_time ASC
    </select>

    <!-- 根据节点ID查询节点信息 -->
    <select id="selectByNodeId" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_course_node
        WHERE node_id = #{nodeID} AND deleted = 0
    </select>

    <!-- 根据章节ID查询节点列表 -->
    <select id="selectByChapterId" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_course_node
        WHERE chapter_id = #{chapterId} AND deleted = 0
        ORDER BY order_num ASC, create_time ASC
    </select>

    <!-- 根据节点名称查询节点 -->
    <select id="selectByName" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_course_node
        WHERE name = #{name} AND deleted = 0
    </select>

    <!-- 根据节点类型查询节点列表 -->
    <select id="selectByNodeType" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_course_node
        WHERE node_type = #{nodeType} AND deleted = 0
        ORDER BY order_num ASC, create_time ASC
    </select>

    <!-- 根据章节ID和排序查询节点列表 -->
    <select id="selectByChapterIdOrderBySort" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_course_node
        WHERE chapter_id = #{chapterId} AND deleted = 0
        ORDER BY order_num ASC, create_time ASC
    </select>

    <!-- 检查节点名称是否存在 -->
    <select id="countByName" resultType="int">
        SELECT COUNT(1)
        FROM tb_course_node
        WHERE name = #{name} AND deleted = 0
        <if test="excludeId != null and excludeId != ''">
            AND id != #{excludeId}
        </if>
    </select>

    <!-- 插入学习节点 -->
    <insert id="insertCourseNode" parameterType="org.xyzh.common.dto.study.TbCourseNode">
        INSERT INTO tb_course_node (
            id, chapter_id, name, content, node_type, resource_id, video_url, duration, order_num, is_required,
            creator, updater, create_time, update_time, delete_time, deleted
        ) VALUES (
            #{id}, #{chapterID}, #{name}, #{content}, #{nodeType}, #{resourceID}, #{videoUrl}, #{duration}, #{orderNum}, #{isRequired},
            #{creator}, #{updater}, #{createTime}, #{updateTime}, #{deleteTime}, #{deleted}
        )
    </insert>

    <!-- 更新学习节点 -->
    <update id="updateCourseNode" parameterType="org.xyzh.common.dto.study.TbCourseNode">
        UPDATE tb_course_node
        <set>
            <if test="chapterID != null and chapterID != ''">
                chapter_id = #{chapterID},
            </if>
            <if test="name != null and name != ''">
                name = #{name},
            </if>
            <if test="content != null and content != ''">
                content = #{content},
            </if>
            <if test="nodeType != null">
                node_type = #{nodeType},
            </if>
            <if test="resourceID != null">
                resource_id = #{resourceID},
            </if>
            <if test="videoUrl != null and videoUrl != ''">
                video_url = #{videoUrl},
            </if>
            <if test="duration != null">
                duration = #{duration},
            </if>
            <if test="orderNum != null">
                order_num = #{orderNum},
            </if>
            <if test="isRequired != null">
                is_required = #{isRequired},
            </if>
            <if test="updater != null and updater != ''">
                updater = #{updater},
            </if>
            <if test="updateTime != null">
                update_time = #{updateTime},
            </if>
            <if test="deleteTime != null">
                delete_time = #{deleteTime},
            </if>
            <if test="deleted != null">
                deleted = #{deleted},
            </if>
        </set>
        WHERE id = #{id}
    </update>

    <!-- 删除学习节点 -->
    <delete id="deleteCourseNode" parameterType="org.xyzh.common.dto.study.TbCourseNode">
        DELETE FROM tb_course_node
        WHERE node_id = #{nodeID}
    </delete>

    <!-- 批量插入学习节点 -->
    <insert id="batchInsertCourseNodes" parameterType="java.util.List">
        INSERT INTO tb_course_node (
            id,node_id, chapter_id, name, content, node_type, resource_id, video_url, duration, order_num, is_required,
            creator, create_time
        ) VALUES
        <foreach collection="courseNodeList" item="item" separator=",">
            (
                #{item.id}, #{item.nodeID}, #{item.chapterID}, #{item.name}, #{item.content}, #{item.nodeType}, 
                #{item.resourceID}, #{item.videoUrl}, #{item.duration}, #{item.orderNum}, #{item.isRequired},
                #{item.creator}, #{item.createTime}
            )
        </foreach>
    </insert>

    <!-- 批量删除学习节点 -->
    <delete id="batchDeleteCourseNodes">
        DELETE FROM tb_course_node
        WHERE node_id IN
        <foreach collection="nodeIDs" item="nodeID" open="(" separator="," close=")">
            #{nodeID}
        </foreach>
    </delete>

    <!-- 根据章节ID批量删除节点 -->
    <delete id="deleteByChapterId">
        DELETE FROM tb_course_node
        WHERE chapter_id = #{chapterId}
    </delete>

    <!-- 分页查询学习节点 -->
    <select id="selectCourseNodesPage" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_course_node
        <include refid="Where_Clause" />
        ORDER BY order_num ASC, create_time ASC
        LIMIT #{pageParam.pageSize} OFFSET #{pageParam.offset}
    </select>

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

    <!-- selectCourseNodesByChapterIDs --> 
    <select id="selectCourseNodesByChapterIDs" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_course_node
        WHERE chapter_id IN
        <foreach collection="chapterIDs" item="chapterID" open="(" separator="," close=")">
            #{chapterID}
        </foreach>
        AND deleted = 0
        ORDER BY order_num ASC, create_time ASC
    </select>
    <!-- selectNodesProgress --> 

    <select id="selectNodesProgress" resultMap="CourseItemResultMap">
        SELECT
        tcn.id,
        tcn.node_id,
        tcn.chapter_id,
        tcn.`name`,
        tcn.content,
        tcn.node_type,
        tcn.resource_id,
        tcn.video_url,
        tcn.duration,
        tcn.order_num,
        tcn.is_required,
        tcn.create_time,
        tlr.duration as learning_duration,
        tlr.progress,
        tlr.is_complete,
        tlr.last_learn_time,
        tlr.complete_time
        FROM
            tb_course_node tcn
            LEFT JOIN tb_learning_record tlr ON tcn.node_id = tlr.node_id 
            <if test="userID != null and userID != ''">
                AND tlr.user_id = #{userID}
            </if>
        WHERE
            tcn.chapter_id IN
            <foreach collection="chapterIDs" item="chapterID" open="(" separator="," close=")">
                #{chapterID}
            </foreach>
            AND tcn.deleted = 0
        ORDER BY
            tcn.order_num ASC,
            tcn.create_time ASC
    </select>
</mapper>
