-- 迁移文件: 016_create_audio_synthesis_table.sql
-- 描述: 创建语音合成记录表
-- 需求: 5.1, 5.5, 5.6

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

-- 创建audio_synthesis表
CREATE TABLE IF NOT EXISTS audio_synthesis (
    id SERIAL PRIMARY KEY,
    user_id VARCHAR(50) NOT NULL,
    model VARCHAR(100) NOT NULL,
    voice VARCHAR(100) NOT NULL,
    text TEXT NOT NULL,
    audio_url VARCHAR(500) NOT NULL,
    duration NUMERIC(10, 2),
    format VARCHAR(20),
    characters INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_audio_synthesis_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 添加表注释
COMMENT ON TABLE audio_synthesis IS '语音合成记录表';
COMMENT ON COLUMN audio_synthesis.id IS '主键ID';
COMMENT ON COLUMN audio_synthesis.user_id IS '用户ID（外键关联users表）';
COMMENT ON COLUMN audio_synthesis.model IS 'TTS模型名称';
COMMENT ON COLUMN audio_synthesis.voice IS '音色ID';
COMMENT ON COLUMN audio_synthesis.text IS '合成文本内容';
COMMENT ON COLUMN audio_synthesis.audio_url IS '音频文件OSS地址';
COMMENT ON COLUMN audio_synthesis.duration IS '音频时长（秒）';
COMMENT ON COLUMN audio_synthesis.format IS '音频格式（mp3/wav/pcm）';
COMMENT ON COLUMN audio_synthesis.characters IS '文本字符数';
COMMENT ON COLUMN audio_synthesis.created_at IS '创建时间';

-- 创建索引（优化查询性能）
CREATE INDEX IF NOT EXISTS idx_audio_synthesis_user_id ON audio_synthesis(user_id);
CREATE INDEX IF NOT EXISTS idx_audio_synthesis_created_at ON audio_synthesis(created_at DESC);

-- ============================================
-- 回滚迁移: 删除表和索引
-- ============================================
-- DROP INDEX IF EXISTS idx_audio_synthesis_created_at;
-- DROP INDEX IF EXISTS idx_audio_synthesis_user_id;
-- DROP TABLE IF EXISTS audio_synthesis;