#!/usr/bin/env python3
"""
build_excel.py — собирает Excel из insights.json со всеми листами:
  1. Сводка
  2. Топ просадок
  3. Топ ростов
  4. По брендам
  5. OOS-кандидаты
  6. Воронка-графики (топ-10 просадок)
  7. Методология
"""
from __future__ import annotations

import argparse
import json
import sys
from datetime import date, datetime
from typing import Any

from openpyxl import Workbook
from openpyxl.chart import LineChart, Reference
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.utils import get_column_letter
from openpyxl.formatting.rule import ColorScaleRule, CellIsRule


# ───── Стилевые константы ───────────────────────────────────────────────
HEADER_FILL = PatternFill("solid", fgColor="312e81")  # indigo-900
HEADER_FONT = Font(name="Inter", size=11, bold=True, color="FFFFFF")
SECTION_FILL = PatternFill("solid", fgColor="1f2937")
SECTION_FONT = Font(name="Inter", size=12, bold=True, color="FFFFFF")
TITLE_FONT = Font(name="Inter", size=16, bold=True, color="111827")
CENTER = Alignment(horizontal="center", vertical="center", wrap_text=True)
LEFT = Alignment(horizontal="left", vertical="center", wrap_text=True)
RIGHT = Alignment(horizontal="right", vertical="center")
THIN = Side(style="thin", color="d1d5db")
BORDER = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)

RED_FILL = PatternFill("solid", fgColor="fee2e2")
GREEN_FILL = PatternFill("solid", fgColor="dcfce7")
YELLOW_FILL = PatternFill("solid", fgColor="fef3c7")


def fmt_num(v: Any, digits: int = 0) -> Any:
    if v is None:
        return "—"
    try:
        if digits == 0:
            return int(round(v))
        return round(v, digits)
    except Exception:
        return v


def fmt_pct(v: Any) -> str:
    if v is None:
        return "—"
    try:
        sign = "+" if v >= 0 else ""
        return f"{sign}{round(v, 1)}%"
    except Exception:
        return str(v)


def style_header(ws, row: int, cols: int) -> None:
    for col in range(1, cols + 1):
        c = ws.cell(row=row, column=col)
        c.fill = HEADER_FILL
        c.font = HEADER_FONT
        c.alignment = CENTER
        c.border = BORDER


