package jnpf.dmsMapper;

import jnpf.dmsDatabase.DmsUuidTypeHandler;
import jnpf.dmsPermission.DmsFileCenterQuery;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.session.Configuration;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;

import java.util.Arrays;
import java.util.UUID;

import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertFalse;
import static org.junit.jupiter.api.Assertions.assertTrue;

class DmsFileCenterMapperSqlTest {
    private Configuration configuration;

    @BeforeEach
    void setUp() {
        configuration = new Configuration();
        configuration.getTypeHandlerRegistry().register(UUID.class, DmsUuidTypeHandler.class);
        try (java.io.InputStream stream = getClass().getResourceAsStream("/mapper/DmsFileCenterMapper.xml")) {
            new org.apache.ibatis.builder.xml.XMLMapperBuilder(stream, configuration,
                    "mapper/DmsFileCenterMapper.xml", configuration.getSqlFragments()).parse();
        } catch (java.io.IOException e) { throw new java.io.UncheckedIOException(e); }
    }

    @Test
    void immediateChildrenBranchIsLimitedToApprovalReadGrantsInListingsAndNavigation() {
        var q = query(false); q.setImmediateParentOrgIds(java.util.List.of("parent"));
        for (String method : java.util.List.of("selectFolders", "selectCurrentDocuments", "selectSharedDocuments", "selectHistoricalVersions")) {
            String sql = sql(method, q);
            assertTrue(sql.contains("p.approved_org_scope = 'CHILDREN'"));
            assertTrue(sql.contains("p.granted_by_id = 'dms-approved-read'"));
            assertTrue(sql.contains("p.source_type = 'WORKFLOW'"));
        }
    }

    @Test
    void ordinaryFolderPageFiltersPermissionsBeforeStableLimit() {
        String sql = sql("selectFolders", query(false));

        assertTrue(sql.contains("FROM public.dms_file_permissions p"));
        assertTrue(sql.contains("authorized_navigation_seeds"));
        assertTrue(sql.contains("authorized_navigation_ancestors"));
        assertTrue(sql.contains("SELECT granted.id, granted.parent_id"));
        assertTrue(sql.contains("SELECT document_folder.id, document_folder.parent_id"));
        assertTrue(sql.contains("SELECT version_folder.id, version_folder.parent_id"));
        assertTrue(sql.contains("p.resource_type = 'DOCUMENT'"));
        assertTrue(sql.contains("p.resource_type = 'FILE_VERSION'"));
        assertTrue(sql.contains("dms_access_exclusions"));
        assertTrue(sql.contains("UNION SELECT parent.id, parent.parent_id FROM authorized_navigation_ancestors child"));
        assertTrue(sql.contains("OR f.id IN (SELECT id FROM authorized_navigation_ancestors)"));
        assertTrue(sql.contains("p.principal_type = 'USER'"));
        assertTrue(sql.contains("p.principal_type = 'ORG'"));
        assertTrue(sql.contains("p.principal_type = 'POSITION'"));
        assertTrue(sql.contains("p.principal_type = 'ROLE'"));
        assertTrue(sql.contains("p.principal_type = 'GROUP'"));
        assertTrue(sql.contains("p.resource_scope = 'SUBTREE'"));
        assertTrue(sql.contains("f.f_delete_mark IS NULL"));
        assertTrue(sql.contains("ORDER BY f.sort_code, f.id LIMIT ?"));
        assertTrue(sql.indexOf("dms_file_permissions") < sql.indexOf("LIMIT ?"));
        assertFalse(sql.contains("SELECT *"));
        assertFalse(sql.contains("AS has_children"));
    }

    @Test
    void treeExpansionUsesVisibleChildrenIncludingNavigationAndInheritedRead() {
        DmsFileCenterQuery query = query(false);
        query.setIncludeFolderChildren(true);
        String sql = sql("selectFolders", query);
        String expansion = sql.substring(sql.indexOf(", EXISTS (SELECT 1 FROM public.dms_folders child_folder"),
                sql.indexOf(") AS has_children"));
        assertTrue(expansion.contains("child_folder.tenant_id = ?"));
        assertTrue(expansion.contains("child_folder.parent_id = f.id"));
        assertTrue(expansion.contains("child_folder.f_delete_mark IS NULL"));
        assertTrue(expansion.contains("child_folder.id IN (SELECT id FROM authorized_navigation_ancestors)"));
        assertTrue(expansion.contains("p.resource_id = child_folder.id"));
        assertTrue(expansion.contains("p.resource_scope = 'SUBTREE' AND (p.resource_id = f.id"));
        assertTrue(expansion.contains("p.resource_id IN (SELECT id FROM parent_ancestors)"));
        assertTrue(expansion.contains("p.action_code = 'READ'"));
        assertTrue(expansion.contains("p.revoked_at IS NULL"));
        assertTrue(expansion.contains("p.valid_from <= CURRENT_TIMESTAMP"));
        assertTrue(expansion.contains("CURRENT_TIMESTAMP < p.valid_to"));
        assertTrue(expansion.contains("p.used_count < p.max_use_count"));
        for (String principal : Arrays.asList("USER", "ORG", "POSITION", "ROLE", "GROUP")) {
            assertTrue(expansion.contains("p.principal_type = '" + principal + "'"));
        }
    }

