from abc import ABC, abstractmethod

import MySQLdb


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

    @abstractmethod
    def clear_table(self, table_name):
        pass

    @abstractmethod
    def get_column_names(self):
        pass

    @abstractmethod
    def insert_one(self, table_name, row, field_names=None):
        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 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, row, field_names=None):
        cursor = self.connection.cursor()

        if field_names is not None:
            field_names_formatted = []
            for name in field_names:
                if name != 'NULL' or not name.isnumeric():
                    name = f'`{name}`'
                field_names_formatted.append(name)
            field_names_formatted = f'({",".join(field_names_formatted)})'
        else:
            field_names_formatted = ''

        row_formatted = []
        for value in row:
            if value == 'NULL' or value.isnumeric():
                row_formatted.append(value)
            else:
                row_formatted.append(f'"{value}"')

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

        print(request)

        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