import pytest
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from backend.app.db.base import Base
from sqlmodel import SQLModel
import backend.app.db.models.organisation  # noqa: F401 — registers Organisation with SQLModel.metadata


@pytest.fixture(scope="function")
def engine(postgresql):
    """
    Create a SQLAlchemy engine bound to the ephemeral
    pytest-postgresql database.
    """

    # Build SQLAlchemy URL from psycopg connection info
    connection_string = (
        f"postgresql+psycopg://"
        f"{postgresql.info.user}:"
        f"{postgresql.info.password}@"
        f"{postgresql.info.host}:"
        f"{postgresql.info.port}/"
        f"{postgresql.info.dbname}"
    )

    engine = create_engine(connection_string)

    # Create tables once per test session
    Base.metadata.create_all(engine)
    SQLModel.metadata.create_all(engine)

    # Yeild will split this function into two phase. 1) setup and 2) teardown, the latter of which will run after all
    # tests have completed
    yield engine

    # Clean-up after entire test session
    SQLModel.metadata.drop_all(engine)
    Base.metadata.drop_all(engine)
    engine.dispose()


@pytest.fixture(scope="function")
def db_session(engine):
    """
    Provides a clean transactional session per test.

    Rolls back after each test to keep isolation.
    """

    connection = engine.connect()
    transaction = connection.begin()

    session = sessionmaker(bind=connection)()

    yield session

    session.close()
    transaction.rollback()
    connection.close()
