python/migrations/env.py

"""
Alembic Environment — async PostgreSQL (asyncpg + SQLAlchemy 2.x)

Reads DATABASE_URL from python/.env automatically.
Imports Base.metadata from models so --autogenerate works correctly.

Commands:
    # Generate a new migration (from python/ dir):
    alembic revision --autogenerate -m "describe change"

    # Apply all pending migrations:
    alembic upgrade head

    # Roll back one step:
    alembic downgrade -1
"""

import asyncio
import os
from logging.config import fileConfig

from dotenv import load_dotenv
from sqlalchemy import pool
from sqlalchemy.engine import Connection
from sqlalchemy.ext.asyncio import async_engine_from_config

from alembic import context

# ── Load .env ──────────────────────────────────────────────────────────────
# Resolve .env relative to this file (migrations/ → python/.env)
_env_path = os.path.join(os.path.dirname(__file__), "..", ".env")
load_dotenv(_env_path)

# ── Alembic Config ──────────────────────────────────────────────────────────
config = context.config

# Inject DATABASE_URL from environment so alembic.ini doesn't store secrets
config.set_main_option(
    "sqlalchemy.url",
    os.environ["DATABASE_URL"],
)

# Set up loggers from alembic.ini
if config.config_file_name is not None:
    fileConfig(config.config_file_name)

# ── Import ALL models so autogenerate sees every table ─────────────────────
from models.base import Base          # noqa: E402 F401
import models.visit_model             # noqa: E402 F401
import models.checklist_model         # noqa: E402 F401
import models.photo_model             # noqa: E402 F401

target_metadata = Base.metadata


# ── Offline migrations (generate SQL script without DB connection) ──────────
def run_migrations_offline() -> None:
    """Run migrations in 'offline' mode — outputs SQL to stdout."""
    url = config.get_main_option("sqlalchemy.url")
    context.configure(
        url=url,
        target_metadata=target_metadata,
        literal_binds=True,
        dialect_opts={"paramstyle": "named"},
        compare_type=True,        # detect column type changes
        compare_server_default=True,
    )
    with context.begin_transaction():
        context.run_migrations()


# ── Online migrations (apply directly to running DB) ───────────────────────
def do_run_migrations(connection: Connection) -> None:
    context.configure(
        connection=connection,
        target_metadata=target_metadata,
        compare_type=True,
        compare_server_default=True,
    )
    with context.begin_transaction():
        context.run_migrations()


async def run_async_migrations() -> None:
    """Create async engine and run migrations via sync connection wrapper."""
    connectable = async_engine_from_config(
        config.get_section(config.config_ini_section, {}),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )
    async with connectable.connect() as connection:
        await connection.run_sync(do_run_migrations)
    await connectable.dispose()


def run_migrations_online() -> None:
    """Entry point for online mode — runs the async migration loop."""
    asyncio.run(run_async_migrations())


# ── Dispatch ───────────────────────────────────────────────────────────────
if context.is_offline_mode():
    run_migrations_offline()
else:
    run_migrations_online()