<commit_before># -*- coding: utf-8 -*-

import json

from common import DATETIME_FORMAT
from asyncpg import Connection
from exceptions import MatchNotFound


async def get_scoreboard(con: Connection, match_id: str):

    await con.set_type_codec(
        'json',
        encoder=json.dumps,
        decoder=json.loads,
        schema='pg_catalog'
    )

    query = """
    SELECT
        json_build_object(
            'gt_short',    g.gametype_short,
            'gt',          g.gametype_name,
            'factory',     f.factory_short,
            'map',         mm.map_name,
            'team1_score', m.team1_score,
            'team2_score', m.team2_score,
            'rating_diff', CAST( ROUND( CAST(t.diff AS NUMERIC), 2) AS REAL ),
            'timestamp',   m.timestamp,
            'datetime',    TO_CHAR(to_timestamp(m.timestamp), '{DATETIME_FORMAT}'),
            'duration',    TO_CHAR((m.duration || ' second')::interval, 'MI:SS')
        )
    FROM
        matches m
    LEFT JOIN gametypes g ON g.gametype_id = m.gametype_id
    LEFT JOIN factories f ON f.factory_id = m.factory_id
    LEFT JOIN maps mm ON m.map_id = mm.map_id
    LEFT JOIN (
        SELECT match_id, sum(rating) as diff
        FROM (
            SELECT match_id, team, avg(old_mean)*(case when team = 1 then 1 else -1 end) as rating
            FROM scoreboards
            WHERE match_perf is not NULL AND match_id = $1
            GROUP by match_id, team
        ) t
        GROUP by match_id
    ) t ON t.match_id = m.match_id
    WHERE
        m.match_id = $1;
    """.format(
        DATETIME_FORMAT=DATETIME_FORMAT
    )
    summary = await con.fetchval(query, match_id)
    if summary is None:
        raise MatchNotFound(match_id)

    query = """
    SELECT
        json_object_agg(t.steam_id, t.weapon_stats)
    FROM (
        SELECT
            t.steam_id::text,
            json_object_agg(t.weapon_short, ARRAY[t.frags, t.hits, t.shots]) AS weapon_stats
        FROM (
            SELECT
                s.steam_id,
                w.weapon_short,
                SUM(sw.frags) AS frags,
                SUM(sw.hits) AS hits,
                SUM(sw.shots) AS shots
            FROM
                scoreboards s
            RIGHT JOIN scoreboards_weapons sw ON sw.match_id = s.match_id AND sw.steam_id = s.steam_id AND sw.team = s.team
            LEFT JOIN weapons w ON w.weapon_id = sw.weapon_id
            WHERE
                s.match_id = $1
            GROUP BY s.steam_id, w.weapon_short
        ) t
        GROUP BY t.steam_id
    ) t;
    """
    player_weapon_stats = await con.fetchval(query, match_id)

    query = """
    SELECT
        json_object_agg(t.steam_id, t.medal_stats)
    FROM (
        SELECT
            t.steam_id::text,
            json_object_agg(t.medal_short, t.count) AS medal_stats
        FROM (
            SELECT
                s.steam_id,
                m.medal_short,
                SUM(sm.count) AS count
            FROM
                scoreboards s
            RIGHT JOIN scoreboards_medals sm ON sm.match_id = s.match_id AND sm.steam_id = s.steam_id AND sm.team = s.team
            LEFT JOIN medals m ON m.medal_id = sm.medal_id
            WHERE
                s.match_id = $1
            GROUP BY s.steam_id, m.medal_short
        ) t
        GROUP BY t.steam_id
    ) t;
    """
    player_medal_stats = await con.fetchval(query, match_id)

    query = """
    SELECT
        array_agg(item)
    FROM (
        SELECT
            json_build_object(
                'steam_id', t.steam_id::text,
                'team', t.team::text,
                'name', p.name,
                'stats', json_build_object(
                    'score',                t.score,
                    'frags',                t.frags,
                    'deaths',             t.deaths,
                    'damage_dealt', t.damage_dealt,
                    'damage_taken', t.damage_taken,
                    'alive_time',     t.alive_time
                ),
                'rating', json_build_object(
                    'old',     CAST( ROUND( CAST(t.old_mean            AS NUMERIC), 2) AS REAL ),
                    'old_d', CAST( ROUND( CAST(t.old_deviation AS NUMERIC), 2) AS REAL ),
                    'new',     CAST( ROUND( CAST(t.new_mean            AS NUMERIC), 2) AS REAL ),
                    'new_d', CAST( ROUND( CAST(t.new_deviation AS NUMERIC), 2) AS REAL )
                ),
                'medal_stats', ms.medal_stats,
                'weapon_stats', ws.weapon_stats
            ) AS item
        FROM
            scoreboards t
        LEFT JOIN players p ON p.steam_id = t.steam_id
        LEFT JOIN (
            SELECT
                t.steam_id, t.team,
                json_object_agg(t.weapon_short, ARRAY[t.frags, t.hits, t.shots, t.accuracy]) AS weapon_stats
            FROM
                (
                SELECT
                    s.steam_id, s.team, w.weapon_short, sw.frags, sw.hits, sw.shots,
                    CASE WHEN sw.shots = 0 THEN 0
                        ELSE CAST(100. * sw.hits / sw.shots AS INT)
                    END AS accuracy
                FROM
                    scoreboards s
                RIGHT JOIN scoreboards_weapons sw ON sw.match_id = s.match_id AND sw.steam_id = s.steam_id AND sw.team = s.team
                LEFT JOIN weapons w ON w.weapon_id = sw.weapon_id
                WHERE
                    s.match_id = $1
                ) t
                GROUP BY t.steam_id, t.team
        ) ws ON ws.steam_id = t.steam_id AND ws.team = t.team
        LEFT JOIN (
            SELECT
                t.steam_id, t.team,
                json_object_agg(t.medal_short, t.count) AS medal_stats
            FROM (
                SELECT
                    s.steam_id, s.team, m.medal_short, sm.count
                FROM
                    scoreboards s
                RIGHT JOIN scoreboards_medals sm ON sm.match_id = s.match_id AND sm.steam_id = s.steam_id AND sm.team = s.team
                LEFT JOIN medals m ON m.medal_id = sm.medal_id
                WHERE
                    s.match_id = $1
            ) t
            GROUP BY t.steam_id, t.team
        ) ms ON ms.steam_id = t.steam_id AND ms.team = t.team
        WHERE
            t.match_id = $1
        ORDER BY t.score DESC
    ) t
    """
    overall_stats = await con.fetchval(query, match_id)

    query = """
    SELECT
        array_agg(m.medal_short)
    FROM (
        SELECT DISTINCT medal_id
        FROM scoreboards_medals
        WHERE match_id = $1
        ) sm
     LEFT JOIN medals m ON m.medal_id = sm.medal_id
    """
    medals_available = await con.fetchval(query, match_id)<commit_msg>scoreboard: ordering by steam id on equal score<commit_after># -*- coding: utf-8 -*-

