import ntpath
import logging
import textwrap

import parallels.core
from parallels.core import messages, CommandExecutionError
from parallels.core.migrator_config import MSSQLCopyMethod
from parallels.core.utils.common.ip import resolve_all
from parallels.core.utils.common.threading_utils import execute_once_synchronized_by_args
from parallels.core.utils.database_server_type import DatabaseServerType
from parallels.core.registry import Registry
from parallels.core.utils import windows_utils
from parallels.core import MigrationError
from parallels.core.target_panels import TargetPanels
from parallels.core.utils.entity import Entity
from parallels.core.utils.migrator_utils import get_package_extras_file_path
from parallels.core.utils.unix_utils import upload_file_content_linux, get_safe_filename
from parallels.core.utils.windows_utils import get_from_registry
from parallels.hosting_check.utils.powershell import Powershell
from parallels.core.utils.common import split_nonempty_strs, is_empty, add_dict_key_prefix, cached

logger = logging.getLogger(__name__)


class DatabaseInfo(object):
	"""Information about database to copy"""

	def __init__(self, source_database_server, target_database_server, subscription_name, database_name):
		"""
		:type source_database_server: parallels.core.connections.database_server.DatabaseServer
		:type target_database_server: parallels.core.connections.database_server.DatabaseServer
		:type subscription_name: basestring
		:type database_name: basestring
		"""
		self._source_database_server = source_database_server
		self._target_database_server = target_database_server
		self._subscription_name = subscription_name
		self._database_name = database_name

	@property
	def source_database_server(self):
		"""Source database server of database to copy

		:rtype: parallels.core.connections.database_server.DatabaseServer
		"""
		return self._source_database_server

	@property
	def target_database_server(self):
		"""Target database server of database to copy

		:rtype: parallels.core.connections.database_server.DatabaseServer
		"""
		return self._target_database_server

	@property
	def subscription_name(self):
		"""Name of subscription database belongs to

		:rtype: basestring
		"""
		return self._subscription_name

	@property
	def database_name(self):
		"""Name of database to copy

		:rtype: basestring
		"""
		return self._database_name

	def __str__(self):
		return messages.DATABASE_COPY_INFO_DESCRIPTION % (
			self.database_name, self.subscription_name,
			self.source_database_server.description(),
			self.target_database_server.description()
		)


class DatabaseConnectionInfo(Entity):
	"""Connection information to particular database - host, port, login, password, database name"""

	def __init__(self, host, port, login, password, db_name):
		"""
		:type host: basestring
		:type port: basestring
		:type login: basestring
		:type password: basestring
		:type db_name: basestring
		"""
		self._host = host
		self._port = port
		self._login = login
		self._password = password
		self._db_name = db_name

	@property
	def host(self):
		"""
		:rtype: basestring
		"""
		return self._host

	@property
	def port(self):
		"""
		:rtype: basestring
		"""
		return self._port

	@property
	def login(self):
		"""
		:rtype: basestring
		"""
		return self._login

	@property
	def password(self):
		"""
		:rtype: basestring
		"""
		return self._password

	@property
	def db_name(self):
		"""
		:rtype: basestring
		"""
		return self._db_name


def copy_db_content_linux(database_info, key_pathname):
	"""
	:type database_info: parallels.core.utils.database_utils.DatabaseInfo
	:type key_pathname: basestring
	:rtype: None
	"""
	source = database_info.source_database_server
	target = database_info.target_database_server
	database_name = database_info.database_name
	logger.info(messages.LOG_COPY_DB_CONTENT.format(
		db_name=database_name,
		source=source.description(),
		target=target.description()
	))

	dump_filename = 'db_backup_%s_%s' % (database_info.subscription_name, database_name)

	if source.type() == DatabaseServerType.MYSQL:
		source_passwd_file = None
		backup_command = _get_mysql_backup_command_template(
			source.host, source.port, source.password()
		)
	elif source.type() == DatabaseServerType.POSTGRESQL:
		source_connection_info = _get_db_connection_info(source, database_name)
		source_passwd_file = _create_pgpass_file(source, source_connection_info)
		backup_command = _get_pg_dump_command_template(source.host())
		with source.runner() as source_runner:
			_check_if_pg_dump_installed(source_runner)
	else:
		raise Exception(messages.CANNOT_TRANSFER_DATABASE_OF_UNSUPPORTED_TYPE % source.type())

	with source.runner() as source_runner:
		filename_stem = source.get_session_file_path(dump_filename)
		source_dump_tmpname = get_safe_filename(source_runner, filename_stem)
		source_runner.sh(backup_command + u" > {source_dump_tmpname}", dict(
			src_host=source.host(),
			src_port=source.port(),
			src_admin=source.user(),
			src_password=source.password(),
			db_name=database_name,
			source_dump_tmpname=source_dump_tmpname
		))

	with target.runner() as runner:
		filename_stem = target.get_session_file_path(dump_filename)
		target_dump_tmpname = get_safe_filename(runner, filename_stem)
		runner.sh(
			u"scp -i {key_pathname} -P {port} -o StrictHostKeyChecking=no -o GSSAPIAuthentication=no "
			u"{src_server_ip}:{source_dump_tmpname} {target_dump_tmpname}",
			dict(
				key_pathname=key_pathname,
				src_server_ip=source.utilities_server.ip(),
				source_dump_tmpname=source_dump_tmpname,
				target_dump_tmpname=target_dump_tmpname,
				port=source.utilities_server.settings().ssh_auth.port
			)
		)

	with source.runner() as source_runner:
		if source_passwd_file:
			source_runner.remove_file(source_passwd_file)
		source_runner.remove_file(source_dump_tmpname)

	restore_db_from_dump_linux(target, database_name, target_dump_tmpname)

	with target.runner() as target_runner:
		target_runner.remove_file(target_dump_tmpname)


