package jnpf.dmsMapper;

import jnpf.dmsEntity.permission.vo.DmsFileCenterItemVO;
import jnpf.dmsPermission.DmsFileCenterQuery;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface DmsFileCenterMapper {
    String ACTIVE_READ = "p.tenant_id = #{q.tenantId} AND p.action_code = 'READ' "
            + "AND p.revoked_at IS NULL AND p.valid_from <= CURRENT_TIMESTAMP "
            + "AND (p.valid_to IS NULL OR CURRENT_TIMESTAMP < p.valid_to) "
            + "AND (p.max_use_count IS NULL OR p.used_count < p.max_use_count) ";

    String PRINCIPAL_MATCH = "AND ((p.principal_type = 'USER' AND p.principal_id = #{q.userId}) "
            + "<if test='q.directOrgIds != null and !q.directOrgIds.isEmpty()'> "
            + "OR (p.principal_type = 'ORG' AND p.include_child_orgs = FALSE AND p.principal_id IN "
            + "<foreach collection='q.directOrgIds' item='id' open='(' separator=',' close=')'>#{id}</foreach>) "
            + "</if> "
            + "<if test='q.ancestorOrgIds != null and !q.ancestorOrgIds.isEmpty()'> "
            + "OR (p.principal_type = 'ORG' AND p.include_child_orgs = TRUE AND p.principal_id IN "
            + "<foreach collection='q.ancestorOrgIds' item='id' open='(' separator=',' close=')'>#{id}</foreach>) "
            + "</if> "
            + "<if test='q.positionIds != null and !q.positionIds.isEmpty()'> "
            + "OR (p.principal_type = 'POSITION' AND p.principal_id IN "
            + "<foreach collection='q.positionIds' item='id' open='(' separator=',' close=')'>#{id}</foreach>) "
            + "</if> "
            + "<if test='q.roleIds != null and !q.roleIds.isEmpty()'> "
            + "OR (p.principal_type = 'ROLE' AND p.principal_id IN "
            + "<foreach collection='q.roleIds' item='id' open='(' separator=',' close=')'>#{id}</foreach>) "
            + "</if> "
            + "<if test='q.permissionGroupIds != null and !q.permissionGroupIds.isEmpty()'> "
            + "OR (p.principal_type = 'GROUP' AND p.principal_id IN "
            + "<foreach collection='q.permissionGroupIds' item='id' open='(' separator=',' close=')'>#{id}</foreach>) "
            + "</if>) ";

    String FOLDER_ANCESTORS = "WITH RECURSIVE folder_ancestors(id, parent_id) AS ( "
            + "SELECT f.id, f.parent_id FROM public.dms_folders f "
            + "WHERE f.tenant_id = #{q.tenantId} AND f.id = #{q.folderId} "
            + "UNION ALL SELECT parent.id, parent.parent_id FROM folder_ancestors child "
            + "JOIN public.dms_folders parent ON parent.tenant_id = #{q.tenantId} "
            + "AND parent.id = child.parent_id) ";

    String VERSION_COLUMNS = "v.id, 'FILE_VERSION' AS resource_type, d.folder_id AS parent_id, "
            + "d.name, d.id AS document_id, d.document_no, d.document_type, d.responsible_dept_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, s.lifecycle_status, "
            + "s.effective_from, s.effective_to, v.created_at ";

    String ACTIVE_EXCLUSION = "e.tenant_id = #{q.tenantId} AND e.user_id = #{q.userId} "
            + "AND e.action_code = 'READ' AND e.revoked_at IS NULL "
            + "AND e.valid_from &lt;= CURRENT_TIMESTAMP "
            + "AND (e.valid_to IS NULL OR CURRENT_TIMESTAMP &lt; e.valid_to) ";

    String SHARED_ELIGIBLE_VERSIONS = "WITH eligible_versions(file_version_id) AS ( "
            + "<choose><when test='q.administrator'> "
            + "SELECT s.file_version_id FROM public.dms_version_states s "
            + "WHERE s.tenant_id = #{q.tenantId} "
            + "</when><otherwise> "
            + "SELECT s.file_version_id FROM public.dms_file_permissions p "
            + "JOIN public.dms_version_states s ON s.tenant_id = p.tenant_id "
            + "AND s.document_id = p.resource_id WHERE " + ACTIVE_READ + PRINCIPAL_MATCH
            + "AND p.resource_type = 'DOCUMENT' "
            + "UNION SELECT p.resource_id FROM public.dms_file_permissions p WHERE "
            + ACTIVE_READ + PRINCIPAL_MATCH + "AND p.resource_type = 'FILE_VERSION' "
            + "</otherwise></choose>) ";

    @Select({"<script>",
            "WITH RECURSIVE parent_ancestors(id, parent_id) AS (",
            "<choose>",
            "<when test='q.parentId != null'>",
            "SELECT f.id, f.parent_id FROM public.dms_folders f",
            "WHERE f.tenant_id = #{q.tenantId} AND f.id = #{q.parentId}",
            "</when>",
            "<otherwise>SELECT NULL::UUID, NULL::UUID WHERE FALSE</otherwise>",
            "</choose>",
            "UNION ALL SELECT parent.id, parent.parent_id FROM parent_ancestors child",
            "JOIN public.dms_folders parent ON parent.tenant_id = #{q.tenantId}",
            "AND parent.id = child.parent_id)",
            "<if test='!q.administrator'>",
            ", authorized_navigation_ancestors(id, parent_id) AS (",
            "SELECT granted.id, granted.parent_id FROM public.dms_file_permissions p",
            "JOIN public.dms_folders granted ON granted.tenant_id = p.tenant_id",
            "AND granted.id = p.resource_id WHERE", ACTIVE_READ, PRINCIPAL_MATCH,
            "AND p.resource_type = 'FOLDER'",
            "UNION SELECT parent.id, parent.parent_id FROM authorized_navigation_ancestors child",
            "JOIN public.dms_folders parent ON parent.tenant_id = #{q.tenantId}",
            "AND parent.id = child.parent_id)",
            "</if>",
            "SELECT f.id, 'FOLDER' AS resource_type, f.parent_id, f.name, f.created_at",
            "FROM public.dms_folders f",
            "WHERE f.tenant_id = #{q.tenantId}",
            "<choose>",
            "<when test='q.parentId == null'>AND f.parent_id IS NULL</when>",
            "<otherwise>AND f.parent_id = #{q.parentId}</otherwise>",
            "</choose>",
            "<if test='q.cursorName != null'>",
            "AND (f.name &gt; #{q.cursorName} OR (f.name = #{q.cursorName} AND f.id &gt; #{q.cursorId}))",
            "</if>",
            "<if test='!q.administrator'>",
            "AND (EXISTS (SELECT 1 FROM public.dms_file_permissions p WHERE", ACTIVE_READ, PRINCIPAL_MATCH,
            "AND p.resource_type = 'FOLDER' AND (p.resource_id = f.id OR",
            "(p.resource_scope = 'SUBTREE' AND p.resource_id IN (SELECT id FROM parent_ancestors))))",
            "OR f.id IN (SELECT id FROM authorized_navigation_ancestors))",
            "</if>",
            "ORDER BY f.name, f.id LIMIT #{q.limit}",
            "</script>"})
    List<DmsFileCenterItemVO> selectFolders(@Param("q") DmsFileCenterQuery query);

    @Select({"<script>", FOLDER_ANCESTORS,
            "SELECT", VERSION_COLUMNS,
            "FROM public.dms_documents d",
            "JOIN public.dms_version_states s ON s.tenant_id = d.tenant_id AND s.document_id = d.id",
            "JOIN public.dms_file_versions v ON v.tenant_id = s.tenant_id",
            "AND v.document_id = s.document_id AND v.id = s.file_version_id",
            "WHERE d.tenant_id = #{q.tenantId} AND d.folder_id = #{q.folderId}",
            "AND s.lifecycle_status = 'EFFECTIVE' AND s.effective_from &lt;= CURRENT_TIMESTAMP",
            "AND (s.effective_to IS NULL OR CURRENT_TIMESTAMP &lt; s.effective_to)",
            "<if test='q.keyword != null'>AND d.name LIKE CONCAT(#{q.keyword}, '%')</if>",
            "<if test='q.cursorName != null'>",
            "AND (d.name &gt; #{q.cursorName} OR",
            "(d.name = #{q.cursorName} AND d.id &gt; #{q.cursorDocumentId}))",
            "</if>",
            "<if test='!q.administrator'>",
            "AND NOT EXISTS (SELECT 1 FROM public.dms_access_exclusions e WHERE", ACTIVE_EXCLUSION,
            "AND ((e.resource_type = 'DOCUMENT' AND e.resource_id = d.id)",
            "OR (e.resource_type = 'FILE_VERSION' AND e.resource_id = v.id)))",
            "AND EXISTS (SELECT 1 FROM public.dms_file_permissions p WHERE", ACTIVE_READ, PRINCIPAL_MATCH,
            "AND p.resource_type = 'FOLDER' AND (p.resource_id = #{q.folderId}",
            "OR (p.resource_scope = 'SUBTREE' AND p.resource_id IN (SELECT id FROM folder_ancestors))))",
            "</if>",
            "ORDER BY d.name, d.id LIMIT #{q.limit}",
            "</script>"})
    List<DmsFileCenterItemVO> selectCurrentDocuments(@Param("q") DmsFileCenterQuery query);

    @Select({"<script>", FOLDER_ANCESTORS,
            "SELECT", VERSION_COLUMNS,
            "FROM public.dms_documents d",
            "JOIN public.dms_file_versions v ON v.tenant_id = d.tenant_id AND v.document_id = d.id",
            "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 d.tenant_id = #{q.tenantId} AND d.folder_id = #{q.folderId}",
            "AND s.lifecycle_status IN ('SUPERSEDED', 'EXPIRED', 'REVOKED', 'INVALIDATED', 'OBSOLETE')",
            "<if test='q.keyword != null'>AND d.name LIKE CONCAT(#{q.keyword}, '%')</if>",
            "<if test='q.cursorName != null'>",
            "AND (d.name &gt; #{q.cursorName}",
            "OR (d.name = #{q.cursorName} AND d.id &gt; #{q.cursorDocumentId})",
            "OR (d.name = #{q.cursorName} AND d.id = #{q.cursorDocumentId}",
            "AND v.version_no &lt; #{q.cursorVersionNo})",
            "OR (d.name = #{q.cursorName} AND d.id = #{q.cursorDocumentId}",
            "AND v.version_no = #{q.cursorVersionNo} AND v.id &lt; #{q.cursorId}))",
            "</if>",
            "<if test='!q.administrator'>",
            "AND NOT EXISTS (SELECT 1 FROM public.dms_access_exclusions e WHERE", ACTIVE_EXCLUSION,
            "AND ((e.resource_type = 'DOCUMENT' AND e.resource_id = d.id)",
            "OR (e.resource_type = 'FILE_VERSION' AND e.resource_id = v.id)))",
            "AND EXISTS (SELECT 1 FROM public.dms_file_permissions p WHERE", ACTIVE_READ, PRINCIPAL_MATCH,
            "AND p.resource_type = 'FOLDER' AND (p.resource_id = #{q.folderId}",
            "OR (p.resource_scope = 'SUBTREE' AND p.resource_id IN (SELECT id FROM folder_ancestors))))",
            "</if>",
            "ORDER BY d.name, d.id, v.version_no DESC, v.id DESC LIMIT #{q.limit}",
            "</script>"})
    List<DmsFileCenterItemVO> selectHistoricalVersions(@Param("q") DmsFileCenterQuery query);

    @Select({"<script>",
            "WITH RECURSIVE folder_ancestors(id, parent_id) AS (",
            "SELECT f.id, f.parent_id FROM public.dms_folders f",
            "JOIN public.dms_documents start_document ON start_document.tenant_id = f.tenant_id",
            "AND start_document.folder_id = f.id",
            "WHERE f.tenant_id = #{q.tenantId} AND start_document.id = #{q.documentId}",
            "UNION ALL SELECT parent.id, parent.parent_id FROM folder_ancestors child",
            "JOIN public.dms_folders parent ON parent.tenant_id = #{q.tenantId}",
            "AND parent.id = child.parent_id)",
            "SELECT", VERSION_COLUMNS,
            "FROM public.dms_documents d",
            "JOIN public.dms_file_versions v ON v.tenant_id = d.tenant_id AND v.document_id = d.id",
            "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 d.tenant_id = #{q.tenantId} AND d.id = #{q.documentId}",
            "AND s.lifecycle_status IN ('SUPERSEDED', 'EXPIRED', 'REVOKED', 'INVALIDATED', 'OBSOLETE')",
            "<if test='q.cursorVersionNo != null'>",
            "AND (v.version_no &lt; #{q.cursorVersionNo}",
            "OR (v.version_no = #{q.cursorVersionNo} AND v.id &lt; #{q.cursorId}))",
            "</if>",
            "<if test='!q.administrator'>",
            "AND NOT EXISTS (SELECT 1 FROM public.dms_access_exclusions e WHERE", ACTIVE_EXCLUSION,
            "AND ((e.resource_type = 'DOCUMENT' AND e.resource_id = d.id)",
            "OR (e.resource_type = 'FILE_VERSION' AND e.resource_id = v.id)))",
            "AND EXISTS (SELECT 1 FROM public.dms_file_permissions p WHERE", ACTIVE_READ, PRINCIPAL_MATCH,
            "AND ((p.resource_type = 'DOCUMENT' AND p.resource_id = d.id)",
            "OR (p.resource_type = 'FILE_VERSION' AND p.resource_id = v.id)",
            "OR (p.resource_type = 'FOLDER' AND (p.resource_id = d.folder_id",
            "OR (p.resource_scope = 'SUBTREE' AND p.resource_id IN (SELECT id FROM folder_ancestors))))))",
            "</if>",
            "ORDER BY v.version_no DESC, v.id DESC LIMIT #{q.limit}",
            "</script>"})
    List<DmsFileCenterItemVO> selectDocumentVersions(@Param("q") DmsFileCenterQuery query);

    @Select({"<script>", SHARED_ELIGIBLE_VERSIONS,
            "SELECT", VERSION_COLUMNS,
            "FROM public.dms_documents d",
            "JOIN public.dms_version_states s ON s.tenant_id = d.tenant_id AND s.document_id = d.id",
            "JOIN public.dms_file_versions v ON v.tenant_id = s.tenant_id",
            "AND v.document_id = s.document_id AND v.id = s.file_version_id",
            "JOIN eligible_versions allowed ON allowed.file_version_id = v.id",
            "WHERE d.tenant_id = #{q.tenantId}",
            "AND s.lifecycle_status = 'EFFECTIVE' AND s.effective_from &lt;= CURRENT_TIMESTAMP",
            "AND (s.effective_to IS NULL OR CURRENT_TIMESTAMP &lt; s.effective_to)",
            "<if test='q.cursorName != null'>",
            "AND (d.name &gt; #{q.cursorName} OR",
            "(d.name = #{q.cursorName} AND d.id &gt; #{q.cursorDocumentId}))",
            "</if>",
            "<if test='!q.administrator'>",
            "AND NOT EXISTS (SELECT 1 FROM public.dms_access_exclusions e WHERE", ACTIVE_EXCLUSION,
            "AND ((e.resource_type = 'DOCUMENT' AND e.resource_id = d.id)",
            "OR (e.resource_type = 'FILE_VERSION' AND e.resource_id = v.id)))",
            "</if>",
            "ORDER BY d.name, d.id LIMIT #{q.limit}",
            "</script>"})
    List<DmsFileCenterItemVO> selectSharedDocuments(@Param("q") DmsFileCenterQuery query);

    @Select({"<script>", SHARED_ELIGIBLE_VERSIONS,
            "SELECT", VERSION_COLUMNS,
            "FROM public.dms_documents d",
            "JOIN public.dms_file_versions v ON v.tenant_id = d.tenant_id AND v.document_id = d.id",
            "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",
            "JOIN eligible_versions allowed ON allowed.file_version_id = v.id",
            "WHERE d.tenant_id = #{q.tenantId}",
            "AND s.lifecycle_status IN ('SUPERSEDED', 'EXPIRED', 'REVOKED', 'INVALIDATED', 'OBSOLETE')",
            "<if test='q.cursorName != null'>",
            "AND (d.name &gt; #{q.cursorName}",
            "OR (d.name = #{q.cursorName} AND d.id &gt; #{q.cursorDocumentId})",
            "OR (d.name = #{q.cursorName} AND d.id = #{q.cursorDocumentId}",
            "AND v.version_no &lt; #{q.cursorVersionNo})",
            "OR (d.name = #{q.cursorName} AND d.id = #{q.cursorDocumentId}",
            "AND v.version_no = #{q.cursorVersionNo} AND v.id &lt; #{q.cursorId}))",
            "</if>",
            "<if test='!q.administrator'>",
            "AND NOT EXISTS (SELECT 1 FROM public.dms_access_exclusions e WHERE", ACTIVE_EXCLUSION,
            "AND ((e.resource_type = 'DOCUMENT' AND e.resource_id = d.id)",
            "OR (e.resource_type = 'FILE_VERSION' AND e.resource_id = v.id)))",
            "</if>",
            "ORDER BY d.name, d.id, v.version_no DESC, v.id DESC LIMIT #{q.limit}",
            "</script>"})
    List<DmsFileCenterItemVO> selectSharedHistoricalVersions(@Param("q") DmsFileCenterQuery query);
}