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

-- ======================================================
-- 积分消费记录表迁移脚本 (MySQL专用)
-- 将现有表结构调整为与Go版本一致
-- ======================================================

-- 备份现有数据（可选，建议在生产环境执行前备份）
-- CREATE TABLE points_consumption_log_backup AS SELECT * FROM points_consumption_log;

-- 修改字段属性以匹配Go版本
ALTER TABLE points_consumption_log 
  MODIFY COLUMN user_id VARCHAR(255) NOT NULL COMMENT 'accountID，与Go版本一致',
  MODIFY COLUMN file_name VARCHAR(500) NOT NULL COMMENT '文件名',
  MODIFY COLUMN file_url TEXT COMMENT '文件URL',
  MODIFY COLUMN points_consumed INT NOT NULL DEFAULT 10 COMMENT '消费积分数，默认10',
  MODIFY COLUMN balance_after INT NOT NULL COMMENT '消费后余额';

-- 添加索引以提升查询性能（如果不存在）
CREATE INDEX IF NOT EXISTS idx_user_id ON points_consumption_log(user_id);

-- 验证表结构
SHOW CREATE TABLE points_consumption_log;

-- 验证数据完整性
SELECT COUNT(*) as total_records FROM points_consumption_log;
SELECT user_id, COUNT(*) as record_count 
FROM points_consumption_log 
GROUP BY user_id
ORDER BY record_count DESC
LIMIT 10;