import sqlite3
import sys, time


def createConnection():
    """ create a database connection to the SQLite database
        specified by db_file
    :param db_file: database file
    :return: Connection object or None
    """
    try:
        return sqlite3.connect(db_file)
    except:
        return False

def clearScreen():
    print("\n" * 50)




def init():
    try:
        cursor = conn.cursor()

        cursor.execute('''
            CREATE TABLE IF NOT EXISTS users(
            userID INTEGER PRIMARY KEY,
            username VARCHAR(20) NOT NULL,
            firstname VARCHAR(20) NOT NULL,
            surname VARCHAR(20) NOT NULL,
            age INTEGER(2) NOT NULL,
            yeargroup VARCHAR(10) NOT NULL,
            password VARCHAR(20) NOT NULL);
            ''')

        cursor.execute('''
            CREATE TABLE IF NOT EXISTS topics(
            topicID INTEGER PRIMARY KEY,
            topicName VARCHAR(30) NOT NULL);
            ''')

        cursor.execute('''
            CREATE TABLE IF NOT EXISTS questions(
            questionID INTEGER PRIMARY KEY,
            topicID INTEGER NOT NULL,
            question VARCHAR(100) NOT NULL,
            option1 VARCHAR(50),
            option2 VARCHAR(50),
            option3 VARCHAR(50),
            option4 VARCHAR(50),
            answer VARCHAR(50),
            FOREIGN KEY(topicID) REFERENCES topics(topicID));
            ''')

        cursor.execute('''
            CREATE TABLE IF NOT EXISTS scores(
            scoreID INTEGER PRIMARY KEY,
            userID INTEGER NOT NULL,
            topicID INTEGER NOT NULL,
            score INTEGER NOT NULL,
            difficulty VARCHAR(20),
            grade VARCHAR(5),
            FOREIGN KEY(userID) REFERENCES users(userID),
            FOREIGN KEY(topicID) REFERENCES topics(topicID));
            ''')

#         cursor.execute('''
#             SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name;
#         ''')
#
#         print(cursor.fetchall())


    except:
        print("Couldn't create the tables in {}".format(db_file))


def login():
    cursor = conn.cursor()

#     username = input("Enter your username >> ")
#     password = input("Enter your password >> ")

    username = input("input username: ")
    password = input("input password: ")

    find_user = ('SELECT * FROM users WHERE username = ? AND password = ?')
    cursor.execute(find_user, [(username),(password)])
    results = cursor.fetchall()

    if results:
        for user in results:
            print("Welcome ", user[2], user[3])
            time.sleep(1)
            return user[0]
    else:
        return False

def getGrade(score):
    if score == 0:
        grade = "E"
    elif score <= 25:
        grade = "D"
    elif score <= 50:
        grade = "C"
    elif score <= 75:
        grade = "B"
    elif score <= 100:
        grade = "A"
    else:
        grade = "U"

    return grade

def getDifficultyLevelName(difficulty):
    if difficulty == 1:
        difficultyLevelName = "Easy"
    elif difficulty <= 2:
        difficultyLevelName = "Medium"
    elif difficulty <= 3:
        difficultyLevelName = "Hard"
    else:
        difficultyLevelName = "Unknown"

    return difficultyLevelName

def getQuestionOptions(question, difficulty):
    rightAnswerOption = question[7] # option number of the right answer
    rightAnswerIndex = int(rightAnswerOption) + 2 # index number of the right answer

    howManyOptions = difficulty + 1 #2 options for Easy, 3 options for Medium and 4 options for Hard

    if howManyOptions == 2:
        return ("1. {} \n2. {}\n".format(question[3],question[4]))
    elif howManyOptions == 3:
        return ("1. {} \n2. {} \n3. {} \n".format(question[3],question[4],question[5]))
    else:
        return ("1. {} \n2. {} \n3. {} \n4. {} \n".format(question[3],question[4],question[5],question[6]))


# allows a user to select a topic and difficulty rating (Easy, Medium or Hard) and asks five
# questions on that topic:
# a. ‘Easy’ mode has a choice of two answers for each question
# b. ‘Medium’ mode has a choice of three answers for each question
# c. ‘Hard’ mode has a choice of four answers for each question
# 4. loads the questions and answers from a file stored externally to the game.
# 5. displays the user’s score, percentage and grade achieved for that quiz.


def generateComputerQuestions():
    cursor = conn.cursor()

    cursor.execute('INSERT INTO topics (topicName) VALUES("History"), ("Music"), ("Computer Science");')
    conn.commit()


    cursor.execute ('''
    INSERT INTO questions (topicID, question, option1, option2, option3, option4, answer) VALUES
    ("1", "What type of storage is a Memory Stick?", "Solid State", "Magnetic", "Optical", "Volatile", "1"),
    ("1", "What type of storage is a CD?", "Solid State", "Magnetic", "Optical", "Volatile","3"),
    ("1", "What type of storage is a Hard Disk Drive?", "Solid State", "Magnetic", "Optical", "Volatile", "1"),
    ("1", "What type of storage is a SSD?", "Solid State" , "Magnetic", "Optical", "Volatile", "1"),
    ("2", "What is described as a network in one small geographical area?", "Ring" , "LAN", "Star", "WAN", "2"),
    ("2", "What is described as a network in a large geographical area?", "Ring", "LAN", "Star", "WAN", "4"),
    ("2", "Which topology requires a terminator?", "Bus", "Ring", "Star", "Mesh", "1"),
    ("2", "What type of software is most likely to be free?", "Open Source", "Proprietary", "Utility", "System", "1"),
    ("3", "What type of software is Automatic update?", "Open Source", "Proprietary", "Utility", "System", "3"),
    ("3", "What type of software is an Operating System?", "Open Source", "Proprietary", "Utility", "System", "4"),
    ("3", "Which of the following is sensitive data?", "DOB", "Name", "Political Opinion", "Address", "3"),
    ("3", "What law covers sensitive data?", "Data Protection Act", "Copyright,Designs and Patents", "Computer Misuse", "Freedom of Information", "1");
    ''')
    conn.commit()


