Coverage for dao/player_stats_dao.py: 10.37%
214 statements
« prev ^ index » next coverage.py v7.10.6, created at 2026-04-15 13:02 +0000
1"""
2Player Stats Data Access Object.
4Handles all database operations for player statistics:
5- Per-match stats (player_match_stats table)
6- Season aggregations
7- Goal increment/decrement for live game
8"""
10import structlog
12from dao.base_dao import BaseDAO, dao_cache, invalidates_cache
14logger = structlog.get_logger()
16# Cache patterns for invalidation
17STATS_CACHE_PATTERN = "mt:dao:stats:*"
20class PlayerStatsDAO(BaseDAO):
21 """Data access object for player statistics operations."""
23 # === Read Operations ===
25 def get_match_stats(self, player_id: int, match_id: int) -> dict | None:
26 """
27 Get stats for a player in a specific match.
29 Args:
30 player_id: Player ID
31 match_id: Match ID
33 Returns:
34 Stats dict or None if not found
35 """
36 try:
37 response = (
38 self.client.table("player_match_stats")
39 .select("*")
40 .eq("player_id", player_id)
41 .eq("match_id", match_id)
42 .execute()
43 )
45 if response.data and len(response.data) > 0:
46 return response.data[0]
47 return None
49 except Exception as e:
50 logger.error("stats_get_match_error", player_id=player_id, match_id=match_id, error=str(e))
51 return None
53 @invalidates_cache(STATS_CACHE_PATTERN)
54 def get_or_create_match_stats(self, player_id: int, match_id: int) -> dict | None:
55 """
56 Get or create stats record for a player in a match.
58 Used when recording stats - ensures a record exists.
60 Args:
61 player_id: Player ID
62 match_id: Match ID
64 Returns:
65 Stats dict (existing or newly created)
66 """
67 try:
68 # Try to get existing
69 existing = self.get_match_stats(player_id, match_id)
70 if existing:
71 return existing
73 # Create new record with defaults
74 response = (
75 self.client.table("player_match_stats")
76 .insert(
77 {
78 "player_id": player_id,
79 "match_id": match_id,
80 "started": False,
81 "played": False,
82 "minutes_played": 0,
83 "goals": 0,
84 }
85 )
86 .execute()
87 )
89 if response.data and len(response.data) > 0:
90 logger.info("stats_record_created", player_id=player_id, match_id=match_id)
91 return response.data[0]
92 return None
94 except Exception as e:
95 logger.error("stats_get_or_create_error", player_id=player_id, match_id=match_id, error=str(e))
96 return None
98 @dao_cache("stats:player:{player_id}:season:{season_id}")
99 def get_player_season_stats(self, player_id: int, season_id: int) -> dict | None:
100 """
101 Get aggregated stats for a player across a season.
103 Args:
104 player_id: Player ID
105 season_id: Season ID
107 Returns:
108 Aggregated stats dict with games_played, games_started,
109 total_minutes, total_goals
110 """
111 try:
112 # Get all match stats for this player where match is in the season
113 # Need to join with matches to filter by season
114 response = (
115 self.client.table("player_match_stats")
116 .select("""
117 *,
118 match:matches!inner(id, season_id)
119 """)
120 .eq("player_id", player_id)
121 .eq("match.season_id", season_id)
122 .execute()
123 )
125 stats = response.data or []
127 # Aggregate - only count games where player actually participated
128 games_played = sum(1 for s in stats if s.get("played") or s.get("started"))
129 games_started = sum(1 for s in stats if s.get("started"))
130 total_minutes = sum(s.get("minutes_played", 0) for s in stats)
131 total_goals = sum(s.get("goals", 0) for s in stats)
133 return {
134 "player_id": player_id,
135 "season_id": season_id,
136 "games_played": games_played,
137 "games_started": games_started,
138 "total_minutes": total_minutes,
139 "total_goals": total_goals,
140 }
142 except Exception as e:
143 logger.error("stats_season_error", player_id=player_id, season_id=season_id, error=str(e))
144 return None
146 @dao_cache("stats:team:{team_id}:season:{season_id}")
147 def get_team_stats(self, team_id: int, season_id: int) -> list[dict]:
148 """
149 Get aggregated stats for all players on a team for a season.
151 Args:
152 team_id: Team ID
153 season_id: Season ID
155 Returns:
156 List of player stats dicts sorted by goals (descending)
157 """
158 try:
159 # First get all players on this team/season
160 players_response = (
161 self.client.table("players")
162 .select("id, jersey_number, first_name, last_name")
163 .eq("team_id", team_id)
164 .eq("season_id", season_id)
165 .eq("is_active", True)
166 .execute()
167 )
169 players = players_response.data or []
171 result = []
172 for player in players:
173 # Get season stats for each player
174 stats = self.get_player_season_stats(player["id"], season_id)
175 if stats:
176 result.append(
177 {
178 "player_id": player["id"],
179 "jersey_number": player["jersey_number"],
180 "first_name": player.get("first_name"),
181 "last_name": player.get("last_name"),
182 "games_played": stats["games_played"],
183 "games_started": stats["games_started"],
184 "total_minutes": stats["total_minutes"],
185 "total_goals": stats["total_goals"],
186 }
187 )
189 # Sort by goals descending
190 result.sort(key=lambda x: x["total_goals"], reverse=True)
191 return result
193 except Exception as e:
194 logger.error("stats_team_error", team_id=team_id, season_id=season_id, error=str(e))
195 return []
197 @dao_cache("stats:leaderboard:goals:s{season_id}:l{league_id}:d{division_id}:a{age_group_id}:mt{match_type_id}:lim{limit}")
198 def get_goals_leaderboard(
199 self,
200 season_id: int,
201 league_id: int | None = None,
202 division_id: int | None = None,
203 age_group_id: int | None = None,
204 match_type_id: int | None = None,
205 limit: int = 50,
206 ) -> list[dict]:
207 """
208 Get top goal scorers filtered by league/division/age group/match type.
210 Args:
211 season_id: Season ID (required)
212 league_id: Optional league filter
213 division_id: Optional division filter
214 age_group_id: Optional age group filter
215 match_type_id: Optional match type filter (e.g. 4 for Playoff)
216 limit: Maximum results (default 50)
218 Returns:
219 List of player dicts with goals stats, ranked by goals descending
220 """
221 try:
222 # Build the query using Supabase RPC or raw SQL
223 # Since Supabase Python SDK doesn't support complex aggregations well,
224 # we'll fetch the data and aggregate in Python
226 # Get all player match stats for matches in this season
227 query = (
228 self.client.table("player_match_stats")
229 .select("""
230 player_id,
231 goals,
232 started,
233 played,
234 match:matches!inner(
235 id,
236 season_id,
237 match_status,
238 match_type_id,
239 division_id,
240 age_group_id,
241 division:divisions(
242 id,
243 league_id
244 )
245 ),
246 player:players!inner(
247 id,
248 jersey_number,
249 first_name,
250 last_name,
251 team_id,
252 team:teams(
253 id,
254 name
255 )
256 )
257 """)
258 .eq("match.season_id", season_id)
259 )
261 # Apply optional filters
262 if division_id is not None:
263 query = query.eq("match.division_id", division_id)
264 if age_group_id is not None:
265 query = query.eq("match.age_group_id", age_group_id)
267 response = query.execute()
268 stats = response.data or []
270 # Filter in Python since PostgREST nested filters on !inner joins are unreliable
271 # Include completed and forfeit matches (forfeit matches can have real goals)
272 stats = [
273 s for s in stats
274 if s.get("match", {}).get("match_status") in ("completed", "forfeit")
275 ]
276 if match_type_id is not None:
277 stats = [
278 s for s in stats
279 if s.get("match", {}).get("match_type_id") == match_type_id
280 ]
281 if league_id is not None:
282 # For matches with a division, check division.league_id directly.
283 # For playoff matches (division_id is null), check if the player's
284 # team plays in this league by looking at their divisional matches.
285 league_team_ids = self._get_league_team_ids(league_id, season_id)
286 stats = [
287 s for s in stats
288 if (s.get("match", {}).get("division") or {}).get("league_id") == league_id
289 or (
290 s.get("match", {}).get("division") is None
291 and s.get("player", {}).get("team_id") in league_team_ids
292 )
293 ]
295 # Aggregate by player
296 player_goals: dict[int, dict] = {}
297 for stat in stats:
298 player = stat.get("player")
299 if not player:
300 continue
302 player_id = player["id"]
303 goals = stat.get("goals", 0)
305 if player_id not in player_goals:
306 team = player.get("team", {}) or {}
307 player_goals[player_id] = {
308 "player_id": player_id,
309 "jersey_number": player.get("jersey_number"),
310 "first_name": player.get("first_name"),
311 "last_name": player.get("last_name"),
312 "team_id": player.get("team_id"),
313 "team_name": team.get("name"),
314 "goals": 0,
315 "games_played": 0,
316 }
318 player_goals[player_id]["goals"] += goals
319 if stat.get("played") or stat.get("started"):
320 player_goals[player_id]["games_played"] += 1
322 # Filter out players with 0 goals and sort
323 result = [p for p in player_goals.values() if p["goals"] > 0]
324 result.sort(key=lambda x: (-x["goals"], x["games_played"]))
326 # Calculate goals per game and add rank
327 for i, player in enumerate(result[:limit], start=1):
328 player["rank"] = i
329 games = player["games_played"]
330 player["goals_per_game"] = round(player["goals"] / games, 2) if games > 0 else 0.0
332 return result[:limit]
334 except Exception:
335 logger.exception(
336 "stats_goals_leaderboard_error",
337 season_id=season_id,
338 league_id=league_id,
339 division_id=division_id,
340 age_group_id=age_group_id,
341 match_type_id=match_type_id,
342 )
343 raise
345 def _get_league_team_ids(self, league_id: int, season_id: int) -> set[int]:
346 """Get team IDs that participate in a league via their divisional matches.
348 Used to attribute playoff goals (which have no division) to the correct league.
349 """
350 try:
351 # Get divisions belonging to this league
352 div_response = (
353 self.client.table("divisions")
354 .select("id")
355 .eq("league_id", league_id)
356 .execute()
357 )
358 division_ids = [d["id"] for d in (div_response.data or [])]
359 if not division_ids:
360 return set()
362 # Get teams that have matches in those divisions for this season
363 matches_response = (
364 self.client.table("matches")
365 .select("home_team_id, away_team_id")
366 .eq("season_id", season_id)
367 .in_("division_id", division_ids)
368 .execute()
369 )
370 team_ids = set()
371 for m in matches_response.data or []:
372 team_ids.add(m["home_team_id"])
373 team_ids.add(m["away_team_id"])
374 return team_ids
376 except Exception:
377 logger.exception("stats_get_league_team_ids_error", league_id=league_id)
378 return set()
380 def get_team_match_stats(self, match_id: int, team_id: int) -> list[dict]:
381 """
382 Get player stats for a specific team in a match, joined with player info.
384 Args:
385 match_id: Match ID
386 team_id: Team ID
388 Returns:
389 List of player stats dicts with player details
390 """
391 try:
392 # Get all players on this team for the match's season
393 # First get the match to find the season
394 match_response = (
395 self.client.table("matches")
396 .select("season_id")
397 .eq("id", match_id)
398 .single()
399 .execute()
400 )
401 if not match_response.data:
402 return []
404 season_id = match_response.data["season_id"]
406 # Get all players on the team for this season
407 players_response = (
408 self.client.table("players")
409 .select("id, jersey_number, first_name, last_name")
410 .eq("team_id", team_id)
411 .eq("season_id", season_id)
412 .eq("is_active", True)
413 .order("jersey_number")
414 .execute()
415 )
417 players = players_response.data or []
419 # Get existing stats for these players in this match
420 player_ids = [p["id"] for p in players]
421 if not player_ids:
422 return []
424 stats_response = (
425 self.client.table("player_match_stats")
426 .select("*")
427 .eq("match_id", match_id)
428 .in_("player_id", player_ids)
429 .execute()
430 )
432 stats_by_player = {s["player_id"]: s for s in (stats_response.data or [])}
434 # Merge player info with stats
435 result = []
436 for player in players:
437 stats = stats_by_player.get(player["id"], {})
438 result.append({
439 "player_id": player["id"],
440 "jersey_number": player["jersey_number"],
441 "first_name": player.get("first_name"),
442 "last_name": player.get("last_name"),
443 "started": stats.get("started", False),
444 "played": stats.get("played", False),
445 "minutes_played": stats.get("minutes_played", 0),
446 "goals": stats.get("goals", 0),
447 "yellow_cards": stats.get("yellow_cards", 0),
448 "red_cards": stats.get("red_cards", 0),
449 })
451 return result
453 except Exception as e:
454 logger.error(
455 "stats_team_match_error",
456 match_id=match_id,
457 team_id=team_id,
458 error=str(e),
459 )
460 return []
462 @invalidates_cache(STATS_CACHE_PATTERN)
463 def batch_update_stats(self, match_id: int, player_stats: list[dict]) -> bool:
464 """
465 Batch upsert started/minutes_played for multiple players in a match.
467 Args:
468 match_id: Match ID
469 player_stats: List of dicts with player_id, started, minutes_played
471 Returns:
472 True if successful
473 """
474 try:
475 for entry in player_stats:
476 player_id = entry["player_id"]
477 # Ensure record exists
478 self.get_or_create_match_stats(player_id, match_id)
480 # Update started, played, minutes, and cards
481 played = entry.get("played", False) or entry["started"]
482 self.client.table("player_match_stats").update({
483 "started": entry["started"],
484 "played": played,
485 "minutes_played": entry["minutes_played"],
486 "yellow_cards": entry.get("yellow_cards", 0),
487 "red_cards": entry.get("red_cards", 0),
488 }).eq("player_id", player_id).eq("match_id", match_id).execute()
490 logger.info(
491 "stats_batch_updated",
492 match_id=match_id,
493 player_count=len(player_stats),
494 )
495 return True
497 except Exception as e:
498 logger.error(
499 "stats_batch_update_error",
500 match_id=match_id,
501 error=str(e),
502 )
503 return False
505 # === Update Operations ===
507 @invalidates_cache(STATS_CACHE_PATTERN)
508 def increment_goals(self, player_id: int, match_id: int) -> dict | None:
509 """
510 Increment goal count for a player in a match.
512 Creates stats record if it doesn't exist.
514 Args:
515 player_id: Player ID
516 match_id: Match ID
518 Returns:
519 Updated stats dict
520 """
521 try:
522 # Ensure record exists
523 stats = self.get_or_create_match_stats(player_id, match_id)
524 if not stats:
525 return None
527 current_goals = stats.get("goals", 0)
529 response = (
530 self.client.table("player_match_stats")
531 .update({"goals": current_goals + 1, "played": True})
532 .eq("player_id", player_id)
533 .eq("match_id", match_id)
534 .execute()
535 )
537 if response.data and len(response.data) > 0:
538 logger.info(
539 "stats_goal_incremented",
540 player_id=player_id,
541 match_id=match_id,
542 new_total=current_goals + 1,
543 )
544 return response.data[0]
545 return None
547 except Exception as e:
548 logger.error("stats_increment_goals_error", player_id=player_id, match_id=match_id, error=str(e))
549 return None
551 @invalidates_cache(STATS_CACHE_PATTERN)
552 def decrement_goals(self, player_id: int, match_id: int) -> dict | None:
553 """
554 Decrement goal count for a player in a match.
556 Won't go below 0.
558 Args:
559 player_id: Player ID
560 match_id: Match ID
562 Returns:
563 Updated stats dict
564 """
565 try:
566 stats = self.get_match_stats(player_id, match_id)
567 if not stats:
568 return None
570 current_goals = stats.get("goals", 0)
571 new_goals = max(0, current_goals - 1)
573 response = (
574 self.client.table("player_match_stats")
575 .update({"goals": new_goals})
576 .eq("player_id", player_id)
577 .eq("match_id", match_id)
578 .execute()
579 )
581 if response.data and len(response.data) > 0:
582 logger.info(
583 "stats_goal_decremented",
584 player_id=player_id,
585 match_id=match_id,
586 new_total=new_goals,
587 )
588 return response.data[0]
589 return None
591 except Exception as e:
592 logger.error("stats_decrement_goals_error", player_id=player_id, match_id=match_id, error=str(e))
593 return None
595 @invalidates_cache(STATS_CACHE_PATTERN)
596 def set_started(self, player_id: int, match_id: int, started: bool) -> dict | None:
597 """
598 Set whether a player started a match.
600 Args:
601 player_id: Player ID
602 match_id: Match ID
603 started: True if player started
605 Returns:
606 Updated stats dict
607 """
608 try:
609 # Ensure record exists
610 self.get_or_create_match_stats(player_id, match_id)
612 update_data = {"started": started}
613 if started:
614 update_data["played"] = True
616 response = (
617 self.client.table("player_match_stats")
618 .update(update_data)
619 .eq("player_id", player_id)
620 .eq("match_id", match_id)
621 .execute()
622 )
624 if response.data and len(response.data) > 0:
625 logger.info("stats_started_updated", player_id=player_id, match_id=match_id, started=started)
626 return response.data[0]
627 return None
629 except Exception as e:
630 logger.error("stats_set_started_error", player_id=player_id, match_id=match_id, error=str(e))
631 return None
633 @invalidates_cache(STATS_CACHE_PATTERN)
634 def update_minutes(self, player_id: int, match_id: int, minutes: int) -> dict | None:
635 """
636 Update minutes played for a player in a match.
638 Args:
639 player_id: Player ID
640 match_id: Match ID
641 minutes: Minutes played
643 Returns:
644 Updated stats dict
645 """
646 try:
647 # Ensure record exists
648 self.get_or_create_match_stats(player_id, match_id)
650 response = (
651 self.client.table("player_match_stats")
652 .update({"minutes_played": minutes})
653 .eq("player_id", player_id)
654 .eq("match_id", match_id)
655 .execute()
656 )
658 if response.data and len(response.data) > 0:
659 logger.info("stats_minutes_updated", player_id=player_id, match_id=match_id, minutes=minutes)
660 return response.data[0]
661 return None
663 except Exception as e:
664 logger.error("stats_update_minutes_error", player_id=player_id, match_id=match_id, error=str(e))
665 return None
667 # === Batch Operations ===
669 @invalidates_cache(STATS_CACHE_PATTERN)
670 def record_match_appearance(
671 self, player_id: int, match_id: int, started: bool = False, minutes: int = 0
672 ) -> dict | None:
673 """
674 Record a player's appearance in a match.
676 Convenience method to set started and minutes at once.
678 Args:
679 player_id: Player ID
680 match_id: Match ID
681 started: Whether player started
682 minutes: Minutes played
684 Returns:
685 Updated stats dict
686 """
687 try:
688 # Ensure record exists
689 self.get_or_create_match_stats(player_id, match_id)
691 response = (
692 self.client.table("player_match_stats")
693 .update({"started": started, "played": True, "minutes_played": minutes})
694 .eq("player_id", player_id)
695 .eq("match_id", match_id)
696 .execute()
697 )
699 if response.data and len(response.data) > 0:
700 logger.info(
701 "stats_appearance_recorded",
702 player_id=player_id,
703 match_id=match_id,
704 started=started,
705 minutes=minutes,
706 )
707 return response.data[0]
708 return None
710 except Exception as e:
711 logger.error(
712 "stats_record_appearance_error",
713 player_id=player_id,
714 match_id=match_id,
715 error=str(e),
716 )
717 return None