<?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.news.mapper.ResourceMapper">

    <!-- 基础结果映射 -->
    <resultMap id="BaseResultMap" type="org.xyzh.common.dto.resource.TbResource">
        <id column="id" property="id" jdbcType="VARCHAR"/>
        <result column="resource_id" property="resourceID" jdbcType="VARCHAR"/>
        <result column="title" property="title" jdbcType="VARCHAR"/>
        <result column="content" property="content" jdbcType="LONGVARCHAR"/>
        <result column="summary" property="summary" jdbcType="VARCHAR"/>
        <result column="cover_image" property="coverImage" jdbcType="VARCHAR"/>
        <result column="tag_id" property="tagID" jdbcType="VARCHAR"/>
        <result column="author" property="author" jdbcType="VARCHAR"/>
        <result column="source" property="source" jdbcType="VARCHAR"/>
        <result column="source_url" property="sourceUrl" jdbcType="VARCHAR"/>
        <result column="view_count" property="viewCount" jdbcType="INTEGER"/>
        <result column="like_count" property="likeCount" jdbcType="INTEGER"/>
        <result column="collect_count" property="collectCount" jdbcType="INTEGER"/>
        <result column="status" property="status" jdbcType="INTEGER"/>
        <result column="is_recommend" property="isRecommend" jdbcType="BOOLEAN"/>
        <result column="is_banner" property="isBanner" jdbcType="BOOLEAN"/>
        <result column="publish_time" property="publishTime" jdbcType="TIMESTAMP"/>
        <result column="creator" property="creator" jdbcType="VARCHAR"/>
        <result column="updater" property="updater" jdbcType="VARCHAR"/>
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
        <result column="update_time" property="updateTime" jdbcType="TIMESTAMP"/>
        <result column="delete_time" property="deleteTime" jdbcType="TIMESTAMP"/>
        <result column="deleted" property="deleted" jdbcType="BOOLEAN"/>
    </resultMap>

    <!-- 基础字段 -->
    <sql id="Base_Column_List">
        id, resource_id, title, content, summary, cover_image, tag_id, author, source, 
        source_url, view_count, like_count, collect_count, status, is_recommend, 
        is_banner, publish_time, creator, updater, create_time, update_time, 
        delete_time, deleted
    </sql>

    <!-- 通用条件 -->
    <sql id="Where_Clause">
        <where>
            deleted = 0
            <if test="title != null and title != ''">
                AND title LIKE CONCAT('%', #{title}, '%')
            </if>
            <if test="tagID != null and tagID != ''">
                AND tag_id = #{tagID}
            </if>
            <if test="author != null and author != ''">
                AND author LIKE CONCAT('%', #{author}, '%')
            </if>
            <if test="status != null">
                AND status = #{status}
            </if>
            <if test="isRecommend != null">
                AND is_recommend = #{isRecommend}
            </if>
            <if test="isBanner != null">
                AND is_banner = #{isBanner}
            </if>
        </where>
    </sql>

    <!-- 权限过滤条件（基于dept_path的高效继承） -->
    <sql id="Permission_Filter">
        INNER JOIN tb_resource_permission rp ON r.resource_id = rp.resource_id 
            AND rp.resource_type = 1 
            AND rp.deleted = 0
            AND rp.can_read = 1
            AND (
                -- 全局权限：所有用户可访问
                (rp.dept_id IS NULL AND rp.role_id IS NULL)
                <if test="userDeptRoles != null and userDeptRoles.size() > 0">
                    OR EXISTS (
                        SELECT 1 
                        FROM (
                            <foreach collection="userDeptRoles" item="udr" separator=" UNION ALL ">
                                SELECT #{udr.deptID} AS dept_id, #{udr.deptPath} AS dept_path, #{udr.roleID} AS role_id
                            </foreach>
                        ) user_roles
                        LEFT JOIN tb_sys_dept perm_dept ON perm_dept.dept_id = rp.dept_id AND perm_dept.deleted = 0
                        WHERE 
                           -- 部门级权限：当前部门或父部门（通过dept_path判断继承关系）
                           (rp.role_id IS NULL AND rp.dept_id IS NOT NULL 
                               AND user_roles.dept_path LIKE CONCAT(perm_dept.dept_path, '%'))
                           -- 角色级权限：跨部门的角色权限
                           OR (rp.dept_id IS NULL AND rp.role_id = user_roles.role_id)
                           -- 精确权限：特定部门的特定角色
                           OR (rp.dept_id = user_roles.dept_id AND rp.role_id = user_roles.role_id)
                    )
                </if>
            )
    </sql>

    <!-- selectResources - 添加权限过滤 -->
    <select id="selectResources" resultMap="BaseResultMap">
        SELECT DISTINCT r.*
        FROM tb_resource r
        <include refid="Permission_Filter"/>
        WHERE r.deleted = 0
            <if test="filter.title != null and filter.title != ''">
                AND r.title LIKE CONCAT('%', #{filter.title}, '%')
            </if>
            <if test="filter.tagID != null and filter.tagID != ''">
                AND r.tag_id = #{filter.tagID}
            </if>
            <if test="filter.author != null and filter.author != ''">
                AND r.author LIKE CONCAT('%', #{filter.author}, '%')
            </if>
            <if test="filter.status != null">
                AND r.status = #{filter.status}
            </if>
            <if test="filter.isRecommend != null">
                AND r.is_recommend = #{filter.isRecommend}
            </if>
            <if test="filter.isBanner != null">
                AND r.is_banner = #{filter.isBanner}
            </if>
        ORDER BY r.publish_time DESC, r.create_time DESC
    </select>

    <!-- 根据资源ID查询资源信息 -->
    <select id="selectByResourceId" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_resource
        WHERE resource_id = #{resourceId} AND deleted = 0
    </select>

    <!-- 根据标签ID查询资源列表 -->
    <select id="selectByTagId" resultMap="BaseResultMap">
        SELECT DISTINCT r.*
        FROM tb_resource r
        <include refid="Permission_Filter"/>
        WHERE r.tag_id = #{tagId} AND r.deleted = 0
        ORDER BY r.publish_time DESC, r.create_time DESC
    </select>

    <!-- 根据状态查询资源列表 -->
    <select id="selectByStatus" resultMap="BaseResultMap">
        SELECT DISTINCT r.*
        FROM tb_resource r
        <include refid="Permission_Filter"/>
        WHERE r.status = #{status} AND r.deleted = 0
        ORDER BY r.publish_time DESC, r.create_time DESC
    </select>

    <!-- 根据标签类型查询资源列表 -->
    <select id="selectByType" resultMap="BaseResultMap">
        SELECT DISTINCT r.*
        FROM tb_resource r
        <include refid="Permission_Filter"/>
        WHERE r.tag_id = #{type} AND r.deleted = 0
        ORDER BY r.publish_time DESC, r.create_time DESC
    </select>

    <!-- 查询热门资源列表 -->
    <select id="selectHotResources" resultMap="BaseResultMap">
        SELECT DISTINCT r.*
        FROM tb_resource r
        <include refid="Permission_Filter"/>
        WHERE r.status = 1 AND r.deleted = 0
        ORDER BY r.view_count DESC, r.like_count DESC, r.publish_time DESC
        <if test="limit != null and limit > 0">
            LIMIT #{limit}
        </if>
    </select>

    <!-- 查询最新资源列表 -->
    <select id="selectLatestResources" resultMap="BaseResultMap">
        SELECT DISTINCT r.*
        FROM tb_resource r
        <include refid="Permission_Filter"/>
        WHERE r.status = 1 AND r.deleted = 0
        ORDER BY r.publish_time DESC, r.create_time DESC
        <if test="limit != null and limit > 0">
            LIMIT #{limit}
        </if>
    </select>

    <!-- 根据关键词搜索资源 -->
    <select id="searchByKeyword" resultMap="BaseResultMap">
        SELECT DISTINCT r.*
        FROM tb_resource r
        <include refid="Permission_Filter"/>
        WHERE (r.title LIKE CONCAT('%', #{keyword}, '%') 
               OR content LIKE CONCAT('%', #{keyword}, '%')
               OR summary LIKE CONCAT('%', #{keyword}, '%'))
        AND r.status = 1 AND r.deleted = 0
        ORDER BY r.publish_time DESC, r.create_time DESC
    </select>

    <!-- 检查资源标题是否存在 -->
    <select id="countByTitle" resultType="int">
        SELECT COUNT(DISTINCT r.id)
        FROM tb_resource r
        <include refid="Permission_Filter"/>
        WHERE r.title = #{title} AND r.deleted = 0
        <if test="excludeId != null and excludeId != ''">
            AND r.resource_id != #{excludeId}
        </if>
    </select>

    <!-- 插入资源 -->
    <insert id="insertResource" parameterType="org.xyzh.common.dto.resource.TbResource">
        INSERT INTO tb_resource (
            id, resource_id, title, content, summary, cover_image, tag_id, author, source,
            source_url, creator,create_time
        ) VALUES (
            #{id}, #{resourceID}, #{title}, #{content}, #{summary}, #{coverImage}, #{tagID}, #{author}, #{source},
            #{sourceUrl}, #{creator}, #{createTime}
        )
    </insert>

    <!-- 更新资源 -->
    <update id="updateResource" parameterType="org.xyzh.common.dto.resource.TbResource">
        UPDATE tb_resource
        <set>
            <if test="title != null and title != ''">
                title = #{title},
            </if>
            <if test="content != null and content != ''">
                content = #{content},
            </if>
            <if test="summary != null and summary != ''">
                summary = #{summary},
            </if>
            <if test="coverImage != null and coverImage != ''">
                cover_image = #{coverImage},
            </if>
            <if test="tagID != null and tagID != ''">
                tag_id = #{tagID},
            </if>
            <if test="author != null and author != ''">
                author = #{author},
            </if>
            <if test="source != null and source != ''">
                source = #{source},
            </if>
            <if test="sourceUrl != null and sourceUrl != ''">
                source_url = #{sourceUrl},
            </if>
            <if test="viewCount != null">
                view_count = #{viewCount},
            </if>
            <if test="likeCount != null">
                like_count = #{likeCount},
            </if>
            <if test="collectCount != null">
                collect_count = #{collectCount},
            </if>
            <if test="status != null">
                status = #{status},
            </if>
            <if test="isRecommend != null">
                is_recommend = #{isRecommend},
            </if>
            <if test="isBanner != null">
                is_banner = #{isBanner},
            </if>
            <if test="publishTime != null">
                publish_time = #{publishTime},
            </if>
            <if test="updater != null and updater != ''">
                updater = #{updater},
            </if>
            <if test="updateTime != null">
                update_time = #{updateTime},
            </if>
            <if test="deleteTime != null">
                delete_time = #{deleteTime},
            </if>
            <if test="deleted != null">
                deleted = #{deleted},
            </if>
        </set>
        WHERE resource_id = #{resourceID}
    </update>

    <!-- 删除资源 -->
    <delete id="deleteResource" parameterType="org.xyzh.common.dto.resource.TbResource">
        DELETE FROM tb_resource
        WHERE resource_id = #{resourceID}
    </delete>

    <!-- 批量插入资源 -->
    <insert id="batchInsertResources" parameterType="java.util.List">
        INSERT INTO tb_resource (
            id, resource_id, title, content, summary, cover_image, tag_id, author, source,
            source_url, creator,create_time
        ) VALUES
        <foreach collection="resourceList" item="item" separator=",">
            (
                #{item.id}, #{item.resourceID}, #{item.title}, #{item.content}, #{item.summary}, #{item.coverImage}, 
                #{item.tagID}, #{item.author}, #{item.source}, #{item.sourceUrl}, 
                #{item.creator}, #{item.createTime}
            )
        </foreach>
    </insert>

    <!-- 批量删除资源 -->
    <delete id="batchDeleteResources">
        DELETE FROM tb_resource
        WHERE resource_id IN
        <foreach collection="ids" item="id" open="(" separator="," close=")">
            #{resourceID}
        </foreach>
    </delete>

    <!-- 分页查询资源 - 添加权限过滤 -->
    <select id="selectResourcesPage" resultMap="BaseResultMap">
        SELECT DISTINCT r.*
        FROM tb_resource r
        <include refid="Permission_Filter"/>
        WHERE r.deleted = 0
            <if test="filter.title != null and filter.title != ''">
                AND r.title LIKE CONCAT('%', #{filter.title}, '%')
            </if>
            <if test="filter.tagID != null and filter.tagID != ''">
                AND r.tag_id = #{filter.tagID}
            </if>
            <if test="filter.author != null and filter.author != ''">
                AND r.author LIKE CONCAT('%', #{filter.author}, '%')
            </if>
            <if test="filter.title != null and filter.title != ''">
                AND r.title LIKE CONCAT('%', #{filter.title}, '%')
            </if>
            <if test="filter.status != null">
                AND r.status = #{filter.status}
            </if>
            <if test="filter.isRecommend != null">
                AND r.is_recommend = #{filter.isRecommend}
            </if>
            <if test="filter.isBanner != null">
                AND r.is_banner = #{filter.isBanner}
            </if>
        ORDER BY r.publish_time DESC, r.create_time DESC
        LIMIT #{pageParam.pageSize} OFFSET #{pageParam.offset}
    </select>

    <!-- ResourceVO结果映射（包含推荐信息） -->
    <resultMap id="ResourceVOResultMap" type="org.xyzh.common.vo.ResourceVO">
        <result column="is_top_recommend" property="isTopRecommend" jdbcType="BOOLEAN"/>
        <result column="is_ideological_recommend" property="isIdeologicalRecommend" jdbcType="BOOLEAN"/>
        <association property="resource" javaType="org.xyzh.common.dto.resource.TbResource">
            <id column="id" property="id" jdbcType="VARCHAR"/>
            <result column="resource_id" property="resourceID" jdbcType="VARCHAR"/>
            <result column="title" property="title" jdbcType="VARCHAR"/>
            <result column="content" property="content" jdbcType="LONGVARCHAR"/>
            <result column="summary" property="summary" jdbcType="VARCHAR"/>
            <result column="cover_image" property="coverImage" jdbcType="VARCHAR"/>
            <result column="tag_id" property="tagID" jdbcType="VARCHAR"/>
            <result column="author" property="author" jdbcType="VARCHAR"/>
            <result column="source" property="source" jdbcType="VARCHAR"/>
            <result column="source_url" property="sourceUrl" jdbcType="VARCHAR"/>
            <result column="view_count" property="viewCount" jdbcType="INTEGER"/>
            <result column="like_count" property="likeCount" jdbcType="INTEGER"/>
            <result column="collect_count" property="collectCount" jdbcType="INTEGER"/>
            <result column="status" property="status" jdbcType="INTEGER"/>
            <result column="is_recommend" property="isRecommend" jdbcType="BOOLEAN"/>
            <result column="is_banner" property="isBanner" jdbcType="BOOLEAN"/>
            <result column="publish_time" property="publishTime" jdbcType="TIMESTAMP"/>
            <result column="creator" property="creator" jdbcType="VARCHAR"/>
            <result column="updater" property="updater" jdbcType="VARCHAR"/>
            <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
            <result column="update_time" property="updateTime" jdbcType="TIMESTAMP"/>
            <result column="delete_time" property="deleteTime" jdbcType="TIMESTAMP"/>
            <result column="deleted" property="deleted" jdbcType="BOOLEAN"/>
        </association>
    </resultMap>

    <select id="selectResourcesPageOrderByViewCount" resultMap="ResourceVOResultMap">
        SELECT 
            r.*,
            MAX(CASE WHEN rec.recommend_type = 1 THEN 1 ELSE 0 END) AS is_top_recommend,
            MAX(CASE WHEN rec.recommend_type = 2 THEN 1 ELSE 0 END) AS is_ideological_recommend
        FROM tb_resource r
        LEFT JOIN tb_resource_recommend rec ON r.resource_id = rec.resource_id AND rec.deleted = 0
        <include refid="Permission_Filter"/>
        WHERE r.deleted = 0
            <if test="filter.title != null and filter.title != ''">
                AND r.title LIKE CONCAT('%', #{filter.title}, '%')
            </if>
            <if test="filter.tagID != null and filter.tagID != ''">
                AND r.tag_id = #{filter.tagID}
            </if>
            <if test="filter.author != null and filter.author != ''">
                AND r.author LIKE CONCAT('%', #{filter.author}, '%')
            </if>
            <if test="filter.status != null">
                AND r.status = #{filter.status}
            </if>
            <if test="filter.isRecommend != null">
                AND r.is_recommend = #{filter.isRecommend}
            </if>
            <if test="filter.isBanner != null">
                AND r.is_banner = #{filter.isBanner}
            </if>
        GROUP BY r.id, r.resource_id, r.title, r.content, r.summary, r.cover_image, r.tag_id, 
                 r.author, r.source, r.source_url, r.view_count, r.like_count, r.collect_count, 
                 r.status, r.is_recommend, r.is_banner, r.publish_time, r.creator, r.updater, 
                 r.create_time, r.update_time, r.delete_time, r.deleted
        ORDER BY r.view_count DESC, r.publish_time DESC, r.create_time DESC
        LIMIT #{pageParam.pageSize} OFFSET #{pageParam.offset}
    </select>

    <!-- 统计资源总数 - 添加权限过滤 -->
    <select id="countResources" resultType="long">
        SELECT COUNT(DISTINCT r.id)
        FROM tb_resource r
        <include refid="Permission_Filter"/>
        WHERE r.deleted = 0
            <if test="filter.title != null and filter.title != ''">
                AND r.title LIKE CONCAT('%', #{filter.title}, '%')
            </if>
            <if test="filter.tagID != null and filter.tagID != ''">
                AND r.tag_id = #{filter.tagID}
            </if>
            <if test="filter.author != null and filter.author != ''">
                AND r.author LIKE CONCAT('%', #{filter.author}, '%')
            </if>
            <if test="filter.status != null">
                AND r.status = #{filter.status}
            </if>
            <if test="filter.isRecommend != null">
                AND r.is_recommend = #{filter.isRecommend}
            </if>
            <if test="filter.isBanner != null">
                AND r.is_banner = #{filter.isBanner}
            </if>
            <if test="filter.startTime != null">
                AND r.publish_time &gt;= #{filter.startTime}
            </if>
            <if test="filter.endTime != null">
                AND r.publish_time &lt;= #{filter.endTime}
            </if>
    </select>

    <!-- updateResourceCollectCount --> 

    <update id="updateResourceCollectCount">
        UPDATE tb_resource
        SET collect_count = collect_count + #{collectionValue}
        WHERE resource_id = #{resourceID}
    </update>

    <!-- incrementViewCount --> 

    <update id="incrementViewCount">
        UPDATE tb_resource
        SET view_count = view_count + 1
        WHERE resource_id = #{resourceID}
    </update>


    <!-- selectItem --> 
    <!-- 联合模糊查询文章和课程,时间排序 -->
    <select id="selectItem" resultType="org.xyzh.common.vo.TaskItemVO">
        SELECT * FROM (
            -- 查询文章资源
            SELECT DISTINCT
                1 AS itemType,
                r.resource_id AS resourceID,
                r.title AS resourceName,
                NULL AS courseID,
                NULL AS courseName,
                r.author,
                r.cover_image AS coverImage,
                r.summary,
                r.view_count AS viewCount,
                r.publish_time AS publishTime,
                r.create_time AS createTime
            FROM tb_resource r
            INNER JOIN tb_resource_permission rp ON r.resource_id = rp.resource_id 
                AND rp.resource_type = 1 
                AND rp.deleted = 0
                AND rp.can_read = 1
                AND (
                    -- 全局权限：所有用户可访问
                    (rp.dept_id IS NULL AND rp.role_id IS NULL)
                    <if test="userDeptRoles != null and userDeptRoles.size() > 0">
                        OR EXISTS (
                            SELECT 1 
                            FROM (
                                <foreach collection="userDeptRoles" item="udr" separator=" UNION ALL ">
                                    SELECT #{udr.deptID} AS dept_id, #{udr.deptPath} AS dept_path, #{udr.roleID} AS role_id
                                </foreach>
                            ) user_roles
                            LEFT JOIN tb_sys_dept perm_dept ON perm_dept.dept_id = rp.dept_id AND perm_dept.deleted = 0
                            WHERE 
                               -- 部门级权限：当前部门或父部门（通过dept_path判断继承关系）
                               (rp.role_id IS NULL AND rp.dept_id IS NOT NULL 
                                   AND user_roles.dept_path LIKE CONCAT(perm_dept.dept_path, '%'))
                               -- 角色级权限：跨部门的角色权限
                               OR (rp.dept_id IS NULL AND rp.role_id = user_roles.role_id)
                               -- 精确权限：特定部门的特定角色
                               OR (rp.dept_id = user_roles.dept_id AND rp.role_id = user_roles.role_id)
                        )
                    </if>
                )
            WHERE r.deleted = 0
                <if test="filter.status != null">
                    AND r.status = #{filter.status}
                </if>
                <if test="filter.title != null and filter.title != ''">
                    AND (r.title LIKE CONCAT('%', #{filter.title}, '%') 
                         OR r.summary LIKE CONCAT('%', #{filter.title}, '%'))
                </if>
                
            UNION ALL
            
            -- 查询课程资源
            SELECT DISTINCT
                2 AS itemType,
                NULL AS resourceID,
                NULL AS resourceName,
                c.course_id AS courseID,
                c.name AS courseName,
                c.teacher AS author,
                c.cover_image AS coverImage,
                c.description AS summary,
                c.view_count AS viewCount,
                NULL AS publishTime,
                c.create_time AS createTime
            FROM tb_course c
            INNER JOIN tb_resource_permission rp ON c.course_id = rp.resource_id 
                AND rp.resource_type = 2 
                AND rp.deleted = 0
                AND rp.can_read = 1
                AND (
                    -- 全局权限：所有用户可访问
                    (rp.dept_id IS NULL AND rp.role_id IS NULL)
                    <if test="userDeptRoles != null and userDeptRoles.size() > 0">
                        OR EXISTS (
                            SELECT 1 
                            FROM (
                                <foreach collection="userDeptRoles" item="udr" separator=" UNION ALL ">
                                    SELECT #{udr.deptID} AS dept_id, #{udr.deptPath} AS dept_path, #{udr.roleID} AS role_id
                                </foreach>
                            ) user_roles
                            LEFT JOIN tb_sys_dept perm_dept ON perm_dept.dept_id = rp.dept_id AND perm_dept.deleted = 0
                            WHERE 
                               -- 部门级权限：当前部门或父部门（通过dept_path判断继承关系）
                               (rp.role_id IS NULL AND rp.dept_id IS NOT NULL 
                                   AND user_roles.dept_path LIKE CONCAT(perm_dept.dept_path, '%'))
                               -- 角色级权限：跨部门的角色权限
                               OR (rp.dept_id IS NULL AND rp.role_id = user_roles.role_id)
                               -- 精确权限：特定部门的特定角色
                               OR (rp.dept_id = user_roles.dept_id AND rp.role_id = user_roles.role_id)
                        )
                    </if>
                )
            WHERE c.deleted = 0
                <if test="filter.status != null">
                    AND c.status = #{filter.status}
                </if>
                <if test="filter.title != null and filter.title != ''">
                    AND (c.name LIKE CONCAT('%', #{filter.title}, '%') 
                         OR c.description LIKE CONCAT('%', #{filter.title}, '%'))
                </if>
        ) AS combined_results
        -- 按时间倒序排列，优先使用发布时间，其次使用创建时间
        ORDER BY COALESCE(publishTime, createTime) DESC
        <if test="pageParam != null">
            LIMIT #{pageParam.offset}, #{pageParam.pageSize}
        </if>
    </select>
</mapper>
