def map_captimes_data(request):
    map_id = int(request.matchdict['id'])

    current_page = request.params.get('page', 1)

    try:
        mmap = DBSession.query(Map).filter_by(map_id=map_id).one()

        mct_q = DBSession.query(PlayerCaptime.fastest_cap, PlayerCaptime.create_dt,
                PlayerCaptime.player_id, PlayerCaptime.game_id,
                Game.server_id, Server.name.label('server_name'),
                PlayerGameStat.nick.label('player_nick')).\
                filter(PlayerCaptime.map_id==map_id).\
                filter(PlayerCaptime.game_id==Game.game_id).\
                filter(PlayerCaptime.map_id==Map.map_id).\
                filter(Game.server_id==Server.server_id).\
                filter(PlayerCaptime.player_id==PlayerGameStat.player_id).\
                filter(PlayerCaptime.game_id==PlayerGameStat.game_id).\
                order_by(expr.asc(PlayerCaptime.fastest_cap))

    except Exception as e:
        raise HTTPNotFound

    map_captimes = Page(mct_q, current_page, items_per_page=20, url=page_url)

    map_captimes.items = [MapCapTime(row) for row in map_captimes.items]

    return {
            'map_id':map_id,
            'map':mmap,
            'captimes':map_captimes,
        }

def _rank_index_data(request):
    current_page = request.params.get("page", 1)

    # game type whitelist
    game_types_allowed = ["ca", "ctf", "dm", "duel", "ft", "ka", "tdm"]

    game_type_cd = request.matchdict['game_type_cd']
    if game_type_cd not in game_types_allowed:
        raise httpexceptions.HTTPNotFound()

    ranks_q = DBSession.query(PlayerRank).\
            filter(PlayerRank.game_type_cd==game_type_cd).\
            order_by(PlayerRank.rank)

    game_type = DBSession.query(GameType).\
            filter(GameType.game_type_cd == game_type_cd).one()

    ranks = Page(ranks_q, current_page, url=page_url)

    if len(ranks) == 0:
        ranks = None

    return {
            'ranks':ranks,
            'game_type_cd':game_type_cd,
            'game_type': game_type,
           }

def player_game_index_data(request):
    try:
        player_id = int(request.matchdict['player_id'])
    except:
        player_id = -1

    game_type_cd = None
    game_type_descr = None

    if request.params.has_key('type'):
        game_type_cd = request.params['type']
        try:
            game_type_descr = DBSession.query(GameType.descr).\
                filter(GameType.game_type_cd == game_type_cd).\
                one()[0]
        except Exception as e:
            pass

    else:
        game_type_cd = None
        game_type_descr = None

    if request.params.has_key('page'):
        current_page = request.params['page']
    else:
        current_page = 1

    try:
        player = DBSession.query(Player).\
                filter_by(player_id=player_id).\
                filter(Player.active_ind == True).\
                one()

        rgs_q = recent_games_q(player_id=player.player_id,
            force_player_id=True, game_type_cd=game_type_cd)

        games = Page(rgs_q, current_page, items_per_page=20, url=page_url)

        # replace the items in the canned pagination class with more rich ones
        games.items = [RecentGame(row) for row in games.items]

        games_played = get_games_played(player_id)

    except Exception as e:
        raise e
        player = None
        games = None
        game_type_cd = None
        game_type_descr = None
        games_played = None

    return {
            'player_id':player.player_id,
            'player':player,
            'games':games,
            'game_type_cd':game_type_cd,
            'game_type_descr':game_type_descr,
            'games_played':games_played,
           }

def search_q(nick=None, server_name=None, map_name=None, create_dt=None,
        gametypes=[]):
    session     = DBSession()
    result_type = None
    q           = None

    # player-only searches
    if nick and not server_name and not map_name and not create_dt \
        and len(gametypes) < 1:
        result_type = "player"
        q = session.query(Player)
        if nick:
            q = q.filter(
                    func.upper(Player.stripped_nick).like('%'+nick.upper()+'%')).\
                    filter(Player.player_id > 2).\
                    filter(Player.active_ind == True).\
                    order_by(Player.player_id)

    # server-only searches
    elif server_name and not nick and not map_name and not create_dt and len(gametypes) < 1:
        result_type = "server"
        q = session.query(Server)
        if server_name:
            q = q.filter(func.upper(Server.name).like('%'+server_name.upper()+'%'))\
                .filter(Server.active_ind)\
                .order_by(Server.server_id)

    # map-only searches
    elif map_name and not nick and not server_name and not create_dt \
        and len(gametypes) < 1:
        result_type = "map"
        q = session.query(Map)
        if map_name:
            q = q.filter(func.upper(Map.name).\
                    like('%'+map_name.upper()+'%')).\
                    order_by(Map.map_id)

    # game searches (all else)
    else:
        result_type = "game"
        q = session.query(Game, Server, Map).\
                filter(Game.server_id == Server.server_id).\
                filter(Server.active_ind).\
                filter(Game.map_id == Map.map_id).\
                order_by(Game.game_id.desc())
        if len(gametypes) > 0:
            q = q.filter(Game.game_type_cd.in_(gametypes))
        if nick:
            q = q.filter(func.upper(PlayerGameStat.stripped_nick).\
                    like('%'+nick.upper()+'%')).\
                filter(PlayerGameStat.game_id == Game.game_id)
        if map_name:
            q = q.filter(func.upper(Map.name).\
                    like('%'+map_name.upper()+'%'))
        if server_name:
            q = q.filter(func.upper(Server.name).\
                    like('%'+server_name.upper()+'%'))

    return (result_type, q)