def sheet_summary(wb: Workbook, insights: dict[str, Any]) -> None:
    ws = wb.create_sheet("Сводка")
    target = insights["target_date"]
    ws["A1"] = f"Утренний отчёт WB — анализ за {target}"
    ws["A1"].font = TITLE_FONT
    ws.merge_cells("A1:E1")

    ov = insights.get("account_overview", {})
    deltas = ov.get("deltas", {})

    headers = ["Метрика", "Вчера", "Среднее за 7 дней", "Δ абс.", "Δ %"]
    for i, h in enumerate(headers, start=1):
        ws.cell(row=3, column=i, value=h)
    style_header(ws, 3, len(headers))

    rows = [
        ("Показы карточки", "openCount", "pct", 0),
        ("Добавлено в корзину", "cartCount", "pct", 0),
        ("CR показ → корзина, %", "addToCartConversion", "pp", 2),
        ("Заказы (шт.)", "orderCount", "pct", 0),
        ("CR корзина → заказ, %", "cartToOrderConversion", "pp", 2),
        ("Выкупы (шт.)", "buyoutCount", "pct", 0),
        ("Выкуп, %", "buyoutPercent", "pp", 2),
        ("В избранное", "addToWishlistCount", "pct", 0),
        ("Выручка, ₽", "orderSum", "pct", 0),
    ]
    row = 4
    today = ov.get("today", {})
    base = ov.get("baseline", {})
    for label, key, kind, digits in rows:
        ws.cell(row=row, column=1, value=label).alignment = LEFT
        ws.cell(row=row, column=2, value=fmt_num(today.get(key), digits)).alignment = RIGHT
        ws.cell(row=row, column=3, value=fmt_num(base.get(key), digits)).alignment = RIGHT
        d = deltas.get(key, {})
        abs_v = (today.get(key, 0) or 0) - (base.get(key, 0) or 0)
        ws.cell(row=row, column=4, value=fmt_num(abs_v, digits)).alignment = RIGHT
        if kind == "pct":
            ws.cell(row=row, column=5, value=fmt_pct(d.get("pct"))).alignment = RIGHT
        else:
            ws.cell(row=row, column=5, value=fmt_pct(d.get("pp")) + " п.п." if d.get("pp") is not None else "—").alignment = RIGHT
        # Подсветка
        cell = ws.cell(row=row, column=5)
        val = d.get("pct") if kind == "pct" else d.get("pp")
        if val is not None:
            if (kind == "pct" and val <= -20) or (kind == "pp" and val <= -5):
                cell.fill = RED_FILL
            elif (kind == "pct" and val >= 20) or (kind == "pp" and val >= 5):
                cell.fill = GREEN_FILL
        for col in range(1, 6):
            ws.cell(row=row, column=col).border = BORDER
        row += 1

    ws.cell(row=row + 1, column=1,
            value=f"SKU в выборке: {ov.get('counted_skus', 0)}")
    ws.cell(row=row + 2, column=1,
            value=f"Просадок: {insights.get('totals', {}).get('drops', 0)} · "
                  f"Ростов: {insights.get('totals', {}).get('growths', 0)} · "
                  f"OOS-кандидатов: {insights.get('totals', {}).get('oos_candidates', 0)}")

    ws.column_dimensions["A"].width = 32
    for col in "BCDE":
        ws.column_dimensions[col].width = 20


SKU_COLS = [
    ("Бренд", "brand"),
    ("nmID", "nmID"),
    ("Название", "title"),
    ("Категория", "subjectName"),
    ("Выручка вчера ₽", "today.orderSum"),
    ("Выручка бейз. ₽", "baseline.orderSum"),
    ("Δ выручки ₽", "delta.orderSum.abs"),
    ("Δ выручки %", "delta.orderSum.pct"),
    ("Заказы вчера", "today.orderCount"),
    ("Заказы бейз.", "baseline.orderCount"),
    ("Δ заказов %", "delta.orderCount.pct"),
    ("Показы вчера", "today.openCount"),
    ("Δ показов %", "delta.openCount.pct"),
    ("Корзина вчера", "today.cartCount"),
    ("Δ корзина %", "delta.cartCount.pct"),
    ("CR показ→корзина", "today.addToCartConversion"),
    ("Δ CR показ→корз п.п.", "delta.addToCartConversion.pp"),
    ("CR корз→заказ", "today.cartToOrderConversion"),
    ("Δ CR корз→зак п.п.", "delta.cartToOrderConversion.pp"),
    ("Выкуп %", "today.buyoutPercent"),
    ("Избранное вчера", "today.addToWishlistCount"),
    ("Первый сломанный этап", "first_break"),
    ("OOS", "enrichment.stocks.flag"),
    ("Склад выпал", "enrichment.stocks.dropoff_warehouse"),
    ("Остатки сегодня", "enrichment.stocks.total_today"),
    ("РК", "enrichment.ads.flag"),
    ("Расход РК вчера ₽", "enrichment.ads.spent_today"),
    ("Расход РК среднее ₽", "enrichment.ads.spent_avg"),
    ("Цена СПП ₽", "enrichment.price.spp_price"),
    ("Δ цены %", "enrichment.price.delta_pct"),
    ("Свежий негатив", "enrichment.reviews.flag"),
    ("WB ссылка", "_wb_url"),
]


def dig(s: dict[str, Any], path: str) -> Any:
    cur: Any = s
    for k in path.split("."):
        if isinstance(cur, dict):
            cur = cur.get(k)
        else:
            return None
    return cur


