package jnpf.dmsMapper;

import jnpf.dmsEntity.fenfa.model.DmsWenjianDayinTaizhangQuery;
import jnpf.dmsPermission.DmsUserFolderPermissionTreeQuery;
import jnpf.dmsPermission.DmsUserFolderPermissionPrincipal;
import jnpf.dmsDatabase.DmsUuidTypeHandler;
import jnpf.util.JsonUtil;
import org.apache.ibatis.builder.xml.XMLMapperBuilder;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.scripting.defaults.DefaultParameterHandler;
import org.apache.ibatis.session.Configuration;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.condition.EnabledIfEnvironmentVariable;

import java.io.InputStream;
import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.util.*;

import static org.junit.jupiter.api.Assertions.*;

/** Executes the production mapper SQL against virtual rows, inside a read-only PostgreSQL transaction. */
@EnabledIfEnvironmentVariable(named = "DMS_LEDGER_TEST_JDBC_URL", matches = ".+")
class DmsWenjianDayinTaizhangPostgresTest {
    private Configuration configuration;
    private String fixture;
    private String exclusions = "SELECT NULL::text tenant_id, NULL::text user_id, NULL::text action_code, "
            + "NULL::text resource_type, NULL::uuid resource_id, NULL::timestamptz revoked_at, "
            + "NULL::timestamptz valid_from, NULL::timestamptz valid_to WHERE FALSE";

    @BeforeEach
    void setup() throws Exception {
        configuration = new Configuration();
        configuration.getTypeHandlerRegistry().register(UUID.class, DmsUuidTypeHandler.class);
        try (InputStream xml = getClass().getResourceAsStream("/mapper/DmsUserFolderPermissionTreeMapper.xml")) {
            new XMLMapperBuilder(xml, configuration, "mapper/DmsUserFolderPermissionTreeMapper.xml", configuration.getSqlFragments()).parse();
        }
        try (InputStream xml = getClass().getResourceAsStream("/mapper/DmsWenjianDayinTaizhangMapper.xml")) {
            new XMLMapperBuilder(xml, configuration, "mapper/DmsWenjianDayinTaizhangMapper.xml", configuration.getSqlFragments()).parse();
        }
        try (InputStream sql = getClass().getResourceAsStream("/print-ledger-fixture.sql")) {
            fixture = new String(sql.readAllBytes(), StandardCharsets.UTF_8);
        }
    }

    @Test
    void readExclusionsOverrideDirectoryPersonalAndOverlappingGrants() throws Exception {
        for (String user : Arrays.asList("self", "subtree", "applicant")) {
            for (String type : Arrays.asList("DOCUMENT", "FILE_VERSION")) {
                exclusions = exclusion(user, type, "DOCUMENT".equals(type) ? 201 : 101);
                DmsWenjianDayinTaizhangQuery q = query("USER", user);
                q.setTaskId("task-01");
                assertEquals(0, total(q), user + "/" + type);
                assertTrue(ids(q).isEmpty());
                // Even when both allow branches match, an exclusion must win.
                q.setFolderIdsJson(query("USER", "subtree").getFolderIdsJson());
                assertEquals(0, total(q));
                assertTrue(ids(q).isEmpty());
            }
        }
    }

    @Test
    void exclusionsApplyBeforeCountingPaginationSearchAndEcho() throws Exception {
        exclusions = exclusion("subtree", "DOCUMENT", 230);
        DmsWenjianDayinTaizhangQuery q = query("USER", "subtree");
        q.setLimit(20);
        assertEquals(29, total(q));
        assertEquals(20, ids(q).size());
        assertEquals("task-29", ids(q).get(0));
        q.setOffset(20);
        assertEquals(9, ids(q).size());
        q.setOffset(0);
        q.setSearch("code-30");
        assertEquals(0, total(q));
        assertTrue(ids(q).isEmpty());
        q.setSearch(null);
        q.setTaskId("task-30");
        assertEquals(0, total(q));
        assertTrue(ids(q).isEmpty());
        q.setTaskId("task-29");
        assertEquals(Collections.singletonList("task-29"), ids(q));
    }

