<?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.vctgo.file.mapper.FileReUserOperateMapper">

    <resultMap type="com.vctgo.file.domain.FileReUserOperate" id="FileReUserOperateResult">
        <result property="id" column="id"/>
        <result property="fileId" column="file_id"/>
        <result property="userId" column="user_id"/>
        <result property="userName" column="user_name"/>
        <result property="phoneNumber" column="phone_number"/>
        <result property="fileUserAuthId" column="file_user_auth_id"/>
        <result property="fileUserAuthCode" column="file_user_auth_code"/>
        <result property="fileUserAuthDesc" column="file_user_auth_desc"/>
        <result property="remark" column="remark"/>
        <result property="isDeleted" column="is_deleted"/>
        <result property="createById" column="create_by_id"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateById" column="update_by_id"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="tenantId" column="tenant_id"/>
    </resultMap>

    <sql id="selectFileReUserOperateVo">
        select id, file_id, user_id, user_name,phone_number, file_user_auth_id,file_user_auth_code, file_user_auth_desc, remark, is_deleted, create_by_id, create_by, create_time, update_by_id, update_by, update_time, tenant_id from file_re_user_operate
    </sql>

    <sql id="defaultDeleteCondition">
        is_deleted = '0'
    </sql>

    <select id="selectFileReUserOperateList" resultMap="FileReUserOperateResult">
        <include refid="selectFileReUserOperateVo"/>
        <where>
            <include refid="defaultDeleteCondition"/>
            <if test="query.fileId != null  and query.fileId != ''">and file_id = #{query.fileId}</if>
            <if test="query.userId != null  and query.userId != ''">and user_id = #{query.userId}</if>
            <if test="query.userName != null  and query.userName != ''">and user_name like concat('%',
                #{query.userName}, '%')
            </if>
            <if test="query.fileUserAuthId != null ">and file_user_auth_id = #{query.fileUserAuthId}</if>
            <if test="query.fileUserAuthDesc != null  and query.fileUserAuthDesc != ''">and file_user_auth_desc =
                #{query.fileUserAuthDesc}
            </if>
            <if test="query.isDeleted != null  and query.isDeleted != ''">and is_deleted = #{query.isDeleted}</if>
            <if test="query.createById != null ">and create_by_id = #{query.createById}</if>
            <if test="query.updateById != null ">and update_by_id = #{query.updateById}</if>
            <if test="query.tenantId != null ">and tenant_id = #{query.tenantId}</if>
        </where>
    </select>

    <select id="selectGroupPermissionsByUserIdAndFileId" resultType="java.lang.String">
        SELECT
            g.group_permissions
        FROM
            file_re_user_operate p
                INNER JOIN file_auth_group g ON g.group_code = p.file_user_auth_code
        WHERE
            p.is_deleted = '0' AND p.user_id = #{userId} AND p.file_id = #{fileId}
    </select>

    <select id="selectRootFileIdByUserId" resultType="com.vctgo.file.domain.FileReUserOperate">
        SELECT
            p.file_id
        FROM
            file_re_user_operate p
                INNER JOIN file_info i ON i.id = p.file_id and i.file_parent_id = 0 and i.is_deleted=0 and i.file_is_hide=0
        WHERE
            p.is_deleted = '0' AND p.user_id = #{userId}
    </select>

    <select id="selectFileIdListByUserIdAndTenantId" resultType="java.lang.Long">
        SELECT o.file_id
        FROM file_re_user_operate o
        WHERE o.is_deleted = '0'
          AND o.user_id = #{userId}
          AND o.tenant_id = #{tenantId}
          AND o.file_user_auth_code != ''
	      AND o.file_user_auth_code IS NOT NULL
        UNION
        SELECT i.id
        FROM file_info i
        WHERE i.is_deleted = '0'
          AND i.file_is_hide = '0'
          AND i.file_status = '1'
          AND i.tenant_id = #{tenantId}
        UNION
        SELECT i.id
        FROM file_info i
        WHERE i.is_deleted = '0'
          AND i.file_is_hide = '0'
          AND i.file_status = '2'
          AND i.tenant_id = #{tenantId}
    </select>

    <select id="selectFileIdListFileParentIdAndTenantId" resultType="java.lang.Long">
        SELECT i.id
        FROM file_re_user_operate p INNER JOIN file_info i ON i.id = p.file_id
        WHERE
            p.is_deleted = '0' AND p.user_id = #{userId}  AND i.file_parent_id = #{parentId} AND i.is_deleted = '0' AND i.file_is_hide= '0'
    </select>

    <select id="selectFileIdListByAncestors" resultType="java.lang.Long" parameterType="list">
        SELECT id
        FROM file_info
        WHERE file_is_hide='0' AND is_deleted='0'
        <if test="fileIdList != null and fileIdList.size() != 0">
            AND
            <foreach collection="fileIdList" item="item" separator="OR" index="index" open="(" close=")">
                FIND_IN_SET(#{item}, ancestors)
            </foreach>
        </if>
    </select>

    <select id="selectAncestorsByUserIdAndFileId" resultType="com.vctgo.system.api.domain.FileInfo">
        SELECT i.id,
               i.ancestors,
               "1" file_name
        FROM file_re_user_operate uo
                 INNER JOIN file_info i ON uo.file_id = i.id
        WHERE uo.is_deleted = '0'
          AND uo.user_id = #{userId}
          AND uo.tenant_id = #{tenantId}
          AND uo.file_user_auth_code != ''
          AND i.is_deleted = '0'
          AND i.file_is_hide = '0'
        UNION
        SELECT fi.id,
               fi.ancestors,
               "2" file_name
        FROM file_re_user_operate ruo
                 INNER JOIN file_info fi ON ruo.file_id = fi.id
        WHERE ruo.user_id = #{userId}
          AND ruo.is_deleted = '0'
          AND ruo.file_user_auth_code = ''
          AND ruo.tenant_id = #{tenantId}
          AND fi.is_deleted = '0'
          AND fi.file_is_hide = '0'
        UNION
        SELECT fi.id,
               fi.ancestors,
               "1" file_name
        FROM   file_info fi
        WHERE fi.file_status = '2'
          AND fi.tenant_id = #{tenantId}
          AND fi.is_deleted = '0'
          AND fi.file_is_hide = '0'
    </select>

    <select id="selectFileByParentIdListAndTenantId" resultType="java.lang.Long">
        SELECT
        id
        FROM
        file_info
        WHERE
        is_deleted = '0'
        AND file_is_hide = '0'
        AND tenant_id = #{tenantId}
        <if test="fileIdList != null and fileIdList.size() != 0">
            AND id IN
            (SELECT id
            FROM file_info
            WHERE is_deleted = '0'
            AND file_is_hide = '0'
            <if test="fileIdList != null and fileIdList.size() != 0">
                AND
                <foreach collection="fileIdList" item="item" separator="OR" index="index" open="(" close=")">
                    FIND_IN_SET(#{item}, ancestors)
                </foreach>
            </if>
            <if test="fileIdList != null and fileIdList.size() != 0">
                <foreach collection="fileIdList" item="item" separator=" " index="index" open="" close="">
                    UNION ALL
                    select #{item}
                </foreach>
            </if>
            )
        </if>
        <if test="excludeFileIdList != null and excludeFileIdList.size() != 0">
            AND id NOT IN
            (SELECT id
            FROM file_info
            WHERE is_deleted = '0'
            AND file_is_hide = '0'
            <if test="excludeFileIdList != null and excludeFileIdList.size() != 0">
                AND
                <foreach collection="excludeFileIdList" item="item" separator="OR" index="index" open="(" close=")">
                    FIND_IN_SET(#{item}, ancestors)
                </foreach>
            </if>
            <if test="excludeFileIdList != null and excludeFileIdList.size() != 0">
                <foreach collection="excludeFileIdList" item="item" separator=" " index="index" open="" close="">
                    UNION ALL
                    select #{item}
                </foreach>
            </if>
            )
        </if>
    </select>

    <select id="selectGroupPermissionListByUserIdAndFileId" resultType="com.vctgo.file.domain.FileReUserOperate">
        SELECT
            p.file_id,
            p.user_id,
            p.file_user_auth_code,
            g.group_permissions
        FROM
            file_re_user_operate p
                INNER JOIN file_auth_group g ON g.group_code = p.file_user_auth_code
        WHERE
            p.is_deleted = '0'
          AND p.user_id = #{userId} AND p.file_id in
        <foreach item="fileId" collection="fileIdList" open="(" separator="," close=")">
            #{fileId}
        </foreach>
    </select>

    <select id="selectFileIdByFileParentIdAndTenantId" resultType="java.lang.Long">
        SELECT
            i.id file_id
        FROM
            file_info i
        WHERE
            i.is_deleted = '0'
          AND i.file_is_hide = '0'
          AND i.file_parent_id = #{parentId}
          AND i.tenant_id = #{tenantId}
          AND i.id not in (SELECT up.file_id FROM file_re_user_operate up WHERE up.is_deleted='0' AND up.file_user_auth_code='' AND up.user_id=#{userId} );
    </select>

    <select id="selectFileByTenantIdAndFileId" resultType="com.vctgo.file.domain.FileReUserOperate">
        SELECT
            u.*
        FROM
            file_re_user_operate u
        WHERE
            FIND_IN_SET(u.file_id,#{fileIds} ) AND u.is_deleted='0' AND u.file_user_auth_code != '' AND u.file_user_auth_code IS NOT NULL
    </select>

    <update id="logicalDeleteFileReUserOperateByIds" parameterType="String">
        update file_re_user_operate set is_deleted = #{query.isDeleted},
        update_by_id = #{query.updateById}, update_by = #{query.updateBy},
        update_time = #{query.updateTime} where id in
        <foreach item="id" collection="ids" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>

    <update id="updateFileByUserOperate">
        UPDATE file_re_user_operate
        SET is_deleted = '1',
        update_time = NOW(),
        update_by_id = #{userId}
        WHERE file_id IN
        (SELECT id
        FROM file_info
        WHERE is_deleted = '0'
        AND file_is_hide = '0'
        <if test="fileIdList != null and fileIdList.size() != 0">
            AND
            <foreach collection="fileIdList" item="item" separator="OR" index="index" open="(" close=")">
                FIND_IN_SET(#{item}, ancestors)
            </foreach>
        </if>
        <if test="fileIdList != null and fileIdList.size() != 0">
            <foreach collection="fileIdList" item="item" separator=" " index="index" open="" close="">
                UNION ALL
                select #{item}
            </foreach>
        </if>
        )
    </update>

    <delete id="deleteFileReUserOperateByIds" parameterType="String">
        delete from file_re_user_operate where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <delete id="deleteByFileId" parameterType="String">
        delete from file_re_user_operate where file_id = #{fileId}
    </delete>
</mapper>
