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

    <!-- 基础结果映射 -->
    <resultMap id="BaseResultMap" type="org.xyzh.common.dto.study.TbCourse">
        <id column="id" property="id" jdbcType="VARCHAR"/>
        <result column="course_id" property="courseID" jdbcType="VARCHAR"/>
        <result column="name" property="name" jdbcType="VARCHAR"/>
        <result column="cover_image" property="coverImage" jdbcType="VARCHAR"/>
        <result column="description" property="description" jdbcType="LONGVARCHAR"/>
        <result column="content" property="content" jdbcType="LONGVARCHAR"/>
        <result column="duration" property="duration" jdbcType="INTEGER"/>
        <result column="teacher" property="teacher" jdbcType="VARCHAR"/>
        <result column="status" property="status" jdbcType="INTEGER"/>
        <result column="view_count" property="viewCount" jdbcType="INTEGER"/>
        <result column="learn_count" property="learnCount" jdbcType="INTEGER"/>
        <result column="order_num" property="orderNum" 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>

    <!-- 基础字段 -->
    <sql id="Base_Column_List">
        id, course_id, name, cover_image, description, content, duration, 
        teacher, status, view_count, learn_count, order_num, creator, updater, 
        create_time, update_time, delete_time, deleted
    </sql>

    <!-- 通用条件 -->
    <sql id="Where_Clause">
        <where>
            deleted = 0
            <if test="courseID != null and courseID != ''">
                AND course_id = #{courseID}
            </if>
            <if test="name != null and name != ''">
                AND name LIKE CONCAT('%', #{name}, '%')
            </if>
            <if test="teacher != null and teacher != ''">
                AND teacher LIKE CONCAT('%', #{teacher}, '%')
            </if>
            <if test="status != null">
                AND status = #{status}
            </if>
        </where>
    </sql>
    <sql id="Filter_Clause">
        <if test="filter != null">
            <if test="filter.courseID != null and filter.courseID != ''">
                AND course_id = #{filter.courseID}
            </if>
        </if>
        <if test="filter.name != null and filter.name != ''">
            AND name LIKE CONCAT('%', #{filter.name}, '%')
        </if>
        <if test="filter.teacher != null and filter.teacher != ''">
            AND teacher LIKE CONCAT('%', #{filter.teacher}, '%')
        </if>
        <if test="filter.status != null">
            AND status = #{filter.status}
        </if>
        <if test="filter.orderNum != null">
            AND order_num = #{filter.orderNum}
        </if>
        <if test="filter.creator != null and filter.creator != ''">
            AND creator = #{filter.creator}
        </if>
        <if test="filter.createTime != null">
            AND create_time = #{filter.createTime}
        </if>
    </sql>

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

    <!-- 根据课程ID查询课程信息 -->
    <select id="selectByCourseId" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_course
        WHERE course_id = #{courseId} AND deleted = 0
    </select>

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

    <!-- 根据状态查询课程列表 -->
    <select id="selectByStatus" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_course
        WHERE status = #{status} AND deleted = 0
        ORDER BY order_num ASC, create_time DESC
    </select>

    <!-- 根据类型查询课程列表 -->
    <select id="selectByType" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_course
        WHERE deleted = 0
        ORDER BY order_num ASC, create_time DESC
    </select>

    <!-- 根据难度查询课程列表 -->
    <select id="selectByDifficulty" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_course
        WHERE deleted = 0
        ORDER BY order_num ASC, create_time DESC
    </select>

    <!-- 查询热门课程列表 -->
    <select id="selectHotCourses" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_course
        WHERE status = 1 AND deleted = 0
        ORDER BY view_count DESC, learn_count DESC, create_time DESC
        <if test="limit != null and limit > 0">
            LIMIT #{limit}
        </if>
    </select>

    <!-- 查询最新课程列表 -->
    <select id="selectLatestCourses" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_course
        WHERE status = 1 AND deleted = 0
        ORDER BY create_time DESC
        <if test="limit != null and limit > 0">
            LIMIT #{limit}
        </if>
    </select>

    <!-- 根据关键词搜索课程 -->
    <select id="searchByKeyword" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_course
        WHERE (name LIKE CONCAT('%', #{keyword}, '%') 
               OR description LIKE CONCAT('%', #{keyword}, '%')
               OR content LIKE CONCAT('%', #{keyword}, '%')
               OR teacher LIKE CONCAT('%', #{keyword}, '%'))
        AND status = 1 AND deleted = 0
        ORDER BY order_num ASC, create_time DESC
    </select>

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

    <!-- 插入课程 -->
    <insert id="insertCourse" parameterType="org.xyzh.common.dto.study.TbCourse">
        INSERT INTO tb_course (
            id, course_id, name, cover_image, description, content, duration,
            teacher, status, view_count, learn_count, order_num, creator, create_time
        ) VALUES (
            #{id}, #{courseID}, #{name}, #{coverImage}, #{description}, #{content}, #{duration},
            #{teacher}, #{status}, #{viewCount}, #{learnCount}, #{orderNum}, #{creator},#{createTime}
        )
    </insert>

    <!-- 更新课程 -->
    <update id="updateCourse" parameterType="org.xyzh.common.dto.study.TbCourse">
        UPDATE tb_course
        <set>
            <if test="courseID != null and courseID != ''">
                course_id = #{courseID},
            </if>
            <if test="name != null and name != ''">
                name = #{name},
            </if>
            <if test="coverImage != null and coverImage != ''">
                cover_image = #{coverImage},
            </if>
            <if test="description != null and description != ''">
                description = #{description},
            </if>
            <if test="content != null and content != ''">
                content = #{content},
            </if>
            <if test="duration != null">
                duration = #{duration},
            </if>
            <if test="teacher != null and teacher != ''">
                teacher = #{teacher},
            </if>
            <if test="status != null">
                status = #{status},
            </if>
            <if test="viewCount != null">
                view_count = #{viewCount},
            </if>
            <if test="learnCount != null">
                learn_count = #{learnCount},
            </if>
            <if test="orderNum != null">
                order_num = #{orderNum},
            </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="deleteCourse" parameterType="org.xyzh.common.dto.study.TbCourse">
        DELETE FROM tb_course
        WHERE id = #{id}
    </delete>

    <!-- 批量插入课程 -->
    <insert id="batchInsertCourses" parameterType="java.util.List">
        INSERT INTO tb_course (
            id, course_id, name, cover_image, description, content, duration,
            teacher, status, view_count, learn_count, order_num, creator, updater,
            create_time, update_time, delete_time, deleted
        ) VALUES
        <foreach collection="courseList" item="item" separator=",">
            (
                #{item.id}, #{item.courseID}, #{item.name}, #{item.coverImage}, #{item.description}, 
                #{item.content}, #{item.duration}, #{item.teacher}, #{item.status}, #{item.viewCount}, 
                #{item.learnCount}, #{item.orderNum}, #{item.creator}, #{item.updater}, 
                #{item.createTime}, #{item.updateTime}, #{item.deleteTime}, #{item.deleted}
            )
        </foreach>
    </insert>

    <!-- 批量删除课程 -->
    <delete id="batchDeleteCourses">
        DELETE FROM tb_course
        WHERE id IN
        <foreach collection="ids" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <!-- 分页查询课程 -->
    <select id="selectCoursesPage" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_course
        <include refid="Filter_Clause" />
        ORDER BY order_num ASC, create_time DESC
        LIMIT #{pageParam.pageSize} OFFSET #{pageParam.offset}
    </select>

    <!-- 统计课程总数 -->
    <select id="countCourses" resultType="long">
        SELECT COUNT(1)
        FROM tb_course
        <include refid="Filter_Clause" />
    </select>

</mapper>
