from collections.abc import Generator

from sqlalchemy import create_engine, inspect, text
from sqlalchemy.orm import DeclarativeBase, Session, sessionmaker

from app.config import get_settings

settings = get_settings()
database_url = settings.sqlalchemy_url
connect_args = {"check_same_thread": False} if database_url.startswith("sqlite") else {}
engine = create_engine(database_url, pool_pre_ping=True, connect_args=connect_args)
SessionLocal = sessionmaker(bind=engine, autoflush=False, autocommit=False)


class Base(DeclarativeBase):
    pass


def ensure_schema() -> None:
    """Add newly introduced columns on existing databases (create_all won't alter)."""
    inspector = inspect(engine)
    tables = set(inspector.get_table_names())
    statements: list[str] = []

    if "enquiries" in tables:
        columns = {col["name"] for col in inspector.get_columns("enquiries")}
        if "start_time" not in columns:
            statements.append("ALTER TABLE enquiries ADD COLUMN start_time TIME")
        if "end_time" not in columns:
            statements.append("ALTER TABLE enquiries ADD COLUMN end_time TIME")

    if "products" in tables:
        columns = {col["name"] for col in inspector.get_columns("products")}
        if "video_url" not in columns:
            statements.append("ALTER TABLE products ADD COLUMN video_url VARCHAR(500)")
        if "slug" not in columns:
            statements.append("ALTER TABLE products ADD COLUMN slug VARCHAR(180)")

    if statements:
        with engine.begin() as conn:
            for statement in statements:
                conn.execute(text(statement))

    # Backfill product slugs (runs even when column already existed).
    if "products" in tables:
        from app.models import Product
        from app.utils import unique_product_slug

        with SessionLocal() as db:
            rows = db.query(Product).filter((Product.slug.is_(None)) | (Product.slug == "")).all()
            if rows:
                for row in rows:
                    base = row.name or row.public_id or f"product-{row.id}"
                    row.slug = unique_product_slug(db, base, exclude_id=row.id)
                db.commit()


def get_db() -> Generator[Session, None, None]:
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()