#!/usr/bin/python
import codecs
import collections
import getopt
import os
import pyodbc
import subprocess
import sys
import time

import colorama
import pymysql
import win32com.client
import xmltodict
import yaml
import json
import re

from plesk_migrator.sdk.utils import is_ascii_string, safe_get_dict_value, normalize_path, normalize_domain_name, \
    is_ipv4, is_ipv6, join_nonempty_strs, cached, safe_string_repr, safe_format, generate_password, list_priority_sequence
from netifaces import interfaces, ifaddresses, AF_INET

log_file = None

# Websitepanel resource groups https://mspcontrol.org/wiki/api/
WEBSITEPANEL_MAIL_RESOURCE_GROUP = 4


class Color(object):
    default = '\033[0m'
    white = '\033[97m'
    red = '\033[91m'
    yellow = '\033[93m'
    green = '\033[92m'
    cyan = '\033[96m'


def safe_idn_decode(domain_name):
    """Safely decode domain name from punycode to unicode

    If domain name is already in unicode - return it as is. If any error occured during decoding - return domain
    name as is.

    :type domain_name: str | unicode
    :rtype: unicode
    """
    if not is_ascii_string(domain_name):
        return domain_name

    try:
        return domain_name.decode('idna')
    except:
        log(
            u"Unable to convert domain name from punycode to unicode. Domain name: {domain_name}".format(
                domain_name=domain_name
            ), color=Color.yellow
        )
        return domain_name


def get_ftp_user_password(connection, ownerid):
    return query_exec(
        u"""SELECT ServiceItemProperties.PropertyValue
        FROM ServiceItemProperties
        WHERE ServiceItemProperties.ItemID = %s AND PropertyName = 'Password'""" % ownerid,
        connection
    )


def get_ftp_user_properties(connection, ftp_user_id):
    return query_exec(
        u"""SELECT PropertyName, PropertyValue
        FROM ServiceItemProperties
        WHERE ItemID = '%s'""" % ftp_user_id,
        connection
    )


def get_websitepanel_ftp_users(connection, package_id):
    return query_exec(
        u"""SELECT ServiceItems.ItemName, ServiceItems.ItemID
        FROM ServiceItems
        LEFT JOIN ServiceItemTypes ON ServiceItems.ItemTypeID = ServiceItemTypes.ItemTypeID
        WHERE ServiceItems.PackageID = {package_id}
            AND ServiceItemTypes.DisplayName like 'FTPAccount'""".format(package_id=package_id),
        connection
    )


@cached
def get_domain_web_ips(connection):
    query_result = query_exec(
        u"""SELECT DomainName, ExternalIP
        FROM Domains
        LEFT JOIN ServiceItemProperties ON
          ServiceItemProperties.ItemID = Domains.WebSiteID AND
          PropertyName = 'SiteIPAddressId'
        LEFT JOIN IPAddresses
          ON IPAddresses.AddressID = ServiceItemProperties.PropertyValue
        WHERE WebSiteID IS NOT NULL AND WebSiteID != 0""",
        connection
    )
    return {
        row['DomainName']: row['ExternalIP']
        for row in query_result
        if row['ExternalIP'] is not None and row['ExternalIP'].strip() != ''
    }


def get_ftp_users(data, owner_id, owner_name, subscription_name):
    data["level"] += 1
    ftp_users = []
    for wsp_ftp_user in get_websitepanel_ftp_users(data["connection"], owner_id):
        login = verify_ftp_user_login(data, wsp_ftp_user["ItemName"], subscription_name=subscription_name, owner_name=owner_name)
        log(u"ftp user %s" % login, data=data)
        ftp_user = dict(login=login)
        ftp_user_properties = get_ftp_user_properties(data["connection"], wsp_ftp_user["ItemID"])
        for ftp_user_property in ftp_user_properties:
            if ftp_user_property["PropertyName"] == "Password":
                ftp_user.update(filter_dict(
                    password=verify_password(
                        data, wsp_ftp_user["ItemName"],
                        decrypt(ftp_user_property["PropertyValue"], data["config"]["cryptokey"])
                    )
                ))
            elif ftp_user_property["PropertyName"] == "Folder":
                ftp_user.update(home_directory=ftp_user_property["PropertyValue"].lstrip("\\"))
            elif ftp_user_property["PropertyName"] == "CanRead":
                if ftp_user_property["PropertyValue"].lower() == "true":
                    ftp_user.update(allow_read=True)
                elif ftp_user_property["PropertyValue"].lower() == "false":
                    ftp_user.update(allow_read=False)
            elif ftp_user_property["PropertyName"] == "CanWrite":
                if ftp_user_property["PropertyValue"].lower() == "true":
                    ftp_user.update(allow_write=True)
                elif ftp_user_property["PropertyValue"].lower() == "false":
                    ftp_user.update(allow_write=False)
        ftp_users.append(ftp_user)
    data["level"] -= 1
    return ftp_users


def verify_ftp_user_login(data, login, subscription_name, owner_name):
    if len(login) > 15:
        data["level"] += 1
        newlogin = login[:15]
        log(
            u"Login of FTP user '{login}' does not conform to Plesk rules. It was changed to '{newlogin}'.".format(
                login=login,
                newlogin=newlogin
            ),
            data=data,
            color=Color.yellow
        )
        data['changed_ftp_logins'].append(login + "," + newlogin + "," + subscription_name + "," + owner_name + os.linesep)
        data["level"] -= 1
        return newlogin
    return login


def get_websitepanel_database_users(connection, package_id):
    return query_exec(
        u"""SELECT ServiceItems.ItemName, ServiceItems.ServiceID, ServiceItemTypes.DisplayName,
        ServiceItemProperties.PropertyValue
        FROM ServiceItems
        LEFT JOIN ServiceItemTypes ON ServiceItems.ItemTypeID = ServiceItemTypes.ItemTypeID
        LEFT JOIN ServiceItemProperties ON ServiceItems.ItemID = ServiceItemProperties.ItemID
        WHERE ServiceItems.PackageID = {package_id}
            AND ServiceItemTypes.DisplayName like '%SQL%User'""".format(package_id=package_id),
        connection
    )


def get_database_users(data, owner_id):
    data["level"] += 1
    database_users = []
    for wsp_database_user in get_websitepanel_database_users(data["connection"], owner_id):
        server = wsp_database_user["DisplayName"].rstrip("User")
        add_database_server_to_database_servers_list(data, server, wsp_database_user)
        if "error" in data["database_servers"][server]:
            continue
        log(u"database user {u}".format(u=wsp_database_user["ItemName"]), data=data)
        database_user = filter_dict(
            login=wsp_database_user["ItemName"],
            password=verify_password(data, wsp_database_user["ItemName"], decrypt(wsp_database_user["PropertyValue"], data["config"]["cryptokey"])),
            server=dict(
                type=data["database_servers"][server]["type"],
                host=data["database_servers"][server]["host"],
                port=data["database_servers"][server]["port"]
            ),
        )
        database_users.append(database_user)
    data["level"] -= 1
    return database_users


def get_websitepanel_service_properties(connection, service_id):
    result = query_exec(
        u"""SELECT ServiceProperties.PropertyName, ServiceProperties.PropertyValue
        FROM ServiceProperties
        WHERE ServiceID = {service_id}""".format(service_id=service_id),
        connection
    )
    return {row["PropertyName"]: row["PropertyValue"] for row in result}


def get_database_server_properties(data, server_type, service_id):
    websitepanel_service_properties = get_websitepanel_service_properties(data["connection"], service_id)
    if server_type == "mysql":
        return dict(
            type=server_type,
            host=websitepanel_service_properties["internaladdress"],
            port='3306',
            login=websitepanel_service_properties["rootlogin"],
            password=decrypt(websitepanel_service_properties["rootpassword"], data["config"]["cryptokey"])
        )
    elif server_type == "mssql":
        return dict(
            type=server_type,
            host=websitepanel_service_properties["internaladdress"],
            port='1433',
            login=websitepanel_service_properties["salogin"],
            password=decrypt(websitepanel_service_properties["sapassword"], data["config"]["cryptokey"])
        )


def list_mssql_database_names(connection):
    results = []
    with connection.cursor() as cursor:
        cursor.execute(u"""SELECT [name] FROM master.dbo.sysdatabases""")
        for row in cursor.fetchall():
            results.append(row[0])
        return results


def list_mysql_database_names(connection):
    results = []
    with connection.cursor() as cursor:
        cursor.execute(u"""SHOW DATABASES""")
        for row in cursor.fetchall():
            results.append(row[0].lower())
        return results


def list_mssql_database_names_on_server(data, db_server):
    if "databases" in data["database_servers"][db_server]:
        return data["database_servers"][db_server]["databases"]
    connection_string = "{driver};server={server};uid={login};pwd={password};".format(
        driver="DRIVER={SQL Server}",
        server=data["database_servers"][db_server]["host"],
        login=data["database_servers"][db_server]["login"],
        password=data["database_servers"][db_server]["password"]
    )
    try:
        data["database_servers"][db_server]["connection"] = pyodbc.connect(connection_string)
        data["database_servers"][db_server]["databases"] = list_mssql_database_names(
            data["database_servers"][db_server]["connection"]
        )
        data["database_servers"][db_server]["connection"].close()
    except pyodbc.Error as e:
        log('Failed to connect to mssql database server {host}: {emesg}'.format(
            host=data["database_servers"][db_server]["host"], emesg=safe_string_repr(e.args[1])
        ), color=Color.red)
        data["database_servers"][db_server]["databases"] = []
    return data["database_servers"][db_server]["databases"]


