import os
import re
import unicodedata
import difflib
import pandas as pd
from collections import deque, defaultdict, Counter
from sympy import symbols, Eq, solve
from datetime import datetime, timedelta

from openpyxl import Workbook, load_workbook
from openpyxl.styles import Alignment, Font, PatternFill, Border, Side
from openpyxl.utils import get_column_letter

✅ Rich text support (font colors inside a single cell)

from openpyxl.cell.rich_text import CellRichText, TextBlock
from openpyxl.cell.text import InlineFont

===================== НАСТРОЙКИ =====================

excel_file_path = r"C:\Users\User\Desktop\results\results.xlsx"

=====================================================

league_thresholds = {
"Albania Superiore": (2.52, 2.73),
"Andorra Primera": (2.42, 2.75),
"Armenia Premier": (2.32, 3.20),
"Bosnia WWin Liga": (2.00, 3.20),
"Croatia HNL": (2.29, 3.25),
"Cyprus League": (2.25, 3.20),
"Cyprus Division 2": (2.46, 2.70),
"Czech Chance Liga": (2.25, 3.30),
"Denmark Superliga": (2.40, 3.00),
"Greece Super League": (2.36, 3.40),
"Greece Super League 2": (2.25, 3.30),
"Hungary OTP Bank Liga": (2.17, 3.40),
"Hungary Merkantil Bank Liga": (2.38, 3.30),
"Luxembourg BGL Ligue": (2.20, 3.00),
"Malta Premier League": (2.75, 2.60),
"Montenegro Prva Liga": (2.50, 3.20),
"North Macedonia 1 MFL": (2.13, 3.60),
"Slovakia Nike Liga": (2.30, 3.00),
"Slovenia Prva Liga": (2.47, 3.00),
"Algeria Ligue 2": (1.87, 5.00),
"Mexico Liga de Expansion MX": (2.00, 4.00),
"South Africa Premiership": (2.31, 3.45),
"Croatia Prva NL": (2.40, 2.80),
"India ISL": (2.17, 3.33),
"Indonesia Liga 1": (2.27, 3.45),
"Bulgaria Vtora liga": (2.27, 3.45),
"Zambia Super League": (2.47, 3.10),
"Romania Liga 3": (2.30, 2.94),
"Myanmar National League": (2.50, 2.50),
"Tanzania Ligi Kuu Bara": (2.20, 3.30),
"France Nacional": (2.32, 3.30),
"Nigeria NPFL": (1.50, 6.00),
"Turkey 1.Lig": (2.27, 3.20),
"Algeria Ligue 1": (2.05, 4.30),
"Italy Serie C - A": (2.38, 3.30),
"Italy Serie B": (2.46, 3.50),
"Algeria Ligue 2 - 21": (2.00, 3.20),
"England National League South": (2.38, 3.00),
"Brazil Paulista A4": (2.44, 3.00),
"Ethiopia": (3.12, 2.85),
"Spain LaLiga": (2.29, 3.61),
"Australia NPL": (2.22, 3.00),
"Bulgaria Vtora Liga": (2.08, 3.70),
"Italy Serie C - C": (2.40, 3.20),
"Spain Segunda": (2.11, 4.02),
"France Ligue 1": (2.30, 3.60),
"Brazil Serie A": (2.05, 4.00),
"Brazil Serie B": (2.05, 4.00),
"Brazil Serie C": (2.15, 3.70),
"Iceland Besta Delid": (2.12, 3.10),
"Australia NPL Northern": (2.47, 2.30),
"Norway Division 2": (2.08, 2.94),
"USA MLS": (2.27, 3.20),
"Argentina": (2.22, 3.80),
"Mexico": (2.08, 3.80),
"Bundesliga - 2": (2.16, 3.55),
"France - 2": (2.16, 3.55),
"Netherlands - Eridivisie": (2.20, 3.20),
"England - Premier League": (2.18, 3.60)
}

===================== helpers =====================

def normalize_dashes(text):
if isinstance(text, str):
return text.replace('–', '-').replace('—', '-').replace('−', '-')
return text

def clean_team_name(name):
if isinstance(name, str):
name = normalize_dashes(name)
return ' '.join(name.strip().split())
return name