    @Test
    void administratorTreeExpansionStillFiltersTenantAndDeletedChildren() {
        DmsFileCenterQuery query = query(true);
        query.setIncludeFolderChildren(true);
        String sql = sql("selectFolders", query);
        assertTrue(sql.contains("child_folder.tenant_id = ?"));
        assertTrue(sql.contains("child_folder.f_delete_mark IS NULL"));
        assertTrue(sql.contains(") AS has_children"));
        assertFalse(sql.contains("dms_file_permissions"));
        assertFalse(sql.contains("authorized_navigation_ancestors"));
    }

    @Test
    void administratorFolderPageDoesNotRequireAllowRows() {
        String sql = sql("selectFolders", query(true));

        assertFalse(sql.contains("dms_file_permissions"));
        assertFalse(sql.contains("authorized_navigation_seeds"));
        assertFalse(sql.contains("authorized_navigation_ancestors"));
        assertTrue(sql.contains("f.f_delete_mark IS NULL"));
        assertTrue(sql.contains("ORDER BY f.sort_code, f.id LIMIT ?"));
    }

    @Test
    void folderCursorUsesSortCodeAndIdBoundary() {
        DmsFileCenterQuery query = query(true);
        query.setCursorSortCode(2048L);
        query.setCursorId(UUID.fromString("018f0000-0000-7000-8000-000000000603"));

        String sql = sql("selectFolders", query);

        assertTrue(sql.contains("f.sort_code > ?"));
        assertTrue(sql.contains("f.sort_code = ? AND f.id > ?"));
        assertFalse(sql.contains("f.name > ?"));
    }

    @Test
    void currentDocumentPageUsesEffectiveWindowResourceReadAndExclusionPriority() {
        String sql = sql("selectCurrentDocuments", query(false));

        assertTrue(sql.contains("d.id, 'DOCUMENT' AS resource_type"));
        assertTrue(sql.contains("d.main_document_id IS NULL"));
        assertTrue(sql.contains("member.id=d.id OR member.main_document_id=d.id"));
        assertTrue(sql.contains("s.lifecycle_status = 'EFFECTIVE'"));
        assertTrue(sql.contains("s.effective_from <= CURRENT_TIMESTAMP"));
        assertTrue(sql.contains("CURRENT_TIMESTAMP < s.effective_to"));
        assertTrue(sql.contains("NOT EXISTS (SELECT 1 FROM public.dms_access_exclusions e"));
        assertTrue(sql.contains("p.resource_type = 'FOLDER'"));
        assertTrue(sql.contains("p.resource_type = 'DOCUMENT'"));
        assertTrue(sql.contains("p.resource_type = 'FILE_VERSION'"));
        assertTrue(sql.contains("ORDER BY COALESCE((SELECT initial_version.file_name"));
    }

    @Test
    void currentFileSearchUsesUnifiedFieldsAcrossFolderSubtree() {
        DmsFileCenterQuery query = query(false);
        query.setKeyword("Report");
        query.setCursorName("Document A");
        query.setCursorDocumentId(UUID.fromString("018f0000-0000-7000-8000-000000000701"));
        query.setCursorId(UUID.fromString("018f0000-0000-7000-8000-000000000702"));

        String sql = sql("selectCurrentFiles", query);

        assertTrue(sql.contains("SELECT v.id, 'FILE_VERSION' AS resource_type"));
        assertTrue(sql.contains("s.lifecycle_status = 'EFFECTIVE'"));
        assertTrue(sql.contains("s.review_cycle_months"));
        assertTrue(sql.contains("s.last_reviewed_on"));
        assertTrue(sql.contains("s.next_review_on"));
        assertTrue(sql.contains("WITH RECURSIVE folder_descendants"));
        assertTrue(sql.contains("child.parent_id = parent.id"));
        assertTrue(sql.contains("d.folder_id IN (SELECT id FROM folder_descendants)"));
        assertTrue(sql.contains("STRPOS(LOWER(COALESCE(v.file_name, '')), LOWER(?)) > 0"));
        assertTrue(sql.contains("STRPOS(LOWER(COALESCE(d.name, '')), LOWER(?)) > 0"));
        assertTrue(sql.contains("STRPOS(LOWER(COALESCE(d.document_no, '')), LOWER(?)) > 0"));
        assertFalse(sql.contains("STARTS_WITH"));
        assertTrue(sql.contains("d.id = ? AND v.id > ?"));
        assertTrue(sql.contains("dms_access_exclusions"));
        assertTrue(sql.contains("dms_file_permissions"));
        assertTrue(sql.contains("SELECT ancestor_id FROM search_folder_ancestors WHERE folder_id = d.folder_id"));
        assertTrue(sql.contains("ORDER BY d.name, d.id, v.id LIMIT ?"));
        assertBalancedParentheses(sql);
    }

