from parallels.core.logging import get_logger
from parallels.core.hosting_repository.database_server import DatabaseServerEntity
from parallels.core.hosting_repository.service_plan import ServicePlanModel, ServicePlanEntity
from parallels.core.utils.mysql import escape_args_list
from parallels.plesk.hosting_repository.base import PleskBaseModel
from parallels.plesk.hosting_repository.utils.service_plan_importer import ServicePlanImporter

logger = get_logger(__name__)


class PleskServicePlanModel(ServicePlanModel, PleskBaseModel):
    def get_list(self, filter_id=None, filter_owner_id=None, filter_name=None):
        """Retrive list of service plans from target Plesk which ID listed in given filter

        :type filter_id: list[int] | None
        :type filter_owner_id: list[int] | None
        :type filter_name: list[str] | None
        :rtype: list[parallels.core.hosting_repository.service_plan.ServicePlanEntity]
        """
        if filter_id is not None and len(filter_id) == 0:
            #  filter by id is empty, so no one service plan could be retrieved
            return []

        if filter_owner_id is not None and len(filter_owner_id) == 0:
            #  filter by owner id is empty, so no one service plan could be retrieved
            return []

        if filter_name is not None and len(filter_name) == 0:
            #  filter by name is empty, so no one service plan could be retrieved
            return []

        # retrieve service plans
        service_plans = {}
        query = """
            SELECT id, name, owner_id
            FROM Templates
            WHERE type = "domain"
        """
        query_args = {}
        if filter_id is not None:
            filter_id_placeholders, filter_id_values = escape_args_list(filter_id, 'id')
            query += ' AND id in ({filter_id_placeholders_str})'.format(
                filter_id_placeholders_str=', '.join(filter_id_placeholders)
            )
            query_args.update(filter_id_values)
        if filter_owner_id is not None:
            filter_owner_id_placeholders, filter_owner_id_values = escape_args_list(filter_owner_id, 'owner_id')
            query += ' AND owner_id in ({filter_owner_id_placeholders_str})'.format(
                filter_owner_id_placeholders_str=', '.join(filter_owner_id_placeholders)
            )
            query_args.update(filter_owner_id_values)
        if filter_name is not None:
            filter_name_placeholders, filter_name_values = escape_args_list(filter_name, 'name')
            query += ' AND name in ({filter_name_placeholders_str})'.format(
                filter_name_placeholders_str=', '.join(filter_name_placeholders)
            )
            query_args.update(filter_name_values)
        for row in self.plesk_server.query_panel_db(query, query_args):
            service_plans[row['id']] = ServicePlanEntity(row['id'], row['name'], row['owner_id'])

        if len(service_plans) == 0:
            # no one service plan was retrieved with given filters, so no need to proceed
            return []

        # retrieve settings for just retrieved service plans
        filter_actual_id_placeholders, filter_actual_id_values = escape_args_list(service_plans.keys(), 'id')
        query = """
            SELECT tmpl_id, element, value
            FROM TmplData
            WHERE tmpl_id in ({filter_actual_id_placeholders_str})
        """.format(filter_actual_id_placeholders_str=', '.join(filter_actual_id_placeholders))
        query_args = filter_actual_id_values
        properties = {}
        for row in self.plesk_server.query_panel_db(query, query_args):
            properties[(row['tmpl_id'], row['element'])] = row['value']

        # retrieve database servers
        query = """
            SELECT id, host, port, type
            FROM DatabaseServers
            """
        database_servers = {}
        for row in self.plesk_server.query_panel_db(query):
            database_servers[str(row['id'])] = DatabaseServerEntity(row['id'], row['host'], row['port'], row['type'])

        # assign service plan settings to service plans
        for service_plan_id, service_plan in service_plans.items():
            # assign shell
            service_plan.shell = properties.get((service_plan_id, 'shell'))
            # assign default database servers
            for database_server_type in ['mysql', 'postgresql', 'mssql']:
                database_server_id = properties.get((service_plan_id, 'default_server_%s' % database_server_type))
                database_server = database_servers.get(str(database_server_id))
                if database_server is not None:
                    service_plan.set_database_server(database_server)

        return service_plans.values()

    def create(self, name, owner_username, settings):
        """Create service plan with given name, owner and settings in target Plesk

        :type name: str
        :type owner_username: str
        :type settings: parallels.plesk.models.target_data_model.PleskPlanSettings
        """
        ServicePlanImporter().create_service_plan(name, owner_username, settings)
