from typing import List, Tuple, Union
from urllib.parse import urlencode, urlparse, parse_qs, unquote_plus

from sqlalchemy import create_engine as __create_engine, inspect, exc
from errors.errors import PermissionDeniedException
from tenacity import retry, stop_after_attempt
from utils.logging import logging


def create_engine_with_timeout(database_uri: str, timeout: int = 120):
    # https://stackoverflow.com/questions/35640726/how-to-set-connection-timeout-in-sqlalchemy
    kwargs = {}

    if not database_uri.startswith("sqlite://"):
        kwargs = {
            "pool_recycle": 3600,
            "pool_pre_ping": True,
            "pool_timeout": timeout,
            "connect_args": {"connect_timeout": timeout},
        }

    return __create_engine(database_uri, **kwargs)


def get_table_names(database_uri: str) -> List[str]:
    # https://stackoverflow.com/questions/35640726/how-to-set-connection-timeout-in-sqlalchemy
    engine = create_engine_with_timeout(database_uri)
    inspector = inspect(engine)
    tables = inspector.get_table_names() + inspector.get_view_names()

    return list(i.lower() for i in tables)


def get_database_from_uri(database_uri: str) -> str:
    parsed = urlparse(database_uri)
    return parsed.path[1:]


def unify_database_uri(database_uri) -> str:
    if database_uri.startswith("sqlite://"):
        return database_uri

    parsed = urlparse(database_uri)
    if "mysql" == parsed.scheme:
        qs = parse_qs(unquote_plus(parsed.query))
        if "ssl_ca" in qs:
            qs.update({
                "ssl_ca": "/etc/ssl/certs/ca-certificates.crt",
            })
            parsed = parsed._replace(query=urlencode(qs))

        return parsed._replace(scheme="mysql+pymysql").geturl()
    elif parsed.scheme == "postgresql":
        return parsed._replace(scheme="postgresql+psycopg2").geturl()
    elif parsed.scheme == "spider":
        return "sqlite:///spider/database/{}/{}.sqlite".format(parsed.netloc, parsed.netloc)
    elif parsed.scheme == "bird":
        return "sqlite:///spider/bird/dev_databases/{}/{}.sqlite".format(parsed.netloc, parsed.netloc)

    return parsed.geturl()


def check_connection_pass(database_uri) -> Tuple[bool, int, str]:
    try:
        engine = create_engine_with_timeout(database_uri)
        inspector = inspect(engine)
        tables = inspector.get_table_names() + inspector.get_view_names()

        return True, len(tables), "Successfully made your database connection."
    except Exception as e:
        logging.exception("failed to connect database %s: %s", database_uri, e)
        msg = "Failed to connect your database"
        msgs = str(e).split("\n")
        if msgs:
            msgs = msgs[0].split(") ")
            if msgs:
                msg = msg + ": " + msgs[-1]

        return False, 0, msg


@retry(stop=stop_after_attempt(3))
def check_connection_pass_by_dataapi(data_context: dict, app_id, public_key, user_token: Union[str, None]=None, trace_id: str="") -> Tuple[bool, int, str, int]:
    try:
        from svc.dataapi import get_sql_executor
        client = get_sql_executor(data_context, app_id, public_key, user_token=user_token)
        table_names = client.get_table_names()

        table_count = len(table_names)
        # if not table_count:
        #     return False, 0, "Your database is empty, please try another one.", 400

        return True, table_count, "Successfully made your database connection.", 200
    except PermissionDeniedException:
        return False, 0, "You don't have permission to access this database.", 403
    except Exception as e:
        logging.exception("failed to check_connection_pass_by_dataapi: %s", e)

        return False, 0, str(e), 400


def is_cluster_binding_mode(datacontext: dict) -> bool:
    return datacontext.get("cluster_id") is not None and datacontext.get("database") is not None


def is_sql_error(e: Exception) -> bool:
    if isinstance(e, exc.ProgrammingError):
        return True

    if isinstance(e, exc.OperationalError):
        err_msg = str(e)
        if "Unknown column" in err_msg:
            return True

        if "doesn't exist" in err_msg:
            return True

        if "The table" in err_msg and "is full" in err_msg:
            return True

    return False