import sqlite3

class Database:
    def __init__(self, database):
        self.conn = sqlite3.connect(database)
        self.curs = self.conn.cursor() # Ausführung

        create_table = """
        CREATE TABLE IF NOT EXISTS user (
            user_id INTEGER PRIMARY KEY NOT NULL,
            vorname TEXT,
            nachname TEXT,
            position TEXT,
            berechtigung INTEGER
        )
        """

        self.curs.execute(create_table)
        self.conn.commit()

    def newuser(self, user_id, ber, vorname="", nachname="", pos=""):
        insert_user = """
        INSERT INTO user (user_id, vorname, nachname, position, berechtigung) VALUES (?,?,?,?,?)
        """
        try:
            self.curs.execute(insert_user, (user_id, vorname, nachname, pos, ber))
            self.conn.commit()
            return True, ""
        except Exception as e:
            return False, e

    def deleteuser(self, user_id):
        delete_user = """
        DELETE FROM user WHERE user_id = (?)
        """
        self.curs.execute(delete_user, (user_id,))
        self.conn.commit()
        return True

    def changepermission(self, user_id, ber):
        change_permission = """
        UPDATE user SET berechtigung = (?) WHERE user_id = (?)
        """
        if ber == 1 or ber == 0:
            self.curs.execute(change_permission, (ber, user_id))
            self.conn.commit()
            return True
        else:
            return False

    def getalluser(self):
        self.curs.execute("SELECT * FROM user")
        return self.curs.fetchall()

    def onlyoneuser(self, user_id):
        self.curs.execute("SELECT * FROM user WHERE user_id = (?)", (user_id,))
        return self.curs.fetchone()

if __name__ == "__main__":
    test = Database("user.db")
    test.newuser(3, 1, None)
    test.newuser(2, 0)
    print(test.newuser(1, 1, None))
    print(test.newuser(1, 1, None))
    print(test.deleteuser(3))
    print(test.changepermission(2, 1))
    print(test.getalluser())
    print(test.onlyoneuser(5))