import operator
import sqlite3
import datetime
import logging
import pickle


def byteify(input):
    if isinstance(input, dict):
        return {byteify(key): byteify(value) for key, value in input.iteritems()}
    elif isinstance(input, list):
        return [byteify(element) for element in input]
    elif isinstance(input, unicode):
        return input.encode('utf-8')
    else:
        return input


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

##################
## 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):
    conn = sqlite3.connect('db.sqlite3')
    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):
    log("buying ticket - DB")
    try:
        conn = sqlite3.connect('db.sqlite3')
        c = conn.cursor()
        credits = c.execute("SELECT credits, tickets FROM members WHERE member_id = ?;", (member_id,)).fetchone()
        log(credits)
        if not credits:
            log('Bad account lookup, missing credits')
            return False, "Unable to find your account"
        if credits[1] >= 20:
            return False, "You have the maximum number of tickets for this raffle"
        if int(credits[0]) - int(100*amount) < 0:
            log('Not enough credits')
            return False, "You do not have enough credits to purchase a ticket. Credits: {} Tickets: {}".format(credits[0], credits[1])
        conn = sqlite3.connect('db.sqlite3')
        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)
    except Exception as e:
        log(e)
def db_update_credit(member_id, amount):
    conn = sqlite3.connect('db.sqlite3')
    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):
    conn = sqlite3.connect('db.sqlite3')
    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):
    conn = sqlite3.connect('db.sqlite3')
    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):
    conn = sqlite3.connect('db.sqlite3')
    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):
    conn = sqlite3.connect('db.sqlite3')
    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):
    conn = sqlite3.connect('db.sqlite3')
    c = conn.cursor()
    c.execute("DELETE FROM messages WHERE message_id = ?;", (message_id,))
    conn.commit()

def db_get_aliases(member_id):
    conn = sqlite3.connect('db.sqlite3')
    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):
    conn = sqlite3.connect('db.sqlite3')
    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_add_joke(joke):
    conn = sqlite3.connect('db.sqlite3')
    c = conn.cursor()
    c.execute("INSERT INTO jokes (joke) VALUES (?)", (joke,))
    conn.commit()
    conn.close()

def db_get_fortune():
    try:
        conn = sqlite3.connect('db.sqlite3')
        c = conn.cursor()
        # TODO: Move this shit to data
        return c.execute("SELECT fortune FROM fortunes ORDER BY RANDOM() LIMIT 1;").fetchone()[0]
    finally:
        conn.close()

def db_get_joke():
    try:
        conn = sqlite3.connect('db.sqlite3')
        c = conn.cursor()
        return c.execute("SELECT joke FROM jokes ORDER BY RANDOM() LIMIT 1;").fetchone()[0]
    finally:
        conn.close()

def db_add_fortune(fortune, date_added):
    conn = sqlite3.connect('db.sqlite3')
    c = conn.cursor()
    c.execute("INSERT INTO fortunes (fortune, date_added) VALUES (?, ?)", (fortune, date_added))
    conn.commit()
    conn.close()

def db_get_fortune():
    try:
        conn = sqlite3.connect('db.sqlite3')
        c = conn.cursor()
        # TODO: Move this shit to data
        return c.execute("SELECT fortune FROM fortunes ORDER BY RANDOM() LIMIT 1;").fetchone()[0]
    finally:
        conn.close()

def db_get_whoplayed(game_name):
    conn = sqlite3.connect('db.sqlite3')
    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):
    conn = sqlite3.connect('db.sqlite3')
    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):
    conn = sqlite3.connect('db.sqlite3')
    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
    conn = sqlite3.connect('db.sqlite3')
    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_pankration_record(member_id):
    # 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
    pan_record = sqlite3.connect('db.sqlite3')

    c = pan_record.cursor()
    result = None
    result = c.execute("SELECT * FROM pankration WHERE member_id = ?;", (member_id,)).fetchone()
    pan_record.close()
    return dict_factory(c, result)

def db_add_soul_plate(member_id, soul_plate):
    pan_record = db_get_pankration_record(member_id)
    log("{}".format(member_id))
    conn = sqlite3.connect('db.sqlite3')
    c = conn.cursor()
    db_state = c.execute("SELECT soul_plates, member_id FROM pankration WHERE member_id = ?;", (member_id,)).fetchone()
    if not db_state:
        log("no record")
        c.execute("""INSERT INTO pankration(member_id, wins, losses, soul_plates) 
                 VALUES(?, ?, ?, ?);""", (member_id, 0, 0, pickle.dumps([soul_plate])))
        conn.commit()
    else:
        log(db_state)
        if db_state[0] and len(db_state[0]) > 0:
            inv = str(db_state[0])
            soul_plates = pickle.loads(inv)
        else:
            soul_plates = []
        soul_plates.append(soul_plate)
        c.execute("""UPDATE pankration SET soul_plates = ? 
                 WHERE member_id = ?;""", (pickle.dumps(soul_plates), member_id))
        conn.commit()

