---

import pandas as pd
import numpy as np
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Font, Alignment, Border, Side
from openpyxl.utils import get_column_letter

# ---------------------------
# 1. Загрузка и предобработка
# ---------------------------
file_path = 'По группам продуктов ЗП (27.07.2026 23_49).xlsx'  # укажите свой путь

df_raw = pd.read_excel(file_path, sheet_name='ДАННЫЕ', header=None, skiprows=3)
header_row_idx = df_raw[df_raw[0] == 'Номер кассовой смены'].index[0]
df = pd.read_excel(file_path, sheet_name='ДАННЫЕ', header=header_row_idx)

df.columns = df.columns.str.strip()
df = df[df['Продукт'].notna() & (df['Продукт'] != '')]
df['Кол-во проданного'] = pd.to_numeric(df['Кол-во проданного'], errors='coerce')
df['Выручка'] = pd.to_numeric(df['Выручка'], errors='coerce')
df = df[(df['Кол-во проданного'] > 0) & (df['Выручка'] > 0)]

# Нормализация названий заведений
df['Место реализации'] = df['Место реализации'].replace({
    'Королев Ц6А': 'Королёв Ц6А',
    'Королёв П7': 'Королёв П7',
    'Балашиха': 'Балашиха'
})

# Дни недели
df['Дата'] = pd.to_datetime(df['Смена']).dt.date
df['День недели'] = pd.to_datetime(df['Смена']).dt.day_name()
days_map = {
    'Monday': 'понедельник', 'Tuesday': 'вторник', 'Wednesday': 'среда',
    'Thursday': 'четверг', 'Friday': 'пятница', 'Saturday': 'суббота', 'Sunday': 'воскресенье'
}
df['День недели'] = df['День недели'].map(days_map)
week_order = ['понедельник', 'вторник', 'среда', 'четверг', 'пятница', 'суббота', 'воскресенье']
df['День недели'] = pd.Categorical(df['День недели'], categories=week_order, ordered=True)

# Группировка по заведению, продукту, дню
df_agg = df.groupby(['Место реализации', 'Продукт', 'Дата', 'День недели'], as_index=False).agg({
    'Кол-во проданного': 'sum',
    'Выручка': 'sum'
})

# -------------------------------------------------
# 2. ABC-анализ для каждого заведения (по продуктам)
# -------------------------------------------------
def abc_analysis(group):
    group = group.sort_values('Выручка', ascending=False)
    total = group['Выручка'].sum()
    group['cum_sum'] = group['Выручка'].cumsum()
    group['cum_percent'] = group['cum_sum'] / total * 100
    def assign(pct):
        if pct <= 80:
            return 'A'
        elif pct <= 95:
            return 'B'
        else:
            return 'C'
    group['ABC'] = group['cum_percent'].apply(assign)
    return group

abc_results = {}
for place in df_agg['Место реализации'].unique():
    place_data = df_agg[df_agg['Место реализации'] == place]
    product_sales = place_data.groupby('Продукт', as_index=False)['Выручка'].sum()
    product_sales = abc_analysis(product_sales)
    abc_results[place] = product_sales[['Продукт', 'ABC']]

# Добавляем категорию в основной датафрейм
df_agg = df_agg.merge(
    pd.concat([df.assign(Заведение=place) for place, df in abc_results.items()], ignore_index=True),
    left_on=['Место реализации', 'Продукт'],
    right_on=['Заведение', 'Продукт'],
    how='left'
).drop(columns=['Заведение'])

