add new bot stats endpoint

4 files changed, 94 insertions, 1 deletions

diff --git a/planetwars-server/src/db/matches.rs b/planetwars-server/src/db/matches.rs
index 2041296..5e0c5ad 100644
--- a/planetwars-server/src/db/matches.rs
+++ b/planetwars-server/src/db/matches.rs
@@ -4,6 +4,7 @@ use diesel::associations::BelongsTo;
 use diesel::pg::Pg;
 use diesel::query_builder::BoxedSelectStatement;
 use diesel::query_source::{AppearsInFromClause, Once};
+use diesel::sql_types::*;
 use diesel::{
     BelongingToDsl, ExpressionMethods, JoinOnDsl, NullableExpressionMethods, QueryDsl, RunQueryDsl,
 };
@@ -294,3 +295,55 @@ pub fn save_match_result(id: i32, result: MatchResult, conn: &PgConnection) -> Q
         .execute(conn)?;
     Ok(())
 }
+
+#[derive(QueryableByName)]
+pub struct BotStatsRecord {
+    #[sql_type = "Text"]
+    pub opponent: String,
+    #[sql_type = "Text"]
+    pub map: String,
+    #[sql_type = "Nullable<Bool>"]
+    pub win: Option<bool>,
+    #[sql_type = "Int8"]
+    pub count: i64,
+}
+
+pub fn fetch_bot_stats(bot_name: &str, db_conn: &PgConnection) -> QueryResult<Vec<BotStatsRecord>> {
+    diesel::sql_query(
+        "
+SELECT opponent, map, win, COUNT(*) as count
+FROM (
+    SELECT
+        opponent_bot.name as opponent,
+        maps.name as map,
+        (matches.winner = bot_player.player_id) as win
+    FROM matches
+    JOIN maps
+        ON matches.map_id = maps.id
+    JOIN match_players bot_player
+        ON bot_player.match_id = matches.id
+    JOIN bot_versions bot_version
+        ON bot_version.id = bot_player.bot_version_id
+    JOIN bots bot
+        ON bot.id = bot_version.bot_id
+    JOIN match_players opponent_player
+        ON opponent_player.match_id = matches.id
+        AND opponent_player.player_id = 1 - bot_player.player_id
+    JOIN bot_versions opponent_version
+        ON opponent_version.id = opponent_player.bot_version_id
+    LEFT OUTER JOIN bots opponent_bot
+        ON opponent_version.bot_id = opponent_bot.id
+    WHERE
+        matches.state = 'finished'
+        AND matches.is_public
+        AND bot.name = $1
+    ORDER BY
+        matches.created_at DESC 
+    LIMIT 10000
+) bot_matches
+GROUP BY opponent, map, win
+HAVING opponent IS NOT NULL",
+    )
+    .bind::<Text, _>(bot_name)
+    .load(db_conn)
+}
diff --git a/planetwars-server/src/lib.rs b/planetwars-server/src/lib.rs
index 62bf198..1696f1a 100644
--- a/planetwars-server/src/lib.rs
+++ b/planetwars-server/src/lib.rs
@@ -125,6 +125,7 @@ fn api() -> Router {
             get(routes::bots::list_bots).post(routes::bots::create_bot),
         )
         .route("/bots/:bot_name", get(routes::bots::get_bot))
+        .route("/bots/:bot_name/stats", get(routes::bots::get_bot_stats))
         .route(
             "/bots/:bot_name/upload",
             post(routes::bots::upload_code_multipart),
diff --git a/planetwars-server/src/modules/bots.rs b/planetwars-server/src/modules/bots.rs
index 4aba168..6a2883c 100644
--- a/planetwars-server/src/modules/bots.rs
+++ b/planetwars-server/src/modules/bots.rs
@@ -24,7 +24,7 @@ pub fn save_code_string(
         container_digest: None,
     };
     let version = db::bots::create_bot_version(&new_code_bundle, conn)?;
-    // Leave this coupled for now - this is how the behaviour was bevore.
+    // Leave this coupled for now - this is how the behaviour was before.
     // It would be cleaner to separate version setting and bot selection, though.
     if let Some(bot_id) = bot_id {
         db::bots::set_active_version(bot_id, Some(version.id), conn)?;
diff --git a/planetwars-server/src/routes/bots.rs b/planetwars-server/src/routes/bots.rs
index 4ab1b4e..f8087fd 100644
--- a/planetwars-server/src/routes/bots.rs
+++ b/planetwars-server/src/routes/bots.rs
@@ -7,6 +7,7 @@ use rand::distributions::Alphanumeric;
 use rand::Rng;
 use serde::{Deserialize, Serialize};
 use serde_json::{self, json, value::Value as JsonValue};
+use std::collections::HashMap;
 use std::io::Cursor;
 use std::path::PathBuf;
 use std::sync::Arc;
@@ -275,3 +276,41 @@ pub async fn get_code(
         std::fs::read(full_bundle_path).map_err(|_| StatusCode::INTERNAL_SERVER_ERROR)?;
     Ok(bot_code)
 }
+
+#[derive(Default, Serialize, Deserialize)]
+pub struct MatchupStats {
+    win: i64,
+    loss: i64,
+    tie: i64,
+}
+
+impl MatchupStats {
+    fn update(&mut self, win: Option<bool>, count: i64) {
+        match win {
+            Some(true) => self.win += count,
+            Some(false) => self.loss += count,
+            None => self.tie += count,
+        }
+    }
+}
+
+type BotStats = HashMap<String, HashMap<String, MatchupStats>>;
+
+pub async fn get_bot_stats(
+    conn: DatabaseConnection,
+    Path(bot_name): Path<String>,
+) -> Result<Json<BotStats>, StatusCode> {
+    let stats_records = db::matches::fetch_bot_stats(&bot_name, &conn)
+        .map_err(|_| StatusCode::INTERNAL_SERVER_ERROR)?;
+    let mut bot_stats: BotStats = HashMap::new();
+    for record in stats_records {
+        bot_stats
+            .entry(record.opponent)
+            .or_default()
+            .entry(record.map)
+            .or_default()
+            .update(record.win, record.count);
+    }
+
+    Ok(Json(bot_stats))
+}