package com.battery.business.web;

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

import java.math.BigDecimal;
import java.nio.charset.StandardCharsets;
import java.security.MessageDigest;
import java.time.Instant;
import java.util.Date;
import java.util.HexFormat;
import java.util.Map;
import java.util.UUID;
import org.apache.ibatis.datasource.unpooled.UnpooledDataSource;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.jdbc.core.JdbcTemplate;
import com.battery.business.domain.TTestRealtimeRecord;
import com.battery.business.mapper.TTestMapper;

/**
 * 逐行接收的落库语义（生产 Mapper + H2 内存库）：
 * 设备数据行不占系统轮询槽位，所以同一绝对秒内的多行可以各自成立；
 * 来源行身份（厂商测试编号 + 协议序号）承担幂等与冲突判定。
 */
public class VendorSamplePersistenceDatabaseTest
{
    private static final String IDENTITY = "neware76:21:36:1:2";

    private java.sql.Connection keepAlive;
    private JdbcTemplate jdbc;
    private TTestMapper mapper;

    @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);
        // H2 无 MySQL 的 HEX()；测试中补一个等价别名，生产 SQL 仍在 MySQL 上验证。
        jdbc.execute("create alias HEX for 'com.battery.business.web.VendorSamplePersistenceDatabaseTest.hexOf'");
        jdbc.execute("create table t_test_realtime_record(record_id bigint auto_increment primary key,"
                + "test_id bigint, test_battery_id bigint, binding_id bigint, adapter_code varchar(64),"
                + "source_identity varchar(256) not null, source_record_id varchar(128),"
                + "collector_id bigint, collector_channel_code varchar(100), collector_sequence_id bigint,"
                + "collector_device_time_utc timestamp(3), collector_device_time_raw varchar(64),"
                + "collector_adapter_timezone_id varchar(64),"
                + "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), cycle_no int, step_no int, step_type varchar(64),"
                + "step_name varchar(191), voltage_v decimal(30,12), current_a decimal(30,12),"
                + "capacity_ah decimal(30,12), energy_wh decimal(30,12), temperature decimal(30,12),"
                + "positive_potential decimal(30,12), negative_potential decimal(30,12),"
                + "extension_measurements_json varchar(2000), capture_slot_utc timestamp(3) null, "
                + "server_received_at_utc timestamp(3) not null, content_sha256 binary(32) not null,"
                // 轮询槽位唯一键允许多个空值：逐行接收因此可以把同一秒的多行都保存下来。
                + "unique(test_id, test_battery_id, capture_slot_utc))");

        SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
        factory.setDataSource(source);
        factory.setTypeAliasesPackage("com.battery.business.domain");
        factory.setMapperLocations(new org.springframework.core.io.ClassPathResource("mapper/battery/TTestMapper.xml"));
        mapper = new SqlSessionTemplate(factory.getObject()).getMapper(TTestMapper.class);
    }

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

    @Test void sameSecondRowsAreKeptSeparatelyAndRepeatIsRecognisedBySourceRowIdentity()
    {
        TTestRealtimeRecord first = row(7437L, "2026-09-28 09:55:56", "2026-09-28T01:55:56Z");
        assertEquals(1, mapper.insertRealtimeRecord(first));

        // 同一绝对秒的第二行：槽位为空，唯一键不冲突，两行都成立。
        TTestRealtimeRecord second = row(7438L, "2026-09-28 09:55:56", "2026-09-28T01:55:56Z");
        assertEquals(1, mapper.insertRealtimeRecord(second));

        assertEquals(2, jdbc.queryForObject("select count(*) from t_test_realtime_record", Integer.class));
        // 幂等与冲突按来源行身份判定：命中同身份即视为重复，由调用方比较内容摘要。
        assertEquals(hex(first.getContentSha256()),
                mapper.selectRealtimeRecordHashBySourceRecord(4L, IDENTITY, "neware:440:7437"));
        assertNull(mapper.selectRealtimeRecordHashBySourceRecord(4L, IDENTITY, "neware:440:7439"));
        assertTrue(!hex(first.getContentSha256()).equals(hex(second.getContentSha256())));
    }

    @Test void deviceRowKeepsVendorRawTimeServerReceiveTimeAndNoPollingSlot()
    {
        mapper.insertRealtimeRecord(row(7437L, "2026-09-28 09:55:56", "2026-09-28T01:55:56Z"));

        Map<String, Object> stored = jdbc.queryForMap("select source_record_id, source_identity,"
                + " cabinet_device_time_raw, cabinet_device_time_utc, server_received_at_utc, capture_slot_utc,"
                + " cabinet_adapter_timezone_id, extension_measurements_json, voltage_v"
                + " from t_test_realtime_record");
        assertEquals("neware:440:7437", stored.get("source_record_id"));
        assertEquals(IDENTITY, stored.get("source_identity"));
        // 设备时间按适配器时区解释后以 UTC 保存，同时保留原始时间文本与服务器接收时间。
        assertEquals("2026-09-28 09:55:56", stored.get("cabinet_device_time_raw"));
        assertEquals("Asia/Hong_Kong", stored.get("cabinet_adapter_timezone_id"));
        assertEquals(Instant.parse("2026-09-28T01:55:56Z"),
                ((java.sql.Timestamp) stored.get("cabinet_device_time_utc")).toInstant());
        assertEquals(Instant.parse("2026-09-28T02:01:00Z"),
                ((java.sql.Timestamp) stored.get("server_received_at_utc")).toInstant());
        assertNull(stored.get("capture_slot_utc"));
        // H2 的 JSON 列会把 JSON 文本再引一层，这里只断言内容确实保存下来。
        assertTrue(String.valueOf(stored.get("extension_measurements_json")).contains("relativeTimeSeconds"));
        assertEquals(0, new BigDecimal("1.3733").compareTo((BigDecimal) stored.get("voltage_v")));
    }

    /** 供 H2 别名使用的 HEX 等价实现；H2 没有 MySQL 的 HEX()。 */
    public static String hexOf(byte[] value)
    {
        return value == null ? null : HexFormat.of().formatHex(value);
    }

    private TTestRealtimeRecord row(long sequenceId, String raw, String deviceTimeUtc)
    {
        TTestRealtimeRecord row = new TTestRealtimeRecord();
        row.setTestId(1L); row.setTestBatteryId(4L); row.setBindingId(2L); row.setAdapterCode("neware76");
        row.setSourceIdentity(IDENTITY); row.setSourceRecordId("neware:440:" + sequenceId);
        row.setCabinetDevtype(21); row.setCabinetDevid(36); row.setCabinetSubdevid(1); row.setCabinetChlid(2);
        row.setCabinetSequenceId(sequenceId);
        row.setCabinetDeviceTimeUtc(Date.from(Instant.parse(deviceTimeUtc)));
        row.setCabinetDeviceTimeRaw(raw);
        row.setCabinetAdapterTimezoneId("Asia/Hong_Kong");
        row.setCaptureSlotUtc(null);
        row.setServerReceivedAtUtc(Date.from(Instant.parse("2026-09-28T02:01:00Z")));
        row.setCycleNo(3); row.setStepNo(12); row.setStepType("2");
        row.setVoltageV(new BigDecimal("1.3733")); row.setCurrentA(new BigDecimal("-1.1889"));
        row.setCapacityAh(new BigDecimal("1.398612")); row.setEnergyWh(new BigDecimal("0.030648"));
        row.setExtensionMeasurementsJson("{\"relativeTimeSeconds\":300}");
        // 内容摘要把协议序号算进去，使同秒不同行的内容彼此不同。
        row.setContentSha256(sha256("neware:440:" + sequenceId));
        return row;
    }

    private static String hex(byte[] value)
    {
        return HexFormat.of().formatHex(value);
    }

    private static byte[] sha256(String value)
    {
        try
        {
            return MessageDigest.getInstance("SHA-256").digest(value.getBytes(StandardCharsets.UTF_8));
        }
        catch (Exception exception)
        {
            throw new IllegalStateException(exception);
        }
    }

}