# ------------------------------------------------
# 3. Подготовка данных для Excel-таблицы (по дням)
# ------------------------------------------------
# Создаём сводную таблицу: строки - продукт, столбцы - день, значения - кол-во и сумма
def make_pivot(place):
    data = df_agg[df_agg['Место реализации'] == place]
    # Добавим категорию ABC для каждого продукта (одна на продукт)
    product_abc = data[['Продукт', 'ABC']].drop_duplicates()
    # Свод по количеству
    pivot_qty = data.pivot(index='Продукт', columns='День недели', values='Кол-во проданного').fillna(0)
    pivot_qty = pivot_qty.reindex(columns=week_order, fill_value=0)
    # Свод по выручке
    pivot_rev = data.pivot(index='Продукт', columns='День недели', values='Выручка').fillna(0)
    pivot_rev = pivot_rev.reindex(columns=week_order, fill_value=0)
    # Объединяем: для каждого дня две колонки (кол-во, сумма)
    # Создаём мультииндекс для столбцов
    cols = pd.MultiIndex.from_product([week_order, ['Кол-во', 'Сумма']])
    final = pd.DataFrame(index=pivot_qty.index, columns=cols)
    for day in week_order:
        final[(day, 'Кол-во')] = pivot_qty[day]
        final[(day, 'Сумма')] = pivot_rev[day]
    # Добавляем столбец с категорией ABC (для цвета)
    final['ABC'] = product_abc.set_index('Продукт')['ABC']
    # Итоговая строка
    total_row = {}
    for day in week_order:
        total_row[(day, 'Кол-во')] = final[(day, 'Кол-во')].sum()
        total_row[(day, 'Сумма')] = final[(day, 'Сумма')].sum()
    total_row['ABC'] = 'ИТОГО'
    final.loc['ИТОГО'] = total_row
    return final

# Создаём словарь листов
sheets = {}
for place in df_agg['Место реализации'].unique():
    sheets[place] = make_pivot(place)

# Также создадим сводный лист с долей ABC по заведениям
abc_share = df_agg.groupby(['Место реализации', 'ABC']).size().unstack(fill_value=0)
abc_share_pct = abc_share.div(abc_share.sum(axis=1), axis=0) * 100
abc_share_pct = abc_share_pct.round(1)
abc_share_pct['Итого'] = abc_share.sum(axis=1)
sheets['Сводка_ABC'] = abc_share_pct

# ------------------------------------------------
# 4. Запись в Excel с форматированием (openpyxl)
# ------------------------------------------------
output_file = 'ABC_sales_dashboard.xlsx'
with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
    for sheet_name, df_sheet in sheets.items():
        df_sheet.to_excel(writer, sheet_name=sheet_name, startrow=1)

# Теперь стилизуем через openpyxl
wb = load_workbook(output_file)

# Цвета для категорий
color_map = {
    'A': PatternFill(start_color='2E8B57', end_color='2E8B57', fill_type='solid'),  # зелёный
    'B': PatternFill(start_color='DAA520', end_color='DAA520', fill_type='solid'),  # жёлтый
    'C': PatternFill(start_color='CD5C5C', end_color='CD5C5C', fill_type='solid'),  # красный
    'ИТОГО': PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')  # синий для итогов
}
# Стиль границ
thin_border = Border(
    left=Side(style='thin'), right=Side(style='thin'),
    top=Side(style='thin'), bottom=Side(style='thin')
)

