import pandas as pd
import re
import pyperclip
import json
from bs4 import BeautifulSoup
from tkinter import *
from tkinter import ttk, filedialog, messagebox, simpledialog
from collections import defaultdict
from rapidfuzz import process, fuzz
import os


def normalize_variant(val):
    val = re.sub(r'\s*\(\s*', ' (', val)
    val = re.sub(r'\s*\)\s*', ') ', val)
    val = re.sub(r'\s+', ' ', val).strip()
    return val


class ColumnSelectorDialog:
    def __init__(self, parent, columns, current_selection, callback):
        self.parent = parent
        self.columns = columns
        self.current_selection = current_selection
        self.callback = callback
        self.top = Toplevel(parent)
        self.top.title("Выбор дополнительных столбцов")
        self.top.grab_set()
        frame = Frame(self.top)
        frame.pack(padx=10, pady=10)
        self.vars = {}
        selected_set = set(self.current_selection)
        for idx, col in enumerate(columns):
            var = BooleanVar()
            var.set(str(idx + 1) in selected_set)
            cb = Checkbutton(frame, text=f"{idx + 1}: {col}", variable=var)
            cb.pack(anchor='w')
            self.vars[idx + 1] = var
        button_frame = Frame(self.top)
        button_frame.pack(pady=5)
        Button(button_frame, text="Применить", width=12, command=self.apply).pack(side=LEFT, padx=5)
        Button(button_frame, text="Отмена", width=12, command=self.top.destroy).pack(side=LEFT)
        self.top.transient(parent)
        self.top.wait_window(self.top)

    def apply(self):
        selected = [str(i) for i, var in self.vars.items() if var.get()]
        self.callback(selected)
        self.top.destroy()


class ToolTip:
    def __init__(self, widget, text):
        self.widget = widget
        self.text = text
        self.tooltip_window = None
        widget.bind("<Enter>", self.show_tooltip)
        widget.bind("<Leave>", self.hide_tooltip)

    def show_tooltip(self, event=None):
        if self.tooltip_window or not self.text:
            return
        screen_width = self.widget.winfo_screenwidth()
        screen_height = self.widget.winfo_screenheight()
        x = self.widget.winfo_pointerx() + 10
        y = self.widget.winfo_pointery() + 10
        temp = Toplevel(self.widget)
        temp.wm_overrideredirect(True)
        label = Label(temp, text=self.text, background="#ffffe0", relief='solid', borderwidth=1,
                      font=("tahoma", "8", "normal"))
        label.pack()
        temp.update_idletasks()
        width = temp.winfo_width()
        height = temp.winfo_height()
        temp.destroy()
        if x + width + 10 > screen_width:
            x = screen_width - width - 10
        if y + height + 10 > screen_height:
            y = screen_height - height - 10
        self.tooltip_window = tw = Toplevel(self.widget)
        tw.wm_overrideredirect(True)
        tw.wm_geometry(f"+{x}+{y}")
        label = Label(tw, text=self.text, background="#ffffe0", relief='solid', borderwidth=1,
                      font=("tahoma", "8", "normal"))
        label.pack(ipadx=2, ipady=1)

    def hide_tooltip(self, event=None):
        tw = self.tooltip_window
        if tw:
            tw.destroy()
            self.tooltip_window = None


class CaseNormalizationDialog:
    def __init__(self, parent, data, save_callback):
        self.parent = parent
        self.data = data
        self.save_callback = save_callback

        self.top = Toplevel(parent)
        self.top.title("Нормализация регистра значений")
        self.top.geometry("700x500")
        self.top.grab_set()

        self.frame = Frame(self.top)
        self.frame.pack(fill=BOTH, expand=True)

        columns = ('lowercase', 'variants', 'correct')
        self.tree = ttk.Treeview(self.frame, columns=columns, show='headings')
        self.tree.pack(side=LEFT, fill=BOTH, expand=True)

        self.tree.heading('lowercase', text='Значение (нижний регистр)')
        self.tree.heading('variants', text='Варианты')
        self.tree.heading('correct', text='Корректный вариант (редактируемый)')
        self.tree.column('lowercase', width=150)
        self.tree.column('variants', width=300)
        self.tree.column('correct', width=200)

        vsb = Scrollbar(self.frame, orient="vertical", command=self.tree.yview)
        vsb.pack(side=LEFT, fill=Y)
        self.tree.configure(yscrollcommand=vsb.set)

        for lower_val, variants_set in sorted(self.data.items()):
            normalized_variants = set(normalize_variant(v) for v in variants_set)
            variants_joined = "; ".join(sorted(normalized_variants))
            correct_val = sorted(normalized_variants)[0] if normalized_variants else lower_val
            self.tree.insert('', END, values=(lower_val, variants_joined, correct_val))

        self.tree.bind('<Double-1>', self.on_double_click)

        btn_frame = Frame(self.top)
        btn_frame.pack(fill=X, pady=5)

        self.btn_save = Button(btn_frame, text="Сохранить и применить", command=self.on_save)
        self.btn_save.pack(side=RIGHT, padx=5)
        self.btn_cancel = Button(btn_frame, text="Отмена", command=self.top.destroy)
        self.btn_cancel.pack(side=RIGHT)

        self.edit_entry = None

    def on_double_click(self, event):
        region = self.tree.identify("region", event.x, event.y)
        if region != 'cell':
            return
        column = self.tree.identify_column(event.x)
        if column != '#3':
            return
        row_id = self.tree.identify_row(event.y)
        if not row_id:
            return
        x, y, width, height = self.tree.bbox(row_id, column)
        values = list(self.tree.item(row_id, 'values'))
        current_value = values[2]
        if self.edit_entry:
            self.edit_entry.destroy()
            self.edit_entry = None
        self.edit_entry = Entry(self.tree)
        self.edit_entry.insert(0, current_value)
        self.edit_entry.select_range(0, END)
        self.edit_entry.focus_set()
        self.edit_entry.place(x=x, y=y, width=width, height=height)
        self.edit_entry.bind('<Return>', lambda e: self.save_edit(row_id))
        self.edit_entry.bind('<FocusOut>', lambda e: self.save_edit(row_id))

    def save_edit(self, row_id):
        if not self.edit_entry:
            return
        new_val = self.edit_entry.get().strip()
        values = list(self.tree.item(row_id, 'values'))
        if new_val:
            values[2] = new_val
            self.tree.item(row_id, values=values)
        self.edit_entry.destroy()
        self.edit_entry = None

    def on_save(self):
        result = {}
        for item in self.tree.get_children():
            lower_val, variants_str, correct_val = self.tree.item(item, 'values')
            norm_correct_val = normalize_variant(correct_val)
            result[lower_val] = norm_correct_val
        self.save_callback(result)
        self.top.destroy()


