from __future__ import annotations

import asyncio
import hashlib
import json
import re
import secrets
import time
from datetime import date, datetime, timedelta
from pathlib import Path
from typing import Any

from sqlalchemy import and_, delete, desc, func, or_, select
from sqlalchemy.orm import Session

from .client import Stat2TVClient, Stat2TVNotFoundError
from .config import DEFAULT_PERIOD_STATUS_LABELS, DEFAULT_STRENGTH_STATE_LABELS, HockeySettingsStore
from .database import HockeyDatabase
from .iso_country_codes import iso2_from_code
from .localization import (
    bilingual,
    choose_text,
    league_label,
    normalise_language,
    stage_key,
    stage_label,
)
from .match_parser import (
    MatchParseResult,
    ParsedGame,
    parse_date_value,
    parse_games_xml,
    parse_stat2tv_game_by_id,
    parse_score_pair,
    parse_time_value,
)
from .match_details_parser import (
    ParsedCoachProfile,
    ParsedCountry,
    ParsedCountries,
    ParsedMatchDetails,
    ParsedOfficialAssignment,
    ParsedPlayerProfile,
    ParsedRefereeProfile,
    ParsedRosterEntry,
    fallback_player_profile,
    fallback_referee_profile,
    parse_coaches_xml,
    parse_match_details_json,
    parse_player_countries_xml,
    parse_players_xml,
    parse_referees_xml,
)
from .models import (
    Coach,
    CoachTournament,
    Country,
    EndpointDiscovery,
    Game,
    GameControlState,
    GameOfficial,
    GameTeamStatistic,
    GameRoster,
    OperatorSession,
    PenaltyType,
    Player,
    PlayerTournament,
    PlayerSeasonStatistic,
    Referee,
    RefereeTournament,
    ShootoutAttempt,
    SyncRun,
    Team,
    TeamLeague,
    TeamSeasonStatistic,
    Tournament,
    TournamentStanding,
    TournamentStandingRow,
    TournamentStatisticResource,
    TournamentStatisticRow,
    UserPreference,
)
from .parser import ParsedTournament, parse_tournaments_xml
from .shots_parser import ParsedShots, parse_shots_json
from .match_visuals_parser import parse_match_events, parse_shots_map
from .standings_parser import parse_standings_xml
from .tournament_statistics_parser import parse_tournament_statistics_xml
from .navigation_labels import parse_navigation_labels_xml


def normalise_team_color_hex(value: Any) -> str:
    """Return an uppercase #RRGGBB value or an empty string for unset colours."""
    raw_color = str(value or "").strip().upper()
    if not raw_color:
        return ""
    if not raw_color.startswith("#"):
        raw_color = f"#{raw_color}"
    if not re.fullmatch(r"#[0-9A-F]{6}", raw_color):
        raise ValueError("Цвет команды должен быть в формате #RRGGBB")
    return raw_color


DEFAULT_PENALTY_TYPES: tuple[tuple[str, str, str, str], ...] = (
    ("TRIP", "Подножка", "Tripping", "2"),
    ("HOOK", "Задержка клюшкой", "Hooking", "2"),
    ("HOLD", "Задержка соперника", "Holding", "2"),
    (
        "INTERF",
        "Атака игрока, не владеющего шайбой",
        "Interference",
        "2",
    ),
    ("ROUGH", "Грубость", "Roughing", "2"),
    ("SLASH", "Удар клюшкой", "Slashing", "2"),
    (
        "HIGH",
        "Игра высоко поднятой клюшкой",
        "High-sticking",
        "2+2",
    ),
    ("BOARD", "Толчок на борт", "Boarding", "2"),
    ("CHARGE", "Неправильная атака", "Charging", "2"),
    ("ELBOW", "Удар локтем", "Elbowing", "2"),
    (
        "TOO_MANY",
        "Нарушение численного состава",
        "Too many players",
        "2",
    ),
    ("DELAY", "Задержка игры", "Delay of game", "2"),
    (
        "UNSPORT",
        "Неспортивное поведение",
        "Unsportsmanlike conduct",
        "10",
    ),
    ("FIGHT", "Драка", "Fighting", "5+20"),
)