def db_remove_soul_plate(member_id, idx):
    pan_record = db_get_pankration_record(member_id)
    log("Removing Soul Plate: {}".format(member_id))
    conn = sqlite3.connect('db.sqlite3')
    c = conn.cursor()
    db_state = c.execute("SELECT soul_plates, member_id FROM pankration WHERE member_id = ?;", (member_id,)).fetchone()
    if not db_state:
        log("Invalid remove request for reflector: {}".format(idx,))
        return False
    else:        
        if db_state[0] and len(db_state[0]) > 0:
            try:
                log("\nFirst: {}\n\n".format(db_state[0]))
                inv = str(db_state[0])
                soul_plates = pickle.loads(inv)
                log("\n\nPlates: \n{}".format(soul_plates))
            except Exception as e:
                log("Failed! {}".format(e))

        else:
            log("Invalid remove request for reflector: {}".format(idx,))
            return False
        log("\n\nPlates: \n{}".format(soul_plates))
        del soul_plates[idx]
        c.execute("""UPDATE pankration SET soul_plates = ? 
                 WHERE member_id = ?;""", (pickle.dumps(soul_plates), member_id))
        conn.commit()
    return True

def db_update_pankration_record(member_id, column_name, value):
    conn = sqlite3.connect('db.sqlite3')
    c = conn.cursor()
    c.execute("""UPDATE pankration SET """ + column_name + """ = ? 
             WHERE member_id = ?;""", (value, member_id))
    conn.commit()
    conn.close()

def db_add_reflector(member_id, reflector):
    pan_record = db_get_pankration_record(member_id)
    log("{}".format(member_id))
    conn = sqlite3.connect('db.sqlite3')
    c = conn.cursor()
    db_state = c.execute("SELECT reflectors, member_id FROM pankration WHERE member_id = ?;", (member_id,)).fetchone()
    if not db_state:
        log("no record")
        c.execute("""INSERT INTO pankration(member_id, wins, losses, reflectors) 
                 VALUES(?, ?, ?, ?);""", (member_id, 0, 0, pickle.dumps([reflector])))
        conn.commit()
    else:
        #log(db_state)
        if db_state[0] and len(db_state[0]) > 0:
            inv = str(db_state[0])
            reflectors = pickle.loads(inv)
        else:
            reflectors = []
        reflectors.append(reflector)
        c.execute("""UPDATE pankration SET reflectors = ? 
                 WHERE member_id = ?;""", (pickle.dumps(reflectors), member_id))
        conn.commit()
    return True

def db_remove_reflector(member_id, idx):
    pan_record = db_get_pankration_record(member_id)
    log("{}".format(member_id))
    conn = sqlite3.connect('db.sqlite3')
    c = conn.cursor()
    db_state = c.execute("SELECT reflectors, member_id FROM pankration WHERE member_id = ?;", (member_id,)).fetchone()
    if not db_state:
        log("Invalid remove request for reflector: {}".format(idx,))
        return False
    else:
        log(db_state)
        if db_state[0] and len(db_state[0]) > 0:
            inv = str(db_state[0])
            reflectors = pickle.loads(inv)
        else:
            log("Invalid remove request for reflector: {}".format(idx,))
            return False
        del reflectors[idx]
        c.execute("""UPDATE pankration SET reflectors = ? 
                 WHERE member_id = ?;""", (pickle.dumps(reflectors), member_id))
        conn.commit()


def db_convert_soul_plate_to_reflector(member_id, soul_plate, idx):
    if not db_add_reflector(member_id, soul_plate):
        return False
    if not db_remove_soul_plate(member_id, idx):
        return False
    return True


