SET NAMES utf8mb4;

CREATE TABLE t_battery_group (
    group_id BIGINT NOT NULL AUTO_INCREMENT,
    group_name VARCHAR(100) NOT NULL,
    model VARCHAR(100) NOT NULL,
    purpose VARCHAR(500) NULL,
    z VARCHAR(100) NULL,
    h VARCHAR(100) NULL,
    f VARCHAR(100) NULL,
    a VARCHAR(100) NULL,
    b VARCHAR(100) NULL,
    status CHAR(1) NOT NULL DEFAULT '0',
    del_flag VARCHAR(20) NOT NULL DEFAULT '0',
    create_by VARCHAR(64) NOT NULL DEFAULT '',
    create_time DATETIME NULL,
    update_by VARCHAR(64) NOT NULL DEFAULT '',
    update_time DATETIME NULL,
    remark VARCHAR(500) NULL,
    PRIMARY KEY (group_id),
    UNIQUE KEY uk_battery_group_name_del (group_name, del_flag),
    KEY idx_battery_group_status_time (status, del_flag, create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='电池组';

CREATE TABLE t_battery_group_battery (
    relation_id BIGINT NOT NULL AUTO_INCREMENT,
    group_id BIGINT NOT NULL,
    battery_no VARCHAR(100) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    del_flag VARCHAR(20) NOT NULL DEFAULT '0',
    PRIMARY KEY (relation_id),
    UNIQUE KEY uk_battery_no_del (battery_no, del_flag),
    KEY idx_group_battery (group_id, del_flag)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='电池组电池号';

CREATE TABLE t_battery_group_field (
    field_id BIGINT NOT NULL AUTO_INCREMENT,
    field_name VARCHAR(100) NOT NULL,
    field_type VARCHAR(20) NOT NULL,
    status CHAR(1) NOT NULL DEFAULT '0',
    del_flag BIGINT NOT NULL DEFAULT 0,
    create_by VARCHAR(64) NOT NULL DEFAULT '',
    create_time DATETIME NULL,
    update_by VARCHAR(64) NOT NULL DEFAULT '',
    update_time DATETIME NULL,
    remark VARCHAR(500) NULL,
    PRIMARY KEY (field_id),
    UNIQUE KEY uk_battery_group_field_name_del (field_name, del_flag),
    KEY idx_battery_group_field_status (status, create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='电池组字段';

CREATE TABLE t_battery_group_template (
    template_id BIGINT NOT NULL AUTO_INCREMENT,
    template_name VARCHAR(100) NOT NULL,
    create_by VARCHAR(64) NOT NULL DEFAULT '',
    create_time DATETIME NULL,
    update_by VARCHAR(64) NOT NULL DEFAULT '',
    update_time DATETIME NULL,
    PRIMARY KEY (template_id),
    UNIQUE KEY uk_battery_group_template_name (template_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='电池组字段模板';

CREATE TABLE t_battery_group_template_field (
    relation_id BIGINT NOT NULL AUTO_INCREMENT,
    template_id BIGINT NOT NULL,
    field_id BIGINT NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    create_by VARCHAR(64) NOT NULL DEFAULT '',
    create_time DATETIME NULL,
    del_flag BIGINT NOT NULL DEFAULT 0,
    PRIMARY KEY (relation_id),
    UNIQUE KEY uk_template_field_del (template_id, field_id, del_flag),
    KEY idx_template_field_order (template_id, del_flag, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='模板字段关系';

CREATE TABLE t_battery_group_relation_field (
    relation_id BIGINT NOT NULL AUTO_INCREMENT,
    group_id BIGINT NOT NULL,
    template_id BIGINT NOT NULL,
    battery_group_field_id BIGINT NOT NULL,
    del_flag VARCHAR(20) NOT NULL DEFAULT '0',
    PRIMARY KEY (relation_id),
    UNIQUE KEY uk_group_field_del (group_id, battery_group_field_id, del_flag),
    KEY idx_group_relation_field (group_id, del_flag)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='电池组启用字段';

CREATE TABLE t_device_adapter (
    adapter_id BIGINT NOT NULL AUTO_INCREMENT,
    adapter_code VARCHAR(64) NOT NULL,
    adapter_name VARCHAR(100) NOT NULL,
    adapter_type VARCHAR(32) NOT NULL,
    host VARCHAR(255) NOT NULL,
    port INT NOT NULL,
    poll_interval_ms INT NOT NULL DEFAULT 5000,
    timezone_id VARCHAR(64) NOT NULL DEFAULT 'Asia/Hong_Kong',
    capabilities JSON NOT NULL,
    status CHAR(1) NOT NULL DEFAULT '0',
    lease_owner VARCHAR(128) NULL,
    lease_until_utc DATETIME(3) NULL,
    create_by VARCHAR(64) NOT NULL DEFAULT '',
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_by VARCHAR(64) NOT NULL DEFAULT '',
    update_time DATETIME NULL,
    remark VARCHAR(500) NULL,
    del_flag BIGINT NOT NULL DEFAULT 0,
    PRIMARY KEY (adapter_id),
    UNIQUE KEY uk_adapter_code_del (adapter_code, del_flag),
    KEY idx_adapter_enabled (status, del_flag)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='设备适配器实例';

CREATE TABLE t_detection_cabinet (
    cabinet_id BIGINT NOT NULL AUTO_INCREMENT,
    adapter_id BIGINT NOT NULL,
    cabinet_code VARCHAR(64) NOT NULL,
    cabinet_name VARCHAR(100) NOT NULL,
    manufacturer VARCHAR(100) NULL,
    model VARCHAR(100) NULL,
    capacity INT NOT NULL DEFAULT 1,
    channel_count INT NOT NULL DEFAULT 1,
    vendor_device_type INT NOT NULL,
    vendor_device_id INT NOT NULL,
    ip_address VARCHAR(45) NULL,
    mac_address VARCHAR(64) NULL,
    online_status VARCHAR(32) NOT NULL DEFAULT 'OFFLINE',
    last_seen_at_utc DATETIME(3) NULL,
    status CHAR(1) NOT NULL DEFAULT '0',
    create_by VARCHAR(64) NOT NULL DEFAULT '',
    create_time DATETIME NULL,
    update_by VARCHAR(64) NOT NULL DEFAULT '',
    update_time DATETIME NULL,
    remark VARCHAR(500) NULL,
    del_flag BIGINT NOT NULL DEFAULT 0,
    PRIMARY KEY (cabinet_id),
    UNIQUE KEY uk_cabinet_code_del (cabinet_code, del_flag),
    UNIQUE KEY uk_cabinet_vendor_identity (adapter_id, vendor_device_type, vendor_device_id, del_flag),
    KEY idx_cabinet_status (status, online_status, del_flag)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='新威检测柜';

CREATE TABLE t_detection_channel (
    channel_id BIGINT NOT NULL AUTO_INCREMENT,
    adapter_id BIGINT NOT NULL,
    cabinet_id BIGINT NOT NULL,
    devtype INT NOT NULL,
    devid INT NOT NULL,
    subdevid INT NOT NULL,
    chlid INT NOT NULL,
    display_no VARCHAR(64) NULL,
    online_status VARCHAR(32) NOT NULL DEFAULT 'UNKNOWN',
    work_status VARCHAR(32) NOT NULL DEFAULT 'UNKNOWN',
    last_seen_at_utc DATETIME(3) NULL,
    status CHAR(1) NOT NULL DEFAULT '0',
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME NULL,
    del_flag BIGINT NOT NULL DEFAULT 0,
    PRIMARY KEY (channel_id),
    UNIQUE KEY uk_channel_identity (adapter_id, devtype, devid, subdevid, chlid, del_flag),
    KEY idx_channel_cabinet (cabinet_id, status, del_flag),
    KEY idx_channel_observed (online_status, work_status, last_seen_at_utc)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='检测柜物理通道';

CREATE TABLE t_collector (
    collector_id BIGINT NOT NULL AUTO_INCREMENT,
    collector_code VARCHAR(64) NOT NULL,
    collector_name VARCHAR(100) NOT NULL,
    adapter_id BIGINT NULL,
    ip_address VARCHAR(45) NULL,
    mac_address VARCHAR(64) NULL,
    manufacturer VARCHAR(100) NULL,
    model VARCHAR(100) NULL,
    channel_count INT NOT NULL DEFAULT 0,
    online_status VARCHAR(32) NOT NULL DEFAULT 'UNKNOWN',
    status CHAR(1) NOT NULL DEFAULT '0',
    del_flag BIGINT NOT NULL DEFAULT 0,
    create_by VARCHAR(64) NOT NULL DEFAULT '',
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_by VARCHAR(64) NOT NULL DEFAULT '',
    update_time DATETIME NULL,
    remark VARCHAR(500) NULL,
    PRIMARY KEY (collector_id),
    UNIQUE KEY uk_collector_code_del (collector_code, del_flag),
    KEY idx_collector_status (status, online_status, del_flag)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='可选辅助采集器';

CREATE TABLE t_test_type (
    test_type_id BIGINT NOT NULL AUTO_INCREMENT,
    test_type_name VARCHAR(100) NOT NULL,
    status CHAR(1) NOT NULL DEFAULT '0',
    del_flag BIGINT NOT NULL DEFAULT 0,
    create_by VARCHAR(64) NOT NULL DEFAULT '',
    create_time DATETIME NULL,
    update_by VARCHAR(64) NOT NULL DEFAULT '',
    update_time DATETIME NULL,
    remark VARCHAR(500) NULL,
    PRIMARY KEY (test_type_id),
    UNIQUE KEY uk_test_type_name_del (test_type_name, del_flag),
    KEY idx_test_type_status (status, del_flag)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='测试类型';

CREATE TABLE t_test (
    test_id BIGINT NOT NULL AUTO_INCREMENT,
    test_name VARCHAR(100) NOT NULL,
    principal_id BIGINT NOT NULL,
    test_type_id BIGINT NOT NULL,
    test_purpose TEXT NOT NULL,
    test_status VARCHAR(32) NOT NULL DEFAULT 'WAITING',
    monitoring_status VARCHAR(32) NOT NULL DEFAULT 'NOT_MONITORED',
    monitor_timeout_minutes INT NOT NULL DEFAULT 30,
    started_at_utc DATETIME(3) NULL,
    ended_at_utc DATETIME(3) NULL,
    del_flag BIGINT NOT NULL DEFAULT 0,
    create_by VARCHAR(64) NOT NULL DEFAULT '',
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_by VARCHAR(64) NOT NULL DEFAULT '',
    update_time DATETIME NULL,
    remark VARCHAR(500) NULL,
    PRIMARY KEY (test_id),
    UNIQUE KEY uk_test_name_del (test_name, del_flag),
    KEY idx_test_status_time (test_status, monitoring_status, create_time),
    KEY idx_test_principal (principal_id, test_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='测试';

CREATE TABLE t_test_plan_step (
    plan_step_id BIGINT NOT NULL AUTO_INCREMENT,
    test_id BIGINT NOT NULL,
    cycle_no INT NOT NULL,
    step_no INT NOT NULL,
    step_type VARCHAR(64) NOT NULL,
    step_name VARCHAR(100) NOT NULL,
    parameters_json JSON NOT NULL,
    sort_order INT NOT NULL,
    PRIMARY KEY (plan_step_id),
    UNIQUE KEY uk_test_plan_step (test_id, cycle_no, step_no),
    KEY idx_test_plan_order (test_id, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='计划工步';

CREATE TABLE t_test_battery (
    relation_id BIGINT NOT NULL AUTO_INCREMENT,
    test_id BIGINT NOT NULL,
    group_id BIGINT NOT NULL,
    battery_no VARCHAR(100) NOT NULL,
    del_flag BIGINT NOT NULL DEFAULT 0,
    PRIMARY KEY (relation_id),
    UNIQUE KEY uk_test_battery_del (test_id, battery_no, del_flag),
    KEY idx_test_battery_group (group_id, del_flag)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='测试电池号';

CREATE TABLE t_test_battery_field (
    field_id BIGINT NOT NULL AUTO_INCREMENT,
    relation_id BIGINT NOT NULL,
    field_name VARCHAR(100) NOT NULL,
    field_value VARCHAR(500) NULL,
    del_flag BIGINT NOT NULL DEFAULT 0,
    PRIMARY KEY (field_id),
    UNIQUE KEY uk_test_battery_field (relation_id, field_name, del_flag)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='测试电池属性快照';

CREATE TABLE t_test_warning_threshold (
    threshold_id BIGINT NOT NULL AUTO_INCREMENT,
    test_id BIGINT NOT NULL,
    metric_code VARCHAR(100) NOT NULL,
    min_value DECIMAL(20,9) NULL,
    max_value DECIMAL(20,9) NULL,
    unit VARCHAR(32) NOT NULL,
    severity VARCHAR(32) NOT NULL DEFAULT 'WARNING',
    del_flag BIGINT NOT NULL DEFAULT 0,
    PRIMARY KEY (threshold_id),
    UNIQUE KEY uk_test_threshold (test_id, metric_code, del_flag)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='测试告警阈值';

CREATE TABLE t_test_channel_binding (
    binding_id BIGINT NOT NULL AUTO_INCREMENT,
    test_id BIGINT NOT NULL,
    battery_relation_id BIGINT NOT NULL,
    channel_id BIGINT NOT NULL,
    expected_barcode VARCHAR(100) NULL,
    observed_barcode VARCHAR(100) NULL,
    bts_test_id VARCHAR(64) NULL,
    claim_status VARCHAR(32) NOT NULL DEFAULT 'PENDING',
    claimed_at_utc DATETIME(3) NULL,
    released_at_utc DATETIME(3) NULL,
    PRIMARY KEY (binding_id),
    UNIQUE KEY uk_test_binding_battery (test_id, battery_relation_id),
    UNIQUE KEY uk_test_binding_channel (test_id, channel_id),
    KEY idx_binding_claim (channel_id, claim_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='测试与检测柜通道绑定';

CREATE TABLE t_channel_lease (
    channel_id BIGINT NOT NULL,
    test_id BIGINT NOT NULL,
    binding_id BIGINT NOT NULL,
    acquired_by BIGINT NOT NULL,
    acquired_at_utc DATETIME(3) NOT NULL,
    PRIMARY KEY (channel_id),
    UNIQUE KEY uk_channel_lease_binding (binding_id),
    KEY idx_channel_lease_test (test_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='活动通道独占锁';

CREATE TABLE t_channel_snapshot (
    channel_id BIGINT NOT NULL,
    test_id BIGINT NULL,
    binding_id BIGINT NULL,
    work_status VARCHAR(32) NOT NULL,
    cycle_no INT NULL,
    step_no INT NULL,
    step_type VARCHAR(64) NULL,
    voltage_v DECIMAL(20,9) NULL,
    current_a DECIMAL(20,9) NULL,
    capacity_ah DECIMAL(20,9) NULL,
    energy_wh DECIMAL(20,9) NULL,
    device_time_utc DATETIME(3) NULL,
    received_at_utc DATETIME(3) NOT NULL,
    raw_log_code VARCHAR(64) NULL,
    PRIMARY KEY (channel_id),
    KEY idx_snapshot_test (test_id, received_at_utc)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='通道最新实时快照';

CREATE TABLE t_test_sample (
    sample_id BIGINT NOT NULL AUTO_INCREMENT,
    test_id BIGINT NOT NULL,
    binding_id BIGINT NOT NULL,
    channel_id BIGINT NOT NULL,
    bts_test_id VARCHAR(64) NOT NULL,
    seq_id BIGINT UNSIGNED NOT NULL,
    device_time_utc DATETIME(3) NOT NULL,
    device_time_raw VARCHAR(64) NOT NULL,
    received_at_utc DATETIME(3) NOT NULL,
    cycle_no INT NOT NULL,
    step_no INT NOT NULL,
    step_type VARCHAR(64) NOT NULL,
    test_time_ms BIGINT UNSIGNED NULL,
    voltage_v DECIMAL(20,9) NULL,
    current_a DECIMAL(20,9) NULL,
    capacity_ah DECIMAL(20,9) NULL,
    energy_wh DECIMAL(20,9) NULL,
    content_sha256 BINARY(32) NOT NULL,
    PRIMARY KEY (sample_id),
    UNIQUE KEY uk_bts_history_sample (channel_id, bts_test_id, seq_id),
    KEY idx_sample_test_channel_time (test_id, channel_id, device_time_utc),
    KEY idx_sample_binding_step (binding_id, cycle_no, step_no, seq_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='BTS核心历史采样';

CREATE TABLE t_test_sample_measurement (
    measurement_id BIGINT NOT NULL AUTO_INCREMENT,
    sample_id BIGINT NOT NULL,
    metric_code VARCHAR(191) NOT NULL,
    metric_value DECIMAL(30,12) NULL,
    text_value VARCHAR(500) NULL,
    unit VARCHAR(32) NOT NULL DEFAULT '',
    source_type VARCHAR(32) NOT NULL,
    PRIMARY KEY (measurement_id),
    UNIQUE KEY uk_sample_metric (sample_id, metric_code),
    KEY idx_measurement_metric (metric_code, sample_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='动态辅助与DBC测量';

CREATE TABLE t_history_sync_cursor (
    binding_id BIGINT NOT NULL,
    bts_test_id VARCHAR(64) NOT NULL,
    next_seq_id BIGINT UNSIGNED NOT NULL DEFAULT 1,
    highest_saved_seq_id BIGINT UNSIGNED NULL,
    sync_status VARCHAR(32) NOT NULL DEFAULT 'PENDING',
    retry_count INT NOT NULL DEFAULT 0,
    last_error VARCHAR(1000) NULL,
    updated_at_utc DATETIME(3) NOT NULL,
    PRIMARY KEY (binding_id),
    KEY idx_history_sync_work (sync_status, updated_at_utc)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='历史补采持久化游标';

CREATE TABLE t_data_gap (
    gap_id BIGINT NOT NULL AUTO_INCREMENT,
    test_id BIGINT NOT NULL,
    binding_id BIGINT NOT NULL,
    bts_test_id VARCHAR(64) NOT NULL,
    missing_from_seq BIGINT UNSIGNED NOT NULL,
    missing_to_seq BIGINT UNSIGNED NOT NULL,
    gap_status VARCHAR(32) NOT NULL DEFAULT 'OPEN',
    retry_count INT NOT NULL DEFAULT 0,
    first_seen_at_utc DATETIME(3) NOT NULL,
    resolved_at_utc DATETIME(3) NULL,
    resolution_note VARCHAR(1000) NULL,
    PRIMARY KEY (gap_id),
    UNIQUE KEY uk_open_data_gap (binding_id, bts_test_id, missing_from_seq, missing_to_seq),
    KEY idx_gap_test_status (test_id, gap_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='历史数据序号缺口';

CREATE TABLE t_device_run_event (
    run_event_id BIGINT NOT NULL AUTO_INCREMENT,
    channel_id BIGINT NOT NULL,
    bts_test_id VARCHAR(64) NULL,
    observed_barcode VARCHAR(100) NULL,
    first_seen_at_utc DATETIME(3) NOT NULL,
    last_seen_at_utc DATETIME(3) NOT NULL,
    event_status VARCHAR(32) NOT NULL DEFAULT 'UNCLAIMED',
    claimed_test_id BIGINT NULL,
    claimed_binding_id BIGINT NULL,
    claimed_by BIGINT NULL,
    claimed_at_utc DATETIME(3) NULL,
    PRIMARY KEY (run_event_id),
    KEY idx_run_event_status (event_status, first_seen_at_utc),
    KEY idx_run_event_channel (channel_id, last_seen_at_utc)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='未认领及已认领设备运行';

CREATE TABLE t_test_exception (
    exception_id BIGINT NOT NULL AUTO_INCREMENT,
    test_id BIGINT NULL,
    channel_id BIGINT NULL,
    exception_type VARCHAR(64) NOT NULL,
    exception_status VARCHAR(32) NOT NULL DEFAULT 'OPEN',
    severity VARCHAR(32) NOT NULL DEFAULT 'WARNING',
    first_occurred_at_utc DATETIME(3) NOT NULL,
    last_occurred_at_utc DATETIME(3) NOT NULL,
    occurrence_count BIGINT NOT NULL DEFAULT 1,
    detail VARCHAR(2000) NULL,
    handled_by BIGINT NULL,
    handled_at_utc DATETIME(3) NULL,
    handling_note VARCHAR(1000) NULL,
    PRIMARY KEY (exception_id),
    KEY idx_exception_open (test_id, channel_id, exception_type, exception_status),
    KEY idx_exception_time (exception_status, last_occurred_at_utc)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='去重异常事件';

CREATE TABLE t_test_audit (
    audit_id BIGINT NOT NULL AUTO_INCREMENT,
    test_id BIGINT NOT NULL,
    event_type VARCHAR(64) NOT NULL,
    actor_id BIGINT NULL,
    actor_name VARCHAR(64) NOT NULL,
    occurred_at_utc DATETIME(3) NOT NULL,
    previous_value JSON NULL,
    new_value JSON NULL,
    reason VARCHAR(1000) NULL,
    correlation_id VARCHAR(64) NULL,
    PRIMARY KEY (audit_id),
    KEY idx_test_audit_time (test_id, occurred_at_utc, audit_id),
    KEY idx_test_audit_type (event_type, occurred_at_utc)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='只追加测试流程审计';

CREATE TABLE t_protocol_message (
    message_id BIGINT NOT NULL AUTO_INCREMENT,
    adapter_id BIGINT NOT NULL,
    test_id BIGINT NULL,
    direction VARCHAR(16) NOT NULL,
    command_name VARCHAR(64) NOT NULL,
    occurred_at_utc DATETIME(3) NOT NULL,
    parse_status VARCHAR(32) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    content_sha256 CHAR(64) NOT NULL,
    retention_until_utc DATETIME(3) NOT NULL,
    error_detail VARCHAR(1000) NULL,
    PRIMARY KEY (message_id),
    KEY idx_protocol_retention (retention_until_utc),
    KEY idx_protocol_adapter_time (adapter_id, occurred_at_utc),
    KEY idx_protocol_test_time (test_id, occurred_at_utc)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='协议报文文件元数据';