"""真实 SQLAlchemy QueuePool、N+1、SQLite计划与Alembic迁移。"""
from pathlib import Path
import sqlite3
import tempfile

from alembic import command
from alembic.config import Config
from sqlalchemy import ForeignKey, create_engine, event, select, text
from sqlalchemy.exc import TimeoutError as PoolTimeout
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column, relationship, selectinload
from sqlalchemy.pool import QueuePool


class Base(DeclarativeBase):
    pass


class Parent(Base):
    __tablename__ = "parents"
    id: Mapped[int] = mapped_column(primary_key=True)
    children: Mapped[list["Child"]] = relationship()


class Child(Base):
    __tablename__ = "children"
    id: Mapped[int] = mapped_column(primary_key=True)
    parent_id: Mapped[int] = mapped_column(ForeignKey("parents.id"), index=True)


def migrate(url, directory):
    scripts = directory / "migrations"
    (scripts / "versions").mkdir(parents=True)
    (scripts / "env.py").write_text('''from alembic import context
from sqlalchemy import create_engine
from sqlalchemy.pool import NullPool
engine = create_engine(context.config.get_main_option("sqlalchemy.url"), poolclass=NullPool)
try:
    with engine.connect() as connection:
        context.configure(connection=connection)
        with context.begin_transaction():
            context.run_migrations()
finally:
    engine.dispose()
''', encoding="utf-8")
    (scripts / "versions" / "0001_jobs.py").write_text('''from alembic import op
import sqlalchemy as sa
revision = "0001"
down_revision = None
def upgrade():
    op.create_table("jobs", sa.Column("id", sa.Integer, primary_key=True))
def downgrade():
    op.drop_table("jobs")
''', encoding="utf-8")
    (scripts / "versions" / "0002_source.py").write_text('''from alembic import op
import sqlalchemy as sa
revision = "0002"
down_revision = "0001"
def upgrade():
    op.add_column("jobs", sa.Column("source", sa.String(32), nullable=True))
def downgrade():
    raise RuntimeError("refuse destructive downgrade; preserve source data")
''', encoding="utf-8")
    config = Config()
    config.set_main_option("script_location", str(scripts))
    config.set_main_option("sqlalchemy.url", url)
    command.upgrade(config, "0001")
    engine = create_engine(url)
    try:
        with engine.begin() as connection:
            connection.execute(text("INSERT INTO jobs(id) VALUES(1)"))
        command.upgrade(config, "head")
        with engine.begin() as connection:
            assert connection.execute(text("SELECT id,source FROM jobs")).all() == [(1, None)]
            connection.execute(text("UPDATE jobs SET source='api' WHERE id=1"))
            assert connection.execute(text("SELECT version_num FROM alembic_version")).scalar_one() == "0002"
        try:
            command.downgrade(config, "0001")
        except RuntimeError as error:
            assert "destructive" in str(error)
        else:
            raise AssertionError("destructive downgrade accepted")
        print("migration: old row preserved; destructive downgrade rejected")
    finally:
        engine.dispose()


def main():
    with tempfile.TemporaryDirectory() as directory:
        root = Path(directory)
        url = "sqlite:///" + (root / "example.sqlite").as_posix()
        engine = create_engine(url, poolclass=QueuePool, pool_size=1,
                               max_overflow=0, pool_timeout=0.05)
        try:
            held = engine.connect()
            try:
                assert engine.pool.checkedout() == 1
                try:
                    engine.connect()
                except PoolTimeout:
                    print("pool: checkout=1 second borrower times out")
                else:
                    raise AssertionError("pool limit ignored")
            finally:
                held.close()
            assert engine.pool.checkedout() == 0
            with engine.connect() as connection:
                assert connection.execute(text("SELECT 1")).scalar_one() == 1
            Base.metadata.create_all(engine)
            with Session(engine) as session:
                session.add_all([Parent(id=1, children=[Child(id=1), Child(id=2)]),
                                 Parent(id=2, children=[Child(id=3)])])
                session.commit()
            observed = []

            def count_select(connection, cursor, statement, parameters, context, executemany):
                if statement.lstrip().upper().startswith("SELECT"):
                    observed.append(statement)

            event.listen(engine, "before_cursor_execute", count_select)
            results = []
            for eager in [False, True]:
                observed.clear()
                with Session(engine) as session:  # 新会话，避免身份映射掩盖查询。
                    query = select(Parent).order_by(Parent.id)
                    if eager:
                        query = query.options(selectinload(Parent.children))
                    result = [(parent.id, len(parent.children)) for parent in session.scalars(query)]
                expected = 2 if eager else 3
                assert len(observed) == expected, observed
                print("eager:", eager, "SELECT count:", len(observed))
                results.append(result)
            assert results[0] == results[1] == [(1, 2), (2, 1)]
            event.remove(engine, "before_cursor_execute", count_select)
            with engine.connect() as connection:
                plan = connection.execute(text("EXPLAIN QUERY PLAN SELECT * FROM children WHERE parent_id=1")).all()
                assert any("INDEX" in row[-1] for row in plan)
                print("SQLite plan:", plan)
            assert engine.pool.checkedout() == 0
        finally:
            engine.dispose()
        migrate(url, root)
        print("SQLite:", sqlite3.sqlite_version)


if __name__ == "__main__":
    main()
