Coverage for dao/match_dao.py: 20.36%
497 statements
« prev ^ index » next coverage.py v7.10.6, created at 2026-04-15 13:02 +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, all age groups) ---
743 # Intentionally NOT filtered by season or age group — teams age up each
744 # year so cross-age-group history is meaningful and expected.
745 h2h_query = (
746 self.client.table("matches")
747 .select(select_str)
748 .or_(f"home_team_id.eq.{home_team_id},away_team_id.eq.{home_team_id}")
749 .in_("match_status", ["completed", "forfeit"])
750 )
751 h2h_resp = h2h_query.order("match_date", desc=True).execute()
752 head_to_head = [
753 flatten(m)
754 for m in (h2h_resp.data or [])
755 if away_team_id in (m["home_team_id"], m["away_team_id"])
756 ]
758 return {
759 "home_team_id": home_team_id,
760 "away_team_id": away_team_id,
761 "home_team_recent": home_recent,
762 "away_team_recent": away_recent,
763 "common_opponents": common_opponents,
764 "head_to_head": head_to_head,
765 }
767 except Exception:
768 logger.exception("Error building match preview")
769 return {
770 "home_team_id": home_team_id,
771 "away_team_id": away_team_id,
772 "home_team_recent": [],
773 "away_team_recent": [],
774 "common_opponents": [],
775 "head_to_head": [],
776 }
778 @invalidates_cache(MATCHES_CACHE_PATTERN)
779 def add_match(
780 self,
781 home_team_id: int,
782 away_team_id: int,
783 match_date: str,
784 home_score: int,
785 away_score: int,
786 season_id: int,
787 age_group_id: int,
788 match_type_id: int,
789 division_id: int | None = None,
790 status: str | None = "scheduled",
791 created_by: str | None = None,
792 source: str = "manual",
793 external_match_id: str | None = None,
794 scheduled_kickoff: str | None = None,
795 ) -> bool:
796 """Add a new match with audit trail and optional external match_id."""
797 try:
798 data = {
799 "match_date": match_date,
800 "home_team_id": home_team_id,
801 "away_team_id": away_team_id,
802 "home_score": home_score,
803 "away_score": away_score,
804 "season_id": season_id,
805 "age_group_id": age_group_id,
806 "match_type_id": match_type_id,
807 "source": source,
808 }
810 # Add optional fields
811 if division_id:
812 data["division_id"] = division_id
813 if status:
814 data["match_status"] = status # Map status to match_status column
815 if created_by:
816 data["created_by"] = created_by
817 if external_match_id:
818 data["match_id"] = external_match_id
819 if scheduled_kickoff:
820 data["scheduled_kickoff"] = scheduled_kickoff
822 response = self.client.table("matches").insert(data).execute()
824 return bool(response.data)
826 except APIError as e:
827 error_dict = e.args[0] if e.args else {}
828 if error_dict.get("code") == "23505":
829 # Duplicate key violation
830 logger.warning(
831 "Duplicate match detected",
832 home_team_id=home_team_id,
833 away_team_id=away_team_id,
834 match_date=match_date,
835 details=error_dict.get("details"),
836 )
837 raise DuplicateRecordError(
838 message="A match with these teams on this date already exists",
839 details=error_dict.get("details"),
840 ) from e
841 logger.exception("Error adding match")
842 return False
843 except Exception:
844 logger.exception("Error adding match")
845 return False
847 @invalidates_cache(MATCHES_CACHE_PATTERN)
848 def add_match_with_external_id(
849 self,
850 home_team_id: int,
851 away_team_id: int,
852 match_date: str,
853 home_score: int,
854 away_score: int,
855 season_id: int,
856 age_group_id: int,
857 match_type_id: int,
858 external_match_id: str,
859 division_id: int | None = None,
860 created_by: str | None = None,
861 source: str = "match-scraper",
862 ) -> bool:
863 """Add a new match with external match_id and audit trail.
865 This is a convenience wrapper around add_match() for backwards compatibility.
866 Consider using add_match() directly with external_match_id parameter.
867 """
868 return self.add_match(
869 home_team_id=home_team_id,
870 away_team_id=away_team_id,
871 match_date=match_date,
872 home_score=home_score,
873 away_score=away_score,
874 season_id=season_id,
875 age_group_id=age_group_id,
876 match_type_id=match_type_id,
877 division_id=division_id,
878 created_by=created_by,
879 source=source,
880 external_match_id=external_match_id,
881 )
883 @invalidates_cache(MATCHES_CACHE_PATTERN, PLAYOFF_CACHE_PATTERN, TOURNAMENTS_CACHE_PATTERN)
884 def update_match(
885 self,
886 match_id: int,
887 home_team_id: int,
888 away_team_id: int,
889 match_date: str,
890 home_score: int,
891 away_score: int,
892 season_id: int,
893 age_group_id: int,
894 match_type_id: int,
895 division_id: int | None = None,
896 status: str | None = None,
897 updated_by: str | None = None,
898 external_match_id: str | None = None,
899 scheduled_kickoff: str | None = None,
900 ) -> dict | None:
901 """Update an existing match with audit trail and optional external match_id.
903 Returns the updated match data to avoid read-after-write consistency issues.
904 """
905 try:
906 data = {
907 "match_date": match_date,
908 "home_team_id": home_team_id,
909 "away_team_id": away_team_id,
910 "home_score": home_score,
911 "away_score": away_score,
912 "season_id": season_id,
913 "age_group_id": age_group_id,
914 "match_type_id": match_type_id,
915 "division_id": division_id,
916 }
918 # Add optional fields
919 if status:
920 data["match_status"] = status
921 if updated_by:
922 data["updated_by"] = updated_by
923 if external_match_id is not None: # Allow explicit None to clear match_id
924 data["match_id"] = external_match_id
925 if scheduled_kickoff is not None: # Allow explicit None to clear scheduled_kickoff
926 data["scheduled_kickoff"] = scheduled_kickoff
928 # Execute update
929 response = self.client.table("matches").update(data).eq("id", match_id).execute()
931 # Check if update actually affected any rows
932 if not response.data or len(response.data) == 0:
933 logger.warning("Update match failed - no rows affected", match_id=match_id)
934 # Return None to signal failure
935 return None
937 # Clear cache BEFORE re-fetch to avoid returning stale cached data.
938 # The @invalidates_cache decorator clears AFTER the function returns,
939 # but get_match_by_id uses @dao_cache and would hit stale cache.
940 clear_cache(MATCHES_CACHE_PATTERN)
941 clear_cache(PLAYOFF_CACHE_PATTERN)
942 clear_cache(TOURNAMENTS_CACHE_PATTERN)
944 # Get the updated match to return with full relations
945 return self.get_match_by_id(match_id)
947 except Exception:
948 logger.exception("Error updating match")
949 return None
951 @dao_cache("matches:by_id:{match_id}")
952 def get_match_by_id(self, match_id: int) -> dict | None:
953 """Get a single match by ID with all related data."""
954 try:
955 response = (
956 self.client.table("matches")
957 .select("""
958 *,
959 home_team:teams!matches_home_team_id_fkey(
960 id, name,
961 club:clubs(id, name, logo_url, primary_color, secondary_color),
962 division:divisions(id, leagues(sport_type))
963 ),
964 away_team:teams!matches_away_team_id_fkey(
965 id, name,
966 club:clubs(id, name, logo_url, primary_color, secondary_color)
967 ),
968 season:seasons(id, name),
969 age_group:age_groups(id, name),
970 match_type:match_types(id, name),
971 division:divisions(id, name, leagues(id, name, sport_type))
972 """)
973 .eq("id", match_id)
974 .execute()
975 )
977 if response.data and len(response.data) > 0:
978 match = response.data[0]
979 # Extract sport_type from division -> leagues (match division)
980 division = match.get("division") or {}
981 league = division.get("leagues") or {}
982 sport_type = league.get("sport_type")
984 # Fallback: get sport_type from home team's division -> league
985 if not sport_type:
986 home_team = match.get("home_team") or {}
987 team_div = home_team.get("division") or {}
988 team_league = team_div.get("leagues") or {}
989 sport_type = team_league.get("sport_type", "soccer")
991 # Flatten the response to match the format from get_all_matches
992 flat_match = {
993 "id": match["id"],
994 "match_date": match["match_date"],
995 "scheduled_kickoff": match.get("scheduled_kickoff"),
996 "home_team_id": match["home_team_id"],
997 "away_team_id": match["away_team_id"],
998 "home_team_name": match["home_team"]["name"] if match.get("home_team") else "Unknown",
999 "away_team_name": match["away_team"]["name"] if match.get("away_team") else "Unknown",
1000 "home_team_club": match["home_team"].get("club") if match.get("home_team") else None,
1001 "away_team_club": match["away_team"].get("club") if match.get("away_team") else None,
1002 "home_score": match["home_score"],
1003 "away_score": match["away_score"],
1004 "season_id": match["season_id"],
1005 "season_name": match["season"]["name"] if match.get("season") else "Unknown",
1006 "age_group_id": match["age_group_id"],
1007 "age_group_name": match["age_group"]["name"] if match.get("age_group") else "Unknown",
1008 "match_type_id": match["match_type_id"],
1009 "match_type_name": match["match_type"]["name"] if match.get("match_type") else "Unknown",
1010 "division_id": match.get("division_id"),
1011 "division_name": match["division"]["name"] if match.get("division") else "Unknown",
1012 "division": match.get("division"), # Include full division object with leagues
1013 "sport_type": sport_type,
1014 "match_status": match.get("match_status"),
1015 "created_by": match.get("created_by"),
1016 "updated_by": match.get("updated_by"),
1017 "source": match.get("source", "manual"),
1018 "match_id": match.get("match_id"), # External match identifier
1019 "created_at": match["created_at"],
1020 "updated_at": match["updated_at"],
1021 # Live match clock fields
1022 "kickoff_time": match.get("kickoff_time"),
1023 "halftime_start": match.get("halftime_start"),
1024 "second_half_start": match.get("second_half_start"),
1025 "match_end_time": match.get("match_end_time"),
1026 "half_duration": match.get("half_duration", 45),
1027 }
1028 return flat_match
1029 else:
1030 return None
1032 except Exception:
1033 logger.exception("Error retrieving match by ID")
1034 return None
1036 @invalidates_cache(MATCHES_CACHE_PATTERN, TOURNAMENTS_CACHE_PATTERN)
1037 def delete_match(self, match_id: int) -> bool:
1038 """Delete a match."""
1039 try:
1040 self.client.table("matches").delete().eq("id", match_id).execute()
1042 return True # Supabase delete returns empty data even on success
1044 except Exception:
1045 logger.exception("Error deleting match")
1046 return False
1048 @dao_cache("matches:table:{season_id}:{age_group_id}:{division_id}:{match_type}")
1049 def get_league_table(
1050 self,
1051 season_id: int | None = None,
1052 age_group_id: int | None = None,
1053 division_id: int | None = None,
1054 match_type: str = "League",
1055 ) -> list[dict]:
1056 """
1057 Generate league table with optional filters.
1059 This method fetches matches from the database and delegates
1060 the standings calculation to pure functions in dao/standings.py.
1062 Args:
1063 season_id: Filter by season
1064 age_group_id: Filter by age group
1065 division_id: Filter by division
1066 match_type: Filter by match type name (default: "League")
1068 Returns:
1069 List of team standings sorted by points, goal difference, goals scored
1070 """
1071 try:
1072 logger.info(
1073 "generating league table from database",
1074 season_id=season_id,
1075 age_group_id=age_group_id,
1076 division_id=division_id,
1077 match_type=match_type,
1078 )
1079 # Fetch matches from database
1080 matches = self._fetch_matches_for_standings(season_id, age_group_id, division_id)
1082 # Apply filters using pure functions
1083 matches = filter_by_match_type(matches, match_type)
1084 if division_id:
1085 matches = filter_same_division_matches(matches, division_id)
1086 matches = filter_completed_matches(matches)
1088 # Calculate standings using pure function (with form + movement)
1089 return calculate_standings_with_extras(matches)
1091 except Exception:
1092 logger.exception("Error generating league table")
1093 return []
1095 def _fetch_matches_for_standings(
1096 self,
1097 season_id: int | None = None,
1098 age_group_id: int | None = None,
1099 division_id: int | None = None,
1100 ) -> list[dict]:
1101 """
1102 Fetch matches from database for standings calculation.
1104 This is a thin data access method that only handles the query.
1105 All business logic is in pure functions.
1107 Args:
1108 season_id: Filter by season
1109 age_group_id: Filter by age group
1110 division_id: Filter by division
1112 Returns:
1113 List of match dictionaries from database
1114 """
1115 logger.info(
1116 "fetching matches for standings calculation from database",
1117 season_id=season_id,
1118 age_group_id=age_group_id,
1119 division_id=division_id,
1120 )
1121 query = self.client.table("matches").select("""
1122 *,
1123 home_team:teams!matches_home_team_id_fkey(id, name, division_id, club:clubs(id, name, logo_url)),
1124 away_team:teams!matches_away_team_id_fkey(id, name, division_id, club:clubs(id, name, logo_url)),
1125 match_type:match_types(id, name)
1126 """)
1128 # Apply database-level filters
1129 if season_id:
1130 query = query.eq("season_id", season_id)
1131 if age_group_id:
1132 query = query.eq("age_group_id", age_group_id)
1133 if division_id:
1134 query = query.eq("division_id", division_id)
1136 response = query.execute()
1137 return response.data
1139 # === Live Match Methods ===
1141 def get_live_matches(self) -> list[dict]:
1142 """Get all matches with status 'live'.
1144 Returns minimal data for the LIVE tab polling.
1145 """
1146 try:
1147 response = (
1148 self.client.table("matches")
1149 .select("""
1150 id,
1151 match_status,
1152 match_date,
1153 home_score,
1154 away_score,
1155 kickoff_time,
1156 home_team:teams!matches_home_team_id_fkey(id, name),
1157 away_team:teams!matches_away_team_id_fkey(id, name)
1158 """)
1159 .eq("match_status", "live")
1160 .execute()
1161 )
1163 # Flatten the response
1164 result = []
1165 for match in response.data or []:
1166 result.append(
1167 {
1168 "match_id": match["id"],
1169 "match_status": match["match_status"],
1170 "match_date": match["match_date"],
1171 "home_score": match["home_score"],
1172 "away_score": match["away_score"],
1173 "kickoff_time": match.get("kickoff_time"),
1174 "home_team_name": match["home_team"]["name"] if match.get("home_team") else "Unknown",
1175 "away_team_name": match["away_team"]["name"] if match.get("away_team") else "Unknown",
1176 }
1177 )
1178 return result
1180 except Exception:
1181 logger.exception("Error getting live matches")
1182 return []
1184 def get_live_match_state(self, match_id: int) -> dict | None:
1185 """Get full live match state including clock timestamps.
1187 Returns match data with clock fields for the live match view.
1188 """
1189 try:
1190 response = (
1191 self.client.table("matches")
1192 .select("""
1193 *,
1194 home_team:teams!matches_home_team_id_fkey(
1195 id, name,
1196 club:clubs(id, logo_url),
1197 division:divisions(id, leagues(sport_type))
1198 ),
1199 away_team:teams!matches_away_team_id_fkey(id, name, club:clubs(id, logo_url)),
1200 age_group:age_groups(id, name),
1201 match_type:match_types(id, name),
1202 division:divisions(id, name, leagues(id, name, sport_type))
1203 """)
1204 .eq("id", match_id)
1205 .single()
1206 .execute()
1207 )
1209 if not response.data:
1210 return None
1212 match = response.data
1213 # Extract club logo URLs from nested team -> club relationships
1214 home_team = match.get("home_team") or {}
1215 away_team = match.get("away_team") or {}
1216 home_club = home_team.get("club") or {}
1217 away_club = away_team.get("club") or {}
1219 # Extract sport_type from division -> leagues
1220 division = match.get("division") or {}
1221 league = division.get("leagues") or {}
1222 sport_type = league.get("sport_type")
1224 # Fallback: get sport_type from home team's division -> league
1225 if not sport_type:
1226 team_div = home_team.get("division") or {}
1227 team_league = team_div.get("leagues") or {}
1228 sport_type = team_league.get("sport_type", "soccer")
1230 return {
1231 "match_id": match["id"],
1232 "match_status": match.get("match_status"),
1233 "match_date": match["match_date"],
1234 "scheduled_kickoff": match.get("scheduled_kickoff"),
1235 "home_score": match["home_score"],
1236 "away_score": match["away_score"],
1237 "kickoff_time": match.get("kickoff_time"),
1238 "halftime_start": match.get("halftime_start"),
1239 "second_half_start": match.get("second_half_start"),
1240 "match_end_time": match.get("match_end_time"),
1241 "half_duration": match.get("half_duration", 45),
1242 "season_id": match.get("season_id"),
1243 "home_team_id": match["home_team_id"],
1244 "home_team_name": home_team.get("name", "Unknown"),
1245 "home_team_logo": home_club.get("logo_url"),
1246 "away_team_id": match["away_team_id"],
1247 "away_team_name": away_team.get("name", "Unknown"),
1248 "away_team_logo": away_club.get("logo_url"),
1249 "age_group_name": match["age_group"]["name"] if match.get("age_group") else None,
1250 "match_type_name": match["match_type"]["name"] if match.get("match_type") else None,
1251 "division_name": match["division"]["name"] if match.get("division") else None,
1252 "sport_type": sport_type,
1253 }
1255 except Exception:
1256 logger.exception("Error getting live match state", match_id=match_id)
1257 return None
1259 @invalidates_cache(MATCHES_CACHE_PATTERN, TOURNAMENTS_CACHE_PATTERN)
1260 def update_match_clock(
1261 self,
1262 match_id: int,
1263 action: str,
1264 updated_by: str | None = None,
1265 half_duration: int | None = None,
1266 ) -> dict | None:
1267 """Update match clock based on action.
1269 Args:
1270 match_id: The match to update
1271 action: Clock action - 'start_first_half', 'start_halftime',
1272 'start_second_half', 'end_match'
1273 updated_by: UUID of user performing the action
1274 half_duration: Duration of each half in minutes (only for start_first_half)
1276 Returns:
1277 Updated match state or None on error
1278 """
1279 from datetime import datetime
1281 try:
1282 now = datetime.now(UTC).isoformat()
1283 data: dict = {"updated_by": updated_by} if updated_by else {}
1285 if action == "start_first_half":
1286 # Start the match - set kickoff time and status to live
1287 data["kickoff_time"] = now
1288 data["match_status"] = "live"
1289 # Set half duration if provided (default is 45)
1290 if half_duration:
1291 data["half_duration"] = half_duration
1292 elif action == "start_halftime":
1293 # Mark halftime started
1294 data["halftime_start"] = now
1295 elif action == "start_second_half":
1296 # Start second half
1297 data["second_half_start"] = now
1298 elif action == "end_match":
1299 # End the match
1300 data["match_end_time"] = now
1301 data["match_status"] = "completed"
1302 else:
1303 logger.warning("Invalid clock action", action=action)
1304 return None
1306 response = self.client.table("matches").update(data).eq("id", match_id).execute()
1308 if not response.data:
1309 logger.warning("Clock update failed - no rows affected", match_id=match_id)
1310 return None
1312 logger.info(
1313 "match_clock_updated",
1314 match_id=match_id,
1315 action=action,
1316 )
1318 # Return updated state
1319 return self.get_live_match_state(match_id)
1321 except Exception:
1322 logger.exception("Error updating match clock", match_id=match_id, action=action)
1323 return None
1325 @invalidates_cache(MATCHES_CACHE_PATTERN, TOURNAMENTS_CACHE_PATTERN)
1326 def update_match_score(
1327 self,
1328 match_id: int,
1329 home_score: int,
1330 away_score: int,
1331 updated_by: str | None = None,
1332 ) -> dict | None:
1333 """Update match score (used when posting a goal).
1335 Args:
1336 match_id: The match to update
1337 home_score: New home team score
1338 away_score: New away team score
1339 updated_by: UUID of user performing the action
1341 Returns:
1342 Updated match state or None on error
1343 """
1344 try:
1345 data = {
1346 "home_score": home_score,
1347 "away_score": away_score,
1348 }
1349 if updated_by:
1350 data["updated_by"] = updated_by
1352 response = self.client.table("matches").update(data).eq("id", match_id).execute()
1354 if not response.data:
1355 logger.warning("Score update failed - no rows affected", match_id=match_id)
1356 return None
1358 logger.info(
1359 "match_score_updated",
1360 match_id=match_id,
1361 home_score=home_score,
1362 away_score=away_score,
1363 )
1365 return self.get_live_match_state(match_id)
1367 except Exception:
1368 logger.exception("Error updating match score", match_id=match_id)
1369 return None
1371 def get_agent_matches(
1372 self,
1373 team: str,
1374 age_group: str,
1375 league: str,
1376 division: str,
1377 season: str,
1378 start_date: str | None = None,
1379 end_date: str | None = None,
1380 ) -> list[dict]:
1381 """Get individual match records for the audit agent's comparison step.
1383 Returns matches involving the specified team (home or away) in the
1384 given age-group/league/division/season. Used by GET /api/agent/matches.
1386 Args:
1387 team: MT canonical team name, e.g. "IFA".
1388 age_group: e.g. "U14".
1389 league: e.g. "Homegrown".
1390 division: e.g. "Northeast".
1391 season: e.g. "2025-2026".
1392 start_date: If set, only return matches on or after this date (YYYY-MM-DD).
1393 end_date: If set, only return matches on or before this date (YYYY-MM-DD).
1395 Returns:
1396 List of match dicts with fields the audit comparator expects.
1397 """
1398 # Resolve IDs for the reference dimensions
1399 season_resp = self.client.table("seasons").select("id").eq("name", season).limit(1).execute()
1400 if not season_resp.data:
1401 logger.warning("get_agent_matches.season_not_found", season=season)
1402 return []
1403 season_id = season_resp.data[0]["id"]
1405 ag_resp = self.client.table("age_groups").select("id").eq("name", age_group).limit(1).execute()
1406 if not ag_resp.data:
1407 logger.warning("get_agent_matches.age_group_not_found", age_group=age_group)
1408 return []
1409 age_group_id = ag_resp.data[0]["id"]
1411 # Resolve division_id via league join
1412 div_resp = (
1413 self.client.table("divisions")
1414 .select("id, leagues!divisions_league_id_fkey(name)")
1415 .eq("name", division)
1416 .execute()
1417 )
1418 division_id = None
1419 for row in div_resp.data or []:
1420 if row.get("leagues") and row["leagues"].get("name") == league:
1421 division_id = row["id"]
1422 break
1423 if division_id is None:
1424 logger.warning("get_agent_matches.division_not_found", division=division, league=league)
1425 return []
1427 # Resolve team IDs matching the given name
1428 team_resp = self.client.table("teams").select("id").eq("name", team).execute()
1429 team_ids = [r["id"] for r in (team_resp.data or [])]
1430 if not team_ids:
1431 logger.warning("get_agent_matches.team_not_found", team=team)
1432 return []
1434 # Build OR filter for home/away team membership
1435 or_filter = ",".join(
1436 [f"home_team_id.eq.{tid}" for tid in team_ids] + [f"away_team_id.eq.{tid}" for tid in team_ids]
1437 )
1439 try:
1440 query = (
1441 self.client.table("matches")
1442 .select(
1443 "match_id, match_date, scheduled_kickoff, home_score, away_score, match_status, "
1444 "home_team:teams!matches_home_team_id_fkey(name), "
1445 "away_team:teams!matches_away_team_id_fkey(name)"
1446 )
1447 .eq("season_id", season_id)
1448 .eq("age_group_id", age_group_id)
1449 .eq("division_id", division_id)
1450 .neq("match_status", "cancelled")
1451 )
1452 if start_date:
1453 query = query.gte("match_date", start_date)
1454 if end_date:
1455 query = query.lte("match_date", end_date)
1456 response = query.or_(or_filter).order("match_date", desc=False).execute()
1457 except Exception:
1458 logger.exception("get_agent_matches.query_error", team=team)
1459 return []
1461 results = []
1462 for m in response.data or []:
1463 # Format match_time as "HH:MM" from scheduled_kickoff (UTC)
1464 match_time = None
1465 if m.get("scheduled_kickoff"):
1466 try:
1467 from datetime import datetime
1469 kt = datetime.fromisoformat(m["scheduled_kickoff"].replace("Z", "+00:00"))
1470 if kt.hour or kt.minute:
1471 match_time = kt.strftime("%H:%M")
1472 except (ValueError, AttributeError):
1473 pass
1475 results.append(
1476 {
1477 "external_match_id": m.get("match_id"),
1478 "home_team": m["home_team"]["name"] if m.get("home_team") else None,
1479 "away_team": m["away_team"]["name"] if m.get("away_team") else None,
1480 "match_date": m["match_date"],
1481 "match_time": match_time,
1482 "home_score": m.get("home_score"),
1483 "away_score": m.get("away_score"),
1484 "match_status": m.get("match_status"),
1485 "age_group": age_group,
1486 "league": league,
1487 "division": division,
1488 "season": season,
1489 }
1490 )
1492 logger.info(
1493 "get_agent_matches.done",
1494 team=team,
1495 age_group=age_group,
1496 count=len(results),
1497 )
1498 return results
1500 def cancel_match(
1501 self,
1502 home_team: str,
1503 away_team: str,
1504 match_date: str,
1505 age_group: str,
1506 league: str,
1507 division: str,
1508 season: str,
1509 ) -> bool:
1510 """Mark a match as cancelled by natural key.
1512 Only cancels matches with no score (home_score IS NULL) to avoid
1513 accidentally cancelling completed matches.
1515 Returns True if a match was found and cancelled, False if not found.
1516 """
1517 # Resolve dimension IDs (same pattern as get_agent_matches)
1518 season_resp = self.client.table("seasons").select("id").eq("name", season).limit(1).execute()
1519 if not season_resp.data:
1520 logger.warning("cancel_match.season_not_found", season=season)
1521 return False
1522 season_id = season_resp.data[0]["id"]
1524 ag_resp = self.client.table("age_groups").select("id").eq("name", age_group).limit(1).execute()
1525 if not ag_resp.data:
1526 logger.warning("cancel_match.age_group_not_found", age_group=age_group)
1527 return False
1528 age_group_id = ag_resp.data[0]["id"]
1530 div_resp = (
1531 self.client.table("divisions")
1532 .select("id, leagues!divisions_league_id_fkey(name)")
1533 .eq("name", division)
1534 .execute()
1535 )
1536 division_id = None
1537 for row in div_resp.data or []:
1538 if row.get("leagues") and row["leagues"].get("name") == league:
1539 division_id = row["id"]
1540 break
1541 if division_id is None:
1542 logger.warning("cancel_match.division_not_found", division=division, league=league)
1543 return False
1545 home_resp = self.client.table("teams").select("id").eq("name", home_team).execute()
1546 home_ids = [r["id"] for r in (home_resp.data or [])]
1547 away_resp = self.client.table("teams").select("id").eq("name", away_team).execute()
1548 away_ids = [r["id"] for r in (away_resp.data or [])]
1549 if not home_ids or not away_ids:
1550 logger.warning("cancel_match.team_not_found", home_team=home_team, away_team=away_team)
1551 return False
1553 or_parts = [
1554 f"and(home_team_id.eq.{hid},away_team_id.eq.{aid})"
1555 for hid in home_ids
1556 for aid in away_ids
1557 ]
1558 resp = (
1559 self.client.table("matches")
1560 .select("id, home_score")
1561 .eq("season_id", season_id)
1562 .eq("age_group_id", age_group_id)
1563 .eq("division_id", division_id)
1564 .eq("match_date", match_date)
1565 .is_("home_score", "null") # safety: never cancel scored matches
1566 .or_(",".join(or_parts))
1567 .limit(1)
1568 .execute()
1569 )
1571 if not resp.data:
1572 logger.warning(
1573 "cancel_match.not_found",
1574 home_team=home_team,
1575 away_team=away_team,
1576 match_date=match_date,
1577 )
1578 return False
1580 internal_id = resp.data[0]["id"]
1581 self.client.table("matches").update({"match_status": "cancelled"}).eq("id", internal_id).execute()
1582 logger.info(
1583 "cancel_match.done",
1584 internal_id=internal_id,
1585 home_team=home_team,
1586 away_team=away_team,
1587 match_date=match_date,
1588 )
1589 return True
1591 # Admin CRUD methods for reference data have been moved to:
1592 # - SeasonDAO (seasons, age_groups)
1593 # - LeagueDAO (leagues, divisions)
1594 # - MatchTypeDAO (match_types)
1595 # Team methods moved to TeamDAO - see dao/team_dao.py
1596 # Club methods moved to ClubDAO - see dao/club_dao.py
1597 # User profile and player methods moved to PlayerDAO - see dao/player_dao.py