from abc import ABC, abstractmethod

import MySQLdb


class Wrapper(ABC):
    def __init__(self):
        self.schemes = {}

    @abstractmethod
    def close_connection(self):
        pass

    @abstractmethod
    def clear_table(self, table_name):
        pass

    @abstractmethod
    def get_column_names(self):
        pass

    @abstractmethod
    def insert_one(self, table_name, data_row: dict):
        pass

    @abstractmethod
    def update(self, table_name, expressions, conditions):
        pass

    @abstractmethod
    def delete_from(self, table_name, conditions):
        pass

    @abstractmethod
    def get_data(self, table_name):
        pass

    @abstractmethod
    def get_rows(self, table_name, conditions):
        pass


class MySQLWrapper(Wrapper):
    def __init__(self, host, username, password, db_name):
        super().__init__()
        self.connection = MySQLdb.connect(
            host=host,
            user=username,
            password=password,
            db=db_name
        )

    def close_connection(self):
        self.connection.close()

    def clear_table(self, table_name):
        with self.connection.cursor() as cursor:
            cursor.execute(f"DELETE FROM `{table_name}`;")

    def get_column_names(self):
        with self.connection.cursor() as cursor:
            cursor.execute('DESCRIBE table_task1;')
            table_structure = cursor.fetchall()

        table_headers = [field[0] for field in table_structure]
        return table_headers

    def insert_one(self, table_name, data_row: dict):
        scheme = self.schemes[table_name]

        field_names = []
        values = []

        for field_name, value in data_row.items():
            field_names.append(f'`{field_name}`')
            if scheme.fields[field_name].nullable and value is None:
                values.append('NULL')
            elif scheme.fields[field_name].data_type == str:
                values.append(f'"{value}"')
            else:
                values.append(str(value))

        request = "INSERT INTO `{}` ({}) VALUES ({});".format(
            table_name, ",".join(field_names), ",".join(values)
        )

        with self.connection.cursor() as cursor:
            cursor.execute(request)

        self.connection.commit()

    def update(self, table_name, expressions, conditions):
        expressions_formatted = []
        for field_name, value in expressions.items():
            if value != 'NULL' or not value.isnumeric():
                value = f'"{value}"'
            expressions_formatted.append(f'`{field_name}`={value}')

        conditions_formatted = []
        for field_name, value in conditions.items():
            if value != 'NULL' or not value.isnumeric():
                value = f'"{value}"'
            conditions_formatted.append(f'`{field_name}`={value}')

        with self.connection.cursor() as cursor:
            cursor.execute("UPDATE `{}` SET {} WHERE {};".format(
                table_name, ','.join(expressions_formatted), ' AND '.join(conditions_formatted)
            ))

        self.connection.commit()

    def delete_from(self, table_name, conditions):
        conditions_formatted = []
        for field_name, value in conditions.items():
            if value != 'NULL' or not value.isnumeric():
                value = f'"{value}"'
            conditions_formatted.append(f'`{field_name}`={value}')

        with self.connection.cursor() as cursor:
            cursor.execute("DELETE FROM `{}` WHERE {};".format(
                table_name, ' AND '.join(conditions_formatted)
            ))

        self.connection.commit()

    def get_data(self, table_name):
        with self.connection.cursor() as cursor:
            cursor.execute(f'SELECT * FROM `{table_name}`;')
            content = list(map(list, cursor.fetchall()))

        return content

    def get_rows(self, table_name, conditions):
        conditions_formatted = []
        for field_name, value in conditions.items():
            if value != 'NULL' or not value.isnumeric():
                value = f'"{value}"'
            conditions_formatted.append(f'`{field_name}`={value}')

        with self.connection.cursor() as cursor:
            cursor.execute(f'SELECT * FROM `{table_name}` WHERE {" AND ".join(conditions_formatted)};')
            content = list(map(list, cursor.fetchall()))

        return content