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

    <!-- 结果映射 -->
    <resultMap id="ContentAuditItemResultMap" type="com.dora.dto.AdminContentAuditDto$ContentAuditItem">
        <id property="id" column="id"/>
        <result property="contentType" column="content_type"/>
        <result property="title" column="title"/>
        <result property="description" column="description"/>
        <result property="ownerId" column="owner_id"/>
        <result property="ownerUsername" column="owner_username"/>
        <result property="ownerRole" column="owner_role"/>
        <result property="auditStatus" column="audit_status"/>
        <result property="auditStatusName" column="audit_status_name"/>
        <result property="rejectReason" column="reject_reason"/>
        <result property="reviewTime" column="review_time"/>
        <result property="createTime" column="create_time"/>
        <result property="updateTime" column="update_time"/>
        <result property="coverUrl" column="cover_url"/>
        <result property="category" column="category"/>
        <result property="isPublic" column="is_public"/>
        <result property="isFree" column="is_free"/>
        <result property="fullAccessRole" column="full_access_role"/>
        <result property="copyAccessRole" column="copy_access_role"/>
        <result property="duration" column="duration"/>
        <result property="viewCount" column="view_count"/>
        <result property="likeCount" column="like_count"/>
        <result property="courseLevel" column="course_level"/>
        <result property="videoIds" column="video_ids"/>
        <result property="videoTitles" column="video_titles"/>
        <result property="chapterCount" column="chapter_count"/>
        <result property="videoCount" column="video_count"/>
        <result property="vodVideoId" column="vod_video_id"/>
        <result property="workflowData" column="workflow_data"/>
        <result property="dataFileUrl" column="data_file_url"/>
        <result property="detailGallery" column="detail_gallery"/>
    </resultMap>

    <!-- 统计结果映射 -->
    <resultMap id="AuditStatisticsResultMap" type="com.dora.dto.AdminContentAuditDto$AuditStatistics">
        <result property="pendingTotal" column="pending_total"/>
        <result property="pendingVideos" column="pending_videos"/>
        <result property="pendingWorkflows" column="pending_workflows"/>
        <result property="pendingCourses" column="pending_courses"/>
        <result property="todayApprovedTotal" column="today_approved_total"/>
        <result property="todayRejectedTotal" column="today_rejected_total"/>
    </resultMap>

    <!-- 查询内容审核列表总数 -->
    <select id="countContentAuditList" resultType="java.lang.Long">
        SELECT COUNT(*)
        FROM (
            <!-- 视频 -->
            <if test="request.contentType == null or request.contentType == 'video'">
                SELECT v.id
                FROM video v
                LEFT JOIN user u ON v.owner_id = u.id
                WHERE v.is_deleted = 0
                <if test="request.auditStatus != null">
                    AND v.audit_status = #{request.auditStatus}
                </if>
                <if test="request.ownerId != null">
                    AND v.owner_id = #{request.ownerId}
                </if>
                <if test="request.keyword != null and request.keyword != ''">
                    AND (v.title LIKE CONCAT('%', #{request.keyword}, '%') 
                         OR v.description LIKE CONCAT('%', #{request.keyword}, '%'))
                </if>
                <if test="request.createTimeStart != null">
                    AND v.create_time &gt;= #{request.createTimeStart}
                </if>
                <if test="request.createTimeEnd != null">
                    AND v.create_time &lt;= #{request.createTimeEnd}
                </if>
            </if>
            
            <if test="(request.contentType == null or request.contentType == 'video') and (request.contentType == null or request.contentType == 'workflow')">
                UNION ALL
            </if>
            
            <!-- 工作流 -->
            <if test="request.contentType == null or request.contentType == 'workflow'">
                SELECT w.id
                FROM workflow w
                LEFT JOIN user u ON w.owner_id = u.id
                WHERE w.is_deleted = 0
                <if test="request.auditStatus != null">
                    AND w.audit_status = #{request.auditStatus}
                </if>
                <if test="request.ownerId != null">
                    AND w.owner_id = #{request.ownerId}
                </if>
                <if test="request.keyword != null and request.keyword != ''">
                    AND (w.name LIKE CONCAT('%', #{request.keyword}, '%') 
                         OR w.description LIKE CONCAT('%', #{request.keyword}, '%'))
                </if>
                <if test="request.createTimeStart != null">
                    AND w.create_time &gt;= #{request.createTimeStart}
                </if>
                <if test="request.createTimeEnd != null">
                    AND w.create_time &lt;= #{request.createTimeEnd}
                </if>
            </if>
            
            <if test="(request.contentType == null or request.contentType == 'workflow') and (request.contentType == null or request.contentType == 'course')">
                UNION ALL
            </if>
            
            <!-- 课程 -->
            <if test="request.contentType == null or request.contentType == 'course'">
                SELECT DISTINCT c.id
                FROM course c
                LEFT JOIN user u ON c.user_id = u.id
                LEFT JOIN course_chapter cc ON c.id = cc.course_id AND cc.is_deleted = 0
                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 c.is_deleted = 0
                <if test="request.auditStatus != null">
                    AND c.audit_status = #{request.auditStatus}
                </if>
                <if test="request.ownerId != null">
                    AND c.user_id = #{request.ownerId}
                </if>
                <if test="request.keyword != null and request.keyword != ''">
                    AND (c.title LIKE CONCAT('%', #{request.keyword}, '%') 
                         OR c.description LIKE CONCAT('%', #{request.keyword}, '%')
                         OR v.title LIKE CONCAT('%', #{request.keyword}, '%'))
                </if>
                <if test="request.createTimeStart != null">
                    AND c.create_time &gt;= #{request.createTimeStart}
                </if>
                <if test="request.createTimeEnd != null">
                    AND c.create_time &lt;= #{request.createTimeEnd}
                </if>
            </if>
        ) AS combined_content
    </select>

    <!-- 查询内容审核列表 -->
    <select id="selectContentAuditList" resultMap="ContentAuditItemResultMap">
        SELECT * FROM (
            <!-- 视频 -->
            <if test="request.contentType == null or request.contentType == 'video'">
                SELECT 
                    v.id,
                    'video' as content_type,
                    v.title,
                    v.description,
                    v.owner_id,
                    u.username as owner_username,
                    u.role as owner_role,
                    v.audit_status,
                    CASE 
                        WHEN v.audit_status = 0 THEN '待审核'
                        WHEN v.audit_status = 1 THEN '审核通过'
                        WHEN v.audit_status = 2 THEN '审核拒绝'
                        ELSE '未知状态'
                    END as audit_status_name,
                    v.reject_reason,
                    v.review_time,
                    v.create_time,
                    v.update_time,
                    v.cover_url,
                    v.category,
                    v.is_public,
                    NULL as is_free,
                    NULL as full_access_role,
                    NULL as copy_access_role,
                    v.duration,
                    v.view_count,
                    v.like_count,
                    NULL as course_level,
                    v.vod_video_id,
                    NULL as workflow_data,
                    NULL as data_file_url,
                    NULL as detail_gallery
                FROM video v
                LEFT JOIN user u ON v.owner_id = u.id
                WHERE v.is_deleted = 0
                <if test="request.auditStatus != null">
                    AND v.audit_status = #{request.auditStatus}
                </if>
                <if test="request.ownerId != null">
                    AND v.owner_id = #{request.ownerId}
                </if>
                <if test="request.keyword != null and request.keyword != ''">
                    AND (v.title LIKE CONCAT('%', #{request.keyword}, '%') 
                         OR v.description LIKE CONCAT('%', #{request.keyword}, '%'))
                </if>
                <if test="request.createTimeStart != null">
                    AND v.create_time &gt;= #{request.createTimeStart}
                </if>
                <if test="request.createTimeEnd != null">
                    AND v.create_time &lt;= #{request.createTimeEnd}
                </if>
            </if>
            
            <if test="(request.contentType == null or request.contentType == 'video') and (request.contentType == null or request.contentType == 'workflow')">
                UNION ALL
            </if>
            
            <!-- 工作流 -->
            <if test="request.contentType == null or request.contentType == 'workflow'">
                SELECT 
                    w.id,
                    'workflow' as content_type,
                    w.name as title,
                    w.description,
                    w.owner_id,
                    u.username as owner_username,
                    u.role as owner_role,
                    w.audit_status,
                    CASE 
                        WHEN w.audit_status = 0 THEN '待审核'
                        WHEN w.audit_status = 1 THEN '审核通过'
                        WHEN w.audit_status = 2 THEN '审核拒绝'
                        ELSE '未知状态'
                    END as audit_status_name,
                    w.reject_reason,
                    w.review_time,
                    w.create_time,
                    w.update_time,
                    w.cover_url,
                    w.category,
                    w.is_public,
                    w.is_free,
                    w.full_access_role,
                    w.copy_access_role,
                    (SELECT v.duration FROM video v WHERE v.vod_video_id = w.video_id AND v.is_deleted = 0 LIMIT 1) as duration,
                    (SELECT v.view_count FROM video v WHERE v.vod_video_id = w.video_id AND v.is_deleted = 0 LIMIT 1) as view_count,
                    w.like_count,
                    NULL as course_level,
                    w.video_id as video_ids,
                    (SELECT v.title FROM video v WHERE v.vod_video_id = w.video_id AND v.is_deleted = 0 LIMIT 1) as video_titles,
                    NULL as chapter_count,
                    NULL as video_count,
                    w.video_id as vod_video_id,
                    w.data as workflow_data,
                    w.data_file_url as data_file_url,
                    w.detail_gallery as detail_gallery
                FROM workflow w
                LEFT JOIN user u ON w.owner_id = u.id
                WHERE w.is_deleted = 0
                <if test="request.auditStatus != null">
                    AND w.audit_status = #{request.auditStatus}
                </if>
                <if test="request.ownerId != null">
                    AND w.owner_id = #{request.ownerId}
                </if>
                <if test="request.keyword != null and request.keyword != ''">
                    AND (w.name LIKE CONCAT('%', #{request.keyword}, '%') 
                         OR w.description LIKE CONCAT('%', #{request.keyword}, '%')
                         OR EXISTS (SELECT 1 FROM video v WHERE v.vod_video_id = w.video_id AND v.is_deleted = 0 AND v.title LIKE CONCAT('%', #{request.keyword}, '%')))
                </if>
                <if test="request.createTimeStart != null">
                    AND w.create_time &gt;= #{request.createTimeStart}
                </if>
                <if test="request.createTimeEnd != null">
                    AND w.create_time &lt;= #{request.createTimeEnd}
                </if>
            </if>
            
            <if test="(request.contentType == null or request.contentType == 'workflow') and (request.contentType == null or request.contentType == 'course')">
                UNION ALL
            </if>
            
            <!-- 课程 -->
            <if test="request.contentType == null or request.contentType == 'course'">
                SELECT 
                    c.id,
                    'course' as content_type,
                    c.title,
                    c.description,
                    c.user_id as owner_id,
                    u.username as owner_username,
                    u.role as owner_role,
                    c.audit_status,
                    CASE 
                        WHEN c.audit_status = 0 THEN '待审核'
                        WHEN c.audit_status = 1 THEN '审核通过'
                        WHEN c.audit_status = 2 THEN '审核拒绝'
                        ELSE '未知状态'
                    END as audit_status_name,
                    c.reject_reason,
                    c.review_time,
                    c.create_time,
                    c.update_time,
                    c.cover_url,
                    c.category,
                    1 as is_public,
                    c.is_free,
                    c.level as full_access_role,
                    NULL as copy_access_role,
                    NULL as duration,
                    NULL as view_count,
                    NULL as like_count,
                    c.level as course_level,
                    GROUP_CONCAT(DISTINCT v.vod_video_id) as video_ids,
                    GROUP_CONCAT(DISTINCT v.title) as video_titles,
                    COUNT(DISTINCT cc.id) as chapter_count,
                    COUNT(DISTINCT cv.id) as video_count,
                    GROUP_CONCAT(DISTINCT v.vod_video_id) as vod_video_id,
                    NULL as workflow_data,
                    NULL as data_file_url,
                    c.detail_gallery as detail_gallery
                FROM course c
                LEFT JOIN user u ON c.user_id = u.id
                LEFT JOIN course_chapter cc ON c.id = cc.course_id AND cc.is_deleted = 0
                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 c.is_deleted = 0
                <if test="request.auditStatus != null">
                    AND c.audit_status = #{request.auditStatus}
                </if>
                <if test="request.ownerId != null">
                    AND c.user_id = #{request.ownerId}
                </if>
                <if test="request.keyword != null and request.keyword != ''">
                    AND (c.title LIKE CONCAT('%', #{request.keyword}, '%') 
                         OR c.description LIKE CONCAT('%', #{request.keyword}, '%')
                         OR v.title LIKE CONCAT('%', #{request.keyword}, '%'))
                </if>
                <if test="request.createTimeStart != null">
                    AND c.create_time &gt;= #{request.createTimeStart}
                </if>
                <if test="request.createTimeEnd != null">
                    AND c.create_time &lt;= #{request.createTimeEnd}
                </if>
                GROUP BY c.id
            </if>
        ) AS combined_content
        ORDER BY 
        <choose>
            <when test="request.sortField == 'review_time'">
                review_time
            </when>
            <otherwise>
                create_time
            </otherwise>
        </choose>
        <choose>
            <when test="request.sortOrder == 'asc'">
                ASC
            </when>
            <otherwise>
                DESC
            </otherwise>
        </choose>
        LIMIT #{offset}, #{limit}
    </select>

    <!-- 审核视频 -->
    <update id="auditVideo">
        UPDATE video 
        SET audit_status = #{auditStatus},
            reject_reason = #{rejectReason},
            review_time = NOW(),
            update_time = NOW()
        WHERE id = #{id} AND is_deleted = 0
    </update>

    <!-- 审核工作流 -->
    <update id="auditWorkflow">
        UPDATE workflow 
        SET audit_status = #{auditStatus},
            reject_reason = #{rejectReason},
            review_time = NOW(),
            update_time = NOW()
        WHERE id = #{id} AND is_deleted = 0
    </update>

    <!-- 审核课程 -->
    <update id="auditCourse">
        UPDATE course 
        SET audit_status = #{auditStatus},
            reject_reason = #{rejectReason},
            review_time = NOW(),
            update_time = NOW()
        WHERE id = #{id} AND is_deleted = 0
    </update>

    <!-- 统计审核信息 -->
    <select id="selectAuditStatistics" resultMap="AuditStatisticsResultMap">
        SELECT 
            (COALESCE(pending_videos, 0) + COALESCE(pending_workflows, 0) + COALESCE(pending_courses, 0)) as pending_total,
            COALESCE(pending_videos, 0) as pending_videos,
            COALESCE(pending_workflows, 0) as pending_workflows,
            COALESCE(pending_courses, 0) as pending_courses,
            COALESCE(today_approved_total, 0) as today_approved_total,
            COALESCE(today_rejected_total, 0) as today_rejected_total
        FROM (
            SELECT 
                (SELECT COUNT(*) FROM video WHERE audit_status = 0 AND is_deleted = 0) as pending_videos,
                (SELECT COUNT(*) FROM workflow WHERE audit_status = 0 AND is_deleted = 0) as pending_workflows,
                (SELECT COUNT(*) FROM course WHERE audit_status = 0 AND is_deleted = 0) as pending_courses,
                (
                    SELECT COUNT(*) FROM (
                        SELECT id FROM video WHERE audit_status = 1 AND DATE(review_time) = CURDATE() AND is_deleted = 0
                        UNION ALL
                        SELECT id FROM workflow WHERE audit_status = 1 AND DATE(review_time) = CURDATE() AND is_deleted = 0
                        UNION ALL
                        SELECT id FROM course WHERE audit_status = 1 AND DATE(review_time) = CURDATE() AND is_deleted = 0
                    ) as approved_today
                ) as today_approved_total,
                (
                    SELECT COUNT(*) FROM (
                        SELECT id FROM video WHERE audit_status = 2 AND DATE(review_time) = CURDATE() AND is_deleted = 0
                        UNION ALL
                        SELECT id FROM workflow WHERE audit_status = 2 AND DATE(review_time) = CURDATE() AND is_deleted = 0
                        UNION ALL
                        SELECT id FROM course WHERE audit_status = 2 AND DATE(review_time) = CURDATE() AND is_deleted = 0
                    ) as rejected_today
                ) as today_rejected_total
        ) as stats
    </select>

    <!-- 统计今日审核通过数量 -->
    <select id="countTodayApproved" resultType="java.lang.Long">
        SELECT COUNT(*) FROM (
            SELECT id FROM video WHERE audit_status = 1 AND DATE(review_time) = CURDATE() AND is_deleted = 0
            UNION ALL
            SELECT id FROM workflow WHERE audit_status = 1 AND DATE(review_time) = CURDATE() AND is_deleted = 0
            UNION ALL
            SELECT id FROM course WHERE audit_status = 1 AND DATE(review_time) = CURDATE() AND is_deleted = 0
        ) as approved_today
    </select>

    <!-- 统计今日审核拒绝数量 -->
    <select id="countTodayRejected" resultType="java.lang.Long">
        SELECT COUNT(*) FROM (
            SELECT id FROM video WHERE audit_status = 2 AND DATE(review_time) = CURDATE() AND is_deleted = 0
            UNION ALL
            SELECT id FROM workflow WHERE audit_status = 2 AND DATE(review_time) = CURDATE() AND is_deleted = 0
            UNION ALL
            SELECT id FROM course WHERE audit_status = 2 AND DATE(review_time) = CURDATE() AND is_deleted = 0
        ) as rejected_today
    </select>

    <!-- 更新课程属性 -->
    <update id="updateCourseProperties">
        UPDATE course 
        SET 
            <if test="isFree != null">
                is_free = #{isFree},
            </if>
            <if test="level != null">
                level = #{level},
            </if>
            <if test="category != null and category != ''">
                category = #{category},
            </if>
            update_time = NOW()
        WHERE id = #{courseId} AND is_deleted = 0
    </update>

    <!-- 更新工作流属性 -->
    <update id="updateWorkflowProperties">
        UPDATE workflow 
        SET 
            <if test="isFree != null">
                is_free = #{isFree},
            </if>
            <if test="fullAccessRole != null">
                full_access_role = #{fullAccessRole},
            </if>
            <if test="copyAccessRole != null">
                copy_access_role = #{copyAccessRole},
            </if>
            <if test="category != null and category != ''">
                category = #{category},
            </if>
            <if test="rating != null">
                rating = #{rating},
            </if>
            update_time = NOW()
        WHERE id = #{workflowId} AND is_deleted = 0
    </update>

</mapper>
