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

    <resultMap type="com.vctgo.system.api.domain.FileInfo" id="FileInfoResult">
        <id property="id" column="id"/>
        <result property="serviceId" column="service_id"/>
        <result property="serviceName" column="service_name"/>
        <result property="fileUrl" column="file_url"/>
        <result property="fileExtension" column="file_extension"/>
        <result property="fileName" column="file_name"/>
        <result property="fileFullName" column="file_full_name"/>
        <result property="fileStorageUrl" column="file_storage_url"/>
        <result property="fileType" column="file_type"/>
        <result property="fileStatus" column="file_status"/>
        <result property="filePreviewUrl" column="file_preview_url"/>
        <result property="fileExpirationDate" column="file_expiration_date"/>
        <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"/>
        <result property="fileIsHide" column="file_is_hide"/>
        <result property="fileIsDirectory" column="file_is_directory"/>
        <result property="fileParentId" column="file_parent_id"/>
        <result property="fileSize" column="file_size"/>
        <result property="fileStorageName" column="file_storage_name"/>
        <result property="ancestors" column="ancestors"/>
    </resultMap>

    <sql id="selectFileVo">
        select id, service_id, service_name, file_url, file_extension, file_name,file_full_name,
               file_storage_url, file_type, file_status, file_preview_url, is_deleted,
               create_by_id, create_by, create_time, update_by_id, update_by, update_time,
               file_expiration_date, tenant_id,file_is_hide,file_is_directory,file_parent_id,file_size,file_storage_name,ancestors from file_info
    </sql>

    <resultMap type="com.vctgo.system.api.domain.FileInfo" id="selectFileInfoListById" extends="FileInfoResult">
        <collection property="sysFileList" ofType="com.vctgo.system.api.domain.FileInfo" javaType="java.util.List"
                    column="{id = id}" select="selectFileById" fetchType="eager"/>
    </resultMap>

    <select id="selectFileById" resultMap="selectFileInfoListById">
        select * from file_info where is_deleted='0' AND file_parent_id = #{id} AND file_is_hide='0'
    </select>

    <select id="selectOperateFileList" resultMap="FileInfoResult">
        SELECT tab1.* FROM file_info tab1
        INNER JOIN file_re_user_operate tab2 ON tab1.id = tab2.file_id
        WHERE
            tab1.service_name = 'vctgo-file'
            AND tab1.file_is_directory = '1'
            AND tab1.file_is_hide = '0'
            AND tab1.is_deleted = '0'
            AND tab1.tenant_id =#{tenantId}
            AND tab2.user_id =#{userId}
            AND tab2.is_deleted = '0'
            AND tab2.file_user_auth_code != '' AND tab2.file_user_auth_code IS NOT NULL
            order by tab1.file_status asc,tab1.update_time desc
    </select>

    <select id="selectListByAncestors" resultMap="FileInfoResult" parameterType="list">
        select
        *
        from file_info
        where
        file_is_hide='0'
        AND is_deleted='0'
        AND service_name ='vctgo-file'
        AND file_is_directory = '1'
        <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>

        UNION

        select
        *
        from file_info
        where
        file_is_hide='0'
        AND is_deleted='0'
        AND service_name ='vctgo-file'
        AND file_is_directory = '1'
        <if test="fileIdList != null and fileIdList.size() != 0">
            AND id in
            <foreach collection="fileIdList" item="item" index="index" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        order by file_status asc,update_time desc
    </select>

    <select id="selectListByParentId" resultMap="FileInfoResult" parameterType="java.lang.Long">
        select * from file_info where find_in_set(#{fileParentId},ancestors) AND file_is_hide='0' AND is_deleted='0' AND service_name ='vctgo-file'
    </select>

    <select id="selectAllListByParentId" resultMap="FileInfoResult" parameterType="java.lang.Long">
        select * from file_info where find_in_set(#{fileParentId},ancestors) AND service_name ='vctgo-file'
    </select>

    <select id="selectFileManagePage" resultMap="FileInfoResult">
        <include refid="selectFileVo"/>
        <where>
            <if test="query.serviceName !=null and  query.serviceName != '' ">
                AND service_name = #{query.serviceName}
            </if>
            <if test="query.serviceId !=null and  query.serviceId != '' ">
                AND service_id = #{query.serviceId}
            </if>
            <if test="query.fileParentId != null ">
                AND file_parent_id = #{query.fileParentId}
            </if>
            <if test="query.fileParentId == 0 ">
                AND file_parent_id = 0 AND file_is_directory = '1'
            </if>
            <if test="query.fileName != null and query.fileName != '' ">
                AND file_name like concat('%',#{query.fileName},'%')
            </if>
            <if test="query.fileType != null  and query.fileType != ''">
                AND file_type = #{query.fileType}
            </if>
            <if test="query.fileStatus != null  and query.fileStatus != ''">
                AND file_status = #{query.fileStatus}
            </if>
            <if test="query.filePreviewUrl != null  and query.filePreviewUrl != ''">
                AND file_preview_url =#{query.filePreviewUrl}
            </if>
            <if test="query.fileIsHide != null  and query.fileIsHide != ''">
                AND file_is_hide = #{query.fileIsHide}
            </if>
            <if test="query.tenantId != null ">
                AND tenant_id = #{query.tenantId}
            </if>
            <if test="query.isDeleted != null  and query.isDeleted != ''">
                AND is_deleted = #{query.isDeleted}
            </if>
            <if test="query.fileIdList !=null and query.fileIdList.size() > 0">
                AND id in
                <foreach collection="query.fileIdList" item="item" index="index" separator="," open="(" close=")">
                    #{item}
                </foreach>
            </if>
        </where>
        <if test="query.params.orderNumber == 1">
            order by file_status asc,update_time desc
        </if>
        <if test="query.params.orderNumber != 1">
            order by file_status asc,file_is_directory desc, update_time desc
        </if>
    </select>

    <select id="selectFileInfoList" resultMap="selectFileInfoListById">
        <include refid="selectFileVo"/>
        <where>
            <if test="query.serviceName !=null and  query.serviceName != '' ">
                AND service_name = #{query.serviceName}
            </if>
            <if test="query.serviceId !=null and  query.serviceId != '' ">
                AND service_id = #{query.serviceId}
            </if>
            <if test="query.fileUrl != null  and query.fileUrl != ''">
                AND file_url = #{query.fileUrl}
            </if>
            <if test="query.fileExtension != null  and query.fileExtension != ''">
                AND file_extension =#{query.fileExtension}
            </if>
            <if test="query.fileName != null  and query.fileName != ''">
                AND file_name like concat('%',#{query.fileName},'%')
            </if>
            <if test="query.fileStorageUrl != null  and query.fileStorageUrl != ''">
                AND file_storage_url =#{query.fileStorageUrl}
            </if>
            <if test="query.fileType != null  and query.fileType != ''">
                AND file_type = #{query.fileType}
            </if>
            <if test="query.fileStatus != null  and query.fileStatus != ''">
                AND file_status = #{query.fileStatus}
            </if>
            <if test="query.filePreviewUrl != null  and query.filePreviewUrl != ''">
                AND file_preview_url =#{query.filePreviewUrl}
            </if>
            <if test="query.isDeleted != null  and query.isDeleted != ''">
                AND is_deleted = #{query.isDeleted}
            </if>
            <if test="query.fileIsHide != null  and query.fileIsHide != ''">
                AND file_is_hide = #{query.fileIsHide}
            </if>
            <if test="query.fileParentId != null ">
                AND file_parent_id = #{query.fileParentId}
            </if>
            <if test="query.tenantId != null ">
                AND tenant_id = #{query.tenantId}
            </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.fileIdList !=null and query.fileIdList.size() > 0">
                AND id in
                <foreach collection="query.fileIdList" item="item" index="index" separator="," open="(" close=")">
                    #{item}
                </foreach>
            </if>
        </where>
        <if test="query.params.orderNumber == 1">
            order by create_time desc
        </if>
        <if test="query.params.orderNumber != 1">
            order by file_is_directory desc, create_time desc, id desc
        </if>
    </select>

    <select id="selectPointTenantUrlByIds" resultMap="FileInfoResult">
        <include refid="selectFileVo"/>
        <where>id in
            <foreach item="id" collection="ids" open="(" separator="," close=")">
                #{id}
            </foreach>
        </where>
    </select>

    <update id="updateFileServiceIdANDServiceNameByIds">
        update file_info set service_id = #{serviceId}, service_name = #{serviceName}
        <where>
            <if test="ids !=null and  ids != '' ">
                id in
                <foreach item="item" index="index" collection="ids.split(',')" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
        </where>
    </update>

    <update id="updateFileStatusByServiceIdANDServiceName">
        update file_info set is_deleted = #{status}
        <where>
            <if test="serviceName !=null and  serviceName != '' ">
                service_name = #{serviceName}
            </if>
            <if test="serviceId !=null and  serviceId != '' ">
                AND service_id = #{serviceId}
            </if>
        </where>
    </update>

    <update id="updateFileServiceIdANDServiceNameANDStatusByIds">
        update file_info set is_deleted = #{status},service_id = #{serviceId}, service_name = #{serviceName}
        <where>
            <if test="ids !=null and  ids != '' ">
                id in
                <foreach item="item" index="index" collection="ids.split(',')" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
        </where>
    </update>

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

    <update id="moveFileByIds">
        update file_info set file_parent_id = #{toParentId}, update_by_id = #{userId}, update_time = NOW(), ancestors =
        #{ancestors} WHERE id in
        <foreach item="item" index="index" collection="ids.split(',')" open="(" separator="," close=")">
            #{item}
        </foreach>
    </update>

    <update id="hideFileByIds">
        update file_info set file_is_hide = '1', update_by_id = #{userId}, update_time = NOW() WHERE id in
        <foreach item="item" index="index" collection="ids.split(',')" open="(" separator="," close=")">
            #{item}
        </foreach>
    </update>

    <update id="recoverFileByIds">
        update file_info set file_is_hide = '0', update_by_id = #{userId}, update_time = NOW() WHERE file_is_hide = '1'
        AND id in
        <foreach item="item" index="index" collection="ids.split(',')" open="(" separator="," close=")">
            #{item}
        </foreach>
    </update>

    <update id="deleteFileByIds">
        update file_info set is_deleted = '1', update_by_id = #{userId}, update_time = NOW() WHERE id in
        <foreach item="item" index="index" collection="ids.split(',')" open="(" separator="," close=")">
            #{item}
        </foreach>
    </update>

    <update id="updateFileInfo">
        update file_info set file_name = #{fileName}, update_by_id = #{userId}, update_time = NOW() WHERE id = #{fileId}
    </update>

    <select id="selectAncestorsListById" parameterType="java.lang.Long" resultType="com.vctgo.system.api.domain.dto.FileInfoAncestorsDto">
        select
        id as fileId,
        file_name as fileName
        from file_info
        where file_is_hide='0' AND is_deleted='0'
        AND id in
        <foreach item="id" index="index" collection="ancestors.split(',')" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>
</mapper>