def list_mysql_database_names_on_server(data, db_server):
    if "databases" in data["database_servers"][db_server]:
        return data["database_servers"][db_server]["databases"]
    try:
        data["database_servers"][db_server]["connection"] = pymysql.connect(
            user=data["database_servers"][db_server]["login"],
            passwd=data["database_servers"][db_server]["password"]
        )
        data["database_servers"][db_server]["databases"] = list_mysql_database_names(
            data["database_servers"][db_server]["connection"]
        )
        data["database_servers"][db_server]["connection"].close()
    except pymysql.err.OperationalError as e:
        log('Failed to connect to mssql database server {host}: {emesg}'.format(
            host=data["database_servers"][db_server]["host"], emesg=safe_string_repr(e.args[1])
        ), color=Color.red)
        data["database_servers"][db_server]["databases"] = []
    return data["database_servers"][db_server]["databases"]


def check_database_exists_on_server(data, db_server, database):
    if data["database_servers"][db_server]["type"] == "mssql" \
            and database not in list_mssql_database_names_on_server(data, db_server):
        log(u"Database '{database}' is not found on mssql server '{server}'".format(
            database=database, server=data["database_servers"][db_server]["host"]
        ), data=data, color=Color.yellow)
        return False
    if data["database_servers"][db_server]["type"] == "mysql" \
            and database.lower() not in list_mysql_database_names_on_server(data, db_server):
        log(u"Database '{database}' is not found on mysql server '{server}'".format(
            database=database, server=data["database_servers"][db_server]["host"]
        ), data=data, color=Color.yellow)
        return False
    return True


def get_websitepanel_databases(connection, package_id):
    return query_exec(
        u"""SELECT ServiceItems.ItemName, ServiceItems.ServiceID, ServiceItemTypes.DisplayName
        FROM ServiceItems
        LEFT JOIN ServiceItemTypes ON ServiceItems.ItemTypeID = ServiceItemTypes.ItemTypeID
        WHERE ServiceItems.PackageID = {package_id}
            AND ServiceItemTypes.DisplayName like '%SQL%Database'""".format(package_id=package_id),
        connection
    )


def add_database_server_to_database_servers_list(data, database_server_name, service_item):
    if database_server_name.lower().startswith("mysql"):
        database_server_type = "mysql"
    elif database_server_name.lower().startswith("mssql"):
        database_server_type = "mssql"
    else:
        return
    if database_server_name not in data["database_servers"].keys():
        properties = get_database_server_properties(data, database_server_type, service_item["ServiceID"])
        if properties:
            data["database_servers"][database_server_name] = properties
        else:
            data["database_servers"][database_server_name] = dict(error=True)
            log(
                "Cannot get properties of database server '%s'. "
                "All databases related to this server will be skipped" % database_server_name,
                data=data, color=Color.red, is_error=True
            )


def get_databases(data, owner_id):
    data["level"] += 1
    databases = []
    for wsp_database in get_websitepanel_databases(data["connection"], owner_id):
        server = wsp_database["DisplayName"].rstrip("Database")
        add_database_server_to_database_servers_list(data, server, wsp_database)
        if "error" in data["database_servers"][server] or \
                not check_database_exists_on_server(data, server, wsp_database["ItemName"]):
            continue
        log(u"database %s" % wsp_database["ItemName"], data=data)
        database = filter_dict(
            name=wsp_database["ItemName"],
            server=dict(
                type=data["database_servers"][server]["type"],
                host=data["database_servers"][server]["host"],
                port=data["database_servers"][server]["port"]
            ),
            type=data["database_servers"][server]["type"]
        )
        databases.append(database)
    data["level"] -= 1
    return databases


def get_websitepanel_mail_users(connection, package_id, domain):
    return query_exec(
        u"""SELECT ServiceItems.ItemName, ServiceItemProperties.PropertyValue
        FROM ServiceItems
        LEFT JOIN ServiceItemTypes ON ServiceItems.ItemTypeID = ServiceItemTypes.ItemTypeID
        LEFT JOIN ServiceItemProperties ON ServiceItems.ItemID = ServiceItemProperties.ItemID
        WHERE ServiceItems.PackageID = {package_id}
            AND ServiceItemTypes.DisplayName = 'MailAccount'
            AND ServiceItems.ItemName like '%@{domain}'""".format(package_id=package_id, domain=domain),
        connection
    )


def get_mailbox(data, mailbox_name, ob_accounts_by_email):
    if safe_get_dict_value(data, "config", "mail_provider", "name") != "hMailServer5" or ob_accounts_by_email is None:
        return

    if mailbox_name not in ob_accounts_by_email:
        log(
            "Mailbox '{mailbox}' exists in control panel, but does not exist in hMail: it won't be migrated".format(
                mailbox=mailbox_name
            ), color=Color.yellow, data=data
        )
        return None

    ob_account = ob_accounts_by_email[mailbox_name]
    return dict(
        active=ob_account.Active,
        max_size=ob_account.MaxSize,
        forward_address=ob_account.ForwardAddress,
        forward_enabled=ob_account.ForwardEnabled,
        vacation_message_is_on=ob_account.VacationMessageIsOn,
        vacation_subject=ob_account.VacationSubject,
        vacation_message=ob_account.VacationMessage,
        vac_mes_exp=ob_account.VacationMessageExpires,
        vac_mes_exp_date=ob_account.VacationMessageExpiresDate
    )


def get_mailbox_forwarding(mailbox):
    if mailbox["forward_enabled"] and len(mailbox["forward_address"]) > 0:
        return filter_dict(
            addresses=filter_list([mailbox["forward_address"]]),
            enabled=mailbox["forward_enabled"]
        )


def get_mailbox_autoreply(mailbox):
    vacation_message_is_on = mailbox.get("vacation_message_is_on")
    vacation_subject = mailbox.get("vacation_subject", '')
    vacation_message = mailbox.get("vacation_message", '')
    # Do not dump autoresponder if it is empty and disabled
    if vacation_message_is_on is not True and vacation_subject == '' and vacation_message == '':
        return None

    # If vacation subject is empty, set default subject
    if vacation_subject == '':
        vacation_subject = 'Re: <request_subject>'
    mailbox_autoreply = dict(
        subject=safe_string_repr(vacation_subject),
        message=safe_string_repr(vacation_message),
        enabled=vacation_message_is_on is True
    )

    # If vacation message is disabled, information about expire date will be skipped by Plesk, no need to dump it
    if vacation_message_is_on is True and mailbox.get("vac_mes_exp") is True:
        mailbox_autoreply.update(filter_dict(
            end_date=get_autoresponder_expire_date(mailbox)
        ))
    return mailbox_autoreply


def get_autoresponder_expire_date(mailbox):
    exp_date = mailbox.get("vac_mes_exp_date")
    if exp_date is not None:
        # Autoresponder date format is WebsitePanel '2017-02-27 00:00:00'
        # Dump date only, because no time option in Plesk
        splitted_exp_date = exp_date.split()
        if len(splitted_exp_date) > 0:
            return splitted_exp_date[0]


def get_hmail_mail_users(data, package_id, domain, ob_domain):
    data["level"] += 1
    mail_users = []

    if ob_domain:
        ob_accounts = [ob_domain.Accounts.Item(i) for i in range(ob_domain.Accounts.Count)]
        ob_accounts_by_email = {account.Address: account for account in ob_accounts}
    else:
        ob_accounts_by_email = None

    for wsp_mail_user in get_websitepanel_mail_users(data["connection"], package_id, domain):
        name = wsp_mail_user["ItemName"].split('@')[0]
        domain = safe_idn_decode(wsp_mail_user["ItemName"].split('@')[1].lower())
        log(u"mail user {name}@{domain}".format(name=name, domain=domain), data=data)
        mail_user = filter_dict(
            name=name,
            password=verify_password(data, name, decrypt(wsp_mail_user["PropertyValue"], data["config"]["cryptokey"])),
        )
        mailbox_name = wsp_mail_user["ItemName"]
        mailbox = get_mailbox(data, mailbox_name, ob_accounts_by_email)
        if not mailbox:
            continue
        if mailbox["max_size"] < 0:
            mailbox_size = '-1'
        else:
            mailbox_size = str(mailbox["max_size"]) + "M"
        mail_user.update(filter_dict(
            disk_quota=mailbox_size,
            mailbox=mailbox["active"],
            forwarding=get_mailbox_forwarding(mailbox),
            auto_reply=get_mailbox_autoreply(mailbox)
        ))
        mail_users.append(mail_user)
    data["level"] -= 1
    return mail_users


