# Declaration of libraries
import sqlite3 # Database
import getpass # Not displaying user input, unfortunately uncontrollable in IDLE's shell
import time # Used to pause the program
from statistics import mean # average score
#### Declaration of variables needed for manipulation of database
db = sqlite3.connect('usernames.db')
c = db.cursor()
##### Initialise tables if they don't already exist
c.execute("""CREATE TABLE IF NOT EXISTS details(username TEXT, name TEXT, surname TEXT, password TEXT, age INT)""")
c.execute("""CREATE TABLE IF NOT EXISTS questions(subject TEXT, question TEXT, answer1 TEXT, answer2 TEXT, answer3 TEXT, answer4 TEXT, correct TEXT)""")
c.execute("""CREATE TABLE IF NOT EXISTS scores(subject TEXT, username TEXT, score TEXT, totalquestions INT,percentage INT,difficulty TEXT,grade TEXT)""")
db.commit() # Save any changes

######## Functions ##############################
def ADD(data, table):
    #data: list/tuple (HOLDS DATA TO BE ADDED TO THE DATABASE)
    # table: string (USED TO SELECT THE TABLE TO BE ADDED TO)
    if table == "details":
        c.execute("""INSERT INTO details(username, name, surname, password, age) VALUES ('%s','%s','%s','%s','%s')"""%(data[0],data[1],data[2],data[3],data[4]))
        db.commit()
    if table == "questions":
        c.execute("""INSERT INTO questions(subject, question, answer1, answer2, answer3, answer4, correct) VALUES ('%s','%s','%s','%s','%s','%s','%s')"""%(data[0],data[1],data[2],data[3],data[4],data[5],data[6]))
        db.commit()
    if table == "scores":
        c.execute("""INSERT INTO scores(subject, username, score, totalquestions, percentage, difficulty,grade) VALUES ('%s','%s','%s','%s','%s','%s','%s')"""%(data[0],data[1],data[2],data[3],data[4],data[5],data[6]))
        db.commit()

def CHECK(username, password, option):
    # username: string (USERNAME TO BE CHECKED)
    # password: string (PASSWORD TO BE CHECKED FOR OPTION 1 )
    # option: integer (USED TO SELECT EITHER CHECKING FOR ONLY USERNAME (2) OR USERNAME AND PASSWORD
    # RETURNS: bool (whether record exists)
    if option == 1:
        c.execute("""SELECT * FROM DETAILS WHERE username='%s' AND
        password='%s'""" %(username,password))
    if option == 2:
        c.execute("""SELECT * FROM DETAILS WHERE username='%s'"""%(username))

    result = c.fetchone() # Returns the first line, it is a list
    if result != None :
        return True
    else:
        return None


def cAsker(message, check, rangeCheck):
    # message: string (MESSAGE TO BE OUTPUTTED)
    # check: boolean (SET TO TRUE IF NUMBER HAS TO BE IN SPECIFIC RANGE
    # rangeCheck: string (AN EXPRESSION THAT IS EXECUTED, SHOULD CHECK FOR CERTAIN RANGE OF CHOICE)
    # RETURNS: choice
    if check == False:   # Execute this if size of integer doesn't matter
        while True:
            try:
                choice = int(input(message))
            except ValueError:
                print("Please enter an integer")
            else:
                break
    if check == True:
        while True: # Execute this if size of integer does matter
            try:
                choice = int(input(message))
            except ValueError:
                print("Please enter an integer")
            else:
                if not (eval(rangeCheck)): #Inverses rangeCheck, rangeCheck sees if code is not in range
                    break
                else:
                    print("Please select one of the choices")
    return choice # Function returns the integer

def returnGrade(percentage):
    # percentage: integer (PERCENTAGE TO CHECK THE GRADE FOR)
    rounded = round(percentage,-1)
    switcher = {100: "A**", 90: "A*", 80: "A", 70: "B",  60: "C",
                50: "D",  40: "E",  30: "F",  20: "G", 10: "U",  0: "U-"}
    return switcher.get(rounded,'') #Retrieves the value of the dictionary with the same key as rounded


