-- Ticket 03：设备适配器连接与发现运行状态。
-- 设备配置和通道事实继续使用 V2 表；本迁移只增加可观察的运行时结果。

ALTER TABLE t_device_adapter
    ADD COLUMN connection_status VARCHAR(32) NOT NULL DEFAULT 'DISCONNECTED'
        COMMENT '连接状态：DISCONNECTED、CONNECTED、ERROR' AFTER status,
    ADD COLUMN last_error_code VARCHAR(64) NULL COMMENT '最近一次稳定错误分类' AFTER connection_status,
    ADD COLUMN last_error_message VARCHAR(500) NULL COMMENT '最近一次连接或协议错误' AFTER last_error_code,
    ADD COLUMN last_attempt_at_utc DATETIME(3) NULL COMMENT '最近一次发现尝试的服务器 UTC 时间' AFTER last_error_message,
    ADD COLUMN last_observed_at_utc DATETIME(3) NULL COMMENT '最近一次成功观测的服务器 UTC 时间' AFTER last_attempt_at_utc,
    ADD CONSTRAINT chk_adapter_connection_status
        CHECK (connection_status IN ('DISCONNECTED', 'CONNECTED', 'ERROR')),
    ADD KEY idx_adapter_poll_due (status, connection_status, last_attempt_at_utc, del_flag);

-- 适配器页面复用“检测柜”菜单；按钮权限必须进入 sys_menu，普通角色才可被授予 REST 权限。
SET @adapter_permission_parent := (
    SELECT menu_id FROM sys_menu
    WHERE perms = 'system:detectionCabinet:list'
    ORDER BY menu_id DESC LIMIT 1
);
SET @adapter_permission_base := (SELECT COALESCE(MAX(menu_id), 0) FROM sys_menu);

INSERT INTO sys_menu
    (menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
     is_frame, is_cache, menu_type, visible, status, perms, icon,
     create_by, create_time, update_by, update_time, remark)
VALUES
    (@adapter_permission_base + 1, '适配器列表', @adapter_permission_parent, 11, '', NULL, NULL, '', 1, 0, 'F', '0', '0', 'system:deviceAdapter:list', '#', 'admin', NOW(), '', NULL, ''),
    (@adapter_permission_base + 2, '适配器查看', @adapter_permission_parent, 12, '', NULL, NULL, '', 1, 0, 'F', '0', '0', 'system:deviceAdapter:query', '#', 'admin', NOW(), '', NULL, ''),
    (@adapter_permission_base + 3, '适配器新增', @adapter_permission_parent, 13, '', NULL, NULL, '', 1, 0, 'F', '0', '0', 'system:deviceAdapter:add', '#', 'admin', NOW(), '', NULL, ''),
    (@adapter_permission_base + 4, '适配器编辑', @adapter_permission_parent, 14, '', NULL, NULL, '', 1, 0, 'F', '0', '0', 'system:deviceAdapter:edit', '#', 'admin', NOW(), '', NULL, ''),
    (@adapter_permission_base + 5, '适配器启停', @adapter_permission_parent, 15, '', NULL, NULL, '', 1, 0, 'F', '0', '0', 'system:deviceAdapter:changeStatus', '#', 'admin', NOW(), '', NULL, ''),
    (@adapter_permission_base + 6, '适配器发现', @adapter_permission_parent, 16, '', NULL, NULL, '', 1, 0, 'F', '0', '0', 'system:deviceAdapter:discover', '#', 'admin', NOW(), '', NULL, '');