import sqlite3 as sql


class Database:
    def __init__(self, tables, name):
        self.name = name+'.db'
        self.db = sql.connect(self.name)
        self.cursor = self.db.cursor()
        self.cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
        db_tables = {table[0]: None for table in self.cursor.fetchall()}
        for table in db_tables:
            self.cursor.execute("SELECT * FROM %s" %table)
            db_tables[table] = [description[0] for description in self.cursor.description]
        for table, fields in tables.items():
            if table not in db_tables or len(tuple(set(fields).difference(set(db_tables[table]))) + tuple(set(db_tables[table]).difference(set(fields)))) > 0:
                self.delete_table(table, close=False)
                self.cursor.execute('''CREATE TABLE %s (%s)''' %(table, ", ".join(fields)))
        self.db.commit()
        self.db.close()

    def check_database_connection(self):
        try:
            self.cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
        except sql.ProgrammingError:
            self.db = sql.connect(self.name)
            self.cursor = self.db.cursor()

    def insert_column(self, table, values, close=True):
        self.check_database_connection()
        self.cursor = self.db.cursor()
        self.cursor.execute(f"INSERT INTO {table}({','.join(list(values.keys()))}) VALUES({','.join(['?' for _ in range(len(values))])})", list(values.values()))
        self.db.commit()
        if close:
            self.db.close()

    def get_column(self, table, columns, filter='', limit='', limitOffset='', close=True):
        self.check_database_connection()
        self.cursor = self.db.cursor()
        if filter != '':
            filter = f" WHERE {filter[0]} ='{filter[1]}'"
        if limit != '':
            limit = f" LIMIT {str(limit)}"
            if limitOffset != '':
                limitOffset = f" OFFSET {str(limitOffset)}"
        self.cursor.execute(f"SELECT {', '.join(columns)} FROM {table}" + filter + limit + limitOffset)
        columns = self.cursor.fetchall()
        if close:
            self.db.close()
        return columns

    def delete_column(self, table, filter, close=True):
        self.check_database_connection()
        self.cursor = self.db.cursor()
        self.cursor.execute(f"DELETE FROM {table} WHERE {filter[0]} = '{filter[1]}'")
        self.db.commit()
        if close:
            self.db.close()

    def delete_table(self, table, close=True):
        self.check_database_connection()
        self.cursor = self.db.cursor()
        self.cursor.execute("DROP table IF EXISTS %s" %table)
        self.db.commit()
        if close:
            self.db.close()

    def update_column(self, table, columnInfo, close=True):
        self.check_database_connection()
        self.cursor = self.db.cursor()
        self.cursor.execute(f"UPDATE {table} SET {columnInfo[0]} = '{columnInfo[1]}' WHERE {columnInfo[2]} = '{columnInfo[3]}'")
        self.db.commit()
        if close:
            self.db.close()