def db_register_battle(member_id, reflector, idx, target_member_id=None):
    pan_record = db_get_pankration_record(member_id)
    conn = sqlite3.connect('db.sqlite3')
    c = conn.cursor()
    if target_member_id:
        db_state = c.execute("SELECT battle_id, reflector_primary FROM pankration_arena WHERE battle_status = 'waiting' AND direct_challenge = ?;", (member_id,)).fetchall()
    else:
        db_state = c.execute("SELECT battle_id, reflector_primary FROM pankration_arena WHERE battle_status = 'waiting' AND direct_challenge = 0;").fetchall()
    if not db_state:
        log("No battles available: {}".format(idx,))
        if target_member_id:
            c.execute("""INSERT INTO pankration_arena(primary_member_id, reflector_primary, direct_challenge) 
                      VALUES(?, ?, ?);""", (member_id, pickle.dumps(reflector), target_member_id))
        else:
            c.execute("""INSERT INTO pankration_arena(primary_member_id, reflector_primary) 
                      VALUES(?, ?);""", (member_id, pickle.dumps(reflector)))
        conn.commit()
        conn.close()
        db_remove_reflector(member_id, idx)
    else:
        for row in db_state:
            primary_reflector = pickle.loads(str(row[1]))
            primary_level = primary_reflector.level
            # TODO: Setup challenges based on level difference of no more than 10 or spawn a new monster.
            # if primary_level - 10 <= reflector.level <= primary_level + 10:
            battle_id = row[0]
            c.execute("""UPDATE pankration_arena SET secondary_member_id = ?, reflector_secondary = ?, battle_status = 'queued', queue_start = ?
                 WHERE battle_id = ?;""", (member_id, pickle.dumps(reflector), datetime.datetime.now().strftime('%Y/%m/%d %H:%M:%S'), battle_id))
            conn.commit()
            conn.close()
            db_remove_reflector(member_id, idx)
            return


def db_complete_battle(battle_id, primary_member_id, primary_reflector, secondary_member_id,
                       secondary_reflector, winner):
    log('Completing battle')
    conn = sqlite3.connect('db.sqlite3')
    c = conn.cursor()
    c.execute("""UPDATE pankration_arena SET battle_status = 'finished', battle_end = ?
         WHERE battle_id = ?;""", (datetime.datetime.now().strftime('%Y/%m/%d %H:%M:%S'), battle_id))
    conn.commit()
    log('Completing battle - finished')
    # TODO: Settle any wagers
    reflectors = pickle.loads(str(db_get_pankration_record(primary_member_id)['reflectors']))
    reflectors.append(primary_reflector)
    log('Completing battle - Primary')
    if winner == 1:
        c.execute("""UPDATE pankration SET reflectors = ?, wins = wins + 1
         WHERE member_id = ?;""", (pickle.dumps(reflectors), primary_member_id))
    else:
        c.execute("""UPDATE pankration SET reflectors = ?, losses = losses + 1
         WHERE member_id = ?;""", (pickle.dumps(reflectors), primary_member_id))
    conn.commit()

    reflectors2 = pickle.loads(str(db_get_pankration_record(secondary_member_id)['reflectors']))
    reflectors2.append(secondary_reflector)
    log('Completing battle - secondary')
    if winner == 2:
        c.execute("""UPDATE pankration SET reflectors = ?, wins = wins + 1
         WHERE member_id = ?;""", (pickle.dumps(reflectors2), secondary_member_id))
    else:
        c.execute("""UPDATE pankration SET reflectors = ?, losses = losses + 1
         WHERE member_id = ?;""", (pickle.dumps(reflectors2), secondary_member_id))
    conn.commit()
    conn.close()
    log('Completing battle - done')


def db_start_battle(battle_id):
    conn = sqlite3.connect('db.sqlite3')
    c = conn.cursor()
    c.execute("""UPDATE pankration_arena SET battle_status = 'fighting', battle_start = ?
     WHERE battle_id = ?;""", (datetime.datetime.now().strftime('%Y/%m/%d %H:%M:%S'), battle_id))
    conn.commit()
    conn.close()

def db_get_battle_queue():
    conn = sqlite3.connect('db.sqlite3')
    c = conn.cursor()
    result = c.execute("SELECT * FROM pankration_arena WHERE battle_status = 'queued' ORDER BY queue_start;").fetchall()
    conn.close()
    results = []
    for row in result:
        results.append(dict_factory(c, row))

    return results


def db_get_member_username(member_id):
    # 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 = c.execute("SELECT member_name FROM members WHERE member_id = ?;", (member_id,)).fetchone()
    member_conn.close()
    return result[0]


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()
    elif 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
    conn = sqlite3.connect('db.sqlite3')
    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']

    conn = sqlite3.connect('db.sqlite3')
    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)