import re

from parallels.core.logging import get_logger
from parallels.core.runners.base import BaseRunner
from parallels.core.utils.common import safe_string_repr
from parallels.hosting_check import DomainIssue, Severity
from parallels.hosting_check import MySQLDatabaseIssueType
from parallels.hosting_check import MySQLClientCLI
from parallels.hosting_check.utils.text_utils import format_list
from parallels.hosting_check import NonZeroExitCodeException
from parallels.hosting_check.messages import MSG

logger = get_logger(__name__)


class MySQLDatabaseChecker(object):
    def check(self, domains_to_check):
        """
        :type domains_to_check: list[parallels.hosting_check.DomainMySQLDatabases]
        """
        issues = []
        for domain_to_check in domains_to_check:
            issues += self._check_single_domain(domain_to_check)
        return issues

    def _check_single_domain(self, domain_to_check):
        """
        :type domain_to_check: parallels.hosting_check.DomainMySQLDatabases
        """
        issues = []

        def add_issue(severity, category, problem):
            issues.append(
                DomainIssue(
                    domain_name=domain_to_check.domain_name, 
                    severity=severity, 
                    category=category, 
                    problem=problem
                )
            )

        for database in domain_to_check.databases:
            if not self._database_exists_panel(
                domain_to_check, database, issues
            ):
                # skip all the other checks - no sense to check 
                # if it even was not created in the panel
                continue 

            try:
                source_tables = None
                try:
                    source_tables = _list_mysql_tables(
                        database.source_name, database.source_access
                    )
                except Exception as e:
                    logger.debug(u"Exception:", exc_info=e)
                    add_issue(
                        severity=Severity.WARNING,
                        category=MySQLDatabaseIssueType.FAILED_TO_FETCH_TABLES_FROM_SOURCE,
                        problem=MSG(
                            MySQLDatabaseIssueType.FAILED_TO_FETCH_TABLES_FROM_SOURCE,
                            host=database.source_access.host, 
                            database=database.source_name,
                        )
                    )
                    
                if source_tables is not None:
                    target_tables = _list_mysql_tables(
                        database.target_name, database.target_access
                    )
                    if not (target_tables >= source_tables):
                        add_issue(
                            severity=Severity.ERROR, 
                            category=MySQLDatabaseIssueType.DIFFERENT_TABLES_SET,
                            problem=MSG(
                                MySQLDatabaseIssueType.DIFFERENT_TABLES_SET,
                                database=database.target_name,
                                tables_list=format_list(
                                    source_tables - target_tables
                                )
                            )
                        )

                for user in database.users:
                    if not self._database_user_exists_on_target(
                        domain_to_check, database, user, issues
                    ):
                        # proceed to the next checks only if user exist on the
                        # target panel
                        continue

                    if user.password_type != 'plain':
                        add_issue(
                            severity=Severity.WARNING, 
                            category=MySQLDatabaseIssueType.ENCRYPTED_PASSWORD,
                            problem=MSG(
                                MySQLDatabaseIssueType.ENCRYPTED_PASSWORD,
                                host=database.target_access.host, 
                                database=database.target_name,
                                user=user.login,
                            )
                        )
                        continue

                    if not _check_db_access(
                        database.target_name, database.target_access, user
                    ):
                        add_issue(
                            severity=Severity.ERROR,
                            category=MySQLDatabaseIssueType.FAILED_TO_EXECUTE_SIMPLE_QUERY_AS_USER,
                            problem=MSG(
                                MySQLDatabaseIssueType.FAILED_TO_EXECUTE_SIMPLE_QUERY_AS_USER,
                                user=user.login,
                                database=database.target_name,
                            )
                        )
            except MySQLClientConnectionError as e:
                logger.debug(u"Exception:", exc_info=e)
                add_issue(
                    severity=Severity.ERROR, 
                    category=MySQLDatabaseIssueType.CONNECTION_ERROR,
                    problem=MSG(
                        MySQLDatabaseIssueType.CONNECTION_ERROR,
                        host=database.target_access.host, 
                        database=database.target_name,
                    )
                )
            except MySQLClientDatabaseDoesNotExist as e:
                logger.debug(u"Exception:", exc_info=e)
                add_issue(
                    severity=Severity.ERROR, 
                    category=MySQLDatabaseIssueType.DATABASE_DOES_NOT_EXIST,
                    problem=MSG(
                        MySQLDatabaseIssueType.DATABASE_DOES_NOT_EXIST,
                        database=database.target_name,
                        host=database.target_access.host
                    )
                )
            except MySQLClientError as e:
                logger.debug(u"Exception:", exc_info=e)
                add_issue(
                    severity=Severity.ERROR, 
                    category=MySQLDatabaseIssueType.MYSQL_CLIENT_GENERIC_ERROR,
                    problem=MSG(
                        MySQLDatabaseIssueType.MYSQL_CLIENT_GENERIC_ERROR,
                        database=database.target_name,
                        host=database.target_access.host, 
                        output=e.stdout + e.stderr
                    )
                )
            except Exception as e:
                logger.exception()
                add_issue(
                    severity=Severity.WARNING, 
                    category=MySQLDatabaseIssueType.INTERNAL_ERROR,
                    problem=MSG(
                        MySQLDatabaseIssueType.INTERNAL_ERROR,
                        reason=safe_string_repr(e),
                        database=database.target_name
                    )
                )

        return issues

    @staticmethod
    def _database_exists_panel(domain_to_check, database, issues):
        """
        :type domain_to_check: parallels.hosting_check.DomainMySQLDatabases
        :type database: parallels.hosting_check.MySQLDatabase
        :type issues: list
        """
        domain_target_panel_databases = domain_to_check.target_panel_databases

        target_panel_databases = None
        if domain_target_panel_databases is not None:
            target_panel_databases = set([])
            for db in domain_target_panel_databases:
                target_panel_databases.add(db.name)

        if (
            target_panel_databases is not None and
            database.target_name not in target_panel_databases
        ):
            issues.append(
                DomainIssue(
                    domain_name=domain_to_check.domain_name,
                    severity=Severity.ERROR, 
                    category=MySQLDatabaseIssueType.DATABASE_DOES_NOT_EXIST_IN_PANEL,
                    problem=MSG(
                        MySQLDatabaseIssueType.DATABASE_DOES_NOT_EXIST_IN_PANEL,
                        database=database.target_name
                    )
                )
            )
            return False
        else:
            return True

    @staticmethod
    def _database_user_exists_on_target(domain_to_check, database, user, issues):
        """
        :type domain_to_check: parallels.hosting_check.DomainMySQLDatabases
        :type database: parallels.hosting_check.MySQLDatabase
        :type issues: list
        """
        domain_target_panel_databases = domain_to_check.target_panel_databases

        if domain_target_panel_databases is not None:
            target_panel_databases = dict()
            for db in domain_target_panel_databases:
                target_panel_databases[db.name] = db.users
            target_panel_users = target_panel_databases[database.target_name]
        else:
            target_panel_users = None

        if (
            target_panel_users is not None and
            user.login not in target_panel_users
        ):
            issues.append(
                DomainIssue(
                    domain_name=domain_to_check.domain_name,
                    severity=Severity.ERROR, 
                    category=MySQLDatabaseIssueType.DATABASE_USER_DOES_NOT_EXIST_IN_PANEL,
                    problem=MSG(
                        MySQLDatabaseIssueType.DATABASE_USER_DOES_NOT_EXIST_IN_PANEL,
                        user=user.login, database=database.target_name
                    )
                )
            )
            return False
        else:
            return True


