import csv
from io import BytesIO, StringIO
from pathlib import Path

import httpx
from openpyxl import Workbook

from timesheet.defectdb import (HEADERS, OPTIONAL_HEADERS, _stock_state,
                                download_handler_csv, download_handler_xlsx,
                                external_unit_key, _handler_image_type,
                                parse_handler_csv,
                                parse_handler_xlsx,
                                parse_stock_xlsx, warehouse_group_from_name)


ROOT = Path(__file__).resolve().parents[2]
ENGINE = ROOT / "engine" / "timesheet"


def workbook_bytes(*, bad_total=False, without_optional=False):
    book = Workbook()
    sheet = book.active
    sheet.title = "TDSheet"
    headers = ([name for name in HEADERS if name not in OPTIONAL_HEADERS]
               if without_optional else list(HEADERS))
    sheet.append(headers)
    row = [None] * len(headers)
    values = {
        "Сегмент номенклатура": "Менеджер",
        "Номенклатура": "A-1, Товар тестовый",
        "Номенклатура.Классификация брака": "Механика",
        "Вид номенклатуры": "Бытовая техника",
        "Склад": "Брак Таборы", "Описание брака": "Царапина",
        "Дата создания брака": "09.10.2026", "Товар брака": "Да",
        "Номенклатура.Артикул": "A-1", "Номенклатура.Код": "00-1",
        "Номенклатура.Код для поиска": "1", "Сумма брака": 20,
        "Остаток": 2, "Себестоимость": 10,
    }
    for name, value in values.items():
        if name in headers:
            row[headers.index(name)] = value
    sheet.append(row)
    total = [None] * len(headers)
    total[0] = "Итого"
    total[headers.index("Сумма брака")] = 21 if bad_total else 20
    total[headers.index("Остаток")] = 2
    sheet.append(total)
    out = BytesIO()
    book.save(out)
    return out.getvalue()


def test_parser_reads_snapshot_and_checks_totals():
    parsed = parse_stock_xlsx(workbook_bytes(), "отчет по браку-09102026.xlsx")
    assert parsed["rows_total"] == 1
    assert parsed["snapshot_on"].isoformat() == "2026-10-09"
    assert str(parsed["amount_total"]) == "20.00"
    assert parsed["rows"][0]["name"] == "Товар тестовый"


def test_parser_rejects_inconsistent_terminal_total():
    try:
        parse_stock_xlsx(workbook_bytes(bad_total=True), "report.xlsx")
    except ValueError as exc:
        assert "Итог" in str(exc)
    else:
        raise AssertionError("неверный итог должен быть отклонён")


def test_optional_nomenclature_columns_may_be_absent():
    parsed = parse_stock_xlsx(
        workbook_bytes(without_optional=True), "остатки-10102026.xlsx")
    row = parsed["rows"][0]
    assert row["source_code"] is None
    assert row["article"] is None
    assert row["brand"] is None
    assert row["product_key"].startswith("name:")


def test_stock_comparison_ignores_row_order_but_detects_value_change():
    one = {"product_key": "code:1", "warehouse": "Склад",
           "quantity": 1, "cost": 10, "amount": 10}
    two = {"product_key": "code:1", "warehouse": "Склад",
           "quantity": 2, "cost": 10, "amount": 20}
    assert _stock_state([one, two]) == _stock_state([two, one])
    changed = dict(two, quantity=3, amount=30)
    assert _stock_state([one, two]) != _stock_state([one, changed])


def test_warehouse_group_is_detected_case_insensitively():
    assert warehouse_group_from_name("ПВЗ Мозырь") == "pvz"
    assert warehouse_group_from_name("ДЕПО Мозырь") == "depot"
    assert warehouse_group_from_name("21ВЕК Сервис РемЗона") == "service"
    assert warehouse_group_from_name("Неизвестный склад") == "other"


