import sqlite3
from sqlite3 import Error
from datetime import datetime

database = 'authorized_cards.db'

sql_create_user_table = """ CREATE TABLE IF NOT EXISTS users (
                                        id integer PRIMARY KEY,
                                        name text NOT NULL,
                                        card_id integer NOT NULL,
                                        counter integer NOT NULL,
                                        first_seen date NOT NULL,
                                        last_seen date NOT NULL 
                                    ); """


def create_connection(db_file):
    """ create a database connection to the SQLite database
        specified by the db_file
    :param db_file: database file
    :return: Connection object or None
    """
    conn = None
    try:
        conn = sqlite3.connect(db_file)
    except Error as e:
        print(e)

    return conn


def create_table(conn, create_table_sql):
    """ create a table from the create_table_sql statement
    :param conn: Connection object
    :param create_table_sql: a CREATE TABLE statement
    :return:
    """
    try:
        c = conn.cursor()
        c.execute(create_table_sql)
    except Error as e:
        print(e)


def create_user(conn, user, card_id, counter, first_seen, last_seen):
    """
    Create a new task
    :param conn:
    :return:
    """

    sql = ''' INSERT INTO users(name, card_id, counter, first_seen, last_seen)
              VALUES(?,?,?,?,?) '''
    cur = conn.cursor()
    cur.execute(sql, (user, card_id, counter, first_seen, last_seen))
    return cur.lastrowid


def select_all_users(conn):
    """
    Query all rows in the tasks table
    :param conn: the Connection object
    :return:
    """
    cur = conn.cursor()
    cur.execute("SELECT * FROM users")

    rows = cur.fetchall()

    return rows

def register_access(conn, id):
    """
    increment the authentication counter in the database for the given id and save time of the last access
    """
    sql = ''' UPDATE users
              SET counter = counter + 1 ,
                  last_seen = ?
              WHERE id = ?'''
    cur = conn.cursor()
    cur.execute(sql, (str(datetime.utcnow()), id,))
    conn.commit()


def setup_db():
    # create a database connection
    conn = create_connection(database)

    # create tables
    if conn is not None:
        # create projects table
        create_table(conn, sql_create_user_table)
    else:
        print("Error! cannot create the database connection.")

    return conn