"""Ассортиментный отчёт по текущему свободному остатку уценки.

Отчёт не подменяет отсутствующие данные нулями и не прогнозирует спрос.
Одна строка реестра соответствует product_id; количество больше единицы
явно обозначает агрегированную запись источника.
"""
from __future__ import annotations

from collections import defaultdict
from dataclasses import dataclass
from datetime import date, datetime
from decimal import Decimal
from io import BytesIO
import re
from statistics import mean, median
from typing import Any, Iterable

from openpyxl import Workbook
from openpyxl.cell import WriteOnlyCell
from openpyxl.styles import Alignment, Border, Font, NamedStyle, PatternFill, Side
from openpyxl.utils import get_column_letter


AGE_BANDS = (
    ("0–30 дней", 0, 30), ("31–60 дней", 31, 60),
    ("61–90 дней", 61, 90), ("91–180 дней", 91, 180),
    ("181–365 дней", 181, 365), ("366–730 дней", 366, 730),
    ("Более 730 дней", 731, None),
)
HISTORY_NOTE = ("Локальная база приложения содержит текущий срез карточек уценки; "
                "он не доказывает дату первоначального поступления товара в компанию.")


@dataclass(frozen=True)
class Column:
    title: str
    key: str
    width: float = 16
    style: str = "body"


def _text(value: Any) -> str:
    return str(value or "").strip()


def _number(value: Any) -> float | None:
    return float(value) if value is not None else None


def _days(report_date: date, value: date | None) -> int | None:
    return max(0, (report_date - value).days) if value else None


def _matches(text: str, pattern: str) -> bool:
    return bool(re.search(pattern, text, re.I))


def _age_band(days: int | None) -> str:
    if days is None:
        return "Дата неизвестна"
    for label, low, high in AGE_BANDS:
        if days >= low and (high is None or days <= high):
            return label
    return "Дата неизвестна"


def _defect_details(description: str, condition_type: str) -> dict[str, Any]:
    text = f"{description} {condition_type}".casefold()
    technical = _matches(text, r"не\s*работ|неисправ|не\s*включ|ошибк|не\s*нагрев|"
                               r"не\s*охлаж|слом|протеч|коротк|поврежден\w*\s+кабел")
    missing = _matches(text, r"некомплект|не\s*комплект|отсутств|не\s*хватает|без\s+")
    used = _matches(text, r"\bб\s*/?\s*у\b|след\w*\s+эксплуатац|использова")
    restored = _matches(text, r"после\s+ремонт|восстанов|после\s+сервис")
    cosmetic = _matches(text, r"царап|пот[её]рт|скол|вмят|трещ|деформ|ржав|"
                              r"загряз|поврежден\w*\s+упаков|упаковк\w*\s+повреж")
    safety = _matches(text, r"пожар|коротк\w*\s+замыкан|оголен|искр|"
                            r"поврежден\w*\s+кабел|протеч")
    types = []
    for flag, label in ((technical, "Техническая неисправность"),
                        (missing, "Некомплектность"), (used, "Следы использования"),
                        (restored, "Восстановленный товар"),
                        (cosmetic, "Косметический недостаток")):
        if flag:
            types.append(label)
    if not types:
        types.append("Не определён")
    if technical:
        functionality = "Неисправен или требуется диагностика"
    elif _matches(text, r"исправен|работоспособ|проверен\w*\s*,?\s*работ"):
        functionality = "Работоспособность подтверждена описанием"
    else:
        functionality = "Работоспособность не подтверждена"
    if missing:
        completeness = "Некомплект"
    elif _matches(text, r"полный\s+комплект|комплектн"):
        completeness = "Комплектность указана"
    else:
        completeness = "Комплектность не указана"
    packaging = ("Повреждена" if _matches(text, r"упаковк|коробк") and cosmetic
                 else "Не указано")
    original_box = ("Нет или повреждена" if _matches(text, r"без\s+короб|нет\s+короб")
                    else "Не указано")
    diagnosis = ("Упомянута в описании" if _matches(text, r"диагност|провер")
                 else "Не указана")
    return {
        "defect_type": "; ".join(types), "technical": technical,
        "safety": safety, "missing_parts": missing, "used": used,
        "functionality": functionality, "completeness": completeness,
        "packaging": packaging, "original_box": original_box,
        "diagnosis": diagnosis,
    }