def player_info_data(request):
    player_id = int(request.matchdict['id'])
    if player_id <= 2:
        player_id = -1;

    try:
        player = DBSession.query(Player).filter_by(player_id=player_id).\
                filter(Player.active_ind == True).one()

        games_played   = get_games_played(player_id)
        overall_stats  = get_overall_stats(player_id)
        fav_maps       = get_fav_maps(player_id)
        elos           = get_elos(player_id)
        ranks          = get_ranks(player_id)
        medals         = get_player_medals(player_id)
        recent_games   = get_recent_games(player_id)
        cake_day       = is_cake_day(player.create_dt)

    except Exception as e:
        raise pyramid.httpexceptions.HTTPNotFound

        ## do not raise application exceptions here (only for debugging)
        # raise e

    return {'player':player,
            'games_played':games_played,
            'overall_stats':overall_stats,
            'fav_maps':fav_maps,
            'elos':elos,
            'ranks':ranks,
            'medals':medals,
            'recent_games':recent_games,
            'cake_day':cake_day,
            }

def player_elo_info_data(request):
    """
    Provides elo information on a specific player. Raw data is returned.
    """
    (idfp, status) = verify_request(request)
    log.debug("d0_blind_id verification: idfp={0} status={1}\n".format(idfp, status))

    log.debug("\n----- BEGIN REQUEST BODY -----\n" + request.body +
            "----- END REQUEST BODY -----\n\n")

    hashkey = request.matchdict['hashkey']

    # the incoming hashkey is double quoted, and WSGI unquotes once...
    hashkey = unquote(hashkey)

    try:
        player = DBSession.query(Player).\
                filter(Player.player_id == Hashkey.player_id).\
                filter(Player.active_ind == True).\
                filter(Hashkey.hashkey == hashkey).one()

        elos = get_elos(player.player_id)

    except Exception as e:
        log.debug(e)
        raise pyramid.httpexceptions.HTTPNotFound

    return {
        'hashkey':hashkey,
        'player':player,
        'elos':elos,
    }

    def top_players(self):
        """Top players on this server by total playing time."""
        try:
            top_players_q = DBSession.query(
                fg.row_number().over(
                    order_by=expr.desc(func.sum(PlayerGameStat.alivetime))).label("rank"),
                Player.player_id, Player.nick,
                func.sum(PlayerGameStat.alivetime).label("alivetime"))\
                .filter(Player.player_id == PlayerGameStat.player_id)\
                .filter(Game.game_id == PlayerGameStat.game_id)\
                .filter(Game.server_id == self.server_id)\
                .filter(Player.player_id > 2)\
                .filter(PlayerGameStat.create_dt > (self.now - timedelta(days=self.lifetime)))\
                .order_by(expr.desc(func.sum(PlayerGameStat.alivetime)))\
                .group_by(Player.nick)\
                .group_by(Player.player_id)

            if self.last:
                top_players_q = top_players_q.offset(self.last)

            if self.limit:
                top_players_q = top_players_q.limit(self.limit)

            top_players = top_players_q.all()

        except Exception as e:
            log.debug(e)
            raise HTTPNotFound

        return top_players

    def get_top_scorers(self):
        """Top players by score. Shared by all renderers."""
        cutoff = self.now - timedelta(days=self.lifetime)
        cutoff = self.now - timedelta(days=120)

        top_scorers_q = DBSession.query(
            fg.row_number().over(order_by=expr.desc(func.sum(PlayerGameStat.score))).label("rank"),
            Player.player_id, Player.nick, func.sum(PlayerGameStat.score).label("total_score"))\
            .filter(Player.player_id == PlayerGameStat.player_id)\
            .filter(Game.game_id == PlayerGameStat.game_id)\
            .filter(Game.map_id == self.map_id)\
            .filter(Player.player_id > 2)\
            .filter(PlayerGameStat.create_dt > cutoff)\
            .order_by(expr.desc(func.sum(PlayerGameStat.score)))\
            .group_by(Player.nick)\
            .group_by(Player.player_id)

        if self.last:
            top_scorers_q = top_scorers_q.offset(self.last)

        if self.limit:
            top_scorers_q = top_scorers_q.limit(self.limit)

        top_scorers = top_scorers_q.all()

        return top_scorers

    def top_maps(self):
        """Returns the raw data shared by all renderers."""
        try:
            top_maps_q = DBSession.query(
                fg.row_number().over(order_by=expr.desc(func.count())).label("rank"),
                Game.map_id, Map.name, func.count().label("times_played"))\
                .filter(Map.map_id == Game.map_id)\
                .filter(Game.server_id == self.server_id)\
                .filter(Game.create_dt > (self.now - timedelta(days=self.lifetime)))\
                .group_by(Game.map_id)\
                .group_by(Map.name) \
                .order_by(expr.desc(func.count()))

            if self.last:
                top_maps_q = top_maps_q.offset(self.last)

            if self.limit:
                top_maps_q = top_maps_q.limit(self.limit)

            top_maps = top_maps_q.all()
        except Exception as e:
            log.debug(e)
            raise HTTPNotFound

        return top_maps

