feat: leaderboads

4 files changed, 507 insertions, 1 deletions

diff --git a/src/commands/leaderboard.rs b/src/commands/leaderboard.rs
new file mode 100644
index 0000000..94d67d1
--- /dev/null
+++ b/src/commands/leaderboard.rs
@@ -0,0 +1,329 @@
+use poise::serenity_prelude as serenity;
+
+use crate::Data;
+use crate::database;
+use crate::error::{Error, Result};
+
+type Context<'a> = poise::Context<'a, Data, Error>;
+
+const PAGE_SIZE: i64 = 10;
+
+/// Format seconds to human-readable duration
+fn format_duration(seconds: f64) -> String {
+    let total = seconds as u64;
+    let hours = total / 3600;
+    let minutes = (total % 3600) / 60;
+    if hours > 0 {
+        format!("{}h {}m", hours, minutes)
+    } else {
+        format!("{}m", minutes)
+    }
+}
+
+/// Format number with thousands separator
+fn format_number(n: i64) -> String {
+    let s = n.to_string();
+    let mut result = String::new();
+    for (i, c) in s.chars().rev().enumerate() {
+        if i > 0 && i % 3 == 0 {
+            result.push(',');
+        }
+        result.push(c);
+    }
+    result.chars().rev().collect()
+}
+
+/// Get rank suffix (1st, 2nd, 3rd, etc.)
+fn rank_suffix(rank: i64) -> String {
+    let suffix = if rank % 100 >= 11 && rank % 100 <= 13 {
+        "th"
+    } else {
+        match rank % 10 {
+            1 => "st",
+            2 => "nd",
+            3 => "rd",
+            _ => "th",
+        }
+    };
+    format!("{}{}", rank, suffix)
+}
+
+/// Resolve user ID to display name
+async fn resolve_display_name(ctx: Context<'_>, user_id: &str) -> String {
+    let id = user_id.parse::<u64>().unwrap_or(0);
+    let user_id_obj = serenity::UserId::new(id);
+
+    // Try to get from cache first
+    if let Some(user) = ctx.cache().user(user_id_obj) {
+        return user.name.clone();
+    }
+
+    // Fallback to API
+    if let Ok(user) = user_id_obj.to_user(ctx.http()).await {
+        return user.name;
+    }
+
+    user_id.to_string()
+}
+
+/// Create the stats embed for a user
+async fn create_stats_embed(
+    ctx: Context<'_>,
+    user_id: &str,
+    stats: &database::UserStats,
+) -> serenity::CreateEmbed {
+    let display_name = resolve_display_name(ctx, user_id).await;
+
+    // Get ranks for each metric
+    let msg_rank = database::get_user_rank(&ctx.data().db_pool, user_id, "messages").await.unwrap_or(0);
+    let active_rank = database::get_user_rank(&ctx.data().db_pool, user_id, "active").await.unwrap_or(0);
+    let afk_rank = database::get_user_rank(&ctx.data().db_pool, user_id, "afk").await.unwrap_or(0);
+    let total_rank = database::get_user_rank(&ctx.data().db_pool, user_id, "total").await.unwrap_or(0);
+
+    let rank_str = |rank: i64| if rank > 0 { format!(" (#{})", rank_suffix(rank)) } else { " (unranked)".to_string() };
+
+    let total_voice = stats.total_voice_active + stats.total_voice_afk;
+
+    serenity::CreateEmbed::default()
+        .title(format!("Stats for {}", display_name))
+        .color(serenity::Color::from_rgb(59, 130, 246))
+        .field(
+            "Messages",
+            format!("{}{}", format_number(stats.message_count), rank_str(msg_rank)),
+            true,
+        )
+        .field(
+            "Voice Active",
+            format!("{}{}", format_duration(stats.total_voice_active), rank_str(active_rank)),
+            true,
+        )
+        .field(
+            "Voice AFK",
+            format!("{}{}", format_duration(stats.total_voice_afk), rank_str(afk_rank)),
+            true,
+        )
+        .field(
+            "Total Voice",
+            format!("{}{}", format_duration(total_voice), rank_str(total_rank)),
+            true,
+        )
+        .footer(serenity::CreateEmbedFooter::new("Use /leaderboard messages|active|afk|total to see full leaderboard"))
+}
+
+/// Create the leaderboard embed
+async fn create_leaderboard_embed(
+    ctx: Context<'_>,
+    metric: &str,
+    ascending: bool,
+    page: i64,
+) -> Result<(serenity::CreateEmbed, i64)> {
+    let offset = (page - 1) * PAGE_SIZE;
+    let (entries, total_count) = database::get_leaderboard(
+        &ctx.data().db_pool,
+        metric,
+        ascending,
+        PAGE_SIZE,
+        offset,
+    )
+    .await?;
+
+    let total_pages = (total_count as f64 / PAGE_SIZE as f64).ceil() as i64;
+    let total_pages = total_pages.max(1);
+
+    let title = match metric {
+        "messages" => "Messages Leaderboard",
+        "active" => "Voice Active Leaderboard",
+        "afk" => "Voice AFK Leaderboard",
+        "total" => "Total Voice Leaderboard",
+        _ => "Leaderboard",
+    };
+
+    let direction = if ascending { "↑" } else { "↓" };
+
+    let mut description = String::new();
+    for (i, entry) in entries.iter().enumerate() {
+        let rank = offset + i as i64 + 1;
+        let display_name = resolve_display_name(ctx, &entry.user_id).await;
+
+        let value = match metric {
+            "messages" => format_number(entry.message_count),
+            "active" => format_duration(entry.total_voice_active),
+            "afk" => format_duration(entry.total_voice_afk),
+            "total" => format_duration(entry.total_voice_active + entry.total_voice_afk),
+            _ => format_number(entry.message_count),
+        };
+
+        description.push_str(&format!("**#{}** — {} — {}\n", rank_suffix(rank), display_name, value));
+    }
+
+    if description.is_empty() {
+        description = "No entries yet.".to_string();
+    }
+
+    // Get current user's rank and stats for footer
+    let user_id = ctx.author().id.to_string();
+    let user_stats = database::get_user_stats(&ctx.data().db_pool, &user_id).await?;
+    let user_rank = database::get_user_rank(&ctx.data().db_pool, &user_id, metric).await.unwrap_or(0);
+
+    let footer_text = if let Some(stats) = &user_stats {
+        let user_value = match metric {
+            "messages" => format_number(stats.message_count),
+            "active" => format_duration(stats.total_voice_active),
+            "afk" => format_duration(stats.total_voice_afk),
+            "total" => format_duration(stats.total_voice_active + stats.total_voice_afk),
+            _ => format_number(stats.message_count),
+        };
+        if user_rank > 0 {
+            format!("Your rank: #{} ({})", rank_suffix(user_rank), user_value)
+        } else {
+            "You are not ranked yet".to_string()
+        }
+    } else {
+        "You are not ranked yet".to_string()
+    };
+
+    let embed = serenity::CreateEmbed::default()
+        .title(format!("{} {}", title, direction))
+        .description(description)
+        .color(serenity::Color::from_rgb(59, 130, 246))
+        .footer(serenity::CreateEmbedFooter::new(footer_text))
+        .field("Page", format!("{}/{}", page, total_pages), true);
+
+    Ok((embed, total_pages))
+}
+
+/// Show your personal stats
+#[poise::command(slash_command)]
+pub async fn me(ctx: Context<'_>) -> Result<()> {
+    let user_id = ctx.author().id.to_string();
+    let stats = database::get_user_stats(&ctx.data().db_pool, &user_id).await?;
+
+    let embed = if let Some(stats) = &stats {
+        create_stats_embed(ctx, &user_id, stats).await
+    } else {
+        // User has no stats yet - show zeros
+        let empty_stats = database::UserStats {
+            user_id: user_id.clone(),
+            message_count: 0,
+            total_voice_active: 0.0,
+            total_voice_afk: 0.0,
+        };
+        create_stats_embed(ctx, &user_id, &empty_stats).await
+    };
+
+    ctx.send(
+        poise::CreateReply::default()
+            .embed(embed),
+    )
+    .await?;
+    Ok(())
+}
+
+/// Show messages leaderboard
+#[poise::command(slash_command)]
+pub async fn messages(
+    ctx: Context<'_>,
+    #[description = "Sort order (default: descending)"]
+    #[autocomplete = "sort_order_autocomplete"]
+    order: Option<String>,
+) -> Result<()> {
+    let ascending = order.as_deref() == Some("ascending");
+    let page = 1;
+
+    let (embed, _total_pages) = create_leaderboard_embed(ctx, "messages", ascending, page).await?;
+
+    ctx.send(
+        poise::CreateReply::default()
+            .embed(embed),
+    )
+    .await?;
+    Ok(())
+}
+
+/// Show voice active leaderboard
+#[poise::command(slash_command)]
+pub async fn active(
+    ctx: Context<'_>,
+    #[description = "Sort order (default: descending)"]
+    #[autocomplete = "sort_order_autocomplete"]
+    order: Option<String>,
+) -> Result<()> {
+    let ascending = order.as_deref() == Some("ascending");
+    let page = 1;
+
+    let (embed, _total_pages) = create_leaderboard_embed(ctx, "active", ascending, page).await?;
+
+    ctx.send(
+        poise::CreateReply::default()
+            .embed(embed),
+    )
+    .await?;
+    Ok(())
+}
+
+/// Show voice AFK leaderboard
+#[poise::command(slash_command)]
+pub async fn afk(
+    ctx: Context<'_>,
+    #[description = "Sort order (default: descending)"]
+    #[autocomplete = "sort_order_autocomplete"]
+    order: Option<String>,
+) -> Result<()> {
+    let ascending = order.as_deref() == Some("ascending");
+    let page = 1;
+
+    let (embed, _total_pages) = create_leaderboard_embed(ctx, "afk", ascending, page).await?;
+
+    ctx.send(
+        poise::CreateReply::default()
+            .embed(embed),
+    )
+    .await?;
+    Ok(())
+}
+
+/// Show total voice leaderboard
+#[poise::command(slash_command)]
+pub async fn total(
+    ctx: Context<'_>,
+    #[description = "Sort order (default: descending)"]
+    #[autocomplete = "sort_order_autocomplete"]
+    order: Option<String>,
+) -> Result<()> {
+    let ascending = order.as_deref() == Some("ascending");
+    let page = 1;
+
+    let (embed, _total_pages) = create_leaderboard_embed(ctx, "total", ascending, page).await?;
+
+    ctx.send(
+        poise::CreateReply::default()
+            .embed(embed),
+    )
+    .await?;
+    Ok(())
+}
+
+/// Autocomplete for sort order
+async fn sort_order_autocomplete(
+    _ctx: Context<'_>,
+    _partial: &str,
+) -> Vec<serenity::AutocompleteChoice> {
+    vec![
+        serenity::AutocompleteChoice::new("Descending (highest first)", "descending"),
+        serenity::AutocompleteChoice::new("Ascending (lowest first)", "ascending"),
+    ]
+}
+
+/// Leaderboard commands
+#[poise::command(
+    slash_command,
+    subcommands("me", "messages", "active", "afk", "total"),
+    subcommand_required
+)]
+pub async fn leaderboard(_ctx: Context<'_>) -> Result<()> {
+    Ok(())
+}
+
+pub fn commands() -> Vec<poise::Command<Data, Error>> {
+    vec![leaderboard()]
+}
diff --git a/src/commands/mod.rs b/src/commands/mod.rs
index 9d6a1d0..12e2a3d 100644
--- a/src/commands/mod.rs
+++ b/src/commands/mod.rs
@@ -8,6 +8,7 @@ pub mod cron;
 pub mod trigger;
 pub mod utilities;
 pub mod autocomplete;