def build_sku_row(s: dict[str, Any]) -> list[Any]:
    card = s.get("card", {})
    deltas = s.get("deltas", {})
    today = s.get("today", {})
    base = s.get("baseline", {})
    enrich = s.get("enrichment", {})

    def g(path: str) -> Any:
        section, _, rest = path.partition(".")
        if section == "brand":
            return card.get("brand")
        if section == "nmID":
            return s.get("nmID")
        if section == "title":
            return card.get("title")
        if section == "subjectName":
            return card.get("subjectName")
        if section == "first_break":
            return s.get("first_break")
        if section == "today":
            return fmt_num(today.get(rest), 1 if rest in ("addToCartConversion", "cartToOrderConversion", "buyoutPercent") else 0)
        if section == "baseline":
            return fmt_num(base.get(rest), 1 if rest in ("addToCartConversion", "cartToOrderConversion", "buyoutPercent") else 0)
        if section == "delta":
            metric, _, sub = rest.partition(".")
            d = deltas.get(metric, {})
            if sub == "abs":
                return fmt_num(d.get("abs"), 0)
            if sub == "pct":
                return fmt_pct(d.get("pct"))
            if sub == "pp":
                return fmt_pct(d.get("pp")) + " п.п." if d.get("pp") is not None else "—"
        if section == "enrichment":
            return dig(enrich, rest)
        if section == "_wb_url":
            nm = s.get("nmID")
            return f"https://www.wildberries.ru/catalog/{nm}/detail.aspx" if nm else None
        return None

    return [g(path) for _, path in SKU_COLS]


def sheet_sku_list(wb: Workbook, name: str, skus: list[dict[str, Any]]) -> None:
    ws = wb.create_sheet(name)
    headers = [c[0] for c in SKU_COLS]
    for i, h in enumerate(headers, start=1):
        ws.cell(row=1, column=i, value=h)
    style_header(ws, 1, len(headers))
    for r, s in enumerate(skus, start=2):
        row = build_sku_row(s)
        for c, v in enumerate(row, start=1):
            cell = ws.cell(row=r, column=c, value=v)
            cell.border = BORDER
            cell.alignment = LEFT if c in (1, 3, 4, 23, 24, 25, 27, 32) else RIGHT
            # Подсветка флагов
            col_name = SKU_COLS[c - 1][0]
            if col_name == "OOS" and v in ("OOS_full", "OOS_warehouse_dropoff"):
                cell.fill = RED_FILL
            elif col_name == "РК" and v in ("RK_off", "RK_dropped"):
                cell.fill = RED_FILL
            elif col_name == "РК" and v == "RK_boosted":
                cell.fill = YELLOW_FILL
            elif col_name == "Свежий негатив" and v == "fresh_negative":
                cell.fill = RED_FILL
            elif col_name == "Δ цены %":
                try:
                    if v is not None and abs(float(str(v).replace("%", "").replace("+", ""))) >= 3:
                        cell.fill = YELLOW_FILL
                except Exception:
                    pass

    # Color-scale на Δ выручки %
    if len(skus) >= 1:
        last_row = 1 + len(skus)
        ws.conditional_formatting.add(
            f"H2:H{last_row}",
            ColorScaleRule(
                start_type="num", start_value=-100, start_color="b91c1c",
                mid_type="num", mid_value=0, mid_color="ffffff",
                end_type="num", end_value=100, end_color="15803d",
            ),
        )

    # Ширины
    widths = [16, 12, 38, 18, 14, 14, 12, 12, 12, 12, 12, 12, 12, 12, 12, 14, 14,
              14, 14, 10, 12, 22, 14, 22, 14, 14, 16, 18, 12, 12, 16, 38]
    for i, w in enumerate(widths, start=1):
        ws.column_dimensions[get_column_letter(i)].width = w
    ws.freeze_panes = "D2"


