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

    <!-- 基础结果映射 -->
    <resultMap id="BaseResultMap" type="org.xyzh.common.dto.study.TbCourseTag">
        <id column="id" property="id" jdbcType="VARCHAR"/>
        <result column="course_id" property="courseID" jdbcType="VARCHAR"/>
        <result column="tag_id" property="tagID" jdbcType="VARCHAR"/>
        <result column="creator" property="creator" jdbcType="VARCHAR"/>
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
    </resultMap>

    <!-- 基础字段 -->
    <sql id="Base_Column_List">
        id, course_id, tag_id, creator, create_time
    </sql>

    <!-- 通用条件 -->
    <sql id="Where_Clause">
        <where>
            <if test="courseID != null and courseID != ''">
                AND course_id = #{courseID}
            </if>
            <if test="tagID != null and tagID != ''">
                AND tag_id = #{tagID}
            </if>
        </where>
    </sql>

    <!-- selectCourseTags -->
    <select id="selectCourseTags" resultMap="BaseResultMap">
        SELECT 
        <include refid="Base_Column_List"/>
        FROM tb_course_tag
        <include refid="Where_Clause"/>
        ORDER BY create_time DESC
    </select>

    <!-- 根据关联ID查询关联信息 -->
    <select id="selectByRelationId" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_course_tag
        WHERE id = #{relationId}
    </select>

    <!-- 根据课程ID查询标签关联列表 -->
    <select id="selectByCourseId" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_course_tag
        WHERE course_id = #{courseId}
        ORDER BY create_time DESC
    </select>

    <!-- 根据标签ID查询课程关联列表 -->
    <select id="selectByTagId" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_course_tag
        WHERE tag_id = #{tagId}
        ORDER BY create_time DESC
    </select>

    <!-- 根据课程ID和标签ID查询关联信息 -->
    <select id="selectByCourseIdAndTagId" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_course_tag
        WHERE course_id = #{courseId} AND tag_id = #{tagId}
    </select>

    <!-- 检查课程标签关联是否存在 -->
    <select id="countByCourseIdAndTagId" resultType="int">
        SELECT COUNT(1)
        FROM tb_course_tag
        WHERE course_id = #{courseId} AND tag_id = #{tagId}
        <if test="excludeId != null and excludeId != ''">
            AND id != #{excludeId}
        </if>
    </select>

    <!-- 插入课程标签关联 -->
    <insert id="insertCourseTag" parameterType="org.xyzh.common.dto.study.TbCourseTag">
        INSERT INTO tb_course_tag (
            id, course_id, tag_id, creator, create_time
        ) VALUES (
            #{id}, #{courseID}, #{tagID}, #{creator}, #{createTime}
        )
    </insert>

    <!-- 更新课程标签关联 -->
    <update id="updateCourseTag" parameterType="org.xyzh.common.dto.study.TbCourseTag">
        UPDATE tb_course_tag
        <set>
            <if test="courseID != null and courseID != ''">
                course_id = #{courseID},
            </if>
            <if test="tagID != null and tagID != ''">
                tag_id = #{tagID},
            </if>
            <if test="creator != null and creator != ''">
                creator = #{creator},
            </if>
            <if test="createTime != null">
                create_time = #{createTime},
            </if>
        </set>
        WHERE id = #{id}
    </update>

    <!-- 删除课程标签关联 -->
    <delete id="deleteCourseTag" parameterType="org.xyzh.common.dto.study.TbCourseTag">
        DELETE FROM tb_course_tag
        WHERE id = #{id}
    </delete>

    <!-- 批量插入课程标签关联 -->
    <insert id="batchInsertCourseTags" parameterType="java.util.List">
        INSERT INTO tb_course_tag (
            id, course_id, tag_id, creator, create_time
        ) VALUES
        <foreach collection="courseTagList" item="item" separator=",">
            (
                #{item.id}, #{item.courseID}, #{item.tagID}, #{item.creator}, #{item.createTime}
            )
        </foreach>
    </insert>

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

    <!-- 根据课程ID批量删除标签关联 -->
    <delete id="deleteByCourseId">
        DELETE FROM tb_course_tag
        WHERE course_id = #{courseId}
    </delete>

    <!-- 根据标签ID批量删除课程关联 -->
    <delete id="deleteByTagId">
        DELETE FROM tb_course_tag
        WHERE tag_id = #{tagId}
    </delete>

    <!-- 分页查询课程标签关联 -->
    <select id="selectCourseTagsPage" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_course_tag
        <include refid="Where_Clause" />
        ORDER BY create_time DESC
        LIMIT #{pageParam.pageSize} OFFSET #{pageParam.offset}
    </select>

    <!-- 统计课程标签关联总数 -->
    <select id="countCourseTags" resultType="long">
        SELECT COUNT(1)
        FROM tb_course_tag
        <include refid="Where_Clause" />
    </select>

</mapper>
