from parallels.core.logging import get_logger
from parallels.core.runners.base import BaseRunner
from parallels.plesk.source.expand import messages
from parallels.plesk.source.expand.expand_data.model import Server, AssignedPleskClient, ExpandPlan, ExpandReseller, \
    DNSServerMapping, AssignedPleskDomain
from parallels.core.dump.entity.password import Password
from parallels.core.utils.common import group_by_id


logger = get_logger(__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_servers):
        logger.debug(messages.LOG_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_servers.items())
        model_servers = []
        for s in servers:
            if s['ip_address'] not in plesk_servers_by_ip:
                logger.debug(
                    messages.EXPAND_SERVER_IS_SKIPPED_BECAUSE_NOT_IN_CONFIG,
                    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(messages.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(messages.LOG_GET_EXPAND_RESELLERS)
        logger.info(messages.FETCH_INFORMATION_ABOUT_EXPAND_RESELLERS)

        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_servers):
        """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_servers.items())
        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(messages.FETCH_DATA_FROM_EXPAND_DATABASE_BY_SQL_QUERY, sql)
        with self._expand_main_server.runner() as runner:
            assert isinstance(runner, BaseRunner)
            stdout = runner.execute_command(
                # '--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)
            ).stdout

        result = list()
        column_names = None
        for line in stdout.split('\n'):
            line = line.decode('utf-8', errors='replace')
            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
