def apply_evolution(username, position):
    con = get_connection()
    evolution_result = check_evolution_eligibility(username, position)
    trade_evolution_result = check_trade_evolution_eligibility(
        username, position)
    if evolution_result is not None:
        nickname = evolution_result[0]
        id = evolution_result[3]
        if nickname == evolution_result[1]:
            nickname = evolution_result[2]
        with con:
            cur = con.cursor()
            cur.execute("""
                UPDATE userpokemon SET pokemon_id = %s, nickname = %s
                    WHERE username = %s AND position = %s
                """, [id, nickname, username, position])
            cur.close()
            return nickname + " has evolved! Raise your Kappa s!!!"
    elif trade_evolution_result is not None:
        con = get_connection()
        nickname = trade_evolution_result[0]
        id = trade_evolution_result[3]
        if nickname == trade_evolution_result[1]:
            nickname = trade_evolution_result[2]
        with con:
            cur = con.cursor()
            cur.execute("""
                UPDATE userpokemon SET pokemon_id = %s, nickname = %s
                    WHERE username = %s AND position = %s
                """, [id, nickname, username, position])
            cur.close()
            return nickname + " has evolved! Raise your Kappa s!!!"
    else:
        return "No Pokemon eligible for evolution."

def check_trade_evolution_eligibility(username, position):
    con = get_connection()
    with con:
        cur = con.cursor()
        cur.execute(
            """SELECT userpokemon.nickname, pokemon.name, pokeset.name, pokeset.id,
        pokeset.evolution_set, pokeset.evolution_index, pokeset.evolution_trigger, userpokemon.for_trade
        FROM userpokemon JOIN pokemon on userpokemon.pokemon_id = pokemon.id
        JOIN pokemon as pokeset on pokemon.evolution_set = pokeset.evolution_set
        WHERE userpokemon.username = %s AND userpokemon.position = %s
        AND pokeset.evolution_index = pokemon.evolution_index + 1
        AND pokeset.evolution_trigger = 20 and for_trade = 2;
        """,
            [username, position],
        )

        # +----------+---------+--------+----+---------------+-----------------+-------------------+
        # | nickname | name    | name   | id | evolution_set | evolution_index + evolution_trigger |
        # +----------+---------+--------+----+---------------+-----------------+-------------------+
        # | Haunter  | Haunter | Gengar | 94 |            39 |               3 +                20 |
        # +----------+---------+--------+----+---------------+-----------------+-------------------+

        eligible_evolution = cur.fetchone()
        cur.close()
        return eligible_evolution

def trade_transaction(giver, giver_position, receiver, receiver_position):
    con = get_connection()
    with con:
        cur = con.cursor()
        cur.execute(
            """
            UPDATE userpokemon SET username = %s, for_trade = 2, position = 0
                WHERE username = %s
                    AND position = %s""",
            [receiver, giver, giver_position],
        )
        cur.execute(
            """
            UPDATE userpokemon SET username = %s, for_trade = 2, position = %s
                WHERE username = %s
                    AND position = %s""",
            [giver, giver_position, receiver, receiver_position],
        )
        cur.execute(
            """
            UPDATE userpokemon SET position = %s, for_trade = 2
                WHERE position = 0""",
            [receiver_position],
        )
        cur.execute(
            """
            UPDATE userpokemon SET time_trade_set = NULL
                WHERE username = %s AND position = %s
        """,
            [receiver, receiver_position],
        )
        cur.close()

def modify_user_points(username, delta):
    donation_points = get_user_points(username)
    if isinstance(donation_points, str):
        donation_points = 0
    time_points = get_user_time_points(username)
    if isinstance(time_points, str):
        time_points = 0
    con = get_connection()
    with con:
        cur = con.cursor()
        if abs(delta) > donation_points and delta <= 0:
            time_points_to_remove = abs(donation_points - abs(delta))
            cur.execute(
                """INSERT INTO users (username, donation_points)
                    VALUES (%s, %s) ON DUPLICATE KEY
                    UPDATE donation_points = %s,
                    time_points = time_points - %s""", [
                    username, 0, 0, time_points_to_remove])
        else:  # standard for removal
            cur.execute(
                """INSERT INTO users (username, donation_points)
                    VALUES (%s, %s) ON DUPLICATE KEY
                    UPDATE donation_points = donation_points + %s""", [
                    username, delta, delta])
        cur.close()

