from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from app.config import settings
from app.db.database import Base

# Determine engine arguments based on the database mode from the central config
engine_args = {}
if settings.DB_MODE == "sqlite":
    # This argument is required for SQLite to allow it to be used by multiple threads.
    # We also increase the timeout for NFS environments to 30 seconds.
    engine_args["connect_args"] = {"check_same_thread": False, "timeout": 60}
    # M6: Scale pool size for high-concurrency background ops
    engine_args["pool_size"] = 20
    engine_args["max_overflow"] = 30
else:
    # 'pool_pre_ping' checks if a database connection is still alive before using it.
    # This prevents errors from connections that have been timed out by the DB server.
    engine_args["pool_pre_ping"] = True
    engine_args["pool_size"] = 20
    engine_args["max_overflow"] = 30

# Always pre-check connections
engine_args["pool_pre_ping"] = True

import os

# Create the data directory if it doesn't exist to prevent sqlite3.OperationalError
if settings.DATABASE_URL and settings.DATABASE_URL.startswith("sqlite:///"):
    db_path = settings.DATABASE_URL.replace("sqlite:///", "")
    os.makedirs(os.path.dirname(db_path), exist_ok=True)

# Create the SQLAlchemy engine using the centralized URL and determined arguments
engine = create_engine(settings.DATABASE_URL, **engine_args)

# M6: Speed up SQLite on NFS by using Write-Ahead Logging (WAL) and reducing sync level
if settings.DB_MODE == "sqlite":
    from sqlalchemy import event
    @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.close()

# SessionLocal is a factory for creating new database session objects.
# It's the standard way to interact with the database in SQLAlchemy.
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

from contextlib import contextmanager

@contextmanager
def get_db_session():
    """
    Context-manager database session for use outside of FastAPI request scope.
    Used by background services (e.g. NodeRegistryService) that need their own session.
    VERSION M6: No auto-commit. Caller must session.commit() explicitly.
    """
    db = SessionLocal()
    try:
        yield db
    except Exception:
        db.rollback()
        raise
    finally:
        db.close()

import asyncio
from functools import partial

async def async_db_op(func, *args, **kwargs):
    """Refactored M6: Run a DB-blocking function in a separate thread to keep the event loop alive."""
    loop = asyncio.get_running_loop()
    return await loop.run_in_executor(None, partial(func, *args, **kwargs))

def create_db_and_tables():
    """
    Creates all database tables defined by models inheriting from Base.
    This is typically called once on application startup.
    """
    print("Creating database tables...")
    # Base.metadata contains all the schema information from your models.
    Base.metadata.create_all(bind=engine)