from xml.etree import ElementTree

from parallels.core import get_logger
from parallels.core.hosting_repository.panel import PanelModel, DNSTemplateRecord, DNSTemplate
from parallels.core.registry import Registry
from parallels.core.utils import plesk_utils
from parallels.core.utils.common import cached, is_empty
from parallels.core.utils.plesk_utils import get_packages_xml
from parallels.plesk import messages
from parallels.plesk.hosting_repository.base import PleskBaseModel

logger = get_logger(__name__)


class PleskPanelModel(PanelModel, PleskBaseModel):
    def refresh_components(self):
        """Initiate update of cached data about installed target Plesk components; after that target Plesk should
        have an up-to-date info about installed components and ready to perform any capability checking
        """
        plesk_server = Registry.get_instance().get_context().conn.target.plesk_server
        if not plesk_server.is_windows():
            # components data is always up-to-date on unix
            return

        plesk_utils.refresh_node_components(plesk_server)

    def is_service_plan_addon_available(self):
        """Return True in target Plesk is able to manipulate with service plan add-ons

        :rtype: bool
        """
        return True

    @cached
    def get_administrator_guid(self, subscription_name):
        """Get GUID of administrator account for server with specified subscription

        :type subscription_name: str | unicode
        :rtype: str | unicode | None
        """
        query = "SELECT guid FROM clients WHERE type = 'admin' AND login = 'admin'"
        rows = self.plesk_server.query_panel_db(query)
        if len(rows) > 0:
            return rows[0]['guid']
        else:
            return None

    @cached
    def get_administrator_id(self, subscription_name):
        """Get ID of administrator account for server with specified subscription

        :type subscription_name: str | unicode
        :rtype: int
        """
        # fallback to "1" in case if failed to find admin user in database,
        # which gives at least some chance for migration to work
        default_admin_id = 1

        query = "SELECT id FROM clients WHERE type = 'admin' AND login = 'admin'"
        rows = self.plesk_server.query_panel_db(query)
        if len(rows) > 0:
            admin_id = rows[0]['id']
            try:
                admin_id = int(admin_id)
            except ValueError:
                admin_id = default_admin_id
        else:
            admin_id = default_admin_id

        return admin_id

    def get_installed_components(self, subscription_name):
        """Get list of installed components with their versions on the server with specified subscription

        :type subscription_name: str | unicode
        :rtype: dict[str | unicode, str | unicode]
        """
        # SQL queries are taken from "getSoftwarePackages" at parallels/plesk/source/plesk/extras/pmm_17_0/DAL.pm
        if self.plesk_server.plesk_version >= (11, 1, 13):
            query = """
              SELECT
                e.name AS name, e.value AS version
              FROM ServiceNodeEnvironment AS e
              INNER JOIN ServiceNodes AS n
                ON e.serviceNodeId = n.id
              WHERE
                n.ipAddress = 'local' AND
                e.section = 'componentsPackages' AND
                e.value IS NOT NULL AND
                e.value <> ''
            """
        elif self.plesk_server.plesk_version >= (10, 4, 0):
            query = """
              SELECT
                SUBSTRING(p.name, 17) AS name, p.value AS version
              FROM ServiceNodeProperties AS p
              INNER JOIN ServiceNodes AS n
                ON p.serviceNodeId = n.id
              WHERE
                n.name = 'local' AND
                p.name LIKE 'server.packages.%'
            """
        else:
            query = "SELECT name, version FROM Components WHERE version <> 'not_installed'"

        rows = self.plesk_server.query_panel_db(query)
        return {row['name']: row['version'] for row in rows}

    def get_dns_template(self, subscription_name):
        """Get server-wide DNS template of the panel

        :type subscription_name: str | unicode
        :rtype: parallels.core.hosting_repository.panel.DNSTemplate
        """
        return self._get_server_dns_template()

    @cached
    def get_available_webmails(self):
        """Get list of available webmail application IDs

        :rtype: list[str | unicode] | None
        """
        try:
            webmails = []

            if self.plesk_server.is_windows():
                webmails.extend(self._get_built_in_webmails_windows())
            else:
                webmails.extend(self._get_built_in_webmails_linux())

            webmails.extend(self._get_external_webmails())

            return webmails
        except Exception as e:
            logger.exception()
            logger.fwarn(messages.FAILED_TO_LIST_WEBMAILS, reason=e)
            return None

    def _get_built_in_webmails_windows(self):
        """
        :rtype: list[str | unicode]
        """
        webmails = []

        packages_xml_str = get_packages_xml(self._plesk_server)
        packages_xml = ElementTree.fromstring(packages_xml_str)
        webmail_prefix = 'webmail.'

        for elem in packages_xml.findall('type'):
            type_name_full = elem.get('name', '')
            type_default = elem.get('default')

            if not type_name_full.startswith(webmail_prefix):
                continue

            type_name = type_name_full[len(webmail_prefix):]

            if is_empty(type_default) or type_name != type_default:
                continue

            for package in elem.findall('package'):
                if package.get('name') == type_name:
                    webmails.append(type_name)

        return webmails

    def _get_built_in_webmails_linux(self):
        """
        :rtype: list[str | unicode]
        """
        webmails = []

        query = "SELECT product_id FROM Webmails WHERE enabled = 'true'"
        rows = self.plesk_server.query_panel_db(query)
        for row in rows:
            webmails.append(row['product_id'])

        return webmails

    def _get_external_webmails(self):
        """
        :rtype: list[str | unicode]
        """
        webmails = []

        query = "SELECT name FROM externalWebmails WHERE enabled = 1"
        rows = self.plesk_server.query_panel_db(query)
        for row in rows:
            webmails.append(row['name'])

        return webmails

    @cached
    def _get_server_dns_template(self):
        """Get server-wide DNS template of the panel

        :rtype: parallels.core.hosting_repository.panel.DNSTemplate
        """
        query = "SELECT type, host, val, opt FROM dns_recs_t"

        rows = self.plesk_server.query_panel_db(query)
        dns_records = []

        for row in rows:
            dns_records.append(DNSTemplateRecord(
                rec_type=row['type'], src=row['host'], dst=row['val'], opt=row['opt']
            ))

        return DNSTemplate(dns_records)
