package com.battery.business.web;

import static org.junit.jupiter.api.Assertions.*;
import java.util.List;
import java.util.Map;
import java.util.LinkedHashMap;
import java.util.UUID;
import com.alibaba.fastjson2.JSON;
import com.battery.business.domain.TTestDataRevisionAction;
import com.battery.business.mapper.TTestMapper;
import com.battery.business.service.ITestConsoleDataService;
import com.battery.business.service.impl.TestConsoleDataServiceImpl;
import org.apache.ibatis.datasource.unpooled.UnpooledDataSource;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.Test;
import org.springframework.aop.framework.ProxyFactory;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.annotation.AnnotationTransactionAttributeSource;
import org.springframework.transaction.interceptor.TransactionInterceptor;
import org.springframework.test.util.ReflectionTestUtils;

/** 使用生产 Mapper 和 Spring 事务验证导入；内存数据库不连接共享开发库。 */
class ConsoleSingleSheetImportDatabaseTest
{
    private java.sql.Connection keepAlive;
    private TTestController controller;
    private TTestMapper mapper;
    private ITestConsoleDataService service;
    private JdbcTemplate jdbc;

    @BeforeEach void setUp() throws Exception
    {
        var source = new UnpooledDataSource("org.h2.Driver", "jdbc:h2:mem:" + UUID.randomUUID() + ";MODE=MySQL;DATABASE_TO_LOWER=TRUE", "sa", "");
        keepAlive = source.getConnection();
        jdbc = new JdbcTemplate(source);
        jdbc.execute("create table sys_user(user_id bigint, nick_name varchar(64), del_flag varchar(1))");
        jdbc.execute("create table t_test_type(test_type_id bigint, test_type_name varchar(64), del_flag int)");
        jdbc.execute("create table t_test(test_id bigint primary key, test_name varchar(100), creator_id bigint, principal_id bigint, test_type_id bigint, test_purpose varchar(100), test_status varchar(20), reception_status varchar(20), monitor_timeout_minutes int, started_at_utc timestamp, reception_boundary_at_utc timestamp, ended_at_utc timestamp, del_flag int default 0, create_by varchar(64), create_time timestamp, update_by varchar(64), update_time timestamp, remark varchar(100), logic_step_mappings_json varchar(100), console_detail_json varchar(100))");
        jdbc.execute("insert into t_test(test_id,test_name,test_status,reception_status) values(1,'导入测试','COMPLETED','ENDED')");
        jdbc.execute("create table t_test_battery(test_battery_id bigint primary key, test_id bigint, battery_no varchar(100), del_flag int default 0)");
        jdbc.execute("insert into t_test_battery values(4,1,'BAT-1',0)");
        jdbc.execute("create table t_test_channel_binding(binding_id bigint, test_battery_id bigint, channel_id bigint)");
        jdbc.execute("insert into t_test_channel_binding values(5,4,11)");
        jdbc.execute("create table t_detection_channel(channel_id bigint, cabinet_id bigint, devtype int, devid int, subdevid int, chlid int)");
        jdbc.execute("insert into t_detection_channel values(11,1,27,1,1,2)");
        jdbc.execute("create table t_detection_cabinet(cabinet_id bigint, adapter_code varchar(64))");
        jdbc.execute("insert into t_detection_cabinet values(1,'adapter-a')");
        jdbc.execute("create table t_test_data_revision_action(action_id bigint auto_increment primary key, test_id bigint, data_type varchar(20), operation_type varchar(20), scope_summary varchar(200), identity_summary json, before_content_json json, changes_summary json, reason varchar(200), operator_id bigint, operator_name varchar(64), created_at_utc timestamp)");
        jdbc.execute("alter table t_test_data_revision_action add constraint chk_data_revision_type check (data_type in ('REALTIME','STEP','CYCLE'))");
        org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(keepAlive,
                new ClassPathResource("db/migration-final/V9__workbook_import_revision.sql"));
        String common = "test_id bigint not null, test_battery_id bigint not null, binding_id bigint not null, adapter_code varchar(64) not null, source_identity varchar(256) not null, source_record_id varchar(128), server_received_at_utc timestamp(3), cycle_no int, content_sha256 binary(32) not null, extension_measurements_json varchar(2000)";
        String metrics = "step_no int, step_name varchar(191), step_type varchar(64), voltage_v decimal(30,12), current_a decimal(30,12), capacity_ah decimal(30,12), energy_wh decimal(30,12) check (energy_wh <> 999)";
        jdbc.execute("create table t_test_realtime_record(record_id bigint auto_increment primary key," + common + "," + metrics + ", cabinet_devtype int, cabinet_devid int, cabinet_subdevid int, cabinet_chlid int, cabinet_sequence_id bigint, cabinet_device_time_utc timestamp(3), cabinet_device_time_raw varchar(64), cabinet_adapter_timezone_id varchar(64), capture_slot_utc timestamp(3) not null, temperature decimal(30,12), positive_potential decimal(30,12), negative_potential decimal(30,12), collector_id bigint, collector_channel_code varchar(100), collector_device_time_utc timestamp(3), unique(test_id,test_battery_id,capture_slot_utc))");
        // 从 V7 的非空槽位起点执行生产迁移，覆盖存量库升级路径。
        org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(keepAlive,
                new ClassPathResource("db/migration-final/V8__import_records_without_polling_slots.sql"));
        String deviceTime = "dedupe_key varchar(512) not null, device_time_utc timestamp(3) not null, device_time_raw varchar(64) not null, adapter_timezone_id varchar(64) not null, unique(test_battery_id,source_identity,dedupe_key)";
        jdbc.execute("create table t_test_step_record(step_record_id bigint auto_increment primary key," + common + "," + metrics + "," + deviceTime + ")");
        jdbc.execute("create table t_test_cycle_record(cycle_record_id bigint auto_increment primary key," + common + ",charge_capacity_ah decimal(30,12),discharge_capacity_ah decimal(30,12),charge_energy_wh decimal(30,12),discharge_energy_wh decimal(30,12),efficiency decimal(30,12)," + deviceTime + ")");
        var factory = new SqlSessionFactoryBean(); factory.setDataSource(source);
        factory.setTypeAliasesPackage("com.battery.business.domain");
        factory.setMapperLocations(new ClassPathResource("mapper/battery/TTestMapper.xml"));
        mapper = new SqlSessionTemplate(factory.getObject()).getMapper(TTestMapper.class);
        var proxy = new ProxyFactory(new TestConsoleDataServiceImpl(mapper));
        proxy.addAdvice(new TransactionInterceptor(new DataSourceTransactionManager(source), new AnnotationTransactionAttributeSource()));
        service = (ITestConsoleDataService) proxy.getProxy();
        controller = new TTestController() {
            @Override public Long getUserId() { return 1L; }
            @Override public String getUsername() { return "tester"; }
        };
        ReflectionTestUtils.setField(controller, "consoleDataService", service);
    }

