Проблема именно в rich text: ты записываешь подсветку как CellRichText, но при следующем запуске открываешь wtp_results.xlsx через load_workbook() без rich_text=True. В этом режиме openpyxl не загружает rich text runs, и при сохранении книги перезаписывает sharedStrings уже без форматирования — из-за этого на старых листах/строках пропадает подсветка.

✅ Главный фикс: load_workbook(path, rich_text=True).
Я также добавил страховочный восстановитель, который, если вдруг в файле уже оказались “плоские” строки (без rich text), может пересобрать Matches обратно в CellRichText по колонкам Start/End.

Ниже — полный исправленный код (вставляй целиком):

python
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:
            # Excel date serial
            return datetime(1899, 12, 30) + timedelta(days=float(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]

    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

    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)

    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

    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()

    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):
    """
    Используется для сравнения цепочек (без odds).
    """
    if not text:
        return ""
    text = _plain_text(text)

    lines = []
    for ln in str(text).splitlines():
        ln = normalize_dashes(ln).strip()
        # ✅ корректно убираем "(...)" в конце строки
        ln = re.sub(r"\s*$\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)

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

        base = f"{date} | {home}-{away}"
        pad_spaces = " " * max(0, (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)


# ===================== OPTIONAL: восстановление rich text (страховка) =====================
_odds_re = re.compile(r"$\s*([0-9]+(?:[.,][0-9]+)?)\s*,\s*([0-9]+(?:[.,][0-9]+)?)\s*,\s*([0-9]+(?:[.,][0-9]+)?)\s*$\s*$")

def parse_match_line_to_tuple(line: str):
    """
    Парсит строку вида:
    "date | Home-Away    (1.94, 3.37, 4.50)"
    Возвращает кортеж для build_matches_rich_text: ("", date, home, away, o1, ox, o2)
    """
    if not line:
        return None
    line = normalize_dashes(str(line)).strip()
    if not line:
        return None

    # date | rest
    if "|" in line:
        date_part, rest = line.split("|", 1)
        date_part = date_part.strip()
        rest = rest.strip()
    else:
        date_part, rest = "", line

    o1 = ox = o2 = ""
    m = _odds_re.search(rest)
    if m:
        o1, ox, o2 = m.group(1), m.group(2), m.group(3)
        rest = rest[:m.start()].rstrip()

    sp = split_pair_robust(rest)
    if not sp:
        return None
    home, away = sp
    home = clean_team_name(home)
    away = clean_team_name(away)
    return ("", date_part, home, away, o1, ox, o2)

def rehydrate_matches_column_to_rich_text(ws):
    """
    Если по какой-то причине в старых строках Matches стал обычным текстом,
    пересобираем его обратно в CellRichText по Start/End.
    НЕ трогаем ячейки, которые уже CellRichText.
    """
    START_COL = 3
    END_COL = 4
    MATCHES_COL = 8

    for r in range(2, ws.max_row + 1):
        start_team = ws.cell(row=r, column=START_COL).value
        end_team = ws.cell(row=r, column=END_COL).value
        if not start_team or not end_team:
            continue
        start_team = clean_team_name(start_team)
        end_team = clean_team_name(end_team)

        cell = ws.cell(row=r, column=MATCHES_COL)
        v = cell.value
        if v is None or v == "":
            continue

        if isinstance(v, CellRichText):
            continue  # уже rich text — отлично

        text = _plain_text(v)
        lines = [ln.strip() for ln in str(text).splitlines() if ln.strip()]
        chain = []
        for ln in lines:
            tup = parse_match_line_to_tuple(ln)
            if tup:
                chain.append(tup)

        if chain:
            cell.value = build_matches_rich_text(chain, start_team, end_team)


# ===================== 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))

    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

        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()

    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):
    """
    ✅ КЛЮЧЕВОЕ ИЗМЕНЕНИЕ:
    load_workbook(..., rich_text=True) — иначе openpyxl «теряет» rich text при загрузке
    и при сохранении старая подсветка пропадает.
    """
    if os.path.exists(output_excel_path):
        wb = load_workbook(output_excel_path, rich_text=True)  # ✅ FIX
    else:
        wb = Workbook()

    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

        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

    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)

# ✅ Страховка: если в старых строках Matches вдруг стали обычным текстом — восстановим rich text
# (не трогает уже корректные CellRichText)
rehydrate_matches_column_to_rich_text(ws)

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

    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}")