def _placement(item: dict[str, Any], defect: dict[str, Any]) -> dict[str, Any]:
    text = " ".join(_text(item.get(key)) for key in
                    ("product_name", "category_1", "category_2", "category_3", "category_4")).casefold()
    dimensions = [item.get("depth_cm"), item.get("width_cm"), item.get("height_cm")]
    known = [float(value) for value in dimensions if value is not None]
    max_dimension = max(known) if known else None
    weight = _number(item.get("weight_kg"))
    furniture = _matches(text, r"мебел|шкаф|стол|стул|кресл|диван|кроват|"
                               r"матрас|стеллаж|комод|тумб|полк")
    large_appliance = _matches(text, r"холодиль|морозиль|стираль|сушильн\w*\s+машин|"
                                    r"посудомоеч|духов\w*\s+шкаф|плит[аы]|винн\w*\s+шкаф")
    assembly = furniture and _matches(text, r"шкаф|стол|кроват|стеллаж|комод|тумб|полк|кресл")
    power = _matches(text, r"техник|электр|телевиз|компьют|ноутбук|пылесос|"
                           r"холодиль|стираль|кофе|чайник|микровол|духов")
    dimensions_missing = len(known) < 3
    large = ((max_dimension is not None and max_dimension >= 100) or
             (weight is not None and weight >= 30) or large_appliance or furniture)
    if defect["technical"] or (dimensions_missing and not text.strip()):
        placement_class = "X"
    elif (large_appliance or furniture) and ((max_dimension or 0) >= 150 or (weight or 0) >= 50):
        placement_class = "E"
    elif furniture:
        placement_class = "D"
    elif large_appliance:
        placement_class = "C"
    elif (max_dimension or 0) >= 60 or (weight or 0) >= 10:
        placement_class = "B"
    else:
        placement_class = "A"
    physical = ""
    if placement_class == "E":
        physical = "Мебель" if furniture else "Крупногабаритная техника"
    placement_type = {
        "A": "Полка или стеллаж", "B": "Отдельное или напольное место",
        "C": "Напольная экспозиция КБТ", "D": "Зона мебели после сборки",
        "E": "Продажа со склада по карточке и фотографиям",
        "X": "Не определён до проверки",
    }[placement_class]
    base_time = {"A": 10, "B": 20, "C": 30, "D": 45, "E": 20, "X": None}[placement_class]
    needs_check = defect["technical"] or defect["functionality"].endswith("не подтверждена")
    prep_time = None if base_time is None else base_time + (30 if assembly else 0) + (15 if needs_check else 0)
    return {
        "placement_class": placement_class, "physical_class": physical,
        "placement_type": placement_type, "assembly": assembly,
        "assembly_complexity": "Средняя" if assembly else "Не требуется",
        "power_demo": power, "large_item": large,
        "special_conditions": large, "dimensions_missing": dimensions_missing,
        "check_before_display": needs_check, "prep_minutes": prep_time,
    }