# noinspection PyAbstractClass
class BaseMySQLClientCLI(MySQLClientCLI):
    """Base class to execute MySQL client binary with specified arguments 
    """
    def __init__(self, server, mysql_bin='mysql', skip_secure_auth=False):
        """
        :param parallels.hosting_check.ServerBase server: server with MySQL client binary
        :param str mysql_bin: path to MySQL client binary (absolute or relative to PATH)
        :param bool skip_secure_auth: whether to pass --skip-secure-auth flag to MySQL client
        This flag is necessary when client version is greater than server version.
        Do not pass it when it is not necessary, or when client is of the same version as server.
        """
        self.server = server
        self.mysql_bin = mysql_bin
        self._skip_secure_auth = skip_secure_auth

    def use_skip_secure_auth(self):
        return self._skip_secure_auth


class UnixMySQLClientCLI(BaseMySQLClientCLI):
    """Execute MySQL client binary with specified arguments on Unix server""" 
    def execute_mysql_command(self, cmd_str, args):
        args['mysql_bin'] = self.mysql_bin
        with self.server.runner() as runner:
            assert isinstance(runner, BaseRunner)
            return runner.execute_command(
                "{mysql_bin} " + cmd_str, args
            ).stdout


class WindowsMySQLClientCLI(BaseMySQLClientCLI):
    """Execute MySQL client binary with specified arguments on Windows server
    """ 
    def execute_mysql_command(self, cmd_str, args):
        args['mysql_bin'] = self.mysql_bin
        with self.server.runner() as runner:
            assert isinstance(runner, BaseRunner)
            return runner.execute_command(
                '{mysql_bin} ' + cmd_str, args
            ).stdout