    @AfterEach void closeDatabase() throws Exception { if (keepAlive != null) keepAlive.close(); }

    @Test void threeTypesPersistActualBindingsTimesAndNumbersAndSkipRetries()
    {
        for (String type : List.of("REALTIME", "STEP", "CYCLE"))
        {
            assertEquals(1, submit(type, List.of(row())).get("importedCount"));
            assertEquals(1, submit(type, List.of(row())).get("skippedCount"));
            var records = mapper.selectImportDataRecords(1L, type);
            assertEquals(1, records.size());
            assertEquals("17", records.get(0).get("source_record_id"));
            assertEquals("adapter-a:27:1:1:2", records.get(0).get("source_identity"));
            String timeColumn = type.equals("REALTIME") ? "cabinet_device_time_raw" : "device_time_raw";
            assertEquals("2023-05-08 11:42:11", records.get(0).get(timeColumn));
            String storedTimeColumn = type.equals("REALTIME") ? "cabinet_device_time_utc" : "device_time_utc";
            assertEquals(1683517331000L, ((java.util.Date) records.get(0).get(storedTimeColumn)).getTime());
        }
        assertEquals("17", mapper.selectPersistedFinalRealtimeRecords(1L).get(0).get("recordno"));
        assertEquals(3, service.selectActions(new TTestDataRevisionAction()).size());
    }

    @Test void databaseFailureRollsBackEarlierRowsAndTheRevision()
    {
        var failing = row(); failing.put("recordNo", "18"); failing.put("recordedAt", 1683517391000L); failing.put("energy", 999);
        assertThrows(RuntimeException.class, () -> submit("REALTIME", List.of(row(), failing)));
        assertTrue(mapper.selectImportDataRecords(1L, "REALTIME").isEmpty());
        assertTrue(service.selectActions(new TTestDataRevisionAction()).isEmpty());
    }

