-- 正式拓扑保存稳定适配器编码；代码固定装配的 dev-sim 不再需要数据库适配器主键。
ALTER TABLE t_detection_cabinet
    ADD COLUMN adapter_code VARCHAR(64) NULL AFTER adapter_id;

ALTER TABLE t_detection_channel
    ADD COLUMN adapter_code VARCHAR(64) NULL AFTER adapter_id;

UPDATE t_detection_cabinet cabinet
JOIN t_device_adapter adapter ON adapter.adapter_id = cabinet.adapter_id
SET cabinet.adapter_code = adapter.adapter_code;

UPDATE t_detection_channel channel_fact
JOIN t_device_adapter adapter ON adapter.adapter_id = channel_fact.adapter_id
SET channel_fact.adapter_code = adapter.adapter_code;

-- 先移除没有测试、采样、快照、异常或运行事件引用的旧开发候选通道。
DELETE channel_fact
FROM t_detection_channel channel_fact
JOIN t_detection_cabinet cabinet ON cabinet.cabinet_id = channel_fact.cabinet_id
JOIN t_device_adapter adapter ON adapter.adapter_id = cabinet.adapter_id
WHERE adapter.adapter_code = 'dev-sim'
  AND cabinet.registration_status = 'DISCOVERED'
  -- MySQL 禁止 DELETE 的子查询直接读取目标表，双层派生表强制先物化受保护柜体。
  AND cabinet.cabinet_id NOT IN (
      SELECT protected_cabinet.cabinet_id
      FROM (
          SELECT DISTINCT sibling.cabinet_id
          FROM t_detection_channel sibling
          WHERE
          EXISTS (SELECT 1 FROM t_test_channel_binding binding WHERE binding.channel_id = sibling.channel_id)
          OR EXISTS (SELECT 1 FROM t_channel_lease lease_fact WHERE lease_fact.channel_id = sibling.channel_id)
          OR EXISTS (SELECT 1 FROM t_channel_snapshot snapshot_fact WHERE snapshot_fact.channel_id = sibling.channel_id)
          OR EXISTS (SELECT 1 FROM t_test_sample sample_fact WHERE sample_fact.channel_id = sibling.channel_id)
          OR EXISTS (SELECT 1 FROM t_device_run_event run_event WHERE run_event.channel_id = sibling.channel_id)
          OR EXISTS (SELECT 1 FROM t_test_exception exception_fact WHERE exception_fact.channel_id = sibling.channel_id)
      ) protected_cabinet
  )
  AND NOT EXISTS (SELECT 1 FROM t_test_channel_binding binding WHERE binding.channel_id = channel_fact.channel_id)
  AND NOT EXISTS (SELECT 1 FROM t_channel_lease lease_fact WHERE lease_fact.channel_id = channel_fact.channel_id)
  AND NOT EXISTS (SELECT 1 FROM t_channel_snapshot snapshot_fact WHERE snapshot_fact.channel_id = channel_fact.channel_id)
  AND NOT EXISTS (SELECT 1 FROM t_test_sample sample_fact WHERE sample_fact.channel_id = channel_fact.channel_id)
  AND NOT EXISTS (SELECT 1 FROM t_device_run_event run_event WHERE run_event.channel_id = channel_fact.channel_id)
  AND NOT EXISTS (SELECT 1 FROM t_test_exception exception_fact WHERE exception_fact.channel_id = channel_fact.channel_id);

-- 只删除已经没有通道和测试关联的旧开发候选柜体。
DELETE cabinet
FROM t_detection_cabinet cabinet
JOIN t_device_adapter adapter ON adapter.adapter_id = cabinet.adapter_id
WHERE adapter.adapter_code = 'dev-sim'
  AND cabinet.registration_status = 'DISCOVERED'
  AND NOT EXISTS (SELECT 1 FROM t_detection_channel channel_fact WHERE channel_fact.cabinet_id = cabinet.cabinet_id);

ALTER TABLE t_detection_channel
    DROP INDEX uk_channel_identity,
    MODIFY adapter_id BIGINT NULL,
    MODIFY adapter_code VARCHAR(64) NOT NULL,
    ADD UNIQUE KEY uk_channel_stable_identity
        (adapter_code, devtype, devid, subdevid, chlid, del_flag);

ALTER TABLE t_detection_cabinet
    ADD KEY idx_cabinet_adapter (adapter_id),
    DROP INDEX uk_cabinet_vendor_identity,
    MODIFY adapter_id BIGINT NULL,
    MODIFY adapter_code VARCHAR(64) NOT NULL,
    ADD UNIQUE KEY uk_cabinet_stable_identity
        (adapter_code, vendor_device_type, vendor_device_id, del_flag);

-- 已正式登记的 dev-sim 拓扑改用 adapter_code，解除对临时数据库配置行的依赖。
UPDATE t_detection_channel SET adapter_id = NULL WHERE adapter_code = 'dev-sim';
UPDATE t_detection_cabinet SET adapter_id = NULL WHERE adapter_code = 'dev-sim';

-- 没有历史协议或采集器引用时清理旧 dev-sim 配置；有引用时保留历史行但运行时不再读取它。
DELETE adapter
FROM t_device_adapter adapter
WHERE adapter.adapter_code = 'dev-sim'
  AND NOT EXISTS (SELECT 1 FROM t_collector collector WHERE collector.adapter_id = adapter.adapter_id)
  AND NOT EXISTS (SELECT 1 FROM t_protocol_message message_fact WHERE message_fact.adapter_id = adapter.adapter_id);

-- 若历史引用要求保留行，则将其隐藏并禁用，运行时恢复和管理列表都不再读取。
UPDATE t_device_adapter
SET status = '1', del_flag = adapter_id, update_by = 'system', update_time = sysdate()
WHERE adapter_code = 'dev-sim' AND del_flag = 0;