---

import os
import re
import glob
import pandas as pd

# ================= НАСТРОЙКИ ПУТЕЙ =================
MAIN_FILE = r"result_updated.xlsx"  # Ваша готовая таблица (куда больше не надо добавлять строки)
REF_FILE = r"справочник_групп.xlsx" # НОВАЯ таблица (где группа, уровень, форма, факультет)
DUMP_FOLDER = r"C:\Users\n.o.ishanhodzhaeva\Desktop\Выгрузка по кафедрам\zv" # Папка с 40+ файлами
OUTPUT_FILE = r"result_perfect.xlsx" # Имя итогового файла
# ===================================================

def find_col(columns, keywords):
    """Находит столбец по ключевым словам"""
    for col in columns:
        col_low = str(col).lower().strip()
        if any(k in col_low for k in keywords):
            return col
    return None

def clean_val(val):
    """Очищает значение от лишних пробелов и пустых 'nan'"""
    if pd.isna(val):
        return ""
    s = re.sub(r'\s+', ' ', str(val)).strip()
    return "" if s.lower() in ['nan', 'none', 'null'] else s

def clean_teacher_name(name):
    """Убирает старые скобки с кафедрой из конца ФИО, чтобы не задвоить: 'Иванов И.И. (ИМП)' -> 'Иванов И.И.'"""
    name = clean_val(name)
    return re.sub(r'\s*\([^)]+\)\s*$', '', name).strip()

def make_abbreviation(full_dept_name, known_abbrevs):
    """Превращает полное название кафедры в аббревиатуру или находит её в базе ваших 4000 строк"""
    dept = clean_val(full_dept_name)
    if not dept:
        return ""
    
    # Убираем слово "кафедра" в начале, если оно есть
    dept_clean = re.sub(r'^(кафедра|каф\.?)\s+', '', dept, flags=re.IGNORECASE).strip()
    
    # 1. Если это уже готовая аббревиатура из ваших 4к строк (без учета регистра)
    for k_low, k_orig in known_abbrevs.items():
        if dept_clean.lower() == k_low:
            return k_orig

    # 2. Если название короткое (до 15 символов, 1-2 слова) — возвращаем как есть
    if len(dept_clean) <= 15 and len(dept_clean.split()) <= 2:
        return dept_clean

    # 3. Если это длинное название из нескольких слов — собираем аббревиатуру по первым буквам
    stop_words = {'и', 'в', 'на', 'с', 'по', 'для', 'от', 'при', 'о', 'у', 'за', 'над', 'под'}
    words = re.findall(r'[А-Яа-яЁёA-Za-z0-9]+', dept_clean)
    abbr_letters = []
    for w in words:
        if w.lower() in stop_words:
            abbr_letters.append(w.lower())
        else:
            abbr_letters.append(w[0].upper())
    
    generated = "".join(abbr_letters)
    # Проверяем, есть ли похожая аббревиатура в ваших 4000 строках
    if generated.lower() in known_abbrevs:
        return known_abbrevs[generated.lower()]
    return generated


# --- ШАГ 1. Загружаем вашу готовую таблицу ---
print("1. Загрузка вашей основной таблицы...")
all_sheets = pd.read_excel(MAIN_FILE, sheet_name=None)
target_sheet = list(all_sheets.keys())[0]
df_main = all_sheets[target_sheet].copy()
df_main.columns = df_main.columns.astype(str).str.strip()

c_inst = find_col(df_main.columns, ['факультет', 'институт'])
c_grp = find_col(df_main.columns, ['групп'])
c_form = find_col(df_main.columns, ['форма'])
c_lvl = find_col(df_main.columns, ['уровень', 'подготовк'])
c_subj = find_col(df_main.columns, ['наименование', 'дисциплин', 'предмет'])
c_teach = find_col(df_main.columns, ['преподавател', 'фио', 'ппс'])
c_dept = find_col(df_main.columns, ['кафедр'])

