import sqlite3

database = 'instance/sqlite3.db' # this is location of database

def schema():
    
    # Connect to the database file
    conn = sqlite3.connect(database)

    # Create a cursor object to execute SQL queries
    cursor = conn.cursor()
    
    # Fetch results of Schema
    results = cursor.execute("PRAGMA table_info('players')").fetchall()

    # Print the results
    for row in results:
        print(row)

    # Close the database connection
    conn.close()
    
schema()

import sqlite3

def read():
    # Connect to the database file
    conn = sqlite3.connect(database)

    # Create a cursor object to execute SQL queries
    cursor = conn.cursor()
    
    # Execute a SELECT statement to retrieve data from a table
    results = cursor.execute('SELECT * FROM players').fetchall()

    # Print the results
    if len(results) == 0:
        print("Table is empty")
    else:
        for row in results:
            print(row)

    # Close the cursor and connection objects
    cursor.close()
    conn.close()
    
read()

import sqlite3

from sqlalchemy import PickleType

def create():
    player_name = input("Enter a player name: ")
    team_name = input("Enter the player's team name: ")
    position = input("Enter the player's position: ")
    
    # Connect to the database file
    conn = sqlite3.connect(database)

    # Create a cursor object to execute SQL commands
    cursor = conn.cursor()

    try:
        # Execute an SQL command to insert data into a table
        cursor.execute("INSERT INTO players (_name, _team, _position, _likes, _dislikes) VALUES (?, ?, ?, ?, ?)", (player_name, team_name, position, 0, 0))
        
        # Commit the changes to the database
        conn.commit()
        print(f"A new user record {player_name} has been created")
                
    except sqlite3.Error as error:
        print("Error while executing the INSERT:", error)


    # Close the cursor and connection objects
    cursor.close()
    conn.close()
    
create()

import sqlite3

def updateLikes():
    pid = input("Enter player ID to update: ")
    likes = input("Enter new likes: ")
    
    # Connect to the database file
    conn = sqlite3.connect(database)

    # Create a cursor object to execute SQL commands
    cursor = conn.cursor()

    try:
        # Execute an SQL command to update data in a table
        cursor.execute("UPDATE players SET _likes = ? WHERE id = ?", (pid, likes))
        if cursor.rowcount == 0:
            # The uid was not found in the table
            print(f"No uid {pid} was not found in the table")
        else:
            print(f"The row with user id {pid} has the new likes: {likes}")
            conn.commit()
    except sqlite3.Error as error:
        print("Error while executing the UPDATE:", error)
    
    # Close the cursor and connection objects
    cursor.close()
    conn.close()

def updateDislikes():
    pid = input("Enter player ID to update: ")
    dislikes = input("Enter new dislikes: ")
    
    # Connect to the database file
    conn = sqlite3.connect(database)

    # Create a cursor object to execute SQL commands
    cursor = conn.cursor()

    try:
        # Execute an SQL command to update data in a table
        cursor.execute("UPDATE players SET _dislikes = ? WHERE id = ?", (pid, dislikes))
        if cursor.rowcount == 0:
            # The uid was not found in the table
            print(f"No uid {pid} was not found in the table")
        else:
            print(f"The row with user id {pid} has the new dislikes: {dislikes}")
            conn.commit()
    except sqlite3.Error as error:
        print("Error while executing the UPDATE:", error)
        
    # Close the cursor and connection objects
    cursor.close()
    conn.close()

updateLikes()
updateDislikes()

import sqlite3

def delete():
    pid = input("Enter user id to delete")

    # Connect to the database file
    conn = sqlite3.connect(database)

    # Create a cursor object to execute SQL commands
    cursor = conn.cursor()
    
    try:
        cursor.execute("DELETE FROM players WHERE id = ?", (pid))
        if cursor.rowcount == 0:
            # The uid was not found in the table
            print(f"No id {pid} was not found in the table")
        else:
            # The uid was found in the table and the row was deleted
            print(f"The row with id {pid} was successfully deleted")
        conn.commit()
    except sqlite3.Error as error:
        print("Error while executing the DELETE:", error)
        
    # Close the cursor and connection objects
    cursor.close()
    conn.close()

delete()
    

def menu():
    operation = input("Enter: (C)reate (R)ead (U)pdate or (D)elete or (S)chema")
    if operation.lower() == 'c':
        create()
    elif operation.lower() == 'r':
        read()
    elif operation.lower() == 'ul':
        updateLikes()
    elif operation.lower() == 'ud':
        updateDislikes()
    elif operation.lower() == 'd':
        delete()
    elif operation.lower() == 's':
        schema()
    elif len(operation)==0: # Escape Key
        return
    else:
        print("Please enter c, r, u, or d") 
    menu() # recursion, repeat menu
        
try:
    menu() # start menu
except:
    print("Perform Jupyter 'Run All' prior to starting menu")