def get_hmail_catch_all_action(data, ob_domain):
    if safe_get_dict_value(data, "config", "mail_provider", "name") == "hMailServer5":
        if ob_domain and ob_domain.Postmaster != '' and '@' in ob_domain.Postmaster:
            if not ob_domain.Postmaster.startswith('@') and not ob_domain.Postmaster.endswith('@'):
                return dict(
                    name="forward",
                    forward_email=ob_domain.Postmaster
                )


def get_mail_service(data, mail_domain_id, package_id, domain, disabled_by_parent=False):
    # pass through parent object status
    disabled_by_list = []
    if disabled_by_parent:
        disabled_by_list.append("parent")
    if not mail_domain_id:
        disabled_by_list.append("client")
        return dict(disabled_by=disabled_by_list)
    mail_provider = safe_get_dict_value(data, "config", "mail_provider", "name")
    if mail_provider == "hMailServer5":
        ob_domain = None
        try:
           ob_domain = data["com"].Domains.ItemByName(domain)
        except Exception:
            pass
        mail_service = filter_dict(
            mail_users=get_hmail_mail_users(data, package_id, domain, ob_domain),
            catch_all_action=get_hmail_catch_all_action(data, ob_domain),
            disabled_by=disabled_by_list
        )
    elif mail_provider == "MailEnable":
        mail_service = filter_dict(
            mail_users=get_mailenable_mail_users(data, domain),
            disabled_by=disabled_by_list
        )
    else:
        mail_service = dict()
    return mail_service


def get_mailenable_mail_users(data, domain):
    data["level"] += 1
    mail_users = []
    if "mailenable_data" not in data:
        data["mailenable_data"] = {}
        if "MAILENABLE_PATH" in os.environ:
            mailenable_filepath = os.environ["MAILENABLE_PATH"] + '\\Config\\AUTH.TAB'
            if os.path.isfile(mailenable_filepath):
                data["mailenable_data"] = get_mailenable_mailboxes_dict(mailenable_filepath)
            else:
                log(
                    u"MailEnable data file '{mailenable_filepath}' is not found. Mailboxes will not be dumped".format(
                        mailenable_filepath=mailenable_filepath
                    ), Color.red
                )
        else:
            log(u"MailEnable path not found in os environment variables. Mailboxes will not be dumped", Color.red)
    if domain in data["mailenable_data"]:
        for username, password in data["mailenable_data"][domain].iteritems():
            log(u"mail user {name}@{domain}".format(name=username, domain=domain), data=data)
            mail_user = filter_dict(
                name=username,
                password=password,
            )
            mail_users.append(mail_user)
    data["level"] -= 1
    return mail_users


def get_mailenable_mailboxes_dict(filename):
    mailboxes_dict = {}
    with open(filename) as fp:
        data = fp.read()
    line = data.split('\n')
    for word in line:
        mbox = word.split('\t')
        if mbox[4] == 'USER':
            uname = mbox[0].split('@')
            username = uname[0]
            password = mbox[2]
            domain = mbox[3]
            if domain not in mailboxes_dict:
                mailboxes_dict.update({domain: {}})
            mailboxes_dict[domain].update({username: password})
    return mailboxes_dict


def get_websitepanel_domain_name_by_id(connection, website_id):
    return query_exec(
        u"""SELECT ItemName
        FROM ServiceItems
        WHERE ItemID = '%s'""" % website_id,
        connection
    )


def get_domain_name_by_id(connection, website_id):
    domain_name = get_websitepanel_domain_name_by_id(connection, website_id)
    if len(domain_name) > 0:
        return domain_name[0].get("ItemName")


def get_addon_domain_docroot(data, website_id, subscription_docroot):
    if not subscription_docroot or not website_id:
        return None

    subscription_docroot = normalize_path(subscription_docroot)

    domain = get_domain_name_by_id(data["connection"], website_id)
    if not domain:
        return None

    domain_docroot = os.path.join(domain, "wwwroot")

    # Try to get domain document root from IIS configuration
    iis_site_info = get_iis_config_site_infos().get(normalize_domain_name(domain))
    if iis_site_info is not None and iis_site_info.root_application_path is not None:
        root_application_path = normalize_path(iis_site_info.root_application_path)
        if root_application_path.startswith(subscription_docroot):
            domain_docroot = root_application_path[len(subscription_docroot)+1:]

    if check_dir(os.path.join(subscription_docroot, domain_docroot), data):
        return domain_docroot
    else:
        return None


def get_websitepanel_website_id(connection, package_id, domain_name):
    return query_exec(
        u"""SELECT WebSiteID
        FROM Domains
        WHERE PackageID = '{package_id}' AND DomainName = '{domain_name}' AND WebSiteID IS NOT NULL""".format(
            package_id=package_id, domain_name=domain_name
        ),
        connection
    )


def get_website_id(connection, package_id, domain_name):
    website_id = get_websitepanel_website_id(connection, package_id, domain_name)
    if len(website_id) > 0:
        return website_id[0].get("WebSiteID")


def get_websitepanel_mail_domain_id(connection, package_id, domain_name):
    return query_exec(
        u"""SELECT MailDomainID
        FROM Domains
        WHERE PackageID = '{package_id}' AND DomainName = '{domain_name}' AND MailDomainID IS NOT NULL""".format(
            package_id=package_id, domain_name=domain_name
        ),
        connection
    )


def get_mail_domain_id(connection, package_id, domain_name):
    website_id = get_websitepanel_mail_domain_id(connection, package_id, domain_name)
    if len(website_id) > 0:
        return website_id[0].get("MailDomainID")


def get_websitepanel_domains(connection, package_id):
    return query_exec(
        u"""SELECT Domains.DomainName
        FROM Domains
        WHERE Domains.PackageID = '%s'
            AND Domains.IsInstantAlias = '0'
            AND Domains.IsDomainPointer = '0'""" % package_id,
        connection
    )


def get_websitepanel_domain_names(connection, package_id):
    domain_names = []
    for wsp_domain in get_websitepanel_domains(connection, package_id):
        domain_names.append(wsp_domain["DomainName"])
    return domain_names


def parse_dns_zone_lines(lines, domain):
    dns_zone = {"ttl": None, "soa": None, "records": []}
    for line in lines:
        if len(line) == 0:
            continue
        line_decoded = safe_idn_decode(line).decode('utf-8')
        if line_decoded.startswith("$TTL"):
            dns_zone.update(ttl=line_decoded.split()[1])
            continue
        src, second, third = line_decoded.split(None, 2)
        if second in ("A", "MX", "TXT", "SPF", "CNAME", "AAAA"):
            rtype = second
            dst = third
        else:
            rtype = third.split(None, 1)[0]
            dst = third.split(None, 1)[1]
        src = src.lower().replace("..", ".")
        dst = dst.lower().replace("..", ".")
        if src == "@":
            src = domain + "."
        elif not src.endswith("."):
            src += "." + domain + "."
        if rtype == "SOA":
            ssline = dst.split(None, 2)
            dns_zone.update(soa={"email": ssline[1], "params": ssline[2].strip("()").split()})
        elif rtype == "NS":
            dns_zone["records"].append({"type": "NS", "src": src, "dst": dst})
        elif rtype == "A":
            dns_zone["records"].append({"type": "A", "src": src, "dst": dst})
        elif rtype == "MX":
            ssline = dst.split()
            dst_mx = ssline[1]
            if not dst_mx.endswith(".") and not is_ipv4(dst_mx) and not is_ipv6(dst_mx):
                dst_mx += "." + domain + "."
            dns_zone["records"].append({"type": "MX", "src": src, "dst": dst_mx, "opt": ssline[0]})
        elif rtype in ("TXT", "SPF"):
            dns_zone["records"].append({"type": "TXT", "src": src, "dst": dst.strip('"')})
        elif rtype == "CNAME":
            dns_zone["records"].append({"type": "CNAME", "src": src, "dst": dst})
        elif rtype == "AAAA":
            dns_zone["records"].append({"type": "AAAA", "src": src, "dst": dst})
    return dns_zone


def get_dns_zone(data, domain, disabled_by_parent=False, normalize_priority=False):
    if not data["config"].get("dns_zone_file"):
        return
    dns_zone_file_path = data["config"]["dns_zone_file"].replace("[domain_name]", domain)
    dns_zone_lines = get_lines(dns_zone_file_path, display=False)
    dns_zone = {}
    # pass through parent object status
    disabled_by_list = []
    if disabled_by_parent:
        disabled_by_list.append("parent")
    if not dns_zone_lines:
        disabled_by_list.append("client")
        return filter_dict(type="master", disabled_by=disabled_by_list)

    data["level"] += 1
    domain_idna = safe_idn_decode(domain.lower())
    wsp_dns_zone = parse_dns_zone_lines(dns_zone_lines, domain_idna)
    log(u"dns zone %s" % domain_idna, data=data)
    dns_zone.update(filter_dict(
        type="master",
        ttl=wsp_dns_zone["ttl"] if "ttl" in wsp_dns_zone else None,
        dns_records=wsp_dns_zone["records"],
        disabled_by=disabled_by_list
    ))
    if wsp_dns_zone["soa"] is not None:
        dns_zone.update(filter_dict(
            refresh=wsp_dns_zone["soa"]["params"][1],
            retry=wsp_dns_zone["soa"]["params"][2],
            expire=wsp_dns_zone["soa"]["params"][3],
            minimum=wsp_dns_zone["soa"]["params"][4],
            email=wsp_dns_zone["soa"]["email"]
        ))
    if normalize_priority:
        dns_zone = normalize_mx_priority(data=data, domain_name=domain_idna, dns_zone=dns_zone)
    data["level"] -= 1
    return dns_zone