def restore_db_from_dump_linux(server, database_name, dump_filename):
	"""
	:type server: parallels.core.connections.database_server.DatabaseServer
	:type database_name: basestring
	:type dump_filename: basestring
	"""
	connection_info = _get_db_connection_info(server, database_name)

	if server.type() == DatabaseServerType.MYSQL:
		passwd_file = _create_mysql_options_file(server, connection_info)
		restore_command = _get_mysql_restore_command_template(
			server.host(), server.port(), passwd_file
		)
	elif server.type() == DatabaseServerType.POSTGRESQL:
		passwd_file = _create_pgpass_file(server, connection_info)
		restore_command = _get_pg_restore_command_template(
			server.host(), passwd_file
		)
	else:
		raise Exception(messages.CANNOT_TRANSFER_DATABASE_OF_UNSUPPORTED_TYPE % server.type())

	with server.runner() as runner:
		destination_credentials = _get_target_db_connection_settings(connection_info)
		command_parameters = dict(target_dump_filename=dump_filename)
		command_parameters.update(destination_credentials)
		runner.sh(restore_command + u" < {target_dump_filename}", command_parameters)
		runner.remove_file(passwd_file)


def _get_target_db_connection_settings(connection_settings):
	"""
	:type connection_settings: parallels.core.utils.database_utils.DatabaseConnectionInfo
	:rtype: dict[basestring, basestring]
	"""
	return add_dict_key_prefix('dst_', connection_settings.as_dictionary())


def _get_db_connection_info(server, db_name):
	"""Return an object that defines database connection.

	:type server: parallels.core.connections.database_server.DatabaseServer
	:type db_name: basestring
	:rtype: parallels.core.utils.database_utils.DatabaseConnectionInfo
	"""
	return DatabaseConnectionInfo(
		host=server.host(), port=server.port(), login=server.user(),
		password=server.password(), db_name=db_name
	)


def _get_mysql_backup_command_template(host, port, password):
	"""
	:type host: basestring
	:type port: basestring
	:type password: basestring
	:rtype: str
	"""
	backup_command = (
		u"mysqldump -h {src_host} -P {src_port} -u{src_admin} --quick --quote-names "
		u"--add-drop-table --default-character-set=utf8 --set-charset {db_name}")
	if host == 'localhost' and port == 3306:
		# a workaround for Plesk
		backup_command += u" -p\"`cat /etc/psa/.psa.shadow`\""
	elif password != '':
		backup_command += u" -p{src_password}"
	return backup_command


def _get_mysql_restore_command_template(host, port, option_file=None):
	"""
	:type host: basestring
	:type port: basestring
	:type option_file: str
	:rtype: str
	"""
	restore_command = (
		u"mysql %s -h {dst_host} -P {dst_port} -u{dst_login} {dst_db_name}")
	if option_file:
		credentials = '--defaults-file=' + option_file
	elif host == 'localhost' and port == 3306:
		credentials = u" --no-defaults -p\"`cat /etc/psa/.psa.shadow`\""
	else:
		credentials = u" --no-defaults -p{dst_password}"
	return restore_command % (credentials,)


