koprogo_api/infrastructure/database/repositories/
account_repository_impl.rs

1// Infrastructure: PostgreSQL Account Repository Implementation
2//
3// CREDITS & ATTRIBUTION:
4// This implementation is inspired by the Noalyss project (https://gitlab.com/noalyss/noalyss)
5// Noalyss is a free accounting software for Belgian and French accounting
6// License: GPL-2.0-or-later (GNU General Public License version 2 or later)
7// Copyright: (C) 1989, 1991 Free Software Foundation, Inc.
8// Copyright: Dany De Bontridder <dany@alchimerys.eu>
9//
10// The SQL queries and approach are based on Noalyss' Acc_Plan_SQL and Tmp_Pcmn_SQL classes.
11// See: include/database/acc_plan_sql.class.php in Noalyss repository
12
13use crate::application::ports::AccountRepository;
14use crate::domain::entities::{Account, AccountType};
15use async_trait::async_trait;
16use sqlx::PgPool;
17use uuid::Uuid;
18
19/// PostgreSQL implementation of AccountRepository
20///
21/// Manages accounts in the Belgian PCMN (Plan Comptable Minimum Normalisé).
22/// Inspired by Noalyss PostgreSQL schema and repository pattern.
23pub struct PostgresAccountRepository {
24    pool: PgPool,
25}
26
27impl PostgresAccountRepository {
28    pub fn new(pool: PgPool) -> Self {
29        Self { pool }
30    }
31}
32
33#[async_trait]
34impl AccountRepository for PostgresAccountRepository {
35    async fn create(&self, account: &Account) -> Result<Account, String> {
36        let result = sqlx::query!(
37            r#"
38            INSERT INTO accounts (id, code, label, parent_code, account_type, direct_use, organization_id, created_at, updated_at)
39            VALUES ($1, $2, $3, $4, $5::account_type, $6, $7, $8, $9)
40            RETURNING id, code, label, parent_code, account_type AS "account_type: AccountType",
41                      direct_use, organization_id, created_at, updated_at
42            "#,
43            account.id,
44            &account.code,
45            &account.label,
46            account.parent_code.as_deref(),
47            account.account_type.clone() as AccountType,
48            account.direct_use,
49            account.organization_id,
50            account.created_at,
51            account.updated_at
52        )
53        .fetch_one(&self.pool)
54        .await
55        .map_err(|e| format!("Failed to create account: {}", e))?;
56
57        Ok(Account {
58            id: result.id,
59            code: result.code,
60            label: result.label,
61            parent_code: result.parent_code,
62            account_type: result.account_type,
63            direct_use: result.direct_use,
64            organization_id: result.organization_id,
65            created_at: result.created_at,
66            updated_at: result.updated_at,
67        })
68    }
69
70    async fn find_by_id(&self, id: Uuid) -> Result<Option<Account>, String> {
71        let result = sqlx::query!(
72            r#"
73            SELECT id, code, label, parent_code, account_type AS "account_type: AccountType",
74                   direct_use, organization_id, created_at, updated_at
75            FROM accounts
76            WHERE id = $1
77            "#,
78            id
79        )
80        .fetch_optional(&self.pool)
81        .await
82        .map_err(|e| format!("Failed to find account by id: {}", e))?;
83
84        Ok(result.map(|row| Account {
85            id: row.id,
86            code: row.code,
87            label: row.label,
88            parent_code: row.parent_code,
89            account_type: row.account_type,
90            direct_use: row.direct_use,
91            organization_id: row.organization_id,
92            created_at: row.created_at,
93            updated_at: row.updated_at,
94        }))
95    }
96
97    async fn find_by_code(
98        &self,
99        code: &str,
100        organization_id: Uuid,
101    ) -> Result<Option<Account>, String> {
102        let result = sqlx::query!(
103            r#"
104            SELECT id, code, label, parent_code, account_type AS "account_type: AccountType",
105                   direct_use, organization_id, created_at, updated_at
106            FROM accounts
107            WHERE code = $1 AND organization_id = $2
108            "#,
109            code,
110            organization_id
111        )
112        .fetch_optional(&self.pool)
113        .await
114        .map_err(|e| format!("Failed to find account by code: {}", e))?;
115
116        Ok(result.map(|row| Account {
117            id: row.id,
118            code: row.code,
119            label: row.label,
120            parent_code: row.parent_code,
121            account_type: row.account_type,
122            direct_use: row.direct_use,
123            organization_id: row.organization_id,
124            created_at: row.created_at,
125            updated_at: row.updated_at,
126        }))
127    }
128
129    async fn find_by_organization(&self, organization_id: Uuid) -> Result<Vec<Account>, String> {
130        let rows = sqlx::query!(
131            r#"
132            SELECT id, code, label, parent_code, account_type AS "account_type: AccountType",
133                   direct_use, organization_id, created_at, updated_at
134            FROM accounts
135            WHERE organization_id = $1
136            ORDER BY code ASC
137            "#,
138            organization_id
139        )
140        .fetch_all(&self.pool)
141        .await
142        .map_err(|e| format!("Failed to find accounts by organization: {}", e))?;
143
144        Ok(rows
145            .into_iter()
146            .map(|row| Account {
147                id: row.id,
148                code: row.code,
149                label: row.label,
150                parent_code: row.parent_code,
151                account_type: row.account_type,
152                direct_use: row.direct_use,
153                organization_id: row.organization_id,
154                created_at: row.created_at,
155                updated_at: row.updated_at,
156            })
157            .collect())
158    }
159
160    async fn find_by_type(
161        &self,
162        account_type: AccountType,
163        organization_id: Uuid,
164    ) -> Result<Vec<Account>, String> {
165        let rows = sqlx::query!(
166            r#"
167            SELECT id, code, label, parent_code, account_type AS "account_type: AccountType",
168                   direct_use, organization_id, created_at, updated_at
169            FROM accounts
170            WHERE account_type = $1::account_type AND organization_id = $2
171            ORDER BY code ASC
172            "#,
173            account_type as AccountType,
174            organization_id
175        )
176        .fetch_all(&self.pool)
177        .await
178        .map_err(|e| format!("Failed to find accounts by type: {}", e))?;
179
180        Ok(rows
181            .into_iter()
182            .map(|row| Account {
183                id: row.id,
184                code: row.code,
185                label: row.label,
186                parent_code: row.parent_code,
187                account_type: row.account_type,
188                direct_use: row.direct_use,
189                organization_id: row.organization_id,
190                created_at: row.created_at,
191                updated_at: row.updated_at,
192            })
193            .collect())
194    }
195
196    async fn find_by_parent_code(
197        &self,
198        parent_code: &str,
199        organization_id: Uuid,
200    ) -> Result<Vec<Account>, String> {
201        let rows = sqlx::query!(
202            r#"
203            SELECT id, code, label, parent_code, account_type AS "account_type: AccountType",
204                   direct_use, organization_id, created_at, updated_at
205            FROM accounts
206            WHERE parent_code = $1 AND organization_id = $2
207            ORDER BY code ASC
208            "#,
209            parent_code,
210            organization_id
211        )
212        .fetch_all(&self.pool)
213        .await
214        .map_err(|e| format!("Failed to find accounts by parent code: {}", e))?;
215
216        Ok(rows
217            .into_iter()
218            .map(|row| Account {
219                id: row.id,
220                code: row.code,
221                label: row.label,
222                parent_code: row.parent_code,
223                account_type: row.account_type,
224                direct_use: row.direct_use,
225                organization_id: row.organization_id,
226                created_at: row.created_at,
227                updated_at: row.updated_at,
228            })
229            .collect())
230    }
231
232    async fn find_direct_use_accounts(
233        &self,
234        organization_id: Uuid,
235    ) -> Result<Vec<Account>, String> {
236        let rows = sqlx::query!(
237            r#"
238            SELECT id, code, label, parent_code, account_type AS "account_type: AccountType",
239                   direct_use, organization_id, created_at, updated_at
240            FROM accounts
241            WHERE direct_use = true AND organization_id = $1
242            ORDER BY code ASC
243            "#,
244            organization_id
245        )
246        .fetch_all(&self.pool)
247        .await
248        .map_err(|e| format!("Failed to find direct use accounts: {}", e))?;
249
250        Ok(rows
251            .into_iter()
252            .map(|row| Account {
253                id: row.id,
254                code: row.code,
255                label: row.label,
256                parent_code: row.parent_code,
257                account_type: row.account_type,
258                direct_use: row.direct_use,
259                organization_id: row.organization_id,
260                created_at: row.created_at,
261                updated_at: row.updated_at,
262            })
263            .collect())
264    }
265
266    async fn search_by_code_pattern(
267        &self,
268        code_pattern: &str,
269        organization_id: Uuid,
270    ) -> Result<Vec<Account>, String> {
271        let rows = sqlx::query!(
272            r#"
273            SELECT id, code, label, parent_code, account_type AS "account_type: AccountType",
274                   direct_use, organization_id, created_at, updated_at
275            FROM accounts
276            WHERE code LIKE $1 AND organization_id = $2
277            ORDER BY code ASC
278            "#,
279            code_pattern,
280            organization_id
281        )
282        .fetch_all(&self.pool)
283        .await
284        .map_err(|e| format!("Failed to search accounts by code pattern: {}", e))?;
285
286        Ok(rows
287            .into_iter()
288            .map(|row| Account {
289                id: row.id,
290                code: row.code,
291                label: row.label,
292                parent_code: row.parent_code,
293                account_type: row.account_type,
294                direct_use: row.direct_use,
295                organization_id: row.organization_id,
296                created_at: row.created_at,
297                updated_at: row.updated_at,
298            })
299            .collect())
300    }
301
302    async fn update(&self, account: &Account) -> Result<Account, String> {
303        let result = sqlx::query!(
304            r#"
305            UPDATE accounts
306            SET code = $2, label = $3, parent_code = $4, account_type = $5::account_type,
307                direct_use = $6, updated_at = $7
308            WHERE id = $1
309            RETURNING id, code, label, parent_code, account_type AS "account_type: AccountType",
310                      direct_use, organization_id, created_at, updated_at
311            "#,
312            account.id,
313            &account.code,
314            &account.label,
315            account.parent_code.as_deref(),
316            account.account_type.clone() as AccountType,
317            account.direct_use,
318            account.updated_at
319        )
320        .fetch_one(&self.pool)
321        .await
322        .map_err(|e| format!("Failed to update account: {}", e))?;
323
324        Ok(Account {
325            id: result.id,
326            code: result.code,
327            label: result.label,
328            parent_code: result.parent_code,
329            account_type: result.account_type,
330            direct_use: result.direct_use,
331            organization_id: result.organization_id,
332            created_at: result.created_at,
333            updated_at: result.updated_at,
334        })
335    }
336
337    async fn delete(&self, id: Uuid) -> Result<(), String> {
338        // Validation inspired by Noalyss Acc_Plan_SQL::delete()
339        // Check if account has children
340        let has_children = sqlx::query!(
341            "SELECT COUNT(*) as count FROM accounts WHERE parent_code = (SELECT code FROM accounts WHERE id = $1)",
342            id
343        )
344        .fetch_one(&self.pool)
345        .await
346        .map_err(|e| format!("Failed to check for child accounts: {}", e))?;
347
348        if has_children.count.unwrap_or(0) > 0 {
349            return Err(
350                "Cannot delete account: it has child accounts. Delete children first.".to_string(),
351            );
352        }
353
354        // Check if account is used in expenses
355        let is_used = sqlx::query!(
356            "SELECT COUNT(*) as count FROM expenses WHERE account_code = (SELECT code FROM accounts WHERE id = $1)",
357            id
358        )
359        .fetch_one(&self.pool)
360        .await
361        .map_err(|e| format!("Failed to check account usage: {}", e))?;
362
363        if is_used.count.unwrap_or(0) > 0 {
364            return Err(
365                "Cannot delete account: it is used in expense transactions. Archive instead."
366                    .to_string(),
367            );
368        }
369
370        // Proceed with deletion
371        let result = sqlx::query!("DELETE FROM accounts WHERE id = $1", id)
372            .execute(&self.pool)
373            .await
374            .map_err(|e| format!("Failed to delete account: {}", e))?;
375
376        if result.rows_affected() == 0 {
377            return Err("Account not found".to_string());
378        }
379
380        Ok(())
381    }
382
383    async fn exists(&self, code: &str, organization_id: Uuid) -> Result<bool, String> {
384        let result = sqlx::query!(
385            "SELECT COUNT(*) as count FROM accounts WHERE code = $1 AND organization_id = $2",
386            code,
387            organization_id
388        )
389        .fetch_one(&self.pool)
390        .await
391        .map_err(|e| format!("Failed to check account existence: {}", e))?;
392
393        Ok(result.count.unwrap_or(0) > 0)
394    }
395
396    async fn count_by_organization(&self, organization_id: Uuid) -> Result<i64, String> {
397        let result = sqlx::query!(
398            "SELECT COUNT(*) as count FROM accounts WHERE organization_id = $1",
399            organization_id
400        )
401        .fetch_one(&self.pool)
402        .await
403        .map_err(|e| format!("Failed to count accounts: {}", e))?;
404
405        Ok(result.count.unwrap_or(0))
406    }
407}
koprogo_api/infrastructure/database/repositories/account_repository_impl.rs

koprogo_api/infrastructure/database/repositories/
account_repository_impl.rs
