From 11507d00fb774e59afe2c304a6922f875865caee Mon Sep 17 00:00:00 2001 From: salmonik Date: Wed, 19 Aug 2026 19:28:39 +0200 Subject: feat: leaderboads --- CONTEXT.md | 14 ++ Cargo.lock | 2 +- Cargo.toml | 2 +- src/commands/leaderboard.rs | 329 ++++++++++++++++++++++++++++++++++++++++++++ src/commands/mod.rs | 2 + src/database/mod.rs | 145 +++++++++++++++++++ src/events/mod.rs | 32 ++++- 7 files changed, 523 insertions(+), 3 deletions(-) create mode 100644 src/commands/leaderboard.rs diff --git a/CONTEXT.md b/CONTEXT.md index f39bb31..e272a7a 100644 --- a/CONTEXT.md +++ b/CONTEXT.md @@ -18,3 +18,17 @@ holds no implementation decisions or specs (those live in `docs/adr/`). - **Flush** — the `/record` action: snapshot the current recording buffer to an mp3 and upload it to Copyparty. Non-destructive — the buffer keeps recording after a flush, so successive calls may overlap. + +## Stats & Leaderboard + +- **User stats** — cumulative per-user counters (message count, total voice + active time, total voice AFK time) stored in the `user_stats` table. Persists + across restarts. Distinct from *voice activity* which is session-scoped. +- **Voice activity** (session) — per-session timing metadata (join time, current + active/AFK durations, is_afk flag). Resets when user joins VC. Used for + real-time status, not leaderboard rankings. +- **Leaderboard** — `/leaderboard` command group showing ranked user stats. + Metrics: `messages`, `active` (voice), `afk` (voice), `total` (voice active + + afk). Paged at 10 entries, descending by default. +- **Tracked guild** — the guild containing `status_channel_id`. Message counts + are only tracked in this guild. diff --git a/Cargo.lock b/Cargo.lock index 10dbabe..4031efa 100644 --- a/Cargo.lock +++ b/Cargo.lock @@ -4391,7 +4391,7 @@ dependencies = [ [[package]] name = "senator-sperma" -version = "0.3.0" +version = "0.4.0" dependencies = [ "chrono", "cron", diff --git a/Cargo.toml b/Cargo.toml index d311d17..7127862 100644 --- a/Cargo.toml +++ b/Cargo.toml @@ -1,6 +1,6 @@ [package] name = "senator-sperma" -version = "0.3.0" +version = "0.4.0" edition = "2024" [profile.dev] 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::().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, +) -> 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, +) -> 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, +) -> 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, +) -> 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 { + 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> { + 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> { 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> { + 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, 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 = 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 { + // 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 {} \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; -- cgit v1.2.3