import operator
import sqlite3
from random import randint
import sys
from time import time

"""
24-NOV-2019
3.11 Assignment
"""

# verbose == true : activate logging for the SQL statements
verbose = False
# verbose = True

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


def log(msg):
    """log messages for debugging"""
    if verbose:
        print("LOG:", msg)


def read_all_cards():
    """Read all cards from table computer"""
    sql = "SELECT * FROM computer"
    log("SQL:>>>>>" + sql + "<<<<<")
    result = conn.execute(sql)
    return result.fetchall()


def insert_picked(name):
    """add a card to table picked"""
    sql = "INSERT INTO picked(name, time) VALUES ('{}', {})".format(name, time())
    log("SQL:>>>>>" + sql + "<<<<<")
    conn.execute(sql)
    conn.commit()


def read_last_picked():
    """get the last card added to table picked"""
    sql = "SELECT * FROM picked ORDER BY time DESC"
    log("SQL:>>>>>" + sql + "<<<<<")
    result = conn.execute(sql)
    return result.fetchone()


def pick_card():
    """choose a card and add it to table picked"""
    last_picked_card = read_last_picked()
    log("last_picked_card:")
    log(last_picked_card)

    random_card = cards[randint(0, len(cards) - 1)]

    if last_picked_card is not None:
        while random_card[0] == last_picked_card[0]:
            random_card = cards[randint(0, len(cards) - 1)]

    insert_picked(random_card[0])
    log("picked card:")
    log(random_card)
    return random_card


def read_last2_picked():
    """get the last 2 cards added to table picked"""
    sql = "SELECT * FROM picked ORDER BY time DESC"
    log("SQL:>>>>>" + sql + "<<<<<")
    result = conn.execute(sql)
    # card played 2nd
    r1 = result.fetchone()
    # card played 1st
    r2 = result.fetchone()
    return [r1, r2]


def add_to_result(card1_name, card2_name, winner_name):
    """add the game result of a round to table result"""
    sql = "INSERT INTO result(card1, card2, winner) VALUES ('{}', '{}', '{}')".format(card1_name, card2_name, winner_name)
    log("SQL:>>>>>" + sql + "<<<<<")
    conn.execute(sql)
    conn.commit()


def print_statistics():
    """collect and print all available statistics data"""
    sql = "SELECT * FROM result"
    log("SQL:>>>>>" + sql + "<<<<<")
    result = conn.execute(sql)
    results = result.fetchall()

    if results is None:
        print("")
        print("Game Statistics")
        print("---------------")
        print("Number of results so far: 0")
        print("---------------")
        return

    # use a dictionary for each field in table result
    # key: card, value: count (how often was this card found)
    dict_card1 = {}
    dict_card2 = {}
    dict_winner = {}
    for result in results:
        card1 = result[0]
        card2 = result[1]
        winner = result[2]
        if card1 in dict_card1:
            dict_card1[card1] = dict_card1[card1] + 1
        else:
            dict_card1[card1] = 1
        if card2 in dict_card2:
            dict_card2[card2] = dict_card2[card2] + 1
        else:
            dict_card2[card2] = 1
        if winner in dict_winner:
            dict_winner[winner] = dict_winner[winner] + 1
        else:
            dict_winner[winner] = 1

    # now we have to revert sort the dictionaries by the values (i.e. counter)
    # the following code is a very short solution of this problem
    # source of the dict sort by value code:
    # Thomas Cokelaer
    # How to sort a dictionary by values in Python
    # https://thomas-cokelaer.info/blog/2017/12/how-to-sort-a-dictionary-by-values-in-python/
    sorted_dict_card1 = sorted(dict_card1.items(), key=operator.itemgetter(1), reverse=True)
    sorted_dict_card2 = sorted(dict_card2.items(), key=operator.itemgetter(1), reverse=True)
    sorted_dict_winner = sorted(dict_winner.items(), key=operator.itemgetter(1), reverse=True)

    print("")
    print("Game Statistics")
    print("---------------")
    print("Number of results so far: {}".format(len(results)))
    print("")
    print("+---------------------------------------------------+")
    print("|           WINNER HIGHSCORE LIST                   |")
    print("+---------------------------------------------------+")
    print("|   hits | card                                     |")
    print("+--------+------------------------------------------+")
    for k,v in sorted_dict_winner:
        print("| {:6} | {:40} |".format(v, k))
    print("+---------------------------------------------------+")
    print("")
    print("+---------------------------------------------------+")
    print("|            CARD1 HIGHSCORE LIST                   |")
    print("+---------------------------------------------------+")
    print("|   hits | card                                     |")
    print("+--------+------------------------------------------+")
    for k,v in sorted_dict_card1:
        print("| {:6} | {:40} |".format(v, k))
    print("+---------------------------------------------------+")
    print("")
    print("+---------------------------------------------------+")
    print("|            CARD2 HIGHSCORE LIST                   |")
    print("+---------------------------------------------------+")
    print("|   hits | card                                     |")
    print("+--------+------------------------------------------+")
    for k,v in sorted_dict_card2:
        print("| {:6} | {:40} |".format(v, k))
    print("+---------------------------------------------------+")


while True:
    player = input("Are you player (1) or (2) (or q for quit)>")
    if player in ["q", "Q"]:
        sys.exit(0)
    if player in ["1", "2"]:
        break

cards = read_all_cards()
choosing_player = "1"
rounds = 5
for r in range(rounds):
    resp = input("Press enter to pick a card when both players are ready (or q to quit)>")
    if resp in ["q", "Q"]:
        break

    card = pick_card()
    print("picked card:")
    print("{:10}: {}".format("name", card[0]))
    print("{:10}: {}".format("cores", card[1]))
    print("{:10}: {}".format("cpu_speed", card[2]))
    print("{:10}: {}".format("ram", card[3]))
    print("{:10}: {}".format("cost", card[4]))

    if player == "1":
        print("Player " + choosing_player + " picks.")
        winner = input("Did you win? (Y)es, (N)o, (D)raw >").lower()

        last_cards = read_last2_picked()
        # last_cards[0] : card played 2nd
        # last_cards[1] : card played 1st

        if winner == "y":
            if player == choosing_player:
                add_to_result(last_cards[1][0], last_cards[0][0], last_cards[0][0])
            else:
                add_to_result(last_cards[1][0], last_cards[0][0], last_cards[1][0])
            choosing_player = player
        elif winner == "n":
            if player == choosing_player:
                add_to_result(last_cards[1][0], last_cards[0][0], last_cards[1][0])
            else:
                add_to_result(last_cards[1][0], last_cards[0][0], last_cards[0][0])
            choosing_player = "2" if player == "1" else "1"
    else:
        if r < (rounds - 1):
            print("You are Player 2 - wait until Player 1 have entered the result of this round.")

if player == "1":
    print_statistics()
else:
    print("")
    print("Game statistics is shown for Player 1 only after Player 1 entered the result")

conn.close()