@cached
def get_local_ips():
    try:
        for interface in interfaces():
            for link in ifaddresses(interface).get(AF_INET, []):
                if link.get("addr"):
                    return [link["addr"]]
    except Exception:
        return []


def fix_ip_addresses(data, ip_addresses, domain):
    """
    Will check ip_addresses if contains invalid IP address records and try to fix them from list of ip_addresses of server and address of domain from DNS
    :param data: data structure
    :param ip_addresses: ip_address structures list
    :param domain: domain name to use in dns search
    :return:
    """
    # check if we have no any IP address formed not like XXX.XXX.XXX.XXX
    if len([addr.get('address') for addr in ip_addresses if not is_ipv4(addr.get('address'))]) == 0:
        return
    # otherwise get DNS zone to check this server have any IP address suitable to replace badformed IP address in ip_addresses structure
    websitepanel_domain_idna = safe_idn_decode(domain.lower())
    dns_zone = get_dns_zone(data=data, domain=domain)
    if dns_zone is None:
        return
    # get ip addresses for domain and later for www.domain if domain is a CNAME record of www.domain A record
    # devided because A records for domain is more preferred rather then A record for www.domain
    valid_ips = []
    for dns_record in dns_zone.get('dns_records', []):
        if dns_record.get("type") == 'A' and dns_record.get("src") == websitepanel_domain_idna + "." and dns_record.get("dst"):
            valid_ips.append(dns_record["dst"])
            break
    if len(valid_ips) == 0:
        for dns_record in dns_zone.get('dns_records', []):
            if dns_record.get("type") == 'A' and dns_record.get("src") == "www."+websitepanel_domain_idna + "." and dns_record.get("dst"):
                valid_ips.append(dns_record["dst"])
                break
    # if no suitable DNS records found get ips from server IPs list
    if len(valid_ips) == 0:
        valid_ips = get_local_ips()
    # finally, check invalid ips and replace them with first element of valid_ips list
    if len(valid_ips) > 0:
        for addr in ip_addresses:
            if not is_ipv4(addr.get('address')):
                addr['address'] = valid_ips[0]


def normalize_mx_priority(data, domain_name, dns_zone):
    # if dns_zone has incorrect structure
    if type(dns_zone) != dict or dns_zone.get("dns_records") is None or type(dns_zone["dns_records"]) != list:
        return dns_zone
    # now create to separate lists for MX and all other
    non_mx_records = [i for i in dns_zone["dns_records"] if i["type"] != "MX"]
    mx_records = [i for i in dns_zone["dns_records"] if i["type"] == "MX"]
    # if we have any record of type MX and priority larger then data["max_mx_priority"]
    if len(mx_records) == 0:
        return dns_zone
    integer_priorities = [int(record["opt"]) for record in mx_records]
    max_zone_priority = max(integer_priorities)
    if max_zone_priority <= data["max_mx_priority"]:
        return dns_zone
    data["level"] += 1
    log(text=safe_format(u"Wrong MX record priority of {priority} found for domain {domain}, will drop all MX records priority to minimum values",
                         priority=max_zone_priority, domain=domain_name), color=Color.yellow, data=data)
    dict_of_priorities = list_priority_sequence(numbers=integer_priorities, max_value=data["max_mx_priority"])
    for record in mx_records:
        if dict_of_priorities.get(record['opt']):
            log(text=safe_format(u"MX record '{record}' priority is changed to {priority}",
                                 record=safe_string_repr(record), priority=dict_of_priorities[record["opt"]]), color=Color.cyan, data=data)
            record["opt"] = str(dict_of_priorities[record["opt"]])
        else:
            log(text=safe_format(u"Can not find normalized priority for MX record '{record}', will leave it untouched",
                     record=safe_string_repr(record)), color=Color.yellow, data=data)
    normalized_dns_zone = dict(dns_zone)
    normalized_dns_zone["dns_records"]=non_mx_records+mx_records
    data["level"] -= 1
    return normalized_dns_zone



def get_addon_domains(data, package_id, docroot, disabled_by_parent=False):
    data["level"] += 1
    addon_domains = []
    wsp_domain_names = get_websitepanel_domain_names(data["connection"], package_id)
    domain_web_ips = get_domain_web_ips(data["connection"])
    for wsp_domain in wsp_domain_names:
        websitepanel_domain_idna = safe_idn_decode(wsp_domain.lower())
        log(u"addon domain %s" % websitepanel_domain_idna, data=data)
        website_id = get_website_id(data["connection"], package_id, wsp_domain)
        mail_domain_id = get_mail_domain_id(data["connection"], package_id, wsp_domain)

        iis_appcmd_info = get_iis_appcmd_site_infos().get(normalize_domain_name(wsp_domain))

        ip_addresses = None
        if data["get_ips_from_wsp"]:
            if wsp_domain in domain_web_ips:
                ip_addresses = [dict(
                    type="shared",
                    address=domain_web_ips[wsp_domain]
                )]
        else:
            if iis_appcmd_info is not None and len(iis_appcmd_info.ip_addresses) > 0:
                ip_addresses = [{"type": "shared", "address": address} for address in iis_appcmd_info.ip_addresses]

        if ip_addresses:
            fix_ip_addresses(data, ip_addresses, wsp_domain)

        disabled_by_list = []
        if iis_appcmd_info is not None and iis_appcmd_info.is_stopped:
            disabled_by_list.append("client")
        if disabled_by_parent:
            disabled_by_list.append("parent")

        addon_domain = filter_dict(
            name=websitepanel_domain_idna,
            target_document_root=get_addon_domain_docroot(data, website_id, docroot),
            no_web_hosting=None if website_id else True,
            mail_service=get_mail_service(data, mail_domain_id, package_id, wsp_domain),
            ip_addresses=ip_addresses,
            disabled_by=disabled_by_list,
            dns_zone=get_dns_zone(data=data, domain=wsp_domain, disabled_by_parent=len(disabled_by_list) > 0, normalize_priority=True)
        )

        if addon_domain["name"].startswith("www.") and addon_domain["name"].lstrip("www.") in wsp_domain_names:
            if "target_document_root" not in addon_domain:
                continue
            if addon_domain["target_document_root"] != get_addon_domain_docroot(
                    data, get_website_id(data["connection"], package_id, wsp_domain), docroot
            ):
                log(u"domain '%s' already exists without the 'www' prefix and will be skipped" % addon_domain["name"],
                    data=data, color=Color.yellow
                    )
            continue
        addon_domains.append(addon_domain)
    data["level"] -= 1
    return addon_domains


def get_websitepanel_user_web_files(connection, package_id):
    return query_exec(
        u"""SELECT ServiceItems.ItemName
        FROM ServiceItems
        LEFT JOIN ServiceItemTypes ON ServiceItems.ItemTypeID = ServiceItemTypes.ItemTypeID
        WHERE ServiceItems.PackageId = %s
            AND ServiceItemTypes.DisplayName = 'HomeFolder'""" % package_id,
        connection
    )


def get_subscription_docroot(data, owner_id):
    docroot = get_websitepanel_user_web_files(data["connection"], owner_id)
    if len(docroot) > 0:
        return check_dir(docroot[0].get("ItemName"), data)


def get_user_web_files(docroot):
    if docroot:
        return [dict(
            source=docroot,
            target="{webspace_root}"
        )]


def get_websitepanel_package_quotas(connection, owner_id):
    return query_exec(
        u"""SELECT PackageQuotas.QuotaValue, Quotas.QuotaName
        FROM PackageQuotas
        LEFT JOIN Quotas ON PackageQuotas.QuotaID = Quotas.QuotaID
        WHERE PackageQuotas.PackageID = '%s'""" % owner_id,
        connection
    )


def get_package_quotas(data, owner_id):
    wsp_quotas = {}
    for websitepanel_quota in get_websitepanel_package_quotas(data["connection"], owner_id):
        wsp_quotas.update({websitepanel_quota["QuotaName"]: str(websitepanel_quota["QuotaValue"])})
    return get_quotas(data, wsp_quotas)


def get_package_limits(data, wsp_subscription):
    quotas = get_plan_quotas(data, wsp_subscription["PlanID"])
    if wsp_subscription["OverrideQuotas"] == '1':
        quotas.update(get_package_quotas(data, wsp_subscription["PackageID"]))
    return quotas


def get_subscription_description(subscription):
    description = ""
    if subscription.get("StatusID") == 2:
        description += "Status \"Suspended\" in WebsitePanel. "
    elif subscription.get("StatusID") == 3:
        description += "Status \"Cancelled\" in WebsitePanel. "
    comments = subscription.get("PackageComments")
    if comments is not None:
        description += comments
    return description


