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,
            passwd=password,
            db=db_name
        )

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

    def clear_table(self, table_name):
        cursor = self.connection.cursor()
        cursor.execute(f"START TRANSACTION; DELETE FROM `{table_name}`; COMMIT;")
        cursor.close()

    def get_column_names(self):
        cursor = self.connection.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):
        cursor = self.connection.cursor()
        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].data_type == str:
                values.append(f'"{value}"')
            else:
                values.append(value)

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

        cursor.execute(request)
        cursor.close()

    def update(self, table_name, expressions, conditions):
        cursor = self.connection.cursor()

        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}')

        cursor.execute("START TRANSACTION; UPDATE `{}` SET {} WHERE {}; COMMIT;".format(
            table_name, ','.join(expressions_formatted), ' AND '.join(conditions_formatted)
        ))

        cursor.close()

    def delete_from(self, table_name, conditions):
        cursor = self.connection.cursor()

        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}')

        cursor.execute("START TRANSACTION; DELETE FROM `{}` WHERE {}; COMMIT;".format(
            table_name, ' AND '.join(conditions_formatted)
        ))

        cursor.close()

    def get_data(self, table_name):
        cursor = self.connection.cursor()

        cursor.execute(f'SELECT * FROM `{table_name}`;')
        content = list(map(list, cursor.fetchall()))

        cursor.close()
        return content

    def get_rows(self, table_name, conditions):
        cursor = self.connection.cursor()

        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}')

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

        cursor.close()

        return content