import sqlite3

conn = sqlite3.connect('db.sqlite3')

def log(message):
    logging.warning("{} - {}".format(datetime.datetime.now().strftime('%Y/%m/%d %H:%M:%S'), message))

##################
## Database Calls
##################

# Converts a row into a dictionary
def dict_factory(cursor, row):
    if row == None:
        return None
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

def db_get_credit(member_id):
    c = conn.cursor()
    credits = c.execute("SELECT credits FROM members WHERE member_id = ?;", (member_id,)).fetchone()
    if credits:
        return credits[0]

def db_buy_ticket(member_id, amount):
    c = conn.cursor()
    credits = c.execute("SELECT credits, tickets FROM members WHERE member_id = ?;", (member_id,)).fetchone()
    if not credits:
        return False, "Unable to find your account"

    if int(credits[0]) - int(100*amount) < 0:
        return False, "You do not have enough credits to purchase a ticket. Credits: {} Tickets: {}".format(credits[0], credits[1])
    c = conn.cursor()
    cost = int(100 * amount)
    c.execute("""UPDATE members SET credits = credits - ?, tickets = tickets + ?
             WHERE member_id = ?;""", (cost, amount, member_id))
    conn.commit()
    return True, int(credits[1]) + int(amount)

def db_update_credit(member_id, amount):
    c = conn.cursor()
    credits = c.execute("SELECT credits FROM members WHERE member_id = ?;", (member_id,)).fetchone()
    if not credits:
        return False, "Unable to find your account"

    if int(credits[0]) + int(amount) < 0:
        return False, "You do not have enough credits to cover amount requested. Credit: {} Amount Requested: {}".format(credits[0], amount)
        if int(credits[0]) < 0:
            c.execute("""UPDATE members SET credits = 0
                     WHERE member_id = ?;""", (amount, member_id))
            conn.commit()

    c.execute("""UPDATE members SET credits = credits + ? 
             WHERE member_id = ?;""", (amount, member_id))
    conn.commit()
    return True, ""

def db_add_minigame(member_id, minigame_name, state):
    c = conn.cursor()
    db_state = c.execute("SELECT state FROM minigames WHERE member_id = ? AND minigame_name = ?;", (member_id, minigame_name)).fetchone()
    if not db_state:
        c.execute("""INSERT INTO minigames(member_id, minigame_name, state) 
                 VALUES(?, ?, ?);""", (member_id, minigame_name, state))
        conn.commit()
    else:
        c.execute("""UPDATE minigames SET state = ? 
                 WHERE member_id = ? AND minigame_name = ?;""", (state, member_id, minigame_name))
        conn.commit()

def db_get_minigame_state(member_id, minigame_name):
    c = conn.cursor()
    state = c.execute("SELECT state FROM minigames WHERE member_id = ? AND minigame_name = ?;", (member_id, minigame_name)).fetchone()
    if state:
        return state[0]

def db_delete_minigame_state(member_id, minigame_name):
    c = conn.cursor()
    c.execute("DELETE FROM minigames WHERE member_id = ? AND minigame_name = ?;", (member_id, minigame_name))
    conn.commit()

def db_add_message(message, delivery_time, channel, message_from, message_to, user_id):
    c = conn.cursor()
    c.execute("""INSERT INTO messages(message, delivery_time, channel, message_from, message_to, user_id) 
                 VALUES(?, ?, ?, ?, ?, ?);""", (message, delivery_time, channel, message_from, message_to, user_id))
    conn.commit()

def db_delete_sent_message(message_id):
    c = conn.cursor()
    c.execute("DELETE FROM messages WHERE message_id = ?;", (message_id,))
    conn.commit()

def db_get_aliases(member_id):
    c = conn.cursor()
    aliases = c.execute("SELECT alias_name FROM aliases WHERE member_id = ?;", (member_id,)).fetchall()
    if aliases:
        alias_list = []
        for alias in aliases:
            alias_list.append(alias[0])
        return alias_list
    else:
        return None