def _create_mysql_options_file(remote_server, connection_info):
	"""Create a mysql options file and upload it to the remote server.

	:type connection_info: parallels.core.utils.database_utils.DatabaseConnectionInfo
	"""
	filename = 'my_{host}_{db_name}.cnf'.format(**connection_info.as_dictionary())
	connection_str = u"[client]\nuser={login}\npassword='{password}'".format(
		**connection_info.as_dictionary()
	)
	remote_filename = upload_file_content_linux(remote_server, filename, connection_str)
	return remote_filename


def _get_pg_dump_command_template(hostname, passwd_file=None):
	"""
	:type hostname: basestring
	:type passwd_file: basestring | None
	:rtype: str
	"""
	if passwd_file:
		credentials = u"PGPASSFILE=%s" % passwd_file
	else:
		credentials = u"PGUSER={src_admin} PGPASSWORD={src_password} PGDATABASE={db_name}"
	backup_command = "pg_dump --format=custom --blobs --no-owner --ignore-version"
	if hostname != 'localhost':
		backup_command += " -h {src_host} -p {src_port}"
	return u"%s %s" % (credentials, backup_command)


def _get_pg_restore_command_template(hostname, passwd_file):
	"""
	:type hostname: basestring
	:type passwd_file: basestring | None
	:rtype: basestring
	"""
	credentials = u"PGPASSFILE=%s" % passwd_file
	restore_command = u"pg_restore -v -U {dst_login} -d {dst_db_name} -c"
	if hostname != 'localhost':
		restore_command += " -h {dst_host} -p {dst_port}"
	return u"%s %s" % (credentials, restore_command)


def _create_pgpass_file(server, connection_info):
	"""Create a PostgreSQL password file and upload it to the remote server.

	:type connection_info: parallels.core.utils.database_utils.DatabaseConnectionInfo
	:rtype: basestring
	"""
	filename = 'pgpass_{host}_{db_name}'.format(**connection_info.as_dictionary())
	connection_str = '*:*:{db_name}:{login}:{password}'.format(**connection_info.as_dictionary())
	remote_filename = upload_file_content_linux(server, filename, connection_str)
	return remote_filename


def _check_if_pg_dump_installed(runner):
	"""Make sure 'pg_dump' utility is installed."""
	if runner.run_unchecked('which', ['pg_dump'])[0] != 0:
		raise MigrationError(
			textwrap.dedent(messages.PG_DUMP_UTILITY_IS_NOT_INSTALLED % runner.ssh.hostname)
		)


def restore_db_from_dump_windows(db_server, database_name, dump_filename):
	"""Restore database dump file on Windows database server

	:type db_server: parallels.core.connections.database_server.DatabaseServer
	:type database_name: basestring
	:type dump_filename: basestring
	"""
	if db_server.type() == DatabaseServerType.MYSQL:
		restore_windows_mysql_dump(db_server, database_name, dump_filename)
	elif db_server.type() == DatabaseServerType.MSSQL:
		restore_windows_mssql_dump(db_server, database_name, dump_filename)
	else:
		logger.error(messages.DATABASE_UNSUPPORTED_TYPE_AND_HENCE_NOT)


def copy_db_content_windows(database_info, rsync, mssql_copy_method=MSSQLCopyMethod.AUTO):
	"""Copy database contents from one Windows database server to another

	:type database_info: parallels.core.utils.database_utils.DatabaseInfo
	:type rsync: parallels.core.windows_rsync.RsyncControl
	:type mssql_copy_method: str | unicode
	"""
	source = database_info.source_database_server
	target = database_info.target_database_server
	logger.info(messages.LOG_COPY_DB_CONTENT.format(
		db_name=database_info.database_name,
		source=source.description(),
		target=target.description()
	))

	if source.type() == DatabaseServerType.MYSQL:
		_copy_db_content_windows_mysql(database_info, rsync)
	elif source.type() == DatabaseServerType.MSSQL:
		if mssql_copy_method == MSSQLCopyMethod.AUTO:
			# Automatically select available copy method. If we have access to both source and target servers - use
			# native backups, otherwise use SMO dump that does not require direct access
			if source.physical_server is not None and target.physical_server is not None:
				mssql_copy_method = MSSQLCopyMethod.NATIVE
			else:
				mssql_copy_method = MSSQLCopyMethod.TEXT

		if mssql_copy_method == MSSQLCopyMethod.TEXT:
			# Copy databases with Plesk dbbackup.exe utility, '--copy' command, which uses text dump created with SMO
			_copy_db_content_windows_mssql_text_backup(database_info)
		elif mssql_copy_method == MSSQLCopyMethod.NATIVE:
			# Copy databases with MSSQL native backups - T-SQL "BACKUP" and "RESTORE" procedures
			_copy_db_content_windows_mssql_native_backup(database_info, rsync)
		else:
			assert False
	else:
		logger.error(messages.DATABASE_UNSUPPORTED_TYPE_AND_HENCE_NOT)


