import logging
from parallels.expand_migrator.expand_data.model import Server, AssignedPleskClient, ExpandPlan, ExpandReseller, \
	DNSServerMapping, AssignedPleskDomain
from parallels.common.plesk_backup.data_model import Password
from parallels.utils import group_by_id


logger = logging.getLogger(__name__)


class ExpandDatabaseData(object):
	"""Retrieve data from Expand by using direct SQL queries to Expand database"""

	def __init__(self, expand_main_server):
		self._expand_main_server = expand_main_server

	def get_servers(self, source_plesks):
		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, source_id) for source_id, srv in source_plesks.iteritems())
		model_servers = []
		for s in servers:
			if s['ip_address'] not in plesk_servers_by_ip:
				logger.debug(
					u"Expand server #%s is skipped as its IP address '%s' "
					u"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_dns_server_ips(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_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_assigned_domains(self):
		domains_sql = u"""
			SELECT
				object_id AS domain_id, person_id AS reseller_id
			FROM exp_person_object_right
			WHERE object_type = 'plesk_domain'
			UNION ALL
				SELECT pd.id, ep.person_id
					FROM plesk_domain pd
					JOIN exp_person_object_right ep ON pd.server_id = ep.object_id
					WHERE ep.object_type = 'plesk_server'
			UNION ALL
				SELECT pd.id, ep.person_id
					FROM exp_person_object_right ep
					JOIN plesk_group_repository pg ON pg.group_id = ep.object_id
					JOIN plesk_domain pd ON pd.server_id = pg.server_id
				WHERE ep.object_type = 'group_nodes'
		"""

		assigned_domains = self._fetch_db(domains_sql)

		return [
			AssignedPleskDomain(
				domain_id=int(c['domain_id']),
				reseller_id=int(c['reseller_id']),
			)
			for c in assigned_domains
		]

	def get_expand_plans(self, expand_resellers):
		expand_resellers_by_id = group_by_id(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_expand_resellers(self):
		logger.debug(u"Get Expand resellers")
		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(
				ExpandReseller(
					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=reseller['enabled'] == 'true',
					limits=dict([]),
					permissions=dict([]),
					plan_id=None,
				)
			)
		return exp_resellers_list

	def get_dns_server_mapping(self, source_plesks):
		"""Get information about Plesk server to centralized DNS server mapping"""

		plesk_servers_by_ip = dict((srv.ip, source_id) for source_id, srv in source_plesks.iteritems())
		sql = u"""
			SELECT
				ps.ip_address AS plesk_server_ip_address,
				INET_NTOA(ds.ip) AS dns_server_ip_address,
				dsr.dns_mode as dns_mode
			FROM plesk_server ps
			JOIN dns_server_repository dsr ON dsr.server_id = ps.id
			JOIN dns_slave_server_repository dssr ON dssr.dns_id = dsr.id
			JOIN dns_server ds ON ds.id = dssr.slave_dns_id
			UNION
				SELECT
					ps.ip_address AS plesk_server_ip_address,
					INET_NTOA(ds.ip) AS dns_server_ip_address,
					dsr.dns_mode as dns_mode
				FROM plesk_server ps
				JOIN dns_server_repository dsr ON dsr.server_id = ps.id
				JOIN dns_server ds ON ds.id = dsr.id"""

		dns_mappings = []

		for row in self._fetch_db(sql):
			plesk_ip = row['plesk_server_ip_address']
			if plesk_ip not in plesk_servers_by_ip:
				continue

			dns_mappings.append(DNSServerMapping(
				plesk_id=plesk_servers_by_ip[plesk_ip],
				dns_server_ip=row['dns_server_ip_address'],
				mode=row['dns_mode']
			))

		return dns_mappings

	def _fetch_db(self, sql):
		logger.debug(u"Fetch data from Expand database, query: %s", sql)
		with self._expand_main_server.runner() as runner:
			stdout = 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