import os
from sqlmodel import create_engine, Session
from dotenv import load_dotenv

# Load environment variables from .env
load_dotenv()

# Get DATABASE_URL from .env
DATABASE_URL = os.getenv("DATABASE_URL")
if DATABASE_URL and "localhost" in DATABASE_URL:
    DATABASE_URL = DATABASE_URL.replace("localhost", "127.0.0.1")
if not DATABASE_URL:
    # Use in-memory SQLite as fallback (e.g. during test collection/execution)
    DATABASE_URL = "sqlite:///:memory:"

# Create the engine (tune pool settings as needed)
if DATABASE_URL.startswith("sqlite"):
    from sqlalchemy.pool import StaticPool
    from sqlalchemy import event
    engine = create_engine(
        DATABASE_URL,
        connect_args={"check_same_thread": False},
        poolclass=StaticPool if DATABASE_URL == "sqlite:///:memory:" else None,
        echo=False
    )
    @event.listens_for(engine, "connect")
    def set_sqlite_pragma(dbapi_connection, connection_record):
        cursor = dbapi_connection.cursor()
        cursor.execute("PRAGMA journal_mode=WAL")
        cursor.execute("PRAGMA synchronous=NORMAL")
        cursor.execute("PRAGMA cache_size=10000")
        cursor.execute("PRAGMA temp_store=MEMORY")
        cursor.close()
else:
    engine = create_engine(
        DATABASE_URL,
        echo=False,  # Set to True for SQL debugging
        pool_size=10,
        max_overflow=20,
        pool_timeout=30,
        pool_recycle=1800,
        pool_pre_ping=True,  # Test connections before using them to prevent "server has gone away" errors
    )

# Dependency for FastAPI routes
def get_session():
    with Session(engine) as session:
        yield session
