OPEN THE RUST GATES

Signed-off-by: salmonik <raisersoft@proton.me>

1 files changed, 344 insertions, 0 deletions

diff --git a/src/database/mod.rs b/src/database/mod.rs
new file mode 100644
index 0000000..53f4bf4
--- /dev/null
+++ b/src/database/mod.rs
@@ -0,0 +1,344 @@
+use chrono::{DateTime, Utc};
+use sqlx::FromRow;
+use sqlx::SqlitePool;
+
+use crate::error::{Error, Result};
+
+pub type DbPool = SqlitePool;
+
+pub async fn init_pool(path: &str) -> Result<DbPool> {
+    if let Some(parent) = std::path::Path::new(path).parent() {
+        tokio::fs::create_dir_all(parent).await.map_err(Error::Io)?;
+    }
+    let pool = SqlitePool::connect(&format!("sqlite:{path}?mode=rwc")).await?;
+    run_migrations(&pool).await?;
+    Ok(pool)
+}
+
+async fn run_migrations(pool: &DbPool) -> Result<()> {
+    sqlx::query(
+        "CREATE TABLE IF NOT EXISTS settings (
+            key TEXT PRIMARY KEY,
+            value TEXT NOT NULL
+        )",
+    )
+    .execute(pool)
+    .await?;
+
+    sqlx::query(
+        "CREATE TABLE IF NOT EXISTS voice_activity (
+            user_id TEXT PRIMARY KEY,
+            join_time REAL NOT NULL DEFAULT 0,
+            total_active REAL NOT NULL DEFAULT 0,
+            total_afk REAL NOT NULL DEFAULT 0,
+            is_afk INTEGER NOT NULL DEFAULT 0,
+            afk_start REAL NOT NULL DEFAULT 0,
+            active_start REAL NOT NULL DEFAULT 0,
+            channel_name TEXT NOT NULL DEFAULT '',
+            last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+        )",
+    )
+    .execute(pool)
+    .await?;
+
+    sqlx::query(
+        "CREATE TABLE IF NOT EXISTS cron_jobs (
+            id INTEGER PRIMARY KEY AUTOINCREMENT,
+            cron_expression TEXT NOT NULL,
+            type TEXT NOT NULL CHECK(type IN ('audio', 'wiadomość')),
+            channel_id INTEGER NOT NULL,
+            active INTEGER NOT NULL DEFAULT 1,
+            audio_filename TEXT,
+            message TEXT,
+            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+        )",
+    )
+    .execute(pool)
+    .await?;
+
+    sqlx::query(
+        "CREATE TABLE IF NOT EXISTS triggers (
+            id INTEGER PRIMARY KEY AUTOINCREMENT,
+            name TEXT UNIQUE NOT NULL,
+            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+            last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+        )",
+    )
+    .execute(pool)
+    .await?;
+
+    sqlx::query(
+        "CREATE TABLE IF NOT EXISTS trigger_keywords (
+            id INTEGER PRIMARY KEY AUTOINCREMENT,
+            trigger_id INTEGER NOT NULL REFERENCES triggers(id) ON DELETE CASCADE,
+            keyword TEXT NOT NULL,
+            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+            UNIQUE(trigger_id, keyword)
+        )",
+    )
+    .execute(pool)
+    .await?;
+
+    sqlx::query(
+        "CREATE TABLE IF NOT EXISTS trigger_responses (
+            id INTEGER PRIMARY KEY AUTOINCREMENT,
+            trigger_id INTEGER NOT NULL REFERENCES triggers(id) ON DELETE CASCADE,
+            response_text TEXT NOT NULL,
+            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+        )",
+    )
+    .execute(pool)
+    .await?;
+
+    Ok(())
+}
+
+// -- Voice Activity --
+#[derive(Debug, Clone, FromRow)]
+pub struct VoiceActivity {
+    pub user_id: String,
+    pub join_time: f64,
+    pub total_active: f64,
+    pub total_afk: f64,
+    pub is_afk: i32,
+    pub afk_start: f64,
+    pub active_start: f64,
+    pub channel_name: String,
+    #[allow(dead_code)]
+    pub last_updated: Option<DateTime<Utc>>,
+}
+
+#[allow(dead_code)]
+pub async fn get_voice_activity(pool: &DbPool, user_id: &str) -> Result<Option<VoiceActivity>> {
+    Ok(
+        sqlx::query_as("SELECT * FROM voice_activity WHERE user_id = ?")
+            .bind(user_id)
+            .fetch_optional(pool)
+            .await?,
+    )
+}
+
+pub async fn upsert_voice_activity(pool: &DbPool, va: &VoiceActivity) -> Result<()> {
+    sqlx::query(
+        "INSERT INTO voice_activity (user_id, join_time, total_active, total_afk, is_afk, afk_start, active_start, channel_name)
+         VALUES (?, ?, ?, ?, ?, ?, ?, ?)
+         ON CONFLICT(user_id) DO UPDATE SET
+            join_time = excluded.join_time,
+            total_active = excluded.total_active,
+            total_afk = excluded.total_afk,
+            is_afk = excluded.is_afk,
+            afk_start = excluded.afk_start,
+            active_start = excluded.active_start,
+            channel_name = excluded.channel_name,
+            last_updated = CURRENT_TIMESTAMP",
+    )
+    .bind(&va.user_id)
+    .bind(va.join_time)
+    .bind(va.total_active)
+    .bind(va.total_afk)
+    .bind(va.is_afk)
+    .bind(va.afk_start)
+    .bind(va.active_start)
+    .bind(&va.channel_name)
+    .execute(pool)
+    .await?;
+    Ok(())
+}
+
+// -- Cron Jobs --
+#[derive(Debug, Clone, FromRow)]
+pub struct CronJob {
+    pub id: i64,
+    pub cron_expression: String,
+    pub r#type: String,
+    pub channel_id: i64,
+    pub active: i32,
+    pub audio_filename: Option<String>,
+    pub message: Option<String>,
+    #[allow(dead_code)]
+    pub created_at: Option<DateTime<Utc>>,
+}
+
+#[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?)
+}
+
+#[allow(dead_code)]
+pub async fn list_active_cron_jobs(pool: &DbPool) -> Result<Vec<CronJob>> {
+    Ok(sqlx::query_as("SELECT * FROM cron_jobs WHERE active = 1")
+        .fetch_all(pool)
+        .await?)
+}
+
+pub async fn add_cron_job(
+    pool: &DbPool,
+    cron_expression: &str,
+    r#type: &str,
+    channel_id: i64,
+    audio_filename: Option<&str>,
+    message: Option<&str>,
+) -> Result<i64> {
+    let result = sqlx::query(
+        "INSERT INTO cron_jobs (cron_expression, type, channel_id, audio_filename, message) VALUES (?, ?, ?, ?, ?)",
+    )
+    .bind(cron_expression)
+    .bind(r#type)
+    .bind(channel_id)
+    .bind(audio_filename)
+    .bind(message)
+    .execute(pool)
+    .await?;
+    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 = ?")
+        .bind(id)
+        .execute(pool)
+        .await?;
+    Ok(result.rows_affected() > 0)
+}
+
+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?;
+    Ok(result.rows_affected() > 0)
+}
+
+// -- Triggers --
+#[derive(Debug, Clone, FromRow)]
+pub struct Trigger {
+    pub id: i64,
+    pub name: String,
+    pub created_at: Option<DateTime<Utc>>,
+    pub last_updated: Option<DateTime<Utc>>,
+}
+
+#[allow(dead_code)]
+#[derive(Debug, Clone, FromRow)]
+pub struct TriggerKeyword {
+    pub id: i64,
+    pub trigger_id: i64,
+    pub keyword: String,
+}
+
+#[allow(dead_code)]
+#[derive(Debug, Clone, FromRow)]
+pub struct TriggerResponse {
+    pub id: i64,
+    pub trigger_id: i64,
+    pub response_text: String,
+}
+
+pub async fn create_trigger(pool: &DbPool, name: &str) -> Result<i64> {
+    let result = sqlx::query("INSERT INTO triggers (name) VALUES (?)")
+        .bind(name)
+        .execute(pool)
+        .await?;
+    Ok(result.last_insert_rowid())
+}
+
+pub async fn delete_trigger(pool: &DbPool, id: i64) -> Result<bool> {
+    let result = sqlx::query("DELETE FROM triggers WHERE id = ?")
+        .bind(id)
+        .execute(pool)
+        .await?;
+    Ok(result.rows_affected() > 0)
+}
+
+pub async fn get_trigger_by_name(pool: &DbPool, name: &str) -> Result<Option<Trigger>> {
+    Ok(sqlx::query_as("SELECT * FROM triggers WHERE name = ?")
+        .bind(name)
+        .fetch_optional(pool)
+        .await?)
+}
+
+pub async fn list_triggers(pool: &DbPool) -> Result<Vec<Trigger>> {
+    Ok(sqlx::query_as("SELECT * FROM triggers ORDER BY name")
+        .fetch_all(pool)
+        .await?)
+}
+
+pub async fn add_keyword(pool: &DbPool, trigger_id: i64, keyword: &str) -> Result<()> {
+    sqlx::query("INSERT OR IGNORE INTO trigger_keywords (trigger_id, keyword) VALUES (?, ?)")
+        .bind(trigger_id)
+        .bind(keyword)
+        .execute(pool)
+        .await?;
+    update_trigger_timestamp(pool, trigger_id).await
+}
+
+pub async fn remove_keyword(pool: &DbPool, trigger_id: i64, keyword: &str) -> Result<bool> {
+    let result = sqlx::query("DELETE FROM trigger_keywords WHERE trigger_id = ? AND keyword = ?")
+        .bind(trigger_id)
+        .bind(keyword)
+        .execute(pool)
+        .await?;
+    update_trigger_timestamp(pool, trigger_id).await?;
+    Ok(result.rows_affected() > 0)
+}
+
+pub async fn add_response(pool: &DbPool, trigger_id: i64, response: &str) -> Result<()> {
+    sqlx::query("INSERT INTO trigger_responses (trigger_id, response_text) VALUES (?, ?)")
+        .bind(trigger_id)
+        .bind(response)
+        .execute(pool)
+        .await?;
+    update_trigger_timestamp(pool, trigger_id).await
+}
+
+pub async fn remove_response(pool: &DbPool, trigger_id: i64, response: &str) -> Result<bool> {
+    let result =
+        sqlx::query("DELETE FROM trigger_responses WHERE trigger_id = ? AND response_text = ?")
+            .bind(trigger_id)
+            .bind(response)
+            .execute(pool)
+            .await?;
+    update_trigger_timestamp(pool, trigger_id).await?;
+    Ok(result.rows_affected() > 0)
+}
+
+pub async fn get_keywords(pool: &DbPool, trigger_id: i64) -> Result<Vec<String>> {
+    let rows: Vec<(String,)> =
+        sqlx::query_as("SELECT keyword FROM trigger_keywords WHERE trigger_id = ?")
+            .bind(trigger_id)
+            .fetch_all(pool)
+            .await?;
+    Ok(rows.into_iter().map(|r| r.0).collect())
+}
+
+pub async fn get_responses(pool: &DbPool, trigger_id: i64) -> Result<Vec<String>> {
+    let rows: Vec<(String,)> =
+        sqlx::query_as("SELECT response_text FROM trigger_responses WHERE trigger_id = ?")
+            .bind(trigger_id)
+            .fetch_all(pool)
+            .await?;
+    Ok(rows.into_iter().map(|r| r.0).collect())
+}
+
+pub async fn load_all_triggers_with_keywords_and_responses(
+    pool: &DbPool,
+) -> Result<Vec<(Trigger, Vec<String>, Vec<String>)>> {
+    let triggers = list_triggers(pool).await?;
+    let mut result = Vec::new();
+    for t in triggers {
+        let keywords = get_keywords(pool, t.id).await?;
+        let responses = get_responses(pool, t.id).await?;
+        result.push((t, keywords, responses));
+    }
+    Ok(result)
+}
+
+async fn update_trigger_timestamp(pool: &DbPool, id: i64) -> Result<()> {
+    sqlx::query("UPDATE triggers SET last_updated = CURRENT_TIMESTAMP WHERE id = ?")
+        .bind(id)
+        .execute(pool)
+        .await?;
+    Ok(())
+}