Trumps

#  pick a random card from cards database

# Trumps type game

from random import randint
from time import time
import sqlite3

connex = sqlite3.connect("computer_cards.db") # mu connection to the computer_cards database

"""
def update_winner_loser(p1, p2, pw):
    insert_sql = "INSERT INTO result (p1, p2, pw) VALUES ('{}','{}','{}')".format(p1,p2,pw)
    connex.execute(insert_sql)
    connex.commit()
"""
# function to insert the card picked and time in the picked table
def insert_picked(name):
    insert_sql = "INSERT INTO picked(name, time) VALUES('{}',{})".format(name, time())
    connex.execute(insert_sql)
    connex.commit()

# function to read the last card added to the picked table
def read_last_picked():
    last_picked = connex.execute("SELECT * FROM picked ORDER BY time DESC")
    return last_picked.fetchone()

# function to pick a random number between 1 and the length of the table computer
def pick_rdm_index():  # this is going to count rows in our table
    rowcntsql = connex.execute("SELECT count (*) FROM computer") # counts lenght of computer table
    rowcntval = (rowcntsql.fetchall())  #comes back as a list with tuple inside
    #print(rowcntval) # testing
    lastrow = (rowcntval[0][0]) # visit the tuple on index 0 and bring back the list item at index [0]
    #print(type(lastrow)) # checking its an intger
    #print(lastrow)
    return (randint(1,lastrow)) # needs to start at row 1 as it is a basic list

# function to pick a random card, call the pick_rdm_index function above
def pick_random_card():
    last_picked_card = read_last_picked() # gets the last card picked
    rndrow = pick_rdm_index() # gets random index
    #print(rndrow) for testing
    sel_card_sql = "SELECT * FROM computer WHERE rowid = {} ".format(rndrow) # get the row that corresponds with the random index
    cardtgt = connex.execute(sel_card_sql)
    cardpicked=cardtgt.fetchone()
    #print(last_picked_card, cardpicked,"1") #testing while loop works
    while cardpicked[0] == last_picked_card[0]: # loop to test last card picked is not then picked again
        rndrow = pick_rdm_index()
        sel_card_sql = "SELECT * FROM computer WHERE rowid = {} ".format(rndrow)
        cardtgt = connex.execute(sel_card_sql)
        cardpicked=cardtgt.fetchone()
        #print(last_picked_card, cardpicked,"2") # tsting while loop works

    #print(cardpicked)
    #print(cardpicked[0])
    insert_picked(cardpicked[0]) # puts the card picked into the picked table
    return cardpicked

# function to get the last 2 cards picked and the winning card into the results table, if a draw ignore
def write_results(first,winner):
    last_picked = connex.execute("SELECT * FROM picked ORDER BY time DESC")
    last_card_picked = last_picked.fetchone()
    next_last_card_picked = last_picked.fetchone()
    print(first,winner)
    if first == "1":
        if winner == "W":
            insert_last_card_sql ="INSERT INTO result (card1, card2, winner) VALUES ('{}','{}', '{}')".format(next_last_card_picked[0],last_card_picked[0], next_last_card_picked[0])
            connex.execute(insert_last_card_sql)
            connex.commit()
        elif winner == "L":
            insert_last_card_sql ="INSERT INTO result (card1, card2, winner) VALUES ('{}','{}', '{}')".format(next_last_card_picked[0],last_card_picked[0], last_card_picked[0])
            connex.execute(insert_last_card_sql)
            connex.commit()
    elif first == "2":
        if winner == "W":
            insert_last_card_sql ="INSERT INTO result (card1, card2, winner) VALUES ('{}','{}', '{}')".format(next_last_card_picked[0],last_card_picked[0], next_last_card_picked[0])
            connex.execute(insert_last_card_sql)
            connex.commit()
        elif winner == "L":
            insert_last_card_sql ="INSERT INTO result (card1, card2, winner) VALUES ('{}','{}', '{}')".format(next_last_card_picked[0],last_card_picked[0], last_card_picked[0])
            connex.execute(insert_last_card_sql)
            connex.commit()

#  the game

player = input("Are you player (1) or player (2) >> ")

choosing_player = "1"

for round in range(3):
    input("Presss enter to pick a card when both players are ready >> ")
    card = pick_random_card()
    card_text = "{}, cores = {}, speed={}Ghz, RAM = {}MB, cost ={}$".format(card[0],card[1],card[2],card[3],card[4])
    print(card_text)
    winner = input("Did you win? (Y)es, (N)o, (D)raw >> ").lower()
    if winner == "y":
        if player == "1":
            write_results("1","W")
            choosing_player = player
        elif player =="2":
            write_results("2", "W")
            choosing_player = player

    elif winner == "n":
        if player == "1":
            write_results("2", "L")
            choosing_player = "2" if player =="1" else "1"
    elif player =="2":
            write_results("1","L")
            choosing_player = "2" if player =="1" else "1"
    elif winner == "d":
        pass
    print(f"Player {choosing_player} picks")

#print("interesting Stat", in)
most_wins_computer_name_sql = "SELECT winner, COUNT(winner) qty FROM result  GROUP BY winner HAVING COUNT (winner)=( SELECT MAX(mycount) FROM ( SELECT winner, COUNT(winner) mycount FROM result GROUP BY winner));"
intstat = connex.execute(most_wins_computer_name_sql)
output=intstat.fetchone()
print(f"Interesting Statistic the card that won the most was {output[0]} with {output[1]} items")