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

    <resultMap id="WorkflowResultMap" type="com.dora.entity.Workflow">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="description" property="description"/>
        <result column="owner_id" property="ownerId"/>
        <result column="cover_url" property="coverUrl"/>
        <result column="detail_gallery" property="detailGallery"/>
        <result column="video_id" property="vodVideoId"/>
        <result column="data" property="data"/>
        <result column="data_file_url" property="dataFileUrl"/>
        <result column="like_count" property="likeCount"/>
        <result column="is_public" property="isPublic"/>
        <result column="full_access_role" property="fullAccessRole"/>
        <result column="copy_access_role" property="copyAccessRole"/>
        <result column="price" property="price"/>
        <result column="audit_status" property="auditStatus"/>
        <result column="category" property="category"/>
        <result column="category_id" property="categoryId"/>
        <result column="rating" property="rating"/>
        <result column="reject_reason" property="rejectReason"/>
        <result column="review_time" property="reviewTime"/>
        <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="selectById" resultMap="WorkflowResultMap">
        SELECT * FROM workflow WHERE id = #{id} AND is_deleted = 0
    </select>

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

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

    <select id="listByOwnerPaged" resultMap="WorkflowResultMap">
        SELECT 
            id, name, description, cover_url, detail_gallery, price, owner_id, category,
            audit_status, reject_reason, review_time, is_free, full_access_role,
            copy_access_role, is_public, like_count, rating,
            video_id, data, data_file_url, create_time, update_time,
            is_deleted
        FROM workflow
        WHERE owner_id = #{ownerId}
          AND is_deleted = 0
        <if test="auditStatus != null">
            AND audit_status = #{auditStatus}
        </if>
        <if test="keyword != null and keyword != ''">
            AND (name 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 workflow
        WHERE owner_id = #{ownerId}
          AND is_deleted = 0
        <if test="auditStatus != null">
            AND audit_status = #{auditStatus}
        </if>
        <if test="keyword != null and keyword != ''">
            AND (name LIKE CONCAT('%', #{keyword}, '%')
                 OR description LIKE CONCAT('%', #{keyword}, '%'))
        </if>
    </select>

    <select id="selectHotWorkflows" resultMap="WorkflowResultMap">
        SELECT 
            id, name, description, cover_url, detail_gallery, price, owner_id, category,
            audit_status, reject_reason, review_time, is_free, full_access_role,
            copy_access_role, is_public, like_count, rating,
            video_id, data, data_file_url, create_time, update_time,
            is_deleted
        FROM workflow 
        WHERE audit_status = 1 AND is_deleted = 0
        ORDER BY like_count DESC, create_time DESC
        LIMIT #{offset}, #{limit}
    </select>

    <select id="selectWorkflowsByCategory" resultMap="WorkflowResultMap">
        SELECT 
            w.id, w.name, w.description, w.cover_url, w.detail_gallery, w.price, w.owner_id, w.category, w.category_id,
            w.audit_status, w.reject_reason, w.review_time, w.is_free, w.full_access_role,
            w.copy_access_role, w.is_public, w.like_count, w.rating,
            w.video_id, w.data, w.data_file_url, w.create_time, w.update_time,
            w.is_deleted
        FROM workflow w
        WHERE w.audit_status = 1 AND w.is_deleted = 0
          AND (
            -- 如果是"hot"热门分类，返回所有工作流
            #{category} = 'hot'
            OR 
            -- 如果传入的是数字，按category_id查询
            (#{category} REGEXP '^[0-9]+$' AND w.category_id = CAST(#{category} AS UNSIGNED))
            OR 
            -- 如果传入的是字符串，按category名称查询（兼容老数据）
            (#{category} NOT REGEXP '^[0-9]+$' AND #{category} != 'hot' AND w.category = #{category})
            OR
            -- 如果传入的是数字但老数据用的是分类名，通过category表查询
            (#{category} REGEXP '^[0-9]+$' AND w.category_id IS NULL AND w.category = (SELECT name FROM category WHERE id = CAST(#{category} AS UNSIGNED)))
          )
        ORDER BY w.like_count DESC, w.create_time DESC
        LIMIT #{offset}, #{limit}
    </select>

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

    <!-- 按分类ID查询工作流列表 -->
    <select id="selectWorkflowsByCategoryId" resultMap="WorkflowResultMap">
        SELECT 
            w.id, w.name, w.description, w.cover_url, w.detail_gallery, w.price, w.owner_id, w.category, w.category_id,
            w.audit_status, w.reject_reason, w.review_time, w.is_free, w.full_access_role,
            w.copy_access_role, w.is_public, w.like_count, w.rating,
            w.video_id, w.data, w.data_file_url, w.create_time, w.update_time,
            w.is_deleted
        FROM workflow w
        WHERE w.audit_status = 1 AND w.is_deleted = 0
          AND (w.category_id = #{categoryId} 
               OR (w.category_id IS NULL AND w.category = (SELECT name FROM category WHERE id = #{categoryId})))
        ORDER BY w.like_count DESC, w.create_time DESC
        LIMIT #{offset}, #{limit}
    </select>

    <select id="countWorkflowsByCategoryId" resultType="java.lang.Long">
        SELECT COUNT(*) 
        FROM workflow w
        WHERE w.audit_status = 1 AND w.is_deleted = 0
          AND (w.category_id = #{categoryId} 
               OR (w.category_id IS NULL AND w.category = (SELECT name FROM category WHERE id = #{categoryId})))
    </select>

    <select id="countAllWorkflows" resultType="java.lang.Long">
        SELECT COUNT(*) FROM workflow 
        WHERE audit_status = 1 AND is_deleted = 0
    </select>

    <select id="selectCategoryCounts" resultType="com.dora.mapper.WorkflowMapper$WorkflowCategoryCount">
        SELECT category, COUNT(*) as count 
        FROM workflow 
        WHERE audit_status = 1 AND is_deleted = 0 AND category IS NOT NULL
        GROUP BY category
        ORDER BY count DESC
    </select>

    <update id="submitForAudit">
        UPDATE workflow
        SET 
            name = COALESCE(#{name}, name),
            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>

    <insert id="insert" parameterType="com.dora.entity.Workflow" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO workflow (
            name, description, owner_id, cover_url, detail_gallery, video_id, data, data_file_url,
            like_count, is_public, full_access_role, copy_access_role, price,
            audit_status, category, rating, is_free, create_time, update_time, is_deleted
        ) VALUES (
            #{name}, #{description}, #{ownerId}, #{coverUrl}, #{detailGallery}, #{vodVideoId}, #{data}, #{dataFileUrl},
            COALESCE(#{likeCount}, 0), COALESCE(#{isPublic}, 0), 
            COALESCE(#{fullAccessRole}, 1), COALESCE(#{copyAccessRole}, 1), 
            COALESCE(#{price}, 0.00), 0, #{category}, #{rating}, 
            COALESCE(#{isFree}, 0), NOW(), NOW(), 0
        )
    </insert>

    <update id="updateLikeCount">
        UPDATE workflow
        SET like_count = #{likeCount}, update_time = NOW()
        WHERE id = #{workflowId} AND is_deleted = 0
    </update>

    <update id="updateById" parameterType="com.dora.entity.Workflow">
        UPDATE workflow
        <set>
            <if test="name != null">name = #{name},</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="vodVideoId != null">video_id = #{vodVideoId},</if>
            <if test="data != null">data = #{data},</if>
            <if test="dataFileUrl != null">data_file_url = #{dataFileUrl},</if>
            <if test="isPublic != null">is_public = #{isPublic},</if>
            <if test="fullAccessRole != null">full_access_role = #{fullAccessRole},</if>
            <if test="copyAccessRole != null">copy_access_role = #{copyAccessRole},</if>
            <if test="price != null">price = #{price},</if>
            <if test="category != null">category = #{category},</if>
            <if test="isFree != null">is_free = #{isFree},</if>
            <if test="auditStatus != null">audit_status = #{auditStatus},</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 workflow
        SET is_deleted = 1,
            update_time = NOW()
        WHERE id = #{id} AND owner_id = #{ownerId} AND is_deleted = 0
    </update>

    <select id="selectByNameAndOwner" resultMap="WorkflowResultMap">
        SELECT * FROM workflow 
        WHERE name = #{name} AND owner_id = #{ownerId} AND is_deleted = 0
        LIMIT 1
    </select>

    <!-- 搜索工作流 -->
    <select id="searchWorkflows" resultMap="WorkflowResultMap">
        SELECT * FROM workflow 
        WHERE audit_status = 1 AND is_deleted = 0
        <if test="params.keyword != null and params.keyword != ''">
            AND (
                name 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 like_count DESC, create_time DESC
        LIMIT #{params.offset}, #{params.limit}
    </select>

    <!-- 统计搜索工作流数量 -->
    <select id="countSearchWorkflows" resultType="Long">
        SELECT COUNT(*)
        FROM workflow 
        WHERE audit_status = 1 AND is_deleted = 0
        <if test="params.keyword != null and params.keyword != ''">
            AND (
                name 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>

    <!-- 根据VOD视频ID统计关联的工作流数量 -->
    <select id="countByVodVideoId" resultType="java.lang.Integer">
        SELECT COUNT(*) FROM workflow 
        WHERE video_id = #{vodVideoId} AND is_deleted = 0
    </select>

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

</mapper> 