Initial commit

crates/cgcx-db/Cargo.toml

@@ -0,0 +1,13 @@
+[package]
+name = "cgcx-db"
+version.workspace = true
+edition.workspace = true
+
+[dependencies]
+cgcx-core = { path = "../cgcx-core" }
+cgcx-config = { path = "../cgcx-config" }
+chrono = { version = "0.4", features = ["serde"] }
+rusqlite = { version = "0.32", features = ["bundled", "chrono"] }
+rusqlite_migration = "1.3"
+tokio = { version = "1", features = ["sync", "rt"] }
+tracing = "0.1"

crates/cgcx-db/src/lib.rs

@@ -0,0 +1,55 @@
+use cgcx_core::{Result, CgcxError};
+use rusqlite::Connection;
+use std::path::Path;
+use std::sync::Arc;
+use tokio::sync::Mutex;
+
+pub mod repos;
+
+pub use repos::*;
+
+#[derive(Clone)]
+pub struct Database {
+    conn: Arc<Mutex<Connection>>,
+}
+
+impl Database {
+    pub fn open<P: AsRef<Path>>(path: P) -> Result<Self> {
+        let conn = Connection::open(path).map_err(|e| CgcxError::Database(e.to_string()))?;
+        conn.execute_batch(
+            "PRAGMA journal_mode = WAL;
+             PRAGMA foreign_keys = ON;
+             PRAGMA busy_timeout = 5000;"
+        ).map_err(|e| CgcxError::Database(e.to_string()))?;
+        Ok(Self {
+            conn: Arc::new(Mutex::new(conn)),
+        })
+    }
+
+    pub fn open_in_memory() -> Result<Self> {
+        let conn = Connection::open_in_memory().map_err(|e| CgcxError::Database(e.to_string()))?;
+        conn.execute_batch(
+            "PRAGMA journal_mode = WAL;
+             PRAGMA foreign_keys = ON;
+             PRAGMA busy_timeout = 5000;"
+        ).map_err(|e| CgcxError::Database(e.to_string()))?;
+        Ok(Self {
+            conn: Arc::new(Mutex::new(conn)),
+        })
+    }
+
+    pub fn conn(&self) -> Arc<Mutex<Connection>> {
+        self.conn.clone()
+    }
+
+    pub async fn run_migrations(&self) -> Result<()> {
+        let mut conn = self.conn.lock().await;
+        let migrations = rusqlite_migration::Migrations::new(vec![
+            rusqlite_migration::M::up(include_str!("../../../migrations/001_init.sql")),
+            rusqlite_migration::M::up(include_str!("../../../migrations/002_indexes.sql")),
+        ]);
+        migrations.to_latest(&mut *conn)
+            .map_err(|e| CgcxError::Database(format!("migration failed: {}", e)))?;
+        Ok(())
+    }
+}

crates/cgcx-db/src/repos.rs

