shudao-main/shudao-chat-py/migrate_scene_module.sql

-- ============================================================
-- 场景模块数据库迁移脚本
-- 用途：对齐 Go 版本的场景模块功能
-- 创建时间：2026-04-02
-- ============================================================

-- 1. 创建场景模板表
-- ============================================================
CREATE TABLE IF NOT EXISTS `scene_template` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `scene_name` varchar(255) NOT NULL COMMENT '场景名称',
  `scene_type` varchar(100) NOT NULL COMMENT '场景类型（如 bridge, tunnel）',
  `scene_desc` text COMMENT '场景描述',
  `model_name` varchar(255) NOT NULL COMMENT 'YOLO 模型名称',
  `created_at` bigint DEFAULT NULL COMMENT '创建时间（Unix 时间戳）',
  `updated_at` bigint DEFAULT NULL COMMENT '更新时间（Unix 时间戳）',
  `deleted_at` bigint DEFAULT NULL COMMENT '删除时间（Unix 时间戳）',
  `is_deleted` bigint DEFAULT '0' COMMENT '是否删除 0:否 1:是',
  PRIMARY KEY (`id`),
  KEY `idx_scene_type` (`scene_type`),
  KEY `idx_is_deleted` (`is_deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='场景模板表';


-- 2. 检查并添加 recognition_record 表缺失字段
-- ============================================================

-- 2.1 添加 scene_type 字段（如果不存在）
SET @column_exists = (
    SELECT COUNT(*) 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'recognition_record' 
    AND COLUMN_NAME = 'scene_type'
);

SET @sql = IF(@column_exists = 0, 
    'ALTER TABLE `recognition_record` ADD COLUMN `scene_type` varchar(100) DEFAULT '''' COMMENT ''场景类型'' AFTER `user_id`',
    'SELECT ''scene_type 字段已存在'' AS message'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


-- 2.2 添加 hazard_count 字段（如果不存在）
SET @column_exists = (
    SELECT COUNT(*) 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'recognition_record' 
    AND COLUMN_NAME = 'hazard_count'
);

SET @sql = IF(@column_exists = 0, 
    'ALTER TABLE `recognition_record` ADD COLUMN `hazard_count` int DEFAULT 0 COMMENT ''隐患数量'' AFTER `recognition_image_url`',
    'SELECT ''hazard_count 字段已存在'' AS message'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


-- 2.3 添加 current_step 字段（如果不存在）
SET @column_exists = (
    SELECT COUNT(*) 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'recognition_record' 
    AND COLUMN_NAME = 'current_step'
);

SET @sql = IF(@column_exists = 0, 
    'ALTER TABLE `recognition_record` ADD COLUMN `current_step` int DEFAULT 1 COMMENT ''当前步骤（1-3）'' AFTER `hazard_count`',
    'SELECT ''current_step 字段已存在'' AS message'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


-- 2.4 添加 hazard_details 字段（如果不存在）
SET @column_exists = (
    SELECT COUNT(*) 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'recognition_record' 
    AND COLUMN_NAME = 'hazard_details'
);

SET @sql = IF(@column_exists = 0, 
    'ALTER TABLE `recognition_record` ADD COLUMN `hazard_details` text COMMENT ''隐患详情 JSON'' AFTER `current_step`',
    'SELECT ''hazard_details 字段已存在'' AS message'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


-- 3. 添加索引（如果不存在）
-- ============================================================

-- 3.1 添加 scene_type 索引
SET @index_exists = (
    SELECT COUNT(*) 
    FROM INFORMATION_SCHEMA.STATISTICS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'recognition_record' 
    AND INDEX_NAME = 'idx_scene_type'
);

SET @sql = IF(@index_exists = 0, 
    'ALTER TABLE `recognition_record` ADD INDEX `idx_scene_type` (`scene_type`)',
    'SELECT ''idx_scene_type 索引已存在'' AS message'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


-- 3.2 添加联合索引（user_id + created_at）
SET @index_exists = (
    SELECT COUNT(*) 
    FROM INFORMATION_SCHEMA.STATISTICS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'recognition_record' 
    AND INDEX_NAME = 'idx_user_created'
);

SET @sql = IF(@index_exists = 0, 
    'ALTER TABLE `recognition_record` ADD INDEX `idx_user_created` (`user_id`, `created_at`)',
    'SELECT ''idx_user_created 索引已存在'' AS message'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


-- 4. 验证迁移结果
-- ============================================================
SELECT '=== 迁移完成，验证表结构 ===' AS message;

-- 验证 scene_template 表
SELECT 
    TABLE_NAME,
    TABLE_COMMENT,
    TABLE_ROWS
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME = 'scene_template';

-- 验证 recognition_record 表新增字段
SELECT 
    COLUMN_NAME,
    COLUMN_TYPE,
    COLUMN_DEFAULT,
    COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME = 'recognition_record'
AND COLUMN_NAME IN ('scene_type', 'hazard_count', 'current_step', 'hazard_details')
ORDER BY ORDINAL_POSITION;

-- 验证索引
SELECT 
    INDEX_NAME,
    COLUMN_NAME,
    SEQ_IN_INDEX
FROM INFORMATION_SCHEMA.STATISTICS 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME = 'recognition_record'
AND INDEX_NAME IN ('idx_scene_type', 'idx_user_created')
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

SELECT '=== 迁移脚本执行完成 ===' AS message;