def prepare_item(row: dict[str, Any], report_date: date) -> dict[str, Any]:
    item = dict(row)
    quantity = max(0, int(item.get("available_quantity") or 0))
    description = _text(item.get("description"))
    defect = _defect_details(description, _text(item.get("condition_type")))
    placement = _placement(item, defect)
    has_price = bool(item.get("has_price")) and item.get("site_price") is not None
    if quantity <= 0:
        admission = "Не допущен: нет доступного остатка"
    elif defect["safety"]:
        admission = "Не допущен до устранения ограничений"
    elif defect["technical"] or not description or not has_price:
        admission = "Требует проверки"
    else:
        admission = "Предварительно допущен"
    needs_check = admission == "Требует проверки" or placement["check_before_display"]
    exclude = admission.startswith("Не допущен")
    can_display = (admission == "Предварительно допущен" and
                   placement["placement_class"] in {"A", "B", "C"} and
                   not placement["assembly"] and not needs_check)
    days_discount = _days(report_date, item.get("date_of_creation"))
    days_no_movement = _days(report_date, item.get("last_stock_change_date"))
    new_price = _number(item.get("current_new_price"))
    price = _number(item.get("site_price"))
    cost = _number(item.get("accounting_cost"))
    discount = _number(item.get("discount_percent"))
    issues = ["Нет даты первого поступления в компанию", "Нет даты поступления в отдел брака"]
    for missing, label in (
        (not item.get("product_id"), "Нет уникального идентификатора"),
        (not item.get("origin_product_id"), "Нет кода нового товара"),
        (not description, "Нет описания дефекта"),
        (not has_price, "Нет действующей цены уценки"),
        (new_price is None, "Нет актуальной цены нового товара"),
        (cost is None, "Нет учётной стоимости"),
        (placement["dimensions_missing"], "Габариты не определены полностью"),
        (not item.get("category_2"), "Нет товарной категории"),
        (not item.get("warehouses"), "Склад не указан"),
    ):
        if missing:
            issues.append(label)
    score = 0.0
    if not exclude:
        score += 30 if admission == "Предварительно допущен" else 15
        completeness = 20 - 3 * sum((not description, not has_price, new_price is None,
                                     cost is None, placement["dimensions_missing"],
                                     not item.get("category_2")))
        score += max(0, completeness)
        score += {"A": 15, "B": 12, "C": 8, "D": 8, "E": 5, "X": 0}[placement["placement_class"]]
        score += min(10, max(0, (discount or 0) / 5))
        score += min(10, max(0, (days_discount or 0) / 73))
        score += 5 if new_price is not None and price is not None else 0
        score += 5 if cost is not None else 0
    score = round(min(100, score), 1)
    priority = ("Не включать" if exclude else "Высокий" if score >= 70
                else "Средний" if score >= 50 else "Низкий")
    reasons = []
    if can_display:
        reasons.append("готов к выставлению")
    if needs_check:
        reasons.append("нужна проверка")
    if placement["assembly"]:
        reasons.append("нужна сборка")
    if placement["placement_class"] in {"C", "D", "E"}:
        reasons.append("предпочтительна продажа со склада")
    if days_discount is not None and days_discount > 365:
        reasons.append("давний остаток без предположения о спросе")
    item.update(defect)
    item.update(placement)
    item.update({
        "record_type": "Экземпляр" if quantity == 1 else "Агрегированная запись",
        "quantity": quantity, "status": "В текущем срезе свободного остатка",
        "days_company": None, "days_defect": None, "days_discount": days_discount,
        "days_no_movement": days_no_movement, "age_company_band": "Дата неизвестна",
        "age_discount_band": _age_band(days_discount), "date_missing": True,
        "cost_value": cost * quantity if cost is not None else None,
        "discount_value": price * quantity if price is not None else None,
        "absolute_discount": new_price - price if new_price is not None and price is not None else None,
        "admission": admission, "needs_check": needs_check, "exclude": exclude,
        "can_display": can_display, "warehouse_sale": placement["placement_class"] in {"C", "D", "E"},
        "priority_score": score, "priority": priority,
        "priority_reason": "; ".join(reasons) or "требуется оценка специалиста",
        "quality_issues": "; ".join(issues),
        "sales_history": "История продаж недоступна",
    })
    return item


def _weighted_median(rows: Iterable[dict[str, Any]], key: str) -> float | None:
    values: list[float] = []
    for row in rows:
        value = row.get(key)
        if value is not None:
            values.extend([float(value)] * max(0, int(row.get("quantity") or 0)))
    return median(values) if values else None


def _group(items: list[dict[str, Any]], keys: tuple[str, ...]) -> list[tuple[tuple[Any, ...], list[dict[str, Any]]]]:
    groups: dict[tuple[Any, ...], list[dict[str, Any]]] = defaultdict(list)
    for item in items:
        groups[tuple(item.get(key) or "Не указано" for key in keys)].append(item)
    return sorted(groups.items(), key=lambda pair: tuple(str(x) for x in pair[0]))