class ReplacementRulesEditorDialog:
    """
    Окно для редактирования правил замены — шаблоны и замены с возможностью включения/выключения каждого правила.
    """

    def __init__(self, parent, rules, save_callback):
        """
        :param parent: родительский tkinter виджет
        :param rules: список правил [(enabled, pattern, replacement, description), ...]
                      либо [(pattern, replacement, description), ...] если enabled отсутствует - считается True
        :param save_callback: функция обратного вызова с обновленным списком правил
        """
        self.parent = parent
        self.save_callback = save_callback

        # Нормализуем входные правила: если нет enabled, добавляем True
        self.rules = []
        for rule in rules:
            if len(rule) == 4:
                self.rules.append(rule)
            elif len(rule) == 3:
                self.rules.append((True, *rule))
            else:
                # на всякий случай, ожидаем минимум 3 элемента
                self.rules.append((True, rule[0], rule[1], rule[2] if len(rule)>2 else ""))

        self.top = Toplevel(parent)
        self.top.title("Редактор правил замены")
        self.top.geometry("900x600")
        self.top.grab_set()

        self.frame = Frame(self.top)
        self.frame.pack(fill=BOTH, expand=True, padx=10, pady=10)

        columns = ('enabled', 'pattern', 'replacement', 'description')
        self.tree = ttk.Treeview(self.frame, columns=columns, show='headings')
        self.tree.pack(side=LEFT, fill=BOTH, expand=True)

        self.tree.heading('enabled', text='Вкл.')
        self.tree.heading('pattern', text='Шаблон')
        self.tree.heading('replacement', text='Замена')
        self.tree.heading('description', text='Описание')

        self.tree.column('enabled', width=40, anchor=CENTER)
        self.tree.column('pattern', width=250, anchor=W)
        self.tree.column('replacement', width=250, anchor=W)
        self.tree.column('description', width=280, anchor=W)

        vsb = Scrollbar(self.frame, orient="vertical", command=self.tree.yview)
        vsb.pack(side=LEFT, fill=Y)
        self.tree.configure(yscrollcommand=vsb.set)

        # Заполнить дерево значениями с символом для enabled
        for rule in self.rules:
            enabled, pattern, replacement, description = rule
            enabled_symbol = "☑" if enabled else "☐"
            self.tree.insert('', END, values=(enabled_symbol, pattern, replacement, description))

        # Привязываем событие клика для переключения enabled
        self.tree.bind('<Button-1>', self.on_treeview_click)
        # Для редактирования ячеек остальных колонок на двойной клик
        self.tree.bind('<Double-1>', self.on_double_click)

        btn_frame = Frame(self.top)
        btn_frame.pack(fill=X, pady=5)

        Button(btn_frame, text="Добавить", command=self.add_rule).pack(side=LEFT, padx=5)
        Button(btn_frame, text="Удалить", command=self.delete_selected).pack(side=LEFT, padx=5)
        Button(btn_frame, text="Импорт из Excel", command=self.import_from_excel).pack(side=LEFT, padx=5)
        Button(btn_frame, text="Экспорт в Excel", command=self.export_to_excel).pack(side=LEFT, padx=5)

        Button(btn_frame, text="Сохранить и применить", command=self.on_save).pack(side=RIGHT, padx=5)
        Button(btn_frame, text="Отмена", command=self.top.destroy).pack(side=RIGHT)

        self.edit_entry = None
        self.edit_col = None

    def on_treeview_click(self, event):
        # Обрабатываем только клик по колонке enabled
        region = self.tree.identify("region", event.x, event.y)
        if region != "cell":
            return
        col = self.tree.identify_column(event.x)
        row = self.tree.identify_row(event.y)

        if not row or col != '#1':  # Колонка enabled
            return

        values = list(self.tree.item(row, 'values'))
        if values[0] == "☑":
            values[0] = "☐"
        else:
            values[0] = "☑"
        self.tree.item(row, values=values)

        # Отменяем дальнейшую обработку, чтобы не запускать редактирование
        return "break"

    def on_double_click(self, event):
        # Редактируем только колонки pattern, replacement, description (2,3,4)
        region = self.tree.identify("region", event.x, event.y)
        if region != 'cell':
            return

        col = self.tree.identify_column(event.x)
        row = self.tree.identify_row(event.y)
        if not row or col == '#1':  # не редактируем enabled
            return
        col_idx = int(col.replace("#", "")) - 1

        if self.edit_entry:
            self.edit_entry.destroy()
            self.edit_entry = None

        x, y, width, height = self.tree.bbox(row, col)
        values = list(self.tree.item(row, "values"))
        current_value = values[col_idx]

        entry = Entry(self.tree)
        entry.insert(0, current_value)
        entry.select_range(0, END)
        entry.focus_set()
        entry.place(x=x, y=y, width=width, height=height)

        def save_edit(event=None):
            new_val = entry.get()
            values[col_idx] = new_val
            self.tree.item(row, values=values)
            entry.destroy()
            self.edit_entry = None

        entry.bind("<Return>", save_edit)
        entry.bind("<FocusOut>", save_edit)
        self.edit_entry = entry
        self.edit_col = col_idx

    def add_rule(self):
        enabled_symbol = "☑"  # по умолчанию включено
        self.tree.insert('', END, values=(enabled_symbol, "", "", ""))

    def delete_selected(self):
        selected = self.tree.selection()
        for item in selected:
            self.tree.delete(item)

    def on_save(self):
        new_rules = []
        for item in self.tree.get_children():
            vals = self.tree.item(item, "values")
            if len(vals) >= 3:
                enabled = (vals[0] == "☑")
                pattern = vals[1].strip()
                replacement = vals[2]
                description = vals[3] if len(vals) > 3 else ""
                if not pattern:
                    continue
                try:
                    re.compile(pattern)
                except Exception as e:
                    messagebox.showerror("Ошибка в паттерне", f"Паттерн '{pattern}' неправильный:\n{str(e)}")
                    return
                new_rules.append((enabled, pattern, replacement, description))
        self.save_callback(new_rules)
        self.top.destroy()

    def import_from_excel(self):
        file_path = filedialog.askopenfilename(filetypes=[("Excel файлы", "*.xlsx")])
        if not file_path:
            return
        try:
            df = pd.read_excel(file_path)
            if len(df.columns) < 2:
                messagebox.showwarning("Ошибка", "Файл должен содержать минимум два столбца: паттерн и замена")
                return
            self.tree.delete(*self.tree.get_children())
            for idx, row in df.iterrows():
                pattern = str(row.iloc[0]) if pd.notna(row.iloc[0]) else ""
                replacement = str(row.iloc[1]) if pd.notna(row.iloc[1]) else ""
                description = str(row.iloc[2]) if (len(df.columns) > 2 and pd.notna(row.iloc[2])) else ""
                if pattern:
                    enabled_symbol = "☑"
                    self.tree.insert('', END, values=(enabled_symbol, pattern, replacement, description))
        except Exception as e:
            messagebox.showerror("Ошибка импорта", str(e))

    def export_to_excel(self):
        file_path = filedialog.asksaveasfilename(defaultextension=".xlsx",
                                                 filetypes=[("Excel файлы", "*.xlsx")])
        if not file_path:
            return
        try:
            data = []
            for item in self.tree.get_children():
                vals = self.tree.item(item, "values")
                enabled = (vals[0] == "☑")
                data.append((
                    vals[1],  # pattern
                    vals[2],  # replacement
                    vals[3],  # description
                    enabled
                ))
            df = pd.DataFrame(data, columns=['Pattern', 'Replacement', 'Description', 'Enabled'])
            df.to_excel(file_path, index=False)
            messagebox.showinfo("Экспорт успешен", f"Файл сохранён: {file_path}")
        except Exception as e:
            messagebox.showerror("Ошибка экспорта", str(e))