def sanitize_pair_text(p):
if not isinstance(p, str):
return p
p = p.strip()
p = normalize_dashes(p)
p = re.sub(r"\s*->\s*&\s*[A-Za-z]:[\/].*", "", p) p = re.sub(r"\s*&\s*[A-Za-z]:[\\/].*", "", p)
return p.strip()

def parse_date_to_dt(raw_date):
if isinstance(raw_date, datetime):
return raw_date
if isinstance(raw_date, (float, int)):
try:
return datetime(1899, 12, 30) + timedelta(days=int(raw_date))
except:
return None
if isinstance(raw_date, str):
try:
if ' - ' in raw_date:
date_part = raw_date.split(' - ')[0].strip()
return datetime.strptime(date_part, '%d.%m.%Y')
return datetime.strptime(raw_date.strip(), '%d.%m.%Y')
except:
return None
return None

def to_float(x):
return float(str(x).replace(',', '.'))

def fmt2(x) -> str:
try:
return f"{to_float(x):.2f}"
except:
return str(x)

def strip_accents(s: str) -> str:
s = unicodedata.normalize("NFKD", s)
return "".join(ch for ch in s if not unicodedata.combining(ch))

ORG_TOKENS = {
"fc","cf","ad","cd","ud","sc","ac","afc","fk","sk","nk","as","ss","sv","if","bk","kf",
"cfc","fcd","f.c","c.f","a.d","u.d","c.d","s.c","a.c",
"club","clube","clubs","football","futbol","fútbol","calcio"
}

CONNECTOR_TOKENS = {
"de","del","la","las","los","el","da","do","di","du","der","den","van","von","the","of","and"
}

ABBREV_MAP = {
"dep": "deportivo",
"depor": "deportivo",
}

def canonical_team_key(name: str) -> str:
if not isinstance(name, str):
return ""
s = clean_team_name(name)
s = strip_accents(s.lower())
s = re.sub(r"[^0-9a-z\s]", " ", s)
tokens = [t for t in s.split() if t]

text
normalized = []
for t in tokens:
    t = ABBREV_MAP.get(t, t)
    if t in ORG_TOKENS:
        continue
    if t in CONNECTOR_TOKENS:
        continue
    normalized.append(t)

if not normalized:
    normalized = tokens

return " ".join(normalized).strip()

def key_tokens(key: str):
return [t for t in key.split() if t]

def match_score_by_tokens(user_key: str, rep_key: str) -> float:
if not user_key or not rep_key:
return 0.0
u_toks = set(key_tokens(user_key))
r_toks = set(key_tokens(rep_key))
if not u_toks or not r_toks:
return 0.0

text
inter = len(u_toks & r_toks)
if inter == 0:
    return 0.0

containment = inter / min(len(u_toks), len(r_toks))
dice = (2 * inter) / (len(u_toks) + len(r_toks))
seq = difflib.SequenceMatcher(None, user_key, rep_key).ratio()
return max(seq, dice, 0.98 * containment)

def build_team_normalization_maps(raw_rows):
key_to_counts = defaultdict(Counter)

text
for league, date, home, away, o1, ox, o2 in raw_rows:
    kh = canonical_team_key(home)
    ka = canonical_team_key(away)
    if kh:
        key_to_counts[kh][home] += 1
    if ka:
        key_to_counts[ka][away] += 1

rep_by_key = {}
alias_to_rep = {}

for key, cnt in key_to_counts.items():
    most = cnt.most_common()
    max_count = most[0][1]
    candidates = [name for name, c in most if c == max_count]
    rep = min(candidates, key=lambda x: (len(x), x.lower()))
    rep_by_key[key] = rep
    for name in cnt.keys():
        alias_to_rep[name] = rep

rep_names = set(rep_by_key.values())
rep_key_by_name = {rep: canonical_team_key(rep) for rep in rep_names}

return rep_by_key, alias_to_rep, rep_names, rep_key_by_name

def resolve_user_team_name(
user_name: str,
alias_to_rep,
rep_by_key,
rep_names,
rep_key_by_name,
fuzzy_min_ratio=0.84,
ambiguity_eps=0.02,
margin=0.08
):
cleaned = clean_team_name(user_name)
if cleaned in rep_names:
return cleaned, None

text
if cleaned in alias_to_rep:
    return alias_to_rep[cleaned], None

