koprogo_api/infrastructure/database/repositories/
notice_repository_impl.rs

1use crate::application::ports::NoticeRepository;
2use crate::domain::entities::{Notice, NoticeCategory, NoticeStatus, NoticeType};
3use crate::infrastructure::database::DbPool;
4use async_trait::async_trait;
5use chrono::Utc;
6use sqlx::Row;
7use uuid::Uuid;
8
9pub struct PostgresNoticeRepository {
10    pool: DbPool,
11}
12
13impl PostgresNoticeRepository {
14    pub fn new(pool: DbPool) -> Self {
15        Self { pool }
16    }
17}
18
19/// Helper function to map database row to Notice entity
20fn map_row_to_notice(row: &sqlx::postgres::PgRow) -> Notice {
21    let notice_type_str: String = row.get("notice_type");
22    let category_str: String = row.get("category");
23    let status_str: String = row.get("status");
24
25    Notice {
26        id: row.get("id"),
27        building_id: row.get("building_id"),
28        author_id: row.get("author_id"),
29        notice_type: serde_json::from_str(&format!("\"{}\"", notice_type_str))
30            .unwrap_or(NoticeType::Announcement),
31        category: serde_json::from_str(&format!("\"{}\"", category_str))
32            .unwrap_or(NoticeCategory::General),
33        title: row.get("title"),
34        content: row.get("content"),
35        status: serde_json::from_str(&format!("\"{}\"", status_str)).unwrap_or(NoticeStatus::Draft),
36        is_pinned: row.get("is_pinned"),
37        published_at: row.get("published_at"),
38        expires_at: row.get("expires_at"),
39        archived_at: row.get("archived_at"),
40        event_date: row.get("event_date"),
41        event_location: row.get("event_location"),
42        contact_info: row.get("contact_info"),
43        created_at: row.get("created_at"),
44        updated_at: row.get("updated_at"),
45    }
46}
47
48#[async_trait]
49impl NoticeRepository for PostgresNoticeRepository {
50    async fn create(&self, notice: &Notice) -> Result<Notice, String> {
51        let notice_type_str = serde_json::to_string(&notice.notice_type)
52            .map_err(|e| format!("Failed to serialize notice_type: {}", e))?
53            .trim_matches('"')
54            .to_string();
55
56        let category_str = serde_json::to_string(&notice.category)
57            .map_err(|e| format!("Failed to serialize category: {}", e))?
58            .trim_matches('"')
59            .to_string();
60
61        let status_str = serde_json::to_string(&notice.status)
62            .map_err(|e| format!("Failed to serialize status: {}", e))?
63            .trim_matches('"')
64            .to_string();
65
66        sqlx::query(
67            r#"
68            INSERT INTO notices (
69                id, building_id, author_id, notice_type, category, title, content,
70                status, is_pinned, published_at, expires_at, archived_at,
71                event_date, event_location, contact_info, created_at, updated_at
72            )
73            VALUES ($1, $2, $3, $4::notice_type, $5::notice_category, $6, $7, $8::notice_status,
74                    $9, $10, $11, $12, $13, $14, $15, $16, $17)
75            "#,
76        )
77        .bind(notice.id)
78        .bind(notice.building_id)
79        .bind(notice.author_id)
80        .bind(&notice_type_str)
81        .bind(&category_str)
82        .bind(&notice.title)
83        .bind(&notice.content)
84        .bind(&status_str)
85        .bind(notice.is_pinned)
86        .bind(notice.published_at)
87        .bind(notice.expires_at)
88        .bind(notice.archived_at)
89        .bind(notice.event_date)
90        .bind(&notice.event_location)
91        .bind(&notice.contact_info)
92        .bind(notice.created_at)
93        .bind(notice.updated_at)
94        .execute(&self.pool)
95        .await
96        .map_err(|e| format!("Failed to create notice: {}", e))?;
97
98        Ok(notice.clone())
99    }
100
101    async fn find_by_id(&self, id: Uuid) -> Result<Option<Notice>, String> {
102        let row = sqlx::query(
103            r#"
104            SELECT id, building_id, author_id, notice_type::text AS notice_type,
105                   category::text AS category, title, content, status::text AS status,
106                   is_pinned, published_at, expires_at, archived_at,
107                   event_date, event_location, contact_info, created_at, updated_at
108            FROM notices
109            WHERE id = $1
110            "#,
111        )
112        .bind(id)
113        .fetch_optional(&self.pool)
114        .await
115        .map_err(|e| format!("Failed to find notice by ID: {}", e))?;
116
117        Ok(row.as_ref().map(map_row_to_notice))
118    }
119
120    async fn find_by_building(&self, building_id: Uuid) -> Result<Vec<Notice>, String> {
121        let rows = sqlx::query(
122            r#"
123            SELECT id, building_id, author_id, notice_type::text AS notice_type,
124                   category::text AS category, title, content, status::text AS status,
125                   is_pinned, published_at, expires_at, archived_at,
126                   event_date, event_location, contact_info, created_at, updated_at
127            FROM notices
128            WHERE building_id = $1
129            ORDER BY
130                is_pinned DESC,
131                created_at DESC
132            "#,
133        )
134        .bind(building_id)
135        .fetch_all(&self.pool)
136        .await
137        .map_err(|e| format!("Failed to find notices by building: {}", e))?;
138
139        Ok(rows.iter().map(map_row_to_notice).collect())
140    }
141
142    async fn find_published_by_building(&self, building_id: Uuid) -> Result<Vec<Notice>, String> {
143        let rows = sqlx::query(
144            r#"
145            SELECT id, building_id, author_id, notice_type::text AS notice_type,
146                   category::text AS category, title, content, status::text AS status,
147                   is_pinned, published_at, expires_at, archived_at,
148                   event_date, event_location, contact_info, created_at, updated_at
149            FROM notices
150            WHERE building_id = $1 AND status = 'Published'
151            ORDER BY
152                is_pinned DESC,
153                published_at DESC
154            "#,
155        )
156        .bind(building_id)
157        .fetch_all(&self.pool)
158        .await
159        .map_err(|e| format!("Failed to find published notices: {}", e))?;
160
161        Ok(rows.iter().map(map_row_to_notice).collect())
162    }
163
164    async fn find_pinned_by_building(&self, building_id: Uuid) -> Result<Vec<Notice>, String> {
165        let rows = sqlx::query(
166            r#"
167            SELECT id, building_id, author_id, notice_type::text AS notice_type,
168                   category::text AS category, title, content, status::text AS status,
169                   is_pinned, published_at, expires_at, archived_at,
170                   event_date, event_location, contact_info, created_at, updated_at
171            FROM notices
172            WHERE building_id = $1 AND is_pinned = true
173            ORDER BY published_at DESC
174            "#,
175        )
176        .bind(building_id)
177        .fetch_all(&self.pool)
178        .await
179        .map_err(|e| format!("Failed to find pinned notices: {}", e))?;
180
181        Ok(rows.iter().map(map_row_to_notice).collect())
182    }
183
184    async fn find_by_type(
185        &self,
186        building_id: Uuid,
187        notice_type: NoticeType,
188    ) -> Result<Vec<Notice>, String> {
189        let notice_type_str = serde_json::to_string(&notice_type)
190            .map_err(|e| format!("Failed to serialize notice_type: {}", e))?
191            .trim_matches('"')
192            .to_string();
193
194        let rows = sqlx::query(
195            r#"
196            SELECT id, building_id, author_id, notice_type::text AS notice_type,
197                   category::text AS category, title, content, status::text AS status,
198                   is_pinned, published_at, expires_at, archived_at,
199                   event_date, event_location, contact_info, created_at, updated_at
200            FROM notices
201            WHERE building_id = $1 AND notice_type = $2::notice_type
202            ORDER BY
203                is_pinned DESC,
204                created_at DESC
205            "#,
206        )
207        .bind(building_id)
208        .bind(&notice_type_str)
209        .fetch_all(&self.pool)
210        .await
211        .map_err(|e| format!("Failed to find notices by type: {}", e))?;
212
213        Ok(rows.iter().map(map_row_to_notice).collect())
214    }
215
216    async fn find_by_category(
217        &self,
218        building_id: Uuid,
219        category: NoticeCategory,
220    ) -> Result<Vec<Notice>, String> {
221        let category_str = serde_json::to_string(&category)
222            .map_err(|e| format!("Failed to serialize category: {}", e))?
223            .trim_matches('"')
224            .to_string();
225
226        let rows = sqlx::query(
227            r#"
228            SELECT id, building_id, author_id, notice_type::text AS notice_type,
229                   category::text AS category, title, content, status::text AS status,
230                   is_pinned, published_at, expires_at, archived_at,
231                   event_date, event_location, contact_info, created_at, updated_at
232            FROM notices
233            WHERE building_id = $1 AND category = $2::notice_category
234            ORDER BY
235                is_pinned DESC,
236                created_at DESC
237            "#,
238        )
239        .bind(building_id)
240        .bind(&category_str)
241        .fetch_all(&self.pool)
242        .await
243        .map_err(|e| format!("Failed to find notices by category: {}", e))?;
244
245        Ok(rows.iter().map(map_row_to_notice).collect())
246    }
247
248    async fn find_by_status(
249        &self,
250        building_id: Uuid,
251        status: NoticeStatus,
252    ) -> Result<Vec<Notice>, String> {
253        let status_str = serde_json::to_string(&status)
254            .map_err(|e| format!("Failed to serialize status: {}", e))?
255            .trim_matches('"')
256            .to_string();
257
258        let rows = sqlx::query(
259            r#"
260            SELECT id, building_id, author_id, notice_type::text AS notice_type,
261                   category::text AS category, title, content, status::text AS status,
262                   is_pinned, published_at, expires_at, archived_at,
263                   event_date, event_location, contact_info, created_at, updated_at
264            FROM notices
265            WHERE building_id = $1 AND status = $2::notice_status
266            ORDER BY
267                is_pinned DESC,
268                created_at DESC
269            "#,
270        )
271        .bind(building_id)
272        .bind(&status_str)
273        .fetch_all(&self.pool)
274        .await
275        .map_err(|e| format!("Failed to find notices by status: {}", e))?;
276
277        Ok(rows.iter().map(map_row_to_notice).collect())
278    }
279
280    async fn find_by_author(&self, author_id: Uuid) -> Result<Vec<Notice>, String> {
281        let rows = sqlx::query(
282            r#"
283            SELECT id, building_id, author_id, notice_type::text AS notice_type,
284                   category::text AS category, title, content, status::text AS status,
285                   is_pinned, published_at, expires_at, archived_at,
286                   event_date, event_location, contact_info, created_at, updated_at
287            FROM notices
288            WHERE author_id = $1
289            ORDER BY created_at DESC
290            "#,
291        )
292        .bind(author_id)
293        .fetch_all(&self.pool)
294        .await
295        .map_err(|e| format!("Failed to find notices by author: {}", e))?;
296
297        Ok(rows.iter().map(map_row_to_notice).collect())
298    }
299
300    async fn find_expired(&self, building_id: Uuid) -> Result<Vec<Notice>, String> {
301        let now = Utc::now();
302
303        let rows = sqlx::query(
304            r#"
305            SELECT id, building_id, author_id, notice_type::text AS notice_type,
306                   category::text AS category, title, content, status::text AS status,
307                   is_pinned, published_at, expires_at, archived_at,
308                   event_date, event_location, contact_info, created_at, updated_at
309            FROM notices
310            WHERE building_id = $1
311              AND status = 'Published'
312              AND expires_at IS NOT NULL
313              AND expires_at < $2
314            ORDER BY expires_at ASC
315            "#,
316        )
317        .bind(building_id)
318        .bind(now)
319        .fetch_all(&self.pool)
320        .await
321        .map_err(|e| format!("Failed to find expired notices: {}", e))?;
322
323        Ok(rows.iter().map(map_row_to_notice).collect())
324    }
325
326    async fn update(&self, notice: &Notice) -> Result<Notice, String> {
327        let notice_type_str = serde_json::to_string(&notice.notice_type)
328            .map_err(|e| format!("Failed to serialize notice_type: {}", e))?
329            .trim_matches('"')
330            .to_string();
331
332        let category_str = serde_json::to_string(&notice.category)
333            .map_err(|e| format!("Failed to serialize category: {}", e))?
334            .trim_matches('"')
335            .to_string();
336
337        let status_str = serde_json::to_string(&notice.status)
338            .map_err(|e| format!("Failed to serialize status: {}", e))?
339            .trim_matches('"')
340            .to_string();
341
342        let result = sqlx::query(
343            r#"
344            UPDATE notices
345            SET building_id = $2,
346                author_id = $3,
347                notice_type = $4::notice_type,
348                category = $5::notice_category,
349                title = $6,
350                content = $7,
351                status = $8::notice_status,
352                is_pinned = $9,
353                published_at = $10,
354                expires_at = $11,
355                archived_at = $12,
356                event_date = $13,
357                event_location = $14,
358                contact_info = $15,
359                updated_at = $16
360            WHERE id = $1
361            "#,
362        )
363        .bind(notice.id)
364        .bind(notice.building_id)
365        .bind(notice.author_id)
366        .bind(&notice_type_str)
367        .bind(&category_str)
368        .bind(&notice.title)
369        .bind(&notice.content)
370        .bind(&status_str)
371        .bind(notice.is_pinned)
372        .bind(notice.published_at)
373        .bind(notice.expires_at)
374        .bind(notice.archived_at)
375        .bind(notice.event_date)
376        .bind(&notice.event_location)
377        .bind(&notice.contact_info)
378        .bind(notice.updated_at)
379        .execute(&self.pool)
380        .await
381        .map_err(|e| format!("Failed to update notice: {}", e))?;
382
383        if result.rows_affected() == 0 {
384            return Err("Notice not found".to_string());
385        }
386
387        Ok(notice.clone())
388    }
389
390    async fn delete(&self, id: Uuid) -> Result<(), String> {
391        let result = sqlx::query(
392            r#"
393            DELETE FROM notices
394            WHERE id = $1
395            "#,
396        )
397        .bind(id)
398        .execute(&self.pool)
399        .await
400        .map_err(|e| format!("Failed to delete notice: {}", e))?;
401
402        if result.rows_affected() == 0 {
403            return Err("Notice not found".to_string());
404        }
405
406        Ok(())
407    }
408
409    async fn count_by_building(&self, building_id: Uuid) -> Result<i64, String> {
410        let row = sqlx::query(
411            r#"
412            SELECT COUNT(*) as count
413            FROM notices
414            WHERE building_id = $1
415            "#,
416        )
417        .bind(building_id)
418        .fetch_one(&self.pool)
419        .await
420        .map_err(|e| format!("Failed to count notices by building: {}", e))?;
421
422        let count: i64 = row.get("count");
423        Ok(count)
424    }
425
426    async fn count_published_by_building(&self, building_id: Uuid) -> Result<i64, String> {
427        let row = sqlx::query(
428            r#"
429            SELECT COUNT(*) as count
430            FROM notices
431            WHERE building_id = $1 AND status = 'Published'
432            "#,
433        )
434        .bind(building_id)
435        .fetch_one(&self.pool)
436        .await
437        .map_err(|e| format!("Failed to count published notices: {}", e))?;
438
439        let count: i64 = row.get("count");
440        Ok(count)
441    }
442
443    async fn count_pinned_by_building(&self, building_id: Uuid) -> Result<i64, String> {
444        let row = sqlx::query(
445            r#"
446            SELECT COUNT(*) as count
447            FROM notices
448            WHERE building_id = $1 AND is_pinned = true
449            "#,
450        )
451        .bind(building_id)
452        .fetch_one(&self.pool)
453        .await
454        .map_err(|e| format!("Failed to count pinned notices: {}", e))?;
455
456        let count: i64 = row.get("count");
457        Ok(count)
458    }
459}
koprogo_api/infrastructure/database/repositories/notice_repository_impl.rs

koprogo_api/infrastructure/database/repositories/
notice_repository_impl.rs
