package jnpf.dmsMapper;

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.Test;
import org.junit.jupiter.api.condition.EnabledIfEnvironmentVariable;
import java.io.InputStream;
import java.sql.*;
import java.util.*;
import static org.junit.jupiter.api.Assertions.*;

/** Production SQL against read-only CTE fixtures and the actual schema; never writes business data. */
@EnabledIfEnvironmentVariable(named = "DMS_LEDGER_TEST_JDBC_URL", matches = ".+")
class DmsWenjianBufaPrintMapperPostgresTest {
    @Test
    void preservesInvalidSourceRowsButNeverResolvesWrongTenantDeletedOrMismatchedVersions() throws Exception {
        String fixture = """
                WITH details AS (
                  SELECT 'detail-'||n f_id, CASE WHEN n=8 THEN 'other' ELSE 'tenant-a' END f_tenant_id,
                    CASE WHEN n=9 THEN 1 ELSE NULL END f_delete_mark,
                    CASE WHEN n=10 THEN 'other-request' ELSE 'request' END f_foreign_id,
                    'file-'||n wenjian_id, 'version-'||n banben_id,
                    'V1'::text banben_hao, '3'::text bufa_fenshu, 'purpose'::text yongtu,
                    'APPROVED-DOC'::text wenjian_bianhao, 'Approved document'::text wenjian_mingcheng
                  FROM generate_series(1,10) n
                ), resources AS (
                  SELECT 'file-'||n f_id, CASE WHEN n=2 THEN 'other' ELSE 'tenant-a' END f_tenant_id,
                    CASE WHEN n=3 THEN 1 ELSE NULL END f_delete_mark, 'yes'::text shifou_shoukong
                  FROM generate_series(1,10) n
                ), versions AS (
                  SELECT 'version-'||n f_id, CASE WHEN n=4 THEN 'other' ELSE 'tenant-a' END f_tenant_id,
                    CASE WHEN n=5 THEN 1 ELSE NULL END f_delete_mark,
                    CASE WHEN n=6 THEN 'wrong-file' ELSE 'file-'||n END wenjian_id,
                    CASE WHEN n=7 THEN 'V2' ELSE 'V1' END banbenhao,
                    'DOC'::text wenjian_bianhao, 'Document'::text wenjian_mingcheng,
                    now() shengxiao_shijian, NULL::text shifou_shoukong
                  FROM generate_series(1,10) n
                )
                """;
        List<Map<String,Object>> rows = query("selectDetails", sql -> fixture + sql
                .replace("public.dms_wenjian_bufa_shenqing_zixiang", "details")
                .replace("public.dms_wenjian_ziyuan", "resources")
                .replace("public.dms_wenjian_banben", "versions"), "request");
        assertEquals(7, rows.size());
        assertEquals("version-1", rows.get(0).get("resolved_version_id"));
        assertEquals("yes", rows.get(0).get("shifou_shoukong"));
        assertEquals("APPROVED-DOC", rows.get(0).get("wenjian_bianhao"));
        assertEquals("Approved document", rows.get(0).get("wenjian_mingcheng"));
        for (int i=1;i<rows.size();i++) assertNull(rows.get(i).get("resolved_version_id"));
    }

    @Test
    void productionColumnsAndLockStatementMatchActualPostgresSchema() throws Exception {
        assertTrue(query("selectDetails", sql -> sql, "__codex_nonexistent_bufa_request__").isEmpty());
        // EXPLAIN checks syntax/types without acquiring a row lock in the read-only transaction.
        assertFalse(query("lockRequests", sql -> "EXPLAIN " + sql, "__codex_nonexistent_bufa_request__").isEmpty());
    }

    private List<Map<String,Object>> query(String method, java.util.function.UnaryOperator<String> transform,
                                          String requestId) throws Exception {
        Configuration config = new Configuration();
        String resource = "mapper/DmsWenjianBufaPrintMapper.xml";
        try (InputStream xml = getClass().getResourceAsStream("/" + resource)) {
            new XMLMapperBuilder(xml, config, resource, config.getSqlFragments()).parse();
        }
        MappedStatement statement = config.getMappedStatement("jnpf.dmsMapper.DmsWenjianBufaPrintMapper." + method);
        Map<String,Object> parameters = new HashMap<>();
        parameters.put("tenantId", "tenant-a");
        parameters.put("requestId", requestId);
        parameters.put("taskId", requestId);
        BoundSql bound = statement.getBoundSql(parameters);
        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(transform.apply(bound.getSql()))) {
                ps.setQueryTimeout(20);
                new DefaultParameterHandler(statement, parameters, bound).setParameters(ps);
                try (ResultSet rs = ps.executeQuery()) {
                    List<Map<String,Object>> result = new ArrayList<>();
                    while (rs.next()) {
                        Map<String,Object> row = new HashMap<>();
                        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();
            }
        }
    }
}