#数据库迁移工具 Alembic：版本化管理你的数据库表结构

# alembic/env.py
from logging.config import fileConfig
from sqlalchemy import engine_from_config, pool
from alembic import context

# 导入你的模型（重要！Alembic 据此生成迁移脚本）
from models import Base  # SQLAlchemy Base
from config import get_settings

config = context.config
settings = get_settings()

# 设置数据库 URL
config.set_main_option("sqlalchemy.url", settings.database_url)

if config.config_file_name is not None:
    fileConfig(config.config_file_name)

target_metadata = Base.metadata


def run_migrations_offline() -> None:
    """离线模式：生成 SQL 脚本，不连接数据库"""
    url = config.get_main_option("sqlalchemy.url")
    context.configure(
        url=url,
        target_metadata=target_metadata,
        literal_binds=True,
        dialect_opts={"paramstyle": "named"},
    )
    with context.begin_transaction():
        context.run_migrations()


def run_migrations_online() -> None:
    """在线模式：直接连接数据库执行迁移"""
    connectable = engine_from_config(
        config.get_section(config.config_ini_section),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )
    with connectable.connect() as connection:
        context.configure(connection=connection, target_metadata=target_metadata)
        with context.begin_transaction():
            context.run_migrations()


if context.is_offline_mode():
    run_migrations_offline()
else:
    run_migrations_online()

# alembic/versions/20260326_add_user_avatar.py
"""add user avatar column

Revision ID: abc123
Revises: def456
Create Date: 2026-03-26 14:00:00.000000

"""
from alembic import op
import sqlalchemy as sa

# revision identifiers
revision = 'abc123'
down_revision = 'def456'
branch_labels = None
depends_on = None

def upgrade() -> None:
    op.add_column('users', sa.Column('avatar', sa.String(length=500), nullable=True))

def downgrade() -> None:
    op.drop_column('users', 'avatar')

# 当 autogenerate 无法处理时，手动编写
def upgrade() -> None:
    # 添加列
    op.add_column("users", sa.Column("phone", sa.String(20)))

    # 重命名列
    op.alter_column("users", "name", new_column_name="display_name")

    # 创建索引
    op.create_index("ix_users_email", "users", ["email"], unique=True)

    # 创建外键
    op.create_foreign_key(
        "fk_posts_author", "posts", "users", ["author_id"], ["id"]
    )

def downgrade() -> None:
    op.drop_column("users", "phone")
    op.drop_index("ix_users_email", table_name="users")

# 003_add_comments.py
down_revision = "002_add_posts"  # 指向 002

# 002_add_posts.py
down_revision = "001_add_users"  # 指向 001

# 001_add_users.py
down_revision = None  # 初始版本

# 迁移链 A: base → 001a → 002a
# 迁移链 B: base → 001b → 002b

# 合并后：
# base → 001a → 002a → 003_merge → 001b → 002b → head
# 或者合并到统一的主线

# config.py
import os

class Settings:
    env = os.getenv("ENV", "development")

    databases = {
        "development": "postgresql+asyncpg://localhost:5432/myapp_dev",
        "testing": "postgresql+asyncpg://localhost:5432/myapp_test",
        "production": "postgresql+asyncpg://user:pass@db.example.com/myapp",
    }

    @property
    def database_url(self):
        return self.databases.get(self.env, self.databases["development"])