key = canonical_team_key(cleaned)
if key in rep_by_key:
    rep = rep_by_key[key]
    if rep != cleaned:
        return rep, f"Нормализация: '{cleaned}' -> '{rep}'"
    return rep, None

scored = []
for rep in rep_names:
    rep_key = rep_key_by_name.get(rep, "")
    score = match_score_by_tokens(key, rep_key)
    if score > 0:
        scored.append((score, rep))

if not scored:
    return cleaned, f"ВНИМАНИЕ: команда '{cleaned}' не найдена для нормализации (ключ='{key}')."

scored.sort(reverse=True, key=lambda x: x[0])
best_score, best_rep = scored[0]
second_score = scored[1][0] if len(scored) > 1 else 0.0
near_best = [rep for sc, rep in scored if sc >= best_score - ambiguity_eps]

if len(near_best) > 1 and best_score < 0.95:
    return cleaned, (
        f"ВНИМАНИЕ: '{cleaned}' неоднозначно (ключ='{key}'), возможные варианты: "
        f"{', '.join(near_best[:6])}" + (" ..." if len(near_best) > 6 else "")
    )

if best_score >= fuzzy_min_ratio and (best_score >= 0.95 or (best_score - second_score) >= margin):
    return best_rep, f"Fuzzy/token-нормализация: '{cleaned}' -> '{best_rep}' (score={best_score:.2f})"

return cleaned, f"ВНИМАНИЕ: команда '{cleaned}' не найдена для нормализации (ключ='{key}')."

def split_pair_robust(p: str):
p = normalize_dashes(p)
if " - " in p:
a, b = p.split(" - ", 1)
return a.strip(), b.strip()

text
parts = re.split(r"\s+[-]\s+", p)
if len(parts) == 2:
    return parts[0].strip(), parts[1].strip()

parts2 = re.split(r"\s*-\s*", p)
if len(parts2) == 2:
    return parts2[0].strip(), parts2[1].strip()

return None

===== signatures =====

def line_compact(s: str) -> str:
s = normalize_dashes(str(s))
s = re.sub(r"\s+", " ", s).strip()
return s

def signature_from_chain_base_lines(base_lines):
return "\n".join(line_compact(x) for x in base_lines)

def _plain_text(value) -> str:
if value is None:
return ""
if isinstance(value, CellRichText):
return "".join(tb.text for tb in value)
return str(value)

def signature_from_matches_cell(text):
if not text:
return ""
text = _plain_text(text)

text
lines = []
for ln in str(text).splitlines():
    ln = normalize_dashes(ln).strip()
    ln = re.sub(r"\s*$[^)]*$\s*$", "", ln).strip()
    ln = re.sub(r"\s+", " ", ln).strip()
    if ln:
        lines.append(ln)
return "\n".join(lines)

===================== RichText coloring =====================

INLINE_MONO = InlineFont(rFont="Consolas")
INLINE_HOME_HL = InlineFont(rFont="Consolas", b=True, color="1F4E79") # start-team as HOME
INLINE_AWAY_HL = InlineFont(rFont="Consolas", b=True, color="9C0006") # end-team as AWAY

def build_matches_rich_text(chain, start_team, end_team):
"""
CellRichText для колонки Matches.
Важно: переносы строк добавляем В КОНЕЦ последнего TextBlock строки,
чтобы Excel корректно показывал "в столбик".
"""
base_lines = [f"{m[1]} | {m[2]}-{m[3]}" for m in chain]
max_base_len = max((len(b) for b in base_lines), default=0)

text
blocks = []
for idx, match in enumerate(chain):
    _league, date, home, away, o1, ox, o2 = match

    base = f"{date} | {home}-{away}"
    pad_spaces = " " * (max_base_len - len(base) + 2)
    odds_str = f"({fmt2(o1)}, {fmt2(ox)}, {fmt2(o2)})"

    blocks.append(TextBlock(INLINE_MONO, f"{date} | "))
    blocks.append(TextBlock(INLINE_HOME_HL if home == start_team else INLINE_MONO, str(home)))
    blocks.append(TextBlock(INLINE_MONO, "-"))
    blocks.append(TextBlock(INLINE_AWAY_HL if away == end_team else INLINE_MONO, str(away)))

    # ✅ перенос строки добавляем сюда же (а не отдельным блоком)
    tail = f"{pad_spaces}{odds_str}"
    if idx != len(chain) - 1:
        tail += "\n"
    blocks.append(TextBlock(INLINE_MONO, tail))