# Собираем эталонные аббревиатуры кафедр и связку (Преподаватель -> Кафедра) из ваших 4000 строк
known_abbrevs = {}      # {нижний_регистр: ПравильноеНаписание}
teacher_to_dept = {}    # {чистое_фио_нижний_регистр: Кафедра}

for _, row in df_main.iterrows():
    dept_val = clean_val(row[c_dept]) if c_dept else ""
    teach_val = clean_val(row[c_teach]) if c_teach else ""
    
    # Если в ФИО уже есть (Кафедра), вытаскиваем её тоже как эталон
    match = re.search(r'\(([^)]+)\)\s*$', teach_val)
    dept_in_brackets = match.group(1).strip() if match else ""
    
    actual_dept = dept_val or dept_in_brackets
    if actual_dept:
        known_abbrevs[actual_dept.lower()] = actual_dept
        if teach_val:
            clean_t = clean_teacher_name(teach_val).lower()
            if clean_t:
                teacher_to_dept[clean_t] = actual_dept


# --- ШАГ 2. Читаем НОВУЮ таблицу для сверки групп (Факультет, Форма, Уровень) ---
print("2. Чтение новой таблицы-справочника групп...")
ref_dict = {} # {номер_группы: {'inst': ..., 'form': ..., 'lvl': ...}}

if os.path.exists(REF_FILE):
    ref_sheets = pd.read_excel(REF_FILE, sheet_name=None)
    for _, r_df in ref_sheets.items():
        if r_df.empty:
            continue
        r_df.columns = r_df.columns.astype(str).str.strip()
        r_grp = find_col(r_df.columns, ['групп'])
        r_inst = find_col(r_df.columns, ['факультет', 'институт'])
        r_form = find_col(r_df.columns, ['форма'])
        r_lvl = find_col(r_df.columns, ['уровень', 'подготовк'])
        
        if not r_grp:
            continue
            
        for _, r_row in r_df.iterrows():
            # На случай если в справочнике группы тоже через "|"
            raw_g = clean_val(r_row[r_grp])
            for g in raw_g.split('|'):
                g = g.strip()
                if not g:
                    continue
                ref_dict[g] = {
                    'inst': clean_val(r_row[r_inst]) if r_inst else "",
                    'form': clean_val(r_row[r_form]) if r_form else "",
                    'lvl': clean_val(r_row[r_lvl]) if r_lvl else ""
                }
    print(f"   Загружено уникальных групп для сверки: {len(ref_dict)}")
else:
    print(f"   [ВНИМАНИЕ] Файл справочника '{REF_FILE}' не найден! Проверьте имя файла.")


# --- ШАГ 3. Сканируем 40+ файлов выгрузки для поиска кафедр ---
print("3. Поиск аббревиатур кафедр в 40+ файлах выгрузки...")
dump_files = glob.glob(os.path.join(DUMP_FOLDER, '*.xls*'))

# База из выгрузки: {(группа, предмет, фио): кафедра} и {(предмет, фио): кафедра}
dump_dept_map = {}

