import logging
from .data_model import Model, CentralizedMailServer, Server, ExpandClient, \
PleskClient, ExpandResellers, AssignedPleskClient, PleskDomain, ExpandPlan, \
PleskDomainAlias
from parallels.expand_api.operator import PleskServerOperator, \
PleskMailServerOperator, PleskCentralizedDbServerOperator, ClientOperator, \
PleskClientOperator, PleskDomainOperator, DomainTemplateOperator, \
PleskDomainAliasOperator
from parallels.utils import if_not_none, group_by_id
from parallels.utils.ip import resolve
from parallels.common.plesk_backup.data_model import Password

class DataSource:
	logger = logging.getLogger(__name__)

	def __init__(self, api, expand_runner):
		self.api = api
		self.expand_plesk_servers = []
		self.expand_resellers = []
		self.expand_runner = expand_runner

	def get_model(self, source_plesks, source_centralized_mail_servers):
		self.logger.debug(u"Get Expand data model")
		self.expand_plesk_servers = self.get_servers(source_plesks)
		self.expand_resellers = self.get_expand_resellers()
		plesk_domains=self.get_plesk_domains()
		return Model(
			servers=self.expand_plesk_servers,
			centralized_mail_servers=self.get_centralized_mail_servers(source_centralized_mail_servers),
			expand_clients=self.get_expand_clients(),
			plesk_clients=list(self.iter_plesk_clients()),
			expand_resellers=self.expand_resellers,
			assigned_plesk_clients=self.get_assigned_clients(),
			plesk_domains=plesk_domains,
			plesk_domain_aliases=self.get_plesk_domain_aliases(plesk_domains),
			expand_plans=self.get_expand_plans()
		)

	def get_servers(self, source_plesks):
		self.logger.debug(u"Get servers")

		sql = u"SELECT id, ip_address, name FROM plesk_server"
		servers = self._fetch_db(sql)

		plesk_servers_by_ip = dict((srv.ip, id) for id, srv in source_plesks.iteritems())
		model_servers = []
		for s in servers:
			if s['ip_address'] not in plesk_servers_by_ip:
				self.logger.debug(u"Expand server #%s is skipped as its IP address '%s' is not among selected source Plesks IP addresses", s['id'], s['ip_address'])
				continue
			plesk_id = plesk_servers_by_ip[s['ip_address']]

			model_servers.append(Server(id=int(s['id']), name=s['name'], plesk_id=plesk_id))

		return model_servers

	def get_centralized_mail_servers(self, centralized_mail_servers):
		cmail_server_plesk_id_by_ip = dict((srv.ip, id) for id, srv in centralized_mail_servers.iteritems())

		cmail_servers = []
		for result in self.api.send(
				PleskMailServerOperator.Get(
					filter=PleskMailServerOperator.Get.FilterAll(),
				)
			):
			server_ip = result.data.ip
			if server_ip in cmail_server_plesk_id_by_ip:
				cmail_servers.append(
					CentralizedMailServer(
						id=result.data.id, ip=server_ip, 
						plesk_id=cmail_server_plesk_id_by_ip[server_ip],
						assigned_server_ids=result.data.assigned_server_ids
				))
			else:
				self.logger.debug(u"Expand centralized mail server #%s is skipped as its IP '%s' is not among selected centralized mail servers IP addresses" % (result.data.id, server_ip,))

		return cmail_servers

	def get_expand_clients(self):
		self.logger.debug(u"Get Expand clients")
		return [
			ExpandClient(
				id=result.data.id,
				info=result.data.info,
				personal_info=result.data.personal_info
			)
			for result in self.api.send(
				ClientOperator.Get(
					filter=ClientOperator.Get.FilterAll(),
					dataset=[ClientOperator.Get.Dataset.INFO, ClientOperator.Get.Dataset.PERSONAL_INFO]
				)
			)
		]

	def iter_plesk_clients(self):
		self.logger.debug(u"Get Plesk clients")
		plesk_client_id_to_expand_client_id = self._get_plesk_client_id_to_expand_client_id()
		for server in self.expand_plesk_servers:
			for plesk_client_info in self._get_plesk_clients_by_server_id(server.id):
				yield PleskClient(
					id=plesk_client_info.id,
					login=plesk_client_info.gen_info.login,
					pname=plesk_client_info.gen_info.pname,
					plesk_id=server.plesk_id,
					expand_client_id=plesk_client_id_to_expand_client_id.get(plesk_client_info.id)
				)

	def get_plesk_domains(self):
		self.logger.debug(u"Get Plesk domains")
		domain_templates = {}
		for result in self.api.send(
			DomainTemplateOperator.Get(
				filter=DomainTemplateOperator.Get.FilterAll(),
				dataset=[DomainTemplateOperator.Get.Dataset.GEN_SETUP]
			)
		):
			domain_templates[result.data.id] = result.data.name

		expand_plesk_servers_by_id = group_by_id(self.expand_plesk_servers, lambda s: s.id)

		return [
			PleskDomain(
				id=result.data.id,
				name=result.data.name,
				client_id=result.data.client_id,
				plesk_id=expand_plesk_servers_by_id[result.data.server_id].plesk_id,
				tmpl_id=result.data.tmpl_id,
				tmpl_name=if_not_none(result.data.tmpl_id, lambda tmpl_id: domain_templates[tmpl_id])
			)
			for result in self.api.send(
				PleskDomainOperator.Get(
					filter=PleskDomainOperator.Get.FilterAll(),
					dataset=[]
				)
			)
			if result.data.server_id in expand_plesk_servers_by_id
		]

	def get_plesk_domain_aliases(self, plesk_domains):
		self.logger.debug(u"Get Plesk aliases")

		domain_aliases = []
		for domain in plesk_domains:
			for result in self.api.send(
				PleskDomainAliasOperator.Get(
					filter=PleskDomainAliasOperator.Get.FilterByDomainId(domain_id=domain.id)
				)
			):
				domain_aliases.append(
					PleskDomainAlias(
						id=result.data.id,
						name=result.data.name
					)
				)
		return domain_aliases

	def get_dns_server_ips(self):
		self.logger.debug(u"Get IP addresses of centralized DNS servers")
		return {str(r['ip_address']) for r in self._fetch_db(u"SELECT INET_NTOA(ip) AS ip_address FROM dns_server")}

	def get_plesk_server_ips(self):
		self.logger.debug(u"Get IP addresses of Plesk servers")
		plesk_servers_result = self.api.send(
			PleskServerOperator.Get(
				filter=PleskServerOperator.Get.FilterAll(),
				dataset=[PleskServerOperator.Get.Dataset.IPPOOL]
			)
		)

		return {
			ip_info.ip_address 
			for result in plesk_servers_result
			for ip_info in result.data.ips 
		}

	def get_mail_server_ips(self):
		self.logger.debug(u"Get IP addresses of centralized mail servers")
		return {
			result.data.ip for result in self.api.send(
				PleskMailServerOperator.Get(
					filter=PleskServerOperator.Get.FilterAll(),
				)
			)
		}

	def get_db_server_ips(self):
		self.logger.debug(u"Get IP addresses of centralized database servers")

		plesk_server_ids = {result.data.id for result in self.api.send(
			PleskServerOperator.Get(
				filter=PleskServerOperator.Get.FilterAll(),
				dataset=[PleskServerOperator.Get.Dataset.IPPOOL] # need to specify something here to get the IDs
			)
		)}
		
		db_server_ips = set()
		db_hosts = {
			result.data.host for result in self.api.send(
				PleskCentralizedDbServerOperator.Get(
					filter=PleskCentralizedDbServerOperator.Get.FilterByPleskServerId(plesk_server_ids),
				)
			) if not result.data.is_local
		}

		for db_host in db_hosts:
			db_server_ip = resolve(db_host) # may be it is better to resolve hostname on Expand cluster, but it is much more complex to implement
			if db_server_ip is None:
				self.logger.debug(u"Unable to resolve database server hostname '%s'. It will be silently skipped." % (db_host))
			else:
				db_server_ips.add(db_server_ip)

		return db_server_ips

	def _fetch_db(self, sql):
		self.logger.debug(u"Fetch data from Expand database, query: %s", sql)
		stdout = self.expand_runner.sh(
			# '--batch' causes mysql to print field values tab-separated (instead of tabular form with '|')
			# '--raw' disables special characters (\t, \n, \\) escaping. TODO: enable escapings and parse them.
			u"""/usr/local/expand/sbin/expandmysql --execute {sql} --batch --raw""", dict(sql=sql)
		)

		result = list()
		column_names = None
		for line in stdout.split('\n'):
			if line == '':
				continue
			if column_names is None:
				column_names = line.split('\t')
			else:
				values = line.split('\t')
				result_row = dict(zip(column_names, values))
				result.append(result_row)
	
		return result
			

	def get_expand_resellers(self):
		self.logger.debug(u"Get Expand resellers")
		self.logger.info(u"Fetch information about Expand resellers from Expand database.")

		sql = u"""SELECT ep.* FROM exp_person ep JOIN exp_person_role epr ON epr.person_id = ep.id 
			JOIN exp_sec_role esr ON epr.role_id = esr.id WHERE esr.descr = 'Reseller'"""
		resellers = self._fetch_db(sql)
				
		exp_resellers_list = []
		for reseller in resellers:
			exp_resellers_list.append(
				ExpandResellers(
					id=int(reseller['id']),
					login=reseller['login'],
					name=reseller['name'] 
						if reseller['name'] != ''
						else reseller['login'],
					email=reseller['email'],
					password=Password('plain', reseller['password']),
					locale=reseller['ui_locale'],
					is_active=True 
						if reseller['enabled'] == 'true'
						else False,
					limits=dict([]),
					permissions=dict([]),
					plan_id=None,
				)
			)
		return exp_resellers_list

	def get_assigned_clients(self):
		"""Get assigned clients.
		   Objects: domains, clients, servers, server groups can be assigned to resellers, and an object can be assigned to several resellers.
		   Return list of clients assigned to resellers, directly or indirectly.
		"""
		clients_sql = u"""SELECT object_id AS client_id, person_id AS reseller_id, object_id AS assignee_id, object_type AS assignee_type
			             FROM exp_person_object_right WHERE object_type = 'plesk_client'
				 UNION ALL
				 SELECT pc.id, ep.person_id, ep.object_id, ep.object_type
				     FROM plesk_client pc JOIN exp_person_object_right ep ON pc.server_id = ep.object_id WHERE ep.object_type = 'plesk_server'
				 UNION ALL
				 SELECT pc.id, ep.person_id, ep.object_id, ep.object_type
				     FROM exp_person_object_right ep JOIN plesk_group_repository pg ON pg.group_id = ep.object_id JOIN plesk_client pc ON pc.server_id = pg.server_id
				     WHERE ep.object_type = 'group_nodes'"""
		assigned_clients = self._fetch_db(clients_sql)

		return [
			AssignedPleskClient(client_id=int(c['client_id']), reseller_id=int(c['reseller_id']), assignee_id=int(c['assignee_id']), assignee_type=c['assignee_type'])
			for c in assigned_clients
		]

	def get_expand_plans(self):
		expand_resellers_by_id = group_by_id(self.expand_resellers, lambda r: r.id)
		expand_plans = []
		sql = u"SELECT id, person_id, name FROM plesk_tmpl_domain"
		plans = self._fetch_db(sql)
		for plan in plans:
			reseller_id = int(plan['person_id'])
			if reseller_id not in expand_resellers_by_id:
				reseller_id = None

			expand_plans.append(ExpandPlan(id=int(plan['id']), reseller_id=reseller_id, name=plan['name']))
		return expand_plans

	def _get_plesk_client_id_to_expand_client_id(self):
		result = {}
		expand_client_ids = [client.id for client in self.get_expand_clients()]
		for expand_client_id, plesk_client_ids in self._get_plesk_client_ids_by_expand_client_ids(expand_client_ids):
			for plesk_client_id in plesk_client_ids:
				result[plesk_client_id] = expand_client_id
		return result

	def _get_plesk_client_ids_by_expand_client_ids(self, expand_client_ids):
		return [
			result.data for result in self.api.send(
				ClientOperator.InstanceGet(
					filter=ClientOperator.InstanceGet.FilterByClientId(ids=expand_client_ids)
				)
			)
		]

	def _get_plesk_clients_by_server_id(self, server_id):
		return [
			result.data for result in self.api.send(
				PleskClientOperator.Get(
					filter=PleskClientOperator.Get.FilterByServerId(server_id=server_id),
					dataset=[PleskClientOperator.Get.Dataset.GEN_INFO]
				)
			)
		]

