Coverage for dao/team_dao.py: 11.05%
246 statements
« prev ^ index » next coverage.py v7.10.6, created at 2026-04-13 14:26 +0000
1"""
2Team Data Access Object.
4Handles all database operations related to teams including:
5- Team CRUD operations
6- Team-age group mappings
7- Team-match type participations
8- Team queries and filters
9- Team-club associations
10"""
12import structlog
14from dao.base_dao import BaseDAO, dao_cache, invalidates_cache
16logger = structlog.get_logger()
18# Cache pattern for invalidation
19TEAMS_CACHE_PATTERN = "mt:dao:teams:*"
22class TeamDAO(BaseDAO):
23 """Data access object for team operations."""
25 # === Team Query Methods ===
27 @dao_cache("teams:all")
28 def get_all_teams(self) -> list[dict]:
29 """Get all teams with their age groups."""
30 response = (
31 self.client.table("teams")
32 .select("""
33 *,
34 leagues!teams_league_id_fkey (
35 id,
36 name,
37 sport_type
38 ),
39 team_mappings (
40 age_groups (
41 id,
42 name
43 ),
44 divisions (
45 id,
46 name,
47 league_id,
48 leagues!divisions_league_id_fkey (
49 id,
50 name,
51 sport_type
52 )
53 )
54 )
55 """)
56 .order("name")
57 .execute()
58 )
60 # Flatten the age groups and divisions for each team
61 teams = []
62 for team in response.data:
63 # Extract league_name from the joined leagues table
64 if team.get("leagues"):
65 team["league_name"] = team["leagues"]["name"]
67 age_groups = []
68 divisions_by_age_group = {}
69 if "team_mappings" in team:
70 for tag in team["team_mappings"]:
71 if tag.get("age_groups"):
72 age_group = tag["age_groups"]
73 age_groups.append(age_group)
74 if tag.get("divisions"):
75 division = tag["divisions"]
76 # Add league_name and sport_type to division for easy access in frontend
77 if division.get("leagues"):
78 division["league_name"] = division["leagues"]["name"]
79 division["sport_type"] = division["leagues"].get("sport_type", "soccer")
80 divisions_by_age_group[age_group["id"]] = division
81 team["age_groups"] = age_groups
82 team["divisions_by_age_group"] = divisions_by_age_group
83 teams.append(team)
85 return teams
87 @dao_cache("teams:by_match_type:{match_type_id}:{age_group_id}:{division_id}")
88 def get_teams_by_match_type_and_age_group(
89 self, match_type_id: int, age_group_id: int, division_id: int | None = None
90 ) -> list[dict]:
91 """Get teams that can participate in a specific match type and age group.
93 Args:
94 match_type_id: Filter by match type (e.g., League, Cup)
95 age_group_id: Filter by age group (e.g., U14, U15)
96 division_id: Optional - Filter by division (e.g., Bracket A for Futsal)
98 Note: Due to PostgREST limitations with multiple inner joins, we query
99 junction tables directly and intersect results when filtering by division.
100 """
101 # Get team IDs that have the required match type
102 mt_response = (
103 self.client.table("team_match_types")
104 .select("team_id")
105 .eq("match_type_id", match_type_id)
106 .eq("age_group_id", age_group_id)
107 .eq("is_active", True)
108 .execute()
109 )
110 match_type_team_ids = {r["team_id"] for r in mt_response.data}
112 if not match_type_team_ids:
113 return []
115 # If division filter is specified, intersect with division teams
116 if division_id:
117 div_response = (
118 self.client.table("team_mappings")
119 .select("team_id")
120 .eq("age_group_id", age_group_id)
121 .eq("division_id", division_id)
122 .execute()
123 )
124 division_team_ids = {r["team_id"] for r in div_response.data}
125 final_team_ids = match_type_team_ids & division_team_ids
126 else:
127 final_team_ids = match_type_team_ids
129 if not final_team_ids:
130 return []
132 # Fetch full team data for the filtered IDs
133 response = (
134 self.client.table("teams")
135 .select("""
136 *,
137 team_mappings (
138 age_groups (
139 id,
140 name
141 ),
142 divisions (
143 id,
144 name
145 )
146 )
147 """)
148 .in_("id", list(final_team_ids))
149 .order("name")
150 .execute()
151 )
153 # Flatten the age groups and divisions for each team
154 teams = []
155 for team in response.data:
156 age_groups = []
157 divisions_by_age_group = {}
158 if "team_mappings" in team:
159 for tag in team["team_mappings"]:
160 if tag.get("age_groups"):
161 age_group = tag["age_groups"]
162 age_groups.append(age_group)
163 if tag.get("divisions"):
164 divisions_by_age_group[age_group["id"]] = tag["divisions"]
165 team["age_groups"] = age_groups
166 team["divisions_by_age_group"] = divisions_by_age_group
167 teams.append(team)
169 return teams
171 @dao_cache("teams:by_name:{name}")
172 def get_team_by_name(self, name: str) -> dict | None:
173 """Get a team by name (case-insensitive exact match).
175 Returns the first matching team with basic info (id, name, city).
176 For match-scraper integration, this helps look up teams by name.
177 """
178 response = (
179 self.client.table("teams").select("id, name, city, academy_team").ilike("name", name).limit(1).execute()
180 )
182 if response.data and len(response.data) > 0:
183 return response.data[0]
184 return None
186 @dao_cache("teams:by_id:{team_id}")
187 def get_team_by_id(self, team_id: int) -> dict | None:
188 """Get a team by ID.
190 Returns team info (id, name, city, club_id).
191 """
192 response = self.client.table("teams").select("id, name, city, club_id").eq("id", team_id).limit(1).execute()
194 if response.data and len(response.data) > 0:
195 return response.data[0]
196 return None
198 @dao_cache("teams:with_details:{team_id}")
199 def get_team_with_details(self, team_id: int) -> dict | None:
200 """Get a team with club, league, division, and age group details.
202 Returns enriched team info for the team roster page header.
203 """
204 response = (
205 self.client.table("teams")
206 .select("""
207 id, name, city, academy_team, league_id,
208 club:clubs(id, name, logo_url, primary_color, secondary_color),
209 division:divisions(id, name),
210 age_group:age_groups(id, name)
211 """)
212 .eq("id", team_id)
213 .limit(1)
214 .execute()
215 )
217 if not response.data or len(response.data) == 0:
218 return None
220 team = response.data[0]
221 result = {
222 "id": team.get("id"),
223 "name": team.get("name"),
224 "city": team.get("city"),
225 "academy_team": team.get("academy_team"),
226 "club": team.get("club"),
227 "league": None,
228 "division": team.get("division"),
229 "age_group": team.get("age_group"),
230 }
232 # Fetch league separately (no FK relationship)
233 league_id = team.get("league_id")
234 if league_id:
235 league_response = self.client.table("match_types").select("id, name").eq("id", league_id).limit(1).execute()
236 if league_response.data and len(league_response.data) > 0:
237 result["league"] = league_response.data[0]
239 return result
241 @dao_cache("teams:club_basic:{club_id}")
242 def get_club_teams_basic(self, club_id: int) -> list[dict]:
243 """Get teams for a club without match/player counts."""
244 response = (
245 self.client.table("teams_with_league_badges")
246 .select("id,name,club_id,league_id,league_name,mapping_league_names")
247 .eq("club_id", club_id)
248 .order("name")
249 .execute()
250 )
251 return [*response.data]
253 @dao_cache("teams:club:{club_id}")
254 def get_club_teams(self, club_id: int) -> list[dict]:
255 """Get all teams for a club across all leagues.
257 Args:
258 club_id: The club ID from the clubs table
260 Returns:
261 List of teams belonging to this club with team_mappings included,
262 plus match_count, player_count, age_group_name, and division_name
263 """
264 response = (
265 self.client.table("teams")
266 .select("""
267 *,
268 leagues!teams_league_id_fkey (
269 id,
270 name
271 ),
272 team_mappings (
273 age_groups (
274 id,
275 name
276 ),
277 divisions (
278 id,
279 name,
280 league_id,
281 leagues!divisions_league_id_fkey (
282 id,
283 name
284 )
285 )
286 )
287 """)
288 .eq("club_id", club_id)
289 .order("name")
290 .execute()
291 )
293 # Get team IDs for batch counting
294 team_ids = [team["id"] for team in response.data]
296 # Get match counts for all teams in one query
297 match_counts = {}
298 if team_ids:
299 home_matches = self.client.table("matches").select("home_team_id").in_("home_team_id", team_ids).execute()
300 away_matches = self.client.table("matches").select("away_team_id").in_("away_team_id", team_ids).execute()
301 for match in home_matches.data:
302 tid = match["home_team_id"]
303 match_counts[tid] = match_counts.get(tid, 0) + 1
304 for match in away_matches.data:
305 tid = match["away_team_id"]
306 match_counts[tid] = match_counts.get(tid, 0) + 1
308 # Get player counts for all teams in one query
309 player_counts = {}
310 if team_ids:
311 players = self.client.table("user_profiles").select("team_id").in_("team_id", team_ids).execute()
312 for player in players.data:
313 tid = player["team_id"]
314 player_counts[tid] = player_counts.get(tid, 0) + 1
316 # Process teams to add league_name and age_groups
317 teams = []
318 for team in response.data:
319 team_data = {**team}
320 if team.get("leagues"):
321 team_data["league_name"] = team["leagues"].get("name")
322 else:
323 team_data["league_name"] = None
325 age_groups = []
326 first_division_name = None
327 if team.get("team_mappings"):
328 seen_age_groups = set()
329 for mapping in team["team_mappings"]:
330 if mapping.get("age_groups"):
331 ag_id = mapping["age_groups"]["id"]
332 if ag_id not in seen_age_groups:
333 age_groups.append(mapping["age_groups"])
334 seen_age_groups.add(ag_id)
335 if first_division_name is None and mapping.get("divisions"):
336 first_division_name = mapping["divisions"].get("name")
337 team_data["age_groups"] = age_groups
338 team_data["age_group_name"] = age_groups[0]["name"] if age_groups else None
339 team_data["division_name"] = first_division_name
340 team_data["match_count"] = match_counts.get(team["id"], 0)
341 team_data["player_count"] = player_counts.get(team["id"], 0)
343 teams.append(team_data)
345 return teams
347 # === Team CRUD Methods ===
349 @invalidates_cache(TEAMS_CACHE_PATTERN)
350 def add_team(
351 self,
352 name: str,
353 city: str,
354 age_group_ids: list[int],
355 match_type_ids: list[int] | None = None,
356 division_id: int | None = None,
357 club_id: int | None = None,
358 academy_team: bool = False,
359 ) -> bool:
360 """Add a new team with age groups, division, and optional club.
362 Args:
363 name: Team name
364 city: Team city
365 age_group_ids: List of age group IDs (required, at least one)
366 match_type_ids: List of match type IDs (optional)
367 division_id: Division ID (optional, only required for league teams)
368 club_id: Optional club ID
369 academy_team: Whether this is an academy team
370 """
371 logger.info(
372 "Creating team",
373 team_name=name,
374 city=city,
375 age_group_count=len(age_group_ids),
376 match_type_count=len(match_type_ids) if match_type_ids else 0,
377 division_id=division_id,
378 club_id=club_id,
379 academy_team=academy_team,
380 )
382 # Validate required fields
383 if not age_group_ids or len(age_group_ids) == 0:
384 raise ValueError("Team must have at least one age group")
386 # Get league_id from division (if division provided)
387 league_id = None
388 if division_id is not None:
389 division_response = self.client.table("divisions").select("league_id").eq("id", division_id).execute()
390 if not division_response.data:
391 raise ValueError(f"Division {division_id} not found")
392 league_id = division_response.data[0]["league_id"]
394 # Insert team
395 team_data = {
396 "name": name,
397 "city": city,
398 "academy_team": academy_team,
399 "club_id": club_id,
400 "league_id": league_id,
401 "division_id": division_id,
402 }
403 team_response = self.client.table("teams").insert(team_data).execute()
405 if not team_response.data:
406 return False
408 team_id = team_response.data[0]["id"]
409 logger.info("Team record created", team_id=team_id, team_name=name)
411 # Add age group associations
412 for age_group_id in age_group_ids:
413 data = {
414 "team_id": team_id,
415 "age_group_id": age_group_id,
416 "division_id": division_id,
417 }
418 self.client.table("team_mappings").insert(data).execute()
420 # Add game type participations
421 if match_type_ids:
422 for match_type_id in match_type_ids:
423 for age_group_id in age_group_ids:
424 match_type_data = {
425 "team_id": team_id,
426 "match_type_id": match_type_id,
427 "age_group_id": age_group_id,
428 "is_active": True,
429 }
430 self.client.table("team_match_types").insert(match_type_data).execute()
432 logger.info(
433 "Team creation completed",
434 team_id=team_id,
435 team_name=name,
436 age_groups=len(age_group_ids),
437 match_types=len(match_type_ids) if match_type_ids else 0,
438 )
439 return True
441 @invalidates_cache(TEAMS_CACHE_PATTERN)
442 def update_team(
443 self,
444 team_id: int,
445 name: str,
446 city: str,
447 academy_team: bool = False,
448 club_id: int | None = None,
449 ) -> dict | None:
450 """Update a team."""
451 update_data = {
452 "name": name,
453 "city": city,
454 "academy_team": academy_team,
455 "club_id": club_id,
456 }
457 logger.debug("DAO update_team", team_id=team_id, update_data=update_data)
459 result = self.client.table("teams").update(update_data).eq("id", team_id).execute()
461 return result.data[0] if result.data else None
463 @invalidates_cache(TEAMS_CACHE_PATTERN)
464 def delete_team(self, team_id: int) -> bool:
465 """Delete a team and its related data.
467 Cascades deletion of:
468 - team_mappings (FK constraint)
469 - team_match_types (FK constraint)
470 - matches where team is home or away (FK constraint)
471 """
472 # Delete team_mappings first (FK constraint)
473 self.client.table("team_mappings").delete().eq("team_id", team_id).execute()
475 # Delete team_match_types (FK constraint)
476 self.client.table("team_match_types").delete().eq("team_id", team_id).execute()
478 # Delete matches where this team participates (FK constraint)
479 self.client.table("matches").delete().eq("home_team_id", team_id).execute()
480 self.client.table("matches").delete().eq("away_team_id", team_id).execute()
482 # Now delete the team
483 result = self.client.table("teams").delete().eq("id", team_id).execute()
484 return len(result.data) > 0
486 # === Team Mapping Methods ===
488 @invalidates_cache(TEAMS_CACHE_PATTERN)
489 def update_team_division(self, team_id: int, age_group_id: int, division_id: int) -> bool:
490 """Update the division for a team in a specific age group."""
491 response = (
492 self.client.table("team_mappings")
493 .update({"division_id": division_id})
494 .eq("team_id", team_id)
495 .eq("age_group_id", age_group_id)
496 .execute()
497 )
498 return bool(response.data)
500 @invalidates_cache(TEAMS_CACHE_PATTERN)
501 def create_team_mapping(self, team_id: int, age_group_id: int, division_id: int) -> dict:
502 """Create a team mapping, update team's league_id, and enable League match participation.
504 When assigning a team to a division (which belongs to a league), this method:
505 1. Updates the team's league_id to match the division's league
506 2. Creates the team_mapping entry
507 3. Auto-creates a team_match_types entry for League matches (match_type_id=1)
508 """
509 # Get the league_id from the division
510 division_response = self.client.table("divisions").select("league_id").eq("id", division_id).execute()
511 if division_response.data:
512 league_id = division_response.data[0]["league_id"]
513 self.client.table("teams").update(
514 {
515 "league_id": league_id,
516 "division_id": division_id,
517 }
518 ).eq("id", team_id).execute()
520 # Create the team mapping
521 result = (
522 self.client.table("team_mappings")
523 .insert(
524 {
525 "team_id": team_id,
526 "age_group_id": age_group_id,
527 "division_id": division_id,
528 }
529 )
530 .execute()
531 )
533 # Auto-create team_match_types entry for League matches
534 LEAGUE_MATCH_TYPE_ID = 1
535 existing = (
536 self.client.table("team_match_types")
537 .select("id")
538 .eq("team_id", team_id)
539 .eq("match_type_id", LEAGUE_MATCH_TYPE_ID)
540 .eq("age_group_id", age_group_id)
541 .execute()
542 )
543 if not existing.data:
544 self.client.table("team_match_types").insert(
545 {
546 "team_id": team_id,
547 "match_type_id": LEAGUE_MATCH_TYPE_ID,
548 "age_group_id": age_group_id,
549 "is_active": True,
550 }
551 ).execute()
552 logger.info(
553 "Auto-created team_match_types entry for League",
554 team_id=team_id,
555 age_group_id=age_group_id,
556 )
558 return result.data[0]
560 @invalidates_cache(TEAMS_CACHE_PATTERN)
561 def delete_team_mapping(self, team_id: int, age_group_id: int, division_id: int) -> bool:
562 """Delete a team mapping."""
563 result = (
564 self.client.table("team_mappings")
565 .delete()
566 .eq("team_id", team_id)
567 .eq("age_group_id", age_group_id)
568 .eq("division_id", division_id)
569 .execute()
570 )
571 return len(result.data) > 0
573 @invalidates_cache(TEAMS_CACHE_PATTERN)
574 def update_team_club(self, team_id: int, club_id: int | None) -> dict:
575 """Update the club for a team.
577 Args:
578 team_id: The team ID to update
579 club_id: The club ID to assign (or None to remove club association)
581 Returns:
582 Updated team dict
583 """
584 result = self.client.table("teams").update({"club_id": club_id}).eq("id", team_id).execute()
585 if not result.data or len(result.data) == 0:
586 raise ValueError(f"Failed to update club for team {team_id}")
587 return result.data[0]
589 # === Team Match Type Participation Methods ===
591 def add_team_match_type_participation(self, team_id: int, match_type_id: int, age_group_id: int) -> bool:
592 """Add a team's participation in a specific match type and age group."""
593 try:
594 self.client.table("team_match_types").insert(
595 {
596 "team_id": team_id,
597 "match_type_id": match_type_id,
598 "age_group_id": age_group_id,
599 "is_active": True,
600 }
601 ).execute()
602 return True
603 except Exception:
604 logger.exception("Error adding team match type participation")
605 return False
607 def remove_team_match_type_participation(self, team_id: int, match_type_id: int, age_group_id: int) -> bool:
608 """Remove a team's participation in a specific match type and age group."""
609 try:
610 self.client.table("team_match_types").update({"is_active": False}).eq("team_id", team_id).eq(
611 "match_type_id", match_type_id
612 ).eq("age_group_id", age_group_id).execute()
613 return True
614 except Exception:
615 logger.exception("Error removing team match type participation")
616 return False
618 # === Other Query Methods (not cached) ===
620 def get_teams_by_club_ids(self, club_ids: list[int]) -> list[dict]:
621 """Get teams for multiple clubs without match/player counts.
623 This is optimized for admin club listings that only need team details.
624 """
625 if not club_ids:
626 return []
628 response = (
629 self.client.table("teams_with_league_badges")
630 .select("id,name,club_id,league_id,league_name,mapping_league_names")
631 .in_("club_id", club_ids)
632 .order("name")
633 .execute()
634 )
636 return [{**team} for team in response.data]
638 def get_club_for_team(self, team_id: int) -> dict | None:
639 """Get the club for a team.
641 Args:
642 team_id: The team ID
644 Returns:
645 Club dict if team belongs to a club, None otherwise
646 """
647 team_response = self.client.table("teams").select("club_id").eq("id", team_id).execute()
648 if not team_response.data or len(team_response.data) == 0:
649 return None
651 club_id = team_response.data[0].get("club_id")
652 if not club_id:
653 return None
655 club_response = self.client.table("clubs").select("*").eq("id", club_id).execute()
656 if club_response.data and len(club_response.data) > 0:
657 return club_response.data[0]
658 return None
660 def get_team_game_counts(self) -> dict[int, int]:
661 """Get game counts for all teams in a single optimized query.
663 Returns a dictionary mapping team_id -> game_count.
664 """
665 try:
666 response = self.client.rpc("get_team_game_counts").execute()
668 if not response.data:
669 # Fallback to Python aggregation
670 matches = self.client.table("matches").select("home_team_id,away_team_id").execute()
671 counts = {}
672 for match in matches.data:
673 home_id = match["home_team_id"]
674 away_id = match["away_team_id"]
675 counts[home_id] = counts.get(home_id, 0) + 1
676 counts[away_id] = counts.get(away_id, 0) + 1
677 return counts
679 return {row["team_id"]: row["game_count"] for row in response.data}
680 except Exception:
681 logger.exception("Error getting team game counts")
682 return {}