<?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="com.dora.mapper.CourseMapper">

    <!-- 结果映射 -->
    <resultMap id="CourseResultMap" type="com.dora.entity.Course">
        <id column="id" property="id"/>
        <result column="title" property="title"/>
        <result column="description" property="description"/>
        <result column="cover_url" property="coverUrl"/>
        <result column="detail_gallery" property="detailGallery"/>
        <result column="price" property="price"/>
        <result column="level" property="level"/>
        <result column="category" property="category"/>
        <result column="category_id" property="categoryId"/>
        <result column="audit_status" property="auditStatus"/>
        <result column="reject_reason" property="rejectReason"/>
        <result column="review_time" property="reviewTime"/>
        <result column="user_id" property="userId"/>
        <result column="is_free" property="isFree"/>
        <result column="create_time" property="createTime"/>
        <result column="update_time" property="updateTime"/>
        <result column="is_deleted" property="isDeleted"/>
    </resultMap>

    <!-- 获取热门课程列表 -->
    <select id="selectHotCourses" resultMap="CourseResultMap">
        SELECT 
            c.id, c.title, c.description, c.cover_url, c.detail_gallery, c.price, c.level, c.category, c.category_id,
            c.audit_status, c.reject_reason, c.review_time, c.user_id, c.is_free,
            c.create_time, c.update_time, c.is_deleted
        FROM course c
        LEFT JOIN (
            -- 计算课程总观看次数
            SELECT 
                cc.course_id,
                COALESCE(SUM(v.view_count), 0) as total_view_count
            FROM course_chapter cc
            LEFT JOIN course_video cv ON cc.id = cv.chapter_id AND cv.is_deleted = 0
            LEFT JOIN video v ON cv.video_id = v.id AND v.is_deleted = 0
            WHERE cc.is_deleted = 0
            GROUP BY cc.course_id
        ) view_stats ON c.id = view_stats.course_id
        LEFT JOIN (
            -- 计算课程点赞次数
            SELECT 
                course_id,
                COUNT(*) as like_count
            FROM course_like
            WHERE is_deleted = 0
            GROUP BY course_id
        ) like_stats ON c.id = like_stats.course_id
        WHERE c.is_deleted = 0 
          AND c.audit_status = 1
        ORDER BY 
            -- 综合评分公式: 观看次数40% + 点赞次数30% + 时间分数30%
            (
                (COALESCE(view_stats.total_view_count, 0) * 0.4) + 
                (COALESCE(like_stats.like_count, 0) * 0.3) + 
                ((1000 / (DATEDIFF(NOW(), c.create_time) + 1)) * 0.3)
            ) DESC
        LIMIT #{offset}, #{limit}
    </select>

    <!-- 根据分类获取课程列表 -->
    <select id="selectCoursesByCategory" resultMap="CourseResultMap">
        SELECT 
            c.id, c.title, c.description, c.cover_url, c.detail_gallery, c.price, c.level, c.category, c.category_id,
            c.audit_status, c.reject_reason, c.review_time, c.user_id, c.is_free,
            c.create_time, c.update_time, c.is_deleted
        FROM course c
        WHERE c.is_deleted = 0 
          AND c.audit_status = 1
          AND (
            -- 如果是"hot"热门分类，返回所有课程
            #{category} = 'hot'
            OR 
            -- 如果传入的是数字，按category_id查询
            (#{category} REGEXP '^[0-9]+$' AND c.category_id = CAST(#{category} AS UNSIGNED))
            OR 
            -- 如果传入的是字符串，按category名称查询（兼容老数据）
            (#{category} NOT REGEXP '^[0-9]+$' AND #{category} != 'hot' AND c.category = #{category})
            OR
            -- 如果传入的是数字但老数据用的是分类名，通过category表查询
            (#{category} REGEXP '^[0-9]+$' AND c.category_id IS NULL AND c.category = (SELECT name FROM category WHERE id = CAST(#{category} AS UNSIGNED)))
          )
        ORDER BY 
            (CASE WHEN c.is_free = 1 THEN 1 ELSE 0 END) DESC,  -- 免费课程优先
            c.create_time DESC  -- 最新创建优先
        LIMIT #{offset}, #{limit}
    </select>

    <!-- 根据分类获取课程总数 -->
    <select id="countCoursesByCategory" resultType="long">
        SELECT COUNT(*)
        FROM course c
        WHERE c.is_deleted = 0 
          AND c.audit_status = 1
          AND (
            -- 如果是"hot"热门分类，返回所有课程
            #{category} = 'hot'
            OR 
            -- 如果传入的是数字，按category_id查询
            (#{category} REGEXP '^[0-9]+$' AND c.category_id = CAST(#{category} AS UNSIGNED))
            OR 
            -- 如果传入的是字符串，按category名称查询（兼容老数据）
            (#{category} NOT REGEXP '^[0-9]+$' AND #{category} != 'hot' AND c.category = #{category})
            OR
            -- 如果传入的是数字但老数据用的是分类名，通过category表查询
            (#{category} REGEXP '^[0-9]+$' AND c.category_id IS NULL AND c.category = (SELECT name FROM category WHERE id = CAST(#{category} AS UNSIGNED)))
          )
    </select>

    <!-- 根据分类ID获取课程列表（兼容新旧数据） -->
    <select id="selectCoursesByCategoryId" resultMap="CourseResultMap">
        SELECT 
            c.id, c.title, c.description, c.cover_url, c.detail_gallery, c.price, c.level, c.category, c.category_id,
            c.audit_status, c.reject_reason, c.review_time, c.user_id, c.is_free,
            c.create_time, c.update_time, c.is_deleted
        FROM course c
        WHERE c.is_deleted = 0 
          AND c.audit_status = 1
          AND (c.category_id = #{categoryId} 
               OR (c.category_id IS NULL AND c.category = (SELECT name FROM category WHERE id = #{categoryId})))
        ORDER BY 
            (CASE WHEN c.is_free = 1 THEN 1 ELSE 0 END) DESC,  -- 免费课程优先
            c.create_time DESC  -- 最新创建优先
        LIMIT #{offset}, #{limit}
    </select>

    <!-- 根据类目ID获取课程总数（兼容新旧数据） -->
    <select id="countCoursesByCategoryId" resultType="long">
        SELECT COUNT(*)
        FROM course c
        WHERE c.is_deleted = 0 
          AND c.audit_status = 1
          AND (c.category_id = #{categoryId} 
               OR (c.category_id IS NULL AND c.category = (SELECT name FROM category WHERE id = #{categoryId})))
    </select>

    <!-- 获取课程总数 -->
    <select id="countAllCourses" resultType="long">
        SELECT COUNT(*)
        FROM course 
        WHERE is_deleted = 0 
          AND audit_status = 1
    </select>

    <!-- 根据ID获取课程详情（不过滤审核状态，用于内部管理） -->
    <select id="selectById" resultMap="CourseResultMap">
        SELECT 
            id, title, description, cover_url, detail_gallery, price, level, category, category_id,
            audit_status, reject_reason, review_time, user_id, is_free,
            create_time, update_time, is_deleted
        FROM course 
        WHERE id = #{id} 
          AND is_deleted = 0
    </select>

    <!-- 用户端获取已审核通过的课程详情 -->
    <select id="selectApprovedById" resultMap="CourseResultMap">
        SELECT 
            id, title, description, cover_url, detail_gallery, price, level, category, category_id,
            audit_status, reject_reason, review_time, user_id, is_free,
            create_time, update_time, is_deleted
        FROM course 
        WHERE id = #{id} 
          AND is_deleted = 0
          AND audit_status = 1
    </select>

    <!-- 根据外部课程ID获取课程（已移除external_id列，如需启用请先在表结构添加该列） -->

    <!-- 获取所有分类及其课程数量 -->
    <select id="selectCategoryCounts" resultType="com.dora.mapper.CourseMapper$CourseCategoryCount">
        SELECT 
            category,
            COUNT(*) as count
        FROM course 
        WHERE is_deleted = 0 
          AND audit_status = 1
          AND category IS NOT NULL
        GROUP BY category
        ORDER BY count DESC
    </select>

    <!-- 我的课程分页 -->
    <select id="listByOwnerPaged" resultMap="CourseResultMap">
        SELECT 
            id, title, description, cover_url, detail_gallery, price, level, category, category_id,
            audit_status, reject_reason, review_time, user_id, is_free,
            create_time, update_time, is_deleted
        FROM course
        WHERE user_id = #{ownerId}
          AND is_deleted = 0
        <if test="auditStatus != null">
            AND audit_status = #{auditStatus}
        </if>
        <if test="keyword != null and keyword != ''">
            AND (title LIKE CONCAT('%', #{keyword}, '%')
                 OR description LIKE CONCAT('%', #{keyword}, '%'))
        </if>
        ORDER BY create_time DESC
        LIMIT #{offset}, #{limit}
    </select>

    <!-- 我的课程统计 -->
    <select id="countByOwner" resultType="long">
        SELECT COUNT(*)
        FROM course
        WHERE user_id = #{ownerId}
          AND is_deleted = 0
        <if test="auditStatus != null">
            AND audit_status = #{auditStatus}
        </if>
        <if test="keyword != null and keyword != ''">
            AND (title LIKE CONCAT('%', #{keyword}, '%')
                 OR description LIKE CONCAT('%', #{keyword}, '%'))
        </if>
    </select>

    <!-- 提交课程审核 -->
    <update id="submitForAudit">
        UPDATE course 
        SET title = COALESCE(#{title}, title),
            description = COALESCE(#{description}, description),
            is_free = COALESCE(#{isFree}, is_free),
            level = COALESCE(#{level}, level),
            audit_status = 0,
            reject_reason = NULL,
            review_time = NULL,
            update_time = NOW()
        WHERE id = #{id} AND user_id = #{ownerId} AND is_deleted = 0
    </update>

    <!-- 插入课程（最小信息集） -->
    <insert id="insert" parameterType="com.dora.entity.Course" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO course (title, description, cover_url, detail_gallery, price, level, category, category_id,
                            audit_status, reject_reason, review_time, user_id, is_free,
                            create_time, update_time, is_deleted)
        VALUES (#{title}, #{description}, #{coverUrl}, #{detailGallery}, COALESCE(#{price}, 0.00),
                COALESCE(#{level}, 0), #{category}, #{categoryId}, 0, NULL, NULL, #{userId},
                COALESCE(#{isFree}, 1), NOW(), NOW(), 0)
    </insert>

    <!-- 更新课程信息 -->
    <update id="updateById" parameterType="com.dora.entity.Course">
        UPDATE course 
        <set>
            <if test="title != null">title = #{title},</if>
            <if test="description != null">description = #{description},</if>
            <if test="coverUrl != null">cover_url = #{coverUrl},</if>
            <if test="detailGallery != null">detail_gallery = #{detailGallery},</if>
            <if test="category != null">category = #{category},</if>
            <if test="categoryId != null">category_id = #{categoryId},</if>
            <if test="isFree != null">is_free = #{isFree},</if>
            <if test="level != null">level = #{level},</if>
            update_time = NOW()
        </set>
        WHERE id = #{id} AND is_deleted = 0
        <if test="userId != null">
            AND user_id = #{userId}
        </if>
    </update>

    <update id="softDelete">
        UPDATE course
        SET is_deleted = 1,
            update_time = NOW()
        WHERE id = #{id} AND user_id = #{ownerId} AND is_deleted = 0
    </update>

    <!-- 搜索课程 -->
    <select id="searchCourses" resultMap="CourseResultMap">
        SELECT 
            id, title, description, cover_url, price, level, category, category_id,
            audit_status, reject_reason, review_time, user_id, is_free,
            create_time, update_time, is_deleted
        FROM course 
        WHERE is_deleted = 0 AND audit_status = 1
        <if test="params.keyword != null and params.keyword != ''">
            AND (
                title LIKE CONCAT('%', #{params.keyword}, '%') 
                OR description LIKE CONCAT('%', #{params.keyword}, '%')
                OR category LIKE CONCAT('%', #{params.keyword}, '%')
            )
        </if>
        <if test="params.category != null and params.category != ''">
            AND category = #{params.category}
        </if>
        <if test="params.freeOnly != null and params.freeOnly == true">
            AND is_free = 1
        </if>
        ORDER BY 
            (CASE WHEN is_free = 1 THEN 1 ELSE 0 END) DESC, -- 免费课程优先
            create_time DESC -- 最新创建优先
        LIMIT #{params.offset}, #{params.limit}
    </select>

    <!-- 统计搜索课程数量 -->
    <select id="countSearchCourses" resultType="Long">
        SELECT COUNT(*)
        FROM course 
        WHERE is_deleted = 0 AND audit_status = 1
        <if test="params.keyword != null and params.keyword != ''">
            AND (
                title LIKE CONCAT('%', #{params.keyword}, '%') 
                OR description LIKE CONCAT('%', #{params.keyword}, '%')
                OR category LIKE CONCAT('%', #{params.keyword}, '%')
            )
        </if>
        <if test="params.category != null and params.category != ''">
            AND category = #{params.category}
        </if>
        <if test="params.freeOnly != null and params.freeOnly == true">
            AND is_free = 1
        </if>
    </select>

    <!-- 获取课程观看次数 -->
    <select id="getCourseViewCount" resultType="java.lang.Integer">
        SELECT IFNULL(SUM(v.view_count), 0) as total_view_count
        FROM course_chapter cc
        LEFT JOIN course_video cv ON cc.id = cv.chapter_id AND cv.is_deleted = 0
        LEFT JOIN video v ON cv.video_id = v.id AND v.is_deleted = 0
        WHERE cc.course_id = #{courseId} AND cc.is_deleted = 0
    </select>

    <!-- 获取课程点赞次数 -->
    <select id="getCourseLikeCount" resultType="java.lang.Integer">
        SELECT COUNT(*) 
        FROM course_like
        WHERE course_id = #{courseId} AND is_deleted = 0
    </select>

    <!-- 获取课程总时长（秒） -->
    <select id="getCourseDuration" resultType="java.lang.Integer">
        SELECT IFNULL(SUM(cv.duration_sec), 0)
        FROM course_video cv
        INNER JOIN course_chapter cc ON cv.chapter_id = cc.id
        WHERE cc.course_id = #{courseId}
          AND cv.is_deleted = 0
          AND cc.is_deleted = 0
    </select>

    <!-- 数据迁移：将老的category字段同步到category_id字段 -->
    <update id="migrateCategoryToId">
        UPDATE course c
        LEFT JOIN category cat ON cat.name = c.category AND cat.type = 1 AND cat.is_deleted = 0
        SET c.category_id = cat.id,
            c.update_time = NOW()
        WHERE c.is_deleted = 0 
          AND c.category IS NOT NULL 
          AND c.category != ''
          AND c.category_id IS NULL
          AND cat.id IS NOT NULL
    </update>

</mapper> 