import logging
import parallels
from parallels.common.utils.database_server_type import DatabaseServerType

from parallels.common.registry import Registry
from parallels.common import run_command
from parallels.common.utils import windows_utils
from parallels.common import MigrationError
from parallels.common.target_panels import TargetPanels
from parallels.common.utils.migrator_utils import get_package_extras_file_path, get_package_scripts_file_path
from parallels.hosting_check.utils.powershell import Powershell
from parallels.utils import split_nonempty_strs, is_empty

logger = logging.getLogger(__name__)


def copy_db_content_linux(database_info, key_pathname):
	source = database_info.source_database_server
	target = database_info.target_database_server
	logger.info(u"Copy database '{database_name}' content from {source} to {target}".format(
		database_name=database_info.database_name,
		source=source.description(),
		target=target.description()
	))

	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 source.type() == DatabaseServerType.MYSQL:
		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 {database_name}"
		# workaround for Plesk feature - it does not tell default MySQL server's admin password
		if source.host() == 'localhost' and source.port() == 3306:
			backup_command += u" -p\"`cat /etc/psa/.psa.shadow`\""
		else:
			backup_command += u" -p{src_password}"

		restore_command = u"mysql --no-defaults -h {dst_host} -P {dst_port} -u{dst_admin} {database_name}"
		# workaround for Plesk feature - it does not tell default MySQL server's admin password
		if target.host() == 'localhost' and target.port() == 3306:
			restore_command += u" -p\"`cat /etc/psa/.psa.shadow`\""
		else:
			restore_command += u" -p{dst_password}"

	elif source.type() == DatabaseServerType.POSTGRESQL:
		backup_command = u"PGUSER={src_admin} PGPASSWORD={src_password} PGDATABASE={database_name} pg_dump -Fc -b -O -i"
		if source.host() != 'localhost':
			backup_command += " -h {src_host} -p {src_port}"
		restore_command = u"PGUSER={dst_admin} PGPASSWORD={dst_password} pg_restore -v -d {database_name} -c"
		if target.host() != 'localhost':
			restore_command += " -h {dst_host} -p {dst_port}"
		with source.runner() as source_runner:
			if source_runner.run_unchecked('which', ['pg_dump'])[0] != 0:
				raise MigrationError(
					"pg_dump utility is not installed on '%s' server. "
					"Please make sure that:\n"
					"1) All external PostgreSQL servers (other than Plesk servers) are specified in config.ini "
					"in 'external-postgresql-servers' option, and pg_dump utility is installed on them.\n"
					"OR\n"
					"2) pg_dump is installed on all Plesk servers that have domains with PostgreSQL databases.\n"
					"Once the problem is fixed, run 'copy-db-content' migration tool command"
					"to copy only the databases" % source.ip()
				)

	else:
		raise Exception(u"Database has unsupported type '%s' and hence will not be copied" % source.type())

	with source.runner() as source_runner:
		source_runner.sh(backup_command + u" > {source_dump_filename}", dict(
			src_host=source.host(),
			src_port=source.port(),
			src_admin=source.user(),
			src_password=source.password(),
			database_name=database_info.database_name,
			source_dump_filename=source_dump_filename
		))

	with target.runner() as runner:
		runner.sh(
			u"scp -i {key_pathname} -o StrictHostKeyChecking=no -o GSSAPIAuthentication=no "
			u"{src_server_ip}:{source_dump_filename} {target_dump_filename}", dict(
				key_pathname=key_pathname,
				src_server_ip=source.ip(),
				source_dump_filename=source_dump_filename,
				target_dump_filename=target_dump_filename
			)
		)

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

	with target.runner() as target_runner:
		target_runner.sh(restore_command + u" < {target_dump_filename}", dict(
			dst_host=target.host(),
			dst_port=target.port(),
			dst_admin=target.user(),
			dst_password=target.password(),
			database_name=database_info.database_name,
			target_dump_filename=target_dump_filename,
		))
		target_runner.remove_file(target_dump_filename)


