# SQL CODE BEGINNING
import sqlite3

def getSQLfile(filename):
	f = open(filename,"r")
	sql = f.read()
	f.close()
	return sql

connection = sqlite3.connect("cycleclub.db")

pointer = connection.cursor()
pointer1 = connection.cursor()
pointer2  = connection.cursor()
pointer3 = connection.cursor()
pointer4 = connection.cursor()
pointer5 = connection.cursor

# different sql command, displays all data

print("Check if your username exists within the database")

usernameSearch = str(input("What username are you searching for? \n"))




SQL = ("SELECT Password From Members")
SQL1 = ("SELECT Username From Members")
SQL2 = ("SELECT Lock From Members")


pointer.execute(SQL)
pointer1.execute(SQL1)
pointer2.execute(SQL2)


results = pointer.fetchall()
results1= pointer1.fetchall()
results2 = pointer2.fetchall()


# SQL CODE END

output = []
passwords = []
locklevels = []

# adds the relevant fields to lists for comparrison
for row in results1:
	for field in row: # for every value in each column
		output.append(field)

for row in results:
	for field in row:
		passwords.append(field)

for row in results2:
	for field in row:
		locklevels.append(field)


maxlen = len(results1)
#maxlen = maxlen - 1

counter = 0
counter1 = 0
counter2 = 0

safeCounter = 4
maxlen = str(maxlen)

# Checks if user exists and if the account is locked
while True:
	breakprotocol = 0
	if str(counter) == str(maxlen):
		print("Username not found")
		exit()
	elif usernameSearch == output[counter]:
		print("User found")
		if str(locklevels[counter]) == "0":
			breakprotocol = 1
			break
		else:
			print("Your account is locked")
			exit()
	elif breakprotocol == 1:
		break
	else:
		counter = counter + 1
		pass

print("Welcome to the Yeadley Club, enter your password ")

# Password Check
while True:
	userPassword = input("")
	if userPassword != passwords[counter]:
		safeCounter = safeCounter - 1
		print("Incorrect password, {} attempts remaining".format(safeCounter))
		if safeCounter == 0:
			print("You're account is now locked. Contact an administrator. ")
			SQL4 = ("UPDATE Members SET Lock = '1' WHERE Username = '{}'".format(usernameSearch))
			pointer4.execute(SQL4)
			connection.commit()
			exit()
		else:
			pass
	elif userPassword == passwords[counter]:
		print("Welcome {}".format(usernameSearch))
		break
	else:
		safeCounter = safeCounter + 1

# Inner program
while True:
	print("What would you like to do? Press\n (1) to set lock level\n (2) to see members\n (3) to add a new user\n(4) to exit program")
	innerChoice = str(input(""))
	# change lock
	if innerChoice == "1":
		resetLock = input("Who would you like to reset? ")
		while True:

			if str(counter1) == str(maxlen):
				print("Username not found")
				counter1 = 0
				break
			elif resetLock == output[counter1]:
				print("User found")
				while True:
					newLock = str(input("Enter (1) for the account to be set to lock, (0) for unlocked "))
					if newLock == "1" or newLock == "0":
						newLock == int(newLock)
						break
					else:
						pass

				SQL3 = ("UPDATE Members SET Lock = '{}' WHERE Username = '{}'".format(newLock,resetLock))
				pointer3.execute(SQL3)
				connection.commit()
				print("Executed")
				break
			else:
				counter1 = counter1 + 1
				pass
	elif innerChoice == "2":
		print(output)
		print(locklevels)
		pass
	elif innerChoice == "3":
		firstName = input("Enter your first name ")
		lastName = input("Enter your surname ")
		while True:
			newUsername = input("Enter a unique username ")
			if newUsername == output[counter2]:
				print("Username not accepted")
				counter2 = counter2 + 1
				if str(maxlen) == str(counter2):
					break
				else:
					pass
				pass
			else:
				counter2 = counter2 + 1
				print("accepted")
		print("so far")
		break


	elif innerChoice == "4":
		exit()
	else:
		pass



connection.commit()
connection.close()