-- 迁移文件: 017_create_voice_clone_table.sql
-- 描述: 创建声音复刻音色表
-- 需求: 5.2, 5.5, 5.6

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

-- 创建voice_clone表
CREATE TABLE IF NOT EXISTS voice_clone (
    id SERIAL PRIMARY KEY,
    user_id VARCHAR(50) NOT NULL,
    voice_id VARCHAR(200) NOT NULL UNIQUE,
    target_model VARCHAR(100) NOT NULL,
    prefix VARCHAR(20) NOT NULL,
    status VARCHAR(20) DEFAULT 'DEPLOYING',
    audio_url VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_voice_clone_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 添加表注释
COMMENT ON TABLE voice_clone IS '声音复刻音色表';
COMMENT ON COLUMN voice_clone.id IS '主键ID';
COMMENT ON COLUMN voice_clone.user_id IS '用户ID（外键关联users表）';
COMMENT ON COLUMN voice_clone.voice_id IS 'DashScope返回的音色ID';
COMMENT ON COLUMN voice_clone.target_model IS '目标TTS模型';
COMMENT ON COLUMN voice_clone.prefix IS '音色前缀名称';
COMMENT ON COLUMN voice_clone.status IS '音色状态（DEPLOYING/DEPLOYED/FAILED）';
COMMENT ON COLUMN voice_clone.audio_url IS '原始音频文件OSS地址';
COMMENT ON COLUMN voice_clone.created_at IS '创建时间';
COMMENT ON COLUMN voice_clone.updated_at IS '更新时间';

-- 创建索引（优化查询性能）
CREATE INDEX IF NOT EXISTS idx_voice_clone_user_id ON voice_clone(user_id);
CREATE INDEX IF NOT EXISTS idx_voice_clone_voice_id ON voice_clone(voice_id);
CREATE INDEX IF NOT EXISTS idx_voice_clone_status ON voice_clone(status);
CREATE INDEX IF NOT EXISTS idx_voice_clone_created_at ON voice_clone(created_at DESC);

-- ============================================
-- 回滚迁移: 删除表和索引
-- ============================================
-- DROP INDEX IF EXISTS idx_voice_clone_created_at;
-- DROP INDEX IF EXISTS idx_voice_clone_status;
-- DROP INDEX IF EXISTS idx_voice_clone_voice_id;
-- DROP INDEX IF EXISTS idx_voice_clone_user_id;
-- DROP TABLE IF EXISTS voice_clone;