---

import os
import re
import glob
import pandas as pd

# ================= НАСТРОЙКИ =================
MAIN_FILE = 'result.xlsx'           # Ваша основная таблица
DUMP_FOLDER = './выгрузка'          # Папка с 40+ файлами выгрузки
OUTPUT_FILE = 'result_updated.xlsx' # Итоговый файл

# Если нужно добавлять из выгрузки ТОЛЬКО физкультуру — оставьте True.
# Если нужно добавить вообще ВСЕ недостающие предметы (заочников и очно-заочников) из 40 файлов — поставьте False.
ONLY_PE_FROM_DUMPS = False
# =============================================

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_str(val):
    """Очищает строку для точного сравнения (убирает лишние пробелы и невидимые символы)"""
    if pd.isna(val) or str(val).strip().lower() in ['nan', 'none', '']:
        return ''
    return re.sub(r'\s+', ' ', str(val).strip()).lower()

def process_dataframe(df, cols_map, only_pe_explode=False):
    """Разбивает группы, правит факультеты, проставляет форму обучения и кафедру"""
    if df.empty:
        return df

    c_inst = cols_map['inst']
    c_grp = cols_map['grp']
    c_form = cols_map['form']
    c_lvl = cols_map['lvl']
    c_subj = cols_map['subj']
    c_teach = cols_map['teach']
    c_dept = cols_map['dept']

    # 1. Разбиваем группы через "|" на отдельные строки
    if c_grp and c_grp in df.columns:
        if only_pe_explode and c_subj in df.columns:
            # В основной таблице разбиваем "|" только у физкультуры (или везде, где есть "|")
            mask_pipe = df[c_grp].astype(str).str.contains(r'\|', na=False)
            df.loc[mask_pipe, c_grp] = df.loc[mask_pipe, c_grp].astype(str).str.split('|')
            df = df.explode(c_grp).reset_index(drop=True)
        else:
            df[c_grp] = df[c_grp].astype(str).str.split('|')
            df = df.explode(c_grp).reset_index(drop=True)
        
        df[c_grp] = df[c_grp].astype(str).str.strip()

    # 2. Меняем ИГРИК -> ФГРИК и ставим ПШ ИД для 261-1011, 264-1011
    if c_inst and c_inst in df.columns:
        df[c_inst] = df[c_inst].replace({'ИГРИК': 'ФГРИК'})
        if c_grp and c_grp in df.columns:
            mask_pshid = df[c_grp].isin(['261-1011', '264-1011'])
            df.loc[mask_pshid, c_inst] = 'ПШ ИД'

    # 3. Определяем Форму обучения и Уровень образования по 3-й цифре группы (256-333 -> 6)
    code_map = {
        '1': ('Очная', 'Бакалавриат'),
        '2': ('Очно-заочная', 'Бакалавриат'),
        '3': ('Заочная', 'Бакалавриат'),
        '4': ('Очная', 'Магистратура'),
        '5': ('Очно-заочная', 'Магистратура'),
        '6': ('Заочная', 'Магистратура')
    }

    def apply_group_logic(row):
        grp = str(row[c_grp]).strip() if c_grp else ''
        prefix = grp.split('-')[0]
        if len(prefix) >= 3 and prefix[:3].isdigit():
            third_digit = prefix[2]
            if third_digit in code_map:
                if c_form:
                    row[c_form] = code_map[third_digit][0]
                if c_lvl:
                    row[c_lvl] = code_map[third_digit][1]

        # 4. Если столбец "Кафедра" пустой, но в "Преподаватель" есть "(ИМП)" -> вытаскиваем кафедру
        if c_teach and c_dept and c_teach in row and c_dept in row:
            dept_val = str(row[c_dept]).strip()
            teach_val = str(row[c_teach]).strip()
            if (pd.isna(row[c_dept]) or dept_val in ['', 'nan', 'None']) and '(' in teach_val and ')' in teach_val:
                match = re.search(r'\(([^)]+)\)\s*$', teach_val)
                if match:
                    row[c_dept] = match.group(1).strip()

        return row

    df = df.apply(apply_group_logic, axis=1)
    return df