    @Test
    void fileNameExistenceUsesDirectFolderExactNameAndCurrentVersions() {
        String sql = sql("fileNameExists", query(false));

        assertTrue(sql.contains("d.folder_id = ?"));
        assertTrue(sql.contains("v.file_name = ?"));
        assertTrue(sql.contains("s.lifecycle_status = 'EFFECTIVE'"));
        assertTrue(sql.contains("d.access_status = 'enabled'"));
        assertTrue(sql.contains("d.tenant_id = ?"));
        assertFalse(sql.contains("dms_access_exclusions"));
        assertFalse(sql.contains("dms_file_permissions"));
        assertFalse(sql.contains("folder_descendants"));
        assertFalse(sql.contains("version_label ="));
        assertBalancedParentheses(sql);
    }

    @Test
    void administratorFileSearchKeepsBusinessScopeWithoutPermissionPredicates() {
        DmsFileCenterQuery query = query(true);
        query.setKeyword("玻思韬");

        String sql = sql("selectCurrentFiles", query);

        assertTrue(sql.contains("d.tenant_id = ?"));
        assertTrue(sql.contains("d.folder_id IN (SELECT id FROM folder_descendants)"));
        assertTrue(sql.contains("s.lifecycle_status = 'EFFECTIVE'"));
        assertTrue(sql.contains("STRPOS(LOWER(COALESCE(d.name, '')), LOWER(?)) > 0"));
        assertFalse(sql.contains("dms_file_permissions"));
        assertFalse(sql.contains("dms_access_exclusions"));
        assertBalancedParentheses(sql);
    }

    @Test
    void historicalPageReturnsOneDocumentContainerForVisibleTerminalVersions() {
        DmsFileCenterQuery query = query(false);
        query.setCursorName("A");
        query.setCursorDocumentId(UUID.fromString("018f0000-0000-7000-8000-000000000701"));

        String sql = sql("selectHistoricalVersions", query);

        assertTrue(sql.contains("d.id, 'DOCUMENT' AS resource_type"));
        assertTrue(sql.contains("d.main_document_id IS NULL"));
        assertTrue(sql.contains("member.id=d.id OR member.main_document_id=d.id"));
        assertTrue(sql.contains("AND EXISTS (SELECT 1 FROM public.dms_documents member"));
        assertHistoricalVisibility(sql);
        assertFalse(sql.contains("PENDING_EFFECTIVE"));
        assertFalse(sql.contains("v.version_no < ?"));
        assertFalse(sql.contains("v.id < ?"));
        assertTrue(sql.contains("p.resource_type = 'FOLDER'"));
        assertTrue(sql.contains("p.resource_type = 'DOCUMENT'"));
        assertTrue(sql.contains("p.resource_type = 'FILE_VERSION'"));
        assertTrue(sql.contains("ORDER BY COALESCE((SELECT initial_version.file_name"));
    }

    @Test
    void historicalFileSearchReturnsOnlyMatchingTerminalFileVersions() {
        DmsFileCenterQuery query = query(false);
        query.setKeyword("Archive");
        query.setCursorName("Document A");
        query.setCursorDocumentId(UUID.fromString("018f0000-0000-7000-8000-000000000701"));
        query.setCursorVersionNo(2L);
        query.setCursorId(UUID.fromString("018f0000-0000-7000-8000-000000000702"));

        String sql = sql("selectHistoricalFiles", query);

        assertTrue(sql.contains("SELECT v.id, 'FILE_VERSION' AS resource_type"));
        assertHistoricalVisibility(sql);
        assertFalse(sql.contains("PENDING_EFFECTIVE"));
        assertTrue(sql.contains("d.folder_id IN (SELECT id FROM folder_descendants)"));
        assertTrue(sql.contains("STRPOS(LOWER(COALESCE(v.file_name, '')), LOWER(?)) > 0"));
        assertTrue(sql.contains("STRPOS(LOWER(COALESCE(d.name, '')), LOWER(?)) > 0"));
        assertTrue(sql.contains("STRPOS(LOWER(COALESCE(d.document_no, '')), LOWER(?)) > 0"));
        assertTrue(sql.contains("v.version_no < ?"));
        assertTrue(sql.contains("dms_access_exclusions"));
        assertTrue(sql.contains("dms_file_permissions"));
        assertTrue(sql.contains("ORDER BY d.name, d.id, v.version_no DESC, v.id DESC LIMIT ?"));
        assertBalancedParentheses(sql);
    }

