python
import os
import re
import unicodedata
import difflib
from collections import deque, defaultdict, Counter
from datetime import datetime, timedelta

import pandas as pd  # можно оставить (ниже не используется для чтения исходника)
from sympy import symbols, Eq, solve

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"

# ✅ NEW: дефолтный коэффициент "перевертыша" (если для лиги не задан свой)
DEFAULT_FLIP_K = 2.6
# =====================================================


# ✅ ВАЖНО:
# Теперь можно задавать для лиги НЕ ТОЛЬКО пороги (Th home / Th away),
# но и коэффициент перевертыша (flip_k).
# Формат значения:
#   "League Name": (th_home, th_away)              -> flip_k возьмётся DEFAULT_FLIP_K
#   "League Name": (th_home, th_away, flip_k)     -> flip_k будет индивидуальным
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),

    # ✅ Пример: у Segunda Spain свой индекс 2.6 (как у тебя)
    "Spain Segunda": (2.11, 4.02, 2.60),

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

# ✅ NEW: единая функция получения параметров лиги (пороги + flip_k)
def get_league_params(league_name: str):
    """
    Возвращает (th_home, th_away, flip_k)
    - если лига не найдена: (2.40, 3.00, DEFAULT_FLIP_K)
    - если задано 2 числа: flip_k = DEFAULT_FLIP_K
    - если задано 3 числа: flip_k берётся из словаря
    """
    v = league_thresholds.get(league_name)
    if not v:
        return 2.40, 3.00, DEFAULT_FLIP_K
    if isinstance(v, (list, tuple)) and len(v) >= 3:
        return float(v[0]), float(v[1]), float(v[2])
    return float(v[0]), float(v[1]), DEFAULT_FLIP_K


# ===================== 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=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 row in raw_rows:
        league, date, home, away = row[0], row[1], row[2], row[3]
        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*$", "", ln).strip()
        ln = re.sub(r"\s+", " ", ln).strip()
        if ln:
            lines.append(ln)
    return "\n".join(lines)


# ===================== NEW: чтение исходника + стили odds =====================
def _rgb6_from_color_obj(color_obj):
    """Возвращает RGB (6 символов) или None."""
    if color_obj is None:
        return None
    try:
        if getattr(color_obj, "type", None) == "rgb" and getattr(color_obj, "rgb", None):
            rgb = color_obj.rgb
            # часто приходит ARGB: 'FF112233'
            if isinstance(rgb, str) and len(rgb) == 8:
                rgb = rgb[2:]
            if isinstance(rgb, str) and len(rgb) == 6:
                return rgb.upper()
    except Exception:
        pass
    return None

def _extract_cell_emphasis(cell):
    """
    Достаём подсветку из ячейки:
    - сначала цвет шрифта
    - если нет, но есть заливка — берём цвет заливки (как цвет текста, т.к. фон в rich text нельзя)
    - bold переносим
    """
    if cell is None:
        return (None, False)

    bold = bool(getattr(cell.font, "b", False)) if cell.font else False

    font_rgb = _rgb6_from_color_obj(getattr(cell.font, "color", None)) if cell.font else None
    if font_rgb:
        return (font_rgb, bold)

    fill = getattr(cell, "fill", None)
    if fill and getattr(fill, "patternType", None):
        fill_rgb = _rgb6_from_color_obj(getattr(fill, "fgColor", None))
        if fill_rgb:
            return (fill_rgb, True or bold)

    # можно считать "подвохом" ещё и comment
    if getattr(cell, "comment", None) is not None:
        return ("7030A0", True)  # фиолетовый маркер

    return (None, bold)

def read_matches_with_odds_styles(path, sheet_name=None):
    """
    Возвращает raw_rows:
    (league, date, home, away, o1, ox, o2, odds_style)
    odds_style = (c1,b1,cx,bx,c2,b2) где c* = RGB6 или None
    """
    wb = load_workbook(path, data_only=True)
    ws = wb[sheet_name] if sheet_name and sheet_name in wb.sheetnames else wb.active

    # Попытка определить, есть ли заголовок
    start_row = 1
    a1 = ws.cell(row=1, column=1).value
    b1 = ws.cell(row=1, column=2).value
    if isinstance(a1, str) and isinstance(b1, str):
        # если похоже на header
        if ("league" in a1.lower() or "лига" in a1.lower() or "date" in b1.lower() or "дата" in b1.lower()):
            start_row = 2

    rows = []
    for r in range(start_row, ws.max_row + 1):
        league = ws.cell(row=r, column=1).value
        date   = ws.cell(row=r, column=2).value
        home   = ws.cell(row=r, column=3).value
        away   = ws.cell(row=r, column=4).value
        o1     = ws.cell(row=r, column=5).value
        ox     = ws.cell(row=r, column=6).value
        o2     = ws.cell(row=r, column=7).value

        # пропуск пустых строк
        if league is None and date is None and home is None and away is None:
            continue

        home = clean_team_name(home)
        away = clean_team_name(away)

        # стили odds (E/F/G)
        c1, b1_ = _extract_cell_emphasis(ws.cell(row=r, column=5))
        cx, bx_ = _extract_cell_emphasis(ws.cell(row=r, column=6))
        c2, b2_ = _extract_cell_emphasis(ws.cell(row=r, column=7))
        odds_style = (c1, b1_, cx, bx_, c2, b2_)

        rows.append((league, date, home, away, o1, ox, o2, odds_style))

    return rows


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

# NEW: кэш InlineFont под цвет/жирность, чтобы не плодить объекты
_INLINE_FONT_CACHE = {}