    @Test void workbookImportsThreeSheetsWithOneRevisionAndSkipsRetry()
    {
        var sheets = Map.of("REALTIME", List.of(row(), row()), "STEP", List.of(row()), "CYCLE", List.of(row()));
        var result = submitWorkbook(sheets);
        assertEquals(3, result.get("importedCount"));
        assertEquals(1, result.get("skippedCount"));
        assertNotNull(result.get("actionId"));
        for (String type : List.of("REALTIME", "STEP", "CYCLE"))
        {
            assertEquals(1, mapper.selectImportDataRecords(1L, type).size());
            var query = new TTestDataRevisionAction(); query.setDataType(type);
            assertEquals(1, service.selectActions(query).size());
        }
        var actions = service.selectActions(new TTestDataRevisionAction());
        assertEquals(1, actions.size());
        assertEquals("ALL", actions.get(0).getDataType());
        assertEquals("三页验收", actions.get(0).getReason());
        var retry = submitWorkbook(sheets);
        assertEquals(0, retry.get("importedCount"));
        assertEquals(4, retry.get("skippedCount"));
        assertNull(retry.get("actionId"));
        assertEquals(1, service.selectActions(new TTestDataRevisionAction()).size());
    }

    private com.ruoyi.common.core.domain.AjaxResult submitWorkbook(Map<String, List<Map<String, Object>>> sheets)
    {
        return controller.dataAction(1L, new TTestController.ConsoleDataActionRequest("ALL", "IMPORT", "三 Sheet 导入", "[]",
                JSON.toJSONString(sheets), "三页验收", List.of()));
    }

    @Test void workbookLateDatabaseFailureRollsBackAllSheetsAndRevision()
    {
        var cycle = row(); cycle.put("dischargeEnergy", 999);
        jdbc.execute("alter table t_test_cycle_record add check (discharge_energy_wh <> 999)");
        assertThrows(RuntimeException.class, () -> submitWorkbook(Map.of("REALTIME", List.of(row()), "STEP", List.of(row()), "CYCLE", List.of(cycle))));
        assertWorkbookEmpty();
    }

    @Test void workbookMissingEmptyInvalidOrConflictingSheetNeverWritesAnySheet()
    {
        var sheets = new LinkedHashMap<String, List<Map<String, Object>>>();
        sheets.put("REALTIME", List.of(row())); sheets.put("STEP", List.of(row()));
        assertThrows(RuntimeException.class, () -> submitWorkbook(sheets));
        assertWorkbookEmpty();
        sheets.put("CYCLE", List.of());
        assertThrows(RuntimeException.class, () -> submitWorkbook(sheets));
        assertWorkbookEmpty();
        var invalid = row(); invalid.put("cycleNo", 0); invalid.put("sourceRow", 23);
        sheets.put("CYCLE", List.of(invalid));
        assertTrue(assertThrows(RuntimeException.class, () -> submitWorkbook(sheets)).getMessage().contains("循环工作表，第23行"));
        assertWorkbookEmpty();
        var conflict = row(); conflict.put("dischargeCapacity", 88);
        sheets.put("CYCLE", List.of(row(), conflict));
        assertTrue(assertThrows(RuntimeException.class, () -> submitWorkbook(sheets)).getMessage().contains("冲突拒绝"));
        assertWorkbookEmpty();
    }

    @Test void workbookRejectsDifferentBatterySetsEvenWhenBothBatteriesBelongToTest()
    {
        jdbc.execute("insert into t_test_battery values(6,1,'BAT-2',0)");
        jdbc.execute("insert into t_test_channel_binding values(7,6,11)");
        var other = row(); other.put("batteryNo", "BAT-2");
        var sheets = Map.of("REALTIME", List.of(row()), "STEP", List.of(row()), "CYCLE", List.of(other));
        assertTrue(assertThrows(RuntimeException.class, () -> submitWorkbook(sheets)).getMessage().contains("电池号集合不一致"));
        assertWorkbookEmpty();
        other.put("batteryNo", "OTHER");
        assertTrue(assertThrows(RuntimeException.class, () -> submitWorkbook(sheets)).getMessage().contains("不属于当前测试"));
        assertWorkbookEmpty();
    }

    @Test void workbookConflictWithExistingCyclePreservesFactsAndRejectsOtherSheets()
    {
        submit("CYCLE", List.of(row()));
        var conflict = row(); conflict.put("dischargeCapacity", 88);
        assertThrows(RuntimeException.class, () -> submitWorkbook(Map.of("REALTIME", List.of(row()), "STEP", List.of(row()), "CYCLE", List.of(conflict))));
        assertTrue(mapper.selectImportDataRecords(1L, "REALTIME").isEmpty());
        assertTrue(mapper.selectImportDataRecords(1L, "STEP").isEmpty());
        assertEquals(1, mapper.selectImportDataRecords(1L, "CYCLE").size());
        assertEquals(1, service.selectActions(new TTestDataRevisionAction()).size());
    }

