-- 最终业务数据库基线（Ticket03）。本脚本只创建结构，不导入演示业务数据。
-- 业务关联由事务、删除顺序、唯一约束和索引维护，不建立物理外键。
SET NAMES utf8mb4;
SET time_zone = '+00:00';

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,
    status CHAR(1) NOT NULL DEFAULT '0',
    del_flag TINYINT NOT NULL DEFAULT 0,
    create_by VARCHAR(64) NOT NULL DEFAULT '',
    create_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    update_by VARCHAR(64) NOT NULL DEFAULT '',
    update_time DATETIME(3) 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 (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 TINYINT NOT NULL DEFAULT 0,
    PRIMARY KEY (relation_id),
    UNIQUE KEY uk_group_battery_no_del (group_id, battery_no, del_flag),
    KEY idx_group_battery (group_id, del_flag, sort_order)
) 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 TINYINT NOT NULL DEFAULT 0,
    create_by VARCHAR(64) NOT NULL DEFAULT '',
    create_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    update_by VARCHAR(64) NOT NULL DEFAULT '',
    update_time DATETIME(3) 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, del_flag)
) 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(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    update_by VARCHAR(64) NOT NULL DEFAULT '',
    update_time DATETIME(3) 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(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    del_flag TINYINT 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 NULL,
    battery_group_field_id BIGINT NOT NULL,
    field_value VARCHAR(500) NULL,
    del_flag TINYINT 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',
    read_only TINYINT(1) NOT NULL DEFAULT 1,
    capabilities JSON NOT NULL,
    connection_status VARCHAR(16) NOT NULL DEFAULT 'OFFLINE',
    last_error_code VARCHAR(64) NULL,
    last_error_message VARCHAR(500) NULL,
    last_attempt_at_utc DATETIME(3) NULL,
    last_observed_at_utc DATETIME(3) NULL,
    status CHAR(1) NOT NULL DEFAULT '0',
    del_flag TINYINT NOT NULL DEFAULT 0,
    create_by VARCHAR(64) NOT NULL DEFAULT '',
    create_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    update_by VARCHAR(64) NOT NULL DEFAULT '',
    update_time DATETIME(3) NULL,
    remark VARCHAR(500) NULL,
    PRIMARY KEY (adapter_id),
    UNIQUE KEY uk_adapter_code_del (adapter_code, del_flag),
    KEY idx_adapter_connection (connection_status, 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 DECIMAL(10,2) 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,
    registration_status VARCHAR(16) NOT NULL DEFAULT 'REGISTERED',
    online_status VARCHAR(16) NOT NULL DEFAULT 'OFFLINE',
    last_seen_at_utc DATETIME(3) NULL,
    status CHAR(1) NOT NULL DEFAULT '0',
    del_flag TINYINT NOT NULL DEFAULT 0,
    create_by VARCHAR(64) NOT NULL DEFAULT '',
    create_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    update_by VARCHAR(64) NOT NULL DEFAULT '',
    update_time DATETIME(3) NULL,
    remark VARCHAR(500) NULL,
    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 (registration_status, online_status, 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(16) NOT NULL DEFAULT 'OFFLINE',
    work_status VARCHAR(32) NOT NULL DEFAULT 'UNOBSERVABLE',
    last_seen_at_utc DATETIME(3) NULL,
    status CHAR(1) NOT NULL DEFAULT '0',
    del_flag TINYINT NOT NULL DEFAULT 0,
    create_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    update_time DATETIME(3) NULL,
    PRIMARY KEY (channel_id),
    UNIQUE KEY uk_channel_stable_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(16) NOT NULL DEFAULT 'OFFLINE',
    last_seen_at_utc DATETIME(3) NULL,
    status CHAR(1) NOT NULL DEFAULT '0',
    del_flag TINYINT NOT NULL DEFAULT 0,
    create_by VARCHAR(64) NOT NULL DEFAULT '',
    create_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    update_by VARCHAR(64) NOT NULL DEFAULT '',
    update_time DATETIME(3) NULL,
    remark VARCHAR(500) NULL,
    PRIMARY KEY (collector_id),
    UNIQUE KEY uk_collector_code_del (collector_code, del_flag),
    KEY idx_collector_status (online_status, 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 TINYINT NOT NULL DEFAULT 0,
    create_by VARCHAR(64) NOT NULL DEFAULT '',
    create_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    update_by VARCHAR(64) NOT NULL DEFAULT '',
    update_time DATETIME(3) 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,
    creator_id BIGINT 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',
    reception_status VARCHAR(32) NOT NULL DEFAULT 'NOT_STARTED',
    monitor_timeout_minutes INT NOT NULL DEFAULT 30,
    started_at_utc DATETIME(3) NULL,
    ended_at_utc DATETIME(3) NULL,
    logic_step_mappings_json JSON NULL,
    console_detail_json JSON NULL,
    del_flag TINYINT NOT NULL DEFAULT 0,
    create_by VARCHAR(64) NOT NULL DEFAULT '',
    create_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    update_by VARCHAR(64) NOT NULL DEFAULT '',
    update_time DATETIME(3) 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, reception_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_battery (
    test_battery_id BIGINT NOT NULL AUTO_INCREMENT,
    test_id BIGINT NOT NULL,
    group_id BIGINT NOT NULL,
    battery_no VARCHAR(100) NOT NULL,
    collector_id BIGINT NOT NULL,
    del_flag TINYINT NOT NULL DEFAULT 0,
    PRIMARY KEY (test_battery_id),
    UNIQUE KEY uk_test_battery_no_del (test_id, battery_no, del_flag),
    KEY idx_test_battery_group (group_id, del_flag),
    KEY idx_test_battery_test (test_id, 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,
    test_battery_id BIGINT NOT NULL,
    channel_id BIGINT NOT NULL,
    expected_barcode VARCHAR(100) 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, test_battery_id),
    UNIQUE KEY uk_test_binding_channel (test_id, channel_id),
    KEY idx_binding_channel (channel_id, claim_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='测试检测柜通道绑定';

CREATE TABLE t_test_collector_channel (
    mapping_id BIGINT NOT NULL AUTO_INCREMENT,
    test_id BIGINT NOT NULL,
    test_battery_id BIGINT NOT NULL,
    collector_id BIGINT NOT NULL,
    measurement_code VARCHAR(100) NOT NULL,
    measurement_name VARCHAR(100) NULL,
    channel_code VARCHAR(100) NOT NULL,
    PRIMARY KEY (mapping_id),
    UNIQUE KEY uk_test_collector_measurement (test_id, test_battery_id, measurement_code),
    UNIQUE KEY uk_test_collector_channel (test_id, collector_id, channel_code),
    KEY idx_test_collector_battery (test_battery_id)
) 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(30,12) NULL,
    max_value DECIMAL(30,12) NULL,
    unit VARCHAR(32) NOT NULL DEFAULT '',
    severity VARCHAR(32) NOT NULL DEFAULT 'WARNING',
    del_flag TINYINT NOT NULL DEFAULT 0,
    PRIMARY KEY (threshold_id),
    UNIQUE KEY uk_test_threshold (test_id, metric_code, del_flag),
    KEY idx_threshold_test (test_id, severity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='测试预警阈值';

CREATE TABLE t_test_realtime_record (
    record_id BIGINT NOT NULL AUTO_INCREMENT,
    test_id BIGINT NOT NULL,
    test_battery_id BIGINT NOT NULL,
    binding_id BIGINT NOT NULL,
    adapter_code VARCHAR(64) NOT NULL,
    cabinet_devtype INT NULL,
    cabinet_devid INT NULL,
    cabinet_subdevid INT NULL,
    cabinet_chlid INT NULL,
    cabinet_sequence_id BIGINT NULL,
    cabinet_device_time_utc DATETIME(3) NULL,
    cabinet_device_time_raw VARCHAR(64) NULL,
    cabinet_adapter_timezone_id VARCHAR(64) NULL,
    collector_id BIGINT NULL,
    collector_channel_code VARCHAR(100) NULL,
    collector_sequence_id BIGINT NULL,
    collector_device_time_utc DATETIME(3) NULL,
    collector_device_time_raw VARCHAR(64) NULL,
    collector_adapter_timezone_id VARCHAR(64) NULL,
    capture_slot_utc DATETIME(3) NOT NULL,
    server_received_at_utc DATETIME(3) NOT NULL,
    cycle_no INT NULL,
    step_no INT NULL,
    step_type VARCHAR(64) NULL,
    step_name VARCHAR(191) NULL,
    voltage_v DECIMAL(30,12) NULL,
    current_a DECIMAL(30,12) NULL,
    capacity_ah DECIMAL(30,12) NULL,
    energy_wh DECIMAL(30,12) NULL,
    temperature DECIMAL(30,12) NULL,
    positive_potential DECIMAL(30,12) NULL,
    negative_potential DECIMAL(30,12) NULL,
    extension_measurements_json JSON NULL,
    content_sha256 BINARY(32) NOT NULL,
    PRIMARY KEY (record_id),
    UNIQUE KEY uk_realtime_slot (test_id, test_battery_id, capture_slot_utc),
    KEY idx_realtime_test_time (test_id, capture_slot_utc),
    KEY idx_realtime_binding_time (binding_id, server_received_at_utc)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='实时采样记录';

CREATE TABLE t_test_step_record (
    step_record_id BIGINT NOT NULL AUTO_INCREMENT,
    test_id BIGINT NOT NULL,
    test_battery_id BIGINT NOT NULL,
    adapter_code VARCHAR(64) NOT NULL,
    source_identity VARCHAR(256) NOT NULL,
    source_record_id VARCHAR(128) NULL,
    adapter_timezone_id VARCHAR(64) NOT NULL,
    device_time_utc DATETIME(3) NOT NULL,
    device_time_raw VARCHAR(64) NOT NULL,
    cycle_no INT NOT NULL,
    step_no INT NOT NULL,
    step_type VARCHAR(64) NULL,
    step_name VARCHAR(191) NULL,
    voltage_v DECIMAL(30,12) NULL,
    current_a DECIMAL(30,12) NULL,
    capacity_ah DECIMAL(30,12) NULL,
    energy_wh DECIMAL(30,12) NULL,
    extension_measurements_json JSON NULL,
    content_sha256 BINARY(32) NOT NULL,
    PRIMARY KEY (step_record_id),
    UNIQUE KEY uk_step_identity (test_battery_id, source_identity, source_record_id, device_time_utc, cycle_no, step_no),
    KEY idx_step_test_order (test_id, test_battery_id, cycle_no, step_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='工步记录';

CREATE TABLE t_test_cycle_record (
    cycle_record_id BIGINT NOT NULL AUTO_INCREMENT,
    test_id BIGINT NOT NULL,
    test_battery_id BIGINT NOT NULL,
    adapter_code VARCHAR(64) NOT NULL,
    source_identity VARCHAR(256) NOT NULL,
    source_record_id VARCHAR(128) NULL,
    adapter_timezone_id VARCHAR(64) NOT NULL,
    device_time_utc DATETIME(3) NOT NULL,
    device_time_raw VARCHAR(64) NOT NULL,
    cycle_no INT NOT NULL,
    charge_capacity_ah DECIMAL(30,12) NULL,
    discharge_capacity_ah DECIMAL(30,12) NULL,
    charge_energy_wh DECIMAL(30,12) NULL,
    discharge_energy_wh DECIMAL(30,12) NULL,
    efficiency DECIMAL(30,12) NULL,
    extension_measurements_json JSON NULL,
    content_sha256 BINARY(32) NOT NULL,
    PRIMARY KEY (cycle_record_id),
    UNIQUE KEY uk_cycle_identity (test_battery_id, source_identity, source_record_id, device_time_utc, cycle_no),
    KEY idx_cycle_test_order (test_id, test_battery_id, cycle_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='循环记录';

CREATE TABLE t_test_logic_step_mapping (
    mapping_id BIGINT NOT NULL AUTO_INCREMENT,
    test_id BIGINT NOT NULL,
    logical_no INT NOT NULL,
    logical_name VARCHAR(191) NOT NULL,
    start_step_no INT NOT NULL,
    end_step_no INT NOT NULL,
    PRIMARY KEY (mapping_id),
    UNIQUE KEY uk_logic_step_no (test_id, logical_no),
    KEY idx_logic_step_range (test_id, start_step_no, end_step_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='测试逻辑工步映射';

CREATE TABLE t_test_data_revision_action (
    action_id BIGINT NOT NULL AUTO_INCREMENT,
    test_id BIGINT NOT NULL,
    data_type VARCHAR(16) NOT NULL,
    operation_type VARCHAR(16) NOT NULL,
    scope_summary VARCHAR(1000) NOT NULL,
    identity_summary JSON NOT NULL,
    before_content_json JSON NULL,
    changes_summary JSON NULL,
    reason VARCHAR(500) NOT NULL,
    operator_id BIGINT NULL,
    operator_name VARCHAR(64) NOT NULL,
    created_at_utc DATETIME(3) NOT NULL,
    PRIMARY KEY (action_id),
    KEY idx_data_revision_test_time (test_id, data_type, created_at_utc)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='测试数据修订动作';

CREATE TABLE t_test_warning_exception (
    exception_id BIGINT NOT NULL AUTO_INCREMENT,
    test_id BIGINT NOT NULL,
    test_battery_id BIGINT NULL,
    threshold_id BIGINT NOT NULL,
    source_type VARCHAR(32) NOT NULL,
    source_identity VARCHAR(256) NOT NULL,
    metric_code VARCHAR(100) NOT NULL,
    abnormal_value DECIMAL(30,12) NULL,
    unit VARCHAR(32) NOT NULL DEFAULT '',
    rule_snapshot JSON NOT NULL,
    severity VARCHAR(32) NOT NULL DEFAULT 'WARNING',
    exception_status VARCHAR(16) NOT NULL DEFAULT 'OPEN',
    first_occurred_at_utc DATETIME(3) NOT NULL,
    last_occurred_at_utc DATETIME(3) NOT NULL,
    occurrence_count BIGINT NOT NULL DEFAULT 1,
    handled_by BIGINT NULL,
    handled_at_utc DATETIME(3) NULL,
    handling_note VARCHAR(1000) NULL,
    open_dedupe_key VARCHAR(512) GENERATED ALWAYS AS
        (CASE WHEN exception_status = 'OPEN'
              THEN CONCAT(test_id, ':', threshold_id, ':', metric_code)
              ELSE NULL END) STORED,
    PRIMARY KEY (exception_id),
    KEY idx_warning_exception_test (test_id, exception_status, last_occurred_at_utc),
    UNIQUE KEY uk_warning_exception_open (open_dedupe_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='测试预警异常';

ALTER TABLE t_device_adapter
    ADD CONSTRAINT chk_adapter_connection_status
        CHECK (connection_status IN ('ONLINE', 'OFFLINE'));
ALTER TABLE t_detection_cabinet
    ADD CONSTRAINT chk_cabinet_registration_status
        CHECK (registration_status IN ('DISCOVERED', 'REGISTERED', 'DISABLED')),
    ADD CONSTRAINT chk_cabinet_online_status
        CHECK (online_status IN ('ONLINE', 'OFFLINE'));
ALTER TABLE t_detection_channel
    ADD CONSTRAINT chk_channel_online_status
        CHECK (online_status IN ('ONLINE', 'OFFLINE')),
    ADD CONSTRAINT chk_channel_work_status
        CHECK (work_status IN ('RUNNING', 'PAUSED', 'ENDED', 'ABNORMAL', 'UNOBSERVABLE'));
ALTER TABLE t_collector
    ADD CONSTRAINT chk_collector_online_status
        CHECK (online_status IN ('ONLINE', 'OFFLINE'));
ALTER TABLE t_test
    ADD CONSTRAINT chk_test_status
        CHECK (test_status IN ('WAITING', 'RUNNING', 'PAUSED', 'COMPLETED')),
    ADD CONSTRAINT chk_test_reception_status
        CHECK (reception_status IN ('NOT_STARTED', 'RECEIVING', 'PAUSED', 'ENDED'));
ALTER TABLE t_test_warning_exception
    ADD CONSTRAINT chk_warning_exception_status
        CHECK (exception_status IN ('OPEN', 'CLOSED'));
ALTER TABLE t_test_data_revision_action
    ADD CONSTRAINT chk_data_revision_type
        CHECK (data_type IN ('REALTIME', 'STEP', 'CYCLE')),
    ADD CONSTRAINT chk_data_revision_operation
        CHECK (operation_type IN ('EDIT', 'IMPORT', 'DELETE'));