def get_websitepanel_subscriptions(connection, owner_id):
    return query_exec(
        u"""SELECT PackageID, PackageName, PackageComments, StatusID, PlanID, OverrideQuotas
        FROM Packages
        WHERE UserID = %s""" % owner_id,
        connection
    )


def get_subscriptions(data, owner_id, login, password, disabled_by_parent=False):
    data["level"] += 1
    subscriptions = []
    for wsp_subscription in get_websitepanel_subscriptions(data["connection"], owner_id):
        package_id = str(wsp_subscription["PackageID"])
        name = login.replace('_', '-').replace('@', '-') + '-' + package_id + ".package"
        log(u"Subscription %s" % name, data=data)
        docroot = get_subscription_docroot(data, wsp_subscription["PackageID"])
        # Allowed max login length in Plesk is 15 characters.
        # Login have to ends with package_id to be unique, because first 15 characters can be the same.
        fixed_login = to_ascii(login.replace('@', '-'))[:15-len(package_id)] + package_id
        password = verify_password(data, fixed_login, password)

        # pass through parent object status
        disabled_by_list = []
        if disabled_by_parent:
            disabled_by_list.append("parent")
        if wsp_subscription["StatusID"] != 1:
            disabled_by_list.append("client")

        subscription = filter_dict(
            name=name,
            admin_description=get_subscription_description(wsp_subscription),
            sys_user=filter_dict(
                login=fixed_login,
                password=password
            ),
            limits=get_package_limits(data, wsp_subscription),
            web_files=get_user_web_files(docroot),
            addon_domains=get_addon_domains(data, wsp_subscription["PackageID"], docroot, len(disabled_by_list) > 0),
            # subdomains=get_subdomains(),
            databases=get_databases(data, wsp_subscription["PackageID"]),
            database_users=get_database_users(data, wsp_subscription["PackageID"]),
            ftp_users=get_ftp_users(data=data, owner_id=wsp_subscription["PackageID"], owner_name=fixed_login, subscription_name=name),
            # scheduled_tasks=get_scheduled_tasks(),
            mail_service={"disabled_by": ["client"]},
            is_fake="true",
            subscription_disabled_by=disabled_by_list
        )
        subscriptions.append(subscription)
    data["level"] -= 1
    return subscriptions


def get_description(user):
    description = ""
    if user.get("StatusID") == 2:
        description += "Status \"Suspended\" in WebsitePanel. "
    elif user.get("StatusID") == 3:
        description += "Status \"Cancelled\" in WebsitePanel. "
    if user.get("Created"):
        description += "Created %s in WebsitePanel" % user["Created"] + '. '
    if user.get("Changed"):
        description += "Changed %s in WebsitePanel" % user["Changed"] + '. '
    return description


def get_comment(user):
    comment = ""
    if user.get("SecondaryEmail"):
        comment += "SecondaryEmail:" + user["SecondaryEmail"] + '. '
    if user.get("SecondaryPhone"):
        comment += "SecondaryPhone:" + user["SecondaryPhone"] + '. '
    if user.get("Comments"):
        comment += user["Comments"] + '. '
    return comment


def get_contact_info(user):
    return filter_dict(
        name=join_nonempty_strs(
            [user["Firstname"], user["Lastname"]]
        ),
        email=user["Email"],
        company=user["CompanyName"],
        country_code=user["Country"],
        zip=user["Zip"],
        state=user["State"],
        city=user["City"],
        address=user["Address"],
        phone=user["PrimaryPhone"],
        fax=user["Fax"],
        # im=,
        # im_type=,
        comment=get_comment(user)
    )


def get_roles():
    return [
        dict(
            name="peer",
            permissions=dict(
                ftpAccountsManagement="true",
                dnsManagement="true",
                logRotationManagement="true",
                browseStats="true",
                filesManagement="true",
                webSitesAndDomainsManagement="true",
                databasesManagement="true",
                applicationsManagement="true",
                mailManagement="true",
                sitebuilderManagement="true",
                antivirusManagement="true",
                mailListsManagement="true",
                spamfilterManagement="true",
                backupRestoreManagement="true",
                userManagement="true"
            ),
            service_permissions=[
                dict(
                    classname="Smb_Service_Provider_FileSharing",
                    description="SMB File Sharing Service",
                    external_id="main",
                    permission_code="external",
                    permission_class="account",
                    value=True
                )
            ]
        ),
        dict(
            name="demo",
            permissions=dict(
                userManagement="false",
                backupRestoreManagement="false",
                spamfilterManagement="false",
                mailListsManagement="false",
                sitebuilderManagement="false",
                antivirusManagement="false",
                mailManagement="false",
                applicationsManagement="false",
                databasesManagement="false",
                webSitesAndDomainsManagement="false",
                logRotationManagement="false",
                browseStats="false",
                filesManagement="false",
                dnsManagement="false",
                ftpAccountsManagement="false"
            )
        )
    ]


def get_websitepanel_users(connection, owner_id):
    return query_exec(
        u"""SELECT UserID, StatusID, IsDemo, Username, Password, Firstname, Lastname, Email, Created, Changed, Comments,
        SecondaryEmail, Address, City, State, Country, Zip, PrimaryPhone, SecondaryPhone, Fax, CompanyName
        FROM users
        WHERE RoleID = 3 AND OwnerID = %s AND IsPeer = 1""" % owner_id,
        connection
    )


def get_users(data, owner_id, disabled_by_parent=False):
    data["level"] += 1
    users = []
    for wsp_user in get_websitepanel_users(data["connection"], owner_id):
        log(u"User %s" % wsp_user["Username"], data=data)
        password = decrypt(wsp_user["Password"], data["config"]["cryptokey"])

        # pass through parent object status
        disabled_by_list = []
        if disabled_by_parent:
            disabled_by_list.append("parent")
        if wsp_user["StatusID"] != 1:
            disabled_by_list.append("client")

        customer = filter_dict(
            login=wsp_user["Username"],
            password=password,
            disabled_by=disabled_by_list,
            contact_info=get_contact_info(wsp_user),
            role="Demo" if wsp_user["IsDemo"] == 1 else "Peer"
        )
        users.append(customer)
    data["level"] -= 1
    return users


def to_ascii(origin_login):
    """Replace non ASCII characters with numbers

    :type origin_login: str | unicode
    :rtype: str
    """
    result = ''
    for character in origin_login:
        if ord(character) < 128:
            result += character
        else:
            result += str(ord(character))
    return result


def get_websitepanel_customers(connection, owner_id):
    return query_exec(
        u"""SELECT UserID, StatusID, Username, Password, Firstname, Lastname, Email, Created, Changed, Comments,
        SecondaryEmail, Address, City, State, Country, Zip, PrimaryPhone, SecondaryPhone, Fax, CompanyName
        FROM users
        WHERE RoleID = 3 AND OwnerID = %s AND IsPeer = 0""" % owner_id,
        connection
    )


def get_customers(data, owner_id, disabled_by_parent=False):
    data["level"] += 1
    customers = []
    for wsp_customer in get_websitepanel_customers(data["connection"], owner_id):
        if wsp_customer["Username"] == "admin":
            log(
                u"System user name 'admin' is reserved by Plesk. Login 'admin' will be replaced to 'wspadmin'.",
                color=Color.yellow, data=data
            )
            login = "wspadmin"
        else:
            login = to_ascii(wsp_customer["Username"].replace(' ', '_'))
        log(u"Customer %s" % login, data=data)
        password = verify_password(data, login, decrypt(wsp_customer["Password"], data["config"]["cryptokey"]))
        disabled_by_list = []
        if wsp_customer["StatusID"] != 1:
            disabled_by_list.append("client")
        if disabled_by_parent:
            disabled_by_list.append("parent")
        customer = filter_dict(
            login=login,
            password=password,
            disabled_by=disabled_by_list,
            contact_info=get_contact_info(wsp_customer),
            description=get_description(wsp_customer),
            subscriptions=get_subscriptions(data, wsp_customer["UserID"], wsp_customer["Username"], password, len(disabled_by_list) > 0),
            users=get_users(data, wsp_customer["UserID"], len(disabled_by_list) > 0),
            roles=get_roles()
        )
        customers.append(customer)
    data["level"] -= 1
    return customers


def get_websitepanel_resellers(connection):
    return query_exec(
        u"""SELECT UserID, StatusID, Username, Password, Firstname, Lastname, Email, Created, Changed, Comments,
        SecondaryEmail, Address, City, State, Country, Zip, PrimaryPhone, SecondaryPhone, Fax, CompanyName
        FROM users
        WHERE RoleID = 2""",
        connection
    )


def verify_password(data, login, password):
    # "numbers, upper and lower-case characters, and special characters !,@,#,$,%,^,&,*,?,_,~"
    allowed_symbols = re.compile("^[\w!@#\$%\^&\*\?~]+$")
    if password is None or (login is not None and login in password) or not re.match(allowed_symbols, password):
        data["level"] += 1
        log(u"password does not conform to Plesk rules and will be generated from random", data=data, color=Color.yellow)
        data["level"] -= 1
        newpassword = generate_password(12)
        data['changed_passwords'].append(login + "," + password + "," + newpassword + os.linesep)
        return newpassword
    return password