def _list_mysql_tables(database_name, mysql_database_access):
    try:
        stdout = _execute_query(
            database_name, mysql_database_access, 
            mysql_database_access.admin_user,
            'show tables'
        )
        return set(stdout.split())
    except NonZeroExitCodeException as e:
        _check_mysql_error(e.stdout, e.stderr)


def _check_db_access(database_name, mysql_database_access, user):
    try:
        stdout = _execute_query(
            database_name, mysql_database_access, user,
            'select 1'
        )
        return stdout.strip() == '1'
    except NonZeroExitCodeException:
        # simply return false if mysql client binary returned 
        # non-zero exit code
        return False


def _execute_query(database_name, database_access, user, query):
    """Execute SQL query on database with MySQL client binary

    :type database_access: parallels.hosting_check.MySQLDatabaseAccess

    Returns:
        stdout of executed command
    Raises:
        NonZeroExitCodeException if mysql client binary returned
        non zero exit code
    """
    secure_auth_option, passwd_option = ('', '')
    if database_access.mysql_client_cli.use_skip_secure_auth():
        secure_auth_option = ' --skip-secure-auth'
    if user.password:
        passwd_option = ' -p{password}'
    return database_access.mysql_client_cli.execute_mysql_command(
        "--silent%s --skip-column-names "
        "-h {host} -P {port} -u {user}%s {database} "
        "-e {query}" % (secure_auth_option, passwd_option),
        dict(
            user=user.login, password=user.password,
            host=database_access.host, port=database_access.port,
            database=database_name,
            query=query
        )
    )


def _check_mysql_error(stdout, stderr):
    for s in (stderr, stdout):
        m = re.match('^\s*ERROR (\d+)', s)
        if m is not None:
            # connection error codes, refer to MySQL guides for more
            # information
            # (https://dev.mysql.com/doc/refman/5.5/en/error-messages-client.html)
            if m.group(1) in ('2002', '2003', '2005', '2006', '2013'): 
                raise MySQLClientConnectionError(stdout, stderr)
            # unknown database error, refer to MySQL guides for more
            # information
            # (https://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html)
            if m.group(1) == '1049':
                raise MySQLClientDatabaseDoesNotExist(stdout, stderr)

    raise MySQLClientError(stdout, stderr)


class MySQLClientError(Exception):
    def __init__(self, stdout, stderr):
        self.stdout = stdout
        self.stderr = stderr


class MySQLClientConnectionError(MySQLClientError):
    pass


class MySQLClientDatabaseDoesNotExist(MySQLClientError):
    pass
