from sqlalchemy import create_engine, event
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from pathlib import Path

# Import DB path from existing database.py
import sys
sys.path.append(str(Path(__file__).parent.parent))
from database import DB_PATH

# Create SQLAlchemy engine with same optimizations as raw SQL connection
engine = create_engine(
    f'sqlite:///{DB_PATH}',
    connect_args={
        "check_same_thread": False,
        "timeout": 30,
    },
    echo=False,
    pool_pre_ping=True,
    pool_size=5,
    max_overflow=10,
)

# Apply SQLite PRAGMA optimizations on connect
@event.listens_for(engine, "connect")
def set_sqlite_pragma(dbapi_conn, connection_record):
    """Set SQLite PRAGMA settings for performance and reliability"""
    cursor = dbapi_conn.cursor()
    cursor.execute("PRAGMA foreign_keys = ON")
    cursor.execute("PRAGMA journal_mode = WAL")
    cursor.execute("PRAGMA synchronous = NORMAL")
    cursor.execute("PRAGMA busy_timeout = 5000")
    cursor.execute("PRAGMA cache_size = -20000")
    cursor.execute("PRAGMA temp_store = MEMORY")
    try:
        cursor.execute("PRAGMA mmap_size = 268435456")
    except Exception:
        pass
    cursor.close()

# Create session factory
SessionLocal = sessionmaker(
    autocommit=False,
    autoflush=False,
    bind=engine
)

# Create declarative base for models
Base = declarative_base()


def get_db():
    """
    Dependency function for FastAPI routes to get database session.
    
    Usage in routes:
        @app.get("/example")
        def example(db: Session = Depends(get_db)):
            # Use db.query() here
            pass
    """
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()