import sqlite3
from time import time
from random import randint

conn = sqlite3.connect("computer_cards.db")

def read_all_cards():
    result = conn.execute("SELECT * FROM computer")
    return result.fetchall()

def pick_card():
    cards = read_all_cards()
    last_picked_card = read_last_picked()
    random_card = cards[randint(0, len(cards) - 1)]
    while random_card[0] == last_picked_card[0]:
        random_card = cards[randint(0, len(cards) - 1)]
    insert_picked(random_card[0])
    return random_card

def insert_picked(name):
    insert_sql = "INSERT INTO picked(name, time) VALUES ('{}', {})".format(name, time())
    conn.execute(insert_sql)
    conn.commit()

def insert_winner(name):
    result = conn.execute("SELECT * FROM picked ORDER BY time DESC")
    winner= result.fetchone()
    ##print("Winner =",winner)
    loser=result.fetchone()
    ##print("Winner =",loser)
    ##print("Card1 =",name)
    insert_sql="INSERT INTO result(card1,card2,winner) VALUES ('{}','{}','{}')".format(name,loser[0],winner[0])
    conn.execute(insert_sql)
    conn.commit()

def stat_winner(name):
    stat=0
    result = conn.execute("SELECT * FROM result WHERE winner='{}'".format(name))
    print("The winner card has won {} times up to now.".format(len(result.fetchall())))


def read_last_picked():
    result = conn.execute("SELECT * FROM picked ORDER BY time DESC")
    return result.fetchone()

choosing_player = "1"

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

for round in range(5):
    input("Press enter to pick a card when both players are ready >")

    card = pick_card()
    card_text = "{}, cores={}, speed={}GHz, RAM={}MB, cost={}$".format(card[0], card[1], card[2], card[3], card[4])
    print(card_text)
    print("___________________________________________")
    print("|   {:<10}{:^20}".format("Name = ",card[0]))
    print("|   {:<10}{:^5}".format("Cores = ",card[1]))
    print("|   {:<10}{:^5}  GHz".format("CPU SPeed= ", card[2]))
    print("|   {:<10}{:^5}  MB".format("RAM = ", card[3]))
    print("|   {:<10}{:^5}  €".format("Cost = ",card[4]))
    print("___________________________________________")
    print("Player " + choosing_player + " picks.")
    winner = input("Did you win? (Y)es, (N)o, (D)raw >").lower()
    if winner == "y":
        choosing_player = player
        insert_winner(card[0])
        stat_winner(card[0])
    elif winner == "n":
        choosing_player = "2" if player == "1" else "1"



conn.close()