import logging
import os
from urllib.parse import parse_qsl, urlencode, urlparse, urlunparse

from dotenv import load_dotenv
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker, declarative_base

# Load environment variables from .env if present
load_dotenv()

_log = logging.getLogger("app.database")


def _normalize_database_url(raw: str) -> tuple[str, dict]:
    """Normalize Postgres DB URL for SQLAlchemy.

    Accepts Prisma-style ``?schema=public`` (stripped into search_path)
    and ``postgres://`` aliases. SQLite is not supported.
    """
    url = (raw or "").strip()
    if not url:
        raise RuntimeError(
            "DB_URL (or DATABASE_URL) is required. "
            "Set a PostgreSQL URL, e.g. "
            "postgresql://user:pass@host:5432/dbname?schema=public"
        )

    if url.startswith("postgres://"):
        url = "postgresql://" + url[len("postgres://") :]

    if url.startswith("sqlite"):
        raise RuntimeError(
            "SQLite is no longer supported. Set DB_URL to PostgreSQL, e.g. "
            "postgresql://user:pass@host:5432/dbname?schema=public"
        )

    if not url.startswith("postgresql"):
        raise RuntimeError(
            f"Unsupported database URL scheme (expected postgresql): {url.split('://', 1)[0]}"
        )

    parsed = urlparse(url)
    query = dict(parse_qsl(parsed.query, keep_blank_values=True))
    schema = query.pop("schema", None)
    connect_args: dict = {}
    if schema:
        connect_args["options"] = f"-csearch_path={schema}"

    cleaned = urlunparse(parsed._replace(query=urlencode(query)))
    return cleaned, connect_args


# Prefer DB_URL (project convention) then DATABASE_URL — Postgres required.
_raw_url = os.getenv("DB_URL") or os.getenv("DATABASE_URL") or ""
DATABASE_URL, _CONNECT_ARGS = _normalize_database_url(_raw_url)

engine = create_engine(
    DATABASE_URL,
    connect_args=_CONNECT_ARGS,
    pool_pre_ping=True,
    future=True,
)

# Session factory
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine, future=True)

# Base class for ORM models
Base = declarative_base()


def ensure_schema() -> None:
    """Create ORM tables if missing (idempotent).

    Prefer ``alembic upgrade head`` in staging/prod; this is a safety net when
    migrations have not been applied yet.
    """
    # Import models so they register on Base.metadata before create_all.
    import app.models.manager_clone  # noqa: F401
    # interview_sessions: LEGACY — /interviews/sessions/* removed (Platform owns
    # live sessions). Keep existing PG rows; do not create_all this table on new
    # installs. Re-enable import only if session APIs return.
    # import app.models.interview_session  # noqa: F401
    import app.models.gateway_persist  # noqa: F401
    import app.models.jobs  # noqa: F401
    import app.models.candidate_performance  # noqa: F401
    import app.models.interview_workflow  # noqa: F401
    import app.models.interview_evaluation  # noqa: F401
    import app.models.persona  # noqa: F401
    import app.models.resume_profile  # noqa: F401

    Base.metadata.create_all(bind=engine)
    _log.info(
        "DB schema ready (%s)",
        DATABASE_URL.split("://", 1)[0] if "://" in DATABASE_URL else "db",
    )


def schema_has_manager_clone_tables() -> bool:
    """Quick check used by health/debug paths."""
    try:
        with engine.connect() as conn:
            rows = conn.execute(
                text(
                    "SELECT 1 FROM information_schema.tables "
                    "WHERE table_name='manager_clone_versions'"
                )
            ).fetchall()
            return bool(rows)
    except Exception:
        return False


# Dependency for FastAPI routes
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()
