from parallels.ppa.source.helm4 import messages
import logging
import xml.etree.ElementTree as et
import os
import uuid

from parallels.core.utils.common import xml, generate_random_password, cached
from parallels.core.utils import unique_name_generator
from parallels.core.dump import save_backup_tar


class BackupAgent:
	logger = logging.getLogger(__name__)

	# Database service guids
	mssql_guid = 'FB0DA48E-3E80-4D15-9069-1F0A37B48EB2'	# mssql
	mssql2005_guid = 'FE043CBC-4D5A-4F6D-8DE4-F8B8AB0ED433'	# mssql 2005
	mssql2008_guid = '9A009F9F-CD69-4402-9588-F52819721421'	# mssql 2008
	mysql4_guid = 'C4480D0B-620F-4DD5-B0F4-5C42B3F662F3'	# mysql 4
	mysql5_guid = '24017B82-3769-484A-9808-9FD984DF5871'	# mysql 5

	# Web service guids
	iis6_guid = '62541890-8A7F-47B3-A099-ED7569347718'	# iis6
	iis7_guid = 'EF9200DF-2A17-4956-94D5-3B9911E19217'	# iis7

	# Mail service guids
	smart_guid = 'E2EA7F3E-C089-4E81-9097-55F13621DE4D'	# smartermail

	# FTP service guids
	msftp_guid = '26C5ADFD-EB95-4F35-AC21-1F880E2618B1'	# ms ftp

	def __init__(self, database_connection, helm_server_runner, helm_conn_settings):
		self._backup_tree = et.ElementTree(
				xml.elem('migration-dump', [], {'content-included': 'false', 'agent-name': 'PleskX', 'dump-format': 'panel', 'dump-version': '11.0.9'})
				)

		self._database_connection = database_connection
		self._helm_server_runner = helm_server_runner

		self._migration_tool_path = os.path.join(
			helm_conn_settings.migration_tool_dir,
			helm_conn_settings.migration_tool_name
		)

		# Max length user name 16
		self._unique_sysuser_names = unique_name_generator.UniqueNameGenerator("%d", 16)
		self._unique_ftpuser_names = unique_name_generator.UniqueNameGenerator("%d", 16)

	def make_backup(self):
		self._add_admin_node()

		self._add_server_node()
		
	def save_backup(self, backup_save_file):
		if os.path.exists(backup_save_file):
			self.logger.debug(messages.REMOVE_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_admin_node(self):
		admin_node = xml.elem('admin', [], {'guid': str(uuid.uuid1())})
		self._backup_tree.getroot().append(admin_node)

		self.logger.info(messages.FETCH_DOMAINS_COULD_TAKE_LONG_TIME)
		domains_info = self._get_domains_info()
		if domains_info is None:
			return None

		self._add_resellers_node(admin_node, 1, domains_info)
		self._add_clients_node(admin_node, 1, domains_info)
		self._add_subscriptions(admin_node, 1, domains_info)

	def _add_resellers_node(self, owner_node, owner_id, domains_info):
		resellers_node = et.SubElement(owner_node, 'resellers')
		self._database_connection.execute(u"""SELECT Account.AccountId, Account.AccountName, Account.CompanyName,
		Account.AddressLine1, Account.AddressLine2, Account.AddressLine3, Account.Town, Account.CountyCode, 
		Account.Postcode, Account.CountryCode, Account.EmailAddress, Account.AccountStatus FROM Account
		JOIN AccountRole ON AccountRole.AccountRoleId = Account.AccountRoleId 
		WHERE 
			Account.ParentAccountId = %s
			AND AccountRole.TemplateName = 'Reseller'""" % owner_id)

		for id, username, company, address1, address2, address3, city, county_code, zip, country_code, email, status in self._database_connection.fetchall():
			self.logger.info(messages.FETCH_RESELLERS_INFO_FOR_ACCOUNT % username)
			logins_data = self._get_logins_data(id)
			(login_name, password, first_name, last_name, locale) = logins_data[0]
			if (self._is_none_or_empty(first_name) and self._is_none_or_empty(last_name)):
				first_name = login_name

			preferences_node = self._create_account_preferences_node(
				{'country': country_code, 'phone': '', 'state': self._get_state(county_code, country_code, id), 'email': email, 'city': city, 'zip': zip[:8],
				'fax': '', 'address': self._strings_concat([address1, address2, address3]), 'company': company, 'locale': locale}
			)
			properties_node = self._create_account_properties_node(self._decrypt_password(password), 'plain', status)

			base_attributes = {'name': username, 'contact': self._strings_concat([last_name, first_name]), 'guid': str(uuid.uuid1())}
			reseller_attributes = base_attributes.copy()
			reseller_attributes.update({'id': str(id)})
			reseller_node = et.SubElement(resellers_node, 'reseller', reseller_attributes)

			reseller_node.append(preferences_node)
			reseller_node.append(properties_node)

			self._add_subscriptions(reseller_node, id, domains_info)
			self._add_clients_node(reseller_node, id, domains_info)

			users_node = et.SubElement(reseller_node, 'users')
			for login_name, password, first_name, last_name, locale in logins_data:
				if (self._is_none_or_empty(first_name) and self._is_none_or_empty(last_name)):
					first_name = login_name

				preferences_node = self._create_account_preferences_node(
					{'country': country_code, 'phone': '', 'state': self._get_state(county_code, country_code, id), 'email': email, 'city': city, 'zip': zip[:8],
					'fax': '', 'address': self._strings_concat([address1, address2, address3]), 'company': company, 'locale': locale}
				)
				properties_node = self._create_account_properties_node(self._decrypt_password(password), 'plain', status)

				user_attributes = base_attributes.copy()
				user_attributes.update({'name': login_name, 'is-domain-admin': 'false', 'email': email, 'is-built-in': 'true'})
			
				user_node = self._create_account_user_node(properties_node, 'Owner', preferences_node, user_attributes)				
				users_node.append(user_node)

	def _add_clients_node(self, owner_node, owner_id, domains_info):
		clients_node = et.SubElement(owner_node, 'clients')

		self._database_connection.execute(u"""SELECT Account.AccountId, Account.AccountName, Account.CompanyName,
		Account.AddressLine1, Account.AddressLine2, Account.AddressLine3, Account.Town, Account.CountyCode, 
		Account.Postcode, Account.CountryCode, Account.EmailAddress, Account.AccountStatus FROM Account
		JOIN AccountRole ON AccountRole.AccountRoleId = Account.AccountRoleId 
		WHERE 
			Account.ParentAccountId = %s
			AND AccountRole.TemplateName = 'User'""" % owner_id)
		for user_id, username, company, address1, address2, address3, city, county_code, zip, country_code, email, status in self._database_connection.fetchall():
			self.logger.info(messages.FETCH_CLIENTS_INFO_FOR_ACCOUNT % username)
			logins_data = self._get_logins_data(user_id)
			(login_name, password, first_name, last_name, locale) = logins_data[0]
			if self._is_none_or_empty(first_name):
				first_name = login_name
			if self._is_none_or_empty(last_name):
				last_name = login_name
				
			preferences_node = self._create_account_preferences_node(
				{'country': country_code, 'phone': '', 'state': self._get_state(county_code, country_code, user_id), 'email': email, 'city': city, 'zip': zip[:8],
				'fax': '', 'address': self._strings_concat([address1, address2, address3]), 'company': company, 'locale': locale}
			)
			properties_node = self._create_account_properties_node(self._decrypt_password(password), 'plain', status)
			
			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_node = et.SubElement(clients_node, 'client', clients_attributes)
			
			client_node.append(preferences_node)
			client_node.append(properties_node)

			self._add_subscriptions(client_node, user_id, domains_info)

			users_node = et.SubElement(client_node, 'users')
			for login_name, password, first_name, last_name, locale in logins_data:
				if (self._is_none_or_empty(first_name) and self._is_none_or_empty(last_name)):
					first_name = login_name

				preferences_node = self._create_account_preferences_node(
					{'country': country_code, 'phone': '', 'state': self._get_state(county_code, country_code, user_id), 'email': email, 'city': city, 'zip': zip[:8],
					'fax': '', 'address': self._strings_concat([address1, address2, address3]), 'company': company, 'locale': locale}
				)
				properties_node = self._create_account_properties_node(self._decrypt_password(password), 'plain', status)

				user_attributes = base_attributes.copy()
				user_attributes.update({'name': login_name, 'is-domain-admin': 'false', 'email': email, 'is-built-in': 'true'})
			
				user_node = self._create_account_user_node(properties_node, 'Owner', preferences_node, user_attributes)				
				users_node.append(user_node)

	def _add_subscriptions(self, account_node, account_id, domains_info):
		self._database_connection.execute(u"""SELECT OnlineServiceId, Name
				FROM OnlineService
				WHERE AccountId = %s""" % account_id)
		
		domains_node = et.SubElement(account_node, 'domains')
		domains_data = self._database_connection.fetchall()
		for domain_id, domain_name in domains_data:
			self.logger.info(messages.FETCH_DOMAINS_INFO % domain_name)
			domain_node = xml.elem('domain', [], {'guid': str(uuid.uuid1()), 'name': domain_name})

			self._add_subscription_children_nodes(domain_node, domain_id, domains_info)
			domains_node.append(domain_node)

	def _get_domains_info(self):
		try:
			domains_info_xml = self._helm_server_runner.sh('{migration_tool_path} --domains-info',
					dict(migration_tool_path=self._migration_tool_path))
		except Exception as e:
			self.logger.debug(messages.LOG_EXCEPTION, exc_info=True)
			self.logger.error(messages.CANNOT_GET_DOMAINS_INFO % e)
			return None
		return et.fromstring(domains_info_xml)

	def _add_subscription_children_nodes(self, domain_node, domain_id, domains_info):
		self._add_domain_preferences_node(domain_node, domain_id)
		self._add_domain_properties_node(domain_node, domain_id, domains_info)

		self._add_mailsystem_node(domain_node, domain_id, domains_info)

		self._add_databases_node(domain_node, domain_id, domains_info)

		#Parked domain doesn't have web hosting
		if self._is_parked_domain(domain_id) is True:
			return None

		shosting = self._create_shosting_node(domain_id, domains_info)
		if shosting is not None:
			domain_node.append(shosting)
		else:
			if self._is_web_hosting(domain_id) is True:
				phosting = self._create_phosting_node_for_domain(domain_id, self._get_domain_name(domain_id), domains_info)
				if phosting is not None:
					domain_node.append(phosting)

	@cached
	def _get_logins_data(self, account_id):
		self._database_connection.execute(u"""SELECT LoginName, LoginPassword, FirstName, LastName, LoginCulture FROM Login
		WHERE 
			LoginPassword IS NOT NULL 
			AND LoginPassword <> '' 
			AND AccountId = %s
		ORDER BY LoginId""" % account_id)
		return self._database_connection.fetchall()

	def _add_server_node(self):
		self.logger.info(u"Fetch server's info.")
		server_node = xml.elem('server')
		self._backup_tree.getroot().append(server_node)

		self._add_db_servers_node(server_node)

	def _add_db_servers_node(self, server_node):
		db_servers_node = et.SubElement(server_node, 'db-servers')

		self._database_connection.execute(u""" SELECT IpAddress, ServiceId, ProviderGuid FROM Service
				JOIN Server on Server.ServerId = Service.ServerId
				WHERE 
					ProviderGuid IN ('%s', '%s', '%s', '%s', '%s')"""
				% (self.mssql_guid, self.mssql2005_guid, self.mssql2008_guid, self.mysql4_guid, self.mysql5_guid))
		for server_ip, service_id, guid in self._database_connection.fetchall():
			server_instance_name = self._get_service_property(service_id, 'ServerInstanceName')
			db_admin_name = self._get_service_property(service_id, 'AdminUsername')
			db_admin_password = self._decrypt_password(self._get_service_property(service_id, 'AdminPassword'))
			db_port = self._get_service_property(service_id, 'ServerPort')

			db_servers_node.append(xml.elem('db-server', [
				xml.text_elem('host', self._strings_concat([server_ip, server_instance_name], '\\')),
				xml.text_elem('port', db_port),
				xml.elem('db-admin', [
					xml.text_elem('password', db_admin_password, {'type': 'plain'})
					], {'name': db_admin_name}),
				], {'type': 'mysql' if str(guid).upper() in [self.mysql4_guid, self.mysql5_guid] else 'mssql'}))

	def _create_account_preferences_node(self, preferences):
		pinfo = []
		for attribute_name, attribute_value in preferences.iteritems():
			if self._is_none_or_empty(attribute_value):
				continue
			pinfo.append(xml.text_elem('pinfo', attribute_value, {'name': attribute_name}))
		return xml.elem('preferences', pinfo)

	@classmethod
	def _create_account_properties_node(cls, password, password_type, suspended):
		return xml.elem('properties', [
			xml.text_elem('password', password, {'type': password_type}),
			cls._create_status_element(suspended)
		])

	@staticmethod
	def _create_account_user_node(properties, role, preferences, attributes):
		limits_and_permissions = xml.elem('limits-and-permissions', [xml.text_elem('role-ref', role)])
		return xml.elem('user', [properties, limits_and_permissions, preferences], attributes)

	@staticmethod
	def _create_status_element(suspended):
		status_children = xml.elem('enabled') if suspended == 0 else xml.elem('disabled-by', [], {'name' : 'admin'})
		return xml.elem('status', [status_children])

	@staticmethod
	def _strings_concat(strings_array, delimiter = ' '):
		return delimiter.join([s for s in strings_array if s is not None and s is not ''])

	@staticmethod
	def _is_none_or_empty(s):
		return s is None or s == ''

	@cached	
	def _get_state(self, county_code, country_code, accountId):
		if county_code is None:
			self._database_connection.execute(u"""SELECT PropertyValue FROM AccountProperty
				WHERE 
				PropertyName = 'County' 
				AND AccountId = %s""" % accountId)
		else:
			self._database_connection.execute(u"""SELECT Name FROM County
				WHERE 
				CountyCode = '%s'
				AND CountryCode = '%s'""" % (county_code, country_code))

	
		state = self._database_connection.fetchone()
		return '' if state is None else state[0]

	@cached
	def _get_service_property(self, service_id, property_name):
		self._database_connection.execute(u"""SELECT PropertyValue FROM ServiceProperty
				WHERE 
					ServiceId = %s
					AND PropertyName = '%s'""" % (service_id, property_name))

		property_value = self._database_connection.fetchone()
		return '' if property_value is None else property_value[0]

	# If domain status is active then return True else return False
	@cached
	def _get_domain_status(self, domain_id):
		self._database_connection.execute(u"""SELECT PropertyValue FROM OnlineServiceProperty
				WHERE 
					OnlineServiceId = %s
					AND PropertyName = 'Status'""" % domain_id)

		property_value = self._database_connection.fetchone()
		return False if property_value is None else property_value[0] == "1"

	@cached
	def _is_parked_domain(self, domain_id):
		self._database_connection.execute(u"""SELECT PropertyValue FROM OnlineServiceProperty
				WHERE 
					OnlineServiceId = %s
					AND PropertyName = 'Status'""" % domain_id)

		property_value = self._database_connection.fetchone()
		return False if property_value is None else property_value[0] == "2"

	# If domain web status is active then return True else return False
	def _get_domain_web_status(self, domain_id, domains_info):
		for website_status in domains_info.findall('.//domain[@id="%s"]/WebsiteStatus' % domain_id):
			if website_status.attrib['value'] == "0":
				return False

		return True

	def _add_domain_preferences_node(self, domain_node, domain_id):
		self.logger.debug(messages.FETCH_DOMAIN_PREFERENCES % domain_id)
		preferences_node = xml.elem('preferences')

		self._database_connection.execute(u"SELECT Name FROM DomainAlias where DomainId = %s" % domain_id)

		for alias_name in self._database_connection.fetchall():
			alias_attributes = {'name': alias_name[0], 'web': 'true', 'mail': 'false', 'seo-redirect': 'false'}
			preferences_node.append(xml.elem('domain-alias', [self._create_status_element(0)], alias_attributes))

		domain_node.append(preferences_node)

	def _add_domain_properties_node(self, domain_node, domain_id, domains_info):
		self.logger.debug(messages.FETCH_DOMAIN_PROPERTIES % domain_id)
		properties_children_nodes = []

		ip_node = self._create_ip_node(self._get_domain_ip(domain_id))
		if ip_node is not None:
			properties_children_nodes.append(ip_node)
		domain_status = self._get_domain_status(domain_id)
		domain_web_status = self._get_domain_web_status(domain_id, domains_info)

		self._add_domain_status_node(properties_children_nodes, domain_status, domain_web_status)

		if len(properties_children_nodes) > 0:
			domain_node.append(xml.elem('properties', properties_children_nodes))

	@classmethod
	def _add_domain_status_node(cls, properties_children_nodes, domain_status, domain_web_status):
		# In helm user can stop domain and suspend web each independently.
		# If domain stopped or suspend web then it is suspended in PPA.
		properties_children_nodes.append(cls._create_status_element(domain_status or domain_web_status))

		# If web stopped, need to add domain parameter 'turnOffAction'.
		if domain_web_status:
			dom_param = xml.elem('dom-param', [
						xml.elem('param', [
							xml.text_elem('name', 'turnOffAction'),
							xml.text_elem('value', 'suspend'.encode('base-64'))
						])
				])
			# if suspend only web, need to add domain parameter 'selfTurnOffAction'.
			if not domain_status:
				dom_param.append(
						xml.elem('param', [
							xml.text_elem('name', 'selfTurnOffAction'),
							xml.text_elem('value', 'suspend'.encode('base-64'))
						])
					)

			properties_children_nodes.append(dom_param)

	def _create_ip_node(self, ip):
		if ip is None:
			return None

		self._database_connection.execute(u"""SELECT StaticIpAddress FROM IPAddress WHERE IpAddress = '%s'""" % ip)
		is_exclusive = self._database_connection.fetchone()
		type = 'shared'
		if is_exclusive is not None and is_exclusive[0] == True:
			type = 'exclusive'

		return xml.elem('ip', [
				xml.text_elem('ip-type', type),
				xml.text_elem('ip-address', ip)
			])

	def _create_shosting_node(self, domain_id, domains_info):
		forwarding_node = self._create_forwarding_node(domain_id, domains_info)
		if forwarding_node is None:
			return None

		return xml.elem('shosting', [forwarding_node])

	def _create_phosting_node_for_domain(self, domain_id, domain_name, domains_info):
		phosting_node = xml.elem('phosting', [], {'www-root': 'httpdocs', 'cgi_bin_mode': 'www-root'})

		self._add_hosting_preferences_node(phosting_node, domain_id)

		ip_node = self._create_ip_node(self._get_domain_ip(domain_id))
		if ip_node is not None:
			phosting_node.append(xml.elem('properties', [ip_node]))

		self._add_scripting_node(phosting_node, domain_id, domains_info)

		self._add_mime_types_node(phosting_node, domain_id, domains_info)

		self._add_ftp_users_node(phosting_node, domain_id, domains_info)

		self._add_sites_node(phosting_node, domain_id, domains_info)

		return phosting_node

	def _create_phosting_node_for_site(self, domain_id, domain_name):
		phosting_node = xml.elem('phosting', [], {'www-root': domain_name, 'cgi_bin_mode': 'www-root'})

		self._add_hosting_preferences_node(phosting_node, domain_id)

		ip_node = self._create_ip_node(self._get_domain_ip(domain_id))
		if ip_node is not None:
			phosting_node.append(xml.elem('properties', [ip_node]))

		return phosting_node

	def _create_forwarding_node(self, domain_id, domains_info):
		for forwarding in domains_info.findall('.//domain[@id="%s"]/Forwarding' % domain_id):
			if forwarding.attrib['value'] != '':
				return xml.text_elem('url', forwarding.attrib['value'])

		return None

	@cached
	def _is_web_hosting(self, domain_id):
		self._database_connection.execute(u"""SELECT 1 FROM Service
				JOIN Server on Server.ServerId = Service.ServerId
				JOIN OnlineService_Service on OnlineService_Service.ServiceId = Service.ServiceId
				WHERE 
					ProviderGuid IN ('%s', '%s')
					AND OnlineService_Service.OnlineServiceId = %s"""
			% (self.iis6_guid, self.iis7_guid, domain_id))

		if self._database_connection.fetchone():
			return True

		return False

	def _add_scripting_node(self, phosting_node, domain_id, domains_info):
		scripting_attributes = {}
		supported_scripting = dict({'PHP' : 'php', 'Python': 'python', 'Perl': 'perl', 'ASP': 'asp', 'AspNetVersion' : 'asp_dot_net'})
		for scripting in domains_info.findall('.//domain[@id="%s"]/LimitsAndPermissions//scripting' % domain_id):
			if scripting.attrib['name'] in supported_scripting:
				scripting_name = supported_scripting[scripting.attrib['name']]
				scripting_attributes[scripting_name] = 'false' if scripting.attrib['value'] == 'False' or scripting.attrib['value'] == '0' else 'true'

		for coldfusion in domains_info.findall('.//domain[@id="%s"]/Coldfusion' % domain_id):
			scripting_attributes['coldfusion'] = 'true' if coldfusion.attrib['value'] == 'True' else 'false'

		scripting_node = xml.elem('limits-and-permissions',[
					xml.elem('scripting', [], scripting_attributes)
				])

		phosting_node.append(scripting_node)

	def _add_mime_types_node(self, phosting_node, domain_id, domains_info):
		mime_types = domains_info.findall('.//domain[@id="%s"]/MimeTypes//mimetype' % domain_id)
		if not mime_types:
			return None

		mime_types_node = et.SubElement(phosting_node, 'mime-types')
		for mime_type in mime_types:
			mime_types_node.append(xml.elem('mime-type', [], {'ext': mime_type.attrib['extension'], 'value': mime_type.attrib['mimetype']}))

	def _add_mailsystem_node(self, domain_node, domain_id, domains_info):
		mailsystem = []

		self._database_connection.execute(u""" SELECT Service.ServiceId FROM Service
				JOIN OnlineService_Service on OnlineService_Service.ServiceId = Service.ServiceId
				WHERE 
					ProviderGuid = '%s'
					AND OnlineService_Service.OnlineServiceId = %s""" %  (self.smart_guid, domain_id))

		service_id = self._database_connection.fetchone()
		if service_id is None:
			return None

		self._add_mailsystem_properties_node(mailsystem, service_id[0], domain_id)
		self._add_mailsystem_mailusers_node(mailsystem, domain_id, domains_info)
		self._add_mailsystem_preferences_node(mailsystem, domain_id, domains_info)

		if len(mailsystem) > 0:
			domain_node.append(xml.elem('mailsystem', mailsystem))

	def _add_mailsystem_properties_node(self, mailsystem_node, service_id, domain_id):
		mailsystem_properties_node = xml.elem('properties')

		domain_status = self._get_domain_status(domain_id)
		mailsystem_properties_node.append(self._create_status_element(domain_status))

		ip_node = self._create_ip_node(self._get_service_property(service_id, 'IpAddress'))
		if ip_node is not None:
			mailsystem_properties_node.append(ip_node)

		mailsystem_node.append(mailsystem_properties_node)

	def _create_mailuser_by_pop3_account(self, mail_account):
		mailbox_quota = str(int(mail_account.attrib['MaxSize']) * 1024 * 1024)
		mailuser_node = xml.elem('mailuser', [], {'name': mail_account.attrib['UserName'].split('@')[0], 'forwarding-enabled': 'false' if mail_account.attrib['ForwardingAddress'] == "" else 'true', 'mailbox-quota': "-1" if mailbox_quota == "0" else mailbox_quota})

		self._add_mailuser_properties_node(mail_account, mailuser_node)
		self._add_mailuser_preferences_node(mail_account, mailuser_node)

		return mailuser_node

	@staticmethod
	def _add_mailuser_properties_node(mail_account, mailuser_node):
		mailuser_properties_node = xml.elem('properties', [xml.text_elem('password', mail_account.attrib['Password'], {'type': 'plain'})])
		mailuser_node.append(mailuser_properties_node)

	@staticmethod
	def _add_mailuser_preferences_node(mail_account, mailuser_node):
		mailuser_preferences_node = xml.elem('preferences')
		if mail_account.attrib['DeleteMsgOnForward'] == "True" and mail_account.attrib['ForwardingAddress'] != "" :
			mailuser_preferences_node.append(xml.elem('mailbox', [], {'enabled': 'false'}))
		else:
			mailuser_preferences_node.append(xml.elem('mailbox', [], {'enabled': 'true', 'type': 'mbox'}))

		if mail_account.attrib['ForwardingAddress'] != "":
			mailuser_preferences_node.append(xml.text_elem('forwarding', mail_account.attrib['ForwardingAddress']))
		if mail_account.attrib['EnableAutoResponder'] == "True":
			autoresponders =  et.SubElement(mailuser_preferences_node, 'autoresponders')
			autoresponders.append(xml.elem('autoresponder', [xml.text_elem('text', mail_account.attrib['OutOfOfficeMessage'].encode('base-64'))], {'subject': mail_account.attrib['AutoResponderSubject'].encode('base-64')}))

		mailuser_node.append(mailuser_preferences_node)


	@staticmethod
	def _create_mailuser_by_alias(mail_alias):
		mailuser_node_attributes = {'name': mail_alias.attrib['Name'], 'forwarding-enabled': 'true'}
		mailuser_node = xml.elem('mailuser', [], mailuser_node_attributes)
		mailuser_properties_node = xml.elem('properties', [xml.text_elem('password', generate_random_password(), {'type': 'plain'})])
		
		mailuser_preferences_node = xml.elem('preferences')
		mailuser_preferences_node.append(xml.elem('mailbox', [], {'enabled': 'false'}))
		for address in mail_alias.findall('.//Address'):
			mailuser_preferences_node.append(xml.text_elem('forwarding', address.attrib['Value']))
		
		mailuser_node.append(mailuser_properties_node)
		mailuser_node.append(mailuser_preferences_node)

		return mailuser_node

	def _add_mailsystem_mailusers_node(self, mailsystem_node, domain_id, domains_info):
		mailusers = []

		for mail_account in domains_info.findall('.//domain[@id="%s"]/Mailsystem//Account' % domain_id):
			mailuser_node = self._create_mailuser_by_pop3_account(mail_account)

			mailusers.append(mailuser_node)

		for mail_alias in domains_info.findall('.//domain[@id="%s"]/MailAliases//Alias' % domain_id):
			mailuser_node = self._create_mailuser_by_alias(mail_alias)

			mailusers.append(mailuser_node)

		if len(mailusers) > 0:
			mailsystem_node.append(xml.elem('mailusers', mailusers))

	def _add_mailsystem_preferences_node(self, mailsystem_node, domain_id, domains_info):
		mailsystem_preferences = []
	
		mail_aliases = domains_info.findall('.//domain[@id="%s"]/MailAliases//Alias' % domain_id)
		if mail_aliases:
			domain_name = self._get_domain_name(domain_id)
			for mail_alias in mail_aliases:
				if mail_alias.attrib['IsCatchAll'] == 'True':
					mailsystem_preferences.append(xml.text_elem('catch-all', self._strings_concat([mail_alias.attrib['Name'], domain_name], '@')))

		if len(mailsystem_preferences) > 0:
			mailsystem_node.append(xml.elem('preferences', mailsystem_preferences))

	def _add_databases_node(self, domain_node, domain_id, domains_info):
		databases_node = et.SubElement(domain_node, 'databases')

		for database_info in domains_info.findall('.//domain[@id="%s"]/DatabaseInfo' % domain_id):
			server_ip = database_info.attrib['ServerIp']
			service_id = database_info.attrib['ServiceId']
			guid = database_info.attrib['Guid']
			server_instance_name = self._get_service_property(service_id, 'ServerInstanceName')
			db_port = self._get_service_property(service_id, 'ServerPort')

			databases = database_info.findall('.//Database')
			users = database_info.findall('.//User')
			for database in databases:
				database_name = database.attrib['name']
				database_node = et.SubElement(databases_node, 'database', {'name': database_name, 'type': 'mysql' if str(guid) in [self.mysql4_guid, self.mysql5_guid] else 'mssql'})

				database_node.append(xml.elem('db-server', [
					xml.text_elem('host', self._strings_concat([server_ip, server_instance_name], '\\')),
					xml.text_elem('port', db_port)
					], {'type': 'mysql' if str(guid) in [self.mysql4_guid, self.mysql5_guid] else 'mssql'}))

				for user in users:
					if user.attrib['db'] == database_name:
						database_node.append(xml.elem('dbuser', [
								xml.text_elem('password', user.attrib['password'], {'type': 'plain'}),
								], {'name': user.attrib['name']}))
	
	def _add_sites_node(self, phosting_node, domain_id, domains_info):
		sites = []

		self._database_connection.execute(u"SELECT Name FROM SubDomain WHERE DomainId = %s" % domain_id)

		sites_data = self._database_connection.fetchall()
		for site_name in sites_data:
			domain_name = self._get_domain_name(domain_id)
			site_node = xml.elem('site', [], {'guid': str(uuid.uuid1()), 'name': self._strings_concat([site_name[0], domain_name], '.'), 'parent-domain-name': domain_name})
			et.SubElement(site_node, 'preferences')

			self._add_domain_properties_node(site_node, domain_id, domains_info)

			phosting = self._create_phosting_node_for_site(domain_id, self._strings_concat([site_name[0], domain_name], '.'))
			site_node.append(phosting)

			sites.append(site_node)

		if len(sites) > 0:
			phosting_node.append(xml.elem('sites', sites))

	@cached
	def _get_domain_name(self, domain_id):
		self._database_connection.execute(u"SELECT Name FROM OnlineService WHERE OnlineServiceId = %s" % domain_id)
		domain_name = self._database_connection.fetchone()
		return None if domain_name is None else domain_name[0]

	@cached
	def _get_account_id_by_domain(self, domain_id):
		self._database_connection.execute(u"SELECT AccountId FROM OnlineService WHERE OnlineServiceId = %s" % domain_id)
		account_id = self._database_connection.fetchone()
		return None if account_id is None else account_id[0]

	@cached
	def _get_account_name_by_domain(self, domain_id):
		self._database_connection.execute(u"""SELECT AccountName FROM Account
					JOIN OnlineService on OnlineService.AccountId = Account.AccountId
					WHERE OnlineServiceId = %s""" % domain_id)
		account_name = self._database_connection.fetchone()
		return None if account_name is None else account_name[0]

	@cached
	def _get_domain_ip(self, domain_id):
		self._database_connection.execute(u"""SELECT Server.IpAddress, OnlineService_Service.IpAddress  FROM Server
					JOIN Service on Server.ServerId = Service.ServerId
					JOIN OnlineService_Service on OnlineService_Service.ServiceId = Service.ServiceId
					WHERE 
						ProviderGuid IN ('%s', '%s')
						AND OnlineService_Service.OnlineServiceId = %s"""
				% (self.iis6_guid, self.iis7_guid, domain_id))

		if self._database_connection.rowcount() == 0:
			return None

		(shared_ip, dedicated_ip) = self._database_connection.fetchone()
		if dedicated_ip is None:
			return shared_ip

		return dedicated_ip

	def _add_hosting_preferences_node(self, phosting_node, domain_id):
		preferences_node = et.SubElement(phosting_node, 'preferences')
		sysuser_name = self._unique_sysuser_names(self._get_account_name_by_domain(domain_id) + "_web")
		preferences_node.append(xml.elem('sysuser', [xml.text_elem('password', generate_random_password(), {'type': 'plain'})], {'name': sysuser_name}))


	def _add_ftp_users_node(self, preferences_node, domain_id, domains_info):
		ftp_accounts = domains_info.findall('.//domain[@id="%s"]/FTPAccounts/FTPAccount' % domain_id)
		if not ftp_accounts:
			return None

		ftp_users_node = et.SubElement(preferences_node, 'ftpusers')
		for ftp_account in ftp_accounts:
			ftp_user_name = self._unique_ftpuser_names(ftp_account.attrib['name'])
			ftp_user = xml.elem('ftpuser', [
					xml.elem('sysuser',[
						xml.text_elem('password', ftp_account.attrib['password'], {'type': 'plain'})
					], {'name': ftp_user_name, 'home': ftp_account.attrib['path']})
				],
				{'name': ftp_account.attrib['name']}
			)
			if (ftp_account.attrib['canread'] == 'True'):
				ftp_user.append(xml.text_elem('permission', 'download', {}))
			if (ftp_account.attrib['canwrite'] == 'True'):
				ftp_user.append(xml.text_elem('permission', 'upload', {}))
			ftp_users_node.append(ftp_user)

	def _decrypt_password(self, password):
		try:
			return self._helm_server_runner.sh(
				ur'{migration_tool_path} --decrypt-password {password}',
				dict(migration_tool_path=self._migration_tool_path, password=password)
			)
		except Exception as e:
			self.logger.debug(u'Exception:', exc_info=e)
			self.logger.warning(messages.CANNOT_DECRYPT_PASSWORD)
			return generate_random_password()
