Coverage for dao/team_dao.py: 11.05%

1"""

2Team Data Access Object.

4Handles all database operations related to teams including:

5- Team CRUD operations

6- Team-age group mappings

7- Team-match type participations

8- Team queries and filters

9- Team-club associations

10"""

12import structlog

14from dao.base_dao import BaseDAO, dao_cache, invalidates_cache

16logger = structlog.get_logger()

18# Cache pattern for invalidation

19TEAMS_CACHE_PATTERN = "mt:dao:teams:*"

22class TeamDAO(BaseDAO):

23 """Data access object for team operations."""

25 # === Team Query Methods ===

27 @dao_cache("teams:all")

28 def get_all_teams(self) -> list[dict]:

29 """Get all teams with their age groups."""

30 response = (

31 self.client.table("teams")

32 .select("""

33 *,

34 leagues!teams_league_id_fkey (

35 id,

36 name,

37 sport_type

38 ),

39 team_mappings (

40 age_groups (

41 id,

42 name

43 ),

44 divisions (

45 id,

46 name,

47 league_id,

48 leagues!divisions_league_id_fkey (

49 id,

50 name,

51 sport_type

52 )

53 )

54 )

55 """)

56 .order("name")

57 .execute()

58 )

60 # Flatten the age groups and divisions for each team

61 teams = []

62 for team in response.data:

63 # Extract league_name from the joined leagues table

64 if team.get("leagues"):

65 team["league_name"] = team["leagues"]["name"]

67 age_groups = []

68 divisions_by_age_group = {}

69 if "team_mappings" in team:

70 for tag in team["team_mappings"]:

71 if tag.get("age_groups"):

72 age_group = tag["age_groups"]

73 age_groups.append(age_group)

74 if tag.get("divisions"):

75 division = tag["divisions"]

76 # Add league_name and sport_type to division for easy access in frontend

77 if division.get("leagues"):

78 division["league_name"] = division["leagues"]["name"]

79 division["sport_type"] = division["leagues"].get("sport_type", "soccer")

80 divisions_by_age_group[age_group["id"]] = division

81 team["age_groups"] = age_groups

82 team["divisions_by_age_group"] = divisions_by_age_group

83 teams.append(team)

85 return teams

87 @dao_cache("teams:by_match_type:{match_type_id}:{age_group_id}:{division_id}")

88 def get_teams_by_match_type_and_age_group(

89 self, match_type_id: int, age_group_id: int, division_id: int | None = None

90 ) -> list[dict]:

91 """Get teams that can participate in a specific match type and age group.

93 Args:

94 match_type_id: Filter by match type (e.g., League, Cup)

95 age_group_id: Filter by age group (e.g., U14, U15)

96 division_id: Optional - Filter by division (e.g., Bracket A for Futsal)

98 Note: Due to PostgREST limitations with multiple inner joins, we query

99 junction tables directly and intersect results when filtering by division.

100 """

101 # Get team IDs that have the required match type

102 mt_response = (

103 self.client.table("team_match_types")

104 .select("team_id")

105 .eq("match_type_id", match_type_id)

106 .eq("age_group_id", age_group_id)

107 .eq("is_active", True)

108 .execute()

109 )

110 match_type_team_ids = {r["team_id"] for r in mt_response.data}

111

112 if not match_type_team_ids:

113 return []

114

115 # If division filter is specified, intersect with division teams

116 if division_id:

117 div_response = (

118 self.client.table("team_mappings")

119 .select("team_id")

120 .eq("age_group_id", age_group_id)

121 .eq("division_id", division_id)

122 .execute()

123 )

124 division_team_ids = {r["team_id"] for r in div_response.data}

125 final_team_ids = match_type_team_ids & division_team_ids

126 else:

127 final_team_ids = match_type_team_ids

128

129 if not final_team_ids:

130 return []

131

132 # Fetch full team data for the filtered IDs

133 response = (

134 self.client.table("teams")

135 .select("""

136 *,

137 team_mappings (

138 age_groups (

139 id,

140 name

141 ),

142 divisions (

143 id,

144 name

145 )

146 )

147 """)

148 .in_("id", list(final_team_ids))

149 .order("name")

150 .execute()

151 )

152

153 # Flatten the age groups and divisions for each team

154 teams = []

155 for team in response.data:

156 age_groups = []

