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

from openpyxl import load_workbook
import pytest

from timesheet import discountdb


ROOT = Path(__file__).resolve().parents[2]
WEB = (ROOT / "engine" / "timesheet" / "webapp.py").read_text(encoding="utf-8")
DB = (ROOT / "engine" / "timesheet" / "discountdb.py").read_text(encoding="utf-8")
NAV = (ROOT / "engine" / "timesheet" / "templates" / "_defect_nav.html").read_text(encoding="utf-8")
PAGE = (ROOT / "engine" / "timesheet" / "templates" / "discount_products.html").read_text(encoding="utf-8")
CSS = (ROOT / "engine" / "timesheet" / "static" / "app.css").read_text(encoding="utf-8")
SQL = (ROOT / "sql" / "139_discount_stock.sql").read_text(encoding="utf-8")


def test_discount_tables_keep_active_and_archived_cards():
    assert "CREATE TABLE IF NOT EXISTS discount_product" in SQL
    assert "CREATE TABLE IF NOT EXISTS discount_sync" in SQL
    assert "is_active" in SQL and "archived_at" in SQL
    assert "GRANT SELECT, INSERT, UPDATE, DELETE ON discount_product, discount_sync" in SQL


def test_source_is_the_latest_read_only_discount_snapshot():
    assert "FROM triovist.products_broken" in DB
    assert "created_at = (SELECT max(created_at) FROM triovist.products_broken)" in DB
    assert "product_id LIKE '%расп%'" in DB
    assert "product_id NOT ILIKE '%некомплект%'" in DB
    assert "Разрешены только запросы чтения" in DB or "разрешены только запросы чтения" in DB


def test_current_snapshot_merges_identical_cross_warehouse_rows(monkeypatch):
    columns = ["product_id", "origin_product_id_text", "product_code",
               "origin_product_code", "product_name", "brand", "grade",
               "date_of_creation_text", "description", "site_price",
               "broken_percent", "has_price", "category_2", "category_4",
               "snapshot_at_text"]
    row = ["100-расп-1", "100", "00-1", "00-0", "Товар", "Бренд", "A",
           "2026-10-01", "Описание", 10, 20, 1, "Категория", "Вид",
           "2026-10-27T21:51:22+03:00"]

    class Client:
        def query(self, _sql):
            return columns, [row, list(row)]

    monkeypatch.setattr(discountdb, "_McpClient", Client)
    snapshot = discountdb.fetch_current_products()
    assert len(snapshot["rows"]) == 1


def test_current_snapshot_rejects_conflicting_product_rows(monkeypatch):
    columns = ["product_id", "origin_product_id_text", "product_code",
               "origin_product_code", "product_name", "brand", "grade",
               "date_of_creation_text", "description", "site_price",
               "broken_percent", "has_price", "category_2", "category_4",
               "snapshot_at_text"]
    first = ["100-расп-1", "100", "00-1", "00-0", "Товар", "Бренд", "A",
             "2026-10-01", "Описание", 10, 20, 1, "Категория", "Вид",
             "2026-10-27T21:51:22+03:00"]
    second = list(first)
    second[10] = 25

    class Client:
        def query(self, _sql):
            return columns, [first, second]

    monkeypatch.setattr(discountdb, "_McpClient", Client)
    with pytest.raises(RuntimeError, match="противоречащие строки"):
        discountdb.fetch_current_products()


def test_sync_archives_missing_and_restores_returned_products():
    assert "SET is_active = false" in DB
    assert "archived_at = coalesce" in DB
    assert "is_active = true, archived_at = NULL" in DB
    assert "Текущий срез уценки пуст; архивирование отменено" in DB


def test_discount_tab_routes_and_controls_exist():
    assert 'href="/defects/discounts"' in NAV
    assert '@app.get("/defects/discounts"' in WEB
    assert '@app.post("/defects/discounts/sync")' in WEB
    assert 'action="/defects/discounts/sync"' in PAGE
    assert PAGE.count('class="pagination') >= 2
    assert "Что означает архив" in PAGE
    assert 'name="manufacturer"' in PAGE
    assert 'href="/defects/discounts/export.xlsx?{{ search_query }}"' in PAGE
    assert '@app.get("/defects/discounts/export.xlsx")' in WEB


def test_discount_page_is_compact_on_desktop_and_mobile_friendly():
    assert "{% block body_class %}discount-page{% endblock %}" in PAGE
    assert "body.discount-page" in CSS
    assert "height: 100vh; overflow: hidden" in CSS
    assert ".discount-table-wrap" in CSS
    assert ".discount-table td::before" in CSS


def test_manufacturer_and_category_are_exact_filters():
    assert 'clauses.append("p.category_2 = %s")' in DB
    assert 'clauses.append("p.brand = %s")' in DB
    assert '"discount_brands": brands' in DB


def test_excel_export_has_filters_typed_values_and_frozen_header():
    snapshot = datetime(2026, 10, 26, 21, 52, 20)
    rows = [{
        "is_active": True,
        "product_name": "Робот-пылесос",
        "product_id": "10000587-расп-2547899",
        "origin_product_id": 10000587,
        "product_code": "00-09444569",
        "origin_product_code": "00-08230217",
        "brand": "Trouver",
        "category_2": "Техника для дома",
        "category_4": "Робот-пылесос",
        "grade": "B",
        "date_of_creation": date(2026, 9, 16),
        "age_days": 40,
        "site_price": Decimal("1341.75"),
        "discount_percent": Decimal("25"),
        "has_price": True,
        "description": "Потёртости",
        "source_snapshot_at": snapshot,
        "archived_at": None,
    }]
    payload = discountdb.build_export_xlsx(
        rows, snapshot, "текущий остаток; производитель: Trouver")
    workbook = load_workbook(BytesIO(payload), read_only=False, data_only=False)
    sheet = workbook["Уценка"]
    assert sheet["A1"].value == "Уценка"
    assert sheet.freeze_panes == "A7"
    assert sheet.auto_filter.ref == "A6:R7"
    assert sheet["G6"].value == "Производитель"
    assert sheet["H6"].value == "Категория товара"
    assert sheet["G7"].value == "Trouver"
    assert sheet["K7"].value == datetime(2026, 9, 16)
    assert sheet["M7"].value == 1341.75
    assert sheet["N7"].value == 25