return CellRichText(blocks)

===================== read matches =====================

df = pd.read_excel(excel_file_path)

raw_rows = []
for _, row in df.iterrows():
league = row[0]
date = row[1]
home = clean_team_name(row[2])
away = clean_team_name(row[3])
odds1 = row[4]
odds_draw = row[5]
odds2 = row[6]
raw_rows.append((league, date, home, away, odds1, odds_draw, odds2))

rep_by_key, alias_to_rep, rep_names, rep_key_by_name = build_team_normalization_maps(raw_rows)

matches = []
for league, date, home, away, odds1, odds_draw, odds2 in raw_rows:
home_rep = alias_to_rep.get(home, home)
away_rep = alias_to_rep.get(away, away)
matches.append((league, date, home_rep, away_rep, odds1, odds_draw, odds2))

===================== chains =====================

def find_chains(matches, start_team, end_team, min_depth=1, max_depth=3):
graph = {}
for match in matches:
league, date, home_team, away_team, odds1, odds_draw, odds2 = match
graph.setdefault(home_team, [])
graph.setdefault(away_team, [])
graph[home_team].append((away_team, match))
graph[away_team].append((home_team, match))

text
chains = []
queue = deque([(start_team, [], set())])

while queue:
    current_team, path, visited = queue.popleft()

    if current_team == end_team and path:
        if min_depth <= len(path) <= max_depth:
            chains.append(path)
        continue

    if len(path) >= max_depth:
        continue

    for neighbor, match in graph.get(current_team, []):
        if match not in visited:
            new_visited = visited.copy()
            new_visited.add(match)
            queue.append((neighbor, path + [match], new_visited))

return chains

===================== input pairs =====================

raw_input_teams = input(
"Введите либо 2 команды (как раньше), либо список пар через запятую.\n"
"Пример: Malaga - Granada, Granada - Albacete\n"
"Можно использовать '-', '–' или '—'.\n"
"Ввод: "
).strip()

raw_input_teams = sanitize_pair_text(raw_input_teams)

team_pairs = []
has_any_dash = '-' in raw_input_teams

if (',' in raw_input_teams) or has_any_dash:
parts = [p.strip() for p in re.split(r"[,\n;]+", raw_input_teams) if p.strip()]
for p in parts:
p = sanitize_pair_text(p)
if not p:
continue

text
    sp = split_pair_robust(p)
    if not sp:
        print(f"Пропуск (неверный формат пары, нужен 'Команда1 - Команда2'): {p}")
        continue

    a, b = sp
    a = clean_team_name(a)
    b = clean_team_name(b)
    if a and b:
        team_pairs.append((a, b))

if not team_pairs:
    start_team = clean_team_name(raw_input_teams)
    end_team = clean_team_name(input("Введите название команды назначения: ").strip())
    team_pairs = [(start_team, end_team)]

else:
start_team = clean_team_name(raw_input_teams)
end_team = clean_team_name(input("Введите название команды назначения: ").strip())
team_pairs = [(start_team, end_team)]

resolved_pairs = []
for a, b in team_pairs:
ra, wa = resolve_user_team_name(a, alias_to_rep, rep_by_key, rep_names, rep_key_by_name)
rb, wb = resolve_user_team_name(b, alias_to_rep, rep_by_key, rep_names, rep_key_by_name)
if wa:
print(wa)
if wb:
print(wb)
resolved_pairs.append((ra, rb))
team_pairs = resolved_pairs

===================== calculator =====================

class TeamStrengthCalculator:
def init(self):
self.games = []
self.equations_method_2 = []
self.teams = set()

text
def add_games(self, games):
    for g in games:
        league, date, home_team, away_team, home_odds, draw_odds, away_odds = g
        self.games.append({
            'league': league,
            'date': date,
            'home_team': clean_team_name(home_team),
            'away_team': clean_team_name(away_team),
            'home_odds': to_float(home_odds),
            'draw_odds': to_float(draw_odds),
            'away_odds': to_float(away_odds)
        })
        self.teams.add(clean_team_name(home_team))
        self.teams.add(clean_team_name(away_team))

