package jnpf.dmsMapper;

import jnpf.dmsEntity.permission.entity.DmsFileVersionEntity;
import jnpf.dmsEntity.permission.form.DmsPersonalFolderFileForm;
import jnpf.dmsEntity.permission.vo.DmsPersonalFolderFileVO;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.UUID;

@Mapper
public interface DmsPersonalFolderMapper {

    @Select({"<script>",
            "SELECT v.id, v.tenant_id, v.document_id, v.version_no, v.version_label, v.file_name,",
            "v.mime_type, v.size_bytes, v.checksum_sha256, v.prepared_by, v.prepared_on, v.shifou_shoukong,",
            "v.created_by, v.created_at FROM public.dms_file_versions v",
            "JOIN public.dms_version_states s ON s.tenant_id = v.tenant_id",
            "AND s.document_id = v.document_id AND s.file_version_id = v.id",
            "WHERE v.tenant_id = #{tenantId} AND v.id IN",
            "<foreach collection='fileVersionIds' item='id' open='(' separator=',' close=')'>#{id}</foreach>",
            "AND (s.lifecycle_status IN ('EFFECTIVE', 'SUPERSEDED', 'EXPIRED', 'REVOKED', 'OBSOLETE')",
            "OR (s.lifecycle_status = 'INVALIDATED' AND s.approved_at IS NOT NULL))",
            "</script>"})
    List<DmsFileVersionEntity> selectReadableVersions(
            @Param("tenantId") String tenantId,
            @Param("fileVersionIds") List<UUID> fileVersionIds);

    @Insert({"<script>",
            "INSERT INTO public.dms_personal_folder_items",
            "(tenant_id, user_id, folder_type, document_id, file_version_id, valid_to)",
            "VALUES",
            "<foreach collection='files' item='file' separator=','>",
            "(#{tenantId}, #{userId}, 'BORROW', #{file.documentId}, #{file.fileVersionId}, #{file.validTo})",
            "</foreach>",
            "ON CONFLICT (tenant_id, user_id, folder_type, file_version_id) DO UPDATE",
            "SET valid_to = EXCLUDED.valid_to",
            "WHERE (EXCLUDED.valid_to IS NULL AND dms_personal_folder_items.valid_to IS NOT NULL)",
            "OR dms_personal_folder_items.valid_to = 'infinity'::TIMESTAMPTZ",
            "OR (EXCLUDED.valid_to IS NOT NULL",
            "AND dms_personal_folder_items.valid_to &lt; EXCLUDED.valid_to)",
            "</script>"})
    int insertBorrowSnapshots(
            @Param("tenantId") String tenantId,
            @Param("userId") String userId,
            @Param("files") List<DmsPersonalFolderFileForm.FileRelation> files);

    @Insert({"<script>",
            "INSERT INTO public.dms_personal_folder_items",
            "(tenant_id, user_id, folder_type, document_id, file_version_id, valid_to)",
            "VALUES",
            "<foreach collection='files' item='file' separator=','>",
            "(#{tenantId}, #{userId}, 'DOWNLOAD', #{file.documentId}, #{file.fileVersionId}, #{file.validTo})",
            "</foreach>",
            "ON CONFLICT (tenant_id, user_id, folder_type, file_version_id) DO UPDATE",
            "SET valid_to = EXCLUDED.valid_to",
            "WHERE (EXCLUDED.valid_to IS NULL AND dms_personal_folder_items.valid_to IS NOT NULL)",
            "OR dms_personal_folder_items.valid_to = 'infinity'::TIMESTAMPTZ",
            "OR (EXCLUDED.valid_to IS NOT NULL",
            "AND dms_personal_folder_items.valid_to &lt; EXCLUDED.valid_to)",
            "</script>"})
    int insertDownloadSnapshots(
            @Param("tenantId") String tenantId,
            @Param("userId") String userId,
            @Param("files") List<DmsPersonalFolderFileForm.FileRelation> files);

    @Insert("INSERT INTO public.dms_personal_folder_items "
            + "(tenant_id, user_id, folder_type, document_id, file_version_id, valid_to) "
            + "VALUES (#{tenantId}, #{userId}, 'FAVORITE', #{documentId}, #{fileVersionId}, NULL) "
            + "ON CONFLICT (tenant_id, user_id, folder_type, file_version_id) DO NOTHING")
    int insertFavoriteSnapshot(@Param("tenantId") String tenantId,
                               @Param("userId") String userId,
                               @Param("documentId") UUID documentId,
                               @Param("fileVersionId") UUID fileVersionId);

    @Select({"<script>", DmsPermissionResourceMapper.RESOURCE_VIEW_SELECT_SQL,
            ", personal_item.valid_to AS valid_to",
            DmsPermissionResourceMapper.RESOURCE_VIEW_FROM_SQL,
            "JOIN public.dms_personal_folder_items personal_item",
            "ON personal_item.tenant_id = r.tenant_id AND personal_item.file_version_id = r.id",
            "WHERE personal_item.tenant_id = #{tenantId}",
            "AND personal_item.user_id = #{userId}",
            "AND personal_item.folder_type = #{folderType}",
            "AND r.resource_type = 'FILE_VERSION'",
            "<if test='keyword != null and keyword != \"\"'>",
            "AND (STRPOS(LOWER(COALESCE(v.file_name, '')), LOWER(#{keyword})) &gt; 0",
            "OR STRPOS(LOWER(COALESCE(version_document.name, '')), LOWER(#{keyword})) &gt; 0",
            "OR STRPOS(LOWER(COALESCE(version_document.document_no, '')), LOWER(#{keyword})) &gt; 0)",
            "</if>",
            "ORDER BY personal_item.added_at DESC, r.id DESC LIMIT #{limit}",
            "</script>"})
    List<DmsPersonalFolderFileVO> selectFiles(@Param("tenantId") String tenantId,
                                               @Param("userId") String userId,
                                               @Param("folderType") String folderType,
                                               @Param("keyword") String keyword,
                                               @Param("limit") int limit);
}