ALTER TABLE t_test_realtime_record
    ADD COLUMN source_identity VARCHAR(256) NULL AFTER adapter_code,
    ADD COLUMN source_record_id VARCHAR(128) NULL AFTER source_identity;

UPDATE t_test_realtime_record
SET source_identity = adapter_code,
    source_record_id = CASE WHEN cabinet_sequence_id IS NOT NULL THEN CONCAT('cabinet:', cabinet_sequence_id)
                            WHEN collector_sequence_id IS NOT NULL THEN CONCAT('collector:', collector_sequence_id)
                            ELSE NULL END
WHERE source_identity IS NULL;

ALTER TABLE t_test_realtime_record
    MODIFY COLUMN source_identity VARCHAR(256) NOT NULL,
    ADD KEY idx_realtime_source (test_battery_id, source_identity, source_record_id);

ALTER TABLE t_test_step_record
    ADD COLUMN binding_id BIGINT NULL AFTER test_battery_id,
    ADD COLUMN dedupe_key VARCHAR(512) NULL AFTER source_record_id,
    ADD COLUMN server_received_at_utc DATETIME(3) NULL AFTER device_time_raw;

UPDATE t_test_step_record record_fact
JOIN t_test_channel_binding binding ON binding.test_id = record_fact.test_id
    AND binding.test_battery_id = record_fact.test_battery_id
SET record_fact.binding_id = binding.binding_id,
    record_fact.server_received_at_utc = record_fact.device_time_utc,
    record_fact.dedupe_key = CASE
        WHEN record_fact.source_record_id IS NOT NULL AND TRIM(record_fact.source_record_id) <> ''
            THEN CONCAT('record:', record_fact.source_record_id)
        ELSE CONCAT('fallback:', DATE_FORMAT(record_fact.device_time_utc, '%Y-%m-%dT%H:%i:%s.%fZ'),
                    ':', record_fact.cycle_no, ':', record_fact.step_no)
    END;

ALTER TABLE t_test_step_record
    MODIFY COLUMN binding_id BIGINT NOT NULL,
    MODIFY COLUMN dedupe_key VARCHAR(512) NOT NULL,
    MODIFY COLUMN server_received_at_utc DATETIME(3) NOT NULL,
    DROP INDEX uk_step_identity,
    ADD UNIQUE KEY uk_step_dedupe (test_battery_id, source_identity(128), dedupe_key(128)),
    ADD KEY idx_step_binding_time (binding_id, server_received_at_utc);

ALTER TABLE t_test_cycle_record
    ADD COLUMN binding_id BIGINT NULL AFTER test_battery_id,
    ADD COLUMN dedupe_key VARCHAR(512) NULL AFTER source_record_id,
    ADD COLUMN server_received_at_utc DATETIME(3) NULL AFTER device_time_raw;

UPDATE t_test_cycle_record record_fact
JOIN t_test_channel_binding binding ON binding.test_id = record_fact.test_id
    AND binding.test_battery_id = record_fact.test_battery_id
SET record_fact.binding_id = binding.binding_id,
    record_fact.server_received_at_utc = record_fact.device_time_utc,
    record_fact.dedupe_key = CASE
        WHEN record_fact.source_record_id IS NOT NULL AND TRIM(record_fact.source_record_id) <> ''
            THEN CONCAT('record:', record_fact.source_record_id)
        ELSE CONCAT('fallback:', DATE_FORMAT(record_fact.device_time_utc, '%Y-%m-%dT%H:%i:%s.%fZ'),
                    ':', record_fact.cycle_no)
    END;

ALTER TABLE t_test_cycle_record
    MODIFY COLUMN binding_id BIGINT NOT NULL,
    MODIFY COLUMN dedupe_key VARCHAR(512) NOT NULL,
    MODIFY COLUMN server_received_at_utc DATETIME(3) NOT NULL,
    DROP INDEX uk_cycle_identity,
    ADD UNIQUE KEY uk_cycle_dedupe (test_battery_id, source_identity(128), dedupe_key(128)),
    ADD KEY idx_cycle_binding_time (binding_id, server_received_at_utc);