package jnpf.elnService.impl;

import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import jnpf.audit.model.AuditEventDTO;
import jnpf.elnEntity.runtime.ElnRecordForms;
import jnpf.elnEntity.runtime.ElnRecordVos;
import jnpf.elnMapper.*;
import jnpf.elnService.ElnRecordSourceSyncService;
import jnpf.elnService.ElnTestWorkItemSnapshotProvider;
import jnpf.lims.api.model.*;
import org.junit.After;
import org.junit.Assume;
import org.junit.Before;
import org.junit.Test;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.aop.framework.ProxyFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.transaction.annotation.AnnotationTransactionAttributeSource;
import org.springframework.transaction.interceptor.TransactionInterceptor;

import java.lang.reflect.Proxy;
import java.net.URI;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import static org.junit.Assert.*;

public class ElnConfiguredRecordPostgresTest {
    private JdbcTemplate admin;
    private JdbcTemplate database;
    private String schema;
    private ElnRecordSourceSyncService service;
    private DriverManagerDataSource dataSource;
    private SqlSessionTemplate session;
    private ElnRecordFormulaCalculationServiceImpl calculation;
    private final ElnSourceSnapshot source = new ElnSourceSnapshot();
    private final List<AuditEventDTO> audits = new ArrayList<>();

    @Before
    public void isolatedDatabase() throws Exception {
        String url = System.getProperty("eln.source.pg.url");
        Assume.assumeTrue("Run with the isolated PostgreSQL fixture", url != null);
        URI target = URI.create(url.substring("jdbc:".length()));
        assertEquals("127.0.0.1", target.getHost());
        assertEquals("/eln_source_catalog_test", target.getPath());
        assertNull(target.getQuery());
        admin = new JdbcTemplate(new DriverManagerDataSource(url, "eln_source_test", "eln_source_test_only"));
        schema = "eln_source_sync_" + java.util.UUID.randomUUID().toString().replace("-", "");
        admin.execute("CREATE SCHEMA " + schema);
        dataSource = new DriverManagerDataSource(url + "?currentSchema=" + schema,
                "eln_source_test", "eln_source_test_only");
        database = new JdbcTemplate(dataSource);
        createTables();
        seedRecord();

        MybatisConfiguration configuration = new MybatisConfiguration();
        com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor interceptor =
                new com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new com.baomidou.mybatisplus.extension.plugins.inner.OptimisticLockerInnerInterceptor());
        configuration.addInterceptor(interceptor);
        configuration.addMapper(ElnRecordMapper.class);
        configuration.addMapper(ElnRecordRevisionMapper.class);
        configuration.addMapper(ElnRecordFieldValueMapper.class);
        configuration.addMapper(ElnFieldVersionMapper.class);
        configuration.addMapper(ElnSharedFillOperationMapper.class);
        configuration.addMapper(ElnTemplateMapper.class);
        configuration.addMapper(ElnTemplateBindingMapper.class);
        MybatisSqlSessionFactoryBean factory = new MybatisSqlSessionFactoryBean();
        factory.setDataSource(dataSource);
        factory.setConfiguration(configuration);
        session = new SqlSessionTemplate(factory.getObject());

