-- 迁移文件: 030_create_asr_recognition_v2_table.sql
-- 描述: 创建语音识别任务表V2（异步模式）
-- 策略: 创建新表，不影响现有 asr_task 和 asr_recognition 表

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

-- 创建 asr_recognition_v2 表
CREATE TABLE IF NOT EXISTS asr_recognition_v2 (
    id SERIAL PRIMARY KEY,
    user_id VARCHAR(50) NOT NULL,
    task_id VARCHAR(100) NOT NULL UNIQUE,
    model VARCHAR(100) NOT NULL,
    file_url VARCHAR(500) NOT NULL,
    status VARCHAR(20) DEFAULT 'PENDING',
    result_text TEXT,
    result_url VARCHAR(500),
    duration INTEGER,
    bill NUMERIC(10, 4) DEFAULT 0,
    error_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP,
    CONSTRAINT fk_asr_recognition_v2_user 
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 添加表注释
COMMENT ON TABLE asr_recognition_v2 IS '语音识别任务表V2（异步模式，统一架构）';
COMMENT ON COLUMN asr_recognition_v2.id IS '主键ID';
COMMENT ON COLUMN asr_recognition_v2.user_id IS '用户ID（外键关联users表）';
COMMENT ON COLUMN asr_recognition_v2.task_id IS 'DashScope任务ID（唯一）';
COMMENT ON COLUMN asr_recognition_v2.model IS 'ASR模型名称';
COMMENT ON COLUMN asr_recognition_v2.file_url IS '音频文件URL';
COMMENT ON COLUMN asr_recognition_v2.status IS '任务状态（PENDING/PROCESSING/SUCCEEDED/FAILED）';
COMMENT ON COLUMN asr_recognition_v2.result_text IS '识别结果文本';
COMMENT ON COLUMN asr_recognition_v2.result_url IS '识别结果文件URL（长文本）';
COMMENT ON COLUMN asr_recognition_v2.duration IS '音频时长（秒）';
COMMENT ON COLUMN asr_recognition_v2.bill IS '费用（元）';
COMMENT ON COLUMN asr_recognition_v2.error_message IS '错误信息（失败时）';
COMMENT ON COLUMN asr_recognition_v2.created_at IS '创建时间';
COMMENT ON COLUMN asr_recognition_v2.updated_at IS '更新时间';
COMMENT ON COLUMN asr_recognition_v2.completed_at IS '完成时间';

-- 创建索引（优化查询性能）
CREATE INDEX idx_asr_recognition_v2_user_id ON asr_recognition_v2(user_id);
CREATE INDEX idx_asr_recognition_v2_task_id ON asr_recognition_v2(task_id);
CREATE INDEX idx_asr_recognition_v2_status ON asr_recognition_v2(status);
CREATE INDEX idx_asr_recognition_v2_created_at ON asr_recognition_v2(created_at DESC);

-- ============================================
-- 回滚迁移: 删除表和索引
-- ============================================
-- DROP INDEX IF EXISTS idx_asr_recognition_v2_created_at;
-- DROP INDEX IF EXISTS idx_asr_recognition_v2_status;
-- DROP INDEX IF EXISTS idx_asr_recognition_v2_task_id;
-- DROP INDEX IF EXISTS idx_asr_recognition_v2_user_id;
-- DROP TABLE IF EXISTS asr_recognition_v2;