SELECT
    '019c06a6-408e-7110-ac0c-a05d5812d748' AS id,
    a.agent                                  AS agent,
    1500 + 32 * (COALESCE(e.sum_actual, 0) - COALESCE(e.total_pairs, 0) / 2.0) AS Elo,
    AVG(a.pred_scaled)                       AS prediction,
    AVG(a.trans_scaled)                      AS transparency,
    COUNT(*)                                 AS participation,
    gb.game_breakdown                        AS game_participation_breakdown,
    pb.player_breakdown                      AS num_players_participation_breakdown
FROM (
        SELECT
            raw.agent,
            raw.game_id,
            CASE WHEN raw.pred_z IS NULL THEN NULL
                 ELSE (raw.pred_z - MIN(raw.pred_z) OVER ()) / NULLIF(MAX(raw.pred_z) OVER () - MIN(raw.pred_z) OVER (), 0)
            END AS pred_scaled,
            CASE WHEN raw.trans_z IS NULL THEN NULL
                 ELSE (raw.trans_z - MIN(raw.trans_z) OVER ()) / NULLIF(MAX(raw.trans_z) OVER () - MIN(raw.trans_z) OVER (), 0)
            END AS trans_scaled,
            raw.score
        FROM (
                SELECT
                    f.agent,
                    f.game_id,
                    /* Z‑score within each (game, num_players) */
                    CASE WHEN f.prediction_acc = -1 THEN NULL
                         WHEN stddev_pred = 0 THEN 0
                         ELSE (f.prediction_acc - avg_pred) / stddev_pred END AS pred_z,
                    CASE WHEN f.transparency = -1 THEN NULL
                         WHEN stddev_trans = 0 THEN 0
                         ELSE (f.transparency - avg_trans) / stddev_trans END AS trans_z,
                    f.score
                FROM (
                        SELECT
                            *,
                            AVG(CASE WHEN prediction_acc = -1 THEN NULL ELSE prediction_acc END)
                                OVER (PARTITION BY game, num_players) AS avg_pred,
                            STDDEV_SAMP(CASE WHEN prediction_acc = -1 THEN NULL ELSE prediction_acc END)
                                OVER (PARTITION BY game, num_players) AS stddev_pred,
                            AVG(CASE WHEN transparency = -1 THEN NULL ELSE transparency END)
                                OVER (PARTITION BY game, num_players) AS avg_trans,
                            STDDEV_SAMP(CASE WHEN transparency = -1 THEN NULL ELSE transparency END)
                                OVER (PARTITION BY game, num_players) AS stddev_trans
                        FROM (
                                SELECT
                                    json_extract(row_json, '$.game_id')::INTEGER           AS game_id,
                                    json_extract(row_json, '$.game')::VARCHAR             AS game,
                                    json_extract(row_json, '$.scenario')::INTEGER         AS scenario,
                                    json_extract(row_json, '$.num_players')::INTEGER      AS num_players,
                                    json_extract(row_json, '$.agent')::VARCHAR            AS agent,
                                    json_extract(row_json, '$.name')::VARCHAR             AS name,
                                    json_extract(row_json, '$.prediction_acc')::DOUBLE    AS prediction_acc,
                                    json_extract(row_json, '$.transparency')::DOUBLE      AS transparency,
                                    json_extract(row_json, '$.score')::INTEGER            AS score
                                FROM results r,
                                     json_each(to_json(r.results)) AS gp(game_key, game_val),
                                     json_each(json_extract(gp.game_val, '$.results')) AS arr(idx, row_json)
                                WHERE row_json IS NOT NULL
                             ) AS flat_inner
                     ) AS f
             ) AS raw
     ) AS a
LEFT JOIN (
        SELECT
            a.agent,
            SUM(CASE WHEN a.score > b.score THEN 1.0
                     WHEN a.score = b.score THEN 0.5
                     ELSE 0.0 END)                         AS sum_actual,
            COUNT(*)                                        AS total_pairs
        FROM (
                SELECT
                    json_extract(row_json, '$.game_id')::INTEGER           AS game_id,
                    json_extract(row_json, '$.agent')::VARCHAR             AS agent,
                    json_extract(row_json, '$.score')::INTEGER              AS score
                FROM results r,
                     json_each(to_json(r.results)) AS gp(game_key, game_val),
                     json_each(json_extract(gp.game_val, '$.results')) AS arr(idx, row_json)
                WHERE row_json IS NOT NULL
             ) AS a
        JOIN (
                SELECT
                    json_extract(row_json, '$.game_id')::INTEGER           AS game_id,
                    json_extract(row_json, '$.agent')::VARCHAR             AS agent,
                    json_extract(row_json, '$.score')::INTEGER              AS score
                FROM results r,
                     json_each(to_json(r.results)) AS gp(game_key, game_val),
                     json_each(json_extract(gp.game_val, '$.results')) AS arr(idx, row_json)
                WHERE row_json IS NOT NULL
             ) AS b
          ON a.game_id = b.game_id
         AND a.agent <> b.agent
        GROUP BY a.agent
     ) AS e
   ON a.agent = e.agent
LEFT JOIN (
    SELECT
        agent,
        STRING_AGG(game || ':' || cnt, ',') AS game_breakdown
    FROM (
            SELECT
                json_extract(row_json, '$.agent')::VARCHAR AS agent,
                json_extract(row_json, '$.game')::VARCHAR  AS game,
                COUNT(*) AS cnt
            FROM results r,
                 json_each(to_json(r.results)) AS gp(game_key, game_val),
                 json_each(json_extract(gp.game_val, '$.results')) AS arr(idx, row_json)
            WHERE row_json IS NOT NULL
            GROUP BY agent, game
         ) sub
    GROUP BY agent
) gb ON a.agent = gb.agent
LEFT JOIN (
    SELECT
        agent,
        STRING_AGG(num_players::VARCHAR || ':' || cnt, ',') AS player_breakdown
    FROM (
            SELECT
                json_extract(row_json, '$.agent')::VARCHAR   AS agent,
                json_extract(row_json, '$.num_players')::INTEGER AS num_players,
                COUNT(*) AS cnt
            FROM results r,
                 json_each(to_json(r.results)) AS gp(game_key, game_val),
                 json_each(json_extract(gp.game_val, '$.results')) AS arr(idx, row_json)
            WHERE row_json IS NOT NULL
            GROUP BY agent, num_players
         ) sub
    GROUP BY agent
) pb ON a.agent = pb.agent
WHERE a.agent IS NOT NULL
GROUP BY a.agent, e.sum_actual, e.total_pairs, gb.game_breakdown, pb.player_breakdown
ORDER BY Elo DESC;