def get_elos(player_id):
    """
    Provides a breakdown of the player's elos by game type.

    Returns a dictionary of namedtuples with the following members:
        - player_id
        - game_type_cd
        - games
        - elo

    The key to the dictionary is the game type code. There is also an
    "overall" game_type_cd which is the overall best rank.
    """
    raw_elos = DBSession.query(PlayerElo).filter_by(player_id=player_id).\
            order_by(PlayerElo.elo.desc()).all()

    elos = {}
    found_max_elo = False
    for row in raw_elos:
        if not found_max_elo:
            elos['overall'] = row
            found_max_elo = True

        elos[row.game_type_cd] = row

    return elos

def merge(request):
    '''A simple merge view. The merge.mako template does the work.'''
    s = DBSession()

    # only do a merge if we have all of the required data
    if request.params.has_key("csrf_token"):
        # check the token to prevent request forgery
        st = request.session.get_csrf_token()
        check_csrf_token(request)

        if request.params.has_key("w_pid") and request.params.has_key("l_pid"):
            w_pid = request.params.get("w_pid")
            l_pid = request.params.get("l_pid")

            # do the merge, hope for the best!
            try:
                s.execute("select merge_players(:w_pid, :l_pid)",
                    {"w_pid": w_pid, "l_pid": l_pid})

                s.commit()

                request.session.flash(
                    "Successfully merged player %s into %s!" % (l_pid, w_pid),
                    "success")

            except:
                s.rollback()

                request.session.flash(
                    "Could not merge player %s into %s." % (l_pid, w_pid),
                    "failure")

    return {}

def groupfinder(userid, request):
    groups = []
    try:
        groups_q = DBSession.query(PlayerGroups.group_name).filter(Player.email_addr == userid).all()

        for g in groups_q:
            groups.append(g.group_name)
    except:
        pass

    return groups

def player_captimes_data(request):
    player_id = int(request.matchdict['player_id'])
    if player_id <= 2:
        player_id = -1;

    page = request.params.get("page", 1)

    sort = request.params.get("sort", "create_dt")

    try:
        player = DBSession.query(Player).filter_by(player_id=player_id).one()

        pct_q = DBSession.query(PlayerCaptime.fastest_cap, PlayerCaptime.create_dt,
                PlayerCaptime.player_id, PlayerCaptime.game_id, PlayerCaptime.map_id,
                Map.name.label('map_name'), Game.server_id, Server.name.label('server_name')).\
                filter(PlayerCaptime.player_id==player_id).\
                filter(PlayerCaptime.game_id==Game.game_id).\
                filter(PlayerCaptime.map_id==Map.map_id).\
                filter(Game.server_id==Server.server_id)

        if sort == "fastest":
            pct_q = pct_q.order_by(PlayerCaptime.fastest_cap)
        else:
            sort = "create_dt"
            pct_q = pct_q.order_by(expr.desc(PlayerCaptime.create_dt))

    except Exception as e:
        raise pyramid.httpexceptions.HTTPNotFound

    captimes = Page(pct_q, page, items_per_page=20, url=page_url)

    # replace the items in the canned pagination class with more rich ones
    captimes.items = [PlayerCapTime(row) for row in captimes.items]

    return {
            "player_id" : player_id,
            "player"    : player,
            "captimes"  : captimes,
            "page"      : page,
            "sort"      : sort,
        }

