Maas-Model-platform/backend/migrations/021_create_asr_recognition_table.sql

-- 迁移文件: 021_create_asr_recognition_table.sql
-- 描述: 创建同步语音识别记录表
-- 需求: 存储同步语音识别的历史记录

-- ============================================
-- 正向迁移: 创建表和索引
-- ============================================

-- 创建asr_recognition表
CREATE TABLE IF NOT EXISTS asr_recognition (
    id SERIAL PRIMARY KEY,
    user_id VARCHAR(50) NOT NULL,
    model VARCHAR(100) NOT NULL,
    audio_url VARCHAR(500),
    audio_base64 TEXT,
    language VARCHAR(20),
    enable_itn BOOLEAN DEFAULT FALSE,
    context TEXT,
    result_text TEXT NOT NULL,
    detected_language VARCHAR(20),
    emotion VARCHAR(20),
    duration INTEGER,
    input_tokens INTEGER DEFAULT 0,
    output_tokens INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_asr_recognition_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 添加表注释
COMMENT ON TABLE asr_recognition IS '同步语音识别记录表';
COMMENT ON COLUMN asr_recognition.id IS '主键ID';
COMMENT ON COLUMN asr_recognition.user_id IS '用户ID（外键关联users表）';
COMMENT ON COLUMN asr_recognition.model IS 'ASR模型名称';
COMMENT ON COLUMN asr_recognition.audio_url IS '音频文件URL（如果有）';
COMMENT ON COLUMN asr_recognition.audio_base64 IS 'Base64编码的音频数据（如果有）';
COMMENT ON COLUMN asr_recognition.language IS '指定语种（zh/en/ja/ko等）';
COMMENT ON COLUMN asr_recognition.enable_itn IS '是否启用逆文本标准化';
COMMENT ON COLUMN asr_recognition.context IS '上下文提示';
COMMENT ON COLUMN asr_recognition.result_text IS '识别结果文本';
COMMENT ON COLUMN asr_recognition.detected_language IS '检测到的语言';
COMMENT ON COLUMN asr_recognition.emotion IS '情感类型';
COMMENT ON COLUMN asr_recognition.duration IS '音频时长（秒）';
COMMENT ON COLUMN asr_recognition.input_tokens IS '输入Token数';
COMMENT ON COLUMN asr_recognition.output_tokens IS '输出Token数';
COMMENT ON COLUMN asr_recognition.created_at IS '创建时间';

-- 创建索引（优化查询性能）
CREATE INDEX IF NOT EXISTS idx_asr_recognition_user_id ON asr_recognition(user_id);
CREATE INDEX IF NOT EXISTS idx_asr_recognition_created_at ON asr_recognition(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_asr_recognition_model ON asr_recognition(model);

-- ============================================
-- 回滚迁移: 删除表和索引
-- ============================================
-- DROP INDEX IF EXISTS idx_asr_recognition_model;
-- DROP INDEX IF EXISTS idx_asr_recognition_created_at;
-- DROP INDEX IF EXISTS idx_asr_recognition_user_id;
-- DROP TABLE IF EXISTS asr_recognition;