def get_resellers(data):
    data["level"] += 1
    resellers = []
    for wsp_reseller in get_websitepanel_resellers(data["connection"]):
        login = to_ascii(wsp_reseller["Username"].replace(' ', '_'))
        log(u"Reseller %s" % login, data=data)
        password = verify_password(data, login, decrypt(wsp_reseller["Password"], data["config"]["cryptokey"]))
        disabled_by_list = []
        if wsp_reseller["StatusID"] != 1:
            disabled_by_list.append("client")
        reseller = filter_dict(
            login=login,
            password=password,
            disabled_by=disabled_by_list,
            contact_info=get_contact_info(wsp_reseller),
            description=get_description(wsp_reseller),
            customers=get_customers(data, wsp_reseller["UserID"], len(disabled_by_list) > 0),
            subscriptions=get_subscriptions(data, wsp_reseller["UserID"], wsp_reseller["Username"], password, len(disabled_by_list) > 0),
            hosting_plans=get_hosting_plans(data, wsp_reseller["UserID"]),
            users=get_users(data, wsp_reseller["UserID"], len(disabled_by_list) > 0),
            roles=get_roles()
        )
        resellers.append(reseller)
    data["level"] -= 1
    return resellers


def max_quota(*args):
    if '-1' in args:
        return '-1'
    mq = max(*args)
    if mq is None:
        return None
    return str(mq)


def get_quotas(data, quotas):
    return filter_dict(
        disk_space="-1",
        max_traffic="-1",
        max_site="-1",
        max_subdom="-1",
        max_box="-1",
        mbox_quota="-1",
        max_maillists="-1",
        max_subftp_users="-1",
        max_db="-1",
        mysql_dbase_space="-1",
        max_mssql_db="-1",
        mssql_dbase_space="-1",
    ) if data["nolimits"] else filter_dict(
        # max_cl=,
        disk_space=quotas.get("OS.Diskspace"),
        max_traffic=quotas.get("OS.Bandwidth"),
        max_site=quotas.get("OS.Domains"),
        max_subdom=quotas.get("OS.SubDomains"),
        # max_dom_aliases=,
        max_box=quotas.get("Mail.Accounts"),
        mbox_quota=quotas.get("Mail.MaxBoxSize"),
        # total_mboxes_quota=,
        max_maillists=quotas.get("Mail.Lists"),
        max_subftp_users=quotas.get("FTP.Accounts"),
        max_db=max_quota(
            quotas.get("MySQL4.Databases"),
            quotas.get("MySQL5.Databases"),
        ),
        mysql_dbase_space=max_quota(
            quotas.get("MySQL4.MaxDatabaseSize"),
            quotas.get("MySQL5.MaxDatabaseSize")
        ),
        max_mssql_db=max_quota(
            quotas.get("MsSQL2000.Databases"),
            quotas.get("MsSQL2005.Databases"),
            quotas.get("MsSQL2008.Databases"),
            quotas.get("MsSQL2012.Databases")
        ),
        mssql_dbase_space=max_quota(
            quotas.get("MsSQL2000.MaxDatabaseSize"),
            quotas.get("MsSQL2005.MaxDatabaseSize"),
            quotas.get("MsSQL2008.MaxDatabaseSize"),
            quotas.get("MsSQL2012.MaxDatabaseSize")
        ),
    )


def get_websitepanel_plan_quotas(connection, owner_id):
    return query_exec(
        u"""SELECT HostingPlanQuotas.QuotaValue, Quotas.QuotaName
        FROM HostingPlanQuotas
        LEFT JOIN Quotas ON HostingPlanQuotas.QuotaID = Quotas.QuotaID
        WHERE HostingPlanQuotas.PlanID = '%s'""" % owner_id,
        connection
    )


def get_plan_quotas(data, owner_id):
    wsp_quotas = {}
    for websitepanel_quota in get_websitepanel_plan_quotas(data["connection"], owner_id):
        wsp_quotas.update({websitepanel_quota["QuotaName"]: str(websitepanel_quota["QuotaValue"])})
    return get_quotas(data, wsp_quotas)


def get_websitepanel_hosting_plans(connection, owner_id):
    return query_exec(
        u"""SELECT PlanID, PlanName, IsAddon
        FROM HostingPlans
        WHERE UserID='%s'""" % owner_id,
        connection
    )


def get_hosting_plans(data, owner_id):
    data["level"] += 1
    hosting_plans = []
    for wsp_hosting_plan in get_websitepanel_hosting_plans(data["connection"], owner_id):
        log(u"Hosting plan %s" % wsp_hosting_plan["PlanName"], data=data)
        hosing_plan = filter_dict(
            name=wsp_hosting_plan["PlanName"],
            items=get_plan_quotas(data, wsp_hosting_plan["PlanID"]),
            is_addon=True if wsp_hosting_plan["IsAddon"] == '1' else False
        )
        hosting_plans.append(hosing_plan)
    data["level"] -= 1
    return hosting_plans


def get_database_servers(data):
    database_servers = []
    for wsp_database_server_key, wsp_database_server_properties in data["database_servers"].iteritems():
        if "error" in data["database_servers"][wsp_database_server_key]:
            continue
        database_server = dict(
            type=wsp_database_server_properties["type"],
            host=wsp_database_server_properties["host"],
            port=wsp_database_server_properties["port"],
            admin=dict(
                login=wsp_database_server_properties["login"],
                password=wsp_database_server_properties["password"]
            )
        )
        database_servers.append(database_server)
    return database_servers


def get_default_mail_server(data):
    default_mail_server = dict()
    if safe_get_dict_value(data, "config", "mail_provider", "name") == "hMailServer5":
        default_mail_server.update(
            # from MailMigrator's help: - hmail4 (hMail 4 and later)
            type='hmail4',
            admin=filter_dict(
                login=data["config"]["mail_provider"].get("login"),
                password=data["config"]["mail_provider"].get("password")
            )
        )
    return default_mail_server


def map_objects(data):
    log(u"START OBJECTS MAPPING", color=Color.white) if data.get("verbosity") else None
    dump = filter_dict(
        # reseller_plans=get_reseller_plans(),
        hosting_plans=get_hosting_plans(data, '1'),
        resellers=get_resellers(data),
        customers=get_customers(data, '1'),
    )
    dump.update(filter_dict(
        database_servers=get_database_servers(data),
        default_mail_server=get_default_mail_server(data),
    ))
    log(u"FINISH OBJECTS MAPPING", color=Color.white) if data.get("verbosity") else None
    return dump


def write_changed_ftp_logins(filename, data):
    try:
        log(text="Going to write down the changed ftp logins to file %s" % filename)
        with open(filename, 'w') as fp:
            fp.writelines(data)
    except Exception as e:
        log(text="Failed to write down changed ftp logins file due to %s" % safe_string_repr(e), color=Color.yellow)


def write_changed_passwords(filename, data):
    try:
        log(text="Going to write down the changed passwords to file %s" % filename)
        with open(filename, 'w') as fp:
            fp.writelines([i.encode("utf-8") for i in data])
    except Exception as e:
        log(text="Failed to write down changed passwords file due to %s" % safe_string_repr(e), color=Color.yellow)


def get_dump(args):
    dump = {}
    data = dict(
        level=-1,
        verbosity=args.get("verbosity"),
        wsp_config_file=args.get("config"),
        config={},
        warnings=[],
        errors=[],
        connection=[],
        database_servers={},
        com=None,
        nolimits=args.get("nolimits"),
        changed_ftp_logins_log_file=args.get("changed_ftp_logins_log_file"),
        changed_ftp_logins=[],
        changed_passwords_log_file=args.get("changed_passwords_log_file"),
        changed_passwords=[],
        get_ips_from_wsp=args.get("get_ips_from_wsp"),
        max_mx_priority=50,
    )

    sys.stdout = codecs.getwriter('utf-8')(sys.stdout)
    colorama.init()
    prepare_output_dir(args["output_dir"])
    prepare_log_file(args["output_dir"])
    data["config"].update(get_websitepanel_config(data["wsp_config_file"]))
    setup_connections(data)
    data["config"].update(get_websitepanel_config_from_database(data))
    setup_com(data)
    try:
        log(u"Dumping objects")
        dump.update(map_objects(data))
    finally:
        close_connections(data)
    show_warnings(data)
    write_changed_ftp_logins(data.get('changed_ftp_logins_log_file'), data.get('changed_ftp_logins'))
    write_changed_passwords(data.get('changed_passwords_log_file'), data.get('changed_passwords'))
    return dump


def setup_com(data):
    if safe_get_dict_value(data, "config", "mail_provider", "name") == "hMailServer5":
        log(u"Authenticating hmail api")
        data["com"] = win32com.client.Dispatch("hMailServer.Application")
        data["com"].Authenticate(data["config"]["mail_provider"]["login"], data["config"]["mail_provider"]["password"])