class HockeyDataService:
    def __init__(
        self,
        *,
        base_dir: Path,
        database: HockeyDatabase,
        settings: HockeySettingsStore,
    ) -> None:
        self.base_dir = Path(base_dir)
        self.database = database
        self.settings = settings
        self.client = Stat2TVClient(settings)
        self.seed_file = self.base_dir / "data" / "tournaments_seed.xml"
        self.snapshot_dir = self.base_dir / "hockey_data" / "snapshots"
        self.snapshot_dir.mkdir(parents=True, exist_ok=True)
        self._live_locks: dict[str, asyncio.Lock] = {}
        self._live_response_cache: dict[str, tuple[float, Any]] = {}
        self._resource_response_cache: dict[str, tuple[float, Any]] = {}
        self._global_game_search_lock = asyncio.Lock()
        self._navigation_labels_cache: dict[str, tuple[float, dict[str, Any]]] = {}

    def bootstrap(self) -> dict[str, Any]:
        self.database.create_all()
        with self.database.session() as session:
            count = session.scalar(select(func.count(Tournament.id))) or 0
            self._seed_penalty_types(session)
            self._sync_team_directory_session(session)
            # Repair games imported by older builds: if a localized raw datetime
            # was stored but game_date stayed NULL, recover the real DATE now.
            self._backfill_missing_game_dates(session)
            normalized_backfill = self._backfill_normalized_statistics(session)
        if count == 0 and self.seed_file.exists():
            return self.import_tournaments_file(self.seed_file, source="seed")
        return {"ok": True, "source": "database", "count": int(count), "normalized_backfill": normalized_backfill}

    @staticmethod
    def _backfill_missing_game_dates(session: Session) -> int:
        """Recover DATE/time from raw Stat2TV/KHL strings already in the DB."""
        rows = session.scalars(
            select(Game).where(
                Game.game_date.is_(None),
                Game.start_datetime_raw.is_not(None),
                Game.start_datetime_raw != "",
            )
        ).all()
        repaired = 0
        for row in rows:
            parsed_date = parse_date_value(row.start_datetime_raw)
            parsed_time = parse_time_value(row.start_datetime_raw)
            if parsed_date is not None:
                row.game_date = parsed_date
                repaired += 1
            if not row.start_time and parsed_time:
                row.start_time = parsed_time
        return repaired

    @staticmethod
    def _seed_penalty_types(session: Session) -> int:
        count = session.scalar(select(func.count(PenaltyType.id))) or 0
        if count:
            return 0
        now = datetime.utcnow()
        for index, (code, name_ru, name_en, preset) in enumerate(
            DEFAULT_PENALTY_TYPES,
            start=1,
        ):
            session.add(
                PenaltyType(
                    code=code,
                    name_ru=name_ru,
                    name_en=name_en,
                    default_preset=preset,
                    sort_order=index * 10,
                    active=True,
                    created_at=now,
                    updated_at=now,
                )
            )
        return len(DEFAULT_PENALTY_TYPES)

    @staticmethod
    def _upsert_tournament(
        session: Session,
        item: ParsedTournament,
        *,
        now: datetime,
    ) -> None:
        existing = session.scalar(
            select(Tournament).where(Tournament.external_id == item.external_id)
        )
        if existing is None:
            existing = Tournament(external_id=item.external_id)
            session.add(existing)

        existing.level = item.level
        existing.name_ru = item.name_ru
        existing.name_en = item.name_en
        existing.common_name_ru = item.common_name_ru
        existing.common_name_en = item.common_name_en
        existing.season_part = item.season_part
        existing.season = item.season
        existing.start_date = item.start_date
        existing.end_date = item.end_date
        existing.round_code = item.round_code
        existing.game_id_supported = item.game_id_supported
        existing.raw_xml = item.raw_xml
        existing.synced_at = now

    def import_tournaments_content(
        self,
        content: bytes,
        *,
        source: str,
        endpoint: str = "tournaments.xml",
        auth_mode: str = "",
        status_code: int = 200,
    ) -> dict[str, Any]:
        started = datetime.utcnow()
        parsed = parse_tournaments_xml(content)
        digest = hashlib.sha256(content).hexdigest()
        stamp = started.strftime("%Y%m%d_%H%M%S_%f")
        snapshot_file = self.snapshot_dir / f"{stamp}_{digest[:12]}_tournaments.xml"
        snapshot_file.write_bytes(content)

        with self.database.session() as session:
            run = SyncRun(
                endpoint=endpoint,
                status="success",
                auth_mode=auth_mode,
                status_code=status_code,
                item_count=len(parsed),
                source=source,
                snapshot_path=str(snapshot_file.relative_to(self.base_dir)),
                started_at=started,
                finished_at=datetime.utcnow(),
            )
            session.add(run)
            now = datetime.utcnow()
            for item in parsed:
                self._upsert_tournament(session, item, now=now)

        return {
            "ok": True,
            "source": source,
            "count": len(parsed),
            "sha256": digest,
            "snapshot": str(snapshot_file.relative_to(self.base_dir)),
        }

    def import_tournaments_file(
        self,
        path: Path,
        *,
        source: str = "file",
    ) -> dict[str, Any]:
        return self.import_tournaments_content(
            Path(path).read_bytes(),
            source=source,
            endpoint=Path(path).name,
        )

    async def sync_tournaments(self) -> dict[str, Any]:
        started = datetime.utcnow()
        try:
            response = await self.client.fetch_xml("tournaments.xml")
            return self.import_tournaments_content(
                response.content,
                source="stat2tv",
                endpoint=response.url,
                auth_mode=response.auth_mode,
                status_code=response.status_code,
            )
        except Exception as error:
            with self.database.session() as session:
                session.add(
                    SyncRun(
                        endpoint="tournaments.xml",
                        status="error",
                        source="stat2tv",
                        error=str(error),
                        started_at=started,
                        finished_at=datetime.utcnow(),
                    )
                )
            raise

    async def test_connection(self) -> dict[str, Any]:
        response = await self.client.fetch_xml("tournaments.xml")
        parsed = parse_tournaments_xml(response.content)
        return {
            "ok": True,
            "status_code": response.status_code,
            "auth_mode": response.auth_mode,
            "content_type": response.content_type,
            "items_found": len(parsed),
            "url": response.url,
        }

    def last_sync(self) -> dict[str, Any] | None:
        with self.database.session() as session:
            row = session.scalar(select(SyncRun).order_by(desc(SyncRun.id)).limit(1))
            if row is None:
                return None
            return {
                "status": row.status,
                "source": row.source,
                "endpoint": row.endpoint,
                "item_count": row.item_count,
                "error": row.error,
                "finished_at": row.finished_at.isoformat() if row.finished_at else None,
            }

    def _serialize_tournament(
        self,
        row: Tournament,
        *,
        language: str,
        fallback_language: str,
    ) -> dict[str, Any]:
        common = choose_text(
            row.common_name_ru,
            row.common_name_en,
            language=language,
            fallback_language=fallback_language,
        )
        full = choose_text(
            row.name_ru,
            row.name_en,
            language=language,
            fallback_language=fallback_language,
        )
        return {
            "id": row.external_id,
            "external_id": row.external_id,
            "level": row.level,
            "league": league_label(row.level, language),
            "season": row.season,
            "season_part": row.season_part,
            "stage_key": stage_key(row.season_part, row.round_code),
            "stage": stage_label(row.season_part, row.round_code, language),
            "round": row.round_code,
            "start_date": row.start_date.isoformat() if row.start_date else None,
            "end_date": row.end_date.isoformat() if row.end_date else None,
            "game_id_supported": row.game_id_supported,
            "name": common or full,
            "full_name": full or common,
            "names": {
                "common": bilingual(row.common_name_ru, row.common_name_en),
                "full": bilingual(row.name_ru, row.name_en),
            },
        }

    def tournament(
        self,
        external_id: str,
        *,
        language: str,
    ) -> dict[str, Any] | None:
        settings = self.settings.public()
        language = normalise_language(language, settings["ui_language"])
        with self.database.session() as session:
            row = session.scalar(
                select(Tournament).where(Tournament.external_id == str(external_id))
            )
            return (
                self._serialize_tournament(
                    row,
                    language=language,
                    fallback_language=settings["fallback_language"],
                )
                if row
                else None
            )

    @staticmethod
    def _normalise_league_key(value: str) -> str:
        key = "".join(
            character
            for character in str(value or "").strip().lower()
            if character.isalnum() or character in {"_", "-"}
        )
        return key[:32]

    def _tournament_league_key(
        self,
        session: Session,
        tournament_external_id: str,
    ) -> str:
        tournament = session.scalar(
            select(Tournament).where(
                Tournament.external_id == str(tournament_external_id)
            )
        )
        if tournament is None:
            return ""
        key = self._normalise_league_key(tournament.level)
        if key:
            return key
        searchable = " ".join(
            (
                tournament.name_ru,
                tournament.name_en,
                tournament.common_name_ru,
                tournament.common_name_en,
            )
        ).lower()
        for marker, fallback in (
            ("кхл", "khl"),
            ("kontinental hockey", "khl"),
            ("вхл", "vhl"),
            ("мхл", "mhl"),
            ("жхл", "whl"),
        ):
            if marker in searchable:
                return fallback
        return "other"

    @staticmethod
    def _directory_team_id(raw_id: str, *, league_key: str, name: str) -> str:
        value = str(raw_id or "").strip()
        if value and len(value) <= 64:
            return value
        material = value or f"{league_key}|{name}"
        return "team-" + hashlib.sha1(material.encode("utf-8")).hexdigest()[:40]

    def _sync_team_directory_session(
        self,
        session: Session,
        *,
        tournament_external_id: str = "",
    ) -> dict[str, int]:
        statement = select(Game)
        if tournament_external_id:
            statement = statement.where(
                Game.tournament_external_id == str(tournament_external_id)
            )
        games = list(session.scalars(statement))
        leagues = {
            tournament_id: self._tournament_league_key(session, tournament_id)
            for tournament_id in {
                game.tournament_external_id for game in games
            }
        }
        created = 0
        updated = 0
        seen: set[tuple[str, str]] = set()
        now = datetime.utcnow()

        for game in games:
            league_key = leagues.get(game.tournament_external_id, "")
            if not league_key:
                continue
            for side in ("home", "away"):
                raw_id = str(
                    getattr(game, f"{side}_team_external_id")
                    or getattr(game, f"{side}_entry_external_id")
                    or ""
                ).strip()
                name_ru = str(getattr(game, f"{side}_team_name_ru") or "").strip()
                name_en = str(getattr(game, f"{side}_team_name_en") or "").strip()
                external_id = self._directory_team_id(
                    raw_id,
                    league_key=league_key,
                    name=name_ru or name_en,
                )
                identity = (league_key, external_id)
                if identity in seen or not (raw_id or name_ru or name_en):
                    continue
                seen.add(identity)

                team = session.scalar(
                    select(Team).where(Team.external_id == external_id)
                )
                if team is None:
                    team = Team(external_id=external_id)
                    session.add(team)
                    session.flush()

                membership = session.scalar(
                    select(TeamLeague).where(
                        TeamLeague.team_id == team.id,
                        TeamLeague.league_key == league_key,
                    )
                )
                manual = membership is not None and membership.source == "manual"
                if not manual:
                    team.name_ru = name_ru or team.name_ru
                    team.name_en = name_en or team.name_en
                    team.short_name_ru = str(
                        getattr(game, f"{side}_team_short_ru") or ""
                    ).strip() or team.short_name_ru
                    team.short_name_en = str(
                        getattr(game, f"{side}_team_short_en") or ""
                    ).strip() or team.short_name_en
                    team.city_ru = str(
                        getattr(game, f"{side}_team_city_ru") or ""
                    ).strip() or team.city_ru
                    team.city_en = str(
                        getattr(game, f"{side}_team_city_en") or ""
                    ).strip() or team.city_en
                    team.raw_payload = json.dumps(
                        {
                            "source": "schedule",
                            "original_team_id": raw_id,
                            "game_id": game.external_id,
                            "tournament_id": game.tournament_external_id,
                        },
                        ensure_ascii=False,
                    )
                    team.synced_at = now

                if membership is None:
                    membership = TeamLeague(
                        team_id=team.id,
                        league_key=league_key,
                        tournament_external_id=game.tournament_external_id,
                        source="stat2tv",
                        active=True,
                        synced_at=now,
                    )
                    session.add(membership)
                    created += 1
                else:
                    membership.tournament_external_id = game.tournament_external_id
                    membership.synced_at = now
                    if not manual:
                        membership.source = "stat2tv"
                        membership.active = True
                    updated += 1
        return {"created": created, "updated": updated, "games": len(games)}

    def sync_team_directory(
        self,
        *,
        tournament_external_id: str = "",
    ) -> dict[str, Any]:
        with self.database.session() as session:
            result = self._sync_team_directory_session(
                session,
                tournament_external_id=tournament_external_id,
            )
        return {"ok": True, **result}

    def _serialize_directory_team(
        self,
        team: Team,
        membership: TeamLeague,
        *,
        language: str,
    ) -> dict[str, Any]:
        settings = self.settings.public()
        return {
            "id": membership.id,
            "team_id": team.id,
            "external_id": team.external_id,
            "league_key": membership.league_key,
            "league": league_label(membership.league_key, language),
            "tournament_external_id": membership.tournament_external_id,
            "name": choose_text(
                team.name_ru,
                team.name_en,
                language=language,
                fallback_language=settings["fallback_language"],
            ),
            "name_ru": team.name_ru,
            "name_en": team.name_en,
            "short_name_ru": team.short_name_ru,
            "short_name_en": team.short_name_en,
            "city_ru": team.city_ru,
            "city_en": team.city_en,
            "color_hex": team.color_hex,
            "logo_url": team.logo_url,
            "source": membership.source,
            "active": bool(membership.active),
            "synced_at": (
                membership.synced_at.isoformat() if membership.synced_at else None
            ),
        }

    def team_directory(
        self,
        *,
        language: str = "ru",
        tournament_external_id: str = "",
        league_key: str = "",
        include_inactive: bool = False,
    ) -> dict[str, Any]:
        settings = self.settings.public()
        language = normalise_language(language, settings["ui_language"])
        with self.database.session() as session:
            selected_league = self._normalise_league_key(league_key)
            if tournament_external_id:
                selected_league = self._tournament_league_key(
                    session,
                    tournament_external_id,
                )

            statement = select(Team, TeamLeague).join(
                TeamLeague,
                TeamLeague.team_id == Team.id,
            )
            if selected_league:
                statement = statement.where(
                    TeamLeague.league_key == selected_league
                )
            if not include_inactive:
                statement = statement.where(TeamLeague.active.is_(True))
            rows = session.execute(
                statement.order_by(
                    TeamLeague.league_key,
                    Team.name_ru,
                    Team.name_en,
                    Team.external_id,
                )
            ).all()
            league_values = list(
                session.scalars(
                    select(TeamLeague.league_key)
                    .distinct()
                    .order_by(TeamLeague.league_key)
                )
            )
            items = [
                self._serialize_directory_team(
                    team,
                    membership,
                    language=language,
                )
                for team, membership in rows
            ]
        return {
            "language": language,
            "tournament_external_id": tournament_external_id,
            "selected_league": selected_league,
            "selected_league_label": (
                league_label(selected_league, language) if selected_league else ""
            ),
            "leagues": [
                {
                    "value": value,
                    "label": league_label(value, language),
                }
                for value in league_values
            ],
            "count": len(items),
            "items": items,
        }

    def save_directory_team(
        self,
        payload: dict[str, Any],
        *,
        membership_id: int | None = None,
        language: str = "ru",
    ) -> dict[str, Any]:
        with self.database.session() as session:
            membership = session.get(TeamLeague, membership_id) if membership_id else None
            if membership_id and membership is None:
                raise ValueError("Команда в справочнике не найдена")
            team = session.get(Team, membership.team_id) if membership else None

            external_id = str(payload.get("external_id") or "").strip()
            if team is None:
                if not external_id:
                    raise ValueError("Укажите ID команды из API")
                if len(external_id) > 64:
                    raise ValueError("ID команды не должен быть длиннее 64 символов")
                team = session.scalar(
                    select(Team).where(Team.external_id == external_id)
                )
                if team is None:
                    team = Team(external_id=external_id)
                    session.add(team)
                    session.flush()

            tournament_id = str(
                payload.get("tournament_external_id")
                or (membership.tournament_external_id if membership else "")
                or ""
            ).strip()
            league_key = self._normalise_league_key(
                str(
                    payload.get("league_key")
                    or (membership.league_key if membership else "")
                    or ""
                )
            )
            if not league_key and tournament_id:
                league_key = self._tournament_league_key(session, tournament_id)
            if not league_key:
                raise ValueError("Укажите лигу команды")

            if membership is None:
                membership = session.scalar(
                    select(TeamLeague).where(
                        TeamLeague.team_id == team.id,
                        TeamLeague.league_key == league_key,
                    )
                )
            if membership is None:
                membership = TeamLeague(
                    team_id=team.id,
                    league_key=league_key,
                )
                session.add(membership)

            for field in (
                "name_ru",
                "name_en",
                "short_name_ru",
                "short_name_en",
                "city_ru",
                "city_en",
                "logo_url",
            ):
                if field in payload and payload[field] is not None:
                    setattr(team, field, str(payload[field]).strip())

            if "color_hex" in payload and payload["color_hex"] is not None:
                team.color_hex = normalise_team_color_hex(payload["color_hex"])

            if not (team.name_ru or team.name_en):
                raise ValueError("Укажите название команды на русском или английском")

            now = datetime.utcnow()
            team.synced_at = now
            membership.league_key = league_key
            membership.tournament_external_id = tournament_id
            membership.source = "manual"
            membership.active = bool(payload.get("active", True))
            membership.synced_at = now
            session.flush()
            return self._serialize_directory_team(
                team,
                membership,
                language=normalise_language(language),
            )

    @staticmethod
    def _age_on_date(birth_date: date | None, on_date: date | None = None) -> int | None:
        if birth_date is None:
            return None
        today = on_date or date.today()
        return today.year - birth_date.year - (
            (today.month, today.day) < (birth_date.month, birth_date.day)
        )

    async def sync_country_directory(self) -> dict[str, Any]:
        settings = self.settings.public()
        endpoint = str(settings.get("player_countries_endpoint_template") or "players-countries.xml").strip()
        response = await self._fetch_cached_resource(
            endpoint, cache_seconds=float(settings["match_directories_cache_seconds"])
        )
        parsed = parse_player_countries_xml(response.content)
        now = datetime.utcnow()
        with self.database.session() as session:
            self._store_tournament_directory_profiles(
                session,
                tournament_external_id="",
                player_profiles={}, referee_profiles={}, coach_profiles={},
                countries=parsed, now=now,
            )
            # Old builds could create an empty country with country_id=0 from
            # players that had no nationality assigned. It is not a country and
            # must not remain visible in the directory.
            empty_country = session.scalar(select(Country).where(Country.external_id == "0"))
            if empty_country is not None:
                session.delete(empty_country)
        return {
            "ok": True,
            "endpoint": endpoint,
            "countries": len(parsed.countries),
            "player_links": len(parsed.player_country),
        }

    def country_directory(
        self,
        *,
        language: str = "ru",
        include_inactive: bool = False,
        search: str = "",
    ) -> dict[str, Any]:
        settings = self.settings.public()
        language = normalise_language(language, settings["ui_language"])
        query = str(search or "").strip().casefold()
        with self.database.session() as session:
            statement = select(Country)
            if not include_inactive:
                statement = statement.where(Country.active.is_(True))
            rows = list(session.scalars(statement.order_by(Country.name_ru, Country.name_en, Country.iso2)))
            if query:
                rows = [
                    row for row in rows
                    if query in " ".join((row.external_id, row.iso2, row.iso3, row.name_ru, row.name_en)).casefold()
                ]
        return {
            "language": language,
            "count": len(rows),
            "items": [
                {
                    "id": row.id,
                    "external_id": row.external_id,
                    "iso2": row.iso2,
                    "iso3": row.iso3,
                    "name": choose_text(row.name_ru, row.name_en, language=language, fallback_language=settings["fallback_language"]),
                    "name_ru": row.name_ru,
                    "name_en": row.name_en,
                    "flag": row.flag_emoji or self._country_flag(row.iso2),
                    "flag_url": f"/hockey-assets/flags/{row.iso2.lower()}.svg" if len(row.iso2 or "") == 2 else "",
                    "active": bool(row.active),
                    "synced_at": row.synced_at.isoformat() if row.synced_at else None,
                }
                for row in rows
            ],
        }

    def save_country(self, payload: dict[str, Any], *, country_id: int | None = None, language: str = "ru") -> dict[str, Any]:
        with self.database.session() as session:
            row = session.get(Country, country_id) if country_id else None
            external_id = str(payload.get("external_id") or (row.external_id if row else "")).strip()
            raw_iso2 = "".join(ch for ch in str(payload.get("iso2") or "").upper() if ch.isalpha())
            iso3 = "".join(ch for ch in str(payload.get("iso3") or "").upper() if ch.isalpha())[:3]
            iso2 = (iso2_from_code(raw_iso2) or iso2_from_code(iso3) or raw_iso2[:2])
            if row is None:
                if not external_id:
                    external_id = iso3 or iso2
                if not external_id:
                    raise ValueError("Укажите ID или ISO-код страны")
                row = session.scalar(select(Country).where(Country.external_id == external_id))
                if row is None:
                    row = Country(external_id=external_id)
                    session.add(row)
            row.iso2 = iso2
            row.iso3 = iso3
            row.name_ru = str(payload.get("name_ru") or row.name_ru or "").strip()
            row.name_en = str(payload.get("name_en") or row.name_en or "").strip()
            row.flag_emoji = self._country_flag(iso2)
            row.active = bool(payload.get("active", True))
            row.synced_at = datetime.utcnow()
            session.flush()
            return {
                "ok": True,
                "id": row.id,
                "external_id": row.external_id,
                "iso2": row.iso2,
                "iso3": row.iso3,
                "name_ru": row.name_ru,
                "name_en": row.name_en,
                "flag": row.flag_emoji,
                "flag_url": f"/hockey-assets/flags/{row.iso2.lower()}.svg" if len(row.iso2 or "") == 2 else "",
                "active": bool(row.active),
            }

    async def sync_player_directory(self, *, tournament_external_id: str) -> dict[str, Any]:
        settings = self.settings.public()
        player_endpoint = self._resolve_match_resource_endpoint(
            settings["players_endpoint_template"],
            tournament_external_id=tournament_external_id,
            game_external_id="",
        )
        country_endpoint = str(settings.get("player_countries_endpoint_template") or "players-countries.xml")
        responses = await asyncio.gather(
            self._fetch_cached_resource(player_endpoint, cache_seconds=float(settings["match_directories_cache_seconds"])),
            self._fetch_cached_resource(country_endpoint, cache_seconds=float(settings["match_directories_cache_seconds"])),
            return_exceptions=True,
        )
        if isinstance(responses[0], Exception):
            raise responses[0]
        players_response = responses[0]
        player_profiles = parse_players_xml(players_response.content)
        coach_profiles = parse_coaches_xml(players_response.content)
        countries = None if isinstance(responses[1], Exception) else parse_player_countries_xml(responses[1].content)
        now = datetime.utcnow()
        with self.database.session() as session:
            counts = self._store_tournament_directory_profiles(
                session,
                tournament_external_id=tournament_external_id,
                player_profiles=player_profiles,
                referee_profiles={},
                coach_profiles=coach_profiles,
                countries=countries,
                now=now,
            )
        print(
            f"[Hockey DB] players updated tournament={tournament_external_id} "
            f"players={counts.get('players', 0)} coaches={counts.get('coaches', 0)}"
        )
        return {"ok": True, "endpoint": player_endpoint, **counts}

    def player_directory(
        self,
        *,
        tournament_external_id: str = "",
        language: str = "ru",
        include_inactive: bool = False,
        search: str = "",
    ) -> dict[str, Any]:
        settings = self.settings.public()
        language = normalise_language(language, settings["ui_language"])
        query = str(search or "").strip().casefold()
        with self.database.session() as session:
            statement = select(Player, PlayerTournament).join(
                PlayerTournament, PlayerTournament.player_id == Player.id, isouter=True
            )
            if tournament_external_id:
                statement = statement.where(PlayerTournament.tournament_external_id == str(tournament_external_id))
            if not include_inactive:
                statement = statement.where(Player.active.is_(True))
            rows = session.execute(statement.order_by(Player.last_name_ru, Player.first_name_ru, Player.external_id)).all()
            country_ids = {player.country_external_id for player, _ in rows if player.country_external_id}
            countries = {
                row.external_id: row for row in session.scalars(select(Country).where(Country.external_id.in_(country_ids)))
            } if country_ids else {}
            items = []
            seen: set[tuple[int, str]] = set()
            for player, membership in rows:
                key = (player.id, membership.tournament_external_id if membership else "")
                if key in seen:
                    continue
                seen.add(key)
                full_ru = player.full_name_ru or " ".join(filter(None, (player.last_name_ru, player.first_name_ru)))
                full_en = player.full_name_en or " ".join(filter(None, (player.last_name_en, player.first_name_en)))
                searchable = " ".join((player.external_id, full_ru, full_en, player.position_ru, player.position_en, membership.team_name_ru if membership else "", membership.team_name_en if membership else "")).casefold()
                if query and query not in searchable:
                    continue
                country = countries.get(player.country_external_id)
                items.append({
                    "id": player.id,
                    "external_id": player.external_id,
                    "name": choose_text(full_ru, full_en, language=language, fallback_language=settings["fallback_language"]),
                    "full_name_ru": full_ru,
                    "full_name_en": full_en,
                    "first_name_ru": player.first_name_ru,
                    "first_name_en": player.first_name_en,
                    "last_name_ru": player.last_name_ru,
                    "last_name_en": player.last_name_en,
                    "position_ru": player.position_ru,
                    "position_en": player.position_en,
                    "birth_date": player.birth_date.isoformat() if player.birth_date else None,
                    "age": self._age_on_date(player.birth_date),
                    "height_cm": player.height_cm,
                    "weight_kg": player.weight_kg,
                    "stick": player.stick,
                    "country_external_id": player.country_external_id,
                    "country_code": player.country_code or (country.iso2 if country else ""),
                    "country_name": choose_text(country.name_ru, country.name_en, language=language, fallback_language=settings["fallback_language"]) if country else "",
                    "flag": (country.flag_emoji or self._country_flag(country.iso2)) if country else self._country_flag(player.country_code),
                    "photo_url": player.photo_url,
                    "active": bool(player.active),
                    "tournament_external_id": membership.tournament_external_id if membership else "",
                    "club_external_id": membership.club_external_id if membership else "",
                    "team_entry_external_id": membership.team_entry_external_id if membership else "",
                    "team_name_ru": membership.team_name_ru if membership else "",
                    "team_name_en": membership.team_name_en if membership else "",
                    "jersey_number": membership.jersey_number if membership else "",
                    "synced_at": player.synced_at.isoformat() if player.synced_at else None,
                })
        return {"language": language, "tournament_external_id": tournament_external_id, "count": len(items), "items": items}

    def save_player(self, payload: dict[str, Any], *, player_id: int | None = None, language: str = "ru") -> dict[str, Any]:
        with self.database.session() as session:
            row = session.get(Player, player_id) if player_id else None
            external_id = str(payload.get("external_id") or (row.external_id if row else "")).strip()
            if row is None:
                if not external_id:
                    raise ValueError("Укажите ID игрока")
                row = session.scalar(select(Player).where(Player.external_id == external_id)) or Player(external_id=external_id)
                session.add(row)
            for field in ("first_name_ru", "first_name_en", "last_name_ru", "last_name_en", "full_name_ru", "full_name_en", "position_ru", "position_en", "stick", "country_external_id", "photo_url"):
                if field in payload:
                    setattr(row, field, str(payload.get(field) or "").strip())
            if "country_code" in payload:
                raw_country_code = str(payload.get("country_code") or "").strip().upper()
                row.country_code = iso2_from_code(raw_country_code) or raw_country_code
            row.birth_date = parse_date_value(str(payload.get("birth_date") or "")) if "birth_date" in payload else row.birth_date
            if "height_cm" in payload: row.height_cm = max(0, int(payload.get("height_cm") or 0))
            if "weight_kg" in payload: row.weight_kg = max(0, int(payload.get("weight_kg") or 0))
            row.active = bool(payload.get("active", True))
            row.synced_at = datetime.utcnow()
            session.flush()
            tournament_id = str(payload.get("tournament_external_id") or "").strip()
            if tournament_id:
                profile = ParsedPlayerProfile(
                    external_id=row.external_id,
                    full_name_ru=row.full_name_ru,
                    full_name_en=row.full_name_en,
                    raw_payload=json.dumps({
                        "clubidg": payload.get("club_external_id") or "",
                        "clubidt": payload.get("team_entry_external_id") or "",
                        "teamname": payload.get("team_name_ru") or "",
                        "teamnameen": payload.get("team_name_en") or "",
                        "jn": payload.get("jersey_number") or "",
                    }, ensure_ascii=False),
                )
                self._upsert_player_tournament(session, player=row, profile=profile, tournament_external_id=tournament_id, now=row.synced_at)
            return {"ok": True, "id": row.id, "external_id": row.external_id}

    async def sync_referee_directory(self, *, tournament_external_id: str) -> dict[str, Any]:
        settings = self.settings.public()
        endpoint = self._resolve_match_resource_endpoint(
            settings["referees_endpoint_template"],
            tournament_external_id=tournament_external_id,
            game_external_id="",
        )
        response = await self._fetch_cached_resource(endpoint, cache_seconds=float(settings["match_directories_cache_seconds"]))
        profiles = parse_referees_xml(response.content)
        now = datetime.utcnow()
        with self.database.session() as session:
            self._store_tournament_directory_profiles(
                session, tournament_external_id=tournament_external_id,
                player_profiles={}, referee_profiles=profiles, coach_profiles={},
                countries=None, now=now,
            )
        return {"ok": True, "endpoint": endpoint, "referees": len(profiles)}

    def referee_directory(self, *, tournament_external_id: str = "", language: str = "ru", include_inactive: bool = False, search: str = "") -> dict[str, Any]:
        settings = self.settings.public()
        language = normalise_language(language, settings["ui_language"])
        query = str(search or "").strip().casefold()
        with self.database.session() as session:
            statement = select(Referee).join(
                RefereeTournament, RefereeTournament.referee_id == Referee.id, isouter=True
            )
            if tournament_external_id:
                statement = statement.where(RefereeTournament.tournament_external_id == str(tournament_external_id))
            if not include_inactive:
                statement = statement.where(Referee.active.is_(True))
            rows = list(session.scalars(statement.order_by(Referee.last_name_ru, Referee.first_name_ru, Referee.external_id)).unique())
            items = []
            for row in rows:
                full_ru = row.full_name_ru or " ".join(filter(None, (row.last_name_ru, row.first_name_ru)))
                full_en = row.full_name_en or " ".join(filter(None, (row.last_name_en, row.first_name_en)))
                if query and query not in " ".join((row.external_id, full_ru, full_en, row.number, row.position_code)).casefold():
                    continue
                items.append({
                    "id": row.id, "external_id": row.external_id,
                    "name": choose_text(full_ru, full_en, language=language, fallback_language=settings["fallback_language"]),
                    "full_name_ru": full_ru, "full_name_en": full_en,
                    "first_name_ru": row.first_name_ru, "first_name_en": row.first_name_en,
                    "last_name_ru": row.last_name_ru, "last_name_en": row.last_name_en,
                    "position_code": row.position_code, "number": row.number,
                    "birth_date": row.birth_date.isoformat() if row.birth_date else None,
                    "age": self._age_on_date(row.birth_date),
                    "country_ru": row.country_ru, "country_en": row.country_en,
                    "country_code": row.country_code, "flag": self._country_flag(row.country_code),
                    "town_ru": row.town_ru, "town_en": row.town_en,
                    "active": bool(row.active), "synced_at": row.synced_at.isoformat() if row.synced_at else None,
                })
        return {"language": language, "tournament_external_id": tournament_external_id, "count": len(items), "items": items}

    def save_referee(self, payload: dict[str, Any], *, referee_id: int | None = None) -> dict[str, Any]:
        with self.database.session() as session:
            row = session.get(Referee, referee_id) if referee_id else None
            external_id = str(payload.get("external_id") or (row.external_id if row else "")).strip()
            if row is None:
                if not external_id: raise ValueError("Укажите ID судьи")
                row = session.scalar(select(Referee).where(Referee.external_id == external_id)) or Referee(external_id=external_id)
                session.add(row)
            for field in ("first_name_ru", "first_name_en", "last_name_ru", "last_name_en", "full_name_ru", "full_name_en", "position_code", "number", "country_ru", "country_en", "town_ru", "town_en"):
                if field in payload: setattr(row, field, str(payload.get(field) or "").strip())
            if "country_code" in payload:
                raw_country_code = str(payload.get("country_code") or "").strip().upper()
                row.country_code = iso2_from_code(raw_country_code) or raw_country_code
            if "birth_date" in payload: row.birth_date = parse_date_value(str(payload.get("birth_date") or ""))
            row.active = bool(payload.get("active", True)); row.synced_at = datetime.utcnow(); session.flush()
            return {"ok": True, "id": row.id, "external_id": row.external_id}

    def coach_directory(self, *, tournament_external_id: str = "", language: str = "ru", include_inactive: bool = False, search: str = "") -> dict[str, Any]:
        settings = self.settings.public(); language = normalise_language(language, settings["ui_language"]); query = str(search or "").strip().casefold()
        with self.database.session() as session:
            statement = select(Coach, CoachTournament).join(CoachTournament, CoachTournament.coach_id == Coach.id, isouter=True)
            if tournament_external_id: statement = statement.where(CoachTournament.tournament_external_id == str(tournament_external_id))
            if not include_inactive: statement = statement.where(Coach.active.is_(True))
            rows = session.execute(statement.order_by(Coach.last_name_ru, Coach.first_name_ru, Coach.external_id)).all()
            items=[]; seen=set()
            for coach,membership in rows:
                key=(coach.id, membership.tournament_external_id if membership else "", membership.team_external_id if membership else "")
                if key in seen: continue
                seen.add(key)
                full_ru=coach.full_name_ru or " ".join(filter(None,(coach.last_name_ru,coach.first_name_ru)))
                full_en=coach.full_name_en or " ".join(filter(None,(coach.last_name_en,coach.first_name_en)))
                searchable=" ".join((coach.external_id,full_ru,full_en,membership.team_name_ru if membership else "",membership.team_name_en if membership else "")).casefold()
                if query and query not in searchable: continue
                items.append({
                    "id":coach.id,"external_id":coach.external_id,
                    "name":choose_text(full_ru,full_en,language=language,fallback_language=settings["fallback_language"]),
                    "full_name_ru":full_ru,"full_name_en":full_en,
                    "first_name_ru":coach.first_name_ru,"first_name_en":coach.first_name_en,
                    "last_name_ru":coach.last_name_ru,"last_name_en":coach.last_name_en,
                    "birth_date":coach.birth_date.isoformat() if coach.birth_date else None,"age":self._age_on_date(coach.birth_date),
                    "country_external_id":coach.country_external_id,"country_code":coach.country_code,"flag":self._country_flag(coach.country_code),
                    "photo_url":coach.photo_url,"active":bool(coach.active),
                    "tournament_external_id":membership.tournament_external_id if membership else "",
                    "team_external_id":membership.team_external_id if membership else "",
                    "team_name_ru":membership.team_name_ru if membership else "","team_name_en":membership.team_name_en if membership else "",
                    "role_ru":membership.role_ru if membership else "","role_en":membership.role_en if membership else "",
                    "synced_at":coach.synced_at.isoformat() if coach.synced_at else None,
                })
        return {"language":language,"tournament_external_id":tournament_external_id,"count":len(items),"items":items}

    async def sync_coach_directory(self, *, tournament_external_id: str) -> dict[str, Any]:
        result = await self.sync_player_directory(tournament_external_id=tournament_external_id)
        directory = self.coach_directory(tournament_external_id=tournament_external_id, include_inactive=True)
        return {"ok": True, "coaches": directory["count"], "players_xml": result.get("endpoint", "")}

    def save_coach(self, payload: dict[str, Any], *, coach_id: int | None = None) -> dict[str, Any]:
        with self.database.session() as session:
            row=session.get(Coach,coach_id) if coach_id else None
            external_id=str(payload.get("external_id") or (row.external_id if row else "")).strip()
            if row is None:
                if not external_id: raise ValueError("Укажите ID тренера")
                row=session.scalar(select(Coach).where(Coach.external_id==external_id)) or Coach(external_id=external_id); session.add(row)
            for field in ("first_name_ru","first_name_en","last_name_ru","last_name_en","full_name_ru","full_name_en","country_external_id","photo_url"):
                if field in payload: setattr(row,field,str(payload.get(field) or "").strip())
            if "country_code" in payload:
                raw_country_code=str(payload.get("country_code") or "").strip().upper()
                row.country_code=iso2_from_code(raw_country_code) or raw_country_code
            if "birth_date" in payload: row.birth_date=parse_date_value(str(payload.get("birth_date") or ""))
            row.active=bool(payload.get("active",True)); row.synced_at=datetime.utcnow(); session.flush()
            tournament_id=str(payload.get("tournament_external_id") or "").strip()
            if tournament_id:
                profile=ParsedCoachProfile(external_id=row.external_id,full_name_ru=row.full_name_ru,full_name_en=row.full_name_en,team_external_id=str(payload.get("team_external_id") or ""),team_name_ru=str(payload.get("team_name_ru") or ""),team_name_en=str(payload.get("team_name_en") or ""),role_ru=str(payload.get("role_ru") or "Главный тренер"),role_en=str(payload.get("role_en") or "Head coach"))
                self._upsert_coach_tournament(session,coach=row,profile=profile,tournament_external_id=tournament_id,now=row.synced_at)
            return {"ok":True,"id":row.id,"external_id":row.external_id}

    @staticmethod
    def _serialize_penalty_type(
        row: PenaltyType,
        *,
        language: str,
        fallback_language: str,
    ) -> dict[str, Any]:
        return {
            "id": row.id,
            "code": row.code,
            "name": choose_text(
                row.name_ru,
                row.name_en,
                language=language,
                fallback_language=fallback_language,
            ),
            "name_ru": row.name_ru,
            "name_en": row.name_en,
            "default_preset": row.default_preset,
            "team_penalty": bool(row.team_penalty),
            "sort_order": row.sort_order,
            "active": bool(row.active),
            "updated_at": row.updated_at.isoformat() if row.updated_at else None,
        }

    def penalty_directory(
        self,
        *,
        language: str = "ru",
        include_inactive: bool = False,
    ) -> dict[str, Any]:
        settings = self.settings.public()
        language = normalise_language(language, settings["ui_language"])
        with self.database.session() as session:
            statement = select(PenaltyType)
            if not include_inactive:
                statement = statement.where(PenaltyType.active.is_(True))
            rows = list(
                session.scalars(
                    statement.order_by(
                        PenaltyType.sort_order,
                        PenaltyType.name_ru,
                        PenaltyType.id,
                    )
                )
            )
        return {
            "language": language,
            "count": len(rows),
            "items": [
                self._serialize_penalty_type(
                    row,
                    language=language,
                    fallback_language=settings["fallback_language"],
                )
                for row in rows
            ],
        }

    def save_penalty_type(
        self,
        payload: dict[str, Any],
        *,
        penalty_id: int | None = None,
        language: str = "ru",
    ) -> dict[str, Any]:
        with self.database.session() as session:
            row = session.get(PenaltyType, penalty_id) if penalty_id else None
            if penalty_id and row is None:
                raise ValueError("Удаление в справочнике не найдено")

            code = "".join(
                character
                for character in str(payload.get("code") or "").strip().upper()
                if character.isalnum() or character in {"_", "-"}
            )[:64]
            if not code:
                raise ValueError("Укажите код удаления")
            duplicate = session.scalar(
                select(PenaltyType).where(PenaltyType.code == code)
            )
            if duplicate is not None and duplicate.id != penalty_id:
                raise ValueError(f"Код {code} уже используется")

            name_ru = str(payload.get("name_ru") or "").strip()
            name_en = str(payload.get("name_en") or "").strip()
            if not name_ru or not name_en:
                raise ValueError("Заполните название на русском и английском")

            now = datetime.utcnow()
            if row is None:
                next_order = int(
                    session.scalar(select(func.max(PenaltyType.sort_order))) or 0
                ) + 10
                row = PenaltyType(
                    code=code,
                    created_at=now,
                    sort_order=next_order,
                )
                session.add(row)
            row.code = code
            row.name_ru = name_ru
            row.name_en = name_en
            row.default_preset = str(
                payload.get("default_preset") or "2"
            ).strip()[:32]
            row.team_penalty = bool(payload.get("team_penalty", False))
            if payload.get("sort_order") is not None:
                row.sort_order = int(payload["sort_order"])
            row.active = bool(payload.get("active", True))
            row.updated_at = now
            session.flush()
            settings = self.settings.public()
            return self._serialize_penalty_type(
                row,
                language=normalise_language(language, settings["ui_language"]),
                fallback_language=settings["fallback_language"],
            )

    def navigation(
        self,
        *,
        language: str,
        on_date: date,
        search: str = "",
        league: str = "",
        season: str = "",
        stage: str = "",
        limit: int = 600,
    ) -> dict[str, Any]:
        settings = self.settings.public()
        language = normalise_language(language, settings["ui_language"])
        fallback_language = settings["fallback_language"]
        search_value = search.strip().lower()

        with self.database.session() as session:
            all_rows = list(
                session.scalars(
                    select(Tournament).order_by(
                        desc(Tournament.season),
                        Tournament.level,
                        Tournament.start_date,
                        Tournament.id,
                    )
                )
            )

        def is_today(row: Tournament) -> bool:
            return bool(
                row.start_date
                and row.end_date
                and row.start_date <= on_date <= row.end_date
            )

        today_rows = [row for row in all_rows if is_today(row)]
        latest_season = next((row.season for row in all_rows if row.season), "")
        recommended_rows = today_rows or [
            row for row in all_rows if row.season == latest_season
        ]

        def matches(row: Tournament) -> bool:
            if league and row.level != league:
                return False
            if season and row.season != season:
                return False
            if stage and stage_key(row.season_part, row.round_code) != stage:
                return False
            if search_value:
                haystack = " ".join(
                    [
                        row.name_ru,
                        row.name_en,
                        row.common_name_ru,
                        row.common_name_en,
                        row.season,
                        row.level,
                    ]
                ).lower()
                if search_value not in haystack:
                    return False
            return True

        filtered = [row for row in all_rows if matches(row)][:limit]
        serialized = [
            self._serialize_tournament(
                row,
                language=language,
                fallback_language=fallback_language,
            )
            for row in filtered
        ]
        today_serialized = [
            self._serialize_tournament(
                row,
                language=language,
                fallback_language=fallback_language,
            )
            for row in today_rows
        ]
        recommended_serialized = [
            self._serialize_tournament(
                row,
                language=language,
                fallback_language=fallback_language,
            )
            for row in recommended_rows[:100]
        ]

        league_values = sorted(
            {row.level for row in all_rows if row.level},
            key=lambda value: league_label(value, language),
        )
        seasons = sorted(
            {row.season for row in all_rows if row.season},
            reverse=True,
        )
        stages = sorted(
            {stage_key(row.season_part, row.round_code) for row in all_rows}
        )

        return {
            "meta": {
                "date": on_date.isoformat(),
                "language": language,
                "fallback_language": fallback_language,
                "total": len(all_rows),
                "shown": len(serialized),
                "today_count": len(today_serialized),
                "latest_season": latest_season,
                "credentials_configured": self.settings.status()[
                    "credentials_configured"
                ],
                "last_sync": self.last_sync(),
            },
            "filters": {
                "leagues": [
                    {"value": value, "label": league_label(value, language)}
                    for value in league_values
                ],
                "seasons": seasons,
                "stages": [
                    {
                        "value": value,
                        "label": stage_label(
                            "playoff" if value == "playoff" else (
                                "regular" if value == "regular" else value
                            ),
                            "",
                            language,
                        )
                        if value in {"regular", "playoff", "playout"}
                        else (
                            "Кубок" if language == "ru" and value == "cup"
                            else "Cup" if value == "cup"
                            else "Другой этап" if language == "ru"
                            else "Other stage"
                        ),
                    }
                    for value in stages
                ],
            },
            "today": today_serialized,
            "recommended": recommended_serialized,
            "items": serialized,
        }

    def data_for_builder(self) -> dict[str, Any]:
        settings = self.settings.public()
        today = date.today()
        nav = self.navigation(
            language=settings["ui_language"],
            on_date=today,
            limit=120,
        )
        selected = (nav["today"] or nav["recommended"] or [None])[0]
        return {
            "hockey": {
                "api": {
                    "base_url": settings["base_url"],
                    "configured": self.settings.status()["credentials_configured"],
                    "last_sync": nav["meta"]["last_sync"],
                },
                "language": {
                    "display": settings["ui_language"],
                    "fallback": settings["fallback_language"],
                    "available": ["ru", "en"],
                },
                "today": nav["today"],
                "tournaments": nav["recommended"],
                "selected_tournament": selected,
                "games_today": [],
                "selected_game": None,
                "home": {"name": "", "players": []},
                "away": {"name": "", "players": []},
                "referees": [],
                "match_details": None,
                "standings": None,
                "tournament_statistics": {
                    "available_tabs": [],
                    "powerplay": {"available": False, "sections": []},
                    "rank": {"available": False, "sections": []},
                },
                "operator_session": None,
            }
        }


    @staticmethod
    def _tournament_game_endpoint_candidates() -> list[str]:
        """Fallback schedule endpoints scoped to one tournament directory.

        Stat2TV tournaments do not always expose the canonical live schedule file.
        These probes deliberately stay inside ``/{tournament_id}/`` so selecting a
        tournament can still recover its schedule without scanning unrelated APIs.
        """
        return [
            "{tournament_id}/schedule-{tournament_id}-live.xml",
            "{tournament_id}/schedule-{tournament_id}.xml",
            "{tournament_id}/schedule-live.xml",
            "{tournament_id}/schedule.xml",
            "{tournament_id}/games-{tournament_id}-live.xml",
            "{tournament_id}/games-{tournament_id}.xml",
            "{tournament_id}/games.xml",
            "{tournament_id}/matches-{tournament_id}-live.xml",
            "{tournament_id}/matches-{tournament_id}.xml",
            "{tournament_id}/matches.xml",
            "{tournament_id}/calendar-{tournament_id}.xml",
            "{tournament_id}/calendar.xml",
        ]

    @staticmethod
    def _game_endpoint_candidates() -> list[str]:
        """Likely Stat2TV endpoint shapes.

        The successful template is saved in hockey_api.json, so discovery normally
        runs only once. The list is deliberately finite to avoid excessive requests.
        """
        return [
            "{tournament_id}/schedule-{tournament_id}-live.xml",
            *HockeyDataService._tournament_game_endpoint_candidates(),
            "games.xml?tournament={tournament_id}",
            "games.xml?tournamentId={tournament_id}",
            "games.xml?TournamentId={tournament_id}",
            "games.xml?id={tournament_id}",
            "games.xml?tid={tournament_id}",
            "matches.xml?tournament={tournament_id}",
            "matches.xml?tournamentId={tournament_id}",
            "matches.xml?TournamentId={tournament_id}",
            "matches.xml?id={tournament_id}",
            "calendar.xml?tournament={tournament_id}",
            "calendar.xml?tournamentId={tournament_id}",
            "schedule.xml?tournament={tournament_id}",
            "schedule.xml?tournamentId={tournament_id}",
            "events.xml?tournament={tournament_id}",
            "tournament_games.xml?id={tournament_id}",
            "tournament/{tournament_id}/games.xml",
            "tournaments/{tournament_id}/games.xml",
            "games/{tournament_id}.xml",
            "games.xml?tournament={tournament_id}&date={date}",
            "matches.xml?tournament={tournament_id}&date={date}",
            "calendar.xml?tournament={tournament_id}&date={date}",
        ]

    def _resolve_game_endpoint(
        self,
        template: str,
        *,
        tournament_external_id: str,
        on_date: date | None,
    ) -> str:
        tournament = self.tournament(tournament_external_id, language="ru") or {}
        values = {
            "tournament_id": tournament_external_id,
            "id": tournament_external_id,
            "date": (on_date or date.today()).isoformat(),
            "season": tournament.get("season", ""),
            "level": tournament.get("level", ""),
        }
        try:
            return template.format(**values)
        except (KeyError, ValueError) as error:
            raise ValueError(
                f"Некорректный шаблон endpoint матчей: {template}"
            ) from error

    @staticmethod
    def _resolve_match_resource_endpoint(
        template: str,
        *,
        tournament_external_id: str,
        game_external_id: str,
    ) -> str:
        try:
            return template.format(
                tournament_id=tournament_external_id,
                game_id=game_external_id,
                id=game_external_id,
            )
        except (KeyError, ValueError) as error:
            raise ValueError(
                f"Некорректный шаблон ресурса матча: {template}"
            ) from error

    async def _fetch_cached_resource(
        self,
        endpoint: str,
        *,
        cache_seconds: float,
    ) -> Any:
        now = time.monotonic()
        cached = self._resource_response_cache.get(endpoint)
        if cached and now - cached[0] < max(0.0, cache_seconds):
            return cached[1]
        response = await self.client.fetch_xml(endpoint)
        self._resource_response_cache[endpoint] = (now, response)
        return response

    def _save_snapshot(
        self,
        content: bytes,
        *,
        resource: str,
        tournament_external_id: str,
    ) -> Path:
        digest = hashlib.sha256(content).hexdigest()[:12]
        stamp = datetime.utcnow().strftime("%Y%m%d_%H%M%S_%f")
        safe_tournament = "".join(
            character for character in str(tournament_external_id)
            if character.isalnum() or character in {"-", "_"}
        )[:48]
        path = self.snapshot_dir / (
            f"{stamp}_{safe_tournament}_{digest}_{resource}.xml"
        )
        path.write_bytes(content)
        return path

    @staticmethod
    def _upsert_game(
        session: Session,
        item: ParsedGame,
        *,
        endpoint: str,
        now: datetime,
    ) -> None:
        existing = session.scalar(
            select(Game).where(Game.external_id == item.external_id)
        )
        if existing is None:
            existing = Game(
                external_id=item.external_id,
                tournament_external_id=item.tournament_external_id,
            )
            session.add(existing)

        existing.tournament_external_id = item.tournament_external_id
        existing.tournament_type = item.tournament_type
        existing.game_number = item.game_number
        existing.round_number = item.round_number
        existing.round_name_ru = item.round_name_ru
        existing.round_name_en = item.round_name_en
        existing.day_of_week = item.day_of_week
        existing.changes = item.changes
        # Schedule endpoints are not always complete. Never erase a date/time
        # already recovered from the selected game's detail card with an empty
        # schedule value. If the parser gave us only a localized raw string,
        # recover DATE/time from it here as a second safety net.
        parsed_raw_date = parse_date_value(item.start_datetime_raw) if item.start_datetime_raw else None
        parsed_raw_time = parse_time_value(item.start_datetime_raw) if item.start_datetime_raw else ""
        if item.game_date is not None:
            existing.game_date = item.game_date
        elif existing.game_date is None and parsed_raw_date is not None:
            existing.game_date = parsed_raw_date
        if item.start_time:
            existing.start_time = item.start_time
        elif not existing.start_time and parsed_raw_time:
            existing.start_time = parsed_raw_time
        if item.start_datetime_raw:
            existing.start_datetime_raw = item.start_datetime_raw
        existing.status = item.status
        existing.approved = item.approved

        existing.arena_external_id = item.arena_external_id
        existing.arena_ru = item.arena_ru
        existing.arena_en = item.arena_en
        existing.arena_city_ru = item.arena_city_ru
        existing.arena_city_en = item.arena_city_en

        existing.home_team_external_id = item.home_team_external_id
        existing.away_team_external_id = item.away_team_external_id
        existing.home_entry_external_id = item.home_entry_external_id
        existing.away_entry_external_id = item.away_entry_external_id
        existing.home_team_name_ru = item.home_team_name_ru
        existing.home_team_name_en = item.home_team_name_en
        existing.home_team_short_ru = item.home_team_short_ru
        existing.home_team_short_en = item.home_team_short_en
        existing.away_team_name_ru = item.away_team_name_ru
        existing.away_team_name_en = item.away_team_name_en
        existing.away_team_short_ru = item.away_team_short_ru
        existing.away_team_short_en = item.away_team_short_en
        existing.home_team_city_ru = item.home_team_city_ru
        existing.home_team_city_en = item.home_team_city_en
        existing.away_team_city_ru = item.away_team_city_ru
        existing.away_team_city_en = item.away_team_city_en

        existing.home_score = item.home_score
        existing.away_score = item.away_score
        existing.score_raw = item.score_raw
        existing.overtime_type = item.overtime_type
        existing.period_1_score = item.period_1_score
        existing.period_2_score = item.period_2_score
        existing.period_3_score = item.period_3_score
        existing.period_4_score = item.period_4_score
        existing.series_score = item.series_score
        existing.attendance = item.attendance
        existing.temporary_value = item.temporary_value
        existing.period_scores = item.period_scores

        existing.source_endpoint = endpoint
        existing.raw_payload = item.raw_xml
        existing.synced_at = now
    def import_games_content(
        self,
        content: bytes,
        *,
        tournament_external_id: str,
        endpoint_template: str,
        resolved_endpoint: str,
        auth_mode: str,
        status_code: int,
        source: str = "stat2tv",
        save_snapshot: bool = True,
        record_discovery: bool = True,
        record_sync: bool = True,
    ) -> dict[str, Any]:
        parsed = parse_games_xml(
            content,
            tournament_external_id=tournament_external_id,
        )
        if not parsed.games:
            raise ValueError(
                f"XML получен, но матчи не распознаны. Root: {parsed.root_tag or '—'}"
            )

        snapshot = (
            self._save_snapshot(
                content,
                resource="games",
                tournament_external_id=tournament_external_id,
            )
            if save_snapshot
            else None
        )
        snapshot_relative = (
            str(snapshot.relative_to(self.base_dir)) if snapshot else ""
        )
        now = datetime.utcnow()
        with self.database.session() as session:
            for item in parsed.games:
                self._upsert_game(
                    session,
                    item,
                    endpoint=resolved_endpoint,
                    now=now,
                )
            session.flush()
            team_directory = self._sync_team_directory_session(
                session,
                tournament_external_id=tournament_external_id,
            )
            if record_discovery:
                session.add(
                    EndpointDiscovery(
                        resource_type="games",
                        tournament_external_id=tournament_external_id,
                        endpoint_template=endpoint_template,
                        resolved_endpoint=resolved_endpoint,
                        status="success",
                        status_code=status_code,
                        root_tag=parsed.root_tag,
                        item_tag=parsed.item_tag,
                        item_count=len(parsed.games),
                        diagnostics_json=json.dumps(
                            {
                                "fields": parsed.fields[:250],
                                "candidate_nodes": parsed.candidate_nodes,
                                "confidence": parsed.confidence,
                                "sample": parsed.sample,
                            },
                            ensure_ascii=False,
                        ),
                        snapshot_path=snapshot_relative,
                    )
                )
            if record_sync:
                session.add(
                    SyncRun(
                        endpoint=resolved_endpoint,
                        status="success",
                        auth_mode=auth_mode,
                        status_code=status_code,
                        item_count=len(parsed.games),
                        source=source,
                        snapshot_path=snapshot_relative,
                        started_at=now,
                        finished_at=datetime.utcnow(),
                    )
                )

        return {
            "ok": True,
            "source": source,
            "tournament_external_id": tournament_external_id,
            "endpoint_template": endpoint_template,
            "endpoint": resolved_endpoint,
            "auth_mode": auth_mode,
            "count": len(parsed.games),
            "root_tag": parsed.root_tag,
            "item_tag": parsed.item_tag,
            "confidence": parsed.confidence,
            "sample": parsed.sample,
            "snapshot": snapshot_relative,
            "team_directory": team_directory,
        }

    def _record_discovery_failure(
        self,
        *,
        tournament_external_id: str,
        endpoint_template: str,
        resolved_endpoint: str,
        status_code: int = 0,
        root_tag: str = "",
        diagnostics: dict[str, Any] | None = None,
        error: str = "",
        snapshot_path: str = "",
    ) -> None:
        with self.database.session() as session:
            session.add(
                EndpointDiscovery(
                    resource_type="games",
                    tournament_external_id=tournament_external_id,
                    endpoint_template=endpoint_template,
                    resolved_endpoint=resolved_endpoint,
                    status="error",
                    status_code=status_code,
                    root_tag=root_tag,
                    diagnostics_json=json.dumps(
                        diagnostics or {},
                        ensure_ascii=False,
                    ),
                    snapshot_path=snapshot_path,
                    error=error,
                )
            )

    def _write_discovery_report(
        self,
        *,
        tournament_external_id: str,
        attempts: list[dict[str, Any]],
    ) -> Path:
        stamp = datetime.utcnow().strftime("%Y%m%d_%H%M%S_%f")
        path = self.snapshot_dir / (
            f"{stamp}_{tournament_external_id}_games_discovery.json"
        )
        path.write_text(
            json.dumps(
                {
                    "created_at": datetime.utcnow().isoformat(),
                    "tournament_external_id": tournament_external_id,
                    "attempts": attempts,
                },
                ensure_ascii=False,
                indent=2,
            ),
            encoding="utf-8",
        )
        return path

    async def sync_games(
        self,
        *,
        tournament_external_id: str,
        on_date: date | None = None,
        force_discovery: bool = False,
        lightweight: bool = False,
    ) -> dict[str, Any]:
        settings = self.settings.public()
        configured = str(settings.get("games_endpoint_template") or "").strip()

        templates: list[str] = []
        if configured and not force_discovery:
            # Fast path first, but do NOT stop discovery at the tournament folder.
            # Some Stat2TV tournaments have no schedule-<id>-live.xml at all and
            # expose their games only through a root endpoint with tournament/date
            # query parameters.  The previous build never reached those candidates
            # during the automatic sync, so an operator saw an empty date until a
            # manual rediscovery was requested.
            templates.append(configured)
            templates.extend(self._tournament_game_endpoint_candidates())
            templates.extend(self._game_endpoint_candidates())
        else:
            templates.extend(self._game_endpoint_candidates())

        # Preserve order and avoid duplicate probes.
        templates = list(dict.fromkeys(template for template in templates if template))
        attempts: list[dict[str, Any]] = []

        for template in templates:
            resolved = self._resolve_game_endpoint(
                template,
                tournament_external_id=tournament_external_id,
                on_date=on_date,
            )
            attempt: dict[str, Any] = {
                "template": template,
                "endpoint": resolved,
                "status": "error",
            }
            try:
                response = await self.client.fetch_xml(resolved)
                attempt["status_code"] = response.status_code
                attempt["content_type"] = response.content_type
                parsed = parse_games_xml(
                    response.content,
                    tournament_external_id=tournament_external_id,
                )
                attempt.update({
                    "root_tag": parsed.root_tag,
                    "item_tag": parsed.item_tag,
                    "item_count": len(parsed.games),
                    "candidate_nodes": parsed.candidate_nodes,
                    "confidence": parsed.confidence,
                    "sample": parsed.sample,
                })

                if not parsed.games:
                    snapshot = self._save_snapshot(
                        response.content,
                        resource="games_unparsed",
                        tournament_external_id=tournament_external_id,
                    )
                    attempt["snapshot"] = str(snapshot.relative_to(self.base_dir))
                    attempt["error"] = "XML получен, но матчи не распознаны"
                    self._record_discovery_failure(
                        tournament_external_id=tournament_external_id,
                        endpoint_template=template,
                        resolved_endpoint=resolved,
                        status_code=response.status_code,
                        root_tag=parsed.root_tag,
                        diagnostics=attempt,
                        error=attempt["error"],
                        snapshot_path=attempt["snapshot"],
                    )
                    attempts.append(attempt)
                    continue

                result = self.import_games_content(
                    response.content,
                    tournament_external_id=tournament_external_id,
                    endpoint_template=template,
                    resolved_endpoint=resolved,
                    auth_mode=response.auth_mode,
                    status_code=response.status_code,
                    save_snapshot=not lightweight,
                    record_discovery=not lightweight,
                    record_sync=not lightweight,
                )
                attempt["status"] = "success"
                attempts.append(attempt)

                if template != configured:
                    self.settings.update_public({
                        "games_endpoint_template": template,
                    })
                selected_date_count = len(parsed.games)
                if on_date is not None:
                    selected_date_count = sum(
                        1 for game in parsed.games if game.game_date == on_date
                    )
                result["attempts"] = attempts
                result["selected_date"] = on_date.isoformat() if on_date else ""
                result["selected_date_count"] = selected_date_count
                result["fallback_used"] = bool(
                    configured and template != configured
                )
                return result

            except Exception as error:
                attempt["error"] = str(error)
                attempts.append(attempt)
                self._record_discovery_failure(
                    tournament_external_id=tournament_external_id,
                    endpoint_template=template,
                    resolved_endpoint=resolved,
                    status_code=int(attempt.get("status_code") or 0),
                    root_tag=str(attempt.get("root_tag") or ""),
                    diagnostics=attempt,
                    error=str(error),
                    snapshot_path=str(attempt.get("snapshot") or ""),
                )

        report = self._write_discovery_report(
            tournament_external_id=tournament_external_id,
            attempts=attempts,
        )
        raise RuntimeError(
            "Не удалось автоматически определить XML endpoint матчей. "
            f"Диагностический отчёт: {report.relative_to(self.base_dir)}"
        )


    async def sync_selected_game(
        self,
        *,
        tournament_external_id: str,
        game_external_id: str,
        language: str = "ru",
    ) -> dict[str, Any]:
        """Refresh one selected game without rewriting the full schedule table."""
        settings = self.settings.public()
        template = str(
            settings.get("games_endpoint_template")
            or "{tournament_id}/schedule-{tournament_id}-live.xml"
        ).strip()
        endpoint = self._resolve_game_endpoint(
            template,
            tournament_external_id=tournament_external_id,
            on_date=None,
        )
        lock = self._live_locks.setdefault(
            str(tournament_external_id),
            asyncio.Lock(),
        )

        async with lock:
            now_monotonic = time.monotonic()
            cached = self._live_response_cache.get(str(tournament_external_id))
            if cached and now_monotonic - cached[0] < 0.75:
                response = cached[1]
            else:
                response = await self.client.fetch_xml(endpoint)
                self._live_response_cache[str(tournament_external_id)] = (
                    now_monotonic,
                    response,
                )

            item = parse_stat2tv_game_by_id(
                response.content,
                tournament_external_id=tournament_external_id,
                game_external_id=game_external_id,
            )
            if item is None:
                raise ValueError(
                    f"Матч {game_external_id} не найден в расписании турнира"
                )

            now = datetime.utcnow()
            with self.database.session() as session:
                self._upsert_game(
                    session,
                    item,
                    endpoint=endpoint,
                    now=now,
                )
                session.flush()
                self._sync_team_directory_session(
                    session,
                    tournament_external_id=tournament_external_id,
                )
                row = session.scalar(
                    select(Game).where(Game.external_id == str(game_external_id))
                )
                if row is None:
                    raise ValueError("Не удалось сохранить выбранный матч")
                payload = self._serialize_game(
                    row,
                    language=normalise_language(
                        language,
                        settings["ui_language"],
                    ),
                    fallback_language=settings["fallback_language"],
                )

        return {
            "ok": True,
            "endpoint": endpoint,
            "auth_mode": response.auth_mode,
            "game": payload,
        }


    @staticmethod
    def _country_flag(code: str) -> str:
        value = iso2_from_code(str(code or ""))
        if len(value) != 2:
            return ""
        return "".join(chr(127397 + ord(character)) for character in value)

    @staticmethod
    def _raw_profile_object(raw_payload: str) -> dict[str, Any]:
        try:
            value = json.loads(raw_payload or "{}")
        except (TypeError, ValueError, json.JSONDecodeError):
            return {}
        return value if isinstance(value, dict) else {}

    @staticmethod
    def _upsert_country_profile(
        session: Session,
        profile: ParsedCountry,
        *,
        now: datetime,
    ) -> Country:
        row = session.scalar(
            select(Country).where(Country.external_id == profile.external_id)
        )
        if row is None:
            row = Country(external_id=profile.external_id)
            session.add(row)
        for field in ("iso2", "iso3", "name_ru", "name_en", "flag_emoji", "raw_payload"):
            value = getattr(profile, field)
            if value:
                setattr(row, field, value)
        if not row.flag_emoji and row.iso2:
            row.flag_emoji = HockeyDataService._country_flag(row.iso2)
        row.active = profile.active
        row.synced_at = now
        return row

    @staticmethod
    def _upsert_coach_profile(
        session: Session,
        profile: ParsedCoachProfile,
        *,
        now: datetime,
    ) -> Coach:
        row = session.scalar(select(Coach).where(Coach.external_id == profile.external_id))
        if row is None:
            row = Coach(external_id=profile.external_id)
            session.add(row)
        for field in (
            "first_name_ru", "first_name_en", "last_name_ru", "last_name_en",
            "full_name_ru", "full_name_en", "country_external_id",
            "country_code", "photo_url", "raw_payload",
        ):
            value = getattr(profile, field)
            if value:
                setattr(row, field, value)
        if profile.birth_date is not None:
            row.birth_date = profile.birth_date
        row.active = profile.active
        row.synced_at = now
        return row

    @staticmethod
    def _upsert_player_tournament(
        session: Session,
        *,
        player: Player,
        profile: ParsedPlayerProfile,
        tournament_external_id: str,
        now: datetime,
    ) -> PlayerTournament:
        raw = HockeyDataService._raw_profile_object(profile.raw_payload)
        team_entry_id = str(
            raw.get("clubidt") or raw.get("teamid") or raw.get("team_id")
            or raw.get("teamentryid") or raw.get("team_entry_id") or ""
        ).strip()
        club_id = str(
            raw.get("clubidg") or raw.get("clubid") or raw.get("club_id")
            or raw.get("teamclubid") or ""
        ).strip()
        row = session.scalar(
            select(PlayerTournament).where(
                PlayerTournament.player_id == player.id,
                PlayerTournament.tournament_external_id == str(tournament_external_id),
                PlayerTournament.team_entry_external_id == team_entry_id,
            )
        )
        if row is None:
            row = PlayerTournament(
                player_id=player.id,
                tournament_external_id=str(tournament_external_id),
                team_entry_external_id=team_entry_id,
            )
            session.add(row)
        row.club_external_id = club_id
        row.team_name_ru = str(raw.get("teamname") or raw.get("clubname") or "").strip()
        row.team_name_en = str(raw.get("teamnameen") or raw.get("clubnameen") or "").strip()
        row.jersey_number = str(raw.get("jn") or raw.get("number") or "").strip()
        row.source = "stat2tv"
        row.active = profile.active
        row.synced_at = now
        return row

    @staticmethod
    def _upsert_coach_tournament(
        session: Session,
        *,
        coach: Coach,
        profile: ParsedCoachProfile,
        tournament_external_id: str,
        now: datetime,
    ) -> CoachTournament:
        team_id = str(profile.team_external_id or "").strip()
        row = session.scalar(
            select(CoachTournament).where(
                CoachTournament.coach_id == coach.id,
                CoachTournament.tournament_external_id == str(tournament_external_id),
                CoachTournament.team_external_id == team_id,
            )
        )
        if row is None:
            row = CoachTournament(
                coach_id=coach.id,
                tournament_external_id=str(tournament_external_id),
                team_external_id=team_id,
            )
            session.add(row)
        row.team_name_ru = profile.team_name_ru
        row.team_name_en = profile.team_name_en
        row.role_ru = profile.role_ru or "Главный тренер"
        row.role_en = profile.role_en or "Head coach"
        row.source = "stat2tv"
        row.active = profile.active
        row.synced_at = now
        return row

    @staticmethod
    def _upsert_player_profile(
        session: Session,
        profile: ParsedPlayerProfile,
        *,
        now: datetime,
    ) -> Player:
        row = session.scalar(
            select(Player).where(Player.external_id == profile.external_id)
        )
        if row is None:
            row = Player(external_id=profile.external_id)
            session.add(row)
        for field in (
            "first_name_ru",
            "first_name_en",
            "last_name_ru",
            "last_name_en",
            "full_name_ru",
            "full_name_en",
            "position_ru",
            "position_en",
            "stick",
            "country_external_id",
            "country_code",
            "photo_url",
            "raw_payload",
        ):
            value = getattr(profile, field)
            if value:
                setattr(row, field, value)
        if profile.birth_date is not None:
            row.birth_date = profile.birth_date
        if profile.height_cm:
            row.height_cm = profile.height_cm
        if profile.weight_kg:
            row.weight_kg = profile.weight_kg
        row.active = profile.active
        row.synced_at = now
        return row

    @staticmethod
    def _upsert_referee_profile(
        session: Session,
        profile: ParsedRefereeProfile,
        *,
        now: datetime,
    ) -> Referee:
        row = session.scalar(
            select(Referee).where(Referee.external_id == profile.external_id)
        )
        if row is None:
            row = Referee(external_id=profile.external_id)
            session.add(row)
        for field in (
            "first_name_ru",
            "first_name_en",
            "last_name_ru",
            "last_name_en",
            "full_name_ru",
            "full_name_en",
            "position_code",
            "number",
            "country_ru",
            "country_en",
            "country_code",
            "town_ru",
            "town_en",
            "raw_payload",
        ):
            value = getattr(profile, field)
            if value:
                setattr(row, field, value)
        row.active = profile.active
        if profile.birth_date is not None:
            row.birth_date = profile.birth_date
        row.synced_at = now
        return row

    @staticmethod
    def _stat_float(value: Any, default: float = 0.0) -> float:
        if value in (None, "", "-"):
            return default
        try:
            return float(str(value).replace(",", "."))
        except (TypeError, ValueError):
            return default

    @classmethod
    def _stat_int(cls, value: Any, default: int = 0) -> int:
        return int(cls._stat_float(value, float(default)))

    @staticmethod
    def _season_role(player: Player) -> str:
        text = f"{player.position_ru} {player.position_en}".strip().lower()
        if any(token in text for token in ("goal", "врат", "вр", " g ")) or text in {"g", "вр"}:
            return "goalkeeper"
        return "skater"

    def _upsert_player_season_statistics(
        self,
        session: Session,
        *,
        player: Player,
        membership: PlayerTournament,
        raw_payload: str,
        now: datetime,
    ) -> PlayerSeasonStatistic | None:
        raw = self._details_json_object(raw_payload)
        role = self._season_role(player)
        stats = self._normalise_season_player_statistics(raw, role=role)
        existing = session.scalar(
            select(PlayerSeasonStatistic).where(
                PlayerSeasonStatistic.player_id == player.id,
                PlayerSeasonStatistic.tournament_external_id == membership.tournament_external_id,
                PlayerSeasonStatistic.team_entry_external_id == membership.team_entry_external_id,
            )
        )
        if not stats.get("available"):
            if existing is not None:
                session.delete(existing)
            return None
        row = existing or PlayerSeasonStatistic(
            player_id=player.id,
            player_external_id=player.external_id,
            tournament_external_id=membership.tournament_external_id,
            team_entry_external_id=membership.team_entry_external_id,
        )
        if existing is None:
            session.add(row)
        row.player_external_id = player.external_id
        row.club_external_id = membership.club_external_id
        row.team_name_ru = membership.team_name_ru
        row.team_name_en = membership.team_name_en
        row.jersey_number = membership.jersey_number
        row.role = role
        # Reset both skater and goalkeeper groups so a role correction cannot
        # leave stale values from an older sync.
        integer_fields = (
            "games", "goals", "assists", "points", "penalty_minutes", "plus_minus",
            "plus", "minus", "power_play_goals", "short_handed_goals",
            "game_winning_goals", "even_strength_goals", "overtime_goals", "shots",
            "faceoffs", "faceoffs_won", "takeaways", "interceptions", "hits",
            "blocked_shots", "wins", "losses", "draws", "overtime_losses",
            "shots_against", "goals_against", "saves", "shutouts", "shootouts",
            "shootout_goals_against",
        )
        float_fields = (
            "shot_pct", "faceoff_pct", "average_shifts", "average_shots", "save_pct",
            "goals_against_average", "shootout_save_pct",
        )
        string_fields = (
            "average_time_on_ice", "average_time_on_attack", "average_even_strength_time",
            "average_power_play_time", "average_short_handed_time", "time_on_ice",
        )
        for field in integer_fields:
            setattr(row, field, self._stat_int(stats.get(field)))
        for field in float_fields:
            setattr(row, field, self._stat_float(stats.get(field)))
        for field in string_fields:
            setattr(row, field, str(stats.get(field) or ""))
        row.raw_payload = raw_payload or ""
        row.synced_at = now
        return row

    def _store_game_team_statistics(
        self,
        session: Session,
        *,
        game: Game,
        raw_teams: dict[str, Any],
        raw_periods: dict[str, Any],
        now: datetime,
    ) -> int:
        session.execute(
            delete(GameTeamStatistic).where(
                GameTeamStatistic.game_external_id == game.external_id
            )
        )
        created = 0
        for source_side, side in (("A", "home"), ("B", "away")):
            team_id = game.home_team_external_id if side == "home" else game.away_team_external_id
            total_source = raw_teams.get(source_side) if isinstance(raw_teams.get(source_side), dict) else None
            segments: list[tuple[str, int, dict[str, Any]]] = []
            if total_source is not None:
                segments.append(("total", 0, total_source))
            period_source = raw_periods.get(source_side) if isinstance(raw_periods.get(source_side), dict) else {}
            for period_key, period_row in period_source.items():
                if isinstance(period_row, dict):
                    segments.append((f"period-{period_key}", self._stat_int(period_key), period_row))
            for segment_key, period_number, source in segments:
                values = self._normalise_team_statistics(source)
                row = GameTeamStatistic(
                    game_external_id=game.external_id,
                    tournament_external_id=game.tournament_external_id,
                    team_external_id=team_id,
                    side=side,
                    segment_key=segment_key,
                    period_number=period_number,
                    goals=self._stat_int(values.get("goals")),
                    shots=self._stat_int(values.get("shots")),
                    shot_attempts=(game.shot_attempts_home if side == "home" else game.shot_attempts_away) if segment_key == "total" else 0,
                    faceoffs=self._stat_int(values.get("faceoffs")),
                    faceoffs_won=self._stat_int(values.get("faceoffs_won")),
                    faceoffs_won_pct=self._stat_float(values.get("faceoffs_won_pct")),
                    hits=self._stat_int(values.get("hits")),
                    blocked_shots=self._stat_int(values.get("blocked_shots")),
                    takeaways=self._stat_int(values.get("takeaways")),
                    giveaways=self._stat_int(values.get("giveaways")),
                    interceptions=self._stat_int(values.get("interceptions")),
                    penalty_minutes=self._stat_int(values.get("penalty_minutes")),
                    penalties=self._stat_int(values.get("penalties")),
                    time_on_attack=str(values.get("time_on_attack") or ""),
                    even_strength_time=str(values.get("even_strength_time") or ""),
                    power_play_time=str(values.get("power_play_time") or ""),
                    short_handed_time=str(values.get("short_handed_time") or ""),
                    empty_net_time=str(values.get("empty_net_time") or ""),
                    raw_payload=json.dumps(source, ensure_ascii=False, separators=(",", ":")),
                    synced_at=now,
                )
                session.add(row)
                created += 1
        return created

    def _store_normalized_standings(
        self,
        session: Session,
        *,
        tournament_external_id: str,
        parsed: dict[str, Any],
        now: datetime,
    ) -> int:
        tournament_id = str(tournament_external_id)
        session.execute(
            delete(TournamentStandingRow).where(
                TournamentStandingRow.tournament_external_id == tournament_id
            )
        )
        created = 0
        for variant in parsed.get("variants") or []:
            variant_id = str(variant.get("id") or "league")
            for group in variant.get("groups") or []:
                group_id = str(group.get("id") or variant_id)
                group_names = group.get("names") if isinstance(group.get("names"), dict) else {}
                for team in group.get("teams") or []:
                    names = team.get("names") if isinstance(team.get("names"), dict) else {}
                    entry_id = str(team.get("entry_id") or team.get("id") or "")
                    club_id = str(team.get("club_id") or "")
                    fallback_name = str(names.get("ru") or names.get("en") or team.get("name") or "").strip()
                    team_key = entry_id or club_id or f"name:{fallback_name.casefold()}"
                    row = TournamentStandingRow(
                        tournament_external_id=tournament_id,
                        variant_id=variant_id,
                        group_id=group_id,
                        group_name_ru=str(group_names.get("ru") or ""),
                        group_name_en=str(group_names.get("en") or ""),
                        standings_type=str(group.get("standings_type") or ""),
                        conference=str(group.get("conference") or ""),
                        playoff_places=self._stat_int(group.get("playoff_places")),
                        team_key=team_key,
                        team_entry_external_id=entry_id,
                        club_external_id=club_id,
                        team_name_ru=str(names.get("ru") or ""),
                        team_name_en=str(names.get("en") or ""),
                        rank=self._stat_int(team.get("rank")),
                        games=self._stat_int(team.get("games")),
                        wins=self._stat_int(team.get("wins")),
                        overtime_wins=self._stat_int(team.get("overtime_wins")),
                        shootout_wins=self._stat_int(team.get("shootout_wins")),
                        shootout_losses=self._stat_int(team.get("shootout_losses")),
                        overtime_losses=self._stat_int(team.get("overtime_losses")),
                        losses=self._stat_int(team.get("losses")),
                        points=self._stat_int(team.get("points")),
                        points_pct=self._stat_float(team.get("points_pct")),
                        goals_for=self._stat_int(team.get("goals_for")),
                        goals_against=self._stat_int(team.get("goals_against")),
                        penalty_minutes=self._stat_int(team.get("penalty_minutes")),
                        opponent_penalty_minutes=self._stat_int(team.get("opponent_penalty_minutes")),
                        playoffs_in=bool(team.get("playoffs_in")),
                        playoffs_out=bool(team.get("playoffs_out")),
                        raw_payload=json.dumps(team, ensure_ascii=False, separators=(",", ":")),
                        synced_at=now,
                    )
                    session.add(row)
                    created += 1
        session.flush()
        self._rebuild_team_season_statistics(session, tournament_id, now=now)
        return created

    def _store_normalized_tournament_statistic_rows(
        self,
        session: Session,
        *,
        tournament_external_id: str,
        resource_type: str,
        parsed: dict[str, Any],
        now: datetime,
    ) -> int:
        tournament_id = str(tournament_external_id)
        resource_type = str(resource_type)
        session.execute(
            delete(TournamentStatisticRow).where(
                TournamentStatisticRow.tournament_external_id == tournament_id,
                TournamentStatisticRow.resource_type == resource_type,
            )
        )
        created = 0
        for section in parsed.get("sections") or []:
            section_id = str(section.get("id") or "section")
            section_names = section.get("names") if isinstance(section.get("names"), dict) else {}
            periods = section.get("periods") if isinstance(section.get("periods"), dict) else {}
            for index, item in enumerate(section.get("rows") or [], start=1):
                values = item.get("values") if isinstance(item.get("values"), dict) else {}
                names = item.get("names") if isinstance(item.get("names"), dict) else {}
                team_names = item.get("team_names") if isinstance(item.get("team_names"), dict) else {}
                source_tag = str(item.get("source_tag") or "").lower()
                player_id = str(item.get("player_id") or "")
                team_id = str(item.get("team_id") or "")
                row_external_id = str(item.get("id") or "")
                if resource_type == "powerplay" or source_tag in {"team", "club"}:
                    entity_type = "team"
                elif player_id or source_tag in {"player", "person", "athlete"}:
                    entity_type = "player"
                else:
                    entity_type = "unknown"
                display_ru = str(names.get("ru") or "")
                display_en = str(names.get("en") or "")
                if entity_type == "team":
                    display_ru = str(team_names.get("ru") or display_ru or item.get("name") or "")
                    display_en = str(team_names.get("en") or display_en or "")
                row_key = row_external_id or player_id or team_id or display_ru or display_en or f"row-{index}"
                def v(*keys: str) -> Any:
                    for key in keys:
                        if key in values and values[key] not in (None, ""):
                            return values[key]
                    return None
                row = TournamentStatisticRow(
                    tournament_external_id=tournament_id,
                    resource_type=resource_type,
                    section_id=section_id,
                    section_name_ru=str(section_names.get("ru") or ""),
                    section_name_en=str(section_names.get("en") or ""),
                    period_ru=str(periods.get("ru") or ""),
                    period_en=str(periods.get("en") or ""),
                    row_key=row_key[:192],
                    row_external_id=row_external_id,
                    entity_type=entity_type,
                    player_external_id=player_id,
                    team_external_id=team_id,
                    rank=self._stat_int(item.get("rank")),
                    number=str(item.get("number") or ""),
                    name_ru=display_ru,
                    name_en=display_en,
                    team_name_ru=str(team_names.get("ru") or ""),
                    team_name_en=str(team_names.get("en") or ""),
                    games=self._stat_int(v("gp", "games", "games_played")),
                    goals=self._stat_int(v("g", "goals")),
                    assists=self._stat_int(v("a", "assists")),
                    points=self._stat_int(v("pts", "points")),
                    penalty_minutes=self._stat_int(v("pim", "penalty_minutes")),
                    # Stat2TV uses both the compact KHL keys (pp/ppgf) and
                    # the older generic aliases (ppo/ppg) depending on feed.
                    power_play_goals=self._stat_int(v("ppg", "ppgf", "power_play_goals")),
                    power_play_assists=self._stat_int(v("ppa", "power_play_assists")),
                    power_play_points=self._stat_int(v("ppp", "power_play_points")),
                    power_play_opportunities=self._stat_int(v("ppo", "pp", "power_play_opportunities")),
                    power_play_pct=self._stat_float(v("pp_pct", "pp_percent", "power_play_pct", "power_play_percent")),
                    short_handed_goals=self._stat_int(v("shg", "short_handed_goals")),
                    wins=self._stat_int(v("w", "wins")),
                    losses=self._stat_int(v("l", "losses")),
                    save_pct=self._stat_float(v("sv_pct", "save_pct")),
                    goals_against_average=self._stat_float(v("gaa", "goals_against_average")),
                    time_on_ice=str(v("toi", "time_on_ice") or ""),
                    values_json=json.dumps(values, ensure_ascii=False, separators=(",", ":")),
                    raw_payload=json.dumps(item, ensure_ascii=False, separators=(",", ":")),
                    synced_at=now,
                )
                session.add(row)
                created += 1
        session.flush()
        if resource_type == "powerplay":
            self._rebuild_team_season_statistics(session, tournament_id, now=now)
        return created

    def _rebuild_team_season_statistics(
        self,
        session: Session,
        tournament_external_id: str,
        *,
        now: datetime,
    ) -> int:
        """Rebuild one season row per team from standings + power-play data.

        Stat2TV powerplay XML can contain several team sections (goals, special
        teams, actual PP table, etc.). The same club therefore appears several
        times with the same id. Only the most PP-specific row may participate in
        hockey_team_season_statistics; otherwise the unique (tournament, team)
        key is violated.
        """
        tournament_id = str(tournament_external_id)

        # With autoflush=False a previous rebuild can still have pending ORM
        # objects in this Session. Remove those before issuing the SQL DELETE so
        # they cannot be inserted later and collide with the freshly rebuilt set.
        for pending in list(session.new):
            if (
                isinstance(pending, TeamSeasonStatistic)
                and str(pending.tournament_external_id) == tournament_id
            ):
                session.expunge(pending)

        session.execute(
            delete(TeamSeasonStatistic).where(
                TeamSeasonStatistic.tournament_external_id == tournament_id
            )
        )
        session.flush()

        standings = list(session.scalars(
            select(TournamentStandingRow).where(
                TournamentStandingRow.tournament_external_id == tournament_id
            )
        ))
        powerplay = list(session.scalars(
            select(TournamentStatisticRow).where(
                TournamentStatisticRow.tournament_external_id == tournament_id,
                TournamentStatisticRow.resource_type == "powerplay",
                TournamentStatisticRow.entity_type == "team",
            )
        ))

        # Prefer the general league standings row; conference/division copies are
        # kept in hockey_tournament_standing_rows but must not duplicate a team in
        # the season aggregate.
        ordered = sorted(
            standings,
            key=lambda row: (0 if row.variant_id == "league" else 1, row.rank, row.id),
        )
        base_by_key: dict[str, TournamentStandingRow] = {}
        for row in ordered:
            base_by_key.setdefault(row.team_key, row)

        def norm_name(value: str) -> str:
            return re.sub(r"\s+", " ", str(value or "").strip().casefold())

        def values_of(row: TournamentStatisticRow) -> dict[str, Any]:
            try:
                value = json.loads(row.values_json or "{}")
            except (TypeError, ValueError):
                return {}
            return value if isinstance(value, dict) else {}

        def pp_score(row: TournamentStatisticRow) -> tuple[int, int, int]:
            """Prefer the actual PP section over other team sections."""
            values = values_of(row)
            keys = {str(key).casefold() for key in values}
            strong = sum(
                1
                for key in ("pp", "ppo", "ppgf", "ppg", "pp_pct", "pk_pct")
                if key in keys
            )
            related = sum(
                1
                for key in ("shgf", "shga", "ppga", "tish")
                if key in keys
            )
            # Stable tie-breaker: rows with populated normalized PP fields win.
            populated = int(bool(row.power_play_opportunities)) + int(bool(row.power_play_goals)) + int(bool(row.power_play_pct))
            return (strong, related, populated)

        def canonical_pp_key(row: TournamentStatisticRow) -> str:
            return str(
                row.team_external_id
                or row.row_external_id
                or (f"name:{norm_name(row.name_ru or row.team_name_ru or row.name_en or row.team_name_en)}" if (row.name_ru or row.team_name_ru or row.name_en or row.team_name_en) else "")
            )

        # Deduplicate repeated team rows across all sections of powerplay XML.
        best_pp_by_team: dict[str, TournamentStatisticRow] = {}
        for row in powerplay:
            key = canonical_pp_key(row)
            if not key:
                continue
            current = best_pp_by_team.get(key)
            if current is None or pp_score(row) > pp_score(current):
                best_pp_by_team[key] = row

        # Build aliases so standings can match Stat2TV team-entry id, club id or
        # localized team name. When aliases collide, keep the more PP-specific row.
        pp_by_key: dict[str, TournamentStatisticRow] = {}
        for row in best_pp_by_team.values():
            candidates = [
                row.team_external_id,
                row.row_external_id,
                norm_name(row.name_ru or row.team_name_ru),
                norm_name(row.name_en or row.team_name_en),
            ]
            for candidate in candidates:
                if not candidate:
                    continue
                current = pp_by_key.get(candidate)
                if current is None or pp_score(row) > pp_score(current):
                    pp_by_key[candidate] = row

        created = 0
        used_pp_ids: set[int] = set()
        for key, base in base_by_key.items():
            candidates = [
                base.team_entry_external_id,
                base.club_external_id,
                norm_name(base.team_name_ru),
                norm_name(base.team_name_en),
            ]
            pp = next(
                (pp_by_key.get(candidate) for candidate in candidates if candidate and pp_by_key.get(candidate) is not None),
                None,
            )
            if pp is not None and pp.id is not None:
                used_pp_ids.add(pp.id)
            row = TeamSeasonStatistic(
                tournament_external_id=tournament_id,
                team_key=key,
                team_entry_external_id=base.team_entry_external_id,
                club_external_id=base.club_external_id,
                team_name_ru=base.team_name_ru,
                team_name_en=base.team_name_en,
                rank=base.rank,
                games=base.games,
                wins=base.wins,
                overtime_wins=base.overtime_wins,
                shootout_wins=base.shootout_wins,
                shootout_losses=base.shootout_losses,
                overtime_losses=base.overtime_losses,
                losses=base.losses,
                points=base.points,
                points_pct=base.points_pct,
                goals_for=base.goals_for,
                goals_against=base.goals_against,
                penalty_minutes=base.penalty_minutes,
                opponent_penalty_minutes=base.opponent_penalty_minutes,
                power_play_goals=pp.power_play_goals if pp else 0,
                power_play_opportunities=pp.power_play_opportunities if pp else 0,
                power_play_pct=pp.power_play_pct if pp else 0.0,
                power_play_values_json=pp.values_json if pp else "{}",
                standings_raw_payload=base.raw_payload,
                powerplay_raw_payload=pp.raw_payload if pp else "",
                synced_at=now,
            )
            session.add(row)
            created += 1

        # Preserve a PP-only team if standings are genuinely unavailable, but only
        # once per canonical team id. Never preserve every repeated XML section.
        existing_keys = set(base_by_key)
        for canonical_key, pp in best_pp_by_team.items():
            if pp.id is not None and pp.id in used_pp_ids:
                continue
            key = canonical_key
            if not key or key in existing_keys:
                continue
            session.add(TeamSeasonStatistic(
                tournament_external_id=tournament_id,
                team_key=key,
                team_entry_external_id=pp.team_external_id or pp.row_external_id,
                team_name_ru=pp.name_ru or pp.team_name_ru,
                team_name_en=pp.name_en or pp.team_name_en,
                games=pp.games,
                power_play_goals=pp.power_play_goals,
                power_play_opportunities=pp.power_play_opportunities,
                power_play_pct=pp.power_play_pct,
                power_play_values_json=pp.values_json,
                powerplay_raw_payload=pp.raw_payload,
                synced_at=now,
            ))
            existing_keys.add(key)
            created += 1

        # Flush here: unique-key problems, if any, are raised at the rebuild site
        # instead of surfacing later from an unrelated sync operation.
        session.flush()
        return created

    def _backfill_normalized_statistics(self, session: Session) -> dict[str, int]:
        now = datetime.utcnow()
        result = {"game_team_statistics": 0, "player_season_statistics": 0, "standing_rows": 0, "statistic_rows": 0, "team_season_statistics": 0}

        if (session.scalar(select(func.count(GameTeamStatistic.id))) or 0) == 0:
            games = list(session.scalars(select(Game).where(Game.details_raw_payload_ru != "")))
            for game in games:
                raw = self._details_json_object(game.details_raw_payload_ru or game.details_raw_payload_en)
                teams = raw.get("teams") if isinstance(raw.get("teams"), dict) else {}
                periods = raw.get("teams_periods") if isinstance(raw.get("teams_periods"), dict) else {}
                result["game_team_statistics"] += self._store_game_team_statistics(session, game=game, raw_teams=teams, raw_periods=periods, now=now)

        if (session.scalar(select(func.count(PlayerSeasonStatistic.id))) or 0) == 0:
            memberships = session.execute(
                select(Player, PlayerTournament).join(PlayerTournament, PlayerTournament.player_id == Player.id)
            ).all()
            for player, membership in memberships:
                if self._upsert_player_season_statistics(session, player=player, membership=membership, raw_payload=player.raw_payload, now=now) is not None:
                    result["player_season_statistics"] += 1

        if (session.scalar(select(func.count(TournamentStandingRow.id))) or 0) == 0:
            for source in session.scalars(select(TournamentStanding)).all():
                if not source.raw_payload:
                    continue
                try:
                    parsed = parse_standings_xml(source.raw_payload, language="ru")
                except Exception:
                    continue
                result["standing_rows"] += self._store_normalized_standings(session, tournament_external_id=source.tournament_external_id, parsed=parsed, now=now)

        if (session.scalar(select(func.count(TournamentStatisticRow.id))) or 0) == 0:
            for source in session.scalars(select(TournamentStatisticResource).where(TournamentStatisticResource.available.is_(True))).all():
                if not source.raw_payload:
                    continue
                try:
                    parsed = parse_tournament_statistics_xml(source.raw_payload, resource_type=source.resource_type, language="ru")
                except Exception:
                    continue
                result["statistic_rows"] += self._store_normalized_tournament_statistic_rows(session, tournament_external_id=source.tournament_external_id, resource_type=source.resource_type, parsed=parsed, now=now)

        tournament_ids = set(session.scalars(select(TournamentStandingRow.tournament_external_id)).all()) | set(session.scalars(select(TournamentStatisticRow.tournament_external_id)).all())
        if tournament_ids:
            # Rebuild after both raw sources have been normalized so combined team
            # season rows contain standings + power-play fields immediately. Each
            # rebuild safely replaces only that tournament's aggregate rows.
            for tournament_id in sorted(tournament_ids):
                result["team_season_statistics"] += self._rebuild_team_season_statistics(session, tournament_id, now=now)
        return result

    def backfill_normalized_statistics(self) -> dict[str, Any]:
        with self.database.session() as session:
            result = self._backfill_normalized_statistics(session)
        return {"ok": True, "backfill": result, "status": self.normalized_storage_status()}

    def normalized_storage_status(self, *, tournament_external_id: str = "") -> dict[str, Any]:
        models = {
            "schedule_games": Game,
            "game_team_statistics": GameTeamStatistic,
            "player_season_statistics": PlayerSeasonStatistic,
            "team_season_statistics": TeamSeasonStatistic,
            "standings_rows": TournamentStandingRow,
            "powerplay_rank_rows": TournamentStatisticRow,
        }
        result: dict[str, Any] = {"tournament_id": str(tournament_external_id or ""), "tables": {}}
        with self.database.session() as session:
            for key, model in models.items():
                statement = select(func.count(model.id))
                tournament_column = getattr(model, "tournament_external_id", None)
                if tournament_external_id and tournament_column is not None:
                    statement = statement.where(tournament_column == str(tournament_external_id))
                count = int(session.scalar(statement) or 0)
                synced_column = getattr(model, "synced_at", None)
                last_synced = None
                if synced_column is not None:
                    last_stmt = select(func.max(synced_column))
                    if tournament_external_id and tournament_column is not None:
                        last_stmt = last_stmt.where(tournament_column == str(tournament_external_id))
                    last_synced = session.scalar(last_stmt)
                result["tables"][key] = {"table": model.__tablename__, "rows": count, "last_synced_at": last_synced.isoformat() if last_synced else ""}
            resource_stmt = select(TournamentStatisticRow.resource_type, func.count(TournamentStatisticRow.id)).group_by(TournamentStatisticRow.resource_type)
            if tournament_external_id:
                resource_stmt = resource_stmt.where(TournamentStatisticRow.tournament_external_id == str(tournament_external_id))
            result["resource_rows"] = {str(kind): int(count) for kind, count in session.execute(resource_stmt).all()}
        return result

    def _store_tournament_directory_profiles(
        self,
        session: Session,
        *,
        tournament_external_id: str,
        player_profiles: dict[str, ParsedPlayerProfile],
        referee_profiles: dict[str, ParsedRefereeProfile],
        coach_profiles: dict[str, ParsedCoachProfile],
        countries: ParsedCountries | None,
        now: datetime,
    ) -> dict[str, int]:
        country_rows: dict[str, Country] = {}
        country_by_code: dict[str, Country] = {}
        if countries is not None:
            for profile in countries.countries.values():
                row = self._upsert_country_profile(session, profile, now=now)
                country_rows[profile.external_id] = row
                for code in (profile.iso2, profile.iso3):
                    if code:
                        country_by_code[code.upper()] = row
            session.flush()

            for player_id, country_ref in countries.player_country.items():
                profile = player_profiles.get(str(player_id))
                country = country_rows.get(str(country_ref)) or country_by_code.get(str(country_ref).upper())
                if profile is not None:
                    profile.country_external_id = (
                        country.external_id if country is not None else str(country_ref)
                    )
                    if country is not None:
                        profile.country_code = country.iso2 or country.iso3
                existing = session.scalar(
                    select(Player).where(Player.external_id == str(player_id))
                )
                if existing is not None:
                    existing.country_external_id = (
                        country.external_id if country is not None else str(country_ref)
                    )
                    if country is not None:
                        existing.country_code = country.iso2 or country.iso3
                    existing.synced_at = now

        for profile in player_profiles.values():
            if profile.country_external_id and not profile.country_code:
                country = country_rows.get(profile.country_external_id) or country_by_code.get(profile.country_external_id.upper())
                if country is not None:
                    profile.country_external_id = country.external_id
                    profile.country_code = country.iso2 or country.iso3
            player = self._upsert_player_profile(session, profile, now=now)
            session.flush()
            membership = self._upsert_player_tournament(
                session, player=player, profile=profile,
                tournament_external_id=tournament_external_id, now=now,
            )
            session.flush()
            self._upsert_player_season_statistics(
                session, player=player, membership=membership,
                raw_payload=profile.raw_payload, now=now,
            )

        for profile in referee_profiles.values():
            referee = self._upsert_referee_profile(session, profile, now=now)
            session.flush()
            if tournament_external_id:
                membership = session.scalar(
                    select(RefereeTournament).where(
                        RefereeTournament.referee_id == referee.id,
                        RefereeTournament.tournament_external_id == str(tournament_external_id),
                    )
                )
                if membership is None:
                    membership = RefereeTournament(
                        referee_id=referee.id,
                        tournament_external_id=str(tournament_external_id),
                    )
                    session.add(membership)
                membership.source = "stat2tv"
                membership.active = profile.active
                membership.synced_at = now

        for profile in coach_profiles.values():
            coach = self._upsert_coach_profile(session, profile, now=now)
            session.flush()
            self._upsert_coach_tournament(
                session, coach=coach, profile=profile,
                tournament_external_id=tournament_external_id, now=now,
            )

        return {
            "countries": len(countries.countries) if countries is not None else 0,
            "players": len(player_profiles),
            "referees": len(referee_profiles),
            "coaches": len(coach_profiles),
        }

    def _store_match_details(
        self,
        session: Session,
        *,
        game: Game,
        details: ParsedMatchDetails,
        player_profiles: dict[str, ParsedPlayerProfile],
        referee_profiles: dict[str, ParsedRefereeProfile],
        endpoint_ru: str,
        raw_ru: str,
        raw_en: str,
        now: datetime,
    ) -> None:
        game.details_source_endpoint = endpoint_ru
        game.details_raw_payload_ru = raw_ru
        game.details_raw_payload_en = raw_en
        game.details_synced_at = now
        self._apply_details_game_fields(game, details, now=now)

        if not game.home_team_name_ru:
            game.home_team_name_ru = str(details.game_ru.get("teamA") or "")
        if not game.home_team_name_en:
            game.home_team_name_en = str(details.game_en.get("teamA") or "")
        if not game.away_team_name_ru:
            game.away_team_name_ru = str(details.game_ru.get("teamB") or "")
        if not game.away_team_name_en:
            game.away_team_name_en = str(details.game_en.get("teamB") or "")
        if not game.arena_ru:
            game.arena_ru = str(details.game_ru.get("place") or "")
        if not game.arena_en:
            game.arena_en = str(details.game_en.get("place") or "")
        game.home_team_external_id = game.home_team_external_id or str(
            details.game_ru.get("idclubA") or details.game_en.get("idclubA") or ""
        )
        game.away_team_external_id = game.away_team_external_id or str(
            details.game_ru.get("idclubB") or details.game_en.get("idclubB") or ""
        )
        game.home_entry_external_id = game.home_entry_external_id or str(
            details.game_ru.get("idteamA") or details.game_en.get("idteamA") or ""
        )
        game.away_entry_external_id = game.away_entry_external_id or str(
            details.game_ru.get("idteamB") or details.game_en.get("idteamB") or ""
        )
        if not game.overtime_type:
            game.overtime_type = str(
                details.game_ru.get("ots")
                or details.game_ru.get("overtime")
                or details.game_ru.get("finishType")
                or ""
            )

        for source_side, side in (("A", "home"), ("B", "away")):
            team_ru = details.teams_ru.get(source_side) if isinstance(details.teams_ru.get(source_side), dict) else {}
            team_en = details.teams_en.get(source_side) if isinstance(details.teams_en.get(source_side), dict) else {}
            suffix = "A" if source_side == "A" else "B"
            coach_name_ru = str(team_ru.get("coach_fullname") or team_ru.get("coach") or "").strip()
            coach_name_en = str(team_en.get("coach_fullname") or team_en.get("coach") or coach_name_ru).strip()
            coach_id = str(team_ru.get("coach_id") or team_en.get("coach_id") or "").strip()
            team_id = str(
                details.game_ru.get(f"idclub{suffix}")
                or details.game_en.get(f"idclub{suffix}")
                or (game.home_team_external_id if side == "home" else game.away_team_external_id)
                or ""
            ).strip()
            if coach_name_ru or coach_name_en:
                if not coach_id:
                    digest = hashlib.sha1(f"{team_id}:{coach_name_ru or coach_name_en}".encode("utf-8")).hexdigest()[:18]
                    coach_id = f"auto-{digest}"
                profile = ParsedCoachProfile(
                    external_id=coach_id[:64],
                    full_name_ru=coach_name_ru,
                    full_name_en=coach_name_en,
                    team_external_id=team_id,
                    team_name_ru=str(team_ru.get("name") or details.game_ru.get(f"team{suffix}") or ""),
                    team_name_en=str(team_en.get("name") or details.game_en.get(f"team{suffix}") or ""),
                    raw_payload=json.dumps({"ru": team_ru, "en": team_en}, ensure_ascii=False),
                )
                coach = self._upsert_coach_profile(session, profile, now=now)
                session.flush()
                self._upsert_coach_tournament(
                    session, coach=coach, profile=profile,
                    tournament_external_id=game.tournament_external_id, now=now,
                )

        existing_rosters = list(
            session.scalars(
                select(GameRoster).where(
                    GameRoster.game_external_id == game.external_id
                )
            )
        )
        roster_by_key = {
            (row.team_external_id, row.player_external_id): row
            for row in existing_rosters
        }
        seen_rosters: set[tuple[str, str]] = set()
        api_line_order: dict[tuple[str, str], int] = {}
        for entry in details.rosters:
            profile = player_profiles.get(entry.player_external_id)
            if profile is None:
                profile = fallback_player_profile(entry)
            else:
                profile.full_name_ru = profile.full_name_ru or entry.name_ru
                profile.full_name_en = profile.full_name_en or entry.name_en
                profile.position_ru = profile.position_ru or entry.position_ru
                profile.position_en = profile.position_en or entry.position_en
            self._upsert_player_profile(session, profile, now=now)

            team_id = entry.team_external_id or (
                game.home_team_external_id
                if entry.side == "home"
                else game.away_team_external_id
            )
            key = (team_id, entry.player_external_id)
            roster = roster_by_key.get(key)
            if roster is None:
                roster = GameRoster(
                    game_external_id=game.external_id,
                    team_external_id=team_id,
                    player_external_id=entry.player_external_id,
                )
                session.add(roster)
            roster.side = entry.side
            roster.number = entry.number
            roster.role = entry.role
            roster.position_ru = entry.position_ru
            roster.position_en = entry.position_en
            api_line = str(entry.line_number or "").strip()
            api_order_key = (entry.side, api_line)
            api_line_order[api_order_key] = api_line_order.get(api_order_key, 0) + 1
            roster.api_line_number = api_line
            roster.api_line_sort_order = api_line_order[api_order_key]
            if not bool(roster.line_manual):
                roster.line_number = api_line
                roster.line_sort_order = roster.api_line_sort_order
            roster.captain_role = entry.captain_role
            roster.lineup_status = entry.lineup_status
            roster.raw_payload = entry.raw_payload
            roster.synced_at = now
            seen_rosters.add(key)

        for roster in existing_rosters:
            key = (roster.team_external_id, roster.player_external_id)
            if key not in seen_rosters:
                session.delete(roster)

        existing_officials = list(
            session.scalars(
                select(GameOfficial).where(
                    GameOfficial.game_external_id == game.external_id
                )
            )
        )
        official_by_key = {
            (row.role, row.slot): row for row in existing_officials
        }
        seen_officials: set[tuple[str, int]] = set()
        for assignment in details.officials:
            profile = referee_profiles.get(assignment.referee_external_id)
            if profile is None:
                profile = fallback_referee_profile(assignment)
            else:
                profile.full_name_ru = profile.full_name_ru or assignment.name_ru
                profile.full_name_en = profile.full_name_en or assignment.name_en
                profile.number = profile.number or assignment.number
            self._upsert_referee_profile(session, profile, now=now)

            key = (assignment.role, assignment.slot)
            official = official_by_key.get(key)
            if official is None:
                official = GameOfficial(
                    game_external_id=game.external_id,
                    role=assignment.role,
                    slot=assignment.slot,
                )
                session.add(official)
            official.referee_external_id = assignment.referee_external_id
            official.number = assignment.number
            official.raw_payload = assignment.raw_payload
            official.synced_at = now
            seen_officials.add(key)

        for official in existing_officials:
            if (official.role, official.slot) not in seen_officials:
                session.delete(official)

        raw_teams = details.teams_ru if isinstance(details.teams_ru, dict) else {}
        raw_periods = details.team_periods_ru if isinstance(getattr(details, "team_periods_ru", None), dict) else {}
        if not raw_periods:
            raw_obj = self._details_json_object(raw_ru)
            raw_periods = raw_obj.get("teams_periods") if isinstance(raw_obj.get("teams_periods"), dict) else {}
        self._store_game_team_statistics(
            session, game=game, raw_teams=raw_teams, raw_periods=raw_periods, now=now
        )

    @staticmethod
    def _manual_numeric(value: Any, default: int = 0) -> int:
        try:
            return int(float(str(value or default).replace(",", ".")))
        except (TypeError, ValueError):
            return default

    @classmethod
    def _details_score(cls, game_ru: dict[str, Any], game_en: dict[str, Any]) -> tuple[int, int, str, str]:
        raw = str(
            game_ru.get("score")
            or game_en.get("score")
            or game_ru.get("result")
            or game_en.get("result")
            or ""
        ).strip()
        score_match = re.search(r"(\d+)\s*:\s*(\d+)", raw)
        home, away = (int(score_match.group(1)), int(score_match.group(2))) if score_match else parse_score_pair(raw)
        if not raw or (home == 0 and away == 0 and ":" not in raw):
            home = cls._manual_numeric(
                game_ru.get("scoreA") or game_ru.get("goalsA") or game_ru.get("scA")
                or game_en.get("scoreA") or game_en.get("goalsA") or game_en.get("scA")
            )
            away = cls._manual_numeric(
                game_ru.get("scoreB") or game_ru.get("goalsB") or game_ru.get("scB")
                or game_en.get("scoreB") or game_en.get("goalsB") or game_en.get("scB")
            )
            raw = f"{home}:{away}" if home or away else raw
        compact = "".join(ch for ch in raw.casefold() if ch.isalnum())
        finish = ""
        if any(marker in compact for marker in ("so", "shootout", "бул", "б")):
            finish = "SO"
        elif any(marker in compact for marker in ("ot", "overtime", "оверт", "от")):
            finish = "OT"
        explicit = str(
            game_ru.get("ots") or game_en.get("ots")
            or game_ru.get("overtime") or game_en.get("overtime")
            or game_ru.get("finishType") or game_en.get("finishType")
            or ""
        ).strip()
        return home, away, raw, explicit or finish

    @classmethod
    def _apply_details_game_fields(cls, game: Game, details: ParsedMatchDetails, *, now: datetime) -> None:
        game_ru = details.game_ru or {}
        game_en = details.game_en or game_ru
        home, away, score_raw, finish = cls._details_score(game_ru, game_en)
        game.home_score = home
        game.away_score = away
        game.score_raw = score_raw or (f"{home}:{away}" if home or away else "")
        if finish:
            game.overtime_type = finish
        raw_status = str(game_ru.get("status") or game_en.get("status") or game.status or "").strip()
        if raw_status:
            game.status = raw_status
        status_value = cls._manual_numeric(game_ru.get("statusvalue") or game_en.get("statusvalue"), -1)
        finished = status_value >= 90 or cls.is_terminal_game_status(raw_status)
        if finished:
            game.approved = True
        raw_date = str(
            game_ru.get("date")
            or game_ru.get("gamedate")
            or game_ru.get("startdate")
            or game_ru.get("datetime")
            or game_ru.get("startdatetime")
            or game_en.get("date")
            or game_en.get("gamedate")
            or game_en.get("startdate")
            or game_en.get("datetime")
            or game_en.get("startdatetime")
            or ""
        ).strip()
        raw_time = str(
            game_ru.get("starttime")
            or game_ru.get("gametime")
            or game_ru.get("time")
            or game_en.get("starttime")
            or game_en.get("gametime")
            or game_en.get("time")
            or ""
        ).strip()
        combined_raw_datetime = " ".join(part for part in (raw_date, raw_time) if part).strip()
        parsed_date = (
            parse_date_value(raw_date)
            or parse_date_value(combined_raw_datetime)
            or parse_date_value(game.start_datetime_raw)
        )
        parsed_time = (
            parse_time_value(raw_time or raw_date)
            or parse_time_value(combined_raw_datetime)
            or parse_time_value(game.start_datetime_raw)
        )

        # The schedule remains the primary source. The detail card is a fallback
        # only when the selected match has no schedule date/time.
        date_was_missing = game.game_date is None
        time_was_missing = not bool(game.start_time)
        if date_was_missing and parsed_date is not None:
            game.game_date = parsed_date
        if time_was_missing and parsed_time:
            game.start_time = parsed_time
        if (date_was_missing or time_was_missing or not game.start_datetime_raw) and (raw_date or raw_time):
            # raw_date can already contain time ("16 августа 2026, Вс 13:00:00").
            # Avoid appending the same time twice.
            if raw_time and raw_time not in raw_date:
                game.start_datetime_raw = " ".join(part for part in (raw_date, raw_time) if part)
            else:
                game.start_datetime_raw = raw_date or raw_time

        # Final guard: the DB must never keep a usable localized raw date while
        # game_date is NULL. This also covers slightly different API payloads.
        if game.game_date is None and game.start_datetime_raw:
            recovered_date = parse_date_value(game.start_datetime_raw)
            if recovered_date is not None:
                game.game_date = recovered_date
        if not game.start_time and game.start_datetime_raw:
            recovered_time = parse_time_value(game.start_datetime_raw)
            if recovered_time:
                game.start_time = recovered_time

        if game_ru.get("number") not in (None, ""):
            game.game_number = str(game_ru.get("number"))
        game.attendance = cls._manual_numeric(game_ru.get("audience") or game_en.get("audience"), game.attendance or 0)
        game.synced_at = now

    @classmethod
    def _create_manual_game_from_details(
        cls,
        session: Session,
        *,
        tournament_external_id: str,
        details: ParsedMatchDetails,
        now: datetime,
    ) -> Game:
        tournament = session.scalar(
            select(Tournament).where(
                Tournament.external_id == str(tournament_external_id)
            )
        )
        if tournament is None:
            raise ValueError("Выбранный турнир не найден")

        game_ru = details.game_ru or {}
        game_en = details.game_en or game_ru
        score_a, score_b, score_raw, finish_type = cls._details_score(game_ru, game_en)
        raw_status = str(
            game_ru.get("status") or game_en.get("status") or "scheduled"
        ).strip()
        status_key = raw_status.casefold()
        approved = str(
            game_ru.get("approved") or game_en.get("approved") or ""
        ).strip().casefold() in {"1", "true", "yes", "on"}
        if any(marker in status_key for marker in ("finish", "final", "заверш")):
            approved = True

        raw_date = str(
            game_ru.get("date")
            or game_ru.get("gamedate")
            or game_ru.get("startdate")
            or game_ru.get("datetime")
            or game_ru.get("startdatetime")
            or game_en.get("date")
            or game_en.get("gamedate")
            or game_en.get("startdate")
            or game_en.get("datetime")
            or game_en.get("startdatetime")
            or ""
        ).strip()
        raw_time = str(
            game_ru.get("time")
            or game_ru.get("gametime")
            or game_ru.get("starttime")
            or game_en.get("time")
            or game_en.get("gametime")
            or game_en.get("starttime")
            or ""
        ).strip()
        row = Game(
            external_id=details.game_external_id,
            tournament_external_id=str(tournament_external_id),
            tournament_type=tournament.level or "",
            game_number=str(game_ru.get("number") or game_ru.get("gamenumber") or ""),
            game_date=parse_date_value(raw_date),
            start_time=parse_time_value(raw_time or raw_date),
            start_datetime_raw=(
                " ".join(part for part in (raw_date, raw_time) if part)
                if raw_time and raw_time not in raw_date
                else (raw_date or raw_time)
            ),
            status="finished" if approved else (raw_status or "scheduled"),
            approved=approved,
            arena_ru=str(game_ru.get("place") or ""),
            arena_en=str(game_en.get("place") or game_ru.get("place") or ""),
            home_team_external_id=str(game_ru.get("idclubA") or game_en.get("idclubA") or ""),
            away_team_external_id=str(game_ru.get("idclubB") or game_en.get("idclubB") or ""),
            home_entry_external_id=str(game_ru.get("idteamA") or game_en.get("idteamA") or ""),
            away_entry_external_id=str(game_ru.get("idteamB") or game_en.get("idteamB") or ""),
            home_team_name_ru=str(game_ru.get("teamA") or ""),
            home_team_name_en=str(game_en.get("teamA") or game_ru.get("teamA") or ""),
            away_team_name_ru=str(game_ru.get("teamB") or ""),
            away_team_name_en=str(game_en.get("teamB") or game_ru.get("teamB") or ""),
            home_score=score_a,
            away_score=score_b,
            score_raw=score_raw or (f"{score_a}:{score_b}" if score_a or score_b else ""),
            overtime_type=finish_type,
            source_endpoint="manual-match-id",
            synced_at=now,
        )
        session.add(row)
        session.flush()
        cls._apply_details_game_fields(row, details, now=now)
        return row

    async def _sync_optional_shots(
        self,
        *,
        tournament_external_id: str,
        game_external_id: str,
        force: bool = False,
    ) -> dict[str, Any]:
        settings = self.settings.public()
        template = str(settings.get("shots_endpoint_template") or "").strip()
        if not template:
            return {"available": False, "skipped": True, "reason": "template-disabled"}
        endpoint = self._resolve_match_resource_endpoint(
            template,
            tournament_external_id=tournament_external_id,
            game_external_id=game_external_id,
        )
        now = datetime.utcnow()
        negative_seconds = int(settings.get("optional_resource_negative_cache_seconds") or 86400)
        with self.database.session() as session:
            game = session.scalar(select(Game).where(Game.external_id == str(game_external_id)))
            if game is None:
                raise ValueError(f"Матч {game_external_id} не найден")
            if (
                not force
                and game.shots_synced_at
                and not game.shots_resource_available
                and (now - game.shots_synced_at).total_seconds() < negative_seconds
                and game.shots_source_endpoint == endpoint
            ):
                return {
                    "available": False,
                    "cached": True,
                    "endpoint": endpoint,
                }
            home_ids = (
                game.home_team_external_id,
                game.home_entry_external_id,
            )
            away_ids = (
                game.away_team_external_id,
                game.away_entry_external_id,
            )
            home_names = (
                game.home_team_name_ru,
                game.home_team_name_en,
                game.home_team_short_ru,
                game.home_team_short_en,
            )
            away_names = (
                game.away_team_name_ru,
                game.away_team_name_en,
                game.away_team_short_ru,
                game.away_team_short_en,
            )

        try:
            response = await self._fetch_cached_resource(endpoint, cache_seconds=0.75)
            parsed: ParsedShots = parse_shots_json(
                response.content,
                home_ids=home_ids,
                away_ids=away_ids,
                home_names=home_names,
                away_names=away_names,
            )
        except Stat2TVNotFoundError as error:
            with self.database.session() as session:
                game = session.scalar(select(Game).where(Game.external_id == str(game_external_id)))
                if game is not None:
                    game.shots_resource_available = False
                    game.shots_source_endpoint = endpoint
                    game.shots_raw_payload = ""
                    game.shots_synced_at = now
            return {
                "available": False,
                "endpoint": endpoint,
                "status_code": error.status_code,
            }
        except ValueError as error:
            # A valid but empty/unmappable payload is treated like an absent
            # optional resource and is cached to avoid repeated requests.
            with self.database.session() as session:
                game = session.scalar(select(Game).where(Game.external_id == str(game_external_id)))
                if game is not None:
                    game.shots_resource_available = False
                    game.shots_source_endpoint = endpoint
                    game.shots_raw_payload = response.content.decode(
                        "utf-8-sig", errors="replace"
                    )
                    game.shots_synced_at = now
            return {
                "available": False,
                "endpoint": endpoint,
                "cached_negative": True,
                "reason": str(error),
            }

        with self.database.session() as session:
            game = session.scalar(select(Game).where(Game.external_id == str(game_external_id)))
            if game is None:
                raise ValueError(f"Матч {game_external_id} не найден")
            game.shots_resource_available = True
            game.shot_attempts_home = parsed.home
            game.shot_attempts_away = parsed.away
            game.shots_source_endpoint = endpoint
            game.shots_raw_payload = response.content.decode("utf-8-sig", errors="replace")
            game.shots_synced_at = now
            for stat_row in session.scalars(
                select(GameTeamStatistic).where(
                    GameTeamStatistic.game_external_id == game.external_id,
                    GameTeamStatistic.segment_key == "total",
                )
            ):
                stat_row.shot_attempts = parsed.home if stat_row.side == "home" else parsed.away
                stat_row.synced_at = now
        return {
            "available": True,
            "endpoint": endpoint,
            "home": parsed.home,
            "away": parsed.away,
            "strategy": parsed.strategy,
        }

    async def sync_match_details(
        self,
        *,
        tournament_external_id: str,
        game_external_id: str,
        language: str = "ru",
        allow_create: bool = False,
        force_optional_shots: bool = False,
        skip_optional_shots: bool = False,
    ) -> dict[str, Any]:
        settings = self.settings.public()
        templates = {
            "match_ru": settings["match_json_endpoint_template"],
            "match_en": settings["match_json_en_endpoint_template"],
            "players": settings["players_endpoint_template"],
            "countries": settings["player_countries_endpoint_template"],
            "referees": settings["referees_endpoint_template"],
        }
        endpoints = {
            key: self._resolve_match_resource_endpoint(
                template,
                tournament_external_id=tournament_external_id,
                game_external_id=game_external_id,
            )
            for key, template in templates.items()
        }

        response_ru = await self._fetch_cached_resource(
            endpoints["match_ru"], cache_seconds=0.75
        )
        warnings: list[str] = []
        try:
            response_en = await self._fetch_cached_resource(
                endpoints["match_en"], cache_seconds=0.75
            )
        except Exception as error:
            response_en = None
            warnings.append(f"Английская карточка матча недоступна: {error}")

        directory_cache = float(settings["match_directories_cache_seconds"])
        player_profiles: dict[str, ParsedPlayerProfile] = {}
        referee_profiles: dict[str, ParsedRefereeProfile] = {}
        coach_profiles: dict[str, ParsedCoachProfile] = {}
        countries: ParsedCountries | None = None
        try:
            players_response = await self._fetch_cached_resource(
                endpoints["players"], cache_seconds=directory_cache
            )
            player_profiles = parse_players_xml(players_response.content)
            coach_profiles = parse_coaches_xml(players_response.content)
        except Exception as error:
            warnings.append(f"Справочник игроков недоступен: {error}")
        try:
            countries_response = await self._fetch_cached_resource(
                endpoints["countries"], cache_seconds=directory_cache
            )
            countries = parse_player_countries_xml(countries_response.content)
        except Exception as error:
            warnings.append(f"Справочник стран игроков недоступен: {error}")
        try:
            referees_response = await self._fetch_cached_resource(
                endpoints["referees"], cache_seconds=directory_cache
            )
            referee_profiles = parse_referees_xml(referees_response.content)
        except Exception as error:
            warnings.append(f"Справочник судей недоступен: {error}")

        details = parse_match_details_json(
            response_ru.content,
            response_en.content if response_en is not None else None,
            expected_game_id=str(game_external_id),
        )
        raw_ru = response_ru.content.decode("utf-8-sig")
        raw_en = (
            response_en.content.decode("utf-8-sig")
            if response_en is not None
            else raw_ru
        )
        now = datetime.utcnow()
        with self.database.session() as session:
            game = session.scalar(
                select(Game).where(Game.external_id == str(game_external_id))
            )
            if game is None:
                if not allow_create:
                    raise ValueError(f"Матч {game_external_id} не найден")
                game = self._create_manual_game_from_details(
                    session,
                    tournament_external_id=tournament_external_id,
                    details=details,
                    now=now,
                )
            if str(game.tournament_external_id) != str(tournament_external_id):
                raise ValueError("Матч не относится к выбранному турниру")
            self._store_tournament_directory_profiles(
                session,
                tournament_external_id=tournament_external_id,
                player_profiles=player_profiles,
                referee_profiles=referee_profiles,
                coach_profiles=coach_profiles,
                countries=countries,
                now=now,
            )
            self._store_match_details(
                session,
                game=game,
                details=details,
                player_profiles=player_profiles,
                referee_profiles=referee_profiles,
                endpoint_ru=endpoints["match_ru"],
                raw_ru=raw_ru,
                raw_en=raw_en,
                now=now,
            )

        shots_meta: dict[str, Any] = {"available": False, "skipped": bool(skip_optional_shots)}
        if not skip_optional_shots:
            try:
                shots_meta = await self._sync_optional_shots(
                    tournament_external_id=tournament_external_id,
                    game_external_id=game_external_id,
                    force=force_optional_shots,
                )
            except Exception as error:
                warnings.append(f"Дополнительные данные shots недоступны: {error}")

        payload = self.game_details(game_external_id, language=language)
        if payload is None:
            raise ValueError("Не удалось прочитать сохранённую карточку матча")
        print(
            f"[Hockey DB] game updated tournament={tournament_external_id} "
            f"game={game_external_id}"
        )
        return {
            "ok": True,
            "endpoint": endpoints["match_ru"],
            "auth_mode": response_ru.auth_mode,
            "warnings": warnings,
            "shots": shots_meta,
            **payload,
        }

    async def sync_game_by_id(
        self,
        *,
        tournament_external_id: str,
        game_external_id: str,
        language: str = "ru",
    ) -> dict[str, Any]:
        schedule: dict[str, Any] | None = None
        warnings: list[str] = []
        try:
            schedule = await self.sync_selected_game(
                tournament_external_id=tournament_external_id,
                game_external_id=game_external_id,
                language=language,
            )
        except Exception as error:
            warnings.append(f"Расписание для матча недоступно: {error}")

        payload = await self.sync_match_details(
            tournament_external_id=tournament_external_id,
            game_external_id=game_external_id,
            language=language,
            allow_create=True,
            force_optional_shots=True,
        )
        payload["schedule"] = schedule
        payload.setdefault("warnings", []).extend(warnings)
        return payload

    @staticmethod
    def _stable_match_json_object(value: Any) -> Any:
        """Remove transport-only fields before comparing live match JSON."""
        ignored = {"db3_gentime", "generated_at", "generatedat"}
        if isinstance(value, dict):
            return {
                str(key): HockeyDataService._stable_match_json_object(item)
                for key, item in sorted(value.items(), key=lambda pair: str(pair[0]))
                if str(key).casefold() not in ignored
            }
        if isinstance(value, list):
            return [HockeyDataService._stable_match_json_object(item) for item in value]
        return value

    @classmethod
    def _stable_match_json_digest(cls, value: bytes | str | dict[str, Any] | None) -> str:
        if value in (None, "", b""):
            return ""
        try:
            if isinstance(value, dict):
                parsed = value
            else:
                text = value.decode("utf-8-sig", errors="replace") if isinstance(value, bytes) else str(value)
                parsed = json.loads(text)
        except (TypeError, ValueError, json.JSONDecodeError):
            return hashlib.sha256(str(value).encode("utf-8", errors="replace")).hexdigest()
        stable = cls._stable_match_json_object(parsed)
        return hashlib.sha256(
            json.dumps(stable, ensure_ascii=False, sort_keys=True, separators=(",", ":")).encode("utf-8")
        ).hexdigest()

    @classmethod
    def _changed_match_json_sections(cls, old_raw: str, new_raw: bytes | str) -> list[str]:
        try:
            old = json.loads(old_raw) if old_raw else {}
            new_text = new_raw.decode("utf-8-sig", errors="replace") if isinstance(new_raw, bytes) else str(new_raw)
            new = json.loads(new_text)
        except (TypeError, ValueError, json.JSONDecodeError):
            return ["данные матча"]
        labels = {
            "game": "счёт и статус",
            "teams": "командная статистика",
            "players": "статистика игроков",
            "events": "события",
            "teams_periods": "статистика по периодам",
            "shots_map": "карта бросков",
        }
        changed: list[str] = []
        for key, label in labels.items():
            if cls._stable_match_json_digest(old.get(key, {})) != cls._stable_match_json_digest(new.get(key, {})):
                changed.append(label)
        return changed or ["данные матча"]

    def _game_search_tournament_groups(self, game_external_id: str) -> list[list[str]]:
        """Order tournament probes for fast manual game-ID discovery.

        The ``gameid`` flag in tournaments.xml is only a capability hint. Some
        current/pre-season tournaments expose ``json/{game_id}.json`` even when
        the flag is absent, so active tournaments must be checked before the
        much larger historical ``gameid=true`` set.
        """
        today = date.today()
        recent_from = today - timedelta(days=365 * 4)
        near_future_until = today + timedelta(days=75)
        with self.database.session() as session:
            existing = session.scalar(
                select(Game).where(Game.external_id == str(game_external_id))
            )
            rows = list(session.scalars(select(Tournament)))
        existing_id = str(existing.tournament_external_id) if existing is not None else ""

        def has_dates(row: Tournament) -> bool:
            return row.start_date is not None or row.end_date is not None

        def is_active(row: Tournament) -> bool:
            # Undated legacy tournaments used to be treated as active and could
            # jump ahead of the real current season. Do not do that.
            return bool(
                has_dates(row)
                and (row.start_date is None or row.start_date <= today)
                and (row.end_date is None or row.end_date >= today)
            )

        def is_near_future(row: Tournament) -> bool:
            return bool(
                row.start_date is not None
                and today < row.start_date <= near_future_until
            )

        def stamp(row: Tournament) -> int:
            return (row.end_date or row.start_date or date.min).toordinal()

        rows.sort(
            key=lambda row: (
                1 if is_active(row) else 0,
                1 if is_near_future(row) else 0,
                1 if row.game_id_supported else 0,
                stamp(row),
            ),
            reverse=True,
        )

        groups: list[list[str]] = []
        used: set[str] = set()

        def append_group(items: list[Tournament]) -> None:
            ids: list[str] = []
            for row in items:
                external_id = str(row.external_id)
                if not external_id or external_id in used:
                    continue
                used.add(external_id)
                ids.append(external_id)
            if ids:
                groups.append(ids)

        if existing_id:
            used.add(existing_id)
            groups.append([existing_id])

        # 1) What is being played right now. This intentionally ignores the
        #    gameid capability flag. It fixes current pre-season/manual IDs.
        append_group([row for row in rows if is_active(row)])

        # 2) Tournaments starting soon (new season/cups are often loaded into
        #    Stat2TV before the first schedule XML is available).
        append_group([row for row in rows if is_near_future(row)])

        # 3) Recent feeds explicitly advertising game-ID support.
        append_group([
            row
            for row in rows
            if row.game_id_supported
            and (row.end_date or row.start_date or date.min) >= recent_from
        ])

        # 4) Recent tournaments without the capability flag.
        append_group([
            row
            for row in rows
            if (row.end_date or row.start_date or date.min) >= recent_from
        ])

        # 5) Historical fallback. Kept for old match IDs, but only after the
        #    likely current candidates have been exhausted.
        append_group(rows)
        return groups

    async def _refresh_tournaments_for_game_search(self) -> int:
        """Refresh the tournament directory without creating debug snapshots.

        Manual game-ID lookup needs the newest tournament IDs, but calling the
        full sync routine here would create a tournaments snapshot every time an
        operator enters a game ID.
        """
        response = await self.client.fetch_xml("tournaments.xml")
        parsed = parse_tournaments_xml(response.content)
        now = datetime.utcnow()
        with self.database.session() as session:
            for item in parsed:
                self._upsert_tournament(session, item, now=now)
        return len(parsed)

    async def _discover_game_tournament(self, game_external_id: str) -> str:
        settings = self.settings.public()
        template = str(settings.get("match_json_endpoint_template") or "").strip()
        if not template:
            raise ValueError("Шаблон JSON матча не настроен")

        async def probe(tournament_id: str, semaphore: asyncio.Semaphore) -> str | None:
            endpoint = self._resolve_match_resource_endpoint(
                template,
                tournament_external_id=tournament_id,
                game_external_id=game_external_id,
            )
            async with semaphore:
                try:
                    response = await self.client.fetch_xml(endpoint)
                    parse_match_details_json(response.content, None, expected_game_id=str(game_external_id))
                    return tournament_id
                except Exception:
                    return None

        async with self._global_game_search_lock:
            for group in self._game_search_tournament_groups(game_external_id):
                semaphore = asyncio.Semaphore(14)
                tasks = [asyncio.create_task(probe(tournament_id, semaphore)) for tournament_id in group]
                try:
                    for task in asyncio.as_completed(tasks):
                        tournament_id = await task
                        if tournament_id:
                            for pending in tasks:
                                if not pending.done():
                                    pending.cancel()
                            await asyncio.gather(*tasks, return_exceptions=True)
                            return tournament_id
                finally:
                    for task in tasks:
                        if not task.done():
                            task.cancel()
                    await asyncio.gather(*tasks, return_exceptions=True)
        raise ValueError(f"Матч {game_external_id} не найден ни в одном доступном турнире")

    async def sync_game_by_id_global(
        self,
        *,
        game_external_id: str,
        language: str = "ru",
    ) -> dict[str, Any]:
        # Manual lookup must not depend on a stale local tournament directory.
        # Refresh it first, but keep the cached database usable if Stat2TV is
        # temporarily unavailable.
        try:
            await self._refresh_tournaments_for_game_search()
        except Exception:
            pass

        tournament_external_id = await self._discover_game_tournament(str(game_external_id))
        payload = await self.sync_game_by_id(
            tournament_external_id=tournament_external_id,
            game_external_id=str(game_external_id),
            language=language,
        )
        payload["tournament"] = self.tournament(tournament_external_id, language=language)
        payload["discovered_tournament_id"] = tournament_external_id
        return payload

    async def sync_live_game(
        self,
        *,
        tournament_external_id: str,
        game_external_id: str,
        language: str = "ru",
    ) -> dict[str, Any]:
        before = self.game_details(game_external_id, language=language)
        before_game = (before or {}).get("game") or {}
        with self.database.session() as session:
            row = session.scalar(select(Game).where(Game.external_id == str(game_external_id)))
            old_raw = row.details_raw_payload_ru if row is not None else ""

        schedule_warning = ""
        try:
            await self.sync_selected_game(
                tournament_external_id=tournament_external_id,
                game_external_id=game_external_id,
                language=language,
            )
        except Exception as error:
            schedule_warning = str(error)

        settings = self.settings.public()
        endpoint = self._resolve_match_resource_endpoint(
            str(settings.get("match_json_endpoint_template") or "{tournament_id}/json/{game_id}.json"),
            tournament_external_id=tournament_external_id,
            game_external_id=game_external_id,
        )
        response = await self.client.fetch_xml(endpoint)
        self._resource_response_cache[endpoint] = (time.monotonic(), response)
        json_changed = self._stable_match_json_digest(old_raw) != self._stable_match_json_digest(response.content)
        changed_sections: list[str] = []
        if json_changed or not old_raw:
            changed_sections = self._changed_match_json_sections(old_raw, response.content)
            details = await self.sync_match_details(
                tournament_external_id=tournament_external_id,
                game_external_id=game_external_id,
                language=language,
                allow_create=True,
                force_optional_shots=False,
            )
        else:
            details = self.game_details(game_external_id, language=language)
            if details is None:
                details = await self.sync_match_details(
                    tournament_external_id=tournament_external_id,
                    game_external_id=game_external_id,
                    language=language,
                    allow_create=True,
                    force_optional_shots=False,
                )

        after_game = (details or {}).get("game") or {}
        schedule_changed = any(
            before_game.get(key) != after_game.get(key)
            for key in ("home_score", "away_score", "score", "status", "is_finished", "finish_type")
        )
        if schedule_changed and "счёт и статус" not in changed_sections:
            changed_sections.insert(0, "счёт и статус")
        result = dict(details or {})
        result.update({
            "ok": True,
            "changed": bool(json_changed or schedule_changed),
            "changed_sections": changed_sections,
            "refresh_interval_ms": 1000,
            "schedule_warning": schedule_warning,
        })
        return result

    async def sync_selected_game_details(
        self,
        *,
        tournament_external_id: str,
        game_external_id: str,
        language: str = "ru",
        opening: bool = False,
    ) -> dict[str, Any]:
        schedule: dict[str, Any] | None = None
        schedule_warning = ""
        if opening:
            # BUILD110: the operator clicked a row that already came from the
            # local schedule. Do not spend the critical roster-loading window on
            # another schedule round-trip; live polling refreshes it afterwards.
            schedule = self.game(game_external_id, language=language)
        else:
            try:
                schedule = await self.sync_selected_game(
                    tournament_external_id=tournament_external_id,
                    game_external_id=game_external_id,
                    language=language,
                )
            except Exception as error:
                if self.game(game_external_id, language=language) is None:
                    raise
                schedule_warning = str(error)

        details_warning = ""
        try:
            # Opening a match waits only for the core card/rosters. Optional
            # shots are deliberately deferred to live/background refresh so a
            # slow auxiliary feed cannot keep the loading overlay open.
            payload = await asyncio.wait_for(
                self.sync_match_details(
                    tournament_external_id=tournament_external_id,
                    game_external_id=game_external_id,
                    language=language,
                    allow_create=True,
                    force_optional_shots=not opening,
                    skip_optional_shots=opening,
                ),
                timeout=12.0,
            )
        except Exception as error:
            details_warning = str(error)
            payload = self.game_details(game_external_id, language=language)
            if payload is None:
                raise
            payload = dict(payload)
            payload.update({
                "ok": True,
                "degraded": True,
                "details_sync_failed": True,
                "shots": {"available": False},
            })

        payload["schedule"] = schedule
        if schedule_warning:
            payload.setdefault("warnings", []).append(
                f"Расписание не обновлено: {schedule_warning}"
            )
        if details_warning:
            payload.setdefault("warnings", []).append(
                f"Полная карточка матча пока недоступна: {details_warning}"
            )
        return payload

    @staticmethod
    def _standings_name_key(value: Any) -> str:
        return "".join(
            character for character in str(value or "").casefold()
            if character.isalnum()
        )

    @classmethod
    def _highlight_standings_teams(
        cls,
        payload: dict[str, Any],
        game: Game | None,
    ) -> dict[str, Any]:
        if game is None:
            return payload

        sides = {
            "home": {
                "entry_ids": {str(game.home_entry_external_id or "")},
                "club_ids": {str(game.home_team_external_id or "")},
                "names": {
                    cls._standings_name_key(game.home_team_name_ru),
                    cls._standings_name_key(game.home_team_name_en),
                    cls._standings_name_key(game.home_team_short_ru),
                    cls._standings_name_key(game.home_team_short_en),
                },
            },
            "away": {
                "entry_ids": {str(game.away_entry_external_id or "")},
                "club_ids": {str(game.away_team_external_id or "")},
                "names": {
                    cls._standings_name_key(game.away_team_name_ru),
                    cls._standings_name_key(game.away_team_name_en),
                    cls._standings_name_key(game.away_team_short_ru),
                    cls._standings_name_key(game.away_team_short_en),
                },
            },
        }
        for values in sides.values():
            values["entry_ids"].discard("")
            values["club_ids"].discard("")
            values["names"].discard("")

        for variant in payload.get("variants", []):
            for group in variant.get("groups", []):
                for team in group.get("teams", []):
                    entry_id = str(team.get("entry_id") or team.get("id") or "")
                    club_id = str(team.get("club_id") or "")
                    names = team.get("names") if isinstance(team.get("names"), dict) else {}
                    name_keys = {
                        cls._standings_name_key(team.get("name")),
                        cls._standings_name_key(names.get("ru")),
                        cls._standings_name_key(names.get("en")),
                    }
                    name_keys.discard("")
                    matched_side = ""
                    for side, values in sides.items():
                        id_match = (
                            entry_id in values["entry_ids"]
                            or club_id in values["club_ids"]
                        )
                        name_match = bool(name_keys & values["names"])
                        if id_match or name_match:
                            matched_side = side
                            break
                    team["highlighted"] = bool(matched_side)
                    team["side"] = matched_side
        return payload

    def standings(
        self,
        tournament_external_id: str,
        *,
        language: str = "ru",
        game_external_id: str = "",
    ) -> dict[str, Any] | None:
        settings = self.settings.public()
        language = normalise_language(language, settings["ui_language"])
        with self.database.session() as session:
            row = session.scalar(
                select(TournamentStanding).where(
                    TournamentStanding.tournament_external_id
                    == str(tournament_external_id)
                )
            )
            if row is None:
                return None
            game = (
                session.scalar(
                    select(Game).where(Game.external_id == str(game_external_id))
                )
                if game_external_id
                else None
            )
            raw_payload = row.raw_payload
            endpoint = row.source_endpoint
            synced_at = row.synced_at

        payload = parse_standings_xml(raw_payload, language=language)
        payload = self._highlight_standings_teams(payload, game)
        return {
            "standings": payload,
            "endpoint": endpoint,
            "synced_at": synced_at.isoformat() if synced_at else "",
            "cached": True,
        }

    async def sync_standings(
        self,
        tournament_external_id: str,
        *,
        language: str = "ru",
        game_external_id: str = "",
    ) -> dict[str, Any]:
        settings = self.settings.public()
        endpoint = self._resolve_match_resource_endpoint(
            settings["standings_endpoint_template"],
            tournament_external_id=str(tournament_external_id),
            game_external_id=str(game_external_id),
        )
        response = await self._fetch_cached_resource(
            endpoint,
            cache_seconds=float(settings["standings_cache_seconds"]),
        )
        parsed = parse_standings_xml(response.content, language=language)
        source_tournament_id = str(parsed.get("tournament_id") or "")
        if source_tournament_id and source_tournament_id != str(tournament_external_id):
            raise ValueError("Stat2TV вернул таблицу другого турнира")

        raw_payload = response.content.decode("utf-8-sig")
        now = datetime.utcnow()
        with self.database.session() as session:
            row = session.scalar(
                select(TournamentStanding).where(
                    TournamentStanding.tournament_external_id
                    == str(tournament_external_id)
                )
            )
            if row is None:
                row = TournamentStanding(
                    tournament_external_id=str(tournament_external_id)
                )
                session.add(row)
            row.source_endpoint = endpoint
            row.raw_payload = raw_payload
            row.synced_at = now
            normalized_rows = self._store_normalized_standings(
                session,
                tournament_external_id=str(tournament_external_id),
                parsed=parsed,
                now=now,
            )

        print(f"[Hockey DB] standings updated tournament={tournament_external_id} rows={normalized_rows}")
        result = self.standings(
            str(tournament_external_id),
            language=language,
            game_external_id=game_external_id,
        )
        if result is None:
            raise ValueError("Не удалось сохранить турнирную таблицу")
        result.update({"ok": True, "cached": False, "auth_mode": response.auth_mode})
        return result

    async def statistic_navigation_labels(
        self,
        tournament_external_id: str,
        *,
        force: bool = False,
    ) -> dict[str, Any]:
        tournament_id = str(tournament_external_id or "").strip()
        now = time.monotonic()
        cached = self._navigation_labels_cache.get(tournament_id)
        if cached and not force and now - cached[0] < 300:
            return cached[1]

        settings = self.settings.public()
        endpoint = self._resolve_match_resource_endpoint(
            str(settings.get("navigation_endpoint_template") or "{tournament_id}/navigation-{tournament_id}.xml"),
            tournament_external_id=tournament_id,
            game_external_id="",
        )
        payload: dict[str, Any]
        try:
            response = await self._fetch_cached_resource(endpoint, cache_seconds=0.0 if force else 300.0)
            payload = parse_navigation_labels_xml(response.content)
            payload.update({"source": "league", "endpoint": endpoint, "tournament_id": tournament_id})
        except Exception as error:
            fallback_path = self.base_dir / "data" / "navigation-fallback.xml"
            if fallback_path.exists():
                payload = parse_navigation_labels_xml(fallback_path.read_bytes())
                payload.update({
                    "source": "fallback",
                    "endpoint": "data/navigation-fallback.xml",
                    "tournament_id": tournament_id,
                    "warning": str(error),
                })
            else:
                payload = {
                    "available": False, "columns": {}, "columns_by_section": {},
                    "sections": {}, "source": "none", "endpoint": endpoint,
                    "tournament_id": tournament_id, "warning": str(error),
                }
        self._navigation_labels_cache[tournament_id] = (now, payload)
        return payload

    @staticmethod
    def _empty_tournament_statistic(resource_type: str) -> dict[str, Any]:
        return {
            "resource_type": resource_type,
            "available": False,
            "sections": [],
            "generated_at": "",
            "tournament_id": "",
        }

    def tournament_statistics(
        self,
        tournament_external_id: str,
        *,
        language: str = "ru",
    ) -> dict[str, Any]:
        settings = self.settings.public()
        language = normalise_language(language, settings["ui_language"])
        resource_types = ("powerplay", "rank")
        with self.database.session() as session:
            rows = list(
                session.scalars(
                    select(TournamentStatisticResource).where(
                        TournamentStatisticResource.tournament_external_id
                        == str(tournament_external_id)
                    )
                )
            )

        by_type = {row.resource_type: row for row in rows}
        payload: dict[str, Any] = {
            "tournament_id": str(tournament_external_id),
            "available_tabs": [],
        }
        resources_meta: dict[str, Any] = {}
        for resource_type in resource_types:
            row = by_type.get(resource_type)
            parsed = self._empty_tournament_statistic(resource_type)
            if row is not None and row.available and row.raw_payload:
                try:
                    parsed = parse_tournament_statistics_xml(
                        row.raw_payload,
                        resource_type=resource_type,
                        language=language,
                    )
                except Exception as error:
                    parsed = self._empty_tournament_statistic(resource_type)
                    parsed["parse_error"] = str(error)
            parsed["checked"] = row is not None
            parsed["status_code"] = row.status_code if row is not None else 0
            parsed["endpoint"] = row.source_endpoint if row is not None else ""
            parsed["checked_at"] = (
                row.checked_at.isoformat() if row is not None and row.checked_at else ""
            )
            parsed["synced_at"] = (
                row.synced_at.isoformat() if row is not None and row.synced_at else ""
            )
            if row is not None and row.error_message:
                parsed["error"] = row.error_message
            if parsed.get("available"):
                payload["available_tabs"].append(resource_type)
            payload[resource_type] = parsed
            resources_meta[resource_type] = {
                "checked": row is not None,
                "available": bool(parsed.get("available")),
                "status_code": row.status_code if row is not None else 0,
                "checked_at": parsed["checked_at"],
            }
        payload["resources"] = resources_meta
        return payload

    async def sync_tournament_statistics(
        self,
        tournament_external_id: str,
        *,
        language: str = "ru",
        force: bool = False,
    ) -> dict[str, Any]:
        settings = self.settings.public()
        language = normalise_language(language, settings["ui_language"])
        now = datetime.utcnow()
        resource_config = {
            "powerplay": settings["powerplay_endpoint_template"],
            "rank": settings["rank_endpoint_template"],
        }
        with self.database.session() as session:
            existing_rows = {
                row.resource_type: row
                for row in session.scalars(
                    select(TournamentStatisticResource).where(
                        TournamentStatisticResource.tournament_external_id
                        == str(tournament_external_id)
                    )
                )
            }

        result_meta: dict[str, Any] = {}
        for resource_type, template in resource_config.items():
            existing = existing_rows.get(resource_type)
            age_seconds = (
                (now - existing.checked_at).total_seconds()
                if existing is not None and existing.checked_at
                else None
            )
            if existing is not None and existing.available:
                ttl = float(settings["tournament_statistics_cache_seconds"])
            elif existing is not None and existing.status_code in {200, 204, 404, 410}:
                # A valid but empty XML is also an optional resource miss. Do not
                # request it again on every tournament/game opening.
                ttl = float(settings["optional_resource_negative_cache_seconds"])
            else:
                ttl = 0.0
            if not force and age_seconds is not None and age_seconds < ttl:
                result_meta[resource_type] = {
                    "ok": True,
                    "cached": True,
                    "available": existing.available,
                    "status_code": existing.status_code,
                    "skipped": True,
                }
                continue

            endpoint = self._resolve_match_resource_endpoint(
                template,
                tournament_external_id=str(tournament_external_id),
                game_external_id="",
            )
            try:
                response = await self._fetch_cached_resource(
                    endpoint,
                    cache_seconds=(
                        0.0 if force
                        else float(settings["tournament_statistics_cache_seconds"])
                    ),
                )
                parsed = parse_tournament_statistics_xml(
                    response.content,
                    resource_type=resource_type,
                    language=language,
                )
                source_tournament_id = str(parsed.get("tournament_id") or "")
                if source_tournament_id and source_tournament_id != str(tournament_external_id):
                    raise ValueError(
                        f"Stat2TV вернул {resource_type} другого турнира"
                    )
                raw_payload = response.content.decode("utf-8-sig")
                checked_at = datetime.utcnow()
                with self.database.session() as session:
                    row = session.scalar(
                        select(TournamentStatisticResource).where(
                            TournamentStatisticResource.tournament_external_id
                            == str(tournament_external_id),
                            TournamentStatisticResource.resource_type == resource_type,
                        )
                    )
                    if row is None:
                        row = TournamentStatisticResource(
                            tournament_external_id=str(tournament_external_id),
                            resource_type=resource_type,
                        )
                        session.add(row)
                    row.source_endpoint = endpoint
                    row.raw_payload = raw_payload
                    row.available = bool(parsed.get("available"))
                    row.status_code = int(response.status_code)
                    row.error_message = ""
                    row.checked_at = checked_at
                    row.synced_at = checked_at if row.available else None
                    normalized_rows = self._store_normalized_tournament_statistic_rows(
                        session,
                        tournament_external_id=str(tournament_external_id),
                        resource_type=resource_type,
                        parsed=parsed,
                        now=checked_at,
                    )
                print(
                    f"[Hockey DB] {resource_type} updated "
                    f"tournament={tournament_external_id} rows={normalized_rows}"
                )
                self._save_snapshot(
                    response.content,
                    resource=resource_type,
                    tournament_external_id=str(tournament_external_id),
                )
                result_meta[resource_type] = {
                    "ok": True,
                    "cached": False,
                    "available": bool(parsed.get("available")),
                    "status_code": int(response.status_code),
                    "endpoint": endpoint,
                    "auth_mode": response.auth_mode,
                }
            except Stat2TVNotFoundError as error:
                checked_at = datetime.utcnow()
                with self.database.session() as session:
                    row = session.scalar(
                        select(TournamentStatisticResource).where(
                            TournamentStatisticResource.tournament_external_id
                            == str(tournament_external_id),
                            TournamentStatisticResource.resource_type == resource_type,
                        )
                    )
                    if row is None:
                        row = TournamentStatisticResource(
                            tournament_external_id=str(tournament_external_id),
                            resource_type=resource_type,
                        )
                        session.add(row)
                    row.source_endpoint = endpoint
                    row.raw_payload = ""
                    row.available = False
                    row.status_code = int(error.status_code)
                    row.error_message = ""
                    row.checked_at = checked_at
                    row.synced_at = None
                    session.execute(
                        delete(TournamentStatisticRow).where(
                            TournamentStatisticRow.tournament_external_id == str(tournament_external_id),
                            TournamentStatisticRow.resource_type == resource_type,
                        )
                    )
                    if resource_type == "powerplay":
                        self._rebuild_team_season_statistics(
                            session, str(tournament_external_id), now=checked_at
                        )
                result_meta[resource_type] = {
                    "ok": True,
                    "cached": False,
                    "available": False,
                    "status_code": int(error.status_code),
                    "endpoint": endpoint,
                    "missing": True,
                }
            except Exception as error:
                checked_at = datetime.utcnow()
                with self.database.session() as session:
                    row = session.scalar(
                        select(TournamentStatisticResource).where(
                            TournamentStatisticResource.tournament_external_id
                            == str(tournament_external_id),
                            TournamentStatisticResource.resource_type == resource_type,
                        )
                    )
                    if row is None:
                        row = TournamentStatisticResource(
                            tournament_external_id=str(tournament_external_id),
                            resource_type=resource_type,
                            available=False,
                        )
                        session.add(row)
                    row.source_endpoint = endpoint
                    row.error_message = str(error)[:2000]
                    row.status_code = 0
                    row.checked_at = checked_at
                result_meta[resource_type] = {
                    "ok": False,
                    "cached": bool(existing and existing.available),
                    "available": bool(existing and existing.available),
                    "status_code": 0,
                    "endpoint": endpoint,
                    "error": str(error),
                }

        payload = self.tournament_statistics(
            str(tournament_external_id), language=language
        )
        return {
            "ok": not any(not item.get("ok") for item in result_meta.values()),
            "statistics": payload,
            "resources": result_meta,
        }

    @staticmethod
    def _details_json_object(value: str) -> dict[str, Any]:
        if not value:
            return {}
        try:
            result = json.loads(value)
        except (TypeError, ValueError):
            return {}
        return result if isinstance(result, dict) else {}

    @staticmethod
    def _number_sort_value(value: str) -> tuple[int, str]:
        digits = "".join(character for character in str(value) if character.isdigit())
        return (int(digits) if digits else 10000, str(value))

    @staticmethod
    def _normalise_team_statistics(row: Any) -> dict[str, Any]:
        source = row if isinstance(row, dict) else {}

        def value(key: str, fallback: str | None = None) -> Any:
            if key in source:
                return source[key]
            if fallback and fallback in source:
                return source[fallback]
            return None

        return {
            "goals": value("goals"),
            "shots": value("shots"),
            "faceoffs": value("fo"),
            "faceoffs_won": value("fow"),
            "faceoffs_won_pct": value("fow_pct"),
            "hits": value("hits"),
            "blocked_shots": value("bls"),
            "takeaways": value("tka"),
            "giveaways": value("gva"),
            "interceptions": value("p_intc"),
            "penalty_minutes": value("pim", "outs"),
            "penalties": value("fouls"),
            "time_on_attack": value("toa"),
            "even_strength_time": value("tie"),
            "power_play_time": value("tipp"),
            "short_handed_time": value("tish"),
            "empty_net_time": value("tien"),
        }

    @staticmethod
    def _normalise_player_statistics(
        row: Any,
        *,
        role: str,
    ) -> dict[str, Any]:
        source = row if isinstance(row, dict) else {}

        def number(key: str, default: int = 0) -> int | float:
            value = source.get(key)
            if value in (None, ""):
                return default
            try:
                numeric = float(str(value).replace(",", "."))
            except (TypeError, ValueError):
                return default
            return int(numeric) if numeric.is_integer() else numeric

        played = str(source.get("pig") or "").strip().lower() in {
            "1", "true", "yes", "y", "on"
        }
        if role == "goalkeeper":
            shots_against = number("sog")
            goals_against = number("ga")
            saves_value = source.get("sv")
            saves = (
                number("sv")
                if saves_value not in (None, "")
                else max(0, int(shots_against) - int(goals_against))
            )
            save_pct_value = source.get("sv_pct")
            save_pct = number("sv_pct") if save_pct_value not in (None, "") else None
            if save_pct is None and shots_against:
                save_pct = round(float(saves) / float(shots_against) * 100, 2)
            return {
                "kind": "goalkeeper",
                "played": played,
                "shots_against": shots_against,
                "saves": saves,
                "goals_against": goals_against,
                "save_pct": save_pct,
                "time_on_ice": source.get("toi") or "",
                "assists": number("assists"),
                "penalty_minutes": number("pim"),
            }

        faceoffs = number("fo")
        faceoffs_won = number("fow")
        faceoff_pct = (
            round(float(faceoffs_won) / float(faceoffs) * 100, 1)
            if faceoffs
            else None
        )
        return {
            "kind": "skater",
            "played": played,
            "goals": number("goals"),
            "assists": number("assists"),
            "points": number("pts"),
            "shots": number("shots"),
            "hits": number("hits"),
            "blocked_shots": number("bls"),
            "takeaways": number("tka"),
            "penalty_minutes": number("pim"),
            "penalties": number("outs"),
            "faceoffs": faceoffs,
            "faceoffs_won": faceoffs_won,
            "faceoff_pct": faceoff_pct,
            "time_on_ice": source.get("toi") or "",
            "even_strength_time": source.get("tie") or "",
            "power_play_time": source.get("tipp") or "",
            "short_handed_time": source.get("tish") or "",
            "empty_net_time": source.get("tien") or "",
            "shifts": number("si"),
        }

    @staticmethod
    def _normalise_season_player_statistics(
        row: Any,
        *,
        role: str,
    ) -> dict[str, Any]:
        source = row if isinstance(row, dict) else {}

        def number(key: str, default: int | float = 0) -> int | float:
            value = source.get(key)
            if value in (None, "", "-"):
                return default
            try:
                numeric = float(str(value).replace(",", "."))
            except (TypeError, ValueError):
                return default
            return int(numeric) if numeric.is_integer() else numeric

        available = bool(source.get("gp") not in (None, ""))
        common = {
            "available": available,
            "games": number("gp"),
            "goals": number("g"),
            "assists": number("a"),
            "penalty_minutes": number("pim"),
            "team_entry_id": str(source.get("clubidt") or ""),
            "club_id": str(source.get("clubidg") or ""),
            "team_name": str(source.get("teamname") or source.get("teamnameen") or ""),
            "jersey_number": str(source.get("jn") or ""),
        }
        if role == "goalkeeper":
            return {
                **common,
                "kind": "goalkeeper",
                "wins": number("w"),
                "losses": number("l"),
                "draws": number("n"),
                "overtime_losses": number("sop"),
                "shots_against": number("sog"),
                "goals_against": number("ga"),
                "saves": number("sv"),
                "save_pct": number("sv_pct", 0),
                "goals_against_average": number("gaa", 0),
                "shutouts": number("so"),
                "time_on_ice": str(source.get("toi") or ""),
                "shootouts": number("ps"),
                "shootout_goals_against": number("psga"),
                "shootout_save_pct": number("pssv_pct", 0),
            }
        return {
            **common,
            "kind": "skater",
            "points": number("pts"),
            "plus_minus": number("pm"),
            "plus": number("plus"),
            "minus": number("minus"),
            "power_play_goals": number("ppg"),
            "short_handed_goals": number("shg"),
            "game_winning_goals": number("gwg"),
            "even_strength_goals": number("eqg"),
            "overtime_goals": number("otg"),
            "shots": number("sog"),
            "shot_pct": number("sog_pct", 0),
            "faceoffs": number("fo"),
            "faceoffs_won": number("fow"),
            "faceoff_pct": number("fo_pct", 0),
            "takeaways": number("tka"),
            "interceptions": number("p_intc"),
            "hits": number("hits"),
            "blocked_shots": number("bls"),
            "average_time_on_ice": str(source.get("toi_avg") or ""),
            "average_shifts": number("sft_avg", 0),
            "average_shots": number("sog_avg", 0),
            "average_time_on_attack": str(source.get("toa_avg") or ""),
            "average_even_strength_time": str(source.get("tie_avg") or ""),
            "average_power_play_time": str(source.get("tipp_avg") or ""),
            "average_short_handed_time": str(source.get("tish_avg") or ""),
        }

    @classmethod
    def _team_statistics_payload(
        cls,
        raw_teams: dict[str, Any],
        raw_periods: dict[str, Any],
    ) -> dict[str, Any]:
        total = {
            "home": cls._normalise_team_statistics(raw_teams.get("A")),
            "away": cls._normalise_team_statistics(raw_teams.get("B")),
        }
        home_periods = {
            str(key): value
            for key, value in (
                raw_periods.get("A", {}).items()
                if isinstance(raw_periods.get("A"), dict)
                else []
            )
        }
        away_periods = {
            str(key): value
            for key, value in (
                raw_periods.get("B", {}).items()
                if isinstance(raw_periods.get("B"), dict)
                else []
            )
        }
        period_ids = sorted(
            {str(value) for value in (*home_periods.keys(), *away_periods.keys())},
            key=cls._number_sort_value,
        )
        periods = {
            period_id: {
                "home": cls._normalise_team_statistics(
                    home_periods.get(period_id)
                ),
                "away": cls._normalise_team_statistics(
                    away_periods.get(period_id)
                ),
            }
            for period_id in period_ids
        }
        return {
            "available": bool(raw_teams or periods),
            "segments": ["total", *period_ids],
            "total": total,
            "periods": periods,
        }

    def game_details(
        self,
        external_id: str,
        *,
        language: str = "ru",
    ) -> dict[str, Any] | None:
        settings = self.settings.public()
        language = normalise_language(language, settings["ui_language"])
        fallback_language = settings["fallback_language"]
        with self.database.session() as session:
            game = session.scalar(
                select(Game).where(Game.external_id == str(external_id))
            )
            if game is None:
                return None
            game_payload = self._serialize_game(
                game,
                language=language,
                fallback_language=fallback_language,
            )
            roster_rows = list(
                session.scalars(
                    select(GameRoster).where(
                        GameRoster.game_external_id == game.external_id
                    )
                )
            )
            player_ids = {row.player_external_id for row in roster_rows}
            player_rows = (
                list(
                    session.scalars(
                        select(Player).where(Player.external_id.in_(player_ids))
                    )
                )
                if player_ids
                else []
            )
            players = {row.external_id: row for row in player_rows}
            country_ids = {row.country_external_id for row in player_rows if row.country_external_id}
            country_rows = (
                list(session.scalars(select(Country).where(Country.external_id.in_(country_ids))))
                if country_ids else []
            )
            countries_by_id = {row.external_id: row for row in country_rows}

            official_rows = list(
                session.scalars(
                    select(GameOfficial).where(
                        GameOfficial.game_external_id == game.external_id
                    )
                )
            )
            referee_ids = {row.referee_external_id for row in official_rows}
            referee_rows = (
                list(
                    session.scalars(
                        select(Referee).where(Referee.external_id.in_(referee_ids))
                    )
                )
                if referee_ids
                else []
            )
            referees = {row.external_id: row for row in referee_rows}

            raw_payload = (
                game.details_raw_payload_en
                if language == "en"
                else game.details_raw_payload_ru
            ) or game.details_raw_payload_ru or game.details_raw_payload_en
            fallback_raw_payload = (
                game.details_raw_payload_ru
                if language == "en"
                else game.details_raw_payload_en
            ) or raw_payload
            raw_details = self._details_json_object(raw_payload)
            fallback_raw_details = self._details_json_object(fallback_raw_payload)
            raw_game = raw_details.get("game") if isinstance(raw_details.get("game"), dict) else {}
            raw_teams = raw_details.get("teams") if isinstance(raw_details.get("teams"), dict) else {}
            raw_periods = (
                raw_details.get("teams_periods")
                if isinstance(raw_details.get("teams_periods"), dict)
                else {}
            )
            raw_players = (
                raw_details.get("players")
                if isinstance(raw_details.get("players"), dict)
                else {}
            )
            details_synced_at = game.details_synced_at
            details_source_endpoint = game.details_source_endpoint

        serialized_players: dict[str, list[dict[str, Any]]] = {
            "home": [],
            "away": [],
        }
        for roster in roster_rows:
            player = players.get(roster.player_external_id)
            if player is None:
                continue
            full_name_ru = player.full_name_ru or " ".join(
                part for part in (player.last_name_ru, player.first_name_ru) if part
            )
            full_name_en = player.full_name_en or " ".join(
                part for part in (player.last_name_en, player.first_name_en) if part
            )
            name = choose_text(
                full_name_ru,
                full_name_en,
                language=language,
                fallback_language=fallback_language,
            )
            position = choose_text(
                roster.position_ru or player.position_ru,
                roster.position_en or player.position_en,
                language=language,
                fallback_language=fallback_language,
            )
            source_side = "A" if roster.side == "home" else "B"
            side_rows = (
                raw_players.get(source_side)
                if isinstance(raw_players.get(source_side), dict)
                else {}
            )
            raw_player = side_rows.get(str(roster.number))
            if not isinstance(raw_player, dict):
                raw_player = next(
                    (
                        value
                        for value in side_rows.values()
                        if isinstance(value, dict)
                        and str(value.get("id") or "") == str(player.external_id)
                    ),
                    {},
                )
            player_statistics = self._normalise_player_statistics(
                raw_player,
                role=roster.role,
            )
            season_statistics = self._normalise_season_player_statistics(
                self._details_json_object(player.raw_payload),
                role=roster.role,
            )
            country = countries_by_id.get(player.country_external_id)
            country_code = player.country_code or (country.iso2 if country else "") or (country.iso3 if country else "")
            country_name = choose_text(
                country.name_ru if country else "",
                country.name_en if country else "",
                language=language,
                fallback_language=fallback_language,
            ) if country else ""
            serialized_players.setdefault(roster.side, []).append(
                {
                    "id": player.external_id,
                    "external_id": player.external_id,
                    "db_id": player.id,
                    "side": roster.side,
                    "team_id": roster.team_external_id,
                    "number": roster.number,
                    "name": name,
                    "names": bilingual(full_name_ru, full_name_en),
                    "first_name": choose_text(
                        player.first_name_ru,
                        player.first_name_en,
                        language=language,
                        fallback_language=fallback_language,
                    ),
                    "last_name": choose_text(
                        player.last_name_ru,
                        player.last_name_en,
                        language=language,
                        fallback_language=fallback_language,
                    ),
                    "position": position,
                    "positions": bilingual(
                        roster.position_ru or player.position_ru,
                        roster.position_en or player.position_en,
                    ),
                    "role": roster.role,
                    "line": roster.line_number,
                    "line_sort_order": int(roster.line_sort_order or 0),
                    "line_manual": bool(roster.line_manual),
                    "api_line": roster.api_line_number,
                    "api_line_sort_order": int(roster.api_line_sort_order or 0),
                    "captain_role": roster.captain_role,
                    "lineup_status": roster.lineup_status,
                    "birth_date": (
                        player.birth_date.isoformat() if player.birth_date else None
                    ),
                    "age": self._age_on_date(player.birth_date),
                    "height_cm": player.height_cm,
                    "weight_kg": player.weight_kg,
                    "stick": player.stick,
                    "country_id": player.country_external_id,
                    "country_code": country_code,
                    "country_name": country_name,
                    "country_flag": (country.flag_emoji if country else "") or self._country_flag(country_code),
                    "photo_url": player.photo_url,
                    "statistics": player_statistics,
                    "season_statistics": season_statistics,
                }
            )

        for items in serialized_players.values():
            has_lines = any(str(item.get("line") or "").strip() for item in items)
            if has_lines:
                def line_sort_value(item: dict[str, Any]) -> tuple[Any, ...]:
                    raw_line = str(item.get("line") or "").strip()
                    try:
                        line_number = int(raw_line)
                    except (TypeError, ValueError):
                        line_number = 10_000 if not raw_line else 9_000
                    order = int(item.get("line_sort_order") or 0)
                    return (
                        line_number,
                        order if order > 0 else 10_000,
                        self._number_sort_value(str(item.get("number") or "")),
                        str(item.get("name") or ""),
                    )
                items.sort(key=line_sort_value)
            else:
                items.sort(
                    key=lambda item: (
                        self._number_sort_value(str(item["number"])),
                        str(item["name"]),
                    )
                )

        serialized_officials: list[dict[str, Any]] = []
        role_labels = {
            "head": ("Главный судья", "Referee"),
            "linesman": ("Линейный судья", "Linesman"),
        }
        for assignment in sorted(
            official_rows,
            key=lambda row: (
                0 if row.role == "head" else 1,
                row.slot,
            ),
        ):
            referee = referees.get(assignment.referee_external_id)
            if referee is None:
                continue
            full_name_ru = referee.full_name_ru or " ".join(
                part for part in (referee.last_name_ru, referee.first_name_ru) if part
            )
            full_name_en = referee.full_name_en or " ".join(
                part for part in (referee.last_name_en, referee.first_name_en) if part
            )
            label_ru, label_en = role_labels.get(
                assignment.role, (assignment.role, assignment.role)
            )
            serialized_officials.append(
                {
                    "id": referee.external_id,
                    "external_id": referee.external_id,
                    "name": choose_text(
                        full_name_ru,
                        full_name_en,
                        language=language,
                        fallback_language=fallback_language,
                    ),
                    "names": bilingual(full_name_ru, full_name_en),
                    "role": assignment.role,
                    "role_label": choose_text(
                        label_ru,
                        label_en,
                        language=language,
                        fallback_language=fallback_language,
                    ),
                    "slot": assignment.slot,
                    "number": assignment.number or referee.number,
                    "country_code": referee.country_code,
                    "country_flag": self._country_flag(referee.country_code),
                }
            )

        game_payload["home"]["players"] = serialized_players.get("home", [])
        game_payload["away"]["players"] = serialized_players.get("away", [])
        game_payload["home"]["coach"] = str(
            (raw_teams.get("A") or {}).get("coach_fullname") or ""
        ) if isinstance(raw_teams.get("A"), dict) else ""
        game_payload["away"]["coach"] = str(
            (raw_teams.get("B") or {}).get("coach_fullname") or ""
        ) if isinstance(raw_teams.get("B"), dict) else ""
        game_payload["referees"] = serialized_officials
        team_statistics = self._team_statistics_payload(raw_teams, raw_periods)
        if game.shots_resource_available:
            team_statistics.setdefault("total", {}).setdefault("home", {})["shot_attempts"] = game.shot_attempts_home
            team_statistics.setdefault("total", {}).setdefault("away", {})["shot_attempts"] = game.shot_attempts_away
            team_statistics["shots_resource"] = {
                "available": True,
                "endpoint": game.shots_source_endpoint,
                "synced_at": game.shots_synced_at.isoformat() if game.shots_synced_at else None,
            }
            team_statistics["available"] = True
        else:
            team_statistics["shots_resource"] = {
                "available": False,
                "endpoint": game.shots_source_endpoint,
                "synced_at": game.shots_synced_at.isoformat() if game.shots_synced_at else None,
            }
        team_statistics["coaches"] = {
            "home": game_payload["home"].get("coach") or "",
            "away": game_payload["away"].get("coach") or "",
        }
        game_payload["team_statistics"] = team_statistics
        player_statistics: dict[str, Any] = {
            "available": any(
                serialized_players.get(side)
                for side in ("home", "away")
            )
        }
        for side in ("home", "away"):
            player_statistics[side] = {
                "skaters": [
                    player
                    for player in serialized_players.get(side, [])
                    if player.get("role") != "goalkeeper"
                ],
                "goalkeepers": [
                    player
                    for player in serialized_players.get(side, [])
                    if player.get("role") == "goalkeeper"
                ],
            }
        game_payload["player_statistics"] = player_statistics
        season_player_statistics: dict[str, Any] = {"available": False}
        for side in ("home", "away"):
            season_player_statistics[side] = {
                "skaters": [
                    player
                    for player in serialized_players.get(side, [])
                    if player.get("role") != "goalkeeper"
                ],
                "goalkeepers": [
                    player
                    for player in serialized_players.get(side, [])
                    if player.get("role") == "goalkeeper"
                ],
            }
            if any(
                player.get("season_statistics", {}).get("available")
                for player in serialized_players.get(side, [])
            ):
                season_player_statistics["available"] = True
        game_payload["season_player_statistics"] = season_player_statistics

        home_ids = (
            game_payload.get("home", {}).get("id"),
            game_payload.get("home", {}).get("entry_id"),
        )
        away_ids = (
            game_payload.get("away", {}).get("id"),
            game_payload.get("away", {}).get("entry_id"),
        )
        home_names = (
            game_payload.get("home", {}).get("name"),
            game_payload.get("home", {}).get("short_name"),
            *(game_payload.get("home", {}).get("names", {}) or {}).values(),
            *(game_payload.get("home", {}).get("short_names", {}) or {}).values(),
        )
        away_names = (
            game_payload.get("away", {}).get("name"),
            game_payload.get("away", {}).get("short_name"),
            *(game_payload.get("away", {}).get("names", {}) or {}).values(),
            *(game_payload.get("away", {}).get("short_names", {}) or {}).values(),
        )

        events_payload = parse_match_events(
            raw_details,
            language=language,
            home_ids=home_ids,
            away_ids=away_ids,
            home_names=home_names,
            away_names=away_names,
        )
        if not events_payload.get("available") and fallback_raw_details != raw_details:
            events_payload = parse_match_events(
                fallback_raw_details,
                language=language,
                home_ids=home_ids,
                away_ids=away_ids,
                home_names=home_names,
                away_names=away_names,
            )

        shots_map_payload = parse_shots_map(
            raw_details,
            language=language,
            home_ids=home_ids,
            away_ids=away_ids,
            home_names=home_names,
            away_names=away_names,
        )
        if not shots_map_payload.get("available") and fallback_raw_details != raw_details:
            shots_map_payload = parse_shots_map(
                fallback_raw_details,
                language=language,
                home_ids=home_ids,
                away_ids=away_ids,
                home_names=home_names,
                away_names=away_names,
            )

        def identity_variants(value: Any) -> set[str]:
            tokens = [
                "".join(character for character in part.casefold() if character.isalnum())
                for part in str(value or "").split()
            ]
            tokens = [token for token in tokens if token]
            if not tokens:
                return set()
            return {"".join(tokens), "".join(reversed(tokens))}

        players_by_id: dict[str, dict[str, Any]] = {}
        players_by_name: dict[str, dict[str, Any]] = {}
        players_by_side_number: dict[tuple[str, str], dict[str, Any]] = {}
        for side in ("home", "away"):
            for player in serialized_players.get(side, []):
                enriched = {**player, "side": side}
                player_id = str(player.get("id") or player.get("external_id") or "").strip()
                if player_id:
                    players_by_id[player_id] = enriched
                number = str(player.get("number") or "").strip()
                if number:
                    players_by_side_number[(side, number)] = enriched
                for variant in identity_variants(player.get("name")):
                    players_by_name.setdefault(variant, enriched)

        def enrich_visual_items(payload: dict[str, Any]) -> None:
            for item in payload.get("items", []):
                player = players_by_id.get(str(item.get("player_id") or "").strip())
                if player is None:
                    for variant in identity_variants(item.get("player_name")):
                        player = players_by_name.get(variant)
                        if player is not None:
                            break
                if player is None:
                    side_key = str(item.get("side") or "").strip()
                    number_key = str(item.get("player_number") or "").strip()
                    if side_key and number_key:
                        player = players_by_side_number.get((side_key, number_key))
                if player is not None:
                    item["player_id"] = str(player.get("id") or player.get("external_id") or item.get("player_id") or "")
                    item["player_name"] = str(player.get("name") or item.get("player_name") or "")
                    item["player_number"] = str(player.get("number") or item.get("player_number") or "")
                    item["side"] = str(player.get("side") or item.get("side") or "")
                    item["team_id"] = str(player.get("team_id") or item.get("team_id") or "")
                side = str(item.get("side") or "")
                if side in {"home", "away"}:
                    team = game_payload.get(side, {})
                    item["team_id"] = str(item.get("team_id") or team.get("id") or team.get("entry_id") or "")
                    raw_team_name = str(item.get("team_name") or "").strip()
                    if raw_team_name.casefold() in {"a", "b", "home", "away"}:
                        raw_team_name = ""
                    item["team_name"] = str(raw_team_name or team.get("name") or "")
                    description = str(item.get("description") or "")
                    for prefix in ("A · ", "B · ", "A - ", "B - "):
                        if description.startswith(prefix):
                            item["description"] = f"{item['team_name']} · {description[len(prefix):]}".strip(" ·")
                            break

        enrich_visual_items(events_payload)
        enrich_visual_items(shots_map_payload)
        if shots_map_payload.get("available"):
            player_rows: dict[str, dict[str, Any]] = {}
            for item in shots_map_payload.get("items", []):
                key = str(item.get("player_id") or "").strip()
                if not key:
                    variants = identity_variants(item.get("player_name"))
                    key = next(iter(variants), "")
                if not key and item.get("side") and item.get("player_number"):
                    key = f"{item.get('side')}:{item.get('player_number')}"
                if not key:
                    continue
                player_rows.setdefault(key, {
                    "id": item.get("player_id") or "",
                    "name": item.get("player_name") or "",
                    "number": item.get("player_number") or "",
                    "side": item.get("side") or "",
                    "team_id": item.get("team_id") or "",
                    "team_name": item.get("team_name") or "",
                })
            shots_map_payload["players"] = sorted(
                player_rows.values(),
                key=lambda item: (
                    0 if item.get("side") == "home" else 1,
                    str(item.get("name") or "").casefold(),
                    str(item.get("number") or ""),
                ),
            )

        game_payload["events"] = events_payload
        game_payload["shots_map"] = shots_map_payload
        game_payload["details_loaded"] = bool(details_synced_at)
        game_payload["details_synced_at"] = (
            details_synced_at.isoformat() if details_synced_at else None
        )

        details_payload = {
            "loaded": bool(details_synced_at),
            "source_endpoint": details_source_endpoint,
            "synced_at": details_synced_at.isoformat() if details_synced_at else None,
            "status": str(raw_game.get("status") or ""),
            "status_value": raw_game.get("statusvalue"),
            "place": str(raw_game.get("place") or game_payload.get("arena") or ""),
            "home_coach": game_payload["home"]["coach"],
            "away_coach": game_payload["away"]["coach"],
            "home_players": len(game_payload["home"]["players"]),
            "away_players": len(game_payload["away"]["players"]),
            "referees": len(serialized_officials),
            "statistics_periods": max(0, len(team_statistics["segments"]) - 1),
            "player_statistics": bool(player_statistics["available"]),
            "season_player_statistics": bool(season_player_statistics["available"]),
            "events": bool(events_payload.get("available")),
            "shots_map": bool(shots_map_payload.get("available")),
        }
        return {
            "game": game_payload,
            "referees": serialized_officials,
            "team_statistics": team_statistics,
            "details": details_payload,
        }

    @staticmethod
    def _normalise_control_period(value: Any) -> str:
        raw = str(value or "").strip().casefold()
        aliases = {
            "1": "1", "p1": "1", "period1": "1", "п1": "1",
            "2": "2", "p2": "2", "period2": "2", "п2": "2",
            "3": "3", "p3": "3", "period3": "3", "п3": "3",
            "4": "ot", "ot": "ot", "overtime": "ot", "овертайм": "ot", "от": "ot",
            "5": "so", "so": "so", "shootout": "so", "буллиты": "so", "б": "so",
            "finished": "finished", "finish": "finished", "final": "finished",
            "завершен": "finished", "завершён": "finished",
        }
        result = aliases.get(raw, raw)
        overtime_match = re.fullmatch(
            r"(?:ot|overtime|овертайм|от)[\s:_-]*([1-9][0-9]{0,2})",
            result,
            flags=re.IGNORECASE,
        )
        if overtime_match:
            return f"ot{int(overtime_match.group(1))}"
        if result not in {"1", "2", "3", "ot", "so", "finished"}:
            raise ValueError("Неизвестный период матча")
        return result

    @staticmethod
    def _overtime_number(value: str) -> int:
        match = re.fullmatch(r"ot([1-9][0-9]{0,2})", str(value or "").strip().lower())
        return int(match.group(1)) if match else 0

    @classmethod
    def _period_status_payload(
        cls,
        settings: dict[str, Any],
        *,
        current_period: str,
        language: str = "ru",
    ) -> dict[str, Any]:
        current = cls._normalise_control_period(current_period)
        overtime_number = cls._overtime_number(current)
        lookup_key = "ot_numbered" if overtime_number else current
        if lookup_key not in {"1", "2", "3", "ot", "ot_numbered", "so", "finished"}:
            lookup_key = "1"
        source_all = settings.get("period_status_labels")
        source_all = source_all if isinstance(source_all, dict) else {}
        source = source_all.get(lookup_key)
        source = source if isinstance(source, dict) else {}

        defaults = DEFAULT_PERIOD_STATUS_LABELS[lookup_key]

        def value_for(fmt: str, lang: str) -> str:
            fmt_source = source.get(fmt)
            fmt_source = fmt_source if isinstance(fmt_source, dict) else {}
            raw = str(fmt_source.get(lang, defaults[fmt][lang]) or "")
            number = overtime_number or 1
            return raw.replace("{n}", str(number)).replace("{period}", current)

        values = {
            fmt: {lang: value_for(fmt, lang) for lang in ("ru", "en")}
            for fmt in ("compact", "short", "long")
        }
        active_language = "en" if language == "en" else "ru"
        return {
            "key": current,
            "lookup_key": lookup_key,
            "number": overtime_number if overtime_number else (int(current) if current in {"1", "2", "3"} else 0),
            "compact": values["compact"][active_language],
            "short": values["short"][active_language],
            "long": values["long"][active_language],
            "label": values["short"][active_language],
            "ru": values["short"]["ru"],
            "en": values["short"]["en"],
            "formats": values,
        }

    @classmethod
    def _control_period_options(
        cls,
        language: str,
        *,
        stage: str,
        current_period: str,
    ) -> list[dict[str, str]]:
        base = [
            {"value": "1", "label": "1st period" if language == "en" else "1 период"},
            {"value": "2", "label": "2nd period" if language == "en" else "2 период"},
            {"value": "3", "label": "3rd period" if language == "en" else "3 период"},
        ]
        if stage == "regular":
            base.extend([
                {"value": "ot", "label": "Overtime" if language == "en" else "Овертайм"},
                {"value": "so", "label": "Shootout" if language == "en" else "Буллиты"},
            ])
        elif stage == "playoff":
            # Playoff overtime is unlimited.  Always expose the next overtime;
            # selecting it makes one more option appear on the following refresh.
            current_number = cls._overtime_number(current_period)
            visible_count = max(2, current_number + 1)
            for number in range(1, visible_count + 1):
                base.append({
                    "value": f"ot{number}",
                    "label": (
                        f"Overtime {number}"
                        if language == "en"
                        else f"{number} овертайм"
                    ),
                })
        else:
            base.append({
                "value": "ot",
                "label": "Overtime" if language == "en" else "Овертайм",
            })
        base.append({
            "value": "finished",
            "label": "Finished" if language == "en" else "Матч завершён",
        })
        return base

    @staticmethod
    def _get_or_create_game_control(
        session: Session,
        game_external_id: str,
    ) -> GameControlState:
        row = session.scalar(
            select(GameControlState).where(
                GameControlState.game_external_id == str(game_external_id)
            )
        )
        if row is None:
            row = GameControlState(game_external_id=str(game_external_id))
            session.add(row)
            session.flush()
        return row

    @staticmethod
    def _shootout_attempt_payload(row: ShootoutAttempt) -> dict[str, Any]:
        return {
            "id": row.id,
            "sequence_number": row.sequence_number,
            "team_attempt_number": row.team_attempt_number,
            "series_number": row.series_number,
            "side": row.side,
            "team_id": row.team_external_id,
            "player_id": row.player_external_id,
            "player_number": row.player_number,
            "player_name": row.player_name,
            "scored": bool(row.scored),
            "created_at": row.created_at.isoformat(),
        }

    @staticmethod
    def _period_phase(stage: str, current_period: str) -> str:
        value = str(current_period or "1").strip().lower()
        if value in {"1", "2", "3"}:
            return "regulation"
        if value == "so":
            return "shootout"
        if value == "finished":
            return "finished"
        if value == "ot" or value.startswith("ot"):
            return "playoff_overtime" if stage == "playoff" else "regular_overtime"
        return "regulation"

    @classmethod
    def _timer_rule_payload(
        cls,
        settings: dict[str, Any],
        *,
        stage: str,
        current_period: str,
        language: str = "ru",
    ) -> dict[str, Any]:
        phase = cls._period_phase(stage, current_period)
        if phase == "regular_overtime":
            minutes = int(settings.get("timer_regular_overtime_minutes", 5) or 5)
        elif phase == "playoff_overtime":
            minutes = int(settings.get("timer_playoff_overtime_minutes", 20) or 20)
        elif phase in {"shootout", "finished"}:
            minutes = 0
        else:
            minutes = int(settings.get("timer_period_minutes", 20) or 20)
        phase_labels = {
            "regulation": ("Основное время", "Regulation"),
            "regular_overtime": ("Овертайм регулярного чемпионата", "Regular-season overtime"),
            "playoff_overtime": ("Овертайм плей-офф", "Playoff overtime"),
            "shootout": ("Буллиты", "Shootout"),
            "finished": ("Матч завершён", "Finished"),
        }
        ru, en = phase_labels.get(phase, phase_labels["regulation"])
        return {
            "phase": phase,
            "phase_label": en if language == "en" else ru,
            "minutes": max(0, minutes),
            "duration_ms": max(0, minutes) * 60 * 1000,
            "reset_on_period_change": bool(settings.get("timer_reset_on_period_change", True)),
        }

    @staticmethod
    def _active_penalty_counts(timer_state: dict[str, Any]) -> tuple[int, int]:
        board = timer_state.get("penalty_board") if isinstance(timer_state, dict) else {}
        items = board.get("penalties") if isinstance(board, dict) else []
        home = 0
        away = 0
        for item in items if isinstance(items, list) else []:
            if not isinstance(item, dict) or bool(item.get("finished", False)):
                continue

            # An assigned penalty affects the numerical strength immediately,
            # even while its clock is paused and before the operator presses
            # Start.  Incomplete dashboard drafts must not affect the scorebar.
            player = item.get("player") if isinstance(item.get("player"), dict) else {}
            side = str(item.get("side") or player.get("side") or "").lower()
            infraction = item.get("infraction")
            preset = str(item.get("preset") or "").strip()
            try:
                duration_ms = int(round(float(item.get("durationMs", item.get("duration_ms", 0)) or 0)))
                remaining_ms = int(round(float(item.get("remainingMs", item.get("remaining_ms", duration_ms)) or 0)))
            except (TypeError, ValueError):
                duration_ms = remaining_ms = 0
            assigned = (
                side in {"home", "away"}
                and bool(infraction)
                and bool(preset)
                and duration_ms > 0
                and remaining_ms > 0
            )
            if not assigned:
                continue

            # BUILD96: a prepared penalty must not change numerical strength until
            # the operator explicitly starts it. New Runtime snapshots always send
            # startedOnce. Legacy snapshots (without the marker) keep the old
            # assigned-immediately behaviour for backward compatibility.
            has_started_marker = "startedOnce" in item or "started_once" in item
            if has_started_marker:
                started = bool(item.get("startedOnce", item.get("started_once", False))) or bool(item.get("running", False))
            else:
                started = bool(item.get("running", False)) or remaining_ms < duration_ms or assigned
            if not started:
                continue
            if side == "home":
                home += 1
            else:
                away += 1
        return home, away

    @classmethod
    def _strength_payload(
        cls,
        settings: dict[str, Any],
        *,
        stage: str,
        current_period: str,
        timer_state: dict[str, Any],
        language: str = "ru",
    ) -> dict[str, Any]:
        phase = cls._period_phase(stage, current_period)
        if phase == "regular_overtime":
            base = int(settings.get("strength_regular_overtime_skaters", 3) or 3)
            penalty_mode = "add_opponent"
        elif phase == "playoff_overtime":
            base = int(settings.get("strength_playoff_overtime_skaters", 5) or 5)
            penalty_mode = "subtract"
        else:
            base = int(settings.get("strength_regulation_skaters", 5) or 5)
            penalty_mode = "subtract"
        base = max(3, min(6, base))
        minimum = max(2, min(base, int(settings.get("strength_min_skaters", 3) or 3)))
        home_penalties, away_penalties = cls._active_penalty_counts(timer_state)

        if phase in {"shootout", "finished"}:
            home_skaters = away_skaters = 0
        elif penalty_mode == "add_opponent":
            # In 3-on-3 overtime every active penalty adds one skater to the
            # opponent. Coincidental penalties therefore produce 4-on-4, two
            # penalties on each side produce 5-on-5, and two penalties on one
            # side produce 5-on-3. Do not cancel the counts against each other.
            home_skaters = min(5, base + away_penalties)
            away_skaters = min(5, base + home_penalties)
        else:
            home_skaters = max(minimum, base - home_penalties)
            away_skaters = max(minimum, base - away_penalties)

        advantage_side = (
            "home" if home_skaters > away_skaters
            else "away" if away_skaters > home_skaters
            else ""
        )
        strength_label = (
            f"{home_skaters}×{away_skaters}"
            if home_skaters and away_skaters else ""
        )
        state_key = (
            f"{max(home_skaters, away_skaters)}x{min(home_skaters, away_skaters)}"
            if home_skaters and away_skaters else ""
        )

        label_phase = phase if phase in {"regulation", "regular_overtime", "playoff_overtime"} else "regulation"
        label_matrix = settings.get("strength_state_labels")
        label_matrix = label_matrix if isinstance(label_matrix, dict) else {}
        phase_labels_map = label_matrix.get(label_phase)
        phase_labels_map = phase_labels_map if isinstance(phase_labels_map, dict) else {}
        configured_state = phase_labels_map.get(state_key)
        state_labels = configured_state if isinstance(configured_state, dict) else {}
        language_key = "en" if language == "en" else "ru"

        # BUILD94: the captions configured in "Таймеры и численные составы" are
        # the single source of truth for strength text everywhere: Runtime,
        # Mapping and vMix. Do not synthesize a second caption from state_key and
        # do not silently substitute the legacy PP/PK strings. When a raw/legacy
        # settings dictionary is missing a matrix cell, use the same default
        # matrix that the settings UI/store uses. An explicitly present empty
        # value stays empty.
        if language_key in state_labels:
            state_label = str(state_labels.get(language_key) or "").strip()
        else:
            default_phase = DEFAULT_STRENGTH_STATE_LABELS.get(label_phase, {})
            default_state = default_phase.get(state_key, {}) if isinstance(default_phase, dict) else {}
            state_label = str(default_state.get(language_key) or "").strip()

        # HOME/AWAY are separate Mapping destinations, but their text must come
        # from the same configured state caption. If penalties are active, both
        # side-label identifiers expose that configured caption; the one-plate
        # television routing still decides which side is actually shown on air.
        penalty_state_active = bool(state_key and (home_penalties > 0 or away_penalties > 0))
        home_label = state_label if penalty_state_active else ""
        away_label = state_label if penalty_state_active else ""
        phase_labels = {
            "regulation": ("Основное время", "Regulation"),
            "regular_overtime": ("Овертайм регулярки", "Regular OT"),
            "playoff_overtime": ("Овертайм плей-офф", "Playoff OT"),
            "shootout": ("Буллиты", "Shootout"),
            "finished": ("Матч завершён", "Finished"),
        }
        ru, en = phase_labels.get(phase, phase_labels["regulation"])
        return {
            "phase": phase,
            "phase_label": en if language == "en" else ru,
            "base_skaters": base,
            "minimum_skaters": minimum,
            "penalty_mode": penalty_mode,
            "home_penalties": home_penalties,
            "away_penalties": away_penalties,
            "home_skaters": home_skaters,
            "away_skaters": away_skaters,
            "strength_label": strength_label,
            "state_key": state_key,
            "state_label": state_label,
            "advantage_numbers": state_key.replace("x", "×") if advantage_side else "",
            "advantage_side": advantage_side,
            "home_label": home_label,
            "away_label": away_label,
        }

    @staticmethod
    def _default_game_timer_state(default_ms: int = 20 * 60 * 1000) -> dict[str, Any]:
        default_ms = max(0, int(default_ms or 0))
        return {
            "saved": False,
            "main_timer": {
                "current_ms": default_ms,
                "running": False,
                "paused": True,
                "finished": False,
                "reached": {},
            },
            "penalty_board": {
                "penalties": [],
                "history": [],
                "advantage_cycle": {
                    "had_advantage": False,
                    "last_advantage_side": "",
                },
            },
        }

    @classmethod
    def _normalise_game_timer_state(
        cls,
        value: Any,
        *,
        default_ms: int = 20 * 60 * 1000,
    ) -> dict[str, Any]:
        default = cls._default_game_timer_state(default_ms)
        if not isinstance(value, dict):
            return default

        source_main = value.get("main_timer")
        source_main = source_main if isinstance(source_main, dict) else {}
        try:
            current_ms = int(round(float(source_main.get("current_ms", default_ms))))
        except (TypeError, ValueError):
            current_ms = int(default_ms)
        current_ms = max(-12 * 60 * 60 * 1000, min(12 * 60 * 60 * 1000, current_ms))
        finished = bool(source_main.get("finished", False))
        running = bool(source_main.get("running", False)) and not finished
        reached = source_main.get("reached")
        if not isinstance(reached, dict):
            reached = {}
        reached = {
            str(key)[:64]: bool(item)
            for key, item in list(reached.items())[:64]
        }

        source_board = value.get("penalty_board")
        source_board = source_board if isinstance(source_board, dict) else {}
        penalties = source_board.get("penalties")
        history = source_board.get("history")
        penalties = [item for item in penalties if isinstance(item, dict)] if isinstance(penalties, list) else []
        history = [item for item in history if isinstance(item, dict)] if isinstance(history, list) else []
        source_cycle = source_board.get("advantage_cycle")
        source_cycle = source_cycle if isinstance(source_cycle, dict) else {}
        cycle_side = str(source_cycle.get("last_advantage_side") or "").lower()
        if cycle_side not in {"home", "away"}:
            cycle_side = ""
        advantage_cycle = {
            "had_advantage": bool(source_cycle.get("had_advantage", False)) and bool(cycle_side),
            "last_advantage_side": cycle_side,
        }

        normalised = {
            "saved": True,
            "main_timer": {
                "current_ms": current_ms,
                "running": running,
                "paused": bool(source_main.get("paused", not running)),
                "finished": finished,
                "reached": reached,
            },
            "penalty_board": {
                "penalties": penalties[:64],
                "history": history[:64],
                "advantage_cycle": advantage_cycle,
            },
        }
        encoded = json.dumps(normalised, ensure_ascii=False, separators=(",", ":"))
        if len(encoded.encode("utf-8")) > 1_500_000:
            raise ValueError("Состояние таймеров слишком большое")
        return normalised

    @staticmethod
    def _runtime_flags_from_row(row: GameControlState) -> dict[str, bool]:
        raw = str(getattr(row, "runtime_flags_json", "") or "").strip()
        if not raw:
            return {}
        try:
            payload = json.loads(raw)
        except (TypeError, ValueError, json.JSONDecodeError):
            return {}
        if not isinstance(payload, dict):
            return {}
        result: dict[str, bool] = {}
        for key, value in list(payload.items())[:128]:
            clean_key = re.sub(r"[^A-Za-z0-9_.:-]+", "_", str(key or "").strip())[:96]
            if clean_key:
                result[clean_key] = bool(value)
        return result

    @staticmethod
    def _runtime_values_from_row(row: GameControlState) -> dict[str, str]:
        raw = str(getattr(row, "runtime_values_json", "") or "").strip()
        if not raw:
            return {}
        try:
            payload = json.loads(raw)
        except (TypeError, ValueError, json.JSONDecodeError):
            return {}
        if not isinstance(payload, dict):
            return {}
        result: dict[str, str] = {}
        for key, value in list(payload.items())[:256]:
            clean_key = re.sub(r"[^A-Za-z0-9_.:-]+", "_", str(key or "").strip())[:128]
            if clean_key:
                result[clean_key] = str(value if value is not None else "")[:512]
        return result

    @staticmethod
    def _score_control_payload(game: Game, row: GameControlState | None) -> dict[str, Any]:
        """Return the authoritative operator score for one game.

        While manual mode is off, LIVE follows the current DB/API score.  The first
        operator +/- action freezes LIVE into match-scoped runtime values so later
        Stat2TV updates cannot overwrite an on-air correction.
        """
        game_home = max(0, int(getattr(game, "home_score", 0) or 0))
        game_away = max(0, int(getattr(game, "away_score", 0) or 0))
        values = HockeyDataService._runtime_values_from_row(row) if row is not None else {}
        external_valid = str(values.get("score.external_valid", "0") or "0").strip().lower() in {"1", "true", "yes", "on"}
        manual = str(values.get("score.manual", "0") or "0").strip().lower() in {"1", "true", "yes", "on"}

        def _stored(key: str, fallback: int) -> int:
            try:
                return max(0, int(float(str(values.get(key, fallback) or fallback).strip())))
            except (TypeError, ValueError):
                return fallback

        external_home = _stored("score.external_home", game_home) if external_valid else game_home
        external_away = _stored("score.external_away", game_away) if external_valid else game_away
        live_home = _stored("score.home", external_home) if manual else external_home
        live_away = _stored("score.away", external_away) if manual else external_away
        return {
            "home": live_home,
            "away": live_away,
            "external_home": external_home,
            "external_away": external_away,
            "external_source": str(values.get("score.external_source", "game") or "game"),
            "manual": manual,
            "source": "manual" if manual else "external",
            "differs": live_home != external_home or live_away != external_away,
        }

    @classmethod
    def _timer_state_from_row(
        cls,
        row: GameControlState,
        *,
        default_ms: int = 20 * 60 * 1000,
    ) -> dict[str, Any]:
        raw = str(row.timer_state_json or "").strip()
        if not raw:
            return cls._default_game_timer_state(default_ms)
        try:
            payload = json.loads(raw)
        except (TypeError, ValueError, json.JSONDecodeError):
            return cls._default_game_timer_state(default_ms)
        result = cls._normalise_game_timer_state(payload, default_ms=default_ms)
        result["saved"] = True
        result["updated_at"] = (
            row.timer_state_updated_at.isoformat()
            if row.timer_state_updated_at else ""
        )
        return result

    def game_control(
        self,
        game_external_id: str,
        *,
        language: str = "ru",
    ) -> dict[str, Any] | None:
        settings = self.settings.public()
        language = normalise_language(language, settings["ui_language"])
        with self.database.session() as session:
            game = session.scalar(
                select(Game).where(Game.external_id == str(game_external_id))
            )
            if game is None:
                return None
            tournament = session.scalar(
                select(Tournament).where(
                    Tournament.external_id == game.tournament_external_id
                )
            )
            stage = (
                stage_key(tournament.season_part, tournament.round_code)
                if tournament else "other"
            )
            regular_season = stage == "regular"
            control = self._get_or_create_game_control(session, game.external_id)
            attempts = list(
                session.scalars(
                    select(ShootoutAttempt)
                    .where(ShootoutAttempt.game_external_id == game.external_id)
                    .order_by(ShootoutAttempt.sequence_number, ShootoutAttempt.id)
                )
            )

            home_attempts = [item for item in attempts if item.side == "home"]
            away_attempts = [item for item in attempts if item.side == "away"]
            home_goals = sum(1 for item in home_attempts if item.scored)
            away_goals = sum(1 for item in away_attempts if item.scored)
            initial_attempts = 5 if int(control.shootout_initial_attempts or 3) == 5 else 3
            extra_rounds = max(0, int(control.shootout_extra_rounds or 0))
            allowed_per_side = initial_attempts + extra_rounds
            both_complete = (
                len(home_attempts) >= allowed_per_side
                and len(away_attempts) >= allowed_per_side
            )
            tied = home_goals == away_goals
            next_side = "home" if len(home_attempts) <= len(away_attempts) else "away"
            current_period = self._normalise_control_period(control.current_period or "1")
            if stage == "playoff":
                if current_period == "ot":
                    current_period = "ot1"
                elif current_period == "so":
                    current_period = "ot1"
            elif stage != "regular" and current_period == "so":
                current_period = "ot"
            if current_period != control.current_period:
                control.current_period = current_period
                control.updated_at = datetime.utcnow()

            period_status = self._period_status_payload(
                settings,
                current_period=current_period,
                language=language,
            )
            timer_rules = self._timer_rule_payload(
                settings,
                stage=stage,
                current_period=current_period,
                language=language,
            )
            timer_state = self._timer_state_from_row(
                control,
                default_ms=timer_rules["duration_ms"],
            )
            strength = self._strength_payload(
                settings,
                stage=stage,
                current_period=current_period,
                timer_state=timer_state,
                language=language,
            )
            raw_team_states = settings.get("scoreboard_team_states")
            raw_team_states = raw_team_states if isinstance(raw_team_states, dict) else {}
            scoreboard_team_states: dict[str, dict[str, Any]] = {}
            for state_key in (
                "home_delayed_penalty",
                "away_delayed_penalty",
                "home_empty_net",
                "away_empty_net",
            ):
                source = raw_team_states.get(state_key)
                source = source if isinstance(source, dict) else {}
                scoreboard_team_states[state_key] = {
                    "ru": str(source.get("ru") or ""),
                    "en": str(source.get("en") or ""),
                    "label": str(source.get(language) or source.get("ru") or source.get("en") or ""),
                    "input": str(source.get("input") or ""),
                    "input_title": str(source.get("input_title") or ""),
                    "overlay": str(source.get("overlay") or "2"),
                    "enabled": bool(source.get("enabled", True)),
                }

            return {
                "game_id": game.external_id,
                "stage": stage,
                "regular_season": regular_season,
                "playoff": stage == "playoff",
                "current_period": current_period,
                "period_options": self._control_period_options(
                    language,
                    stage=stage,
                    current_period=current_period,
                ),
                "period_status": period_status,
                "updated_at": control.updated_at.isoformat(),
                "timer_rules": timer_rules,
                "strength": strength,
                "scoreboard_team_states": scoreboard_team_states,
                "score": self._score_control_payload(game, control),
                "timers": timer_state,
                "flags": self._runtime_flags_from_row(control),
                "values": self._runtime_values_from_row(control),
                "shootout": {
                    "available": regular_season,
                    "started": bool(control.shootout_started),
                    "initial_attempts": initial_attempts,
                    "extra_rounds": extra_rounds,
                    "allowed_per_side": allowed_per_side,
                    "next_side": next_side,
                    "home": {
                        "attempts": len(home_attempts),
                        "goals": home_goals,
                    },
                    "away": {
                        "attempts": len(away_attempts),
                        "goals": away_goals,
                    },
                    "tied": tied,
                    "both_complete": both_complete,
                    "can_add_round": bool(control.shootout_started and both_complete and tied),
                    "winner_side": (
                        "home" if both_complete and home_goals > away_goals
                        else "away" if both_complete and away_goals > home_goals
                        else ""
                    ),
                    "attempts": [self._shootout_attempt_payload(item) for item in attempts],
                },
            }

    def save_game_timers(
        self,
        game_external_id: str,
        *,
        main_timer: dict[str, Any] | None = None,
        penalty_board: dict[str, Any] | None = None,
        updated_by: str = "",
        language: str = "ru",
    ) -> dict[str, Any]:
        settings = self.settings.public()
        now = datetime.utcnow()
        with self.database.session() as session:
            game = session.scalar(
                select(Game).where(Game.external_id == str(game_external_id))
            )
            if game is None:
                raise ValueError("Матч не найден")
            tournament = session.scalar(
                select(Tournament).where(
                    Tournament.external_id == game.tournament_external_id
                )
            )
            stage = (
                stage_key(tournament.season_part, tournament.round_code)
                if tournament else "other"
            )
            row = self._get_or_create_game_control(session, game.external_id)
            rules = self._timer_rule_payload(
                settings,
                stage=stage,
                current_period=row.current_period or "1",
                language=language,
            )
            timer_state = self._normalise_game_timer_state(
                {
                    "main_timer": main_timer or {},
                    "penalty_board": penalty_board or {},
                },
                default_ms=rules["duration_ms"],
            )
            stored_state = {
                "main_timer": timer_state["main_timer"],
                "penalty_board": timer_state["penalty_board"],
            }
            row.timer_state_json = json.dumps(
                stored_state,
                ensure_ascii=False,
                separators=(",", ":"),
            )
            row.timer_state_updated_at = now
            row.updated_by = str(updated_by or "")[:128]
            row.updated_at = now
        result = self.game_control(game_external_id, language=language)
        if result is None:
            raise ValueError("Матч не найден")
        return result

    def set_game_flags(
        self,
        game_external_id: str,
        *,
        flags: dict[str, bool] | None = None,
        updated_by: str = "",
        language: str = "ru",
    ) -> dict[str, Any]:
        flags = flags if isinstance(flags, dict) else {}
        if len(flags) > 64:
            raise ValueError("За один запрос можно изменить не более 64 флагов")
        with self.database.session() as session:
            game = session.scalar(
                select(Game).where(Game.external_id == str(game_external_id))
            )
            if game is None:
                raise ValueError("Матч не найден")
            row = self._get_or_create_game_control(session, game.external_id)
            current = self._runtime_flags_from_row(row)
            for raw_key, raw_value in flags.items():
                key = re.sub(r"[^A-Za-z0-9_.:-]+", "_", str(raw_key or "").strip())[:96]
                if not key:
                    continue
                current[key] = bool(raw_value)
            row.runtime_flags_json = json.dumps(
                current, ensure_ascii=False, separators=(",", ":")
            )
            row.updated_by = str(updated_by or "")[:128]
            row.updated_at = datetime.utcnow()
        result = self.game_control(game_external_id, language=language)
        if result is None:
            raise ValueError("Матч не найден")
        return result

    def set_game_values(
        self,
        game_external_id: str,
        *,
        values: dict[str, Any] | None = None,
        updated_by: str = "",
        language: str = "ru",
    ) -> dict[str, Any]:
        values = values if isinstance(values, dict) else {}
        if len(values) > 64:
            raise ValueError("За один запрос можно изменить не более 64 значений")
        with self.database.session() as session:
            game = session.scalar(
                select(Game).where(Game.external_id == str(game_external_id))
            )
            if game is None:
                raise ValueError("Матч не найден")
            row = self._get_or_create_game_control(session, game.external_id)
            current = self._runtime_values_from_row(row)
            for raw_key, raw_value in values.items():
                key = re.sub(r"[^A-Za-z0-9_.:-]+", "_", str(raw_key or "").strip())[:128]
                if not key:
                    continue
                current[key] = str(raw_value if raw_value is not None else "")[:512]
            row.runtime_values_json = json.dumps(
                current, ensure_ascii=False, separators=(",", ":")
            )
            row.updated_by = str(updated_by or "")[:128]
            row.updated_at = datetime.utcnow()
        result = self.game_control(game_external_id, language=language)
        if result is None:
            raise ValueError("Матч не найден")
        return result

    def set_game_score(
        self,
        game_external_id: str,
        *,
        command: str,
        updated_by: str = "",
        language: str = "ru",
    ) -> dict[str, Any]:
        command = str(command or "").strip().lower()
        aliases = {
            "home_plus": "home_plus", "home+": "home_plus", "home_inc": "home_plus",
            "home_minus": "home_minus", "home-": "home_minus", "home_dec": "home_minus",
            "away_plus": "away_plus", "away+": "away_plus", "away_inc": "away_plus",
            "away_minus": "away_minus", "away-": "away_minus", "away_dec": "away_minus",
            "sync": "sync", "resync": "sync", "external": "sync",
        }
        command = aliases.get(command, command)
        if command not in {"home_plus", "home_minus", "away_plus", "away_minus", "sync"}:
            raise ValueError("Неизвестная команда счёта")

        with self.database.session() as session:
            game = session.scalar(select(Game).where(Game.external_id == str(game_external_id)))
            if game is None:
                raise ValueError("Матч не найден")
            row = self._get_or_create_game_control(session, game.external_id)
            current = self._runtime_values_from_row(row)
            score = self._score_control_payload(game, row)

            if command == "sync":
                current["score.manual"] = "0"
                current["score.home"] = str(score["external_home"])
                current["score.away"] = str(score["external_away"])
            else:
                home = int(score["home"])
                away = int(score["away"])
                if command == "home_plus":
                    home += 1
                elif command == "home_minus":
                    home = max(0, home - 1)
                elif command == "away_plus":
                    away += 1
                elif command == "away_minus":
                    away = max(0, away - 1)
                current["score.manual"] = "1"
                current["score.home"] = str(home)
                current["score.away"] = str(away)

            row.runtime_values_json = json.dumps(current, ensure_ascii=False, separators=(",", ":"))
            row.updated_by = str(updated_by or "")[:128]
            row.updated_at = datetime.utcnow()

        result = self.game_control(game_external_id, language=language)
        if result is None:
            raise ValueError("Матч не найден")
        return result

    def save_game_lines(
        self,
        game_external_id: str,
        *,
        side: str,
        assignments: list[dict[str, Any]],
        updated_by: str = "",
        language: str = "ru",
    ) -> dict[str, Any]:
        """Persist the operator's effective line order for one team.

        BUILD122 deliberately stores the result on ``hockey_game_rosters`` so
        broadcast SQL can read the same roster table directly.  ``line_manual``
        protects these values from later Stat2TV roster refreshes while the
        parallel api_* fields keep the newest API baseline available for reset.
        """
        side = str(side or "").strip().lower()
        if side not in {"home", "away"}:
            raise ValueError("Неизвестная сторона команды")

        normalised: dict[str, tuple[str, int]] = {}
        for raw in assignments or []:
            player_id = str(raw.get("player_external_id") or raw.get("player_id") or "").strip()
            if not player_id:
                continue
            line = str(raw.get("line_number") or "").strip()
            if line:
                if not line.isdigit() or not (1 <= int(line) <= 99):
                    raise ValueError("Номер звена должен быть от 1 до 99")
                line = str(int(line))
            try:
                sort_order = max(0, int(raw.get("sort_order") or 0))
            except (TypeError, ValueError):
                sort_order = 0
            normalised[player_id] = (line, sort_order)

        with self.database.session() as session:
            game = session.scalar(select(Game).where(Game.external_id == str(game_external_id)))
            if game is None:
                raise ValueError("Матч не найден")
            rosters = list(session.scalars(select(GameRoster).where(and_(
                GameRoster.game_external_id == game.external_id,
                GameRoster.side == side,
            ))))
            roster_by_id = {str(row.player_external_id): row for row in rosters}
            unknown = sorted(set(normalised) - set(roster_by_id))
            if unknown:
                raise ValueError(f"Игрок не найден в составе: {unknown[0]}")
            for player_id, (line, sort_order) in normalised.items():
                roster = roster_by_id[player_id]
                # First manual edit after upgrading from an older build: keep the
                # already stored API line as a reset baseline before overwriting it.
                if not str(roster.api_line_number or "").strip() and str(roster.line_number or "").strip():
                    roster.api_line_number = str(roster.line_number or "").strip()
                    roster.api_line_sort_order = int(roster.line_sort_order or 0)
                roster.line_number = line
                roster.line_sort_order = sort_order
                roster.line_manual = True
                roster.synced_at = datetime.utcnow()

        result = self.game_details(game_external_id, language=language)
        if result is None:
            raise ValueError("Матч не найден")
        return result

    def reset_game_lines(
        self,
        game_external_id: str,
        *,
        side: str,
        updated_by: str = "",
        language: str = "ru",
    ) -> dict[str, Any]:
        """Return one team's effective line layout to the latest API baseline."""
        side = str(side or "").strip().lower()
        if side not in {"home", "away"}:
            raise ValueError("Неизвестная сторона команды")
        with self.database.session() as session:
            game = session.scalar(select(Game).where(Game.external_id == str(game_external_id)))
            if game is None:
                raise ValueError("Матч не найден")
            rosters = list(session.scalars(select(GameRoster).where(and_(
                GameRoster.game_external_id == game.external_id,
                GameRoster.side == side,
            ))))
            for roster in rosters:
                roster.line_number = str(roster.api_line_number or "").strip()
                roster.line_sort_order = int(roster.api_line_sort_order or 0)
                roster.line_manual = False
                roster.synced_at = datetime.utcnow()

        result = self.game_details(game_external_id, language=language)
        if result is None:
            raise ValueError("Матч не найден")
        return result

    def set_game_period(
        self,
        game_external_id: str,
        *,
        current_period: str,
        updated_by: str = "",
        language: str = "ru",
    ) -> dict[str, Any]:
        current_period = self._normalise_control_period(current_period)
        settings = self.settings.public()
        with self.database.session() as session:
            game = session.scalar(
                select(Game).where(Game.external_id == str(game_external_id))
            )
            if game is None:
                raise ValueError("Матч не найден")
            tournament = session.scalar(
                select(Tournament).where(
                    Tournament.external_id == game.tournament_external_id
                )
            )
            stage = (
                stage_key(tournament.season_part, tournament.round_code)
                if tournament else "other"
            )
            if stage == "regular":
                if current_period not in {"1", "2", "3", "ot", "so", "finished"}:
                    raise ValueError("Для регулярного чемпионата выбран неизвестный период")
            elif stage == "playoff":
                if current_period == "ot":
                    current_period = "ot1"
                if current_period == "so" or current_period not in {"1", "2", "3", "finished"} and not self._overtime_number(current_period):
                    raise ValueError("В плей-офф буллиты недоступны; выберите номер овертайма")
            else:
                if current_period not in {"1", "2", "3", "ot", "finished"}:
                    raise ValueError("Для этого этапа выбран неизвестный период")
            row = self._get_or_create_game_control(session, game.external_id)
            previous_period = self._normalise_control_period(row.current_period or "1")
            period_changed = previous_period != current_period
            now = datetime.utcnow()
            if period_changed and bool(settings.get("timer_reset_on_period_change", True)):
                rules = self._timer_rule_payload(
                    settings,
                    stage=stage,
                    current_period=current_period,
                    language=language,
                )
                previous_state = self._timer_state_from_row(
                    row,
                    default_ms=rules["duration_ms"],
                )
                reset_state = self._default_game_timer_state(rules["duration_ms"])
                # Active penalties and their history continue across a period
                # boundary; only the main game clock is reset.
                reset_state["penalty_board"] = previous_state.get("penalty_board") or {
                    "penalties": [],
                    "history": [],
                }
                row.timer_state_json = json.dumps(
                    {
                        "main_timer": reset_state["main_timer"],
                        "penalty_board": reset_state["penalty_board"],
                    },
                    ensure_ascii=False,
                    separators=(",", ":"),
                )
                row.timer_state_updated_at = now
            row.current_period = current_period
            row.updated_by = str(updated_by or "")[:128]
            row.updated_at = now
        result = self.game_control(game_external_id, language=language)
        if result is None:
            raise ValueError("Матч не найден")
        return result

    def configure_shootout(
        self,
        game_external_id: str,
        *,
        initial_attempts: int,
        reset: bool = False,
        updated_by: str = "",
        language: str = "ru",
    ) -> dict[str, Any]:
        initial_attempts = 5 if int(initial_attempts) == 5 else 3
        settings = self.settings.public()
        with self.database.session() as session:
            game = session.scalar(
                select(Game).where(Game.external_id == str(game_external_id))
            )
            if game is None:
                raise ValueError("Матч не найден")
            tournament = session.scalar(
                select(Tournament).where(
                    Tournament.external_id == game.tournament_external_id
                )
            )
            if not tournament or stage_key(tournament.season_part, tournament.round_code) != "regular":
                raise ValueError("Буллиты доступны только для регулярного чемпионата")
            row = self._get_or_create_game_control(session, game.external_id)
            attempts_count = session.scalar(
                select(func.count(ShootoutAttempt.id)).where(
                    ShootoutAttempt.game_external_id == game.external_id
                )
            ) or 0
            if attempts_count and not reset and row.shootout_initial_attempts != initial_attempts:
                raise ValueError("Чтобы изменить количество буллитов, начните серию заново")
            if reset or not row.shootout_started:
                session.execute(
                    delete(ShootoutAttempt).where(
                        ShootoutAttempt.game_external_id == game.external_id
                    )
                )
                row.shootout_extra_rounds = 0
            row.shootout_initial_attempts = initial_attempts
            row.shootout_started = True
            previous_period = self._normalise_control_period(row.current_period or "1")
            now = datetime.utcnow()
            if previous_period != "so" and bool(settings.get("timer_reset_on_period_change", True)):
                previous_state = self._timer_state_from_row(row, default_ms=0)
                reset_state = self._default_game_timer_state(0)
                reset_state["penalty_board"] = previous_state.get("penalty_board") or {
                    "penalties": [],
                    "history": [],
                }
                row.timer_state_json = json.dumps(
                    {
                        "main_timer": reset_state["main_timer"],
                        "penalty_board": reset_state["penalty_board"],
                    },
                    ensure_ascii=False,
                    separators=(",", ":"),
                )
                row.timer_state_updated_at = now
            row.current_period = "so"
            row.updated_by = str(updated_by or "")[:128]
            row.updated_at = now
        result = self.game_control(game_external_id, language=language)
        if result is None:
            raise ValueError("Матч не найден")
        return result

    def add_shootout_round(
        self,
        game_external_id: str,
        *,
        updated_by: str = "",
        language: str = "ru",
    ) -> dict[str, Any]:
        with self.database.session() as session:
            game = session.scalar(
                select(Game).where(Game.external_id == str(game_external_id))
            )
            if game is None:
                raise ValueError("Матч не найден")
            row = self._get_or_create_game_control(session, game.external_id)
            if not row.shootout_started:
                raise ValueError("Сначала начните серию буллитов")
            attempts = list(
                session.scalars(
                    select(ShootoutAttempt).where(
                        ShootoutAttempt.game_external_id == game.external_id
                    )
                )
            )
            allowed = (5 if row.shootout_initial_attempts == 5 else 3) + max(0, row.shootout_extra_rounds)
            home = [item for item in attempts if item.side == "home"]
            away = [item for item in attempts if item.side == "away"]
            if len(home) < allowed or len(away) < allowed:
                raise ValueError("Сначала завершите текущую серию обеих команд")
            if sum(item.scored for item in home) != sum(item.scored for item in away):
                raise ValueError("Новая серия нужна только при равном счёте")
            row.shootout_extra_rounds = max(0, row.shootout_extra_rounds) + 1
            row.updated_by = str(updated_by or "")[:128]
            row.updated_at = datetime.utcnow()
        result = self.game_control(game_external_id, language=language)
        if result is None:
            raise ValueError("Матч не найден")
        return result

    def add_shootout_attempt(
        self,
        game_external_id: str,
        *,
        side: str,
        player_external_id: str,
        scored: bool,
        created_by: str = "",
        language: str = "ru",
    ) -> dict[str, Any]:
        side = str(side or "").strip().lower()
        if side not in {"home", "away"}:
            raise ValueError("Неизвестная сторона команды")
        settings = self.settings.public()
        language = normalise_language(language, settings["ui_language"])
        fallback_language = settings["fallback_language"]
        player_external_id = str(player_external_id or "").strip()
        if not player_external_id:
            raise ValueError("Не выбран игрок")

        with self.database.session() as session:
            game = session.scalar(
                select(Game).where(Game.external_id == str(game_external_id))
            )
            if game is None:
                raise ValueError("Матч не найден")
            row = self._get_or_create_game_control(session, game.external_id)
            if not row.shootout_started:
                raise ValueError("Сначала начните серию буллитов")
            roster = session.scalar(
                select(GameRoster).where(
                    and_(
                        GameRoster.game_external_id == game.external_id,
                        GameRoster.side == side,
                        GameRoster.player_external_id == player_external_id,
                    )
                )
            )
            if roster is None:
                raise ValueError("Игрок не найден в составе выбранной команды")
            player = session.scalar(
                select(Player).where(Player.external_id == player_external_id)
            )
            if player is not None:
                name_ru = player.full_name_ru or " ".join(
                    part for part in (player.last_name_ru, player.first_name_ru) if part
                )
                name_en = player.full_name_en or " ".join(
                    part for part in (player.last_name_en, player.first_name_en) if part
                )
                player_name = choose_text(
                    name_ru,
                    name_en,
                    language=language,
                    fallback_language=fallback_language,
                )
            else:
                player_name = player_external_id

            current_side_count = session.scalar(
                select(func.count(ShootoutAttempt.id)).where(
                    and_(
                        ShootoutAttempt.game_external_id == game.external_id,
                        ShootoutAttempt.side == side,
                    )
                )
            ) or 0
            allowed = (5 if row.shootout_initial_attempts == 5 else 3) + max(0, row.shootout_extra_rounds)
            if current_side_count >= allowed:
                raise ValueError("Для этой команды текущая серия уже завершена")
            sequence = (session.scalar(
                select(func.max(ShootoutAttempt.sequence_number)).where(
                    ShootoutAttempt.game_external_id == game.external_id
                )
            ) or 0) + 1
            team_attempt_number = int(current_side_count) + 1
            initial = 5 if row.shootout_initial_attempts == 5 else 3
            series_number = 1 if team_attempt_number <= initial else 1 + team_attempt_number - initial
            team_id = game.home_team_external_id if side == "home" else game.away_team_external_id
            session.add(
                ShootoutAttempt(
                    game_external_id=game.external_id,
                    sequence_number=sequence,
                    team_attempt_number=team_attempt_number,
                    series_number=series_number,
                    side=side,
                    team_external_id=team_id,
                    player_external_id=player_external_id,
                    player_number=roster.number,
                    player_name=player_name,
                    scored=bool(scored),
                    created_by=str(created_by or "")[:128],
                )
            )
            row.current_period = "so"
            row.updated_by = str(created_by or "")[:128]
            row.updated_at = datetime.utcnow()
        result = self.game_control(game_external_id, language=language)
        if result is None:
            raise ValueError("Матч не найден")
        return result

    def delete_shootout_attempt(
        self,
        game_external_id: str,
        attempt_id: int,
        *,
        updated_by: str = "",
        language: str = "ru",
    ) -> dict[str, Any]:
        with self.database.session() as session:
            attempt = session.scalar(
                select(ShootoutAttempt).where(
                    and_(
                        ShootoutAttempt.id == int(attempt_id),
                        ShootoutAttempt.game_external_id == str(game_external_id),
                    )
                )
            )
            if attempt is None:
                raise ValueError("Буллит не найден")
            session.delete(attempt)
            row = self._get_or_create_game_control(session, str(game_external_id))
            row.updated_by = str(updated_by or "")[:128]
            row.updated_at = datetime.utcnow()
        result = self.game_control(game_external_id, language=language)
        if result is None:
            raise ValueError("Матч не найден")
        return result

    @staticmethod
    def is_terminal_game_status(status: str) -> bool:
        value = "".join(
            character for character in str(status or "").strip().lower()
            if character.isalnum() or character in {"_", "-"}
        )
        terminal_values = {
            "finished", "finish", "final", "ended", "complete", "completed",
            "closed", "over", "played", "gameover", "matchfinished",
            "завершен", "завершён", "закончен", "окончен", "сыгран",
            "матчзавершен", "матчзавершён",
            "f", "ft", "3", "4", "5",
        }
        if value in terminal_values:
            return True
        return any(
            marker in value
            for marker in (
                "finish", "final", "complete", "ended", "gameover",
                "заверш", "закончен", "окончен", "сыгран",
            )
        )

    @staticmethod
    def _finish_type(value: Any) -> str:
        raw = str(value or "").strip().casefold()
        compact = "".join(character for character in raw if character.isalnum())
        if not compact:
            return ""
        if (
            compact in {"б", "so", "ps", "s", "2", "буллиты", "буллит"}
            or compact.startswith(("so", "ps", "б"))
            or "shootout" in compact
            or "бул" in compact
        ):
            return "SO"
        if (
            compact in {"от", "ot", "o", "1", "овертайм"}
            or compact.startswith(("ot", "от"))
            or "overtime" in compact
            or "оверт" in compact
        ):
            return "OT"
        return ""

    def _serialize_game(
        self,
        row: Game,
        *,
        language: str,
        fallback_language: str,
    ) -> dict[str, Any]:
        home_name = choose_text(
            row.home_team_name_ru,
            row.home_team_name_en,
            language=language,
            fallback_language=fallback_language,
        )
        away_name = choose_text(
            row.away_team_name_ru,
            row.away_team_name_en,
            language=language,
            fallback_language=fallback_language,
        )
        home_short = choose_text(
            row.home_team_short_ru,
            row.home_team_short_en,
            language=language,
            fallback_language=fallback_language,
        )
        away_short = choose_text(
            row.away_team_short_ru,
            row.away_team_short_en,
            language=language,
            fallback_language=fallback_language,
        )
        arena = choose_text(
            row.arena_ru,
            row.arena_en,
            language=language,
            fallback_language=fallback_language,
        )
        arena_city = choose_text(
            row.arena_city_ru,
            row.arena_city_en,
            language=language,
            fallback_language=fallback_language,
        )
        home_city = choose_text(
            row.home_team_city_ru,
            row.home_team_city_en,
            language=language,
            fallback_language=fallback_language,
        )
        away_city = choose_text(
            row.away_team_city_ru,
            row.away_team_city_en,
            language=language,
            fallback_language=fallback_language,
        )
        round_name = choose_text(
            row.round_name_ru,
            row.round_name_en,
            language=language,
            fallback_language=fallback_language,
        )

        is_finished = bool(row.approved) or self.is_terminal_game_status(row.status)
        finish_type = self._finish_type(row.overtime_type) if is_finished else ""
        finish_label = ("Б" if language == "ru" else "SO") if finish_type == "SO" else ("ОТ" if language == "ru" else "OT") if finish_type == "OT" else ""
        return {
            "id": row.external_id,
            "external_id": row.external_id,
            "tournament_external_id": row.tournament_external_id,
            "tournament_type": row.tournament_type,
            "game_number": row.game_number,
            "round": {
                "number": row.round_number,
                "name": round_name,
                "names": bilingual(row.round_name_ru, row.round_name_en),
            },
            "day_of_week": row.day_of_week,
            "changes": row.changes,
            "date": row.game_date.isoformat() if row.game_date else None,
            "time": row.start_time,
            "start_datetime_raw": row.start_datetime_raw,
            "status": row.status,
            "approved": bool(row.approved),
            "is_finished": is_finished,
            "refresh_interval_ms": 0 if is_finished else 1000,
            "arena": arena,
            "arena_city": arena_city,
            "arena_id": row.arena_external_id,
            "arenas": bilingual(row.arena_ru, row.arena_en),
            "arena_cities": bilingual(row.arena_city_ru, row.arena_city_en),
            "home": {
                "id": row.home_team_external_id,
                "entry_id": row.home_entry_external_id,
                "name": home_name,
                "short_name": home_short,
                "city": home_city,
                "names": bilingual(
                    row.home_team_name_ru,
                    row.home_team_name_en,
                ),
                "short_names": bilingual(
                    row.home_team_short_ru,
                    row.home_team_short_en,
                ),
                "cities": bilingual(
                    row.home_team_city_ru,
                    row.home_team_city_en,
                ),
                "score": row.home_score,
            },
            "away": {
                "id": row.away_team_external_id,
                "entry_id": row.away_entry_external_id,
                "name": away_name,
                "short_name": away_short,
                "city": away_city,
                "names": bilingual(
                    row.away_team_name_ru,
                    row.away_team_name_en,
                ),
                "short_names": bilingual(
                    row.away_team_short_ru,
                    row.away_team_short_en,
                ),
                "cities": bilingual(
                    row.away_team_city_ru,
                    row.away_team_city_en,
                ),
                "score": row.away_score,
            },
            "score": {
                "raw": row.score_raw,
                "home": row.home_score,
                "away": row.away_score,
                "overtime_type": row.overtime_type,
                "finish_type": finish_type,
                "finish_label": finish_label,
                "periods": [
                    row.period_1_score,
                    row.period_2_score,
                    row.period_3_score,
                    row.period_4_score,
                ],
                "series": row.series_score,
            },
            "attendance": row.attendance,
            "temporary_value": row.temporary_value,
            "synced_at": row.synced_at.isoformat() if row.synced_at else None,
        }
    def games(
        self,
        *,
        tournament_external_id: str = "",
        on_date: date | None = None,
        language: str = "ru",
        all_dates: bool = False,
    ) -> dict[str, Any]:
        settings = self.settings.public()
        language = normalise_language(language, settings["ui_language"])
        fallback_language = settings["fallback_language"]

        with self.database.session() as session:
            statement = select(Game)
            if tournament_external_id:
                statement = statement.where(
                    Game.tournament_external_id == tournament_external_id
                )
            if on_date is not None and not all_dates:
                statement = statement.where(Game.game_date == on_date)
            statement = statement.order_by(
                Game.game_date,
                Game.start_time,
                Game.id,
            )
            rows = list(session.scalars(statement))

            dates_statement = select(Game.game_date).where(
                Game.game_date.is_not(None)
            )
            if tournament_external_id:
                dates_statement = dates_statement.where(
                    Game.tournament_external_id == tournament_external_id
                )
            available_dates = sorted({
                value.isoformat()
                for value in session.scalars(dates_statement)
                if value is not None
            })

            last_synced = max(
                (row.synced_at for row in rows if row.synced_at),
                default=None,
            )

        items = [
            self._serialize_game(
                row,
                language=language,
                fallback_language=fallback_language,
            )
            for row in rows
        ]
        return {
            "meta": {
                "tournament_external_id": tournament_external_id,
                "date": on_date.isoformat() if on_date else None,
                "language": language,
                "count": len(items),
                "available_dates": available_dates,
                "schedule_loaded": bool(available_dates),
                "last_synced_at": (
                    last_synced.isoformat() if last_synced else None
                ),
                "endpoint_template": settings.get(
                    "games_endpoint_template", ""
                ),
                "credentials_configured": self.settings.status()[
                    "credentials_configured"
                ],
            },
            "items": items,
        }

    def game(
        self,
        external_id: str,
        *,
        language: str = "ru",
    ) -> dict[str, Any] | None:
        settings = self.settings.public()
        language = normalise_language(language, settings["ui_language"])
        with self.database.session() as session:
            row = session.scalar(
                select(Game).where(Game.external_id == str(external_id))
            )
            return (
                self._serialize_game(
                    row,
                    language=language,
                    fallback_language=settings["fallback_language"],
                )
                if row
                else None
            )

    def endpoint_diagnostics(
        self,
        *,
        tournament_external_id: str,
        limit: int = 50,
    ) -> dict[str, Any]:
        with self.database.session() as session:
            rows = list(
                session.scalars(
                    select(EndpointDiscovery)
                    .where(
                        EndpointDiscovery.tournament_external_id
                        == tournament_external_id
                    )
                    .order_by(desc(EndpointDiscovery.id))
                    .limit(limit)
                )
            )
        return {
            "tournament_external_id": tournament_external_id,
            "items": [
                {
                    "status": row.status,
                    "template": row.endpoint_template,
                    "endpoint": row.resolved_endpoint,
                    "status_code": row.status_code,
                    "root_tag": row.root_tag,
                    "item_tag": row.item_tag,
                    "item_count": row.item_count,
                    "diagnostics": json.loads(row.diagnostics_json or "{}"),
                    "snapshot_path": row.snapshot_path,
                    "error": row.error,
                    "created_at": row.created_at.isoformat(),
                }
                for row in rows
            ],
        }
    def save_preference(
        self,
        *,
        wfl_user_id: str,
        display_language: str,
        vmix_language: str,
        tournament_external_id: str,
    ) -> dict[str, Any]:
        display_language = normalise_language(display_language)
        vmix_language = (
            vmix_language if vmix_language in {"ru", "en", "bilingual"} else "ru"
        )
        with self.database.session() as session:
            row = session.get(UserPreference, wfl_user_id)
            if row is None:
                row = UserPreference(wfl_user_id=wfl_user_id)
                session.add(row)
            row.display_language = display_language
            row.vmix_language = vmix_language
            row.selected_tournament_external_id = tournament_external_id
            row.updated_at = datetime.utcnow()
        return {
            "wfl_user_id": wfl_user_id,
            "display_language": display_language,
            "vmix_language": vmix_language,
            "selected_tournament_external_id": tournament_external_id,
            "triggers_enabled": True,
            "toasts_enabled": not bool(row.triggers_disabled),
        }

    def user_preference(self, *, wfl_user_id: str) -> dict[str, Any]:
        with self.database.session() as session:
            row = session.get(UserPreference, wfl_user_id)
            if row is None:
                row = UserPreference(wfl_user_id=wfl_user_id)
                session.add(row)
            return {
                "wfl_user_id": wfl_user_id,
                "display_language": normalise_language(row.display_language or "ru"),
                "vmix_language": row.vmix_language if row.vmix_language in {"ru", "en", "bilingual"} else "ru",
                "selected_tournament_external_id": str(row.selected_tournament_external_id or ""),
                "triggers_enabled": True,
                "toasts_enabled": not bool(row.triggers_disabled),
            }

    def save_toasts_preference(self, *, wfl_user_id: str, enabled: bool) -> dict[str, Any]:
        # Reuse the build55 additive column for compatibility, but from build59 it
        # represents only routine toast visibility. Trigger execution stays on.
        with self.database.session() as session:
            row = session.get(UserPreference, wfl_user_id)
            if row is None:
                row = UserPreference(wfl_user_id=wfl_user_id)
                session.add(row)
            row.triggers_disabled = not bool(enabled)
            row.updated_at = datetime.utcnow()
        return {"wfl_user_id": wfl_user_id, "triggers_enabled": True, "toasts_enabled": bool(enabled)}

    def save_triggers_preference(self, *, wfl_user_id: str, enabled: bool) -> dict[str, Any]:
        # Backwards-compatible alias for build55 clients.
        return self.save_toasts_preference(wfl_user_id=wfl_user_id, enabled=enabled)

    def open_session(
        self,
        *,
        wfl_user_id: str,
        login_snapshot: str = "",
        display_name_snapshot: str = "",
        tournament_external_id: str,
        game_external_id: str = "",
        display_language: str = "ru",
        vmix_language: str = "ru",
        vmix_device_uuid: str = "",
        replace_existing: bool = True,
    ) -> dict[str, Any]:
        wfl_user_id = wfl_user_id.strip()
        if not wfl_user_id:
            raise ValueError("Не указан пользователь")
        tournament_external_id = str(tournament_external_id).strip()
        if not tournament_external_id:
            raise ValueError("Не указан турнир")
        game_external_id = str(game_external_id or "").strip()
        vmix_device_uuid = str(vmix_device_uuid or "").strip()[:128]

        with self.database.session() as session:
            # A vMix channel is permanent for the WFL account.  Reuse the latest
            # operator row (including a previously closed legacy row) and only
            # change the tournament/game bound to it.  This keeps the public vMix
            # URL stable across browser logins, page reloads and match changes.
            active_rows = list(
                session.scalars(
                    select(OperatorSession)
                    .where(
                        and_(
                            OperatorSession.wfl_user_id == wfl_user_id,
                            OperatorSession.status == "active",
                        )
                    )
                    .order_by(desc(OperatorSession.id))
                )
            )
            if active_rows:
                existing_user = active_rows[0]
                rows = active_rows + list(
                    session.scalars(
                        select(OperatorSession)
                        .where(
                            and_(
                                OperatorSession.wfl_user_id == wfl_user_id,
                                OperatorSession.status != "active",
                            )
                        )
                        .order_by(desc(OperatorSession.id))
                    )
                )
            else:
                rows = list(
                    session.scalars(
                        select(OperatorSession)
                        .where(OperatorSession.wfl_user_id == wfl_user_id)
                        .order_by(desc(OperatorSession.id))
                    )
                )
                existing_user = rows[0] if rows else None
            if existing_user is not None:
                if (
                    not replace_existing
                    and existing_user.status == "active"
                    and (
                        existing_user.tournament_external_id != tournament_external_id
                        or existing_user.game_external_id != game_external_id
                    )
                ):
                    raise ValueError(
                        "У пользователя уже есть активный персональный канал vMix"
                    )

                now = datetime.utcnow()
                existing_user.login_snapshot = login_snapshot.strip()
                existing_user.display_name_snapshot = display_name_snapshot.strip()
                existing_user.tournament_external_id = tournament_external_id
                existing_user.game_external_id = game_external_id
                existing_user.display_language = normalise_language(display_language)
                existing_user.vmix_language = (
                    vmix_language
                    if vmix_language in {"ru", "en", "bilingual"}
                    else "ru"
                )
                # Browser-local Agent selection is part of the current session.
                # Empty explicitly clears an older device binding so a second
                # browser cannot accidentally inherit another operator's vMix.
                existing_user.vmix_device_uuid = vmix_device_uuid
                existing_user.status = "active"
                existing_user.closed_at = None
                existing_user.last_activity_at = now

                # Close duplicate legacy rows so only one account channel can be
                # switched from the operator interface. Their old public links
                # remain read-only snapshots because get_session still resolves
                # them, which avoids breaking an old vMix project unexpectedly.
                for duplicate in rows[1:]:
                    if duplicate.status == "active":
                        duplicate.status = "closed"
                        duplicate.closed_at = now
                        duplicate.last_activity_at = now
                session.flush()
                return self._session_payload(existing_user)

            row = OperatorSession(
                session_token=secrets.token_urlsafe(32),
                wfl_user_id=wfl_user_id,
                login_snapshot=login_snapshot.strip(),
                display_name_snapshot=display_name_snapshot.strip(),
                tournament_external_id=tournament_external_id,
                game_external_id=game_external_id,
                display_language=normalise_language(display_language),
                vmix_language=(
                    vmix_language
                    if vmix_language in {"ru", "en", "bilingual"}
                    else "ru"
                ),
                vmix_device_uuid=vmix_device_uuid,
            )
            session.add(row)
            session.flush()
            return self._session_payload(row)

    @staticmethod
    def _session_payload(row: OperatorSession) -> dict[str, Any]:
        return {
            "token": row.session_token,
            "channel_key": row.session_token,
            "channel_owner": row.login_snapshot or row.wfl_user_id,
            "wfl_user_id": row.wfl_user_id,
            "login_snapshot": row.login_snapshot,
            "display_name_snapshot": row.display_name_snapshot,
            "tournament_external_id": row.tournament_external_id,
            "game_external_id": row.game_external_id,
            "display_language": row.display_language,
            "vmix_language": row.vmix_language,
            "vmix_device_id": row.vmix_device_uuid,
            "status": row.status,
            "created_at": row.created_at.isoformat(),
        }

    def get_user_vmix_channel(self, wfl_user_id: str) -> dict[str, Any] | None:
        """Return the persistent vMix channel assigned to one WFL account."""
        wfl_user_id = str(wfl_user_id or "").strip()
        if not wfl_user_id:
            return None
        with self.database.session() as session:
            row = session.scalar(
                select(OperatorSession)
                .where(
                    and_(
                        OperatorSession.wfl_user_id == wfl_user_id,
                        OperatorSession.status == "active",
                    )
                )
                .order_by(desc(OperatorSession.id))
            )
            if row is None:
                row = session.scalar(
                    select(OperatorSession)
                    .where(OperatorSession.wfl_user_id == wfl_user_id)
                    .order_by(desc(OperatorSession.id))
                )
            return self._session_payload(row) if row is not None else None

    def get_session(self, token: str) -> dict[str, Any] | None:
        with self.database.session() as session:
            row = session.scalar(
                select(OperatorSession).where(
                    OperatorSession.session_token == token
                )
            )
            if row is None:
                return None
            row.last_activity_at = datetime.utcnow()
            return self._session_payload(row)

    def close_session(self, token: str, *, wfl_user_id: str | None = None) -> bool:
        with self.database.session() as session:
            row = session.scalar(
                select(OperatorSession).where(
                    OperatorSession.session_token == token
                )
            )
            if row is None:
                return False
            if wfl_user_id is not None and row.wfl_user_id != wfl_user_id:
                return False
            now = datetime.utcnow()
            if wfl_user_id is not None:
                # Browser-side legacy code may still send DELETE while switching
                # tournaments. A personal vMix channel must survive that action,
                # otherwise the URL would change. Keep it active and only touch it.
                row.status = "active"
                row.closed_at = None
                row.last_activity_at = now
                return True
            row.status = "closed"
            row.closed_at = now
            row.last_activity_at = now
            return True

    def vmix_links(
        self,
        *,
        token: str,
        base_url: str,
        language: str,
    ) -> dict[str, Any]:
        language = (
            language if language in {"ru", "en", "bilingual"} else "ru"
        )
        prefix = (
            base_url.rstrip("/")
            + f"/vmix/hockey/channel/{token}/{language}"
        )
        links: dict[str, Any] = {
            "info": f"{prefix}/info.json",
            "scoreboard": f"{prefix}/scoreboard.json",
            "home_lineup": f"{prefix}/home-lineup.json",
            "away_lineup": f"{prefix}/away-lineup.json",
            "officials": f"{prefix}/officials.json",
            "penalties": f"{prefix}/penalties.json",
            "events": f"{prefix}/events.json",
        }
        try:
            from broadcast_settings.settings import read_vmix_json

            links["custom"] = {
                str(config.get("key")): f"{prefix}/custom/{config.get('key')}.json"
                for config in (read_vmix_json().get("configs") or [])
                if isinstance(config, dict) and config.get("enabled", True) is not False and config.get("key")
            }
        except Exception:
            links["custom"] = {}
        return links
