import pygame, sys
import pygame.freetype
import time
import random
from pygame.locals import *
from tkinter import ttk
import tkinter
from tkinter import messagebox
import sqlite3


import inputbox #This is needed to add an interface where the user can input the anwser to the math question
import random #The function "random" is used in the get_single_question() to choose a random question from the database

questions_already_solved=[0] #Array needed when the user resolve a question, so it doesn't appear anymore

#SQL DATABASES~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
def SQLQuestion():
    with sqlite3.connect("games.db") as db:
        cursor = db.cursor()

    cursor.execute("""CREATE TABLE IF NOT EXISTS game (
                questionID integer PRIMARY KEY AUTOINCREMENT,
                question text,
                answer text
                )""")

def SQLUser():
    with sqlite3.connect("User.db") as db:
        cursor = db.cursor()

    cursor.execute("""CREATE TABLE IF NOT EXISTS user (
                userID INTEGER PRIMARY KEY,
                username VARCHAR(20) NOT NULL,
                password VARCHAR(20) NOT NULL,
                userscore INTEGER,
                usertime REAL
                )""")

#USER LOG IN/CREATE/DELETE ACCOUNT~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
def login(usernameLogin, passwordLogin,btn):
    while True:
        username = usernameLogin.get()#Asks for username
        password = passwordLogin.get()#Asks for password
        with sqlite3.connect("User.db") as db:#Creates a connection to database
            c = db.cursor()
        find_user = ("SELECT * FROM user WHERE username = ? AND password = ?")#Validates inputs for account
        c.execute(find_user,[(username),(password)])
        results = c.fetchall()#Fetches values from database

        if results:#Validates if the username/password is recognised
            for i in results:
                nameid = i[1]
                c.execute("SELECT userID FROM user WHERE username = ?",(nameid,))
                usID = c.fetchone()
                messagebox.showinfo("", "Welcome "+i[1]+"!")
                btn.destroy()
                QuestionMenu(usID)
            break

        else:
            messagebox.showinfo("", "Password and username is not recognised")
            break
        window.destroy()

def newUser(username1, password1):
    found = 0
    while found == 0:
        username = username1.get()
        with sqlite3.connect("User.db") as db:
            c = db.cursor()
        findUser = ("SELECT * FROM user WHERE username = ?")
        c.execute(findUser, [(username)])#Checks existence of username in database

        if c.fetchall():
            messagebox.showinfo("Username", "Username taken please try again.")
            break
        else:
            messagebox.showinfo("", "Account has been created!")
            found = 1

    password = password1.get()
    insertData = '''INSERT INTO user(username, password)
    VALUES(?,?)'''#Inserts new account into databse
    c.execute(insertData, [(username),(password)])
    db.commit()

def newUserTkinter():
    window = tkinter.Tk()
    window.title("Create new account")

    labelOne = ttk.Label(window, text = "Enter a username:")
    labelOne.grid(row = 0, column = 0)
    username1 = tkinter.StringVar(window)#value type is classified as a string
    usernameEntry = ttk.Entry(window, width = 30, textvariable = username1)
    usernameEntry.grid(row = 1, column = 0)

    labelTwo = ttk.Label(window, text = "Enter a password:")
    labelTwo.grid(row = 2, column = 0)
    password1 = tkinter.StringVar(window)#value type is classified as a string
    passwordEntry = ttk.Entry(window, width = 30, textvariable = password1)
    passwordEntry.grid(row = 3, column = 0)

    btn = ttk.Button(window, text="Submit", command=lambda: newUser(username1, password1))
    btn.grid(row = 3, column = 1)

def removeUser(usernameD, passwordD):
    exists = 0
    while exists == 0:#Validates exsistence of account username
        username = usernameD.get()
        password = passwordD.get()
        with sqlite3.connect("User.db") as db:
            c = db.cursor()
        findUser = ("SELECT * FROM user WHERE username = ?")
        c.execute(findUser, [(username)])

        if c.fetchall():
            messagebox.showinfo("Delete account", "Account deleted!")
            exists = 1
        else:
            messagebox.showinfo("", "Account does not exist")
            break

    remove_user = ("DELETE from user WHERE username = ? AND password = ?")
    c.execute(remove_user,[(username),(password)])
    db.commit()

