"""
db.py
-----
Conexión centralizada a SQL Server (local + Streamlit Cloud).

- Local: lee variables desde .env (python-dotenv) y/o variables de entorno.
- Streamlit Cloud: lee desde st.secrets (Settings -> Secrets).
- Nunca hardcodear credenciales.

Requisitos:
- python-dotenv
- sqlalchemy
- pyodbc
"""

from __future__ import annotations

import os
from urllib.parse import quote_plus

import sqlalchemy as sa
from dotenv import load_dotenv

try:
    import streamlit as st
except Exception:
    st = None


def _get_env_bool(name: str, default: bool = False) -> bool:
    raw = _get(name, None)
    if raw is None:
        return default
    return str(raw).strip().lower() in {"1", "true", "yes", "y", "on"}


def _get(name: str, default: str | None = "") -> str | None:
    """
    Lee configuración en este orden:
    1) Variables de entorno (os.getenv)
    2) Streamlit secrets (st.secrets) (Cloud)
       - soporta claves top-level: st.secrets["CABANNA_DB_SERVER"]
       - soporta sección [cabanna]: st.secrets["cabanna"]["CABANNA_DB_SERVER"]
    """
    v = os.getenv(name)
    if v is not None and str(v).strip() != "":
        return str(v).strip()

    if st is not None:
        try:
            # top-level
            if name in st.secrets:
                return str(st.secrets[name]).strip()

            # sección [cabanna]
            if "cabanna" in st.secrets and name in st.secrets["cabanna"]:
                return str(st.secrets["cabanna"][name]).strip()
        except Exception:
            pass

    return default


def get_engine() -> sa.Engine:
    """
    Variables requeridas:
    - CABANNA_DB_SERVER
    - CABANNA_DB_PORT (default 1433)
    - CABANNA_DB_NAME
    - CABANNA_DB_USER
    - CABANNA_DB_PASSWORD
    - CABANNA_ODBC_DRIVER (default: ODBC Driver 18 for SQL Server)
    - CABANNA_TRUST_SERVER_CERT (opcional: true/false)
    """
    # Carga .env si existe (local)
    load_dotenv()

    server = _get("CABANNA_DB_SERVER", "") or ""
    port = _get("CABANNA_DB_PORT", "1433") or "1433"
    db = _get("CABANNA_DB_NAME", "") or ""
    user = _get("CABANNA_DB_USER", "") or ""
    pwd = _get("CABANNA_DB_PASSWORD", "") or ""

    driver = _get("CABANNA_ODBC_DRIVER", "ODBC Driver 18 for SQL Server") or "ODBC Driver 18 for SQL Server"
    trust_cert = _get_env_bool("CABANNA_TRUST_SERVER_CERT", default=True)

    missing = [k for k, v in {
        "CABANNA_DB_SERVER": server,
        "CABANNA_DB_NAME": db,
        "CABANNA_DB_USER": user,
        "CABANNA_DB_PASSWORD": pwd,
    }.items() if not v]

    if missing:
        raise ValueError(f"Faltan variables de entorno/secrets para DB: {', '.join(missing)}")

    driver_enc = quote_plus(driver)
    trust_param = "&TrustServerCertificate=yes" if trust_cert else ""

    conn_str = (
        f"mssql+pyodbc://{user}:{pwd}@{server},{port}/{db}"
        f"?driver={driver_enc}"
        f"{trust_param}"
    )

    return sa.create_engine(conn_str, pool_pre_ping=True)