def handler_workbook_bytes():
    book = Workbook()
    sheet = book.active
    sheet.title = "ОБРАБОТКА"
    sheet.append([
        "Номенклатура", "Себестоимость", "Класификация брака", "Обработчик",
        "Описание 1С", "Дата", "Состояние", "Заводская упаковка", " Инструкция",
        "ГТ производителя", "Комплектация", "Наличия СИ",
        "Акт об отсутствии УКЗ", "Состояние УКЗ", "Серийный номер",
        "Описание дефектов ", "Ссылка на фото", "Заключение СЦ", "Вид брака",
        "Вид номенклатуры", "Вес", "последний поставщик",
        "Товар из ячейки обработки", "Вид работы", "Менеджер",
    ])
    sheet.append(["Формула"])
    row = [None] * 25
    row[0] = "8027747-БК-1233788, Смартфон"
    row[1] = "2 762,33"
    row[3] = "Иванов"
    row[5] = "24.10.2026 12:30:00"
    row[6] = "БУ"
    row[15] = "Царапина"
    row[18] = "Визуальный"
    row[23] = "Проверка"
    sheet.append(row)
    sheet.append(row)
    out = BytesIO()
    book.save(out)
    return out.getvalue()


def handler_csv_bytes():
    text = StringIO(newline="")
    writer = csv.writer(text)
    writer.writerow([
        "Номенклатура", "Себестоимость", "Класификация брака", "Обработчик",
        "Описание 1С", "Дата", "Состояние", "Заводская упаковка", " Инструкция",
        "ГТ производителя", "Комплектация", "Наличия СИ",
        "Акт об отсутствии УКЗ", "Состояние УКЗ", "Серийный номер",
        "Описание дефектов ", "Ссылка на фото", "Заключение СЦ", "Вид брака",
        "Вид номенклатуры", "Вес", "последний поставщик",
        "Товар из ячейки обработки", "Вид работы", "Менеджер",
    ])
    writer.writerow(["Формула"])
    row = [""] * 25
    row[0] = "8027747-БК-1233788, Смартфон"
    row[1] = "2 762,33"
    row[3] = "Иванов"
    row[5] = "24.10.2026 12:30:00"
    row[15] = "Царапина, справа"
    writer.writerow(row)
    return text.getvalue().encode("utf-8")


def test_handler_parser_keeps_repeated_processing_events():
    parsed = parse_handler_xlsx(handler_workbook_bytes(), "Обработчик.xlsx")
    assert parsed["rows_total"] == 2
    assert parsed["unique_unit_keys"] == 1
    assert parsed["reprocessed_events"] == 1
    assert parsed["rows"][0]["external_unit_key"] == "8027747-БК-1233788"
    assert str(parsed["rows"][0]["unit_cost"]) == "2762.33"


def test_handler_csv_parser_uses_the_same_row_contract():
    parsed = parse_handler_csv(handler_csv_bytes(), "Обработчик.csv")
    assert parsed["rows_total"] == 1
    assert parsed["rows"][0]["external_unit_key"] == "8027747-БК-1233788"
    assert parsed["rows"][0]["defect_description"] == "Царапина, справа"


def test_external_unit_key_normalizes_case_and_spaces():
    assert external_unit_key("9611733-расп-2430574, Товар") == "9611733-РАСП-2430574"


def test_handler_google_sync_accepts_xlsx_and_rejects_login_page():
    payload = handler_workbook_bytes()

    def xlsx_response(request):
        return httpx.Response(
            200, content=payload,
            headers={"content-type":
                     "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"},
            request=request)

    with httpx.Client(transport=httpx.MockTransport(xlsx_response)) as client:
        assert download_handler_xlsx(client) == payload

    def login_response(request):
        return httpx.Response(
            200, content=b"<html>sign in</html>",
            headers={"content-type": "text/html"}, request=request)

    with httpx.Client(transport=httpx.MockTransport(login_response)) as client:
        try:
            download_handler_xlsx(client)
        except ValueError as exc:
            assert "запросил вход" in str(exc)
        else:
            raise AssertionError("страница входа не должна считаться XLSX")

    csv_payload = handler_csv_bytes()

    def csv_response(request):
        return httpx.Response(
            200, content=csv_payload,
            headers={"content-type": "text/csv; charset=utf-8"}, request=request)

    with httpx.Client(transport=httpx.MockTransport(csv_response)) as client:
        assert download_handler_csv(client) == csv_payload