def _summary(items: list[dict[str, Any]], keys: tuple[str, ...],
             age_key: str = "days_discount") -> list[dict[str, Any]]:
    total = sum(item["quantity"] for item in items) or 1
    rows = []
    for values, group in _group(items, keys):
        quantity = sum(item["quantity"] for item in group)
        ages = [item[age_key] for item in group if item.get(age_key) is not None]
        rows.append({**dict(zip(keys, values)), "records": len(group), "quantity": quantity,
                     "share": quantity / total,
                     "cost_value": sum(item["cost_value"] or 0 for item in group),
                     "discount_value": sum(item["discount_value"] or 0 for item in group),
                     "average_age": mean(ages) if ages else None,
                     "median_age": _weighted_median(group, age_key)})
    return rows


REGISTRY_COLUMNS = (
    Column("Тип записи", "record_type", 22), Column("ID экземпляра", "product_id", 26, "text"),
    Column("Полный код номенклатуры", "product_id", 26, "text"),
    Column("Код нового товара", "origin_product_id", 18, "text"),
    Column("Код 1С уценки", "product_code", 18, "text"),
    Column("Код 1С нового товара", "origin_product_code", 20, "text"),
    Column("Наименование", "product_name", 42, "wrap"), Column("Бренд", "brand", 18),
    Column("Модель", "model", 24), Column("Категория 1", "category_1", 24),
    Column("Категория 2", "category_2", 28), Column("Категория 3", "category_3", 28),
    Column("Исходная категория из 1С", "category_4", 28),
    Column("ID вида номенклатуры", "product_category_id", 18, "integer"),
    Column("ID товарной категории", "product_type_id", 18, "integer"),
    Column("Производитель", "brand", 18), Column("Ссылка 21vek.by", "product_url", 42),
    Column("Ссылка на фотографию", "photo_url", 24), Column("Склад", "warehouses", 36, "wrap"),
    Column("Адрес хранения / ячейка", "storage_address", 20),
    Column("Фактическое количество", "quantity", 16, "integer"),
    Column("Доступное количество", "quantity", 16, "integer"),
    Column("Зарезервированное количество", "reserved_quantity", 18, "integer"),
    Column("Статус товара", "status", 26), Column("Направление / подразделение", "department", 22),
    Column("Ответственный", "responsible", 20), Column("Первое поступление в компанию", "first_company_date", 18, "date"),
    Column("Последнее поступление в компанию", "last_company_date", 18, "date"),
    Column("Дата возврата покупателем", "customer_return_date", 18, "date"),
    Column("Поступление в отдел брака", "defect_date", 18, "date"),
    Column("Получение статуса Уценка", "date_of_creation", 18, "date"),
    Column("Размещение на текущем складе", "current_warehouse_date", 18, "date"),
    Column("Последняя переоценка", "discount_set_date", 18, "date"),
    Column("Последнее изменение остатка / склада", "last_stock_change_date", 20, "date"),
    Column("Дней в компании", "days_company", 14, "integer"), Column("Дней в браке", "days_defect", 14, "integer"),
    Column("Дней в уценке", "days_discount", 14, "integer"), Column("Дней без движения", "days_no_movement", 16, "integer"),
    Column("Нет достоверной даты", "date_missing", 18), Column("Первоначальная розничная цена", "initial_retail_price", 18, "money"),
    Column("Текущая цена нового товара", "current_new_price", 18, "money"),
    Column("Текущая цена уценки", "site_price", 18, "money"), Column("Учётная стоимость", "accounting_cost", 18, "money"),
    Column("Сумма остатка по учётной стоимости", "cost_value", 20, "money"),
    Column("Потенциальная выручка", "discount_value", 20, "money"),
    Column("Абсолютная скидка", "absolute_discount", 16, "money"), Column("Скидка, %", "discount_percent", 12, "percent_number"),
    Column("Дата установления текущей цены", "last_price_change", 18, "date"),
    Column("Переоценок в доступной истории", "repricing_count", 18, "integer"), Column("Валюта", "currency", 10),
    Column("Тип дефекта", "defect_type", 30, "wrap"), Column("Описание дефекта", "description", 60, "wrap"),
    Column("Причина уценки", "condition_type", 20), Column("Комплектность", "completeness", 24),
    Column("Отсутствующие принадлежности", "missing_accessories", 28), Column("Работоспособность", "functionality", 34, "wrap"),
    Column("Статус диагностики", "diagnosis", 22), Column("Состояние упаковки", "packaging", 20),
    Column("Оригинальная коробка", "original_box", 20), Column("Фото фактического дефекта", "actual_defect_photos", 20),
    Column("Допуск к продаже", "admission", 34, "wrap"), Column("Нужна дополнительная проверка", "needs_check", 20),
    Column("Длина товара, см", "depth_cm", 14, "decimal2"), Column("Ширина товара, см", "width_cm", 14, "decimal2"),
    Column("Высота товара, см", "height_cm", 14, "decimal2"), Column("Вес товара, кг", "weight_kg", 14, "decimal3"),
    Column("Длина упаковки, см", "package_depth_cm", 16, "decimal2"), Column("Ширина упаковки, см", "package_width_cm", 16, "decimal2"),
    Column("Высота упаковки, см", "package_height_cm", 16, "decimal2"), Column("Вес в упаковке, кг", "package_weight_kg", 16, "decimal3"),
    Column("Необходимость сборки", "assembly", 18), Column("Сложность сборки", "assembly_complexity", 18),
    Column("Проверка перед выставлением", "check_before_display", 20), Column("Подключение к электропитанию", "power_demo", 20),
    Column("Класс размещения", "placement_class", 14), Column("Физическая категория класса E", "physical_class", 24),
    Column("Предполагаемый тип размещения", "placement_type", 34, "wrap"), Column("Подготовка, минут", "prep_minutes", 16, "integer"),
    Column("Крупногабаритный товар", "large_item", 18), Column("Специальные условия", "special_conditions", 18),
    Column("Габариты не определены", "dimensions_missing", 18), Column("Можно сразу выставить", "can_display", 18),
    Column("Требуется проверка", "needs_check", 18), Column("Требуется сборка", "assembly", 18),
    Column("Предпочтительна продажа со склада", "warehouse_sale", 22), Column("Не включать до устранения ограничений", "exclude", 24),
    Column("Предварительный балл", "priority_score", 16, "decimal1"), Column("Приоритет", "priority", 14),
    Column("Причина приоритета", "priority_reason", 44, "wrap"), Column("История продаж", "sales_history", 24),
    Column("Проблемы качества данных", "quality_issues", 70, "wrap"), Column("Дата среза источника", "source_snapshot_at", 20, "datetime"),
    Column("История доступна с", "history_available_from", 18, "date"),
)


