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

    <resultMap id="VideoResultMap" type="com.dora.entity.Video">
        <id column="id" property="id"/>
        <result column="title" property="title"/>
        <result column="description" property="description"/>
        <result column="owner_id" property="ownerId"/>
        <result column="cover_url" property="coverUrl"/>
        <result column="vod_video_id" property="vodVideoId"/>
        <result column="duration" property="duration"/>
        <result column="view_count" property="viewCount"/>
        <result column="like_count" property="likeCount"/>
        <result column="is_public" property="isPublic"/>
        <result column="audit_status" property="auditStatus"/>
        <result column="category" property="category"/>
        <result column="reject_reason" property="rejectReason"/>
        <result column="review_time" property="reviewTime"/>
        <result column="create_time" property="createTime"/>
        <result column="update_time" property="updateTime"/>
        <result column="is_deleted" property="isDeleted"/>
    </resultMap>

    <select id="selectById" resultMap="VideoResultMap">
        SELECT * FROM video WHERE id = #{id} AND is_deleted = 0
    </select>

    <select id="selectByVodVideoId" resultMap="VideoResultMap">
        SELECT * FROM video WHERE vod_video_id = #{vodVideoId} AND is_deleted = 0
        ORDER BY id DESC LIMIT 1
    </select>

    <insert id="insert" parameterType="com.dora.entity.Video" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO video (
            title, description, owner_id, cover_url, vod_video_id, 
            duration, view_count, like_count, is_public, audit_status, 
            category, reject_reason, review_time, create_time, update_time, is_deleted
        ) VALUES (
            #{title}, #{description}, #{ownerId}, #{coverUrl}, #{vodVideoId},
            #{duration}, #{viewCount}, #{likeCount}, #{isPublic}, #{auditStatus},
            #{category}, #{rejectReason}, #{reviewTime}, NOW(), NOW(), #{isDeleted}
        )
    </insert>

    <select id="selectByIds" resultMap="VideoResultMap">
        SELECT * FROM video 
        WHERE id IN 
        <foreach collection="ids" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
        AND is_deleted = 0
    </select>

    <select id="selectByOwnerId" resultMap="VideoResultMap">
        SELECT * FROM video 
        WHERE owner_id = #{ownerId} AND is_deleted = 0
        ORDER BY create_time DESC
    </select>

    <select id="listByOwnerPaged" resultMap="VideoResultMap">
        SELECT * FROM video
        WHERE owner_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 video
        WHERE owner_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 video
        SET 
            title = COALESCE(#{title}, title),
            description = COALESCE(#{description}, description),
            audit_status = 0,
            reject_reason = NULL,
            review_time = NULL,
            update_time = NOW()
        WHERE id = #{id} AND owner_id = #{ownerId} AND is_deleted = 0
    </update>

    <select id="sumViewCountByCourseId" resultType="java.lang.Integer">
        SELECT SUM(v.view_count) 
        FROM video v
        WHERE v.id IN (
            SELECT DISTINCT cv.video_id
            FROM course_video cv
            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
              AND cv.video_id IS NOT NULL
        )
        AND v.is_deleted = 0
    </select>

    <update id="updateById" parameterType="com.dora.entity.Video">
        UPDATE video
        <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="vodVideoId != null">vod_video_id = #{vodVideoId},</if>
            <if test="duration != null">duration = #{duration},</if>
            <if test="isPublic != null">is_public = #{isPublic},</if>
            <if test="auditStatus != null">audit_status = #{auditStatus},</if>
            <if test="category != null">category = #{category},</if>
            <if test="isDeleted != null">is_deleted = #{isDeleted},</if>
            update_time = NOW()
        </set>
        WHERE id = #{id} AND is_deleted = 0
        <if test="ownerId != null">
            AND owner_id = #{ownerId}
        </if>
    </update>

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

    <!-- 获取所有已通过审核的视频（用于统计分析） -->
    <select id="selectAll" resultMap="VideoResultMap">
        SELECT * FROM video 
        WHERE audit_status = 1 AND is_deleted = 0
        ORDER BY create_time DESC
    </select>

</mapper> 