from sqlalchemy import Engine, create_engine, text

from tht.config import DatabaseConfig


def make_engine(cfg: DatabaseConfig) -> Engine:
    # MVP: il transport `direct` supporta SOLO PostgreSQL (driver psycopg2; introspezione
    # su cataloghi pg_*; sqlcheck/EXPLAIN dialetto postgres). Il DWH centrale di
    # produzione usa il transport `rest`. Il supporto multi-dialetto (sqlserver/mariadb/
    # informix, cfr. Thoth/thoth_sqldb2) e' lavoro futuro separato: aggiungere un campo
    # dialect/driver a DatabaseConfig e astrarre introspect/sqlcheck/connection.
    url = (
        f"postgresql+psycopg2://{cfg.user}:{cfg.password}"
        f"@{cfg.host}:{cfg.port}/{cfg.database}"
    )
    return create_engine(url, echo=False)


def ping(engine: Engine) -> None:
    with engine.connect() as conn:
        conn.execute(text("SELECT 1"))


def writable_tables(engine: Engine, schema: str) -> list[str]:
    """Tabelle dello schema su cui l'utente corrente ha privilegi di scrittura."""
    q = text("""
        SELECT c.relname
        FROM pg_class c
        JOIN pg_namespace n ON n.oid = c.relnamespace
        WHERE n.nspname = :schema
          AND c.relkind IN ('r', 'p')
          AND has_table_privilege(current_user, c.oid, 'INSERT, UPDATE, DELETE')
        ORDER BY c.relname
    """)
    with engine.connect() as conn:
        return [row[0] for row in conn.execute(q, {"schema": schema})]


def can_create_in_schema(engine: Engine, schema: str) -> bool:
    q = text("SELECT has_schema_privilege(current_user, :schema, 'CREATE')")
    with engine.connect() as conn:
        return bool(conn.execute(q, {"schema": schema}).scalar())