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

    <!-- 结果映射 -->
    <resultMap id="BaseResultMap" type="org.xyzh.common.dto.system.TbSysFile">
        <id column="id" property="ID" />
        <result column="file_id" property="fileID" />
        <result column="file_name" property="fileName" />
        <result column="original_name" property="originalName" />
        <result column="file_path" property="filePath" />
        <result column="file_url" property="fileUrl" />
        <result column="file_size" property="fileSize" />
        <result column="file_type" property="fileType" />
        <result column="mime_type" property="mimeType" />
        <result column="storage_type" property="storageType" />
        <result column="module" property="module" />
        <result column="business_id" property="businessID" />
        <result column="uploader" property="uploader" />
        <result column="create_time" property="createTime" />
        <result column="update_time" property="updateTime" />
        <result column="delete_time" property="deleteTime" />
        <result column="deleted" property="deleted" />
    </resultMap>

    <!-- 基础列 -->
    <sql id="Base_Column_List">
        id, file_id, file_name, original_name, file_path, file_url, file_size, 
        file_type, mime_type, storage_type, module, business_id, uploader,
        create_time, update_time, delete_time, deleted
    </sql>

    <!-- 插入文件记录 -->
    <insert id="insertFile">
        INSERT INTO tb_sys_file (
            id, file_id, file_name, original_name, file_path, file_url, file_size,
            file_type, mime_type, storage_type, module, business_id, uploader,
            create_time, update_time, deleted
        ) VALUES (
            #{file.ID}, #{file.fileID}, #{file.fileName}, #{file.originalName}, #{file.filePath}, #{file.fileUrl}, #{file.fileSize},
            #{file.fileType}, #{file.mimeType}, #{file.storageType}, #{file.module}, #{file.businessID}, #{file.uploader},
            #{file.createTime}, #{file.updateTime}, #{file.deleted}
        )
    </insert>

    <!-- 根据文件ID查询文件信息 -->
    <select id="selectFileById" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_sys_file
        WHERE id = #{fileId}
        AND deleted = 0
    </select>

    <!-- 根据文件ID查询文件信息（包括已删除） -->
    <select id="selectFileByIdIncludeDeleted" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_sys_file
        WHERE id = #{fileId}
    </select>

    <!-- 根据业务ID查询文件列表 -->
    <select id="selectFilesByBusinessId" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_sys_file
        WHERE module = #{module}
        AND business_id = #{businessId}
        AND deleted = 0
        ORDER BY create_time DESC
    </select>

    <!-- 根据上传者查询文件列表 -->
    <select id="selectFilesByUploader" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_sys_file
        WHERE uploader = #{uploader}
        AND deleted = 0
        ORDER BY create_time DESC
    </select>

    <!-- 逻辑删除文件 -->
    <update id="logicDeleteFileById">
        UPDATE tb_sys_file
        SET deleted = 1,
            delete_time = NOW()
        WHERE id = #{fileId}
        AND deleted = 0
    </update>

    <!-- 物理删除文件 -->
    <delete id="deleteFileById">
        DELETE FROM tb_sys_file
        WHERE id = #{fileId}
    </delete>

    <!-- 批量逻辑删除文件 -->
    <update id="batchLogicDeleteFiles">
        UPDATE tb_sys_file
        SET deleted = 1,
            delete_time = NOW()
        WHERE id IN
        <foreach collection="fileIds" item="fileId" open="(" separator="," close=")">
            #{fileId}
        </foreach>
        AND deleted = 0
    </update>

</mapper>