        // This fixture has no formulas; only the database query for active formulas is stubbed.
        ElnTemplateFormulaBindingMapper formulas = (ElnTemplateFormulaBindingMapper) Proxy.newProxyInstance(
                ElnTemplateFormulaBindingMapper.class.getClassLoader(),
                new Class<?>[]{ElnTemplateFormulaBindingMapper.class}, (proxy, method, args) -> {
                    if ("selectList".equals(method.getName())) return Collections.emptyList();
                    throw new AssertionError("Unexpected formula query: " + method.getName());
                });
        calculation = new ElnRecordFormulaCalculationServiceImpl(
                formulas, null, null, null, null, null, new jnpf.elnService.formula.FormulaDependencyGraph());
        ElnRecordSourceSyncServiceImpl implementation = new ElnRecordSourceSyncServiceImpl(
                session.getMapper(ElnRecordMapper.class), session.getMapper(ElnRecordRevisionMapper.class),
                session.getMapper(ElnRecordFieldValueMapper.class), session.getMapper(ElnTemplateBindingMapper.class),
                session.getMapper(ElnFieldVersionMapper.class),
                provider(), calculation, audits::add,
                () -> new ElnRecordSourceSyncServiceImpl.UserContext("0", "user-1"),
                jnpf.elnService.ElnCrossTemplateSnapshotResolver.none(),
                new ElnRecordSourceSyncOperationService(session.getMapper(ElnSharedFillOperationMapper.class)));
        ProxyFactory proxy = new ProxyFactory(implementation);
        proxy.addAdvice(new TransactionInterceptor(new DataSourceTransactionManager(dataSource),
                new AnnotationTransactionAttributeSource()));
        service = (ElnRecordSourceSyncService) proxy.getProxy();
        source.setSourceRef("item-1");
        source.setPropertyCode("test.basis");
        source.setPropertyRevision(1);
        source.setDataType("TEXT");
        source.setOutcomeCode("MISSING");
        source.setReasonCode("NAME_MISSING");
        source.setAcquiredAt("2026-09-06T02:00:00Z");
        source.setLineage("task.details@1/basis");
    }

    @After
    public void cleanup() {
        if (schema != null && schema.matches("eln_source_sync_[a-f0-9]{32}")) {
            admin.execute("DROP SCHEMA " + schema + " CASCADE");
        }
    }

    @Test
    public void explicitClearRemainsEmptyWhenReadInANewTransaction() {
        ElnRecordVos.SourceSyncPreview before = service.preview("record-1");
        assertEquals("old basis", before.getDifferences().get(0).getPreviousValue());
        ElnRecordForms.SourceSync command = clearCommand(before);

        service.synchronize("record-1", command);

        ElnRecordVos.SourceSyncPreview after = service.preview("record-1");
        assertNull(after.getDifferences().get(0).getPreviousValue());
        assertNull(after.getDifferences().get(0).getCurrentValue());
        assertEquals(Integer.valueOf(5), after.getRecordVersion());
        assertEquals(java.util.Date.from(java.time.Instant.parse("2026-09-06T02:00:00Z")),
                after.getDifferences().get(0).getPreviousFetchedTime());
        assertEquals(1, audits.size());
        assertTrue(audits.get(0).getFieldDiffs().contains("Verified missing at source"));
    }

    @Test
    public void creationReadsTheTemplatePinnedSourceRevisionAndPersistsTheSnapshot() {
        jnpf.elnService.ElnRecordCreationService creation = creationService();
        source.setSourceRef("item-new");
        source.setOutcomeCode("SUCCESS");
        source.setValue("Configured V1");
        source.setReasonCode(null);
        database.execute("INSERT INTO eln_field_version SELECT (jsonb_populate_record(NULL::eln_field_version, "
                + "to_jsonb(v) || '{\"f_id\":\"version-2\",\"banben_hao\":2,\"bangding_peizhi\":{\"read\":{"
                + "\"source\":\"LIMS_TASK\",\"property\":\"test.basis\",\"propertyRevision\":2}}}'::jsonb)).* "
                + "FROM eln_field_version v WHERE f_id='version-1'");

        ElnRecordVos.Created created = creation.create(createCommand());

        assertFalse(created.isExisting());
        assertEquals(created.getRevisionId() + ".docx", created.getDocument().getFileId());
        ElnRecordVos.SourceSyncPreview persisted = service.preview(created.getRecordId());
        assertEquals(created.getRevisionId(), persisted.getRevisionId());
        assertEquals("Configured V1", persisted.getDifferences().get(0).getPreviousValue());
        assertEquals(Integer.valueOf(1), persisted.getDifferences().get(0).getSourcePropertyRevision());
        assertEquals("task.details@1/basis", persisted.getDifferences().get(0).getSourceLineage());
    }

    @Test
    public void creationPreservesAllDeclaredDecimalDigitsInPostgres() {
        jnpf.elnService.ElnRecordCreationService creation = creationService();
        database.execute("UPDATE eln_field_version SET shuju_leixing='DECIMAL'");
        source.setSourceRef("item-new");
        source.setDataType("DECIMAL");
        source.setOutcomeCode("SUCCESS");
        source.setValue("12345678901234567890.123456789012345678");

        ElnRecordVos.Created created = creation.create(createCommand());

        assertEquals("12345678901234567890.123456789012345678",
                service.preview(created.getRecordId()).getDifferences().get(0).getPreviousValue());
    }

    @Test
    public void failedInitialSnapshotWriteRollsBackRecordAndRevisionBeforeRetry() {
        jnpf.elnService.ElnRecordCreationService creation = creationService();
        database.execute("INSERT INTO eln_field_version SELECT (jsonb_populate_record(NULL::eln_field_version, "
                + "to_jsonb(v) || '{\"f_id\":\"version-other\",\"ziduan_dingyi_id\":\"definition-other\"}'::jsonb)).* "
                + "FROM eln_field_version v WHERE f_id='version-1'");
        database.execute("INSERT INTO eln_template_binding SELECT (jsonb_populate_record(NULL::eln_template_binding, "
                + "to_jsonb(b) || '{\"f_id\":\"binding-2\",\"ziduan_bianma\":\"basis_other\","
                + "\"ziduan_dingyi_id\":\"definition-other\",\"ziduan_banben_id\":\"version-other\"}'::jsonb)).* "
                + "FROM eln_template_binding b WHERE f_id='binding-1'");
        database.execute("CREATE FUNCTION reject_second_initial_snapshot() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN "
                + "IF EXISTS (SELECT 1 FROM eln_record_field_value WHERE dianzi_jilu_id=NEW.dianzi_jilu_id) "
                + "THEN RAISE EXCEPTION 'fixture second initial snapshot failure'; END IF; RETURN NEW; END $$");
        database.execute("CREATE TRIGGER reject_second_initial_snapshot BEFORE INSERT ON eln_record_field_value "
                + "FOR EACH ROW EXECUTE FUNCTION reject_second_initial_snapshot()");
        source.setSourceRef("item-new");
        source.setOutcomeCode("SUCCESS");
        source.setValue("Configured V1");

        try {
            creation.create(createCommand());
            fail("The second initial snapshot write must fail");
        } catch (org.springframework.dao.DataAccessException expected) {
            assertTrue(expected.getMostSpecificCause().getMessage().contains("fixture second initial snapshot failure"));
        }

        database.execute("DROP TRIGGER reject_second_initial_snapshot ON eln_record_field_value");
        ElnRecordVos.Created retried = creation.create(createCommand());
        assertFalse(retried.isExisting());
        ElnRecordVos.SourceSyncPreview persisted = service.preview(retried.getRecordId());
        assertEquals(retried.getRevisionId(), persisted.getRevisionId());
        assertEquals(2, persisted.getDifferences().size());
        for (ElnRecordVos.SourceSyncDifference difference : persisted.getDifferences()) {
            assertEquals("Configured V1", difference.getPreviousValue());
        }
    }

    @Test
    public void missingConfiguredValueInsertFailureRollsBackAndAllowsRetry() {
        database.execute("DELETE FROM eln_record_field_value WHERE f_id='value-1'");
        database.execute("INSERT INTO eln_field_version (f_id,ziduan_dingyi_id,banben_hao,ziduan_mingcheng,"
                + "shuju_leixing,shifou_bitian,biz_status,bangding_peizhi) VALUES "
                + "('version-manual','definition-manual',1,'Manual note','TEXT',0,'PUBLISHED',"
                + "'{\"read\":{\"source\":\"MANUAL\"}}')");
        database.execute("INSERT INTO eln_template_binding (f_id,muban_id,ziduan_dingyi_id,ziduan_bianma,"
                + "ziduan_banben_id,kongjian_juese,bangding_zhuangtai) VALUES "
                + "('binding-manual','template-1','definition-manual','manual_note','version-manual','PRIMARY','ACTIVE')");
        database.execute("INSERT INTO eln_record_field_value (f_id,dianzi_jilu_id,xiuding_id,muban_id,"
                + "muban_bangding_id,ziduan_dingyi_id,ziduan_bianma,ziduan_banben_id,bangding_juese,"
                + "bangding_zhuangtai,zhi_wenben,zhi_laiyuan) VALUES "
                + "('value-manual','record-1','revision-1','template-1','binding-manual','definition-manual',"
                + "'manual_note','version-manual','PRIMARY','ACTIVE','keep this note','MANUAL')");
        database.execute("CREATE FUNCTION reject_recovered_snapshot() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN "
                + "IF NEW.muban_bangding_id='binding-1' THEN RAISE EXCEPTION 'fixture recovered snapshot failure'; "
                + "END IF; RETURN NEW; END $$");
        database.execute("CREATE TRIGGER reject_recovered_snapshot BEFORE INSERT ON eln_record_field_value "
                + "FOR EACH ROW EXECUTE FUNCTION reject_recovered_snapshot()");
        source.setOutcomeCode("SUCCESS");
        source.setValue("Recovered basis");
        source.setReasonCode(null);
        ElnRecordVos.SourceSyncPreview preview = service.preview("record-1");
        ElnRecordForms.SourceSync command = syncCommand(preview, "recover-missing-1");

        try {
            service.synchronize("record-1", command);
            fail("The recovered snapshot insert must fail");
        } catch (org.springframework.dao.DataAccessException expected) {
            assertTrue(expected.getMostSpecificCause().getMessage().contains("fixture recovered snapshot failure"));
        }

        assertEquals(Integer.valueOf(4), database.queryForObject(
                "SELECT f_version FROM eln_record WHERE f_id='record-1'", Integer.class));
        assertEquals(Integer.valueOf(0), database.queryForObject(
                "SELECT count(*) FROM eln_record_field_value WHERE muban_bangding_id='binding-1'", Integer.class));
        assertEquals("keep this note", database.queryForObject(
                "SELECT zhi_wenben FROM eln_record_field_value WHERE f_id='value-manual'", String.class));
        assertTrue(audits.isEmpty());

        database.execute("DROP TRIGGER reject_recovered_snapshot ON eln_record_field_value");
        assertEquals(1, service.synchronize("record-1", command).getChangedCount());
        assertEquals(Integer.valueOf(5), database.queryForObject(
                "SELECT f_version FROM eln_record WHERE f_id='record-1'", Integer.class));
        assertEquals("Recovered basis", database.queryForObject(
                "SELECT zhi_wenben FROM eln_record_field_value WHERE muban_bangding_id='binding-1'", String.class));
        assertEquals("keep this note", database.queryForObject(
                "SELECT zhi_wenben FROM eln_record_field_value WHERE f_id='value-manual'", String.class));
    }

    private ElnRecordForms.Create createCommand() {
        ElnRecordForms.Create command = new ElnRecordForms.Create();
        command.setTestWorkItemId("item-new");
        command.setOperationCode("create-new");
        return command;
    }

    private jnpf.elnService.ElnRecordCreationService creationService() {
        database.execute("CREATE TABLE eln_template (f_id text PRIMARY KEY, f_tenant_id text DEFAULT '0', "
                + "muban_bianhao text, muban_banben text, jianyan_xiangmu text, jianyan_xiangmu_bianma text, "
                + "muban_file text, biz_status text, biz_enabled text, shengxiao_riqi timestamp(0), f_delete_mark integer DEFAULT 0)");
        database.execute("CREATE TABLE eln_template_current_version (f_tenant_id text DEFAULT '0', "
                + "dangqian_muban_id text, f_delete_mark integer DEFAULT 0)");
        database.execute("INSERT INTO eln_template (f_id,muban_bianhao,muban_banben,jianyan_xiangmu_bianma,muban_file,biz_status,biz_enabled,shengxiao_riqi) "
                + "VALUES ('template-1','TPL-1','V1','TEST-1','[{\"fileId\":\"template.docx\",\"name\":\"template.docx\",\"fileExtension\":\"docx\"}]',"
                + "'PUBLISHED','enabled','2026-09-01')");
        database.execute("INSERT INTO eln_template_current_version (dangqian_muban_id) VALUES ('template-1')");
        database.execute("ALTER TABLE eln_record_revision ADD FOREIGN KEY (dianzi_jilu_id) REFERENCES eln_record(f_id)");
        database.execute("ALTER TABLE eln_record ADD FOREIGN KEY (dangqian_xiuding_id) REFERENCES eln_record_revision(f_id)");
        database.execute("ALTER TABLE eln_record_field_value ADD FOREIGN KEY (xiuding_id) REFERENCES eln_record_revision(f_id), "
                + "ADD FOREIGN KEY (dianzi_jilu_id) REFERENCES eln_record(f_id), "
                + "ADD FOREIGN KEY (ziduan_banben_id) REFERENCES eln_field_version(f_id)");
        database.execute("CREATE UNIQUE INDEX record_work_item ON eln_record(f_tenant_id,jianyan_xiang_id)");

        jnpf.file.FileApi fileApi = org.mockito.Mockito.mock(jnpf.file.FileApi.class);
        jnpf.file.FileUploadApi fileUploadApi = org.mockito.Mockito.mock(jnpf.file.FileUploadApi.class);
        org.mockito.Mockito.when(fileApi.getPath("annex")).thenReturn("fixture-annex");
        org.mockito.Mockito.when(fileUploadApi.replaceFile(org.mockito.ArgumentMatchers.any(),
                org.mockito.ArgumentMatchers.eq("fixture-annex"), org.mockito.ArgumentMatchers.anyString())).thenReturn(true);
        org.mockito.Mockito.when(fileUploadApi.downFile(org.mockito.ArgumentMatchers.any())).thenAnswer(call ->
                feign.Response.builder().status(200).reason("OK")
                        .request(feign.Request.create(feign.Request.HttpMethod.GET, "http://fixture/template.docx",
                                Collections.emptyMap(), null, java.nio.charset.StandardCharsets.UTF_8, null))
                        .body("isolated document bytes", java.nio.charset.StandardCharsets.UTF_8).build());
        ElnRecordCreationServiceImpl implementation = new ElnRecordCreationServiceImpl(provider(),
                session.getMapper(ElnTemplateMapper.class), session.getMapper(ElnTemplateBindingMapper.class),
                session.getMapper(ElnFieldVersionMapper.class), session.getMapper(ElnRecordMapper.class),
                session.getMapper(ElnRecordRevisionMapper.class), session.getMapper(ElnRecordFieldValueMapper.class),
                new jnpf.elnService.template.ElnTemplateFileService(fileApi, fileUploadApi), calculation, null,
                () -> ElnRecordCreationServiceImpl.userContext("0", "user-1", "User One"));
        ProxyFactory proxy = new ProxyFactory(implementation);
        proxy.addAdvice(new TransactionInterceptor(new DataSourceTransactionManager(dataSource),
                new AnnotationTransactionAttributeSource()));
        return (jnpf.elnService.ElnRecordCreationService) proxy.getProxy();
    }

    private ElnRecordForms.SourceSync clearCommand(ElnRecordVos.SourceSyncPreview preview) {
        ElnRecordForms.SourceSync command = syncCommand(
                preview, "sync-" + preview.getRecordVersion());
        command.setClearReasons(Collections.singletonMap("binding-1", "Verified missing at source"));
        return command;
    }

    private ElnRecordForms.SourceSync syncCommand(
            ElnRecordVos.SourceSyncPreview preview, String operationCode) {
        ElnRecordForms.SourceSync command = new ElnRecordForms.SourceSync();
        command.setOperationCode(operationCode);
        command.setExpectedRecordVersion(preview.getRecordVersion());
        command.setPreviewToken(preview.getPreviewToken());
        return command;
    }

    @Test
    public void clearingDecimalPersistsNullAndAuditsTheNumericSlot() throws Exception {
        database.execute("UPDATE eln_field_version SET shuju_leixing='DECIMAL'");
        database.execute("UPDATE eln_record_field_value SET zhi_wenben=NULL, zhi_shuzhi=123.456789012345678901");
        source.setDataType("DECIMAL");
        ElnRecordVos.SourceSyncPreview before = service.preview("record-1");
        assertEquals("123.456789012345678901", before.getDifferences().get(0).getPreviousValue());

        service.synchronize("record-1", clearCommand(before));

        assertNull(service.preview("record-1").getDifferences().get(0).getPreviousValue());
        com.fasterxml.jackson.databind.JsonNode audit = new com.fasterxml.jackson.databind.ObjectMapper()
                .readTree(audits.get(0).getFieldDiffs()).get(0);
        assertEquals("zhi_shuzhi", audit.path("field").asText());
        assertEquals("123.456789012345678901", audit.path("oldData").asText());
    }

    @Test
    public void clearingDatesPersistsNullAndAuditsTheDateSlot() throws Exception {
        for (String type : new String[]{"DATE", "DATETIME"}) {
            database.update("UPDATE eln_field_version SET shuju_leixing=?", type);
            database.execute("UPDATE eln_record_field_value SET zhi_wenben=NULL, zhi_riqi='2026-09-01 10:20:30'");
            source.setDataType(type);
            ElnRecordVos.SourceSyncPreview before = service.preview("record-1");
            String expected = "DATE".equals(type) ? "2026-09-01" : "2026-09-01 10:20:30";
            assertEquals(expected, before.getDifferences().get(0).getPreviousValue());

            service.synchronize("record-1", clearCommand(before));

            assertNull(service.preview("record-1").getDifferences().get(0).getPreviousValue());
            com.fasterxml.jackson.databind.JsonNode audit = new com.fasterxml.jackson.databind.ObjectMapper()
                    .readTree(audits.get(audits.size() - 1).getFieldDiffs()).get(0);
            assertEquals("zhi_riqi", audit.path("field").asText());
            assertEquals(expected, audit.path("oldData").asText());
        }
    }

    @Test
    public void clearReplayIsIdempotentAndChangedSelectionOrReasonIsRejected() {
        ElnRecordVos.SourceSyncPreview before = service.preview("record-1");
        ElnRecordForms.SourceSync command = clearCommand(before);
        ElnRecordVos.SourceSynchronized first = service.synchronize("record-1", command);
        assertEquals(first.getRecordVersion(), service.synchronize("record-1", command).getRecordVersion());
        assertEquals(1, audits.size());

        command.setClearReasons(Collections.emptyMap());
        assertRejected(command, "操作码已用于其他请求");
        command.setClearReasons(Collections.singletonMap("binding-1", "A different reason"));
        assertRejected(command, "操作码已用于其他请求");
        assertEquals(Integer.valueOf(5), service.preview("record-1").getRecordVersion());
        assertEquals(1, audits.size());
    }

    @Test
    public void invalidClearReasonsAndUnknownBindingsLeaveSnapshotsUnchanged() {
        ElnRecordVos.SourceSyncPreview before = service.preview("record-1");
        ElnRecordForms.SourceSync command = clearCommand(before);
        for (String reason : new String[]{null, " ", String.join("", Collections.nCopies(501, "x"))}) {
            command.setClearReasons(Collections.singletonMap("binding-1", reason));
            assertRejected(command, reason != null && reason.length() > 500 ? "500" : "填写原因");
        }
        command.setClearReasons(Collections.singletonMap("unknown-binding", "Confirmed"));
        assertRejected(command, "不属于本次来源同步");
        assertOriginalSnapshot();
    }

    @Test
    public void requiredMissingAndInapplicableSourcesCannotBeCleared() {
        ElnRecordForms.SourceSync command = clearCommand(service.preview("record-1"));
        database.execute("UPDATE eln_field_version SET shifou_bitian=1");
        for (String outcome : new String[]{"MISSING", "NOT_APPLICABLE"}) {
            source.setOutcomeCode(outcome);
            assertRejected(command, "必填 LIMS 来源缺失");
        }
        database.execute("UPDATE eln_field_version SET shifou_bitian=0");
        source.setOutcomeCode("MISSING");
        assertOriginalSnapshot();
    }

    @Test
    public void sourceErrorsAndSuccessfulValuesCannotBeCleared() {
        ElnRecordForms.SourceSync command = clearCommand(service.preview("record-1"));
        for (String outcome : new String[]{"UNSUPPORTED_PROPERTY", "SOURCE_INVALID", "SOURCE_FORBIDDEN",
                "SOURCE_AMBIGUOUS", "SOURCE_LIMIT", "SOURCE_UNAVAILABLE"}) {
            source.setOutcomeCode(outcome);
            assertRejected(command, "来源取数失败");
        }
        source.setOutcomeCode("SUCCESS");
        source.setValue("new basis");
        assertRejected(clearCommand(service.preview("record-1")), "不允许清空");
        source.setOutcomeCode("MISSING");
        source.setValue(null);
        assertOriginalSnapshot();
    }

    @Test
    public void laterFieldWriteFailureRollsBackAllClearsAndAllowsTheSameOperationToRetry() {
        database.execute("INSERT INTO eln_record_field_value SELECT (jsonb_populate_record(NULL::eln_record_field_value, "
                + "to_jsonb(v) || '{\"f_id\":\"value-2\",\"muban_bangding_id\":\"binding-2\"}'::jsonb)).* "
                + "FROM eln_record_field_value v WHERE f_id='value-1'");
        // Whichever row is updated first, the second clear fails after observing the first within this transaction.
        database.execute("CREATE FUNCTION reject_second_clear() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN "
                + "IF NEW.zhi_wenben IS NULL AND EXISTS (SELECT 1 FROM eln_record_field_value WHERE f_id<>NEW.f_id AND zhi_wenben IS NULL) "
                + "THEN RAISE EXCEPTION 'fixture second snapshot write failure'; END IF; RETURN NEW; END $$");
        database.execute("CREATE TRIGGER reject_second_clear BEFORE UPDATE ON eln_record_field_value "
                + "FOR EACH ROW EXECUTE FUNCTION reject_second_clear()");
        ElnRecordForms.SourceSync command = clearCommand(service.preview("record-1"));
        java.util.Map<String, String> reasons = new java.util.LinkedHashMap<>(command.getClearReasons());
        reasons.put("binding-2", "Also verified missing");
        command.setClearReasons(reasons);

        try {
            service.synchronize("record-1", command);
            fail("The second snapshot write must fail");
        } catch (org.springframework.dao.DataAccessException expected) {
            assertTrue(expected.getMostSpecificCause().getMessage().contains("fixture second snapshot write failure"));
        }

        ElnRecordVos.SourceSyncPreview after = service.preview("record-1");
        assertEquals(Integer.valueOf(4), after.getRecordVersion());
        assertEquals(2, after.getDifferences().size());
        for (ElnRecordVos.SourceSyncDifference difference : after.getDifferences()) {
            assertEquals("old basis", difference.getPreviousValue());
        }
        assertTrue(audits.isEmpty());
        database.execute("DROP TRIGGER reject_second_clear ON eln_record_field_value");
        assertEquals(2, service.synchronize("record-1", command).getChangedCount());
        for (ElnRecordVos.SourceSyncDifference difference : service.preview("record-1").getDifferences()) {
            assertNull(difference.getPreviousValue());
        }
    }

    private void assertRejected(ElnRecordForms.SourceSync command, String message) {
        try {
            service.synchronize("record-1", command);
            fail("Unsafe clear must be rejected");
        } catch (jnpf.exception.DataException expected) {
            assertTrue(expected.getMessage(), expected.getMessage().contains(message));
        }
    }

    private void assertOriginalSnapshot() {
        ElnRecordVos.SourceSyncPreview after = service.preview("record-1");
        assertEquals(Integer.valueOf(4), after.getRecordVersion());
        assertEquals("old basis", after.getDifferences().get(0).getPreviousValue());
        assertTrue(audits.isEmpty());
    }

    private ElnTestWorkItemSnapshotProvider provider() {
        return new ElnTestWorkItemSnapshotProvider() {
            @Override
            public TestWorkItemSnapshotBatch resolve(List<String> ids, List<String> properties) {
                throw new AssertionError("Explicit actor required");
            }

            @Override
            public TestWorkItemSnapshotBatch resolveAsActor(String tenantId, String userId,
                    List<String> ids, List<String> properties) {
                TestWorkItemSnapshot item = new TestWorkItemSnapshot();
                item.setTestWorkItemId(ids.get(0));
                item.setTestDefinitionCode("TEST-1");
                item.setTaskStatus("pending_test");
                item.setOutcomeCode("SUCCESS");
                item.setProperties(Collections.emptyList());
                TestWorkItemSnapshotBatch batch = new TestWorkItemSnapshotBatch();
                batch.setItems(Collections.singletonList(item));
                return batch;
            }

            @Override
            public List<ElnSourceSnapshot> resolveConfiguredAsActor(String tenantId, String userId,
                    List<String> ids, List<ElnSourcePropertyRef> properties) {
                assertEquals("0", tenantId);
                assertEquals("user-1", userId);
                assertEquals(Collections.singletonList(new ElnSourcePropertyRef("test.basis", 1)), properties);
                return Collections.singletonList(source);
            }
        };
    }

    private void createTables() {
        String technical = "f_id text PRIMARY KEY, f_tenant_id text DEFAULT '0', f_version integer DEFAULT 0, "
                + "f_creator_user_id text, f_creator_time timestamp(0), f_last_modify_user_id text, "
                + "f_last_modify_time timestamp(0), f_delete_user_id text, f_delete_time timestamp(0), f_delete_mark integer DEFAULT 0";
        database.execute("CREATE TABLE eln_record (" + technical + ", jilu_bianhao text, jianyan_xiang_id text, "
                + "muban_id text, muban_bianhao text, muban_banben text, chuangjian_caozuo_bianma text, dangqian_xiuding_id text)");
        database.execute("CREATE TABLE eln_record_revision (" + technical + ", dianzi_jilu_id text, xiuding_hao integer, "
                + "laiyuan_xiuding_id text, xiuding_caozuo_bianma text, jilu_wendang text, biz_status text)");
        database.execute("CREATE TABLE eln_shared_fill_operation (" + technical + ", operation_code text, "
                + "request_digest text, operation_status text, result_json text, operator_user_id text, "
                + "UNIQUE (f_tenant_id, operation_code))");
        database.execute("CREATE TABLE eln_field_version (" + technical + ", ziduan_dingyi_id text, banben_hao integer, "
                + "ziduan_mingcheng text, shuju_leixing text, danwei text, shifou_bitian integer, pici_moshi text, "
                + "bangding_peizhi jsonb, kongjian_peizhi jsonb, recommended_checkbox_group_code text, "
                + "recommended_checkbox_min_selected integer, recommended_checkbox_max_selected integer, biz_status text, zuofei_yuanyin text)");
        database.execute("CREATE TABLE eln_template_binding (f_id text PRIMARY KEY, f_tenant_id text DEFAULT '0', "
                + "f_version integer DEFAULT 0, f_creator_user_id text, f_creator_time timestamp(0), "
                + "f_last_modify_user_id text, f_last_modify_time timestamp(0), f_delete_user_id text, "
                + "f_delete_time timestamp(0), f_delete_mark integer DEFAULT 0, muban_id text, muban_bianhao text, "
                + "ziduan_dingyi_id text, ziduan_bianma text, ziduan_banben_id text, kongjian_biaoqian text, "
                + "kongjian_bieming text, kongjian_leixing text, kongjian_juese text, yinyong_shili_id text, "
                + "repeat_group_code text, repeat_prototype_row_id text, checkbox_group_code text, step_id text, "
                + "bangding_zhuangtai text, caozuo_bianma text, laiyuan_bangding_id text, daoqi_shijian timestamp(0), "
                + "zhuangtai_yuanyin text)");
        database.execute("CREATE TABLE eln_record_field_value (" + technical + ", dianzi_jilu_id text, xiuding_id text, "
                + "muban_id text, muban_bangding_id text, ziduan_dingyi_id text, ziduan_bianma text, ziduan_banben_id text, "
                + "bangding_juese text, bangding_zhuangtai text, zhi_wenben text, zhi_shuzhi numeric(38,18), zhi_riqi timestamp(0), "
                + "zhi_laiyuan text, yuan_shuxing text, yuan_shuxing_banben integer, yuan_quzhi_zhuangtai text, "
                + "yuan_quzhi_yuanyin text, yuan_xueyuan text, yuan_yinyong_leixing text, yuan_yinyong_id text, yuan_huoqu_shijian timestamp(0), "
                + "kuamo_laiyuan_jilu_id text, kuamo_laiyuan_xiuding_id text, kuamo_laiyuan_zhi_id text, "
                + "kuamo_laiyuan_ziduan_banben_id text, kuamo_huoqu_shijian timestamp(0), "
                + "jisuan_zhuangtai text, jisuan_xiaoxi text, jisuan_shijian timestamp(0))");
    }

    private void seedRecord() {
        database.execute("INSERT INTO eln_record (f_id,jilu_bianhao,jianyan_xiang_id,muban_id,dangqian_xiuding_id,f_version) "
                + "VALUES ('record-1','ELN-1','item-1','template-1','revision-1',4)");
        database.execute("INSERT INTO eln_record_revision (f_id,dianzi_jilu_id,xiuding_hao,jilu_wendang,biz_status) "
                + "VALUES ('revision-1','record-1',1,'revision-1.docx','DRAFT')");
        database.execute("INSERT INTO eln_field_version (f_id,ziduan_dingyi_id,banben_hao,ziduan_mingcheng,shuju_leixing,shifou_bitian,biz_status,bangding_peizhi) "
                + "VALUES ('version-1','definition-1',1,'Basis','TEXT',0,'PUBLISHED',"
                + "'{\"read\":{\"source\":\"LIMS_TASK\",\"property\":\"test.basis\",\"propertyRevision\":1}}')");
        database.execute("INSERT INTO eln_template_binding (f_id,muban_id,ziduan_dingyi_id,ziduan_bianma,"
                + "ziduan_banben_id,kongjian_juese,bangding_zhuangtai) VALUES "
                + "('binding-1','template-1','definition-1','basis','version-1','PRIMARY','ACTIVE')");
        database.execute("INSERT INTO eln_record_field_value (f_id,dianzi_jilu_id,xiuding_id,muban_id,muban_bangding_id,"
                + "ziduan_dingyi_id,ziduan_bianma,ziduan_banben_id,bangding_juese,bangding_zhuangtai,zhi_wenben,"
                + "zhi_laiyuan,yuan_shuxing,yuan_shuxing_banben,yuan_quzhi_zhuangtai,yuan_yinyong_leixing,yuan_yinyong_id,yuan_huoqu_shijian) "
                + "VALUES ('value-1','record-1','revision-1','template-1','binding-1','definition-1','basis','version-1',"
                + "'PRIMARY','ACTIVE','old basis','LIMS_TASK','test.basis',1,'SUCCESS','LIMS_TASK','item-1','2026-09-01 10:00:00')");
    }
}