def _copy_db_content_windows_mysql(database_info, rsync):
	"""Copy MySQL database contents from one Windows database server to another

	:type database_info: parallels.core.utils.database_utils.DatabaseInfo
	:type rsync: parallels.core.windows_rsync.RsyncControl
	"""
	source = database_info.source_database_server
	target = database_info.target_database_server

	with target.runner() as runner_target, source.runner() as runner_source:
		dump_filename = 'db_backup_%s_%s.sql' % (database_info.subscription_name, database_info.database_name)
		source_dump_filename = source.panel_server.get_session_file_path(dump_filename)
		target_dump_filename = target.panel_server.get_session_file_path(dump_filename)

		if not is_windows_mysql_client_configured(target.panel_server):
			raise MigrationError(messages.MYSQL_CLIENT_BINARY_WAS_NOT_FOUND % (
				target.description(), database_info.database_name
			))

		runner_source.sh(
			ur'cmd.exe /C "{path_to_mysqldump} -h {host} -P {port} -u{user} -p{password} {database_name} '
			ur'--result-file={source_dump_filename}"' + (
				' --skip-secure-auth' if source.panel_server.mysql_use_skip_secure_auth() else ''
			),
			dict(
				path_to_mysqldump=source.panel_server.get_path_to_mysqldump(),
				host=source.host(),
				port=source.port(),
				user=source.user(),
				password=source.password(),
				database_name=database_info.database_name,
				source_dump_filename=source_dump_filename
			)
		)

		try:
			logger.debug(messages.COPY_DATABASE_DUMP_FROM_SOURCE_TARGET)

			rsync.sync(
				source_path='migrator/%s' % (
					source_dump_filename[source_dump_filename.rfind('\\') + 1:]
				),
				target_path=windows_utils.convert_path_to_cygwin(
					target_dump_filename
				),
			)
		except CommandExecutionError as e:
			logger.debug(messages.LOG_EXCEPTION, exc_info=True)
			raise MigrationError(
				messages.RSYNC_FAILED_COPY_DATABASE_DUMP_FROM % (
					source.panel_server.ip(),
					target.panel_server.ip(),
					e.stderr
				)
			)
		except Exception as e:
			logger.debug(messages.LOG_EXCEPTION, exc_info=True)
			raise MigrationError(
				messages.RSYNC_FAILED_COPY_DATABASE_DUMP_FROM_1 % (
					source.panel_server.ip(),
					target.panel_server.ip(),
					str(e)
				)
			)

		restore_windows_mysql_dump(target, database_info.database_name, target_dump_filename)

		logger.debug(messages.LOG_REMOVE_DB_DUMP_FILES % (
			source_dump_filename, target_dump_filename
		))
		runner_source.remove_file(source_dump_filename)
		runner_target.remove_file(target_dump_filename)


def restore_windows_mysql_dump(db_server, database_name, dump_filename):
	"""Restore MySQL database dump on specified database server

	:type db_server: parallels.core.connections.database_server.DatabaseServer
	:type database_name: basestring
	:type dump_filename: basestring
	"""
	logger.debug(messages.RESTORE_DATABASE_DUMP_TARGET_SERVER)
	with db_server.runner() as runner:
		runner.sh(
			ur'{mysql_client} --no-defaults -h {host} -P {port} -u{login} -p{password} {database_name} '
			ur'-e "source %s"' % dump_filename,
			dict(
				mysql_client=get_windows_mysql_client(db_server.panel_server),
				host=db_server.host(),
				port=db_server.port(),
				login=db_server.user(),
				password=db_server.password(),
				database_name=database_name
			)
		)


def _copy_db_content_windows_mssql_text_backup(database_info):
	"""Copy MSSQL database contents from one Windows database server to another, using SMO text dump

	:type database_info: parallels.core.utils.database_utils.DatabaseInfo
	"""
	source = database_info.source_database_server
	target = database_info.target_database_server
	with target.panel_server.runner() as runner_target:
		runner_target.sh(
			ur'cmd.exe /C "{dbbackup_path} --copy -copy-if-logins-exist -with-data -src-server={src_host} '
			ur'-server-type={db_type} -src-server-login={src_admin} -src-server-pwd={src_password} '
			ur'-src-database={database_name} -dst-server={dst_host} -dst-server-login={dst_admin} '
			ur'-dst-server-pwd={dst_password} -dst-database={database_name}"',
			dict(
				dbbackup_path=_get_dbbackup_bin(target.panel_server),
				db_type=source.type(),
				src_host=windows_utils.get_dbbackup_mssql_host(source.host(), source.ip()),
				src_admin=source.user(),
				src_password=source.password(),
				dst_host=target.host(),
				dst_admin=target.user(),
				dst_password=target.password(),
				database_name=database_info.database_name
			)
		)


