import psycopg2

class postgresConnection:

    lockingVals = [1000]
    noLockingVals = [1000]

    def __init__(self):
        try:
            self.connection = psycopg2.connect(
                host="localhost",
                user="postgres",
                database="turingDB",
                password="123"
            )
            self.connection.autocommit = True
            self.cursor = self.connection.cursor()
            self.locked = False
            self.step = 0  # required to handle locking for the transaction schedule
        except:
            print("Database connection failed!")


    def create_turingBank(self):
        sql_command = "CREATE TABLE TuringBanks ( " \
                      "ID INTEGER PRIMARY KEY, " \
                      "Bank VARCHAR(100), " \
                      "Value INTEGER " \
                      ");"
        try:
            self.cursor.execute(sql_command)
        except:
            print("Table TuringBanks already exists!")

    def drop_bank(self):
        sql_command = "DROP TABLE turingbanks;"
        try:
            self.cursor.execute(sql_command)
        except:
            print("Cannot delete a Table that doesn't exist!")

    def table_startValues(self):
        sql_command = "INSERT INTO Public.turingbanks " \
                      "VALUES" \
                      "(1, 'Bank1', 1000), " \
                      "(2, 'Bank2', 1000);"
        try:
            self.cursor.execute(sql_command)
        except:
            print("Values already inserted!")

    # HERE ARE THE DIFFERENT ACTIONS FOR EACH TRANSACTION

    ##################### BEGIN TESTING SECTION ###########################

    # def add_50_bank1(self):
    #     sql_command = "UPDATE turingbanks " \
    #                   "SET value = value + 50 " \
    #                   "WHERE id = 1"
    #     self.cursor.execute(sql_command)
    #
    # def add_100_bank1(self):
    #     sql_command = "UPDATE turingbanks " \
    #                   "SET value = value + 100 " \
    #                   "WHERE id = 1"
    #     self.cursor.execute(sql_command)
    #
    # def add_50_bank2(self):
    #     sql_command = "UPDATE turingbanks " \
    #                   "SET value = value + 50 " \
    #                   "WHERE id = 2"
    #     self.cursor.execute(sql_command)
    #
    # def add_100_bank2(self):
    #     sql_command = "UPDATE turingbanks " \
    #                   "SET value = value + 100 " \
    #                   "WHERE id = 2"
    #     self.cursor.execute(sql_command)
    #
    # def sub_50_bank1(self):
    #     sql_command = "UPDATE turingbanks " \
    #                   "SET value = value - 50 " \
    #                   "WHERE id = 1"
    #     self.cursor.execute(sql_command)
    #
    # def sub_100_bank1(self):
    #     sql_command = "UPDATE turingbanks " \
    #                   "SET value = value - 100 " \
    #                   "WHERE id = 1"
    #     self.cursor.execute(sql_command)
    #
    # def sub_50_bank2(self):
    #     sql_command = "UPDATE turingbanks " \
    #                   "SET value = value - 50 " \
    #                   "WHERE id = 2"
    #     self.cursor.execute(sql_command)
    #
    # def sub_100_bank2(self):
    #     sql_command = "UPDATE turingbanks " \
    #                   "SET value = value - 100 " \
    #                   "WHERE id = 2"
    #     self.cursor.execute(sql_command)

    ##################### END TESTING SECTION ###########################

    # FIRST VERSION
    # def add_val_bank1(self, value: int):
    #     sql_command = "UPDATE turingbanks " \
    #                   "SET value = " + str(self.get_val_bank1()) + " + " + str(value[0]) + " WHERE id = 1"
    #     self.cursor.execute(sql_command)
    #     self.lockingVals.append(self.get_val_bank1())
    #
    # FIRST VERSION
    # def sub_val_bank1(self, value: int):
    #     sql_command = "UPDATE turingbanks " \
    #                   "SET value = " + str(self.get_val_bank1()) + " - " + str(value[0]) + " WHERE id = 1"
    #     self.cursor.execute(sql_command)
    #     self.lockingVals.append(self.get_val_bank1())

    # FIRST VERSION
    def add_val_bank2(self, value: int):
        cur_val = self.get_val_bank2()
        sql_command = "UPDATE turingbanks " \
                      "SET value = " + str(cur_val) + " + " + str(value[0]) + " WHERE id = 2"
        self.cursor.execute(sql_command)
        self.noLockingVals.append(cur_val + value[0])
    # FIRST VERSION
    def sub_val_bank2(self, value: int):
        cur_val = self.get_val_bank2()
        sql_command = "UPDATE turingbanks " \
                      "SET value = " + str(cur_val) + " - " + str(value[0]) + " WHERE id = 2"
        self.cursor.execute(sql_command)
        self.noLockingVals.append(cur_val - value[0])

    def add_val_bank1(self, value: int, pos: int):
        while 1:
            if pos == self.step:
                sql_command = "UPDATE turingbanks " \
                              "SET value = " + str(self.get_val_bank1()) + " + " + str(value[0]) + " WHERE id = 1"
                self.cursor.execute(sql_command)
                self.lockingVals.append(self.get_val_bank1())
                self.step += 1
                break
            else:
                continue

    def sub_val_bank1(self, value: int, pos: int):
        while 1:
            if pos == self.step:
                sql_command = "UPDATE turingbanks " \
                              "SET value = " + str(self.get_val_bank1()) + " - " + str(value[0]) + " WHERE id = 1"
                self.cursor.execute(sql_command)
                self.lockingVals.append(self.get_val_bank1())
                self.step += 1
                break
            else:
                continue

    # def add_val_bank2(self, value: int):
    #     sql_command = "UPDATE turingbanks " \
    #                   "SET value = " + str(self.get_val_bank2()) + " + " + str(value[0]) + " WHERE id = 2"
    #     self.cursor.execute(sql_command)
    #     self.noLockingVals.append(self.get_val_bank2())
    #
    # def sub_val_bank2(self, value: int):
    #     sql_command = "UPDATE turingbanks " \
    #                   "SET value = " + str(self.get_val_bank2()) + " - " + str(value[0]) + " WHERE id = 2"
    #     self.cursor.execute(sql_command)
    #     self.noLockingVals.append(self.get_val_bank2())

    def get_val_bank1(self) -> int:
        sql_command = "SELECT value FROM turingbanks " \
                      "WHERE id = 1"
        cursor = self.connection.cursor()
        cursor.execute(sql_command)
        value = cursor.fetchone()[0]
        return value

    def get_val_bank2(self) -> int:
        sql_command = "SELECT value FROM turingbanks " \
                      "WHERE id = 2"
        cursor = self.connection.cursor()
        cursor.execute(sql_command)
        value = cursor.fetchone()[0]
        return value

    def reset_lockingvals(self):
        self.lockingVals = []

    def reset_nolockingvals(self):
        self.noLockingVals = []

    def lock(self):
        self.locked = True

    def unlock(self):
        self.locked = False

    def reset_table(self):
        self.drop_bank()
        self.create_turingBank()
        self.table_startValues()
        self.step = 0









