from parallels.core.hosting_repository.mail_account import MailAccountModel, MailAccountEntity
from parallels.plesk.hosting_repository.base import PleskBaseModel
from parallels.plesk.hosting_repository.utils.cli.mail import MailAccountCreateCli, MailAccountSetForwardingCli, \
    MailAccountsSetAlisesCli, MailAccountEnableMailboxCli


class PleskMailAccountModel(MailAccountModel, PleskBaseModel):
    def create_from_dump(self, subscription_name, mail_account_dump):
        """Create mail account in target Plesk

        :type subscription_name: str | unicode
        :type mail_account_dump: parallels.core.dump.entity.mail_account.MailAccount
        """
        command = MailAccountCreateCli(
            runner=self.plesk_cli_runner,
            mail_account_name=mail_account_dump.name,
            mail_user_password=mail_account_dump.password.password_text,
            mail_user_password_type=mail_account_dump.password.password_type,
            is_enable_cp_access=mail_account_dump.is_enable_cp_access,
            is_enable_mailbox=mail_account_dump.is_enable_mailbox
        )
        command.run()

    def create(self, mail_account, subscription_name):
        """Create mail account in target panel

        :type subscription_name: str | unicode
        :type mail_account: parallels.core.hosting_repository.mail_account.CreateMailAccountEntity
        """
        command = MailAccountCreateCli(
            runner=self.plesk_cli_runner,
            mail_account_name=mail_account.name,
            mail_user_password=mail_account.password,
            mail_user_password_type='plain',
            is_enable_cp_access=True,
            is_enable_mailbox=True
        )
        command.run()

    def enable_mailbox(self, subscription_name, mail_user_name, domain_name, password):
        """Enable mailbox for specified mail account

        :type subscription_name: str | unicode
        :type mail_user_name: str | unicode
        :type domain_name: str | unicode
        :type password: str | unicode
        """
        command = MailAccountEnableMailboxCli(
            runner=self.plesk_cli_runner,
            mail_account_name='%s@%s' % (mail_user_name, domain_name),
            mail_user_password=password,
            mail_user_password_type='plain',
        )
        command.run()

    def set_forwarding(self, subscription_name, mail_account_name, is_enable_forwarding, forwarding_addresses):
        """Set forwarding for given mail user in target Plesk

        :type subscription_name: str | unicode
        :type mail_account_name: str | unicode
        :type is_enable_forwarding: bool
        :type forwarding_addresses: list[str]
        """
        command = MailAccountSetForwardingCli(
            self.plesk_cli_runner,
            mail_account_name,
            is_enable_forwarding,
            forwarding_addresses
        )
        command.run()

    def set_aliases(self, subscription_name, mail_account_name, aliases):
        """Set aliases for given mail account in target Plesk

        :type subscription_name: str | unicode
        :type mail_account_name: str | unicode
        :type aliases: list[str]
        """
        command = MailAccountsSetAlisesCli(self.plesk_cli_runner, mail_account_name, aliases)
        command.run()

    def is_exists_by_subscription(self, subscription_name, mail_user_name, domain_name):
        """Check if mail user with given name exists in target Plesk for given domain

        :type subscription_name: str
        :type mail_user_name: str
        :type domain_name: str
        :rtype: bool
        """
        query = """
            SELECT mail.id
            FROM mail join domains on mail.dom_id = domains.id
            WHERE mail.mail_name = %(mail_user_name)s AND domains.name = %(domain_name)s
        """
        result = self.plesk_server.query_panel_db(query, dict(
            mail_user_name=mail_user_name,
            domain_name=domain_name.encode('idna')
        ))
        return len(result) > 0

    def is_mailbox_enabled(self, subscription_name, mail_user_name, domain_name):
        """Check if mail account with given name exists in target panel and mailbox is enabled for it

        Returns:
        - True, if mail account exists and mailbox is enabled
        - False, if mail account exists and mailbox is disabled
        - None, if mail account does not exists

        :type subscription_name: str | unicode
        :type mail_user_name: str | unicode
        :type domain_name: str | unicode
        :rtype: bool | None
        """
        query = """
            SELECT mail.postbox
            FROM mail join domains on mail.dom_id = domains.id
            WHERE mail.mail_name = %(mail_user_name)s AND domains.name = %(domain_name)s
        """
        result = self.plesk_server.query_panel_db(query, dict(
            mail_user_name=mail_user_name,
            domain_name=domain_name.encode('idna')
        ))
        if len(result) == 0:
            return None
        else:
            return result[0].get('postbox') == 'true'

    def get_password(self, subscription_name, mail_user_name, domain_name):
        """Get password of mail account with given name

        Returns tuple (password, password type).

        :type subscription_name: str | unicode
        :type mail_user_name: str | unicode
        :type domain_name: str | unicode
        :rtype: tuple
        """
        query = """
            SELECT accounts.password, accounts.type
            FROM mail
            JOIN domains on mail.dom_id = domains.id
            JOIN accounts ON accounts.id = mail.account_id
            WHERE mail.mail_name = %(mail_user_name)s AND domains.name = %(domain_name)s
        """
        result = self.plesk_server.query_panel_db(query, dict(
            mail_user_name=mail_user_name,
            domain_name=domain_name.encode('idna')
        ))
        if len(result) > 0:
            row = result[0]
            return row['password'], row['type']
        else:
            return None

    def get_mailbox_effective_quota(self, subscription_name, mail_user_name, domain_name):
        """Get effective quota (according to mailbox limit and subscription limit) of mail account with given name

        Returns quota as string value.

        :type subscription_name: str | unicode
        :type mail_user_name: str | unicode
        :type domain_name: str | unicode
        :rtype: str
        """
        mailbox_limit_query = """
            SELECT mail.mbox_quota
            FROM mail 
            JOIN domains on mail.dom_id = domains.id
            WHERE mail.mail_name = %(mail_user_name)s AND domains.name = %(domain_name)s
        """
        mailbox_limit_result = self.plesk_server.query_panel_db(mailbox_limit_query, dict(
            mail_user_name=mail_user_name,
            domain_name=domain_name.encode('idna')
        ))
        if len(mailbox_limit_result) > 0:
            row = mailbox_limit_result[0]
            mailbox_quota = str(row['mbox_quota'])
        else:
            mailbox_quota = None

        if mailbox_quota is None or str(mailbox_quota) == '-1':
            subscription_limit_query = """
                SELECT Limits.value AS limit_value
                FROM Subscriptions 
                JOIN domains ON domains.id = Subscriptions.object_id AND Subscriptions.object_type = 'domain' 
                JOIN Limits ON Limits.id = Subscriptions.id 
                WHERE domains.name = %(subscription_name)s AND Limits.limit_name = 'mbox_quota'
            """

            subscription_limit_result = self.plesk_server.query_panel_db(subscription_limit_query, dict(
                subscription_name=subscription_name
            ))
            if len(subscription_limit_result) > 0:
                subscription_limit_row = subscription_limit_result[0]
                mailbox_quota = str(subscription_limit_row['limit_value'])

        return mailbox_quota

    def get_list_by_domain(self, subscription_name, domain_name):
        """Retrieve list of mail accounts in panel for given domain

        :type subscription_name: str | unicode
        :type domain_name: str | unicode
        :rtype: list[parallels.core.hosting_repository.mail_account.MailAccountEntity]
        """
        query = """
           SELECT mail.mail_name
           FROM mail JOIN domains ON mail.dom_id = domains.id
           WHERE domains.name = %(domain_name)s
        """
        result = self.plesk_server.query_panel_db(query, dict(
            domain_name=domain_name.encode('idna')
        ))
        mail_accounts = []
        for row in result:
            mail_accounts.append(MailAccountEntity(short_name=row['mail_name']))
        return mail_accounts