def copy_db_content_windows(database_info):
	source = database_info.source_database_server
	target = database_info.target_database_server
	logger.info(u"Copy database '{database_name}' content from {source} to {target}".format(
		database_name=database_info.database_name,
		source=source.description(),
		target=target.description()
	))

	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)

	with target.panel_server.runner() as runner_target, source.panel_server.runner() as runner_source:

		if source.type() == DatabaseServerType.MYSQL:
			if not is_windows_mysql_client_configured(target.panel_server):
				raise MigrationError((
					u"mysql client binary was not found on %s, database '%s' of subscription will not be copied."
					u"Make sure that: \n"
					u"1) MySQL is installed on service node.\n"
					u"2) MySQL 'bin' directory is added to PATH environment variable, so MySQL client can be simply"
					u"started with 'mysql' command.\n"
					u"3) If (1) and (2) are ok, restart 'pem' service with 'net stop pem'"
					u"and then 'net start pem' commands.\n"
					u"Please check migration tool documentation for more details"
				) % (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}"', 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(u"Copy database dump from source to target server with rsync")

				context = Registry.get_instance().get_context()
				rsync = context.rsync_pool.get(source.panel_server, target.panel_server)
				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 run_command.HclRunnerException as e:
				logger.debug(u"Exception: ", exc_info=e)
				raise MigrationError(
					u"Rsync failed to copy a database dump from the source (%s) to the target server (%s): %s\n"
					u"1. This could happen because of a network connection issue. Retry copying the database "
					u"content with the help of the copy-db-content command.\n"
					u"2. Check whether rsync is installed on the source server." % (
						source.panel_server.ip(),
						target.panel_server.ip(),
						e.stderr
					)
				)
			except Exception as e:
				logger.debug(u"Exception: ", exc_info=e)
				raise MigrationError(
					u"Rsync failed to copy a database dump from the source (%s) to the target server (%s): %s\n"
					u"1. This could happen because of a network connection issue. Retry copying the database "
					u"content with the help of the copy-db-content command.\n"
					u"2. Check whether rsync is installed on the source server." % (
						source.panel_server.ip(),
						target.panel_server.ip(),
						str(e)
					)
				)

			logger.debug(u"Restore database dump on target server")
			runner_target.sh(
				ur'{mysql_client} --no-defaults -h {host} -P {port} -u{login} -p{password} {database_name} '
				ur'-e "source {target_dump_filename}"',
				dict(
					mysql_client=get_windows_mysql_client(target.panel_server),
					host=target.host(),
					port=target.port(),
					login=target.user(),
					password=target.password(),
					database_name=database_info.database_name,
					target_dump_filename=target_dump_filename
				)
			)

			logger.debug(u"Remove database dump files; source: %s, target: %s" % (
				source_dump_filename,
				target_dump_filename
			))
			runner_source.remove_file(source_dump_filename)
			runner_target.remove_file(target_dump_filename)

		elif source.type() == DatabaseServerType.MSSQL:
			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=windows_utils.path_join(target.panel_server.plesk_dir, r'admin\bin\dbbackup.exe'),
					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
				)
			)
		else:
			logger.error(u"Database has unsupported type and hence will not be copied")


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.common.connections.database_server.DatabaseServer
	:raises parallels.common.utils.database_utils.DatabaseServerConnectionException:
	"""
	if is_empty(database_server.user()) or is_empty(database_server.password()):
		raise DatabaseServerConnectionException(
			"{server} is not properly configured on target server: "
			"credentials (login or password)are not set.\n"
			"Please configure the server, then proceed with migration.".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.common.connections.database_server.DatabaseServer:
	:raises parallels.common.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(
				"Connection to {server} failed.\n"
				"Command was: {command}\n"
				"Stdout: {stdout}\n"
				"Stderr: {stderr}\n"
				"Exit code: {exit_code}".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.common.connections.database_server.DatabaseServer:
	:raises parallels.common.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(
			"Connection to {server} failed.\n"
			"Command was: {command}\n"
			"Stdout: {stdout}\n"
			"Stderr: {stderr}\n"
			"Exit code: {exit_code}".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.common.connections.database_server.DatabaseServer:
	:raises parallels.common.utils.database_utils.DatabaseServerConnectionException:
	"""
	with database_server.runner() as runner:
		script_name = 'check_mssql_connection.ps1'
		local_script_path = get_package_scripts_file_path(parallels.plesks_migrator, script_name)
		remote_script_path = database_server.panel_server.get_session_file_path(script_name)
		runner.upload_file(local_script_path, remote_script_path)
		powershell = Powershell(runner, input_format_none=True)
		args = {
			'dataSource': database_server.host(),
			'login': database_server.user(),
			'pwd': database_server.password(),
		}
		exit_code, stdout, stderr = powershell.execute_script_unchecked(remote_script_path, args)
		if exit_code != 0 or stderr != '':
			raise DatabaseServerConnectionException(
				"Connection to {server} failed.\n"
				"Command that checks MSSQL connection with Powershell was: {command}\n"
				"Stdout: {stdout}\n"
				"Stderr: {stderr}\n"
				"Exit code: {exit_code}".format(
					server=database_server.description(),
					command=powershell.get_command_string(remote_script_path, args),
					stdout=stdout,
					stderr=stderr,
					exit_code=exit_code
				)
			)


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.common.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.common.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.common.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.common.connections.database_server.DatabaseServer
	:rtype: set[basestring]
	"""
	with database_server.runner() as runner:
		script_name = 'list_mssql_databases.ps1'
		local_script_path = get_package_extras_file_path(parallels.common, script_name)
		remote_script_path = database_server.panel_server.get_session_file_path(script_name)
		runner.upload_file(local_script_path, remote_script_path)
		powershell = Powershell(runner, input_format_none=True)
		exit_code, stdout, _ = powershell.execute_script_unchecked(
			remote_script_path, {
				'dataSource': database_server.host(),
				'login': database_server.user(),
				'pwd': database_server.password(),
			}
		)

		if exit_code != 0:
			raise MigrationError(
				"Failed to get list of databases on MSSQL database server %s:%s" % (
					database_server.host(), database_server.port()
				)
			)

		return set(split_nonempty_strs(stdout))