@bot.command(pass_context=True)
async def ow_ru(ctx):
    """Update Discord users' roles according to Overwatch SR"""
    await bot.send_typing(ctx.message.channel)
    squery = sql.select(overwatch_table)
    # Because another query occurs in the loop, you have to put the data into an array first.
    data = c.execute(squery).fetchall()
    for row in data:
        battle_tag = row[0]
        sr = str(owh.get_sr(battle_tag))
        log.info("Updating {} to SR: {}".format(battle_tag, sr))
        uquery = sql.update(overwatch_table, {"SR":sr}, condition={"BattleTag":battle_tag})
        c.execute(uquery)

    conn.commit()

    server = ctx.message.server
    await update_roles(server)
    await bot.say("Done updating roles!")


# Update OW roles for given member in given server
async def update_role(member, server):
    """ Update a single role for the given member """
    log.info("--- UPDATING ROLES for {} ---".format(member))
    sr = 0
    # Get a list of all entries for member
    query = sql.select(overwatch_table, order="SR DESC", condition={"DiscordName":str(member)})
    # Determine highest SR for member
    data = c.execute(query).fetchall()
    if len(data) < 1:
        # Member doesn't exist in table
        log.info("    Member " + str(member) + " doesn't exist in table!")
        return
    else:
        log.info("    Using highest SR for {}".format(member))
        sr = data[0][1]

    # Determine OW rank from SR
    rank = get_rank(sr)
    log.info("    SR: {0} -- Rank: {1}".format(sr, rank))

    # Replace roles implementation here
    new_roles = member.roles
    # Remove all OW related roles from list of member's roles
    for role in ow_roles[server]:
        if role in new_roles:
            log.info("    Removing role {0}".format(role))
            new_roles.remove(role)

    # If player is unranked, don't assign any new roles, otherwise append the correct role
    if rank == "unranked":
        log.info("    Member {0} is unranked, no new role assigned.".format(str(member)))
    else:
        log.info("    Giving member role: {0}".format(rank))
        new_roles.append(discord.utils.get(server.roles, name=rank))

    # Replace roles accordingly
    await bot.replace_roles(member, *new_roles)

# Update OW roles in the given server
async def update_roles(server):

    log.info("--- UPDATING ROLES PER SR ---")
    # Grab distinct members from table
    query = sql.select(overwatch_table, distinct=True, column_names=["DiscordName"])
    data = c.execute(query).fetchall()
    # Build list of the names for which to check
    members = [u[0] for u in data]
    log.info("MEMBERS: " + ','.join(members))
    # Build list of requests to update_role
    # Need to use server.get_member_named() because discord.utils.get doesn't work with
    # discord member names if you pass in the # part. This way is more robust.
    # If a person doesn't exist in the table, it pretty gracefully skips it.
    # requests = [update_role(server.get_member_named(member), server) for member in members]
    requests = []
    for member in members:
        discord_member = server.get_member_named(member)
        if discord_member is None:
            log.warning("Member {0} does not exist in server".format(member))
        else:
            # requests.append(update_role(discord_member, server))
            await update_role(discord_member, server)
    # Asynchronously perform all calls.
    # await asyncio.wait(requests)

def get_rank(sr):
    if sr >= 4000:
        return "grandmaster"
    elif sr >= 3500 and sr <= 3999:
        return "master"
    elif sr >= 3000 and sr <= 3499:
        return "diamond"
    elif sr >= 2500 and sr <= 2999:
        return "platinum"
    elif sr >= 2000 and sr <= 2499:
        return "gold"
    elif sr >= 1500 and sr <= 1999:
        return "silver"
    elif sr >= 1 and sr <= 1499:
        return "bronze"
    elif sr == 0:
        return "unranked"

async def remove_other_ranks(server, rank, member):
    log.info("    PASSED IN: {0}".format(rank))
    ranks = ["grandmaster","master","diamond","platinum","gold","silver","bronze"]
    # Build list to remove all rank roles.
    roles = [discord.utils.get(server.roles, name=rank_name) for rank_name in ranks]
    await bot.remove_roles(member, *roles)