def get_damage_stats(player_id, weapon_cd, games):
    """
    Provides damage info for weapon_cd by player_id for the past N games.
    """
    try:
        raw_avg = DBSession.query(func.sum(PlayerWeaponStat.actual),
                func.sum(PlayerWeaponStat.hit)).\
                filter(PlayerWeaponStat.player_id == player_id).\
                filter(PlayerWeaponStat.weapon_cd == weapon_cd).\
                one()

        avg = round(float(raw_avg[0])/raw_avg[1], 2)

        # Determine the damage efficiency (hit, fired) numbers for $games games
        # This is then enumerated to create parameters for a flot graph
        raw_dmgs = DBSession.query(PlayerWeaponStat.game_id,
            PlayerWeaponStat.actual, PlayerWeaponStat.hit).\
                filter(PlayerWeaponStat.player_id == player_id).\
                filter(PlayerWeaponStat.weapon_cd == weapon_cd).\
                order_by(PlayerWeaponStat.game_id.desc()).\
                limit(games).\
                all()

        # they come out in opposite order, so flip them in the right direction
        raw_dmgs.reverse()

        dmgs = []
        for i in range(len(raw_dmgs)):
            # try to derive, unless we've hit nothing then set to 0!
            try:
                dmg = round(float(raw_dmgs[i][1])/raw_dmgs[i][2], 2)
            except:
                dmg = 0.0

            dmgs.append((raw_dmgs[i][0], dmg))
    except Exception as e:
        dmgs = []
        avg = 0.0

    return (avg, dmgs)

    def server_index(self):
        """Returns the raw data shared by all renderers."""
        try:
            server_q = DBSession.query(Server)\
                .filter(Server.active_ind)\
                .order_by(Server.server_id.desc())
            servers = Page(server_q, self.page, items_per_page=25, url=page_url)

        except Exception as e:
            log.debug(e)
            raise HTTPNotFound

        return servers

def get_accuracy_stats(player_id, weapon_cd, games):
    """
    Provides accuracy for weapon_cd by player_id for the past N games.
    """
    # Reaching back 90 days should give us an accurate enough average
    # We then multiply this out for the number of data points (games) to
    # create parameters for a flot graph
    try:
        raw_avg = DBSession.query(func.sum(PlayerWeaponStat.hit),
                func.sum(PlayerWeaponStat.fired)).\
                filter(PlayerWeaponStat.player_id == player_id).\
                filter(PlayerWeaponStat.weapon_cd == weapon_cd).\
                one()

        avg = round(float(raw_avg[0])/raw_avg[1]*100, 2)

        # Determine the raw accuracy (hit, fired) numbers for $games games
        # This is then enumerated to create parameters for a flot graph
        raw_accs = DBSession.query(PlayerWeaponStat.game_id,
            PlayerWeaponStat.hit, PlayerWeaponStat.fired).\
                filter(PlayerWeaponStat.player_id == player_id).\
                filter(PlayerWeaponStat.weapon_cd == weapon_cd).\
                order_by(PlayerWeaponStat.game_id.desc()).\
                limit(games).\
                all()

        # they come out in opposite order, so flip them in the right direction
        raw_accs.reverse()

        accs = []
        for i in range(len(raw_accs)):
            accs.append((raw_accs[i][0], round(float(raw_accs[i][1])/raw_accs[i][2]*100, 2)))
    except:
        accs = []
        avg = 0.0

    return (avg, accs)

def get_top_players_by_time(limit=None, start=None):
    """
    The top players by the amount of time played during a date range.
    """
    q = DBSession.query(ActivePlayer)

    if start is not None:
        q = q.filter(ActivePlayer.sort_order >= start)

    q = q.order_by(ActivePlayer.sort_order)

    if limit is not None:
        q = q.limit(limit)

    return q.all()

def get_player_medals(player_id):
    """Retrieves the list of medals the player has received from tournaments or
    other contests."""
    try:
        medals = DBSession.query(PlayerMedal)\
                .filter(PlayerMedal.player_id==player_id)\
                .order_by(PlayerMedal.place)\
                .order_by(PlayerMedal.create_dt)\
                .all()

        return medals
    
    except Exception as e:
        log.debug(e)
        return []

def get_top_servers_by_play_time(limit=None, start=None):
    """
    The top servers by the cumulative amount of time played on them during a given interval.
    """
    q = DBSession.query(ActiveServer)

    if start is not None:
        q = q.filter(ActiveServer.sort_order >= start)

    q = q.order_by(ActiveServer.sort_order)

    if limit is not None:
        q = q.limit(limit)

    return q.all()

def get_top_maps_by_games(limit=None, start=None):
    """
    The top maps by the number of games played during a date range.
    """
    q = DBSession.query(ActiveMap)

    if start is not None:
        q = q.filter(ActiveMap.sort_order >= start)

    q = q.order_by(ActiveMap.sort_order)

    if limit is not None:
        q = q.limit(limit)

    return q.all()