412 lines
12 KiB
Python
412 lines
12 KiB
Python
from numpy import place
|
||
|
||
from db import get_connection
|
||
from repositories.team_repository import get_team_id_by_external_id
|
||
from repositories.stadium_repository import get_or_create_stadium
|
||
from parsers.parser_sources import get_default_source_key, list_parser_sources
|
||
|
||
|
||
def ensure_match_source_key_column() -> None:
|
||
"""Добавляет источник турнира для матчей, чтобы vMix выбирал правильную папку логотипов."""
|
||
conn = get_connection()
|
||
try:
|
||
with conn.cursor() as cur:
|
||
cur.execute(
|
||
"""
|
||
ALTER TABLE matches
|
||
ADD COLUMN IF NOT EXISTS source_key VARCHAR(50);
|
||
"""
|
||
)
|
||
conn.commit()
|
||
except Exception:
|
||
conn.rollback()
|
||
raise
|
||
finally:
|
||
conn.close()
|
||
|
||
|
||
|
||
|
||
def _normalize_source_key(value: str | None) -> str:
|
||
return str(value or "").upper().strip()
|
||
|
||
|
||
def infer_match_source_key(
|
||
explicit_source_key: str | None = None,
|
||
season: str | None = None,
|
||
tour: str | None = None,
|
||
) -> str:
|
||
"""Определяет источник матча, даже если старые строки matches.source_key ещё пустые.
|
||
|
||
Приоритет:
|
||
1. matches.source_key;
|
||
2. точное совпадение season с parser_sources.season;
|
||
3. кубковые слова в tour;
|
||
4. обычный дефолт из настроек проекта.
|
||
"""
|
||
explicit = _normalize_source_key(explicit_source_key)
|
||
if explicit:
|
||
return explicit
|
||
|
||
season_value = str(season or "").strip()
|
||
tour_value = str(tour or "").lower().strip()
|
||
|
||
try:
|
||
sources = list_parser_sources()
|
||
except Exception:
|
||
sources = []
|
||
|
||
if season_value and sources:
|
||
matches = [
|
||
_normalize_source_key(source.get("key"))
|
||
for source in sources
|
||
if str(source.get("season") or "").strip() == season_value
|
||
]
|
||
matches = [key for key in matches if key]
|
||
if len(matches) == 1:
|
||
return matches[0]
|
||
|
||
cup_markers = (
|
||
"кубок",
|
||
"финал",
|
||
"полуфинал",
|
||
"четвертьфинал",
|
||
"1/2",
|
||
"1/4",
|
||
"1/8",
|
||
"1/16",
|
||
"групп",
|
||
"этап",
|
||
"стад",
|
||
)
|
||
if any(marker in tour_value for marker in cup_markers):
|
||
known_keys = {_normalize_source_key(source.get("key")) for source in sources}
|
||
if "RUSSIAN_CUP" in known_keys:
|
||
return "RUSSIAN_CUP"
|
||
|
||
try:
|
||
return _normalize_source_key(get_default_source_key()) or "SUPERLEAGUE"
|
||
except Exception:
|
||
return "SUPERLEAGUE"
|
||
|
||
|
||
def resolve_match_source_key(match_id: int | str | None, fallback: str | None = None) -> str:
|
||
"""Возвращает source_key для матча и, если возможно, записывает его в matches.
|
||
|
||
Это нужно для старых матчей Кубка России, которые могли быть загружены до появления
|
||
колонки source_key. Без этого vMix скачивает пресет и собирает пути как для Суперлиги.
|
||
"""
|
||
fallback_key = _normalize_source_key(fallback)
|
||
if not match_id:
|
||
return fallback_key or infer_match_source_key(fallback_key)
|
||
|
||
conn = get_connection()
|
||
try:
|
||
with conn.cursor() as cur:
|
||
cur.execute(
|
||
"""
|
||
SELECT source_key, season, tour
|
||
FROM matches
|
||
WHERE id = %s
|
||
LIMIT 1;
|
||
""",
|
||
(match_id,),
|
||
)
|
||
row = cur.fetchone()
|
||
|
||
if not row:
|
||
return fallback_key or infer_match_source_key(fallback_key)
|
||
|
||
explicit_key, season, tour = row
|
||
source_key = infer_match_source_key(explicit_key or fallback_key, season, tour)
|
||
|
||
if source_key and not _normalize_source_key(explicit_key):
|
||
cur.execute(
|
||
"""
|
||
UPDATE matches
|
||
SET source_key = %s, updated_at = NOW()
|
||
WHERE id = %s
|
||
AND (source_key IS NULL OR TRIM(source_key) = '');
|
||
""",
|
||
(source_key, match_id),
|
||
)
|
||
conn.commit()
|
||
|
||
return source_key
|
||
except Exception:
|
||
conn.rollback()
|
||
return fallback_key or infer_match_source_key(fallback_key)
|
||
finally:
|
||
conn.close()
|
||
|
||
|
||
def upsert_match(
|
||
external_id: str,
|
||
home_team_id: int,
|
||
away_team_id: int,
|
||
match_date=None,
|
||
status: str = "scheduled",
|
||
home_score: int | None = None,
|
||
away_score: int | None = None,
|
||
tour: str | None = None,
|
||
season: str | None = None,
|
||
place: str | None = None,
|
||
stadium_id: int | None = None,
|
||
date_raw: str | None = None,
|
||
score_add: str | None = None,
|
||
source_key: str | None = None,
|
||
) -> None:
|
||
query = """
|
||
INSERT INTO matches (
|
||
external_id,
|
||
home_team_id,
|
||
away_team_id,
|
||
match_date,
|
||
status,
|
||
home_score,
|
||
away_score,
|
||
tour,
|
||
season,
|
||
place,
|
||
stadium_id,
|
||
date_raw,
|
||
score_add,
|
||
source_key,
|
||
created_at,
|
||
updated_at
|
||
)
|
||
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW(), NOW())
|
||
ON CONFLICT (external_id)
|
||
DO UPDATE SET
|
||
home_team_id = EXCLUDED.home_team_id,
|
||
away_team_id = EXCLUDED.away_team_id,
|
||
match_date = EXCLUDED.match_date,
|
||
status = EXCLUDED.status,
|
||
home_score = EXCLUDED.home_score,
|
||
away_score = EXCLUDED.away_score,
|
||
tour = EXCLUDED.tour,
|
||
season = EXCLUDED.season,
|
||
place = EXCLUDED.place,
|
||
stadium_id = EXCLUDED.stadium_id,
|
||
date_raw = EXCLUDED.date_raw,
|
||
score_add = EXCLUDED.score_add,
|
||
source_key = COALESCE(EXCLUDED.source_key, matches.source_key),
|
||
updated_at = NOW();
|
||
"""
|
||
|
||
conn = get_connection()
|
||
try:
|
||
with conn.cursor() as cur:
|
||
cur.execute(
|
||
query,
|
||
(
|
||
external_id,
|
||
home_team_id,
|
||
away_team_id,
|
||
match_date,
|
||
status,
|
||
home_score,
|
||
away_score,
|
||
tour,
|
||
season,
|
||
place,
|
||
stadium_id,
|
||
date_raw,
|
||
score_add,
|
||
source_key,
|
||
),
|
||
)
|
||
conn.commit()
|
||
except Exception:
|
||
conn.rollback()
|
||
raise
|
||
finally:
|
||
conn.close()
|
||
|
||
|
||
def upsert_match_by_team_external_ids(
|
||
external_id: str,
|
||
home_team_external_id: str,
|
||
away_team_external_id: str,
|
||
match_date=None,
|
||
status: str = "scheduled",
|
||
home_score: int | None = None,
|
||
away_score: int | None = None,
|
||
tour: str | None = None,
|
||
season: str | None = None,
|
||
place: str | None = None,
|
||
stadium_id: int | None = None,
|
||
date_raw: str | None = None,
|
||
score_add: str | None = None,
|
||
source_key: str | None = None,
|
||
) -> None:
|
||
home_team_id = get_team_id_by_external_id(home_team_external_id)
|
||
away_team_id = get_team_id_by_external_id(away_team_external_id)
|
||
stadium_id = get_or_create_stadium(place)
|
||
|
||
if home_team_id is None:
|
||
raise ValueError(f"Home team not found by external_id: {home_team_external_id}")
|
||
|
||
if away_team_id is None:
|
||
raise ValueError(f"Away team not found by external_id: {away_team_external_id}")
|
||
|
||
upsert_match(
|
||
external_id=external_id,
|
||
home_team_id=home_team_id,
|
||
away_team_id=away_team_id,
|
||
match_date=match_date,
|
||
status=status,
|
||
home_score=home_score,
|
||
away_score=away_score,
|
||
tour=tour,
|
||
season=season,
|
||
place=place,
|
||
stadium_id=stadium_id,
|
||
date_raw=date_raw,
|
||
score_add=score_add,
|
||
source_key=source_key,
|
||
)
|
||
|
||
|
||
def clear_match_squad_data(match_id: int) -> None:
|
||
conn = get_connection()
|
||
try:
|
||
with conn.cursor() as cur:
|
||
cur.execute("DELETE FROM match_lineup_players WHERE match_id = %s", (match_id,))
|
||
cur.execute("DELETE FROM match_coaches WHERE match_id = %s", (match_id,))
|
||
cur.execute("DELETE FROM match_lineups WHERE match_id = %s", (match_id,))
|
||
|
||
# если есть таблица судей матча
|
||
cur.execute("DELETE FROM match_referees WHERE match_id = %s", (match_id,))
|
||
|
||
# если есть матчевые расстановки
|
||
cur.execute("DELETE FROM match_formations WHERE match_id = %s", (match_id,))
|
||
|
||
conn.commit()
|
||
except Exception:
|
||
conn.rollback()
|
||
raise
|
||
finally:
|
||
conn.close()
|
||
|
||
|
||
def get_match_by_external_id(external_id: str):
|
||
query = """
|
||
SELECT id, external_id, home_team_id, away_team_id
|
||
FROM matches
|
||
WHERE external_id = %s
|
||
LIMIT 1;
|
||
"""
|
||
|
||
conn = get_connection()
|
||
try:
|
||
with conn.cursor() as cur:
|
||
cur.execute(query, (str(external_id).strip(),))
|
||
return cur.fetchone()
|
||
finally:
|
||
conn.close()
|
||
|
||
|
||
def mark_match_parsed(external_id: str) -> None:
|
||
query = """
|
||
UPDATE matches
|
||
SET parsed = TRUE,
|
||
parse_error = NULL,
|
||
updated_at = NOW()
|
||
WHERE external_id = %s;
|
||
"""
|
||
|
||
conn = get_connection()
|
||
try:
|
||
with conn.cursor() as cur:
|
||
cur.execute(query, (str(external_id).strip(),))
|
||
conn.commit()
|
||
except Exception:
|
||
conn.rollback()
|
||
raise
|
||
finally:
|
||
conn.close()
|
||
|
||
|
||
def mark_match_parse_error(external_id: str, error_text: str) -> None:
|
||
query = """
|
||
UPDATE matches
|
||
SET parsed = FALSE,
|
||
parse_error = %s,
|
||
updated_at = NOW()
|
||
WHERE external_id = %s;
|
||
"""
|
||
|
||
conn = get_connection()
|
||
try:
|
||
with conn.cursor() as cur:
|
||
cur.execute(query, (str(error_text)[:2000], str(external_id).strip()))
|
||
conn.commit()
|
||
except Exception:
|
||
conn.rollback()
|
||
raise
|
||
finally:
|
||
conn.close()
|
||
|
||
|
||
def get_tour_schedule_by_match_id(match_id: int) -> list[dict]:
|
||
conn = get_connection()
|
||
try:
|
||
with conn.cursor() as cur:
|
||
cur.execute(
|
||
"""
|
||
SELECT m.tour
|
||
FROM matches m
|
||
WHERE m.id = %s
|
||
""",
|
||
(match_id,),
|
||
)
|
||
row = cur.fetchone()
|
||
if not row or not row[0]:
|
||
return []
|
||
|
||
tour = row[0]
|
||
|
||
cur.execute(
|
||
"""
|
||
SELECT
|
||
m.id,
|
||
m.external_id,
|
||
m.match_date,
|
||
m.place,
|
||
m.status,
|
||
m.home_score,
|
||
m.away_score,
|
||
ht.name AS home_team_name,
|
||
at.name AS away_team_name,
|
||
COALESCE(m.channel, '') AS channel
|
||
FROM matches m
|
||
LEFT JOIN teams ht ON ht.id = m.home_team_id
|
||
LEFT JOIN teams at ON at.id = m.away_team_id
|
||
WHERE m.tour = %s
|
||
ORDER BY
|
||
m.match_date NULLS LAST,
|
||
m.id
|
||
""",
|
||
(tour,),
|
||
)
|
||
rows = cur.fetchall()
|
||
|
||
return [
|
||
{
|
||
"match_id": r[0],
|
||
"match_external_id": r[1],
|
||
"match_date": r[2],
|
||
"stadium_name": r[3] or "",
|
||
"status": r[4] or "",
|
||
"home_score": r[5],
|
||
"away_score": r[6],
|
||
"home_team_name": r[7] or "",
|
||
"away_team_name": r[8] or "",
|
||
"channel": r[9] or "",
|
||
}
|
||
for r in rows
|
||
]
|
||
finally:
|
||
conn.close()
|