Maas-Model-platform/backend/migrations/014_create_ai_picture_table.sql

-- 迁移文件: 014_create_ai_picture_table.sql
-- 描述: 创建AI生图记录表
-- 需求: 5.1, 5.2

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

-- 创建ai_picture表
CREATE TABLE IF NOT EXISTS ai_picture (
    id SERIAL PRIMARY KEY,
    model_id VARCHAR(100) NOT NULL,
    model_name VARCHAR(100) NOT NULL,
    user_id VARCHAR(50) NOT NULL,
    input_type VARCHAR(20) NOT NULL,
    input_data TEXT NOT NULL,
    image_count INTEGER NOT NULL DEFAULT 1,
    output_images JSONB NOT NULL,
    cost DECIMAL(10, 4) NOT NULL DEFAULT 0,
    status VARCHAR(20) NOT NULL DEFAULT 'success',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 添加表注释
COMMENT ON TABLE ai_picture IS 'AI生图记录表';
COMMENT ON COLUMN ai_picture.id IS '主键ID';
COMMENT ON COLUMN ai_picture.model_id IS '模型ID';
COMMENT ON COLUMN ai_picture.model_name IS '模型名称';
COMMENT ON COLUMN ai_picture.user_id IS '用户ID';
COMMENT ON COLUMN ai_picture.input_type IS '输入类型: text/image';
COMMENT ON COLUMN ai_picture.input_data IS '输入数据：文本或OSS地址';
COMMENT ON COLUMN ai_picture.image_count IS '生成图片数量';
COMMENT ON COLUMN ai_picture.output_images IS '输出图片OSS地址数组';
COMMENT ON COLUMN ai_picture.cost IS '费用（元）';
COMMENT ON COLUMN ai_picture.status IS '状态';
COMMENT ON COLUMN ai_picture.created_at IS '创建时间';

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

-- ============================================
-- 回滚迁移: 删除表和索引
-- ============================================
-- DROP INDEX IF EXISTS idx_ai_picture_created_at;
-- DROP INDEX IF EXISTS idx_ai_picture_user_id;
-- DROP TABLE IF EXISTS ai_picture;