# --- ШАГ 1. Читаем все листы вашей основной таблицы ---
if not os.path.exists(MAIN_FILE):
    raise FileNotFoundError(f"Файл {MAIN_FILE} не найден!")

print(f"Загрузка файла {MAIN_FILE}...")
all_main_sheets = pd.read_excel(MAIN_FILE, sheet_name=None)

# Берем первый (или активный) лист как основной рабочий
target_sheet_name = list(all_main_sheets.keys())[0]
main_df = all_main_sheets[target_sheet_name].copy()
main_df.columns = main_df.columns.astype(str).str.strip()

print(f"Рабочий лист: '{target_sheet_name}', исходных строк: {len(main_df)}")

# Определяем столбцы по вашей фотографии
cols_map = {
    'inst': find_col(main_df.columns, ['факультет', 'институт']),
    'grp': find_col(main_df.columns, ['групп']),
    'form': find_col(main_df.columns, ['форма']),
    'lvl': find_col(main_df.columns, ['уровень']),
    'subj': find_col(main_df.columns, ['наименование', 'дисциплин', 'предмет']),
    'teach': find_col(main_df.columns, ['преподавател', 'фио', 'ппс']),
    'dept': find_col(main_df.columns, ['кафедр'])
}

print("Сопоставлены столбцы:", {k: v for k, v in cols_map.items() if v})

# Обрабатываем основную таблицу (разбиваем '|', правим ИГРИК и ПШ ИД, НИЧЕГО НЕ УДАЛЯЯ)
main_df = process_dataframe(main_df, cols_map, only_pe_explode=True)
print(f"Строк в основной таблице после разделения групп через '|': {len(main_df)}")

# Автоматический справочник: Группа -> Институт (чтобы заполнять институт, если в выгрузке он пустой)
group_to_inst = {}
if cols_map['grp'] and cols_map['inst']:
    for _, r in main_df[[cols_map['grp'], cols_map['inst']]].dropna().iterrows():
        g_val = str(r[cols_map['grp']]).strip()
        i_val = str(r[cols_map['inst']]).strip()
        if g_val and i_val and i_val != 'nan':
            group_to_inst[g_val] = i_val


# --- ШАГ 2. Собираем данные из 40+ файлов выгрузки ---
dump_files = glob.glob(os.path.join(DUMP_FOLDER, '*.xls*'))
print(f"\nНайдено файлов в папке '{DUMP_FOLDER}': {len(dump_files)}")

new_rows_list = []

for fpath in dump_files:
    if os.path.abspath(fpath) == os.path.abspath(MAIN_FILE) or '~$' in fpath:
        continue

    try:
        xls = pd.read_excel(fpath, sheet_name=None, header=None)
        for s_name, raw_df in xls.items():
            if raw_df.empty:
                continue

            # Ищем строку с шапкой таблицы (первые 20 строк)
            header_idx = None
            for idx, row in raw_df.head(20).iterrows():
                row_text = ' '.join(row.dropna().astype(str)).lower()
                if 'групп' in row_text and any(w in row_text 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_inst = find_col(df_s.columns, ['факультет', 'институт'])
            s_grp = find_col(df_s.columns, ['групп'])
            s_form = find_col(df_s.columns, ['форма'])
            s_lvl = 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, ['кафедр'])

            if not s_grp or not s_subj:
                continue

            # Переименовываем столбцы под формат вашей основной таблицы
            rename_map = {}
            if s_inst and cols_map['inst']: rename_map[s_inst] = cols_map['inst']
            if s_grp and cols_map['grp']: rename_map[s_grp] = cols_map['grp']
            if s_form and cols_map['form']: rename_map[s_form] = cols_map['form']
            if s_lvl and cols_map['lvl']: rename_map[s_lvl] = cols_map['lvl']
            if s_subj and cols_map['subj']: rename_map[s_subj] = cols_map['subj']
            if s_teach and cols_map['teach']: rename_map[s_teach] = cols_map['teach']
            if s_dept and cols_map['dept']: rename_map[s_dept] = cols_map['dept']

            df_s = df_s.rename(columns=rename_map)

            # Убираем пустые строки без группы или дисциплины
            df_s = df_s.dropna(subset=[cols_map['grp'], cols_map['subj']], how='any')
            if df_s.empty:
                continue

            # Если включен фильтр только на физкультуру
            if ONLY_PE_FROM_DUMPS:
                mask_pe = df_s[cols_map['subj']].astype(str).str.lower().str.contains('физическая культура', na=False)
                df_s = df_s[mask_pe].copy()
                if df_s.empty:
                    continue

            # Добавляем недостающие столбцы из главной таблицы
            for c in main_df.columns:
                if c not in df_s.columns:
                    df_s[c] = None

            df_s = df_s[main_df.columns].copy()
            new_rows_list.append(df_s)

    except Exception as e:
        print(f"Ошибка при чтении {os.path.basename(fpath)}: {e}")


