simple-web-app

database.rs (41097B)

---

 use std::collections::HashMap;
 use rusqlite::{params, Connection};
 
 use crate::error::AppError;
 
 // Target type constants for votes
 pub const TARGET_TYPE_STORY: i64 = 1;
 pub const TARGET_TYPE_COMMENT: i64 = 2;
 
 // ==========================================================================
 // Data types
 // ==========================================================================
 
 #[derive(Debug, Clone, serde::Serialize, serde::Deserialize)]
 pub struct Story {
     pub id: i64,
     pub url: Option<String>,
     pub title: String,
     pub text: String,
     pub domain: Option<String>,
     pub score: i64,
     pub comment_count: i64,
     pub published: i64,
     pub created_by: Option<String>,
     pub author: Option<String>,
     pub language: String,
 }
 
 #[derive(Debug, Clone, serde::Serialize, serde::Deserialize)]
 pub struct Comment {
     pub id: i64,
     pub story_id: i64,
     pub parent_id: Option<i64>,
     pub path: String,
     pub depth: i64,
     pub text: String,
     pub score: i64,
     pub created_at: i64,
     pub created_by: String,
 }
 
 #[derive(Debug, Clone, serde::Serialize, serde::Deserialize)]
 pub struct Category {
     pub id: i64,
     pub name: Option<String>,
 }
 
 #[derive(Debug, Clone, serde::Serialize, serde::Deserialize)]
 pub struct User {
     pub id: i64,
     pub email: String,
     pub password_hash: String,
     pub username: String,
     pub about: Option<String>,
     pub karma: i64,
     pub created_at: i64,
     pub email_verified: bool,
     pub verification_token: Option<String>,
 }
 
 /// Kept for template compatibility - maps to User fields
 #[derive(Debug, Clone, serde::Serialize, serde::Deserialize)]
 pub struct UserProfile {
     pub id: i64,
     pub user_id: String,
     pub username: String,
     pub about: Option<String>,
     pub karma: i64,
     pub created_at: String,
 }
 
 impl From<User> for UserProfile {
     fn from(u: User) -> Self {
         Self {
             id: u.id,
             user_id: u.id.to_string(),
             username: u.username,
             about: u.about,
             karma: u.karma,
             created_at: time_ago(u.created_at),
         }
     }
 }
 
 #[derive(Debug, Clone, serde::Serialize, serde::Deserialize)]
 pub struct SearchResult {
     pub id: i64,
     pub title: String,
     pub text: String,
     pub score: i64,
     pub comment_count: i64,
 }
 
 #[derive(Debug, Clone, serde::Serialize, serde::Deserialize)]
 pub struct StoryMeta {
     pub story_id: i64,
     pub author_username: Option<String>,
     pub tags: Vec<String>,
 }
 
 // ==========================================================================
 // View types (for templates)
 // ==========================================================================
 
 #[derive(Debug, Clone, serde::Serialize, serde::Deserialize)]
 pub struct StoryWithMeta {
     pub id: i64,
     pub url: Option<String>,
     pub title: String,
     pub text: String,
     pub domain: Option<String>,
     pub score: i64,
     pub comment_count: i64,
     pub published: i64,
     pub created_by: Option<String>,
     pub tags: Vec<Category>,
     pub author_username: Option<String>,
     pub time_ago: String,
     pub user_voted: bool,
     pub rank: usize,
 }
 
 impl StoryWithMeta {
     pub fn from_story(
         story: Story,
         tags: Vec<Category>,
         author_username: Option<String>,
         time_ago: String,
         user_voted: bool,
         rank: usize,
     ) -> Self {
         Self {
             id: story.id,
             url: story.url,
             title: story.title,
             text: story.text,
             domain: story.domain,
             score: story.score,
             comment_count: story.comment_count,
             published: story.published,
             created_by: story.created_by,
             tags,
             author_username,
             time_ago,
             user_voted,
             rank,
         }
     }
 }
 
 #[derive(Debug, Clone, serde::Serialize, serde::Deserialize)]
 pub struct CommentWithMeta {
     pub id: i64,
     pub story_id: i64,
     pub parent_id: Option<i64>,
     pub path: String,
     pub depth: i64,
     pub indent_px: i64,
     pub text: String,
     pub score: i64,
     pub created_at: i64,
     pub created_by: String,
     pub author_username: Option<String>,
     pub time_ago: String,
     pub user_voted: bool,
 }
 
 impl CommentWithMeta {
     pub fn from_comment(
         comment: Comment,
         author_username: Option<String>,
         time_ago: String,
         user_voted: bool,
     ) -> Self {
         Self {
             id: comment.id,
             story_id: comment.story_id,
             parent_id: comment.parent_id,
             path: comment.path.clone(),
             depth: comment.depth,
             indent_px: comment.depth * 20,
             text: comment.text,
             score: comment.score,
             created_at: comment.created_at,
             created_by: comment.created_by,
             author_username,
             time_ago,
             user_voted,
         }
     }
 }
 
 // ==========================================================================
 // Database
 // ==========================================================================
 
 const PRAGMAS: &str = "\
     PRAGMA journal_mode=WAL;\
     PRAGMA synchronous=NORMAL;\
     PRAGMA foreign_keys=ON;\
     PRAGMA busy_timeout=5000;\
     PRAGMA cache_size=-64000;\
     PRAGMA mmap_size=268435456;\
     PRAGMA temp_store=MEMORY;\
 ";
 
 type ReadJob = Box<dyn FnOnce(&Connection) + Send>;
 type WriteJob = Box<dyn FnOnce(&Connection) + Send>;
 
 fn open_conn(path: &str) -> Result<Connection, AppError> {
     let c = Connection::open(path)
         .map_err(|e| AppError::Database(format!("Failed to open db: {e}")))?;
     c.execute_batch(PRAGMAS)
         .map_err(|e| AppError::Database(format!("Failed to set PRAGMAs: {e}")))?;
     Ok(c)
 }
 
 pub struct Database {
     read_tx: crossbeam_channel::Sender<ReadJob>,
     write_tx: std::sync::mpsc::Sender<WriteJob>,
 }
 
 impl Database {
     pub fn new(path: &str) -> Result<Self, AppError> {
         if let Some(parent) = std::path::Path::new(path).parent() {
             std::fs::create_dir_all(parent)
                 .map_err(|e| AppError::Database(format!("Failed to create db directory: {e}")))?;
         }
 
         // Run migrations
         let migration_conn = open_conn(path)?;
         crate::migrations::run(&migration_conn).map_err(AppError::Database)?;
         drop(migration_conn);
 
         // Dedicated read pool: N threads, each owns a connection
         let cores = std::thread::available_parallelism()
             .map(|n| n.get())
             .unwrap_or(4);
         let (read_tx, read_rx) = crossbeam_channel::unbounded::<ReadJob>();
         for _ in 0..cores {
             let rx = read_rx.clone();
             let p = path.to_string();
             std::thread::spawn(move || {
                 let conn = open_conn(&p).expect("failed to open read connection");
                 while let Ok(job) = rx.recv() {
                     job(&conn);
                 }
             });
         }
 
         // Dedicated writer: 1 thread, owns the write connection
         let (write_tx, write_rx) = std::sync::mpsc::channel::<WriteJob>();
         let p = path.to_string();
         std::thread::spawn(move || {
             let conn = open_conn(&p).expect("failed to open write connection");
             while let Ok(job) = write_rx.recv() {
                 job(&conn);
             }
         });
 
         Ok(Self { read_tx, write_tx })
     }
 
     /// Dispatch a read query to the read pool, await the result.
     pub async fn with_read<T: Send + 'static>(
         &self,
         f: impl FnOnce(&Connection) -> Result<T, AppError> + Send + 'static,
     ) -> Result<T, AppError> {
         let (tx, rx) = tokio::sync::oneshot::channel();
         self.read_tx.send(Box::new(move |conn| {
             let _ = tx.send(f(conn));
         })).map_err(|_| AppError::Database("read pool gone".into()))?;
         rx.await.map_err(|_| AppError::Database("read cancelled".into()))?
     }
 
     /// Dispatch a write to the single writer thread, await the result.
     pub async fn with_write<T: Send + 'static>(
         &self,
         f: impl FnOnce(&Connection) -> Result<T, AppError> + Send + 'static,
     ) -> Result<T, AppError> {
         let (tx, rx) = tokio::sync::oneshot::channel();
         self.write_tx.send(Box::new(move |conn| {
             let _ = tx.send(f(conn));
         })).map_err(|_| AppError::Database("writer gone".into()))?;
         rx.await.map_err(|_| AppError::Database("write cancelled".into()))?
     }
 
     // ======================================================================
     // Story queries
     // ======================================================================
 
     fn row_to_story(row: &rusqlite::Row) -> rusqlite::Result<Story> {
         let created_by: Option<i64> = row.get("created_by")?;
         Ok(Story {
             id: row.get("id")?,
             url: row.get("url")?,
             title: row.get("title")?,
             text: row.get("text")?,
             domain: row.get("domain")?,
             score: row.get("score")?,
             comment_count: row.get("comment_count")?,
             published: row.get("published")?,
             created_by: created_by.map(|id| id.to_string()),
             author: row.get("author")?,
             language: row.get("language")?,
         })
     }
 
     pub async fn get_stories_new(&self, limit: usize, offset: usize) -> Result<Vec<Story>, AppError> {
         self.with_read(move |conn| {
             let mut stmt = conn.prepare_cached(
                 "SELECT * FROM story ORDER BY published DESC LIMIT ?1 OFFSET ?2"
             ).map_err(|e| AppError::Database(e.to_string()))?;
 
             let stories = stmt.query_map(params![limit as i64, offset as i64], Self::row_to_story)
                 .map_err(|e| AppError::Database(e.to_string()))?
                 .collect::<Result<Vec<_>, _>>()
                 .map_err(|e| AppError::Database(e.to_string()))?;
 
             Ok(stories)
         }).await
     }
 
     pub async fn get_stories_top(&self, limit: usize, offset: usize) -> Result<Vec<Story>, AppError> {
         self.with_read(move |conn| {
             let mut stmt = conn.prepare_cached(
                 "SELECT * FROM story ORDER BY score DESC, published DESC LIMIT ?1 OFFSET ?2"
             ).map_err(|e| AppError::Database(e.to_string()))?;
 
             let stories = stmt.query_map(params![limit as i64, offset as i64], Self::row_to_story)
                 .map_err(|e| AppError::Database(e.to_string()))?
                 .collect::<Result<Vec<_>, _>>()
                 .map_err(|e| AppError::Database(e.to_string()))?;
 
             Ok(stories)
         }).await
     }
 
     pub async fn get_stories_hot(&self, limit: usize, offset: usize) -> Result<Vec<Story>, AppError> {
         let fetch_limit = (limit + offset) * 2;
         self.with_read(move |conn| {
             let mut stmt = conn.prepare_cached(
                 "SELECT * FROM story ORDER BY published DESC LIMIT ?1"
             ).map_err(|e| AppError::Database(e.to_string()))?;
 
             let mut stories: Vec<Story> = stmt.query_map(params![fetch_limit as i64], Self::row_to_story)
                 .map_err(|e| AppError::Database(e.to_string()))?
                 .collect::<Result<Vec<_>, _>>()
                 .map_err(|e| AppError::Database(e.to_string()))?;
 
             stories.sort_by(|a, b| {
                 let score_a = hot_score(a.score, a.published);
                 let score_b = hot_score(b.score, b.published);
                 score_b.partial_cmp(&score_a).unwrap_or(std::cmp::Ordering::Equal)
             });
 
             let result: Vec<Story> = stories.into_iter().skip(offset).take(limit).collect();
             Ok(result)
         }).await
     }
 
     pub async fn get_story(&self, id: i64) -> Result<Option<Story>, AppError> {
         self.with_read(move |conn| {
             let mut stmt = conn.prepare_cached("SELECT * FROM story WHERE id = ?1")
                 .map_err(|e| AppError::Database(e.to_string()))?;
 
             let mut rows = stmt.query_map(params![id], Self::row_to_story)
                 .map_err(|e| AppError::Database(e.to_string()))?;
 
             match rows.next() {
                 Some(Ok(story)) => Ok(Some(story)),
                 Some(Err(e)) => Err(AppError::Database(e.to_string())),
                 None => Ok(None),
             }
         }).await
     }
 
     pub async fn create_story(
         &self,
         user_id: i64,
         title: &str,
         url: Option<&str>,
         text: &str,
         domain: Option<&str>,
         language: &str,
     ) -> Result<Story, AppError> {
         let title = title.to_string();
         let url = url.map(|s| s.to_string());
         let text = text.to_string();
         let domain = domain.map(|s| s.to_string());
         let language = language.to_string();
         self.with_write(move |conn| {
             let published = now_epoch();
 
             conn.query_row(
                 "INSERT INTO story (title, url, text, domain, published, language, created_by)
                  VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)
                  RETURNING *",
                 params![title, url, text, domain, published, language, user_id],
                 Self::row_to_story,
             ).map_err(|e| AppError::Database(e.to_string()))
         }).await
     }
 
     pub async fn update_story(
         &self,
         id: i64,
         title: &str,
         url: Option<&str>,
         text: &str,
         domain: Option<&str>,
     ) -> Result<(), AppError> {
         let title = title.to_string();
         let url = url.map(|s| s.to_string());
         let text = text.to_string();
         let domain = domain.map(|s| s.to_string());
         self.with_write(move |conn| {
             conn.execute(
                 "UPDATE story SET title = ?1, url = ?2, text = ?3, domain = ?4 WHERE id = ?5",
                 params![title, url, text, domain, id],
             ).map_err(|e| AppError::Database(e.to_string()))?;
             Ok(())
         }).await
     }
 
     pub async fn get_stories_by_tag(&self, category_id: i64, limit: usize, offset: usize) -> Result<Vec<Story>, AppError> {
         self.with_read(move |conn| {
             let mut stmt = conn.prepare_cached(
                 "SELECT s.* FROM story s
                  JOIN story_category sc ON sc.news_item_id = s.id
                  WHERE sc.category_id = ?1
                  ORDER BY s.published DESC
                  LIMIT ?2 OFFSET ?3"
             ).map_err(|e| AppError::Database(e.to_string()))?;
 
             let stories = stmt.query_map(params![category_id, limit as i64, offset as i64], Self::row_to_story)
                 .map_err(|e| AppError::Database(e.to_string()))?
                 .collect::<Result<Vec<_>, _>>()
                 .map_err(|e| AppError::Database(e.to_string()))?;
 
             Ok(stories)
         }).await
     }
 
     pub async fn get_stories_by_user(&self, user_id: i64, limit: usize, offset: usize) -> Result<Vec<Story>, AppError> {
         self.with_read(move |conn| {
             let mut stmt = conn.prepare_cached(
                 "SELECT * FROM story WHERE created_by = ?1 ORDER BY published DESC LIMIT ?2 OFFSET ?3"
             ).map_err(|e| AppError::Database(e.to_string()))?;
 
             let stories = stmt.query_map(params![user_id, limit as i64, offset as i64], Self::row_to_story)
                 .map_err(|e| AppError::Database(e.to_string()))?
                 .collect::<Result<Vec<_>, _>>()
                 .map_err(|e| AppError::Database(e.to_string()))?;
 
             Ok(stories)
         }).await
     }
 
     /// Search stories with selectivity check. Terms appearing in more than
     /// `max_doc_ratio` of all documents are rejected — they produce low-quality
     /// results and are expensive to rank.
     pub async fn search_stories(
         &self,
         query: &str,
         limit: usize,
         max_doc_ratio: f64,
     ) -> Result<Vec<SearchResult>, AppError> {
         let query = query.to_string();
         self.with_read(move |conn| {
             // Check selectivity: reject terms that match too many documents
             let total_docs: i64 = conn
                 .query_row("SELECT COUNT(*) FROM story", [], |row| row.get(0))
                 .map_err(|e| AppError::Database(e.to_string()))?;
 
             if total_docs > 0 {
                 // Check how many documents match. This uses the FTS index
                 // (fast) without ranking (the expensive part).
                 let match_count: i64 = conn
                     .query_row(
                         "SELECT COUNT(*) FROM story_fts WHERE story_fts MATCH ?1",
                         params![query],
                         |row| row.get(0),
                     )
                     .unwrap_or(0);
 
                 if match_count as f64 / total_docs as f64 > max_doc_ratio {
                     return Ok(vec![]);
                 }
             }
 
             let mut stmt = conn.prepare_cached(
                 "SELECT s.id, s.title, s.text, s.score, s.comment_count
                  FROM story_fts fts
                  JOIN story s ON s.id = fts.rowid
                  WHERE story_fts MATCH ?1
                  ORDER BY rank
                  LIMIT ?2"
             ).map_err(|e| AppError::Database(e.to_string()))?;
 
             let results = stmt.query_map(params![query, limit as i64], |row| {
                 Ok(SearchResult {
                     id: row.get(0)?,
                     title: row.get(1)?,
                     text: row.get(2)?,
                     score: row.get(3)?,
                     comment_count: row.get(4)?,
                 })
             })
             .map_err(|e| AppError::Database(e.to_string()))?
             .collect::<Result<Vec<_>, _>>()
             .map_err(|e| AppError::Database(e.to_string()))?;
 
             Ok(results)
         }).await
     }
 
     // ======================================================================
     // Comment queries
     // ======================================================================
 
     fn row_to_comment(row: &rusqlite::Row) -> rusqlite::Result<Comment> {
         let created_by: i64 = row.get("created_by")?;
         Ok(Comment {
             id: row.get("id")?,
             story_id: row.get("story_id")?,
             parent_id: row.get("parent_id")?,
             path: row.get("path")?,
             depth: row.get("depth")?,
             text: row.get("text")?,
             score: row.get("score")?,
             created_at: row.get("created_at")?,
             created_by: created_by.to_string(),
         })
     }
 
     pub async fn get_comments_for_story(&self, story_id: i64) -> Result<Vec<Comment>, AppError> {
         self.with_read(move |conn| {
             let mut stmt = conn.prepare_cached(
                 "SELECT * FROM comment WHERE story_id = ?1 ORDER BY path"
             ).map_err(|e| AppError::Database(e.to_string()))?;
 
             let comments = stmt.query_map(params![story_id], Self::row_to_comment)
                 .map_err(|e| AppError::Database(e.to_string()))?
                 .collect::<Result<Vec<_>, _>>()
                 .map_err(|e| AppError::Database(e.to_string()))?;
 
             Ok(comments)
         }).await
     }
 
     pub async fn create_comment(
         &self,
         user_id: i64,
         story_id: i64,
         parent_id: Option<i64>,
         text: &str,
     ) -> Result<Comment, AppError> {
         let text = text.to_string();
         self.with_write(move |conn| {
             // Calculate path and depth
             let (path, depth) = match parent_id {
                 Some(pid) => {
                     let parent: Option<(String, i64)> = conn.query_row(
                         "SELECT path, depth FROM comment WHERE id = ?1",
                         params![pid],
                         |row| Ok((row.get(0)?, row.get(1)?)),
                     ).ok();
 
                     match parent {
                         Some((parent_path, parent_depth)) => {
                             let sibling_count: i64 = conn.query_row(
                                 "SELECT COUNT(*) FROM comment WHERE parent_id = ?1",
                                 params![pid],
                                 |row| row.get(0),
                             ).map_err(|e| AppError::Database(e.to_string()))?;
 
                             let path = format!("{}.{}", parent_path, sibling_count + 1);
                             (path, parent_depth + 1)
                         }
                         None => {
                             // Parent not found, treat as root
                             let root_count: i64 = conn.query_row(
                                 "SELECT COUNT(*) FROM comment WHERE story_id = ?1 AND depth = 0",
                                 params![story_id],
                                 |row| row.get(0),
                             ).map_err(|e| AppError::Database(e.to_string()))?;
                             ((root_count + 1).to_string(), 0)
                         }
                     }
                 }
                 None => {
                     let root_count: i64 = conn.query_row(
                         "SELECT COUNT(*) FROM comment WHERE story_id = ?1 AND depth = 0",
                         params![story_id],
                         |row| row.get(0),
                     ).map_err(|e| AppError::Database(e.to_string()))?;
                     ((root_count + 1).to_string(), 0)
                 }
             };
 
             conn.query_row(
                 "INSERT INTO comment (story_id, parent_id, path, depth, text, created_by, created_at)
                  VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)
                  RETURNING *",
                 params![story_id, parent_id, path, depth, text, user_id, now_epoch()],
                 Self::row_to_comment,
             ).map_err(|e| AppError::Database(e.to_string()))
         }).await
     }
 
     pub async fn get_comment(&self, id: i64) -> Result<Option<Comment>, AppError> {
         self.with_read(move |conn| {
             let mut stmt = conn.prepare_cached("SELECT * FROM comment WHERE id = ?1")
                 .map_err(|e| AppError::Database(e.to_string()))?;
 
             let mut rows = stmt.query_map(params![id], Self::row_to_comment)
                 .map_err(|e| AppError::Database(e.to_string()))?;
 
             match rows.next() {
                 Some(Ok(comment)) => Ok(Some(comment)),
                 Some(Err(e)) => Err(AppError::Database(e.to_string())),
                 None => Ok(None),
             }
         }).await
     }
 
     pub async fn get_comments_by_user(&self, user_id: i64, limit: usize, offset: usize) -> Result<Vec<Comment>, AppError> {
         self.with_read(move |conn| {
             let mut stmt = conn.prepare_cached(
                 "SELECT * FROM comment WHERE created_by = ?1 ORDER BY created_at DESC LIMIT ?2 OFFSET ?3"
             ).map_err(|e| AppError::Database(e.to_string()))?;
 
             let comments = stmt.query_map(params![user_id, limit as i64, offset as i64], Self::row_to_comment)
                 .map_err(|e| AppError::Database(e.to_string()))?
                 .collect::<Result<Vec<_>, _>>()
                 .map_err(|e| AppError::Database(e.to_string()))?;
 
             Ok(comments)
         }).await
     }
 
     // ======================================================================
     // Vote queries
     // ======================================================================
 
     /// Toggle vote and return (voted, new_score). Reads the score on the
     /// writer connection so it sees the trigger update without a separate round-trip.
     pub async fn toggle_vote(&self, user_id: i64, target_type: i64, target_id: i64) -> Result<(bool, i64), AppError> {
         self.with_write(move |conn| {
             let result = conn.execute(
                 "INSERT INTO vote (user_id, target_type, target_id, created_at) VALUES (?1, ?2, ?3, ?4)",
                 params![user_id, target_type, target_id, now_epoch()],
             );
 
             let voted = match result {
                 Ok(_) => true,
                 Err(rusqlite::Error::SqliteFailure(err, _))
                     if err.code == rusqlite::ffi::ErrorCode::ConstraintViolation =>
                 {
                     conn.execute(
                         "DELETE FROM vote WHERE user_id = ?1 AND target_type = ?2 AND target_id = ?3",
                         params![user_id, target_type, target_id],
                     ).map_err(|e| AppError::Database(e.to_string()))?;
                     false
                 }
                 Err(e) => return Err(AppError::Database(e.to_string())),
             };
 
             // Read score on the writer — sees the trigger update immediately
             let table = if target_type == TARGET_TYPE_STORY { "story" } else { "comment" };
             let score: i64 = conn.query_row(
                 &format!("SELECT score FROM {} WHERE id = ?1", table),
                 params![target_id],
                 |row| row.get(0),
             ).map_err(|e| AppError::Database(e.to_string()))?;
 
             Ok((voted, score))
         }).await
     }
 
     pub async fn get_user_votes(&self, user_id: i64, target_type: i64, target_ids: &[i64]) -> Result<Vec<i64>, AppError> {
         if target_ids.is_empty() {
             return Ok(vec![]);
         }
 
         let target_ids = target_ids.to_vec();
         self.with_read(move |conn| {
             // Build placeholders for IN clause
             let placeholders: Vec<String> = target_ids.iter().enumerate().map(|(i, _)| format!("?{}", i + 3)).collect();
             let sql = format!(
                 "SELECT target_id FROM vote WHERE user_id = ?1 AND target_type = ?2 AND target_id IN ({})",
                 placeholders.join(", ")
             );
 
             let mut stmt = conn.prepare_cached(&sql).map_err(|e| AppError::Database(e.to_string()))?;
 
             // Build params
             let mut param_values: Vec<Box<dyn rusqlite::types::ToSql>> = Vec::new();
             param_values.push(Box::new(user_id));
             param_values.push(Box::new(target_type));
             for &id in &target_ids {
                 param_values.push(Box::new(id));
             }
             let params_ref: Vec<&dyn rusqlite::types::ToSql> = param_values.iter().map(|p| p.as_ref()).collect();
 
             let voted_ids = stmt.query_map(params_ref.as_slice(), |row| row.get::<_, i64>(0))
                 .map_err(|e| AppError::Database(e.to_string()))?
                 .collect::<Result<Vec<_>, _>>()
                 .map_err(|e| AppError::Database(e.to_string()))?;
 
             Ok(voted_ids)
         }).await
     }
 
     // ======================================================================
     // Category queries
     // ======================================================================
 
     pub async fn get_categories(&self, limit: usize) -> Result<Vec<Category>, AppError> {
         self.with_read(move |conn| {
             let mut stmt = conn.prepare_cached("SELECT id, name FROM category LIMIT ?1")
                 .map_err(|e| AppError::Database(e.to_string()))?;
 
             let categories = stmt.query_map(params![limit as i64], |row| {
                 Ok(Category {
                     id: row.get(0)?,
                     name: row.get(1)?,
                 })
             })
             .map_err(|e| AppError::Database(e.to_string()))?
             .collect::<Result<Vec<_>, _>>()
             .map_err(|e| AppError::Database(e.to_string()))?;
 
             Ok(categories)
         }).await
     }
 
     pub async fn get_categories_for_story(&self, story_id: i64) -> Result<Vec<Category>, AppError> {
         self.with_read(move |conn| {
             let mut stmt = conn.prepare_cached(
                 "SELECT c.id, c.name FROM category c
                  JOIN story_category sc ON sc.category_id = c.id
                  WHERE sc.news_item_id = ?1"
             ).map_err(|e| AppError::Database(e.to_string()))?;
 
             let categories = stmt.query_map(params![story_id], |row| {
                 Ok(Category {
                     id: row.get(0)?,
                     name: row.get(1)?,
                 })
             })
             .map_err(|e| AppError::Database(e.to_string()))?
             .collect::<Result<Vec<_>, _>>()
             .map_err(|e| AppError::Database(e.to_string()))?;
 
             Ok(categories)
         }).await
     }
 
     pub async fn add_tag_to_story(&self, story_id: i64, category_id: i64) -> Result<(), AppError> {
         self.with_write(move |conn| {
             conn.execute(
                 "INSERT INTO story_category (news_item_id, category_id) VALUES (?1, ?2)",
                 params![story_id, category_id],
             ).map_err(|e| AppError::Database(e.to_string()))?;
             Ok(())
         }).await
     }
 
     pub async fn remove_all_tags_from_story(&self, story_id: i64) -> Result<(), AppError> {
         self.with_write(move |conn| {
             conn.execute(
                 "DELETE FROM story_category WHERE news_item_id = ?1",
                 params![story_id],
             ).map_err(|e| AppError::Database(e.to_string()))?;
             Ok(())
         }).await
     }
 
     // ======================================================================
     // User queries
     // ======================================================================
 
     fn row_to_user(row: &rusqlite::Row) -> rusqlite::Result<User> {
         let email_verified_int: i64 = row.get("email_verified")?;
         Ok(User {
             id: row.get("id")?,
             email: row.get("email")?,
             password_hash: row.get("password_hash")?,
             username: row.get("username")?,
             about: row.get("about")?,
             karma: row.get("karma")?,
             created_at: row.get("created_at")?,
             email_verified: email_verified_int != 0,
             verification_token: row.get("verification_token")?,
         })
     }
 
     pub async fn get_user_by_id(&self, id: i64) -> Result<Option<User>, AppError> {
         self.with_read(move |conn| {
             let mut stmt = conn.prepare_cached("SELECT * FROM user WHERE id = ?1")
                 .map_err(|e| AppError::Database(e.to_string()))?;
 
             let mut rows = stmt.query_map(params![id], Self::row_to_user)
                 .map_err(|e| AppError::Database(e.to_string()))?;
 
             match rows.next() {
                 Some(Ok(user)) => Ok(Some(user)),
                 Some(Err(e)) => Err(AppError::Database(e.to_string())),
                 None => Ok(None),
             }
         }).await
     }
 
     pub async fn get_user_by_email(&self, email: &str) -> Result<Option<User>, AppError> {
         let email = email.to_string();
         self.with_read(move |conn| {
             let mut stmt = conn.prepare_cached("SELECT * FROM user WHERE email = ?1")
                 .map_err(|e| AppError::Database(e.to_string()))?;
 
             let mut rows = stmt.query_map(params![email], Self::row_to_user)
                 .map_err(|e| AppError::Database(e.to_string()))?;
 
             match rows.next() {
                 Some(Ok(user)) => Ok(Some(user)),
                 Some(Err(e)) => Err(AppError::Database(e.to_string())),
                 None => Ok(None),
             }
         }).await
     }
 
     pub async fn get_user_by_username(&self, username: &str) -> Result<Option<User>, AppError> {
         let username = username.to_string();
         self.with_read(move |conn| {
             let mut stmt = conn.prepare_cached("SELECT * FROM user WHERE username = ?1")
                 .map_err(|e| AppError::Database(e.to_string()))?;
 
             let mut rows = stmt.query_map(params![username], Self::row_to_user)
                 .map_err(|e| AppError::Database(e.to_string()))?;
 
             match rows.next() {
                 Some(Ok(user)) => Ok(Some(user)),
                 Some(Err(e)) => Err(AppError::Database(e.to_string())),
                 None => Ok(None),
             }
         }).await
     }
 
     pub async fn create_user(&self, email: &str, password_hash: &str, username: &str, verification_token: Option<&str>) -> Result<User, AppError> {
         let email = email.to_string();
         let password_hash = password_hash.to_string();
         let username = username.to_string();
         let verification_token = verification_token.map(|s| s.to_string());
         self.with_write(move |conn| {
             conn.query_row(
                 "INSERT INTO user (email, password_hash, username, verification_token, created_at)
                  VALUES (?1, ?2, ?3, ?4, ?5)
                  RETURNING *",
                 params![email, password_hash, username, verification_token, now_epoch()],
                 Self::row_to_user,
             ).map_err(|e| AppError::Database(e.to_string()))
         }).await
     }
 
     pub async fn verify_email_token(&self, token: &str) -> Result<bool, AppError> {
         let token = token.to_string();
         self.with_write(move |conn| {
             let rows_affected = conn.execute(
                 "UPDATE user SET email_verified = 1, verification_token = NULL WHERE verification_token = ?1",
                 params![token],
             ).map_err(|e| AppError::Database(e.to_string()))?;
             Ok(rows_affected > 0)
         }).await
     }
 
     pub async fn update_verification_token(&self, user_id: i64, token: &str) -> Result<(), AppError> {
         let token = token.to_string();
         self.with_write(move |conn| {
             conn.execute(
                 "UPDATE user SET verification_token = ?1 WHERE id = ?2",
                 params![token, user_id],
             ).map_err(|e| AppError::Database(e.to_string()))?;
             Ok(())
         }).await
     }
 
     // ======================================================================
     // Session queries
     // ======================================================================
 
     pub async fn create_session(&self, user_id: i64) -> Result<String, AppError> {
         use rand::Rng;
         let bytes: [u8; 32] = rand::thread_rng().gen();
         let token: String = bytes.iter().map(|b| format!("{:02x}", b)).collect();
 
         self.with_write(move |conn| {
             conn.execute(
                 "INSERT INTO session (token, user_id, created_at) VALUES (?1, ?2, ?3)",
                 params![token, user_id, now_epoch()],
             ).map_err(|e| AppError::Database(e.to_string()))?;
 
             Ok(token)
         }).await
     }
 
     pub async fn get_session_user_id(&self, token: &str) -> Result<Option<i64>, AppError> {
         let token = token.to_string();
         self.with_read(move |conn| {
             let mut stmt = conn.prepare_cached("SELECT user_id FROM session WHERE token = ?1")
                 .map_err(|e| AppError::Database(e.to_string()))?;
 
             match stmt.query_row(params![token], |row| row.get::<_, i64>(0)) {
                 Ok(id) => Ok(Some(id)),
                 Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
                 Err(e) => Err(AppError::Database(e.to_string())),
             }
         }).await
     }
 
     pub async fn delete_session(&self, token: &str) -> Result<(), AppError> {
         let token = token.to_string();
         self.with_write(move |conn| {
             conn.execute("DELETE FROM session WHERE token = ?1", params![token])
                 .map_err(|e| AppError::Database(e.to_string()))?;
             Ok(())
         }).await
     }
 
     pub async fn delete_user_sessions(&self, user_id: i64) -> Result<(), AppError> {
         self.with_write(move |conn| {
             conn.execute("DELETE FROM session WHERE user_id = ?1", params![user_id])
                 .map_err(|e| AppError::Database(e.to_string()))?;
             Ok(())
         }).await
     }
 
     // ======================================================================
     // Enrichment queries
     // ======================================================================
 
     pub async fn enrich_stories(&self, story_ids: &[i64]) -> Result<Vec<StoryMeta>, AppError> {
         if story_ids.is_empty() {
             return Ok(vec![]);
         }
 
         let story_ids = story_ids.to_vec();
         self.with_read(move |conn| {
             // Fetch author usernames
             let placeholders: Vec<String> = story_ids.iter().enumerate().map(|(i, _)| format!("?{}", i + 1)).collect();
             let sql = format!(
                 "SELECT s.id, u.username FROM story s
                  LEFT JOIN user u ON u.id = s.created_by
                  WHERE s.id IN ({})",
                 placeholders.join(", ")
             );
 
             let mut stmt = conn.prepare_cached(&sql).map_err(|e| AppError::Database(e.to_string()))?;
             let mut param_values: Vec<Box<dyn rusqlite::types::ToSql>> = Vec::new();
             for &id in &story_ids {
                 param_values.push(Box::new(id));
             }
             let params_ref: Vec<&dyn rusqlite::types::ToSql> = param_values.iter().map(|p| p.as_ref()).collect();
 
             let author_map: HashMap<i64, Option<String>> = stmt
                 .query_map(params_ref.as_slice(), |row| {
                     Ok((row.get::<_, i64>(0)?, row.get::<_, Option<String>>(1)?))
                 })
                 .map_err(|e| AppError::Database(e.to_string()))?
                 .filter_map(|r| r.ok())
                 .collect();
 
             // Fetch tags
             let tag_sql = format!(
                 "SELECT sc.news_item_id, c.name FROM story_category sc
                  JOIN category c ON c.id = sc.category_id
                  WHERE sc.news_item_id IN ({})",
                 placeholders.join(", ")
             );
 
             let mut tag_stmt = conn.prepare_cached(&tag_sql).map_err(|e| AppError::Database(e.to_string()))?;
 
             let mut tags_map: HashMap<i64, Vec<String>> = HashMap::new();
             let tag_rows = tag_stmt
                 .query_map(params_ref.as_slice(), |row| {
                     Ok((row.get::<_, i64>(0)?, row.get::<_, Option<String>>(1)?))
                 })
                 .map_err(|e| AppError::Database(e.to_string()))?;
 
             for row in tag_rows {
                 if let Ok((story_id, Some(name))) = row {
                     tags_map.entry(story_id).or_default().push(name);
                 }
             }
 
             let result: Vec<StoryMeta> = story_ids
                 .iter()
                 .map(|&sid| StoryMeta {
                     story_id: sid,
                     author_username: author_map.get(&sid).cloned().flatten(),
                     tags: tags_map.get(&sid).cloned().unwrap_or_default(),
                 })
                 .collect();
 
             Ok(result)
         }).await
     }
 
     pub async fn enrich_comment_authors(&self, user_ids: &[i64]) -> Result<HashMap<i64, String>, AppError> {
         if user_ids.is_empty() {
             return Ok(HashMap::new());
         }
 
         let user_ids = user_ids.to_vec();
         self.with_read(move |conn| {
             let placeholders: Vec<String> = user_ids.iter().enumerate().map(|(i, _)| format!("?{}", i + 1)).collect();
             let sql = format!(
                 "SELECT id, username FROM user WHERE id IN ({})",
                 placeholders.join(", ")
             );
 
             let mut stmt = conn.prepare_cached(&sql).map_err(|e| AppError::Database(e.to_string()))?;
             let mut param_values: Vec<Box<dyn rusqlite::types::ToSql>> = Vec::new();
             for &id in &user_ids {
                 param_values.push(Box::new(id));
             }
             let params_ref: Vec<&dyn rusqlite::types::ToSql> = param_values.iter().map(|p| p.as_ref()).collect();
 
             let map: HashMap<i64, String> = stmt
                 .query_map(params_ref.as_slice(), |row| {
                     Ok((row.get::<_, i64>(0)?, row.get::<_, String>(1)?))
                 })
                 .map_err(|e| AppError::Database(e.to_string()))?
                 .filter_map(|r| r.ok())
                 .collect();
 
             Ok(map)
         }).await
     }
 
 }
 
 // ==========================================================================
 // Helper functions
 // ==========================================================================
 
 pub fn now_epoch() -> i64 {
     std::time::SystemTime::now()
         .duration_since(std::time::UNIX_EPOCH)
         .unwrap()
         .as_secs() as i64
 }
 
 pub fn hot_score(points: i64, published: i64) -> f64 {
     let age_hours = (now_epoch() - published) as f64 / 3600.0;
     (points as f64) / (age_hours + 2.0).powf(1.8)
 }
 
 pub fn time_ago(timestamp: i64) -> String {
     let elapsed = now_epoch() - timestamp;
 
     let days = elapsed / 86400;
     let hours = elapsed / 3600;
     let minutes = elapsed / 60;
 
     if days > 0 {
         if days == 1 { "1 day ago".to_string() }
         else { format!("{days} days ago") }
     } else if hours > 0 {
         if hours == 1 { "1 hour ago".to_string() }
         else { format!("{hours} hours ago") }
     } else if minutes > 0 {
         if minutes == 1 { "1 minute ago".to_string() }
         else { format!("{minutes} minutes ago") }
     } else {
         "just now".to_string()
     }
 }
 
 /// Extract domain from URL
 pub fn extract_domain(url_str: &str) -> Option<String> {
     url_str
         .split("//")
         .nth(1)
         .and_then(|rest| rest.split('/').next())
         .map(|host| host.trim_start_matches("www.").to_string())
 }