def get_websitepanel_dns_zone_file(connection):
    return query_exec(
        u"""SELECT PropertyName, PropertyValue
        FROM ServiceProperties
        WHERE PropertyName like 'zone%'""",
        connection
    )


def get_dns_zone_file_template(data):
    dns_zone_config = get_websitepanel_dns_zone_file(data["connection"])
    zone_folder = None
    zone_template = None
    for dns_property in dns_zone_config:
        if dns_property.get("PropertyName") == "zonesfolderpath":
            zone_folder = dns_property.get("PropertyValue")
        if dns_property.get("PropertyName") == "zonefilenametemplate":
            zone_template = dns_property.get("PropertyValue")
    if zone_folder and zone_template:
        return os.path.join(zone_folder, zone_template)
    log(u"Cannot find template of dns zone file in WebsitePanel database. DNS records will not be dumped",
        data=data, color=Color.yellow)


def get_websitepanel_mail_providers(connection):
    return query_exec(
        u"""SELECT Providers.ProviderName
        FROM Services
        LEFT JOIN Providers ON Services.ProviderID = Providers.ProviderID
        WHERE Providers.GroupID = '{group}'""".format(group=WEBSITEPANEL_MAIL_RESOURCE_GROUP),
        connection
    )


def get_mail_provider_properties(connection):
    return query_exec(
        u"""SELECT ServiceProperties.PropertyName, ServiceProperties.PropertyValue
        FROM ServiceProperties
        LEFT JOIN Services ON ServiceProperties.ServiceID = Services.ServiceID
		LEFT JOIN Providers ON Services.ProviderID = Providers.ProviderID
        WHERE Providers.GroupID = '{group}'""".format(group=WEBSITEPANEL_MAIL_RESOURCE_GROUP),
        connection
    )


def get_mail_provider(data):
    mail_provider = {}
    mail_provider_name = get_websitepanel_mail_providers(data["connection"])
    if len(mail_provider_name) > 0:
        provider_name = mail_provider_name[0].get("ProviderName", "")
        mail_provider.update(name=provider_name)
        log(safe_format("Mail provider '{provider_name}' detected", provider_name=provider_name))
        mail_provider_properties = get_mail_provider_properties(data["connection"])
        for mail_provide_property in mail_provider_properties:
            if mail_provide_property["PropertyName"] == "adminusername":
                mail_provider.update(login=mail_provide_property["PropertyValue"])
            elif mail_provide_property["PropertyName"] == "adminpassword":
                mail_provider.update(
                    password=decrypt(mail_provide_property["PropertyValue"], data["config"]["cryptokey"])
                )
    else:
        log(
            u"Mail provider was not detected. Data about mail content from mail provider won't be taken",
            data=data, color=Color.yellow
        )
    return mail_provider


def get_websitepanel_instant_domain_alias(connection):
    return query_exec(
        u"""SELECT Servers.InstantDomainAlias
        FROM Services
        LEFT JOIN Servers
            ON Services.ServiceID = Servers.ServerID
        WHERE Services.ServiceName like 'Internet Information Services%'
            OR Services.ServiceName='Web'""",
        connection
    )


def get_instant_domain_alias(data):
    instant_domain_alias = get_websitepanel_instant_domain_alias(data["connection"])
    if len(instant_domain_alias) > 0:
        return instant_domain_alias[0].get("InstantDomainAlias")


def get_websitepanel_config_from_database(data):
    log(u"Getting panel configuration from database")
    config = filter_dict(
        dns_zone_file=get_dns_zone_file_template(data),
        mail_provider=get_mail_provider(data),
        instant_domain_alias=get_instant_domain_alias(data)
    )
    return config


def get_websitepanel_config(config_file):
    log(u"Getting panel configuration")
    if not os.path.isfile(config_file):
        log(u"Websilte Panel configuration file \"%s\" is not found. "
            "Please use argument \"-c\" and define correct path." % config_file, color=Color.red)
        sys.exit(1)
    config = dict(
        driver="DRIVER={SQL Server};",
        connectionstring=None,
        encryption=False,
        cryptokey=None
    )
    with open(config_file) as conf:
        dict_xml = xmltodict.parse(conf.read())
    try:
        config.update(connectionstring=dict_xml["configuration"]["connectionStrings"]["add"]["@connectionString"])
        for element in dict_xml["configuration"]["appSettings"]["add"]:
            if element["@key"] == "WebsitePanel.EncryptionEnabled" and element["@value"] == "true":
                config.update(encryption=True)
            elif element["@key"] == "WebsitePanel.CryptoKey":
                config.update(cryptokey=element["@value"])
    except KeyError as e:
        log(u"Cannot find key %s in WebsitePanel config" % e, color=Color.red)
        sys.exit(1)
    return config


@cached
def get_codepage():
    try:
        p = subprocess.Popen("cmd.exe /c chcp", stdout=subprocess.PIPE, stderr=subprocess.PIPE)
        stdout, _ = p.communicate()
        output_encoding = stdout.split()[-1].strip('.')
        codepage = convert_windows_encoding_to_python(output_encoding)
        log("Detected codepage: " + codepage)
    except Exception:
        log("Exception: ")

        # Fallback if failed to detect codepage with chcp.com.
        #
        # It is applicable at least for Windows 2003 x64,
        # where chcp.com could not be launched from our 32-bit application.
        # The chcp.com is located at %windir%\System32, while 32-bit application gets %windir%\SysWOW64
        # instead of %windir%\System32, and there is no chcp.com.
        # And actually there is no way to call chcp in that case without installing hotfix and rebooting server:
        # https://support.microsoft.com/en-us/help/942589/a-32-bit-application-cannot-access-the-system32-folder-on-a-computer-t
        #
        # In the future we can switch to sys.stdout.encoding instead of chcp.com after careful testing on all
        # Windows configurations, including custom Windows locales (German, Japanese, etc).
        log(
            "Failed to detect codepage with 'chcp.exe', use Python output codepage (%s)" + repr(
                sys.stdout.encoding
            )
        )
        codepage = sys.stdout.encoding
    return codepage


def convert_windows_encoding_to_python(encoding):
    """
    :type encoding: str | unicode
    :rtype: str | unicode
    """
    code_page_map = {
        '65001': 'utf-8'
    }
    if encoding in code_page_map:
        return code_page_map[encoding]

    # this is correct only for limited number of encodings
    # if function works bad, add new encoding mapping to code_page_map dict
    return u"cp%s" % encoding


def decrypt(crypted, key):
    p = subprocess.Popen([
        os.path.join(os.path.dirname(os.path.abspath(__file__)), "CryptoUtils.exe")
    ], stdin=subprocess.PIPE, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    stdout, stderr = p.communicate(crypted+'\n'+key+'\n')
    return stdout.strip().decode(get_codepage())


def check_dir(path, data):
    if os.path.isdir(path):
        return path
    data["level"] += 1
    log(u"Directory '%s' is not found" % path, data=data, color=Color.yellow)
    data["level"] -= 1


def get_lines(filename, display=True, data=None, strip=True):
    if not os.path.isfile(filename):
        log(u"Cannot open file '%s'" % filename, Color.yellow, display=display, data=data)
        return []
    with open(filename) as f:
        lines = f.readlines()
        if strip:
            lines = [line.rstrip('\n') for line in lines]
        return lines


def filter_dict(**kwargs):
    return dict(
        (key, value)
        for key, value
        in kwargs.iteritems()
        if isinstance(value, (bool, int, long)) or value is not None and len(value) > 0
    )


def filter_list(somelist):
    return list(
        value
        for value
        in somelist
        if value is not None and len(value) > 0
    )


def query_exec(sql_query, connection):
    """:rtype: list[dict]"""
    results = []
    with connection.cursor() as cursor:
        cursor.execute(sql_query)
        columns = [column[0] for column in cursor.description]
        for row in cursor.fetchall():
            results.append(dict(zip(columns, row)))
    return results


def setup_connections(data):
    log(u"Connecting WebsitePanel database")
    try:
        data["connection"] = pyodbc.connect(data["config"]["driver"]+data["config"]["connectionstring"])
    except pyodbc.Error as e:
        log('Failed to connect to WebsitePanel database: %s' % safe_string_repr(e.args[1]), color=Color.red)
        sys.exit(1)


def close_connections(data):
    log(u"Closing connection to WebsitePanel database")
    data["connection"].close()


def show_warnings(data):
    global log_file
    log(u"Printing warnings")
    clear_log(data["warnings"], 0)
    for warning in data["warnings"]:
        print(warning["color"] + warning["text"] + Color.default)
        if log_file is not None:
            log_file.write(warning["text"].encode('utf8') + os.linesep)
    for error in data["errors"]:
        print(error["color"] + error["text"] + Color.default)
        if log_file is not None:
            log_file.write(error["text"].encode('utf8') + os.linesep)


def print_usage():
    print(
        '''Usage: websitepanel-hosting-description.py [OPTIONS]...
    -f, --file          output filename, "json" and "yaml" file extensions recognized, default is "hosting-description.json"
    -v                  verbosity
    -c, --config        WebsitePanel config file
    --limits            dump limits (experimental feature)
    --changed-ftp-logins-log-file   CSV file name to store changed info like "oldlogin,newlogin,subscription,customer" strings. output\changed_ftp_logins.csv by default 
    --changed-passwords-log-file   CSV file name to store changed info like "login,oldpassword,newpassword" strings. output\changed_passwords.csv by default 
Example:
    websitepanel-hosting-description.py -f myserver.yaml -c "C:\WebsitePanel\Enterprise Server\Web.config"'''
    )


def get_args():
    output_dir = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'output')
    config = dict(
        file="hosting-description.json",
        verbosity=None,
        config="C:\WebsitePanel\Enterprise Server\Web.config",
        nolimits=True,
        output_dir=output_dir,
        changed_ftp_logins_log_file=os.path.join(output_dir, "changed_ftp_logins.csv"),
        changed_passwords_log_file=os.path.join(output_dir, "changed_passwords.csv"),
        get_ips_from_wsp=False
    )
    try:
        opts, args = getopt.getopt(sys.argv[1:], "hvf:c:", ["help", "file=", "config=", "limits", "changed-ftp-logins-log-file=", "changed-passwords-log-file=" "get-ips-from-wsp"])
    except getopt.GetoptError:
        print_usage()
        sys.exit(1)
    for opt, arg in opts:
        if opt in ("-h", '--help'):
            print_usage()
            sys.exit()
        elif opt == "-v":
            config["verbosity"] = 1
        elif opt in ("-f", "--file"):
            config["file"] = arg
        elif opt in ("-c", "--config"):
            config["config"] = arg
        elif opt in ("--changed-ftp-logins-log-file", ):
            config["changed_ftp_logins_log_file"] = arg
        elif opt in ("--changed-passwords-log-file", ):
            config["changed_passwords_log_file"] = arg
        elif opt == "--limits":
            config["nolimits"] = False
        elif opt == "--get-ips-from-wsp":
            config["get_ips_from_wsp"] = True
    return config


