From 11507d00fb774e59afe2c304a6922f875865caee Mon Sep 17 00:00:00 2001
From: salmonik <salmonik@tuta.io>
Date: Wed, 19 Aug 2026 19:28:39 +0200
Subject: feat: leaderboads

---
 src/database/mod.rs | 145 ++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 145 insertions(+)

(limited to 'src/database/mod.rs')

diff --git a/src/database/mod.rs b/src/database/mod.rs
index f2185fb..b68d927 100644
--- a/src/database/mod.rs
+++ b/src/database/mod.rs
@@ -101,6 +101,18 @@ async fn run_migrations(pool: &DbPool) -> Result<()> {
     .execute(pool)
     .await?;
 
+    sqlx::query(
+        "CREATE TABLE IF NOT EXISTS user_stats (
+            user_id TEXT PRIMARY KEY,
+            message_count INTEGER NOT NULL DEFAULT 0,
+            total_voice_active REAL NOT NULL DEFAULT 0,
+            total_voice_afk REAL NOT NULL DEFAULT 0,
+            last_message_at TIMESTAMP
+        )",
+    )
+    .execute(pool)
+    .await?;
+
     Ok(())
 }
 
@@ -156,6 +168,139 @@ pub async fn upsert_voice_activity(pool: &DbPool, va: &VoiceActivity) -> Result<
     Ok(())
 }
 
+// -- User Stats (leaderboard) --
+#[derive(Debug, Clone, FromRow)]
+pub struct UserStats {
+    pub user_id: String,
+    pub message_count: i64,
+    pub total_voice_active: f64,
+    pub total_voice_afk: f64,
+}
+
+/// Increment message count for a user.
+pub async fn increment_message_count(pool: &DbPool, user_id: &str) -> Result<()> {
+    sqlx::query(
+        "INSERT INTO user_stats (user_id, message_count, last_message_at)
+         VALUES (?, 1, CURRENT_TIMESTAMP)
+         ON CONFLICT(user_id) DO UPDATE SET
+            message_count = message_count + 1,
+            last_message_at = CURRENT_TIMESTAMP",
+    )
+    .bind(user_id)
+    .execute(pool)
+    .await?;
+    Ok(())
+}
+
+/// Add voice time to a user's cumulative stats.
+pub async fn add_voice_time(
+    pool: &DbPool,
+    user_id: &str,
+    active_seconds: f64,
+    afk_seconds: f64,
+) -> Result<()> {
+    sqlx::query(
+        "INSERT INTO user_stats (user_id, total_voice_active, total_voice_afk)
+         VALUES (?, ?, ?)
+         ON CONFLICT(user_id) DO UPDATE SET
+            total_voice_active = total_voice_active + ?,
+            total_voice_afk = total_voice_afk + ?",
+    )
+    .bind(user_id)
+    .bind(active_seconds)
+    .bind(afk_seconds)
+    .bind(active_seconds)
+    .bind(afk_seconds)
+    .execute(pool)
+    .await?;
+    Ok(())
+}
+
+/// Get stats for a single user.
+pub async fn get_user_stats(pool: &DbPool, user_id: &str) -> Result<Option<UserStats>> {
+    Ok(
+        sqlx::query_as("SELECT * FROM user_stats WHERE user_id = ?")
+            .bind(user_id)
+            .fetch_optional(pool)
+            .await?,
+    )
+}
+
+/// Get leaderboard entries sorted by a metric.
+/// Returns (entries, total_count) where total_count is used for pagination.
+/// SAFETY: `order_clause` and `order` are derived from fixed match arms, not user input.
+pub async fn get_leaderboard(
+    pool: &DbPool,
+    metric: &str,
+    ascending: bool,
+    limit: i64,
+    offset: i64,
+) -> Result<(Vec<UserStats>, i64)> {
+    let order = if ascending { "ASC" } else { "DESC" };
+    // SAFETY: all arms are compile-time constant strings
+    #[allow(clippy::disallowed_methods)]
+    let order_clause = match metric {
+        "messages" => "message_count",
+        "active" => "total_voice_active",
+        "afk" => "total_voice_afk",
+        "total" => "total_voice_active + total_voice_afk",
+        _ => "message_count",
+    };
+
+    let total: (i64,) = sqlx::query_as("SELECT COUNT(*) FROM user_stats")
+        .fetch_one(pool)
+        .await?;
+
+    // SAFETY: order_clause and order are not user-controlled
+    let query = format!(
+        "SELECT * FROM user_stats ORDER BY {} {} LIMIT ? OFFSET ?",
+        order_clause, order
+    );
+    let entries: Vec<UserStats> = sqlx::query_as(sqlx::AssertSqlSafe(query))
+        .bind(limit)
+        .bind(offset)
+        .fetch_all(pool)
+        .await?;
+
+    Ok((entries, total.0))
+}
+
+/// Get a user's rank for a metric (1-indexed, 0 if not found).
+/// SAFETY: `order_clause` is derived from fixed match arms, not user input.
+pub async fn get_user_rank(pool: &DbPool, user_id: &str, metric: &str) -> Result<i64> {
+    // SAFETY: all arms are compile-time constant strings
+    #[allow(clippy::disallowed_methods)]
+    let order_clause = match metric {
+        "messages" => "message_count",
+        "active" => "total_voice_active",
+        "afk" => "total_voice_afk",
+        "total" => "total_voice_active + total_voice_afk",
+        _ => "message_count",
+    };
+
+    // SAFETY: order_clause is not user-controlled
+    let query = format!(
+        "SELECT COUNT(*) + 1 FROM user_stats WHERE {} > (
+            SELECT {} FROM user_stats WHERE user_id = ?
+        )",
+        order_clause, order_clause
+    );
+    let result: (i64,) = sqlx::query_as(sqlx::AssertSqlSafe(query))
+        .bind(user_id)
+        .fetch_one(pool)
+        .await?;
+
+    // If user doesn't exist, return 0
+    let user_exists: Option<(i64,)> = sqlx::query_as(
+        "SELECT 1 FROM user_stats WHERE user_id = ?"
+    )
+    .bind(user_id)
+    .fetch_optional(pool)
+    .await?;
+
+    Ok(if user_exists.is_some() { result.0 } else { 0 })
+}
+
 // -- Cron Jobs --
 #[derive(Debug, Clone, FromRow)]
 pub struct CronJob {
-- 
cgit v1.2.3