from parallels.core.logging import get_logger
from parallels.core.runners.entities import MSSQLConnectionSettings
from parallels.core.runners.exceptions.mssql import MSSQLException
from parallels.core.utils.common import safe_string_repr
from parallels.hosting_check import DomainIssue, Severity
from parallels.hosting_check import MSSQLDatabaseIssueType
from parallels.hosting_check.messages import MSG
from parallels.hosting_check.utils.text_utils import format_list

logger = get_logger(__name__)


class MSSQLDatabaseChecker(object):
    def check(self, domains_to_check):
        """Run checks for specified domain

        :type domains_to_check: list[parallels.hosting_check.DomainMSSQLDatabases]
        """
        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):
        """Run checks for a single domain

        :type domain_to_check: parallels.hosting_check.DomainMSSQLDatabases
        """
        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

                if not _check_login(
                    database.target_server, database.target_access,
                    database.target_access.admin_user
                ):
                    add_issue(
                        severity=Severity.ERROR, 
                        category=MSSQLDatabaseIssueType.FAILED_TO_LOGIN_TO_TARGET_SERVER_AS_ADMIN,
                        problem=MSG(
                            MSSQLDatabaseIssueType.FAILED_TO_LOGIN_TO_TARGET_SERVER_AS_ADMIN,
                            host=database.target_access.host,
                            database=database.target_name,
                        )
                    )
                    continue

                if not _check_login(
                    database.target_server, database.target_access,
                    database.target_access.admin_user, database.target_name
                ):
                    add_issue(
                        severity=Severity.ERROR, 
                        category=MSSQLDatabaseIssueType.FAILED_TO_LOGIN_TO_TARGET_DATABASE_AS_ADMIN,
                        problem=MSG(
                            MSSQLDatabaseIssueType.FAILED_TO_LOGIN_TO_TARGET_DATABASE_AS_ADMIN,
                            database=database.target_name,
                            host=database.target_access.host,
                        )
                    )
                    continue

                try:
                    source_tables = _list_tables(
                        database.source_server, database.source_access,
                        database.source_name
                    )
                except Exception as e:
                    logger.debug(u"Exception:", exc_info=e)
                    add_issue(
                        severity=Severity.WARNING, 
                        category=MSSQLDatabaseIssueType.FAILED_TO_FETCH_TABLES_FROM_SOURCE,
                        problem=MSG(
                            MSSQLDatabaseIssueType.FAILED_TO_FETCH_TABLES_FROM_SOURCE,
                            host=database.source_access.host, 
                            database=database.source_name,
                        )
                    )

                if source_tables is not None:
                    target_tables = _list_tables(
                        database.target_server, database.target_access,
                        database.target_name
                    )
                    if not (target_tables >= source_tables):
                        add_issue(
                            severity=Severity.ERROR, 
                            category=MSSQLDatabaseIssueType.DIFFERENT_TABLES_SET,
                            problem=MSG(
                                MSSQLDatabaseIssueType.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_panel(
                        domain_to_check, database, user, issues
                    ):
                        # proceed to the next checks only if user exist on the
                        # target panel
                        continue

                    if not _check_login(
                        database.target_server, database.target_access,
                        user, database.target_name
                    ):
                        add_issue(
                            severity=Severity.ERROR, 
                            category=MSSQLDatabaseIssueType.FAILED_TO_LOGIN_AS_USER,
                            problem=MSG(
                                MSSQLDatabaseIssueType.FAILED_TO_LOGIN_AS_USER,
                                user=user.login, database=database.target_name
                            )
                        )
            except Exception as e:
                logger.exception()
                add_issue(
                    severity=Severity.WARNING, category=MSSQLDatabaseIssueType.INTERNAL_ERROR,
                    problem=MSG(
                        MSSQLDatabaseIssueType.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.DomainMSSQLDatabases
        :type database: parallels.hosting_check.MSSQLDatabase
        :type issues: list
        :rtype: bool
        """
        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=MSSQLDatabaseIssueType.DATABASE_DOES_NOT_EXIST_IN_PANEL,
                    problem=MSG(
                        MSSQLDatabaseIssueType.DATABASE_DOES_NOT_EXIST_IN_PANEL,
                        database=database.target_name,
                    )
                )
            )
            return False
        else:
            return True

    @staticmethod
    def _database_user_exists_panel(domain_to_check, database, user, issues):
        """
        :type domain_to_check: parallels.hosting_check.DomainMSSQLDatabases
        :type database: parallels.hosting_check.MSSQLDatabase
        :type user: parallels.hosting_check.User
        :type issues: list
        :rtype: bool
        """
        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=MSSQLDatabaseIssueType.DATABASE_USER_DOES_NOT_EXIST_IN_PANEL,
                    problem=MSG(
                        MSSQLDatabaseIssueType.DATABASE_USER_DOES_NOT_EXIST_IN_PANEL,
                        user=user.login, database=database.target_name,
                    )
                )
            )
            return False
        else:
            return True


def _list_tables(server, database_access, database_name):
    """List tables of specified database

    :type server: parallels.hosting_check.ServerBase
    :type database_access: parallels.hosting_check.MSSQLDatabaseSourceAccess
    :type database_name: str | unicode
    :rtype: set[str | unicode]
    """
    connection_settings = MSSQLConnectionSettings(
        host=database_access.host, user=database_access.admin_user.login,
        password=database_access.admin_user.password, database=database_name
    )
    with server.runner() as runner:
        results = runner.mssql_query(
            connection_settings,
            "SELECT name FROM sysobjects "
            "WHERE OBJECTPROPERTY(ID,N'IsTable')=1 and OBJECTPROPERTY(ID,N'IsMSShipped')=0"
        )
    return (
        {row['name'] for row in results} -
        # Exclude these table as they are marked as user ones by MSSQL, but actually system ones
        # (used by Diagram Designer of MS SQL Server Management Studio).
        # They are not copied by default, and we don't want to check them.
        {'sysdiagrams', 'dtproperties'}
    )


def _check_login(server, database_access, user, database_name=None):
    """Check whether we can login under specified user to specified database server

    :type server: parallels.hosting_check.ServerBase
    :type database_access: parallels.hosting_check.MSSQLDatabaseSourceAccess
    :type user: parallels.hosting_check.User
    :type database_name: str | unicode | None
    :rtype: bool
    """
    connection_settings = MSSQLConnectionSettings(
        host=database_access.host, user=user.login,
        password=user.password, database=database_name
    )
    # Just a test command to execute over connection, we do not check any results
    try:
        with server.runner() as runner:
            runner.mssql_query(connection_settings, "EXEC sp_databases")
    except MSSQLException:
        logger.debug("Exception: ", exc_info=True)
        return False
    return True