def removeUserTkinter():
    window = tkinter.Tk()
    window.title("Delete account")

    labelOne = ttk.Label(window, text = "Enter account username:")
    labelOne.grid(row = 0, column = 0)
    usernameD = tkinter.StringVar(window)#value type is classified as a string
    usernameEntry = ttk.Entry(window, width = 30, textvariable = usernameD)
    usernameEntry.grid(row = 1, column = 0)

    labelTwo = ttk.Label(window, text = "Enter account password:")
    labelTwo.grid(row = 2, column = 0)
    passwordD = tkinter.StringVar(window)#value type is classified as a string
    passwordEntry = ttk.Entry(window, width = 30, textvariable = passwordD)
    passwordEntry.grid(row = 3, column = 0)

    btn = ttk.Button(window, text="Submit", command=lambda: removeUser(usernameD, passwordD))
    btn.grid(row = 3, column = 1)

def menu():
    with sqlite3.connect("User.db") as db:
        c = db.cursor()
    c.execute("SELECT * FROM user")
    print(c.fetchall())

    window = tkinter.Tk()
    window.title("Treasure Hunt Game!")

    labelOne = ttk.Label(window, text = """ ~~~~~~~~~~~~~ USER MENU ~~~~~~~~~~~~~
    """)#label displays instruction
    labelOne.grid(row = 0, column = 0)#places label in a grid

    btn = ttk.Button(window, text = "Create account", command = newUserTkinter)
    btn.grid(row = 1, column = 0)#places button in a grid

    btn = ttk.Button(window, text = "Delete account", command = removeUserTkinter)
    btn.grid(row = 2, column = 0)#places button in a grid

    labelTwo = ttk.Label(window, text = "Login to your account:")
    labelTwo.grid(row = 3, column = 0)

    usernameLogin = tkinter.StringVar(window)#value type is classified as a string
    usernameEntry = ttk.Entry(window, width = 30, textvariable = usernameLogin)
    usernameEntry.grid(row = 5, column = 0)

    labelTwo = ttk.Label(window, text = "Username")
    labelTwo.grid(row = 4, column = 0)

    passwordLogin = tkinter.StringVar(window)#value type is classified as a string
    passwordEntry = ttk.Entry(window, width = 30, textvariable = passwordLogin)
    passwordEntry.grid(row = 7, column = 0)

    labelTwo = ttk.Label(window, text = "Password")
    labelTwo.grid(row = 6, column = 0)

    btn = ttk.Button(window, text="Log in", command=lambda: login(usernameLogin, passwordLogin, window))
    btn.grid(row = 7, column = 1)

#SQL QUESTION ADD/REMOVE/GET~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
def insert_question(emp):
    conn = sqlite3.connect('games.db')
    c = conn.cursor()
    c.execute("INSERT INTO game VALUES (?, ?, ?)", (emp))
    conn.commit()

def get_question():
    conn = sqlite3.connect('games.db')
    c = conn.cursor()
    c.execute("SELECT * FROM game")
    return c.fetchall()

def get_number_total_question(): #Get the total number of question
    conn = sqlite3.connect('games.db')
    c = conn.cursor()
    c.execute("SELECT COUNT(*) FROM game")
    return c.fetchone()[0]

def get_single_question(question_number): #Get a question from the database
    conn = sqlite3.connect('games.db')
    c = conn.cursor()
    c.execute("SELECT question FROM game WHERE questionID="+str(question_number))
    return c.fetchone()[0]

def get_answer(question_number): #Get the answer from the database
    conn = sqlite3.connect('games.db')
    c = conn.cursor()
    c.execute("SELECT answer FROM game WHERE questionID="+str(question_number))
    return c.fetchone()[0]