    @Test
    void currentDocumentDirectoryReturnsOnlyTheEffectiveVersion() {
        String sql = sql("selectCurrentDocumentVersions", query(false));

        assertTrue(sql.contains("'FILE_VERSION' AS resource_type"));
        assertTrue(sql.contains("d.id = ?"));
        assertTrue(sql.contains("s.lifecycle_status = 'EFFECTIVE'"));
        assertTrue(sql.contains("s.effective_from <= CURRENT_TIMESTAMP"));
        assertTrue(sql.contains("CURRENT_TIMESTAMP < s.effective_to"));
        assertTrue(sql.contains("p.resource_type = 'FOLDER'"));
        assertTrue(sql.contains("p.resource_type = 'DOCUMENT'"));
        assertTrue(sql.contains("p.resource_type = 'FILE_VERSION'"));
        assertTrue(sql.contains("ORDER BY v.version_no DESC, v.id DESC LIMIT ?"));
    }

    @Test
    void documentVersionSearchUsesTheSameContainsFields() {
        DmsFileCenterQuery query = query(true);
        query.setKeyword("玻思韬");

        String sql = sql("selectCurrentDocumentVersions", query);

        assertTrue(sql.contains("STRPOS(LOWER(COALESCE(v.file_name, '')), LOWER(?)) > 0"));
        assertTrue(sql.contains("STRPOS(LOWER(COALESCE(d.name, '')), LOWER(?)) > 0"));
        assertTrue(sql.contains("STRPOS(LOWER(COALESCE(d.document_no, '')), LOWER(?)) > 0"));
        assertFalse(sql.contains("LIKE CONCAT"));
        assertFalse(sql.contains("STARTS_WITH"));
    }

    @Test
    void historicalDocumentDirectoryIncludesElapsedVersionsBeforeWorkerRuns() {
        String sql = sql("selectDocumentVersions", query(false));

        assertTrue(sql.contains("'FILE_VERSION' AS resource_type"));
        assertHistoricalVisibility(sql);
        assertTrue(sql.contains("s.lifecycle_status = 'EFFECTIVE' AND s.effective_to <= CURRENT_TIMESTAMP"));
    }

    @Test
    void sharedPageUsesOnlyDirectDocumentOrVersionAllow() {
        String sql = sql("selectSharedHistoricalVersions", query(false));

        assertTrue(sql.contains("p.resource_type = 'DOCUMENT'"));
        assertTrue(sql.contains("p.resource_type = 'FILE_VERSION'"));
        assertFalse(sql.contains("p.resource_type = 'FOLDER'"));
        assertTrue(sql.contains("dms_access_exclusions"));
    }

    private void assertHistoricalVisibility(String sql) {
        assertTrue(sql.contains("IN ('SUPERSEDED', 'EXPIRED', 'REVOKED', 'OBSOLETE')"));
        assertTrue(sql.contains("s.lifecycle_status = 'INVALIDATED' AND s.approved_at IS NOT NULL"));
        assertFalse(sql.contains("PENDING_APPROVAL"));
    }

    private DmsFileCenterQuery query(boolean administrator) {
        DmsFileCenterQuery query = new DmsFileCenterQuery();
        query.setTenantId("tenant-1");
        query.setUserId("user-1");
        query.setAdministrator(administrator);
        query.setParentId(UUID.fromString("018f0000-0000-7000-8000-000000000601"));
        query.setFolderId(UUID.fromString("018f0000-0000-7000-8000-000000000601"));
        query.setDocumentId(UUID.fromString("018f0000-0000-7000-8000-000000000602"));
        query.setDirectOrgIds(Arrays.asList("org-1"));
        query.setAncestorOrgIds(Arrays.asList("org-1", "org-root"));
        query.setPositionIds(Arrays.asList("position-1"));
        query.setRoleIds(Arrays.asList("role-1"));
        query.setPermissionGroupIds(Arrays.asList("group-1"));
        query.setLimit(51);
        return query;
    }

    private String sql(String method, DmsFileCenterQuery query) {
        MappedStatement statement = configuration.getMappedStatement(
                DmsFileCenterMapper.class.getName() + "." + method);
        BoundSql boundSql = statement.getBoundSql(java.util.Collections.singletonMap("q", query));
        return boundSql.getSql().replaceAll("\\s+", " ").trim();
    }

    private void assertBalancedParentheses(String sql) {
        long opening = sql.chars().filter(value -> value == '(').count();
        long closing = sql.chars().filter(value -> value == ')').count();
        assertEquals(opening, closing, sql);
    }
}