def clear_log(warnings, level):
    if len(warnings) > 0 and warnings[-1]["level"] >= level:
        while len(warnings) > 0 and warnings[-1]["level"] >= level and warnings[-1]["color"] == Color.default:
            warnings.pop()


def log(text, color=Color.default, display=True, data=None, is_error=False):
    level = 0
    split = '. '
    verbosity = 1
    if data:
        level = data["level"]
        clear_log(data["warnings"], level)
        data["warnings"].append({"level": level, "text": split * level + text, "color": color})
        verbosity = data.get("verbosity")
        if is_error:
            data["errors"].append({"text": text, "color": color})
    now = time.strftime("[%Y-%m-%d %H:%M:%S]")
    if display and verbosity:
        print(Color.white + now, Color.default + split * level + color + text + Color.default)
    global log_file
    if log_file is not None:
        log_file.write(now + ' ' + split * level + text.encode('utf8') + os.linesep)


def write_json(filename, data):
    log(u"Writing dump file")
    with open(os.path.join(os.path.dirname(os.path.abspath(__file__)), filename), 'w') as f:
        json.dump(data, f, indent=1, encoding='utf-8')
    log(u"File '%s' is ready" % filename, Color.green)


def write_yaml(filename, data):
    log(u"Writing dump file")
    with open(os.path.join(os.path.dirname(os.path.abspath(__file__)), filename), 'w') as f:
        yaml.safe_dump(data, f, encoding='utf-8', allow_unicode=True)
    log(u"File '%s' is ready" % filename, Color.green)


class IISConfigSiteInfo(object):
    def __init__(self, xml_node):
        self._xml_node = safe_dict(xml_node)

    @property
    def name(self):
        return self._xml_node.get('@name')

    @property
    def server_auto_start(self):
        return self._xml_node.get('@serverAutoStart') != 'false'

    @property
    def root_application_path(self):
        for application in safe_iterable(self._xml_node.get('application')):
            for vdir in safe_iterable(application.get('virtualDirectory')):
                vdir = safe_dict(vdir)
                if vdir.get('@path') == '/':
                    return vdir.get('@physicalPath')

        return None

    def __str__(self):
        return (
            "IIS site(name={name}, server_auto_start={server_auto_start}, "
            "root_application_path={root_application_path})".format(
                name=repr(self.name),
                server_auto_start=repr(self.server_auto_start),
                root_application_path=repr(self.root_application_path)
            )
        )


def safe_dict(o):
    if isinstance(o, dict):
        return o
    else:
        return {}


def safe_iterable(o):
    if isinstance(o, collections.Iterable):
        return o
    else:
        return []


@cached
def get_iis_config_site_infos():
    """Get information about websites from IIS config

    :rtype: dict[str | unicode, IISConfigSiteInfo]
    """
    try:
        windir = os.getenv('windir')

        if windir is None:
            log(u"%windir% environment variable is not defined, data about sites from IIS won't be taken", Color.red)
            return {}

        application_config_path = os.path.join(
            os.getenv('windir'), r"system32\inetsrv\config\applicationHost.config"
        )

        if not os.path.exists(application_config_path):
            log(
                u"IIS 7 application config file '{file}' does not exist. "
                u"Data about sites from IIS won't be taken".format(
                    file=application_config_path
                ),
                Color.red
            )
            return {}

        result = {}

        with open(application_config_path) as fp:
            xml_data = xmltodict.parse(fp.read(), force_list=('application', 'site', 'virtualDirectory'))
            sites_xml = (
                xml_data.get('configuration', {}).get('system.applicationHost', {}).get('sites', {}).get('site', [])
            )
            for site_info_xml in sites_xml:
                iis_site_info = IISConfigSiteInfo(site_info_xml)
                result[normalize_domain_name(iis_site_info.name)] = iis_site_info

        return result
    except Exception as e:
        log(
            u"Failed to read IIS 7 application config file. "
            u"Data about sites from IIS won't be taken. Reason: {reason}".format(
                reason=unicode(e)
            ),
            Color.red
        )
        return {}


class IISAppcmdSiteInfo(object):
    def __init__(self, xml_node):
        self._xml_node = safe_dict(xml_node)

    @property
    def name(self):
        return self._xml_node.get('@SITE.NAME')

    @property
    def binding_sites(self):
        binding_sites = []

        for binding_str in self._xml_node.get('@bindings', '').split(','):
            if binding_str.strip() == '':
                continue

            site_name = binding_str.split(':')[-1]
            binding_sites.append(site_name)

        return binding_sites

    @property
    def ip_addresses(self):
        ips = []
        for binding_str in self._xml_node.get('@bindings', '').split(','):
            if binding_str.strip() == '':
                continue
            # extracting IP address from strings like "http/129.121.3.160:80:buymesmart.com.au"
            ip = binding_str.split(':')[0].split('/')[-1]
            if ip not in ips:
                ips.append(ip)
        return ips

    @property
    def is_stopped(self):
        return self._xml_node.get('@state', '').lower() == 'stopped'

    def __str__(self):
        return (
            "IIS site(name={name}, is_stopped={is_stopped})".format(
                name=repr(self.name),
                is_stopped=repr(self.is_stopped)
            )
        )


@cached
def get_iis_appcmd_site_infos():
    """Get information about websites from 'appcmd' application

    :rtype: dict[str | unicode, ]
    """
    try:
        windir = os.getenv('windir')

        if windir is None:
            log(
                u"%windir% environment variable is not defined, data about sites from 'appcmd' command won't be taken",
                Color.red
            )
            return {}

        appcmd_path = os.path.join(
            windir, r"system32\inetsrv\appcmd"
        )

        appcmd_xml = subprocess.check_output('"{appcmd}" list site /xml'.format(appcmd=appcmd_path))
        xml_data = xmltodict.parse(appcmd_xml, force_list=('site',))

        result = {}

        for site_info in xml_data['appcmd'].get('SITE', []):
            appcmd_site_info = IISAppcmdSiteInfo(site_info)
            result[normalize_domain_name(appcmd_site_info.name)] = appcmd_site_info
            for binding_site in appcmd_site_info.binding_sites:
                result[normalize_domain_name(binding_site)] = appcmd_site_info

        return result
    except Exception as e:
        log(
            u"Failed to get information about sites from 'appcmd' command. "
            u"Data about sites from the utility won't be taken. Reason: {reason}".format(
                reason=unicode(e)
            ),
            Color.red
        )
        return {}


def prepare_output_dir(output_dir):
    if not os.path.isdir(output_dir):
        os.mkdir(output_dir)


def prepare_log_file(output_dir):
    global log_file
    log_file = open(os.path.join(output_dir, 'log.txt'), 'w')


def close_log_file():
    global log_file
    log_file.close()


def write_file(filename, data):
    if filename.endswith('.json'):
        write_json(filename, data)
    elif filename.endswith('.yaml'):
        write_yaml(filename, data)
    else:
        log(text=u'Filename extension did not recognized, will use given filename to dump data in JSON format', color=Color.yellow)
        write_json(filename, data)


if __name__ == '__main__':
    arguments = get_args()
    try:
        write_file(arguments["file"], get_dump(arguments))
    finally:
        close_log_file()
