1 files changed, 111 insertions, 0 deletions

diff --git a/cogs/utils/database.py b/cogs/utils/database.py
new file mode 100644
index 0000000..67cbc9f
--- /dev/null
+++ b/cogs/utils/database.py
@@ -0,0 +1,111 @@
+import os
+import sqlite3
+import logging
+
+logger = logging.getLogger(__name__)
+
+DB_PATH = "data/senator.db"
+
+_initialized = False
+
+
+def get_connection():
+    return sqlite3.connect(get_db_path())
+
+
+def get_db_path():
+    return os.getenv("SENATOR_DB_PATH", DB_PATH)
+
+
+def set_db_path(path):
+    global DB_PATH
+    DB_PATH = path
+
+
+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()