-- 迁移文件: 019_create_system_voice_table.sql
-- 描述: 创建系统预置音色表
-- 需求: 5.4, 5.5, 5.6

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

-- 创建system_voice表
CREATE TABLE IF NOT EXISTS system_voice (
    id SERIAL PRIMARY KEY,
    voice_id VARCHAR(100) NOT NULL UNIQUE,
    name VARCHAR(50) NOT NULL,
    trait VARCHAR(100),
    age VARCHAR(20),
    category VARCHAR(50),
    languages JSONB DEFAULT '[]',
    models JSONB DEFAULT '[]',
    ssml_support BOOLEAN DEFAULT FALSE,
    instruct_support BOOLEAN DEFAULT FALSE,
    timestamp_support BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 添加表注释
COMMENT ON TABLE system_voice IS '系统预置音色表';
COMMENT ON COLUMN system_voice.id IS '主键ID';
COMMENT ON COLUMN system_voice.voice_id IS '音色ID';
COMMENT ON COLUMN system_voice.name IS '音色名称';
COMMENT ON COLUMN system_voice.trait IS '音色特质描述';
COMMENT ON COLUMN system_voice.age IS '年龄段（青年/中年/老年）';
COMMENT ON COLUMN system_voice.category IS '场景分类（通用/客服/新闻/有声书等）';
COMMENT ON COLUMN system_voice.languages IS '支持的语言列表';
COMMENT ON COLUMN system_voice.models IS '支持的TTS模型列表';
COMMENT ON COLUMN system_voice.ssml_support IS '是否支持SSML';
COMMENT ON COLUMN system_voice.instruct_support IS '是否支持指令控制';
COMMENT ON COLUMN system_voice.timestamp_support IS '是否支持时间戳';
COMMENT ON COLUMN system_voice.is_active IS '是否启用';
COMMENT ON COLUMN system_voice.created_at IS '创建时间';

-- 创建索引（优化查询性能）
CREATE INDEX IF NOT EXISTS idx_system_voice_voice_id ON system_voice(voice_id);
CREATE INDEX IF NOT EXISTS idx_system_voice_category ON system_voice(category);
CREATE INDEX IF NOT EXISTS idx_system_voice_is_active ON system_voice(is_active);

-- ============================================
-- 回滚迁移: 删除表和索引
-- ============================================
-- DROP INDEX IF EXISTS idx_system_voice_is_active;
-- DROP INDEX IF EXISTS idx_system_voice_category;
-- DROP INDEX IF EXISTS idx_system_voice_voice_id;
-- DROP TABLE IF EXISTS system_voice;