def user_pokemon_types_summary(username, position):
    con = get_connection()
    with con:
        cur = con.cursor()
        cur.execute("""SELECT userpokemon.nickname AS 'Nickname',
        type_primary.id as 'ID 1', type_secondary.id AS 'ID 2',
        pokemon.name AS 'Name', type_primary.identifier AS 'Type 1',
        type_secondary.identifier AS 'Type 2'
        FROM userpokemon INNER JOIN pokemon
            ON pokemon.id = userpokemon.pokemon_id inner
        JOIN types AS type_primary
            ON (type_primary.id = pokemon.type_primary)
        LEFT OUTER JOIN types AS type_secondary
            ON (type_secondary.id = pokemon.type_secondary)
        WHERE username = %s AND userpokemon.position = %s""", [
            username, position])
        types_summary = cur.fetchone()
        nickname = types_summary[0]
        pokemon_type1_id = types_summary[1]
        pokemon_type2_id = types_summary[2]
        pokemon_name = types_summary[3]
        pokemon_type1 = types_summary[4]
        cur.close()
        if types_summary[2] and types_summary[5] is not None:
            pokemon_type2 = types_summary[5]
            return nickname, pokemon_type1_id, pokemon_type2_id, pokemon_name, pokemon_type1, pokemon_type2
        else:
            pokemon_type2 = "No secondary type."
            return nickname, pokemon_type1_id, 'none', pokemon_name, pokemon_type1, 'none'

def sell_transaction(username):
    con = get_connection()
    seller = 'lorenzotherobot'
    buyer = username
    open_position = 5
    with con:
        cur = con.cursor()
        cur.execute("""SET @seller = %s""", [seller])
        cur.execute("""@position = %s""", [party_position])
        cur.execute("""SET @buyer = %s""", [buyer])
        cur.execute("""SET @position_free = %s""", [open_position])
        cur.execute("""
            SET @price = (
                SELECT asking_price FROM userpokemon WHERE username = @owner
                    AND position = @position)""")
        cur.execute("""
            UPDATE userpokemon SET username = @buyer, for_sale = 0,
                position = @position_free WHERE username = @seller
                    AND position = @position""")
        cur.execute("""
            UPDATE users SET donation_points = donation_points + @price
                WHERE username = @seller""")
        cur.execute("""
            UPDATE users SET donation_points = donation_points - @price
                WHERE username = @buyer""")
        cur.execute("""COMMIT""")
        cur.close()

def buy_items(id, username):
    con = get_connection()
    with con:
        try:
            if int(id) in (1, 2, 3, 4, 5, 11):
                points = int(get_user_points(username))
                value = int(get_item_value(id))
                if points >= value:
                    cur = con.cursor()
                    cur.execute("""
                        INSERT INTO useritems (username, item_id, quantity)
                                VALUES (%s, %s, 1) ON DUPLICATE KEY
                                UPDATE quantity = quantity + 1""", [
                        username, id])
                    cur.execute("""
                        UPDATE users SET donation_points = donation_points - %s
                            WHERE username = %s""", [value, username])
                    cur.close()
                    return "Transaction successful."
                else:
                    return "You need more points for that!"
            else:
                return "That is not a valid item position."
        except Exception as error:
            print error
            return "item ID must be a number"