# gives Fergus the option to generate and output the following reports:
# a. a report that allows Fergus to choose a username, and outputs all of the quizzes
# that they have taken, and the grade for each of those quizzes.
# b. a report that outputs for a selected topic and difficulty: the average score achieved,
# the highest score achieved, and the user details of the person that achieved the
# highest score.


def generateUserName(firstname, age):
    try:
        cursor = conn.cursor()
        username_taken = True

        while username_taken:
            username = firstname[:3] + str(age)
            print("Auto generated username: {}".format(username))

            find_user = ('SELECT * FROM users WHERE username = ?')
            cursor.execute(find_user, [(username)])

            if cursor.fetchall():
                print("Username taken.")
            else:
                username_taken = False
    except:
        print("Couldn't generate unique username.")

    finally:
        return username

def Generate():
    generateComputerQuestions()







def register():
    print("Sign up")

    firstname = input("Please enter your first name >> ")
    lastname = input("Please enter your last name >> ")
    age = int(input("Enter your age:   "))
    while (age<10) and (age>19) :
            print("invalid")
            age = input("what is ur age")



    username = generateUserName(firstname, age)
    yeargroup = input("Please enter your year group >> ")
    pwd1 = input("Please enter a password >> ")
    pwd2 = input("Please re-enter your password >> ")

    while pwd1 != pwd2:
        print("Passwords did not match...")
        pwd1 = input("Please enter a password >> ")
        pwd2 = input("Please re-enter your password >> ")

    insert_data = '''
    INSERT INTO users(username, firstname, surname, age, yeargroup, password)
    VALUES(?,?,?,?,?,?)'''

    cursor = conn.cursor()
    cursor.execute(insert_data, [(username), (firstname), (lastname), (age), (yeargroup), (pwd1)])
    conn.commit()

    print("New account created")
    input("Press Enter to go back to main menu")






def report():
    pass


def showMainMenu():
    print(" " *10, "MAIN MENU")
    print("-" * 32)
    print('Please choose one of the following options \n')
    print("1. Register")
    print("2. Login")
    print("3. Exit")


def showUserMenu():
    print(" " *10, "USER MENU")
    print("-" * 32)
    print('Please choose one of the following options \n')
    print("1. History quiz")
    print("2. Music quiz")
    print("3. Computer Science quiz")
    print("4. Show my scores")
    print("5. Report")
    print("6. Log out")


def takeQuiz(userID, topicID):
#     loadQuestionsAnswers(topic, difficulty)
#     runQuiz()
#     storeResults()
#     displayResults()
    cursor = conn.cursor()

    cursor.execute("SELECT * FROM topics WHERE topicID = ?;", [(topicID)])
    topicName = cursor.fetchall()
    clearScreen()
    print(topicName)
    #print("{} QUIZ\n".format(topicName[0][1]).upper())

    difficulty = int(input("Difficulty Level\n1. Easy\n2. Medium\n3. Hard\n>> "))
    print()

    score = 0
    topicID = difficulty
    cursor.execute("SELECT * FROM questions WHERE topicID = ?;", [(topicID)])
    questions = cursor.fetchall()
    #print(questions)
    numberOfQuestions = 0 #used to help work out the score / percentage

    for question in questions:
        print("Question: {}".format(question[2]))
        print(getQuestionOptions(question, difficulty +1))

        choice = input("Answer >> ")
        rightAnswerOption = question[7] # option number of the right answer
        rightAnswerIndex = int(rightAnswerOption) + 2 # index number of the right answer

        if choice == rightAnswerOption:
            print("Correct.\n")
            score += 1
            time.sleep(1)
            print()
        else:
            print("Incorrect. \n")
            print("Right answer: {}".format(question[rightAnswerIndex]))
            time.sleep(1)
            input()

    numberOfQuestions += 1

    score = int((score / numberOfQuestions))
    grade = getGrade(score)
    print("Your score was: {} which is grade {}".format(score, grade))

    insert_data = ("INSERT INTO scores(userID, topicID, score, difficulty, grade) VALUES (?,?,?,?,?);")
    cursor.execute(insert_data,[(userID), (topicID), (score), (getDifficultyLevelName(difficulty)), (grade)])
    conn.commit()
    input("Press Enter to go back to main menu")



def main():
    if conn:
        init()

        while True:
            clearScreen()
            showMainMenu()
            option = input(">> ")

            if option == "1":
                register()

            elif option == "2":
                user = login()
                if user:
                    while True:
                        clearScreen()
                        showUserMenu()
                        user_option =input(">> ")
                        Generate()
                        if user_option == "1":
                            takeQuiz(user, 1)

                        elif user_option == "2":
                            takeQuiz(user, 2)

                        elif user_option == "3":
                            takeQuiz(user, 3)

                        elif user_option == "4":
                            showScores(user)

                        elif user_option == "5":
                            report(user)

                        elif user_option == "6":
                            #LOG OUT AND GO BACK TO MAIN MENU LOOP
                            break
                else:
                    print("Invalid username or password.")
                    input("Press Enter to go back to main menu")

            elif option == "3":
#                 END OF MAIN MENU LOOP
                confirm = input("Do you really want to exit? (y/n)").lower()[0]
                if confirm == 'y':
                    sys.exit()

    else:
        print("Couldn't connect to {}".format(db_file))
        print("Goodbye...")
        sys.exit()


if __name__ == '__main__':
    db_file = "my_quiz.db"
    conn = createConnection()
    main()