Coverage for dao/tournament_dao.py: 9.52%
236 statements
« prev ^ index » next coverage.py v7.10.6, created at 2026-04-15 12:24 +0000
1"""
2Tournament Data Access Object.
4Handles all database operations for tournaments, including:
5- Tournament CRUD
6- Tournament match creation (with auto-create of lightweight opponent teams)
7- Public read: tournament list and detail with match results
8"""
10import structlog
12from dao.base_dao import BaseDAO, dao_cache, invalidates_cache
14logger = structlog.get_logger()
16TOURNAMENTS_CACHE_PATTERN = "mt:dao:tournaments:*"
18# match_type_id=2 is "Tournament" (seed data)
19TOURNAMENT_MATCH_TYPE_ID = 2
21VALID_ROUNDS = {
22 "group_stage",
23 "round_of_16",
24 "quarterfinal",
25 "semifinal",
26 "final",
27 "third_place",
28 "wildcard",
29 "silver_semifinal",
30 "bronze_semifinal",
31 "silver_final",
32 "bronze_final",
33}
36class TournamentDAO(BaseDAO):
37 """Data access object for tournament operations."""
39 # =========================================================================
40 # Public read
41 # =========================================================================
43 def _attach_age_groups(self, tournaments: list[dict]) -> list[dict]:
44 """Fetch age groups from the junction table and attach to each tournament."""
45 if not tournaments:
46 return tournaments
47 ids = [t["id"] for t in tournaments]
48 try:
49 rows = (
50 self.client.table("tournament_age_groups")
51 .select("tournament_id, age_group:age_groups(id, name)")
52 .in_("tournament_id", ids)
53 .execute()
54 ).data or []
55 except Exception:
56 logger.exception("Error fetching tournament age groups")
57 rows = []
59 by_tid: dict[int, list] = {t["id"]: [] for t in tournaments}
60 for row in rows:
61 tid = row["tournament_id"]
62 if tid in by_tid:
63 by_tid[tid].append(row["age_group"])
65 for t in tournaments:
66 t["age_groups"] = by_tid[t["id"]]
67 return tournaments
69 def _attach_match_counts(self, tournaments: list[dict]) -> list[dict]:
70 """Fetch match counts from the matches table and attach to each tournament."""
71 if not tournaments:
72 return tournaments
73 ids = [t["id"] for t in tournaments]
74 try:
75 rows = (
76 self.client.table("matches")
77 .select("tournament_id")
78 .in_("tournament_id", ids)
79 .execute()
80 ).data or []
81 except Exception:
82 logger.exception("Error fetching tournament match counts")
83 rows = []
84 counts: dict[int, int] = {t["id"]: 0 for t in tournaments}
85 for row in rows:
86 tid = row.get("tournament_id")
87 if tid in counts:
88 counts[tid] += 1
89 for t in tournaments:
90 t["match_count"] = counts[t["id"]]
91 return tournaments
93 def _sync_age_groups(self, tournament_id: int, age_group_ids: list[int]) -> None:
94 """Replace all age group links for a tournament."""
95 self.client.table("tournament_age_groups").delete().eq("tournament_id", tournament_id).execute()
96 if age_group_ids:
97 rows = [{"tournament_id": tournament_id, "age_group_id": ag_id} for ag_id in age_group_ids]
98 self.client.table("tournament_age_groups").insert(rows).execute()
100 @dao_cache("tournaments:active")
101 def get_active_tournaments(self) -> list[dict]:
102 """Return all active tournaments ordered by start date descending."""
103 try:
104 response = (
105 self.client.table("tournaments")
106 .select("id, name, start_date, end_date, location, description, is_active")
107 .eq("is_active", True)
108 .order("start_date", desc=True)
109 .execute()
110 )
111 data = self._attach_age_groups(response.data or [])
112 return self._attach_match_counts(data)
113 except Exception:
114 logger.exception("Error fetching active tournaments")
115 return []
117 @dao_cache("tournaments:all")
118 def get_all_tournaments(self) -> list[dict]:
119 """Return all tournaments (admin use)."""
120 try:
121 response = (
122 self.client.table("tournaments")
123 .select("id, name, start_date, end_date, location, description, is_active")
124 .order("start_date", desc=True)
125 .execute()
126 )
127 data = self._attach_age_groups(response.data or [])
128 return self._attach_match_counts(data)
129 except Exception:
130 logger.exception("Error fetching all tournaments")
131 return []
133 @dao_cache("tournaments:by_id:{tournament_id}")
134 def get_tournament_by_id(self, tournament_id: int) -> dict | None:
135 """Return tournament with all matches for tracked teams.
137 Matches are enriched with home/away team names so the frontend
138 can display them without additional lookups.
139 """
140 try:
141 t_response = (
142 self.client.table("tournaments")
143 .select("id, name, start_date, end_date, location, description, is_active")
144 .eq("id", tournament_id)
145 .single()
146 .execute()
147 )
148 if not t_response.data:
149 return None
151 tournament = self._attach_age_groups([t_response.data])[0]
153 # Fetch matches linked to this tournament
154 m_response = (
155 self.client.table("matches")
156 .select("""
157 id,
158 match_date,
159 scheduled_kickoff,
160 match_status,
161 home_score,
162 away_score,
163 home_penalty_score,
164 away_penalty_score,
165 tournament_group,
166 tournament_round,
167 age_group:age_groups!matches_age_group_id_fkey(id, name),
168 home_team:teams!matches_home_team_id_fkey(id, name),
169 away_team:teams!matches_away_team_id_fkey(id, name)
170 """)
171 .eq("tournament_id", tournament_id)
172 .order("match_date", desc=False)
173 .execute()
174 )
176 tournament["matches"] = m_response.data or []
177 return tournament
179 except Exception:
180 logger.exception("Error fetching tournament by id", tournament_id=tournament_id)
181 return None
183 # =========================================================================
184 # Admin write
185 # =========================================================================
187 @invalidates_cache(TOURNAMENTS_CACHE_PATTERN)
188 def create_tournament(
189 self,
190 name: str,
191 start_date: str,
192 end_date: str | None = None,
193 location: str | None = None,
194 description: str | None = None,
195 age_group_ids: list[int] | None = None,
196 is_active: bool = True,
197 ) -> dict:
198 """Create a new tournament.
200 Args:
201 name: Tournament name (e.g. '2026 Generation adidas Cup')
202 start_date: ISO date string
203 end_date: ISO date string (optional)
204 location: Venue/city (optional)
205 description: Free text notes (optional)
206 age_group_ids: Age groups for the tournament (optional)
207 is_active: Controls public visibility (default True)
209 Returns:
210 Created tournament record with age_groups list
211 """
212 data = {
213 "name": name,
214 "start_date": start_date,
215 "is_active": is_active,
216 }
217 if end_date:
218 data["end_date"] = end_date
219 if location:
220 data["location"] = location
221 if description:
222 data["description"] = description
224 try:
225 response = self.client.table("tournaments").insert(data).execute()
226 tournament = response.data[0]
227 if age_group_ids:
228 self._sync_age_groups(tournament["id"], age_group_ids)
229 return self._attach_age_groups([tournament])[0]
230 except Exception:
231 logger.exception("Error creating tournament", name=name)
232 raise
234 @invalidates_cache(TOURNAMENTS_CACHE_PATTERN)
235 def update_tournament(
236 self,
237 tournament_id: int,
238 name: str | None = None,
239 start_date: str | None = None,
240 end_date: str | None = None,
241 location: str | None = None,
242 description: str | None = None,
243 age_group_ids: list[int] | None = None,
244 is_active: bool | None = None,
245 ) -> dict | None:
246 """Update tournament fields. Only provided (non-None) fields are changed.
248 age_group_ids replaces all existing age group links when provided.
249 """
250 updates: dict = {}
251 if name is not None:
252 updates["name"] = name
253 if start_date is not None:
254 updates["start_date"] = start_date
255 if end_date is not None:
256 updates["end_date"] = end_date
257 if location is not None:
258 updates["location"] = location
259 if description is not None:
260 updates["description"] = description
261 if is_active is not None:
262 updates["is_active"] = is_active
264 try:
265 if updates:
266 self.client.table("tournaments").update(updates).eq("id", tournament_id).execute()
267 if age_group_ids is not None:
268 self._sync_age_groups(tournament_id, age_group_ids)
269 return self.get_tournament_by_id(tournament_id)
270 except Exception:
271 logger.exception("Error updating tournament", tournament_id=tournament_id)
272 raise
274 @invalidates_cache(TOURNAMENTS_CACHE_PATTERN)
275 def delete_tournament(self, tournament_id: int) -> bool:
276 """Delete a tournament and cascade-nullify match links (via FK ON DELETE SET NULL)."""
277 try:
278 self.client.table("tournaments").delete().eq("id", tournament_id).execute()
279 return True
280 except Exception:
281 logger.exception("Error deleting tournament", tournament_id=tournament_id)
282 return False
284 # =========================================================================
285 # Tournament match management
286 # =========================================================================
288 @staticmethod
289 def _normalize_team_name(name: str) -> str:
290 """Normalize a team name for comparison (trim, collapse whitespace)."""
291 return " ".join(name.strip().split())
293 def lookup_teams_by_name(self, name: str) -> dict:
294 """Look up teams by name without creating anything.
296 Returns a dict with:
297 - exact: team dict if an exact (case-insensitive) match exists, else None
298 - similar: list of team dicts whose names contain any word from the query
299 """
300 normalized = self._normalize_team_name(name)
302 # Exact case-insensitive match
303 exact_response = (
304 self.client.table("teams")
305 .select("id, name, league_id, division_id, club_id")
306 .ilike("name", normalized)
307 .limit(1)
308 .execute()
309 )
310 exact = exact_response.data[0] if exact_response.data else None
312 # Similar: name contains any significant word (>= 4 chars) from query
313 similar: list[dict] = []
314 if not exact:
315 _skip = {"city", "club", "team", "boys", "girls", "academy", "united", "soccer", "football"}
316 words = [w for w in normalized.split() if len(w) >= 4 and w.lower() not in _skip]
317 seen_ids: set[int] = set()
318 for word in words:
319 rows = (
320 self.client.table("teams")
321 .select("id, name, league_id, division_id, club_id")
322 .ilike("name", f"%{word}%")
323 .limit(5)
324 .execute()
325 ).data or []
326 for row in rows:
327 if row["id"] not in seen_ids:
328 seen_ids.add(row["id"])
329 similar.append(row)
331 return {"exact": exact, "similar": similar}
333 def get_or_create_opponent_team(self, name: str, age_group_id: int) -> int:
334 """Find an existing team by name or create a lightweight tournament-only team.
336 Tournament opponents are created with no league, division, or club so
337 they don't pollute league standings or admin team lists.
339 Args:
340 name: Opponent team name (e.g. 'Cedar Stars Academy')
341 age_group_id: Age group for the team
343 Returns:
344 Team ID (existing or newly created)
345 """
346 normalized = self._normalize_team_name(name)
348 # Exact case-insensitive match
349 response = (
350 self.client.table("teams")
351 .select("id, name")
352 .ilike("name", normalized)
353 .limit(1)
354 .execute()
355 )
356 if response.data:
357 team_id = response.data[0]["id"]
358 logger.info("Found existing team for tournament opponent", name=normalized, team_id=team_id)
359 return team_id
361 # Create lightweight team: no league, division, or club
362 team_response = (
363 self.client.table("teams")
364 .insert({
365 "name": name,
366 "city": "",
367 "academy_team": False,
368 "club_id": None,
369 "league_id": None,
370 "division_id": None,
371 })
372 .execute()
373 )
374 if not team_response.data:
375 raise RuntimeError(f"Failed to create opponent team: {name}")
377 team_id = team_response.data[0]["id"]
379 # Add age group mapping so the team can appear in age-group-filtered queries
380 self.client.table("team_mappings").insert({
381 "team_id": team_id,
382 "age_group_id": age_group_id,
383 "division_id": None,
384 }).execute()
386 logger.info("Created tournament opponent team", name=name, team_id=team_id, age_group_id=age_group_id)
387 return team_id
389 @invalidates_cache(TOURNAMENTS_CACHE_PATTERN)
390 def create_tournament_match(
391 self,
392 tournament_id: int,
393 our_team_id: int,
394 opponent_name: str,
395 match_date: str,
396 age_group_id: int,
397 season_id: int,
398 is_home: bool = True,
399 home_score: int | None = None,
400 away_score: int | None = None,
401 home_penalty_score: int | None = None,
402 away_penalty_score: int | None = None,
403 match_status: str = "scheduled",
404 tournament_group: str | None = None,
405 tournament_round: str | None = None,
406 scheduled_kickoff: str | None = None,
407 ) -> dict:
408 """Create a match linked to a tournament.
410 The opponent team is resolved by name — created automatically if not
411 already in the database.
413 Args:
414 tournament_id: Tournament this match belongs to
415 our_team_id: ID of the tracked team (IFA, Cedar Stars, etc.)
416 opponent_name: Opponent's name as plain text
417 match_date: ISO date string (YYYY-MM-DD)
418 age_group_id: Age group for both teams
419 season_id: Season this match falls within
420 is_home: True if our_team_id is the home team
421 home_score / away_score: Scores (None = not yet played)
422 match_status: 'scheduled', 'completed', etc.
423 tournament_group: e.g. 'Group A'
424 tournament_round: e.g. 'group_stage', 'quarterfinal'
425 scheduled_kickoff: ISO datetime string (optional)
427 Returns:
428 Created match record dict
429 """
430 if tournament_round and tournament_round not in VALID_ROUNDS:
431 raise ValueError(f"Invalid tournament_round '{tournament_round}'. Must be one of {VALID_ROUNDS}")
433 opponent_id = self.get_or_create_opponent_team(opponent_name, age_group_id)
435 home_team_id = our_team_id if is_home else opponent_id
436 away_team_id = opponent_id if is_home else our_team_id
438 data: dict = {
439 "home_team_id": home_team_id,
440 "away_team_id": away_team_id,
441 "match_date": match_date,
442 "season_id": season_id,
443 "age_group_id": age_group_id,
444 "match_type_id": TOURNAMENT_MATCH_TYPE_ID,
445 "division_id": None,
446 "match_status": match_status,
447 "source": "manual",
448 "tournament_id": tournament_id,
449 }
451 if home_score is not None:
452 data["home_score"] = home_score
453 if away_score is not None:
454 data["away_score"] = away_score
455 if home_penalty_score is not None:
456 data["home_penalty_score"] = home_penalty_score
457 if away_penalty_score is not None:
458 data["away_penalty_score"] = away_penalty_score
459 if tournament_group:
460 data["tournament_group"] = tournament_group
461 if tournament_round:
462 data["tournament_round"] = tournament_round
463 if scheduled_kickoff:
464 data["scheduled_kickoff"] = scheduled_kickoff
466 try:
467 response = self.client.table("matches").insert(data).execute()
468 if not response.data:
469 raise RuntimeError("Match insert returned no data")
470 match = response.data[0]
471 logger.info(
472 "Created tournament match",
473 tournament_id=tournament_id,
474 match_id=match["id"],
475 home_team_id=home_team_id,
476 away_team_id=away_team_id,
477 )
478 return match
479 except Exception:
480 logger.exception("Error creating tournament match", tournament_id=tournament_id)
481 raise
483 @invalidates_cache(TOURNAMENTS_CACHE_PATTERN)
484 def update_tournament_match(
485 self,
486 match_id: int,
487 home_score: int | None = None,
488 away_score: int | None = None,
489 home_penalty_score: int | None = None,
490 away_penalty_score: int | None = None,
491 match_status: str | None = None,
492 tournament_group: str | None = None,
493 tournament_round: str | None = None,
494 scheduled_kickoff: str | None = None,
495 match_date: str | None = None,
496 swap_home_away: bool = False,
497 ) -> dict | None:
498 """Update score, status, or context fields on a tournament match."""
499 if tournament_round and tournament_round not in VALID_ROUNDS:
500 raise ValueError(f"Invalid tournament_round '{tournament_round}'. Must be one of {VALID_ROUNDS}")
502 updates: dict = {}
503 if home_score is not None:
504 updates["home_score"] = home_score
505 if away_score is not None:
506 updates["away_score"] = away_score
507 if home_penalty_score is not None:
508 updates["home_penalty_score"] = home_penalty_score
509 if away_penalty_score is not None:
510 updates["away_penalty_score"] = away_penalty_score
511 if match_status is not None:
512 updates["match_status"] = match_status
513 if tournament_group is not None:
514 updates["tournament_group"] = tournament_group
515 if tournament_round is not None:
516 updates["tournament_round"] = tournament_round
517 if scheduled_kickoff is not None:
518 updates["scheduled_kickoff"] = scheduled_kickoff
519 if match_date is not None:
520 updates["match_date"] = match_date
522 if swap_home_away:
523 # Fetch current team IDs to swap them
524 current = (
525 self.client.table("matches")
526 .select("home_team_id, away_team_id")
527 .eq("id", match_id)
528 .single()
529 .execute()
530 ).data
531 if current:
532 updates["home_team_id"] = current["away_team_id"]
533 updates["away_team_id"] = current["home_team_id"]
535 if not updates:
536 return None
538 try:
539 response = (
540 self.client.table("matches")
541 .update(updates)
542 .eq("id", match_id)
543 .execute()
544 )
545 return response.data[0] if response.data else None
546 except Exception:
547 logger.exception("Error updating tournament match", match_id=match_id)
548 raise
550 @invalidates_cache(TOURNAMENTS_CACHE_PATTERN)
551 def delete_tournament_match(self, match_id: int) -> bool:
552 """Remove a match from a tournament (deletes the match record entirely)."""
553 try:
554 self.client.table("matches").delete().eq("id", match_id).execute()
555 return True
556 except Exception:
557 logger.exception("Error deleting tournament match", match_id=match_id)
558 return False