def db_add_aliases(member_id, alias_name):
    c = conn.cursor()
    c.execute("INSERT INTO aliases(alias_name, member_id) VALUES (?, ?);", (alias_name, member_id,))
    conn.commit()

def db_get_messages():
    msg_conn = sqlite3.connect('db.sqlite3')

    c = msg_conn.cursor()
    messages = c.execute("SELECT * FROM messages WHERE datetime('now') >= datetime( replace(delivery_time, '/', '-'));").fetchall()
    if messages:
        db_messages = []
        for message in messages:
            db_messages.append(dict_factory(c, message))
    else:
        db_messages = None
    msg_conn.close()
    return db_messages

def db_get_whoplayed(game_name):
    c = conn.cursor()
    members = c.execute("""SELECT m.member_name, xmg.launch_count
                         FROM members m 
                         INNER JOIN xmember_games xmg ON
                           m.member_id = xmg.member_id
                         INNER JOIN games g ON
                           g.game_id = xmg.game_id
                         WHERE g.game_name COLLATE nocase = ?
                         Order By xmg.launch_count, m.member_name DESC;""", (game_name,)).fetchall()
    member_list = {}
    for member in members:
        member_list[member[0]] = member[1]
    #log(member_list)
    return sorted(member_list.items(), reverse=True, key=operator.itemgetter(1))
    #return sorted(member_list, reverse=True, key=lambda tup: tup[1])

def db_get_games(username):
    c = conn.cursor()
    games = c.execute("""SELECT g.game_name, xmg.launch_count FROM games g
                         INNER JOIN xmember_games xmg ON 
                            g.game_id = xmg.game_id
                         INNER JOIN members m ON
                            m.member_id = xmg.member_id
                         WHERE m.member_name COLLATE nocase = ?;""", (username,)).fetchall()
    games_list = {}
    for game in games:
        games_list[game[0]] = game[1]
    return games_list

def db_get_games_list(limit):
    c = conn.cursor()
    games_list = c.execute("""SELECT g.game_name, count(DISTINCT xmg.member_id)
                              FROM games g 
                              INNER JOIN xmember_games xmg ON
                                g.game_id = xmg.game_id
                              Group By xmg.game_id
                              Order By COUNT(DISTINCT xmg.member_id) DESC
                              LIMIT ?""", (limit,)).fetchall()
    return games_list

def db_add_game(member_id, game_name):
    # Do a lookup by ID, if it's found but the name doesn't match then add a row to aliases with the previous name and change the member name
    c = conn.cursor()
    games = c.execute("SELECT game_id FROM games WHERE game_name = ?;", (game_name,)).fetchone()
    db_game_id = 0
    if not games:
        log("Adding Game: {}".format(game_name,))
        c.execute("INSERT INTO games(game_name) VALUES(?);", (game_name,))
        conn.commit()
        db_game_id = c.execute("select last_insert_rowid();").fetchone()[0]
    else:
        db_game_id = games[0]
    #log("DB Game ID: {}".format(db_game_id,))
    member_games = c.execute("SELECT launch_count FROM xmember_games WHERE game_id = ? AND member_id = ?;", (db_game_id, member_id)).fetchone()
    if not member_games:
        #log("Inserting Member Games: {}, {}".format(db_game_id, member_id))
        c.execute("INSERT INTO xmember_games(game_id, member_id, launch_count) VALUES(?, ?, 1);", (db_game_id, member_id))
        conn.commit()
    else:
        #log("Updating Member Games: {}, {}".format(db_game_id, member_id))
        c.execute("UPDATE xmember_games SET launch_count = launch_count + 1 WHERE game_id = ? AND member_id = ?;", (db_game_id, member_id))
        conn.commit()

def db_get_all_members():
    # Do a lookup by ID, if it's found but the name doesn't match then add a row to aliases with the previous name and change the member name
    member_conn = sqlite3.connect('db.sqlite3')

    c = member_conn.cursor()
    results = c.execute("SELECT member_id, member_name, discord_id, discord_mention, is_afk, afk_at, status, prev_status, status_change_at, current_game, credits, tickets FROM members;").fetchall()
    member_conn.close()
    members_list = []
    for member in results:
        members_list.append(dict_factory(c, member))
    return members_list

