koprogo_api/infrastructure/database/repositories/
building_repository_impl.rs

1use crate::application::dto::{BuildingFilters, PageRequest};
2use crate::application::ports::BuildingRepository;
3use crate::domain::entities::{Building, BuildingMetrics};
4use crate::infrastructure::database::pool::DbPool;
5use async_trait::async_trait;
6use rust_decimal::Decimal;
7use sqlx::Row;
8use uuid::Uuid;
9
10pub struct PostgresBuildingRepository {
11    pool: DbPool,
12}
13
14impl PostgresBuildingRepository {
15    pub fn new(pool: DbPool) -> Self {
16        Self { pool }
17    }
18}
19
20#[async_trait]
21impl BuildingRepository for PostgresBuildingRepository {
22    async fn create(&self, building: &Building) -> Result<Building, String> {
23        sqlx::query(
24            r#"
25            INSERT INTO buildings (id, acp_id, name, address, city, postal_code, country, total_units, total_tantiemes, construction_year, syndic_name, syndic_email, syndic_phone, syndic_address, syndic_office_hours, syndic_emergency_contact, slug, created_at, updated_at)
26            VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19)
27            "#,
28        )
29        .bind(building.id)
30        .bind(building.acp_id)
31        .bind(&building.name)
32        .bind(&building.address)
33        .bind(&building.city)
34        .bind(&building.postal_code)
35        .bind(&building.country)
36        .bind(building.total_units)
37        .bind(building.total_tantiemes)
38        .bind(building.construction_year)
39        .bind(&building.syndic_name)
40        .bind(&building.syndic_email)
41        .bind(&building.syndic_phone)
42        .bind(&building.syndic_address)
43        .bind(&building.syndic_office_hours)
44        .bind(&building.syndic_emergency_contact)
45        .bind(&building.slug)
46        .bind(building.created_at)
47        .bind(building.updated_at)
48        .execute(&self.pool)
49        .await
50        .map_err(|e| format!("Database error: {}", e))?;
51
52        Ok(building.clone())
53    }
54
55    async fn find_by_id(&self, id: Uuid) -> Result<Option<Building>, String> {
56        let row = sqlx::query(
57            r#"
58            SELECT id, acp_id, name, address, city, postal_code, country, total_units, total_tantiemes, construction_year, syndic_name, syndic_email, syndic_phone, syndic_address, syndic_office_hours, syndic_emergency_contact, slug, created_at, updated_at
59            FROM buildings
60            WHERE id = $1
61            "#,
62        )
63        .bind(id)
64        .fetch_optional(&self.pool)
65        .await
66        .map_err(|e| format!("Database error: {}", e))?;
67
68        Ok(row.map(|row| Building {
69            id: row.get("id"),
70            acp_id: row.get("acp_id"),
71            name: row.get("name"),
72            address: row.get("address"),
73            city: row.get("city"),
74            postal_code: row.get("postal_code"),
75            country: row.get("country"),
76            total_units: row.get("total_units"),
77            total_tantiemes: row.get("total_tantiemes"),
78            construction_year: row.get("construction_year"),
79            syndic_name: row.get("syndic_name"),
80            syndic_email: row.get("syndic_email"),
81            syndic_phone: row.get("syndic_phone"),
82            syndic_address: row.get("syndic_address"),
83            syndic_office_hours: row.get("syndic_office_hours"),
84            syndic_emergency_contact: row.get("syndic_emergency_contact"),
85            slug: row.get("slug"),
86            created_at: row.get("created_at"),
87            updated_at: row.get("updated_at"),
88        }))
89    }
90
91    async fn find_all(&self) -> Result<Vec<Building>, String> {
92        let rows = sqlx::query(
93            r#"
94            SELECT id, acp_id, name, address, city, postal_code, country, total_units, total_tantiemes, construction_year, syndic_name, syndic_email, syndic_phone, syndic_address, syndic_office_hours, syndic_emergency_contact, slug, created_at, updated_at
95            FROM buildings
96            ORDER BY created_at DESC
97            "#,
98        )
99        .fetch_all(&self.pool)
100        .await
101        .map_err(|e| format!("Database error: {}", e))?;
102
103        Ok(rows
104            .iter()
105            .map(|row| Building {
106                id: row.get("id"),
107                acp_id: row.get("acp_id"),
108                name: row.get("name"),
109                address: row.get("address"),
110                city: row.get("city"),
111                postal_code: row.get("postal_code"),
112                country: row.get("country"),
113                total_units: row.get("total_units"),
114                total_tantiemes: row.get("total_tantiemes"),
115                construction_year: row.get("construction_year"),
116                syndic_name: row.get("syndic_name"),
117                syndic_email: row.get("syndic_email"),
118                syndic_phone: row.get("syndic_phone"),
119                syndic_address: row.get("syndic_address"),
120                syndic_office_hours: row.get("syndic_office_hours"),
121                syndic_emergency_contact: row.get("syndic_emergency_contact"),
122                slug: row.get("slug"),
123                created_at: row.get("created_at"),
124                updated_at: row.get("updated_at"),
125            })
126            .collect())
127    }
128
129    async fn find_all_paginated(
130        &self,
131        page_request: &PageRequest,
132        filters: &BuildingFilters,
133    ) -> Result<(Vec<Building>, i64), String> {
134        // Validate page request
135        page_request.validate()?;
136
137        // Build WHERE clause dynamically
138        let mut where_clauses = Vec::new();
139        let mut param_count = 0;
140
141        // Story 1.2/1.3 — scope filter on `acp_id` direct or via parent
142        // organization (resolved by use_case `list_for_acp` / `list_for_scope`).
143        if filters.organization_id.is_some() {
144            param_count += 1;
145            // Filter by org via the parent ACP's organization_id (since
146            // buildings.organization_id was DROPped in migration 040000).
147            where_clauses.push(format!(
148                "acp_id IN (SELECT id FROM acps WHERE organization_id = ${})",
149                param_count
150            ));
151        }
152
153        if filters.acp_id.is_some() {
154            param_count += 1;
155            where_clauses.push(format!("acp_id = ${}", param_count));
156        }
157
158        if filters.city.is_some() {
159            param_count += 1;
160            where_clauses.push(format!("city ILIKE ${}", param_count));
161        }
162
163        if filters.construction_year.is_some() {
164            param_count += 1;
165            where_clauses.push(format!("construction_year = ${}", param_count));
166        }
167
168        if filters.min_units.is_some() {
169            param_count += 1;
170            where_clauses.push(format!("total_units >= ${}", param_count));
171        }
172
173        if filters.max_units.is_some() {
174            param_count += 1;
175            where_clauses.push(format!("total_units <= ${}", param_count));
176        }
177
178        // BUG-WF14-2: Filtrer par owner — ne montrer que les buildings où le user possède un lot
179        if filters.owner_user_id.is_some() {
180            param_count += 1;
181            where_clauses.push(format!(
182                "id IN (SELECT DISTINCT u.building_id FROM units u \
183                 INNER JOIN unit_owners uo ON uo.unit_id = u.id \
184                 INNER JOIN owners o ON o.id = uo.owner_id \
185                 WHERE o.user_id = ${} AND uo.end_date IS NULL)",
186                param_count
187            ));
188        }
189
190        if filters.search.is_some() {
191            param_count += 1;
192            where_clauses.push(format!(
193                "(name ILIKE ${p} OR city ILIKE ${p} OR address ILIKE ${p})",
194                p = param_count
195            ));
196        }
197
198        let where_clause = if where_clauses.is_empty() {
199            String::new()
200        } else {
201            format!("WHERE {}", where_clauses.join(" AND "))
202        };
203
204        // Validate sort column (whitelist)
205        let allowed_columns = [
206            "name",
207            "created_at",
208            "total_units",
209            "city",
210            "construction_year",
211        ];
212        let sort_column = page_request.sort_by.as_deref().unwrap_or("created_at");
213
214        if !allowed_columns.contains(&sort_column) {
215            return Err(format!("Invalid sort column: {}", sort_column));
216        }
217
218        // Count total items
219        let count_query = format!("SELECT COUNT(*) FROM buildings {}", where_clause);
220        let mut count_query = sqlx::query_scalar::<_, i64>(&count_query);
221
222        if let Some(org_id) = filters.organization_id {
223            count_query = count_query.bind(org_id);
224        }
225        if let Some(acp_id) = filters.acp_id {
226            count_query = count_query.bind(acp_id);
227        }
228        if let Some(city) = &filters.city {
229            count_query = count_query.bind(format!("%{}%", city));
230        }
231        if let Some(year) = filters.construction_year {
232            count_query = count_query.bind(year);
233        }
234        if let Some(min) = filters.min_units {
235            count_query = count_query.bind(min);
236        }
237        if let Some(max) = filters.max_units {
238            count_query = count_query.bind(max);
239        }
240        if let Some(owner_id) = filters.owner_user_id {
241            count_query = count_query.bind(owner_id);
242        }
243        if let Some(search) = &filters.search {
244            count_query = count_query.bind(format!("%{}%", search));
245        }
246
247        let total_items = count_query
248            .fetch_one(&self.pool)
249            .await
250            .map_err(|e| format!("Database error: {}", e))?;
251
252        // Fetch paginated data
253        param_count += 1;
254        let limit_param = param_count;
255        param_count += 1;
256        let offset_param = param_count;
257
258        let data_query = format!(
259            "SELECT id, acp_id, name, address, city, postal_code, country, total_units, total_tantiemes, construction_year, syndic_name, syndic_email, syndic_phone, syndic_address, syndic_office_hours, syndic_emergency_contact, slug, created_at, updated_at \
260             FROM buildings {} ORDER BY {} {} LIMIT ${} OFFSET ${}",
261            where_clause,
262            sort_column,
263            page_request.order.to_sql(),
264            limit_param,
265            offset_param
266        );
267
268        let mut data_query = sqlx::query(&data_query);
269
270        if let Some(org_id) = filters.organization_id {
271            data_query = data_query.bind(org_id);
272        }
273        if let Some(acp_id) = filters.acp_id {
274            data_query = data_query.bind(acp_id);
275        }
276        if let Some(city) = &filters.city {
277            data_query = data_query.bind(format!("%{}%", city));
278        }
279        if let Some(year) = filters.construction_year {
280            data_query = data_query.bind(year);
281        }
282        if let Some(min) = filters.min_units {
283            data_query = data_query.bind(min);
284        }
285        if let Some(max) = filters.max_units {
286            data_query = data_query.bind(max);
287        }
288        if let Some(owner_id) = filters.owner_user_id {
289            data_query = data_query.bind(owner_id);
290        }
291        if let Some(search) = &filters.search {
292            data_query = data_query.bind(format!("%{}%", search));
293        }
294
295        data_query = data_query
296            .bind(page_request.limit())
297            .bind(page_request.offset());
298
299        let rows = data_query
300            .fetch_all(&self.pool)
301            .await
302            .map_err(|e| format!("Database error: {}", e))?;
303
304        let buildings: Vec<Building> = rows
305            .iter()
306            .map(|row| Building {
307                id: row.get("id"),
308                acp_id: row.get("acp_id"),
309                name: row.get("name"),
310                address: row.get("address"),
311                city: row.get("city"),
312                postal_code: row.get("postal_code"),
313                country: row.get("country"),
314                total_units: row.get("total_units"),
315                total_tantiemes: row.get("total_tantiemes"),
316                construction_year: row.get("construction_year"),
317                syndic_name: row.get("syndic_name"),
318                syndic_email: row.get("syndic_email"),
319                syndic_phone: row.get("syndic_phone"),
320                syndic_address: row.get("syndic_address"),
321                syndic_office_hours: row.get("syndic_office_hours"),
322                syndic_emergency_contact: row.get("syndic_emergency_contact"),
323                slug: row.get("slug"),
324                created_at: row.get("created_at"),
325                updated_at: row.get("updated_at"),
326            })
327            .collect();
328
329        Ok((buildings, total_items))
330    }
331
332    async fn update(&self, building: &Building) -> Result<Building, String> {
333        sqlx::query(
334            r#"
335            UPDATE buildings
336            SET acp_id = $2, name = $3, address = $4, city = $5, postal_code = $6, country = $7, total_units = $8, total_tantiemes = $9, construction_year = $10, syndic_name = $11, syndic_email = $12, syndic_phone = $13, syndic_address = $14, syndic_office_hours = $15, syndic_emergency_contact = $16, slug = $17, updated_at = $18
337            WHERE id = $1
338            "#,
339        )
340        .bind(building.id)
341        .bind(building.acp_id)
342        .bind(&building.name)
343        .bind(&building.address)
344        .bind(&building.city)
345        .bind(&building.postal_code)
346        .bind(&building.country)
347        .bind(building.total_units)
348        .bind(building.total_tantiemes)
349        .bind(building.construction_year)
350        .bind(&building.syndic_name)
351        .bind(&building.syndic_email)
352        .bind(&building.syndic_phone)
353        .bind(&building.syndic_address)
354        .bind(&building.syndic_office_hours)
355        .bind(&building.syndic_emergency_contact)
356        .bind(&building.slug)
357        .bind(building.updated_at)
358        .execute(&self.pool)
359        .await
360        .map_err(|e| format!("Database error: {}", e))?;
361
362        Ok(building.clone())
363    }
364
365    async fn delete(&self, id: Uuid) -> Result<bool, String> {
366        let result = sqlx::query("DELETE FROM buildings WHERE id = $1")
367            .bind(id)
368            .execute(&self.pool)
369            .await
370            .map_err(|e| format!("Database error: {}", e))?;
371
372        Ok(result.rows_affected() > 0)
373    }
374
375    async fn find_by_slug(&self, slug: &str) -> Result<Option<Building>, String> {
376        let row = sqlx::query(
377            r#"
378            SELECT id, acp_id, name, address, city, postal_code, country, total_units, total_tantiemes, construction_year, syndic_name, syndic_email, syndic_phone, syndic_address, syndic_office_hours, syndic_emergency_contact, slug, created_at, updated_at
379            FROM buildings
380            WHERE slug = $1
381            "#,
382        )
383        .bind(slug)
384        .fetch_optional(&self.pool)
385        .await
386        .map_err(|e| format!("Database error: {}", e))?;
387
388        Ok(row.map(|row| Building {
389            id: row.get("id"),
390            acp_id: row.get("acp_id"),
391            name: row.get("name"),
392            address: row.get("address"),
393            city: row.get("city"),
394            postal_code: row.get("postal_code"),
395            country: row.get("country"),
396            total_units: row.get("total_units"),
397            total_tantiemes: row.get("total_tantiemes"),
398            construction_year: row.get("construction_year"),
399            syndic_name: row.get("syndic_name"),
400            syndic_email: row.get("syndic_email"),
401            syndic_phone: row.get("syndic_phone"),
402            syndic_address: row.get("syndic_address"),
403            syndic_office_hours: row.get("syndic_office_hours"),
404            syndic_emergency_contact: row.get("syndic_emergency_contact"),
405            slug: row.get("slug"),
406            created_at: row.get("created_at"),
407            updated_at: row.get("updated_at"),
408        }))
409    }
410
411    /// Story 1.4 — Building + metrics via LEFT JOIN units agrégé.
412    ///
413    /// `SUM(quota::NUMERIC)` cast en NUMERIC pour rester Decimal strict (cf.
414    /// ADR-0007/0008 + mémoire `no-f64-in-money`). Lorsqu'aucune unit
415    /// n'existe, `SUM` renvoie NULL côté SQL → `COALESCE(..., 0)` ramène à
416    /// `Decimal::ZERO` (jamais NaN, jamais panic).
417    ///
418    /// Cluster #433 : la colonne `units.quota` reste `DOUBLE PRECISION` côté
419    /// schéma (migration territoire 1.2), mais le `::NUMERIC` côté SELECT
420    /// produit un Decimal exact sur la somme — c'est la cible #433 sur la
421    /// frontière infra↔domain pour cette story.
422    async fn find_by_id_with_metrics(
423        &self,
424        id: Uuid,
425    ) -> Result<Option<(Building, BuildingMetrics)>, String> {
426        let row = sqlx::query(
427            r#"
428            SELECT
429                b.id, b.acp_id, b.name, b.address, b.city, b.postal_code, b.country,
430                b.total_units, b.total_tantiemes, b.construction_year,
431                b.syndic_name, b.syndic_email, b.syndic_phone, b.syndic_address,
432                b.syndic_office_hours, b.syndic_emergency_contact, b.slug,
433                b.created_at, b.updated_at,
434                COALESCE(COUNT(u.id)::INT, 0)                              AS units_count,
435                COALESCE(SUM(u.quota::NUMERIC), 0::NUMERIC)                AS quota_sum
436            FROM buildings b
437            LEFT JOIN units u ON u.building_id = b.id
438            WHERE b.id = $1
439            GROUP BY b.id
440            "#,
441        )
442        .bind(id)
443        .fetch_optional(&self.pool)
444        .await
445        .map_err(|e| format!("Database error: {}", e))?;
446
447        Ok(row.map(|row| {
448            let units_count: i32 = row.try_get("units_count").unwrap_or(0);
449            let quota_sum: Decimal = row.try_get("quota_sum").unwrap_or(Decimal::ZERO);
450            let building = Building {
451                id: row.get("id"),
452                acp_id: row.get("acp_id"),
453                name: row.get("name"),
454                address: row.get("address"),
455                city: row.get("city"),
456                postal_code: row.get("postal_code"),
457                country: row.get("country"),
458                total_units: row.get("total_units"),
459                total_tantiemes: row.get("total_tantiemes"),
460                construction_year: row.get("construction_year"),
461                syndic_name: row.get("syndic_name"),
462                syndic_email: row.get("syndic_email"),
463                syndic_phone: row.get("syndic_phone"),
464                syndic_address: row.get("syndic_address"),
465                syndic_office_hours: row.get("syndic_office_hours"),
466                syndic_emergency_contact: row.get("syndic_emergency_contact"),
467                slug: row.get("slug"),
468                created_at: row.get("created_at"),
469                updated_at: row.get("updated_at"),
470            };
471            let metrics = BuildingMetrics {
472                units_count,
473                quota_sum,
474            };
475            (building, metrics)
476        }))
477    }
478}
koprogo_api/infrastructure/database/repositories/building_repository_impl.rs

koprogo_api/infrastructure/database/repositories/
building_repository_impl.rs
