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, Counter
from rapidfuzz import process, fuzz
import os

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 = {}
        for idx, col in enumerate(columns):
            var = BooleanVar()
            var.set(str(idx + 1) in current_selection)
            cb = Checkbutton(frame, text=f"{idx + 1}: {col}", variable=var)
            cb.pack(anchor='w')
            self.vars[idx + 1] = var
        Button(self.top, text="Применить", command=self.apply).pack(pady=5)
        Button(self.top, text="Отмена", command=self.top.destroy).pack()

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

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):
        """
        data: dict mapping lowercased value -> set of original variants
        save_callback: function to call with dict mapping lowercase->correct variant
        """
        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)

        # Treeview columns: lowercase variant, variants (joined), chosen correct variant (editable)
        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)

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

        # Populate treeview
        for lower_val, variants_set in sorted(self.data.items()):
            variants_joined = "; ".join(sorted(variants_set))
            # Choose default correct variant as most frequent or first (we don't have frequencies, so first)
            correct_val = sorted(variants_set)[0] if variants_set else lower_val
            self.tree.insert('', END, values=(lower_val, variants_joined, correct_val))

        # Bind double click on 'correct' column to edit
        self.tree.bind('<Double-1>', self.on_double_click)

        # Buttons
        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':  # Only allow edit in 'correct' column (3rd column)
            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]

        # Destroy previous entry if exists
        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):
        # Gather corrected mapping
        result = {}
        for item in self.tree.get_children():
            lower_val, variants_str, correct_val = self.tree.item(item, 'values')
            result[lower_val] = correct_val
        self.save_callback(result)
        self.top.destroy()

