-- 更新设备表，添加 AMT 状态和 Agent 状态字段（安全版本）

-- 1. 检查并添加 amt_status 字段（如果不存在）
SET @col_exists = 0;
SELECT COUNT(*) INTO @col_exists 
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'soybean_admin' 
  AND TABLE_NAME = 'sys_device' 
  AND COLUMN_NAME = 'amt_status';

SET @sql = IF(@col_exists = 0,
    'ALTER TABLE sys_device ADD COLUMN amt_status VARCHAR(20) DEFAULT ''offline'' COMMENT ''AMT状态: online-在线, offline-离线''',
    'SELECT ''amt_status column already exists'' AS message');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 2. 检查并添加 agent_status 字段（如果不存在）
SET @col_exists = 0;
SELECT COUNT(*) INTO @col_exists 
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'soybean_admin' 
  AND TABLE_NAME = 'sys_device' 
  AND COLUMN_NAME = 'agent_status';

SET @sql = IF(@col_exists = 0,
    'ALTER TABLE sys_device ADD COLUMN agent_status VARCHAR(20) DEFAULT ''offline'' COMMENT ''Agent状态: online-在线, offline-离线''',
    'SELECT ''agent_status column already exists'' AS message');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 3. 将原有的 status 字段数据迁移到 amt_status（仅当 amt_status 为 NULL 或 'offline' 时）
UPDATE sys_device 
SET amt_status = CASE 
    WHEN status IN ('online', 'offline') THEN status 
    ELSE 'offline' 
END
WHERE amt_status IS NULL OR amt_status = 'offline';

-- 4. 确保所有设备都有 agent_status（默认为离线）
UPDATE sys_device 
SET agent_status = 'offline' 
WHERE agent_status IS NULL;

-- 5. 查看更新后的数据
SELECT id, device_name, status, amt_status, agent_status, ip_address 
FROM sys_device 
LIMIT 10;