    @Test void workbookCannotImportBeforeTestCompletionOrWithoutReason()
    {
        var sheets = Map.of("REALTIME", List.of(row()), "STEP", List.of(row()), "CYCLE", List.of(row()));
        assertThrows(RuntimeException.class, () -> controller.dataAction(1L, new TTestController.ConsoleDataActionRequest("ALL", "IMPORT", "三 Sheet 导入", "[]", JSON.toJSONString(sheets), " ", List.of())));
        jdbc.update("update t_test set test_status='RUNNING', reception_status='RECEIVING' where test_id=1");
        assertThrows(RuntimeException.class, () -> submitWorkbook(sheets));
        assertWorkbookEmpty();
    }

    @Test void workbookRejectsExistingExtraStepMeasurementInsteadOfSkippingIt()
    {
        submit("STEP", List.of(row()));
        jdbc.update("update t_test_step_record set extension_measurements_json=?", "{\"stepDuration\":3752,\"auxTemperature\":99}");
        var sheets = Map.of("REALTIME", List.of(row()), "STEP", List.of(row()), "CYCLE", List.of(row()));
        assertTrue(assertThrows(RuntimeException.class, () -> submitWorkbook(sheets)).getMessage().contains("冲突拒绝"));
        assertTrue(mapper.selectImportDataRecords(1L, "REALTIME").isEmpty());
        assertTrue(mapper.selectImportDataRecords(1L, "CYCLE").isEmpty());
        assertEquals(1, service.selectActions(new TTestDataRevisionAction()).size());
    }

    private void assertWorkbookEmpty()
    {
        for (String type : List.of("REALTIME", "STEP", "CYCLE")) assertTrue(mapper.selectImportDataRecords(1L, type).isEmpty());
        assertTrue(service.selectActions(new TTestDataRevisionAction()).isEmpty());
    }

    @Test void differentRecordsInTheSameSecondPreserveDeviceTime()
    {
        var next = row(); next.put("recordNo", "18"); next.put("sequenceId", 18); next.put("stepNo", 2);
        assertEquals(2, submit("REALTIME", List.of(row(), next)).get("importedCount"));
        assertEquals(2, submit("REALTIME", List.of(row(), next)).get("skippedCount"));
        var records = mapper.selectImportDataRecords(1L, "REALTIME");
        assertEquals(2, records.size());
        for (var record : records) {
            assertNull(record.get("capture_slot_utc"));
            assertEquals(1683517331000L, ((java.util.Date) record.get("cabinet_device_time_utc")).getTime());
        }
    }

    @Test
    @org.junit.jupiter.api.condition.EnabledIfEnvironmentVariable(named = "TICKET03_SAMPLE_JSON", matches = ".+")
    void suppliedPartyWorkbookImportsAllRowsAndSkipsEveryRetry() throws Exception
    {
        var sample = JSON.parseObject(java.nio.file.Files.readString(java.nio.file.Path.of(System.getenv("TICKET03_SAMPLE_JSON"))));
        for (String type : List.of("REALTIME", "STEP", "CYCLE"))
        {
            List<Map<String, Object>> rows = (List) sample.getJSONArray(type).toJavaList(Map.class);
            int count = switch (type) { case "REALTIME" -> 7605; case "STEP" -> 129; default -> 33; };
            assertEquals(count, submit(type, rows).get("importedCount"));
            assertEquals(count, mapper.selectImportDataRecords(1L, type).size());
            assertEquals(count, submit(type, rows).get("skippedCount"));
        }
        assertEquals(3, service.selectActions(new TTestDataRevisionAction()).size());
    }

    private Map<String, Object> row()
    {
        var row = new LinkedHashMap<String, Object>(Map.of("recordNo", "17", "batteryNo", "BAT-1", "recordedAt", 1683517331000L,
                "cycleNo", 1, "stepNo", 1, "stepName", "恒流放电", "sequenceId", 17, "capacity", 8.5122));
        row.put("deviceTimeRaw", "2023-05-08 11:42:11"); row.put("adapterTimezoneId", "Asia/Hong_Kong"); row.put("stepDuration", 3752);
        return row;
    }

    private com.ruoyi.common.core.domain.AjaxResult submit(String type, List<Map<String, Object>> rows)
    {
        return controller.dataAction(1L, new TTestController.ConsoleDataActionRequest(type, "IMPORT", "单页导入", "[]",
                JSON.toJSONString(rows), "开发测试", List.of()));
    }
}