import logging
import xml.etree.ElementTree as et
import os
import io
import uuid
import base64
import hashlib
import urllib

from ConfigParser import RawConfigParser

from parallels.utils import xml, cached
from parallels.common import checking, run_command

from .remote_script import HsphereRunner
from parallels.common.plesk_backup import save_backup_tar

class InconsistencyException(Exception):
	"""This exception should be used for such H-Sphere database inconsistencies that blocks subscription migration"""
	pass

class BackupAgent:
	logger = logging.getLogger(__name__)

	def __init__(self, connection_cursor, hsphere_ssh_runner, ppa_runner, iis_nodes_settings, windows_session_dir, _get_session_file_path_func, _get_hsphere_script_local_path_func, report):
		self.backup_tree = et.ElementTree(
			xml.elem('migration-dump', [], {'content-included': 'false', 'agent-name': 'PleskX', 'dump-format': 'panel', 'dump-version': '11.0.9'})
		)
		self.backup_tree.getroot().append(xml.elem('admin', [], {'guid': str(uuid.uuid1())}))
		self.backup_tree.getroot().append(xml.elem('server'))
		self.conn = connection_cursor
		self.cp_runner = hsphere_ssh_runner
		self.ppa_runner = ppa_runner
		self.report = report
		self.server_report = report.subtarget(u"%s server" % 'Source', 'hsphere')
		self.iis_nodes_settings = iis_nodes_settings
		self.windows_session_dir = windows_session_dir
		self.get_session_file_path_func = _get_session_file_path_func
		self.get_hsphere_script_local_path_func = _get_hsphere_script_local_path_func

	def make_backup(self):
		user_ids = self._add_accounts()
		self._add_subscriptions(user_ids)
		
		self._add_db_servers()

	def save_backup(self, backup_save_file):
		if os.path.exists(backup_save_file):
			self.logger.debug(u"Removing the existing backup file.")
			os.remove(backup_save_file)
		
		backup_file_content = et.tostring(self.backup_tree.getroot(), 'utf-8', 'xml')
		save_backup_tar(backup_file_content, backup_save_file)

	def _add_subscriptions(self, user_account_ids):
		for user_id in user_account_ids:
			owner_node = self._get_owner_node(user_id)
			if owner_node is None:
				self.logger.debug(u"A user with ID  %s is not present in the backup." % user_id)
				continue

			self.conn.execute(u"SELECT username, account_id, reseller_id FROM user_account ua JOIN users u ON u.id = ua.user_id WHERE user_id = %s" % user_id)
			for username, account_id, reseller_id in self.conn.fetchall():
				customer_report = self._get_customer_report(username, reseller_id)
				try:
					self.logger.debug(u"Retrieving information about the main domain of the account with ID %s." % account_id)
					main_domain_info = self._get_account_main_domain_info(account_id)
					if main_domain_info is None:
						continue
					domain_id, domain_name, apache, iis, mail = main_domain_info
				except Exception as e:
					self.logger.debug(u"Exception:", exc_info=e)
					self.logger.error(u"Failed to fetch subscription from H-Sphere for account with '%i' id: %s" % (account_id, str(e)))
					customer_report.add_issue(checking.Problem('hsphere_subscription_issue', checking.Problem.ERROR, str(e)),
						u"Unable to retrieve information about one of the subscriptions. Resolve inconsistencies in the H-Sphere's database and re-run the migration tool."
					)
					continue

				subscription_report = customer_report.subtarget('Subscription', domain_name)
				try:
					if iis is None and apache is None and mail is None:
						self.logger.debug("Skip domain '%s' that has neither a Web service nor a Mail service", domain_name)
						subscription_report.add_issue(checking.Problem('hsphere_subscription_issue', checking.Problem.WARNING,
								u"The subscription will not be migrated to PPA because it does not have web and mail services."
							),
							u"You can move the subscription to the PPA server in either of the following ways:\n"
							u"1. Create a subscription on the PPA server manually.\n"
							u"2. Add mail or web resource and re-run the migration tool."
						)
						continue
					self.logger.info(u"Retrieving information about main domain %s of account %s." % (domain_name, self._get_account_username_by_id(account_id)))

					domains = xml.elem('domains')
					domain_children = self._get_domain_children_nodes(domain_id, domain_name, report = subscription_report, is_subscription = True)
					domains.append(xml.elem('domain', domain_children, {'guid': str(uuid.uuid1()), 'name': domain_name}))

					users = owner_node.find('./users')
					domain_position = owner_node.getchildren().index(users)
					owner_node.insert(domain_position, domains)
				except InconsistencyException as e:
					self.logger.error(u"Failed to retrieve information about the subscription %s from H-Sphere because of H-Sphere database inconsistency: %s" % (domain_name, str(e)))
					subscription_report.add_issue(checking.Problem('hsphere_subscription_issue', checking.Problem.ERROR, str(e),),
						u"The subscription will not be migrated to PPA. Resolve inconsistencies in the H-Sphere's database and re-run the migration tool.\n"
						u"To execute an H-Sphere database query:\n"
						u"1. Log in to the H-Sphere management node as root.\n"
						u"2. Execute the \"su - cpanel -c 'psql hsphere'\" command to open the PostgreSQL console."
					)
				except Exception as e:
					self.logger.debug(u"Exception:", exc_info=e)
					error_msg = u"Failed to retrieve information about the subscription %s from H-Sphere: %s." % (domain_name, str(e))
					self.logger.error(error_msg)
					subscription_report.add_issue(checking.Problem('hsphere_subscription_issue', checking.Problem.ERROR, error_msg,),
						u"The subscription will not be migrated to PPA. If you want to migrate the subscription:\n"
						u"1. Try to re-run the migration tool.\n"
						u"2. Migrate the failed subscription manually.\n"
						u"3. Refer to the technical support to resolve the issue."
					)


	def _get_account_main_domain_info(self, account_id):
		# Get all domains of account and fetch only first (with less id), so we consider that it's a main domain of account
		self.conn.execute("""
		SELECT 
			domains.id, domains.name, 
			apache_vhost.id IS NOT NULL AS apache, 
			iis_vhost.id IS NOT NULL AS iis, 
			pc_mail.child_id IS NOT NULL AS mail
			FROM domains
			JOIN parent_child ON parent_child.child_id = domains.id
			LEFT JOIN parent_child pc_apache ON pc_apache.parent_id = domains.id AND pc_apache.child_type = 9
			LEFT JOIN apache_vhost ON apache_vhost.id = pc_apache.child_id
			LEFT JOIN parent_child pc_iis ON pc_iis.parent_id = domains.id AND pc_iis.child_type = 9
			LEFT JOIN iis_vhost ON iis_vhost.id = pc_iis.child_id
			LEFT JOIN parent_child pc_mail ON pc_mail.parent_id = domains.id AND pc_mail.child_type = 1000
			WHERE
				parent_child.child_type IN (2, 34, 35)
				AND parent_child.account_id = '%s'
			ORDER BY domains.id
			LIMIT 1
		""" % account_id)
		# 2 - domain
		# 34 - service domain
		# 35 - third level domain
		# 9 - hosting
		# 1000 - mail service

		return self.conn.fetchone()

	def _add_db_servers(self):
		server_node = self._get_server_node()

		self.conn.execute(u""" SELECT l_server_ips.ip FROM l_server 
			JOIN l_server_ips ON l_server.id = l_server_ips.l_server_id
			JOIN l_server_groups ON l_server_groups.id = l_server.group_id
			WHERE 
				l_server_groups.type_id = 4
			""") # 4 - MySql Group
		if self.conn.rowcount() == 0:
			return

		db_servers = []
		database_server_ip = None
		for server_ip in self.conn.fetchall():
			try:
				database_server_ip = server_ip = server_ip[0]
				db_server_runner = HsphereRunner(self.cp_runner, server_ip)
				mysql_config_string = db_server_runner.sh('cat ~mysql/.my.cnf')

				config = RawConfigParser()
				config.readfp(io.BytesIO(mysql_config_string.encode('utf-8')))
				db_admin_name = config.get('client', 'user')
				db_admin_password = config.get('client', 'password')

				db_servers.append(xml.elem('db-server', [
					xml.text_elem('host', server_ip),
					xml.text_elem('port', '3306'),
					xml.elem('db-admin', [
						xml.text_elem('password', db_admin_password, {'type': 'plain'})
					], {'name': db_admin_name}),
				], {'type': 'mysql'}))
			except Exception as e:
				self.logger.debug(u"Exception:", exc_info=e)
				self.logger.error("Failed to fetch '%s' database server from H-Sphere: %s" % (database_server_ip, str(e)))
				self.server_report.add_issue(checking.Problem('hsphere_database_server_issue', checking.Problem.ERROR, str(e),),
					u"All subscriptions that use the database server %s will not be restored correctly. Resolve inconsistencies in the H-Sphere's database and re-run the migration tool.\n"
					u"If there are no subscriptions that use this database server, ignore this error." % database_server_ip
				)

		server_node.append(xml.elem('db-servers', db_servers))

	def _add_accounts(self):
		self.logger.debug(u"Retrieving information about resellers from H-Sphere.")

		resellers_ids = []
		try:
			resellers_ids = self._add_resellers_node()
		except Exception as e:
			self.logger.debug(u"Exception:", exc_info=e)
			self.server_report.add_issue(checking.Problem('hsphere_account_issue', checking.Problem.ERROR, str(e),),
				u"Unable to add resellers to an H-Sphere backup. This could happen because of inconsistencies in the H-Sphere's database.\n"
				u"Repair such inconsistencies and re-run the migration tool."
			)

		clients_owner = [1] + resellers_ids

		admin_node = self._get_admin_node()
		clients_node = et.SubElement(admin_node, 'clients')

		def _get_owner_node():
			if owner_id == 1:
				return clients_node
			else:
				reseller_node = self._get_reseller_node(owner_id)
				users = reseller_node.find('./users')
				domain_position = reseller_node.getchildren().index(users)
				clients = xml.elem('clients', [])
				reseller_node.insert(domain_position, clients)
				return clients

		user_ids = []
		for owner_id in clients_owner:
			owner_node = _get_owner_node()
			try:
				user_ids += self._add_clients(owner_node, owner_id)
			except Exception as e:
				self.logger.debug(u"Exception:", exc_info=e)
				self.server_report.add_issue(checking.Problem('hsphere_account_issue', checking.Problem.ERROR, str(e),),
					u"Unable to add accounts to the account with ID %i. This could happen because of inconsistencies in the H-Sphere's database.\n"
					u"Repair such inconsistencies and re-run the migration tool." % owner_id
				)

		return resellers_ids + user_ids

	def _get_account_username_by_id(self, account_id):
		self.conn.execute(u"SELECT username FROM users u JOIN user_account ua ON ua.user_id = u.id WHERE ua.account_id = %i" % account_id)
		return self.conn.fetchvalue()

	def _add_clients(self, clients_node, reseller_id = 1):
		user_ids = []
		self.conn.execute(u""" SELECT id, username, password FROM users 
			WHERE 
				password IS NOT NULL 
				AND password <> ''
				AND users.username <> 'admin'
				AND NOT EXISTS (SELECT 1 
								FROM resellers 
								WHERE id = users.id)
				AND reseller_id = %s """ % (reseller_id))

		for user_id, username, password in self.conn.fetchall():
			customer_report = self._get_customer_report(username, reseller_id)
			try:
				self.conn.execute(u""" SELECT contact_info.name, contact_info.last_name, contact_info.company, contact_info.address1,
					contact_info.address2, contact_info.city, contact_info.state, contact_info.postal_code, contact_info.country, contact_info.phone,
					contact_info.email, contact_info.state2, accounts.suspended, user_account.account_id FROM user_account
					JOIN accounts ON accounts.id = user_account.account_id
					JOIN contact_info ON contact_info.id = accounts.ci_id
					WHERE
						user_account.user_id = %s
						ORDER BY accounts.id""" % (user_id))

				if self.conn.rowcount() == 0:
					continue

				first_name, last_name, company, address1, address2, city, state, zip, country, phone, email, _, suspended, _ = self.conn.fetchone()

				preferences = self._get_account_preferences_node(
					{'country': country, 'phone': phone, 'state': self._get_default_state(state), 'email': email, 'city': city, 'zip': zip,
					'fax': '', 'address': self._strings_concat(address1, address2), 'company': company, 'locale': 'en-US'}
				)
				properties = self._get_account_properties_node(password, 'plain', 'enabled' if suspended != '' else suspended)

				base_attributes = {'name': username, 'contact': self._strings_concat(last_name, first_name), 'guid': str(uuid.uuid1())}
				clients_attributes = base_attributes.copy()
				clients_attributes.update({'id': str(user_id)})
				client = et.SubElement(clients_node, 'client', clients_attributes)

				user_attributes = base_attributes.copy()
				user_attributes.update({'is-domain-admin': 'false', 'email': email, 'is-built-in': 'true'})
				users = self._get_account_users_node(properties, 'Owner', preferences, user_attributes)

				client.append(preferences)
				client.append(properties)
				client.append(users)
				user_ids.append(user_id)
			except Exception as e:
				self.logger.debug(u"Exception:", exc_info=e)
				self.logger.error("Failed to fetch reseller account with '%i' id from Hsphere: %s" % (user_id, str(e)))
				customer_report.add_issue(checking.Problem('hsphere_customer_issue', checking.Problem.ERROR, str(e),),
					u"The customer will not be migrated to PPA. Resolve inconsistencies in the H-Sphere's database and re-run the migration tool."
				)
		return user_ids

	def _add_resellers_node(self):
		self.conn.execute(u""" SELECT users.id, users.username, users.password, contact_info.name, contact_info.last_name, contact_info.company, 
		contact_info.address1, contact_info.address2, contact_info.city, contact_info.state, contact_info.postal_code, contact_info.country, 
		contact_info.phone, contact_info.email, contact_info.state2, accounts.suspended FROM users 
		JOIN resellers ON resellers.id = users.id 
		JOIN user_account ON user_account.user_id = users.id 
		JOIN accounts ON accounts.id = user_account.account_id 
		JOIN contact_info ON contact_info.id = accounts.ci_id 
		WHERE 
			accounts.id = (SELECT MIN(account_id) FROM user_account WHERE user_id = users.id)
			AND password IS NOT NULL 
			AND password <> '' """)

		admin_node = self._get_admin_node()
		resellers = et.SubElement(admin_node, 'resellers')
		resellers_ids = []
		for id, username, password, first_name, last_name, company, address1, address2, city, state, zip, country, phone, email, _, suspended in self.conn.fetchall():
			reseller_report = self.server_report.subtarget('Reseller', username)
			try:
				preferences = self._get_account_preferences_node(
					{'country': country, 'phone': phone, 'state': self._get_default_state(state), 'email': email, 'city': city, 'zip': zip,
					'fax': '', 'address': self._strings_concat(address1, address2), 'company': company, 'locale': 'en-US'}
				)
				properties = self._get_account_properties_node(password, 'plain', 'enabled' if suspended != '' else suspended)

				base_attributes = {'name': username, 'contact': self._strings_concat(last_name, first_name), 'guid': str(uuid.uuid1())}

				resellers_attributes = base_attributes.copy()
				resellers_attributes.update({'id': str(id)})
				reseller = et.SubElement(resellers, 'reseller', resellers_attributes)

				resellers_user_attributes = base_attributes.copy()
				resellers_user_attributes.update({'is-domain-admin': 'false', 'email': email, 'is-built-in': 'true'})
				users = self._get_account_users_node(properties, 'Owner', preferences, resellers_user_attributes)

				reseller.append(preferences)
				reseller.append(properties)
				reseller.append(users)
				resellers_ids.append(id)
			except Exception as e:
				self.logger.debug(u"Exception:", exc_info=e)
				self.logger.error("Failed to fetch reseller account '%s' from H-Sphere: %s" % (username, str(e)))
				reseller_report.add_issue(checking.Problem('hsphere_reseller_issue', checking.Problem.ERROR, str(e)),
					u"The reseller will not be migrated to PPA. Resolve inconsistencies in the H-Sphere's database and re-run the migration tool."
				)
		return resellers_ids

	def _get_admin_node(self):
		return self.backup_tree.find('.//admin')
	
	def _get_server_node(self):
		return self.backup_tree.find('.//server')
	
	def _get_owner_node(self, user_id):
		reseller_owner = self._get_reseller_node(user_id)
		if reseller_owner is not None:
			return reseller_owner
		return self.backup_tree.find('.//client[@id="%s"]' % user_id)

	def _get_reseller_node(self, reseller_id):
		return self.backup_tree.find('.//reseller[@id="%s"]' % (reseller_id))
	
	def _get_phosting_node(self, domain_id, is_subscription, certificates, report):
		""" Skipped following nodes: webusers, subdomains """
		
		if not self._is_domain_resource_supported(domain_id, 9): # 9 - Hosting
			self.logger.debug(u"An XML node describing hosting settings will not be added for the domain with ID %s." % domain_id)
			return None
		
		hosting_attributes = {'www-root': self._get_domain_name(domain_id), 'cgi_bin_mode': 'www-root'}
		phosting_children = []
		hosting_preferences_ids = self._extract_hosting_preferences(domain_id)
		hosting_preferences = self._get_hosting_preferences(domain_id, hosting_preferences_ids, is_subscription)
		if hosting_preferences is not None:
			phosting_children.append(hosting_preferences)

		domain_name = self._get_domain_name(domain_id)

		ip_node = self._get_ip_node(self._get_domain_ip(domain_name))
		if ip_node is not None:
			phosting_children.append(xml.elem('properties', [ip_node]))
		phosting_children.append(self._get_scripting_node(domain_id, hosting_preferences_ids))

		web_settings_node = self._get_web_settings_node(domain_id)
		if web_settings_node is not None:
			phosting_children.append(web_settings_node)

		ftp_users = self._get_ftp_users_node(domain_id, report)
		if ftp_users is not None:
			phosting_children.append(ftp_users)
		
		self.conn.execute(u"SELECT parent_type FROM parent_child WHERE parent_id = %s" % domain_id)
		domain_type = self.conn.fetchone()[0]
		sites_node = None
		if domain_type != 31 and is_subscription == True: #31 - Subdomains
			# Get info about all addon domains and subdomains for subscription
			sites_node = self._get_sites_node(domain_id, report)
		if sites_node is not None:
			phosting_children.append(sites_node)
			
		if hosting_preferences_ids.has_key(16):#16 - error docs
			hosting_attributes['errdocs'] = 'true'
		if hosting_preferences_ids.has_key(24):#24 - ssl
			hosting_attributes['https'] = 'true'
			if certificates is not None:
				private_key = certificates.find('.//certificate/private-key')
				if private_key is not None:
					hosting_attributes['certificate'] = hashlib.md5(urllib.quote_plus(private_key.text.replace('\r', ''))).hexdigest()
				else:
					self.logger.debug(u"Unable to attach a certificate to the domain with ID %s." % domain_id)

		return xml.elem('phosting', phosting_children, hosting_attributes)
		
	def _get_domain_children_nodes(self, domain_id, domain_name, report, is_subscription = True):
		"""Return xml node for subscription: domain node in xml"""
		
		self.logger.debug(u"Retrieving information about domains related to the domain with ID %s." % domain_id)
		certificates = self._get_certificates_node(report, domain_id)
		
		phosting = self._get_phosting_node(domain_id, is_subscription, certificates, report)
		mailsystem = self._get_mailsystem_node(domain_id)
		databases = None
		if is_subscription:
			databases = self._get_databases_node(domain_id, report)

		properties_children = []
		ip_node = self._get_ip_node(self._get_domain_ip(domain_name))
		if ip_node is not None:
			properties_children.append(ip_node)
		properties_children += [self._get_status_element()] # + dns_zone_node
		domain_children = [xml.elem('preferences', self._get_domain_preferences_children(domain_id)), xml.elem('properties', properties_children)]

		if mailsystem is not None:
			domain_children.append(mailsystem)
		if databases is not None:
			domain_children.append(databases)
		if certificates is not None:
			domain_children.append(certificates)
		if phosting is not None:
			domain_children.append(phosting)
		
		return domain_children

	def _get_sites_node(self, domain_id, report):
		""" iterate over addon domains, subdomains for main subscription domain"""
		sites_children = []
		account_id = self._get_account_id(domain_id)

		def _get_subdomain_parent_name(subdomain_id):
			self.conn.execute(u"SELECT parent_id, parent_type FROM parent_child WHERE child_id = %s" % (subdomain_id))
			parent_id, parent_type = self.conn.fetchone()
			if parent_type == 31: # Subdomain of subdomain -> go next cycle
				return _get_subdomain_parent_name(parent_id)
			else:
				self.conn.execute(u"SELECT name FROM domains WHERE id = %s""" % parent_id)
				return self.conn.fetchone()[0]

		def _iterate_domains(site_ids, is_subdomain = False):
			for site_id in site_ids:
				site_attributes = {'guid': str(uuid.uuid1())}
				self.conn.execute(u"SELECT name FROM domains WHERE id=%s" % site_id)
				site_name = self.conn.fetchone()[0]
				site_attributes['name'] = site_name
				if is_subdomain:
					site_attributes['parent-domain-name'] = _get_subdomain_parent_name(site_id)

				site_children = self._get_domain_children_nodes(site_id, site_name, report, is_subscription = False)
				sites_children.append(xml.elem('site', site_children, site_attributes))

		def _process_subdomains(parent_domain_id):
			self.conn.execute(u"SELECT child_id, child_type FROM parent_child WHERE child_type = 31 AND parent_id=%s" % parent_domain_id)
			# 31 - Subdomains
			subdomain_ids = [i[0] for i in self.conn.fetchall()]
			_iterate_domains(subdomain_ids, is_subdomain = True)
			return subdomain_ids
		
		self.logger.debug(u"Retrieving information about add-on domains of the domain with ID %s." % domain_id)
		self.conn.execute(u""" SELECT domains.id FROM domains
			JOIN parent_child pc1 ON pc1.child_id = domains.id
			WHERE 
				pc1.child_type IN (2, 32, 34, 35, 37)
				AND pc1.account_id = %s
				AND NOT EXISTS (SELECT 1 FROM parent_child WHERE parent_id = domains.id AND child_type = 6410)
				ORDER BY domains.id
		"""  % account_id)
		# 2 - Domains
		# 32 - Stopgap Domain
		# 35 - Third level Domain
		# 37 - Parked Domain
		# 6410 - Domain alias

		addon_domains_ids = [i[0] for i in self.conn.fetchall()]
		process_rows = [i for i in addon_domains_ids if i != domain_id]
		_iterate_domains(process_rows) # iterate addon sites

		while len(addon_domains_ids) != 0:
			current_parent_id = addon_domains_ids.pop(0) # extract first processed parent
			processed_sub_ids = _process_subdomains(current_parent_id)
			addon_domains_ids += processed_sub_ids # add added subdomains

		if len(sites_children) !=0:
			return xml.elem('sites', sites_children)
		else:
			return None

	def _get_ftp_users_node(self, domain_id, report):
		account_id = self._get_account_id(domain_id)
		main_domain_id, _ = self._get_main_domain(account_id)

		if main_domain_id != domain_id:
			self.logger.debug(u"Skipping the detection of FTP users of the domain with ID %s." % domain_id)
			return None
		
		self.logger.debug(u"Retrieving information about FTP users of the domain with ID %s." % domain_id)
		db_query = u"""SELECT unix_user.login, unix_user.group_name, unix_user.password, unix_user.dir FROM parent_child
			JOIN unix_user ON unix_user.id = parent_child.child_id
			WHERE
				account_id = %s
				AND parent_type = 7
				AND child_type = 2010""" % account_id
		self.conn.execute(db_query)
		# 7 - unixuser
		# 2010 - sub ftp user
		if self.conn.rowcount() == 0:
			return None
		ftp_users_children = [] 
		for login, group_name, password, dir in self.conn.fetchall():
			if dir.startswith('/'):	# absolute paths - unix ftp accounts. strip account home to get ftp user home.
				if dir.index(group_name) is None:
					report.add_issue(checking.Problem('hsphere_subscription_issue', checking.Problem.WARNING,
							u"There are inconsistencies in the H-Sphere's database. \n"
							u"The SQL query: %s.\n"
							u"""The value of "group_name" (group_name is "%s") should be contained in %s.""" % (db_query, group_name, dir)
						),
						u"To execute an H-Sphere database query:\n"
						u"1. Log in to the H-Sphere management node as root.\n"
						u"2. Execute the \"su - cpanel -c 'psql hsphere'\" command to open the PostgreSQL console.\n"
						u"Resolve inconsistencies in the H-Sphere's database and re-run the migration tool."
					)
					ftp_user_home = None
				else:
					ftp_user_home = dir[dir.index(group_name) + len(group_name) + 1:]
			else:			# relative paths - windows ftp accounts. paths are already relative to account home, no need in stripping
				ftp_user_home = dir
			if ftp_user_home is not None:
				ftp_users_children.append(xml.elem('ftpuser', [
					xml.elem('sysuser',[
							xml.text_elem('password', password, {'type': 'plain'})
						], {'name': login, 'home': ftp_user_home})
					],
					{'name': login}
				))
		return xml.elem('ftpusers', ftp_users_children)

	def _get_domain_preferences_children(self, domain_id):
		self.logger.debug(u"Retrieving information about website settings for the domain with ID %s." % domain_id)
		domain_aliases = []

		def _add_alias(alias_id):
			alias_name = self._get_domain_name(domain_alias_id)
			self.conn.execute(u"SELECT child_id, child_type FROM parent_child WHERE parent_id = %s" % alias_id)

			alias_attributes = {'name': alias_name, 'web': 'true', 'mail': 'false'}
			resources = dict(self.conn.fetchall())

			if resources.has_key(3001): # 3001 - dns zone
				alias_attributes['dns'] = 'true'
			elif resources.has_key(1000): # 1000 - mail service
				alias_attributes['mail'] = 'true'
			domain_aliases.append(xml.elem('domain-alias', [self._get_status_element()], alias_attributes))

		# add aliases by old hsphere scheme
		self.conn.execute(u"SELECT child_id FROM parent_child WHERE parent_id = %s and child_type = 6400" % domain_id) # 6400 Domain alias
		domain_aliase_ids = [i[0] for i in self.conn.fetchall()]
		# add aliases by new hsphere scheme
		self.conn.execute(u"""SELECT DISTINCT parent_child.parent_id FROM domain_resource_alias 
			JOIN parent_child ON parent_child.child_id = domain_resource_alias.id
			WHERE 
				actual_domain_name = '%s'
				AND actual_resource_type='hosting' """ % self._get_domain_name(domain_id))
		
		domain_aliase_ids += [i[0] for i in self.conn.fetchall()]

		for domain_alias_id in domain_aliase_ids:
			_add_alias(domain_alias_id)

		return domain_aliases

	def _get_hosting_preferences(self, domain_id, hosting_preferences_ids, is_subscription):
		""" Skipped following nodes: logrotation, anonftp, pdir, webstat, performance, shared-ssl """

		preferences_children = []

		sysuser_row = self._get_sys_user(domain_id)
		if sysuser_row is not None:
			if is_subscription:
				sys_user_name, sys_user_password, os_type = sysuser_row
				sys_user_attributes = {'name': sys_user_name}
				if os_type == 1: # unix
					sys_user_attributes['shell'] = '/usr/local/psa/bin/chrootsh'

				preferences_children.append(xml.elem('sysuser', [xml.text_elem('password', sys_user_password, {'type': 'plain'})], sys_user_attributes))
			if hosting_preferences_ids.has_key(23): # frontpage
				self.conn.execute(u""" SELECT login,password FROM frontpage WHERE id= %s """ % hosting_preferences_ids[23])
				row = self.conn.fetchone()

				if row is not None and None not in row:	# Windows-based subscriptions can have frontpage resource enabled, but not provided with credentials. Skip such frontpage.
					fp_user_login, fp_user_password = self.conn.fetchone()
					preferences_children.append(xml.elem('fpuser', [
						xml.text_elem('password', fp_user_password, {'type': 'plain'})
					], {'name': fp_user_login}))

		return xml.elem('preferences', preferences_children)
	
	def _extract_hosting_preferences(self, domain_id):
		self.conn.execute(u""" SELECT pc1.child_id, pc1.child_type FROM parent_child pc1 
			INNER JOIN parent_child pc2 ON pc1.parent_id = pc2.child_id AND pc1.parent_type = pc2.child_type 
			WHERE 
				pc1.parent_type = 9 AND pc2.parent_id = %s 
				AND pc2.parent_type IN (2, 31, 34, 35)""" % (domain_id))
		# 9 - Hosting
		# 2 - domain; 31 - subdomains; 34 - Service Domain; 35 - Third level domain
		ret = {}
		for child_id, child_type in self.conn.fetchall():
			ret[child_type] = child_id
		return ret

	def _get_certificates_node(self, report, domain_id):
		certificate_nodes = self._get_manually_installed_certificates(report, domain_id)
		if len(certificate_nodes) > 0:
			return xml.elem('certificates', certificate_nodes)
		else:
			return None

	def _get_comodo_certificates(self, report, domain_id):
		"""Return list of <certificate> nodes for Comodo SSL certificates
		"""
		domain_name = self._get_domain_name(domain_id)
		self.conn.execute("""
			SELECT cs.pri, cs.cert
			FROM comodo_ssl cs
			JOIN parent_child pc_hosting ON pc_hosting.child_id = cs.id AND pc_hosting.child_type = 152 	-- ssl certificates
			JOIN parent_child pc_domain ON pc_domain.child_id = pc_hosting.parent_id
			WHERE pc_domain.parent_id = %s
		""" % domain_id)
		certificate_nodes = []
		for pkey, cert in self.conn.fetchall():
			if cert is not None:	# do not transfer the not-yet-issued certificates
				certificate_nodes.append(xml.elem(
					'certificate',
					[
						xml.text_elem('certificate-data', cert),
						xml.text_elem('private-key', pkey),
					],
					{
						'name': domain_name,
					}
				))
		return certificate_nodes

	def _get_manually_installed_certificates(self, report, domain_id):
		"""Return list of <certificate> nodes for manually installed certificates (self-signed or purchased elsewhere)
		"""
		hosting_preferences_ids = self._extract_hosting_preferences(domain_id)
		if not hosting_preferences_ids.has_key(24): #24 - ssl
			self.logger.debug(u"Information about SSL certificates for the domain with ID %s will not be retrieved." % domain_id)
			return []

		domain_name = self._get_domain_name(domain_id)
		domain_ip = self._get_domain_ip(domain_name)
		
		sysuser_row = self._get_sys_user(domain_id)
		if sysuser_row is None:
			self.logger.debug(u"Information about SSL certificates for the domain with ID %s will not be retrieved because there is no system user." % domain_id)
			return []

		sys_user_login, _, os_type = sysuser_row
		if os_type != 1: # if not unix
			node_ip = self._get_node_ip(self._get_domain_name(domain_id))
			if not self.iis_nodes_settings.has_key(node_ip):
				report.add_issue(checking.Problem('hsphere_subscription_issue', checking.Problem.WARNING,
					u"Unable to retrieve the SSL certificates of domain %s because there are no credentials specified for the server at %s. Specify these credentials in the migration tool's configuration." % (domain_name, node_ip)),
					u""
				)
				return []
			try:
				script_filename = 'get_domain_certificate.vbs'
				source_runner = run_command.WinexeRunner(self.iis_nodes_settings[node_ip])

				source_temp_filepath = self.windows_session_dir + '\\' + script_filename
				source_runner.mkdir(self.windows_session_dir)
				source_runner.upload_file(self.get_hsphere_script_local_path_func(script_filename), source_temp_filepath)
				source_runner.run('cscript', [source_temp_filepath, domain_name, self.windows_session_dir])

				pfx_filename = self.get_session_file_path_func('%s.pfx' % domain_name)
				source_runner.get_file('%s\\%s.pfx' % (self.windows_session_dir, domain_name), pfx_filename)

				source_runner.remove_file(source_temp_filepath)
				source_runner.remove_file('%s\\%s.pfx' % (self.windows_session_dir, domain_name))

				pem_filename = self.get_session_file_path_func('%s.pem' % domain_name)
				key_filename = self.get_session_file_path_func('%s.key' % domain_name)
				crt_filename = self.get_session_file_path_func('%s.crt' % domain_name)

				self.ppa_runner.run('openssl', ['pkcs12', '-in', pfx_filename, '-out', pem_filename, '-nodes', '-password', 'pass:Password'])
				self.ppa_runner.run('openssl', ['pkey', '-in', pem_filename, '-out', key_filename])
				self.ppa_runner.run('openssl', ['x509', '-in', pem_filename, '-out', crt_filename])

				certificate_data = self.ppa_runner.sh('cat %s' % crt_filename)
				certificate_child = [xml.text_elem('certificate-data', certificate_data)]

				private_key = self.ppa_runner.sh('cat %s' % key_filename)
				certificate_child.append(xml.text_elem('private-key', private_key))
				return [xml.elem('certificate', certificate_child, {'name': domain_name})]
			except Exception as e:
				self.logger.debug(u"Exception: ", exc_info=e)
				report.add_issue(checking.Problem('hsphere_subscription_issue', checking.Problem.WARNING, str(e)),
					u"Unable to retrieve information about SSL certificates for  the domain {domain_name} from the H-Sphere node at IP address {service_node_ip}:\n"
					u"1. Try to re-run the migration tool. (This could happen because of a temporary network issue).\n"
					u"2. Migrate the certificates manually.".format(service_node_ip=node_ip, domain_name=domain_name)
				)
				return []

		base_path = '/hsphere/local/home/%s/ssl.conf/%s' % (sys_user_login, domain_name)
		try:
			db_server_runner = HsphereRunner(self.cp_runner, domain_ip)

			certificate_data = db_server_runner.sh('cat %s/server.crt' % base_path)

			certificate_child = [xml.text_elem('certificate-data', certificate_data)]
			self.conn.execute(u"SELECT chain, cert, rev, vdepth, vclient, site_name FROM apache_ssl WHERE id = %s" % hosting_preferences_ids[24])
			certificate_info = self.conn.fetchone()
			if certificate_info is not None and certificate_info[0] == 1:
				ca_certificate = db_server_runner.sh('cat %s/ca.crt' % base_path)
				certificate_child.append(xml.text_elem('ca-certificate', ca_certificate))

			private_key = db_server_runner.sh('cat %s/server.key' % base_path)
			certificate_child.append(xml.text_elem('private-key', private_key))
			return [xml.elem('certificate', certificate_child, {'name': domain_name})]
		except Exception as e:
			self.logger.error(u"Unable to retrieve information about SSL certificates for the domain %s: %s" % (domain_name , str(e)))
			report.add_issue(checking.Problem('hsphere_subscription_issue', checking.Problem.WARNING, str(e)),
				u"Unable to retrieve information about SSL certificates for  the domain {domain_name} from the H-Sphere node at IP address {service_node_ip}:\n"
				u"1. Try to re-run the migration tool. (This could happen because of a temporary network issue).\n"
				u"2. Execute the necessary command in the console and make sure that the certificate's files and directories ({base_path}) are present at node {service_node_ip}:\n"
				u"   2.1. Log in to the H-Sphere node as root.\n"
				u"""   2.2. Execute the command "su - cpanel -c \"ssh root@{service_node_ip} '<command>'\"".\n"""
				u"3. Migrate the certificates manually.".format(service_node_ip=domain_ip, domain_name=domain_name, base_path=base_path)
			)
		return []

	def _get_databases_node(self, domain_id, report):
		""" Only Mysql databases is supported"""
		account_id = self._get_account_id(domain_id)
		databases_children = []
		if self._is_account_resource_supported(account_id, 6000): # MySQL databases
			databases_children += self._get_mysql_databases_nodes(account_id, report)
		if self._is_account_resource_supported(account_id, 6800): # MS SQL databases
			databases_children += self._get_mssql_databases_nodes(account_id, report)

		# Database users that have access to all databases:
		# when migrating MySQL databases we consider that all database users of subscription
		# have access to all databases of subscription; we perform such change
		# because Plesk supports only 2 modes: 
		# - user has access to the only database
		# - user have has access to all databases of subscription
		if self._is_account_resource_supported(account_id, 6000): # MySQL databases
			databases_children += self._get_mysql_user_nodes(account_id, report)
		
		if len(databases_children) == 0:
			return None
		else:
			return xml.elem('databases', databases_children)

	def _get_mysql_databases_nodes(self, account_id, report):
		""" Only Mysql databases is supported"""
		databases_children = []
		
		self.conn.execute(u""" SELECT r.mysql_host_id, db.db_name FROM mysqlres r 
			INNER JOIN mysqldb db ON r.id = db.parent_id 
			INNER JOIN parent_child pc ON r.id = pc.child_id AND pc.child_type = 6000 
			WHERE pc.account_id = %s
		""" % account_id)
		
		for mysql_server_id, mysql_db_name in self.conn.fetchall():
			db_query = "SELECT ip FROM l_server_ips WHERE l_server_id = %s" % mysql_server_id
			self.conn.execute(db_query)
			mysql_server_ip = self.conn.fetchone()[0]
			if mysql_server_ip is not None:
				database_children = [xml.elem('db-server', [
					xml.text_elem('host', mysql_server_ip),
					xml.text_elem('port', '3306'), # Default MySql port
				], {'type': 'mysql'})]

				databases_children.append(xml.elem('database', database_children, {'name': mysql_db_name, 'type': 'mysql'}))
			else:
				report.add_issue(checking.Problem('hsphere_subscription_issue', checking.Problem.ERROR,
						u"There are inconsistencies in the H-Sphere's database.\n"
						u"The SQL query: %s.\n"
						u"The database server should have a valid IP address." % db_query
					),
					u"All databases on this database server will not be migrated to PPA. Resolve inconsistencies in the H-Sphere's database and re-run the migration tool."
				)
				continue
			
		return xml.elem('databases', databases_children)

	def _get_mysql_user_nodes(self, account_id, report):
		db_users = []

		self.conn.execute(u""" SELECT r.mysql_host_id FROM mysqlres r 
			INNER JOIN parent_child pc ON r.id = pc.child_id AND pc.child_type = 6000 
			WHERE pc.account_id = %s
		""" % account_id)

		for mysql_server_id, in self.conn.fetchall():
			mysql_server_ip = self._get_l_server_ip_by_id(mysql_server_id)
			if mysql_server_ip is not None:
				db_query = u""" SELECT mysql_users.login, mysql_users.password  FROM mysql_users
					JOIN parent_child ON mysql_users.parent_id = parent_child.child_id
					WHERE
						parent_child.parent_id = %s
						AND parent_child.child_type = 6000
				""" % account_id
				self.conn.execute(db_query)

				for login, password in self.conn.fetchall():
					if login is not None and password is not None:
						db_users.append(xml.elem('dbuser', [
							xml.text_elem('password', password, {'type': 'plain'}),
							xml.elem('db-server', [
								xml.text_elem('host', mysql_server_ip),
								xml.text_elem('port', '3306'), # default MySQL port
							], {'type': 'mysql'})
						], {'name': login}))
					else:
						report.add_issue(checking.Problem('hsphere_subscription_issue', checking.Problem.ERROR,
								u"There are inconsistencies in the H-Sphere's database.\n"
								u"The SQL query: %s.\n"
								u"Unable to add information about the MySQL server to the backup because the username and password are not specified." % (db_query,)
							),
							u"To execute an H-Sphere database query:\n"
							u"1. Log in to the H-Sphere node as root.\n"
							u"2. Execute the command \"su - cpanel -c 'psql hsphere'\" to open the PostgreSQL console."
							u"The database will not be migrated to PPA. Resolve inconsistencies in the H-Sphere's database and re-run the migration tool."
						)
			else:
				report.add_issue(checking.Problem('hsphere_subscription_issue', checking.Problem.ERROR,
						u"There are inconsistencies in the H-Sphere's database.\n"
						u"The SQL query: %s.\n"
						u"The database server should have a valid IP address." % db_query
					),
					u"All databases on this database server will not be migrated to PPA. Resolve inconsistencies in the H-Sphere's database and re-run the migration tool."
				)
				continue

		return [xml.elem('dbusers', db_users)]

	def _get_l_server_ip_by_id(self, l_server_id):
		db_query = "SELECT ip FROM l_server_ips WHERE l_server_id = %s" % l_server_id
		self.conn.execute(db_query)
		return self.conn.fetchone()[0]

	def _get_mssql_databases_nodes(self, account_id, report):
		databases_nodes = []

		self.conn.execute(u"""
			SELECT lsi.ip, mr.id
			FROM l_server_ips lsi
			JOIN l_server ls ON ls.id = lsi.l_server_id
			JOIN mssqlres mr ON mr.mssql_host_id = ls.id
			JOIN parent_child pc ON pc.child_id = mr.id AND pc.child_type = 6800	-- MS SQL
			WHERE pc.account_id = %s
		""" % account_id)
		# 6800 - MS SQL
		for mssql_server_ip, mssql_res_id in self.conn.fetchall():
			db_server_node = xml.elem('db-server', [
				xml.text_elem('host', mssql_server_ip),
				xml.text_elem('port', '0'),
			], {'type': 'mssql'})

			# select ids and names of databases with the main logins having access to these databases
			self.conn.execute(u"""
				SELECT md.id, md.mssql_db_name, ml.login, ml.password
				FROM mssql_dbs md
				JOIN mssql_logins ml ON ml.id = md.mssql_login_id
				JOIN parent_child pcd ON pcd.child_id = md.id AND pcd.child_type = 6802		-- MS SQL Database
				JOIN parent_child pcl ON pcl.child_id = pcd.parent_id AND pcl.child_type = 6801	-- MS SQL Login
				WHERE pcl.parent_id = %s
			""" % (mssql_res_id))
		
			for db_id, db_name, login, password in self.conn.fetchall():
				db_users_nodes = [xml.elem('dbuser', [
					xml.text_elem('password', password, {'type': 'plain'}),
				], {'name': login, 'default': 'true'})]
	
				# fetch additional logins that have access to this db
				self.conn.execute(u"""
					SELECT ml.login, ml.password, mu.user_name
					FROM mssql_logins ml
					JOIN mssql_users mu ON mu.mssql_login_id = ml.id
					WHERE mu.mssql_db_id = %s
				""" % (db_id))

				has_different_login_user = False
				for login, password, user_name in self.conn.fetchall():
					if login != user_name:
						has_different_login_user = True
						warn_msg = u"The Microsoft SQL database user '%s' of the database '%s' has name different from the associated database login '%s'" % (user_name, db_name, login)
						self.logger.warning(warn_msg)
						report.add_issue(checking.Problem('hsphere_subscription_issue', checking.Problem.WARNING, warn_msg),
							u"Such database users are not supported by PPA. PPA requires login to be equal to user name. Please do either of:\n"
							"* Adjust the data in H-Sphere: make the database login equal to the database user name, and fix the database permissions of this user.\n"
							"The migration tool will then transfer this database with corrected users.\n"
							"* Use the --ignore-fetch-source-errors option to skip transferring this database and all logins and users related to it.\n" 
							"You will have to transfer this database manually afterwards."
						)
						continue

					db_users_nodes.append(xml.elem('dbuser', [
						xml.text_elem('password', password, {'type': 'plain'}),
					], {'name': login, 'default': 'false'}))
			
				if not has_different_login_user:
					databases_nodes.append(
						xml.elem('database', [db_server_node] + db_users_nodes, {'name': db_name, 'type': 'mssql'})
					)

		return databases_nodes

	def _get_mailsystem_node(self, domain_id):
		if not self._is_domain_resource_supported(domain_id, 1000): # 1000 - Mail Service
			self.logger.debug(u"Skip mailsystem node fetching for #'%s' domain: there is not mail service for domain." % domain_id)
			return None

		properties_children = [self._get_status_element()] # status of mailsystem - enabled
		domain_mail_ip = self._get_domain_mail_ip(self._get_domain_name(domain_id))
		if domain_mail_ip is not None:
			properties_children.append(self._get_ip_node(domain_mail_ip))

		mailsystem_children = [xml.elem('properties', properties_children)]
		mailusers = self._get_mailusers_node(domain_id)
		if mailusers is not None:
			mailsystem_children.append(mailusers)

		mailsystem_preferences = self._get_mailsystem_preferences_node(domain_id)
		if mailsystem_preferences is not None:
			mailsystem_children.append(mailsystem_preferences)

		return xml.elem('mailsystem', mailsystem_children)

	def _get_mailsystem_preferences_node(self, domain_id):
		account_id = self._get_account_id(domain_id)
		preferences_children = []
		
		self.logger.debug(u"Fetch domain-keys for '#%s' domain" % domain_id)
		self.conn.execute(u""" SELECT * FROM parent_child 
			WHERE 
				parent_type = 1001 
				AND child_type = 1020 
				AND account_id = %s """ % (account_id))
		# 1001 - mail domain
		# 1020 - domain keys
		if self.conn.rowcount() != 0:
			preferences_children.append(xml.elem('domain-keys', [], {'state': 'true'}))
		
		self.logger.debug(u"Fetch grey listing for '#%s' domain" % domain_id)
		self.conn.execute(u""" SELECT * FROM parent_child 
			WHERE 
				parent_type = 1001 
				AND child_type = 1022 
				AND account_id = %s """ % (account_id))
		# 1001 - mail domain
		# 1022 - grey listing
		if self.conn.rowcount() != 0:
			preferences_children.append(xml.text_elem('grey-listing', 'on'))

		if len(preferences_children) == 0:
			return None
		return xml.elem('preferences', preferences_children)
	
	def _get_mailusers_node(self, domain_id):
		domain_name = self._get_domain_name(domain_id)
		mailusers_children = []
		self.conn.execute(u"""SELECT child_id FROM parent_child 
			WHERE 
				parent_id = (
					SELECT child_id FROM parent_child 
						WHERE 
							parent_id = (
								SELECT child_id FROM parent_child 
									WHERE 
										parent_id = %s 
										AND child_type = 1000
							) 
							AND child_type = 1001
				) 
				AND child_type IN (1002,1004,1005)
		""" % domain_id)
		# 1001 - mail domain
		# 1002 - mailbox
		# 1004 - mail_forward
		# 1005 - responder
		
		for row in self.conn.fetchall():
			is_mailbox_enabled = False
			mailbox_id = row[0]
			self.conn.execute(u"SELECT email, password FROM mailboxes WHERE id = %s AND full_email like '%%@%s'" % (mailbox_id, domain_name))
			fetch_result = self.conn.fetchone()
			[email_name, password] = [None, None]
			if fetch_result is not None:
				is_mailbox_enabled = True
				email_name, password = fetch_result
			else:
				self.conn.execute(u"SELECT email_local FROM mail_forwards WHERE id = %s" % mailbox_id)
				fetch_result = self.conn.fetchone()
				if fetch_result is not None:
					if not self._is_mailbox_exists(fetch_result[0] + '@' + domain_name):
						email_name = fetch_result[0]
					else:
						self.logger.debug(u"Skip '%s' forwarding mailuser due to mailbox with same name exists" % (fetch_result[0] + '@' + domain_name))
						continue
				else:
					self.conn.execute(u"SELECT local_email FROM responders WHERE id = %s" % mailbox_id)
					fetch_result = self.conn.fetchone()
					if fetch_result is not None:
						if self._is_mailbox_exists(fetch_result[0] + '@' + domain_name) == False and self._is_mail_forwarding_enabled(domain_id, fetch_result[0]) == False:
							email_name = fetch_result[0]
						else:
							self.logger.debug(u"Skip '%s' autoresponderdue to mailbox or forwarding with same name exists" % (fetch_result[0] + '@' + domain_name))
							continue
					else:
						self.logger.debug(u"Unable to determine type of '%s' email" % mailbox_id)
						continue

			mailuser_attributes = {'name': email_name}
			mailuser_attributes['forwarding-enabled'] = 'true' if self._is_mail_forwarding_enabled(domain_id, email_name) else 'false'
			
			self.conn.execute(u"""SELECT quotas.size_mb FROM quotas 
				JOIN parent_child ON parent_child.child_id = quotas.id
				WHERE 
					parent_child.parent_id = %s""" % mailbox_id)
			quota = self.conn.fetchone()
			
			if quota is not None:
				mailuser_attributes['mailbox-quota'] = str(quota[0] * 1024 * 1024) # set quota in Mb

			mailuser_properties_children = []
			if is_mailbox_enabled == True:
				mailuser_properties_children.append(xml.text_elem('password', password, {'type': 'plain'}))

			mailuser_children = [
				xml.elem('properties', mailuser_properties_children),
				self._get_mailuser_preferences_node(domain_id, email_name, mailbox_id, is_mailbox_enabled)
			]
			
			mailusers_children.append(
				xml.elem('mailuser', mailuser_children, mailuser_attributes)
			)

		if len(mailusers_children) == 0:
			return None

		return xml.elem('mailusers', mailusers_children)

	def _get_mailuser_preferences_node(self, domain_id, email_name, mailbox_id, is_mailbox_enabled):
		if is_mailbox_enabled:
			preferences_children = [xml.elem('mailbox', [], {'enabled': 'true', 'type': 'mbox'})]
		else:
			preferences_children = [xml.elem('mailbox', [], {'enabled': 'false'})]

		self.conn.execute(u"SELECT account_id FROM parent_child WHERE child_id = %s" % mailbox_id)
		account_id = self.conn.fetchone()[0]
		
		self.conn.execute(u""" SELECT child_id FROM parent_child 
			WHERE 
				parent_id = (
					SELECT child_id FROM parent_child 
						WHERE parent_id = %s AND child_type = 1000
				) 
				AND child_type = 1001""" % domain_id)
		# 1001 - mail domain
		domain_mail_id = self.conn.fetchone()[0]
		domain_name = self._get_domain_name(domain_id)
		
		self.logger.debug(u"Fetch domain aliases for '#%s' mailbox" % mailbox_id)
		
		self.conn.execute(u""" SELECT a.email_local FROM parent_child p 
			JOIN mail_aliases a ON a.id = p.child_id 
			WHERE 
				p.parent_id = {domain_mail_id}
				AND p.child_type = 1006 
				AND (a.email_foreign LIKE '%%;{email_name};%%' OR a.email_foreign LIKE '{email_name};%%' OR a.email_foreign LIKE '{email_name}')
		""".format(domain_mail_id = domain_mail_id, email_name = email_name))
		# 1006 - alias

		# There are some options that are not supported by plesk/ppa and other plesk based products:
		# Mailalias to existed mailbox and other
		# We detect such configuration we : 
		#  1. add only aliases that points to not existed mail addresses;
		#  2. substitute aliases that points to existing mailboxes by forwaring to 'first' mailbox
		for alias_name in self.conn.fetchall():
			if self._is_mailbox_exists(alias_name[0] + '@' + domain_name) == False and self._is_mail_forwarding_enabled(domain_id, alias_name[0]) == False:
				preferences_children.append(xml.text_elem('alias', alias_name[0]))
			else:
				self.logger.debug(u"Skip '%s' alias for '%s' domain due to such mailbox already exists" % (alias_name[0], domain_name))

		self.logger.debug(u"Fetch forwarding for '#%s' mailbox" % mailbox_id)
		self.conn.execute(u""" SELECT m.email_foreign FROM parent_child p
			JOIN mail_forwards m ON m.id = p.child_id 
			WHERE 
				p.parent_id = %s 
				AND p.child_type = 1004
				AND m.email_local = '%s' """ % (domain_mail_id, email_name))

		# 1001 - mail domain
		# 1004 - forwarding
		for forwarding_list in self.conn.fetchall():
			for forwarding_name in filter(None, forwarding_list[0].split(';')):
				preferences_children.append(xml.text_elem('forwarding', forwarding_name))

		# Iterate over mailaliases to find some aliases that point to current mailbox		
		self.conn.execute(u""" SELECT mail_aliases.email_local, mail_aliases.email_foreign FROM parent_child 
			JOIN mail_aliases ON mail_aliases.id = parent_child.child_id 
			WHERE 
				parent_id = %s AND child_type = 1006"""
			% domain_mail_id)
		
		for local_mail, foreign_list in self.conn.fetchall():
			if local_mail == email_name:
				for alias_forwarding in filter(None, foreign_list.split(';')):
					if self._is_mailbox_exists(local_mail + '@' + domain_name) == False:
						self.logger.debug(u"Add '%s' forwarding for #'%s' mailbox" % (alias_forwarding + '@' + domain_name, mailbox_id))
						preferences_children.append(xml.text_elem('forwarding', alias_forwarding + '@' + domain_name))

		self.logger.debug(u"Fetch autoresponders for '#%s' mailbox" % mailbox_id)
		self.conn.execute(u""" SELECT responders.foreign_email, responders.subject, responders.message, responders.include_incoming FROM parent_child 
			JOIN responders ON responders.id = parent_child.child_id
			WHERE 
				parent_child.parent_id = %s  
				AND parent_child.child_type = 1005 
				AND responders.local_email = '%s' """ % (domain_mail_id, email_name))
		# 1001 - mail domain
		# 1005 - Responder
		if self.conn.rowcount() != 0:
			responders_children = []
			for _, subject, message, _ in self.conn.fetchall():
				responders_children.append(xml.elem('autoresponder', [
					xml.text_elem('text', base64.b64encode(message), {'charset': 'utf-8'})
				], {'status': 'on', 'subject': base64.b64encode(subject), 'content-type': 'text/plain'})
			)

			preferences_children.append(xml.elem('autoresponders', responders_children))

		self.logger.debug(u"Fetch antispam for '#%s' mailbox" % mailbox_id)
		self.conn.execute(u""" SELECT antispam.use_mdomain_prefs FROM parent_child 
			JOIN antispam ON antispam.id = parent_child.child_id
			WHERE 
				parent_child.parent_type = 1001 
				AND parent_child.child_type = 1011 
				AND parent_child.account_id = %s 
				AND antispam.local = '%s' """ % (account_id, email_name))
		# 1001 - mail domain
		# 1011 - antispam
		if self.conn.rowcount() != 0:
			if self.conn.fetchone()[0] == 1:
				spamassasin_children = []
				spamassasin_attributes = {'status': 'on'}
				
				self.conn.execute(u'SELECT preference, value FROM mail_preferences WHERE mobject_id = %s' % domain_mail_id)
				for pref_name, pref_value in self.conn.fetchall():
					if pref_name == 'spam_blacklist_from':
						for bl_email in filter(None, pref_value.split(',')):
							spamassasin_children.append(xml.text_elem('blacklist-member', bl_email))
					elif pref_name == 'spam_whitelist_from':
						for wl_email in filter(None, pref_value.split(',')):
							spamassasin_children.append(xml.text_elem('whitelist-member', wl_email))
					elif pref_name == 'spam_processing':
						if pref_value == 'remove':
							spamassasin_attributes['action'] = 'delete'
						elif pref_value == 'mark':
							spamassasin_attributes['action'] = 'mark'
						else:
							spamassasin_attributes['action'] = 'move'

				preferences_children.append(xml.elem('spamassassin', spamassasin_children, spamassasin_attributes))

		self.logger.debug(u"Fetch antivirus for '#%s' mailbox" % mailbox_id)
		self.conn.execute(u""" SELECT antivirus.use_mdomain_prefs FROM parent_child 
			JOIN antivirus ON antivirus.id = parent_child.child_id
			WHERE 
				parent_child.parent_type = 1001 
				AND parent_child.child_type = 1012 
				AND parent_child.account_id = %s 
				AND antivirus.local = '%s' """ % (account_id, email_name))
		# 1001 - mail domain
		# 1012 - antivirus
		if self.conn.rowcount() != 0:
			if self.conn.fetchone()[0] == 1:
				preferences_children.append(xml.elem('virusfilter', [], {'state': 'in'})) # Set 'in' for antivirus due to Plesk cannot support other H-sphere options for antivirus

		return xml.elem('preferences', preferences_children)

	def _is_mailbox_exists(self, email):
		self.logger.debug(u"Check that '%s' mail exists." % email)
		self.conn.execute(u" SELECT id FROM mailboxes WHERE full_email = '%s'" % email)
		return True if self.conn.rowcount() != 0 else False

	def _is_mail_forwarding_enabled(self, domain_id, mailname):
		self.conn.execute(u""" SELECT child_id FROM parent_child 
			WHERE 
				parent_id = (
					SELECT child_id FROM parent_child 
						WHERE parent_id = %s AND child_type = 1000
				) 
				AND child_type = 1001""" % domain_id)
		# 1001 - mail domain
		domain_mail_id = self.conn.fetchone()[0]
		
		self.conn.execute(u""" SELECT m.id FROM parent_child p
			JOIN mail_forwards m ON m.id = p.child_id 
			WHERE 
				p.parent_id = %s 
				AND p.child_type = 1004
				AND m.email_local = '%s'"""
		% (domain_mail_id, mailname))
		# 1004 - mail_forward
		
		return True if self.conn.rowcount() !=0 else False

	def _get_sys_user(self, domain_id):
		db_query = u"""SELECT uu.login, uu.password, ps.os_type
			FROM unix_user uu JOIN l_server ls ON ls.id = uu.hostid JOIN p_server ps ON ps.id = ls.p_server_id
			WHERE uu.id = (SELECT parent_id FROM parent_child WHERE parent_type = 7 AND child_id = %s)""" % domain_id
		# 7 - Web Server User (unixuser)
		self.conn.execute(db_query)

		sysuser_row = self.conn.fetchone()
		if sysuser_row is not None:
			sys_user_name = sysuser_row[0]
			sys_user_password = sysuser_row[1]
			self._assert_db_inconsistency(
				sys_user_name is not None,
				u"H-Sphere database inconsistency.\n"
				u"Query was '%s'.\n"
				u"Sysuser has empty login. Expected that sysuser does not have empty login." % db_query,
			)
			self._assert_db_inconsistency(
				sys_user_password is not None,
				u"H-Sphere database inconsistency. \n"
				u"Query was '%s'.\n"
				u"Sysuser '%s' has empty password. Expected that sysuser does not have empty password." % (db_query, sys_user_name)
			)

		return sysuser_row

	def _get_web_settings_node(self, domain_id):
		self.conn.execute(u"""
			SELECT di.name FROM directory_ind di
			JOIN parent_child pc_di ON pc_di.child_id = di.id AND pc_di.child_type = 55
			JOIN parent_child pc_hosting ON pc_hosting.child_id = pc_di.parent_id AND pc_hosting.child_type = 9
				WHERE pc_hosting.parent_id = %d
		""" % domain_id)
		# 9 - hosting
		# 55 - directory indexes
		if self.conn.rowcount == 0:
			return None

		dir_value = ' '.join([i[0] for i in self.conn.fetchall()])
		settings = [xml.elem('setting', [xml.text_elem('name', 'directoryIndex'), xml.text_elem('value', dir_value)], {})]

		return xml.elem('web-settings',settings, {'vhost-name': self._get_domain_name(domain_id)})

	def _get_scripting_node(self, domain_id, hosting_preferences_ids):
		# find out what versions of scripting languages are used by domain
		# the path to scripting is: domain <- vhost <- scripting
		# NOTE the pc_vhost.child_type = 9 condition. It is not necessary for correct join,
		# but greatly helps postgresql 7.4 query optimizer, forcing it to use indexes instead of scanning very large parent_child table.
		self.conn.execute(u"""
			SELECT rv.type_id, rv.version
			FROM resource_version rv
			JOIN parent_child pc_scripting ON pc_scripting.child_id = rv.resource_id
			JOIN parent_child pc_vhost ON pc_vhost.child_id = pc_scripting.parent_id AND pc_vhost.child_type = 9	-- hosting
			WHERE pc_vhost.parent_id = %s
		""" % domain_id)

		scripting_attributes = {}
		for row in self.conn.fetchall():
			if row[0] == 63:	# ASP.NET
				versions_hs_to_ppa = {
					'aspnet1_1': '1.1',
					'aspnet2_0': '2.0',
						   # '4.0' is Plesk's version string for ASP.NET 4.0.30319
				}
				scripting_attributes['managed_runtime_version'] = versions_hs_to_ppa.get(row[1], '2.0')
			elif row[0] == 11:	# PHP
				versions_hs_to_ppa = {
					'php4_4': '4',
					'php5_2': '5',
					'php5_3': '5.3',
				}
				scripting_attributes['php_version'] = versions_hs_to_ppa.get(row[1], '5')

		for scripting_key, scripting_name in {13: 'ssi', 11: 'php', 10: 'cgi', 27: 'asp', 15: 'ssi', 63: 'asp_dot_net', 6500: 'coldfusion'}.iteritems():#202: 'ruby'
			if hosting_preferences_ids.has_key(scripting_key):
				scripting_attributes[scripting_name] = 'true'

		return xml.elem('limits-and-permissions',[
			xml.elem('scripting', [], scripting_attributes)
		])
	
	def _get_account_preferences_node(self, preferences):
		pinfo = []
		for attribute_name, attribute_value in preferences.iteritems():
			if attribute_value == '' or attribute_value is None:
				continue
			pinfo.append(xml.text_elem('pinfo', attribute_value, {'name': attribute_name}))
		return xml.elem('preferences', pinfo)

	def _get_account_properties_node(self, password, password_type, suspended='enabled'):
		return xml.elem('properties', [
			xml.text_elem('password', password, {'type': password_type}),
			self._get_status_element(suspended)
		])

	def _get_node_ip(self, domain_name):
		# determine host_id
		self.conn.execute(u"""
			SELECT apache_vhost.host_id, iis_vhost.host_id
			FROM domains d
			JOIN parent_child pc ON pc.parent_id = d.id AND pc.child_type = 9	-- Hosting
			LEFT JOIN apache_vhost ON apache_vhost.id = pc.child_id
			LEFT JOIN iis_vhost ON iis_vhost.id = pc.child_id
			WHERE d.name = '%s'
		""" % domain_name)
		row = self.conn.fetchone()
		if row is None:
			return None
		host_id = row[0] or row[1]

		# try retrieving shared IP
		self.conn.execute(u"""
			SELECT lsi.ip
			FROM l_server_ips lsi
			WHERE lsi.l_server_id = %s AND lsi.flag = 2	-- shared IP
		""" % host_id)

		shared_ip = self.conn.fetchvalue()
		if shared_ip is not None:
			self.logger.debug(u"Domain '%s' has shared IP '%s'", domain_name, shared_ip)
			return shared_ip

		return None

	def _get_domain_ip(self, domain_name):
		# determine host_id
		self.conn.execute(u"""
			SELECT apache_vhost.host_id, iis_vhost.host_id
			FROM domains d
			JOIN parent_child pc ON pc.parent_id = d.id AND pc.child_type = 9	-- Hosting
			LEFT JOIN apache_vhost ON apache_vhost.id = pc.child_id
			LEFT JOIN iis_vhost ON iis_vhost.id = pc.child_id
			WHERE d.name = '%s'
		""" % domain_name)
		row = self.conn.fetchone()
		if row is None:
			return None
		host_id = row[0] or row[1]

		# determine IP resource ID
		# domains without such resource do not have IP address
		# domains with such resource have
		# either dedicated IP address (linked with this resource ID)
		# or shared IP (just any shared IP address on logical server)
		self.conn.execute(u"""
			SELECT pc_ip.child_id
			FROM domains d
			JOIN parent_child pc_ip ON pc_ip.parent_id = d.id AND pc_ip.child_type = 8	-- IP address
			WHERE d.name =  '%s'
		""" % domain_name)
		row = self.conn.fetchone()
		if row is None:
			return None
		ip_res_id = row[0]

		# try retrieving dedicated IP
		self.conn.execute(u"""
			SELECT lsi.ip
			FROM l_server_ips lsi
			WHERE lsi.l_server_id = %s AND lsi.r_id = %s AND lsi.r_type = 8		-- IP address
		""" % (host_id, ip_res_id))
		dedicated_ip = self.conn.fetchvalue()

		if dedicated_ip is not None:
			self.logger.debug(u"Domain '%s' has dedicated IP '%s'", domain_name, dedicated_ip)
			return dedicated_ip

		# try retrieving shared IP
		self.conn.execute(u"""
			SELECT lsi.ip
			FROM l_server_ips lsi
			WHERE lsi.l_server_id = %s AND lsi.flag = 2	-- shared IP
		""" % host_id)

		shared_ip = self.conn.fetchvalue()
		if shared_ip is not None:
			self.logger.debug(u"Domain '%s' has shared IP '%s'", domain_name, shared_ip)
			return shared_ip

		self.logger.debug(u"Domain '%s' has no IP address", domain_name)
		return None

	def _get_domain_mail_ip(self, domain_name):
		self.conn.execute(u""" SELECT l_server_ips.ip FROM domains
				JOIN parent_child ON parent_child.parent_id = domains.id
				JOIN mail_services ON mail_services.id = parent_child.child_id
				JOIN l_server_ips ON l_server_ips.l_server_id = mail_services.mail_server
				WHERE
					parent_child.child_type = 1000
					AND domains.name = '%s' """ % (domain_name)) 
		# 1000 - mailsysem
		fetch_result = self.conn.fetchone()
		return fetch_result[0] if fetch_result is not None else None

	@cached
	def _get_all_exclusive_ips(self):
		"""Return the list of all l_server_ip addresses that are exclusive := referred from parent_child
		"""
		self.conn.execute(u'SELECT s.ip FROM l_server_ips s WHERE EXISTS (SELECT 1 FROM parent_child pc WHERE pc.child_id = s.r_id)')
		return [i[0] for i in self.conn.fetchall()]

	def _get_ip_node(self, ip):
		if ip is None:
			return None
		else:
			exclusive_ips = self._get_all_exclusive_ips()
			ip_type = 'shared'
			if ip in exclusive_ips:
				ip_type = 'exclusive'
			return xml.elem('ip', [
				xml.text_elem('ip-type', ip_type),
				xml.text_elem('ip-address', ip)
			])

	def _get_domain_name(self, domain_id):
		self.conn.execute(u"SELECT name FROM domains WHERE id = %s" % domain_id)
		return self.conn.fetchone()[0]
	
	def _get_account_id(self, domain_id):
		self.conn.execute(u"SELECT account_id FROM parent_child WHERE parent_id=%s" % domain_id)
		return self.conn.fetchone()[0]

	
	def _is_unix_domain(self, domain_id):
		self.conn.execute(u""" SELECT domains.id, domains.name FROM domains
			JOIN parent_child ON parent_child.child_id = domains.id
			WHERE 
				parent_child.child_type IN (2, 34, 35)
				AND parent_child.account_id = %s ORDER BY domains.id""" % (domain_id))
		
		fetch_result = self.conn.fetchone()
		return (fetch_result[0], fetch_result[1]) if fetch_result is not None else None
	
	def _get_main_domain(self, account_id):
		self.conn.execute(u""" SELECT domains.id, domains.name FROM domains
			JOIN parent_child ON parent_child.child_id = domains.id
			WHERE 
				parent_child.child_type IN (2, 34, 35)
				AND parent_child.account_id = %s ORDER BY domains.id""" % (account_id))# 2 - domain
		
		fetch_result = self.conn.fetchone()
		return (fetch_result[0], fetch_result[1]) if fetch_result is not None else None

	def _get_account_users_node(self, properties, role, preferences, attributes):
		limits_and_permissions = xml.elem('limits-and-permissions', [xml.text_elem('role-ref', role)])
		return xml.elem('users', [xml.elem('user', [properties, limits_and_permissions, preferences], attributes)])
	
	def _get_status_element(self, suspended = 'enabled', elem_name = 'status'):
		status_children = xml.elem('enabled') if suspended == 'enabled' else xml.elem('disabled-by', [xml.text_elem('name', suspended)])
		return xml.elem(elem_name, [status_children])

	def _is_domain_resource_supported(self, domain_id, resource_num):
		account_id = self._get_account_id(domain_id)
		self.conn.execute(u"SELECT child_id FROM parent_child WHERE account_id = %s AND parent_id = %s AND child_type = %s" % (account_id, domain_id, resource_num))
		return True if self.conn.fetchone() is not None else False

	def _is_account_resource_supported(self, account_id, resource_num):
		self.conn.execute(u"SELECT child_id FROM parent_child WHERE parent_id = %s AND child_type = %s" % (account_id, resource_num))
		return True if self.conn.fetchone() is not None else False

	def _strings_concat(self, str1, str2, delimiter = ' '):
		return delimiter.join([str1 if str1 is not None else '', str2 if str2 is not None else ''])
	
	def _get_default_state(self, state):
		return '' if state == 'NA' else state

	def _get_customer_report(self, login, reseller_id):
		if reseller_id == 1:
			return self.server_report.subtarget('Client', login)
		else:
			reseller_login = self._get_account_username_by_id(reseller_id)
			return self.server_report.subtarget('Reseller', reseller_login).subtarget('Client', login)

	def _assert_db_inconsistency(self, condition, message):
		if not condition:
			raise InconsistencyException(message)
