4 files changed, 206 insertions, 26 deletions

diff --git a/cogs/utils/database.py b/cogs/utils/database.py
new file mode 100644
index 0000000..ea7d356
--- /dev/null
+++ b/cogs/utils/database.py
@@ -0,0 +1,126 @@
+import os
+import sqlite3
+import logging
+from contextlib import contextmanager
+
+logger = logging.getLogger(__name__)
+
+DB_PATH = "data/senator.db"
+
+_initialized = False
+
+
+def get_connection():
+    conn = sqlite3.connect(get_db_path())
+    return _ConnectionContextManager(conn)
+
+
+def get_db_path():
+    return os.getenv("SENATOR_DB_PATH", DB_PATH)
+
+
+def set_db_path(path):
+    global DB_PATH
+    DB_PATH = path
+
+
+class _ConnectionContextManager:
+    """Context manager for sqlite3 connections that auto-closes."""
+    def __init__(self, conn):
+        self.conn = conn
+
+    def __enter__(self):
+        return self.conn
+
+    def __exit__(self, exc_type, exc_val, exc_tb):
+        self.conn.close()
+        return False
+
+
+def init_database():
+    global _initialized
+    if _initialized:
+        return
+
+    _initialized = True
+
+    db_path = get_db_path()
+    db_dir = os.path.dirname(db_path)
+    if db_dir and not os.path.exists(db_dir):
+        os.makedirs(db_dir)
+
+    conn = sqlite3.connect(db_path)
+    try:
+        c = conn.cursor()
+
+        c.execute("PRAGMA foreign_keys = ON")
+
+        c.execute("""
+        CREATE TABLE IF NOT EXISTS settings (
+            key TEXT PRIMARY KEY,
+            value TEXT
+        )""")
+
+        c.execute("""
+        CREATE TABLE IF NOT EXISTS cron_jobs (
+            id INTEGER PRIMARY KEY AUTOINCREMENT,
+            cron_expression TEXT NOT NULL,
+            type TEXT NOT NULL,
+            channel_id INTEGER NOT NULL,
+            active INTEGER DEFAULT 1,
+            audio_filename TEXT,
+            message TEXT,
+            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+        )""")
+
+        c.execute("""
+        CREATE TABLE IF NOT EXISTS voice_activity (
+            user_id TEXT PRIMARY KEY,
+            join_time REAL,
+            total_active REAL DEFAULT 0,
+            total_afk REAL DEFAULT 0,
+            is_afk INTEGER DEFAULT 0,
+            afk_start REAL,
+            active_start REAL,
+            channel_name TEXT,
+            last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+        )""")
+
+        c.execute("CREATE INDEX IF NOT EXISTS idx_voice_activity_user_id ON voice_activity(user_id)")
+        c.execute("CREATE INDEX IF NOT EXISTS idx_cron_jobs_active ON cron_jobs(active)")
+
+        c.execute("""
+        CREATE TABLE IF NOT EXISTS triggers (
+            id INTEGER PRIMARY KEY AUTOINCREMENT,
+            name TEXT NOT NULL UNIQUE,
+            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+            last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+        )""")
+
+        c.execute("""
+        CREATE TABLE IF NOT EXISTS trigger_keywords (
+            id INTEGER PRIMARY KEY AUTOINCREMENT,
+            trigger_id INTEGER NOT NULL,
+            keyword TEXT NOT NULL,
+            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+            FOREIGN KEY (trigger_id) REFERENCES triggers(id) ON DELETE CASCADE,
+            UNIQUE(trigger_id, keyword)
+        )""")
+
+        c.execute("""
+        CREATE TABLE IF NOT EXISTS trigger_responses (
+            id INTEGER PRIMARY KEY AUTOINCREMENT,
+            trigger_id INTEGER NOT NULL,
+            response_text TEXT NOT NULL,
+            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+            FOREIGN KEY (trigger_id) REFERENCES triggers(id) ON DELETE CASCADE
+        )""")
+
+        c.execute("CREATE INDEX IF NOT EXISTS idx_trigger_keywords_trigger_id ON trigger_keywords(trigger_id)")
+        c.execute("CREATE INDEX IF NOT EXISTS idx_trigger_keywords_keyword ON trigger_keywords(keyword)")
+        c.execute("CREATE INDEX IF NOT EXISTS idx_trigger_responses_trigger_id ON trigger_responses(trigger_id)")
+
+        conn.commit()
+        logger.info("Database initialized successfully")
+    finally:
+        conn.close()
diff --git a/cogs/utils/file_search.py b/cogs/utils/file_search.py
index 8111216..788f50c 100644
--- a/cogs/utils/file_search.py
+++ b/cogs/utils/file_search.py
@@ -34,10 +34,22 @@ class UniversalSearcher:
         query = query.strip()
         logger.info(f"Starting copyparty search for query: '{query}'")
 
-        # Build the query with optional extension filtering
-        name_query = f'name like "*{query}*"'
+        tokens = query.split()
+        if not tokens:
+            return []
+
+        token_queries = []
+        for token in tokens:
+            path_query = f'path like "*{token}*"'
+            name_query = f'name like "*{token}*"'
+            token_queries.append(f"({path_query} or {name_query})")
+
+        if len(token_queries) == 1:
+            name_query = token_queries[0]
+        else:
+            name_query = " and ".join(token_queries)
+
         if file_extensions:
-            # Create extension filters using copyparty's .ext$ syntax
             ext_filters = [f"name like \"*.{ext.lower()}\"" for ext in file_extensions]
             ext_query = " or ".join(ext_filters)
             query = f"{name_query} and ({ext_query})"
diff --git a/cogs/utils/settings.py b/cogs/utils/settings.py
index d66ce7f..b1a585c 100644
--- a/cogs/utils/settings.py
+++ b/cogs/utils/settings.py
@@ -3,16 +3,18 @@
 # Copyright (C) 2026 Salmonik @ salmonik@tuta.io
 # https://codeberg.org/salmonik/senator-sperma
 
-import json
 import logging
-import aiofiles
+import sqlite3
 import os
 from dataclasses import dataclass, field
 
+from cogs.utils.database import get_connection, init_database
+
 logger = logging.getLogger(__name__)
 
-settings_file = "data/settings.json"
-DEFAULT_BASEURL = "http://localhost:8080"
+DEFAULT_BASEURL = "http://localhost:3939"
+
+init_database()
 
 
 @dataclass
@@ -28,24 +30,21 @@ class Settings:
         }
 
         try:
-            if os.path.exists(settings_file):
-                async with aiofiles.open(settings_file, "r") as f:
-                    content = await f.read()
-                    if content.strip():
-                        self._data = json.loads(content)
-                        self._sync_from_data()
-                        logger.info("Settings loaded into memory")
-                    else:
-                        self._data = default_settings.copy()
-                        self._sync_from_data()
-                        await self.save()
-                        logger.warning("Empty settings.json, reset to defaults")
+            with get_connection() as conn:
+                c = conn.cursor()
+                c.execute("SELECT key, value FROM settings")
+                rows = c.fetchall()
+
+            if rows:
+                self._data = {row[0]: row[1] for row in rows}
+                self._sync_from_data()
+                logger.info("Settings loaded from database")
             else:
                 self._data = default_settings.copy()
                 self._sync_from_data()
                 await self.save()
-                logger.info("Created default settings.json")
-        except json.JSONDecodeError as e:
+                logger.info("Created default settings in database")
+        except Exception as e:
             logger.error(f"Error loading settings: {e}")
             self._data = default_settings.copy()
             self._sync_from_data()
@@ -53,17 +52,27 @@ class Settings:
         return self
 
     def _sync_from_data(self):
-        self.volume = self._data.get("volume", 1.0)
+        try:
+            self.volume = float(self._data.get("volume", 1.0))
+        except (ValueError, TypeError):
+            self.volume = 1.0
         self.baseurl = self._data.get("baseurl", DEFAULT_BASEURL)
 
     def _sync_to_data(self):
-        self._data["volume"] = self.volume
+        self._data["volume"] = str(self.volume)
         self._data["baseurl"] = self.baseurl
 
     async def save(self):
         self._sync_to_data()
-        async with aiofiles.open(settings_file, "w") as f:
-            await f.write(json.dumps(self._data, indent=2))
+        with get_connection() as conn:
+            c = conn.cursor()
+            c.execute("PRAGMA foreign_keys = ON")
+            for key, value in self._data.items():
+                c.execute(
+                    "INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)",
+                    (key, value),
+                )
+            conn.commit()
 
 
 _settings_instance: Settings | None = None
@@ -90,4 +99,16 @@ def get_volume() -> float:
 
 
 def get_baseurl() -> str:
-    return _settings_instance.baseurl.rstrip("/") if _settings_instance else DEFAULT_BASEURL
+    url = os.getenv("COPYPARTY_URL")
+    if not url:
+        logger.warning("COPYPARTY_URL not set! Using default http://localhost:3939")
+        return DEFAULT_BASEURL
+    return url.rstrip("/")
+
+
+def get_uploadurl() -> str:
+    url = os.getenv("COPYPARTY_URL_UPLOAD")
+    if not url:
+        logger.warning("COPYPARTY_URL_UPLOAD not set! Using default http://localhost:3939")
+        return DEFAULT_BASEURL
+    return url.rstrip("/")
diff --git a/cogs/utils/url_helper.py b/cogs/utils/url_helper.py
new file mode 100644
index 0000000..eac06f1
--- /dev/null
+++ b/cogs/utils/url_helper.py
@@ -0,0 +1,21 @@
+# This file is part of Senator Sperma
+# Licensed under the GPL-3.0 License
+# Copyright (C) 2026 Salmonik @ salmonik@tuta.io
+# https://codeberg.org/salmonik/senator-sperma
+
+
+def clean_url_for_ytdlp(url: str) -> str:
+    """Clean URL by removing everything after the first '&'.
+
+    This prevents yt-dlp from interpreting additional query parameters
+    (like &list=...) as playlist URLs.
+
+    Example:
+        Input: https://www.youtube.com/watch?v=lPGipwoJiOM&list=RDlPGipwoJiOM&
+        Output: https://www.youtube.com/watch?v=lPGipwoJiOM
+    """
+    # Find the first '&' and truncate
+    first_ampersand = url.find('&')
+    if first_ampersand != -1:
+        return url[:first_ampersand]
+    return url