---

from pathlib import Path
from openpyxl import load_workbook

# Файлы лежат рядом с notebook.ipynb
SOURCE_FILE = Path("Потребность в диагностике (1).xlsx")
TARGET_FILE = Path("База 2026 корректировка 3.xlsx")
OUTPUT_FILE = Path("result_base.xlsx")

SOURCE_SHEET = "Лист1"
TARGET_SHEET = "Вся База"


def normalize_identifier_from_need(col_m_value):
    """Преобразует идентификатор к виду short_id."""
    if col_m_value is None:
        return None

    m_str = str(col_m_value).strip()

    if not m_str:
        return None

    dr_pos = m_str.lower().find("dr")

    if dr_pos != -1:
        short_id = m_str[:dr_pos].strip()
    else:
        short_id = m_str

    if short_id.endswith("_s"):
        return short_id

    if short_id.endswith("_"):
        return short_id.rstrip("_")

    return short_id


def as_text(value):
    """Преобразует значение в текст, не меняя номера заявок и заданий."""
    if value is None:
        return ""
    return str(value).strip()


def normalize_for_match(value):
    """
    Приводит ID к единому виду для поиска:
    убирает внешние пробелы и приводит к нижнему регистру.
    """
    if value is None:
        return None

    value = str(value).strip()

    if not value:
        return None

    return value.lower()


# Проверка наличия файлов
if not SOURCE_FILE.exists():
    raise FileNotFoundError(f"Не найден первый файл: {SOURCE_FILE.resolve()}")

if not TARGET_FILE.exists():
    raise FileNotFoundError(f"Не найден файл базы: {TARGET_FILE.resolve()}")


# Первая книга: потоковое чтение, быстро и с меньшим использованием памяти.
wb_source = load_workbook(
    SOURCE_FILE,
    read_only=True,
    data_only=False
)

# Вторая книга: обычный режим, потому что в неё нужно записывать данные.
wb_target = load_workbook(
    TARGET_FILE,
    read_only=False,
    data_only=False
)

ws_source = wb_source[SOURCE_SHEET]
ws_target = wb_target[TARGET_SHEET]

print(f"Первая таблица: {ws_source.max_row} строк")
print(f"Вторая таблица: {ws_target.max_row} строк")


# ---------------------------------------------------------
# 1. Индексируем AE второй таблицы.
# AE = 31. Будем читать только этот один столбец.
# ---------------------------------------------------------
index_ae = {}

for row_number, row_values in enumerate(
    ws_target.iter_rows(
        min_row=2,
        max_row=ws_target.max_row,
        min_col=31,
        max_col=31,
        values_only=True
    ),
    start=2
):
    value_ae = row_values[0]

    # Если AE уже содержит короткий ID, не обрезаем его по dr.
    # Только приводим его к единому виду для сравнения.
    id_ae = normalize_for_match(value_ae)

    if id_ae:
        index_ae.setdefault(id_ae, []).append(row_number)

print(f"В индекс добавлено уникальных ID из AE: {len(index_ae)}")


# ---------------------------------------------------------
# 2. Читаем из первой таблицы только A, B и M.
# Диапазон A:M нужен, потому что M — 13-й столбец.
# iter_rows идёт последовательно и работает значительно быстрее.
# ---------------------------------------------------------
not_found = []
duplicate_ids = []
updated_rows = 0
processed_rows = 0
empty_m_rows = 0

for source_row, values in enumerate(
    ws_source.iter_rows(
        min_row=2,
        max_row=ws_source.max_row,
        min_col=1,
        max_col=13,
        values_only=True
    ),
    start=2
):
    # values — кортеж из A:M:
    # values[0] = A, values[1] = B, values[12] = M
    request_number = as_text(values[0])
    task_number = as_text(values[1])
    value_m = values[12]

    short_id = normalize_identifier_from_need(value_m)

    if not short_id:
        empty_m_rows += 1
        continue

    # Сравниваем с ключами из AE в одинаковом виде.
    search_id = normalize_for_match(short_id)

    found_rows = index_ae.get(search_id)

    if not found_rows:
        not_found.append({
            "строка_Лист1": source_row,
            "идентификатор_M": short_id
        })
        continue

    if len(found_rows) > 1:
        duplicate_ids.append({
            "строка_Лист1": source_row,
            "идентификатор": short_id,
            "строки_Вся_База": found_rows
        })

    for target_row in found_rows:
        # AF = 32, AG = 33
        cell_af = ws_target.cell(row=target_row, column=32)
        cell_ag = ws_target.cell(row=target_row, column=33)

        # Сохраняем как текст, в том числе для номеров с ведущими нулями.
        cell_af.number_format = "@"
        cell_ag.number_format = "@"

        cell_af.value = request_number
        cell_ag.value = task_number

        updated_rows += 1

    processed_rows += 1

    # Индикатор прогресса: появится внизу ячейки Jupyter.
    if processed_rows % 1000 == 0:
        print(
            f"Обработано совпавших строк: {processed_rows}; "
            f"заполнено строк базы: {updated_rows}"
        )


# ---------------------------------------------------------
# 3. Сохраняем новую копию базы.
# ---------------------------------------------------------
wb_target.save(OUTPUT_FILE)

wb_source.close()
wb_target.close()

print("\nГОТОВО")
print(f"Файл результата: {OUTPUT_FILE.resolve()}")
print(f"Заполнено строк в базе: {updated_rows}")
print(f"Строк с пустым M: {empty_m_rows}")
print(f"ID из M, не найдено в AE: {len(not_found)}")
print(f"ID с дублями в AE: {len(duplicate_ids)}")

if not_found:
    print("\nПервые 10 ID, которые не нашлись в AE:")
    for item in not_found[:10]:
        print(item)

if duplicate_ids:
    print("\nПервые 10 повторяющихся ID в AE:")
    for item in duplicate_ids[:10]:
        print(item)