def _styles(workbook: Workbook) -> None:
    for style in (
        NamedStyle(name="body", font=Font(name="Arial", size=9), alignment=Alignment(vertical="center")),
        NamedStyle(name="text", font=Font(name="Arial", size=9), number_format="@", alignment=Alignment(vertical="center")),
        NamedStyle(name="wrap", font=Font(name="Arial", size=9), alignment=Alignment(vertical="top", wrap_text=True)),
        NamedStyle(name="integer", font=Font(name="Arial", size=9), number_format="#,##0", alignment=Alignment(vertical="center")),
        NamedStyle(name="decimal1", font=Font(name="Arial", size=9), number_format="#,##0.0", alignment=Alignment(vertical="center")),
        NamedStyle(name="decimal2", font=Font(name="Arial", size=9), number_format="#,##0.00", alignment=Alignment(vertical="center")),
        NamedStyle(name="decimal3", font=Font(name="Arial", size=9), number_format="#,##0.000", alignment=Alignment(vertical="center")),
        NamedStyle(name="money", font=Font(name="Arial", size=9), number_format='#,##0.00 "BYN"', alignment=Alignment(vertical="center")),
        NamedStyle(name="percent", font=Font(name="Arial", size=9), number_format="0.0%", alignment=Alignment(vertical="center")),
        NamedStyle(name="percent_number", font=Font(name="Arial", size=9), number_format="0.0", alignment=Alignment(vertical="center")),
        NamedStyle(name="date", font=Font(name="Arial", size=9), number_format="dd.mm.yyyy", alignment=Alignment(vertical="center")),
        NamedStyle(name="datetime", font=Font(name="Arial", size=9), number_format="dd.mm.yyyy hh:mm", alignment=Alignment(vertical="center")),
    ):
        workbook.add_named_style(style)


