SELECT
  r.participants.purple_agent AS id,
  ROUND(
    1500 + SUM(
      32 * (
        (CASE WHEN g.won THEN 1 ELSE 0 END)
        - 1.0 / (1 + POW(10, 0 / 400.0))
      )
    ),
    0
  ) AS "ELO Rating",
  ROUND(AVG(CASE WHEN g.won THEN 1 ELSE 0 END) * 100, 1) AS "Win Rate (%)",
  COUNT(*) AS "Games Played"
FROM results r
CROSS JOIN UNNEST(r.results) AS rr(run)
CROSS JOIN UNNEST(run.games) AS gg(g)
GROUP BY id
ORDER BY "ELO Rating" DESC, id

SELECT
  r.participants.purple_agent AS id,
  SUM(run.num_games) AS "Total Games",
  SUM(run.games_completed) AS "Games Completed",
  ROUND(SUM(run.num_games * run.win_rate) / NULLIF(SUM(run.num_games), 0) * 100, 1) AS "Win Rate (%)",
  ROUND(SUM(run.num_games * run.survival_rate) / NULLIF(SUM(run.num_games), 0) * 100, 1) AS "Survival Rate (%)",
  ROUND(SUM(run.num_games * run.average_irs) / NULLIF(SUM(run.num_games), 0) * 100, 1) AS "Average IRS (%)",
  ROUND(SUM(run.num_games * run.average_vrs) / NULLIF(SUM(run.num_games), 0) * 100, 1) AS "Average VRS (%)",
  ROUND(SUM(run.num_games * run.average_mss) / NULLIF(SUM(run.num_games), 0) * 100, 1) AS "Average MSS (%)",
  ROUND(SUM(run.num_games * run.average_persuasion) / NULLIF(SUM(run.num_games), 0) * 100, 1) AS "Average Persuasion (%)",
  ROUND(SUM(run.num_games * run.average_deception) / NULLIF(SUM(run.num_games), 0) * 100, 1) AS "Average Deception (%)",
  ROUND(SUM(run.num_games * run.average_manipulation_success_d1) / NULLIF(SUM(run.num_games), 0) * 100, 1) AS "Avg D1 Manipulation (%)",
  ROUND(SUM(run.num_games * run.average_manipulation_success_d2) / NULLIF(SUM(run.num_games), 0) * 100, 1) AS "Avg D2 Manipulation (%)",
  ROUND(SUM(run.num_games * run.average_auto_sabotage) / NULLIF(SUM(run.num_games), 0) * 100, 1) AS "Avg Auto-Sabotage (%)"
FROM results r
CROSS JOIN UNNEST(r.results) AS rr(run)
GROUP BY id
ORDER BY "Win Rate (%)" DESC, "Survival Rate (%)" DESC, id

SELECT
  r.participants.purple_agent AS id,
  ROUND(SUM(run.num_games * COALESCE(try_cast(run.average_seer_check_accuracy AS DOUBLE), 0.0)) / NULLIF(SUM(run.num_games), 0) * 100, 1) AS "Seer Check Accuracy (%)",
  ROUND(SUM(run.num_games * COALESCE(try_cast(run.average_witch_heal_effectiveness AS DOUBLE), 0.0)) / NULLIF(SUM(run.num_games), 0) * 100, 1) AS "Witch Heal Effectiveness (%)",
  ROUND(SUM(run.num_games * COALESCE(try_cast(run.average_witch_poison_effectiveness AS DOUBLE), 0.0)) / NULLIF(SUM(run.num_games), 0) * 100, 1) AS "Witch Poison Effectiveness (%)",
  ROUND(SUM(run.num_games * COALESCE(try_cast(run.average_hunter_shot_accuracy AS DOUBLE), 0.0)) / NULLIF(SUM(run.num_games), 0) * 100, 1) AS "Hunter Shot Accuracy (%)",
  ROUND(SUM(run.num_games * COALESCE(try_cast(run.average_guard_protection_success AS DOUBLE), 0.0)) / NULLIF(SUM(run.num_games), 0) * 100, 1) AS "Guard Protection Success (%)"
FROM results r
CROSS JOIN UNNEST(r.results) AS rr(run)
GROUP BY id
ORDER BY id

SELECT
  r.participants.purple_agent AS id,
  g.role AS "Role",
  COUNT(*) AS "Games"
FROM results r
CROSS JOIN UNNEST(r.results) AS rr(run)
CROSS JOIN UNNEST(run.games) AS gg(g)
GROUP BY id, "Role"
ORDER BY id, "Games" DESC, "Role"

SELECT
  r.participants.purple_agent AS id,
  g.role AS "Role",
  COUNT(*) AS "Games",
  ROUND(AVG(CASE WHEN g.won THEN 1 ELSE 0 END) * 100, 1) AS "Win Rate (%)",
  ROUND(AVG(CASE WHEN g.survived THEN 1 ELSE 0 END) * 100, 1) AS "Survival Rate (%)"
FROM results r
CROSS JOIN UNNEST(r.results) AS rr(run)
CROSS JOIN UNNEST(run.games) AS gg(g)
GROUP BY id, "Role"
ORDER BY id, "Games" DESC, "Role"

SELECT
  r.participants.purple_agent AS id,
  g.game_number AS "Game #",
  g.role AS "Role",
  g.winner AS "Winner",
  g.won AS "Won",
  g.survived AS "Survived",
  g.rounds AS "Rounds",
  g.metrics.irs AS "IRS",
  g.metrics.vrs AS "VRS",
  g.metrics.mss AS "MSS",
  g.metrics.persuasion_score AS "Persuasion",
  g.metrics.deception_score AS "Deception",
  g.metrics.seer_check_accuracy AS "Seer Check Accuracy",
  g.metrics.witch_heal_effectiveness AS "Witch Heal",
  g.metrics.witch_poison_effectiveness AS "Witch Poison",
  g.metrics.hunter_shot_accuracy AS "Hunter Shot Accuracy",
  g.metrics.guard_protection_success AS "Guard Protection",
  g.metrics.manipulation_success_d1 AS "D1 Manipulation",
  g.metrics.manipulation_success_d2 AS "D2 Manipulation",
  g.metrics.auto_sabotage AS "Auto-Sabotage"
FROM results r
CROSS JOIN UNNEST(r.results) AS rr(run)
CROSS JOIN UNNEST(run.games) AS gg(g)
ORDER BY id, "Game #"