app/database.py

"""
Database Configuration Module for Smart Plant Growth Chamber (PGC)

This module establishes the SQLAlchemy engine, session factory, and base class
for ORM models. It uses SQLite as the local edge database, suitable for the
Beelink GK Mini PC deployment environment.

Architecture Notes:
- Uses SQLAlchemy 2.0+ async-compatible patterns
- Session management via dependency injection in FastAPI
- Windows 11 compatible file paths with forward slashes
"""

import os
from typing import Generator
from pathlib import Path

from sqlalchemy import create_engine, event
from sqlalchemy.orm import sessionmaker, Session, declarative_base
from dotenv import load_dotenv

# Load environment variables from .env file
load_dotenv()

# Database URL configuration
# Default to SQLite database in the project root directory
# Using forward slashes for Windows compatibility with SQLAlchemy
DATABASE_URL = os.getenv("DATABASE_URL", "sqlite:///./pgc_database.db")

# SQLAlchemy Engine Configuration
# check_same_thread=False is required for SQLite with FastAPI's async handling
# This allows multiple threads to use the same connection (safe for read-heavy workloads)
engine = create_engine(
    DATABASE_URL,
    connect_args={"check_same_thread": False},  # SQLite specific
    echo=os.getenv("DEBUG", "false").lower() == "true",  # SQL logging in debug mode
    pool_pre_ping=True,  # Verify connections before use
)

# Enable foreign key constraints for SQLite
# SQLite doesn't enforce FK constraints by default
@event.listens_for(engine, "connect")
def enable_sqlite_fk(dbapi_connection, connection_record):
    """Enable foreign key constraint enforcement for SQLite connections."""
    cursor = dbapi_connection.cursor()
    cursor.execute("PRAGMA foreign_keys=ON")
    cursor.close()


# Session Factory
# autocommit=False: Explicit transaction control
# autoflush=False: Manual flush for better performance control
SessionLocal = sessionmaker(
    autocommit=False,
    autoflush=False,
    bind=engine,
)

# Declarative Base for ORM Models
# All models inherit from this base class
Base = declarative_base()


def get_db() -> Generator[Session, None, None]:
    """
    Dependency injection function for FastAPI endpoints.
    
    Provides a database session that is automatically closed after the request.
    Usage in FastAPI:
        @app.get("/items")
        def read_items(db: Session = Depends(get_db)):
            ...
    
    Yields:
        Session: SQLAlchemy database session
    
    Note:
        The session is automatically closed in the finally block,
        ensuring proper resource cleanup even if an exception occurs.
    """
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()


def init_db() -> None:
    """
    Initialize the database by creating all tables.
    
    This function should be called during application startup.
    It creates all tables defined in the ORM models if they don't exist.
    
    Note:
        Import models here to ensure they are registered with Base.metadata
        before create_all() is called.
    """
    # Import models to register them with Base.metadata
    from app import models  # noqa: F401
    
    Base.metadata.create_all(bind=engine)