def _copy_db_content_windows_mssql_native_backup(database_info, rsync):
	"""Copy MSSQL database contents from one Windows database server to another, using native MSSQL dump

	:type database_info: parallels.core.utils.database_utils.DatabaseInfo
	:type rsync: parallels.core.windows_rsync.RsyncControl
	"""
	source = database_info.source_database_server
	target = database_info.target_database_server
	dump_filename = 'db_backup_%s_%s.sql' % (database_info.subscription_name, database_info.database_name)
	dumps_dir = 'mssql-dumps'
	source_dumps_dir = _create_dumps_dir(source, dumps_dir)
	target_dumps_dir = _create_dumps_dir(target, dumps_dir)
	source_dump_filename = ntpath.join(source_dumps_dir, dump_filename)
	target_dump_filename = ntpath.join(target_dumps_dir, dump_filename)

	if source.physical_server is None:
		raise MigrationError(
			messages.MSSQL_DATABASE_NATIVE_DUMP_NO_PHYSICAL_ACCESS.format(server=source.description())
		)

	if target.physical_server is None:
		raise MigrationError(
			messages.MSSQL_DATABASE_NATIVE_DUMP_NO_PHYSICAL_ACCESS.format(server=target.description())
		)

	with target.panel_server.runner() as runner_target:
		runner_target.sh(
			ur'cmd.exe /C "{dbbackup_path} --backup -server={src_host} '
			ur'-server-type=mssql -server-login={src_admin} -server-pwd={src_password} '
			ur'-database={database_name} -backup-path={backup_path}',
			dict(
				dbbackup_path=_get_dbbackup_bin(target.panel_server),
				src_host=windows_utils.get_dbbackup_mssql_host(source.host(), source.ip()),
				src_admin=source.user(),
				src_password=source.password(),
				database_name=database_info.database_name,
				backup_path=source_dump_filename
			)
		)
		rsync.sync(
			source_path='migrator/%s/%s' % (
				dumps_dir, source_dump_filename[source_dump_filename.rfind('\\') + 1:]
			),
			target_path=windows_utils.convert_path_to_cygwin(
				target_dump_filename
			),
		)

	restore_windows_mssql_dump(target, database_info.database_name, target_dump_filename)

	with source.physical_server.runner() as runner:
		runner.remove_file(source_dump_filename)

	with target.physical_server.runner() as runner:
		runner.remove_file(target_dump_filename)


def restore_windows_mssql_dump(db_server, database_name, dump_filename):
	"""Restore MSSQL native database dump on specified database server

	:type db_server: parallels.core.connections.database_server.DatabaseServer
	:type database_name: basestring
	:type dump_filename: basestring
	"""
	if db_server.physical_server is None:
		raise MigrationError(
			messages.MSSQL_DATABASE_NATIVE_DUMP_NO_PHYSICAL_ACCESS.format(server=db_server.description())
		)
	with db_server.panel_server.runner() as runner_target:
		runner_target.sh(
			ur'cmd.exe /C "{dbbackup_path} --restore -server={dst_host} '
			ur'-server-type=mssql -server-login={dst_admin} -server-pwd={dst_password} '
			ur'-database={database_name} -backup-path={backup_path}',
			dict(
				dbbackup_path=_get_dbbackup_bin(db_server.panel_server),
				dst_host=db_server.host(),
				dst_admin=db_server.user(),
				dst_password=db_server.password(),
				database_name=database_name,
				backup_path=dump_filename
			)
		)


@cached
def _create_dumps_dir(server, dumps_dir):
	"""
	:type server: parallels.core.connections.database_server.DatabaseServer
	:rtype: str
	"""
	full_dumps_dir = server.get_session_file_path(dumps_dir)
	with server.runner() as runner:
		runner.mkdir(full_dumps_dir)

		try:
			set_mssql_dumps_dir_permissions(server, full_dumps_dir)
		except Exception as e:
			logger.debug(messages.LOG_EXCEPTION, exc_info=True)
			logger.warning(messages.FAILED_TO_SET_DBDUMP_DIR_PERMISSIONS.format(
				reason=unicode(e), server=server.description(), directory=full_dumps_dir
			))
		finally:
			# In any case, return path to created dumps directory, even if there were some
			# issues when setting permissions
			return full_dumps_dir