def remove_question(emp):
    conn = sqlite3.connect('games.db')
    c = conn.cursor()
    c.execute("DELETE from game WHERE question = ?", [emp])
    conn.commit()

#Game Menu Tkinter~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
def showInstructions():
    messagebox.showinfo("Instructions", """You are a treasure hunter, your goal is to collect atleast 100
gold by the end of the game from treasure chests randomly scattered across the grid.There are 10 chests within a grid and
each treasure chest is worth 10 gold but can only be reclaimed 3 times before it is replaced by a bandit.
Landing on a bandit will cause you to lose all of your
gold and if all the chests have been replaced by bandits and you have less then 100 gold this means you lose!

Press enter to continue...""")#messagebox used for more simple functions (showing messages)

def showLeaderboard():
    messagebox.showinfo("Leaderboard", "shows leaderboard")

def con():
    messagebox.showinfo("Game", "Time to play!")
    pass
    game()

def showQuestions():
    emps = get_question()
    messagebox.showinfo("List of questions/answers", emps)

def AddQuestion(mathquestion, mathanswer):
    mathquestion1 = mathquestion.get()
    mathanswer1 = mathanswer.get()
    emp_1 = (None, mathquestion1, mathanswer1)
    insert_question(emp_1)
    messagebox.showinfo("Question inputed!")

    emps = get_question()
    print(emps)

def removeQuestion(DeleteQuestion):
    exists = 0
    while exists == 0:#Validates exsistence of question
        DeleteQuestion1 = DeleteQuestion.get()
        conn = sqlite3.connect('games.db')
        c = conn.cursor()
        findQuestion = ("SELECT * FROM game WHERE question = ?")
        c.execute(findQuestion, [(DeleteQuestion1)])

        if c.fetchall():
            messagebox.showinfo("Delete qustion","Question deleted!")
            exists = 1
        else:
            messagebox.showinfo("","Question does not exist")
            break

    remove_question(DeleteQuestion1)

def removeQuestionTk():
    window = tkinter.Tk()
    window.title("Remove a question.")

    labelOne = ttk.Label(window, text = "Enter question to remove:")
    labelOne.grid(row = 0, column = 0)
    DeleteQuestion = tkinter.StringVar(window)#value type is classified as a string
    questionEntry = ttk.Entry(window, width = 30, textvariable = DeleteQuestion)
    questionEntry.grid(row = 1, column = 0)

    btn = ttk.Button(window, text="Submit", command=lambda: removeQuestion(DeleteQuestion))
    btn.grid(row = 1, column = 1)

def QuestionMenu(getID):
    with sqlite3.connect("games.db") as db:
        c = db.cursor()

    with sqlite3.connect("user.db") as us:
        u = us.cursor()

    u.execute("SELECT username FROM user WHERE userID = ?",(getID))
    username = u.fetchone()[0]
    MenuName = ("Welcome",username,"!")

    window = tkinter.Tk()
    window.title("Treasure Hunt Game!")

    labelOne = ttk.Label(window, text = """ ~~~~~~~~~~~~~ GAME MENU ~~~~~~~~~~~~~
    """)#label displays instruction
    labelOne.grid(row = 0, column = 0)#places label in a grid

    labelname = ttk.Label(window, text = MenuName)
    labelname.grid(row = 1, column = 0)

    btn = ttk.Button(window, text = "View instructions", command = showInstructions)#label displays instruction
    btn.grid(row = 2, column = 0)#places button in a grid

    btn = ttk.Button(window, text = "View leaderboard", command = showLeaderboard)
    btn.grid(row = 3, column = 0)

    btn = ttk.Button(window, text = "View all questions", command = showQuestions)
    btn.grid(row = 4, column = 0)

    btn = ttk.Button(window, text = "Continue", command = con)
    btn.grid(row = 5, column = 0)

    labelTwo = ttk.Label(window, text = "Enter a math question:")
    labelTwo.grid(row = 6, column = 0)
    mathquestion = tkinter.StringVar()#value type is classified as a string
    userEntryQ = ttk.Entry(window, width = 30, textvariable = mathquestion)
    userEntryQ.grid(row = 7, column = 0)

    labelTwo = ttk.Label(window, text = "Enter the answer to this question:")
    labelTwo.grid(row = 8, column = 0)
    mathanswer = tkinter.StringVar()
    userEntryQ = ttk.Entry(window, width = 30, textvariable = mathanswer)
    userEntryQ.grid(row = 9, column = 0)

    btn = ttk.Button(window, text = "Submit", command=lambda: AddQuestion(mathquestion, mathanswer))
    btn.grid(row = 9, column = 1)

    btn = ttk.Button(window, text = "Remove a question", command = removeQuestionTk)
    btn.grid(row = 10, column = 0)#places button in a grid

