Coverage for dao/match_dao.py: 20.24%
499 statements
« prev ^ index » next coverage.py v7.10.6, created at 2026-04-15 13:38 +0000
1"""
2Match data access layer for MissingTable.
4Provides data access objects for matches, teams, leagues, divisions, seasons,
5and related soccer/futbol data using Supabase.
6"""
8import os
9from datetime import UTC
11import httpx
12import structlog
13from dotenv import load_dotenv
14from postgrest.exceptions import APIError
16from dao.base_dao import BaseDAO, clear_cache, dao_cache, invalidates_cache
17from dao.exceptions import DuplicateRecordError
18from dao.standings import (
19 calculate_standings_with_extras,
20 filter_by_match_type,
21 filter_completed_matches,
22 filter_same_division_matches,
23)
24from supabase import create_client
26logger = structlog.get_logger()
28# Cache patterns for invalidation
29MATCHES_CACHE_PATTERN = "mt:dao:matches:*"
30PLAYOFF_CACHE_PATTERN = "mt:dao:playoffs:*"
31TOURNAMENTS_CACHE_PATTERN = "mt:dao:tournaments:*"
34# Load environment variables with environment-specific support
35def load_environment():
36 """Load environment variables based on APP_ENV or default to local."""
37 # First load base .env file
38 load_dotenv()
40 # Determine which environment to use
41 app_env = os.getenv("APP_ENV", "local") # Default to local
43 # Load environment-specific file
44 env_file = f".env.{app_env}"
45 if os.path.exists(env_file): 45 ↛ 46line 45 didn't jump to line 46 because the condition on line 45 was never true
46 load_dotenv(env_file, override=False)
47 else:
48 # Fallback to .env.local for backwards compatibility
49 if os.path.exists(".env.local"): 49 ↛ 50line 49 didn't jump to line 50 because the condition on line 49 was never true
50 load_dotenv(".env.local", override=False)
53class SupabaseConnection:
54 """Manage the connection to Supabase with SSL workaround."""
56 def __init__(self):
57 """Initialize Supabase client with custom SSL configuration."""
58 load_environment()
59 self.url = os.getenv("SUPABASE_URL")
60 # Backend should always use SERVICE_KEY for administrative operations
61 service_key = os.getenv("SUPABASE_SERVICE_KEY")
62 anon_key = os.getenv("SUPABASE_ANON_KEY")
63 self.key = service_key or anon_key
65 if not self.url or not self.key: 65 ↛ 66line 65 didn't jump to line 66 because the condition on line 65 was never true
66 raise ValueError("SUPABASE_URL and SUPABASE_ANON_KEY (or SUPABASE_SERVICE_KEY) must be set in .env file")
68 # Debug output - check what keys are actually set
69 key_type = "SERVICE_KEY" if service_key and self.key == service_key else "ANON_KEY"
70 logger.info(f"DEBUG SupabaseConnection: key_type={key_type}, service_key_present={bool(service_key)}")
72 try:
73 # Try with custom httpx client
74 transport = httpx.HTTPTransport(retries=3)
75 timeout = httpx.Timeout(30.0, connect=10.0)
77 # Create custom client with extended timeout and retries
78 http_client = httpx.Client(transport=transport, timeout=timeout, follow_redirects=True)
80 # Create Supabase client with custom HTTP client
81 self.client = create_client(self.url, self.key, options={"httpx_client": http_client})
82 logger.debug("Connection to Supabase established")
84 except Exception:
85 # Fallback to standard client
86 self.client = create_client(self.url, self.key)
87 logger.debug("Connection to Supabase established (fallback client)")
89 def get_client(self):
90 """Get the Supabase client instance."""
91 return self.client
94class MatchDAO(BaseDAO):
95 """Data Access Object for match and league data using normalized schema."""
97 # === Core Match Methods ===
99 def get_match_by_external_id(self, external_match_id: str) -> dict | None:
100 """Get a match by its external match_id (from match-scraper).
102 This is used for deduplication - checking if a match from match-scraper
103 already exists in the database.
105 Returns:
106 Match dict with flattened structure, or None if not found
107 """
108 try:
109 response = (
110 self.client.table("matches")
111 .select("""
112 *,
113 home_team:teams!matches_home_team_id_fkey(id, name),
114 away_team:teams!matches_away_team_id_fkey(id, name),
115 season:seasons(id, name),
116 age_group:age_groups(id, name),
117 match_type:match_types(id, name),
118 division:divisions(id, name)
119 """)
120 .eq("match_id", external_match_id)
121 .limit(1)
122 .execute()
123 )
125 if response.data and len(response.data) > 0:
126 match = response.data[0]
127 # Flatten to match format from get_match_by_id
128 flat_match = {
129 "id": match["id"],
130 "match_date": match["match_date"],
131 "scheduled_kickoff": match.get("scheduled_kickoff"),
132 "home_team_id": match["home_team_id"],
133 "away_team_id": match["away_team_id"],
134 "home_team_name": match["home_team"]["name"] if match.get("home_team") else "Unknown",
135 "away_team_name": match["away_team"]["name"] if match.get("away_team") else "Unknown",
136 "home_score": match["home_score"],
137 "away_score": match["away_score"],
138 "season_id": match["season_id"],
139 "season_name": match["season"]["name"] if match.get("season") else "Unknown",
140 "age_group_id": match["age_group_id"],
141 "age_group_name": match["age_group"]["name"] if match.get("age_group") else "Unknown",
142 "match_type_id": match["match_type_id"],
143 "match_type_name": match["match_type"]["name"] if match.get("match_type") else "Unknown",
144 "division_id": match.get("division_id"),
145 "division_name": match["division"]["name"] if match.get("division") else "Unknown",
146 "match_status": match.get("match_status"),
147 "created_by": match.get("created_by"),
148 "updated_by": match.get("updated_by"),
149 "source": match.get("source", "manual"),
150 "match_id": match.get("match_id"),
151 "created_at": match["created_at"],
152 "updated_at": match["updated_at"],
153 }
154 return flat_match
155 return None
157 except Exception:
158 logger.exception("Error getting match by external ID", external_match_id=external_match_id)
159 return None
161 def get_match_by_teams_and_date(
162 self, home_team_id: int, away_team_id: int, match_date: str, age_group_id: int | None = None
163 ) -> dict | None:
164 """Get a match by home/away teams, date, and optionally age group.
166 This is used as a fallback for deduplication when match_id is not populated
167 (e.g., manually-entered matches without external match IDs).
169 Args:
170 home_team_id: Database ID of home team
171 away_team_id: Database ID of away team
172 match_date: ISO format date string (YYYY-MM-DD)
173 age_group_id: Optional database ID of age group (recommended to avoid false matches)
175 Returns:
176 Match dict with flattened structure, or None if not found
177 """
178 try:
179 query = (
180 self.client.table("matches")
181 .select("""
182 *,
183 home_team:teams!matches_home_team_id_fkey(id, name),
184 away_team:teams!matches_away_team_id_fkey(id, name),
185 season:seasons(id, name),
186 age_group:age_groups(id, name),
187 match_type:match_types(id, name),
188 division:divisions(id, name)
189 """)
190 .eq("home_team_id", home_team_id)
191 .eq("away_team_id", away_team_id)
192 .eq("match_date", match_date)
193 )
195 # Add age_group filter if provided (prevents matching different age groups)
196 if age_group_id is not None:
197 query = query.eq("age_group_id", age_group_id)
199 response = query.limit(1).execute()
201 if response.data and len(response.data) > 0:
202 match = response.data[0]
203 # Flatten to match format from get_match_by_id
204 flat_match = {
205 "id": match["id"],
206 "match_date": match["match_date"],
207 "scheduled_kickoff": match.get("scheduled_kickoff"),
208 "home_team_id": match["home_team_id"],
209 "away_team_id": match["away_team_id"],
210 "home_team_name": match["home_team"]["name"] if match.get("home_team") else "Unknown",
211 "away_team_name": match["away_team"]["name"] if match.get("away_team") else "Unknown",
212 "home_score": match["home_score"],
213 "away_score": match["away_score"],
214 "season_id": match["season_id"],
215 "season_name": match["season"]["name"] if match.get("season") else "Unknown",
216 "age_group_id": match["age_group_id"],
217 "age_group_name": match["age_group"]["name"] if match.get("age_group") else "Unknown",
218 "match_type_id": match["match_type_id"],
219 "match_type_name": match["match_type"]["name"] if match.get("match_type") else "Unknown",
220 "division_id": match.get("division_id"),
221 "division_name": match["division"]["name"] if match.get("division") else "Unknown",
222 "match_status": match.get("match_status"),
223 "created_by": match.get("created_by"),
224 "updated_by": match.get("updated_by"),
225 "source": match.get("source", "manual"),
226 "match_id": match.get("match_id"),
227 "created_at": match["created_at"],
228 "updated_at": match["updated_at"],
229 }
230 return flat_match
231 return None
233 except Exception:
234 logger.exception("Error getting match by teams and date")
235 return None
237 @invalidates_cache(MATCHES_CACHE_PATTERN)
238 def update_match_external_id(self, match_id: int, external_match_id: str) -> bool:
239 """Update only the external match_id field on an existing match.
241 This is used when a manually-entered match is matched with a scraped match,
242 allowing future scrapes to use the external_match_id for deduplication.
244 Args:
245 match_id: Database ID of the match to update
246 external_match_id: External match ID from match-scraper (e.g., "98966")
248 Returns:
249 True if update successful, False otherwise
250 """
251 try:
252 response = (
253 self.client.table("matches")
254 .update(
255 {
256 "match_id": external_match_id,
257 "source": "match-scraper", # Update source to indicate scraper now manages this
258 }
259 )
260 .eq("id", match_id)
261 .execute()
262 )
264 if response.data:
265 logger.info(
266 "Updated match with external match_id",
267 match_id=match_id,
268 external_match_id=external_match_id,
269 )
270 return True
271 return False
273 except Exception:
274 logger.exception("Error updating match external_id")
275 return False
277 @invalidates_cache(MATCHES_CACHE_PATTERN)
278 def create_match(
279 self,
280 home_team_id: int,
281 away_team_id: int,
282 match_date: str,
283 season_id: int,
284 home_score: int | None = None,
285 away_score: int | None = None,
286 match_status: str = "scheduled",
287 source: str = "manual",
288 match_id: str | None = None,
289 age_group_id: int = 1,
290 division_id: int | None = None,
291 scheduled_kickoff: str | None = None,
292 ) -> int | None:
293 """Create a new match with pre-resolved IDs.
295 Callers are responsible for resolving names to IDs before calling this
296 method. The Celery task and API layer both do this already.
298 Args:
299 home_team_id: Database ID of home team
300 away_team_id: Database ID of away team
301 match_date: ISO format date string
302 season_id: Database ID of season
303 home_score: Home team score (optional)
304 away_score: Away team score (optional)
305 match_status: Match status (scheduled, live, completed, etc.)
306 source: Data source (default: "manual", use "match-scraper" for external)
307 match_id: External match ID for deduplication (optional)
308 age_group_id: Database ID of age group (default: 1)
309 division_id: Database ID of division (optional)
311 Returns:
312 Created match ID, or None on failure
313 """
314 try:
315 # Validate division for match-scraper sourced matches
316 if source == "match-scraper" and division_id is None:
317 logger.error("Division is required for match-scraper sourced matches but was not provided")
318 raise ValueError("Division is required for match-scraper sourced matches")
320 match_type_id = 1 # Default League
322 data = {
323 "match_date": match_date,
324 "home_team_id": home_team_id,
325 "away_team_id": away_team_id,
326 "home_score": home_score,
327 "away_score": away_score,
328 "season_id": season_id,
329 "age_group_id": age_group_id,
330 "match_type_id": match_type_id,
331 "division_id": division_id,
332 "match_status": match_status,
333 "source": source,
334 }
336 if match_id:
337 data["match_id"] = match_id
338 if scheduled_kickoff:
339 data["scheduled_kickoff"] = scheduled_kickoff
341 response = self.client.table("matches").insert(data).execute()
343 if response.data and len(response.data) > 0:
344 return response.data[0]["id"]
345 return None
347 except Exception:
348 logger.exception("Error creating match")
349 return None
351 # === Match Methods ===
353 def get_all_matches(
354 self,
355 season_id: int | None = None,
356 age_group_id: int | None = None,
357 division_id: int | None = None,
358 team_id: int | None = None,
359 match_type: str | None = None,
360 start_date: str | None = None,
361 end_date: str | None = None,
362 ) -> list[dict]:
363 """Get all matches with optional filters.
365 Args:
366 season_id: Filter by season ID
367 age_group_id: Filter by age group ID
368 division_id: Filter by division ID
369 team_id: Filter by team ID (home or away)
370 match_type: Filter by match type name
371 start_date: Filter by start date (YYYY-MM-DD)
372 end_date: Filter by end date (YYYY-MM-DD)
373 """
374 try:
375 query = self.client.table("matches").select("""
376 *,
377 home_team:teams!matches_home_team_id_fkey(id, name, club:clubs(id, name, logo_url)),
378 away_team:teams!matches_away_team_id_fkey(id, name, club:clubs(id, name, logo_url)),
379 season:seasons(id, name),
380 age_group:age_groups(id, name),
381 match_type:match_types(id, name),
382 division:divisions(id, name, league_id, leagues!divisions_league_id_fkey(id, name))
383 """)
385 # Apply filters
386 if season_id:
387 query = query.eq("season_id", season_id)
388 if age_group_id:
389 query = query.eq("age_group_id", age_group_id)
390 if division_id:
391 query = query.eq("division_id", division_id)
393 # Date range filters
394 if start_date:
395 query = query.gte("match_date", start_date)
396 if end_date:
397 query = query.lte("match_date", end_date)
399 # For team_id, we need to match either home_team_id OR away_team_id
400 if team_id:
401 query = query.or_(f"home_team_id.eq.{team_id},away_team_id.eq.{team_id}")
403 response = query.order("match_date", desc=True).execute()
405 # Filter by match_type name if specified (post-query filtering)
406 filtered_matches = response.data
407 if match_type:
408 filtered_matches = [
409 match for match in response.data if match.get("match_type", {}).get("name") == match_type
410 ]
412 # Flatten the response for easier use
413 matches = []
414 for match in filtered_matches:
415 flat_match = {
416 "id": match["id"],
417 "match_date": match["match_date"],
418 "scheduled_kickoff": match.get("scheduled_kickoff"),
419 "home_team_id": match["home_team_id"],
420 "away_team_id": match["away_team_id"],
421 "home_team_name": match["home_team"]["name"] if match.get("home_team") else "Unknown",
422 "away_team_name": match["away_team"]["name"] if match.get("away_team") else "Unknown",
423 "home_team_club": match["home_team"].get("club") if match.get("home_team") else None,
424 "away_team_club": match["away_team"].get("club") if match.get("away_team") else None,
425 "home_score": match["home_score"],
426 "away_score": match["away_score"],
427 "season_id": match["season_id"],
428 "season_name": match["season"]["name"] if match.get("season") else "Unknown",
429 "age_group_id": match["age_group_id"],
430 "age_group_name": match["age_group"]["name"] if match.get("age_group") else "Unknown",
431 "match_type_id": match["match_type_id"],
432 "match_type_name": match["match_type"]["name"] if match.get("match_type") else "Unknown",
433 "division_id": match.get("division_id"),
434 "division_name": match["division"]["name"] if match.get("division") else "Unknown",
435 "league_id": match["division"]["league_id"] if match.get("division") else None,
436 "league_name": match["division"]["leagues"]["name"]
437 if match.get("division") and match["division"].get("leagues")
438 else "Unknown",
439 "division": match.get("division"), # Include full division object with leagues
440 "match_status": match.get("match_status"),
441 "created_by": match.get("created_by"),
442 "updated_by": match.get("updated_by"),
443 "source": match.get("source", "manual"),
444 "match_id": match.get("match_id"), # External match identifier
445 "created_at": match["created_at"],
446 "updated_at": match["updated_at"],
447 }
448 matches.append(flat_match)
450 return matches
452 except Exception:
453 logger.exception("Error querying matches")
454 return []
456 def get_match_summary(
457 self,
458 season_name: str,
459 score_from: str | None = None,
460 score_to: str | None = None,
461 ) -> list[dict]:
462 """Get match summary statistics grouped by age group, league, and division.
464 Used by the match-scraper-agent to understand what MT already has
465 and make smart decisions about what to scrape.
467 Args:
468 season_name: Season name, e.g. '2025-2026'.
469 score_from: If set, only count needs_score for matches >= this date.
470 score_to: If set, only count needs_score for matches <= this date.
471 """
472 from collections import defaultdict
473 from datetime import date, timedelta
475 today = date.today().isoformat()
476 kickoff_horizon = (date.today() + timedelta(days=14)).isoformat()
478 # Look up season by name
479 season_resp = self.client.table("seasons").select("id").eq("name", season_name).limit(1).execute()
480 if not season_resp.data:
481 return []
482 season_id = season_resp.data[0]["id"]
484 # Fetch all non-cancelled matches for this season with joins.
485 # Supabase defaults to 1000 rows — paginate to get the full season.
486 _page_size = 1000
487 _offset = 0
488 all_matches: list[dict] = []
489 while True:
490 response = (
491 self.client.table("matches")
492 .select("""
493 match_date, match_status, home_score, away_score, scheduled_kickoff,
494 age_group:age_groups(name),
495 division:divisions(name, league_id, leagues:leagues!divisions_league_id_fkey(name))
496 """)
497 .eq("season_id", season_id)
498 .neq("match_status", "cancelled")
499 .range(_offset, _offset + _page_size - 1)
500 .execute()
501 )
502 all_matches.extend(response.data)
503 if len(response.data) < _page_size:
504 break
505 _offset += _page_size
507 # Group by (age_group, league, division)
508 groups = defaultdict(list)
509 for m in all_matches:
510 ag = m["age_group"]["name"] if m.get("age_group") else "Unknown"
511 div_name = m["division"]["name"] if m.get("division") else "Unknown"
512 league_name = (
513 m["division"]["leagues"]["name"] if m.get("division") and m["division"].get("leagues") else "Unknown"
514 )
515 groups[(ag, league_name, div_name)].append(m)
517 # Compute summary per group
518 summaries = []
519 for (ag, league, div), matches in sorted(groups.items()):
520 by_status = defaultdict(int)
521 needs_score = 0
522 needs_kickoff = 0
523 dates = []
524 last_played = None
526 for m in matches:
527 status = m.get("match_status", "scheduled")
528 by_status[status] += 1
529 md = m["match_date"]
530 dates.append(md)
532 if md < today and status in ("scheduled", "tbd") and m.get("home_score") is None:
533 in_window = (not score_from or md >= score_from) and (not score_to or md <= score_to)
534 if in_window: 534 ↛ 537line 534 didn't jump to line 537 because the condition on line 534 was always true
535 needs_score += 1
537 if status in ("scheduled", "tbd") and today <= md <= kickoff_horizon and not m.get("scheduled_kickoff"): 537 ↛ 538line 537 didn't jump to line 538 because the condition on line 537 was never true
538 needs_kickoff += 1
540 if status in ("completed", "forfeit") and (last_played is None or md > last_played):
541 last_played = md
543 summaries.append(
544 {
545 "age_group": ag,
546 "league": league,
547 "division": div,
548 "total": len(matches),
549 "by_status": dict(by_status),
550 "needs_score": needs_score,
551 "needs_kickoff": needs_kickoff,
552 "date_range": {
553 "earliest": min(dates) if dates else None,
554 "latest": max(dates) if dates else None,
555 },
556 "last_played_date": last_played,
557 }
558 )
560 return summaries
562 def get_matches_by_team(
563 self, team_id: int, season_id: int | None = None, age_group_id: int | None = None
564 ) -> list[dict]:
565 """Get all matches for a specific team."""
566 try:
567 query = (
568 self.client.table("matches")
569 .select("""
570 *,
571 home_team:teams!matches_home_team_id_fkey(id, name, club:clubs(id, name, logo_url)),
572 away_team:teams!matches_away_team_id_fkey(id, name, club:clubs(id, name, logo_url)),
573 season:seasons(id, name),
574 age_group:age_groups(id, name),
575 match_type:match_types(id, name),
576 division:divisions(id, name)
577 """)
578 .or_(f"home_team_id.eq.{team_id},away_team_id.eq.{team_id}")
579 )
581 if season_id:
582 query = query.eq("season_id", season_id)
584 if age_group_id:
585 query = query.eq("age_group_id", age_group_id)
587 response = query.order("match_date", desc=True).execute()
589 # Flatten response (same as get_all_matches)
590 matches = []
591 for match in response.data:
592 flat_match = {
593 "id": match["id"],
594 "match_date": match["match_date"],
595 "scheduled_kickoff": match.get("scheduled_kickoff"),
596 "home_team_id": match["home_team_id"],
597 "away_team_id": match["away_team_id"],
598 "home_team_name": match["home_team"]["name"] if match.get("home_team") else "Unknown",
599 "away_team_name": match["away_team"]["name"] if match.get("away_team") else "Unknown",
600 "home_team_club": match["home_team"].get("club") if match.get("home_team") else None,
601 "away_team_club": match["away_team"].get("club") if match.get("away_team") else None,
602 "home_score": match["home_score"],
603 "away_score": match["away_score"],
604 "season_id": match["season_id"],
605 "season_name": match["season"]["name"] if match.get("season") else "Unknown",
606 "age_group_id": match["age_group_id"],
607 "age_group_name": match["age_group"]["name"] if match.get("age_group") else "Unknown",
608 "match_type_id": match["match_type_id"],
609 "match_type_name": match["match_type"]["name"] if match.get("match_type") else "Unknown",
610 "division_id": match.get("division_id"),
611 "division_name": match["division"]["name"] if match.get("division") else "Unknown",
612 "division": match.get("division"), # Include full division object with leagues
613 "match_status": match.get("match_status"),
614 "created_by": match.get("created_by"),
615 "updated_by": match.get("updated_by"),
616 "source": match.get("source", "manual"),
617 "match_id": match.get("match_id"), # External match identifier
618 "created_at": match.get("created_at"),
619 "updated_at": match.get("updated_at"),
620 }
621 matches.append(flat_match)
623 return matches
625 except Exception:
626 logger.exception("Error querying matches by team")
627 return []
629 def get_match_preview(
630 self,
631 home_team_id: int,
632 away_team_id: int,
633 season_id: int | None = None,
634 age_group_id: int | None = None,
635 recent_count: int = 5,
636 ) -> dict:
637 """Get match preview data: recent form, common opponents, and head-to-head history.
639 Args:
640 home_team_id: ID of the home team in the upcoming match
641 away_team_id: ID of the away team in the upcoming match
642 season_id: Season for recent form and common opponents (None = all seasons)
643 age_group_id: Optional filter to restrict to a specific age group
644 recent_count: How many recent matches to return per team
646 Returns:
647 Dict with home_team_recent, away_team_recent, common_opponents, head_to_head
648 """
649 select_str = """
650 *,
651 home_team:teams!matches_home_team_id_fkey(id, name),
652 away_team:teams!matches_away_team_id_fkey(id, name),
653 season:seasons(id, name),
654 age_group:age_groups(id, name),
655 match_type:match_types(id, name),
656 division:divisions(id, name)
657 """
659 def flatten(match: dict) -> dict:
660 return {
661 "id": match["id"],
662 "match_date": match["match_date"],
663 "scheduled_kickoff": match.get("scheduled_kickoff"),
664 "home_team_id": match["home_team_id"],
665 "away_team_id": match["away_team_id"],
666 "home_team_name": match["home_team"]["name"] if match.get("home_team") else "Unknown",
667 "away_team_name": match["away_team"]["name"] if match.get("away_team") else "Unknown",
668 "home_score": match["home_score"],
669 "away_score": match["away_score"],
670 "season_id": match["season_id"],
671 "season_name": match["season"]["name"] if match.get("season") else "Unknown",
672 "age_group_id": match["age_group_id"],
673 "age_group_name": match["age_group"]["name"] if match.get("age_group") else "Unknown",
674 "match_type_id": match["match_type_id"],
675 "match_type_name": match["match_type"]["name"] if match.get("match_type") else "Unknown",
676 "division_id": match.get("division_id"),
677 "division_name": match["division"]["name"] if match.get("division") else "Unknown",
678 "match_status": match.get("match_status"),
679 "source": match.get("source", "manual"),
680 "match_id": match.get("match_id"),
681 "created_at": match.get("created_at"),
682 "updated_at": match.get("updated_at"),
683 }
685 def build_base_query(team_id: int):
686 q = (
687 self.client.table("matches")
688 .select(select_str)
689 .or_(f"home_team_id.eq.{team_id},away_team_id.eq.{team_id}")
690 .in_("match_status", ["completed", "forfeit"])
691 )
692 if season_id:
693 q = q.eq("season_id", season_id)
694 if age_group_id:
695 q = q.eq("age_group_id", age_group_id)
696 return q
698 try:
699 # --- Recent form for each team ---
700 home_resp = build_base_query(home_team_id).order("match_date", desc=True).limit(recent_count).execute()
701 away_resp = build_base_query(away_team_id).order("match_date", desc=True).limit(recent_count).execute()
702 home_recent = [flatten(m) for m in (home_resp.data or [])]
703 away_recent = [flatten(m) for m in (away_resp.data or [])]
705 # --- Common opponents (season-scoped) ---
706 # Fetch all season matches for both teams to find shared opponents
707 home_all_resp = build_base_query(home_team_id).order("match_date", desc=True).execute()
708 away_all_resp = build_base_query(away_team_id).order("match_date", desc=True).execute()
709 home_all = [flatten(m) for m in (home_all_resp.data or [])]
710 away_all = [flatten(m) for m in (away_all_resp.data or [])]
712 def extract_opponents(matches: list[dict], team_id: int) -> dict[int, str]:
713 """Return {opponent_id: opponent_name} excluding the two preview teams."""
714 opps: dict[int, str] = {}
715 for m in matches:
716 if m["home_team_id"] == team_id:
717 opp_id, opp_name = m["away_team_id"], m["away_team_name"]
718 else:
719 opp_id, opp_name = m["home_team_id"], m["home_team_name"]
720 # Exclude head-to-head matches between the two preview teams
721 if opp_id not in (home_team_id, away_team_id):
722 opps[opp_id] = opp_name
723 return opps
725 home_opps = extract_opponents(home_all, home_team_id)
726 away_opps = extract_opponents(away_all, away_team_id)
727 common_ids = sorted(set(home_opps) & set(away_opps), key=lambda i: home_opps[i])
729 common_opponents = []
730 for opp_id in common_ids:
731 home_vs = [m for m in home_all if opp_id in (m["home_team_id"], m["away_team_id"])]
732 away_vs = [m for m in away_all if opp_id in (m["home_team_id"], m["away_team_id"])]
733 common_opponents.append(
734 {
735 "opponent_id": opp_id,
736 "opponent_name": home_opps[opp_id],
737 "home_team_matches": home_vs,
738 "away_team_matches": away_vs,
739 }
740 )
742 # --- Head-to-head history (all seasons, cross-season) ---
743 # Fetch all matches for home_team and filter to those against away_team
744 h2h_query = (
745 self.client.table("matches")
746 .select(select_str)
747 .or_(f"home_team_id.eq.{home_team_id},away_team_id.eq.{home_team_id}")
748 .in_("match_status", ["completed", "forfeit"])
749 )
750 if age_group_id:
751 h2h_query = h2h_query.eq("age_group_id", age_group_id)
752 h2h_resp = h2h_query.order("match_date", desc=True).execute()
753 head_to_head = [
754 flatten(m)
755 for m in (h2h_resp.data or [])
756 if away_team_id in (m["home_team_id"], m["away_team_id"])
757 ]
759 return {
760 "home_team_id": home_team_id,
761 "away_team_id": away_team_id,
762 "home_team_recent": home_recent,
763 "away_team_recent": away_recent,
764 "common_opponents": common_opponents,
765 "head_to_head": head_to_head,
766 }
768 except Exception:
769 logger.exception("Error building match preview")
770 return {
771 "home_team_id": home_team_id,
772 "away_team_id": away_team_id,
773 "home_team_recent": [],
774 "away_team_recent": [],
775 "common_opponents": [],
776 "head_to_head": [],
777 }
779 @invalidates_cache(MATCHES_CACHE_PATTERN)
780 def add_match(
781 self,
782 home_team_id: int,
783 away_team_id: int,
784 match_date: str,
785 home_score: int,
786 away_score: int,
787 season_id: int,
788 age_group_id: int,
789 match_type_id: int,
790 division_id: int | None = None,
791 status: str | None = "scheduled",
792 created_by: str | None = None,
793 source: str = "manual",
794 external_match_id: str | None = None,
795 scheduled_kickoff: str | None = None,
796 ) -> bool:
797 """Add a new match with audit trail and optional external match_id."""
798 try:
799 data = {
800 "match_date": match_date,
801 "home_team_id": home_team_id,
802 "away_team_id": away_team_id,
803 "home_score": home_score,
804 "away_score": away_score,
805 "season_id": season_id,
806 "age_group_id": age_group_id,
807 "match_type_id": match_type_id,
808 "source": source,
809 }
811 # Add optional fields
812 if division_id:
813 data["division_id"] = division_id
814 if status:
815 data["match_status"] = status # Map status to match_status column
816 if created_by:
817 data["created_by"] = created_by
818 if external_match_id:
819 data["match_id"] = external_match_id
820 if scheduled_kickoff:
821 data["scheduled_kickoff"] = scheduled_kickoff
823 response = self.client.table("matches").insert(data).execute()
825 return bool(response.data)
827 except APIError as e:
828 error_dict = e.args[0] if e.args else {}
829 if error_dict.get("code") == "23505":
830 # Duplicate key violation
831 logger.warning(
832 "Duplicate match detected",
833 home_team_id=home_team_id,
834 away_team_id=away_team_id,
835 match_date=match_date,
836 details=error_dict.get("details"),
837 )
838 raise DuplicateRecordError(
839 message="A match with these teams on this date already exists",
840 details=error_dict.get("details"),
841 ) from e
842 logger.exception("Error adding match")
843 return False
844 except Exception:
845 logger.exception("Error adding match")
846 return False
848 @invalidates_cache(MATCHES_CACHE_PATTERN)
849 def add_match_with_external_id(
850 self,
851 home_team_id: int,
852 away_team_id: int,
853 match_date: str,
854 home_score: int,
855 away_score: int,
856 season_id: int,
857 age_group_id: int,
858 match_type_id: int,
859 external_match_id: str,
860 division_id: int | None = None,
861 created_by: str | None = None,
862 source: str = "match-scraper",
863 ) -> bool:
864 """Add a new match with external match_id and audit trail.
866 This is a convenience wrapper around add_match() for backwards compatibility.
867 Consider using add_match() directly with external_match_id parameter.
868 """
869 return self.add_match(
870 home_team_id=home_team_id,
871 away_team_id=away_team_id,
872 match_date=match_date,
873 home_score=home_score,
874 away_score=away_score,
875 season_id=season_id,
876 age_group_id=age_group_id,
877 match_type_id=match_type_id,
878 division_id=division_id,
879 created_by=created_by,
880 source=source,
881 external_match_id=external_match_id,
882 )
884 @invalidates_cache(MATCHES_CACHE_PATTERN, PLAYOFF_CACHE_PATTERN, TOURNAMENTS_CACHE_PATTERN)
885 def update_match(
886 self,
887 match_id: int,
888 home_team_id: int,
889 away_team_id: int,
890 match_date: str,
891 home_score: int,
892 away_score: int,
893 season_id: int,
894 age_group_id: int,
895 match_type_id: int,
896 division_id: int | None = None,
897 status: str | None = None,
898 updated_by: str | None = None,
899 external_match_id: str | None = None,
900 scheduled_kickoff: str | None = None,
901 ) -> dict | None:
902 """Update an existing match with audit trail and optional external match_id.
904 Returns the updated match data to avoid read-after-write consistency issues.
905 """
906 try:
907 data = {
908 "match_date": match_date,
909 "home_team_id": home_team_id,
910 "away_team_id": away_team_id,
911 "home_score": home_score,
912 "away_score": away_score,
913 "season_id": season_id,
914 "age_group_id": age_group_id,
915 "match_type_id": match_type_id,
916 "division_id": division_id,
917 }
919 # Add optional fields
920 if status:
921 data["match_status"] = status
922 if updated_by:
923 data["updated_by"] = updated_by
924 if external_match_id is not None: # Allow explicit None to clear match_id
925 data["match_id"] = external_match_id
926 if scheduled_kickoff is not None: # Allow explicit None to clear scheduled_kickoff
927 data["scheduled_kickoff"] = scheduled_kickoff
929 # Execute update
930 response = self.client.table("matches").update(data).eq("id", match_id).execute()
932 # Check if update actually affected any rows
933 if not response.data or len(response.data) == 0:
934 logger.warning("Update match failed - no rows affected", match_id=match_id)
935 # Return None to signal failure
936 return None
938 # Clear cache BEFORE re-fetch to avoid returning stale cached data.
939 # The @invalidates_cache decorator clears AFTER the function returns,
940 # but get_match_by_id uses @dao_cache and would hit stale cache.
941 clear_cache(MATCHES_CACHE_PATTERN)
942 clear_cache(PLAYOFF_CACHE_PATTERN)
943 clear_cache(TOURNAMENTS_CACHE_PATTERN)
945 # Get the updated match to return with full relations
946 return self.get_match_by_id(match_id)
948 except Exception:
949 logger.exception("Error updating match")
950 return None
952 @dao_cache("matches:by_id:{match_id}")
953 def get_match_by_id(self, match_id: int) -> dict | None:
954 """Get a single match by ID with all related data."""
955 try:
956 response = (
957 self.client.table("matches")
958 .select("""
959 *,
960 home_team:teams!matches_home_team_id_fkey(
961 id, name,
962 club:clubs(id, name, logo_url, primary_color, secondary_color),
963 division:divisions(id, leagues(sport_type))
964 ),
965 away_team:teams!matches_away_team_id_fkey(
966 id, name,
967 club:clubs(id, name, logo_url, primary_color, secondary_color)
968 ),
969 season:seasons(id, name),
970 age_group:age_groups(id, name),
971 match_type:match_types(id, name),
972 division:divisions(id, name, leagues(id, name, sport_type))
973 """)
974 .eq("id", match_id)
975 .execute()
976 )
978 if response.data and len(response.data) > 0:
979 match = response.data[0]
980 # Extract sport_type from division -> leagues (match division)
981 division = match.get("division") or {}
982 league = division.get("leagues") or {}
983 sport_type = league.get("sport_type")
985 # Fallback: get sport_type from home team's division -> league
986 if not sport_type:
987 home_team = match.get("home_team") or {}
988 team_div = home_team.get("division") or {}
989 team_league = team_div.get("leagues") or {}
990 sport_type = team_league.get("sport_type", "soccer")
992 # Flatten the response to match the format from get_all_matches
993 flat_match = {
994 "id": match["id"],
995 "match_date": match["match_date"],
996 "scheduled_kickoff": match.get("scheduled_kickoff"),
997 "home_team_id": match["home_team_id"],
998 "away_team_id": match["away_team_id"],
999 "home_team_name": match["home_team"]["name"] if match.get("home_team") else "Unknown",
1000 "away_team_name": match["away_team"]["name"] if match.get("away_team") else "Unknown",
1001 "home_team_club": match["home_team"].get("club") if match.get("home_team") else None,
1002 "away_team_club": match["away_team"].get("club") if match.get("away_team") else None,
1003 "home_score": match["home_score"],
1004 "away_score": match["away_score"],
1005 "season_id": match["season_id"],
1006 "season_name": match["season"]["name"] if match.get("season") else "Unknown",
1007 "age_group_id": match["age_group_id"],
1008 "age_group_name": match["age_group"]["name"] if match.get("age_group") else "Unknown",
1009 "match_type_id": match["match_type_id"],
1010 "match_type_name": match["match_type"]["name"] if match.get("match_type") else "Unknown",
1011 "division_id": match.get("division_id"),
1012 "division_name": match["division"]["name"] if match.get("division") else "Unknown",
1013 "division": match.get("division"), # Include full division object with leagues
1014 "sport_type": sport_type,
1015 "match_status": match.get("match_status"),
1016 "created_by": match.get("created_by"),
1017 "updated_by": match.get("updated_by"),
1018 "source": match.get("source", "manual"),
1019 "match_id": match.get("match_id"), # External match identifier
1020 "created_at": match["created_at"],
1021 "updated_at": match["updated_at"],
1022 # Live match clock fields
1023 "kickoff_time": match.get("kickoff_time"),
1024 "halftime_start": match.get("halftime_start"),
1025 "second_half_start": match.get("second_half_start"),
1026 "match_end_time": match.get("match_end_time"),
1027 "half_duration": match.get("half_duration", 45),
1028 }
1029 return flat_match
1030 else:
1031 return None
1033 except Exception:
1034 logger.exception("Error retrieving match by ID")
1035 return None
1037 @invalidates_cache(MATCHES_CACHE_PATTERN, TOURNAMENTS_CACHE_PATTERN)
1038 def delete_match(self, match_id: int) -> bool:
1039 """Delete a match."""
1040 try:
1041 self.client.table("matches").delete().eq("id", match_id).execute()
1043 return True # Supabase delete returns empty data even on success
1045 except Exception:
1046 logger.exception("Error deleting match")
1047 return False
1049 @dao_cache("matches:table:{season_id}:{age_group_id}:{division_id}:{match_type}")
1050 def get_league_table(
1051 self,
1052 season_id: int | None = None,
1053 age_group_id: int | None = None,
1054 division_id: int | None = None,
1055 match_type: str = "League",
1056 ) -> list[dict]:
1057 """
1058 Generate league table with optional filters.
1060 This method fetches matches from the database and delegates
1061 the standings calculation to pure functions in dao/standings.py.
1063 Args:
1064 season_id: Filter by season
1065 age_group_id: Filter by age group
1066 division_id: Filter by division
1067 match_type: Filter by match type name (default: "League")
1069 Returns:
1070 List of team standings sorted by points, goal difference, goals scored
1071 """
1072 try:
1073 logger.info(
1074 "generating league table from database",
1075 season_id=season_id,
1076 age_group_id=age_group_id,
1077 division_id=division_id,
1078 match_type=match_type,
1079 )
1080 # Fetch matches from database
1081 matches = self._fetch_matches_for_standings(season_id, age_group_id, division_id)
1083 # Apply filters using pure functions
1084 matches = filter_by_match_type(matches, match_type)
1085 if division_id:
1086 matches = filter_same_division_matches(matches, division_id)
1087 matches = filter_completed_matches(matches)
1089 # Calculate standings using pure function (with form + movement)
1090 return calculate_standings_with_extras(matches)
1092 except Exception:
1093 logger.exception("Error generating league table")
1094 return []
1096 def _fetch_matches_for_standings(
1097 self,
1098 season_id: int | None = None,
1099 age_group_id: int | None = None,
1100 division_id: int | None = None,
1101 ) -> list[dict]:
1102 """
1103 Fetch matches from database for standings calculation.
1105 This is a thin data access method that only handles the query.
1106 All business logic is in pure functions.
1108 Args:
1109 season_id: Filter by season
1110 age_group_id: Filter by age group
1111 division_id: Filter by division
1113 Returns:
1114 List of match dictionaries from database
1115 """
1116 logger.info(
1117 "fetching matches for standings calculation from database",
1118 season_id=season_id,
1119 age_group_id=age_group_id,
1120 division_id=division_id,
1121 )
1122 query = self.client.table("matches").select("""
1123 *,
1124 home_team:teams!matches_home_team_id_fkey(id, name, division_id, club:clubs(id, name, logo_url)),
1125 away_team:teams!matches_away_team_id_fkey(id, name, division_id, club:clubs(id, name, logo_url)),
1126 match_type:match_types(id, name)
1127 """)
1129 # Apply database-level filters
1130 if season_id:
1131 query = query.eq("season_id", season_id)
1132 if age_group_id:
1133 query = query.eq("age_group_id", age_group_id)
1134 if division_id:
1135 query = query.eq("division_id", division_id)
1137 response = query.execute()
1138 return response.data
1140 # === Live Match Methods ===
1142 def get_live_matches(self) -> list[dict]:
1143 """Get all matches with status 'live'.
1145 Returns minimal data for the LIVE tab polling.
1146 """
1147 try:
1148 response = (
1149 self.client.table("matches")
1150 .select("""
1151 id,
1152 match_status,
1153 match_date,
1154 home_score,
1155 away_score,
1156 kickoff_time,
1157 home_team:teams!matches_home_team_id_fkey(id, name),
1158 away_team:teams!matches_away_team_id_fkey(id, name)
1159 """)
1160 .eq("match_status", "live")
1161 .execute()
1162 )
1164 # Flatten the response
1165 result = []
1166 for match in response.data or []:
1167 result.append(
1168 {
1169 "match_id": match["id"],
1170 "match_status": match["match_status"],
1171 "match_date": match["match_date"],
1172 "home_score": match["home_score"],
1173 "away_score": match["away_score"],
1174 "kickoff_time": match.get("kickoff_time"),
1175 "home_team_name": match["home_team"]["name"] if match.get("home_team") else "Unknown",
1176 "away_team_name": match["away_team"]["name"] if match.get("away_team") else "Unknown",
1177 }
1178 )
1179 return result
1181 except Exception:
1182 logger.exception("Error getting live matches")
1183 return []
1185 def get_live_match_state(self, match_id: int) -> dict | None:
1186 """Get full live match state including clock timestamps.
1188 Returns match data with clock fields for the live match view.
1189 """
1190 try:
1191 response = (
1192 self.client.table("matches")
1193 .select("""
1194 *,
1195 home_team:teams!matches_home_team_id_fkey(
1196 id, name,
1197 club:clubs(id, logo_url),
1198 division:divisions(id, leagues(sport_type))
1199 ),
1200 away_team:teams!matches_away_team_id_fkey(id, name, club:clubs(id, logo_url)),
1201 age_group:age_groups(id, name),
1202 match_type:match_types(id, name),
1203 division:divisions(id, name, leagues(id, name, sport_type))
1204 """)
1205 .eq("id", match_id)
1206 .single()
1207 .execute()
1208 )
1210 if not response.data:
1211 return None
1213 match = response.data
1214 # Extract club logo URLs from nested team -> club relationships
1215 home_team = match.get("home_team") or {}
1216 away_team = match.get("away_team") or {}
1217 home_club = home_team.get("club") or {}
1218 away_club = away_team.get("club") or {}
1220 # Extract sport_type from division -> leagues
1221 division = match.get("division") or {}
1222 league = division.get("leagues") or {}
1223 sport_type = league.get("sport_type")
1225 # Fallback: get sport_type from home team's division -> league
1226 if not sport_type:
1227 team_div = home_team.get("division") or {}
1228 team_league = team_div.get("leagues") or {}
1229 sport_type = team_league.get("sport_type", "soccer")
1231 return {
1232 "match_id": match["id"],
1233 "match_status": match.get("match_status"),
1234 "match_date": match["match_date"],
1235 "scheduled_kickoff": match.get("scheduled_kickoff"),
1236 "home_score": match["home_score"],
1237 "away_score": match["away_score"],
1238 "kickoff_time": match.get("kickoff_time"),
1239 "halftime_start": match.get("halftime_start"),
1240 "second_half_start": match.get("second_half_start"),
1241 "match_end_time": match.get("match_end_time"),
1242 "half_duration": match.get("half_duration", 45),
1243 "season_id": match.get("season_id"),
1244 "home_team_id": match["home_team_id"],
1245 "home_team_name": home_team.get("name", "Unknown"),
1246 "home_team_logo": home_club.get("logo_url"),
1247 "away_team_id": match["away_team_id"],
1248 "away_team_name": away_team.get("name", "Unknown"),
1249 "away_team_logo": away_club.get("logo_url"),
1250 "age_group_name": match["age_group"]["name"] if match.get("age_group") else None,
1251 "match_type_name": match["match_type"]["name"] if match.get("match_type") else None,
1252 "division_name": match["division"]["name"] if match.get("division") else None,
1253 "sport_type": sport_type,
1254 }
1256 except Exception:
1257 logger.exception("Error getting live match state", match_id=match_id)
1258 return None
1260 @invalidates_cache(MATCHES_CACHE_PATTERN, TOURNAMENTS_CACHE_PATTERN)
1261 def update_match_clock(
1262 self,
1263 match_id: int,
1264 action: str,
1265 updated_by: str | None = None,
1266 half_duration: int | None = None,
1267 ) -> dict | None:
1268 """Update match clock based on action.
1270 Args:
1271 match_id: The match to update
1272 action: Clock action - 'start_first_half', 'start_halftime',
1273 'start_second_half', 'end_match'
1274 updated_by: UUID of user performing the action
1275 half_duration: Duration of each half in minutes (only for start_first_half)
1277 Returns:
1278 Updated match state or None on error
1279 """
1280 from datetime import datetime
1282 try:
1283 now = datetime.now(UTC).isoformat()
1284 data: dict = {"updated_by": updated_by} if updated_by else {}
1286 if action == "start_first_half":
1287 # Start the match - set kickoff time and status to live
1288 data["kickoff_time"] = now
1289 data["match_status"] = "live"
1290 # Set half duration if provided (default is 45)
1291 if half_duration:
1292 data["half_duration"] = half_duration
1293 elif action == "start_halftime":
1294 # Mark halftime started
1295 data["halftime_start"] = now
1296 elif action == "start_second_half":
1297 # Start second half
1298 data["second_half_start"] = now
1299 elif action == "end_match":
1300 # End the match
1301 data["match_end_time"] = now
1302 data["match_status"] = "completed"
1303 else:
1304 logger.warning("Invalid clock action", action=action)
1305 return None
1307 response = self.client.table("matches").update(data).eq("id", match_id).execute()
1309 if not response.data:
1310 logger.warning("Clock update failed - no rows affected", match_id=match_id)
1311 return None
1313 logger.info(
1314 "match_clock_updated",
1315 match_id=match_id,
1316 action=action,
1317 )
1319 # Return updated state
1320 return self.get_live_match_state(match_id)
1322 except Exception:
1323 logger.exception("Error updating match clock", match_id=match_id, action=action)
1324 return None
1326 @invalidates_cache(MATCHES_CACHE_PATTERN, TOURNAMENTS_CACHE_PATTERN)
1327 def update_match_score(
1328 self,
1329 match_id: int,
1330 home_score: int,
1331 away_score: int,
1332 updated_by: str | None = None,
1333 ) -> dict | None:
1334 """Update match score (used when posting a goal).
1336 Args:
1337 match_id: The match to update
1338 home_score: New home team score
1339 away_score: New away team score
1340 updated_by: UUID of user performing the action
1342 Returns:
1343 Updated match state or None on error
1344 """
1345 try:
1346 data = {
1347 "home_score": home_score,
1348 "away_score": away_score,
1349 }
1350 if updated_by:
1351 data["updated_by"] = updated_by
1353 response = self.client.table("matches").update(data).eq("id", match_id).execute()
1355 if not response.data:
1356 logger.warning("Score update failed - no rows affected", match_id=match_id)
1357 return None
1359 logger.info(
1360 "match_score_updated",
1361 match_id=match_id,
1362 home_score=home_score,
1363 away_score=away_score,
1364 )
1366 return self.get_live_match_state(match_id)
1368 except Exception:
1369 logger.exception("Error updating match score", match_id=match_id)
1370 return None
1372 def get_agent_matches(
1373 self,
1374 team: str,
1375 age_group: str,
1376 league: str,
1377 division: str,
1378 season: str,
1379 start_date: str | None = None,
1380 end_date: str | None = None,
1381 ) -> list[dict]:
1382 """Get individual match records for the audit agent's comparison step.
1384 Returns matches involving the specified team (home or away) in the
1385 given age-group/league/division/season. Used by GET /api/agent/matches.
1387 Args:
1388 team: MT canonical team name, e.g. "IFA".
1389 age_group: e.g. "U14".
1390 league: e.g. "Homegrown".
1391 division: e.g. "Northeast".
1392 season: e.g. "2025-2026".
1393 start_date: If set, only return matches on or after this date (YYYY-MM-DD).
1394 end_date: If set, only return matches on or before this date (YYYY-MM-DD).
1396 Returns:
1397 List of match dicts with fields the audit comparator expects.
1398 """
1399 # Resolve IDs for the reference dimensions
1400 season_resp = self.client.table("seasons").select("id").eq("name", season).limit(1).execute()
1401 if not season_resp.data:
1402 logger.warning("get_agent_matches.season_not_found", season=season)
1403 return []
1404 season_id = season_resp.data[0]["id"]
1406 ag_resp = self.client.table("age_groups").select("id").eq("name", age_group).limit(1).execute()
1407 if not ag_resp.data:
1408 logger.warning("get_agent_matches.age_group_not_found", age_group=age_group)
1409 return []
1410 age_group_id = ag_resp.data[0]["id"]
1412 # Resolve division_id via league join
1413 div_resp = (
1414 self.client.table("divisions")
1415 .select("id, leagues!divisions_league_id_fkey(name)")
1416 .eq("name", division)
1417 .execute()
1418 )
1419 division_id = None
1420 for row in div_resp.data or []:
1421 if row.get("leagues") and row["leagues"].get("name") == league:
1422 division_id = row["id"]
1423 break
1424 if division_id is None:
1425 logger.warning("get_agent_matches.division_not_found", division=division, league=league)
1426 return []
1428 # Resolve team IDs matching the given name
1429 team_resp = self.client.table("teams").select("id").eq("name", team).execute()
1430 team_ids = [r["id"] for r in (team_resp.data or [])]
1431 if not team_ids:
1432 logger.warning("get_agent_matches.team_not_found", team=team)
1433 return []
1435 # Build OR filter for home/away team membership
1436 or_filter = ",".join(
1437 [f"home_team_id.eq.{tid}" for tid in team_ids] + [f"away_team_id.eq.{tid}" for tid in team_ids]
1438 )
1440 try:
1441 query = (
1442 self.client.table("matches")
1443 .select(
1444 "match_id, match_date, scheduled_kickoff, home_score, away_score, match_status, "
1445 "home_team:teams!matches_home_team_id_fkey(name), "
1446 "away_team:teams!matches_away_team_id_fkey(name)"
1447 )
1448 .eq("season_id", season_id)
1449 .eq("age_group_id", age_group_id)
1450 .eq("division_id", division_id)
1451 .neq("match_status", "cancelled")
1452 )
1453 if start_date:
1454 query = query.gte("match_date", start_date)
1455 if end_date:
1456 query = query.lte("match_date", end_date)
1457 response = query.or_(or_filter).order("match_date", desc=False).execute()
1458 except Exception:
1459 logger.exception("get_agent_matches.query_error", team=team)
1460 return []
1462 results = []
1463 for m in response.data or []:
1464 # Format match_time as "HH:MM" from scheduled_kickoff (UTC)
1465 match_time = None
1466 if m.get("scheduled_kickoff"):
1467 try:
1468 from datetime import datetime
1470 kt = datetime.fromisoformat(m["scheduled_kickoff"].replace("Z", "+00:00"))
1471 if kt.hour or kt.minute:
1472 match_time = kt.strftime("%H:%M")
1473 except (ValueError, AttributeError):
1474 pass
1476 results.append(
1477 {
1478 "external_match_id": m.get("match_id"),
1479 "home_team": m["home_team"]["name"] if m.get("home_team") else None,
1480 "away_team": m["away_team"]["name"] if m.get("away_team") else None,
1481 "match_date": m["match_date"],
1482 "match_time": match_time,
1483 "home_score": m.get("home_score"),
1484 "away_score": m.get("away_score"),
1485 "match_status": m.get("match_status"),
1486 "age_group": age_group,
1487 "league": league,
1488 "division": division,
1489 "season": season,
1490 }
1491 )
1493 logger.info(
1494 "get_agent_matches.done",
1495 team=team,
1496 age_group=age_group,
1497 count=len(results),
1498 )
1499 return results
1501 def cancel_match(
1502 self,
1503 home_team: str,
1504 away_team: str,
1505 match_date: str,
1506 age_group: str,
1507 league: str,
1508 division: str,
1509 season: str,
1510 ) -> bool:
1511 """Mark a match as cancelled by natural key.
1513 Only cancels matches with no score (home_score IS NULL) to avoid
1514 accidentally cancelling completed matches.
1516 Returns True if a match was found and cancelled, False if not found.
1517 """
1518 # Resolve dimension IDs (same pattern as get_agent_matches)
1519 season_resp = self.client.table("seasons").select("id").eq("name", season).limit(1).execute()
1520 if not season_resp.data:
1521 logger.warning("cancel_match.season_not_found", season=season)
1522 return False
1523 season_id = season_resp.data[0]["id"]
1525 ag_resp = self.client.table("age_groups").select("id").eq("name", age_group).limit(1).execute()
1526 if not ag_resp.data:
1527 logger.warning("cancel_match.age_group_not_found", age_group=age_group)
1528 return False
1529 age_group_id = ag_resp.data[0]["id"]
1531 div_resp = (
1532 self.client.table("divisions")
1533 .select("id, leagues!divisions_league_id_fkey(name)")
1534 .eq("name", division)
1535 .execute()
1536 )
1537 division_id = None
1538 for row in div_resp.data or []:
1539 if row.get("leagues") and row["leagues"].get("name") == league:
1540 division_id = row["id"]
1541 break
1542 if division_id is None:
1543 logger.warning("cancel_match.division_not_found", division=division, league=league)
1544 return False
1546 home_resp = self.client.table("teams").select("id").eq("name", home_team).execute()
1547 home_ids = [r["id"] for r in (home_resp.data or [])]
1548 away_resp = self.client.table("teams").select("id").eq("name", away_team).execute()
1549 away_ids = [r["id"] for r in (away_resp.data or [])]
1550 if not home_ids or not away_ids:
1551 logger.warning("cancel_match.team_not_found", home_team=home_team, away_team=away_team)
1552 return False
1554 or_parts = [
1555 f"and(home_team_id.eq.{hid},away_team_id.eq.{aid})"
1556 for hid in home_ids
1557 for aid in away_ids
1558 ]
1559 resp = (
1560 self.client.table("matches")
1561 .select("id, home_score")
1562 .eq("season_id", season_id)
1563 .eq("age_group_id", age_group_id)
1564 .eq("division_id", division_id)
1565 .eq("match_date", match_date)
1566 .is_("home_score", "null") # safety: never cancel scored matches
1567 .or_(",".join(or_parts))
1568 .limit(1)
1569 .execute()
1570 )
1572 if not resp.data:
1573 logger.warning(
1574 "cancel_match.not_found",
1575 home_team=home_team,
1576 away_team=away_team,
1577 match_date=match_date,
1578 )
1579 return False
1581 internal_id = resp.data[0]["id"]
1582 self.client.table("matches").update({"match_status": "cancelled"}).eq("id", internal_id).execute()
1583 logger.info(
1584 "cancel_match.done",
1585 internal_id=internal_id,
1586 home_team=home_team,
1587 away_team=away_team,
1588 match_date=match_date,
1589 )
1590 return True
1592 # Admin CRUD methods for reference data have been moved to:
1593 # - SeasonDAO (seasons, age_groups)
1594 # - LeagueDAO (leagues, divisions)
1595 # - MatchTypeDAO (match_types)
1596 # Team methods moved to TeamDAO - see dao/team_dao.py
1597 # Club methods moved to ClubDAO - see dao/club_dao.py
1598 # User profile and player methods moved to PlayerDAO - see dao/player_dao.py