Coverage for dao/match_dao.py: 19.03%
491 statements
« prev ^ index » next coverage.py v7.10.6, created at 2026-04-12 14:12 +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 response = (
486 self.client.table("matches")
487 .select("""
488 match_date, match_status, home_score, away_score, scheduled_kickoff,
489 age_group:age_groups(name),
490 division:divisions(name, league_id, leagues:leagues!divisions_league_id_fkey(name))
491 """)
492 .eq("season_id", season_id)
493 .neq("match_status", "cancelled")
494 .execute()
495 )
497 # Group by (age_group, league, division)
498 groups = defaultdict(list)
499 for m in response.data:
500 ag = m["age_group"]["name"] if m.get("age_group") else "Unknown"
501 div_name = m["division"]["name"] if m.get("division") else "Unknown"
502 league_name = (
503 m["division"]["leagues"]["name"] if m.get("division") and m["division"].get("leagues") else "Unknown"
504 )
505 groups[(ag, league_name, div_name)].append(m)
507 # Compute summary per group
508 summaries = []
509 for (ag, league, div), matches in sorted(groups.items()):
510 by_status = defaultdict(int)
511 needs_score = 0
512 needs_kickoff = 0
513 dates = []
514 last_played = None
516 for m in matches:
517 status = m.get("match_status", "scheduled")
518 by_status[status] += 1
519 md = m["match_date"]
520 dates.append(md)
522 if md < today and status in ("scheduled", "tbd") and m.get("home_score") is None:
523 in_window = (not score_from or md >= score_from) and (not score_to or md <= score_to)
524 if in_window: 524 ↛ 527line 524 didn't jump to line 527 because the condition on line 524 was always true
525 needs_score += 1
527 if status in ("scheduled", "tbd") and today <= md <= kickoff_horizon and not m.get("scheduled_kickoff"): 527 ↛ 528line 527 didn't jump to line 528 because the condition on line 527 was never true
528 needs_kickoff += 1
530 if status in ("completed", "forfeit") and (last_played is None or md > last_played):
531 last_played = md
533 summaries.append(
534 {
535 "age_group": ag,
536 "league": league,
537 "division": div,
538 "total": len(matches),
539 "by_status": dict(by_status),
540 "needs_score": needs_score,
541 "needs_kickoff": needs_kickoff,
542 "date_range": {
543 "earliest": min(dates) if dates else None,
544 "latest": max(dates) if dates else None,
545 },
546 "last_played_date": last_played,
547 }
548 )
550 return summaries
552 def get_matches_by_team(
553 self, team_id: int, season_id: int | None = None, age_group_id: int | None = None
554 ) -> list[dict]:
555 """Get all matches for a specific team."""
556 try:
557 query = (
558 self.client.table("matches")
559 .select("""
560 *,
561 home_team:teams!matches_home_team_id_fkey(id, name, club:clubs(id, name, logo_url)),
562 away_team:teams!matches_away_team_id_fkey(id, name, club:clubs(id, name, logo_url)),
563 season:seasons(id, name),
564 age_group:age_groups(id, name),
565 match_type:match_types(id, name),
566 division:divisions(id, name)
567 """)
568 .or_(f"home_team_id.eq.{team_id},away_team_id.eq.{team_id}")
569 )
571 if season_id:
572 query = query.eq("season_id", season_id)
574 if age_group_id:
575 query = query.eq("age_group_id", age_group_id)
577 response = query.order("match_date", desc=True).execute()
579 # Flatten response (same as get_all_matches)
580 matches = []
581 for match in response.data:
582 flat_match = {
583 "id": match["id"],
584 "match_date": match["match_date"],
585 "scheduled_kickoff": match.get("scheduled_kickoff"),
586 "home_team_id": match["home_team_id"],
587 "away_team_id": match["away_team_id"],
588 "home_team_name": match["home_team"]["name"] if match.get("home_team") else "Unknown",
589 "away_team_name": match["away_team"]["name"] if match.get("away_team") else "Unknown",
590 "home_team_club": match["home_team"].get("club") if match.get("home_team") else None,
591 "away_team_club": match["away_team"].get("club") if match.get("away_team") else None,
592 "home_score": match["home_score"],
593 "away_score": match["away_score"],
594 "season_id": match["season_id"],
595 "season_name": match["season"]["name"] if match.get("season") else "Unknown",
596 "age_group_id": match["age_group_id"],
597 "age_group_name": match["age_group"]["name"] if match.get("age_group") else "Unknown",
598 "match_type_id": match["match_type_id"],
599 "match_type_name": match["match_type"]["name"] if match.get("match_type") else "Unknown",
600 "division_id": match.get("division_id"),
601 "division_name": match["division"]["name"] if match.get("division") else "Unknown",
602 "division": match.get("division"), # Include full division object with leagues
603 "match_status": match.get("match_status"),
604 "created_by": match.get("created_by"),
605 "updated_by": match.get("updated_by"),
606 "source": match.get("source", "manual"),
607 "match_id": match.get("match_id"), # External match identifier
608 "created_at": match.get("created_at"),
609 "updated_at": match.get("updated_at"),
610 }
611 matches.append(flat_match)
613 return matches
615 except Exception:
616 logger.exception("Error querying matches by team")
617 return []
619 def get_match_preview(
620 self,
621 home_team_id: int,
622 away_team_id: int,
623 season_id: int | None = None,
624 age_group_id: int | None = None,
625 recent_count: int = 5,
626 ) -> dict:
627 """Get match preview data: recent form, common opponents, and head-to-head history.
629 Args:
630 home_team_id: ID of the home team in the upcoming match
631 away_team_id: ID of the away team in the upcoming match
632 season_id: Season for recent form and common opponents (None = all seasons)
633 age_group_id: Optional filter to restrict to a specific age group
634 recent_count: How many recent matches to return per team
636 Returns:
637 Dict with home_team_recent, away_team_recent, common_opponents, head_to_head
638 """
639 select_str = """
640 *,
641 home_team:teams!matches_home_team_id_fkey(id, name),
642 away_team:teams!matches_away_team_id_fkey(id, name),
643 season:seasons(id, name),
644 age_group:age_groups(id, name),
645 match_type:match_types(id, name),
646 division:divisions(id, name)
647 """
649 def flatten(match: dict) -> dict:
650 return {
651 "id": match["id"],
652 "match_date": match["match_date"],
653 "scheduled_kickoff": match.get("scheduled_kickoff"),
654 "home_team_id": match["home_team_id"],
655 "away_team_id": match["away_team_id"],
656 "home_team_name": match["home_team"]["name"] if match.get("home_team") else "Unknown",
657 "away_team_name": match["away_team"]["name"] if match.get("away_team") else "Unknown",
658 "home_score": match["home_score"],
659 "away_score": match["away_score"],
660 "season_id": match["season_id"],
661 "season_name": match["season"]["name"] if match.get("season") else "Unknown",
662 "age_group_id": match["age_group_id"],
663 "age_group_name": match["age_group"]["name"] if match.get("age_group") else "Unknown",
664 "match_type_id": match["match_type_id"],
665 "match_type_name": match["match_type"]["name"] if match.get("match_type") else "Unknown",
666 "division_id": match.get("division_id"),
667 "division_name": match["division"]["name"] if match.get("division") else "Unknown",
668 "match_status": match.get("match_status"),
669 "source": match.get("source", "manual"),
670 "match_id": match.get("match_id"),
671 "created_at": match.get("created_at"),
672 "updated_at": match.get("updated_at"),
673 }
675 def build_base_query(team_id: int):
676 q = (
677 self.client.table("matches")
678 .select(select_str)
679 .or_(f"home_team_id.eq.{team_id},away_team_id.eq.{team_id}")
680 .in_("match_status", ["completed", "forfeit"])
681 )
682 if season_id:
683 q = q.eq("season_id", season_id)
684 if age_group_id:
685 q = q.eq("age_group_id", age_group_id)
686 return q
688 try:
689 # --- Recent form for each team ---
690 home_resp = build_base_query(home_team_id).order("match_date", desc=True).limit(recent_count).execute()
691 away_resp = build_base_query(away_team_id).order("match_date", desc=True).limit(recent_count).execute()
692 home_recent = [flatten(m) for m in (home_resp.data or [])]
693 away_recent = [flatten(m) for m in (away_resp.data or [])]
695 # --- Common opponents (season-scoped) ---
696 # Fetch all season matches for both teams to find shared opponents
697 home_all_resp = build_base_query(home_team_id).order("match_date", desc=True).execute()
698 away_all_resp = build_base_query(away_team_id).order("match_date", desc=True).execute()
699 home_all = [flatten(m) for m in (home_all_resp.data or [])]
700 away_all = [flatten(m) for m in (away_all_resp.data or [])]
702 def extract_opponents(matches: list[dict], team_id: int) -> dict[int, str]:
703 """Return {opponent_id: opponent_name} excluding the two preview teams."""
704 opps: dict[int, str] = {}
705 for m in matches:
706 if m["home_team_id"] == team_id:
707 opp_id, opp_name = m["away_team_id"], m["away_team_name"]
708 else:
709 opp_id, opp_name = m["home_team_id"], m["home_team_name"]
710 # Exclude head-to-head matches between the two preview teams
711 if opp_id not in (home_team_id, away_team_id):
712 opps[opp_id] = opp_name
713 return opps
715 home_opps = extract_opponents(home_all, home_team_id)
716 away_opps = extract_opponents(away_all, away_team_id)
717 common_ids = sorted(set(home_opps) & set(away_opps), key=lambda i: home_opps[i])
719 common_opponents = []
720 for opp_id in common_ids:
721 home_vs = [m for m in home_all if opp_id in (m["home_team_id"], m["away_team_id"])]
722 away_vs = [m for m in away_all if opp_id in (m["home_team_id"], m["away_team_id"])]
723 common_opponents.append(
724 {
725 "opponent_id": opp_id,
726 "opponent_name": home_opps[opp_id],
727 "home_team_matches": home_vs,
728 "away_team_matches": away_vs,
729 }
730 )
732 # --- Head-to-head history (all seasons, cross-season) ---
733 # Fetch all matches for home_team and filter to those against away_team
734 h2h_query = (
735 self.client.table("matches")
736 .select(select_str)
737 .or_(f"home_team_id.eq.{home_team_id},away_team_id.eq.{home_team_id}")
738 .in_("match_status", ["completed", "forfeit"])
739 )
740 if age_group_id:
741 h2h_query = h2h_query.eq("age_group_id", age_group_id)
742 h2h_resp = h2h_query.order("match_date", desc=True).execute()
743 head_to_head = [
744 flatten(m)
745 for m in (h2h_resp.data or [])
746 if away_team_id in (m["home_team_id"], m["away_team_id"])
747 ]
749 return {
750 "home_team_id": home_team_id,
751 "away_team_id": away_team_id,
752 "home_team_recent": home_recent,
753 "away_team_recent": away_recent,
754 "common_opponents": common_opponents,
755 "head_to_head": head_to_head,
756 }
758 except Exception:
759 logger.exception("Error building match preview")
760 return {
761 "home_team_id": home_team_id,
762 "away_team_id": away_team_id,
763 "home_team_recent": [],
764 "away_team_recent": [],
765 "common_opponents": [],
766 "head_to_head": [],
767 }
769 @invalidates_cache(MATCHES_CACHE_PATTERN)
770 def add_match(
771 self,
772 home_team_id: int,
773 away_team_id: int,
774 match_date: str,
775 home_score: int,
776 away_score: int,
777 season_id: int,
778 age_group_id: int,
779 match_type_id: int,
780 division_id: int | None = None,
781 status: str | None = "scheduled",
782 created_by: str | None = None,
783 source: str = "manual",
784 external_match_id: str | None = None,
785 scheduled_kickoff: str | None = None,
786 ) -> bool:
787 """Add a new match with audit trail and optional external match_id."""
788 try:
789 data = {
790 "match_date": match_date,
791 "home_team_id": home_team_id,
792 "away_team_id": away_team_id,
793 "home_score": home_score,
794 "away_score": away_score,
795 "season_id": season_id,
796 "age_group_id": age_group_id,
797 "match_type_id": match_type_id,
798 "source": source,
799 }
801 # Add optional fields
802 if division_id:
803 data["division_id"] = division_id
804 if status:
805 data["match_status"] = status # Map status to match_status column
806 if created_by:
807 data["created_by"] = created_by
808 if external_match_id:
809 data["match_id"] = external_match_id
810 if scheduled_kickoff:
811 data["scheduled_kickoff"] = scheduled_kickoff
813 response = self.client.table("matches").insert(data).execute()
815 return bool(response.data)
817 except APIError as e:
818 error_dict = e.args[0] if e.args else {}
819 if error_dict.get("code") == "23505":
820 # Duplicate key violation
821 logger.warning(
822 "Duplicate match detected",
823 home_team_id=home_team_id,
824 away_team_id=away_team_id,
825 match_date=match_date,
826 details=error_dict.get("details"),
827 )
828 raise DuplicateRecordError(
829 message="A match with these teams on this date already exists",
830 details=error_dict.get("details"),
831 ) from e
832 logger.exception("Error adding match")
833 return False
834 except Exception:
835 logger.exception("Error adding match")
836 return False
838 @invalidates_cache(MATCHES_CACHE_PATTERN)
839 def add_match_with_external_id(
840 self,
841 home_team_id: int,
842 away_team_id: int,
843 match_date: str,
844 home_score: int,
845 away_score: int,
846 season_id: int,
847 age_group_id: int,
848 match_type_id: int,
849 external_match_id: str,
850 division_id: int | None = None,
851 created_by: str | None = None,
852 source: str = "match-scraper",
853 ) -> bool:
854 """Add a new match with external match_id and audit trail.
856 This is a convenience wrapper around add_match() for backwards compatibility.
857 Consider using add_match() directly with external_match_id parameter.
858 """
859 return self.add_match(
860 home_team_id=home_team_id,
861 away_team_id=away_team_id,
862 match_date=match_date,
863 home_score=home_score,
864 away_score=away_score,
865 season_id=season_id,
866 age_group_id=age_group_id,
867 match_type_id=match_type_id,
868 division_id=division_id,
869 created_by=created_by,
870 source=source,
871 external_match_id=external_match_id,
872 )
874 @invalidates_cache(MATCHES_CACHE_PATTERN, PLAYOFF_CACHE_PATTERN, TOURNAMENTS_CACHE_PATTERN)
875 def update_match(
876 self,
877 match_id: int,
878 home_team_id: int,
879 away_team_id: int,
880 match_date: str,
881 home_score: int,
882 away_score: int,
883 season_id: int,
884 age_group_id: int,
885 match_type_id: int,
886 division_id: int | None = None,
887 status: str | None = None,
888 updated_by: str | None = None,
889 external_match_id: str | None = None,
890 scheduled_kickoff: str | None = None,
891 ) -> dict | None:
892 """Update an existing match with audit trail and optional external match_id.
894 Returns the updated match data to avoid read-after-write consistency issues.
895 """
896 try:
897 data = {
898 "match_date": match_date,
899 "home_team_id": home_team_id,
900 "away_team_id": away_team_id,
901 "home_score": home_score,
902 "away_score": away_score,
903 "season_id": season_id,
904 "age_group_id": age_group_id,
905 "match_type_id": match_type_id,
906 "division_id": division_id,
907 }
909 # Add optional fields
910 if status:
911 data["match_status"] = status
912 if updated_by:
913 data["updated_by"] = updated_by
914 if external_match_id is not None: # Allow explicit None to clear match_id
915 data["match_id"] = external_match_id
916 if scheduled_kickoff is not None: # Allow explicit None to clear scheduled_kickoff
917 data["scheduled_kickoff"] = scheduled_kickoff
919 # Execute update
920 response = self.client.table("matches").update(data).eq("id", match_id).execute()
922 # Check if update actually affected any rows
923 if not response.data or len(response.data) == 0:
924 logger.warning("Update match failed - no rows affected", match_id=match_id)
925 # Return None to signal failure
926 return None
928 # Clear cache BEFORE re-fetch to avoid returning stale cached data.
929 # The @invalidates_cache decorator clears AFTER the function returns,
930 # but get_match_by_id uses @dao_cache and would hit stale cache.
931 clear_cache(MATCHES_CACHE_PATTERN)
932 clear_cache(PLAYOFF_CACHE_PATTERN)
933 clear_cache(TOURNAMENTS_CACHE_PATTERN)
935 # Get the updated match to return with full relations
936 return self.get_match_by_id(match_id)
938 except Exception:
939 logger.exception("Error updating match")
940 return None
942 @dao_cache("matches:by_id:{match_id}")
943 def get_match_by_id(self, match_id: int) -> dict | None:
944 """Get a single match by ID with all related data."""
945 try:
946 response = (
947 self.client.table("matches")
948 .select("""
949 *,
950 home_team:teams!matches_home_team_id_fkey(
951 id, name,
952 club:clubs(id, name, logo_url, primary_color, secondary_color),
953 division:divisions(id, leagues(sport_type))
954 ),
955 away_team:teams!matches_away_team_id_fkey(
956 id, name,
957 club:clubs(id, name, logo_url, primary_color, secondary_color)
958 ),
959 season:seasons(id, name),
960 age_group:age_groups(id, name),
961 match_type:match_types(id, name),
962 division:divisions(id, name, leagues(id, name, sport_type))
963 """)
964 .eq("id", match_id)
965 .execute()
966 )
968 if response.data and len(response.data) > 0:
969 match = response.data[0]
970 # Extract sport_type from division -> leagues (match division)
971 division = match.get("division") or {}
972 league = division.get("leagues") or {}
973 sport_type = league.get("sport_type")
975 # Fallback: get sport_type from home team's division -> league
976 if not sport_type:
977 home_team = match.get("home_team") or {}
978 team_div = home_team.get("division") or {}
979 team_league = team_div.get("leagues") or {}
980 sport_type = team_league.get("sport_type", "soccer")
982 # Flatten the response to match the format from get_all_matches
983 flat_match = {
984 "id": match["id"],
985 "match_date": match["match_date"],
986 "scheduled_kickoff": match.get("scheduled_kickoff"),
987 "home_team_id": match["home_team_id"],
988 "away_team_id": match["away_team_id"],
989 "home_team_name": match["home_team"]["name"] if match.get("home_team") else "Unknown",
990 "away_team_name": match["away_team"]["name"] if match.get("away_team") else "Unknown",
991 "home_team_club": match["home_team"].get("club") if match.get("home_team") else None,
992 "away_team_club": match["away_team"].get("club") if match.get("away_team") else None,
993 "home_score": match["home_score"],
994 "away_score": match["away_score"],
995 "season_id": match["season_id"],
996 "season_name": match["season"]["name"] if match.get("season") else "Unknown",
997 "age_group_id": match["age_group_id"],
998 "age_group_name": match["age_group"]["name"] if match.get("age_group") else "Unknown",
999 "match_type_id": match["match_type_id"],
1000 "match_type_name": match["match_type"]["name"] if match.get("match_type") else "Unknown",
1001 "division_id": match.get("division_id"),
1002 "division_name": match["division"]["name"] if match.get("division") else "Unknown",
1003 "division": match.get("division"), # Include full division object with leagues
1004 "sport_type": sport_type,
1005 "match_status": match.get("match_status"),
1006 "created_by": match.get("created_by"),
1007 "updated_by": match.get("updated_by"),
1008 "source": match.get("source", "manual"),
1009 "match_id": match.get("match_id"), # External match identifier
1010 "created_at": match["created_at"],
1011 "updated_at": match["updated_at"],
1012 # Live match clock fields
1013 "kickoff_time": match.get("kickoff_time"),
1014 "halftime_start": match.get("halftime_start"),
1015 "second_half_start": match.get("second_half_start"),
1016 "match_end_time": match.get("match_end_time"),
1017 "half_duration": match.get("half_duration", 45),
1018 }
1019 return flat_match
1020 else:
1021 return None
1023 except Exception:
1024 logger.exception("Error retrieving match by ID")
1025 return None
1027 @invalidates_cache(MATCHES_CACHE_PATTERN, TOURNAMENTS_CACHE_PATTERN)
1028 def delete_match(self, match_id: int) -> bool:
1029 """Delete a match."""
1030 try:
1031 self.client.table("matches").delete().eq("id", match_id).execute()
1033 return True # Supabase delete returns empty data even on success
1035 except Exception:
1036 logger.exception("Error deleting match")
1037 return False
1039 @dao_cache("matches:table:{season_id}:{age_group_id}:{division_id}:{match_type}")
1040 def get_league_table(
1041 self,
1042 season_id: int | None = None,
1043 age_group_id: int | None = None,
1044 division_id: int | None = None,
1045 match_type: str = "League",
1046 ) -> list[dict]:
1047 """
1048 Generate league table with optional filters.
1050 This method fetches matches from the database and delegates
1051 the standings calculation to pure functions in dao/standings.py.
1053 Args:
1054 season_id: Filter by season
1055 age_group_id: Filter by age group
1056 division_id: Filter by division
1057 match_type: Filter by match type name (default: "League")
1059 Returns:
1060 List of team standings sorted by points, goal difference, goals scored
1061 """
1062 try:
1063 logger.info(
1064 "generating league table from database",
1065 season_id=season_id,
1066 age_group_id=age_group_id,
1067 division_id=division_id,
1068 match_type=match_type,
1069 )
1070 # Fetch matches from database
1071 matches = self._fetch_matches_for_standings(season_id, age_group_id, division_id)
1073 # Apply filters using pure functions
1074 matches = filter_by_match_type(matches, match_type)
1075 if division_id:
1076 matches = filter_same_division_matches(matches, division_id)
1077 matches = filter_completed_matches(matches)
1079 # Calculate standings using pure function (with form + movement)
1080 return calculate_standings_with_extras(matches)
1082 except Exception:
1083 logger.exception("Error generating league table")
1084 return []
1086 def _fetch_matches_for_standings(
1087 self,
1088 season_id: int | None = None,
1089 age_group_id: int | None = None,
1090 division_id: int | None = None,
1091 ) -> list[dict]:
1092 """
1093 Fetch matches from database for standings calculation.
1095 This is a thin data access method that only handles the query.
1096 All business logic is in pure functions.
1098 Args:
1099 season_id: Filter by season
1100 age_group_id: Filter by age group
1101 division_id: Filter by division
1103 Returns:
1104 List of match dictionaries from database
1105 """
1106 logger.info(
1107 "fetching matches for standings calculation from database",
1108 season_id=season_id,
1109 age_group_id=age_group_id,
1110 division_id=division_id,
1111 )
1112 query = self.client.table("matches").select("""
1113 *,
1114 home_team:teams!matches_home_team_id_fkey(id, name, division_id, club:clubs(id, name, logo_url)),
1115 away_team:teams!matches_away_team_id_fkey(id, name, division_id, club:clubs(id, name, logo_url)),
1116 match_type:match_types(id, name)
1117 """)
1119 # Apply database-level filters
1120 if season_id:
1121 query = query.eq("season_id", season_id)
1122 if age_group_id:
1123 query = query.eq("age_group_id", age_group_id)
1124 if division_id:
1125 query = query.eq("division_id", division_id)
1127 response = query.execute()
1128 return response.data
1130 # === Live Match Methods ===
1132 def get_live_matches(self) -> list[dict]:
1133 """Get all matches with status 'live'.
1135 Returns minimal data for the LIVE tab polling.
1136 """
1137 try:
1138 response = (
1139 self.client.table("matches")
1140 .select("""
1141 id,
1142 match_status,
1143 match_date,
1144 home_score,
1145 away_score,
1146 kickoff_time,
1147 home_team:teams!matches_home_team_id_fkey(id, name),
1148 away_team:teams!matches_away_team_id_fkey(id, name)
1149 """)
1150 .eq("match_status", "live")
1151 .execute()
1152 )
1154 # Flatten the response
1155 result = []
1156 for match in response.data or []:
1157 result.append(
1158 {
1159 "match_id": match["id"],
1160 "match_status": match["match_status"],
1161 "match_date": match["match_date"],
1162 "home_score": match["home_score"],
1163 "away_score": match["away_score"],
1164 "kickoff_time": match.get("kickoff_time"),
1165 "home_team_name": match["home_team"]["name"] if match.get("home_team") else "Unknown",
1166 "away_team_name": match["away_team"]["name"] if match.get("away_team") else "Unknown",
1167 }
1168 )
1169 return result
1171 except Exception:
1172 logger.exception("Error getting live matches")
1173 return []
1175 def get_live_match_state(self, match_id: int) -> dict | None:
1176 """Get full live match state including clock timestamps.
1178 Returns match data with clock fields for the live match view.
1179 """
1180 try:
1181 response = (
1182 self.client.table("matches")
1183 .select("""
1184 *,
1185 home_team:teams!matches_home_team_id_fkey(
1186 id, name,
1187 club:clubs(id, logo_url),
1188 division:divisions(id, leagues(sport_type))
1189 ),
1190 away_team:teams!matches_away_team_id_fkey(id, name, club:clubs(id, logo_url)),
1191 age_group:age_groups(id, name),
1192 match_type:match_types(id, name),
1193 division:divisions(id, name, leagues(id, name, sport_type))
1194 """)
1195 .eq("id", match_id)
1196 .single()
1197 .execute()
1198 )
1200 if not response.data:
1201 return None
1203 match = response.data
1204 # Extract club logo URLs from nested team -> club relationships
1205 home_team = match.get("home_team") or {}
1206 away_team = match.get("away_team") or {}
1207 home_club = home_team.get("club") or {}
1208 away_club = away_team.get("club") or {}
1210 # Extract sport_type from division -> leagues
1211 division = match.get("division") or {}
1212 league = division.get("leagues") or {}
1213 sport_type = league.get("sport_type")
1215 # Fallback: get sport_type from home team's division -> league
1216 if not sport_type:
1217 team_div = home_team.get("division") or {}
1218 team_league = team_div.get("leagues") or {}
1219 sport_type = team_league.get("sport_type", "soccer")
1221 return {
1222 "match_id": match["id"],
1223 "match_status": match.get("match_status"),
1224 "match_date": match["match_date"],
1225 "scheduled_kickoff": match.get("scheduled_kickoff"),
1226 "home_score": match["home_score"],
1227 "away_score": match["away_score"],
1228 "kickoff_time": match.get("kickoff_time"),
1229 "halftime_start": match.get("halftime_start"),
1230 "second_half_start": match.get("second_half_start"),
1231 "match_end_time": match.get("match_end_time"),
1232 "half_duration": match.get("half_duration", 45),
1233 "season_id": match.get("season_id"),
1234 "home_team_id": match["home_team_id"],
1235 "home_team_name": home_team.get("name", "Unknown"),
1236 "home_team_logo": home_club.get("logo_url"),
1237 "away_team_id": match["away_team_id"],
1238 "away_team_name": away_team.get("name", "Unknown"),
1239 "away_team_logo": away_club.get("logo_url"),
1240 "age_group_name": match["age_group"]["name"] if match.get("age_group") else None,
1241 "match_type_name": match["match_type"]["name"] if match.get("match_type") else None,
1242 "division_name": match["division"]["name"] if match.get("division") else None,
1243 "sport_type": sport_type,
1244 }
1246 except Exception:
1247 logger.exception("Error getting live match state", match_id=match_id)
1248 return None
1250 @invalidates_cache(MATCHES_CACHE_PATTERN, TOURNAMENTS_CACHE_PATTERN)
1251 def update_match_clock(
1252 self,
1253 match_id: int,
1254 action: str,
1255 updated_by: str | None = None,
1256 half_duration: int | None = None,
1257 ) -> dict | None:
1258 """Update match clock based on action.
1260 Args:
1261 match_id: The match to update
1262 action: Clock action - 'start_first_half', 'start_halftime',
1263 'start_second_half', 'end_match'
1264 updated_by: UUID of user performing the action
1265 half_duration: Duration of each half in minutes (only for start_first_half)
1267 Returns:
1268 Updated match state or None on error
1269 """
1270 from datetime import datetime
1272 try:
1273 now = datetime.now(UTC).isoformat()
1274 data: dict = {"updated_by": updated_by} if updated_by else {}
1276 if action == "start_first_half":
1277 # Start the match - set kickoff time and status to live
1278 data["kickoff_time"] = now
1279 data["match_status"] = "live"
1280 # Set half duration if provided (default is 45)
1281 if half_duration:
1282 data["half_duration"] = half_duration
1283 elif action == "start_halftime":
1284 # Mark halftime started
1285 data["halftime_start"] = now
1286 elif action == "start_second_half":
1287 # Start second half
1288 data["second_half_start"] = now
1289 elif action == "end_match":
1290 # End the match
1291 data["match_end_time"] = now
1292 data["match_status"] = "completed"
1293 else:
1294 logger.warning("Invalid clock action", action=action)
1295 return None
1297 response = self.client.table("matches").update(data).eq("id", match_id).execute()
1299 if not response.data:
1300 logger.warning("Clock update failed - no rows affected", match_id=match_id)
1301 return None
1303 logger.info(
1304 "match_clock_updated",
1305 match_id=match_id,
1306 action=action,
1307 )
1309 # Return updated state
1310 return self.get_live_match_state(match_id)
1312 except Exception:
1313 logger.exception("Error updating match clock", match_id=match_id, action=action)
1314 return None
1316 @invalidates_cache(MATCHES_CACHE_PATTERN, TOURNAMENTS_CACHE_PATTERN)
1317 def update_match_score(
1318 self,
1319 match_id: int,
1320 home_score: int,
1321 away_score: int,
1322 updated_by: str | None = None,
1323 ) -> dict | None:
1324 """Update match score (used when posting a goal).
1326 Args:
1327 match_id: The match to update
1328 home_score: New home team score
1329 away_score: New away team score
1330 updated_by: UUID of user performing the action
1332 Returns:
1333 Updated match state or None on error
1334 """
1335 try:
1336 data = {
1337 "home_score": home_score,
1338 "away_score": away_score,
1339 }
1340 if updated_by:
1341 data["updated_by"] = updated_by
1343 response = self.client.table("matches").update(data).eq("id", match_id).execute()
1345 if not response.data:
1346 logger.warning("Score update failed - no rows affected", match_id=match_id)
1347 return None
1349 logger.info(
1350 "match_score_updated",
1351 match_id=match_id,
1352 home_score=home_score,
1353 away_score=away_score,
1354 )
1356 return self.get_live_match_state(match_id)
1358 except Exception:
1359 logger.exception("Error updating match score", match_id=match_id)
1360 return None
1362 def get_agent_matches(
1363 self,
1364 team: str,
1365 age_group: str,
1366 league: str,
1367 division: str,
1368 season: str,
1369 start_date: str | None = None,
1370 end_date: str | None = None,
1371 ) -> list[dict]:
1372 """Get individual match records for the audit agent's comparison step.
1374 Returns matches involving the specified team (home or away) in the
1375 given age-group/league/division/season. Used by GET /api/agent/matches.
1377 Args:
1378 team: MT canonical team name, e.g. "IFA".
1379 age_group: e.g. "U14".
1380 league: e.g. "Homegrown".
1381 division: e.g. "Northeast".
1382 season: e.g. "2025-2026".
1383 start_date: If set, only return matches on or after this date (YYYY-MM-DD).
1384 end_date: If set, only return matches on or before this date (YYYY-MM-DD).
1386 Returns:
1387 List of match dicts with fields the audit comparator expects.
1388 """
1389 # Resolve IDs for the reference dimensions
1390 season_resp = self.client.table("seasons").select("id").eq("name", season).limit(1).execute()
1391 if not season_resp.data:
1392 logger.warning("get_agent_matches.season_not_found", season=season)
1393 return []
1394 season_id = season_resp.data[0]["id"]
1396 ag_resp = self.client.table("age_groups").select("id").eq("name", age_group).limit(1).execute()
1397 if not ag_resp.data:
1398 logger.warning("get_agent_matches.age_group_not_found", age_group=age_group)
1399 return []
1400 age_group_id = ag_resp.data[0]["id"]
1402 # Resolve division_id via league join
1403 div_resp = (
1404 self.client.table("divisions")
1405 .select("id, leagues!divisions_league_id_fkey(name)")
1406 .eq("name", division)
1407 .execute()
1408 )
1409 division_id = None
1410 for row in div_resp.data or []:
1411 if row.get("leagues") and row["leagues"].get("name") == league:
1412 division_id = row["id"]
1413 break
1414 if division_id is None:
1415 logger.warning("get_agent_matches.division_not_found", division=division, league=league)
1416 return []
1418 # Resolve team IDs matching the given name
1419 team_resp = self.client.table("teams").select("id").eq("name", team).execute()
1420 team_ids = [r["id"] for r in (team_resp.data or [])]
1421 if not team_ids:
1422 logger.warning("get_agent_matches.team_not_found", team=team)
1423 return []
1425 # Build OR filter for home/away team membership
1426 or_filter = ",".join(
1427 [f"home_team_id.eq.{tid}" for tid in team_ids] + [f"away_team_id.eq.{tid}" for tid in team_ids]
1428 )
1430 try:
1431 query = (
1432 self.client.table("matches")
1433 .select(
1434 "match_id, match_date, scheduled_kickoff, home_score, away_score, match_status, "
1435 "home_team:teams!matches_home_team_id_fkey(name), "
1436 "away_team:teams!matches_away_team_id_fkey(name)"
1437 )
1438 .eq("season_id", season_id)
1439 .eq("age_group_id", age_group_id)
1440 .eq("division_id", division_id)
1441 .neq("match_status", "cancelled")
1442 )
1443 if start_date:
1444 query = query.gte("match_date", start_date)
1445 if end_date:
1446 query = query.lte("match_date", end_date)
1447 response = query.or_(or_filter).order("match_date", desc=False).execute()
1448 except Exception:
1449 logger.exception("get_agent_matches.query_error", team=team)
1450 return []
1452 results = []
1453 for m in response.data or []:
1454 # Format match_time as "HH:MM" from scheduled_kickoff (UTC)
1455 match_time = None
1456 if m.get("scheduled_kickoff"):
1457 try:
1458 from datetime import datetime
1460 kt = datetime.fromisoformat(m["scheduled_kickoff"].replace("Z", "+00:00"))
1461 if kt.hour or kt.minute:
1462 match_time = kt.strftime("%H:%M")
1463 except (ValueError, AttributeError):
1464 pass
1466 results.append(
1467 {
1468 "external_match_id": m.get("match_id"),
1469 "home_team": m["home_team"]["name"] if m.get("home_team") else None,
1470 "away_team": m["away_team"]["name"] if m.get("away_team") else None,
1471 "match_date": m["match_date"],
1472 "match_time": match_time,
1473 "home_score": m.get("home_score"),
1474 "away_score": m.get("away_score"),
1475 "match_status": m.get("match_status"),
1476 "age_group": age_group,
1477 "league": league,
1478 "division": division,
1479 "season": season,
1480 }
1481 )
1483 logger.info(
1484 "get_agent_matches.done",
1485 team=team,
1486 age_group=age_group,
1487 count=len(results),
1488 )
1489 return results
1491 def cancel_match(
1492 self,
1493 home_team: str,
1494 away_team: str,
1495 match_date: str,
1496 age_group: str,
1497 league: str,
1498 division: str,
1499 season: str,
1500 ) -> bool:
1501 """Mark a match as cancelled by natural key.
1503 Only cancels matches with no score (home_score IS NULL) to avoid
1504 accidentally cancelling completed matches.
1506 Returns True if a match was found and cancelled, False if not found.
1507 """
1508 # Resolve dimension IDs (same pattern as get_agent_matches)
1509 season_resp = self.client.table("seasons").select("id").eq("name", season).limit(1).execute()
1510 if not season_resp.data:
1511 logger.warning("cancel_match.season_not_found", season=season)
1512 return False
1513 season_id = season_resp.data[0]["id"]
1515 ag_resp = self.client.table("age_groups").select("id").eq("name", age_group).limit(1).execute()
1516 if not ag_resp.data:
1517 logger.warning("cancel_match.age_group_not_found", age_group=age_group)
1518 return False
1519 age_group_id = ag_resp.data[0]["id"]
1521 div_resp = (
1522 self.client.table("divisions")
1523 .select("id, leagues!divisions_league_id_fkey(name)")
1524 .eq("name", division)
1525 .execute()
1526 )
1527 division_id = None
1528 for row in div_resp.data or []:
1529 if row.get("leagues") and row["leagues"].get("name") == league:
1530 division_id = row["id"]
1531 break
1532 if division_id is None:
1533 logger.warning("cancel_match.division_not_found", division=division, league=league)
1534 return False
1536 home_resp = self.client.table("teams").select("id").eq("name", home_team).execute()
1537 home_ids = [r["id"] for r in (home_resp.data or [])]
1538 away_resp = self.client.table("teams").select("id").eq("name", away_team).execute()
1539 away_ids = [r["id"] for r in (away_resp.data or [])]
1540 if not home_ids or not away_ids:
1541 logger.warning("cancel_match.team_not_found", home_team=home_team, away_team=away_team)
1542 return False
1544 or_parts = [
1545 f"and(home_team_id.eq.{hid},away_team_id.eq.{aid})"
1546 for hid in home_ids
1547 for aid in away_ids
1548 ]
1549 resp = (
1550 self.client.table("matches")
1551 .select("id, home_score")
1552 .eq("season_id", season_id)
1553 .eq("age_group_id", age_group_id)
1554 .eq("division_id", division_id)
1555 .eq("match_date", match_date)
1556 .is_("home_score", "null") # safety: never cancel scored matches
1557 .or_(",".join(or_parts))
1558 .limit(1)
1559 .execute()
1560 )
1562 if not resp.data:
1563 logger.warning(
1564 "cancel_match.not_found",
1565 home_team=home_team,
1566 away_team=away_team,
1567 match_date=match_date,
1568 )
1569 return False
1571 internal_id = resp.data[0]["id"]
1572 self.client.table("matches").update({"match_status": "cancelled"}).eq("id", internal_id).execute()
1573 logger.info(
1574 "cancel_match.done",
1575 internal_id=internal_id,
1576 home_team=home_team,
1577 away_team=away_team,
1578 match_date=match_date,
1579 )
1580 return True
1582 # Admin CRUD methods for reference data have been moved to:
1583 # - SeasonDAO (seasons, age_groups)
1584 # - LeagueDAO (leagues, divisions)
1585 # - MatchTypeDAO (match_types)
1586 # Team methods moved to TeamDAO - see dao/team_dao.py
1587 # Club methods moved to ClubDAO - see dao/club_dao.py
1588 # User profile and player methods moved to PlayerDAO - see dao/player_dao.py