157 divisions_by_age_group = {}

158 if "team_mappings" in team:

159 for tag in team["team_mappings"]:

160 if tag.get("age_groups"):

161 age_group = tag["age_groups"]

162 age_groups.append(age_group)

163 if tag.get("divisions"):

164 divisions_by_age_group[age_group["id"]] = tag["divisions"]

165 team["age_groups"] = age_groups

166 team["divisions_by_age_group"] = divisions_by_age_group

167 teams.append(team)

168

169 return teams

170

171 @dao_cache("teams:by_name:{name}")

172 def get_team_by_name(self, name: str) -> dict | None:

173 """Get a team by name (case-insensitive exact match).

174

175 Returns the first matching team with basic info (id, name, city).

176 For match-scraper integration, this helps look up teams by name.

177 """

178 response = (

179 self.client.table("teams").select("id, name, city, academy_team").ilike("name", name).limit(1).execute()

180 )

181

182 if response.data and len(response.data) > 0:

183 return response.data[0]

184 return None

185

186 @dao_cache("teams:by_id:{team_id}")

187 def get_team_by_id(self, team_id: int) -> dict | None:

188 """Get a team by ID.

189

190 Returns team info (id, name, city, club_id).

191 """

192 response = self.client.table("teams").select("id, name, city, club_id").eq("id", team_id).limit(1).execute()

193

194 if response.data and len(response.data) > 0:

195 return response.data[0]

196 return None

197

198 @dao_cache("teams:with_details:{team_id}")

199 def get_team_with_details(self, team_id: int) -> dict | None:

200 """Get a team with club, league, division, and age group details.

201

202 Returns enriched team info for the team roster page header.

203 """

204 response = (

205 self.client.table("teams")

206 .select("""

207 id, name, city, academy_team, league_id,

208 club:clubs(id, name, logo_url, primary_color, secondary_color),

209 division:divisions(id, name),

210 age_group:age_groups(id, name)

211 """)

212 .eq("id", team_id)

213 .limit(1)

214 .execute()

215 )

216

217 if not response.data or len(response.data) == 0:

218 return None

219

220 team = response.data[0]

221 result = {

222 "id": team.get("id"),

223 "name": team.get("name"),

224 "city": team.get("city"),

225 "academy_team": team.get("academy_team"),

226 "club": team.get("club"),

227 "league": None,

228 "division": team.get("division"),

229 "age_group": team.get("age_group"),

230 }

231

232 # Fetch league separately (no FK relationship)

233 league_id = team.get("league_id")

234 if league_id:

235 league_response = self.client.table("match_types").select("id, name").eq("id", league_id).limit(1).execute()

236 if league_response.data and len(league_response.data) > 0:

237 result["league"] = league_response.data[0]

238

239 return result

240

241 @dao_cache("teams:club_basic:{club_id}")

242 def get_club_teams_basic(self, club_id: int) -> list[dict]:

243 """Get teams for a club without match/player counts."""

244 response = (

245 self.client.table("teams_with_league_badges")

246 .select("id,name,club_id,league_id,league_name,mapping_league_names")

247 .eq("club_id", club_id)

248 .order("name")

249 .execute()

250 )

251 return [*response.data]

252

253 @dao_cache("teams:club:{club_id}")

254 def get_club_teams(self, club_id: int) -> list[dict]:

255 """Get all teams for a club across all leagues.

256

257 Args:

258 club_id: The club ID from the clubs table

259

260 Returns:

261 List of teams belonging to this club with team_mappings included,

262 plus match_count, player_count, age_group_name, and division_name

263 """

264 response = (

265 self.client.table("teams")

266 .select("""

267 *,

268 leagues!teams_league_id_fkey (

269 id,

270 name

271 ),

272 team_mappings (

273 age_groups (

274 id,

275 name

276 ),

277 divisions (

278 id,

279 name,

280 league_id,

281 leagues!divisions_league_id_fkey (

282 id,

283 name

284 )

285 )

286 )

287 """)

288 .eq("club_id", club_id)

289 .order("name")

290 .execute()

291 )

292

293 # Get team IDs for batch counting

294 team_ids = [team["id"] for team in response.data]

295

296 # Get match counts for all teams in one query

297 match_counts = {}

298 if team_ids:

299 home_matches = self.client.table("matches").select("home_team_id").in_("home_team_id", team_ids).execute()