for fpath in dump_files:
    fname = os.path.basename(fpath)
    if '~$' in fname or os.path.abspath(fpath) in [os.path.abspath(MAIN_FILE), os.path.abspath(REF_FILE)]:
        continue
        
    # Часто название файла выгрузки само содержит аббревиатуру кафедры (например, "Нагрузка_ИМП.xlsx")
    file_stem = os.path.splitext(fname)[0]
    file_abbr_candidate = ""
    for k_low, k_orig in known_abbrevs.items():
        # Ищем точное совпадение аббревиатуры в имени файла
        if re.search(r'(^|[^а-яa-z0-9])' + re.escape(k_low) + r'($|[^а-яa-z0-9])', file_stem.lower()):
            file_abbr_candidate = k_orig
            break

    try:
        xls = pd.read_excel(fpath, sheet_name=None, header=None)
        for s_name, raw_df in xls.items():
            if raw_df.empty:
                continue
                
            # Проверяем первые строки над таблицей (там часто пишут "Кафедра: ...")
            sheet_dept = file_abbr_candidate
            header_idx = None
            
            for idx, row in raw_df.head(20).iterrows():
                row_text = ' '.join(row.dropna().astype(str)).strip()
                row_low = row_text.lower()
                
                # Если в шапке листа написана кафедра
                if 'кафедр' in row_low and 'групп' not in row_low:
                    m = re.search(r'кафедр[аы]?\s*[:\-]?\s*([А-Яа-яЁёA-Za-z0-9\s\-]+)', row_text, re.IGNORECASE)
                    if m and not sheet_dept:
                        sheet_dept = make_abbreviation(m.group(1), known_abbrevs)
                        
                if 'групп' in row_low and any(w in row_low for w in ['дисциплин', 'предмет', 'наименование']):
                    header_idx = idx
                    break
                    
            if header_idx is None:
                continue
                
            df_s = raw_df.iloc[header_idx + 1:].copy()
            df_s.columns = raw_df.iloc[header_idx].astype(str).str.strip()
            
            s_grp = find_col(df_s.columns, ['групп'])
            s_subj = find_col(df_s.columns, ['наименование', 'дисциплин', 'предмет'])
            s_teach = find_col(df_s.columns, ['преподавател', 'фио', 'ппс'])
            s_dept = find_col(df_s.columns, ['кафедр'])
            
            for _, d_row in df_s.iterrows():
                t_raw = clean_val(d_row[s_teach]) if s_teach else ""
                s_raw = clean_val(d_row[s_subj]).lower() if s_subj else ""
                g_raw = clean_val(d_row[s_grp]) if s_grp else ""
                
                # Ищем кафедру: 1) в столбце Кафедра, 2) в скобках у ФИО, 3) из шапки листа/имени файла
                d_val = clean_val(d_row[s_dept]) if s_dept else ""
                if not d_val and '(' in t_raw and ')' in t_raw:
                    m = re.search(r'\(([^)]+)\)\s*$', t_raw)
                    if m:
                        d_val = m.group(1).strip()
                if not d_val:
                    d_val = sheet_dept
                    
                abbr = make_abbreviation(d_val, known_abbrevs)
                if not abbr:
                    continue
                    
                t_clean = clean_teacher_name(t_raw).lower()
                if t_clean and t_clean not in teacher_to_dept:
                    teacher_to_dept[t_clean] = abbr
                if s_raw and t_clean:
                    dump_dept_map[(s_raw, t_clean)] = abbr
                for g_item in g_raw.split('|'):
                    g_clean = g_item.strip().lower()
                    if g_clean and s_raw:
                        dump_dept_map[(g_clean, s_raw, t_clean)] = abbr
    except Exception as e:
        pass


# --- ШАГ 4. Сверяем и обновляем вашу основную таблицу (БЕЗ добавления строк) ---
print("4. Сверка и исправление данных в вашей таблице...")

code_map = {
    '1': ('Очная', 'Бакалавриат'),
    '2': ('Очно-заочная', 'Бакалавриат'),
    '3': ('Заочная', 'Бакалавриат'),
    '4': ('Очная', 'Магистратура'),
    '5': ('Очно-заочная', 'Магистратура'),
    '6': ('Заочная', 'Магистратура')
}

updated_groups_count = 0
updated_depts_count = 0

