buff3.0/backend-client/migrations/20260405_fix_sessions_his_missing_columns.sql

-- Migration: 添加 sessions_his 表缺失的字段
-- Date: 2026-04-05
-- Description: 修复 sessions_his 表缺少 his_created_at 和 his_operation 字段的问题

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- 检查并添加 his_created_at 字段 (如果不存在)
SET @column_exists = (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'sessions_his'
    AND COLUMN_NAME = 'his_created_at'
);

SET @sql = IF(@column_exists = 0,
    'ALTER TABLE `sessions_his` ADD COLUMN `his_created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT \'历史记录创建时间\', ADD INDEX `idx_his_created_at` (`his_created_at`)',
    'SELECT \'Column his_created_at already exists\' AS message'
);

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

-- 检查并添加 his_operation 字段 (如果不存在)
SET @column_exists = (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'sessions_his'
    AND COLUMN_NAME = 'his_operation'
);

SET @sql = IF(@column_exists = 0,
    'ALTER TABLE `sessions_his` ADD COLUMN `his_operation` varchar(10) NOT NULL COMMENT \'操作类型: INSERT/UPDATE/DELETE/RENEW\'',
    'SELECT \'Column his_operation already exists\' AS message'
);

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

SET FOREIGN_KEY_CHECKS = 1;