import sqlite3
from tkinter import ttk
import tkinter
from tkinter import messagebox

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
                )""")


def login(usernameLogin, passwordLogin):
    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:
                messagebox.showinfo("", "Welcome "+i[1]+"!")
            break

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

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="Submit", command=lambda: login(usernameLogin, passwordLogin))
    btn.grid(row = 7, column = 1)

menu()