#########################################################
#################### SECTION 1 ##########################
# Function that will hold all of the code for the login menu
# RETURNS True if CHECK(username, password, 1) == True OR
#   if username == adminDetails[0] and password == adminDetails[1]
def credentials():
    global ausername # Provides user for scores to be added and retrieved later & admin menu authentication
    #####
    correct = '' # Tracks user input for confirmation
    confirmation = True # Used to keep user in first choice with their consent
    ############### Wipes all variables clean so no previous data gets used #######
    print("======== Start Menu ========")
    option = cAsker("\n1) Register \n2) Login \n>.. ", True,"choice < 1 or choice > 2") # choice option

    if option == 1: # Registration option
            while True: # Presence check on details[0], sname, password
                details = [input("What is your first name?: "), input("What is your surname?: "),
                           getpass.getpass("What will your password be?: "), cAsker("How old are you?", False,"choice < 0")]
                if isinstance(details[0], int) == False and isinstance(details[1], int) == False and isinstance(details[2], int) == False:
                    break
            # Carries on with rest of registration only if the user enters inputs
            if correct != "no":
                # Assigns value of username as normal if there are enough characters
                if len(details[0]) >= 3:
                    username = (details[0][:3] + str(details)).lower()
                # Deals with first name being less than 3
                elif len(details[0]) == 2:
                    username = (details[0] + details[1][0] + str(details)).lower()
                else:
                    username = (details[0] + details[1][:2] + str(details)).lower()

                data = (username, details[0], details[1], details[2], details[3]) # holds all data to be added to database
                if CHECK(username, False, 2) == True: # Informs user that username existing
                    print("""Sorry, that username already exists.
                    Please try another name (your middle name?)""")
                else: # Otherwise add details to the database
                    correct = cAsker(("Username: ",username,"\nFirst name: "+ details[0],"\nSurname: " + details[1] + "Age: ",details[3] + "\n1) Confirm \n2) Decline "), True, "choice < 1 or choice > 3" )
                    if correct == 2: # Leaves option
                        option = 0
                    elif correct == 1:
                        ADD(data, "details")
                confirmation = False # Exits this loop.

    if option == 2: # Login option
        username = input("What is your username?: ")
        password = getpass.getpass("What is your password?: ")
        print("\n\n")
        print("Authenticating...")
        for x in range(3): # Prints dots for tension and to make it clearer so more user-friendly
            print(".")
            time.sleep(0.5)
        # Checks to see if the username and password are admin credentials, if they are then assign
        # global variable ausername to the current username and return True to move to next loop
        if username == adminDetails[0] and password == adminDetails[1]:
            ausername = username
            return True

        if CHECK(username, password, 1) == None: # Code to run if combination not found
            time.sleep(0.5)
            print("That account does not exist. Have you registered?")
            time.sleep(0.5)
        # If username and password exist but are not admin
        elif CHECK(username, password, 1) == True:
            time.sleep(0.5)
            print("Authenticated, welcome back!\n")
            time.sleep(0.5)
            ausername = username
            return True # Move to next loop


###########################################################
################## SECTION 2 ##############################
# Function that will hold all of the code for the main menu
# RETURNS False if choice == 3
def main_menu():
    # Checks if admin credentials have been entered
    if ausername == adminDetails[0]:
        while True:
            choice = admin_menu()
            if choice == 3: # Breaks out of infinite loop if admin menu returns 3
                break
    else: # Executes if user is not admin
        print("======== Quiz Menu ========")
        choice = cAsker("1) Take a quiz \n2) View your previous quizzes \n3) Log out\n>.. ", True,"choice < 1 or choice > 3")
    # Option to take a quiz
    if choice == 1:
        print("======== Take a quiz ======")
        choice = cAsker("""1) Music \n2) Computer Science \n>.. """,
                        True,"choice < 1 or choice > 2")
        difficulty = cAsker("1) Easy \n2) Medium \n3) Hard \n>.. ",
                            True,"choice < 1 or choice > 3")
        # Sets subjects and informs user of choice
        if choice == 1:
            print("You have chosen to take a Music test.")
            subject = "Music"
        else:
            print("You have chosen to take a Computer Science test.")
            subject = "Computing"

        c.execute("SELECT * FROM questions WHERE subject='%s'"%(subject)) # Returns all questions
        confirmation = cAsker("Do you want to continue? \n1) Yes \n2) No \n>..",
                              True, "choice < 1 or choice > 2") # Confirms if user wants to do it
        if confirmation == 1: # Code to execute if user confirmed choices
            totalscore = 0 # Resetting score to 0
            results = c.fetchall() # Stores all questions in variable (2D Array)
            # Changes iteration (number of questions) value depending on
            if difficulty == 3:
                iterations = len(results)
            elif difficulty == 2:
                iterations = (len(results) - 5)
            else:
                iterations = (len(results) - 7)
            for x in range(iterations):
                print(results[x][1])
                for y in range(4):
                    print(results[x][y+2])
                userAnswer = cAsker(">..", True,"choice > 4 or choice < 1")
                userAnswer = str(userAnswer)
                if userAnswer == results[x][6]:
                    totalscore += 1
            percentage = int(round(totalscore / iterations * 100))
            possibleGrade = 100
            if difficulty == 1:
                possibleGrade = percentage - 20
            elif difficulty == 2:
                possibleGrade = percentage - 10

            userGrade = returnGrade(possibleGrade)
            print("You got: ",totalscore," correct. \n That is ",percentage, "Percent. \n Your Grade is: ",userGrade)
            grades = {1: "Easy",2: "Medium",3:"Hard"}
            difficulty = grades.get(difficulty,'')
            data = (subject,ausername,totalscore,iterations,percentage,difficulty,userGrade)
            ADD(data,"scores")
            choice = 0
        else:
            choice = 0

    if choice == 2:
        c.execute("SELECT * FROM scores WHERE username='%s'"%(ausername))
        results = c.fetchall()
        print("======== Scores ===========")
        for x in (results):
            print("\nSubject: ",x[0],"\nDifficulty: ",x[5],"\nScore:" ,x[2],"/",x[3],"\nPercentage: ",round(x[4]),"\nGrade: ",x[6])
        if results == []:
            print("No previous quiz history! \n")

    if choice == 3:
        print("\n")
        return False