def _append_meta(sheet, title: str, generated_at: datetime, source: str, note: str) -> None:
    title_cell = WriteOnlyCell(sheet, title)
    title_cell.font = Font(name="Arial", size=14, bold=True, color="1D2025")
    sheet.append([title_cell])
    for value in (f"Сформирован: {generated_at:%d.%m.%Y %H:%M}", source, note):
        cell = WriteOnlyCell(sheet, value)
        cell.font = Font(name="Arial", size=9, italic=True, color="666A70")
        sheet.append([cell])
    sheet.append([])


def _write_sheet(workbook: Workbook, name: str, title: str, columns: tuple[Column, ...],
                 rows: Iterable[dict[str, Any]], generated_at: datetime,
                 source: str, note: str = "") -> int:
    sheet = workbook.create_sheet(name)
    sheet.sheet_view.showGridLines = False
    sheet.freeze_panes = "A7"
    sheet.sheet_properties.tabColor = "7B1F71" if name.startswith("01_") else "B98AB4"
    # В write-only режиме размеры должны быть заданы до первой append():
    # поздние изменения не попадают в XML листа и Excel показывает #######.
    sheet.row_dimensions[6].height = 42
    for index, column in enumerate(columns, 1):
        sheet.column_dimensions[get_column_letter(index)].width = min(70, column.width)
    _append_meta(sheet, title, generated_at, source, note)
    header_fill = PatternFill("solid", fgColor="7B1F71")
    border = Border(bottom=Side(style="thin", color="FFFFFF"))
    header = []
    for column in columns:
        cell = WriteOnlyCell(sheet, column.title)
        cell.font = Font(name="Arial", size=9, bold=True, color="FFFFFF")
        cell.fill = header_fill
        cell.border = border
        cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
        header.append(cell)
    sheet.append(header)
    count = 0
    for row in rows:
        cells = []
        for column in columns:
            value = row.get(column.key)
            if isinstance(value, Decimal):
                value = float(value)
            if isinstance(value, datetime) and value.tzinfo is not None:
                value = value.replace(tzinfo=None)
            if isinstance(value, bool):
                value = "Да" if value else "Нет"
            cell = WriteOnlyCell(sheet, value)
            cell.style = column.style
            cells.append(cell)
        sheet.append(cells)
        count += 1
    last_column = get_column_letter(len(columns))
    sheet.auto_filter.ref = f"A6:{last_column}{max(6, count + 6)}"
    return count


