Maas-Model-platform/backend/migrations/033_create_long_text_audio_table.sql

-- 迁移文件: 033_create_long_text_audio_table.sql
-- 描述: 创建长文本转音频任务表（异步模式）
-- 策略: 全新表，支持长文本分段合成

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

-- 创建 long_text_audio 表
CREATE TABLE IF NOT EXISTS long_text_audio (
    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,
    text_length INTEGER NOT NULL,
    segment_count INTEGER DEFAULT 0,
    segments JSONB DEFAULT '[]'::jsonb,
    audio_url VARCHAR(500),
    status VARCHAR(20) DEFAULT 'PENDING',
    progress INTEGER DEFAULT 0,
    duration NUMERIC(10, 2),
    format VARCHAR(20) DEFAULT 'mp3',
    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_long_text_audio_user 
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 添加表注释
COMMENT ON TABLE long_text_audio IS '长文本转音频任务表（异步模式，支持分段合成）';
COMMENT ON COLUMN long_text_audio.id IS '主键ID';
COMMENT ON COLUMN long_text_audio.user_id IS '用户ID（外键关联users表）';
COMMENT ON COLUMN long_text_audio.task_id IS '任务ID（本地生成UUID）';
COMMENT ON COLUMN long_text_audio.model IS 'TTS模型';
COMMENT ON COLUMN long_text_audio.voice IS '音色ID';
COMMENT ON COLUMN long_text_audio.text IS '原始长文本';
COMMENT ON COLUMN long_text_audio.text_length IS '文本总长度';
COMMENT ON COLUMN long_text_audio.segment_count IS '分段数量';
COMMENT ON COLUMN long_text_audio.segments IS '分段信息（JSONB数组）[{index, text, task_id, audio_url, duration, status}]';
COMMENT ON COLUMN long_text_audio.audio_url IS '最终拼接音频URL';
COMMENT ON COLUMN long_text_audio.status IS '任务状态（PENDING/PROCESSING/SUCCEEDED/FAILED）';
COMMENT ON COLUMN long_text_audio.progress IS '进度百分比（0-100）';
COMMENT ON COLUMN long_text_audio.duration IS '总时长（秒）';
COMMENT ON COLUMN long_text_audio.format IS '音频格式';
COMMENT ON COLUMN long_text_audio.bill IS '费用（元）';
COMMENT ON COLUMN long_text_audio.custom_name IS '用户自定义名称';
COMMENT ON COLUMN long_text_audio.error_message IS '错误信息（失败时）';
COMMENT ON COLUMN long_text_audio.created_at IS '创建时间';
COMMENT ON COLUMN long_text_audio.updated_at IS '更新时间';
COMMENT ON COLUMN long_text_audio.completed_at IS '完成时间';

-- 创建索引（优化查询性能）
CREATE INDEX idx_long_text_audio_user_id ON long_text_audio(user_id);
CREATE INDEX idx_long_text_audio_task_id ON long_text_audio(task_id);
CREATE INDEX idx_long_text_audio_status ON long_text_audio(status);
CREATE INDEX idx_long_text_audio_created_at ON long_text_audio(created_at DESC);

-- ============================================
-- 回滚迁移: 删除表和索引
-- ============================================
-- DROP INDEX IF EXISTS idx_long_text_audio_created_at;
-- DROP INDEX IF EXISTS idx_long_text_audio_status;
-- DROP INDEX IF EXISTS idx_long_text_audio_task_id;
-- DROP INDEX IF EXISTS idx_long_text_audio_user_id;
-- DROP TABLE IF EXISTS long_text_audio;