"""
Database connection and session management
"""
from sqlmodel import SQLModel, create_engine, Session
from typing import Generator
from loguru import logger
import os
from pathlib import Path

# Get project root directory
PROJECT_ROOT = Path(__file__).parent.parent.parent.parent
DATA_DIR = PROJECT_ROOT / "data"

# Create directories
DATA_DIR.mkdir(exist_ok=True)

# Database URL (absolute path)
DATABASE_URL = os.getenv(
    "DATABASE_URL",
    f"sqlite:///{DATA_DIR}/hk_racing.db"
)

# Create engine
engine = create_engine(DATABASE_URL, echo=False)


def create_db_and_tables():
    """Create database and tables"""
    logger.info("Creating database tables...")
    SQLModel.metadata.create_all(engine)
    logger.info(f"✅ Database tables created at {DATA_DIR}/hk_racing.db")


def get_session() -> Generator[Session, None, None]:
    """Get database session"""
    with Session(engine) as session:
        yield session


def init_db():
    """Initialize database"""
    create_db_and_tables()