def sheet_brands(wb: Workbook, drops: list[dict[str, Any]], growths: list[dict[str, Any]]) -> None:
    ws = wb.create_sheet("По брендам")
    # Агрегация
    agg: dict[str, dict[str, float]] = {}
    for s in drops + growths:
        brand = (s.get("card") or {}).get("brand") or "—"
        agg.setdefault(brand, {"drops": 0, "growths": 0, "today_rev": 0, "base_rev": 0})
        if s["category"] in ("drop", "mixed"):
            agg[brand]["drops"] += 1
        if s["category"] in ("growth", "mixed"):
            agg[brand]["growths"] += 1
        agg[brand]["today_rev"] += (s.get("today") or {}).get("orderSum", 0) or 0
        agg[brand]["base_rev"] += (s.get("baseline") or {}).get("orderSum", 0) or 0

    headers = ["Бренд", "SKU в просадках", "SKU в ростах", "Выручка вчера, ₽",
               "Выручка бейзлайн, ₽", "Δ выручки ₽", "Δ выручки %"]
    for i, h in enumerate(headers, start=1):
        ws.cell(row=1, column=i, value=h)
    style_header(ws, 1, len(headers))

    sorted_brands = sorted(agg.items(), key=lambda kv: -(kv[1]["base_rev"] - kv[1]["today_rev"]))
    for r, (brand, a) in enumerate(sorted_brands, start=2):
        delta_abs = a["today_rev"] - a["base_rev"]
        delta_pct_v = (delta_abs / a["base_rev"] * 100) if a["base_rev"] else None
        for c, v in enumerate(
            [brand, a["drops"], a["growths"], int(a["today_rev"]),
             int(a["base_rev"]), int(delta_abs), fmt_pct(delta_pct_v)],
            start=1,
        ):
            cell = ws.cell(row=r, column=c, value=v)
            cell.border = BORDER
            cell.alignment = LEFT if c == 1 else RIGHT
    ws.column_dimensions["A"].width = 24
    for col in "BCDEFG":
        ws.column_dimensions[col].width = 18


def sheet_methodology(wb: Workbook, insights: dict[str, Any]) -> None:
    ws = wb.create_sheet("Методология")
    target = insights["target_date"]
    base_dates = insights["baseline_dates"]
    settings = insights.get("settings_used", {})
    totals = insights.get("totals", {})

    lines = [
        ("Анализируемая дата", target),
        ("Бейзлайн (даты)", ", ".join(base_dates)),
        ("Акционные дни (исключены)", ", ".join(settings.get("promo_dates", []) or ["—"])),
        ("Порог просадки, %", settings.get("drop_pct")),
        ("Порог роста, %", settings.get("growth_pct")),
        ("Мин. заказов/день в бейзлайне", settings.get("min_baseline_orders_per_day")),
        ("Мин. выручки/день в бейзлайне, ₽", settings.get("min_baseline_revenue_per_day_rub")),
        ("Правило «0 заказов вчера»", settings.get("zero_orders_rule")),
        ("Всего просадок", totals.get("drops")),
        ("Всего ростов", totals.get("growths")),
        ("OOS-кандидатов", totals.get("oos_candidates")),
        ("SKU «ок»", totals.get("ok")),
        ("Пропущено новых (<7 дней)", totals.get("skipped_new")),
        ("Пропущено за низкий объём", totals.get("skipped_low_volume")),
        ("Сгенерировано", datetime.now().isoformat(timespec="seconds")),
    ]
    for r, (k, v) in enumerate(lines, start=1):
        ws.cell(row=r, column=1, value=k).font = Font(bold=True)
        ws.cell(row=r, column=2, value=str(v) if v is not None else "—")
        ws.cell(row=r, column=1).alignment = LEFT
        ws.cell(row=r, column=2).alignment = LEFT
    ws.column_dimensions["A"].width = 38
    ws.column_dimensions["B"].width = 64

    # Скипнутые SKU
    skipped = insights.get("skipped", [])
    if skipped:
        ws.cell(row=len(lines) + 3, column=1, value="Список пропущенных SKU").font = Font(bold=True)
        ws.cell(row=len(lines) + 4, column=1, value="nmID")
        ws.cell(row=len(lines) + 4, column=2, value="Название")
        ws.cell(row=len(lines) + 4, column=3, value="Причина")
        style_header(ws, len(lines) + 4, 3)
        for i, s in enumerate(skipped, start=len(lines) + 5):
            ws.cell(row=i, column=1, value=s.get("nmID"))
            ws.cell(row=i, column=2, value=(s.get("card") or {}).get("title"))
            ws.cell(row=i, column=3, value=s.get("reason"))