def get_inline_font(color_rgb6=None, bold=False):
    key = (color_rgb6 or "", bool(bold))
    if key in _INLINE_FONT_CACHE:
        return _INLINE_FONT_CACHE[key]
    kwargs = {"rFont": "Consolas"}
    if bold:
        kwargs["b"] = True
    if color_rgb6:
        kwargs["color"] = color_rgb6
    f = InlineFont(**kwargs)
    _INLINE_FONT_CACHE[key] = f
    return f

def build_matches_rich_text(chain, start_team, end_team):
    """
    CellRichText для колонки Matches.
    odds_style (если есть) переносит подсветку чисел (1,X,2) из исходного results.xlsx.
    """
    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):
        # match может быть старого вида (7 полей) из rehydrate — тогда style пустой
        if len(match) >= 8:
            _league, date, home, away, o1, ox, o2, odds_style = match
        else:
            _league, date, home, away, o1, ox, o2 = match
            odds_style = (None, False, None, False, None, False)

        base = f"{date} | {home}-{away}"
        pad_spaces = " " * max(0, (max_base_len - len(base) + 2))

        # odds style unpack
        c1, b1_, cx, bx_, c2, b2_ = odds_style

        # линия: "date | Home-Away<pad>(o1, ox, 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)))
        blocks.append(TextBlock(INLINE_MONO, pad_spaces + "("))

        blocks.append(TextBlock(get_inline_font(c1, b1_), fmt2(o1)))
        blocks.append(TextBlock(INLINE_MONO, ", "))
        blocks.append(TextBlock(get_inline_font(cx, bx_), fmt2(ox)))
        blocks.append(TextBlock(INLINE_MONO, ", "))
        blocks.append(TextBlock(get_inline_font(c2, b2_), fmt2(o2)))

        tail = ")"
        if idx != len(chain) - 1:
            tail += "\n"
        blocks.append(TextBlock(INLINE_MONO, tail))

    return CellRichText(blocks)


# ✅ NEW: RichText для Result (жирный "перевертыш" в скобках)
INLINE_RES_NORM = InlineFont(rFont="Calibri")
INLINE_RES_BOLD = InlineFont(rFont="Calibri", b=True)

def build_result_rich_text(base_result: str, flip_team: str, flip_hi: float, flip_lo: float):
    if not base_result:
        return base_result
    if not flip_team:
        return base_result
    tail = f" (Перевертыш {flip_team} = {flip_hi:.2f}; {flip_lo:.2f})"
    return CellRichText([
        TextBlock(INLINE_RES_NORM, str(base_result)),
        TextBlock(INLINE_RES_BOLD, tail),
    ])


# ===================== OPTIONAL: восстановление rich text (страховка) =====================
# FIX: корректный регэксп odds в конце строки
_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)"
    Возвращает кортеж: ("", date, home, away, o1, ox, o2, odds_style)
    odds_style восстановить нельзя -> None
    """
    if not line:
        return None
    line = normalize_dashes(str(line)).strip()
    if not line:
        return None

    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)

    odds_style = (None, False, None, False, None, False)
    return ("", date_part, home, away, o1, ox, o2, odds_style)

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 (NEW: openpyxl со стилями odds) =====================
raw_rows = read_matches_with_odds_styles(excel_file_path)

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

matches = []
for row in raw_rows:
    league, date, home, away, odds1, odds_draw, odds2, odds_style = row
    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, odds_style))


# ===================== 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, odds_style = 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']

            # ✅ NEW: берём пороги через get_league_params
            threshold_home, threshold_away, _flip_k = get_league_params(league_name)

            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):
        # ✅ возвращаем: (result_str, warnings, thresholds, flip_meta)
        if not self.games:
            return "", ["Нет матчей для расчета."], ("", ""), None

        first_league = self.games[0]['league']

        # ✅ NEW: берём и пороги, и flip_k одной функцией
        threshold_home, threshold_away, flip_k = get_league_params(first_league)

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

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

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

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

        # ✅ NEW: считаем "перевертыш" от минимального odds, но уже по flip_k конкретной лиги
        try:
            if new_odds_start <= new_odds_end:
                min_team = start_team
                min_odds = float(new_odds_start)
            else:
                min_team = end_team
                min_odds = float(new_odds_end)

            # формулы как у тебя:
            # (min_odds-1)/k + 1
            # (min_odds-1)*k + 1
            flip_hi = (min_odds - 1.0) * flip_k + 1.0
            flip_lo = (min_odds - 1.0) / flip_k + 1.0
            flip_meta = (min_team, flip_hi, flip_lo)
        except Exception:
            flip_meta = None

        return method2_str, [], (f"{threshold_home:.2f}", f"{threshold_away:.2f}"), flip_meta


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

# ✅ страховка на rich text в старых строках
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, odds_style = match
                    # калькулятору стили не нужны
                    games.append((league, date, home, away, o1, ox, o2))
                    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 с переносом подсветки odds из исходника
                matches_cell_value = build_matches_rich_text(chain, start_team, end_team)

                # ✅ NEW: пороги берём через get_league_params (flip_k тут не нужен, но можно)
                th_home, th_away, _flip_k = get_league_params(used_league_for_threshold)
                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()

                # ✅ flip_meta внутри calc уже посчитан с коэффициентом своей лиги
                result2, warnings, _ths, flip_meta = calc.simplify_with_sympy_method2(start_team, end_team)

                # ✅ жирный "перевертыш" в скобках (rich text)
                if result2 and flip_meta:
                    flip_team, flip_hi, flip_lo = flip_meta
                    result_cell_value = build_result_rich_text(result2, flip_team, flip_hi, flip_lo)
                else:
                    result_cell_value = result2

                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,
                        result_cell_value
                    ]
                })

    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)

        # ✅ чуть безопаснее для rich text: сначала берём ячейку, потом присваиваем value
        for col_idx, v in enumerate(vals, start=1):
            c = ws.cell(row=r, column=col_idx)
            c.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}")