def build_report_xlsx(rows: list[dict[str, Any]], stock_rows: list[dict[str, Any]],
                      snapshot_at: datetime | None,
                      generated_at: datetime | None = None) -> bytes:
    generated_at = generated_at or datetime.now()
    report_date = snapshot_at.date() if snapshot_at else generated_at.date()
    items = [prepare_item(row, report_date) for row in rows]
    source = ("Источник: локальная PostgreSQL-база приложения OOUCR "
              "(app.discount_product и локальные справочники); "
              "срез " + (snapshot_at.strftime("%d.%m.%Y %H:%M") if snapshot_at else "не указан"))
    for item in items:
        item.setdefault("currency", "BYN")
    categories = _summary(items, ("category_1", "category_2", "category_3"))
    company_age = _summary(items, ("age_company_band",), age_key="days_company")
    discount_age = _summary(items, ("age_discount_band",))
    finance = []
    for values, group in _group(items, ("category_2",)):
        qty = sum(item["quantity"] for item in group)
        discounts = [item.get("discount_percent") for item in group if item.get("discount_percent") is not None]
        finance.append({"category_2": values[0], "quantity": qty,
                        "new_value": sum((_number(item.get("current_new_price")) or 0) * item["quantity"] for item in group),
                        "cost_value": sum(item["cost_value"] or 0 for item in group),
                        "discount_value": sum(item["discount_value"] or 0 for item in group),
                        "average_discount": mean(float(x) for x in discounts) if discounts else None,
                        "repricing_count": sum(int(item.get("repricing_count") or 0) for item in group),
                        "missing_cost": sum(item["quantity"] for item in group if item.get("accounting_cost") is None)})
    defects = _summary(items, ("defect_type", "functionality", "completeness", "admission"))
    placement = _summary(items, ("placement_class", "placement_type"))
    warehouses = []
    warehouse_groups: dict[str, list[dict[str, Any]]] = defaultdict(list)
    for row in stock_rows:
        warehouse_groups[_text(row.get("warehouse")) or "Склад не указан"].append(row)
    for warehouse, group in sorted(warehouse_groups.items()):
        balance = sum(int(row.get("balance") or 0) for row in group)
        warehouses.append({"warehouse": warehouse, "records": len(group), "quantity": balance,
                           "cost_value": sum((_number(row.get("accounting_cost")) or 0) * max(0, int(row.get("balance") or 0)) for row in group),
                           "discount_value": sum((_number(row.get("site_price")) or 0) * max(0, int(row.get("balance") or 0)) for row in group),
                           "negative": sum(1 for row in group if int(row.get("balance") or 0) < 0)})
    candidates = sorted((item for item in items if item["quantity"] > 0 and not item["exclude"]),
                        key=lambda item: (-item["priority_score"], item["product_id"]))
    quality = [{"product_id": item.get("product_id"), "product_name": item.get("product_name"),
                "issues": item["quality_issues"], "source_snapshot_at": item.get("source_snapshot_at")}
               for item in items if item["quality_issues"]]

    total_units = sum(item["quantity"] for item in items)
    checks = [
        f"единиц в реестре={total_units}, по категориям={sum(r['quantity'] for r in categories)}",
        f"по возрасту уценки={sum(r['quantity'] for r in discount_age)}",
        f"потенциальная выручка={sum(item['discount_value'] or 0 for item in items):.2f} BYN",
        f"дубли ID={len(items) - len({item['product_id'] for item in items})}",
        f"отрицательные складские строки={sum(1 for row in stock_rows if int(row.get('balance') or 0) < 0)}",
    ]
    check_note = "Проверки: " + "; ".join(checks) + ". " + HISTORY_NOTE

    workbook = Workbook(write_only=True)
    _styles(workbook)
    _write_sheet(workbook, "01_Реестр_товаров", "Реестр уценённых товаров",
                 REGISTRY_COLUMNS, items, generated_at, source,
                 f"Записей: {len(items)}; доступных единиц: {total_units}. Коды хранятся текстом.")
    summary_columns = tuple([Column("Категория 1", "category_1", 26), Column("Категория 2", "category_2", 30),
                             Column("Категория 3", "category_3", 30), Column("Записей", "records", 12, "integer"),
                             Column("Единиц", "quantity", 12, "integer"), Column("Доля", "share", 12, "percent"),
                             Column("Учётная стоимость", "cost_value", 18, "money"), Column("Потенциальная выручка", "discount_value", 20, "money"),
                             Column("Средний возраст уценки", "average_age", 18, "decimal1"), Column("Медианный возраст уценки", "median_age", 18, "decimal1")])
    _write_sheet(workbook, "02_Категории", "Остаток по товарным категориям", summary_columns,
                 categories, generated_at, source, "Количество рассчитано по доступному остатку, а не по числу строк.")
    age_columns = (Column("Возрастной интервал", "age_company_band", 22), Column("Записей", "records", 12, "integer"),
                   Column("Единиц", "quantity", 12, "integer"), Column("Доля", "share", 12, "percent"),
                   Column("Учётная стоимость", "cost_value", 18, "money"), Column("Потенциальная выручка", "discount_value", 20, "money"),
                   Column("Средний возраст", "average_age", 16, "decimal1"), Column("Медианный возраст", "median_age", 16, "decimal1"))
    _write_sheet(workbook, "03_Возраст_в_компании", "Возраст товара в компании", age_columns,
                 company_age, generated_at, source, "Дата первого поступления в компанию в доступном источнике отсутствует.")
    age_discount_columns = (Column("Возрастной интервал", "age_discount_band", 22), *age_columns[1:])
    _write_sheet(workbook, "04_Возраст_уценки", "Возраст товара в уценке", age_discount_columns,
                 discount_age, generated_at, source, "Возраст рассчитан от даты создания карточки уценки в 1С.")
    finance_columns = (Column("Категория", "category_2", 30), Column("Единиц", "quantity", 12, "integer"),
                       Column("Стоимость по ценам нового товара", "new_value", 22, "money"), Column("Учётная стоимость", "cost_value", 18, "money"),
                       Column("Потенциальная выручка", "discount_value", 20, "money"), Column("Средняя скидка, %", "average_discount", 16, "percent_number"),
                       Column("Переоценок в доступной истории", "repricing_count", 20, "integer"), Column("Единиц без учётной стоимости", "missing_cost", 20, "integer"))
    _write_sheet(workbook, "05_Финансы", "Финансовая структура остатка", finance_columns,
                 finance, generated_at, source, "Прибыль не рассчитывается. Полная история переоценок в текущем локальном срезе отсутствует.")
    defect_columns = (Column("Тип дефекта", "defect_type", 34, "wrap"), Column("Работоспособность", "functionality", 34, "wrap"),
                      Column("Комплектность", "completeness", 24), Column("Допуск к продаже", "admission", 34, "wrap"),
                      Column("Записей", "records", 12, "integer"), Column("Единиц", "quantity", 12, "integer"),
                      Column("Доля", "share", 12, "percent"), Column("Учётная стоимость", "cost_value", 18, "money"),
                      Column("Потенциальная выручка", "discount_value", 20, "money"))
    _write_sheet(workbook, "06_Дефекты", "Состояние и дефекты", defect_columns,
                 defects, generated_at, source, "Классификация выполнена по тексту описания; исходное описание сохранено в реестре.")
    placement_columns = (Column("Класс", "placement_class", 12), Column("Тип размещения", "placement_type", 38, "wrap"),
                         Column("Записей", "records", 12, "integer"), Column("Единиц", "quantity", 12, "integer"),
                         Column("Доля", "share", 12, "percent"), Column("Учётная стоимость", "cost_value", 18, "money"),
                         Column("Потенциальная выручка", "discount_value", 20, "money"), Column("Средний возраст уценки", "average_age", 18, "decimal1"))
    _write_sheet(workbook, "07_Размещение", "Классы размещения и подготовка", placement_columns,
                 placement, generated_at, source, "Класс E означает преимущественно складскую продажу; физическая категория сохранена в реестре.")
    warehouse_columns = (Column("Склад", "warehouse", 36), Column("Складских строк", "records", 16, "integer"),
                         Column("Доступный остаток", "quantity", 16, "integer"), Column("Учётная стоимость", "cost_value", 18, "money"),
                         Column("Потенциальная выручка", "discount_value", 20, "money"), Column("Отрицательных строк", "negative", 18, "integer"))
    _write_sheet(workbook, "08_Склады", "Остаток по складам", warehouse_columns,
                 warehouses, generated_at, source, "Перемещения не создают новые карточки: реестр дедуплицирован по product_id.")
    candidate_columns = tuple(column for column in REGISTRY_COLUMNS if column.key in {
        "record_type", "product_id", "origin_product_id", "product_name", "brand", "category_2", "category_3",
        "warehouses", "quantity", "site_price", "current_new_price", "discount_percent", "description",
        "admission", "placement_class", "placement_type", "prep_minutes", "can_display", "needs_check",
        "assembly", "warehouse_sale", "exclude", "priority_score", "priority", "priority_reason", "sales_history"})
    _write_sheet(workbook, "09_Кандидаты_в_магазин", "Предварительные кандидаты в магазин", candidate_columns,
                 candidates, generated_at, source,
                 "Рейтинг не прогнозирует спрос и не выбирает ровно 400 единиц. Большой возраст даёт ограниченную прибавку к баллу.")
    quality_columns = (Column("ID экземпляра", "product_id", 26, "text"), Column("Наименование", "product_name", 42, "wrap"),
                       Column("Проблемы качества данных", "issues", 80, "wrap"), Column("Дата среза", "source_snapshot_at", 20, "datetime"))
    _write_sheet(workbook, "10_Качество_данных", "Качество данных и проверки", quality_columns,
                 quality, generated_at, source, check_note)

    output = BytesIO()
    workbook.save(output)
    return output.getvalue()