@@ -0,0 +1,389 @@
+use cgcx_core::{AdminAction, Content, ContentFile, ContentId, ContentStatus, Report, ReportStatus, Result, CgcxError, User};
+use rusqlite::{params, OptionalExtension};
+use std::sync::Arc;
+use tokio::sync::Mutex;
+
+pub struct UserRepo {
+    conn: Arc<Mutex<rusqlite::Connection>>,
+}
+
+impl UserRepo {
+    pub fn new(conn: Arc<Mutex<rusqlite::Connection>>) -> Self {
+        Self { conn }
+    }
+
+    pub async fn ensure_exists(&self, id: i64, username: Option<&str>, first_name: &str) -> Result<()> {
+        let conn = self.conn.lock().await;
+        conn.execute(
+            "INSERT INTO users (id, telegram_username, first_name) VALUES (?1, ?2, ?3)
+             ON CONFLICT(id) DO UPDATE SET telegram_username=excluded.telegram_username, first_name=excluded.first_name",
+            params![id, username, first_name],
+        ).map_err(|e| CgcxError::Database(e.to_string()))?;
+        Ok(())
+    }
+
+    pub async fn get(&self, id: i64) -> Result<Option<User>> {
+        let conn = self.conn.lock().await;
+        let row = conn.query_row(
+            "SELECT id, telegram_username, first_name, role, accepted_terms_at, created_at FROM users WHERE id = ?1",
+            params![id],
+            |row| {
+                let role: String = row.get(3)?;
+                Ok(User {
+                    id: row.get(0)?,
+                    telegram_username: row.get(1)?,
+                    first_name: row.get(2)?,
+                    role: match role.as_str() {
+                        "admin" => cgcx_core::UserRole::Admin,
+                        "banned" => cgcx_core::UserRole::Banned,
+                        _ => cgcx_core::UserRole::User,
+                    },
+                    accepted_terms_at: row.get(4)?,
+                    created_at: row.get(5)?,
+                })
+            },
+        ).optional().map_err(|e| CgcxError::Database(e.to_string()))?;
+        Ok(row)
+    }
+
+    pub async fn set_role(&self, id: i64, role: &str) -> Result<()> {
+        let conn = self.conn.lock().await;
+        conn.execute(
+            "UPDATE users SET role = ?1 WHERE id = ?2",
+            params![role, id],
+        ).map_err(|e| CgcxError::Database(e.to_string()))?;
+        Ok(())
+    }
+
+    pub async fn set_accepted_terms(&self, id: i64) -> Result<()> {
+        let conn = self.conn.lock().await;
+        conn.execute(
+            "UPDATE users SET accepted_terms_at = datetime('now') WHERE id = ?1",
+            params![id],
+        ).map_err(|e| CgcxError::Database(e.to_string()))?;
+        Ok(())
+    }
+}
+
+pub struct ContentRepo {
+    conn: Arc<Mutex<rusqlite::Connection>>,
+}
+
+impl ContentRepo {
+    pub fn new(conn: Arc<Mutex<rusqlite::Connection>>) -> Self {
+        Self { conn }
+    }
+
+    pub async fn insert(&self, content: &Content) -> Result<()> {
+        let conn = self.conn.lock().await;
+        let status = format!("{:?}", content.status).to_lowercase();
+        conn.execute(
+            "INSERT INTO contents (id, user_id, status, view_count, max_views, allow_download, password_hash, created_at, deleted_at)
+             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9)
+             ON CONFLICT(id) DO NOTHING",
+            params![
+                content.id.as_str(),
+                content.user_id,
+                status,
+                content.view_count as i64,
+                content.max_views.map(|v| v as i64),
+                content.allow_download as i64,
+                content.password_hash.as_ref(),
+                content.created_at,
+                content.deleted_at,
+            ],
+        ).map_err(|e| CgcxError::Database(e.to_string()))?;
+        Ok(())
+    }
+
+    pub async fn get(&self, id: &ContentId) -> Result<Option<Content>> {
+        let conn = self.conn.lock().await;
+        let row = conn.query_row(
+            "SELECT id, user_id, status, view_count, max_views, allow_download, password_hash, created_at, deleted_at
+             FROM contents WHERE id = ?1",
+            params![id.as_str()],
+            |row| {
+                let status: String = row.get(2)?;
+                Ok(Content {
+                    id: ContentId::new_unchecked(row.get(0)?),
+                    user_id: row.get(1)?,
+                    status: match status.as_str() {
+                        "staged" => ContentStatus::Staged,
+                        "deleted" => ContentStatus::Deleted,
+                        "blacklisted" => ContentStatus::Blacklisted,
+                        _ => ContentStatus::Active,
+                    },
+                    view_count: row.get::<_, i64>(3)? as u64,
+                    max_views: row.get::<_, Option<i64>>(4)?.map(|v| v as u64),
+                    allow_download: row.get::<_, i64>(5)? != 0,
+                    password_hash: row.get(6)?,
+                    created_at: row.get(7)?,
+                    deleted_at: row.get(8)?,
+                })
+            },
+        ).optional().map_err(|e| CgcxError::Database(e.to_string()))?;
+        Ok(row)
+    }
+
+    pub async fn list_by_user(&self, user_id: i64, limit: usize, offset: usize) -> Result<Vec<Content>> {
+        let conn = self.conn.lock().await;
+        let mut stmt = conn.prepare(
+            "SELECT id, user_id, status, view_count, max_views, allow_download, password_hash, created_at, deleted_at
+             FROM contents WHERE user_id = ?1 AND status != 'deleted' ORDER BY created_at DESC LIMIT ?2 OFFSET ?3"
+        ).map_err(|e| CgcxError::Database(e.to_string()))?;
+        let rows = stmt.query_map(params![user_id, limit as i64, offset as i64], |row| {
+            let status: String = row.get(2)?;
+            Ok(Content {
+                id: ContentId::new_unchecked(row.get(0)?),
+                user_id: row.get(1)?,
+                status: match status.as_str() {
+                    "staged" => ContentStatus::Staged,
+                    "deleted" => ContentStatus::Deleted,
+                    "blacklisted" => ContentStatus::Blacklisted,
+                    _ => ContentStatus::Active,
+                },
+                view_count: row.get::<_, i64>(3)? as u64,
+                max_views: row.get::<_, Option<i64>>(4)?.map(|v| v as u64),
+                allow_download: row.get::<_, i64>(5)? != 0,
+                password_hash: row.get(6)?,
+                created_at: row.get(7)?,
+                deleted_at: row.get(8)?,
+            })
+        }).map_err(|e| CgcxError::Database(e.to_string()))?;
+        let mut out = Vec::new();
+        for r in rows {
+            out.push(r.map_err(|e| CgcxError::Database(e.to_string()))?);
+        }
+        Ok(out)
+    }
+
+    pub async fn count_by_user(&self, user_id: i64) -> Result<usize> {
+        let conn = self.conn.lock().await;
+        let count: i64 = conn.query_row(
+            "SELECT COUNT(*) FROM contents WHERE user_id = ?1 AND status != 'deleted'",
+            params![user_id],
+            |row| row.get(0),
+        ).map_err(|e| CgcxError::Database(e.to_string()))?;
+        Ok(count as usize)
+    }
+
+    pub async fn increment_views(&self, id: &ContentId) -> Result<u64> {
+        let conn = self.conn.lock().await;
+        let new: i64 = conn.query_row(
+            "UPDATE contents SET view_count = view_count + 1 WHERE id = ?1 RETURNING view_count",
+            params![id.as_str()],
+            |row| row.get(0),
+        ).map_err(|e| CgcxError::Database(e.to_string()))?;
+        Ok(new as u64)
+    }
+
+    pub async fn set_status(&self, id: &ContentId, status: ContentStatus) -> Result<()> {
+        let conn = self.conn.lock().await;
+        let s = format!("{:?}", status).to_lowercase();
+        conn.execute(
+            "UPDATE contents SET status = ?1, deleted_at = CASE WHEN ?1 IN ('deleted','blacklisted') THEN datetime('now') ELSE NULL END WHERE id = ?2",
+            params![s, id.as_str()],
+        ).map_err(|e| CgcxError::Database(e.to_string()))?;
+        Ok(())
+    }
+
+    pub async fn delete_permanent(&self, id: &ContentId) -> Result<()> {
+        let mut conn = self.conn.lock().await;
+        let tx = conn.transaction().map_err(|e| CgcxError::Database(e.to_string()))?;
+        tx.execute("DELETE FROM content_files WHERE content_id = ?1", params![id.as_str()])
+            .map_err(|e| CgcxError::Database(e.to_string()))?;
+        tx.execute("DELETE FROM contents WHERE id = ?1", params![id.as_str()])
+            .map_err(|e| CgcxError::Database(e.to_string()))?;
+        tx.commit().map_err(|e| CgcxError::Database(e.to_string()))?;
+        Ok(())
+    }
+}
+
+pub struct ContentFileRepo {
+    conn: Arc<Mutex<rusqlite::Connection>>,
+}
+
+impl ContentFileRepo {
+    pub fn new(conn: Arc<Mutex<rusqlite::Connection>>) -> Self {
+        Self { conn }
+    }
+
+    pub async fn insert(&self, file: &ContentFile) -> Result<()> {
+        let conn = self.conn.lock().await;
+        conn.execute(
+            "INSERT INTO content_files (content_id, file_index, original_name, stored_path, mime_type, size_bytes, ciphertext_size_bytes, encrypted_key_wrapped, encrypted_hash, render_flags, created_at)
+             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11)
+             ON CONFLICT(content_id, file_index) DO NOTHING",
+            params![
+                file.content_id.as_str(),
+                file.file_index as i64,
+                &file.original_name,
+                file.stored_path.to_str(),
+                &file.mime_type,
+                file.size_bytes as i64,
+                file.ciphertext_size_bytes as i64,
+                &file.encrypted_key_wrapped,
+                &file.encrypted_hash,
+                file.render_flags as i64,
+                file.created_at,
+            ],
+        ).map_err(|e| CgcxError::Database(e.to_string()))?;
+        Ok(())
+    }
+
+    pub async fn list_by_content(&self, content_id: &ContentId) -> Result<Vec<ContentFile>> {
+        let conn = self.conn.lock().await;
+        let mut stmt = conn.prepare(
+            "SELECT content_id, file_index, original_name, stored_path, mime_type, size_bytes, ciphertext_size_bytes, encrypted_key_wrapped, encrypted_hash, render_flags, created_at
+             FROM content_files WHERE content_id = ?1 ORDER BY file_index"
+        ).map_err(|e| CgcxError::Database(e.to_string()))?;
+        let rows = stmt.query_map(params![content_id.as_str()], |row| {
+            Ok(ContentFile {
+                content_id: ContentId::new_unchecked(row.get(0)?),
+                file_index: row.get::<_, i64>(1)? as u32,
+                original_name: row.get(2)?,
+                stored_path: std::path::PathBuf::from(row.get::<_, String>(3)?),
+                mime_type: row.get(4)?,
+                size_bytes: row.get::<_, i64>(5)? as u64,
+                ciphertext_size_bytes: row.get::<_, i64>(6)? as u64,
+                encrypted_key_wrapped: row.get(7)?,
+                encrypted_hash: row.get(8)?,
+                render_flags: row.get::<_, i64>(9)? as u32,
+                created_at: row.get(10)?,
+            })
+        }).map_err(|e| CgcxError::Database(e.to_string()))?;
+        let mut out = Vec::new();
+        for r in rows {
+            out.push(r.map_err(|e| CgcxError::Database(e.to_string()))?);
+        }
+        Ok(out)
+    }
+
+    pub async fn find_orphan_files(&self) -> Result<Vec<String>> {
+        let conn = self.conn.lock().await;
+        let mut stmt = conn.prepare(
+            "SELECT cf.stored_path FROM content_files cf
+             JOIN contents c ON c.id = cf.content_id
+             WHERE c.status IN ('deleted', 'blacklisted')"
+        ).map_err(|e| CgcxError::Database(e.to_string()))?;
+        let rows = stmt.query_map([], |row| {
+            row.get::<_, String>(0)
+        }).map_err(|e| CgcxError::Database(e.to_string()))?;
+        let mut out = Vec::new();
+        for r in rows {
+            out.push(r.map_err(|e| CgcxError::Database(e.to_string()))?);
+        }
+        Ok(out)
+    }
+}
+
+pub struct ReportRepo {
+    conn: Arc<Mutex<rusqlite::Connection>>,
+}
+
+impl ReportRepo {
+    pub fn new(conn: Arc<Mutex<rusqlite::Connection>>) -> Self {
+        Self { conn }
+    }
+
+    pub async fn insert(&self, content_id: &ContentId, reporter_user_id: i64, reason: &str) -> Result<i64> {
+        let conn = self.conn.lock().await;
+        conn.execute(
+            "INSERT INTO reports (content_id, reporter_user_id, reason) VALUES (?1, ?2, ?3)",
+            params![content_id.as_str(), reporter_user_id, reason],
+        ).map_err(|e| CgcxError::Database(e.to_string()))?;
+        Ok(conn.last_insert_rowid())
+    }
+
+    pub async fn get(&self, id: i64) -> Result<Option<Report>> {
+        let conn = self.conn.lock().await;
+        let row = conn.query_row(
+            "SELECT id, content_id, reporter_user_id, reason, status, created_at, resolved_at, resolver_id
+             FROM reports WHERE id = ?1",
+            params![id],
+            |row| {
+                let status: String = row.get(4)?;
+                Ok(Report {
+                    id: row.get(0)?,
+                    content_id: ContentId::new_unchecked(row.get(1)?),
+                    reporter_user_id: row.get(2)?,
+                    reason: row.get(3)?,
+                    status: match status.as_str() {
+                        "dismissed" => ReportStatus::Dismissed,
+                        "actioned" => ReportStatus::Actioned,
+                        _ => ReportStatus::Open,
+                    },
+                    created_at: row.get(5)?,
+                    resolved_at: row.get(6)?,
+                    resolver_id: row.get(7)?,
+                })
+            },
+        ).optional().map_err(|e| CgcxError::Database(e.to_string()))?;
+        Ok(row)
+    }
+
+    pub async fn list(&self, limit: usize, offset: usize) -> Result<Vec<Report>> {
+        let conn = self.conn.lock().await;
+        let mut stmt = conn.prepare(
+            "SELECT id, content_id, reporter_user_id, reason, status, created_at, resolved_at, resolver_id
+             FROM reports ORDER BY created_at DESC LIMIT ?1 OFFSET ?2"
+        ).map_err(|e| CgcxError::Database(e.to_string()))?;
+        let rows = stmt.query_map(params![limit as i64, offset as i64], |row| {
+            let status: String = row.get(4)?;
+            Ok(Report {
+                id: row.get(0)?,
+                content_id: ContentId::new_unchecked(row.get(1)?),
+                reporter_user_id: row.get(2)?,
+                reason: row.get(3)?,
+                status: match status.as_str() {
+                    "dismissed" => ReportStatus::Dismissed,
+                    "actioned" => ReportStatus::Actioned,
+                    _ => ReportStatus::Open,
+                },
+                created_at: row.get(5)?,
+                resolved_at: row.get(6)?,
+                resolver_id: row.get(7)?,
+            })
+        }).map_err(|e| CgcxError::Database(e.to_string()))?;
+        let mut out = Vec::new();
+        for r in rows {
+            out.push(r.map_err(|e| CgcxError::Database(e.to_string()))?);
+        }
+        Ok(out)
+    }
+
+    pub async fn resolve(&self, id: i64, status: ReportStatus, resolver_id: i64) -> Result<()> {
+        let conn = self.conn.lock().await;
+        let s = format!("{:?}", status).to_lowercase();
+        conn.execute(
+            "UPDATE reports SET status = ?1, resolver_id = ?2, resolved_at = datetime('now') WHERE id = ?3",
+            params![s, resolver_id, id],
+        ).map_err(|e| CgcxError::Database(e.to_string()))?;
+        Ok(())
+    }
+}
+
+pub struct AdminActionRepo {
+    conn: Arc<Mutex<rusqlite::Connection>>,
+}
+
+impl AdminActionRepo {
+    pub fn new(conn: Arc<Mutex<rusqlite::Connection>>) -> Self {
+        Self { conn }
+    }
+
+    pub async fn insert(&self, action: &AdminAction) -> Result<i64> {
+        let conn = self.conn.lock().await;
+        conn.execute(
+            "INSERT INTO admin_actions (admin_user_id, target_type, target_id, action)
+             VALUES (?1, ?2, ?3, ?4)",
+            params![
+                action.admin_user_id,
+                &action.target_type,
+                &action.target_id,
+                &action.action,
+            ],
+        ).map_err(|e| CgcxError::Database(e.to_string()))?;
+        Ok(conn.last_insert_rowid())
+    }
+}