    @Test
    void inactiveOrUnrelatedExclusionsPreserveExistingVisibility() throws Exception {
        for (String user : Arrays.asList("self", "applicant")) {
            String active = exclusion(user, "DOCUMENT", 201);
            for (String ignored : Arrays.asList(
                    active.replace("NULL::timestamptz revoked_at", "now() revoked_at"),
                    active.replace("now()-interval '1 day'", "now()+interval '1 day'"),
                    active.replace("NULL::timestamptz valid_to", "now() valid_to"),
                    active.replace("'tenant-a'", "'tenant-b'"),
                    active.replace("'" + user + "'", "'another-user'"),
                    active.replace("'READ'", "'DOWNLOAD'"),
                    active.replace("'DOCUMENT'", "'FILE_VERSION'"),
                    exclusion(user, "DOCUMENT", 999))) {
                exclusions = ignored;
                DmsWenjianDayinTaizhangQuery q = query("USER", user);
                assertEquals(1, total(q), ignored);
                assertEquals(Collections.singletonList("task-01"), ids(q));
            }
            exclusions = active.replace("NULL::timestamptz valid_to", "now()+interval '1 day' valid_to");
            assertEquals(0, total(query("USER", user))); // Active finite exclusion.
            exclusions = active.replace("now()-interval '1 day'", "now()");
            assertEquals(0, total(query("USER", user))); // Inclusive valid_from boundary.
        }
    }

    @Test
    void versionExclusionDoesNotHideOtherVersionsButDocumentExclusionDoes() throws Exception {
        // task-02 is a newer version of task-01's document; use version snapshots, not latest resource state.
        fixture = fixture.replace("'file-' || n wenjian_id",
                        "'file-' || CASE WHEN n=2 THEN 1 ELSE n END wenjian_id")
                .replace("'D(00)'::text banbenhao", "CASE WHEN n=2 THEN 'D(01)' ELSE 'D(00)' END banbenhao")
                .replace("(200+n)", "(200+CASE WHEN n=2 THEN 1 ELSE n END)")
                .replace("WHEN n=8 THEN", "WHEN n=2 OR n=8 THEN");
        DmsWenjianDayinTaizhangQuery q = query("USER", "self");
        assertEquals(Arrays.asList("task-02", "task-01"), ids(q));
        exclusions = exclusion("self", "FILE_VERSION", 102);
        assertEquals(Collections.singletonList("task-01"), ids(q));
        exclusions = exclusion("self", "FILE_VERSION", 101);
        assertEquals(Collections.singletonList("task-02"), ids(q));
        exclusions = exclusion("self", "DOCUMENT", 201);
        assertEquals(0, total(q));
        assertTrue(ids(q).isEmpty());
    }

    private String exclusion(String user, String type, int resource) {
        return "SELECT 'tenant-a'::text tenant_id, '" + user + "'::text user_id, 'READ'::text action_code, '"
                + type + "'::text resource_type, '00000000-0000-0000-0000-"
                + String.format(Locale.ROOT, "%012d", resource) + "'::uuid resource_id, "
                + "NULL::timestamptz revoked_at, now()-interval '1 day' valid_from, NULL::timestamptz valid_to";
    }

    @Test
    void personalApplicationRequiresOwnershipApprovalAndMatchingSourceVersion() throws Exception {
        DmsWenjianDayinTaizhangQuery q = query("USER", "applicant");
        assertEquals("[]", q.getFolderIdsJson());
        assertEquals(Collections.singletonList("task-01"), ids(q));
        assertEquals(1, total(q));
        q.setTaskId("task-02");
        assertEquals(0, total(q)); // Same version grant cannot expose another applicant's task.
        q.setTaskId("task-01");
        q.setSearch("no-match");
        assertEquals(0, total(q));
        q.setSearch("code-1");
        assertEquals(1, total(q));
        q.setOffset(1);
        assertTrue(ids(q).isEmpty());
        q.setTenantId("tenant-b");
        assertEquals(0, total(q));
    }

    @Test
    void overlappingDirectoryAndPersonalBranchesDoNotDuplicateTasks() throws Exception {
        DmsWenjianDayinTaizhangQuery q = query("USER", "applicant");
        q.setFolderIdsJson(query("USER", "subtree").getFolderIdsJson());
        assertEquals(30, total(q));
        q.setLimit(20);
        assertEquals(20, ids(q).size());
        q.setOffset(20);
        assertEquals(10, ids(q).size());
        q.setTaskId("task-01");
        assertEquals(1, total(q));
    }

