# db.py - Database connection setup
import os
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, declarative_base
from dotenv import load_dotenv

# Load environment variables from .env.local file
env_path = os.path.join(os.path.dirname(__file__), '..', '.env.local')
load_dotenv(dotenv_path=env_path)

# PostgreSQL connection credentials from .env
DB_USERNAME = os.getenv("DB_USERNAME")
DB_PASSWORD = os.getenv("DB_PASSWORD")
DB_HOST = os.getenv("DB_HOST", "localhost")
DB_PORT = os.getenv("DB_PORT", "5432")
DB_NAME = os.getenv("DB_NAME")

# Check if all required database credentials are provided
if not all([DB_USERNAME, DB_PASSWORD, DB_NAME]):
    print("Warning: Database credentials not found in .env.local")
    print("Please set DB_USERNAME, DB_PASSWORD, and DB_NAME in .env.local")
    print("Using SQLite as fallback database")
    DATABASE_URL = "sqlite:///./kitamove.db"
else:
    # Construct the database URL in SQLAlchemy format
    DATABASE_URL = f"postgresql://{DB_USERNAME}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}"

# Create the SQLAlchemy engine to connect to the database
try:
    engine = create_engine(DATABASE_URL)
    # Test the connection
    with engine.connect() as conn:
        pass
    print(f"Database connected successfully: {DATABASE_URL}")
except Exception as e:
    print(f"Database connection failed: {e}")
    print("Falling back to SQLite database")
    DATABASE_URL = "sqlite:///./kitamove.db"
    engine = create_engine(DATABASE_URL)

# Create a configured "Session" class
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

# Base class for ORM models
Base = declarative_base()

# Function to initialize all tables defined by ORM models
def init_table():
    Base.metadata.create_all(bind=engine)

# Dependency function to get a database session
# This is used with FastAPI's Depends to provide a session for each request
def get_db():
    db = SessionLocal()  # Create a new session
    try:
        yield db        # Provide session to caller
    finally:
        db.close()      # Ensure session is closed after use