# --- ШАГ 3. Проверка на дубликаты и добавление ТОЛЬКО новых строк ---
if new_rows_list:
    dump_df = pd.concat(new_rows_list, ignore_index=True)
    dump_df = process_dataframe(dump_df, cols_map, only_pe_explode=False)

    # Подтягиваем институт по номеру группы, если в файле выгрузки не было столбца "Институт"
    if cols_map['inst'] and cols_map['grp']:
        def fill_missing_inst(row):
            inst_val = str(row[cols_map['inst']]).strip()
            grp_val = str(row[cols_map['grp']]).strip()
            if (pd.isna(row[cols_map['inst']]) or inst_val in ['', 'nan', 'None']) and grp_val in group_to_inst:
                row[cols_map['inst']] = group_to_inst[grp_val]
            return row
        dump_df = dump_df.apply(fill_missing_inst, axis=1)

    # Создаем уникальный ключ для проверки: Группа + Дисциплина + Преподаватель
    def make_key(df_in):
        g = df_in[cols_map['grp']].apply(clean_str) if cols_map['grp'] else ''
        s = df_in[cols_map['subj']].apply(clean_str) if cols_map['subj'] else ''
        t = df_in[cols_map['teach']].apply(clean_str) if cols_map['teach'] and cols_map['teach'] in df_in else ''
        return g + "||" + s + "||" + t

    main_df['_check_key'] = make_key(main_df)
    dump_df['_check_key'] = make_key(dump_df)

    # Убираем пустые группы и дубликаты внутри самой выгрузки
    dump_df = dump_df[dump_df[cols_map['grp']].apply(clean_str) != '']
    dump_df = dump_df.drop_duplicates(subset=['_check_key'])

    # СТРОГАЯ ПРОВЕРКА: оставляем только те строки, которых НЕТ в main_df
    existing_keys = set(main_df['_check_key'])
    rows_to_append = dump_df[~dump_df['_check_key'].isin(existing_keys)].copy()

    # Удаляем технический столбец ключа
    main_df.drop(columns=['_check_key'], inplace=True)
    rows_to_append.drop(columns=['_check_key'], inplace=True)

    print(f"\nНайдено абсолютно новых строк для добавления: {len(rows_to_append)}")

    # Дописываем новые строки в конец вашей таблицы
    final_df = pd.concat([main_df, rows_to_append], ignore_index=True)
else:
    print("\nВ папке выгрузки не найдено подходящих таблиц.")
    final_df = main_df


# --- ШАГ 4. Сохраняем результат (со всеми остальными вкладками) ---
all_main_sheets[target_sheet_name] = final_df

with pd.ExcelWriter(OUTPUT_FILE, engine='openpyxl') as writer:
    for s_name, s_df in all_main_sheets.items():
        s_df.to_excel(writer, sheet_name=s_name, index=False)

print(f"Готово! Итоговое количество строк на листе '{target_sheet_name}': {len(final_df)}")
print(f"Файл сохранен как: {OUTPUT_FILE}")