    @Test
    void inactiveOrUnrelatedPersonalGrantsNeverExposeApplicationTasks() throws Exception {
        String original = fixture;
        String grant = "NULL::timestamptz, now()-interval '1 day', NULL::timestamptz, NULL::integer, 0, 'WORKFLOW'";
        for (String invalid : Arrays.asList(
                "now(), now()-interval '1 day', NULL::timestamptz, NULL::integer, 0, 'WORKFLOW'",
                "NULL::timestamptz, now()+interval '1 day', NULL::timestamptz, NULL::integer, 0, 'WORKFLOW'",
                "NULL::timestamptz, now()-interval '1 day', now(), NULL::integer, 0, 'WORKFLOW'",
                "NULL::timestamptz, now()-interval '1 day', NULL::timestamptz, 1, 1, 'WORKFLOW'",
                "NULL::timestamptz, now()-interval '1 day', NULL::timestamptz, NULL::integer, 0, 'MANUAL'")) {
            fixture = original.replace(grant, invalid);
            assertEquals(0, total(query("USER", "applicant")), invalid);
        }
        for (String[] replacement : Arrays.asList(
                new String[]{"'USER', 'applicant'", "'USER', 'other-user'"},
                new String[]{"'tenant-a', 'USER', 'applicant'", "'tenant-b', 'USER', 'applicant'"},
                new String[]{"'SELF', 'PRINT'", "'SELF', 'READ'"},
                new String[]{"'FILE_VERSION'", "'FILE'"})) {
            fixture = original.replace(replacement[0], replacement[1]);
            assertEquals(0, total(query("USER", "applicant")));
        }
    }

    @Test
    void selfDoesNotInheritAndNavigationAncestorsDoNotGrantAccess() throws Exception {
        assertEquals(Arrays.asList("task-01"), ids(query("USER", "self")));
        assertEquals(Arrays.asList("task-25"), ids(query("USER", "leaf")));
        for (String user : Arrays.asList("read-only", "unknown", "revoked", "expired", "future", "exhausted")) {
            assertEquals(0, total(query("USER", user)), user);
        }
    }

    @Test
    void inheritedPermissionsAreDeduplicatedAndPaginatedAfterFiltering() throws Exception {
        DmsWenjianDayinTaizhangQuery q = query("USER", "subtree");
        assertEquals(30, total(q)); // Excludes missing/deleted resources, cross-tenant resources and canceled tasks.
        q.setLimit(20);
        List<Map<String,Object>> first = rows("selectPage", q);
        assertEquals(20, first.size());
        assertEquals("task-30", first.get(0).get("f_id"));
        assertEquals(1, ((Number)first.get(0).get("yidayin_fenshu")).intValue());
        assertEquals(2, ((Number)first.get(0).get("weidayin_fenshu")).intValue());
        q.setOffset(20);
        assertEquals(10, ids(q).size());
        q.setOffset(40);
        assertTrue(ids(q).isEmpty());
        assertEquals(30, total(q));
    }

    @Test
    void organizationRolePositionAndGroupKindsMatchTreeRules() throws Exception {
        assertEquals(30, total(query("ORG_DIRECT", "org-direct")));
        assertEquals(0, total(query("ORG_ANCESTOR", "org-direct")));
        assertEquals(30, total(query("ORG_ANCESTOR", "org-parent")));
        assertEquals(0, total(query("ORG_DIRECT", "org-parent")));
        assertEquals(30, total(query("ROLE", "role-1")));
        assertEquals(30, total(query("POSITION", "position-1")));
        assertEquals(30, total(query("GROUP", "group-1")));
    }

    @Test
    void searchEchoAndTenantUseSameScopeAsList() throws Exception {
        DmsWenjianDayinTaizhangQuery q = query("USER", "subtree");
        q.setSearch("code-30");
        assertEquals(1, total(q));
        assertEquals(Arrays.asList("task-30"), ids(q));
        q.setSearch(null);
        q.setTaskId("task-31");
        assertEquals(0, total(q));
        q.setTaskId("task-30");
        assertEquals(Arrays.asList("task-30"), ids(q));
        q.setTenantId("tenant-b");
        assertEquals(0, total(q));
    }

