import os
import time
import csv
from datetime import datetime
import xlsxwriter
from PyQt6.QtWidgets import (
    QApplication, QMainWindow, QWidget, QVBoxLayout, QHBoxLayout, QLabel, QPushButton, QLineEdit,
    QTextEdit, QProgressBar, QFileDialog, QMessageBox, QListWidget, QComboBox, QCheckBox, QSpinBox,
    QFontComboBox, QTableWidget, QTableWidgetItem, QHeaderView, QAbstractItemView, QDialog, QColorDialog, QListWidgetItem, QMenu, QGroupBox, QSplitter, QSizePolicy
)
from PyQt6.QtCore import Qt, QThread, pyqtSignal, QSettings, QMimeData, QObject, QTimer, QCoreApplication
from PyQt6.QtGui import QColor, QTextCursor, QDragEnterEvent, QDropEvent, QFont
from concurrent.futures import ThreadPoolExecutor
import weakref
import cProfile
from collections import defaultdict
import traceback


class TSVToExcelConverter(QThread):
    update_progress = pyqtSignal(int)
    log_message = pyqtSignal(str, QColor)

    def __init__(self, tsv_files, output_directory, output_format, auto_open, auto_delete, styles, header_color, split_column, selected_values=None, filter_column=None, filter_values=None, pivot_settings=None):
        super().__init__()
        self.tsv_files = tsv_files
        self.output_directory = output_directory
        self.output_format = output_format
        self.auto_open = auto_open
        self.auto_delete = auto_delete
        self.styles = styles
        self.header_color = header_color
        self.stop_flag = False
        self.split_column = split_column
        self.selected_values = selected_values or []
        self.filter_column = filter_column
        self.filter_values = filter_values or []
        self.pivot_settings = pivot_settings

    def sanitize_sheet_name(self, sheet_name, used_names=None):
        if used_names is None:
            used_names = set()

        invalid_chars = [':', '\\', '/', '?', '*', '[', ']']
        for char in invalid_chars:
            sheet_name = sheet_name.replace(char, '_')

        sheet_name = sheet_name.strip()
        max_length = 29
        sheet_name = sheet_name[:max_length]

        if not sheet_name:
            sheet_name = "Sheet"

        base_name = sheet_name[:29]
        final_name = base_name
        counter = 1
        while final_name.lower() in (name.lower() for name in used_names):
            suffix = f"_{counter}"
            final_name = f"{base_name[:29-len(suffix)]}{suffix}"
            counter += 1

        used_names.add(final_name)
        return final_name

    def check_network_path(self, path):
        try:
            is_network_path = path.startswith('\\\\') or ':' in path and path.split(':')[0] not in ['C', 'D', 'E']
            if is_network_path:
                self.log_message.emit("Идет загрузка из сетевого диска, пожалуйста подождите!", QColor("orange"))
                QCoreApplication.processEvents()
                with open(path, 'rb') as f:
                    f.read(1)
                    f.seek(0)
                self.log_message.emit("", QColor("black"))
                self.log_message.emit(f"{datetime.now().strftime('%H:%M:%S')} Файл успешно загружен с сетевого диска", QColor("green"))
                QCoreApplication.processEvents()
            return True
        except Exception as e:
            self.log_message.emit(f"Ошибка при проверке доступности файла: {str(e)}", QColor("red"))
            return False

    def run(self):
        try:
            total_rows = 0
            for tsv_file in self.tsv_files:
                with open(tsv_file, 'r', encoding='utf-8') as file:
                    reader = csv.reader(file, delimiter='\t')
                    headers = next(reader)
                    filter_column_index = headers.index(self.filter_column) if self.filter_column in headers and self.filter_column != "Не фильтровать" else None
                    for row in reader:
                        if self.passes_filter(row, filter_column_index):
                            total_rows += 1

            processed_rows = 0
            self.log_message.emit(f"Всего строк для обработки (с учетом фильтров): {total_rows}", QColor("blue"))

            for tsv_file in self.tsv_files:
                if self.stop_flag:
                    break
                rows_processed = self.convert_file(tsv_file, total_rows, processed_rows)
                if rows_processed:
                    processed_rows += rows_processed
                else:
                    self.log_message.emit(f"Ошибка при обработке файла {tsv_file}", QColor("red"))

        except Exception as e:
            self.log_message.emit(f"Ошибка в потоке конвертации: {str(e)}", QColor("red"))

    def passes_filter(self, row, filter_column_index):
        if filter_column_index is None or not self.filter_values:
            return True
        return row[filter_column_index] in self.filter_values

    def convert_file(self, tsv_file, total_rows, processed_rows):
        try:
            start_time = time.time()
            base_name = os.path.splitext(os.path.basename(tsv_file))[0]
            timestamp = datetime.now().strftime("%Y-%m-%d %H-%M-%S")
            output_file_name = f"{base_name}_{timestamp}.{self.output_format}"
            self.output_file_path = os.path.join(self.output_directory, output_file_name)

            self.log_message.emit(f"{datetime.now().strftime('%H:%M:%S')} Начало чтения файла: {tsv_file}", QColor("black"))

            workbook_options = {
                'use_zip64': True,
                'constant_memory': True,
                'in_memory': False,
                'strings_to_numbers': True,
                'default_date_format': 'yyyy-mm-dd'
            }
            workbook = xlsxwriter.Workbook(self.output_file_path, workbook_options)

            self.cached_formats = {
                'cell': workbook.add_format({
                    'font_size': self.styles['font_size'],
                    'font_name': self.styles['font_name'],
                    'border': self.styles['border']
                }),
                'header': workbook.add_format({
                    'bold': self.styles['bold'],
                    'italic': self.styles['italic'],
                    'font_size': self.styles['font_size'],
                    'font_name': self.styles['font_name'],
                    'border': self.styles['border'],
                    'bg_color': self.header_color
                })
            }

            with open(tsv_file, 'r', encoding='utf-8') as file:
                reader = csv.reader(file, delimiter='\t')
                headers = next(reader)
                split_column_index = headers.index(self.split_column) if self.split_column in headers and self.split_column != "Не разделять" else None
                filter_column_index = headers.index(self.filter_column) if self.filter_column in headers and self.filter_column != "Не фильтровать" else None

                MAX_ROWS_PER_SHEET = 1000000
                used_sheet_names = set()
                current_file_rows = 0

                update_frequency = max(1, total_rows // 100)

                if split_column_index is not None:
                    worksheets = {}
                    sheet_row_counts = {}
                    others_worksheets = []
                    current_others_sheet = None
                    others_row_count = 0

                    for row_num, row in enumerate(reader, 1):
                        if self.stop_flag:
                            self.log_message.emit("Процесс конвертации прерван пользователем", QColor("orange"))
                            return False

                        if not self.passes_filter(row, filter_column_index):
                            continue

                        current_file_rows += 1
                        if current_file_rows % update_frequency == 0:
                            self.log_message.emit(
                                f"{datetime.now().strftime('%H:%M:%S')} Обработано строк: "
                                f"{processed_rows + current_file_rows} из {total_rows}",
                                QColor("black")
                            )
                            progress = int(((processed_rows + current_file_rows) / total_rows) * 100)
                            self.update_progress.emit(progress)

                        value = row[split_column_index]

                        if value in self.selected_values:
                            if value not in worksheets:
                                safe_value = self.sanitize_sheet_name(value, used_sheet_names)
                                worksheets[value] = [workbook.add_worksheet(safe_value)]
                                sheet_row_counts[value] = [1]
                                for col, header in enumerate(headers):
                                    worksheets[value][0].write(0, col, header, self.cached_formats['header'])

                            current_sheet_index = len(worksheets[value]) - 1
                            current_row_count = sheet_row_counts[value][current_sheet_index]

                            if current_row_count >= MAX_ROWS_PER_SHEET:
                                safe_value = self.sanitize_sheet_name(value, used_sheet_names)
                                new_sheet = workbook.add_worksheet(safe_value)
                                worksheets[value].append(new_sheet)
                                sheet_row_counts[value].append(1)
                                for col, header in enumerate(headers):
                                    new_sheet.write(0, col, header, self.cached_formats['header'])
                                current_sheet_index = len(worksheets[value]) - 1
                                current_row_count = 1

                            current_sheet = worksheets[value][current_sheet_index]
                            current_sheet.write_row(current_row_count, 0, row, self.cached_formats['cell'])
                            sheet_row_counts[value][current_sheet_index] += 1

                        else:
                            if not others_worksheets or others_row_count >= MAX_ROWS_PER_SHEET:
                                safe_value = self.sanitize_sheet_name("Остальные проекты", used_sheet_names)
                                current_others_sheet = workbook.add_worksheet(safe_value)
                                others_worksheets.append(current_others_sheet)
                                others_row_count = 1
                                for col, header in enumerate(headers):
                                    current_others_sheet.write(0, col, header, self.cached_formats['header'])

                            current_others_sheet.write_row(others_row_count, 0, row, self.cached_formats['cell'])
                            others_row_count += 1

                else:
                    current_sheet = None
                    current_row_count = 0
                    sheet_number = 1

                    for row_num, row in enumerate(reader, 1):
                        if self.stop_flag:
                            self.log_message.emit("Процесс конвертации прерван пользователем", QColor("orange"))
                            return False

                        if not self.passes_filter(row, filter_column_index):
                            continue

                        current_file_rows += 1
                        if current_file_rows % update_frequency == 0:
                            self.log_message.emit(
                                f"{datetime.now().strftime('%H:%M:%S')} Обработано строк: "
                                f"{processed_rows + current_file_rows} из {total_rows}",
                                QColor("black")
                            )
                            progress = int(((processed_rows + current_file_rows) / total_rows) * 100)
                            self.update_progress.emit(progress)

                        if current_sheet is None or current_row_count >= MAX_ROWS_PER_SHEET:
                            safe_value = self.sanitize_sheet_name("Все проекты", used_sheet_names)
                            current_sheet = workbook.add_worksheet(safe_value)
                            current_row_count = 0
                            for col, header in enumerate(headers):
                                current_sheet.write(0, col, header, self.cached_formats['header'])
                            current_row_count = 1

                        current_sheet.write_row(current_row_count, 0, row, self.cached_formats['cell'])
                        current_row_count += 1

            if self.pivot_settings:
                try:
                    self.log_message.emit("Начало создания сводной таблицы...", QColor("blue"))
                    with open(tsv_file, 'r', encoding='utf-8') as file:
                        reader = csv.reader(file, delimiter='\t')
                        data = list(reader)

                    pivot_data = self.create_pivot_table(data, self.pivot_settings)
                    if pivot_data:
                        self.write_pivot_table(workbook, pivot_data, self.pivot_settings)
                except Exception as e:
                    self.log_message.emit(f"Ошибка при обработке сводной таблицы: {str(e)}", QColor("red"))

            self.log_message.emit(f"{datetime.now().strftime('%H:%M:%S')} Сохранение файла Excel", QColor("black"))
            workbook.close()

            end_time = time.time()
            processing_time = end_time - start_time
            minutes = int(processing_time // 60)
            seconds = int(processing_time % 60)
            time_format = f"{minutes:02d}:{seconds:02d}"

            self.log_message.emit(f"{datetime.now().strftime('%H:%M:%S')} Файл успешно обработан и сохранен за {time_format}", QColor("green"))

            return current_file_rows

        except Exception as e:
            self.log_message.emit(f"{datetime.now().strftime('%H:%M:%S')} Ошибка при обработке файла {tsv_file}: {str(e)}", QColor("red"))
            return False

    def stop(self):
        self.stop_flag = True

    def create_pivot_table(self, data, settings):
        try:
            self.log_message.emit("Начало создания сводной таблицы...", QColor("blue"))

            headers = data[0]
            try:
                row_indices = [headers.index(row) for row in settings['rows']]
                col_indices = [headers.index(col) for col in settings['columns']]
                val_indices = [headers.index(val) for val in settings['values']]

                if not (row_indices or col_indices or val_indices):
                    self.log_message.emit("Не выбраны поля для сводной таблицы", QColor("red"))
                    return None

            except ValueError as e:
                self.log_message.emit(f"Ошибка: не найдена одна из колонок для сводной таблицы: {str(e)}", QColor("red"))
                return None

            filtered_data = [data[0]]
            filtered_count = 0
            total_count = len(data) - 1

            filter_column = settings.get('filter_column')
            filter_values = settings.get('filter_values', set())

            if filter_column and filter_values:
                try:
                    filter_idx = headers.index(filter_column)
                    self.log_message.emit(
                        f"Применяется фильтр:\n"
                        f"Столбец: {filter_column}\n"
                        f"Разрешенные значения: {', '.join(filter_values)}",
                        QColor("blue")
                    )

                    for row in data[1:]:
                        if len(row) > filter_idx and row[filter_idx] in filter_values:
                            filtered_data.append(row)
                            filtered_count += 1

                except Exception as e:
                    self.log_message.emit(f"Ошибка при фильтрации: {str(e)}", QColor("red"))
                    return None
            else:
                filtered_data = data
                filtered_count = total_count

            self.log_message.emit(
                f"Фильтрация завершена:\n"
                f"Всего строк: {total_count}\n"
                f"Прошло фильтр: {filtered_count}\n"
                f"Отфильтровано: {total_count - filtered_count}",
                QColor("blue")
            )

            if settings.get('remove_duplicates', False):
                self.log_message.emit("Удаление дубликатов...", QColor("blue"))
                unique_data = [filtered_data[0]]
                seen_combinations = set()

                for row in filtered_data[1:]:
                    try:
                        row_key = tuple(row[idx] for idx in row_indices)
                        col_key = tuple(row[idx] for idx in col_indices)
                        val_key = tuple(row[idx] for idx in val_indices)
                        combination = row_key + col_key + val_key

                        if combination not in seen_combinations:
                            seen_combinations.add(combination)
                            unique_data.append(row)
                    except Exception as e:
                        self.log_message.emit(f"Пропущена строка при удалении дубликатов: {str(e)}", QColor("orange"))
                        continue

                filtered_data = unique_data

            pivot_data = {}

            for row in filtered_data[1:]:
                try:
                    row_key = tuple(row[idx] for idx in row_indices) if row_indices else ("Итого",)
                    col_key = tuple(row[idx] for idx in col_indices) if col_indices else ("Итого",)
                    values = [row[idx] for idx in val_indices]

                    if row_key not in pivot_data:
                        pivot_data[row_key] = {}
                    if col_key not in pivot_data[row_key]:
                        if settings['aggregation'] == "Количество":
                            pivot_data[row_key][col_key] = 0
                        else:
                            pivot_data[row_key][col_key] = []

                    if settings['aggregation'] == "Количество":
                        pivot_data[row_key][col_key] += 1
                    else:
                        try:
                            num_values = [float(val) if str(val).replace('.', '', 1).isdigit() else 0 for val in values]
                            pivot_data[row_key][col_key].extend(num_values)
                        except (ValueError, AttributeError):
                            continue

                except Exception as e:
                    self.log_message.emit(f"Пропущена строка из-за ошибки: {str(e)}", QColor("orange"))
                    continue

            if settings['aggregation'] != "Количество":
                aggregated_data = {}
                for row_key in pivot_data:
                    aggregated_data[row_key] = {}
                    for col_key in pivot_data[row_key]:
                        values = pivot_data[row_key][col_key]
                        if values:
                            if settings['aggregation'] == "Сумма":
                                aggregated_data[row_key][col_key] = sum(values)
                            elif settings['aggregation'] == "Среднее":
                                aggregated_data[row_key][col_key] = sum(values) / len(values)
                            elif settings['aggregation'] == "Максимум":
                                aggregated_data[row_key][col_key] = max(values)
                            elif settings['aggregation'] == "Минимум":
                                aggregated_data[row_key][col_key] = min(values)
                return aggregated_data

            return pivot_data

        except Exception as e:
            self.log_message.emit(f"Критическая ошибка при создании сводной таблицы: {str(e)}", QColor("red"))
            return None

    def write_pivot_table(self, workbook, pivot_data, settings):
        try:
            if pivot_data is None:
                self.log_message.emit("Нет данных для создания сводной таблицы", QColor("red"))
                return

            self.log_message.emit("Запись сводной таблицы в Excel...", QColor("blue"))
            worksheet = workbook.add_worksheet("Сводная таблица")

            row_values = sorted(pivot_data.keys())
            col_values = sorted(set(col for row in pivot_data.values() for col in row.keys()))

            current_col = 0
            for row_header in settings['rows']:
                worksheet.write(0, current_col, row_header, self.cached_formats['header'])
                current_col += 1

            for col_idx, col_value in enumerate(col_values, current_col):
                if isinstance(col_value, tuple):
                    header_text = " / ".join(str(v) for v in col_value)
                else:
                    header_text = str(col_value)
                worksheet.write(0, col_idx, header_text, self.cached_formats['header'])

            for row_idx, row_value in enumerate(row_values, 1):
                if isinstance(row_value, tuple):
                    for i, value in enumerate(row_value):
                        worksheet.write(row_idx, i, value, self.cached_formats['header'])
                else:
                    worksheet.write(row_idx, 0, row_value, self.cached_formats['header'])

                for col_idx, col_value in enumerate(col_values, len(settings['rows'])):
                    value = pivot_data[row_value].get(col_value, 0)
                    if isinstance(value, (int, float)):
                        if settings['aggregation'] == "Среднее":
                            worksheet.write(row_idx, col_idx, round(value, 2), self.cached_formats['cell'])
                        else:
                            worksheet.write(row_idx, col_idx, value, self.cached_formats['cell'])
                    else:
                        worksheet.write(row_idx, col_idx, str(value), self.cached_formats['cell'])

            for col_idx in range(len(settings['rows']) + len(col_values)):
                worksheet.set_column(col_idx, col_idx, 15)

            self.log_message.emit("Сводная таблица успешно записана в файл", QColor("green"))

        except Exception as e:
            self.log_message.emit(f"Ошибка при записи сводной таблицы: {str(e)}", QColor("red"))


class TSVPreviewDialog(QDialog):
    def __init__(self, tsv_file):
        super().__init__()
        self.setWindowTitle(f"Предварительный просмотр файла: {os.path.basename(tsv_file)}")
        self.setGeometry(100, 100, 1000, 700)
        self.tsv_file = tsv_file
        self.current_page = 1
        self.rows_per_page = 100
        self.total_rows = 0
        self.headers = []
        self.search_results = []
        self.current_search_index = -1

        layout = QVBoxLayout()
        self.setLayout(layout)

        search_layout = QHBoxLayout()
        self.search_input = QLineEdit()
        self.search_input.setPlaceholderText("Поиск...")
        self.search_input.returnPressed.connect(self.search)
        search_layout.addWidget(self.search_input)

        self.search_column_combo = QComboBox()
        self.search_column_combo.addItem("Все столбцы")
        search_layout.addWidget(self.search_column_combo)

        self.search_button = QPushButton("Найти")
        self.search_button.clicked.connect(self.search)
        search_layout.addWidget(self.search_button)

        self.next_result_button = QPushButton("Следующий")
        self.next_result_button.clicked.connect(self.next_search_result)
        self.next_result_button.setEnabled(False)
        search_layout.addWidget(self.next_result_button)

        self.prev_result_button = QPushButton("Предыдущий")
        self.prev_result_button.clicked.connect(self.prev_search_result)
        self.prev_result_button.setEnabled(False)
        search_layout.addWidget(self.prev_result_button)

        self.search_info = QLabel("")
        search_layout.addWidget(self.search_info)

        layout.addLayout(search_layout)

        self.table_widget = QTableWidget(self)
        layout.addWidget(self.table_widget)

        nav_layout = QHBoxLayout()
        self.prev_page_button = QPushButton("← Предыдущая")
        self.prev_page_button.clicked.connect(self.prev_page)
        nav_layout.addWidget(self.prev_page_button)

        self.page_info = QLabel()
        nav_layout.addWidget(self.page_info)

        self.next_page_button = QPushButton("Следующая →")
        self.next_page_button.clicked.connect(self.next_page)
        nav_layout.addWidget(self.next_page_button)

        nav_layout.addWidget(QLabel("Строк на странице:"))
        self.rows_per_page_combo = QComboBox()
        self.rows_per_page_combo.addItems(["50", "100", "200", "500", "1000"])
        self.rows_per_page_combo.setCurrentText(str(self.rows_per_page))
        self.rows_per_page_combo.currentTextChanged.connect(self.change_rows_per_page)
        nav_layout.addWidget(self.rows_per_page_combo)

        layout.addLayout(nav_layout)

        self.load_file_info()
        self.load_page_data()

    def load_file_info(self):
        try:
            with open(self.tsv_file, 'r', encoding='utf-8') as file:
                self.headers = next(csv.reader(file, delimiter='\t'))
                self.search_column_combo.addItems(self.headers)
                self.total_rows = sum(1 for _ in file)
                self.update_page_info()
        except Exception as e:
            QMessageBox.critical(self, "Ошибка", f"Ошибка при чтении файла: {str(e)}")

    def load_page_data(self):
        try:
            start_row = (self.current_page - 1) * self.rows_per_page

            self.table_widget.clear()
            self.table_widget.setRowCount(0)
            self.table_widget.setColumnCount(len(self.headers))
            self.table_widget.setHorizontalHeaderLabels(self.headers)

            with open(self.tsv_file, 'r', encoding='utf-8') as file:
                reader = csv.reader(file, delimiter='\t')
                next(reader)

                for _ in range(start_row):
                    next(reader, None)

                for row_idx in range(self.rows_per_page):
                    row_data = next(reader, None)
                    if row_data is None:
                        break

                    self.table_widget.insertRow(row_idx)
                    for col_idx, value in enumerate(row_data):
                        item = QTableWidgetItem(value)
                        self.table_widget.setItem(row_idx, col_idx, item)

            self.table_widget.resizeColumnsToContents()

        except Exception as e:
            QMessageBox.critical(self, "Ошибка", f"Ошибка при загрузке данных: {str(e)}")

    def update_page_info(self):
        total_pages = (self.total_rows + self.rows_per_page - 1) // self.rows_per_page
        self.page_info.setText(f"Страница {self.current_page} из {total_pages} (Всего строк: {self.total_rows})")

        self.prev_page_button.setEnabled(self.current_page > 1)
        self.next_page_button.setEnabled(self.current_page < total_pages)

    def next_page(self):
        self.current_page += 1
        self.load_page_data()
        self.update_page_info()

    def prev_page(self):
        self.current_page -= 1
        self.load_page_data()
        self.update_page_info()

    def change_rows_per_page(self, value):
        self.rows_per_page = int(value)
        self.current_page = 1
        self.load_page_data()
        self.update_page_info()

    def search(self):
        search_text = self.search_input.text().lower()
        if not search_text:
            return

        self.search_results = []
        selected_column = self.search_column_combo.currentText()

        try:
            with open(self.tsv_file, 'r', encoding='utf-8') as file:
                reader = csv.reader(file, delimiter='\t')
                headers = next(reader)
                column_index = headers.index(selected_column) if selected_column != "Все столбцы" else None

                for row_idx, row in enumerate(reader, 1):
                    if column_index is not None:
                        if search_text in row[column_index].lower():
                            self.search_results.append(row_idx)
                    else:
                        if any(search_text in cell.lower() for cell in row):
                            self.search_results.append(row_idx)

            if self.search_results:
                self.current_search_index = 0
                self.go_to_search_result()
                self.update_search_buttons()
            else:
                self.search_info.setText("Ничего не найдено")
                self.update_search_buttons(False)

        except Exception as e:
            QMessageBox.critical(self, "Ошибка", f"Ошибка при поиске: {str(e)}")

    def go_to_search_result(self):
        if not self.search_results:
            return

        row_number = self.search_results[self.current_search_index]
        page = (row_number - 1) // self.rows_per_page + 1

        if page != self.current_page:
            self.current_page = page
            self.load_page_data()
            self.update_page_info()

        row_in_page = (row_number - 1) % self.rows_per_page
        self.table_widget.selectRow(row_in_page)
        self.table_widget.scrollToItem(self.table_widget.item(row_in_page, 0))

        self.search_info.setText(f"Результат {self.current_search_index + 1} из {len(self.search_results)}")

    def next_search_result(self):
        if self.search_results:
            self.current_search_index = (self.current_search_index + 1) % len(self.search_results)
            self.go_to_search_result()
            self.update_search_buttons()

    def prev_search_result(self):
        if self.search_results:
            self.current_search_index = (self.current_search_index - 1) % len(self.search_results)
            self.go_to_search_result()
            self.update_search_buttons()

    def update_search_buttons(self, enable=True):
        self.next_result_button.setEnabled(enable and len(self.search_results) > 1)
        self.prev_result_button.setEnabled(enable and len(self.search_results) > 1)


class ColumnValuesDialog(QDialog):
    def __init__(self, values):
        super().__init__()
        self.setWindowTitle("Выберите значения для разделения")
        self.setGeometry(100, 100, 500, 600)

        layout = QVBoxLayout()
        self.setLayout(layout)

        search_layout = QHBoxLayout()
        self.search_input = QLineEdit()
        self.search_input.setPlaceholderText("Поиск значений...")
        self.search_input.textChanged.connect(self.filter_values)
        search_layout.addWidget(self.search_input)
        layout.addLayout(search_layout)

        self.values_list = QListWidget()
        self.values_list.setSelectionMode(QAbstractItemView.SelectionMode.NoSelection)
        for value in sorted(values):
            item = QListWidgetItem(value)
            item.setFlags(item.flags() | Qt.ItemFlag.ItemIsUserCheckable)
            item.setCheckState(Qt.CheckState.Unchecked)
            self.values_list.addItem(item)
        layout.addWidget(self.values_list)

        self.info_label = QLabel("Выбрано: 0 из 0")
        layout.addWidget(self.info_label)

        buttons_layout = QHBoxLayout()
        self.select_all_btn = QPushButton("Выбрать все")
        self.select_all_btn.clicked.connect(self.select_all)
        buttons_layout.addWidget(self.select_all_btn)

        self.clear_all_btn = QPushButton("Очистить все")
        self.clear_all_btn.clicked.connect(self.clear_all)
        buttons_layout.addWidget(self.clear_all_btn)

        self.ok_btn = QPushButton("OK")
        self.ok_btn.clicked.connect(self.accept)
        buttons_layout.addWidget(self.ok_btn)

        layout.addLayout(buttons_layout)

        self.apply_styles()
        self.update_info_label()
        self.values_list.itemChanged.connect(self.update_info_label)

    def apply_styles(self):
        self.setStyleSheet("""
            QDialog {
                background-color: #f0f0f0;
            }
            QListWidget {
                background-color: white;
                border: 1px solid #ccc;
                border-radius: 5px;
                padding: 5px;
                font-size: 14px;
            }
            QLineEdit {
                padding: 8px;
                border: 1px solid #ccc;
                border-radius: 5px;
                font-size: 14px;
                margin-bottom: 10px;
            }
            QPushButton {
                background-color: #4CAF50;
                color: white;
                border: none;
                padding: 8px 16px;
                border-radius: 5px;
                font-size: 14px;
                min-width: 100px;
            }
            QPushButton:hover {
                background-color: #45a049;
            }
            QPushButton:pressed {
                background-color: #3d8b40;
            }
            QLabel {
                font-size: 14px;
                color: #333;
                margin: 5px 0;
            }
        """)

    def filter_values(self, text):
        for i in range(self.values_list.count()):
            item = self.values_list.item(i)
            item.setHidden(text.lower() not in item.text().lower())

    def select_all(self):
        for i in range(self.values_list.count()):
            item = self.values_list.item(i)
            if not item.isHidden():
                item.setCheckState(Qt.CheckState.Checked)

    def clear_all(self):
        for i in range(self.values_list.count()):
            item = self.values_list.item(i)
            if not item.isHidden():
                item.setCheckState(Qt.CheckState.Unchecked)

    def update_info_label(self):
        total_visible = sum(1 for i in range(self.values_list.count()) if not self.values_list.item(i).isHidden())
        selected = sum(1 for i in range(self.values_list.count())
                      if not self.values_list.item(i).isHidden() and
                      self.values_list.item(i).checkState() == Qt.CheckState.Checked)
        self.info_label.setText(f"Выбрано: {selected} из {total_visible}")

    def get_selected_values(self):
        return [self.values_list.item(i).text()
                for i in range(self.values_list.count())
                if self.values_list.item(i).checkState() == Qt.CheckState.Checked]


class PivotSettings(QDialog):
    def __init__(self, columns):
        super().__init__()
        self.setWindowTitle("Настройки сводной таблицы")
        self.setGeometry(100, 100, 600, 400)

        layout = QVBoxLayout()
        self.setLayout(layout)

        self.remove_duplicates = QCheckBox("Удалить дубликаты", self)
        layout.addWidget(self.remove_duplicates)

        row_layout = QVBoxLayout()
        row_layout.addWidget(QLabel("Строки:"))
        self.row_list = QListWidget()
        self.row_list.setSelectionMode(QAbstractItemView.SelectionMode.MultiSelection)
        self.row_list.addItems(columns)
        row_layout.addWidget(self.row_list)
        layout.addLayout(row_layout)

        col_layout = QVBoxLayout()
        col_layout.addWidget(QLabel("Столбцы:"))
        self.col_list = QListWidget()
        self.col_list.setSelectionMode(QAbstractItemView.SelectionMode.MultiSelection)
        self.col_list.addItems(columns)
        col_layout.addWidget(self.col_list)
        layout.addLayout(col_layout)

        val_layout = QVBoxLayout()
        val_layout.addWidget(QLabel("Значения:"))
        self.val_list = QListWidget()
        self.val_list.setSelectionMode(QAbstractItemView.SelectionMode.MultiSelection)
        self.val_list.addItems(columns)
        val_layout.addWidget(self.val_list)
        layout.addLayout(val_layout)

        agg_layout = QHBoxLayout()
        agg_layout.addWidget(QLabel("Агрегация:"))
        self.agg_combo = QComboBox()
        self.agg_combo.addItems(["Сумма", "Среднее", "Количество", "Максимум", "Минимум"])
        agg_layout.addWidget(self.agg_combo)
        layout.addLayout(agg_layout)

        buttons = QHBoxLayout()
        ok_button = QPushButton("OK")
        ok_button.clicked.connect(self.validate_and_accept)
        cancel_button = QPushButton("Отмена")
        cancel_button.clicked.connect(self.reject)
        buttons.addWidget(ok_button)
        buttons.addWidget(cancel_button)
        layout.addLayout(buttons)

    def validate_and_accept(self):
        if not (self.row_list.selectedItems() or
                self.col_list.selectedItems() or
                self.val_list.selectedItems()):
            QMessageBox.warning(self, "Предупреждение",
                                "Необходимо выбрать хотя бы одно поле для создания сводной таблицы")
            return
        self.accept()

    def get_settings(self):
        return {
            'rows': [item.text() for item in self.row_list.selectedItems()],
            'columns': [item.text() for item in self.col_list.selectedItems()],
            'values': [item.text() for item in self.val_list.selectedItems()],
            'aggregation': self.agg_combo.currentText(),
            'remove_duplicates': self.remove_duplicates.isChecked()
        }


class SettingsDialog(QDialog):
    def __init__(self, parent=None):
        super().__init__(parent)
        self.parent = parent
        self.initUI()
        self.loadSettings()

    def initUI(self):
        self.setWindowTitle("Настройки")
        self.setModal(True)
        layout = QVBoxLayout(self)

        general_group = QGroupBox("Основные настройки")
        general_layout = QVBoxLayout()

        theme_layout = QHBoxLayout()
        theme_layout.addWidget(QLabel("Тема оформления:"))
        self.theme_combo = QComboBox()
        self.theme_combo.addItems(["Светлая", "Тёмная"])
        theme_layout.addWidget(self.theme_combo)
        general_layout.addLayout(theme_layout)

        scale_layout = QHBoxLayout()
        scale_layout.addWidget(QLabel("Масштаб интерфейса:"))
        self.scale_combo = QComboBox()
        self.scale_combo.addItems(["75%", "100%", "125%", "150%", "175%", "200%"])
        self.scale_combo.setCurrentText("100%")
        self.scale_combo.currentTextChanged.connect(self.updatePreview)
        scale_layout.addWidget(self.scale_combo)
        general_layout.addLayout(scale_layout)

        self.preview_label = QLabel("Пример текста")
        self.preview_label.setAlignment(Qt.AlignmentFlag.AlignCenter)
        general_layout.addWidget(self.preview_label)

        path_group = QGroupBox("Путь сохранения по умолчанию")
        path_layout = QHBoxLayout()

        self.default_path = QLineEdit()
        self.default_path.setPlaceholderText("Выберите путь для сохранения файлов по умолчанию")
        path_layout.addWidget(self.default_path)

        browse_button = QPushButton("Обзор")
        browse_button.clicked.connect(self.browse_default_path)
        path_layout.addWidget(browse_button)

        path_group.setLayout(path_layout)
        general_layout.addWidget(path_group)

        self.auto_open = QCheckBox("Автоматически открывать файл после конвертации")
        general_layout.addWidget(self.auto_open)

        self.auto_delete = QCheckBox("Автоматически удалять исходный файл после конвертации")
        general_layout.addWidget(self.auto_delete)

        general_group.setLayout(general_layout)
        layout.addWidget(general_group)

        buttons = QHBoxLayout()
        save_btn = QPushButton("Сохранить")
        save_btn.clicked.connect(self.saveSettings)
        cancel_btn = QPushButton("Отмена")
        cancel_btn.clicked.connect(self.reject)
        buttons.addWidget(save_btn)
        buttons.addWidget(cancel_btn)
        layout.addLayout(buttons)

    def browse_default_path(self):
        directory = QFileDialog.getExistingDirectory(self, "Выберите директорию для сохранения")
        if directory:
            self.default_path.setText(directory)

    def updatePreview(self, scale_text):
        try:
            scale = int(scale_text.rstrip('%'))
            font = self.preview_label.font()
            base_size = 9
            font.setPointSize(int(base_size * scale / 100))
            self.preview_label.setFont(font)
        except Exception as e:
            if self.parent:
                self.parent.log_message(f"Ошибка при обновлении предпросмотра: {str(e)}", QColor("red"))

    def loadSettings(self):
        settings = QSettings("MyCompany", "TSVToExcelConverter")
        self.theme_combo.setCurrentText(settings.value("theme", "Светлая"))
        self.scale_combo.setCurrentText(settings.value("scale", "100%"))
        self.default_path.setText(settings.value("default_save_path", ""))
        self.auto_open.setChecked(settings.value("auto_open", False, type=bool))
        self.auto_delete.setChecked(settings.value("auto_delete", False, type=bool))
        self.updatePreview(self.scale_combo.currentText())

    def saveSettings(self):
        settings = QSettings("MyCompany", "TSVToExcelConverter")

        old_theme = settings.value("theme", "Светлая")
        old_scale = settings.value("scale", "100%")

        settings.setValue("theme", self.theme_combo.currentText())
        settings.setValue("scale", self.scale_combo.currentText())
        settings.setValue("default_save_path", self.default_path.text())
        settings.setValue("auto_open", self.auto_open.isChecked())
        settings.setValue("auto_delete", self.auto_delete.isChecked())

        if old_theme != self.theme_combo.currentText():
            if self.theme_combo.currentText() == "Тёмная":
                self.parent.apply_dark_theme()
            else:
                self.parent.apply_light_theme()

        if old_scale != self.scale_combo.currentText():
            self.applyScale()

        if self.default_path.text():
            self.parent.output_directory_path.setText(self.default_path.text())

        self.accept()

    def applyScale(self):
        try:
            scale_text = self.scale_combo.currentText()
            scale = int(scale_text.rstrip('%'))

            widgets = self.parent.findChildren(QWidget)
            base_sizes = {
                QLabel: 9,
                QPushButton: 9,
                QLineEdit: 9,
                QTextEdit: 9,
                QComboBox: 9,
                QCheckBox: 9,
                QSpinBox: 9,
                QListWidget: 9
            }

            for widget in widgets:
                for widget_type, base_size in base_sizes.items():
                    if isinstance(widget, widget_type):
                        font = widget.font()
                        font.setPointSize(int(base_size * scale / 100))
                        widget.setFont(font)
                        break

            self.parent.set_button_sizes()
            self.parent.log_message(f"Масштаб интерфейса изменен на {scale_text}", QColor("green"))

        except Exception as e:
            self.parent.log_message(f"Ошибка при изменении масштаба: {str(e)}", QColor("red"))


class TSVToExcelApp(QMainWindow):
    def __init__(self):
        super().__init__()
        self.initUI()
        self.settings = QSettings("MyCompany", "TSVToExcelConverter")
        self.converter_ref = None
        self.timer = QTimer(self)
        self.timer.timeout.connect(self.update_timer)
        self.start_time = None
        self.filter_values = {}
        self.selected_column_values = {}
        self.current_total_rows = 0

        scale_text = self.settings.value("scale", "100%")
        self.applyScale(scale_text)

        default_path = self.settings.value("default_save_path", "")
        if default_path:
            self.output_directory_path.setText(default_path)

        theme = self.settings.value("theme", "Светлая")
        if theme == "Тёмная":
            self.apply_dark_theme()
        else:
            self.apply_light_theme()

    def initUI(self):
        self.setWindowTitle("TSV to Excel Converter")
        self.setGeometry(100, 100, 800, 600)

        central_widget = QWidget(self)
        self.setCentralWidget(central_widget)

        layout = QVBoxLayout()
        central_widget.setLayout(layout)

        splitter = QSplitter(Qt.Orientation.Vertical)

        top_widget = QWidget()
        top_layout = QVBoxLayout(top_widget)

        file_layout = QHBoxLayout()
        self.tsv_file_list = QListWidget(self)
        self.tsv_file_list.setAcceptDrops(True)
        self.tsv_file_list.dragEnterEvent = self.dragEnterEvent
        self.tsv_file_list.dropEvent = self.dropEvent
        self.tsv_file_list.setFixedHeight(150)
        file_layout.addWidget(self.tsv_file_list)

        button_layout = QVBoxLayout()
        self.select_tsv_button = QPushButton("Добавить файлы", self)
        self.select_tsv_button.clicked.connect(self.select_tsv_files)
        button_layout.addWidget(self.select_tsv_button)

        self.preview_button = QPushButton("Предварительный просмотр", self)
        self.preview_button.clicked.connect(self.preview_tsv_file)
        button_layout.addWidget(self.preview_button)

        self.remove_file_button = QPushButton("Удалить файл", self)
        self.remove_file_button.clicked.connect(self.remove_selected_file)
        button_layout.addWidget(self.remove_file_button)

        file_layout.addLayout(button_layout)
        top_layout.addLayout(file_layout)

        dir_layout = QHBoxLayout()
        self.output_directory_path = QLineEdit(self)
        self.output_directory_path.setPlaceholderText("Выберите директорию для сохранения")
        dir_layout.addWidget(self.output_directory_path)
        self.select_dir_button = QPushButton("Обзор", self)
        self.select_dir_button.clicked.connect(self.select_output_directory)
        dir_layout.addWidget(self.select_dir_button)
        top_layout.addLayout(dir_layout)

        format_layout = QHBoxLayout()
        self.output_format_combo = QComboBox(self)
        self.output_format_combo.addItems(["xlsx", "csv", "ods"])
        format_layout.addWidget(QLabel("Формат выходного файла:"))
        format_layout.addWidget(self.output_format_combo)
        top_layout.addLayout(format_layout)

        column_layout = QHBoxLayout()
        self.column_combo = QComboBox(self)
        self.column_combo.addItem("Не разделять")
        column_layout.addWidget(QLabel("Разделить по столбцу:"))
        column_layout.addWidget(self.column_combo)
        top_layout.addLayout(column_layout)

        filter_layout = QHBoxLayout()
        self.filter_combo = QComboBox(self)
        self.filter_combo.addItem("Не фильтровать")
        filter_layout.addWidget(QLabel("Фильтровать по столбцу:"))
        filter_layout.addWidget(self.filter_combo)
        top_layout.addLayout(filter_layout)

        styles_layout = QHBoxLayout()
        self.bold_checkbox = QCheckBox("Жирный шрифт", self)
        self.italic_checkbox = QCheckBox("Курсив", self)
        self.font_size_spinbox = QSpinBox(self)
        self.font_size_spinbox.setRange(8, 24)
        self.font_size_spinbox.setValue(12)
        self.font_combo = QFontComboBox(self)
        self.border_checkbox = QCheckBox("Выделить все границы", self)
        self.header_color_button = QPushButton("Выбрать цвет шапки", self)
        self.header_color_button.clicked.connect(self.select_header_color)
        styles_layout.addWidget(QLabel("Стили:"))
        styles_layout.addWidget(self.bold_checkbox)
        styles_layout.addWidget(self.italic_checkbox)
        styles_layout.addWidget(QLabel("Размер шрифта:"))
        styles_layout.addWidget(self.font_size_spinbox)
        styles_layout.addWidget(QLabel("Шрифт:"))
        styles_layout.addWidget(self.font_combo)
        styles_layout.addWidget(self.border_checkbox)
        styles_layout.addWidget(self.header_color_button)
        top_layout.addLayout(styles_layout)

        self.start_button = QPushButton("Начать", self)
        self.start_button.clicked.connect(self.start_conversion)
        top_layout.addWidget(self.start_button)

        self.stop_button = QPushButton("Остановить процесс", self)
        self.stop_button.clicked.connect(self.stop_conversion)
        self.stop_button.setEnabled(False)

        stop_pivot_layout = QHBoxLayout()
        stop_pivot_layout.addWidget(self.stop_button)

        self.pivot_button = QPushButton("Создать сводную таблицу", self)
        self.pivot_button.clicked.connect(self.show_pivot_settings)
        stop_pivot_layout.addWidget(self.pivot_button)

        top_layout.addLayout(stop_pivot_layout)

        self.total_rows_label = QLabel("Общее количество строк: 0", self)
        top_layout.addWidget(self.total_rows_label)

        self.timer_label = QLabel("Время с момента запуска: 00:00", self)
        top_layout.addWidget(self.timer_label)

        splitter.addWidget(top_widget)

        log_widget = QWidget()
        log_layout = QVBoxLayout(log_widget)

        self.log_text = QTextEdit(self)
        self.log_text.setReadOnly(True)
        self.log_text.setSizePolicy(QSizePolicy.Policy.Expanding, QSizePolicy.Policy.Expanding)
        log_font = QFont()
        log_font.setPointSize(10)
        self.log_text.setFont(log_font)
        self.log_text.document().setDefaultStyleSheet("""
            * {
                font-size: 10pt;
            }
        """)
        log_layout.addWidget(self.log_text)
        splitter.addWidget(log_widget)

        splitter.setSizes([300, 700])
        layout.addWidget(splitter)

        self.progress_bar = QProgressBar(self)
        layout.addWidget(self.progress_bar)

        file_buttons_layout = QHBoxLayout()
        self.open_file_button = QPushButton("Открыть конвертированный файл", self)
        self.open_file_button.clicked.connect(self.open_converted_file)
        self.open_file_button.setEnabled(False)
        file_buttons_layout.addWidget(self.open_file_button)

        self.delete_file_button = QPushButton("Удалить конвертированный файл", self)
        self.delete_file_button.clicked.connect(self.delete_converted_file)
        self.delete_file_button.setEnabled(False)
        file_buttons_layout.addWidget(self.delete_file_button)

        layout.addLayout(file_buttons_layout)

        bottom_buttons_layout = QHBoxLayout()
        self.close_button = QPushButton("Закрыть", self)
        self.close_button.clicked.connect(self.close)
        self.close_button.setStyleSheet("""
            QPushButton {
                background-color: #d9534f;
                color: white;
                border: none;
                padding: 10px 20px;
                border-radius: 5px;
                font-size: 14px;
            }
            QPushButton:hover {
                background-color: #c9302c;
            }
            QPushButton:pressed {
                background-color: #ac2925;
            }
        """)
        bottom_buttons_layout.addWidget(self.close_button)

        settings_button = QPushButton("Настройки", self)
        settings_button.clicked.connect(self.showSettings)
        bottom_buttons_layout.addWidget(settings_button)

        layout.addLayout(bottom_buttons_layout)

        self.pivot_settings = None

        self.apply_styles()
        self.set_button_sizes()

        # Добавляем подсказки для элементов управления
        self.tsv_file_list.setToolTip(
            "Список выбранных TSV файлов для конвертации.\n"
            "Вы можете добавить файлы кнопкой 'Добавить файлы' или перетащить их сюда мышью."
        )
        
        self.select_tsv_button.setToolTip(
            "Нажмите для выбора одного или нескольких TSV файлов для конвертации.\n"
            "Поддерживается множественный выбор файлов."
        )
        
        self.preview_button.setToolTip(
            "Открывает окно предварительного просмотра выбранного TSV файла.\n"
            "Позволяет просмотреть содержимое файла до конвертации."
        )
        
        self.remove_file_button.setToolTip(
            "Удаляет выбранный файл из списка.\n"
            "Выберите файл в списке и нажмите эту кнопку для удаления."
        )
        
        self.output_directory_path.setToolTip(
            "Путь к папке, куда будут сохранены сконвертированные файлы.\n"
            "Введите путь вручную или нажмите 'Обзор' для выбора папки."
        )
        
        self.select_dir_button.setToolTip(
            "Открывает диалог выбора папки для сохранения сконвертированных файлов."
        )
        
        self.output_format_combo.setToolTip(
            "Выберите формат выходного файла:\n"
            "- xlsx: Файл Excel (рекомендуется)\n"
            "- csv: Текстовый файл с разделителями\n"
            "- ods: Документ OpenDocument"
        )
        
        self.column_combo.setToolTip(
            "Выберите столбец для разделения данных на отдельные листы.\n"
            "После выбора столбца появится диалог выбора значений для разделения."
        )
        
        self.filter_combo.setToolTip(
            "Выберите столбец для фильтрации данных.\n"
            "После выбора столбца появится диалог выбора значений для фильтрации."
        )
        
        self.bold_checkbox.setToolTip("Применить жирное начертание к шапке таблицы")
        self.italic_checkbox.setToolTip("Применить курсивное начертание к шапке таблицы")
        self.font_size_spinbox.setToolTip("Установить размер шрифта для всей таблицы")
        self.font_combo.setToolTip("Выбрать шрифт для всей таблицы")
        self.border_checkbox.setToolTip("Добавить границы ко всем ячейкам таблицы")
        
        self.header_color_button.setToolTip(
            "Нажмите для выбора цвета фона шапки таблицы.\n"
            "Текущий цвет отображается на кнопке."
        )
        
        self.start_button.setToolTip(
            "Начать процесс конвертации файлов.\n"
            "Убедитесь, что выбраны файлы и указана папка для сохранения."
        )
        
        self.stop_button.setToolTip(
            "Остановить текущий процесс конвертации.\n"
            "Частично сконвертированные файлы могут быть сохранены."
        )
        
        self.pivot_button.setToolTip(
            "Открыть настройки создания сводной таблицы.\n"
            "Позволяет создать сводную таблицу на основе выбранных данных."
        )
        
        self.open_file_button.setToolTip(
            "Открыть сконвертированный файл в программе по умолчанию.\n"
            "Доступно после успешной конвертации."
        )
        
        self.delete_file_button.setToolTip(
            "Удалить сконвертированный файл.\n"
            "Доступно после успешной конвертации."
        )
        
        self.close_button.setToolTip("Закрыть программу")
        
        self.log_text.setToolTip(
            "Журнал событий программы.\n"
            "Здесь отображаются все сообщения о ходе конвертации и возможных ошибках."
        )
        
        self.progress_bar.setToolTip(
            "Индикатор прогресса конвертации.\n"
            "Показывает процент выполнения текущей операции."
        )

    def apply_styles(self):
        style = """
        QMainWindow {
            background-color: #f0f0f0;
        }
        QPushButton {
            background-color: #4CAF50;
            color: white;
            border: none;
            padding: 10px 20px;
            border-radius: 5px;
            font-size: 14px;
        }
        QPushButton:hover {
            background-color: #45a049;
        }
        QPushButton:disabled {
            background-color: #a0a0a0;
        }
        QLineEdit {
            padding: 8px;
            border: 1px solid #ccc;
            border-radius: 5px;
            font-size: 14px;
        }
        QTextEdit {
            background-color: #ffffff;
            border: 1px solid #ccc;
            border-radius: 5px;
            padding: 10px;
            font-size: 14px;
        }
        QProgressBar {
            border: 1px solid #ccc;
            border-radius: 5px;
            text-align: center;
            font-size: 14px;
        }
        QProgressBar::chunk {
            background-color: #4CAF50;
            width: 10px;
            margin: 0.5px;
        }
        QListWidget {
            background-color: #ffffff;
            border: 1px solid #ccc;
            border-radius: 5px;
            padding: 10px;
            font-size: 14px;
        }
        QComboBox {
            padding: 8px;
            border: 1px solid #ccc;
            border-radius: 5px;
            font-size: 14px;
        }
        QCheckBox {
            font-size: 14px;
        }
        QSpinBox {
            padding: 8px;
            border: 1px solid #ccc;
            border-radius: 5px;
            font-size: 14px;
        }
        QFontComboBox {
            padding: 8px;
            border: 1px solid #ccc;
            border-radius: 5px;
            font-size: 14px;
        }
        QLabel {
            font-size: 14px;
        }
        """
        self.setStyleSheet(style)

    def set_button_sizes(self):
        max_width = max(self.select_tsv_button.sizeHint().width(),
                        self.preview_button.sizeHint().width(),
                        self.remove_file_button.sizeHint().width(),
                        self.stop_button.sizeHint().width())
        max_height = max(self.select_tsv_button.sizeHint().height(),
                         self.preview_button.sizeHint().height(),
                         self.remove_file_button.sizeHint().height(),
                         self.stop_button.sizeHint().height())

        self.select_tsv_button.setFixedSize(max_width, max_height)
        self.preview_button.setFixedSize(max_width, max_height)
        self.remove_file_button.setFixedSize(max_width, max_height)
        self.stop_button.setFixedSize(max_width, max_height)

    def update_total_rows(self):
        try:
            total_rows = 0
            for i in range(self.tsv_file_list.count()):
                tsv_file = self.tsv_file_list.item(i).text()
                with open(tsv_file, 'r', encoding='utf-8') as file:
                    reader = csv.reader(file, delimiter='\t')
                    headers = next(reader)
                    filter_indices = {}
                    for column, values in self.filter_values.items():
                        if column in headers:
                            filter_indices[headers.index(column)] = values

                    for row in reader:
                        passes_filters = True
                        for col_index, allowed_values in filter_indices.items():
                            if col_index < len(row) and row[col_index] not in allowed_values:
                                passes_filters = False
                                break
                        if passes_filters:
                            total_rows += 1

            self.current_total_rows = total_rows
            self.total_rows_label.setText(f"Общее количество строк: {total_rows}")

            if self.filter_values:
                filtered_info = []
                for column, values in self.filter_values.items():
                    filtered_info.append(f"{column}: {', '.join(values)}")
                self.log_message(
                    f"Применены фильтры:\n" +
                    "\n".join(filtered_info) +
                    f"\nКоличество строк после фильтрации: {total_rows}",
                    QColor("blue")
                )

        except Exception as e:
            self.log_message(f"Ошибка при пересчете строк: {str(e)}", QColor("red"))

    def select_tsv_files(self):
        file_paths, _ = QFileDialog.getOpenFileNames(self, "Выберите файлы TSV", "", "TSV files (*.tsv)")
        if file_paths:
            self.tsv_file_list.clear()
            self.tsv_file_list.addItems(file_paths)
            self.filter_values.clear()
            self.selected_column_values.clear()
            self.update_total_rows()
            if file_paths:
                self.update_column_list(file_paths[0])

    def update_column_list(self, tsv_file):
        try:
            with open(tsv_file, 'r', encoding='utf-8') as file:
                reader = csv.reader(file, delimiter='\t')
                headers = next(reader)
                for combo in [self.column_combo, self.filter_combo]:
                    combo.clear()
                    combo.addItem("Не разделять" if combo == self.column_combo else "Не фильтровать")
                    combo.addItems(headers)

                self.column_combo.currentIndexChanged.connect(self.on_column_selected)
                self.filter_combo.currentIndexChanged.connect(self.on_filter_selected)
        except Exception as e:
            self.log_message(f"Ошибка при чтении заголовков: {str(e)}", QColor("red"))

    def on_column_selected(self, index):
        if index <= 0:
            selected_column = self.column_combo.currentText()
            if selected_column in self.selected_column_values:
                del self.selected_column_values[selected_column]
            return

        selected_column = self.column_combo.currentText()
        if not self.tsv_file_list.count():
            return

        try:
            unique_values = set()
            with open(self.tsv_file_list.item(0).text(), 'r', encoding='utf-8') as file:
                reader = csv.reader(file, delimiter='\t')
                headers = next(reader)
                col_index = headers.index(selected_column)
                for row in reader:
                    if len(row) > col_index:
                        if selected_column in self.filter_values:
                            if row[col_index] in self.filter_values[selected_column]:
                                unique_values.add(row[col_index])
                        else:
                            unique_values.add(row[col_index])

            if not unique_values:
                QMessageBox.warning(self, "Предупреждение",
                    "Нет доступных значений для разделения. Возможно, все значения исключены фильтром.")
                self.column_combo.setCurrentIndex(0)
                return

            dialog = ColumnValuesDialog(unique_values)
            if dialog.exec():
                self.selected_column_values[selected_column] = dialog.get_selected_values()
                self.log_text.setFontPointSize(14)
                self.log_message(f"Выбраны значения для разделения в столбце {selected_column}: {', '.join(self.selected_column_values[selected_column])}", QColor("green"))
                self.log_text.setFontPointSize(9)
            else:
                self.column_combo.setCurrentIndex(0)
                if selected_column in self.selected_column_values:
                    del self.selected_column_values[selected_column]
        except Exception as e:
            self.log_message(f"Ошибка при получении значений столбца: {str(e)}", QColor("red"))

    def on_filter_selected(self, index):
        if index <= 0:
            selected_column = self.filter_combo.currentText()
            if selected_column in self.filter_values:
                del self.filter_values[selected_column]
                self.update_total_rows()
            return

        selected_column = self.filter_combo.currentText()
        if not self.tsv_file_list.count():
            return

        try:
            unique_values = set()
            with open(self.tsv_file_list.item(0).text(), 'r', encoding='utf-8') as file:
                reader = csv.reader(file, delimiter='\t')
                headers = next(reader)
                col_index = headers.index(selected_column)
                for row in reader:
                    if len(row) > col_index:
                        unique_values.add(row[col_index])

            dialog = ColumnValuesDialog(unique_values)
            if dialog.exec():
                self.filter_values[selected_column] = dialog.get_selected_values()
                self.update_total_rows()
                self.log_text.setFontPointSize(14)
                self.log_message(f"Выбраны значения для фильтрации в столбце {selected_column}: {', '.join(self.filter_values[selected_column])}", QColor("green"))
                self.log_text.setFontPointSize(9)
            else:
                self.filter_combo.setCurrentIndex(0)
                if selected_column in self.filter_values:
                    del self.filter_values[selected_column]
                    self.update_total_rows()
        except Exception as e:
            self.log_message(f"Ошибка при получении значений столбца: {str(e)}", QColor("red"))

    def select_output_directory(self):
        directory_path = QFileDialog.getExistingDirectory(self, "Выберите директорию для сохранения")
        if directory_path:
            self.output_directory_path.setText(directory_path)

    def start_conversion(self):
        try:
            tsv_files = [self.tsv_file_list.item(i).text() for i in range(self.tsv_file_list.count())]
            output_directory = self.output_directory_path.text()
            output_format = self.output_format_combo.currentText()
            auto_open = self.open_file_button.isEnabled()
            auto_delete = self.delete_file_button.isEnabled()

            styles = {
                'bold': self.bold_checkbox.isChecked(),
                'italic': self.italic_checkbox.isChecked(),
                'font_size': self.font_size_spinbox.value(),
                'font_name': self.font_combo.currentFont().family(),
                'border': 1 if self.border_checkbox.isChecked() else 0
            }

            header_color = self.header_color_button.palette().color(self.header_color_button.backgroundRole()).name()

            split_column = self.column_combo.currentText()
            filter_column = self.filter_combo.currentText()

            selected_values = (self.selected_column_values.get(split_column, [])
                             if split_column != "Не разделять" else [])
            filter_values = (self.filter_values.get(filter_column, [])
                           if filter_column != "Не фильтровать" else [])

            if not tsv_files or not output_directory:
                QMessageBox.critical(self, "Ошибка", "Пожалуйста, выберите файлы TSV и директорию для сохранения.")
                return

            self.progress_bar.setValue(0)
            self.log_text.clear()

            filter_column = self.filter_combo.currentText()
            if filter_column and filter_column != "Не фильтровать":
                self.pivot_settings['filter_column'] = filter_column
                self.pivot_settings['filter_values'] = self.filter_values.get(filter_column, set())

            self.converter = TSVToExcelConverter(
                tsv_files, output_directory, output_format, auto_open, auto_delete,
                styles, header_color, split_column, selected_values,
                filter_column=filter_column, filter_values=filter_values,
                pivot_settings=self.pivot_settings
            )
            self.converter_ref = weakref.ref(self.converter)
            self.converter.update_progress.connect(self.update_progress)
            self.converter.log_message.connect(self.log_message)
            self.converter.finished.connect(self.conversion_finished)
            self.converter.start()

            self.start_button.setEnabled(False)
            self.stop_button.setEnabled(True)
            self.start_time = time.time()
            self.timer.start(1000)
        except Exception as e:
            self.log_message.emit(f"{datetime.now().strftime('%H:%M:%S')} Ошибка при запуске конвертации: {str(e)}", QColor("red"))

    def stop_conversion(self):
        try:
            if self.converter_ref and self.converter_ref():
                converter = self.converter_ref()
                if converter.isRunning():
                    self.log_message("Останавливаем процесс конвертации...", QColor("orange"))
                    converter.stop()
                    converter.wait(2000)
                    self.log_message("Процесс конвертации остановлен.", QColor("red"))
                    self.start_button.setEnabled(True)
                    self.stop_button.setEnabled(False)
                    self.timer.stop()
                    self.progress_bar.setValue(0)
        except Exception as e:
            self.log_message(f"Ошибка при остановке конвертации: {str(e)}", QColor("red"))

    def update_progress(self, value):
        self.progress_bar.setValue(value)

    def log_message(self, message, color=None):
        cursor = self.log_text.textCursor()
        cursor.movePosition(QTextCursor.MoveOperation.End)
        self.log_text.setTextCursor(cursor)

        formatted_message = f'<span style="color: {color.name() if color else "black"};">{message}</span><br>'
        self.log_text.insertHtml(formatted_message)

        self.log_text.verticalScrollBar().setValue(
            self.log_text.verticalScrollBar().maximum()
        )

    def conversion_finished(self):
        self.progress_bar.setValue(100)
        self.open_file_button.setEnabled(True)
        self.delete_file_button.setEnabled(True)
        self.start_button.setEnabled(True)
        self.stop_button.setEnabled(False)
        self.timer.stop()

    def open_converted_file(self):
        if self.converter_ref and hasattr(self.converter_ref(), 'output_file_path'):
            os.startfile(self.converter_ref().output_file_path)

    def delete_converted_file(self):
        if self.converter_ref and hasattr(self.converter_ref(), 'output_file_path'):
            reply = QMessageBox.question(self, 'Подтверждение удаления', 'Вы действительно хотите удалить файл?',
                                         QMessageBox.StandardButton.Yes | QMessageBox.StandardButton.No)
            if reply == QMessageBox.StandardButton.Yes:
                os.remove(self.converter_ref().output_file_path)
                self.log_message(f"{datetime.now().strftime('%H:%M:%S')} Файл успешно удален.", QColor("red"))
                self.open_file_button.setEnabled(False)
                self.delete_file_button.setEnabled(False)

    def dragEnterEvent(self, event: QDragEnterEvent):
        if event.mimeData().hasUrls():
            event.acceptProposedAction()

    def dropEvent(self, event: QDropEvent):
        for url in event.mimeData().urls():
            file_path = url.toLocalFile()
            if file_path.endswith(".tsv"):
                self.tsv_file_list.addItem(file_path)
                if self.tsv_file_list.count() == 1:
                    self.update_column_list(file_path)

        self.update_total_rows()

    def preview_tsv_file(self):
        selected_file = self.tsv_file_list.currentItem()
        if selected_file:
            tsv_file = selected_file.text()
            preview_dialog = TSVPreviewDialog(tsv_file)
            preview_dialog.exec()

    def closeEvent(self, event):
        reply = QMessageBox.question(
            self,
            'Подтверждение закрытия',
            'Вы действительно хотите закрыть программу?',
            QMessageBox.StandardButton.Yes | QMessageBox.StandardButton.No,
            QMessageBox.StandardButton.No
        )

        if reply == QMessageBox.StandardButton.Yes:
            event.accept()
        else:
            event.ignore()

    def remove_selected_file(self):
        selected_items = self.tsv_file_list.selectedItems()
        if selected_items:
            for item in selected_items:
                self.tsv_file_list.takeItem(self.tsv_file_list.row(item))

    def select_header_color(self):
        color = QColorDialog.getColor()
        if color.isValid():
            self.header_color_button.setStyleSheet(f"background-color: {color.name()};")

    def update_timer(self):
        if self.start_time:
            elapsed_time = int(time.time() - self.start_time)
            minutes = elapsed_time // 60
            seconds = elapsed_time % 60
            self.timer_label.setText(f"Время: {minutes:02d}:{seconds:02d}")

    def show_pivot_settings(self):
        if not self.tsv_file_list.count():
            QMessageBox.warning(self, "Предупреждение", "Сначала добавьте файл TSV")
            return

        try:
            with open(self.tsv_file_list.item(0).text(), 'r', encoding='utf-8') as file:
                headers = next(csv.reader(file, delimiter='\t'))

            dialog = PivotSettings(headers)
            if dialog.exec():
                self.pivot_settings = dialog.get_settings()
                self.log_message("Настройки сводной таблицы сохранены", QColor("green"))
        except Exception as e:
            self.log_message(f"Ошибка при настройке сводной таблицы: {str(e)}", QColor("red"))

    def showSettings(self):
        try:
            dialog = SettingsDialog(self)
            dialog.setModal(True)
            result = dialog.exec()

            if result == QDialog.DialogCode.Accepted:
                self.log_message("Настройки успешно сохранены", QColor("green"))

        except Exception as e:
            self.log_message(f"Ошибка при открытии настроек: {str(e)}", QColor("red"))

    def getDarkTheme(self):
        return """
            QMainWindow, QDialog {
                background-color: #2b2b2b;
                color: #ffffff;
            }
            QPushButton {
                background-color: #3b3b3b;
                color: #ffffff;
                border: 1px solid #555555;
                padding: 5px;
                border-radius: 3px;
            }
            QPushButton:hover {
                background-color: #4b4b4b;
            }
            QPushButton:pressed {
                background-color: #555555;
            }
            QLineEdit, QTextEdit, QListWidget, QComboBox {
                background-color: #3b3b3b;
                color: #ffffff;
                border: 1px solid #555555;
                border-radius: 3px;
            }
            QLabel {
                color: #ffffff;
            }
            QProgressBar {
                border: 1px solid #555555;
                background-color: #3b3b3b;
                color: #ffffff;
                border-radius: 3px;
            }
            QProgressBar::chunk {
                background-color: #4CAF50;
            }
            QCheckBox {
                color: #ffffff;
            }
            QSpinBox {
                background-color: #3b3b3b;
                color: #ffffff;
                border: 1px solid #555555;
            }
        """

    def getLightTheme(self):
        return """
            QMainWindow, QDialog {
                background-color: #f0f0f0;
                color: #000000;
            }
            QPushButton {
                background-color: #4CAF50;
                color: white;
                border: none;
                padding: 5px;
                border-radius: 3px;
            }
            QPushButton:hover {
                background-color: #45a049;
            }
            QPushButton:pressed {
                background-color: #3d8b40;
            }
            QLineEdit, QTextEdit, QListWidget, QComboBox {
                background-color: white;
                border: 1px solid #cccccc;
                border-radius: 5px;
            }
            QProgressBar {
                border: 1px solid #cccccc;
                background-color: white;
                border-radius: 5px;
            }
            QProgressBar::chunk {
                background-color: #4CAF50;
            }
        """

    def applyScale(self, scale_text):
        try:
            scale = int(scale_text.rstrip('%'))
            widgets = self.findChildren(QWidget)
            base_sizes = {
                QLabel: 9,
                QPushButton: 9,
                QLineEdit: 9,
                QTextEdit: 9,
                QComboBox: 9,
                QCheckBox: 9,
                QSpinBox: 9,
                QListWidget: 9
            }

            for widget in widgets:
                for widget_type, base_size in base_sizes.items():
                    if isinstance(widget, widget_type):
                        font = widget.font()
                        font.setPointSize(int(base_size * scale / 100))
                        widget.setFont(font)
                        break

            self.set_button_sizes()
        except Exception as e:
            self.log_message(f"Ошибка при применении масштаба: {str(e)}", QColor("red"))

    def apply_light_theme(self):
        self.setStyleSheet("""
            QMainWindow {
                background-color: #f0f0f0;
            }
            QPushButton {
                background-color: #4CAF50;
                color: white;
                border: none;
                padding: 10px 20px;
                border-radius: 5px;
                font-size: 14px;
            }
            QPushButton:hover {
                background-color: #45a049;
            }
            QPushButton:disabled {
                background-color: #a0a0a0;
            }
            QLineEdit {
                padding: 8px;
                border: 1px solid #ccc;
                border-radius: 5px;
                font-size: 14px;
                background-color: white;
            }
            QTextEdit {
                background-color: white;
                border: 1px solid #ccc;
                border-radius: 5px;
                padding: 10px;
                font-size: 14px;
            }
            QListWidget {
                background-color: white;
                border: 1px solid #ccc;
                border-radius: 5px;
                padding: 10px;
                font-size: 14px;
            }
            QComboBox {
                padding: 8px;
                border: 1px solid #ccc;
                border-radius: 5px;
                font-size: 14px;
                background-color: white;
            }
        """)
        if hasattr(self, 'close_button'):
            self.close_button.setStyleSheet("""
                QPushButton {
                    background-color: #d9534f;
                    color: white;
                    border: none;
                    padding: 10px 20px;
                    border-radius: 5px;
                    font-size: 14px;
                }
                QPushButton:hover {
                    background-color: #c9302c;
                }
                QPushButton:pressed {
                    background-color: #ac2925;
                }
            """)

    def apply_dark_theme(self):
        self.setStyleSheet("""
            QMainWindow {
                background-color: #2b2b2b;
            }
            QPushButton {
                background-color: #45a049;
                color: white;
                border: none;
                padding: 10px 20px;
                border-radius: 5px;
                font-size: 14px;
            }
            QPushButton:hover {
                background-color: #4CAF50;
            }
            QPushButton:disabled {
                background-color: #555555;
            }
            QLineEdit {
                padding: 8px;
                border: 1px solid #555555;
                border-radius: 5px;
                font-size: 14px;
                background-color: #3b3b3b;
                color: white;
            }
            QTextEdit {
                background-color: #3b3b3b;
                border: 1px solid #555555;
                border-radius: 5px;
                padding: 10px;
                font-size: 14px;
                color: white;
            }
            QListWidget {
                background-color: #3b3b3b;
                border: 1px solid #555555;
                border-radius: 5px;
                padding: 10px;
                font-size: 14px;
                color: white;
            }
            QComboBox {
                padding: 8px;
                border: 1px solid #555555;
                border-radius: 5px;
                font-size: 14px;
                background-color: #3b3b3b;
                color: white;
            }
            QLabel {
                color: white;
            }
            QCheckBox {
                color: white;
            }
        """)
        if hasattr(self, 'close_button'):
            self.close_button.setStyleSheet("""
                QPushButton {
                    background-color: #d9534f;
                    color: white;
                    border: none;
                    padding: 10px 20px;
                    border-radius: 5px;
                    font-size: 14px;
                }
                QPushButton:hover {
                    background-color: #c9302c;
                }
                QPushButton:pressed {
                    background-color: #ac2925;
                }
            """)


if __name__ == "__main__":
    app = QApplication([])
    window = TSVToExcelApp()
    window.show()
    app.exec()
Необходимо сделать доработку, добавь возможность в сводной в параметрах выбирать одно и тоже значение, строку или столбец несколько раз, а так же для каждого выбранного значения, столбца или строки выбирать свою агрегацию