1use crate::application::ports::SkillRepository;
2use crate::domain::entities::{ExpertiseLevel, Skill, SkillCategory};
3use crate::infrastructure::database::DbPool;
4use async_trait::async_trait;
5use sqlx::Row;
6use uuid::Uuid;
7
8pub struct PostgresSkillRepository {
9 pool: DbPool,
10}
11
12impl PostgresSkillRepository {
13 pub fn new(pool: DbPool) -> Self {
14 Self { pool }
15 }
16}
17
18fn map_row_to_skill(row: &sqlx::postgres::PgRow) -> Skill {
20 let category_str: String = row.get("skill_category");
21 let level_str: String = row.get("expertise_level");
22
23 Skill {
24 id: row.get("id"),
25 owner_id: row.get("owner_id"),
26 building_id: row.get("building_id"),
27 skill_category: serde_json::from_str(&format!("\"{}\"", category_str))
28 .unwrap_or(SkillCategory::Other),
29 skill_name: row.get("skill_name"),
30 expertise_level: serde_json::from_str(&format!("\"{}\"", level_str))
31 .unwrap_or(ExpertiseLevel::Beginner),
32 description: row.get("description"),
33 is_available_for_help: row.get("is_available_for_help"),
34 hourly_rate_credits: row.get("hourly_rate_credits"),
35 years_of_experience: row.get("years_of_experience"),
36 certifications: row.get("certifications"),
37 created_at: row.get("created_at"),
38 updated_at: row.get("updated_at"),
39 }
40}
41
42#[async_trait]
43impl SkillRepository for PostgresSkillRepository {
44 async fn create(&self, skill: &Skill) -> Result<Skill, String> {
45 let category_str = serde_json::to_string(&skill.skill_category)
46 .map_err(|e| format!("Failed to serialize skill_category: {}", e))?
47 .trim_matches('"')
48 .to_string();
49
50 let level_str = serde_json::to_string(&skill.expertise_level)
51 .map_err(|e| format!("Failed to serialize expertise_level: {}", e))?
52 .trim_matches('"')
53 .to_string();
54
55 sqlx::query(
56 r#"
57 INSERT INTO skills (
58 id, owner_id, building_id, skill_category, skill_name, expertise_level,
59 description, is_available_for_help, hourly_rate_credits,
60 years_of_experience, certifications, created_at, updated_at
61 )
62 VALUES ($1, $2, $3, $4::skill_category, $5, $6::expertise_level, $7, $8, $9, $10, $11, $12, $13)
63 "#,
64 )
65 .bind(skill.id)
66 .bind(skill.owner_id)
67 .bind(skill.building_id)
68 .bind(&category_str)
69 .bind(&skill.skill_name)
70 .bind(&level_str)
71 .bind(&skill.description)
72 .bind(skill.is_available_for_help)
73 .bind(skill.hourly_rate_credits)
74 .bind(skill.years_of_experience)
75 .bind(&skill.certifications)
76 .bind(skill.created_at)
77 .bind(skill.updated_at)
78 .execute(&self.pool)
79 .await
80 .map_err(|e| format!("Failed to create skill: {}", e))?;
81
82 Ok(skill.clone())
83 }
84
85 async fn find_by_id(&self, id: Uuid) -> Result<Option<Skill>, String> {
86 let row = sqlx::query(
87 r#"
88 SELECT id, owner_id, building_id, skill_category::text AS skill_category,
89 skill_name, expertise_level::text AS expertise_level, description,
90 is_available_for_help, hourly_rate_credits, years_of_experience,
91 certifications, created_at, updated_at
92 FROM skills
93 WHERE id = $1
94 "#,
95 )
96 .bind(id)
97 .fetch_optional(&self.pool)
98 .await
99 .map_err(|e| format!("Failed to find skill by ID: {}", e))?;
100
101 Ok(row.as_ref().map(map_row_to_skill))
102 }
103
104 async fn find_by_building(&self, building_id: Uuid) -> Result<Vec<Skill>, String> {
105 let rows = sqlx::query(
106 r#"
107 SELECT id, owner_id, building_id, skill_category::text AS skill_category,
108 skill_name, expertise_level::text AS expertise_level, description,
109 is_available_for_help, hourly_rate_credits, years_of_experience,
110 certifications, created_at, updated_at
111 FROM skills
112 WHERE building_id = $1
113 ORDER BY
114 is_available_for_help DESC,
115 expertise_level DESC,
116 skill_name ASC
117 "#,
118 )
119 .bind(building_id)
120 .fetch_all(&self.pool)
121 .await
122 .map_err(|e| format!("Failed to find skills by building: {}", e))?;
123
124 Ok(rows.iter().map(map_row_to_skill).collect())
125 }
126
127 async fn find_available_by_building(&self, building_id: Uuid) -> Result<Vec<Skill>, String> {
128 let rows = sqlx::query(
129 r#"
130 SELECT id, owner_id, building_id, skill_category::text AS skill_category,
131 skill_name, expertise_level::text AS expertise_level, description,
132 is_available_for_help, hourly_rate_credits, years_of_experience,
133 certifications, created_at, updated_at
134 FROM skills
135 WHERE building_id = $1 AND is_available_for_help = TRUE
136 ORDER BY
137 expertise_level DESC,
138 skill_name ASC
139 "#,
140 )
141 .bind(building_id)
142 .fetch_all(&self.pool)
143 .await
144 .map_err(|e| format!("Failed to find available skills by building: {}", e))?;
145
146 Ok(rows.iter().map(map_row_to_skill).collect())
147 }
148
149 async fn find_by_owner(&self, owner_id: Uuid) -> Result<Vec<Skill>, String> {
150 let rows = sqlx::query(
151 r#"
152 SELECT id, owner_id, building_id, skill_category::text AS skill_category,
153 skill_name, expertise_level::text AS expertise_level, description,
154 is_available_for_help, hourly_rate_credits, years_of_experience,
155 certifications, created_at, updated_at
156 FROM skills
157 WHERE owner_id = $1
158 ORDER BY created_at DESC
159 "#,
160 )
161 .bind(owner_id)
162 .fetch_all(&self.pool)
163 .await
164 .map_err(|e| format!("Failed to find skills by owner: {}", e))?;
165
166 Ok(rows.iter().map(map_row_to_skill).collect())
167 }
168
169 async fn find_by_category(
170 &self,
171 building_id: Uuid,
172 category: SkillCategory,
173 ) -> Result<Vec<Skill>, String> {
174 let category_str = serde_json::to_string(&category)
175 .map_err(|e| format!("Failed to serialize category: {}", e))?
176 .trim_matches('"')
177 .to_string();
178
179 let rows = sqlx::query(
180 r#"
181 SELECT id, owner_id, building_id, skill_category::text AS skill_category,
182 skill_name, expertise_level::text AS expertise_level, description,
183 is_available_for_help, hourly_rate_credits, years_of_experience,
184 certifications, created_at, updated_at
185 FROM skills
186 WHERE building_id = $1 AND skill_category = $2::skill_category
187 ORDER BY
188 is_available_for_help DESC,
189 expertise_level DESC,
190 skill_name ASC
191 "#,
192 )
193 .bind(building_id)
194 .bind(&category_str)
195 .fetch_all(&self.pool)
196 .await
197 .map_err(|e| format!("Failed to find skills by category: {}", e))?;
198
199 Ok(rows.iter().map(map_row_to_skill).collect())
200 }
201
202 async fn find_by_expertise(
203 &self,
204 building_id: Uuid,
205 level: ExpertiseLevel,
206 ) -> Result<Vec<Skill>, String> {
207 let level_str = serde_json::to_string(&level)
208 .map_err(|e| format!("Failed to serialize expertise_level: {}", e))?
209 .trim_matches('"')
210 .to_string();
211
212 let rows = sqlx::query(
213 r#"
214 SELECT id, owner_id, building_id, skill_category::text AS skill_category,
215 skill_name, expertise_level::text AS expertise_level, description,
216 is_available_for_help, hourly_rate_credits, years_of_experience,
217 certifications, created_at, updated_at
218 FROM skills
219 WHERE building_id = $1 AND expertise_level = $2::expertise_level
220 ORDER BY
221 is_available_for_help DESC,
222 skill_name ASC
223 "#,
224 )
225 .bind(building_id)
226 .bind(&level_str)
227 .fetch_all(&self.pool)
228 .await
229 .map_err(|e| format!("Failed to find skills by expertise: {}", e))?;
230
231 Ok(rows.iter().map(map_row_to_skill).collect())
232 }
233
234 async fn find_free_by_building(&self, building_id: Uuid) -> Result<Vec<Skill>, String> {
235 let rows = sqlx::query(
236 r#"
237 SELECT id, owner_id, building_id, skill_category::text AS skill_category,
238 skill_name, expertise_level::text AS expertise_level, description,
239 is_available_for_help, hourly_rate_credits, years_of_experience,
240 certifications, created_at, updated_at
241 FROM skills
242 WHERE building_id = $1 AND (hourly_rate_credits IS NULL OR hourly_rate_credits = 0)
243 ORDER BY
244 is_available_for_help DESC,
245 expertise_level DESC,
246 skill_name ASC
247 "#,
248 )
249 .bind(building_id)
250 .fetch_all(&self.pool)
251 .await
252 .map_err(|e| format!("Failed to find free skills by building: {}", e))?;
253
254 Ok(rows.iter().map(map_row_to_skill).collect())
255 }
256
257 async fn find_professional_by_building(&self, building_id: Uuid) -> Result<Vec<Skill>, String> {
258 let rows = sqlx::query(
259 r#"
260 SELECT id, owner_id, building_id, skill_category::text AS skill_category,
261 skill_name, expertise_level::text AS expertise_level, description,
262 is_available_for_help, hourly_rate_credits, years_of_experience,
263 certifications, created_at, updated_at
264 FROM skills
265 WHERE building_id = $1
266 AND (expertise_level = 'Expert' OR certifications IS NOT NULL)
267 ORDER BY
268 is_available_for_help DESC,
269 skill_name ASC
270 "#,
271 )
272 .bind(building_id)
273 .fetch_all(&self.pool)
274 .await
275 .map_err(|e| format!("Failed to find professional skills by building: {}", e))?;
276
277 Ok(rows.iter().map(map_row_to_skill).collect())
278 }
279
280 async fn update(&self, skill: &Skill) -> Result<Skill, String> {
281 let category_str = serde_json::to_string(&skill.skill_category)
282 .map_err(|e| format!("Failed to serialize skill_category: {}", e))?
283 .trim_matches('"')
284 .to_string();
285
286 let level_str = serde_json::to_string(&skill.expertise_level)
287 .map_err(|e| format!("Failed to serialize expertise_level: {}", e))?
288 .trim_matches('"')
289 .to_string();
290
291 sqlx::query(
292 r#"
293 UPDATE skills
294 SET skill_category = $2::skill_category,
295 skill_name = $3,
296 expertise_level = $4::expertise_level,
297 description = $5,
298 is_available_for_help = $6,
299 hourly_rate_credits = $7,
300 years_of_experience = $8,
301 certifications = $9,
302 updated_at = $10
303 WHERE id = $1
304 "#,
305 )
306 .bind(skill.id)
307 .bind(&category_str)
308 .bind(&skill.skill_name)
309 .bind(&level_str)
310 .bind(&skill.description)
311 .bind(skill.is_available_for_help)
312 .bind(skill.hourly_rate_credits)
313 .bind(skill.years_of_experience)
314 .bind(&skill.certifications)
315 .bind(skill.updated_at)
316 .execute(&self.pool)
317 .await
318 .map_err(|e| format!("Failed to update skill: {}", e))?;
319
320 Ok(skill.clone())
321 }
322
323 async fn delete(&self, id: Uuid) -> Result<(), String> {
324 sqlx::query(
325 r#"
326 DELETE FROM skills WHERE id = $1
327 "#,
328 )
329 .bind(id)
330 .execute(&self.pool)
331 .await
332 .map_err(|e| format!("Failed to delete skill: {}", e))?;
333
334 Ok(())
335 }
336
337 async fn count_by_building(&self, building_id: Uuid) -> Result<i64, String> {
338 let row = sqlx::query(
339 r#"
340 SELECT COUNT(*) as count FROM skills WHERE building_id = $1
341 "#,
342 )
343 .bind(building_id)
344 .fetch_one(&self.pool)
345 .await
346 .map_err(|e| format!("Failed to count skills by building: {}", e))?;
347
348 Ok(row.get("count"))
349 }
350
351 async fn count_available_by_building(&self, building_id: Uuid) -> Result<i64, String> {
352 let row = sqlx::query(
353 r#"
354 SELECT COUNT(*) as count
355 FROM skills
356 WHERE building_id = $1 AND is_available_for_help = TRUE
357 "#,
358 )
359 .bind(building_id)
360 .fetch_one(&self.pool)
361 .await
362 .map_err(|e| format!("Failed to count available skills by building: {}", e))?;
363
364 Ok(row.get("count"))
365 }
366
367 async fn count_by_category(
368 &self,
369 building_id: Uuid,
370 category: SkillCategory,
371 ) -> Result<i64, String> {
372 let category_str = serde_json::to_string(&category)
373 .map_err(|e| format!("Failed to serialize category: {}", e))?
374 .trim_matches('"')
375 .to_string();
376
377 let row = sqlx::query(
378 r#"
379 SELECT COUNT(*) as count
380 FROM skills
381 WHERE building_id = $1 AND skill_category = $2::skill_category
382 "#,
383 )
384 .bind(building_id)
385 .bind(&category_str)
386 .fetch_one(&self.pool)
387 .await
388 .map_err(|e| format!("Failed to count skills by category: {}", e))?;
389
390 Ok(row.get("count"))
391 }
392
393 async fn count_by_expertise(
394 &self,
395 building_id: Uuid,
396 level: ExpertiseLevel,
397 ) -> Result<i64, String> {
398 let level_str = serde_json::to_string(&level)
399 .map_err(|e| format!("Failed to serialize expertise_level: {}", e))?
400 .trim_matches('"')
401 .to_string();
402
403 let row = sqlx::query(
404 r#"
405 SELECT COUNT(*) as count
406 FROM skills
407 WHERE building_id = $1 AND expertise_level = $2::expertise_level
408 "#,
409 )
410 .bind(building_id)
411 .bind(&level_str)
412 .fetch_one(&self.pool)
413 .await
414 .map_err(|e| format!("Failed to count skills by expertise: {}", e))?;
415
416 Ok(row.get("count"))
417 }
418}