def db_get_member(discord_id=None, username=None):
    # Do a lookup by ID, if it's found but the name doesn't match then add a row to aliases with the previous name and change the member name
    member_conn = sqlite3.connect('db.sqlite3')

    c = member_conn.cursor()
    result = None
    if discord_id:
        result = c.execute("SELECT member_id, member_name, discord_id, discord_mention, is_afk, afk_at, status, prev_status, status_change_at, current_game, credits, tickets FROM members WHERE discord_id = ?;", (discord_id,)).fetchone()
    if username:
        result = c.execute("SELECT member_id, member_name, discord_id, discord_mention, is_afk, afk_at, status, prev_status, status_change_at, current_game, credits, tickets FROM members WHERE member_name = ?;", (username,)).fetchone()
    member_conn.close()
    return dict_factory(c, result)

def db_create_member(member):
    # Do a lookup by ID, if it's found but the name doesn't match then add a row to aliases with the previous name and change the member name
    c = conn.cursor()
    c.execute("""INSERT INTO members (member_name, discord_id, discord_mention,
                                      is_afk, afk_at, status, prev_status,
                                      status_change_at, current_game)
                 VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?);""", (member.name.lower(),
                                                         member.id, member.mention(),
                                                         0, datetime.datetime.now().strftime('%Y/%m/%d %H:%M:%S'),
                                                         'online', 'offline',
                                                         datetime.datetime.now().strftime('%Y/%m/%d %H:%M:%S'),
                                                         member.game_id))
    conn.commit()
    if member.game_id != None:
        db_add_game(db_get_member(member.id)['member_id'], member.game_id)

def db_update_member(member, db_member):
    # Do a lookup by ID, if it's found but the name doesn't match then add a row to aliases with the previous name and change the member name
    db_member_id = db_member['member_id']
    db_membername = db_member['member_name']
    status = db_member['status']
    prev_status = db_member['prev_status']

    c = conn.cursor()
    if member.name.lower() != db_membername:
        log("Member Name changed! {} to {}".format(byteify(db_membername), byteify(member.name.lower())))
        c.execute("UPDATE members SET member_name = ? WHERE discord_id = ?;", (member.name.lower(), member.id,))
        conn.commit()

        aliases = c.execute("SELECT * FROM aliases WHERE alias_name = ? AND member_id = ?;", (db_membername, db_member_id)).fetchone()
        log("Alias list for user: {}".format(aliases))
        if aliases == None:
            log("creating new alias: {}, {}".format(byteify(db_membername), db_member_id))
            c.execute("INSERT INTO aliases (alias_name, member_id) VALUES (?, ?);", (db_membername, db_member_id))
            conn.commit()

    if member.status == 'idle':
        afk_at = datetime.datetime.now().strftime('%Y/%m/%d %H:%M:%S')
        is_afk = True
    else:
        is_afk = False
    status_change_at = None
    if status != member.status:
        prev_status = status
        status = member.status
        status_change_at = datetime.datetime.now().strftime('%Y/%m/%d %H:%M:%S')
        if is_afk:
            #log("is afk")
            c.execute("""UPDATE members
                         SET is_afk = ?, afk_at = ?, status = ?,
                             prev_status = ?, status_change_at = ?
                        WHERE discord_id = ?;""", (1, afk_at, status, prev_status, status_change_at, member.id))
            conn.commit()
        else:
            #log("is not afk")
            c.execute("""UPDATE members
                         SET is_afk = ?, status = ?,
                             prev_status = ?, status_change_at = ?
                        WHERE discord_id = ?;""", (0, status, prev_status, status_change_at, member.id))
            conn.commit()
    c.execute("UPDATE members SET current_game = ? WHERE discord_id = ?;", (member.game_id, member.id))
    conn.commit()

    #log("Member: {} \nMember GameID: {} db Game id: {}".format(member, member.game_id, db_member['current_game']))
    if member.game_id != None and member.game_id != db_member['current_game']:
        db_add_game(db_member['member_id'], member.game_id)