#########################################################
#################### SECTION 3 ##########################

def admin_menu():
    print("======== Admin Menu ========")
    choice = cAsker("\n1) Generate report from username \n2) Generate report for topic and difficulty \n3) Logout\n>.. ", True,"choice < 1 or choice > 3")
    if choice == 1: # Quiz history of a specific user
        username = input("What is the username? \n>.. ")
        c.execute("""SELECT * FROM scores WHERE username='%s'""" %(username))
        results = c.fetchall()
        if results == []: # If there is no quiz history or user doesn't exist
            print("User not found or no previous quizzes have been completed.\n")
        else: # Executes if there are scores
            print("======== Scores ===========")
            for x in results:
                print("\nSubject: ",x[0],"\nDifficulty: ",x[5],"\nScore:",x[2],"/",x[3],"\nPercentage: ",round(x[4]),"\nGrade: ",x[6])

    if choice == 2: # Average score fom a subject & difficulty
        subject = cAsker("Please select a subject to get the average scores from.\n1) Music \n2) Computer Science\n>..", True,"choice < 1 or choice > 2")
        difficulty = cAsker("Please select a difficulty\n1) Easy \n2) Medium \n3) Hard \n>..", True,"choice < 1 or choice > 3")
        difficulties = {1: "Easy", 2: "Medium", 3: "Hard"}
        difficulty = difficulties.get(difficulty, '')
        # Only returns score column from table
        if subject == 1:
            c.execute("SELECT score FROM scores WHERE subject='Music' AND difficulty='%s'"%(difficulty))
        else:
            c.execute("SELECT score FROM scores WHERE subject='Computing' AND difficulty='%s'"%(difficulty))
        results = c.fetchall()
        # Calculates average score
        if results != []:
            numbers = [int(x) for y in results for x in y]
            average = mean(numbers)
            c.execute('SELECT * FROM scores ORDER BY score DESC')
            results = c.fetchall()
            # Removes all sublists which do not have x[0] == (desired subject)
            if subject == 1:
                filtered = list(filter(lambda x: x[0] == "Music",results))
            else:
                filtered = list(filter(lambda x: x[0] == "Computing",results))
            highestScore = filtered[0]
            print("======== Scores ===========")
            print("Average Score: ",average,"\nHighest Scorer:",highestScore[1],"\nScore: ",highestScore[2],"/",highestScore[3],"\nPercentage: ",round(highestScore[4]),"\nGrade: ",highestScore[6])

        else: # Executes if there is no previous history (results == [])
            print("======== Scores ===========")
            print("No Data available for that!")

    if choice == 3:
        return 3


############# Variable Declaration
adminDetails = ["admin","fergus123"]
access = None # Keeps the program inside each section


############# Main loop/logic of the code
while True:
    while access == None:
            access = credentials()
    if main_menu() == False:
        access = None
db.commit()