class AttributeEditor:
    MAPPINGS_FILE = 'mappings.json'
    EXCLUSIONS_FILE = 'exclusions.json'
    SETTINGS_FILE = 'settings.json'
    CASE_NORM_FILE = 'case_normalization.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 = {}  # sku -> param -> value
        self.manual_split_values = {}  # param -> delimiter or manual split string
        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": {}
        }

        # New dictionary for case normalization:
        # key: lowercase value -> correct user chosen value
        self.case_normalization = {}

        # Context menu
        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()  # load case normalization
        self.setup_ui()

        self.root.protocol("WM_DELETE_WINDOW", self.on_close)
        self.root.bind_all("<Control-c>", self.global_ctrl_c)
        self.root.bind_all("<Control-v>", self.global_ctrl_v)
        self.root.bind_all("<Control-z>", self.global_ctrl_z)

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

        # Files panel
        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")

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

        file_frame.grid_columnconfigure(6, weight=1)

        # Treeview with data
        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 table
        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)

        self.mapping_tree = ttk.Treeview(mapping_frame, columns=('original', 'replacement'), show='headings', height=8, selectmode='extended')
        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)

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

        # Hotkeys
        self.root.bind("<Control-o>", lambda e: self.load_file())
        self.root.bind("<Control-p>", lambda e: self.process_table())
        self.root.bind("<Control-s>", lambda e: self.save_results())
        self.root.bind("<Control-i>", lambda e: self.import_mappings())
        self.root.bind("<Control-e>", lambda e: self.export_mappings())

        self.refresh_mapping_table()
        self.refresh_exclusions_table()

        self.sort_mapping_tree_by_original_col()

    # --------- Main methods ---------

    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:
            if v and v.lower() not in seen:
                seen.add(v.lower())
                unique.append(v)
        return sep.join(unique)

    def remove_duplicates_from_list(self, values):
        seen = set()
        result = []
        for v in values:
            if v and v.lower() not in seen:
                seen.add(v.lower())
                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) == 2:
                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
            elif len(tds) == 1 and current_param:
                value = self.clean_cell(tds[0].get_text())
                if value:
                    params[current_param] += f"; {value}"
        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_to_color = {}
        group_ids = set(self.similar_groups.values())

        for i, group in enumerate(group_ids):
            group_to_color[group] = color_palette[i % len(color_palette)]

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

    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()
            entry.destroy()

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

    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 replace_mapped_values(self, value):
        if not isinstance(value, str):
            return value
        return self.mappings.get(value, value)

    def replace_mapped_values_in_str(self, value, delimiter=';'):
        if not isinstance(value, str):
            return value
        parts = [part.strip() for part in value.split(delimiter)]
        replaced = [self.mappings.get(part, part) for part in parts]
        return delimiter.join(replaced)

    def apply_case_normalization(self, value):
        """
        If value is str and exists in case_normalization dict (by lower),
        returns user-corrected value preserving original if no mapping.
        """
        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 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)

            default_cols = [str(i + 1) for i in range(len(self.original_df.columns))
                            if self.original_df.columns[i] != self.original_df.columns[int(self.features_col.get()) - 1]]
            self.extra_cols_label.config(text=", ".join(default_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))
                break

    def split_values(self, value, delimiter):
        if any(exc in value for exc in self.exclusions):
            return [value]

        if not isinstance(value, str):
            return []
        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()]
        # Remove dups insensitive to case
        return self.remove_duplicates_from_list(parts)

    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 = {}

        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

        # Build a dict for case variants of values (for normalization dialog)
        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])

            # Fill case_value_variants mapping for all distinct values to build below
            for val in orig_values_set:
                self.case_value_variants[val.lower()].add(val)

            orig_values_joined = "; ".join(orig_values_set)
            # Apply mapping to original values (keys), with case-insensitive normalization:
            orig_values_mapped = []
            parts = [p.strip() for p in re.split(r'[;,]', orig_values_joined) if p.strip()]
            # Remove duplicates case insensitively
            seen = set()
            for p in parts:
                p_mapped = self.mappings.get(p, p)
                # Apply case normalization mapping if exists
                p_mapped = self.apply_case_normalization(p_mapped)
                key = p_mapped.lower()
                if key not in seen:
                    seen.add(key)
                    orig_values_mapped.append(p_mapped)

            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)
            # Apply mapping and case normalization to split result values
            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 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)

                # Apply mapping + case normalization here to each split val
                split_vals_mapped = []
                seen_normalized = set()
                for v in split_vals:
                    v_mapped = self.mappings.get(v, v)
                    v_norm = self.apply_case_normalization(v_mapped)
                    key_norm = v_norm.lower()
                    if key_norm not in seen_normalized:
                        seen_normalized.add(key_norm)
                        split_vals_mapped.append(v_norm)

                item_data[mapped_param] = ", ".join(split_vals_mapped)

            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 export_gui_table(self):
        result_rows = []
        for item in self.tree.get_children():
            values = self.tree.item(item, "values")
            result_rows.append({
                "№": values[0],
                "Исходный параметр": values[1],
                "Исходные значения": values[2],
                "Новый параметр": values[3],
                "Разделитель": values[4],
                "Результат разделения": values[5]
            })

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

        if save_path:
            try:
                df_gui.to_excel(save_path, index=False)
                messagebox.showinfo("Успех", f"Таблица сохранена в {save_path}")
            except Exception as e:
                messagebox.showerror("Ошибка", f"Не удалось сохранить файл: {str(e)}")

    def select_all_mappings(self, event=None):
        self.mapping_tree.selection_set(*self.mapping_tree.get_children())
        return "break"

    def delete_selected_mappings(self, event=None):
        selected = self.mapping_tree.selection()
        if not selected:
            return

        if messagebox.askyesno("Подтверждение", f"Удалить {len(selected)} сопоставлений?"):
            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 not hasattr(self, 'df_columns'):
            messagebox.showinfo("Инфо", "Сначала загрузите файл")
            return

        current_selection = self.extra_cols_label.cget("text").split(", ") if self.extra_cols_label.cget("text") else []
        ColumnSelectorDialog(self.root, self.df_columns, current_selection, self.update_extra_cols)

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

    def show_context_menu(self, event):
        row = self.tree.identify_row(event.y)
        if row:
            if not self.tree.selection():
                self.tree.selection_set(row)
            try:
                self.context_menu.tk_popup(event.x_root, event.y_root)
            finally:
                self.context_menu.grab_release()

    def global_ctrl_c(self, event):
        widget = event.widget
        if isinstance(widget, Entry):
            try:
                selected = widget.selection_get()
                pyperclip.copy(selected)
            except Exception:
                pass
            return "break"
        elif widget == self.tree:
            self.copy_selected_value(event)
            return "break"
        return None

    def global_ctrl_v(self, event):
        try:
            clip = pyperclip.paste()
        except Exception:
            clip = ""

        widget = event.widget
        if isinstance(widget, Entry):
            widget.insert(INSERT, clip)
            return "break"
        return None

    def global_ctrl_z(self, event):
        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)
            except IndexError:
                pass
            return "break"
        return None

    def copy_selected_value(self, event=None):
        focus = self.tree.focus()
        if not focus:
            return

        # event can be None when called programmatically
        col = None
        if event and hasattr(event, 'x'):
            col = self.tree.identify_column(event.x)
        else:
            col = '#1'  # default to first col if unknown

        if not col:
            col = '#1'
        col_idx = int(col.replace('#', '')) - 1
        values = self.tree.item(focus, "values")

        if 0 <= col_idx < len(values):
            pyperclip.copy(str(values[col_idx]))

    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()
            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)
                self.tree.item(item, values=values)

            elif col_index == 5:
                vals = [v.strip() for v in new_value.split(",")]
                self.manual_split_values[orig_param] = new_value
                self.settings["manual_split_values"] = self.manual_split_values.copy()
                self.save_settings()
                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.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 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_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_mapped = [self.mappings.get(v, v) for v in split_vals]
            # Apply case normalization to split values
            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)

            values[5] = ", ".join(split_vals_mapped)
            self.tree.item(item, values=values)

    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 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 = {}

    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 open_case_normalization_dialog(self):
        if not hasattr(self, 'case_value_variants') or not self.case_value_variants:
            # Rebuild mapping from current data if empty or never processed
            self.case_value_variants = defaultdict(set)
            # Try to reconstruct from already processed treeview values too:
            for item in self.tree.get_children():
                values = self.tree.item(item, "values")
                # values[2]: original values string
                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):
            # normalized_dict: lowercased key -> user correct variant (string)
            self.case_normalization = normalized_dict
            self.save_case_normalization()
            # After saving, update UI treeview accordingly
            self.update_main_tree_with_mappings()

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

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

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

Не работает функционал замены выбранного разделителя в поле в столбце "Результат разделения", если среди значений в поле "Результат разделения" находится какое-нибудь значение из поля "Исключения". также непонятно по каким причинам пропадают все значения из поля "Результат разделения", остаются только разделители. Сделай по-умолчанию Разделителем для всех значений в поле "Результат разделения" точку с запятой, а не запятую, как сейчас. Если выделить какое-нибудь значение в любом поле, например, в поле "Новый параметр", а потом нажать Ctrl+V, вставляется сразу 2 одинаковых значения. Сделай для всего приложения глобально возможность работать с быстрыми сочетаниями клавиш, такими как ctrl+c, ctrl+v, ctrl+z (отмена последнего действия), DEL, ctrl+a (выбор всего значения) и тд. Причем сделай так, чтобы сочетания клавиш работали в любой раскладке клавиатуры: и английской, и русской. Добавь вертикальную полосу прокрутки в "Таблицу сопоставлений и поиск". Напиши полный код с учетом необходимых исправлений.