    private DmsWenjianDayinTaizhangQuery query(String kind, String principal) throws Exception {
        DmsWenjianDayinTaizhangQuery q = new DmsWenjianDayinTaizhangQuery();
        q.setTenantId("tenant-a");
        DmsUserFolderPermissionTreeQuery permissionQuery = new DmsUserFolderPermissionTreeQuery();
        permissionQuery.setTenantId("tenant-a");
        permissionQuery.setPrincipals(Collections.singletonList(new DmsUserFolderPermissionPrincipal("current-user", kind, principal)));
        List<Object> folderIds = new ArrayList<>();
        for (Map<String,Object> row : rows("selectPrintFolders", permissionQuery)) folderIds.add(row.get("id").toString());
        q.setFolderIdsJson(JsonUtil.getObjectToString(folderIds));
        q.setUserId(principal);
        q.setApprovedFlowState(2);
        Map<String,Object> personal = new HashMap<>();
        personal.put("tenantId", q.getTenantId());
        personal.put("userId", principal);
        List<String> versionIds = new ArrayList<>();
        for (Map<String,Object> row : rows("selectWorkflowPrintVersions", personal)) versionIds.add(row.get("id").toString());
        q.setWorkflowPrintVersionIdsJson(JsonUtil.getObjectToString(versionIds));
        q.setLimit(100);
        return q;
    }

    private long total(DmsWenjianDayinTaizhangQuery q) throws Exception {
        return ((Number)rows("selectCount",q).get(0).get("total")).longValue();
    }

    private List<String> ids(DmsWenjianDayinTaizhangQuery q) throws Exception {
        List<String> ids = new ArrayList<>();
        for (Map<String,Object> row : rows("selectPage",q)) ids.add((String)row.get("f_id"));
        return ids;
    }

    private List<Map<String,Object>> rows(String method, Object q) throws Exception {
        boolean personal = "selectWorkflowPrintVersions".equals(method);
        MappedStatement statement = configuration.getMappedStatement((personal || "selectPrintFolders".equals(method)
                ? "jnpf.dmsMapper.DmsUserFolderPermissionTreeMapper." : "jnpf.dmsMapper.DmsWenjianDayinTaizhangMapper.")+method);
        @SuppressWarnings("unchecked")
        Map<String,Object> params = personal ? (Map<String,Object>) q : Collections.singletonMap("q", q);
        BoundSql bound = statement.getBoundSql(params);
        String testData = fixture + "fixture_exclusions AS (" + exclusions + "), ";
        String sql = (personal ? "WITH RECURSIVE " + testData + " permission_result AS (" + bound.getSql() + ") SELECT * FROM permission_result"
                : bound.getSql().replaceFirst("WITH (RECURSIVE )?", "WITH RECURSIVE " + testData))
                .replace("public.dms_access_exclusions", "fixture_exclusions")
                .replace("public.dms_file_permissions", "fixture_permissions")
                .replace("public.dms_folders", "fixture_folders")
                .replace("public.dms_wenjian_dayin_renwu", "fixture_tasks")
                .replace("public.dms_wenjian_ziyuan", "fixture_resources")
                .replace("public.dms_wenjian_dayin_shenqing_zixiang", "fixture_details")
                .replace("public.dms_wenjian_dayin_shenqing", "fixture_applications")
                .replace("public.dms_wenjian_banben", "fixture_versions")
                .replace("public.base_organize", "fixture_org")
                .replace("public.dms_wenjian_dayin_jilu", "fixture_prints");
        try (Connection connection = DriverManager.getConnection(System.getenv("DMS_LEDGER_TEST_JDBC_URL"),
                System.getenv("DMS_LEDGER_TEST_DB_USER"), System.getenv("DMS_LEDGER_TEST_DB_PASSWORD"))) {
            connection.setReadOnly(true);
            connection.setAutoCommit(false);
            try (PreparedStatement ps = connection.prepareStatement(sql)) {
                ps.setQueryTimeout(20);
                new DefaultParameterHandler(statement, params, bound).setParameters(ps);
                try (ResultSet rs = ps.executeQuery()) {
                    List<Map<String,Object>> result = new ArrayList<>();
                    while (rs.next()) {
                        Map<String,Object> row = new LinkedHashMap<>();
                        for (int i=1;i<=rs.getMetaData().getColumnCount();i++) row.put(rs.getMetaData().getColumnLabel(i),rs.getObject(i));
                        result.add(row);
                    }
                    return result;
                }
            } finally {
                connection.rollback();
            }
        }
    }
}