class AttributeEditor:
    MAPPINGS_FILE = 'mappings.json'
    EXCLUSIONS_FILE = 'exclusions.json'
    SETTINGS_FILE = 'settings.json'
    CASE_NORM_FILE = 'case_normalization.json'
    REPLACEMENT_RULES_FILE = 'replacement_rules.json'

    def __init__(self, root):
        self.root = root
        self.root.title("Редактор атрибутов товаров")
        self.root.geometry("1350x800")
        self.original_df = None
        self.param_mapping = {}
        self.original_values = {}
        self.manual_split_values = {}
        self.exclusions = []
        self.mappings = {}
        self.sort_directions = {}
        self.edit_entry = None
        self.edit_undo_stack = []
        self.similar_groups = {}
        self.SIMILARITY_THRESHOLD = 85
        self.df_columns = []

        self.mapping_sort_reverse = False
        self._mapping_items_cache = []

        self.settings = {
            "features_col": "",
            "extra_cols": [],
            "manual_split_values": {}
        }

        self.case_normalization = {}

        self.manual_split_results = {}

        self.replacement_rules = []

        self.context_menu = Menu(self.root, tearoff=0)
        self.context_menu.add_command(label="Копировать", command=self.copy_selected_value)
        self.context_menu.add_separator()
        self.context_menu.add_command(label="Объединить выделенные параметры", command=self.merge_selected_similar_params)

        self.load_settings()
        self.load_mappings()
        self.load_excl()
        self.load_case_normalization()
        self.load_replacement_rules()
        self.setup_ui()

        self.root.protocol("WM_DELETE_WINDOW", self.on_close)

    def ctrl_c_handler(self, event):
        if event.state & 0x4 and event.keycode in (54, 67):
            self.copy_selected_value()
            return "break"
        return None

    def ctrl_v_handler(self, event):
        if event.state & 0x4 and event.keycode in (55, 39):
            widget = event.widget
            if isinstance(widget, Entry):
                try:
                    clip = pyperclip.paste()
                    widget.delete("sel.first", "sel.last")
                    widget.insert(INSERT, clip)
                    return "break"
                except Exception:
                    pass
        return None

    def ctrl_z_handler(self, event):
        if event.state & 0x4 and event.keycode in (52, 44):
            if self.edit_entry and self.edit_undo_stack:
                try:
                    prev_value = self.edit_undo_stack.pop()
                    self.edit_entry.delete(0, END)
                    self.edit_entry.insert(0, prev_value)
                    return "break"
                except IndexError:
                    pass
        return None

    def ctrl_delete_handler(self, event):
        if event.keycode == 107:
            self.delete_selected_mappings()
            return "break"
        return None

    def generic_key_handler(self, event):
        if event.state & 0x4:
            if event.keycode in (54, 67):
                return self.ctrl_c_handler(event)
            elif event.keycode in (55, 39):
                return self.ctrl_v_handler(event)
            elif event.keycode in (52, 44):
                return self.ctrl_z_handler(event)
        elif event.keycode == 107:
            return self.ctrl_delete_handler(event)
        return None

    def setup_ui(self):
        self.root.bind_all("<Key>", self.generic_key_handler)

        main_frame = Frame(self.root)
        main_frame.pack(fill=BOTH, expand=True, padx=5, pady=5)

        file_frame = Frame(main_frame)
        file_frame.pack(fill=X, pady=2)

        self.btn_load = Button(file_frame, text="Загрузить файл", command=self.load_file)
        self.btn_load.grid(row=0, column=0, padx=2, sticky=W)
        ToolTip(self.btn_load, "Загрузите Excel-файл с HTML-характеристиками")

        Label(file_frame, text="Столбец характеристик:").grid(row=0, column=1, sticky=E, padx=(15, 2))
        self.features_col = Entry(file_frame, width=4)
        self.features_col.grid(row=0, column=2, sticky=W)
        ToolTip(self.features_col, "Номер столбца с HTML-характеристиками (отсчёт с 1)")
        self.features_col.insert(0, self.settings.get("features_col", ""))

        Label(file_frame, text="Доп. столбцы:").grid(row=0, column=3, sticky=E, padx=(15, 2))
        self.extra_cols_frame = Frame(file_frame)
        self.extra_cols_frame.grid(row=0, column=4, sticky=W)
        self.extra_cols_label = Label(self.extra_cols_frame, text="", width=30, anchor=W)
        self.extra_cols_label.pack(side=LEFT)
        self.extra_cols_button = Button(self.extra_cols_frame, text="...", width=3, command=self.show_extra_cols_dialog)
        self.extra_cols_button.pack(side=LEFT)
        ToolTip(self.extra_cols_button, "Выберите дополнительные столбцы для сохранения")
        if self.settings.get("extra_cols"):
            self.extra_cols_label.config(text=", ".join(self.settings["extra_cols"]))

        self.btn_process = Button(file_frame, text="Обработать таблицу", command=self.process_table)
        self.btn_process.grid(row=0, column=5, padx=(15, 2))
        ToolTip(self.btn_process, "Обработать таблицу и извлечь параметры")

        self.btn_save = Button(file_frame, text="Сохранить результат", command=self.save_results)
        self.btn_save.grid(row=0, column=6, padx=2, sticky=E)
        ToolTip(self.btn_save, "Сохранить обработанные данные в Excel")

        self.btn_replacement_rules = Button(file_frame, text="Редактор правил замены", command=self.open_replacement_rules_editor)
        self.btn_replacement_rules.grid(row=0, column=7, padx=10)
        ToolTip(self.btn_replacement_rules, "Управление правилами замены с использованием регулярных выражений")

        self.btn_case_norm = Button(file_frame, text="Нормализация регистра", command=self.open_case_normalization_dialog)
        self.btn_case_norm.grid(row=0, column=8, padx=10)
        ToolTip(self.btn_case_norm, "Управление регистрами значений. Варианты регистра считаются одинаковыми.")

        file_frame.grid_columnconfigure(6, weight=1)

        tree_frame = Frame(main_frame)
        tree_frame.pack(fill=BOTH, expand=True, pady=5)

        vsb = Scrollbar(tree_frame, orient="vertical")
        vsb.pack(side=RIGHT, fill=Y)

        columns = ('rownum', 'orig_param', 'orig_values', 'new_param', 'delimiter', 'split_result')
        self.tree = ttk.Treeview(tree_frame, columns=columns, show='headings', height=20, yscrollcommand=vsb.set)
        self.tree.pack(side=LEFT, fill=BOTH, expand=True)
        vsb.config(command=self.tree.yview)

        self.tree.heading('rownum', text='№')
        self.tree.heading('orig_param', text='Исходный параметр')
        self.tree.heading('orig_values', text='Исходные значения')
        self.tree.heading('new_param', text='Новый параметр')
        self.tree.heading('delimiter', text='Разделитель')
        self.tree.heading('split_result', text='Результат разделения')

        self.tree.column('rownum', width=40, anchor=CENTER)
        self.tree.column('orig_param', width=130)
        self.tree.column('orig_values', width=250)
        self.tree.column('new_param', width=130)
        self.tree.column('delimiter', width=70)
        self.tree.column('split_result', width=350)

        self.tree.bind("<Double-1>", self.on_double_click)
        self.tree.bind("<Button-3>", self.show_context_menu)
        self.tree.bind("<Button-1>", self.sort_column, True)
        self.tree.bind("<Control-c>", self.copy_selected_value)

        mapping_frame = LabelFrame(main_frame, text="Таблица сопоставлений и поиск")
        mapping_frame.pack(fill=X, pady=5)

        search_frame = Frame(mapping_frame)
        search_frame.pack(fill=X, padx=5, pady=2)
        Label(search_frame, text="Поиск по Исходным значениям:").pack(side=LEFT)
        self.search_var = StringVar()
        self.search_var.trace_add('write', self.filter_mapping_table)
        self.search_entry = Entry(search_frame, textvariable=self.search_var)
        self.search_entry.pack(side=LEFT, fill=X, expand=True, padx=5)

        mapping_vsb = Scrollbar(mapping_frame, orient="vertical")
        mapping_vsb.pack(side=RIGHT, fill=Y)

        self.mapping_tree = ttk.Treeview(mapping_frame, columns=('original', 'replacement'), show='headings', height=8,
                                         selectmode='extended',
                                         yscrollcommand=mapping_vsb.set)
        self.mapping_tree.heading('original', text='Исходное значение', command=self.sort_mapping_tree_by_original_col)
        self.mapping_tree.heading('replacement', text='Замена')
        self.mapping_tree.column('original', width=250)
        self.mapping_tree.column('replacement', width=250)
        self.mapping_tree.pack(side=LEFT, fill=BOTH, expand=True, padx=5)
        mapping_vsb.config(command=self.mapping_tree.yview)

        self.mapping_tree.bind("<Double-1>", self.on_mapping_double_click)
        self.mapping_tree.bind("<Control-a>", self.select_all_mappings)
        self.mapping_tree.bind("<Delete>", self.delete_selected_mappings)

        mapping_buttons = Frame(mapping_frame)
        mapping_buttons.pack(side=LEFT, padx=5, fill=Y)

        btn_add = Button(mapping_buttons, text="Добавить", command=self.add_mapping)
        btn_add.pack(fill=X, pady=2)
        btn_del = Button(mapping_buttons, text="Удалить", command=self.delete_mapping)
        btn_del.pack(fill=X, pady=2)
        btn_import = Button(mapping_buttons, text="Импорт", command=self.import_mappings)
        btn_import.pack(fill=X, pady=2)
        btn_export = Button(mapping_buttons, text="Экспорт", command=self.export_mappings)
        btn_export.pack(fill=X, pady=2)

        exclusions_frame = LabelFrame(main_frame, text="Список исключений (двойной клик - редактирование)")
        exclusions_frame.pack(fill=X, pady=5)

        self.exclusions_tree = ttk.Treeview(exclusions_frame, columns=('exclusion',), show='headings', height=3)
        self.exclusions_tree.heading('exclusion', text='Исключение')
        self.exclusions_tree.column('exclusion', width=400)
        self.exclusions_tree.pack(side=LEFT, fill=BOTH, expand=True)
        self.exclusions_tree.bind("<Double-1>", self.on_exclusion_double_click)

        exclusions_buttons = Frame(exclusions_frame)
        exclusions_buttons.pack(side=LEFT, padx=5, fill=Y)

        btn_add_exc = Button(exclusions_buttons, text="Добавить", command=self.add_exclusion)
        btn_add_exc.pack(fill=X, pady=2)
        btn_del_exc = Button(exclusions_buttons, text="Удалить", command=self.delete_exclusion)
        btn_del_exc.pack(fill=X, pady=2)

        self.refresh_mapping_table()
        self.refresh_exclusions_table()

        self.sort_mapping_tree_by_original_col()

    def clean_cell(self, value):
        if not isinstance(value, str):
            return value
        value = value.strip()
        return re.sub(r'\s+', ' ', value)

    def remove_duplicates_from_cell(self, cell_value):
        if not cell_value:
            return cell_value
        seps = [',', ';']
        sep = None
        for s in seps:
            if s in cell_value:
                sep = s
                break
        if sep is None:
            return cell_value
        items = [self.clean_cell(v) for v in cell_value.split(sep)]
        seen = set()
        unique = []
        for v in items:
            norm_v = normalize_variant(v).lower()
            if v and norm_v not in seen:
                seen.add(norm_v)
                unique.append(v)
        return sep.join(unique)

    def remove_duplicates_from_list(self, values):
        seen = set()
        result = []
        for v in values:
            if v:
                norm_v = normalize_variant(v).lower()
                if norm_v not in seen:
                    seen.add(norm_v)
                    result.append(v)
        return result

    def parse_html_table(self, html):
        soup = BeautifulSoup(html, 'html.parser')
        params = {}
        current_param = None
        for tr in soup.find_all('tr'):
            tds = tr.find_all('td')

            if len(tds) == 0:
                continue

            if len(tds) == 1:
                colspan = tds[0].get('colspan')
                try:
                    colspan_num = int(colspan) if colspan else 1
                except:
                    colspan_num = 1
                if colspan_num >= 2:
                    current_param = None
                    continue
                if current_param:
                    value = self.clean_cell(tds[0].get_text())
                    if value:
                        params[current_param] += f"; {value}"
                continue

            if len(tds) == 2:
                colspan0 = tds[0].get('colspan')
                colspan1 = tds[1].get('colspan')
                try:
                    colspan_num_0 = int(colspan0) if colspan0 else 1
                except:
                    colspan_num_0 = 1
                try:
                    colspan_num_1 = int(colspan1) if colspan1 else 1
                except:
                    colspan_num_1 = 1
                if colspan_num_0 >= 2 or colspan_num_1 >= 2:
                    current_param = None
                    continue

                param = self.clean_cell(tds[0].get_text())
                value = self.clean_cell(tds[1].get_text())
                if param and value:
                    params[param] = value
                    current_param = param
                else:
                    current_param = None
                continue

            current_param = None
        return params

    def find_similar_param_groups(self, param_series):
        params = list(param_series)
        used = set()
        groups = {}
        group_id = 1
        for i, param in enumerate(params):
            if param in used:
                continue
            matches = process.extract(param, params, scorer=fuzz.token_sort_ratio, score_cutoff=self.SIMILARITY_THRESHOLD)
            group = set([m[0] for m in matches])
            group.add(param)
            for p in group:
                if p not in used:
                    groups[p] = group_id
                    used.add(p)
            group_id += 1
        return groups

    def refresh_treeview_with_colors(self):
        self.tree.delete(*self.tree.get_children())
        color_palette = ["#FFCCCC", "#CCFFCC", "#CCCCFF", "#FFFFCC", "#CCFFFF", "#FFCCFF", "#FFDAB9"]
        group_ids = set(self.similar_groups.values())
        group_to_color = {group: color_palette[i % len(color_palette)] for i, group in enumerate(group_ids)}

        for idx, row in self.df.iterrows():
            param = row['Исходный параметр']
            group = self.similar_groups.get(param, 0)
            tag = f"group_{group}" if group else ""
            values = (
                idx + 1,
                row.get('Исходный параметр', ''),
                row.get('Исходные значения', ''),
                row.get('Новый параметр', ''),
                row.get('Разделитель', ''),
                row.get('Результат разделения', ''),
            )
            self.tree.insert('', 'end', values=values, tags=(tag,))

    def merge_selected_similar_params(self):
        selected_items = self.tree.selection()
        if not selected_items or len(selected_items) < 2:
            messagebox.showwarning("Ошибка", "Выберите минимум 2 параметра для объединения")
            return

        selected_params = [self.tree.item(item, 'values')[1] for item in selected_items]

        new_name = simpledialog.askstring("Новое имя параметра", "Введите новое имя для объединенного параметра:")
        if not new_name:
            return

        for param in selected_params:
            self.mappings[param] = new_name

        self.save_mappings()
        self.process_table()
        messagebox.showinfo("Успех", f"Параметры объединены под именем '{new_name}'")

    def sort_column(self, event):
        region = self.tree.identify("region", event.x, event.y)
        if region != "heading":
            return
        column = self.tree.identify_column(event.x)
        col_idx = int(column.replace("#", "")) - 1
        current_reverse = self.sort_directions.get(col_idx, False)

        data = [(self.tree.item(item, "values")[col_idx], item) for item in self.tree.get_children("")]

        try:
            data.sort(
                key=lambda x: float(x[0]) if isinstance(x[0], str) and x[0].replace(".", "", 1).isdigit() else x[0].lower(),
                reverse=current_reverse,
            )
        except Exception:
            data.sort(key=lambda x: x[0].lower() if isinstance(x[0], str) else "", reverse=current_reverse)

        for index, (_, item) in enumerate(data):
            self.tree.move(item, "", index)

        self.sort_directions[col_idx] = not current_reverse

    def save_excl(self):
        with open(self.EXCLUSIONS_FILE, "w", encoding="utf-8") as f:
            json.dump(self.exclusions, f, ensure_ascii=False, indent=2)

    def load_excl(self):
        try:
            with open(self.EXCLUSIONS_FILE, "r", encoding="utf-8") as f:
                self.exclusions = json.load(f)
        except FileNotFoundError:
            self.exclusions = []

    def refresh_exclusions_table(self):
        self.exclusions_tree.delete(*self.exclusions_tree.get_children())
        for exc in self.exclusions:
            self.exclusions_tree.insert("", "end", values=(exc,))

    def add_exclusion(self):
        def save():
            val = entry.get().strip()
            if val and val not in self.exclusions:
                self.exclusions.append(val)
                self.save_excl()
                self.refresh_exclusions_table()
                self.process_table()
            top.destroy()

        top = Toplevel(self.root)
        top.title("Добавить исключение")
        entry = Entry(top)
        entry.pack(fill=X, padx=10, pady=10)
        entry.focus_set()
        Button(top, text="Сохранить", command=save).pack(pady=5)

    def delete_exclusion(self):
        selected = self.exclusions_tree.selection()
        if selected:
            val = self.exclusions_tree.item(selected[0], "values")[0]
            self.exclusions = [x for x in self.exclusions if x != val]
            self.save_excl()
            self.refresh_exclusions_table()
            self.process_table()

    def on_exclusion_double_click(self, event):
        item = self.exclusions_tree.identify_row(event.y)
        if not item:
            return
        old_val = self.exclusions_tree.item(item, "values")[0]
        entry = Entry(self.exclusions_tree)
        entry.insert(0, old_val)
        entry.select_range(0, END)
        entry.focus_set()
        x, y, width, height = self.exclusions_tree.bbox(item, "#1")
        entry.place(x=x, y=y, width=width, height=height)

        def save_edit(event=None):
            new_val = entry.get().strip()
            if new_val and new_val != old_val:
                idx = self.exclusions.index(old_val)
                self.exclusions[idx] = new_val
                self.save_excl()
                self.refresh_exclusions_table()
                self.process_table()
            entry.destroy()

        entry.bind("<Return>", save_edit)
        entry.bind("<FocusOut>", save_edit)

    def save_mappings(self):
        with open(self.MAPPINGS_FILE, "w", encoding="utf-8") as f:
            json.dump(self.mappings, f, ensure_ascii=False, indent=2)

    def load_mappings(self):
        try:
            with open(self.MAPPINGS_FILE, "r", encoding="utf-8") as f:
                self.mappings = json.load(f)
        except FileNotFoundError:
            self.mappings = {}

    def refresh_mapping_table(self):
        self.mapping_tree.delete(*self.mapping_tree.get_children())
        self._mapping_items_cache = []
        for orig, repl in self.mappings.items():
            self._mapping_items_cache.append((orig, repl))
        self._mapping_items_cache.sort(key=lambda x: x[0].lower())
        for orig, repl in self._mapping_items_cache:
            self.mapping_tree.insert("", "end", values=(orig, repl))

    def filter_mapping_table(self, *args):
        search_text = self.search_var.get().lower()
        self.mapping_tree.delete(*self.mapping_tree.get_children())
        filtered = []
        for orig, repl in self._mapping_items_cache:
            if search_text in orig.lower():
                filtered.append((orig, repl))
        for orig, repl in filtered:
            self.mapping_tree.insert("", "end", values=(orig, repl))

    def sort_mapping_tree_by_original_col(self, event=None):
        self.mapping_sort_reverse = not self.mapping_sort_reverse
        self._mapping_items_cache.sort(key=lambda x: x[0].lower(), reverse=self.mapping_sort_reverse)

        self.mapping_tree.delete(*self.mapping_tree.get_children())
        for orig, repl in self._mapping_items_cache:
            self.mapping_tree.insert("", "end", values=(orig, repl))

    def add_mapping(self):
        def save():
            orig = entry_orig.get().strip()
            repl = entry_repl.get().strip()
            if orig:
                self.mappings[orig] = repl if repl else orig
                self.save_mappings()
                self.refresh_mapping_table()
                self.update_main_tree_with_mappings()
                self.process_table()
            top.destroy()

        top = Toplevel(self.root)
        top.title("Добавить сопоставление")
        Label(top, text="Исходное значение:").pack()
        entry_orig = Entry(top)
        entry_orig.pack(fill=X, padx=10)
        Label(top, text="Новое значение:").pack()
        entry_repl = Entry(top)
        entry_repl.pack(fill=X, padx=10)
        Button(top, text="Сохранить", command=save).pack(pady=5)
        entry_orig.focus_set()

    def delete_mapping(self):
        selected = self.mapping_tree.selection()
        if selected:
            orig = self.mapping_tree.item(selected[0], "values")[0]
            if messagebox.askyesno("Подтверждение", f"Удалить сопоставление '{orig}'?"):
                if orig in self.mappings:
                    del self.mappings[orig]
                    self.save_mappings()
                    self.refresh_mapping_table()
                    self.update_main_tree_with_mappings()
                    self.process_table()

    def on_mapping_double_click(self, event):
        item = self.mapping_tree.identify_row(event.y)
        col = self.mapping_tree.identify_column(event.x)
        if not item or not col:
            return

        col_idx = int(col.replace('#', '')) - 1
        values = list(self.mapping_tree.item(item, "values"))
        old_val = values[col_idx]

        entry = Entry(self.mapping_tree)
        entry.insert(0, old_val)
        entry.select_range(0, END)
        entry.focus_set()

        x, y, width, height = self.mapping_tree.bbox(item, col)
        entry.place(x=x, y=y, width=width, height=height)

        def save_edit(event=None):
            new_val = entry.get().strip()
            if not new_val:
                messagebox.showwarning("Ошибка", "Значение не может быть пустым")
                return
            if new_val != old_val:
                if col_idx == 0:
                    old_key = values[0]
                    repl = values[1]
                    if old_key in self.mappings:
                        del self.mappings[old_key]
                    self.mappings[new_val] = repl
                else:
                    self.mappings[values[0]] = new_val

                self.save_mappings()
                self.refresh_mapping_table()
                self.update_main_tree_with_mappings()
                self.process_table()
            entry.destroy()

        entry.bind("<Return>", save_edit)
        entry.bind("<FocusOut>", save_edit)

    def copy_mapping_selected_value(self, event=None):
        focus = self.mapping_tree.focus()
        if not focus:
            return
        col = '#1'
        if event and hasattr(event, 'x'):
            col = self.mapping_tree.identify_column(event.x)
        col_idx = int(col.replace('#', '')) - 1
        values = self.mapping_tree.item(focus, "values")
        if 0 <= col_idx < len(values):
            pyperclip.copy(str(values[col_idx]))

    def import_mappings(self, event=None):
        file_path = filedialog.askopenfilename(filetypes=[("Excel файлы", "*.xlsx")])
        if file_path:
            try:
                df = pd.read_excel(file_path)
                if len(df.columns) < 2:
                    raise ValueError("Файл должен содержать как минимум 2 столбца.")

                for _, row in df.iterrows():
                    orig = str(row.iloc[0]).strip()
                    repl = str(row.iloc[1]).strip()
                    if orig:
                        self.mappings[orig] = repl if repl else orig

                self.save_mappings()
                self.refresh_mapping_table()
                self.update_main_tree_with_mappings()
                self.process_table()
                messagebox.showinfo("Успех", "Сопоставления импортированы")
            except Exception as e:
                messagebox.showerror("Ошибка импорта", str(e))

    def export_mappings(self, event=None):
        df = pd.DataFrame(list(self.mappings.items()), columns=["Исходное значение", "Замена"])
        save_path = filedialog.asksaveasfilename(defaultextension=".xlsx", filetypes=[("Excel файлы", "*.xlsx")])
        if save_path:
            try:
                df.to_excel(save_path, index=False)
                messagebox.showinfo("Успех", "Сопоставления экспортированы")
            except Exception as e:
                messagebox.showerror("Ошибка экспорта", str(e))

    def load_file(self):
        file_paths = filedialog.askopenfilenames(filetypes=[("Excel файлы", "*.xlsx")])
        if not file_paths:
            return

        dfs = []
        failed_files = []

        for file_path in file_paths:
            try:
                df = pd.read_excel(file_path)
                dfs.append(df)
            except Exception as e:
                failed_files.append((file_path, str(e)))

        if dfs:
            self.original_df = pd.concat(dfs, ignore_index=True)
            self.auto_fill_column()
            self.df_columns = list(self.original_df.columns)

            if self.features_col.get().isdigit():
                default_cols = [str(i + 1) for i in range(len(self.original_df.columns))
                                if i != int(self.features_col.get()) - 1]
            else:
                default_cols = []

            sel_cols = self.settings.get("extra_cols", [])
            if not sel_cols:
                sel_cols = default_cols

            self.extra_cols_label.config(text=", ".join(sel_cols))
            self.settings["extra_cols"] = sel_cols
            messagebox.showinfo("Загрузка", f"Загружено файлов: {len(dfs)}")

            if failed_files:
                error_msg = "\n".join([f"• {path}\n  Ошибка: {err}" for path, err in failed_files])
                messagebox.showwarning("Ошибки при загрузке", f"Не удалось загрузить следующие файлы:\n{error_msg}")
        else:
            self.original_df = None
            self.extra_cols_label.config(text="")
            messagebox.showwarning("Ошибка", "Не удалось загрузить ни один файл.")

    def auto_fill_column(self):
        if self.original_df is None:
            return

        for i, col in enumerate(self.original_df.columns):
            if self.original_df[col].astype(str).str.contains("<table", case=False).any():
                self.features_col.delete(0, END)
                self.features_col.insert(0, str(i + 1))
                return

    def split_values(self, value, delimiter):
        if not isinstance(value, str):
            return []

        for exc in self.exclusions:
            if exc and exc.strip() and (exc in value):
                return [value]

        if delimiter == "":
            return [value.strip()] if value.strip() else []

        safe_delimiters = ''.join(['\\' + c if c in r".^$*+?{}[]\|()" else c for c in delimiter])
        pattern = f"[{safe_delimiters}]"

        parts = re.split(pattern, value)
        parts = [part.strip() for part in parts if part.strip()]
        parts = self.remove_duplicates_from_list(parts)
        return parts

    def load_settings(self):
        if os.path.exists(self.SETTINGS_FILE):
            try:
                with open(self.SETTINGS_FILE, "r", encoding="utf-8") as f:
                    self.settings = json.load(f)
            except Exception as e:
                messagebox.showwarning("Ошибка загрузки настроек", f"Не удалось загрузить настройки: {str(e)}")
                self.settings = {}
        else:
            self.settings = {}

    def save_settings(self):
        self.settings["features_col"] = self.features_col.get()
        self.settings["extra_cols"] = self.extra_cols_label.cget("text").split(", ") if self.extra_cols_label.cget("text") else []
        self.settings["manual_split_values"] = self.manual_split_values

        try:
            with open(self.SETTINGS_FILE, "w", encoding="utf-8") as f:
                json.dump(self.settings, f, ensure_ascii=False, indent=2)
        except Exception as e:
            messagebox.showerror("Ошибка сохранения настроек", f"Не удалось сохранить настройки: {str(e)}")

    def save_case_normalization(self):
        try:
            with open(self.CASE_NORM_FILE, "w", encoding="utf-8") as f:
                json.dump(self.case_normalization, f, ensure_ascii=False, indent=2)
        except Exception as e:
            messagebox.showerror("Ошибка сохранения нормализации регистра", f"Не удалось сохранить нормализацию: {str(e)}")

    def load_case_normalization(self):
        if os.path.exists(self.CASE_NORM_FILE):
            try:
                with open(self.CASE_NORM_FILE, "r", encoding="utf-8") as f:
                    self.case_normalization = json.load(f)
            except Exception as e:
                messagebox.showwarning("Ошибка загрузки нормализации регистра", f"Не удалось загрузить нормализацию: {str(e)}")
                self.case_normalization = {}
        else:
            self.case_normalization = {}

    def save_replacement_rules(self):
        try:
            rules_to_save = [{
                "enabled": r[0],
                "pattern": r[1],
                "replacement": r[2],
                "description": r[3] if len(r) > 3 else ""
            } for r in self.replacement_rules]
            with open(self.REPLACEMENT_RULES_FILE, "w", encoding="utf-8") as f:
                json.dump(rules_to_save, f, ensure_ascii=False, indent=2)
        except Exception as e:
            messagebox.showerror("Ошибка сохранения правил замены", f"Не удалось сохранить правила: {str(e)}")

    def load_replacement_rules(self):
        default_rules = [
            (True, r"(\d+\.?\d*)\s*kg", r"\1 кг", "Замена кг (число сохраняется)"),
            (True, r"(\d+\.?\d*)\s*lb", r"\1 фунта", "Замена фунтов"),
            (True, r"(\d+\.?\d*)\s*mm", r"\1 мм", "Миллиметры"),
            (True, r"(\d+\.?\d*)\s*V\b", r"\1 В", "Вольты"),
            (True, r"(\d+\.?\d*)\s*Ω", r"\1 Ом", "Омы"),
            (True, r"(\d+\.?\d*)\s*Mbps", r"\1 Мбит/с", "Мегабиты в секунду"),
            (True, r"(\d+\.?\d*)\s*kbps", r"\1 кбит/с", "Килобиты в секунду"),
            (True, r"(\d+\.?\d*)\s*MP", r"\1 Мп", "Мегапиксели"),
            (True, r"(\d+),(\d+)", r"\1.\2", "Исправление десятичных разделителей (запятая -> точка)"),
            (True, r"(\d+)\.(\d+)", r"\1,\2", "Замена точки на запятую в числах"),
            (True, r"\bx\b", r"×", "Символ умножения"),
            (True, r"\bIR\b", r"ИК", "Инфракрасный"),
            (True, r"\bWDR\b", r"WDR", "(Сокращение сохраняется)"),
            (True, r"\bCMOS\b", r"CMOS", "(Сокращение сохраняется)"),
            (True, r"\bBNC\b", r"BNC", "(Сокращение сохраняется)"),
            (True, r"\bGross Weight\b", r"брутто", "Полный вес"),
            (True, r"\bNet Weight\b", r"нетто", "Чистый вес"),
            (True, r"\bps\b", r"шт.", "Штуки"),
            (True, r"\bInterface\b", r"интерфейс", "Перевод"),
            (True, r"\bInput\b", r"вход", "Перевод"),
            (True, r"\bOutput\b", r"выход", "Перевод"),
            (True, r"\bprogressive scan\b", r"прогрессивный", "Перевод"),
            (True, r"\bDC\b", r"постоянного тока", "Пояснение"),
            (True, r"\bPoE\b", r"PoE", "(Сокращение сохраняется)"),
            (True, r"\b(\d+)m\b", r"\1 м", "Метры"),
            (True, r"\b(\d+)°C\b", r"\1°C", "Градусы Цельсия (сохраняется)"),
            (True, r"\bFPS\b", r"к/с", "Кадры в секунду"),
            (True, r"\bMic\b", r"микрофон", "Перевод"),
            (True, r"\bPan / Tilt\b", r"Поворот / Наклон", "Перевод для PTZ-камер"),
            (True, r"\bIK10\b", r"IK10", "Степень защиты (сохраняется)"),
            (True, r"\bIP67\b", r"IP67", "Степень защиты (сохраняется)"),
        ]
        if os.path.exists(self.REPLACEMENT_RULES_FILE):
            try:
                with open(self.REPLACEMENT_RULES_FILE, "r", encoding="utf-8") as f:
                    loaded = json.load(f)
                    rules = []
                    for r in loaded:
                        pattern = r.get("pattern", "")
                        replacement = r.get("replacement", "")
                        description = r.get("description", "")
                        enabled = r.get("enabled", True)
                        if pattern and replacement is not None:
                            rules.append((enabled, pattern, replacement, description))
                    if rules:
                        self.replacement_rules = rules
                    else:
                        self.replacement_rules = default_rules
            except Exception:
                self.replacement_rules = default_rules
        else:
            self.replacement_rules = default_rules

    def replace_mapped_values_in_str(self, value, delimiter=';'):
        if not isinstance(value, str):
            return value

        parts = [part.strip() for part in re.split(f"[{re.escape(delimiter)}]", value)]
        replaced_parts = []

        for part in parts:
            norm_part = normalize_variant(part)
            replaced = None
            if norm_part in self.mappings:
                replaced = self.mappings[norm_part]
            elif part in self.mappings:
                replaced = self.mappings[part]

            val_to_process = replaced if replaced is not None else part

            for enabled, pattern, replacement, _desc in self.replacement_rules:
                if not enabled:
                    continue
                try:
                    val_to_process_new = re.sub(pattern, replacement, val_to_process)
                except Exception:
                    val_to_process_new = val_to_process
                if val_to_process_new != val_to_process:
                    val_to_process = val_to_process_new

            replaced_parts.append(val_to_process)

        replaced_parts = self.remove_duplicates_from_list(replaced_parts)

        return delimiter.join(replaced_parts)

    def apply_case_normalization(self, value):
        if not isinstance(value, str):
            return value
        key = value.lower()
        return self.case_normalization.get(key, value)

    def apply_case_normalization_in_str(self, value, delimiter=';'):
        if not isinstance(value, str):
            return value
        parts = [part.strip() for part in re.split(f"[{re.escape(delimiter)}]", value)]
        normalized = [self.apply_case_normalization(part) for part in parts]
        normalized = self.remove_duplicates_from_list(normalized)
        return delimiter.join(normalized)

    def update_mappings_from_params(self, params):
        added = False
        for param in params:
            if param not in self.mappings:
                self.mappings[param] = param
                added = True
        if added:
            self.save_mappings()
            self.refresh_mapping_table()

    def process_table(self):
        if self.original_df is None:
            return

        try:
            col_idx = int(self.features_col.get()) - 1
            html_col = self.original_df.columns[col_idx]
        except Exception:
            messagebox.showwarning("Ошибка", "Некорректный номер столбца характеристик")
            return

        self.param_mapping = {}
        self.original_values = {}

        if "manual_split_values" in self.settings and isinstance(self.settings["manual_split_values"], dict):
            self.manual_split_values = self.settings["manual_split_values"].copy()
        else:
            self.manual_split_values = {}

        self.manual_split_results = {}

        for idx, row in self.original_df.iterrows():
            sku = row.get("Артикул", None)
            if sku is None:
                continue
            html = str(row[html_col])
            params = self.parse_html_table(html)
            self.original_values[sku] = {}
            for param, value in params.items():
                clean_value = self.remove_duplicates_from_cell(value)
                self.original_values[sku][param] = clean_value

        all_params = set()
        for sku in self.original_df["Артикул"].unique():
            for param in self.original_values.get(sku, {}):
                all_params.add(param)

        self.update_mappings_from_params(all_params)
        self.similar_groups = self.find_similar_param_groups(list(all_params))
        self.tree.delete(*self.tree.get_children())

        rownum = 1
        self.case_value_variants = defaultdict(set)

        for param in sorted(all_params):
            group_id = self.similar_groups.get(param, 0)
            orig_values_set = set()
            for sku in self.original_df["Артикул"].unique():
                vals = self.original_values.get(sku, {})
                if param in vals:
                    orig_values_set.add(vals[param])

            norm_variants = set()
            for val in orig_values_set:
                norm_val = normalize_variant(val)
                norm_variants.add(norm_val)
                self.case_value_variants[norm_val.lower()].add(val)

            orig_values_joined = "; ".join(norm_variants)

            orig_values_mapped = self.replace_mapped_values_in_str(orig_values_joined, delimiter=';')

            orig_values_mapped = self.apply_case_normalization_in_str(orig_values_mapped, delimiter=';')
            orig_values_mapped = self.remove_duplicates_from_list([p.strip() for p in orig_values_mapped.split(";") if p.strip()])
            orig_values = "; ".join(orig_values_mapped)
            orig_values = self.remove_duplicates_from_cell(orig_values)

            new_param = self.mappings.get(param, param)
            delimiter = ";"
            if param in self.manual_split_values and isinstance(self.manual_split_values[param], str) and self.manual_split_values[param] != False:
                delimiter = self.manual_split_values[param]

            split_result_list = self.split_values(orig_values, delimiter)
            split_result_list = [self.mappings.get(v, v) for v in split_result_list]
            split_result_list = [self.apply_case_normalization(v) for v in split_result_list]
            split_result_list = self.remove_duplicates_from_list(split_result_list)

            split_result_str = "; ".join(split_result_list)

            self.tree.insert("", "end", values=(
                rownum, param, orig_values, new_param, delimiter, split_result_str
            ), tags=(f"group_{group_id}",))
            rownum += 1

        self.df = pd.DataFrame([{
            "№": int(values[0]),
            "Исходный параметр": values[1],
            "Исходные значения": values[2],
            "Новый параметр": values[3],
            "Разделитель": values[4],
            "Результат разделения": values[5]
        } for item in self.tree.get_children() for values in [self.tree.item(item, "values")]])

        self.refresh_treeview_with_colors()

    def update_main_tree_with_mappings(self):
        for item in self.tree.get_children():
            values = list(self.tree.item(item, "values"))
            param = values[1]
            orig_values = values[2]
            delimiter = values[4]
            if not delimiter:
                delimiter = ";"

            new_param = self.mappings.get(param, param)
            values[3] = new_param

            if param in self.manual_split_results:
                split_result_str = self.manual_split_results[param]
            else:
                if param in self.manual_split_values and isinstance(self.manual_split_values[param], str) and self.manual_split_values[param] != False:
                    split_vals = [v.strip() for v in self.manual_split_values[param].split(";") if v.strip()]
                else:
                    split_vals = self.split_values(orig_values, delimiter)

                split_vals_joined = delimiter.join(split_vals)
                split_vals_mapped_str = self.replace_mapped_values_in_str(split_vals_joined, delimiter=delimiter)
                split_vals_mapped = [v.strip() for v in split_vals_mapped_str.split(delimiter) if v.strip()]
                split_vals_mapped = [self.apply_case_normalization(v) for v in split_vals_mapped]
                split_vals_mapped = self.remove_duplicates_from_list(split_vals_mapped)
                split_result_str = delimiter.join(split_vals_mapped)

            values[5] = split_result_str
            self.tree.item(item, values=values)

    def save_results(self):
        if self.original_df is None:
            messagebox.showwarning("Внимание", "Сначала загрузите файл!")
            return

        try:
            extra_cols = [int(x) - 1 for x in self.extra_cols_label.cget("text").split(", ") if x.strip()]
        except Exception:
            extra_cols = []

        cols_to_save = ["Артикул"] + [self.original_df.columns[i] for i in extra_cols]

        result = []

        html_col_idx = int(self.features_col.get()) - 1

        for _, row in self.original_df.iterrows():
            sku = row.get("Артикул", None)
            if sku is None:
                continue
            item_data = {col: row[col] for col in cols_to_save if col in row}

            html = str(row[self.original_df.columns[html_col_idx]])
            params = self.parse_html_table(html)

            for param, value in params.items():
                clean_value = self.remove_duplicates_from_cell(value)
                mapped_param = self.mappings.get(param, param)

                delimiter = ";"
                node_id = None
                for iid in self.tree.get_children():
                    vals = self.tree.item(iid, "values")
                    if vals[1] == param:
                        node_id = iid
                        break
                if node_id:
                    try:
                        delimiter_val = self.tree.item(node_id, "values")[4].strip()
                        if delimiter_val:
                            delimiter = delimiter_val
                    except:
                        pass

                split_vals = self.split_values(clean_value, delimiter)

                split_vals_joined = delimiter.join(split_vals)
                split_vals_mapped_str = self.replace_mapped_values_in_str(split_vals_joined, delimiter=delimiter)
                split_vals_mapped = [v.strip() for v in split_vals_mapped_str.split(delimiter) if v.strip()]

                split_vals_mapped = [self.apply_case_normalization(v) for v in split_vals_mapped]
                seen_normalized = set()
                final_split_vals = []
                for v in split_vals_mapped:
                    key_norm = normalize_variant(v).lower()
                    if key_norm not in seen_normalized:
                        seen_normalized.add(key_norm)
                        final_split_vals.append(v)

                if param in self.manual_split_results:
                    result_str = self.manual_split_results[param]
                else:
                    result_str = ", ".join(final_split_vals)

                item_data[mapped_param] = result_str

            result.append(item_data)

        df_result = pd.DataFrame(result)
        save_path = filedialog.asksaveasfilename(defaultextension=".xlsx", filetypes=[("Excel файлы", "*.xlsx")])

        if save_path:
            try:
                df_result.to_excel(save_path, index=False)
                messagebox.showinfo("Успех", f"Результат сохранён в {save_path}")
            except Exception as e:
                messagebox.showerror("Ошибка сохранения", str(e))

    def on_double_click(self, event):
        region = self.tree.identify("region", event.x, event.y)
        if region != "cell":
            return

        column = self.tree.identify_column(event.x)
        item = self.tree.identify_row(event.y)

        if not item or not column:
            return

        col_index = int(column.replace('#', '')) - 1
        values = list(self.tree.item(item, "values"))
        orig_param = values[1]
        current_value = values[col_index]

        if col_index not in [3, 4, 5]:
            return

        if self.edit_entry:
            self.edit_entry.destroy()
            self.edit_entry = None
            self.edit_undo_stack.clear()

        entry = Entry(self.tree)
        entry.insert(0, current_value)
        entry.select_range(0, END)
        entry.focus_set()

        x, y, width, height = self.tree.bbox(item, column)
        entry.place(x=x, y=y, width=width, height=height)

        self.edit_undo_stack = [current_value]
        self.edit_entry = entry

        def save_and_destroy(event=None):
            new_value = entry.get().strip()
            values[col_index] = new_value

            if col_index == 4:
                self.manual_split_values[orig_param] = new_value
                self.settings["manual_split_values"] = self.manual_split_values.copy()
                self.save_settings()
                orig_values_set = set()
                for sku in self.original_df["Артикул"].unique():
                    vals = self.original_values.get(sku, {})
                    if orig_param in vals:
                        orig_values_set.add(vals[orig_param])
                orig_values = "; ".join(orig_values_set)

                split_result_list = self.split_values(orig_values, new_value)
                split_result_list = [self.mappings.get(v, v) for v in split_result_list]
                split_result_list = [self.apply_case_normalization(v) for v in split_result_list]
                split_result_list = self.remove_duplicates_from_list(split_result_list)

                values[5] = "; ".join(split_result_list)
                if orig_param in self.manual_split_results:
                    del self.manual_split_results[orig_param]
                self.tree.item(item, values=values)

            elif col_index == 5:
                vals = [v.strip() for v in new_value.split(";") if v.strip()]
                self.manual_split_results[orig_param] = "; ".join(vals)
                values[5] = "; ".join(vals)
                self.tree.item(item, values=values)

            elif col_index == 3:
                old_map_val = self.mappings.get(orig_param, orig_param)
                if old_map_val != new_value and new_value.strip():
                    self.mappings[orig_param] = new_value.strip()
                    self.save_mappings()
                    self.refresh_mapping_table()
                    self.update_main_tree_with_mappings()
                    self.process_table()
                self.tree.item(item, values=values)

            entry.destroy()
            self.edit_entry = None
            self.edit_undo_stack.clear()

        entry.bind("<Return>", save_and_destroy)
        entry.bind("<FocusOut>", save_and_destroy)

        def on_edit_change(event_key):
            if self.edit_entry:
                current_text = self.edit_entry.get()
                if self.edit_undo_stack[-1:] != [current_text]:
                    self.edit_undo_stack.append(current_text)
                if len(self.edit_undo_stack) > 20:
                    self.edit_undo_stack.pop(0)

        entry.bind("<KeyPress>", on_edit_change)

    def copy_selected_value(self, event=None):
        focus = self.tree.focus()
        if not focus:
            return
        selection = self.tree.selection()
        if not selection:
            selection = (focus,)
        values = self.tree.item(selection[0], "values")
        if not values:
            return
        col = '#1'
        if event and hasattr(event, 'x'):
            col = self.tree.identify_column(event.x)
        col_idx = int(col.replace('#', '')) - 1
        if 0 <= col_idx < len(values):
            pyperclip.copy(str(values[col_idx]))

    def copy_exclusion_selected_value(self, event=None):
        focus = self.exclusions_tree.focus()
        if not focus:
            return
        values = self.exclusions_tree.item(focus, "values")
        if values:
            pyperclip.copy(values[0])

    def show_context_menu(self, event):
        try:
            self.context_menu.tk_popup(event.x_root, event.y_root)
        finally:
            self.context_menu.grab_release()

    def select_all_mappings(self, event):
        self.mapping_tree.selection_set(self.mapping_tree.get_children())

    def delete_selected_mappings(self, event=None):
        selected = self.mapping_tree.selection()
        if not selected:
            return
        for item in selected:
            orig = self.mapping_tree.item(item, "values")[0]
            if orig in self.mappings:
                del self.mappings[orig]
        self.save_mappings()
        self.refresh_mapping_table()
        self.update_main_tree_with_mappings()
        self.process_table()

    def show_extra_cols_dialog(self):
        if self.original_df is None or self.original_df.empty:
            messagebox.showwarning("Ошибка", "Сначала загрузите файл")
            return
        current_selection = self.settings.get("extra_cols", [])
        ColumnSelectorDialog(self.root, self.df_columns, current_selection, self.on_extra_cols_selected)

    def on_extra_cols_selected(self, selection):
        self.settings["extra_cols"] = selection
        self.extra_cols_label.config(text=", ".join(selection))
        self.save_settings()

    def open_case_normalization_dialog(self):
        if not hasattr(self, 'case_value_variants') or not self.case_value_variants:
            self.case_value_variants = defaultdict(set)
            for item in self.tree.get_children():
                values = self.tree.item(item, "values")
                if len(values) < 3:
                    continue
                vals_str = values[2]
                for val in re.split(r'[;,]', vals_str):
                    clean_val = val.strip()
                    if clean_val:
                        self.case_value_variants[clean_val.lower()].add(clean_val)

        if not self.case_value_variants:
            messagebox.showinfo("Инфо", "Для нормализации регистра сначала обработайте таблицу (нажмите 'Обработать таблицу').")
            return

        def save_callback(normalized_dict):
            self.case_normalization = normalized_dict
            self.save_case_normalization()
            self.update_main_tree_with_mappings()

        CaseNormalizationDialog(self.root, self.case_value_variants, save_callback)

    def open_replacement_rules_editor(self):
        def save_callback(new_rules):
            self.replacement_rules = new_rules
            self.save_replacement_rules()
            self.process_table()

        ReplacementRulesEditorDialog(self.root, self.replacement_rules, save_callback)

    def on_close(self):
        self.save_excl()
        self.save_mappings()
        self.save_settings()
        self.save_case_normalization()
        self.save_replacement_rules()
        self.root.destroy()


if __name__ == "__main__":
    root = Tk()
    app = AttributeEditor(root)
    root.mainloop()

в данном коде не работают горячие клавиши. выделяю текст в поле, например, "Результат разделения", хочу скопировать его в любое другое поле, вставляется  номер строки из столбца №, а не значение, которое я скопировал. Сделай уже наконец возможность копирования и вставки из буфера windows в любой раскладке клавиатуры. Перепиши код полностью! мне нужен полный вариант скрипта!