stateset_db/sqlite/
reviews.rs

1//! SQLite implementation of product review repository
2
3use super::{
4    map_db_error, parse_datetime_row, parse_enum_row, parse_uuid_row, with_immediate_transaction,
5};
6use chrono::Utc;
7use r2d2::Pool;
8use r2d2_sqlite::SqliteConnectionManager;
9use stateset_core::{
10    CommerceError, CreateReview, ProductId, Result, Review, ReviewFilter, ReviewId,
11    ReviewRepository, ReviewSummary, UpdateReview,
12};
13
14#[derive(Debug)]
15pub struct SqliteReviewRepository {
16    pool: Pool<SqliteConnectionManager>,
17}
18
19impl SqliteReviewRepository {
20    #[must_use]
21    pub const fn new(pool: Pool<SqliteConnectionManager>) -> Self {
22        Self { pool }
23    }
24
25    fn conn(&self) -> Result<r2d2::PooledConnection<SqliteConnectionManager>> {
26        self.pool.get().map_err(|e| CommerceError::DatabaseError(e.to_string()))
27    }
28
29    fn row_to_review(row: &rusqlite::Row<'_>) -> rusqlite::Result<Review> {
30        Ok(Review {
31            id: parse_uuid_row(&row.get::<_, String>("id")?, "review", "id")?.into(),
32            product_id: parse_uuid_row(
33                &row.get::<_, String>("product_id")?,
34                "review",
35                "product_id",
36            )?
37            .into(),
38            customer_id: parse_uuid_row(
39                &row.get::<_, String>("customer_id")?,
40                "review",
41                "customer_id",
42            )?
43            .into(),
44            rating: row.get::<_, i32>("rating")? as u8,
45            title: row.get("title")?,
46            body: row.get("body")?,
47            status: parse_enum_row(&row.get::<_, String>("status")?, "review", "status")?,
48            verified_purchase: row.get::<_, i32>("verified_purchase")? != 0,
49            helpful_count: row.get::<_, i32>("helpful_count")? as u32,
50            reported_count: row.get::<_, i32>("reported")? as u32,
51            created_at: parse_datetime_row(
52                &row.get::<_, String>("created_at")?,
53                "review",
54                "created_at",
55            )?,
56            updated_at: parse_datetime_row(
57                &row.get::<_, String>("updated_at")?,
58                "review",
59                "updated_at",
60            )?,
61        })
62    }
63}
64
65impl ReviewRepository for SqliteReviewRepository {
66    fn create(&self, input: CreateReview) -> Result<Review> {
67        let id = ReviewId::new();
68        let now = Utc::now();
69        let id_str = id.to_string();
70        let now_str = now.to_rfc3339();
71
72        with_immediate_transaction(&self.pool, |tx| {
73            tx.execute(
74                "INSERT INTO reviews (id, product_id, customer_id, rating, title, body, status, verified_purchase, created_at, updated_at)
75                 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
76                rusqlite::params![
77                    &id_str,
78                    input.product_id.to_string(),
79                    input.customer_id.to_string(),
80                    input.rating as i32,
81                    &input.title,
82                    &input.body,
83                    "pending",
84                    input.verified_purchase as i32,
85                    &now_str,
86                    &now_str,
87                ],
88            )?;
89
90            tx.query_row("SELECT * FROM reviews WHERE id = ?", [&id_str], Self::row_to_review)
91        })
92    }
93
94    fn get(&self, id: ReviewId) -> Result<Option<Review>> {
95        let conn = self.conn()?;
96        match conn.query_row(
97            "SELECT * FROM reviews WHERE id = ?",
98            [id.to_string()],
99            Self::row_to_review,
100        ) {
101            Ok(review) => Ok(Some(review)),
102            Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
103            Err(e) => Err(map_db_error(e)),
104        }
105    }
106
107    fn update(&self, id: ReviewId, input: UpdateReview) -> Result<Review> {
108        let id_str = id.to_string();
109        let now_str = Utc::now().to_rfc3339();
110
111        with_immediate_transaction(&self.pool, |tx| {
112            let mut sets = vec!["updated_at = ?".to_string()];
113            let mut params: Vec<Box<dyn rusqlite::types::ToSql>> = vec![Box::new(now_str.clone())];
114
115            if let Some(rating) = input.rating {
116                sets.push("rating = ?".into());
117                params.push(Box::new(rating as i32));
118            }
119            if let Some(ref title) = input.title {
120                sets.push("title = ?".into());
121                params.push(Box::new(title.clone()));
122            }
123            if let Some(ref body) = input.body {
124                sets.push("body = ?".into());
125                params.push(Box::new(body.clone()));
126            }
127            if let Some(status) = input.status {
128                sets.push("status = ?".into());
129                params.push(Box::new(status.to_string()));
130            }
131
132            let sql = format!("UPDATE reviews SET {} WHERE id = ?", sets.join(", "));
133            params.push(Box::new(id_str.clone()));
134
135            let param_refs: Vec<&dyn rusqlite::types::ToSql> =
136                params.iter().map(|p| p.as_ref()).collect();
137            tx.execute(&sql, param_refs.as_slice())?;
138
139            tx.query_row("SELECT * FROM reviews WHERE id = ?", [&id_str], Self::row_to_review)
140        })
141    }
142
143    fn list(&self, filter: ReviewFilter) -> Result<Vec<Review>> {
144        let conn = self.conn()?;
145        let mut sql = "SELECT * FROM reviews WHERE 1=1".to_string();
146        let mut params: Vec<Box<dyn rusqlite::types::ToSql>> = vec![];
147
148        if let Some(product_id) = filter.product_id {
149            sql.push_str(" AND product_id = ?");
150            params.push(Box::new(product_id.to_string()));
151        }
152        if let Some(customer_id) = filter.customer_id {
153            sql.push_str(" AND customer_id = ?");
154            params.push(Box::new(customer_id.to_string()));
155        }
156        if let Some(status) = filter.status {
157            sql.push_str(" AND status = ?");
158            params.push(Box::new(status.to_string()));
159        }
160        if let Some(min_rating) = filter.min_rating {
161            sql.push_str(" AND rating >= ?");
162            params.push(Box::new(min_rating as i32));
163        }
164        if let Some(verified_only) = filter.verified_only {
165            // `verified_purchase` is stored as INTEGER (0/1); Postgres applies this
166            // filter too.
167            sql.push_str(" AND verified_purchase = ?");
168            params.push(Box::new(verified_only as i32));
169        }
170
171        sql.push_str(" ORDER BY created_at DESC");
172
173        crate::sqlite::append_limit_offset(&mut sql, filter.limit, filter.offset);
174
175        let param_refs: Vec<&dyn rusqlite::types::ToSql> =
176            params.iter().map(|p| p.as_ref()).collect();
177        let mut stmt = conn.prepare(&sql).map_err(map_db_error)?;
178        let reviews = stmt
179            .query_map(param_refs.as_slice(), Self::row_to_review)
180            .map_err(map_db_error)?
181            .collect::<std::result::Result<Vec<_>, _>>()
182            .map_err(map_db_error)?;
183        Ok(reviews)
184    }
185
186    fn delete(&self, id: ReviewId) -> Result<()> {
187        let conn = self.conn()?;
188        conn.execute("DELETE FROM reviews WHERE id = ?", [id.to_string()]).map_err(map_db_error)?;
189        Ok(())
190    }
191
192    fn get_summary(&self, product_id: ProductId) -> Result<ReviewSummary> {
193        let conn = self.conn()?;
194        let pid = product_id.to_string();
195
196        let total: i64 = conn
197            .query_row(
198                "SELECT COUNT(*) FROM reviews WHERE product_id = ? AND status = 'approved'",
199                [&pid],
200                |row| row.get(0),
201            )
202            .map_err(map_db_error)?;
203
204        let avg: f64 = conn
205            .query_row(
206                "SELECT COALESCE(AVG(CAST(rating AS REAL)), 0.0) FROM reviews WHERE product_id = ? AND status = 'approved'",
207                [&pid],
208                |row| row.get(0),
209            )
210            .map_err(map_db_error)?;
211
212        // Compute rating distribution
213        let mut distribution = [0u32; 5];
214        let mut stmt = conn
215            .prepare("SELECT rating, COUNT(*) FROM reviews WHERE product_id = ? AND status = 'approved' GROUP BY rating")
216            .map_err(map_db_error)?;
217        let rows = stmt
218            .query_map([&pid], |row| Ok((row.get::<_, i32>(0)?, row.get::<_, i32>(1)?)))
219            .map_err(map_db_error)?;
220        for (rating, count) in rows.flatten() {
221            let idx = (rating - 1).clamp(0, 4) as usize;
222            distribution[idx] = count as u32;
223        }
224
225        Ok(ReviewSummary {
226            product_id,
227            total_reviews: total as u64,
228            average_rating: avg,
229            rating_distribution: distribution,
230        })
231    }
232
233    fn mark_helpful(&self, id: ReviewId) -> Result<()> {
234        let conn = self.conn()?;
235        conn.execute(
236            "UPDATE reviews SET helpful_count = helpful_count + 1, updated_at = ? WHERE id = ?",
237            rusqlite::params![Utc::now().to_rfc3339(), id.to_string()],
238        )
239        .map_err(map_db_error)?;
240        Ok(())
241    }
242
243    fn mark_reported(&self, id: ReviewId) -> Result<()> {
244        let conn = self.conn()?;
245        conn.execute(
246            "UPDATE reviews SET reported = reported + 1, updated_at = ? WHERE id = ?",
247            rusqlite::params![Utc::now().to_rfc3339(), id.to_string()],
248        )
249        .map_err(map_db_error)?;
250        Ok(())
251    }
252}
253
254#[cfg(test)]
255mod tests {
256    use super::*;
257    use crate::DatabaseConfig;
258    use crate::sqlite::SqliteDatabase;
259    use stateset_core::CustomerId;
260
261    fn test_repo() -> SqliteReviewRepository {
262        let db = SqliteDatabase::new(&DatabaseConfig::in_memory()).unwrap();
263        // V4 reviews table isn't in the base migration set — create it for tests
264        let conn = db.conn().unwrap();
265        conn.execute_batch(
266            "CREATE TABLE IF NOT EXISTS reviews (
267                id TEXT PRIMARY KEY,
268                product_id TEXT NOT NULL,
269                customer_id TEXT NOT NULL,
270                order_id TEXT,
271                rating INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5),
272                title TEXT,
273                body TEXT,
274                status TEXT NOT NULL DEFAULT 'pending',
275                helpful_count INTEGER NOT NULL DEFAULT 0,
276                reported INTEGER NOT NULL DEFAULT 0,
277                verified_purchase INTEGER NOT NULL DEFAULT 0,
278                created_at TEXT NOT NULL DEFAULT (datetime('now')),
279                updated_at TEXT NOT NULL DEFAULT (datetime('now'))
280            )",
281        )
282        .unwrap();
283        SqliteReviewRepository::new(db.pool().clone())
284    }
285
286    #[test]
287    fn create_and_get_review() {
288        let repo = test_repo();
289        let review = repo
290            .create(CreateReview {
291                product_id: ProductId::new(),
292                customer_id: CustomerId::new(),
293                rating: 5,
294                title: Some("Great product".into()),
295                body: Some("Really loved it".into()),
296                verified_purchase: true,
297            })
298            .unwrap();
299
300        assert_eq!(review.rating, 5);
301        assert_eq!(review.title.as_deref(), Some("Great product"));
302        assert!(review.verified_purchase);
303
304        let fetched = repo.get(review.id).unwrap().unwrap();
305        assert_eq!(fetched.id, review.id);
306    }
307
308    #[test]
309    fn list_reviews_by_product() {
310        let repo = test_repo();
311        let product_id = ProductId::new();
312
313        for i in 1..=3 {
314            repo.create(CreateReview {
315                product_id,
316                customer_id: CustomerId::new(),
317                rating: i as u8 + 2,
318                title: None,
319                body: None,
320                verified_purchase: false,
321            })
322            .unwrap();
323        }
324
325        let reviews =
326            repo.list(ReviewFilter { product_id: Some(product_id), ..Default::default() }).unwrap();
327        assert_eq!(reviews.len(), 3);
328    }
329
330    #[test]
331    fn list_filters_by_verified_only() {
332        let repo = test_repo();
333        let product_id = ProductId::new();
334        repo.create(CreateReview {
335            product_id,
336            customer_id: CustomerId::new(),
337            rating: 5,
338            title: None,
339            body: None,
340            verified_purchase: true,
341        })
342        .unwrap();
343        repo.create(CreateReview {
344            product_id,
345            customer_id: CustomerId::new(),
346            rating: 4,
347            title: None,
348            body: None,
349            verified_purchase: false,
350        })
351        .unwrap();
352
353        // Postgres applies `verified_only`; SQLite must too.
354        let verified = repo
355            .list(ReviewFilter {
356                product_id: Some(product_id),
357                verified_only: Some(true),
358                ..Default::default()
359            })
360            .unwrap();
361        assert_eq!(verified.len(), 1, "verified_only must filter to verified-purchase reviews");
362        assert!(verified[0].verified_purchase);
363    }
364
365    #[test]
366    fn delete_review() {
367        let repo = test_repo();
368        let review = repo
369            .create(CreateReview {
370                product_id: ProductId::new(),
371                customer_id: CustomerId::new(),
372                rating: 3,
373                title: None,
374                body: None,
375                verified_purchase: false,
376            })
377            .unwrap();
378
379        repo.delete(review.id).unwrap();
380        assert!(repo.get(review.id).unwrap().is_none());
381    }
382
383    #[test]
384    fn mark_helpful_increments() {
385        let repo = test_repo();
386        let review = repo
387            .create(CreateReview {
388                product_id: ProductId::new(),
389                customer_id: CustomerId::new(),
390                rating: 4,
391                title: None,
392                body: None,
393                verified_purchase: false,
394            })
395            .unwrap();
396        assert_eq!(review.helpful_count, 0);
397
398        repo.mark_helpful(review.id).unwrap();
399        repo.mark_helpful(review.id).unwrap();
400
401        let updated = repo.get(review.id).unwrap().unwrap();
402        assert_eq!(updated.helpful_count, 2);
403    }
404}
stateset_db/sqlite/reviews.rs

stateset_db/sqlite/
reviews.rs