def sheet_charts(wb: Workbook, insights: dict[str, Any], funnel_raw: dict[str, Any] | None) -> None:
    """Топ-10 просадок: мини-графики воронки за 14 дней."""
    if not funnel_raw:
        return
    drops = insights.get("drops", [])[:10]
    if not drops:
        return
    ws = wb.create_sheet("Воронка-графики")
    ws.cell(row=1, column=1, value="Графики воронки топ-10 просадок (за 14 дней)").font = TITLE_FONT
    ws.merge_cells("A1:H1")

    row = 3
    for s in drops:
        nm = s["nmID"]
        dates = sorted((funnel_raw.get("by_nm", {}).get(str(nm), {}) or {}).get("dates", {}).items())[-14:]
        if not dates:
            continue
        ws.cell(row=row, column=1, value=f"{(s.get('card') or {}).get('title', '')} (nm {nm})").font = Font(bold=True)
        row += 1
        ws.cell(row=row, column=1, value="Дата")
        ws.cell(row=row, column=2, value="Показы")
        ws.cell(row=row, column=3, value="Корзина")
        ws.cell(row=row, column=4, value="Заказы")
        ws.cell(row=row, column=5, value="Выкупы")
        style_header(ws, row, 5)
        start_data = row + 1
        for d_iso, d_data in dates:
            row += 1
            ws.cell(row=row, column=1, value=d_iso)
            ws.cell(row=row, column=2, value=d_data.get("openCount", 0))
            ws.cell(row=row, column=3, value=d_data.get("cartCount", 0))
            ws.cell(row=row, column=4, value=d_data.get("orderCount", 0))
            ws.cell(row=row, column=5, value=d_data.get("buyoutCount", 0))
        # Chart
        chart = LineChart()
        chart.title = "Воронка"
        chart.height = 6
        chart.width = 14
        data_ref = Reference(ws, min_col=2, min_row=start_data - 1, max_col=5, max_row=row)
        cats_ref = Reference(ws, min_col=1, min_row=start_data, max_row=row)
        chart.add_data(data_ref, titles_from_data=True)
        chart.set_categories(cats_ref)
        ws.add_chart(chart, f"G{start_data - 1}")
        row += 3


def main() -> int:
    p = argparse.ArgumentParser()
    p.add_argument("--insights", required=True)
    p.add_argument("--funnel", required=False, help="Optional: path to funnel_raw.json for charts")
    p.add_argument("--output", required=True)
    args = p.parse_args()

    with open(args.insights, encoding="utf-8") as f:
        insights = json.load(f)
    funnel_raw = None
    if args.funnel:
        try:
            with open(args.funnel, encoding="utf-8") as f:
                funnel_raw = json.load(f)
        except Exception as e:
            print(f"[excel] funnel load failed: {e}", file=sys.stderr)

    wb = Workbook()
    # Удалить дефолтный лист
    wb.remove(wb.active)

    sheet_summary(wb, insights)
    sheet_sku_list(wb, "Топ просадок", insights.get("drops", []))
    sheet_sku_list(wb, "Топ ростов", insights.get("growths", []))
    sheet_brands(wb, insights.get("drops", []), insights.get("growths", []))
    sheet_sku_list(wb, "OOS-кандидаты", insights.get("oos_candidates", []))
    sheet_charts(wb, insights, funnel_raw)
    sheet_methodology(wb, insights)

    wb.save(args.output)
    print(f"[excel] saved {args.output}", file=sys.stderr)
    return 0


if __name__ == "__main__":
    sys.exit(main())