300 away_matches = self.client.table("matches").select("away_team_id").in_("away_team_id", team_ids).execute()

301 for match in home_matches.data:

302 tid = match["home_team_id"]

303 match_counts[tid] = match_counts.get(tid, 0) + 1

304 for match in away_matches.data:

305 tid = match["away_team_id"]

306 match_counts[tid] = match_counts.get(tid, 0) + 1

307

308 # Get player counts for all teams in one query

309 player_counts = {}

310 if team_ids:

311 players = self.client.table("user_profiles").select("team_id").in_("team_id", team_ids).execute()

312 for player in players.data:

313 tid = player["team_id"]

314 player_counts[tid] = player_counts.get(tid, 0) + 1

315

316 # Process teams to add league_name and age_groups

317 teams = []

318 for team in response.data:

319 team_data = {**team}

320 if team.get("leagues"):

321 team_data["league_name"] = team["leagues"].get("name")

322 else:

323 team_data["league_name"] = None

324

325 age_groups = []

326 first_division_name = None

327 if team.get("team_mappings"):

328 seen_age_groups = set()

329 for mapping in team["team_mappings"]:

330 if mapping.get("age_groups"):

331 ag_id = mapping["age_groups"]["id"]

332 if ag_id not in seen_age_groups:

333 age_groups.append(mapping["age_groups"])

334 seen_age_groups.add(ag_id)

335 if first_division_name is None and mapping.get("divisions"):

336 first_division_name = mapping["divisions"].get("name")

337 team_data["age_groups"] = age_groups

338 team_data["age_group_name"] = age_groups[0]["name"] if age_groups else None

339 team_data["division_name"] = first_division_name

340 team_data["match_count"] = match_counts.get(team["id"], 0)

341 team_data["player_count"] = player_counts.get(team["id"], 0)

342

343 teams.append(team_data)

344

345 return teams

346

347 # === Team CRUD Methods ===

348

349 @invalidates_cache(TEAMS_CACHE_PATTERN)

350 def add_team(

351 self,

352 name: str,

353 city: str,

354 age_group_ids: list[int],

355 match_type_ids: list[int] | None = None,

356 division_id: int | None = None,

357 club_id: int | None = None,

358 academy_team: bool = False,

359 ) -> bool:

360 """Add a new team with age groups, division, and optional club.

361

362 Args:

363 name: Team name

364 city: Team city

365 age_group_ids: List of age group IDs (required, at least one)

366 match_type_ids: List of match type IDs (optional)

367 division_id: Division ID (optional, only required for league teams)

368 club_id: Optional club ID

369 academy_team: Whether this is an academy team

370 """

371 logger.info(

372 "Creating team",

373 team_name=name,

374 city=city,

375 age_group_count=len(age_group_ids),

376 match_type_count=len(match_type_ids) if match_type_ids else 0,

377 division_id=division_id,

378 club_id=club_id,

379 academy_team=academy_team,

380 )

381

382 # Validate required fields

383 if not age_group_ids or len(age_group_ids) == 0:

384 raise ValueError("Team must have at least one age group")

385

386 # Get league_id from division (if division provided)

387 league_id = None

388 if division_id is not None:

389 division_response = self.client.table("divisions").select("league_id").eq("id", division_id).execute()

390 if not division_response.data:

391 raise ValueError(f"Division {division_id} not found")

392 league_id = division_response.data[0]["league_id"]

393

394 # Insert team

395 team_data = {

396 "name": name,

397 "city": city,

398 "academy_team": academy_team,

399 "club_id": club_id,

400 "league_id": league_id,

401 "division_id": division_id,

402 }

403 team_response = self.client.table("teams").insert(team_data).execute()

404

405 if not team_response.data:

406 return False

407

408 team_id = team_response.data[0]["id"]

409 logger.info("Team record created", team_id=team_id, team_name=name)

410

411 # Add age group associations

412 for age_group_id in age_group_ids:

413 data = {

414 "team_id": team_id,

415 "age_group_id": age_group_id,

416 "division_id": division_id,

417 }

418 self.client.table("team_mappings").insert(data).execute()

419

420 # Add game type participations

421 if match_type_ids:

422 for match_type_id in match_type_ids:

423 for age_group_id in age_group_ids:

424 match_type_data = {

425 "team_id": team_id,

426 "match_type_id": match_type_id,

427 "age_group_id": age_group_id,

428 "is_active": True,

429 }

430 self.client.table("team_match_types").insert(match_type_data).execute()

431

432 logger.info(

433 "Team creation completed",

434 team_id=team_id,

435 team_name=name,

436 age_groups=len(age_group_ids),

437 match_types=len(match_type_ids) if match_type_ids else 0,

438 )

439 return True

440

441 @invalidates_cache(TEAMS_CACHE_PATTERN)

442 def update_team(

443 self,

444 team_id: int,

445 name: str,

446 city: str,

447 academy_team: bool = False,

448 club_id: int | None = None,

449 ) -> dict | None:

450 """Update a team."""

451 update_data = {

452 "name": name,

453 "city": city,

454 "academy_team": academy_team,

455 "club_id": club_id,

456 }

457 logger.debug("DAO update_team", team_id=team_id, update_data=update_data)

458

459 result = self.client.table("teams").update(update_data).eq("id", team_id).execute()

460

461 return result.data[0] if result.data else None

462

463 @invalidates_cache(TEAMS_CACHE_PATTERN)

464 def delete_team(self, team_id: int) -> bool:

465 """Delete a team and its related data.

466

467 Cascades deletion of:

468 - team_mappings (FK constraint)

469 - team_match_types (FK constraint)

470 - matches where team is home or away (FK constraint)

471 """

472 # Delete team_mappings first (FK constraint)

473 self.client.table("team_mappings").delete().eq("team_id", team_id).execute()

474

475 # Delete team_match_types (FK constraint)

476 self.client.table("team_match_types").delete().eq("team_id", team_id).execute()

477

478 # Delete matches where this team participates (FK constraint)

479 self.client.table("matches").delete().eq("home_team_id", team_id).execute()

480 self.client.table("matches").delete().eq("away_team_id", team_id).execute()

481

482 # Now delete the team

483 result = self.client.table("teams").delete().eq("id", team_id).execute()

484 return len(result.data) > 0

485

486 # === Team Mapping Methods ===

487

488 @invalidates_cache(TEAMS_CACHE_PATTERN)

489 def update_team_division(self, team_id: int, age_group_id: int, division_id: int) -> bool:

490 """Update the division for a team in a specific age group."""

491 response = (

492 self.client.table("team_mappings")

493 .update({"division_id": division_id})

494 .eq("team_id", team_id)

495 .eq("age_group_id", age_group_id)

496 .execute()

497 )

498 return bool(response.data)

499

500 @invalidates_cache(TEAMS_CACHE_PATTERN)

501 def create_team_mapping(self, team_id: int, age_group_id: int, division_id: int) -> dict:

502 """Create a team mapping, update team's league_id, and enable League match participation.

503

504 When assigning a team to a division (which belongs to a league), this method:

505 1. Updates the team's league_id to match the division's league

506 2. Creates the team_mapping entry

507 3. Auto-creates a team_match_types entry for League matches (match_type_id=1)

508 """

509 # Get the league_id from the division

510 division_response = self.client.table("divisions").select("league_id").eq("id", division_id).execute()

511 if division_response.data:

512 league_id = division_response.data[0]["league_id"]

513 self.client.table("teams").update(

514 {

515 "league_id": league_id,

516 "division_id": division_id,

517 }

518 ).eq("id", team_id).execute()

519

520 # Create the team mapping

521 result = (

522 self.client.table("team_mappings")

523 .insert(

524 {

525 "team_id": team_id,

526 "age_group_id": age_group_id,

527 "division_id": division_id,

528 }

529 )

530 .execute()

531 )

532

533 # Auto-create team_match_types entry for League matches

534 LEAGUE_MATCH_TYPE_ID = 1

535 existing = (

536 self.client.table("team_match_types")

537 .select("id")

538 .eq("team_id", team_id)

539 .eq("match_type_id", LEAGUE_MATCH_TYPE_ID)

540 .eq("age_group_id", age_group_id)

541 .execute()

542 )

543 if not existing.data:

544 self.client.table("team_match_types").insert(

545 {

546 "team_id": team_id,

547 "match_type_id": LEAGUE_MATCH_TYPE_ID,

548 "age_group_id": age_group_id,

549 "is_active": True,

550 }

551 ).execute()