def set_mssql_dumps_dir_permissions(server, dumps_dir):
	"""Set permissions for MSSQL dumps directory, so MSSQL server can read/write to it

	:type server: parallels.core.connections.database_server.DatabaseServer
	:type dumps_dir: str | unicode
	:rtype: None
	"""
	with server.runner() as runner:
		try:
			if '/inheritance' in runner.sh('icacls /?'):
				# We can use "icacls" utility for configuring permissions
				# For old Windows versions (for example, 2003) this code will be simply skipped
				# and directory permissions will inherit session directory one's. In that case
				# it is a customer's responsibility to configure secure session directory.
				mssql_instance = _get_mssql_instance_name(server.host())
				if mssql_instance:
					mssql_instance_str = '$%s' % mssql_instance
				else:
					mssql_instance_str = ''

				user = get_from_registry(
					runner, [
						r'HKLM\SYSTEM\CurrentControlSet\services\MSSQL%s' % mssql_instance_str,
						r'HKLM\SYSTEM\CurrentControlSet\services\MSSQLSERVER%s' % mssql_instance_str
					], 'ObjectName'
				)

				if is_empty(user):
					# No user defined - leave default permissions
					return
				elif user == 'LocalSystem':
					# Don't need to set permissions for LocalSystem in explicit way
					return
				else:
					icacl_commands = [
						'/inheritance:r',
						# Grant permissions to Administrator, System, and MSSQL service user
						'/grant Administrators:(OI)(CI)F',
						'/grant SYSTEM:(OI)(CI)F',
						'/grant "%s:(OI)(CI)F"' % user
					]
					for icacl_command in icacl_commands:
						runner.sh("icacls {dir} %s" % (icacl_command,), {'dir': dumps_dir})
		except Exception as e:
			logger.debug(messages.LOG_EXCEPTION, exc_info=True)
			logger.warning(messages.FAILED_TO_SET_DBDUMP_DIR_PERMISSIONS.format(
				reason=unicode(e), server=server.description(), directory=dumps_dir
			))


def _get_mssql_instance_name(host):
	"""Get MSSQL instance name from MSSQL host name

	For example, if you have host name:
	192.168.1.110\MSSQL2008
	This function returns "MSSQL2008"
	If that is default instance - it returns None

	:type host: str | unicode
	:rtype: str
	"""
	parts = host.split('\\', 2)
	if len(parts) < 2:
		return None
	else:
		return parts[1]


def _get_dbbackup_bin(server):
	return windows_utils.path_join(server.plesk_dir, r'admin\bin\dbbackup.exe')


def get_windows_mysql_client(target_server):
	context = Registry.get_instance().get_context()
	if context.target_panel in [TargetPanels.PLESK, TargetPanels.PVPS]:
		return windows_utils.path_join(target_server.plesk_dir, 'MySQL\\bin\\mysql')
	else:
		return 'mysql'


def is_windows_mysql_client_configured(target_server):
	mysql = get_windows_mysql_client(target_server)
	with target_server.runner() as runner:
		return runner.sh_unchecked(u'cmd /c "%s" --version' % mysql)[0] == 0


def check_connection(database_server):
	"""Check connection to specified database server

	:type database_server: parallels.core.connections.database_server.DatabaseServer
	:raises parallels.core.utils.database_utils.DatabaseServerConnectionException:
	"""
	if is_empty(database_server.user()) or is_empty(database_server.password()):
		raise DatabaseServerConnectionException(
			messages.SERVER_IS_NOT_PROPERLY_CONFIGURED_TARGET.format(
				server=database_server.description()
			)
		)

	if database_server.type() == DatabaseServerType.MYSQL:
		check_mysql_connection(database_server)
	elif database_server.type() == DatabaseServerType.POSTGRESQL:
		check_postgresql_connection(database_server)
	elif database_server.type() == DatabaseServerType.MSSQL:
		check_mssql_connection(database_server)
	else:
		return


