def add_ip_address(username, ip_address):
    # add ip_address to list of ip_addresses the user has logged in from
    db = db_setup()
    cursor = db.cursor()
    cursor.execute("INSERT INTO ip_addresses(username, ip_address) VALUES('{0}', '{1}')".format(username, ip_address))
    db.close()
    return
def get_logfile_path(username):
    # get the path for the user's logfile
    db = db_setup()
    cursor = db.cursor()
    cursor.execute("SELECT log_path FROM onedir_path WHERE username = '******'".format(username))
    data = cursor.fetchone()
    db.close()
    return data[0]
def get_one_dir_path(username):
    # get the path for the onedir directory on the server side
    db = db_setup()
    cursor = db.cursor()
    cursor.execute("SELECT dir_path FROM onedir_path WHERE username = '******'".format(username))
    data = cursor.fetchone()
    db.close()
    return data[0]
def log_action(user1, action1):
    time1 = datetime.datetime.utcnow()
    db = db_setup()
    cursor = db.cursor()
    cursor.execute(
        "INSERT INTO traffic_report_log(user, action, time) VALUES ('{0}', '{1}', '{2}')".format(user1, action1, time1)
    )
    db.close()
    return
def password_validates(username, password):
    db = db_setup()
    cursor = db.cursor()
    cursor.execute("SELECT password FROM users WHERE username = '******'".format(username))
    result = cursor.fetchone()
    db.close()
    if result[0] == password:
        return True
    else:
        return False
def is_valid_login_information(username, password):
    # check if the username and password given are valid
    db = db_setup()
    cursor = db.cursor()
    cursor.execute("SELECT * FROM users WHERE username = '******' AND password = '******'".format(username, password))
    data = cursor.fetchone()
    db.close()
    if data is None:
        return False
    return True
def username_taken(username):
    # check if the username is already in use
    db = db_setup()
    cursor = db.cursor()
    cursor.execute("SELECT * FROM users WHERE username = '******'".format(username))
    data = cursor.fetchone()
    db.close()
    if data is None:
        return False
    return True
def username_exists(username):
    db = db_setup()
    cursor = db.cursor()
    cursor.execute("SELECT 1 FROM users WHERE username = '******'".format(username))
    result = cursor.fetchone()
    db.close()
    if result is None:
        return False
    if result[0] == 1L:
        return True
    else:
        return False
def create_new_account(username, password, ip_address, one_dir_path, logfile_path):
    db = db_setup()
    cursor = db.cursor()
    cursor.execute("INSERT INTO users(username, password) VALUES('{0}', '{1}')".format(username, password))
    cursor.execute(
        "INSERT INTO onedir_path(username, dir_path, log_path) VALUES('{0}', '{1}', '{2}')".format(
            username, one_dir_path, logfile_path
        )
    )
    cursor.execute("INSERT INTO ip_addresses(username, ip_address) VALUES('{0}', '{1}')".format(username, ip_address))
    db.close()
    return
def ip_address_is_new(username, ip_address):
    # check if the user has logged in in from the given IP address before
    db = db_setup()
    cursor = db.cursor()
    cursor.execute(
        "SELECT * FROM ip_addresses WHERE username = '******' AND ip_address = '{1}'".format(username, ip_address)
    )
    data = cursor.fetchone()
    db.close()
    if data is None:
        return True
    return False
def update_user_password(username, new_password):
    db = db_setup()
    cursor = db.cursor()
    cursor.execute("UPDATE users SET password = '******' WHERE username = '******'".format(new_password, username))
    db.close()
    return True
def register_mac(mac, username):
    db = db_setup()
    cursor = db.cursor()
    cursor.execute("INSERT INTO machines(mac, username) VALUES ('{0}', '{1}')".format(mac, username))
    db.close()
def register_user(username, password):
    db = db_setup()
    cursor = db.cursor()
    cursor.execute("INSERT INTO users(username, password) VALUES ('{0}', '{1}')".format(username, password))
    db.close()
    return True