def test_database_and_routes_are_wired():
    migration = (ROOT / "sql" / "131_defect_stock_workspace.sql").read_text(encoding="utf-8")
    comparison = (ROOT / "sql" / "132_defect_snapshot_comparison.sql").read_text(encoding="utf-8")
    groups = (ROOT / "sql" / "133_defect_warehouse_groups.sql").read_text(encoding="utf-8")
    sources_sql = (ROOT / "sql" / "134_defect_work_sources.sql").read_text(encoding="utf-8")
    handler_sql = (ROOT / "sql" / "136_defect_handler.sql").read_text(encoding="utf-8")
    handler_edits_sql = (ROOT / "sql" / "137_defect_handler_edits.sql").read_text(encoding="utf-8")
    handler_workflow_sql = (ROOT / "sql" / "138_defect_handler_workflow.sql").read_text(encoding="utf-8")
    handler_local_sql = (ROOT / "sql" / "140_defect_handler_local_entry.sql").read_text(encoding="utf-8")
    handler_operations_sql = (ROOT / "sql" / "141_defect_handler_operations.sql").read_text(encoding="utf-8")
    web = (ENGINE / "webapp.py").read_text(encoding="utf-8")
    base = (ENGINE / "templates" / "base.html").read_text(encoding="utf-8")
    search = (ENGINE / "templates" / "defect_search.html").read_text(encoding="utf-8")
    directories = (ENGINE / "templates" / "defect_directories.html").read_text(encoding="utf-8")
    manager_stats = (ENGINE / "templates" / "defect_manager_stats.html").read_text(encoding="utf-8")
    sources = (ENGINE / "templates" / "defect_sources.html").read_text(encoding="utf-8")
    handler = (ENGINE / "templates" / "defect_handler.html").read_text(encoding="utf-8")
    handler_edit = (ENGINE / "templates" / "defect_handler_edit.html").read_text(encoding="utf-8")
    handler_new = (ENGINE / "templates" / "defect_handler_new.html").read_text(encoding="utf-8")
    handler_monitor = (ENGINE / "templates" / "defect_handler_monitor.html").read_text(encoding="utf-8")
    handler_settings = (ENGINE / "templates" / "defect_handler_settings.html").read_text(encoding="utf-8")
    defect_ui = (ENGINE / "static" / "defect_ui.js").read_text(encoding="utf-8")
    css = (ENGINE / "static" / "app.css").read_text(encoding="utf-8")
    assert "CREATE TABLE IF NOT EXISTS defect_stock_import" in migration
    assert "CREATE TABLE IF NOT EXISTS defect_stock_line" in migration
    assert "added_positions" in comparison and "source_key" in comparison
    assert "group_code" in groups and "defect_work_source" in sources_sql
    assert "defect_handler_event" in handler_sql and "row_fingerprint" in handler_sql
    assert "manual_override_fields" in handler_edits_sql
    assert "defect_handler_event_edit" in handler_edits_sql
    assert "workflow_status" in handler_workflow_sql
    assert "completed_by" in handler_workflow_sql
    assert "source_origin" in handler_local_sql
    assert "source_warehouse" in handler_local_sql
    assert "first_import_id DROP NOT NULL" in handler_local_sql
    assert "defect_handler_option" in handler_operations_sql
    assert "defect_handler_photo" in handler_operations_sql
    assert "processor_user_id" in handler_operations_sql
    assert "deleted_at" in handler_operations_sql
    assert '@app.get("/defects"' in web
    assert '@app.post("/defects/import")' in web
    assert '@app.get("/defects/directories"' in web
    assert '@app.get("/defects/managers"' in web
    assert '@app.get("/defects/sources"' in web
    assert '@app.get("/defects/handler"' in web
    assert '@app.post("/defects/handler/import")' in web
    assert '@app.post("/defects/handler/sync")' in web
    assert '@app.get("/defects/handler/new"' in web
    assert '@app.get("/api/defects/handler/stock-search")' in web
    assert '@app.post("/defects/handler/new")' in web
    assert '@app.get("/defects/handler/monitor"' in web
    assert '@app.get("/defects/handler/settings"' in web
    assert '@app.post("/defects/handler/{event_id}/photos")' in web
    assert '@app.post("/defects/handler/{event_id}/delete")' in web
    assert '@app.get("/defects/handler/{event_id}/edit"' in web
    assert '@app.post("/defects/handler/{event_id}/edit")' in web
    assert '@app.post("/defects/warehouses/{warehouse_id}/group")' in web
    assert 'href="/defects"' in base
    assert '<details class="tabs-more">' in base
    assert "Обеспечение" in base and "Руководство" in base
    assert 'warehouse: str = ""' in web
    assert 'class="pagination"' in search
    assert 'name="category"' in search
    assert 'name="date_range"' in search and "data-range-popover" in search
    assert 'name="sort"' in search and "search_query" in search
    assert "Артикул: {{ row.article" in search and "<th>Артикул</th>" not in search
    assert "<th>Учёт по ГТД</th>" not in search and "<th>Код</th>" not in search
    assert search.index("<th>Менеджер</th>") < search.index("<th>Текстовое описание</th>")
    assert "Открыть остатки" in directories
    assert 'name="warehouse"' in manager_stats and "manager_stats" in manager_stats
    assert "Габрияник" not in sources and "row.spreadsheet_url" in sources
    assert "Текстовое описание" in search
    assert "Обработчик брака" in handler and "handler-kpis" in handler
    assert 'action="/defects/handler/sync"' in handler
    assert "Резервная загрузка из XLSX" in handler
    assert handler.count('<nav class="pagination') == 2
    assert search.count('<nav class="pagination') == 2
    assert "data-handler-edit" in handler and "data-handler-dialog" in handler
    assert "Сохранить черновик" in handler_edit
    assert "Завершить обработку" in handler_edit
    assert "manual_override_fields" in handler_edit
    assert 'Новая обработка' in handler
    assert "minimum_length" in web and "handler_stock_search" in web
    assert "activeIndex = -1" in handler_new
    assert "event.key === \"Enter\" && activeIndex >= 0" in handler_new
    assert 'Продолжить мой черновик' in handler_new
    assert 'Предыдущие обработки этого экземпляра' in handler_edit
    assert "localStorage.setItem" in handler_edit and "navigator.onLine" in handler_edit
    assert 'name="processor_user_id"' in handler_edit
    assert 'capture="environment"' in handler_edit
    assert 'Удалить черновик' in handler_edit
    assert 'Мониторинг обработчиков' in handler_monitor
    assert 'Справочники обработчика' in handler_settings
    handler_route = web[web.index('def defect_handler_page('):
                        web.index('@app.get("/defects/handler/new"')]
    assert '"workflow_labels": defectdb.HANDLER_WORKFLOW_LABELS' in handler_route
    assert 'querySelectorAll(".table-scroll")' in defect_ui
    assert "defect-handler-saved" in defect_ui
    assert 'name="processing"' in search and "processing-badge" in search
    assert "defect-handler-page" in handler
    assert "handler-edit-page" in handler_edit
    assert "body.defect-handler-page" in css and "overflow: hidden" in css
    assert "repeat(4, minmax(0, 1fr))" in css
    for hidden_column in ("Код для поиска", "Марка (бренд)",
                          "Процент уценки", "Срок годности", "Картинка"):
        assert hidden_column not in search


def test_handler_photo_type_comes_from_file_signature():
    assert _handler_image_type(b"\xff\xd8\xffanything") == "image/jpeg"
    assert _handler_image_type(b"\x89PNG\r\n\x1a\nanything") == "image/png"
    assert _handler_image_type(b"RIFFxxxxWEBPanything") == "image/webp"
    try:
        _handler_image_type(b"not an image")
    except ValueError as exc:
        assert "JPEG" in str(exc)
    else:
        raise AssertionError("текстовый файл не должен считаться фотографией")
