xiaoxia-saas/alembic/versions/013_add_recipe_tables.py

"""Phase 2 - 配方复用：recipes + recipe_items

Revision ID: 013
Revises: 012
Create Date: 2026-06-29

This migration creates two new tables:
1. recipes — 配方主表
2. recipe_items — 配方素材项表
"""

import sqlalchemy as sa

from alembic import op

# revision identifiers
revision = "013"
down_revision = "012"
branch_labels = None
depends_on = None


def upgrade() -> None:
    conn = op.get_bind()

    # ── 1. Create recipes table ──

    conn.execute(sa.text("""
        CREATE TABLE IF NOT EXISTS recipes (
            id VARCHAR(36) PRIMARY KEY,
            user_id VARCHAR(36) NOT NULL,
            name VARCHAR(200) NOT NULL,
            description TEXT NOT NULL DEFAULT '',
            template_id VARCHAR(36) NOT NULL DEFAULT '',
            generation_params JSONB NOT NULL DEFAULT '{}',
            is_active BOOLEAN NOT NULL DEFAULT TRUE,
            metadata JSONB NOT NULL DEFAULT '{}',
            created_at TIMESTAMP NOT NULL DEFAULT NOW(),
            updated_at TIMESTAMP NOT NULL DEFAULT NOW()
        )
    """))
    conn.execute(sa.text("CREATE INDEX IF NOT EXISTS ix_recipes_user_id ON recipes(user_id)"))

    # ── 2. Create recipe_items table ──

    conn.execute(sa.text("""
        CREATE TABLE IF NOT EXISTS recipe_items (
            id VARCHAR(36) PRIMARY KEY,
            recipe_id VARCHAR(36) NOT NULL,
            item_type VARCHAR(20) NOT NULL,
            item_id VARCHAR(36) NOT NULL,
            position INTEGER NOT NULL DEFAULT 0,
            metadata JSONB NOT NULL DEFAULT '{}'
        )
    """))
    conn.execute(sa.text("CREATE INDEX IF NOT EXISTS ix_recipe_items_recipe_id ON recipe_items(recipe_id)"))


def downgrade() -> None:
    conn = op.get_bind()
    conn.execute(sa.text("DROP TABLE IF EXISTS recipe_items"))
    conn.execute(sa.text("DROP TABLE IF EXISTS recipes"))