import sqlite3
from random import randint
from time import time

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


def get_number_of_cards():
    """Returns the total number of rows in table computer"""
    # not used in this sample but very useful in general
    cursor = conn.cursor()
    sql = "SELECT COUNT(*) FROM 'computer'"
    print("SQL:>>>>>" + sql + "<<<<<")
    cursor.execute(sql)
    count = cursor.fetchone()
    cursor.close()
    print("SQL: number of rows in table computer: {}".format(count[0]))
    return count[0]


def get_number_of_picked_cards():
    """Returns the total number of rows in table picked"""
    # not used in this sample but very useful in general
    cursor = conn.cursor()
    sql = "SELECT COUNT(*) FROM 'picked'"
    print("SQL:>>>>>" + sql + "<<<<<")
    cursor.execute(sql)
    count = cursor.fetchone()
    cursor.close()
    print("SQL: number of rows in table picked: {}".format(count[0]))
    return count[0]


def read_all_cards():
    sql = "SELECT * FROM computer"
    print("SQL:>>>>>" + sql + "<<<<<")
    result = conn.execute(sql)
    return result.fetchall()


def insert_picked(name):
    sql = "INSERT INTO picked(name, time) VALUES ('{}', {})".format(name, time())
    print("SQL:>>>>>" + sql + "<<<<<")
    conn.execute(sql)
    conn.commit()


def read_last_picked():
    sql = "SELECT * FROM picked ORDER BY time DESC"
    print("SQL:>>>>>" + sql + "<<<<<")
    result = conn.execute(sql)
    return result.fetchone()


def dump_table_picked():
    sql = "SELECT * FROM picked ORDER BY time DESC"
    print("SQL:>>>>>" + sql + "<<<<<")
    result = conn.execute(sql)
    picked_cards = result.fetchall()
    print("cards in table picked")
    print("---------------------")
    for card in picked_cards:
        print(card)
    print("---------------------")

def delete_content_of_table_picked():
    sql = "DELETE FROM picked"
    print("SQL:>>>>>" + sql + "<<<<<")
    cursor = conn.cursor()
    cursor.execute(sql)
    print("SQL: number of deleted rows: {}".format(cursor.rowcount))


def pick_card():
    last_picked_card = read_last_picked()
    print("last_picked_card:", last_picked_card)

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

    if last_picked_card is not None:
        # the if above fixes a bug when table picked is empty
        while random_card[0] == last_picked_card[0]:
            random_card = cards[randint(0, len(cards) - 1)]

    insert_picked(random_card[0])
    return random_card


cards = read_all_cards()
print("number of available cards: {}".format(get_number_of_cards()))

picked_cards = get_number_of_picked_cards()
if picked_cards > 10:
    print("truncate table picked")
    delete_content_of_table_picked()

resp = "y"
while resp == "y":
    print("picked card:", pick_card())
    dump_table_picked()
    print("")
    resp = input("pick another card?[y/n]:")

conn.close()