---

import pandas as pd
import glob
import os

# --- НАСТРОЙКИ ПУТЕЙ ---
# Папка, где лежат ваши 40+ таблиц из выгрузки (если они в той же папке, оставьте './выгрузка/*.xlsx')
folder_with_tables = './выгрузка/*.xlsx' 
# Ваш основной файл, куда нужно всё собрать (если он есть отдельно)
main_file = 'result.xlsx'
# Итоговый файл
output_file = 'result_final.xlsx'

# 1. Сбор всех таблиц в одну
all_files = glob.glob(folder_with_tables)
df_list = []

# Если основной файл лежит отдельно, добавляем его в список чтения
if os.path.exists(main_file) and main_file not in all_files:
    all_files.append(main_file)

for file in all_files:
    try:
        # Читаем все листы в каждом Excel-файле (на случай, если данные на разных вкладках)
        xls = pd.read_excel(file, sheet_name=None)
        for sheet_name, df_sheet in xls.items():
            # Убираем лишние пробелы в названиях столбцов
            df_sheet.columns = df_sheet.columns.astype(str).str.strip()
            if 'Наименование дисциплины' in df_sheet.columns and 'Группа' in df_sheet.columns:
                df_list.append(df_sheet)
    except Exception as e:
        print(f"Ошибка при чтении файла {file}: {e}")

if not df_list:
    raise ValueError("Не найдено подходящих таблиц с нужными столбцами!")

# Объединяем все найденные таблицы
df = pd.concat(df_list, ignore_index=True)

# 2. Фильтруем предмет "Физическая культура и спорт" (без учета регистра и лишних пробелов)
target_subject = 'физическая культура и спорт'
df_filtered = df[
    df['Наименование дисциплины'].astype(str).str.strip().str.lower() == target_subject
].copy()

# 3. Разбиваем группы, записанные через "|", на отдельные строки
df_filtered['Группа'] = df_filtered['Группа'].astype(str).str.split('|')
df_exploded = df_filtered.explode('Группа')
df_exploded['Группа'] = df_exploded['Группа'].str.strip()

# Определяем точное название первого столбца с институтом/факультетом (на фото он обрезан: "Институт гру...")
inst_col = [col for col in df_exploded.columns if 'институт' in col.lower() or 'факультет' in col.lower()][0]

# 4. Замена факультетов по вашим правилам
# Меняем ИГРИК на ФГРИК
df_exploded[inst_col] = df_exploded[inst_col].astype(str).str.strip().replace({'ИГРИК': 'ФГРИК'})

# Для групп 261-1011 и 264-1011 ставим факультет ПШ ИД
df_exploded.loc[df_exploded['Группа'].isin(['261-1011', '264-1011']), inst_col] = 'ПШ ИД'

# 5. Автозаполнение Формы обучения и Уровня образования по 3-й цифре группы (например, 256-333 -> '6')
def parse_group_code(row):
    group = str(row['Группа'])
    # Проверяем, что строка начинается минимум с 3 цифр до дефиса
    prefix = group.split('-')[0]
    if len(prefix) >= 3 and prefix.isdigit():
        code = prefix[2] # Третья цифра (индекс 2)
        mapping = {
            '1': ('Очная', 'Бакалавриат'),
            '2': ('Очно-заочная', 'Бакалавриат'),
            '3': ('Заочная', 'Бакалавриат'),
            '4': ('Очная', 'Магистратура'),
            '5': ('Очно-заочная', 'Магистратура'),
            '6': ('Заочная', 'Магистратура')
        }
        if code in mapping:
            return pd.Series(mapping[code])
            
    # Если формат группы нестандартный, оставляем старые значения
    return pd.Series([row.get('Форма обучения'), row.get('Уровень образования')])

df_exploded[['Форма обучения', 'Уровень образования']] = df_exploded.apply(parse_group_code, axis=1)

# Удаляем полные дубликаты строк, если они возникли при слиянии 40 таблиц
df_exploded = df_exploded.drop_duplicates()

# 6. Сохраняем результат
df_exploded.to_excel(output_file, index=False)
print(f"Готово! Обработано строк: {len(df_exploded)}. Результат в файле: {output_file}")