def calculate_strength_difference_method_2(self):
    for game in self.games:
        league_name = game['league']
        home_team   = game['home_team']
        away_team   = game['away_team']
        home_odds   = game['home_odds']
        away_odds   = game['away_odds']

        threshold_home, threshold_away = league_thresholds.get(league_name, (2.40, 3.00))

        if home_odds < away_odds:
            if home_odds < threshold_home:
                home_strength = abs((100 / threshold_home) - (100 / home_odds))
                self.equations_method_2.append(
                    f"{home_team} = {away_team} + {home_strength:.3f}"
                )
            else:
                home_strength = abs((100 / home_odds) - (100 / threshold_home))
                self.equations_method_2.append(
                    f"{away_team} = {home_team} + {home_strength:.3f}"
                )
        else:
            if away_odds < threshold_away:
                away_strength = abs((100 / threshold_away) - (100 / away_odds))
                self.equations_method_2.append(
                    f"{away_team} = {home_team} + {away_strength:.3f}"
                )
            else:
                away_strength = abs((100 / away_odds) - (100 / threshold_away))
                self.equations_method_2.append(
                    f"{home_team} = {away_team} + {away_strength:.3f}"
                )

def _safe_sympy_symbol(self, team: str):
    base = canonical_team_key(team).replace(" ", "_")
    base = re.sub(r"[^0-9a-zA-Z_]", "_", base)
    if not base:
        base = "team"
    if base[0].isdigit():
        base = "t_" + base
    return symbols(base)

def simplify_with_sympy_method2(self, start_team, end_team):
    if not self.games:
        return "", ["Нет матчей для расчета."], ("", "")

    first_league = self.games[0]['league']
    threshold_home, threshold_away = league_thresholds.get(first_league, (2.40, 3.00))

    team_symbols = {}
    for team in self.teams:
        if team == start_team:
            team_symbols[team] = 0
        else:
            team_symbols[team] = self._safe_sympy_symbol(team)

    sympy_equations = []
    for equation in self.equations_method_2:
        parts = equation.split('=')
        if len(parts) == 2 and '+' in parts[1]:
            left_team, right = parts[0].strip(), parts[1].strip()
            right_team, strength_diff = right.split('+')
            right_team = right_team.strip()
            strength_diff = float(strength_diff.strip())
            left_sym = team_symbols[left_team]
            right_sym = team_symbols[right_team]
            sympy_equations.append(Eq(left_sym, right_sym + strength_diff))

    symbols_list = [sym for sym in team_symbols.values() if sym != 0]
    solution = solve(sympy_equations, symbols_list, dict=True) if sympy_equations else []

    end_symbol = team_symbols.get(end_team)
    if not solution or end_symbol is None:
        return "", ["Нет решения для метода 2."], (f"{threshold_home:.2f}", f"{threshold_away:.2f}")

    sol = solution[0]
    if end_symbol not in sol:
        return "", [f"Команда {end_team} не найдена в решении метода 2."], (f"{threshold_home:.2f}", f"{threshold_away:.2f}")

    try:
        end_value_numeric = float(sol[end_symbol].evalf())
    except Exception:
        return "", [f"Не удалось вычислить численное значение силы {end_team} в методе 2."], (f"{threshold_home:.2f}", f"{threshold_away:.2f}")

    strength_diff = -end_value_numeric
    new_odds_start = 100 / ((100 / threshold_home) + strength_diff)
    new_odds_end   = 100 / ((100 / threshold_home) - strength_diff)

    method2_str = (
        f"{start_team} = {end_team} + {strength_diff:.3f}; "
        f"{start_team} odds = {new_odds_start:.3f}; {end_team} odds = {new_odds_end:.3f}"
    )
    return method2_str, [], (f"{threshold_home:.2f}", f"{threshold_away:.2f}")

===================== Excel styles =====================

thin = Side(style="thin", color="000000")
medium = Side(style="medium", color="000000")
BORDER_THIN = Border(left=thin, right=thin, top=thin, bottom=thin)
BORDER_MEDIUM_TOP = Border(left=thin, right=thin, top=medium, bottom=thin)