SQLUser()
SQLQuestion()
menu()

#MAIN GAME~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

#colours
black = (0, 0, 0)
white = (255, 255, 255)
brown = (153, 76, 0)
blue  = (0, 0, 255)
grey  = (192,192,192)

#game dimensions (20,30,20) BASE VALUES
tilesize = 20
mapwidth = 30
mapheight = 20

coins = 0
ship = 1
water = 2
rock = 3
movesMade = 4
stopwatch = 5

inventory = {
    coins: 0,
    movesMade: 0,
    stopwatch: ""
    }

#position of the player
playerPos = [0,0]

resources = [coins, movesMade, stopwatch]


def game():
    pygame.init()
    pygame.display.set_mode((200,200))

    #dictionary for texture of the map
    textures = { #the transform function scales the photo to the tile size
    ship : pygame.transform.smoothscale(pygame.image.load('ship.png').convert_alpha(), (tilesize, tilesize)),
    water: pygame.transform.smoothscale(pygame.image.load('water.png'), (tilesize, tilesize)),
    rock: pygame.transform.smoothscale(pygame.image.load('rock.png').convert_alpha(), (tilesize, tilesize)),
    coins: pygame.transform.smoothscale(pygame.image.load('chest.png'), (tilesize, tilesize)),
    movesMade: pygame.transform.smoothscale(pygame.image.load('player.png'), (tilesize, tilesize)),
    stopwatch: pygame.transform.smoothscale(pygame.image.load('stopwatch.png'), (tilesize, tilesize))
    }

    #image that will represent player
    PLAYER = pygame.transform.smoothscale(pygame.image.load('player.png'), (tilesize, tilesize))

    #utilise list comprehension to create grid
    tilemap = [[water for w in range(mapwidth)] for h in range(mapheight)]

    #set up display
    displaysurf = pygame.display.set_mode((mapwidth*tilesize,mapheight*tilesize + 60))

    #Fonts used to display text
    font = pygame.freetype.SysFont('Times New Roman', 20)
    invfont = pygame.font.Font('FreeSansBold.ttf', 18)

    #loops through each row - Creates the game map
    coinstot=0 #TEST
    coins_per_row=0 #TEST
    for rw in range(mapheight):
        for cl in range(mapwidth):
            randomnumber = random.randint(0,15)
            if randomnumber == 0 or randomnumber == 1:
                tile = rock
            elif randomnumber == 2 or randomnumber == 3 :
                tile = ship
            #elif randomnumber == 4 and coins_per_row<1 and coinstot<=10 : #TEST
            #    tile = coins #TEST
            #    coins_per_row+=1 #TEST
            else:
                tile = water
            #sets position in the grid
            tilemap[rw][cl] = tile
        coinstot+=coins_per_row #TEST
        coins_per_row=0 #TEST

    visit = {}
    start_time = pygame.time.get_ticks() #Sets start time for game
    while True:
        displaysurf.fill(black) #background color

        #user events
        for event in pygame.event.get():
            if event.type == QUIT:
                pygame.quit()
                sys.exit()
            elif event.type == KEYDOWN:
                if event.key == K_RIGHT and playerPos[0] < mapwidth - 1:
                    playerPos[0] += 1
                if event.key == K_LEFT and playerPos[0] > 0:
                    playerPos[0] -= 1
                if event.key == K_UP and playerPos[1] > 0:
                    playerPos[1] -= 1
                if event.key == K_DOWN and playerPos[1] < mapheight -1:
                    playerPos[1] += 1
                if event.key == K_SPACE:
                    pos = (playerPos[1], playerPos[0])
                    if not pos in visit: # checks whether a tile has been used
                        visit[pos] = True
                        currentTile = tilemap[playerPos[1]][playerPos[0]]
                        if currentTile == rock:
                            inventory[movesMade] += 1
                            percent = 30 # coins are kept with a probability of 30 percent and will be lost by 70 percent
                            ran1 = random.randint(0,100) # random value in [0, 99]
                            if ran1 >= percent and inventory[coins] > 30:
                                inventory[coins] -= 30
                            else:
                                inventory[coins] += 20

                        elif currentTile == ship:
                            n_question=0 #The "0 question" doesn't exist, so I use that number to inizialize the variable

                            if (len(questions_already_solved)-1)!=get_number_total_question(): #COMMENT THIS LINE TO MAKE THE QUESTIONS REPEATABLE
                                while n_question in questions_already_solved: #COMMENT THIS LINE TO MAKE THE QUESTIONS REPEATABLE
                                    n_question=random.randint(1,get_number_total_question()) #INDENT THIS LINE CORRECTLY TO MAKE THE QUESTIONS REPEATABLE

                                question = get_single_question(n_question) #Fetches question from the Database
                                system_answer = get_answer(n_question) # Fetches answer of the question from the database

                                screen = pygame.display.set_mode((600, 460))

                                user_answer = inputbox.ask(screen, question)
                                if user_answer == system_answer: #If statement validates answer
                                    messagebox.showinfo("","Correct answer!")
                                    inventory[coins] += 10 #10 coins are added to the inventory
                                    questions_already_solved.append(n_question) #COMMENT THIS LINE TO MAKE THE QUESTIONS REPEATABLE

                                else: #TEST
                                    messagebox.showinfo("","Wrong answer!")

                            else:
                                screen = pygame.display.set_mode((600, 460))
                                user_answer = inputbox.ask(screen, "No more questions! Press ENTER to continue...")#COMMENT THIS LINE TO MAKE THE QUESTIONS REPEATABLE


        #loops through each row
        for row in range(mapheight):
            #loops through each column in row
            for column in range(mapwidth):
                displaysurf.blit(textures[tilemap[row][column]], (column*tilesize,row*tilesize))

        displaysurf.blit(PLAYER,(playerPos[0]*tilesize,playerPos[1]*tilesize))

        placePosition = 10
        for item in resources:
            displaysurf.blit(textures[item],(placePosition, mapheight*tilesize + 20))
            placePosition += 30
            #text displays amount of coin
            textObj = invfont.render(str(inventory[item]), True, white, black)
            displaysurf.blit(textObj,(placePosition, mapheight*tilesize + 20))
            placePosition += 50

        if inventory[coins] < 100:
            current_time = pygame.time.get_ticks() #Displays real time in milliseconds
            delta_time_s = (current_time - start_time) // 1000 #Convert from milliseconds to seconds

            text_surf, text_rect = font.render(str(delta_time_s), (255, 255, 255), size=30) #Renders time text to a surface object
            marginwidth = -30# margins to the window
            marginheight = -240
            size = (200, 200) # window size
            text_pos = (size[0] - text_rect.width - marginwidth, size[1] - text_rect.height - marginheight)

            displaysurf.blit(text_surf, text_pos)
        if inventory[coins] > 100:
            print(delta_time_s)
            break
        pygame.display.update()