for sheet_name in wb.sheetnames:
    ws = wb[sheet_name]
    # Пропускаем сводку (там другая структура)
    if sheet_name == 'Сводка_ABC':
        continue

    # Удалим первую пустую строку (заголовок не нужен)
    ws.delete_rows(1)
    # Добавим заголовок заведения
    ws.insert_rows(1)
    ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=len(ws[1]))
    ws.cell(row=1, column=1, value=f'Заведение: {sheet_name} (продажи по дням недели)')
    ws.cell(row=1, column=1).font = Font(size=16, bold=True)
    ws.cell(row=1, column=1).alignment = Alignment(horizontal='center')

    # Определим, где находится столбец ABC (последний)
    abc_col_idx = None
    for col in range(1, ws.max_column + 1):
        if ws.cell(row=2, column=col).value == 'ABC':
            abc_col_idx = col
            break

    # Теперь пройдём по строкам, начиная с 3 (первая строка - заголовок, вторая - подзаголовки дней)
    for row in range(3, ws.max_row + 1):
        # Проверим, что в ячейке ABC есть значение
        abc_val = ws.cell(row=row, column=abc_col_idx).value
        if abc_val is None:
            continue
        # Для ИТОГО используем синий цвет
        if abc_val == 'ИТОГО':
            fill = color_map['ИТОГО']
            font = Font(bold=True, color='FFFFFF')
        else:
            fill = color_map.get(abc_val, PatternFill())
            font = Font(bold=False)
        # Окрашиваем все ячейки суммы (чётные столбцы, начиная с 2-го дня)
        # Структура: столбцы: день1-кол-во, день1-сумма, день2-кол-во, день2-сумма, ..., ABC
        # Индексы колонок: 1-й день кол-во = 2, сумма = 3, и т.д. (т.к. 1-й столбец - Продукт)
        for col in range(2, abc_col_idx):  # до столбца ABC
            # Проверим, что это столбец с суммой (чётный порядковый номер в паре)
            # Определим, является ли колонка "Сумма" по заголовку
            header_val = ws.cell(row=2, column=col).value
            if header_val == 'Сумма':
                cell = ws.cell(row=row, column=col)
                if cell.value and cell.value != 0:
                    cell.fill = fill
                    cell.font = font
                    cell.border = thin_border
                    # Также можно применить числовой формат
                    cell.number_format = '#,##0.00'
            else:
                # Для кол-ва просто границы
                ws.cell(row=row, column=col).border = thin_border
        # Окрасим ячейку с ABC (для информации)
        ws.cell(row=row, column=abc_col_idx).fill = fill
        ws.cell(row=row, column=abc_col_idx).font = font
        ws.cell(row=row, column=abc_col_idx).border = thin_border

    # Также оформим заголовки дней (строка 2) и названия продуктов (столбец 1)
    for col in range(1, ws.max_column + 1):
        ws.cell(row=2, column=col).font = Font(bold=True)
        ws.cell(row=2, column=col).alignment = Alignment(horizontal='center')
        ws.cell(row=2, column=col).border = thin_border
    for row in range(3, ws.max_row + 1):
        ws.cell(row=row, column=1).font = Font(bold=True)
        ws.cell(row=row, column=1).border = thin_border
        ws.cell(row=row, column=1).alignment = Alignment(horizontal='left')

    # Автоширина колонок
    for col in range(1, ws.max_column + 1):
        col_letter = get_column_letter(col)
        max_len = 0
        for row in range(1, ws.max_row + 1):
            val = ws.cell(row=row, column=col).value
            if val:
                max_len = max(max_len, len(str(val)))
        ws.column_dimensions[col_letter].width = max_len + 2

    # Заморозка панели (чтобы заголовки и продукты всегда были видны)
    ws.freeze_panes = 'B3'  # замораживаем строку 2 и столбец 1

# Оформление сводного листа "Сводка_ABC"
ws_sum = wb['Сводка_ABC']
# Удалим первую пустую строку
ws_sum.delete_rows(1)
# Добавим заголовок
ws_sum.insert_rows(1)
ws_sum.merge_cells(start_row=1, start_column=1, end_row=1, end_column=ws_sum.max_column)
ws_sum.cell(row=1, column=1, value='Сравнение долей ABC-категорий по заведениям')
ws_sum.cell(row=1, column=1).font = Font(size=16, bold=True)
ws_sum.cell(row=1, column=1).alignment = Alignment(horizontal='center')
# Оформим заголовки (строка 2)
for col in range(1, ws_sum.max_column + 1):
    ws_sum.cell(row=2, column=col).font = Font(bold=True)
    ws_sum.cell(row=2, column=col).alignment = Alignment(horizontal='center')
    ws_sum.cell(row=2, column=col).border = thin_border
# Данные
for row in range(3, ws_sum.max_row + 1):
    for col in range(1, ws_sum.max_column + 1):
        ws_sum.cell(row=row, column=col).border = thin_border
        if col == 1:  # название заведения
            continue
        # Если это столбец с процентом A/B/C, окрасим соответственно
        header = ws_sum.cell(row=2, column=col).value
        if header in ['A', 'B', 'C']:
            val = ws_sum.cell(row=row, column=col).value
            if val:
                ws_sum.cell(row=row, column=col).fill = color_map[header]
                ws_sum.cell(row=row, column=col).number_format = '0.0"%"'
# Автоширина
for col in range(1, ws_sum.max_column + 1):
    col_letter = get_column_letter(col)
    max_len = 0
    for row in range(1, ws_sum.max_row + 1):
        val = ws_sum.cell(row=row, column=col).value
        if val:
            max_len = max(max_len, len(str(val)))
    ws_sum.column_dimensions[col_letter].width = max_len + 2

wb.save(output_file)
print(f'Готово! Создан файл: {output_file}')
print(f'Листы: {", ".join(wb.sheetnames)}')