def modify_points_all_users_timer(all_users, points_to_increment=1):
    con = get_connection()
    user_list_for_query = [(x, str(points_to_increment)) for x in all_users]
    try:
        with con:
            cur = con.cursor()
            dData = user_list_for_query  # exact input you gave
            sql = """
                INSERT INTO users (username, points)
                        VALUES (%s, %s)
                    ON DUPLICATE KEY UPDATE points = points +
                """ + str(points_to_increment)
            cur.executemany(sql, ((user, points) for user, points in dData))
            sql = """
                INSERT INTO users (username, time_in_chat)
                    VALUES (%s, %s)
                    ON DUPLICATE KEY UPDATE time_in_chat = time_in_chat +
                        """ + str(5)
            cur.executemany(sql, ((user, points) for user, points in dData))
            cur.close()
            return "success"
    except Exception as error:
        cur.close()
        print "ERROR", error
        return "Error incrementing points:" + str(error)

def get_item_value(id):
    con = get_connection()
    with con:
        cur = con.cursor()
        cur.execute("""SELECT value FROM items WHERE id = %s""", [id])
        value = cur.fetchone()
        cur.close()
        return value[0]

def add_loss(username):
    con = get_connection()
    with con:
        cur = con.cursor()
        cur.execute("""
            UPDATE users SET losses = losses + 1 WHERE username = %s
        """, [username])
        cur.close()

def get_last_battle(username):
    con = get_connection()
    with con:
        cur = con.cursor()
        cur.execute("""SELECT lastbattle from users WHERE username = %s""", [username])
        last_battle = cur.fetchone()
        cur.close()
        return last_battle[0]

def set_battle_timestamp(username, datetime):
    con = get_connection()
    with con:
        cur = con.cursor()
        cur.execute(
            """UPDATE users SET lastbattle = %s WHERE username = %s""", [
                datetime, username])
        cur.close()

def level_up_user_pokemon(username, position):
    con = get_connection()
    with con:
        cur = con.cursor()
        cur.execute("""update userpokemon set level = level + 1
        where username = %s and position = %s
        """, [username, position])
        cur.close()

def update_nickname(nickname, username, position):
    con = get_connection()
    # TODO - error message on no entry
    with con:
        cur = con.cursor()
        cur.execute("""
            UPDATE userpokemon SET nickname = %s WHERE username = %s
                AND position = %s""", [nickname, username, position])
        cur.close()

def reset_trade_timestamp(time):
    con = get_connection()
    with con:
        cur = con.cursor()
        cur.execute("""
            UPDATE userpokemon SET for_trade = 0, time_trade_set = NULL
                WHERE time_trade_set < %s
            """, [time])
        cur.close()

def set_user_points(delta_user, delta):
    con = get_connection()
    with con:
        cur = con.cursor()
        cur.execute("""
            UPDATE users SET points = %s
                WHERE username = %s
            """, [delta, delta_user])
        cur.close()

def get_defender_multiplier(attacker_type, defender_type):
    con = get_connection()
    with con:
        cur = con.cursor()
        cur.execute("""SELECT * from types WHERE id = %s""", [defender_type])
        defender_multipliers = cur.fetchone()
        row_correction = attacker_type + 1
        defender_effect = defender_multipliers[row_correction]
        cur.close()
        return defender_effect

def update_asking_price(username):
    con = get_connection()
    asking_price = 4000
    with con:
        cur = con.cursor()
        cur.execute("""
            UPDATE userpokemon SET asking_price = %s
                WHERE username = %s AND position = %s""", [
                    asking_price, username, party_position])
        cur.close()

def modify_user_points(username, delta):
    con = get_connection()
    with con:
        cur = con.cursor()
        cur.execute(
            """INSERT INTO users (username, points)
                VALUES (%s, %s) ON DUPLICATE KEY
                UPDATE points = points + %s""", [
                username, delta, delta])
        cur.close()

def modify_points_all_users_hack(points_to_increment=1):
    con = get_connection()
    with con:
        cur = con.cursor()
        cur.execute("""
            INSERT INTO users (username, points) VALUES (%s, %s)
                ON DUPLICATE KEY UPDATE points = points + """ +
                    str(points_to_increment), user)
        cur.close()
        return "success"