from collections.abc import Generator
from sqlmodel import SQLModel, Session, create_engine
from sqlalchemy import text
from sqlalchemy.pool import StaticPool

from app.core.config import Settings, get_settings

_engine = None


def init_db(settings: Settings | None = None):
    global _engine
    settings = settings or get_settings()
    connect_args = {}
    engine_kwargs = {}
    if settings.database_url.startswith("sqlite"):
        connect_args = {"check_same_thread": False}
        if settings.database_url == "sqlite://":
            engine_kwargs["poolclass"] = StaticPool
    _engine = create_engine(settings.database_url, connect_args=connect_args, **engine_kwargs)
    import app.models.core  # noqa: F401
    SQLModel.metadata.create_all(_engine)
    if settings.database_url.startswith("sqlite"):
        _apply_sqlite_mvp_upgrades(_engine)
    return _engine


def _apply_sqlite_mvp_upgrades(engine):
    """Tiny MVP schema guard for local SQLite dev DBs.

    Alembic remains the right production path. This keeps the Tailnet preview DB
    usable while the product model is still moving quickly.
    """
    upgrades = {
        "videoasset": [
            ("package_id", "VARCHAR"),
            ("topic_id", "VARCHAR"),
            ("external_youtube_id", "VARCHAR"),
            ("external_tiktok_id", "VARCHAR"),
            ("source_label", "VARCHAR"),
            ("is_demo", "BOOLEAN DEFAULT 0"),
            ("import_batch_label", "VARCHAR"),
        ],
        "postdraft": [
            ("external_post_id", "VARCHAR"),
            ("source_metadata_json", "JSON DEFAULT '{}'"),
        ],
        "analyticsimportbatch": [
            ("source_label", "VARCHAR"),
            ("is_demo", "BOOLEAN DEFAULT 0"),
            ("import_batch_label", "VARCHAR"),
        ],
        "externalpost": [
            ("source_label", "VARCHAR"),
            ("is_demo", "BOOLEAN DEFAULT 0"),
            ("import_batch_label", "VARCHAR"),
            ("source_file_name", "VARCHAR"),
            ("mapping_status", "VARCHAR DEFAULT 'open'"),
            ("ignored_at", "DATETIME"),
            ("ignore_reason", "VARCHAR"),
        ],
        "analyticspostsnapshot": [
            ("source_label", "VARCHAR"),
            ("is_demo", "BOOLEAN DEFAULT 0"),
            ("import_batch_label", "VARCHAR"),
            ("source_file_name", "VARCHAR"),
        ],
        "websitecompanion": [
            ("youtube_video_id", "VARCHAR"),
            ("video_url_internal", "VARCHAR"),
            ("website_link_allowed", "BOOLEAN DEFAULT 0"),
            ("duplicate_status_json", "JSON DEFAULT '{}'"),
        ],
        "theme": [
            ("priority", "INTEGER DEFAULT 0"),
            ("updated_at", "DATETIME"),
        ],
        "productionqueueitem": [
            ("script_constraints", "VARCHAR"),
            ("visual_constraints", "VARCHAR"),
            ("voice_constraints", "VARCHAR"),
            ("expected_package_id", "VARCHAR"),
            ("actual_package_id", "VARCHAR"),
            ("video_asset_id", "VARCHAR"),
        ],
    }
    with engine.begin() as conn:
        for table, columns in upgrades.items():
            existing = {row[1] for row in conn.execute(text(f"PRAGMA table_info({table})"))}
            if not existing:
                continue
            for name, ddl_type in columns:
                if name not in existing:
                    conn.execute(text(f"ALTER TABLE {table} ADD COLUMN {name} {ddl_type}"))


def get_engine():
    global _engine
    if _engine is None:
        return init_db()
    return _engine


def get_session() -> Generator[Session, None, None]:
    with Session(get_engine()) as session:
        yield session


def reset_db_for_tests(engine=None):
    engine = engine or get_engine()
    import app.models.core  # noqa: F401
    SQLModel.metadata.drop_all(engine)
    SQLModel.metadata.create_all(engine)