import json

from common import DATETIME_FORMAT
from asyncpg import Connection
from exceptions import MatchNotFound


async def get_scoreboard(con: Connection, match_id: str):

    await con.set_type_codec(
        'json',
        encoder=json.dumps,
        decoder=json.loads,
        schema='pg_catalog'
    )

    query = """
    SELECT
        json_build_object(
            'gt_short',    g.gametype_short,
            'gt',          g.gametype_name,
            'factory',     f.factory_short,
            'map',         mm.map_name,
            'team1_score', m.team1_score,
            'team2_score', m.team2_score,
            'rating_diff', CAST( ROUND( CAST(t.diff AS NUMERIC), 2) AS REAL ),
            'timestamp',   m.timestamp,
            'datetime',    TO_CHAR(to_timestamp(m.timestamp), '{DATETIME_FORMAT}'),
            'duration',    TO_CHAR((m.duration || ' second')::interval, 'MI:SS')
        )
    FROM
        matches m
    LEFT JOIN gametypes g ON g.gametype_id = m.gametype_id
    LEFT JOIN factories f ON f.factory_id = m.factory_id
    LEFT JOIN maps mm ON m.map_id = mm.map_id
    LEFT JOIN (
        SELECT match_id, sum(rating) as diff
        FROM (
            SELECT match_id, team, avg(old_mean)*(case when team = 1 then 1 else -1 end) as rating
            FROM scoreboards
            WHERE match_perf is not NULL AND match_id = $1
            GROUP by match_id, team
        ) t
        GROUP by match_id
    ) t ON t.match_id = m.match_id
    WHERE
        m.match_id = $1;
    """.format(
        DATETIME_FORMAT=DATETIME_FORMAT
    )
    summary = await con.fetchval(query, match_id)
    if summary is None:
        raise MatchNotFound(match_id)

    query = """
    SELECT
        json_object_agg(t.steam_id, t.weapon_stats)
    FROM (
        SELECT
            t.steam_id::text,
            json_object_agg(t.weapon_short, ARRAY[t.frags, t.hits, t.shots]) AS weapon_stats
        FROM (
            SELECT
                s.steam_id,
                w.weapon_short,
                SUM(sw.frags) AS frags,
                SUM(sw.hits) AS hits,
                SUM(sw.shots) AS shots
            FROM
                scoreboards s
            RIGHT JOIN scoreboards_weapons sw ON sw.match_id = s.match_id AND sw.steam_id = s.steam_id AND sw.team = s.team
            LEFT JOIN weapons w ON w.weapon_id = sw.weapon_id
            WHERE
                s.match_id = $1
            GROUP BY s.steam_id, w.weapon_short
        ) t
        GROUP BY t.steam_id
    ) t;
    """
    player_weapon_stats = await con.fetchval(query, match_id)

    query = """
    SELECT
        json_object_agg(t.steam_id, t.medal_stats)
    FROM (
        SELECT
            t.steam_id::text,
            json_object_agg(t.medal_short, t.count) AS medal_stats
        FROM (
            SELECT
                s.steam_id,
                m.medal_short,
                SUM(sm.count) AS count
            FROM
                scoreboards s
            RIGHT JOIN scoreboards_medals sm ON sm.match_id = s.match_id AND sm.steam_id = s.steam_id AND sm.team = s.team
            LEFT JOIN medals m ON m.medal_id = sm.medal_id
            WHERE
                s.match_id = $1
            GROUP BY s.steam_id, m.medal_short
        ) t
        GROUP BY t.steam_id
    ) t;
    """
    player_medal_stats = await con.fetchval(query, match_id)

    query = """
    SELECT
        array_agg(item)
    FROM (
        SELECT
            json_build_object(
                'steam_id', t.steam_id::text,
                'team', t.team::text,
                'name', p.name,
                'stats', json_build_object(
                    'score',                t.score,
                    'frags',                t.frags,
                    'deaths',             t.deaths,
                    'damage_dealt', t.damage_dealt,
                    'damage_taken', t.damage_taken,
                    'alive_time',     t.alive_time
                ),
                'rating', json_build_object(
                    'old',     CAST( ROUND( CAST(t.old_mean            AS NUMERIC), 2) AS REAL ),
                    'old_d', CAST( ROUND( CAST(t.old_deviation AS NUMERIC), 2) AS REAL ),
                    'new',     CAST( ROUND( CAST(t.new_mean            AS NUMERIC), 2) AS REAL ),
                    'new_d', CAST( ROUND( CAST(t.new_deviation AS NUMERIC), 2) AS REAL )
                ),
                'medal_stats', ms.medal_stats,
                'weapon_stats', ws.weapon_stats
            ) AS item
        FROM
            scoreboards t
        LEFT JOIN players p ON p.steam_id = t.steam_id
        LEFT JOIN (
            SELECT
                t.steam_id, t.team,
                json_object_agg(t.weapon_short, ARRAY[t.frags, t.hits, t.shots, t.accuracy]) AS weapon_stats
            FROM
                (
                SELECT
                    s.steam_id, s.team, w.weapon_short, sw.frags, sw.hits, sw.shots,
                    CASE WHEN sw.shots = 0 THEN 0
                        ELSE CAST(100. * sw.hits / sw.shots AS INT)
                    END AS accuracy
                FROM
                    scoreboards s
                RIGHT JOIN scoreboards_weapons sw ON sw.match_id = s.match_id AND sw.steam_id = s.steam_id AND sw.team = s.team
                LEFT JOIN weapons w ON w.weapon_id = sw.weapon_id
                WHERE
                    s.match_id = $1
                ) t
                GROUP BY t.steam_id, t.team
        ) ws ON ws.steam_id = t.steam_id AND ws.team = t.team
        LEFT JOIN (
            SELECT
                t.steam_id, t.team,
                json_object_agg(t.medal_short, t.count) AS medal_stats
            FROM (
                SELECT
                    s.steam_id, s.team, m.medal_short, sm.count
                FROM
                    scoreboards s
                RIGHT JOIN scoreboards_medals sm ON sm.match_id = s.match_id AND sm.steam_id = s.steam_id AND sm.team = s.team
                LEFT JOIN medals m ON m.medal_id = sm.medal_id
                WHERE
                    s.match_id = $1
            ) t
            GROUP BY t.steam_id, t.team
        ) ms ON ms.steam_id = t.steam_id AND ms.team = t.team
        WHERE
            t.match_id = $1
        ORDER BY t.score DESC, t.steam_id ASC
    ) t
    """
    overall_stats = await con.fetchval(query, match_id)

    query = """
    SELECT
        array_agg(m.medal_short)
    FROM (
        SELECT DISTINCT medal_id
        FROM scoreboards_medals
        WHERE match_id = $1
        ) sm
     LEFT JOIN medals m ON m.medal_id = sm.medal_id
    """
    medals_available = await con.fetchval(query, match_id)
{'fraction_non_alphanumeric': 0.08499702026221692, 'fraction_numerical': 0.0038736591179976162, 'mean_word_length': 1.3765268189060011, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '103906', 'n_tokens_mistral': 4519, 'n_tokens_neox': 4160, 'n_words': 1026}