+pub mod leaderboard;
 
 use crate::Data;
 
@@ -22,5 +23,6 @@ pub fn all_commands() -> Vec<poise::Command<Data, crate::error::Error>> {
     cmds.extend(cron::commands());
     cmds.extend(trigger::commands());
     cmds.extend(utilities::commands());
+    cmds.extend(leaderboard::commands());
     cmds
 }
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 {
diff --git a/src/events/mod.rs b/src/events/mod.rs
index 22fe976..ae0e1e4 100644
--- a/src/events/mod.rs
+++ b/src/events/mod.rs
@@ -172,6 +172,22 @@ pub async fn handle_voice_state_update(
             let active_str = format_duration(va.total_active);
             let afk_str = format_duration(va.total_afk);
 
+            // Accumulate to user_stats if in tracked guild
+            if let Some(status_channel_id) = data.config.status_channel_id
+                && let Some(gid) = guild_id {
+                    let is_tracked = ctx.cache.guild(gid)
+                        .map(|g| g.channels.contains_key(&serenity::ChannelId::new(status_channel_id)))
+                        .unwrap_or(false);
+                    if is_tracked {
+                        database::add_voice_time(
+                            &data.db_pool,
+                            &user_id_str,
+                            va.total_active,
+                            va.total_afk,
+                        ).await?;
+                    }
+                }
+
             if let Some(lc) = log_channel {
                 let msg = format!(
                     "[{current_time}] {member_name} rozłączył się z {} <t:{current_ts}:R>\n\
@@ -324,10 +340,24 @@ pub async fn handle_message(
     data: &Data,
     new_message: &serenity::Message,
 ) -> Result<()> {
-    if new_message.author.id == ctx.cache.current_user().id {
+    // Ignore own messages and other bots
+    if new_message.author.id == ctx.cache.current_user().id || new_message.author.bot {
         return Ok(());
     }
 
+    // Track message count in the tracked guild
+    if let Some(status_channel_id) = data.config.status_channel_id
+        && let Some(guild_id) = new_message.guild_id {
+            // Check if the status channel is in the same guild as the message
+            let is_tracked_guild = ctx.cache.guild(guild_id)
+                .map(|g| g.channels.contains_key(&serenity::ChannelId::new(status_channel_id)))
+                .unwrap_or(false);
+            if is_tracked_guild {
+                database::increment_message_count(&data.db_pool, &new_message.author.id.to_string())
+                    .await?;
+            }
+        }
+
     let content_lower = new_message.content.to_lowercase();
     let response_to_send = {
         let triggers = data.triggers.lock().await;