write batch_delmeme

4 files changed, 139 insertions, 42 deletions

diff --git a/src/bin/batch_delmeme.rs b/src/bin/batch_delmeme.rs
new file mode 100644
index 0000000..99c57fe
--- /dev/null
+++ b/src/bin/batch_delmeme.rs
@@ -0,0 +1,50 @@
+use clap::Parser;
+use diesel_async::AsyncPgConnection;
+use thulani::db;
+
+#[derive(clap::Parser)]
+struct Opts {
+    #[arg(short, long)]
+    user_id: u64,
+
+    #[arg(short, long)]
+    deleter: u64,
+
+    #[arg(long)]
+    demo_meme: bool,
+}
+
+async fn create_demo_meme(conn: &mut AsyncPgConnection, owner: u64) -> anyhow::Result<()> {
+    let meta = db::Metadata::create(conn, owner).await?;
+
+    let meme = db::NewMeme {
+        title:       "my new meme".to_owned(),
+        content:     Some("abc".to_owned()),
+        metadata_id: meta.id,
+        audio_id:    None,
+        image_id:    None,
+    };
+    let meme = meme.save(conn, owner).await?;
+    log::debug!("created demo meme: {meme:#?}");
+
+    Ok(())
+}
+
+#[tokio::main]
+pub async fn main() -> anyhow::Result<()> {
+    thulani::log_setup::init(false).expect("initializing logging");
+    dotenv::dotenv()?;
+
+    let opts = Opts::parse();
+
+    let mut conn = db::connection().await?;
+
+    if opts.demo_meme {
+        create_demo_meme(&mut conn, opts.user_id).await?;
+    }
+
+    let memes_deleted = db::del_memes_by_userid(&mut conn, opts.user_id, opts.deleter).await?;
+    log::info!("deleted memes: {memes_deleted:?}");
+
+    Ok(())
+}
diff --git a/src/commands/meme/history.rs b/src/commands/meme/history.rs
index 335603f..9651ba1 100644
--- a/src/commands/meme/history.rs
+++ b/src/commands/meme/history.rs
@@ -1,3 +1,4 @@
+use chrono::TimeZone;
 use diesel::{
     result::Error as DieselError,
     NotFound,
@@ -133,7 +134,7 @@ pub async fn history(ctx: PoiseContext<'_>, n: Option<usize>) -> anyhow::Result<
         .then(|(i, rec)| async move {
             let mut conn = connection().await?;
 
-            let dt = chrono::DateTime::from_utc(rec.time, chrono::Utc {});
+            let dt = chrono::Utc.from_utc_datetime(&rec.time);
             let ago = TIME_FORMATTER.convert((chrono::Utc::now() - dt).to_std().unwrap());
 
             let rand = if rec.random {
@@ -246,7 +247,7 @@ and *{}* was the most-memed overall ({})"#,
         (stats.audio_memes as f64) / (stats.memes_overall as f64) * 100.,
         stats.image_memes,
         (stats.image_memes as f64) / (stats.memes_overall as f64) * 100.,
-        stats.started_recording.date().format(CLEAN_DATE_FORMAT),
+        stats.started_recording.naive_local().date().format(CLEAN_DATE_FORMAT),
         TIME_FORMATTER.convert((chrono::Utc::now() - stats.started_recording).to_std().unwrap()),
         stats.total_meme_invocations,
         stats.random_meme_invocations,
diff --git a/src/db/mod.rs b/src/db/mod.rs
index 87b1a8d..0a6ec9c 100644
--- a/src/db/mod.rs
+++ b/src/db/mod.rs
@@ -6,11 +6,13 @@ use std::{
 
 use anyhow::anyhow;
 use chrono::{
-    Date,
     DateTime,
+    NaiveDate,
+    TimeZone,
     Utc,
 };
 use deadpool_postgres::{
+    GenericClient,
     Pool as RawPgConnMgr,
     PoolConfig,
 };
@@ -30,14 +32,13 @@ use diesel_async::{
 };
 use serenity::FutureExt;
 
+pub use self::models::*;
+use self::schema::*;
 use crate::{
     Error,
     Result,
 };
 
-pub use self::models::*;
-use self::schema::*;
-
 mod models;
 mod schema;
 
@@ -177,6 +178,52 @@ pub async fn query_meme<T: AsRef<str>>(
     Ok(result)
 }
 
+pub async fn del_memes_by_userid(
+    _conn: &mut AsyncPgConnection,
+    user_id: u64,
+    deleted_by: u64,
+) -> Result<Vec<u64>> {
+    let user_id = user_id as i64;
+    let deleted_by = deleted_by as i64;
+
+    let raw_conn = raw_connection().await?;
+
+    let result = raw_conn
+        .query(
+            r#"
+        WITH
+            meme_deletes AS (
+                DELETE FROM memes
+                    WHERE metadata_id IN (SELECT id FROM metadata WHERE created_by = $1)
+                    RETURNING id, metadata_id
+            )
+            , image_deletes AS (
+                DELETE FROM images
+                    WHERE (SELECT COUNT(*) FROM memes WHERE memes.image_id = images.id) = 0
+                    RETURNING id, metadata_id
+            )
+            , audio_deletes AS (
+                DELETE FROM audio
+                    WHERE (SELECT COUNT(*) FROM memes WHERE memes.audio_id = audio.id) = 0
+                    RETURNING id, metadata_id
+            )
+            , tombstone_inserts AS (
+                INSERT INTO tombstones (meme_id, metadata_id, deleted_at, deleted_by)
+                    SELECT id, metadata_id, NOW(), $2 FROM meme_deletes
+                    RETURNING meme_id, metadata_id
+            )
+
+        SELECT meme_id
+        FROM tombstone_inserts
+        ;
+    "#,
+            &[&user_id, &deleted_by],
+        )
+        .await?;
+
+    Ok(result.into_iter().map(|x| x.get::<_, i32>(0) as u64).collect())
+}
+
 pub async fn delete_meme<T: AsRef<str>>(
     conn: &mut AsyncPgConnection,
     search: T,
@@ -372,10 +419,10 @@ pub struct Stats {
     pub audio_meme_invocations:  usize,
     pub random_meme_invocations: usize,
 
-    pub most_active_day:       Date<Utc>,
+    pub most_active_day:       NaiveDate,
     pub most_active_day_count: usize,
 
-    pub most_audio_active_day:   Date<Utc>,
+    pub most_audio_active_day:   NaiveDate,
     pub most_audio_active_count: usize,
 
     pub most_random_meme_user:          u64,
@@ -394,10 +441,7 @@ pub struct Stats {
 }
 
 pub async fn stats(conn: &mut AsyncPgConnection) -> Result<Stats> {
-    use chrono::{
-        NaiveDate,
-        NaiveDateTime,
-    };
+    use chrono::NaiveDateTime;
     use diesel::dsl::{
         count,
         count_star,
@@ -405,12 +449,7 @@ pub async fn stats(conn: &mut AsyncPgConnection) -> Result<Stats> {
 
     #[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 {})
+        Utc.from_utc_datetime(&ndt)
     }
 
     let total_count: i64 =
@@ -471,7 +510,7 @@ pub async fn stats(conn: &mut AsyncPgConnection) -> Result<Stats> {
         )
         .await?;
 
-    let most_active_day = to_utc_date(row.get(0));
+    let most_active_day = row.get(0);
     let most_active_day_count: i64 = row.get(1);
 
     let row = raw_conn
@@ -488,7 +527,7 @@ pub async fn stats(conn: &mut AsyncPgConnection) -> Result<Stats> {
         )
         .await?;
 
-    let most_active_audio_day = to_utc_date(row.get(0));
+    let most_active_audio_day = row.get(0);
     let most_active_audio_day_count: i64 = row.get(1);
 
     let row = raw_conn
diff --git a/src/log_setup.rs b/src/log_setup.rs
index c01f3f5..780ab78 100644
--- a/src/log_setup.rs
+++ b/src/log_setup.rs
@@ -1,6 +1,12 @@
-use crate::{Result, Error};
+use crate::{
+    Error,
+    Result,
+};
 
-use fern::colors::{Color, ColoredLevelConfig};
+use fern::colors::{
+    Color,
+    ColoredLevelConfig,
+};
 
 pub fn init(file_output: bool) -> Result<()> {
     let colors = ColoredLevelConfig::new()
@@ -10,24 +16,26 @@ pub fn init(file_output: bool) -> Result<()> {
 
     let mut logger = fern::Dispatch::new()
         .level_for("serenity::voice::connection", log::LevelFilter::Error)
-        .chain(fern::Dispatch::new()
-            .format(move |out, message, record| {
-                out.finish(format_args!(
-                    "{} [{}] [{}] {}",
-                    chrono::Local::now().format("%_m/%_d/%y %l:%M:%S%P"),
-                    colors.color(record.level()),
-                    record.target(),
-                    message
-                ))
-            })
-            .level(log::LevelFilter::Warn)
-            .level_for("thulani", log::LevelFilter::Debug)
-            .chain(std::io::stdout())
+        .chain(
+            fern::Dispatch::new()
+                .format(move |out, message, record| {
+                    out.finish(format_args!(
+                        "{} [{}] [{}] {}",
+                        chrono::Local::now().format("%_m/%_d/%y %l:%M:%S%P"),
+                        colors.color(record.level()),
+                        record.target(),
+                        message
+                    ))
+                })
+                .level(log::LevelFilter::Warn)
+                .level_for("thulani", log::LevelFilter::Debug)
+                .level_for("batch_delmeme", log::LevelFilter::Debug)
+                .chain(std::io::stdout()),
         );
 
     if file_output {
-        logger = logger
-            .chain(fern::Dispatch::new()
+        logger = logger.chain(
+            fern::Dispatch::new()
                 .format(|out, message, record| {
                     out.finish(format_args!(
                         "{} [{}] [{}] {}",
@@ -39,11 +47,10 @@ pub fn init(file_output: bool) -> Result<()> {
                 })
                 .level(log::LevelFilter::Info)
                 .level_for("thulani", log::LevelFilter::Trace)
-                .chain(fern::log_file("thulani.log").expect("problem creating log file"))
-            );
+                .level_for("batch_delmeme", log::LevelFilter::Trace)
+                .chain(fern::log_file("thulani.log").expect("problem creating log file")),
+        );
     }
 
-    logger
-        .apply()
-        .map_err(Error::from)
+    logger.apply().map_err(Error::from)
 }