<?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.usercenter.mapper.UserCollectionMapper">

    <!-- 结果映射 -->
    <resultMap id="BaseResultMap" type="org.xyzh.common.dto.usercenter.TbUserCollection">
        <id column="id" property="id" />
        <result column="user_id" property="userID" />
        <result column="collection_type" property="collectionType" />
        <result column="collection_id" property="collectionID" />
        <result column="create_time" property="createTime" />
    </resultMap>
    
    <!-- UserCollectionVO结果映射 -->
    <resultMap id="UserCollectionVOResultMap" type="org.xyzh.common.vo.UserCollectionVO">
        <!-- 收藏基本信息 -->
        <id column="id" property="id" />
        <result column="user_id" property="userID" />
        <result column="collection_type" property="collectionType" />
        <result column="collection_id" property="collectionID" />
        <result column="collection_time" property="collectionTime" />
        <result column="create_time" property="createTime" />
        
        <!-- 资源详情（collection_type=1时有效） -->
        <result column="resource_id" property="resourceID" />
        <result column="resource_title" property="title" />
        <result column="resource_content" property="content" />
        <result column="resource_summary" property="summary" />
        <result column="resource_cover_image" property="coverImage" />
        <result column="resource_tag_id" property="tagID" />
        <result column="resource_author" property="author" />
        <result column="resource_source" property="source" />
        <result column="resource_view_count" property="viewCount" />
        <result column="resource_like_count" property="likeCount" />
        <result column="resource_collect_count" property="collectCount" />
        <result column="resource_status" property="status" />
        <result column="resource_publish_time" property="publishTime" />
        
        <!-- 课程详情（collection_type=2时有效） -->
        <result column="course_id" property="courseID" />
        <result column="course_name" property="courseName" />
        <result column="course_description" property="description" />
        <result column="course_duration" property="duration" />
        <result column="course_teacher" property="teacher" />
        <result column="course_status" property="courseStatus" />
        <result column="course_view_count" property="viewCount" />
        <result column="course_learn_count" property="learnCount" />
    </resultMap>

    <!-- 字段列表 -->
    <sql id="Base_Column_List">
        id, user_id, collection_type, collection_id, create_time
    </sql>

    <!-- 查询条件 -->
    <sql id="Base_Where_Clause">
        <where>
            <if test="filter != null">
                <if test="filter.id != null and filter.id != ''">
                    AND id = #{filter.id}
                </if>
                <if test="filter.userID != null and filter.userID != ''">
                    AND user_id = #{filter.userID}
                </if>
                <if test="filter.collectionType != null">
                    AND collection_type = #{filter.collectionType}
                </if>
                <if test="filter.collectionID != null and filter.collectionID != ''">
                    AND collection_id = #{filter.collectionID}
                </if>
                <if test="filter.createTime != null">
                    AND create_time = #{filter.createTime}
                </if>
            </if>
        </where>
    </sql>

    <!-- 根据ID查询单条记录 -->
    <select id="selectById" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_user_collection
        WHERE id = #{id}
    </select>

    <!-- 根据条件查询单条记录 -->
    <select id="selectOne" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_user_collection
        <include refid="Base_Where_Clause" />
        LIMIT 1
    </select>

    <!-- 根据条件查询列表 -->
    <select id="selectList" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_user_collection
        <include refid="Base_Where_Clause" />
    </select>

    <!-- 根据条件分页查询 -->
    <select id="selectPage" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_user_collection
        <include refid="Base_Where_Clause" />
    </select>

    <!-- 根据条件查询记录（支持排序） -->
    <select id="selectListWithOrder" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_user_collection
        <include refid="Base_Where_Clause" />
        <if test="orderBy != null and orderBy != ''">
            ORDER BY ${orderBy}
            <if test="orderDirection != null and orderDirection != ''">
                ${orderDirection}
            </if>
        </if>
    </select>

    <!-- 根据条件分页查询（支持排序） -->
    <select id="selectPageWithOrder" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_user_collection
        <include refid="Base_Where_Clause" />
        <if test="orderBy != null and orderBy != ''">
            ORDER BY ${orderBy}
            <if test="orderDirection != null and orderDirection != ''">
                ${orderDirection}
            </if>
        </if>
    </select>

    <!-- 插入单条记录 -->
    <insert id="insertOne" parameterType="org.xyzh.common.dto.usercenter.TbUserCollection">
        INSERT INTO tb_user_collection (
            id, user_id, collection_type, collection_id, create_time
        ) VALUES (
            #{entity.id}, #{entity.userID}, #{entity.collectionType}, #{entity.collectionID}, #{entity.createTime}
        )
    </insert>

    <!-- 批量插入记录 -->
    <insert id="batchInsert" parameterType="java.util.List">
        INSERT INTO tb_user_collection (
            id, user_id, collection_type, collection_id, create_time
        ) VALUES
        <foreach collection="entities" item="entity" separator=",">
            (
                #{entity.id}, #{entity.userID}, #{entity.collectionType}, #{entity.collectionID}, #{entity.createTime}
            )
        </foreach>
    </insert>

    <!-- 根据ID更新记录 -->
    <update id="updateById" parameterType="org.xyzh.common.dto.usercenter.TbUserCollection">
        UPDATE tb_user_collection
        <set>
            <if test="entity.userID != null and entity.userID != ''">
                user_id = #{entity.userID},
            </if>
            <if test="entity.collectionType != null">
                collection_type = #{entity.collectionType},
            </if>
            <if test="entity.collectionID != null and entity.collectionID != ''">
                collection_id = #{entity.collectionID},
            </if>
        </set>
        WHERE id = #{entity.id}
    </update>

    <!-- 根据条件更新记录 -->
    <update id="updateByFilter" parameterType="org.xyzh.common.dto.usercenter.TbUserCollection">
        UPDATE tb_user_collection
        <set>
            <if test="entity.userID != null and entity.userID != ''">
                user_id = #{entity.userID},
            </if>
            <if test="entity.collectionType != null">
                collection_type = #{entity.collectionType},
            </if>
            <if test="entity.collectionID != null and entity.collectionID != ''">
                collection_id = #{entity.collectionID},
            </if>
        </set>
        <include refid="Base_Where_Clause" />
    </update>

    <!-- 删除方法使用MyBatis-Plus的BaseMapper提供的方法 -->
    <!-- deleteById(Serializable id) - 根据ID删除 -->
    <!-- deleteBatchIds(Collection<? extends Serializable> idList) - 批量删除 -->
    <!-- deleteByMap(Map<String, Object> columnMap) - 根据Map删除 -->
    <!-- delete(Wrapper<T> queryWrapper) - 根据条件删除 -->

    <!-- 根据条件统计记录数 -->
    <select id="countByFilter" resultType="long">
        SELECT COUNT(1)
        FROM tb_user_collection
        <include refid="Base_Where_Clause" />
    </select>

    <!-- 检查记录是否存在 -->
    <select id="existsByFilter" resultType="boolean">
        SELECT COUNT(1) > 0
        FROM tb_user_collection
        <include refid="Base_Where_Clause" />
    </select>

    <!-- 查询用户收藏列表 -->
    <select id="selectUserCollections" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_user_collection
        <include refid="Base_Where_Clause" />
    </select>

    <!-- 根据用户ID查询收藏记录 -->
    <select id="selectByUserId" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_user_collection
        WHERE user_id = #{userID}
        ORDER BY create_time DESC
    </select>

    <!-- 根据用户ID和类型查询收藏记录 -->
    <select id="selectByUserIdAndType" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_user_collection
        WHERE user_id = #{userID} AND collection_type = #{collectionType}
        ORDER BY create_time DESC
    </select>

    <!-- 根据用户ID查询收藏记录（包含用户基本信息） -->
    <select id="selectUserCollectionsWithUser" resultMap="BaseResultMap">
        SELECT
        uc.id, uc.user_id, uc.collection_type, uc.collection_id, uc.create_time
        FROM tb_user_collection uc
        LEFT JOIN tb_sys_user u ON uc.user_id = u.id
        WHERE uc.user_id = #{userID}
        ORDER BY uc.create_time DESC
    </select>

    <!-- 查询用户收藏统计 -->
    <select id="selectCollectionStatistics" resultMap="BaseResultMap">
        SELECT
        user_id,
        COUNT(*) as collection_type,
        MAX(create_time) as create_time
        FROM tb_user_collection
        WHERE user_id = #{userID}
        GROUP BY user_id
    </select>

    <!-- 插入用户收藏 -->
    <insert id="insertUserCollection" parameterType="org.xyzh.common.dto.usercenter.TbUserCollection">
        INSERT INTO tb_user_collection (
            id, user_id, collection_type, collection_id, create_time
        ) VALUES (
            #{id}, #{userID}, #{collectionType}, #{collectionID}, #{createTime}
        )
    </insert>

    <!-- 更新用户收藏 -->
    <update id="updateUserCollection" parameterType="org.xyzh.common.dto.usercenter.TbUserCollection">
        UPDATE tb_user_collection
        <set>
            <if test="userID != null and userID != ''">
                user_id = #{userID},
            </if>
            <if test="collectionType != null">
                collection_type = #{collectionType},
            </if>
            <if test="collectionID != null and collectionID != ''">
                collection_id = #{collectionID},
            </if>
        </set>
        WHERE id = #{id}
    </update>

    <!-- 删除用户收藏 -->
    <delete id="deleteUserCollection" parameterType="org.xyzh.common.dto.usercenter.TbUserCollection">
        DELETE FROM tb_user_collection
        WHERE user_id = #{userID} AND collection_type = #{collectionType} AND collection_id = #{collectionID}
    </delete>

    <!-- 批量插入用户收藏 -->
    <insert id="batchInsertUserCollections" parameterType="java.util.List">
        INSERT INTO tb_user_collection (
            id, user_id, collection_type, collection_id, create_time
        ) VALUES
        <foreach collection="userCollectionList" item="item" separator=",">
            (
                #{item.id}, #{item.userID}, #{item.collectionType}, #{item.collectionID}, #{item.createTime}
            )
        </foreach>
    </insert>

    <!-- 批量删除用户收藏 -->
    <delete id="batchDeleteUserCollections">
        DELETE FROM tb_user_collection
        WHERE id IN
        <foreach collection="ids" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <!-- 分页查询用户收藏 -->
    <select id="selectUserCollectionsPage" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_user_collection
        <include refid="Base_Where_Clause" />
        ORDER BY create_time DESC
        LIMIT #{pageParam.pageSize} OFFSET #{pageParam.offset}
    </select>

    <!-- 统计用户收藏总数 -->
    <select id="countUserCollections" resultType="long">
        SELECT COUNT(1)
        FROM tb_user_collection
        <include refid="Base_Where_Clause" />
    </select>
    
    <!-- 查询用户收藏列表（包含资源/课程详情）- 一次性JOIN查询 -->
    <select id="selectUserCollectionsVO" resultMap="UserCollectionVOResultMap">
        SELECT
            uc.id,
            uc.user_id,
            uc.collection_type,
            uc.collection_id,
            uc.create_time as collection_time,
            uc.create_time,
            
            <!-- 资源字段 -->
            r.resource_id,
            r.title as resource_title,
            r.content as resource_content,
            r.summary as resource_summary,
            r.cover_image as resource_cover_image,
            r.tag_id as resource_tag_id,
            r.author as resource_author,
            r.source as resource_source,
            r.view_count as resource_view_count,
            r.like_count as resource_like_count,
            r.collect_count as resource_collect_count,
            r.status as resource_status,
            r.publish_time as resource_publish_time,
            
            <!-- 课程字段 -->
            c.course_id,
            c.name as course_name,
            c.description as course_description,
            c.cover_image as course_cover_image,
            c.duration as course_duration,
            c.teacher as course_teacher,
            c.status as course_status,
            c.view_count as course_view_count,
            c.learn_count as course_learn_count
        FROM tb_user_collection uc
        LEFT JOIN tb_resource r ON uc.collection_type = 1 AND uc.collection_id = r.resource_id
        LEFT JOIN tb_course c ON uc.collection_type = 2 AND uc.collection_id = c.course_id
        WHERE uc.user_id = #{userID}
        <if test="collectionType != null">
            AND uc.collection_type = #{collectionType}
        </if>
        ORDER BY uc.create_time DESC
    </select>

</mapper>