def check_mysql_connection(database_server):
	"""Check connection to MySQL database server

	:type database_server: parallels.core.connections.database_server.DatabaseServer:
	:raises parallels.core.utils.database_utils.DatabaseServerConnectionException:
	"""

	with database_server.runner() as runner:
		command = (
			'{mysql} --silent --skip-column-names -h {host} -P {port} -u {user} -p{password} -e {query}')
		args = dict(
			mysql=database_server.mysql_bin,
			user=database_server.user(), password=database_server.password(),
			host=database_server.host(), port=database_server.port(),
			query='SELECT 1'
		)
		exit_code, stdout, stderr = runner.sh_unchecked(command, args)

		if exit_code != 0 or stdout.strip() != '1':
			raise DatabaseServerConnectionException(
				messages.DB_CONNECTION_FAILURE.format(
					server=database_server.description(),
					command=command.format(**args),
					stdout=stdout,
					stderr=stderr,
					exit_code=exit_code
				)
			)


def check_postgresql_connection(database_server):
	"""Check connection to PostgreSQL database server

	:type database_server: parallels.core.connections.database_server.DatabaseServer:
	:raises parallels.core.utils.database_utils.DatabaseServerConnectionException:
	"""
	command = "PGUSER={user} PGPASSWORD={password} psql"
	if database_server.host() != 'localhost':
		command += " -h {host} -p {port}"
	command += " -dtemplate1 -A -t -q -c {query}"
	args = dict(
		user=database_server.user(), password=database_server.password(),
		host=database_server.host(), port=database_server.port(),
		query='SELECT 1'
	)
	with database_server.runner() as runner:
		exit_code, stdout, stderr = runner.sh_unchecked(command, args)

	if exit_code != 0 or stdout.strip() != '1':
		raise DatabaseServerConnectionException(
			messages.DB_CONNECTION_FAILURE.format(
				server=database_server.description(),
				command=command.format(**args),
				stdout=stdout,
				stderr=stderr,
				exit_code=exit_code
			)
		)


def check_mssql_connection(database_server):
	"""Check connection to MSSQL database server

	:type database_server: parallels.core.connections.database_server.DatabaseServer:
	:raises parallels.core.utils.database_utils.DatabaseServerConnectionException:
	"""
	with database_server.runner() as runner:
		remote_script_path = _install_check_mssql_connection_script(database_server)
		powershell = Powershell(runner, input_format_none=True)
		args = {
			'hostname': database_server.host(),
			'login': database_server.user(),
			'password': database_server.password(),
		}
		exit_code, stdout, stderr = powershell.execute_script_unchecked(remote_script_path, args)
		if exit_code != 0 or stderr != '':
			raise DatabaseServerConnectionException(
				messages.MSSQL_DB_CONNECTION_FAILURE.format(
					server=database_server.description(),
					command=powershell.get_command_string(remote_script_path, args),
					stdout=stdout,
					stderr=stderr,
					exit_code=exit_code
				)
			)


@execute_once_synchronized_by_args
def _install_check_mssql_connection_script(database_server):
	"""Upload script to check MSSQL connection to specified database server
	Return remote location of the script

	:type database_server: parallels.core.connections.database_server.DatabaseServer
	:rtype: str | unicode
	"""
	script_name = 'check_mssql_connection.ps1'
	local_script_path = get_package_extras_file_path(parallels.core, script_name)
	remote_script_path = database_server.get_session_file_path(script_name)
	with database_server.runner() as runner:
		runner.upload_file(local_script_path, remote_script_path)
	return remote_script_path


class DatabaseServerConnectionException(Exception):
	pass


def list_databases(database_server):
	"""List databases on specified server.

	Returns list of databases names or None if that function is not supported for that database server type.

	:type database_server: parallels.core.connections.database_server.DatabaseServer
	:rtype: set[basestring] | None
	"""
	if database_server.type() == DatabaseServerType.MYSQL:
		return list_mysql_databases(database_server)
	elif database_server.type() == DatabaseServerType.POSTGRESQL:
		return list_postgresql_databases(database_server)
	elif database_server.type() == DatabaseServerType.MSSQL:
		return list_mssql_databases(database_server)
	else:
		return None


def list_mysql_databases(database_server):
	"""List databases on specified MySQL server.

	Returns list of databases names.

	:type database_server: parallels.core.connections.database_server.DatabaseServer
	:rtype: set[basestring]
	"""
	with database_server.runner() as runner:
		stdout = runner.sh(
			'{mysql} --silent --skip-column-names -h {host} -P {port} -u {user} -p{password} -e {query}',
			dict(
				mysql=database_server.mysql_bin,
				user=database_server.user(), password=database_server.password(),
				host=database_server.host(), port=database_server.port(),
				query='SHOW DATABASES'
			)
		)

		return set(split_nonempty_strs(stdout))


