-- 迁移文件: 031_create_audio_synthesis_v2_table.sql
-- 描述: 创建语音合成任务表V2（异步模式）
-- 策略: 创建新表，不影响现有 audio_synthesis 表

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

-- 创建 audio_synthesis_v2 表
CREATE TABLE IF NOT EXISTS audio_synthesis_v2 (
    id SERIAL PRIMARY KEY,
    user_id VARCHAR(50) NOT NULL,
    task_id VARCHAR(100) NOT NULL UNIQUE,
    model VARCHAR(100) NOT NULL,
    voice VARCHAR(100) NOT NULL,
    text TEXT NOT NULL,
    audio_url VARCHAR(500),
    status VARCHAR(20) DEFAULT 'PENDING',
    duration NUMERIC(10, 2),
    format VARCHAR(20) DEFAULT 'mp3',
    characters INTEGER,
    bill NUMERIC(10, 4) DEFAULT 0,
    custom_name VARCHAR(200),
    error_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP,
    CONSTRAINT fk_audio_synthesis_v2_user 
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 添加表注释
COMMENT ON TABLE audio_synthesis_v2 IS '语音合成任务表V2（异步模式，统一架构）';
COMMENT ON COLUMN audio_synthesis_v2.id IS '主键ID';
COMMENT ON COLUMN audio_synthesis_v2.user_id IS '用户ID（外键关联users表）';
COMMENT ON COLUMN audio_synthesis_v2.task_id IS 'DashScope任务ID（唯一）';
COMMENT ON COLUMN audio_synthesis_v2.model IS 'TTS模型名称';
COMMENT ON COLUMN audio_synthesis_v2.voice IS '音色ID';
COMMENT ON COLUMN audio_synthesis_v2.text IS '合成文本内容';
COMMENT ON COLUMN audio_synthesis_v2.audio_url IS '音频文件OSS地址';
COMMENT ON COLUMN audio_synthesis_v2.status IS '任务状态（PENDING/PROCESSING/SUCCEEDED/FAILED）';
COMMENT ON COLUMN audio_synthesis_v2.duration IS '音频时长（秒）';
COMMENT ON COLUMN audio_synthesis_v2.format IS '音频格式（mp3/wav/pcm）';
COMMENT ON COLUMN audio_synthesis_v2.characters IS '文本字符数';
COMMENT ON COLUMN audio_synthesis_v2.bill IS '费用（元）';
COMMENT ON COLUMN audio_synthesis_v2.custom_name IS '用户自定义名称';
COMMENT ON COLUMN audio_synthesis_v2.error_message IS '错误信息（失败时）';
COMMENT ON COLUMN audio_synthesis_v2.created_at IS '创建时间';
COMMENT ON COLUMN audio_synthesis_v2.updated_at IS '更新时间';
COMMENT ON COLUMN audio_synthesis_v2.completed_at IS '完成时间';

-- 创建索引（优化查询性能）
CREATE INDEX idx_audio_synthesis_v2_user_id ON audio_synthesis_v2(user_id);
CREATE INDEX idx_audio_synthesis_v2_task_id ON audio_synthesis_v2(task_id);
CREATE INDEX idx_audio_synthesis_v2_status ON audio_synthesis_v2(status);
CREATE INDEX idx_audio_synthesis_v2_created_at ON audio_synthesis_v2(created_at DESC);

-- ============================================
-- 回滚迁移: 删除表和索引
-- ============================================
-- DROP INDEX IF EXISTS idx_audio_synthesis_v2_created_at;
-- DROP INDEX IF EXISTS idx_audio_synthesis_v2_status;
-- DROP INDEX IF EXISTS idx_audio_synthesis_v2_task_id;
-- DROP INDEX IF EXISTS idx_audio_synthesis_v2_user_id;
-- DROP TABLE IF EXISTS audio_synthesis_v2;