ALIGN_WRAP_TOP = Alignment(wrap_text=True, vertical="top")
ALIGN_CENTER = Alignment(horizontal="center", vertical="center", wrap_text=True)

FONT_HEADER = Font(bold=True, size=11)
FONT_BOLD = Font(bold=True)
FONT_MONO = Font(name="Consolas")

FILL_HEADER = PatternFill("solid", fgColor="D9E1F2")
FILL_PAIR = PatternFill("solid", fgColor="E2EFDA")
FILL_NEW_CHAIN = PatternFill("solid", fgColor="FFF2CC")
FILL_NONE = PatternFill()

EXPECTED_HEADERS = [
"RUN",
"Request #",
"Start",
"End",
"Th home",
"Th away",
"Avg Date",
"Matches (Date | Home-Away (1,X,2))",
"Result"
]

def init_results_workbook(output_excel_path):
if os.path.exists(output_excel_path):
wb = load_workbook(output_excel_path)
else:
wb = Workbook()

text
if "Results" in wb.sheetnames:
    ws = wb["Results"]
else:
    ws = wb.active
    ws.title = "Results"

if ws.max_row == 1 and ws["A1"].value is None:
    ws.append(EXPECTED_HEADERS)
else:
    current = [ws.cell(row=1, column=i).value for i in range(1, len(EXPECTED_HEADERS) + 1)]
    if current != EXPECTED_HEADERS:
        base = "Results_v2"
        name = base
        i = 2
        while name in wb.sheetnames:
            name = f"{base}_{i}"
            i += 1
        ws = wb.create_sheet(name)
        ws.append(EXPECTED_HEADERS)

for col in range(1, len(EXPECTED_HEADERS) + 1):
    c = ws.cell(row=1, column=col)
    c.font = FONT_HEADER
    c.fill = FILL_HEADER
    c.alignment = ALIGN_CENTER
    c.border = BORDER_THIN

widths = [18, 9, 18, 18, 9, 9, 11, 70, 55]
for i, w in enumerate(widths, start=1):
    ws.column_dimensions[get_column_letter(i)].width = w

ws.freeze_panes = "A2"
return wb, ws

def apply_row_style(ws, r, is_pair_first_row: bool, is_new_chain: bool):
for col in range(1, len(EXPECTED_HEADERS) + 1):
cell = ws.cell(row=r, column=col)
cell.alignment = ALIGN_WRAP_TOP
cell.border = BORDER_MEDIUM_TOP if is_pair_first_row else BORDER_THIN

text
    cell.fill = FILL_NONE

    if is_new_chain:
        cell.fill = FILL_NEW_CHAIN
    else:
        if is_pair_first_row and col in (2, 3, 4):
            cell.fill = FILL_PAIR

    if is_pair_first_row and col in (2, 3, 4):
        cell.font = FONT_BOLD
    elif col == 8:
        cell.font = FONT_MONO
    else:
        cell.font = Font()

def set_row_height_from_matches(ws, r, matches_text):
matches_text = _plain_text(matches_text)
line_count = matches_text.count("\n") + 1 if matches_text else 1
ws.row_dimensions[r].height = min(max(18, 14 * line_count), 260)

def append_row(ws, row_values, is_new_pair=False, is_new_chain=False):
ws.append(row_values)
r = ws.max_row
apply_row_style(ws, r, is_new_pair, is_new_chain)
set_row_height_from_matches(ws, r, row_values[7])

def find_last_segment_for_pair(ws, start_team: str, end_team: str):
start_col = 3
end_col = 4

text
segments = []
in_seg = False
seg_start = None

for r in range(2, ws.max_row + 1):
    s = ws.cell(row=r, column=start_col).value
    e = ws.cell(row=r, column=end_col).value
    s = clean_team_name(s) if s else ""
    e = clean_team_name(e) if e else ""

    if s == start_team and e == end_team:
        if not in_seg:
            in_seg = True
            seg_start = r
    else:
        if in_seg:
            segments.append((seg_start, r - 1))
            in_seg = False
            seg_start = None

if in_seg:
    segments.append((seg_start, ws.max_row))

if not segments:
    return None

return segments[-1]

===================== MAIN =====================

run_dt = datetime.now()
run_stamp = run_dt.strftime("%d.%m.%Y %H:%M:%S")