def list_postgresql_databases(database_server):
	"""List databases on specified PostgreSQL server.

	Returns list of databases names or None if that function is not supported for that database server type.

	:type database_server: parallels.core.connections.database_server.DatabaseServer
	:rtype: set[basestring]
	"""
	with database_server.runner() as runner:
		command = "PGUSER={user} PGPASSWORD={password} psql"
		if database_server.host() != 'localhost':
			command += " -h {host} -p {port}"
		command += " -dtemplate1 -A -t -q -c {query}"
		stdout = runner.sh(
			command,
			dict(
				user=database_server.user(), password=database_server.password(),
				host=database_server.host(), port=database_server.port(),
				query='SELECT datname FROM pg_database'
			)
		)

		return set(split_nonempty_strs(stdout))


def list_mssql_databases(database_server):
	"""List databases on specified MSSQL server.

	Returns list of databases names or None if that function is not supported for that database server type.

	:type database_server: parallels.core.connections.database_server.DatabaseServer
	:rtype: set[basestring]
	"""
	with database_server.runner() as runner:
		remote_script_path = _install_list_mssql_databases_script(database_server)
		powershell = Powershell(runner, input_format_none=True)
		exit_code, stdout, _ = powershell.execute_script_unchecked(
			remote_script_path, {
				'hostname': database_server.host(),
				'login': database_server.user(),
				'password': database_server.password(),
			}
		)

		if exit_code != 0:
			raise MigrationError(
				messages.FAILED_GET_LIST_DATABASES_MSSQL_DATABASE % (
					database_server.host(), database_server.port()
				)
			)

		return set(split_nonempty_strs(stdout))


@execute_once_synchronized_by_args
def _install_list_mssql_databases_script(database_server):
	"""Upload script to get list of MSSQL databases to specified database server
	Return remote location of the script

	:type database_server: parallels.core.connections.database_server.DatabaseServer
	:rtype: str | unicode
	"""
	script_name = 'list_mssql_databases.ps1'
	local_script_path = get_package_extras_file_path(parallels.core, script_name)
	remote_script_path = database_server.get_session_file_path(script_name)
	with database_server.runner() as runner:
		runner.upload_file(local_script_path, remote_script_path)
	return remote_script_path


def compare_mssql_servers(database_server_1, database_server_2):
	"""Check if two MSSQL servers are actually the same server

	This function takes into account that the same server may be registered in different ways:
	by hostname, by IP, as 'localhost' or as '.'.

	:type database_server_1: parallels.core.connections.database_server.DatabaseServer
	:type database_server_2: parallels.core.connections.database_server.DatabaseServer
	:rtype: bool
	"""
	hostname_1, instance_1 = _get_mssql_host_parts(database_server_1)
	hostname_2, instance_2 = _get_mssql_host_parts(database_server_2)
	port_1 = _normalize_mssql_port(database_server_1)
	port_2 = _normalize_mssql_port(database_server_2)

	same_host = (
		database_server_1.ip() in _resolve_mssql_hostname(database_server_2) or
		database_server_2.ip() in _resolve_mssql_hostname(database_server_1)
	)
	same_instance = instance_1 == instance_2
	same_port = port_1 == port_2

	return same_host and same_instance and same_port


def _resolve_mssql_hostname(database_server):
	"""Convert MSSQL database server hostname (without instance name) to list of IP addresses

	:type database_server: parallels.core.connections.database_server.DatabaseServer
	"""
	hostname, _ = _get_mssql_host_parts(database_server)
	if hostname in ('localhost', '.', '127.0.0.1'):
		return [database_server.ip()]
	else:
		return resolve_all(hostname)


def _get_mssql_host_parts(database_server):
	"""Split MSSQL database servers hostname into host and instance parts

	:type database_server: parallels.core.connections.database_server.DatabaseServer
	:rtype: tuple(str, str | None)
	"""
	parts = database_server.host().split('\\')
	if len(parts) == 1:
		# Plain hostname
		return parts[0], None
	elif len(parts) == 2:
		instance_name = parts[1]
		if instance_name == '':
			instance_name = None
		return parts[0], instance_name
	else:
		assert False, messages.ASSERT_INVALID_MSSQL_HOSTNAME.format(hostname=database_server.host())


def _normalize_mssql_port(database_server):
	"""Get valid integer MSSQL server port, considering that all null values mean default port (1433)

	:type database_server: parallels.core.connections.database_server.DatabaseServer
	:rtype: int
	"""
	if database_server.port() in (None, '', 0):
		return 1433
	else:
		return database_server.port()
