"""Excel: отчёт «Потребление корма» (3 листа)."""

from __future__ import annotations

import io

from flask import Flask
from openpyxl import Workbook
from openpyxl.styles import Font
from openpyxl.utils import get_column_letter

from app.services.feed_accounting.consumption_report_builder import build_consumption_report


def _header_font() -> Font:
    return Font(bold=True)


def build_consumption_xlsx(app: Flask, date_from: str, date_to: str) -> bytes:
    report = build_consumption_report(app, date_from, date_to)
    wb = Workbook()

    ws_sum = wb.active
    ws_sum.title = "Сводка"
    ws_sum.append([f"Потребление корма · {date_from} — {date_to}"])
    ws_sum.append([])
    sum_headers = [
        "Компонент",
        "Осталось, кг",
        "Расход за период (факт), кг",
        "Расход /24ч (план), кг",
        "Осталось дней",
        "Поступление, кг",
        "Всего на складе, кг",
    ]
    ws_sum.append(sum_headers)
    header_row = ws_sum.max_row
    for col in range(1, len(sum_headers) + 1):
        ws_sum.cell(row=header_row, column=col).font = _header_font()
    for row in report.summary:
        ws_sum.append(
            [
                row["name"],
                row["remaining_kg"],
                row["consumed_kg"],
                row["planned_per_day_kg"] or "",
                row["days_left"] if row["days_left"] is not None else "",
                row["inflow_kg"],
                row["total_kg"],
            ]
        )
    for col in range(1, len(sum_headers) + 1):
        ws_sum.column_dimensions[get_column_letter(col)].width = 18

    ws_det = wb.create_sheet("Детализация")
    det_headers = [
        "Дата/время начала",
        "Дата/время окончания",
        "Рецепт",
        "Кормораздатчик",
        "Компонент",
        "Целевой вес, кг",
        "Фактический вес, кг",
        "Перегруз, кг",
        "Порядок загрузки",
    ]
    ws_det.append(det_headers)
    for col in range(1, len(det_headers) + 1):
        ws_det.cell(row=1, column=col).font = _header_font()
    for row in report.detail:
        ws_det.append(
            [
                row["start"],
                row["end"],
                row["recipe"],
                row["dispenser"],
                row["component"],
                row["target_kg"],
                row["actual_kg"],
                row["overload_kg"],
                row["loading_order"],
            ]
        )
    for col in range(1, len(det_headers) + 1):
        ws_det.column_dimensions[get_column_letter(col)].width = 16

    ws_daily = wb.create_sheet("Расход по дням")
    day_headers = ["Компонент"] + [d.strftime("%d.%m.%Y") for d in report.days] + ["Итого за период"]
    ws_daily.append(day_headers)
    for col in range(1, len(day_headers) + 1):
        ws_daily.cell(row=1, column=col).font = _header_font()
    for row in report.daily:
        values = [row["name"]]
        for d in report.days:
            v = row["days"].get(d, 0)
            values.append(v if v else 0)
        values.append(row["total_kg"])
        ws_daily.append(values)
    ws_daily.column_dimensions["A"].width = 22
    for col in range(2, len(day_headers) + 1):
        ws_daily.column_dimensions[get_column_letter(col)].width = 11

    buf = io.BytesIO()
    wb.save(buf)
    return buf.getvalue()