552 logger.info(

553 "Auto-created team_match_types entry for League",

554 team_id=team_id,

555 age_group_id=age_group_id,

556 )

557

558 return result.data[0]

559

560 @invalidates_cache(TEAMS_CACHE_PATTERN)

561 def delete_team_mapping(self, team_id: int, age_group_id: int, division_id: int) -> bool:

562 """Delete a team mapping."""

563 result = (

564 self.client.table("team_mappings")

565 .delete()

566 .eq("team_id", team_id)

567 .eq("age_group_id", age_group_id)

568 .eq("division_id", division_id)

569 .execute()

570 )

571 return len(result.data) > 0

572

573 @invalidates_cache(TEAMS_CACHE_PATTERN)

574 def update_team_club(self, team_id: int, club_id: int | None) -> dict:

575 """Update the club for a team.

576

577 Args:

578 team_id: The team ID to update

579 club_id: The club ID to assign (or None to remove club association)

580

581 Returns:

582 Updated team dict

583 """

584 result = self.client.table("teams").update({"club_id": club_id}).eq("id", team_id).execute()

585 if not result.data or len(result.data) == 0:

586 raise ValueError(f"Failed to update club for team {team_id}")

587 return result.data[0]

588

589 # === Team Match Type Participation Methods ===

590

591 def add_team_match_type_participation(self, team_id: int, match_type_id: int, age_group_id: int) -> bool:

592 """Add a team's participation in a specific match type and age group."""

593 try:

594 self.client.table("team_match_types").insert(

595 {

596 "team_id": team_id,

597 "match_type_id": match_type_id,

598 "age_group_id": age_group_id,

599 "is_active": True,

600 }

601 ).execute()

602 return True

603 except Exception:

604 logger.exception("Error adding team match type participation")

605 return False

606

607 def remove_team_match_type_participation(self, team_id: int, match_type_id: int, age_group_id: int) -> bool:

608 """Remove a team's participation in a specific match type and age group."""

609 try:

610 self.client.table("team_match_types").update({"is_active": False}).eq("team_id", team_id).eq(

611 "match_type_id", match_type_id

612 ).eq("age_group_id", age_group_id).execute()

613 return True

614 except Exception:

615 logger.exception("Error removing team match type participation")

616 return False

617

618 # === Other Query Methods (not cached) ===

619

620 def get_teams_by_club_ids(self, club_ids: list[int]) -> list[dict]:

621 """Get teams for multiple clubs without match/player counts.

622

623 This is optimized for admin club listings that only need team details.

624 """

625 if not club_ids:

626 return []

627

628 response = (

629 self.client.table("teams_with_league_badges")

630 .select("id,name,club_id,league_id,league_name,mapping_league_names")

631 .in_("club_id", club_ids)

632 .order("name")

633 .execute()

634 )

635

636 return [{**team} for team in response.data]

637

638 def get_club_for_team(self, team_id: int) -> dict | None:

639 """Get the club for a team.

640

641 Args:

642 team_id: The team ID

643

644 Returns:

645 Club dict if team belongs to a club, None otherwise

646 """

647 team_response = self.client.table("teams").select("club_id").eq("id", team_id).execute()

648 if not team_response.data or len(team_response.data) == 0:

649 return None

650

651 club_id = team_response.data[0].get("club_id")

652 if not club_id:

653 return None

654

655 club_response = self.client.table("clubs").select("*").eq("id", club_id).execute()

656 if club_response.data and len(club_response.data) > 0:

657 return club_response.data[0]

658 return None

659

660 def get_team_game_counts(self) -> dict[int, int]:

661 """Get game counts for all teams in a single optimized query.

662

663 Returns a dictionary mapping team_id -> game_count.

664 """

665 try:

666 response = self.client.rpc("get_team_game_counts").execute()

667

668 if not response.data:

669 # Fallback to Python aggregation

670 matches = self.client.table("matches").select("home_team_id,away_team_id").execute()

671 counts = {}

672 for match in matches.data:

673 home_id = match["home_team_id"]

674 away_id = match["away_team_id"]

675 counts[home_id] = counts.get(home_id, 0) + 1

676 counts[away_id] = counts.get(away_id, 0) + 1

677 return counts

678

679 return {row["team_id"]: row["game_count"] for row in response.data}

680 except Exception:

681 logger.exception("Error getting team game counts")

682 return {}