-- 迁移文件: 018_create_asr_task_table.sql
-- 描述: 创建语音识别任务表
-- 需求: 5.3, 5.5, 5.6

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

-- 创建asr_task表
CREATE TABLE IF NOT EXISTS asr_task (
    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,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_asr_task_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 添加表注释
COMMENT ON TABLE asr_task IS '语音识别任务表';
COMMENT ON COLUMN asr_task.id IS '主键ID';
COMMENT ON COLUMN asr_task.user_id IS '用户ID（外键关联users表）';
COMMENT ON COLUMN asr_task.task_id IS 'DashScope返回的任务ID';
COMMENT ON COLUMN asr_task.model IS 'ASR模型名称';
COMMENT ON COLUMN asr_task.file_url IS '音频文件URL';
COMMENT ON COLUMN asr_task.status IS '任务状态（PENDING/RUNNING/SUCCEEDED/FAILED）';
COMMENT ON COLUMN asr_task.result_text IS '识别结果文本';
COMMENT ON COLUMN asr_task.result_url IS '识别结果文件URL';
COMMENT ON COLUMN asr_task.duration IS '音频时长（秒）';
COMMENT ON COLUMN asr_task.created_at IS '创建时间';
COMMENT ON COLUMN asr_task.updated_at IS '更新时间';

-- 创建索引（优化查询性能）
CREATE INDEX IF NOT EXISTS idx_asr_task_user_id ON asr_task(user_id);
CREATE INDEX IF NOT EXISTS idx_asr_task_task_id ON asr_task(task_id);
CREATE INDEX IF NOT EXISTS idx_asr_task_status ON asr_task(status);
CREATE INDEX IF NOT EXISTS idx_asr_task_created_at ON asr_task(created_at DESC);

-- ============================================
-- 回滚迁移: 删除表和索引
-- ============================================
-- DROP INDEX IF EXISTS idx_asr_task_created_at;
-- DROP INDEX IF EXISTS idx_asr_task_status;
-- DROP INDEX IF EXISTS idx_asr_task_task_id;
-- DROP INDEX IF EXISTS idx_asr_task_user_id;
-- DROP TABLE IF EXISTS asr_task;