from datetime import date, datetime
from decimal import Decimal
from io import BytesIO
from pathlib import Path

from openpyxl import load_workbook

from timesheet import discountreport


ROOT = Path(__file__).resolve().parents[2]
WEB = (ROOT / "engine" / "timesheet" / "webapp.py").read_text(encoding="utf-8")
PAGE = (ROOT / "engine" / "timesheet" / "templates" / "discount_products.html").read_text(encoding="utf-8")
SQL = (ROOT / "sql" / "148_discount_assortment_report.sql").read_text(encoding="utf-8")


def row(**changes):
    value = {
        "product_id": "10142911-расп-2585436", "origin_product_id": 10142911,
        "product_code": "00-10000001", "origin_product_code": "00-00000001",
        "product_category_id": 1, "product_type_id": 2,
        "product_name": "Робот-пылесос Test M1", "brand": "Test", "model": "M1",
        "product_url": "https://www.21vek.by/test.html", "grade": "B",
        "date_of_creation": date(2026, 8, 1), "last_price_change": date(2026, 11, 12),
        "description": "Исправен, царапины на корпусе, полный комплект",
        "site_price": Decimal("750.00"), "discount_percent": Decimal("25"),
        "has_price": True, "category_1": "Товары", "category_2": "Техника для дома",
        "category_3": "Пылесосы", "category_4": "Роботы-пылесосы",
        "depth_cm": Decimal("35"), "width_cm": Decimal("35"),
        "height_cm": Decimal("10"), "weight_kg": Decimal("4.5"),
        "current_new_price": Decimal("1000"), "accounting_cost": Decimal("600"),
        "discount_set_date": date(2026, 11, 1), "condition_type": "new",
        "repricing_count": 3, "history_available_from": date(2026, 7, 18),
        "last_stock_change_date": date(2026, 10, 20), "available_quantity": 1,
        "source_snapshot_at": datetime(2026, 11, 12, 22, 3, 12),
        "warehouses": "Таборы (wms)", "warehouse_count": 1,
    }
    value.update(changes)
    return value


def test_report_has_exact_required_sheets_and_reconciled_counts():
    rows = [row(), row(product_id="8552339-расп-2397020", available_quantity=5,
                       warehouses="ПВЗ Кобрин; Таборы (wms)")]
    stocks = [
        {"product_id": rows[0]["product_id"], "warehouse": "Таборы (wms)", "balance": 1,
         "site_price": Decimal("750"), "accounting_cost": Decimal("600")},
        {"product_id": rows[1]["product_id"], "warehouse": "ПВЗ Кобрин", "balance": 1,
         "site_price": Decimal("750"), "accounting_cost": Decimal("600")},
        {"product_id": rows[1]["product_id"], "warehouse": "Таборы (wms)", "balance": 4,
         "site_price": Decimal("750"), "accounting_cost": Decimal("600")},
    ]
    payload = discountreport.build_report_xlsx(
        rows, stocks, datetime(2026, 11, 12, 22, 3, 12),
        generated_at=datetime(2026, 11, 13, 9, 0))
    book = load_workbook(BytesIO(payload), read_only=False, data_only=False)
    assert book.sheetnames == [
        "01_Реестр_товаров", "02_Категории", "03_Возраст_в_компании",
        "04_Возраст_уценки", "05_Финансы", "06_Дефекты", "07_Размещение",
        "08_Склады", "09_Кандидаты_в_магазин", "10_Качество_данных",
    ]
    registry = book["01_Реестр_товаров"]
    assert registry.freeze_panes == "A7"
    headers = [cell.value for cell in registry[6]]
    assert "Проблемы качества данных" in headers
    assert registry.cell(8, headers.index("Тип записи") + 1).value == "Агрегированная запись"
    categories = book["02_Категории"]
    assert sum(categories.cell(r, 5).value for r in range(7, categories.max_row + 1)) == 6
    assert "единиц в реестре=6" in book["10_Качество_данных"]["A4"].value


def test_unknown_or_unsafe_items_are_not_ready_for_display():
    item = discountreport.prepare_item(
        row(description="Не включается, поврежден кабель", has_price=False),
        date(2026, 11, 12))
    assert item["placement_class"] == "X"
    assert item["exclude"] is True
    assert item["can_display"] is False
    assert item["priority"] == "Не включать"


def test_route_button_and_stock_migration_exist():
    assert '@app.get("/defects/discounts/assortment.xlsx")' in WEB
    assert 'href="/defects/discounts/assortment.xlsx"' in PAGE
    assert "CREATE TABLE IF NOT EXISTS discount_product_stock" in SQL
    assert "assortment_enriched" in SQL

