improved stats output

3 files changed, 166 insertions, 5 deletions

diff --git a/src/commands/meme/history.rs b/src/commands/meme/history.rs
index 051040f..28e4aaa 100644
--- a/src/commands/meme/history.rs
+++ b/src/commands/meme/history.rs
@@ -141,12 +141,23 @@ pub fn history(_: &mut Context, msg: &Message, mut args: Args) -> Result<()> {
 pub fn stats(_: &mut Context, msg: &Message, _: Args) -> Result<()> {
     use db;
     use chrono;
+    use serenity::model::{
+        id::UserId,
+        user::User,
+    };
+    use crate::TARGET_GUILD_ID;
 
     let conn = connection()?;
     let stats = db::stats(&conn)?;
 
     debug!("reporting stats");
 
+    let rand_user: User = UserId(stats.most_random_meme_user).to_user()?;
+    let direct_user: User = UserId(stats.most_directly_named_meme_user).to_user()?;
+
+    let rand_user = rand_user.nick_in(*TARGET_GUILD_ID).unwrap_or(rand_user.name);
+    let direct_user = direct_user.nick_in(*TARGET_GUILD_ID).unwrap_or(direct_user.name);
+
     let s = format!(
         r#"
 {} memes total
@@ -156,7 +167,16 @@ pub fn stats(_: &mut Context, msg: &Message, _: Args) -> Result<()> {
 started recording meme invocations on {} ({})
 {} total meme invocations recorded
 {} of which were random ({:0.1}%)
-and {} were audio ({:0.1}%)"#,
+and {} were audio ({:0.1}%)
+
+the most active day was {} with {} memes
+and the loudest day was {} with {} audio memes
+
+{} has invoked the most random memes ({})
+{} has invoked the most memes by name ({})
+
+{} was the meme most requested by name ({})
+and {} was the most-memed overall ({})"#,
         stats.memes_overall,
         stats.audio_memes,
         (stats.audio_memes as f64) / (stats.memes_overall as f64) * 100.,
@@ -169,6 +189,12 @@ and {} were audio ({:0.1}%)"#,
         (stats.random_meme_invocations as f64) / (stats.total_meme_invocations as f64) * 100.,
         stats.audio_meme_invocations,
         (stats.audio_meme_invocations as f64) / (stats.total_meme_invocations as f64) * 100.,
+        stats.most_active_day, stats.most_active_day_count,
+        stats.most_audio_active_day, stats.most_audio_active_count,
+        rand_user, stats.most_random_meme_user_count,
+        direct_user, stats.most_directly_named_meme_count,
+        stats.most_popular_named_meme, stats.most_popular_named_meme_count,
+        stats.most_popular_meme_overall, stats.most_popular_meme_overall_count,
     );
     send(msg.channel_id, s, msg.tts)
 }
diff --git a/src/db/mod.rs b/src/db/mod.rs
index 191b9e9..cd71e9e 100644
--- a/src/db/mod.rs
+++ b/src/db/mod.rs
@@ -4,6 +4,7 @@ use std::{
 };
 
 use chrono::{
+    Date,
     DateTime,
     Utc,
 };
@@ -12,6 +13,10 @@ use diesel::{
     prelude::*,
     r2d2::{ConnectionManager, ManageConnection},
 };
+use r2d2_postgres::{
+    PostgresConnectionManager as RawPgConnMgr,
+    TlsMode,
+};
 
 use crate::{Error, Result};
 
@@ -24,6 +29,7 @@ mod models;
 lazy_static! {
     static ref DB_URL: String = env::var("DATABASE_URL").expect("no database url in environment").into();
     static ref CONN_MGR: ConnectionManager<PgConnection> = ConnectionManager::new(DB_URL.clone());
+    static ref RAW_CONN_MGR: RawPgConnMgr = RawPgConnMgr::new(DB_URL.clone(), TlsMode::None).unwrap();
 }
 
 pub fn connection() -> Result<PgConnection> {
@@ -179,7 +185,7 @@ pub fn rand_silent_meme(conn: &PgConnection) -> Result<Meme> {
         .map_err(Error::from)
 }
 
-#[derive(Debug, Copy, Clone)]
+#[derive(Debug, Clone)]
 pub struct Stats {
     pub memes_overall: usize,
     pub audio_memes: usize,
@@ -188,11 +194,41 @@ pub struct Stats {
     pub total_meme_invocations: usize,
     pub audio_meme_invocations: usize,
     pub random_meme_invocations: usize,
+
+    pub most_active_day: Date<Utc>,
+    pub most_active_day_count: usize,
+
+    pub most_audio_active_day: Date<Utc>,
+    pub most_audio_active_count: usize,
+
+    pub most_random_meme_user: u64,
+    pub most_random_meme_user_count: usize,
+    pub most_directly_named_meme_user: u64,
+    pub most_directly_named_meme_count: usize,
+
+    pub most_popular_named_meme: String,
+    pub most_popular_named_meme_count: usize,
+
+    pub most_popular_meme_overall: String,
+    pub most_popular_meme_overall_count: usize,
 }
 
 pub fn stats(conn: &PgConnection) -> Result<Stats> {
     use diesel::dsl::{count_star, count};
-    use chrono::NaiveDateTime;
+    use chrono::{
+        NaiveDateTime,
+        NaiveDate,
+    };
+
+    #[inline]
+    fn to_utc(ndt: NaiveDateTime) -> DateTime<Utc> {
+        DateTime::from_utc(ndt, Utc{})
+    }
+
+    #[inline]
+    fn to_utc_date(nd: NaiveDate) -> Date<Utc> {
+        Date::from_utc(nd, Utc{})
+    }
 
     let total_count: i64 = memes::table
         .select(count_star())
@@ -217,7 +253,7 @@ pub fn stats(conn: &PgConnection) -> Result<Stats> {
         .first(conn)
         .map_err(Error::from)?;
 
-    let started_recording = DateTime::from_utc(started_recording, Utc{});
+    let started_recording = to_utc(started_recording);
 
     let total_meme_invocations: i64 = invocation_records::table
         .select(count_star())
@@ -237,6 +273,87 @@ pub fn stats(conn: &PgConnection) -> Result<Stats> {
         .first(conn)
         .map_err(Error::from)?;
 
+    let raw_conn = RAW_CONN_MGR.connect().map_err(Error::from)?;
+
+    let rows = raw_conn.query(r#"
+    SELECT DATE(time) as dt, COUNT(*) FROM invocation_records
+    GROUP BY dt
+    ORDER BY COUNT(*) DESC
+    LIMIT 1;
+    "#, &[])?;
+
+    let row = rows.get(0);
+
+    let most_active_day = to_utc_date(row.get(0));
+    let most_active_day_count: i64 = row.get(1);
+
+    let rows = raw_conn.query(r#"
+    SELECT DATE(time) as dt, COUNT(*) FROM invocation_records
+    INNER JOIN memes ON invocation_records.meme_id = memes.id
+    WHERE memes.audio_id IS NOT NULL
+    GROUP BY dt
+    ORDER BY COUNT(*) DESC
+    LIMIT 1;
+    "#, &[])?;
+
+    let row = rows.get(0);
+
+    let most_active_audio_day = to_utc_date(row.get(0));
+    let most_active_audio_day_count: i64 = row.get(1);
+
+    let rows = raw_conn.query(r#"
+    SELECT user_id, COUNT(*) FROM invocation_records
+    WHERE random IS TRUE
+    GROUP BY user_id
+    ORDER BY COUNT(*) DESC
+    LIMIT 1;
+    "#, &[])?;
+
+    let row = rows.get(0);
+
+    let most_random_invoker: i64 = row.get(0);
+    let most_random_invoker_count: i64 = row.get(1);
+
+    let rows = raw_conn.query(r#"
+    SELECT user_id, COUNT(*) FROM invocation_records
+    WHERE random IS FALSE
+    GROUP BY user_id
+    ORDER BY COUNT(*) DESC
+    LIMIT 1;
+    "#, &[])?;
+
+    let row = rows.get(0);
+
+    let most_specific_invoker: i64 = row.get(0);
+    let most_specific_invoker_count: i64 = row.get(1);
+
+    let rows = raw_conn.query(r#"
+    SELECT memes.title, COUNT(*) FROM invocation_records
+    INNER JOIN memes ON meme_id = memes.id
+    WHERE random IS FALSE
+    GROUP BY memes.title
+    ORDER BY COUNT(*) DESC
+    LIMIT 1;
+    "#, &[])?;
+
+    let row = rows.get(0);
+
+    let most_requested_meme = row.get(0);
+    let most_requested_meme_count: i64 = row.get(1);
+
+    let rows = raw_conn.query(r#"
+    SELECT memes.title, COUNT(*) FROM invocation_records
+    INNER JOIN memes ON meme_id = memes.id
+    GROUP BY memes.title
+    ORDER BY COUNT(*) DESC
+    LIMIT 1;
+    "#, &[])?;
+
+    let row = rows.get(0);
+
+    let most_invoked_meme = row.get(0);
+    let most_invoked_meme_count: i64 = row.get(1);
+
     Ok(Stats {
         memes_overall: total_count as usize,
         image_memes: image_count as usize,
@@ -245,5 +362,21 @@ pub fn stats(conn: &PgConnection) -> Result<Stats> {
         total_meme_invocations: total_meme_invocations as usize,
         audio_meme_invocations: audio_meme_invocations as usize,
         random_meme_invocations: random_meme_invocations as usize,
+
+        most_active_day,
+        most_active_day_count: most_active_day_count as usize,
+        most_audio_active_day: most_active_audio_day,
+        most_audio_active_count: most_active_audio_day_count as usize,
+
+        most_random_meme_user: most_random_invoker as u64,
+        most_random_meme_user_count: most_random_invoker_count as usize,
+        most_directly_named_meme_user: most_specific_invoker as u64,
+        most_directly_named_meme_count: most_specific_invoker_count as usize,
+
+        most_popular_named_meme: most_requested_meme,
+        most_popular_named_meme_count: most_requested_meme_count as usize,
+
+        most_popular_meme_overall: most_invoked_meme,
+        most_popular_meme_overall_count: most_invoked_meme_count as usize,
     })
 }
diff --git a/src/main.rs b/src/main.rs
index 1d1c6dd..d3458b6 100644
--- a/src/main.rs
+++ b/src/main.rs
@@ -7,7 +7,7 @@
 #![feature(box_syntax, box_patterns)]
 
 extern crate chrono;
-#[cfg(feature = "diesel")]
+#[cfg(feature = "db")]
 #[macro_use] extern crate diesel;
 extern crate dotenv;
 #[macro_use] extern crate dotenv_codegen;
@@ -20,6 +20,8 @@ extern crate fnv;
 #[macro_use] extern crate log;
 extern crate pest;
 #[macro_use] extern crate pest_derive;
+#[cfg(feature = "db")] extern crate postgres;
+#[cfg(feature = "db")] extern crate r2d2_postgres;
 extern crate rand;
 extern crate regex;
 extern crate reqwest;