out_dir = os.path.dirname(excel_file_path) if os.path.dirname(excel_file_path) else os.getcwd()
output_excel_path = os.path.join(out_dir, "wtp_results.xlsx")

wb, ws = init_results_workbook(output_excel_path)

for request_num, (start_team, end_team) in enumerate(team_pairs, start=1):
chains = find_chains(matches, start_team, end_team)

text
unique_chains = []
seen = set()
for ch in chains:
    t = tuple(ch)
    if t not in seen:
        seen.add(t)
        unique_chains.append(ch)
chains = unique_chains

desired_rows = []

if not chains:
    desired_rows.append({
        "signature": "",
        "values": [run_stamp, request_num, start_team, end_team, "", "", "", "", ""]
    })
else:
    groups = {}
    for chain in chains:
        L = len(chain)
        groups.setdefault(L, [])
        dts = [parse_date_to_dt(m[1]) for m in chain]
        dts = [d for d in dts if d]
        avg_date = datetime.fromtimestamp(sum(d.timestamp() for d in dts) / len(dts)) if dts else datetime.min
        groups[L].append({"chain": chain, "avg_date": avg_date})

    for L in groups:
        groups[L] = sorted(groups[L], key=lambda x: x["avg_date"], reverse=True)

    for L in sorted(groups.keys()):
        for info in groups[L]:
            chain = info["chain"]
            avg_date = info["avg_date"]
            avg_date_str = avg_date.strftime("%d.%m.%Y") if avg_date != datetime.min else ""

            games = []
            base_lines = []
            used_league_for_threshold = None

            for match in chain:
                league, date, home, away, o1, ox, o2 = match
                games.append(match)
                if used_league_for_threshold is None:
                    used_league_for_threshold = league
                base_lines.append(f"{date} | {home}-{away}")

            chain_sig = signature_from_chain_base_lines(base_lines)

            matches_cell_value = build_matches_rich_text(chain, start_team, end_team)

            th_home, th_away = league_thresholds.get(used_league_for_threshold, (2.40, 3.00))
            th_home_str = f"{th_home:.2f}"
            th_away_str = f"{th_away:.2f}"

            calc = TeamStrengthCalculator()
            calc.add_games(games)
            calc.calculate_strength_difference_method_2()
            result2, warnings, _ths = calc.simplify_with_sympy_method2(start_team, end_team)

            desired_rows.append({
                "signature": chain_sig,
                "values": [
                    run_stamp,
                    request_num,
                    start_team,
                    end_team,
                    th_home_str,
                    th_away_str,
                    avg_date_str,
                    matches_cell_value,
                    result2
                ]
            })

seg = find_last_segment_for_pair(ws, start_team, end_team)

if seg is None:
    first = True
    for item in desired_rows:
        append_row(ws, item["values"], is_new_pair=first, is_new_chain=False)
        first = False
    continue

seg_start, seg_end = seg
current_len = seg_end - seg_start + 1
desired_len = len(desired_rows)

existing_sigs = set()
for r in range(seg_start, seg_end + 1):
    sig = signature_from_matches_cell(ws.cell(row=r, column=8).value)
    existing_sigs.add(sig)

if desired_len > current_len:
    insert_at = seg_end + 1
    ws.insert_rows(insert_at, amount=(desired_len - current_len))
    seg_end += (desired_len - current_len)
elif desired_len < current_len:
    delete_from = seg_start + desired_len
    ws.delete_rows(delete_from, amount=(current_len - desired_len))
    seg_end = seg_start + desired_len - 1

for i, item in enumerate(desired_rows):
    r = seg_start + i
    vals = item["values"]
    sig = item["signature"]

    is_pair_first_row = (i == 0)
    is_new_chain = (sig != "" and sig not in existing_sigs)

    for col_idx, v in enumerate(vals, start=1):
        ws.cell(row=r, column=col_idx, value=v)

    apply_row_style(ws, r, is_pair_first_row, is_new_chain)
    set_row_height_from_matches(ws, r, vals[7])

wb.save(output_excel_path)
print(f"\nГотово. Результаты обновлены в Excel:\n{output_excel_path}") изучи этот код и напиши почему при сохранении новых данных в excel в предыдущем сохраненном листе пропадает цветовая подсветка команд