cron: scope jobs to the guild that created them

2 files changed, 47 insertions, 19 deletions

diff --git a/src/commands/cron.rs b/src/commands/cron.rs
index 47c3d62..ebaa96d 100644
--- a/src/commands/cron.rs
+++ b/src/commands/cron.rs
@@ -44,8 +44,10 @@ pub async fn dodaj_audio(
         ctx.data().copyparty.build_url_no_pw(&audio_filename)
     };
 
+    let guild_id = ctx.guild_id().ok_or_else(lang::err_not_in_guild)?;
     database::add_cron_job(
         &ctx.data().db_pool,
+        guild_id.get() as i64,
         &cron_expression,
         "audio",
         channel_id,
@@ -72,8 +74,10 @@ pub async fn dodaj_wiadomość(
         _ => return Err(lang::err_bad_channel_type()),
     };
 
+    let guild_id = ctx.guild_id().ok_or_else(lang::err_not_in_guild)?;
     database::add_cron_job(
         &ctx.data().db_pool,
+        guild_id.get() as i64,
         &cron_expression,
         "wiadomość",
         channel_id,
@@ -89,7 +93,8 @@ pub async fn dodaj_wiadomość(
 /// List all cron jobs
 #[poise::command(slash_command, guild_only, required_permissions = "MANAGE_GUILD")]
 pub async fn pokaż(ctx: Context<'_>) -> Result<()> {
-    let jobs = database::list_cron_jobs(&ctx.data().db_pool).await?;
+    let guild_id = ctx.guild_id().ok_or_else(lang::err_not_in_guild)?;
+    let jobs = database::list_cron_jobs(&ctx.data().db_pool, guild_id.get() as i64).await?;
     if jobs.is_empty() {
         ctx.say(lang::L_CRON_NONE).await?;
         return Ok(());
@@ -126,7 +131,8 @@ pub async fn usuń(
     ctx: Context<'_>,
     #[description = "ID pracy do usunięcia"] job_id: i64,
 ) -> Result<()> {
-    if database::delete_cron_job(&ctx.data().db_pool, job_id).await? {
+    let guild_id = ctx.guild_id().ok_or_else(lang::err_not_in_guild)?;
+    if database::delete_cron_job(&ctx.data().db_pool, job_id, guild_id.get() as i64).await? {
         ctx.say(lang::fmt_cron_deleted(job_id)).await?;
     } else {
         ctx.say(lang::fmt_cron_not_found(job_id)).await?;
@@ -140,7 +146,8 @@ pub async fn pauza(
     ctx: Context<'_>,
     #[description = "ID pracy do zapauzowania"] job_id: i64,
 ) -> Result<()> {
-    if database::set_cron_job_active(&ctx.data().db_pool, job_id, false).await? {
+    let guild_id = ctx.guild_id().ok_or_else(lang::err_not_in_guild)?;
+    if database::set_cron_job_active(&ctx.data().db_pool, job_id, guild_id.get() as i64, false).await? {
         ctx.say(lang::fmt_cron_paused(job_id)).await?;
     } else {
         ctx.say(lang::fmt_cron_not_found(job_id)).await?;
@@ -154,7 +161,8 @@ pub async fn wznów(
     ctx: Context<'_>,
     #[description = "ID pracy cron do wznowienia"] job_id: i64,
 ) -> Result<()> {
-    if database::set_cron_job_active(&ctx.data().db_pool, job_id, true).await? {
+    let guild_id = ctx.guild_id().ok_or_else(lang::err_not_in_guild)?;
+    if database::set_cron_job_active(&ctx.data().db_pool, job_id, guild_id.get() as i64, true).await? {
         ctx.say(lang::fmt_cron_resumed(job_id)).await?;
     } else {
         ctx.say(lang::fmt_cron_not_found(job_id)).await?;
diff --git a/src/database/mod.rs b/src/database/mod.rs
index b68d927..9679811 100644
--- a/src/database/mod.rs
+++ b/src/database/mod.rs
@@ -55,17 +55,22 @@ async fn run_migrations(pool: &DbPool) -> Result<()> {
             audio_filename TEXT,
             message TEXT,
             created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-            last_run TIMESTAMP
+            last_run TIMESTAMP,
+            guild_id INTEGER NOT NULL DEFAULT 0
         )",
     )
     .execute(pool)
     .await?;
 
-    // Add last_run column for pre-existing databases that lack it.
+    // Add columns for pre-existing databases that lack them.
     sqlx::query("ALTER TABLE cron_jobs ADD COLUMN last_run TIMESTAMP")
         .execute(pool)
         .await
         .ok();
+    sqlx::query("ALTER TABLE cron_jobs ADD COLUMN guild_id INTEGER NOT NULL DEFAULT 0")
+        .execute(pool)
+        .await
+        .ok();
 
     sqlx::query(
         "CREATE TABLE IF NOT EXISTS triggers (
@@ -305,6 +310,8 @@ pub async fn get_user_rank(pool: &DbPool, user_id: &str, metric: &str) -> Result
 #[derive(Debug, Clone, FromRow)]
 pub struct CronJob {
     pub id: i64,
+    #[allow(dead_code)]
+    pub guild_id: i64,
     pub cron_expression: String,
     pub r#type: String,
     pub channel_id: i64,
@@ -317,10 +324,13 @@ pub struct CronJob {
 }
 
 #[allow(dead_code)]
-pub async fn list_cron_jobs(pool: &DbPool) -> Result<Vec<CronJob>> {
-    Ok(sqlx::query_as("SELECT * FROM cron_jobs ORDER BY id")
-        .fetch_all(pool)
-        .await?)
+pub async fn list_cron_jobs(pool: &DbPool, guild_id: i64) -> Result<Vec<CronJob>> {
+    Ok(
+        sqlx::query_as("SELECT * FROM cron_jobs WHERE guild_id = ? ORDER BY id")
+            .bind(guild_id)
+            .fetch_all(pool)
+            .await?,
+    )
 }
 
 #[allow(dead_code)]
@@ -332,6 +342,7 @@ pub async fn list_active_cron_jobs(pool: &DbPool) -> Result<Vec<CronJob>> {
 
 pub async fn add_cron_job(
     pool: &DbPool,
+    guild_id: i64,
     cron_expression: &str,
     r#type: &str,
     channel_id: i64,
@@ -353,9 +364,10 @@ pub async fn add_cron_job(
     }).unwrap_or(existing.len() as i64 + 1);
 
     let result = sqlx::query(
-        "INSERT INTO cron_jobs (id, cron_expression, type, channel_id, audio_filename, message) VALUES (?, ?, ?, ?, ?, ?)",
+        "INSERT INTO cron_jobs (id, guild_id, cron_expression, type, channel_id, audio_filename, message) VALUES (?, ?, ?, ?, ?, ?, ?)",
     )
     .bind(next_id)
+    .bind(guild_id)
     .bind(cron_expression)
     .bind(r#type)
     .bind(channel_id)
@@ -366,9 +378,10 @@ pub async fn add_cron_job(
     Ok(result.last_insert_rowid())
 }
 
-pub async fn delete_cron_job(pool: &DbPool, id: i64) -> Result<bool> {
-    let result = sqlx::query("DELETE FROM cron_jobs WHERE id = ?")
+pub async fn delete_cron_job(pool: &DbPool, id: i64, guild_id: i64) -> Result<bool> {
+    let result = sqlx::query("DELETE FROM cron_jobs WHERE id = ? AND guild_id = ?")
         .bind(id)
+        .bind(guild_id)
         .execute(pool)
         .await?;
     Ok(result.rows_affected() > 0)
@@ -383,12 +396,19 @@ pub async fn update_cron_last_run(pool: &DbPool, id: i64, last_run: DateTime<Utc
     Ok(())
 }
 
-pub async fn set_cron_job_active(pool: &DbPool, id: i64, active: bool) -> Result<bool> {
-    let result = sqlx::query("UPDATE cron_jobs SET active = ? WHERE id = ?")
-        .bind(if active { 1 } else { 0 })
-        .bind(id)
-        .execute(pool)
-        .await?;
+pub async fn set_cron_job_active(
+    pool: &DbPool,
+    id: i64,
+    guild_id: i64,
+    active: bool,
+) -> Result<bool> {
+    let result =
+        sqlx::query("UPDATE cron_jobs SET active = ? WHERE id = ? AND guild_id = ?")
+            .bind(if active { 1 } else { 0 })
+            .bind(id)
+            .bind(guild_id)
+            .execute(pool)
+            .await?;
     Ok(result.rows_affected() > 0)
 }