def update_main_row(row):
    global updated_groups_count, updated_depts_count
    
    grp = clean_val(row[c_grp]) if c_grp else ""
    subj = clean_val(row[c_subj]).lower() if c_subj else ""
    teach_raw = clean_val(row[c_teach]) if c_teach else ""
    dept_raw = clean_val(row[c_dept]) if c_dept else ""
    
    # --- А. СВЕРКА ФАКУЛЬТЕТА, ФОРМЫ И УРОВНЯ ПО НОВОЙ ТАБЛИЦЕ ---
    if grp in ref_dict:
        ref_info = ref_dict[grp]
        if c_inst and ref_info['inst'] and clean_val(row[c_inst]) != ref_info['inst']:
            row[c_inst] = ref_info['inst']
            updated_groups_count += 1
        if c_form and ref_info['form'] and clean_val(row[c_form]) != ref_info['form']:
            row[c_form] = ref_info['form']
        if c_lvl and ref_info['lvl'] and clean_val(row[c_lvl]) != ref_info['lvl']:
            row[c_lvl] = ref_info['lvl']
    else:
        # Если группы вдруг нет в справочнике — страхуем проверкой по 3-й цифре
        prefix = grp.split('-')[0]
        if len(prefix) >= 3 and prefix[:3].isdigit():
            c_digit = prefix[2]
            if c_digit in code_map:
                if c_form: row[c_form] = code_map[c_digit][0]
                if c_lvl: row[c_lvl] = code_map[c_digit][1]

    # Жесткие правила для ИГРИК -> ФГРИК и ПШ ИД
    if c_inst:
        if clean_val(row[c_inst]) == 'ИГРИК':
            row[c_inst] = 'ФГРИК'
        if grp in ['261-1011', '264-1011']:
            row[c_inst] = 'ПШ ИД'
            
    # Особое правило для физкультуры: кафедра всегда ФВ (если на 1-м фото у вас "(ФВ)")
    # --- Б. ОПРЕДЕЛЕНИЕ АББРЕВИАТУРЫ КАФЕДРЫ И ЗАПИСЬ В СКОБКАХ У ПРЕПОДАВАТЕЛЯ ---
    t_base = clean_teacher_name(teach_raw)
    t_low = t_base.lower()
    
    # Проверяем, есть ли уже скобки в ФИО
    m_br = re.search(r'\(([^)]+)\)\s*$', teach_raw)
    dept_from_br = m_br.group(1).strip() if m_br else ""
    
    # Ищем правильную аббревиатуру по приоритету:
    # 1) Уже стоит в столбце Кафедра или в скобках у ФИО
    # 2) Найдена в файлах выгрузки по (Группа + Предмет + ФИО) или (Предмет + ФИО)
    # 3) Найдена в базе преподавателей из ваших 4000 строк
    final_dept = dept_raw or dept_from_br
    if not final_dept:
        final_dept = (
            dump_dept_map.get((grp.lower(), subj, t_low)) or
            dump_dept_map.get((subj, t_low)) or
            teacher_to_dept.get(t_low, "")
        )
    if not final_dept and 'физическая культура' in subj:
        final_dept = 'ФВ' # На вашем 1-м фото у преподавателей физкультуры стоит (ФВ)
        
    final_dept = make_abbreviation(final_dept, known_abbrevs)
    
    # Записываем кафедру в столбец "Кафедра"
    if c_dept and final_dept:
        if clean_val(row[c_dept]) != final_dept:
            updated_depts_count += 1
        row[c_dept] = final_dept
        
    # Записываем ФИО в формате: "Фамилия Имя Отчество (Кафедра)"
    if c_teach and t_base:
        if final_dept:
            row[c_teach] = f"{t_base} ({final_dept})"
        else:
            row[c_teach] = t_base
            
    return row

df_main = df_main.apply(update_main_row, axis=1)

# --- ШАГ 5. Сохранение результата ---
all_sheets[target_sheet] = df_main
with pd.ExcelWriter(OUTPUT_FILE, engine='openpyxl') as writer:
    for s_name, s_df in all_sheets.items():
        s_df.to_excel(writer, sheet_name=s_name, index=False)

print(f"\nГОТОВО! Количество строк не изменилось: {len(df_main)}")
print(f"Исправлено несовпадений по группам (факультет/форма/уровень): {updated_groups_count}")
print(f"Обновлено/добавлено аббревиатур кафедр: {updated_depts_count}")
print(f"Результат сохранен в файл: {OUTPUT_FILE}")