db

import os
import sqlite3
import sign_up
import login
import game

def create():
    # Creates a database in RAM
    db = sqlite3.connect(':memory:')
    # Creates a file called users.sqlite with a SQLite3 DB
    db = sqlite3.connect('Dice_game/users.sqlite')

    # Get a cursor object
    cursor = db.cursor()
    cursor.execute('''
        CREATE TABLE users(username TEXT,
                            password TEXT,
                            highscore INTEGER,
                            wins INTEGER,
                            loses INTEGER)
                    ''') # Creates 5 values in the table
    for x in range(5):
        username = ("Player" + str((x+1)))
        password = "password"
        highscore = 0
        wins = 0
        loses = 0
        cursor.execute('''INSERT INTO users(username, password, highscore, wins, loses)
                        VALUES(?,?,?,?,?)''', (username, password, highscore, wins, loses))
    db.commit()
    db.close()
    game.menu()

def exist():
    filename = ("Dice_game/users.sqlite")
    db_exists = not os.path.exists(filename)
    db = sqlite3.connect(filename)

    if db_exists:
        create()
    else:
        game.menu()

    '''
    check if server exists
    if it does
        go to menu
    if not
    create db
    '''

def new(username, password):
    db = sqlite3.connect("Dice_game/users.sqlite") # Opens db
    cursor = db.cursor()
    username = username
    password = password
    highscore = 0
    wins = 0
    loses = 0

    cursor.execute('''INSERT INTO users(username, password, highscore, wins, loses)
                        VALUES(?,?,?,?,?)''', (username, password, highscore, wins, loses)) # Adds new data to the table
    db.commit() # Adds it to the db
    db.close() # Closes the db

def delete(username, password):
    db = sqlite3.connect("Dice_game/users.sqlite") # Opens db
    cursor = db.cursor()
    cursor.execute('''DELETE FROM users WHERE username = (?) AND password = (?)''', (username, password))
    db.commit() # Adds it to the db
    db.close() # Closes the db
    print("Account deleted")

def highscore(username, highscore):
    db = sqlite3.connect("Dice_game/users.sqlite")
    cursor = db.cursor()
    cursor.execute('''SELECT highscore FROM users WHERE username = ?''', (username,))
    user = cursor.fetchone()
    hs = user[0]

    if hs > highscore or hs == highscore:
        print("No knew highscore")
    else:
        update_hs(username, highscore)

def update_hs(username, highscore):
    db = sqlite3.connect("Dice_game/users.sqlite")
    cursor = db.cursor()
    cursor.execute('''UPDATE users SET highscore = ? WHERE username = ? ''', (highscore, username))
    db.commit()
    db.close()
    print("Highscore saved")

def update_p(password_new, username, password):
    db = sqlite3.connect("Dice_game/users.sqlite")
    cursor = db.cursor()
    cursor.execute('''UPDATE users SET password = ? WHERE username = ? AND password = ?''', (password_new, username, password))
    db.commit()
    db.close()
    print("Password changed")

def username_check(username):
    db = sqlite3.connect("Dice_game/users.sqlite")
    cursor = db.cursor()
    cursor.execute('''SELECT username FROM users WHERE username = ?''', (username,))
    user = cursor.fetchone()

    if user is None:
        print("Username available")
        sign_up.password(username)
        db.commit()
    else:
        print("This username already exists")
        sign_up.username()
        db.commit()

    db.close()


def info(username):
    db = sqlite3.connect("Dice_game/users.sqlite")
    cursor = db.cursor()
    cursor.execute('''SELECT username, highscore, wins, loses FROM users WHERE username = ?''', (username,))
    user = cursor.fetchone()
    print("Name: " + user[0] + "\nHighscore: " + str(user[1]) + "\nWins: " +str(user[2]) + "\n Loses: " + str(user[3]))

    db.commit()
    db.close()

def info_all():
    db = sqlite3.connect("Dice_game/users.sqlite")
    cursor = db.cursor()
    cursor.execute('''SELECT username, highscore FROM users''')
    all_rows = cursor.fetchall()
    all_rows = sorted(all_rows, key=lambda x: x[1], reverse=True)# sorts by second value in reverse order
    table = []
    for row in all_rows:
        # row[0] returns username, row[1] shows highscore
        table.append(f"{row[0]}: {row[1]}")
    for x in range(5):
        print(table[x])

    db.commit()
    db.close()


def log_in_1(username, password):
    db = sqlite3.connect("Dice_game/users.sqlite")
    cursor = db.cursor()
    cursor.execute('''SELECT username, password FROM users WHERE username = ? AND password = ?''', (username, password,))
    user = cursor.fetchone()

    if user is not None:
        print("Welcome to the Dice Game " + username + ", you are PLAYER 1")
        player1 = username
        login.login_2(player1)
    else:
        print("The username and password do not match, please try again")
        login.login_1()

    db.commit()
    db.close()


def log_in_2(player1, username, password):
    db = sqlite3.connect("Dice_game/users.sqlite")
    cursor = db.cursor()
    cursor.execute('''SELECT username, password FROM users WHERE username = ? AND password = ?''', (username, password,))
    user = cursor.fetchone()

    if user is not None:
        print("Welcome to the Dice Game " + username + ", you are PLAYER 2")
        game.game(player1, username)
    else:
        print("The username and password do not match, please try again")
        login.login_2(player1)

    db.commit()
    db.close()


def account(username, password):
    db = sqlite3.connect("Dice_game/users.sqlite")
    cursor = db.cursor()
    cursor.execute('''SELECT username, password FROM users WHERE username = ? AND password = ?''', (username, password,))
    user = cursor.fetchone()

    if user is not None:
        print("Welcome to the Dice Game, " + username + "\n")
        login.login_menu(username, password)
    else:
        print("The username and password do not match, please try again")
        login.account()

    db.commit()
    db.close()

def wins(username):
    db = sqlite3.connect("Dice_game/users.sqlite")
    cursor = db.cursor()
    cursor.execute('''Select wins FROM users WHERE username = ?''', (username,))
    user = cursor.fetchone()
    wins = user[0] + 1
    cursor.execute('''UPDATE users SET wins = ? WHERE username = ?''', (wins, username))
    db.commit()
    db.close()

def loses(username):
    db = sqlite3.connect("Dice_game/users.sqlite")
    cursor = db.cursor()
    cursor.execute('''Select loses FROM users WHERE username = ?''', (username,))
    user = cursor.fetchone()
    loses = user[0] + 1
    cursor.execute('''UPDATE users SET loses = ? WHERE username = ?''', (loses, username))
    db.commit()
    db.close()