"""Импорт и поиск остатков брака из выгрузки 1С.

Файл — снимок на дату, а не перечень движений. Поэтому каждая
загрузка хранится целиком, а поиск по умолчанию работает с последним
успешным снимком. Повторные и отрицательные строки не склеиваются:
это данные источника, которые нужно видеть и проверять.
"""

from __future__ import annotations

import hashlib
import json
import re
import csv
from collections import defaultdict
from datetime import date, datetime, timedelta
from decimal import Decimal, InvalidOperation
from io import BytesIO, StringIO
from typing import Any
from zoneinfo import ZoneInfo

import httpx
import psycopg
from openpyxl import load_workbook


HEADERS = (
    "Сегмент номенклатура", "Номенклатура",
    "Номенклатура.Классификация брака", "Вид номенклатуры",
    "Вес товара", "Склад", "Описание брака", "Заключение СЦ",
    "Дата создания брака", "Дата последней отправки ВСЦ", "Товар брака",
    "Последний поставщик", "Номенклатура.Доп.код ТН ВЭД",
    "Номенклатура.Товар был в употреблении", "Номенклатура.Артикул",
    "Номенклатура.Вести учет по ГТД", "Номенклатура.Код",
    "Номенклатура.Код для поиска", "Номенклатура.Марка (бренд)",
    "Номенклатура.Процент уценки", "Номенклатура.Срок годности",
    "Номенклатура.Ссылка на картинку на сайте", "Номенклатура.Текстовое описание",
    "Сумма брака", "Остаток", "Себестоимость",
)

# Эти поля обогащают карточку товара, но не определяют сам остаток.
# Выгрузка без них принимается; при следующей загрузке уже известное
# значение не затирается пустой ячейкой.
OPTIONAL_HEADERS = frozenset({
    "Номенклатура.Артикул",
    "Номенклатура.Вести учет по ГТД",
    "Номенклатура.Код",
    "Номенклатура.Код для поиска",
    "Номенклатура.Марка (бренд)",
    "Номенклатура.Процент уценки",
    "Номенклатура.Срок годности",
    "Номенклатура.Ссылка на картинку на сайте",
    "Номенклатура.Текстовое описание",
})

WAREHOUSE_GROUPS = (
    ("our", "Наши склады"),
    ("supplier", "Склады поставщика"),
    ("pvz", "Склады ПВЗ"),
    ("depot", "Склады Депо"),
    ("service", "Склады 21Век Сервис"),
    ("other", "Иные склады"),
)
WAREHOUSE_GROUP_LABELS = dict(WAREHOUSE_GROUPS)
REQUIRED_HEADERS = tuple(name for name in HEADERS if name not in OPTIONAL_HEADERS)

HANDLER_SPREADSHEET_ID = "1uq0TDLuj21H89uhEvzGAnv3elgkKxoaVd_e5T30YD1U"
HANDLER_SPREADSHEET_TITLE = "Обработчик"
HANDLER_SHEET = "ОБРАБОТКА"
HANDLER_SOURCE_URL = (
    "https://docs.google.com/spreadsheets/d/"
    f"{HANDLER_SPREADSHEET_ID}/edit?gid=1608899958#gid=1608899958"
)
HANDLER_EXPORT_URL = (
    "https://docs.google.com/spreadsheets/d/"
    f"{HANDLER_SPREADSHEET_ID}/export?format=xlsx"
)
HANDLER_CSV_EXPORT_URL = (
    "https://docs.google.com/spreadsheets/d/"
    f"{HANDLER_SPREADSHEET_ID}/export?format=csv&gid=1608899958"
)
HANDLER_CSV_RETRY_URL = HANDLER_CSV_EXPORT_URL + "&single=true"
HANDLER_EXPORT_MAX_BYTES = 50 * 1024 * 1024
HANDLER_MATCH_LABELS = {
    "exact_current": "Точное совпадение с текущим остатком",
    "unit_key_current": "Совпадение по БК-ключу",
    "ambiguous_current": "Неоднозначное совпадение",
    "not_in_current_stock": "Нет в текущих остатках",
}
PROCESSING_STATUS_LABELS = {
    "processed": "Обработан",
    "needs_attention": "Требует заполнения",
    "unprocessed": "Не обработан",
}
HANDLER_WORKFLOW_LABELS = {
    "draft": "Черновики",
    "completed": "Завершённые в приложении",
    "imported": "Импортированная история",
}
HANDLER_OPTION_FIELDS = {
    "condition_name": "Состояние",
    "factory_packaging": "Заводская упаковка",
    "instruction_name": "Инструкция",
    "manufacturer_warranty": "Гарантийный талон производителя",
    "completeness": "Комплектация",
    "si_presence": "Наличие СИ",
    "missing_ukz_act": "Акт об отсутствии УКЗ",
    "ukz_condition": "Состояние УКЗ",
    "defect_type": "Вид брака",
    "from_processing_cell": "Товар из ячейки обработки",
    "work_type": "Вид работы / результат",
}
HANDLER_PHOTO_MAX_BYTES = 8 * 1024 * 1024
HANDLER_PHOTO_MAX_COUNT = 12
_HANDLER_TZ = ZoneInfo("Europe/Moscow")
_EXTERNAL_UNIT_RE = re.compile(
    r"(?i)(\d+\s*-\s*[а-яa-z]+\s*-\s*\d+)")


def _text(value: object) -> str | None:
    text = str(value).strip() if value is not None else ""
    return text or None


def _decimal(value: object, label: str, row_no: int,
             required: bool = False) -> Decimal | None:
    if value is None or str(value).strip() == "":
        if required:
            raise ValueError(f"Строка {row_no}: не заполнено поле «{label}»")
        return None
    try:
        return Decimal(str(value).replace(" ", "").replace(",", "."))
    except InvalidOperation as exc:
        raise ValueError(f"Строка {row_no}: в поле «{label}» не число") from exc


def _date(value: object, label: str, row_no: int) -> date | None:
    if value is None or str(value).strip() == "":
        return None
    if isinstance(value, datetime):
        return value.date()
    if isinstance(value, date):
        return value
    for fmt in ("%d.%m.%Y", "%Y-%m-%d"):
        try:
            return datetime.strptime(str(value).strip(), fmt).date()
        except ValueError:
            pass
    raise ValueError(f"Строка {row_no}: неверная дата в поле «{label}»")


def _bool(value: object) -> bool | None:
    text = (_text(value) or "").lower()
    if not text:
        return None
    if text in {"да", "true", "1"}:
        return True
    if text in {"нет", "false", "0"}:
        return False
    return None


def _snapshot_date(filename: str) -> date | None:
    match = re.search(r"(?<!\d)(\d{2})(\d{2})(\d{4})(?!\d)", filename)
    if not match:
        return None
    try:
        return date(int(match[3]), int(match[2]), int(match[1]))
    except ValueError:
        return None


def _product_key(source_code: str | None, article: str | None,
                 search_code: str | None, source_name: str) -> str:
    """Стабильный внутренний ключ, даже если кодовых колонок в файле нет."""
    if source_code:
        return "code:" + source_code
    if article:
        return "article:" + article
    if search_code:
        return "search:" + search_code
    normalized = " ".join(source_name.lower().split())
    return "name:" + hashlib.sha256(normalized.encode("utf-8")).hexdigest()


def warehouse_group_from_name(name: str) -> str:
    """Группа, которую можно однозначно определить из названия склада."""
    normalized = name.casefold()
    if "21век сервис" in normalized or "21 век сервис" in normalized:
        return "service"
    if "пвз" in normalized:
        return "pvz"
    if "депо" in normalized:
        return "depot"
    return "other"


def parse_stock_xlsx(data: bytes, filename: str) -> dict[str, Any]:
    """Проверить файл и вернуть нормализованный снимок без записи в БД."""
    if not data:
        raise ValueError("Файл пуст")
    try:
        book = load_workbook(BytesIO(data), read_only=True, data_only=True)
    except Exception as exc:
        raise ValueError("Не удалось прочитать XLSX-файл") from exc
    sheet = book["TDSheet"] if "TDSheet" in book.sheetnames else book[book.sheetnames[0]]
    iterator = sheet.iter_rows(values_only=True)
    try:
        source_headers = next(iterator)
    except StopIteration as exc:
        raise ValueError("В файле нет строк") from exc
    names = [(_text(v) or "") for v in source_headers]
    missing = [name for name in REQUIRED_HEADERS if name not in names]
    if missing:
        raise ValueError("Не хватает колонок: " + ", ".join(missing))
    ix = {name: names.index(name) for name in HEADERS if name in names}

    rows: list[dict[str, Any]] = []
    terminal_qty: Decimal | None = None
    terminal_amount: Decimal | None = None
    for row_no, values in enumerate(iterator, start=2):
        if not any(v is not None and str(v).strip() for v in values):
            continue
        if (_text(values[0]) or "").lower() == "итого":
            terminal_amount = _decimal(values[ix["Сумма брака"]], "Сумма брака", row_no)
            terminal_qty = _decimal(values[ix["Остаток"]], "Остаток", row_no)
            continue
        get = lambda name: (values[ix[name]]
                            if name in ix and ix[name] < len(values) else None)
        source_name = _text(get("Номенклатура"))
        warehouse = _text(get("Склад"))
        source_code = _text(get("Номенклатура.Код"))
        if not source_name or not warehouse:
            raise ValueError(f"Строка {row_no}: не заполнены номенклатура или склад")
        quantity = _decimal(get("Остаток"), "Остаток", row_no, True)
        cost = _decimal(get("Себестоимость"), "Себестоимость", row_no, True)
        amount = _decimal(get("Сумма брака"), "Сумма брака", row_no, True)
        if abs(amount - quantity * cost) > Decimal("0.02"):
            raise ValueError(f"Строка {row_no}: сумма не равна остатку × себестоимости")
        short_name = source_name.split(",", 1)[1].strip() if "," in source_name else source_name
        article = _text(get("Номенклатура.Артикул"))
        search_code = _text(get("Номенклатура.Код для поиска"))
        rows.append({
            "source_row": row_no, "manager": _text(get("Сегмент номенклатура")),
            "source_name": source_name, "name": short_name,
            "classification": _text(get("Номенклатура.Классификация брака")),
            "product_kind": _text(get("Вид номенклатуры")),
            "weight": _decimal(get("Вес товара"), "Вес товара", row_no),
            "warehouse": warehouse,
            "defect_description": _text(get("Описание брака")),
            "service_conclusion": _text(get("Заключение СЦ")),
            "defect_created_on": _date(get("Дата создания брака"), "Дата создания брака", row_no),
            "last_service_sent_on": _date(get("Дата последней отправки ВСЦ"), "Дата последней отправки ВСЦ", row_no),
            "is_defect": _bool(get("Товар брака")), "supplier": _text(get("Последний поставщик")),
            "customs_code": _text(get("Номенклатура.Доп.код ТН ВЭД")),
            "was_used": _bool(get("Номенклатура.Товар был в употреблении")),
            "article": article,
            "customs_tracking": _bool(get("Номенклатура.Вести учет по ГТД")), "source_code": source_code,
            "search_code": search_code, "brand": _text(get("Номенклатура.Марка (бренд)")),
            "discount_percent": _decimal(get("Номенклатура.Процент уценки"), "Процент уценки", row_no),
            "shelf_life": _text(get("Номенклатура.Срок годности")), "image_url": _text(get("Номенклатура.Ссылка на картинку на сайте")),
            "text_description": _text(get("Номенклатура.Текстовое описание")),
            "amount": amount.quantize(Decimal("0.01")), "quantity": quantity,
            "cost": cost.quantize(Decimal("0.01")),
        })
        rows[-1]["product_key"] = _product_key(
            source_code, article, search_code, source_name)
    if not rows:
        raise ValueError("В файле нет строк остатков")
    qty_total = sum((r["quantity"] for r in rows), Decimal(0))
    amount_total = sum((r["amount"] for r in rows), Decimal(0))
    if terminal_qty is not None and abs(qty_total - terminal_qty) > Decimal("0.001"):
        raise ValueError("Итог «Остаток» не сходится со строками")
    if terminal_amount is not None and abs(amount_total - terminal_amount) > Decimal("0.01"):
        raise ValueError("Итог «Сумма брака» не сходится со строками")
    negative = sum(r["quantity"] < 0 for r in rows)
    blank_manager = sum(r["manager"] is None for r in rows)
    warnings = []
    if negative:
        warnings.append(f"Отрицательный остаток: {negative} строк")
    if blank_manager:
        warnings.append(f"Не указан ответственный менеджер: {blank_manager} строк")
    return {
        "filename": filename, "sha256": hashlib.sha256(data).hexdigest(),
        "sheet_name": sheet.title, "snapshot_on": _snapshot_date(filename),
        "rows": rows, "rows_total": len(rows),
        "product_count": len({r["product_key"] for r in rows}),
        "warehouse_count": len({r["warehouse"] for r in rows}),
        "quantity_total": qty_total, "amount_total": amount_total,
        "negative_rows": negative, "blank_manager_rows": blank_manager,
        "warnings": warnings,
    }


def _upsert_names(cur, table: str, names: set[str]) -> dict[str, int]:
    if names:
        cur.executemany(f"INSERT INTO app.{table} (name) VALUES (%s) ON CONFLICT (name) DO NOTHING",
                        [(name,) for name in sorted(names)])
        cur.execute(f"SELECT id, name FROM app.{table} WHERE name = ANY(%s)", (list(names),))
        return {r["name"]: r["id"] for r in cur.fetchall()}
    return {}


def _stock_state(rows: list[dict[str, Any]]) -> dict[tuple[str, str], tuple]:
    """Состояние остатков по паре «номенклатура + место хранения».

    Внутри ключа храним отсортированный набор строк: так повторы не
    склеиваются, а перестановка строк в Excel не считается изменением.
    """
    grouped: dict[tuple[str, str], list[tuple[Decimal, Decimal, Decimal]]] = defaultdict(list)
    for row in rows:
        grouped[(row["product_key"], row["warehouse"])].append(
            (Decimal(row["quantity"]), Decimal(row["cost"]), Decimal(row["amount"])))
    return {key: tuple(sorted(values)) for key, values in grouped.items()}


def _resolve_product_keys(conn: psycopg.Connection,
                          rows: list[dict[str, Any]]) -> None:
    """Сопоставить строку без кода с уже известной номенклатурой.

    Иначе тот же товар из усечённой выгрузки получил бы новый ключ и выглядел
    как удаление и добавление. Берём только однозначные совпадения.
    """
    pending = [row for row in rows if not row["source_code"]]
    if not pending:
        return
    with conn.cursor() as cur:
        cur.execute(
            "SELECT source_key, source_name, article, search_code "
            "FROM app.defect_product")
        products = cur.fetchall()

    def unique_map(field: str) -> dict[str, str]:
        found: dict[str, set[str]] = defaultdict(set)
        for product in products:
            if product[field]:
                found[str(product[field]).strip()].add(product["source_key"])
        return {value: next(iter(keys)) for value, keys in found.items()
                if len(keys) == 1}

    by_article = unique_map("article")
    by_search = unique_map("search_code")
    by_name = unique_map("source_name")
    for row in pending:
        key = ((row["article"] and by_article.get(row["article"]))
               or (row["search_code"] and by_search.get(row["search_code"]))
               or by_name.get(row["source_name"]))
        if key:
            row["product_key"] = key


def _current_stock(conn: psycopg.Connection) -> tuple[dict[tuple[str, str], tuple],
                                                        dict[str, Any] | None]:
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM app.defect_stock_import WHERE is_current")
        current = cur.fetchone()
        if not current:
            return {}, None
        cur.execute(
            """SELECT p.source_key AS product_key, w.source_name AS warehouse,
                      l.quantity, l.unit_cost AS cost, l.defect_amount AS amount
                 FROM app.defect_stock_line l
                 JOIN app.defect_product p ON p.id=l.product_id
                 JOIN app.defect_warehouse w ON w.id=l.warehouse_id
                WHERE l.import_id=%s""", (current["id"],))
        return _stock_state(cur.fetchall()), current


def compare_with_current(conn: psycopg.Connection,
                         parsed: dict[str, Any]) -> dict[str, Any]:
    """Сравнить новый снимок с текущим без записи в базу."""
    new_state = _stock_state(parsed["rows"])
    old_state, current = _current_stock(conn)
    new_keys, old_keys = set(new_state), set(old_state)
    common = new_keys & old_keys
    changed = sum(new_state[key] != old_state[key] for key in common)
    old_qty = Decimal(current["quantity_total"]) if current else Decimal(0)
    old_amount = Decimal(current["amount_total"]) if current else Decimal(0)
    return {
        "compared_to_id": current["id"] if current else None,
        "added_positions": len(new_keys - old_keys),
        "removed_positions": len(old_keys - new_keys),
        "changed_positions": changed,
        "quantity_delta": parsed["quantity_total"] - old_qty,
        "amount_delta": parsed["amount_total"] - old_amount,
        "unchanged": bool(current) and new_state == old_state,
    }


def import_stock_xlsx(conn: psycopg.Connection, data: bytes, filename: str,
                      user_id: int | None) -> dict[str, Any]:
    """Проверить и атомарно записать новый текущий снимок."""
    parsed = parse_stock_xlsx(data, filename)
    rows = parsed["rows"]
    _resolve_product_keys(conn, rows)
    # После сопоставления кодовых полей может не быть, а число
    # реальных номенклатур всё равно нужно считать по итоговому ключу.
    parsed["product_count"] = len({row["product_key"] for row in rows})
    comparison = compare_with_current(conn, parsed)
    if comparison["unchanged"]:
        # Файл проверен, но историю одинаковыми снимками не засоряем.
        return {**{k: v for k, v in parsed.items() if k != "rows"},
                **comparison, "id": comparison["compared_to_id"]}
    with conn.cursor() as cur:
        managers = _upsert_names(cur, "defect_manager", {r["manager"] for r in rows if r["manager"]})
        kinds = _upsert_names(cur, "defect_product_kind", {r["product_kind"] for r in rows if r["product_kind"]})
        classes = _upsert_names(cur, "defect_classification", {r["classification"] for r in rows if r["classification"]})
        suppliers = _upsert_names(cur, "defect_supplier", {r["supplier"] for r in rows if r["supplier"]})
        warehouses = {r["warehouse"] for r in rows}
        cur.executemany(
            """INSERT INTO app.defect_warehouse (source_name, group_code)
               VALUES (%s, %s)
               ON CONFLICT (source_name) DO UPDATE SET is_active = true""",
            [(name, warehouse_group_from_name(name)) for name in sorted(warehouses)])
        cur.execute("SELECT id, source_name FROM app.defect_warehouse WHERE source_name = ANY(%s)",
                    (list(warehouses),))
        warehouse_ids = {r["source_name"]: r["id"] for r in cur.fetchall()}

        products: dict[str, dict[str, Any]] = {}
        for row in rows:
            products[row["product_key"]] = row
        cur.executemany(
            """INSERT INTO app.defect_product
                 (source_key, source_code, search_code, article, name, source_name, weight,
                  customs_code, was_used, customs_tracking, brand,
                  discount_percent, shelf_life, image_url, text_description)
               VALUES (%(product_key)s, %(source_code)s, %(search_code)s, %(article)s, %(name)s,
                       %(source_name)s, %(weight)s, %(customs_code)s, %(was_used)s,
                       %(customs_tracking)s, %(brand)s, %(discount_percent)s,
                       %(shelf_life)s, %(image_url)s, %(text_description)s)
               ON CONFLICT (source_key) DO UPDATE SET
                 source_code=coalesce(excluded.source_code, defect_product.source_code),
                 search_code=coalesce(excluded.search_code, defect_product.search_code),
                 article=coalesce(excluded.article, defect_product.article),
                 name=excluded.name, source_name=excluded.source_name,
                 weight=excluded.weight, customs_code=excluded.customs_code,
                 was_used=excluded.was_used, customs_tracking=excluded.customs_tracking,
                 brand=coalesce(excluded.brand, defect_product.brand),
                 discount_percent=coalesce(excluded.discount_percent, defect_product.discount_percent),
                 shelf_life=coalesce(excluded.shelf_life, defect_product.shelf_life),
                 image_url=coalesce(excluded.image_url, defect_product.image_url),
                 text_description=coalesce(excluded.text_description, defect_product.text_description),
                 updated_at=now()""",
            list(products.values()))
        keys = list(products)
        cur.execute("SELECT id, source_key FROM app.defect_product WHERE source_key = ANY(%s)",
                    (keys,))
        product_ids = {r["source_key"]: r["id"] for r in cur.fetchall()}
        cur.execute("UPDATE app.defect_stock_import SET is_current = false WHERE is_current")
        cur.execute(
            """INSERT INTO app.defect_stock_import
                 (filename, file_sha256, sheet_name, snapshot_on, uploaded_by,
                  is_current, rows_total, product_count, warehouse_count,
                  quantity_total, amount_total, negative_rows,
                  blank_manager_rows, warnings, compared_to_id, added_positions,
                  removed_positions, changed_positions, quantity_delta, amount_delta)
               VALUES (%s,%s,%s,%s,%s,true,%s,%s,%s,%s,%s,%s,%s,%s,
                       %s,%s,%s,%s,%s,%s)
               RETURNING id""",
            (filename, parsed["sha256"], parsed["sheet_name"], parsed["snapshot_on"],
             user_id, parsed["rows_total"], parsed["product_count"],
             parsed["warehouse_count"], parsed["quantity_total"],
             parsed["amount_total"], parsed["negative_rows"],
             parsed["blank_manager_rows"], json.dumps(parsed["warnings"], ensure_ascii=False),
             comparison["compared_to_id"], comparison["added_positions"],
             comparison["removed_positions"], comparison["changed_positions"],
             comparison["quantity_delta"], comparison["amount_delta"]))
        import_id = cur.fetchone()["id"]
        line_sql = """INSERT INTO app.defect_stock_line
          (import_id, source_row, product_id, warehouse_id, manager_id,
           product_kind_id, classification_id, supplier_id, is_defect,
           defect_description, service_conclusion, defect_created_on,
           last_service_sent_on, quantity, unit_cost, defect_amount)
          VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"""
        cur.executemany(line_sql, [(
            import_id, r["source_row"], product_ids[r["product_key"]],
            warehouse_ids[r["warehouse"]], managers.get(r["manager"]),
            kinds.get(r["product_kind"]), classes.get(r["classification"]),
            suppliers.get(r["supplier"]), r["is_defect"], r["defect_description"],
            r["service_conclusion"], r["defect_created_on"], r["last_service_sent_on"],
            r["quantity"], r["cost"], r["amount"]) for r in rows])
    # Связь обработанной БК-единицы с остатком является текущим состоянием,
    # поэтому после каждого нового снимка её пересчитываем. История событий
    # обработчика при этом не меняется.
    reconcile_handler_events(conn)
    conn.commit()
    return {**{k: v for k, v in parsed.items() if k != "rows"},
            **comparison, "id": import_id}


def import_history(conn: psycopg.Connection, limit: int = 20) -> list[dict[str, Any]]:
    with conn.cursor() as cur:
        cur.execute(
            """SELECT i.*, u.full_name AS uploaded_by_name
                 FROM app.defect_stock_import i
                 LEFT JOIN app.app_user u ON u.id = i.uploaded_by
                ORDER BY i.loaded_at DESC LIMIT %s""", (limit,))
        return cur.fetchall()


def filters(conn: psycopg.Connection) -> dict[str, list[dict[str, Any]]]:
    out = {}
    with conn.cursor() as cur:
        cur.execute(
            """SELECT id, source_name AS name, group_code
                 FROM app.defect_warehouse
                ORDER BY CASE group_code
                  WHEN 'our' THEN 1 WHEN 'supplier' THEN 2 WHEN 'pvz' THEN 3
                  WHEN 'depot' THEN 4 WHEN 'service' THEN 5 ELSE 6 END,
                  source_name""")
        out["warehouses"] = cur.fetchall()
    for key, table, label in (
        ("managers", "defect_manager", "name"),
        ("categories", "defect_product_kind", "name"),
        ("classifications", "defect_classification", "name"),
    ):
        with conn.cursor() as cur:
            cur.execute(f"SELECT id, {label} AS name FROM app.{table} ORDER BY {label}")
            out[key] = cur.fetchall()
    return out


def current_summary(conn: psycopg.Connection) -> dict[str, Any] | None:
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM app.defect_stock_import WHERE is_current")
        return cur.fetchone()


def search(conn: psycopg.Connection, query: str = "", warehouse_id: int | None = None,
           manager_id: int | None = None, classification_id: int | None = None,
           category_id: int | None = None, date_from: date | None = None,
           date_to: date | None = None, processing_status: str = "",
           sort: str = "name",
           direction: str = "asc", page: int = 1,
           per_page: int = 100) -> dict[str, Any]:
    """Страница текущих остатков. Без фильтров возвращает всю базу постранично."""
    text = query.strip()
    pattern = f"%{text}%"
    per_page = max(10, min(per_page, 200))
    page = max(1, page)
    processing = (processing_status
                  if processing_status in PROCESSING_STATUS_LABELS else "")
    joins = """FROM app.defect_stock_line l
                 JOIN app.defect_stock_import i ON i.id=l.import_id AND i.is_current
                 JOIN app.defect_product p ON p.id=l.product_id
                 JOIN app.defect_warehouse w ON w.id=l.warehouse_id
                 LEFT JOIN app.defect_manager m ON m.id=l.manager_id
                 LEFT JOIN app.defect_product_kind k ON k.id=l.product_kind_id
                 LEFT JOIN app.defect_classification c ON c.id=l.classification_id
                 LEFT JOIN app.defect_supplier s ON s.id=l.supplier_id
                 LEFT JOIN LATERAL (
                   SELECT count(*) AS processing_count,
                          max(coalesce(e.completed_at, e.processed_at)) AS last_processed_at,
                          bool_or(
                            e.workflow_status='completed' OR
                            (e.workflow_status='imported'
                             AND e.processed_at IS NOT NULL
                             AND nullif(btrim(coalesce(e.processor,'')), '') IS NOT NULL
                             AND coalesce(nullif(btrim(e.defect_type), ''),
                                          nullif(btrim(e.defect_description), ''),
                                          nullif(btrim(e.work_type), ''),
                                          nullif(btrim(e.service_conclusion), '')) IS NOT NULL)
                          ) AS has_completed,
                          bool_or(e.workflow_status='draft') AS has_draft
                     FROM app.defect_handler_event e
                    WHERE e.is_source_present AND e.product_id=p.id
                 ) hs ON true"""
    status_sql = """CASE
                       WHEN coalesce(hs.has_completed, false) THEN 'processed'
                       WHEN coalesce(hs.processing_count, 0) > 0
                         OR coalesce(hs.has_draft, false) THEN 'needs_attention'
                       ELSE 'unprocessed'
                     END"""
    where = """WHERE (%s = '' OR p.name ILIKE %s OR p.source_name ILIKE %s
                       OR coalesce(p.source_code,'') ILIKE %s
                       OR coalesce(p.search_code,'') ILIKE %s
                       OR coalesce(p.article,'') ILIKE %s OR coalesce(p.brand,'') ILIKE %s
                       OR coalesce(p.text_description,'') ILIKE %s
                       OR coalesce(l.defect_description,'') ILIKE %s)
                  AND (%s::bigint IS NULL OR l.warehouse_id=%s::bigint)
                  AND (%s::bigint IS NULL OR l.manager_id=%s::bigint)
                  AND (%s::bigint IS NULL OR l.classification_id=%s::bigint)
                  AND (%s::bigint IS NULL OR l.product_kind_id=%s::bigint)
                  AND (%s::date IS NULL OR l.defect_created_on >= %s::date)
                  AND (%s::date IS NULL OR l.defect_created_on <= %s::date)
                  AND (%s='' OR """ + status_sql + """=%s)"""
    params = (text, pattern, pattern, pattern, pattern, pattern, pattern,
              pattern, pattern, warehouse_id, warehouse_id, manager_id,
              manager_id, classification_id, classification_id,
              category_id, category_id, date_from, date_from, date_to, date_to,
              processing, processing)
    sort_columns = {
        "name": "p.name",
        "unit_cost": "l.unit_cost",
        "quantity": "l.quantity",
        "defect_amount": "l.defect_amount",
        "manager": "coalesce(m.name, '')",
    }
    sort_column = sort_columns.get(sort, sort_columns["name"])
    sort_direction = "DESC" if direction == "desc" else "ASC"
    with conn.cursor() as cur:
        cur.execute("SELECT count(*) AS total " + joins + " " + where, params)
        total = cur.fetchone()["total"]
        pages = max(1, (total + per_page - 1) // per_page)
        page = min(page, pages)
        cur.execute(
            """SELECT l.source_row, p.source_code, p.search_code, p.article,
                      p.name, p.brand, p.customs_tracking, p.discount_percent,
                      p.shelf_life, p.image_url, p.text_description,
                      w.source_name AS warehouse,
                      m.name AS manager, k.name AS product_kind,
                      c.name AS classification, s.name AS supplier,
                      l.quantity, l.unit_cost, l.defect_amount,
                      l.defect_description, l.service_conclusion,
                      l.defect_created_on, l.last_service_sent_on, l.is_defect,
                      coalesce(hs.processing_count, 0) AS processing_count,
                      hs.last_processed_at,
                      """ + status_sql + """ AS processing_status
              """ + joins + " " + where + f"""
                ORDER BY {sort_column} {sort_direction}, p.name, w.source_name, l.source_row
                LIMIT %s OFFSET %s""",
            (*params, per_page, (page - 1) * per_page))
        return {"rows": cur.fetchall(), "total": total, "page": page,
                "pages": pages, "per_page": per_page}


def directory_summary(conn: psycopg.Connection) -> dict[str, list[dict[str, Any]]]:
    """Справочники складов и менеджеров с итогами текущего снимка."""
    with conn.cursor() as cur:
        cur.execute(
            """SELECT w.id, w.source_name AS name, w.group_code,
                      count(l.id) AS positions,
                      count(DISTINCT l.product_id) AS products,
                      coalesce(sum(l.quantity), 0) AS quantity,
                      coalesce(sum(l.defect_amount), 0) AS amount
                 FROM app.defect_warehouse w
                 LEFT JOIN (app.defect_stock_line l
                   JOIN app.defect_stock_import i
                     ON i.id=l.import_id AND i.is_current)
                   ON l.warehouse_id=w.id
                GROUP BY w.id, w.source_name, w.group_code
                ORDER BY CASE w.group_code
                  WHEN 'our' THEN 1 WHEN 'supplier' THEN 2 WHEN 'pvz' THEN 3
                  WHEN 'depot' THEN 4 WHEN 'service' THEN 5 ELSE 6 END,
                  w.source_name""")
        warehouses = cur.fetchall()
        cur.execute(
            """SELECT m.id, m.name,
                      count(l.id) AS positions,
                      count(DISTINCT l.product_id) AS products,
                      coalesce(sum(l.quantity), 0) AS quantity,
                      coalesce(sum(l.defect_amount), 0) AS amount
                 FROM app.defect_manager m
                 LEFT JOIN (app.defect_stock_line l
                   JOIN app.defect_stock_import i
                     ON i.id=l.import_id AND i.is_current)
                   ON l.manager_id=m.id
                GROUP BY m.id, m.name
                ORDER BY m.name""")
        managers = cur.fetchall()
    return {"warehouse_rows": warehouses, "manager_rows": managers}


def grouped_warehouses(rows: list[dict[str, Any]]) -> list[dict[str, Any]]:
    """Все шесть групп в стабильном порядке, включая пока пустые."""
    return [
        {"code": code, "label": label,
         "rows": [row for row in rows if row.get("group_code") == code]}
        for code, label in WAREHOUSE_GROUPS
    ]


def update_warehouse_group(conn: psycopg.Connection, warehouse_id: int,
                           group_code: str) -> None:
    if group_code not in WAREHOUSE_GROUP_LABELS:
        raise ValueError("Неизвестная группа склада")
    with conn.cursor() as cur:
        cur.execute(
            "UPDATE app.defect_warehouse SET group_code=%s WHERE id=%s",
            (group_code, warehouse_id))
        if cur.rowcount != 1:
            raise ValueError("Склад не найден")
    conn.commit()


def work_sources(conn: psycopg.Connection) -> list[dict[str, Any]]:
    """Зарегистрированные рабочие файлы менеджеров."""
    with conn.cursor() as cur:
        cur.execute(
            """SELECT * FROM app.defect_work_source
                WHERE is_active
                ORDER BY specialist, file_name""")
        return cur.fetchall()


def manager_statistics(conn: psycopg.Connection,
                       warehouse_ids: list[int] | None = None) -> list[dict[str, Any]]:
    """Текущие остатки по менеджерам для выбранного набора складов."""
    selected = warehouse_ids or None
    with conn.cursor() as cur:
        cur.execute(
            """SELECT m.id,
                      coalesce(m.name, 'Без менеджера') AS manager,
                      count(l.id) AS positions,
                      count(DISTINCT l.product_id) AS products,
                      count(DISTINCT l.warehouse_id) AS warehouses,
                      coalesce(sum(l.quantity), 0) AS quantity,
                      coalesce(sum(l.defect_amount), 0) AS amount
                 FROM app.defect_stock_line l
                 JOIN app.defect_stock_import i
                   ON i.id=l.import_id AND i.is_current
                 LEFT JOIN app.defect_manager m ON m.id=l.manager_id
                WHERE (%s::bigint[] IS NULL OR l.warehouse_id=ANY(%s::bigint[]))
                GROUP BY m.id, m.name
                ORDER BY m.name NULLS LAST""",
            (selected, selected))
        return cur.fetchall()


# ---------------------------------------------------------------------------
# Обработчик брака. Источник пока загружается вручную как XLSX: это сохраняет
# рабочую Google-таблицу без изменений и даёт воспроизводимый снимок импорта.

_HANDLER_COLUMNS = (
    ("external_name", ("Номенклатура",)),
    ("unit_cost", ("Себестоимость",)),
    ("classification", ("Класификация брака", "Классификация брака")),
    ("processor", ("Обработчик",)),
    ("source_description", ("Описание 1С",)),
    ("processed_at_raw", ("Дата",)),
    ("condition_name", ("Состояние",)),
    ("factory_packaging", ("Заводская упаковка",)),
    ("instruction_name", ("Инструкция",)),
    ("manufacturer_warranty", ("ГТ производителя",)),
    ("completeness", ("Комплектация",)),
    ("si_presence", ("Наличия СИ", "Наличие СИ")),
    ("missing_ukz_act", ("Акт об отсутствии УКЗ",)),
    ("ukz_condition", ("Состояние УКЗ",)),
    ("serial_number", ("Серийный номер",)),
    ("defect_description", ("Описание дефектов",)),
    ("photo_link", ("Ссылка на фото",)),
    ("service_conclusion", ("Заключение СЦ",)),
    ("defect_type", ("Вид брака",)),
    ("product_kind", ("Вид номенклатуры",)),
    ("weight", ("Вес",)),
    ("supplier_name", ("последний поставщик", "Последний поставщик")),
    ("from_processing_cell", ("Товар из ячейки обработки",)),
    ("work_type", ("Вид работы",)),
    ("source_manager", ("Менеджер",)),
)

# Только операционные поля можно менять в приложении. Идентификатор БК,
# стоимость и справочные атрибуты остаются данными источника/остатков.
HANDLER_EDIT_FIELDS = (
    ("processor", "Обработчик"),
    ("processed_at", "Дата и время"),
    ("condition_name", "Состояние"),
    ("factory_packaging", "Заводская упаковка"),
    ("instruction_name", "Инструкция"),
    ("manufacturer_warranty", "ГТ производителя"),
    ("completeness", "Комплектация"),
    ("si_presence", "Наличие СИ"),
    ("missing_ukz_act", "Акт об отсутствии УКЗ"),
    ("ukz_condition", "Состояние УКЗ"),
    ("serial_number", "Серийный номер"),
    ("defect_description", "Описание дефектов"),
    ("photo_link", "Ссылка на фото"),
    ("service_conclusion", "Заключение СЦ"),
    ("defect_type", "Вид брака"),
    ("from_processing_cell", "Товар из ячейки обработки"),
    ("work_type", "Вид работы"),
)
HANDLER_EDIT_FIELD_NAMES = {field for field, _label in HANDLER_EDIT_FIELDS}


def _handler_header(value: object) -> str:
    return " ".join((_text(value) or "").split())


def external_unit_key(value: object) -> str | None:
    """Ключ физической БК-единицы, например 8027747-БК-1233788."""
    source = _text(value)
    if not source:
        return None
    match = _EXTERNAL_UNIT_RE.search(source)
    if match:
        return re.sub(r"\s+", "", match.group(1)).upper()
    prefix = source.split(",", 1)[0].strip()
    return prefix.upper() if "-" in prefix and len(prefix) <= 80 else None


def _handler_decimal(value: object, scale: str) -> Decimal | None:
    if value is None:
        return None
    source = str(value).strip().replace("\xa0", "").replace(" ", "")
    if not source or source.startswith("="):
        return None
    try:
        return Decimal(source.replace(",", ".")).quantize(Decimal(scale))
    except InvalidOperation:
        return None


def _handler_datetime(value: object) -> tuple[datetime | None, str | None]:
    if value is None or str(value).strip() == "":
        return None, None
    raw = str(value).strip()
    if isinstance(value, datetime):
        parsed = value
    elif isinstance(value, date):
        parsed = datetime.combine(value, datetime.min.time())
    else:
        parsed = None
        for pattern in ("%d.%m.%Y %H:%M:%S", "%d.%m.%Y %H:%M", "%d.%m.%Y"):
            try:
                parsed = datetime.strptime(raw, pattern)
                break
            except ValueError:
                pass
        if parsed is None:
            return None, raw
    if parsed.tzinfo is None:
        parsed = parsed.replace(tzinfo=_HANDLER_TZ)
    return parsed, raw


class HandlerDownloadError(ValueError):
    def __init__(self, message: str, *, retryable: bool = False):
        super().__init__(message)
        self.retryable = retryable


def _download_handler_export(url: str, client: httpx.Client | None = None
                             ) -> tuple[bytes, str]:
    """Скачать фиксированный экспорт Google Sheets с ограничением размера."""
    own_client = client is None
    if client is None:
        client = httpx.Client(
            follow_redirects=True,
            timeout=httpx.Timeout(45.0, connect=15.0),
            headers={"User-Agent": "OOuRC defect handler sync/1.0"},
        )
    try:
        try:
            with client.stream("GET", url) as response:
                response.raise_for_status()
                content_type = response.headers.get("content-type", "").lower()
                chunks: list[bytes] = []
                size = 0
                for chunk in response.iter_bytes(1024 * 1024):
                    size += len(chunk)
                    if size > HANDLER_EXPORT_MAX_BYTES:
                        raise ValueError("Экспорт Google Sheets больше 50 МБ")
                    chunks.append(chunk)
        except httpx.TimeoutException as exc:
            raise HandlerDownloadError(
                "Google Sheets не передал экспорт за отведённое время. "
                "Синхронизация не выполнена, данные обработчика не изменялись.",
                retryable=True) from exc
        except httpx.HTTPStatusError as exc:
            status = exc.response.status_code
            if status in {401, 403}:
                message = "Google Sheets отказал в доступе к экспорту. Проверьте доступ к таблице для приложения."
            elif status == 404:
                message = "Google Sheets не нашёл таблицу обработчика. Проверьте ссылку на источник."
            elif status == 429:
                message = "Google временно ограничил число запросов к таблице. Повторите синхронизацию позже."
            else:
                message = f"Google Sheets вернул ошибку экспорта (HTTP {status}). Данные обработчика не изменялись."
            raise HandlerDownloadError(message, retryable=status == 429 or status >= 500) from exc
        except httpx.HTTPError as exc:
            raise HandlerDownloadError(
                "Соединение с Google Sheets прервалось при скачивании экспорта. "
                "Данные обработчика не изменялись.", retryable=True) from exc
    finally:
        if own_client:
            client.close()
    data = b"".join(chunks)
    if not data:
        raise ValueError("Google Sheets вернул пустой файл")
    return data, content_type


def download_handler_xlsx(client: httpx.Client | None = None) -> bytes:
    """Скачать воспроизводимый XLSX-снимок рабочего Google-обработчика.

    Адрес фиксирован в приложении: пользовательский URL сюда не попадает.
    Это одновременно исключает SSRF и не позволяет случайно синхронизировать
    другой файл. Перед импортом дополнительно проверяются размер и ZIP-сигнатура
    XLSX — страница входа Google не должна попасть в парсер как таблица.
    """
    data, content_type = _download_handler_export(HANDLER_EXPORT_URL, client)
    if not data.startswith(b"PK"):
        if "text/html" in content_type:
            raise ValueError(
                "Google Sheets запросил вход. Откройте доступ к файлу по ссылке "
                "для пользователей приложения."
            )
        raise ValueError("Вместо XLSX Google Sheets вернул файл другого формата")
    return data


def download_handler_csv(client: httpx.Client | None = None) -> bytes:
    """Скачать только вкладку «ОБРАБОТКА»; быстрее полного XLSX-книги."""
    # Retry the original export format. Visualization exports infer column types
    # and can silently erase text amounts or mixed serial numbers.
    for index, url in enumerate((HANDLER_CSV_EXPORT_URL, HANDLER_CSV_RETRY_URL)):
        try:
            data, content_type = _download_handler_export(url, client)
            return _validate_handler_csv(data, content_type)
        except HandlerDownloadError as exc:
            if not exc.retryable or index:
                raise
    raise AssertionError("No handler export endpoint")


def _validate_handler_csv(data: bytes, content_type: str) -> bytes:
    if "text/html" in content_type or data.lstrip().startswith(b"<"):
        raise ValueError(
            "Google Sheets запросил вход. Откройте доступ к файлу по ссылке "
            "для пользователей приложения."
        )
    try:
        first_line = data.decode("utf-8-sig", errors="strict").splitlines()[0]
    except (UnicodeDecodeError, IndexError) as exc:
        raise ValueError("Вместо CSV Google Sheets вернул файл другого формата") from exc
    if "Номенклатура" not in first_line or "Обработчик" not in first_line:
        raise ValueError("В экспорте Google Sheets не найдена шапка обработчика")
    return data


def _parse_handler_rows(data: bytes, filename: str,
                        source_headers: tuple[object, ...] | list[object],
                        source_rows: Any) -> dict[str, Any]:
    """Общая нормализация строк XLSX и прямого CSV-экспорта."""
    normalized = [_handler_header(value) for value in source_headers]
    ix: dict[str, int] = {}
    missing: list[str] = []
    for key, aliases in _HANDLER_COLUMNS:
        found = next((normalized.index(alias) for alias in aliases if alias in normalized), None)
        if found is None:
            missing.append(aliases[0])
        else:
            ix[key] = found
    if missing:
        raise ValueError("На вкладке «ОБРАБОТКА» не хватает колонок: " + ", ".join(missing))

    rows: list[dict[str, Any]] = []
    invalid_dates = 0
    for row_no, values in source_rows:
        get = lambda key: values[ix[key]] if ix[key] < len(values) else None
        name = _text(get("external_name"))
        if not name or name in {"Формула", "=нет на складе="}:
            continue
        processed_at, processed_raw = _handler_datetime(get("processed_at_raw"))
        if processed_raw and processed_at is None:
            invalid_dates += 1
        row: dict[str, Any] = {
            "source_row": row_no,
            "external_name": name,
            "external_unit_key": external_unit_key(name),
            "unit_cost": _handler_decimal(get("unit_cost"), "0.01"),
            "weight": _handler_decimal(get("weight"), "0.001"),
            "processed_at": processed_at,
            "processed_at_raw": processed_raw,
        }
        for key, _aliases in _HANDLER_COLUMNS:
            if key not in row and key not in {"unit_cost", "weight", "processed_at_raw"}:
                row[key] = _text(get(key))
        fingerprint_values = {
            key: (value.isoformat() if isinstance(value, datetime) else str(value)
                  if isinstance(value, Decimal) else value)
            for key, value in row.items() if key != "source_row"
        }
        row["row_fingerprint"] = hashlib.sha256(
            json.dumps(fingerprint_values, ensure_ascii=False, sort_keys=True)
            .encode("utf-8")).hexdigest()
        rows.append(row)
    if not rows:
        raise ValueError("На вкладке «ОБРАБОТКА» нет обработанных позиций")
    key_counts: dict[str, int] = defaultdict(int)
    for row in rows:
        if row["external_unit_key"]:
            key_counts[row["external_unit_key"]] += 1
    warnings = []
    if invalid_dates:
        warnings.append(
            f"Не удалось распознать дату: {invalid_dates} строк; исходный текст сохранён")
    return {
        "filename": filename,
        "sha256": hashlib.sha256(data).hexdigest(),
        "rows": rows,
        "rows_total": len(rows),
        "unique_unit_keys": len(key_counts),
        "reprocessed_events": sum(count - 1 for count in key_counts.values() if count > 1),
        "warnings": warnings,
    }


def parse_handler_xlsx(data: bytes, filename: str) -> dict[str, Any]:
    """Прочитать лист «ОБРАБОТКА» без изменения исходной таблицы."""
    if not data:
        raise ValueError("Файл пуст")
    try:
        book = load_workbook(BytesIO(data), read_only=True, data_only=True)
    except Exception as exc:
        raise ValueError("Не удалось прочитать XLSX-файл обработчика") from exc
    try:
        if HANDLER_SHEET not in book.sheetnames:
            raise ValueError(f"В файле нет вкладки «{HANDLER_SHEET}»")
        sheet = book[HANDLER_SHEET]
        source_headers = next(
            sheet.iter_rows(min_row=1, max_row=1, values_only=True))
        source_rows = enumerate(
            sheet.iter_rows(min_row=3, values_only=True), start=3)
        return _parse_handler_rows(
            data, filename, source_headers, source_rows)
    finally:
        book.close()


def parse_handler_csv(data: bytes, filename: str) -> dict[str, Any]:
    """Прочитать прямой CSV-экспорт одной вкладки «ОБРАБОТКА»."""
    if not data:
        raise ValueError("Файл пуст")
    try:
        reader = csv.reader(StringIO(data.decode("utf-8-sig"), newline=""))
        source_headers = next(reader)
        next(reader, None)  # служебная строка формул
        return _parse_handler_rows(
            data, filename, source_headers, enumerate(reader, start=3))
    except UnicodeDecodeError as exc:
        raise ValueError("Не удалось прочитать CSV-файл обработчика") from exc


def _current_handler_links(conn: psycopg.Connection) -> tuple[dict[str, set[int]],
                                                               dict[str, set[int]],
                                                               dict[int, int | None]]:
    """Текущие товары по полному имени/БК-ключу и однозначный менеджер."""
    with conn.cursor() as cur:
        cur.execute(
            """SELECT p.id, p.source_name, l.manager_id
                 FROM app.defect_stock_line l
                 JOIN app.defect_stock_import i
                   ON i.id=l.import_id AND i.is_current
                 JOIN app.defect_product p ON p.id=l.product_id""")
        source = cur.fetchall()
    by_name: dict[str, set[int]] = defaultdict(set)
    by_key: dict[str, set[int]] = defaultdict(set)
    managers: dict[int, set[int]] = defaultdict(set)
    for row in source:
        by_name[row["source_name"]].add(row["id"])
        unit_key = external_unit_key(row["source_name"])
        if unit_key:
            by_key[unit_key].add(row["id"])
        if row["manager_id"] is not None:
            managers[row["id"]].add(row["manager_id"])
    one_manager = {
        product_id: next(iter(ids)) if len(ids) == 1 else None
        for product_id, ids in managers.items()
    }
    return by_name, by_key, one_manager


def _handler_match(name: str, unit_key: str | None,
                   by_name: dict[str, set[int]], by_key: dict[str, set[int]]) -> tuple[str, int | None]:
    exact = by_name.get(name, set())
    if len(exact) == 1:
        return "exact_current", next(iter(exact))
    keyed = by_key.get(unit_key, set()) if unit_key else set()
    if len(keyed) == 1:
        return "unit_key_current", next(iter(keyed))
    if len(exact) > 1 or len(keyed) > 1:
        return "ambiguous_current", None
    return "not_in_current_stock", None


def reconcile_handler_events(conn: psycopg.Connection) -> dict[str, int]:
    """Пересчитать связь истории обработчика с последним снимком остатков."""
    with conn.cursor() as cur:
        cur.execute("SELECT to_regclass('app.defect_handler_event') AS table_name")
        if cur.fetchone()["table_name"] is None:
            return {}
        cur.execute(
            """SELECT id, external_name, external_unit_key
                 FROM app.defect_handler_event""")
        events = cur.fetchall()
    if not events:
        return {key: 0 for key in HANDLER_MATCH_LABELS}
    by_name, by_key, managers = _current_handler_links(conn)
    changes = []
    counts: dict[str, int] = defaultdict(int)
    for event in events:
        status, product_id = _handler_match(
            event["external_name"], event["external_unit_key"], by_name, by_key)
        counts[status] += 1
        changes.append((status, product_id,
                        managers.get(product_id) if product_id else None,
                        event["id"]))
    # Один COPY + один UPDATE заметно быстрее десятков тысяч UPDATE по одному.
    # Заодно updated_at меняется только у реально пересопоставленных строк.
    with conn.cursor() as cur:
        cur.execute("""CREATE TEMP TABLE handler_reconcile_stage (
                         match_status text NOT NULL,
                         product_id bigint,
                         current_manager_id bigint,
                         event_id bigint PRIMARY KEY
                       ) ON COMMIT DROP""")
        with cur.copy(
                "COPY handler_reconcile_stage "
                "(match_status, product_id, current_manager_id, event_id) "
                "FROM STDIN") as copy:
            for change in changes:
                copy.write_row(change)
        cur.execute("""UPDATE app.defect_handler_event e
                          SET match_status=s.match_status,
                              product_id=s.product_id,
                              current_manager_id=s.current_manager_id,
                              updated_at=now()
                         FROM handler_reconcile_stage s
                        WHERE e.id=s.event_id
                          AND (e.match_status, e.product_id, e.current_manager_id)
                              IS DISTINCT FROM
                              (s.match_status, s.product_id,
                               s.current_manager_id)""")
    return dict(counts)


def import_handler_xlsx(conn: psycopg.Connection, data: bytes, filename: str,
                        user_id: int | None,
                        source_modified_at: datetime | None = None) -> dict[str, Any]:
    """Импортировать/обновить исторические строки обработчика по номеру строки."""
    return _import_handler_parsed(
        conn, parse_handler_xlsx(data, filename), user_id, source_modified_at)


def import_handler_csv(conn: psycopg.Connection, data: bytes, filename: str,
                       user_id: int | None,
                       source_modified_at: datetime | None = None) -> dict[str, Any]:
    """Импортировать прямой CSV-экспорт вкладки «ОБРАБОТКА»."""
    return _import_handler_parsed(
        conn, parse_handler_csv(data, filename), user_id, source_modified_at)


def _import_handler_parsed(conn: psycopg.Connection, parsed: dict[str, Any],
                           user_id: int | None,
                           source_modified_at: datetime | None = None
                           ) -> dict[str, Any]:
    with conn.cursor() as cur:
        cur.execute(
            """SELECT * FROM app.defect_handler_import
                WHERE source_spreadsheet_id=%s AND file_sha256=%s""",
            (HANDLER_SPREADSHEET_ID, parsed["sha256"]))
        existing_import = cur.fetchone()
        if existing_import:
            return {
                **{key: value for key, value in parsed.items() if key != "rows"},
                "unchanged": True,
                "id": existing_import["id"],
                "inserted_events": existing_import["inserted_events"],
                "updated_events": existing_import["updated_events"],
                "unchanged_events": existing_import["unchanged_events"],
                "matched_current": existing_import["matched_current"],
                "not_in_current": existing_import["not_in_current"],
                "ambiguous_current": existing_import["ambiguous_current"],
            }
        cur.execute(
            """INSERT INTO app.defect_handler_import
                 (filename, file_sha256, source_spreadsheet_id, source_title,
                  source_sheet, source_modified_at, uploaded_by, rows_total, warnings)
               VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s)
               RETURNING id""",
            (parsed["filename"], parsed["sha256"], HANDLER_SPREADSHEET_ID,
             HANDLER_SPREADSHEET_TITLE, HANDLER_SHEET, source_modified_at,
             user_id, parsed["rows_total"],
             json.dumps(parsed["warnings"], ensure_ascii=False)))
        import_id = cur.fetchone()["id"]
        cur.execute(
            """SELECT source_row, row_fingerprint
                 FROM app.defect_handler_event
                WHERE source_spreadsheet_id=%s AND source_sheet=%s""",
            (HANDLER_SPREADSHEET_ID, HANDLER_SHEET))
        existing_rows = {row["source_row"]: row["row_fingerprint"]
                         for row in cur.fetchall()}
        cur.execute(
            """UPDATE app.defect_handler_event SET is_source_present=false
                WHERE source_spreadsheet_id=%s AND source_sheet=%s""",
            (HANDLER_SPREADSHEET_ID, HANDLER_SHEET))

    inserted = sum(row["source_row"] not in existing_rows for row in parsed["rows"])
    updated = sum(
        row["source_row"] in existing_rows
        and existing_rows[row["source_row"]] != row["row_fingerprint"]
        for row in parsed["rows"])
    unchanged = parsed["rows_total"] - inserted - updated
    fields = [
        "external_name", "external_unit_key", "unit_cost", "classification",
        "processor", "source_description", "processed_at", "processed_at_raw",
        "condition_name", "factory_packaging", "instruction_name",
        "manufacturer_warranty", "completeness", "si_presence",
        "missing_ukz_act", "ukz_condition", "serial_number",
        "defect_description", "photo_link", "service_conclusion", "defect_type",
        "product_kind", "weight", "supplier_name", "from_processing_cell",
        "work_type", "source_manager",
    ]
    updates = ", ".join(
        (f"{field}=CASE WHEN '{field}'=ANY("
         "defect_handler_event.manual_override_fields) "
         f"THEN defect_handler_event.{field} ELSE excluded.{field} END")
        if field in HANDLER_EDIT_FIELD_NAMES or field == "processed_at_raw"
        else f"{field}=excluded.{field}"
        for field in fields)
    stage_columns = [
        "source_spreadsheet_id", "source_sheet", "source_row",
        "row_fingerprint", *fields, "first_import_id", "last_import_id",
        "is_source_present",
    ]
    stage_columns_sql = ", ".join(stage_columns)
    sql = f"""INSERT INTO app.defect_handler_event ({stage_columns_sql})
      SELECT {stage_columns_sql} FROM defect_handler_event_stage
      ON CONFLICT (source_spreadsheet_id, source_sheet, source_row) DO UPDATE SET
        row_fingerprint=excluded.row_fingerprint, {updates},
        last_import_id=excluded.last_import_id, is_source_present=true,
        updated_at=now()"""
    with conn.cursor() as cur:
        cur.execute(
            f"CREATE TEMP TABLE defect_handler_event_stage AS "
            f"SELECT {stage_columns_sql} FROM app.defect_handler_event "
            "WITH NO DATA")
        with cur.copy(
                f"COPY defect_handler_event_stage ({stage_columns_sql}) "
                "FROM STDIN") as copy:
            for row in parsed["rows"]:
                copy.write_row((
                    HANDLER_SPREADSHEET_ID, HANDLER_SHEET, row["source_row"],
                    row["row_fingerprint"],
                    *(row[field] for field in fields),
                    import_id, import_id, True,
                ))
        cur.execute(sql)
    status_counts = reconcile_handler_events(conn)
    matched = (status_counts.get("exact_current", 0)
               + status_counts.get("unit_key_current", 0))
    not_current = status_counts.get("not_in_current_stock", 0)
    ambiguous = status_counts.get("ambiguous_current", 0)
    with conn.cursor() as cur:
        cur.execute(
            """UPDATE app.defect_handler_import
                  SET inserted_events=%s, updated_events=%s, unchanged_events=%s,
                      matched_current=%s, not_in_current=%s, ambiguous_current=%s
                WHERE id=%s""",
            (inserted, updated, unchanged, matched, not_current, ambiguous, import_id))
    conn.commit()
    return {
        **{key: value for key, value in parsed.items() if key != "rows"},
        "id": import_id, "unchanged": False,
        "inserted_events": inserted, "updated_events": updated,
        "unchanged_events": unchanged, "matched_current": matched,
        "not_in_current": not_current, "ambiguous_current": ambiguous,
    }


def handler_import_history(conn: psycopg.Connection,
                           limit: int = 10) -> list[dict[str, Any]]:
    with conn.cursor() as cur:
        cur.execute(
            """SELECT i.*, u.full_name AS uploaded_by_name
                 FROM app.defect_handler_import i
                 LEFT JOIN app.app_user u ON u.id=i.uploaded_by
                ORDER BY i.loaded_at DESC LIMIT %s""", (limit,))
        return cur.fetchall()


def handler_stock_search(conn: psycopg.Connection, query: str, user_id: int,
                         limit: int = 20) -> list[dict[str, Any]]:
    """Найти конкретные строки последнего снимка остатков.

    Выбор идёт по stock_line, а не по модели: один товар может
    одновременно лежать на нескольких складах. Порог два символа
    одинаков для клавиатуры и сканера.
    """
    text = query.strip()
    if len(text) < 2:
        return []
    pattern = f"%{text}%"
    limit = max(1, min(limit, 20))
    unit_key = external_unit_key(text)
    with conn.cursor() as cur:
        cur.execute(
            """SELECT l.id AS stock_line_id, p.id AS product_id,
                      p.source_code, p.search_code, p.article, p.name,
                      p.source_name, p.brand, w.source_name AS warehouse,
                      l.quantity, l.unit_cost, l.defect_description,
                      l.service_conclusion, m.name AS manager,
                      k.name AS product_kind, c.name AS classification,
                      s.name AS supplier,
                      coalesce(hist.event_count, 0) AS event_count,
                      draft.event_id AS own_draft_event_id
                 FROM app.defect_stock_import i
                 JOIN app.defect_stock_line l ON l.import_id=i.id
                 JOIN app.defect_product p ON p.id=l.product_id
                 JOIN app.defect_warehouse w ON w.id=l.warehouse_id
                 LEFT JOIN app.defect_manager m ON m.id=l.manager_id
                 LEFT JOIN app.defect_product_kind k ON k.id=l.product_kind_id
                 LEFT JOIN app.defect_classification c ON c.id=l.classification_id
                 LEFT JOIN app.defect_supplier s ON s.id=l.supplier_id
                 LEFT JOIN LATERAL (
                   SELECT count(*) AS event_count
                     FROM app.defect_handler_event e
                    WHERE e.is_source_present
                      AND (e.product_id=p.id OR
                           (%s::text IS NOT NULL AND e.external_unit_key=%s::text))
                 ) hist ON true
                 LEFT JOIN LATERAL (
                   SELECT e.id AS event_id
                     FROM app.defect_handler_event e
                    WHERE e.created_by=%s AND e.workflow_status='draft'
                      AND e.is_source_present
                      AND (e.source_stock_line_id=l.id OR e.product_id=p.id)
                    ORDER BY e.created_at DESC LIMIT 1
                 ) draft ON true
                WHERE i.is_current
                  AND (p.source_code ILIKE %s
                       OR coalesce(p.search_code, '') ILIKE %s
                       OR coalesce(p.article, '') ILIKE %s
                       OR p.source_name ILIKE %s
                       OR p.name ILIKE %s
                       OR EXISTS (
                         SELECT 1 FROM app.defect_product_barcode b
                          WHERE b.product_id=p.id AND b.barcode ILIKE %s))
                ORDER BY
                  CASE WHEN p.source_code=%s OR p.search_code=%s
                         OR p.article=%s THEN 0 ELSE 1 END,
                  p.source_code, w.source_name, l.id
                LIMIT %s""",
            (unit_key, unit_key, user_id,
             pattern, pattern, pattern, pattern, pattern, pattern,
             text, text, text, limit))
        return cur.fetchall()


def create_handler_event(conn: psycopg.Connection, stock_line_id: int,
                         user_id: int, actor_name: str | None = None) -> int:
    """Создать черновик и зафиксировать данные остатка на этот момент."""
    with conn.cursor() as cur:
        cur.execute(
            """SELECT l.id AS stock_line_id, p.id AS product_id,
                      p.source_name, p.name, p.weight,
                      w.source_name AS warehouse, l.unit_cost,
                      l.defect_description, l.service_conclusion,
                      m.id AS manager_id, m.name AS manager,
                      k.name AS product_kind, c.name AS classification,
                      s.name AS supplier
                 FROM app.defect_stock_import i
                 JOIN app.defect_stock_line l ON l.import_id=i.id
                 JOIN app.defect_product p ON p.id=l.product_id
                 JOIN app.defect_warehouse w ON w.id=l.warehouse_id
                 LEFT JOIN app.defect_manager m ON m.id=l.manager_id
                 LEFT JOIN app.defect_product_kind k ON k.id=l.product_kind_id
                 LEFT JOIN app.defect_classification c ON c.id=l.classification_id
                 LEFT JOIN app.defect_supplier s ON s.id=l.supplier_id
                WHERE i.is_current AND l.id=%s""", (stock_line_id,))
        row = cur.fetchone()
        if not row:
            raise ValueError(
                "Выбранная позиция уже не относится к текущим остаткам. "
                "Выполните поиск ещё раз.")
        cur.execute(
            "SELECT nextval('app.defect_handler_local_row_seq') AS source_row")
        source_row = cur.fetchone()["source_row"]

        unit_key = external_unit_key(row["source_name"])
        snapshot = {
            "stock_line_id": row["stock_line_id"],
            "product_id": row["product_id"],
            "source_name": row["source_name"],
            "warehouse": row["warehouse"],
            "unit_cost": str(row["unit_cost"]),
            "manager": row["manager"],
        }
        fingerprint = hashlib.sha256(
            json.dumps(snapshot, ensure_ascii=False, sort_keys=True)
            .encode("utf-8")).hexdigest()
        cur.execute(
            """INSERT INTO app.defect_handler_event
                 (source_spreadsheet_id, source_sheet, source_row,
                  row_fingerprint, external_name, external_unit_key,
                  product_id, current_manager_id, match_status, unit_cost,
                  classification, processor, source_description,
                  service_conclusion, product_kind, weight, supplier_name,
                  source_manager, is_source_present, workflow_status,
                  source_origin, source_stock_line_id, source_warehouse,
                  created_by, processor_user_id)
               VALUES
                 ('local-app', 'LOCAL', %s, %s, %s, %s, %s, %s,
                  'exact_current', %s, %s, %s, %s, %s, %s, %s, %s,
                  %s, true, 'draft', 'local_app', %s, %s, %s, %s)
               RETURNING id""",
            (source_row, fingerprint, row["source_name"], unit_key,
             row["product_id"], row["manager_id"], row["unit_cost"],
             row["classification"], _text(actor_name),
             row["defect_description"], row["service_conclusion"],
             row["product_kind"], row["weight"], row["supplier"],
             row["manager"], row["stock_line_id"], row["warehouse"], user_id,
             user_id))
        event_id = cur.fetchone()["id"]
    conn.commit()
    return event_id


def handler_unit_history(conn: psycopg.Connection, event_id: int,
                         limit: int = 20) -> list[dict[str, Any]]:
    """Другие обработки того же БК-экземпляра или, если ключа нет, товара."""
    with conn.cursor() as cur:
        cur.execute(
            """SELECT h.id, h.processed_at, h.completed_at, h.processor,
                      h.condition_name, h.defect_type, h.defect_description,
                      h.work_type, h.workflow_status, h.source_warehouse
                 FROM app.defect_handler_event e
                 JOIN app.defect_handler_event h
                   ON h.id<>e.id AND h.is_source_present
                  AND ((e.external_unit_key IS NOT NULL
                        AND h.external_unit_key=e.external_unit_key)
                       OR (e.external_unit_key IS NULL
                           AND e.product_id IS NOT NULL
                           AND h.product_id=e.product_id))
                WHERE e.id=%s
                ORDER BY coalesce(h.completed_at, h.processed_at,
                                  h.created_at) DESC
                LIMIT %s""", (event_id, limit))
        return cur.fetchall()


def handler_event(conn: psycopg.Connection, event_id: int) -> dict[str, Any] | None:
    """Карточка события с текущим и фактически назначенным менеджером."""
    with conn.cursor() as cur:
        cur.execute(
            """SELECT e.*, cm.name AS current_manager,
                      am.name AS assigned_manager, p.name AS current_product_name,
                      cu.full_name AS completed_by_name,
                      pu.full_name AS processor_user_name,
                      cr.full_name AS created_by_name,
                      CASE
                        WHEN e.workflow_status='completed' THEN 'processed'
                        WHEN e.workflow_status='draft' THEN 'needs_attention'
                        WHEN e.processed_at IS NOT NULL
                         AND nullif(btrim(coalesce(e.processor,'')), '') IS NOT NULL
                         AND coalesce(nullif(btrim(e.defect_type), ''),
                                      nullif(btrim(e.defect_description), ''),
                                      nullif(btrim(e.work_type), ''),
                                      nullif(btrim(e.service_conclusion), '')) IS NOT NULL
                          THEN 'processed'
                        ELSE 'needs_attention'
                      END AS completion_status
                 FROM app.defect_handler_event e
                 LEFT JOIN app.defect_manager cm ON cm.id=e.current_manager_id
                 LEFT JOIN app.defect_manager am ON am.id=e.assigned_manager_id
                 LEFT JOIN app.defect_product p ON p.id=e.product_id
                 LEFT JOIN app.app_user cu ON cu.id=e.completed_by
                 LEFT JOIN app.app_user pu ON pu.id=e.processor_user_id
                 LEFT JOIN app.app_user cr ON cr.id=e.created_by
                WHERE e.id=%s AND e.deleted_at IS NULL""", (event_id,))
        return cur.fetchone()


def handler_event_edits(conn: psycopg.Connection, event_id: int,
                        limit: int = 30) -> list[dict[str, Any]]:
    with conn.cursor() as cur:
        cur.execute(
            """SELECT h.*, u.full_name AS edited_by_name
                 FROM app.defect_handler_event_edit h
                 JOIN app.app_user u ON u.id=h.edited_by
                WHERE h.event_id=%s
                ORDER BY h.edited_at DESC LIMIT %s""", (event_id, limit))
        return cur.fetchall()


def handler_edit_options(conn: psycopg.Connection) -> dict[str, list[str]]:
    """Только активные значения управляемых справочников."""
    result: dict[str, list[str]] = {}
    with conn.cursor() as cur:
        for field in HANDLER_OPTION_FIELDS:
            cur.execute(
                """SELECT value FROM app.defect_handler_option
                    WHERE field_key=%s AND is_active
                    ORDER BY sort_order, value LIMIT 300""", (field,))
            result[field] = [row["value"] for row in cur.fetchall()]
        cur.execute("SELECT id, name FROM app.defect_manager ORDER BY name")
        result["managers"] = cur.fetchall()
    return result


def handler_user_options(conn: psycopg.Connection) -> list[dict[str, Any]]:
    """Действующие учётные записи для назначения обработчика администратором."""
    with conn.cursor() as cur:
        cur.execute(
            """SELECT id, full_name, email
                 FROM app.app_user
                WHERE is_active
                ORDER BY full_name, email""")
        return cur.fetchall()


def handler_options_admin(conn: psycopg.Connection) -> list[dict[str, Any]]:
    with conn.cursor() as cur:
        cur.execute(
            """SELECT o.*, cu.full_name AS created_by_name,
                      uu.full_name AS updated_by_name
                 FROM app.defect_handler_option o
                 LEFT JOIN app.app_user cu ON cu.id=o.created_by
                 LEFT JOIN app.app_user uu ON uu.id=o.updated_by
                ORDER BY o.field_key, o.sort_order, o.value""")
        return cur.fetchall()


def save_handler_option(conn: psycopg.Connection, user_id: int,
                        field_key: str, value: str, sort_order: int = 100,
                        is_active: bool = True,
                        option_id: int | None = None) -> int:
    if field_key not in HANDLER_OPTION_FIELDS:
        raise ValueError("Неизвестный справочник")
    clean = _text(value)
    if not clean:
        raise ValueError("Значение справочника не может быть пустым")
    sort_order = max(-10000, min(int(sort_order), 10000))
    with conn.cursor() as cur:
        if option_id is None:
            cur.execute(
                """INSERT INTO app.defect_handler_option
                     (field_key, value, sort_order, is_active, created_by, updated_by)
                   VALUES (%s,%s,%s,%s,%s,%s)
                   RETURNING id""",
                (field_key, clean, sort_order, is_active, user_id, user_id))
        else:
            cur.execute(
                """UPDATE app.defect_handler_option
                      SET field_key=%s, value=%s, sort_order=%s, is_active=%s,
                          updated_by=%s, updated_at=now()
                    WHERE id=%s RETURNING id""",
                (field_key, clean, sort_order, is_active, user_id, option_id))
        row = cur.fetchone()
        if not row:
            raise ValueError("Значение справочника не найдено")
    conn.commit()
    return row["id"]


def _audit_value(value: Any) -> Any:
    if isinstance(value, datetime):
        return value.isoformat()
    if isinstance(value, Decimal):
        return str(value)
    return value


def update_handler_event(conn: psycopg.Connection, event_id: int, user_id: int,
                         values: dict[str, str], assigned_manager: str | None,
                         assignment_note: str | None, save_mode: str = "draft",
                         actor_name: str | None = None,
                         processor_user_id: int | None = None) -> bool:
    """Сохранить ручные изменения и защитить их от следующего импорта."""
    event = handler_event(conn, event_id)
    if not event:
        raise ValueError("Позиция обработчика не найдена")
    if save_mode not in {"draft", "complete"}:
        raise ValueError("Неизвестный режим сохранения")
    if event.get("deleted_at") is not None:
        raise ValueError("Этот черновик удалён")

    processor_name = _text(actor_name)
    if processor_user_id is not None:
        with conn.cursor() as cur:
            cur.execute(
                "SELECT full_name FROM app.app_user WHERE id=%s AND is_active",
                (processor_user_id,))
            selected_user = cur.fetchone()
        if not selected_user:
            raise ValueError("Выбранный обработчик не найден или отключён")
        processor_name = selected_user["full_name"]
    if processor_name:
        values["processor"] = processor_name

    parsed_values: dict[str, Any] = {}
    for field, _label in HANDLER_EDIT_FIELDS:
        raw = values.get(field, "")
        if field == "processed_at":
            raw = raw.strip()
            if raw:
                try:
                    parsed = datetime.strptime(raw, "%Y-%m-%dT%H:%M")
                except ValueError as exc:
                    raise ValueError("Неверная дата и время обработки") from exc
                parsed_values[field] = parsed.replace(tzinfo=_HANDLER_TZ)
            else:
                parsed_values[field] = None
        else:
            parsed_values[field] = _text(raw)

    if save_mode == "complete":
        required = (
            ("condition_name", "Состояние"),
            ("defect_type", "Вид брака"),
            ("defect_description", "Описание дефектов"),
            ("work_type", "Вид работы"),
        )
        missing = [label for field, label in required
                   if not parsed_values.get(field)]
        if missing:
            raise ValueError(
                "Для завершения заполните: " + ", ".join(missing))
        parsed_values["processor"] = (
            parsed_values.get("processor") or _text(actor_name) or "Сотрудник")
        parsed_values["processed_at"] = (
            parsed_values.get("processed_at") or datetime.now(_HANDLER_TZ))

    manager_id = None
    if assigned_manager and assigned_manager.strip():
        try:
            manager_id = int(assigned_manager)
        except ValueError as exc:
            raise ValueError("Неверный менеджер") from exc
        with conn.cursor() as cur:
            cur.execute("SELECT 1 FROM app.defect_manager WHERE id=%s", (manager_id,))
            if not cur.fetchone():
                raise ValueError("Менеджер не найден")
    note = _text(assignment_note)

    changed: dict[str, Any] = {}
    before: dict[str, Any] = {}
    after: dict[str, Any] = {}
    override_fields = set(event.get("manual_override_fields") or [])
    for field, _label in HANDLER_EDIT_FIELDS:
        old = event.get(field)
        new = parsed_values[field]
        if old != new:
            changed[field] = new
            before[field] = _audit_value(old)
            after[field] = _audit_value(new)
            override_fields.add(field)
            if field == "processed_at":
                changed["processed_at_raw"] = (
                    new.astimezone(_HANDLER_TZ).strftime("%d.%m.%Y %H:%M:%S")
                    if new else None)
                override_fields.add("processed_at_raw")

    if event.get("processor_user_id") != processor_user_id:
        before["processor_user_id"] = event.get("processor_user_id")
        after["processor_user_id"] = processor_user_id
        changed["processor_user_id"] = processor_user_id

    if save_mode == "complete":
        target_status = "completed"
    elif event.get("workflow_status") == "completed":
        target_status = "completed"
    elif (event.get("workflow_status") == "imported"
          and event.get("completion_status") == "processed"):
        # Правка уже обработанной исторической строки не делает
        # её черновиком. Новую повторную обработку позже создаст
        # отдельное событие, а не перезапись этого.
        target_status = "imported"
    else:
        target_status = "draft"
    if event.get("workflow_status") != target_status:
        before["workflow_status"] = event.get("workflow_status")
        after["workflow_status"] = target_status
        changed["workflow_status"] = target_status
    if save_mode == "complete":
        completed_at = datetime.now(_HANDLER_TZ)
        if event.get("completed_by") != user_id:
            before["completed_by"] = event.get("completed_by")
            after["completed_by"] = user_id
            changed["completed_by"] = user_id
        if event.get("completed_at") is None:
            before["completed_at"] = None
            after["completed_at"] = completed_at.isoformat()
            changed["completed_at"] = completed_at
    if event.get("assigned_manager_id") != manager_id:
        before["assigned_manager_id"] = event.get("assigned_manager_id")
        after["assigned_manager_id"] = manager_id
    if event.get("assignment_note") != note:
        before["assignment_note"] = event.get("assignment_note")
        after["assignment_note"] = note
    changed_fields = list(before)
    if not changed_fields:
        return False

    assignments = [f"{field}=%s" for field in changed]
    params: list[Any] = list(changed.values())
    assignments.extend([
        "manual_override_fields=%s", "assigned_manager_id=%s",
        "assignment_note=%s", "edited_by=%s", "edited_at=now()",
        "updated_at=now()",
    ])
    params.extend([sorted(override_fields), manager_id, note, user_id, event_id])
    with conn.cursor() as cur:
        cur.execute(
            f"UPDATE app.defect_handler_event SET {', '.join(assignments)} WHERE id=%s",
            params)
        cur.execute(
            """INSERT INTO app.defect_handler_event_edit
                 (event_id, edited_by, before_data, after_data, changed_fields)
               VALUES (%s,%s,%s::jsonb,%s::jsonb,%s)""",
            (event_id, user_id,
             json.dumps(before, ensure_ascii=False, default=str),
             json.dumps(after, ensure_ascii=False, default=str),
             changed_fields))
    conn.commit()
    return True


def _handler_image_type(data: bytes) -> str:
    if data.startswith(b"\xff\xd8\xff"):
        return "image/jpeg"
    if data.startswith(b"\x89PNG\r\n\x1a\n"):
        return "image/png"
    if data[:4] == b"RIFF" and data[8:12] == b"WEBP":
        return "image/webp"
    raise ValueError("Подойдут только фото JPEG, PNG или WebP")


def handler_photos(conn: psycopg.Connection,
                   event_id: int) -> list[dict[str, Any]]:
    with conn.cursor() as cur:
        cur.execute(
            """SELECT p.id, p.filename, p.mime_type, p.size_bytes,
                      p.uploaded_at, u.full_name AS uploaded_by_name
                 FROM app.defect_handler_photo p
                 LEFT JOIN app.app_user u ON u.id=p.uploaded_by
                WHERE p.event_id=%s ORDER BY p.uploaded_at, p.id""",
            (event_id,))
        return cur.fetchall()


def handler_photo_bytes(conn: psycopg.Connection,
                        photo_id: int) -> dict[str, Any] | None:
    with conn.cursor() as cur:
        cur.execute(
            """SELECT p.*, e.is_source_present
                 FROM app.defect_handler_photo p
                 JOIN app.defect_handler_event e ON e.id=p.event_id
                WHERE p.id=%s""", (photo_id,))
        return cur.fetchone()


def save_handler_photos(conn: psycopg.Connection, event_id: int, user_id: int,
                        files: list[tuple[str, bytes]]) -> int:
    if not files:
        raise ValueError("Фотографии не выбраны")
    if len(files) > 5:
        raise ValueError("За один раз можно загрузить не более 5 фото")
    with conn.cursor() as cur:
        cur.execute(
            """SELECT count(*) AS count
                 FROM app.defect_handler_photo p
                 JOIN app.defect_handler_event e ON e.id=p.event_id
                WHERE p.event_id=%s AND e.deleted_at IS NULL""", (event_id,))
        count = cur.fetchone()["count"]
        if count + len(files) > HANDLER_PHOTO_MAX_COUNT:
            raise ValueError(
                f"В карточке может быть не более {HANDLER_PHOTO_MAX_COUNT} фото")
        prepared = []
        for filename, data in files:
            if not data:
                raise ValueError("Один из файлов пустой")
            if len(data) > HANDLER_PHOTO_MAX_BYTES:
                raise ValueError(
                    f"Фото «{filename}» больше 8 МБ. Уменьшите его перед загрузкой.")
            prepared.append((event_id, _text(filename) or "photo", _handler_image_type(data),
                             len(data), data, user_id))
        cur.executemany(
            """INSERT INTO app.defect_handler_photo
                 (event_id, filename, mime_type, size_bytes, data, uploaded_by)
               VALUES (%s,%s,%s,%s,%s,%s)""", prepared)
    conn.commit()
    return len(prepared)


def delete_handler_photo(conn: psycopg.Connection, photo_id: int,
                         event_id: int) -> bool:
    with conn.cursor() as cur:
        cur.execute(
            "DELETE FROM app.defect_handler_photo WHERE id=%s AND event_id=%s",
            (photo_id, event_id))
        removed = cur.rowcount > 0
    conn.commit()
    return removed


def delete_handler_draft(conn: psycopg.Connection, event_id: int, user_id: int,
                         is_admin: bool, reason: str | None = None) -> None:
    event = handler_event(conn, event_id)
    if not event or not event.get("is_source_present"):
        raise ValueError("Черновик не найден")
    if event.get("source_origin") != "local_app" or event.get("workflow_status") != "draft":
        raise ValueError("Удалить можно только черновик, созданный в приложении")
    if not is_admin and event.get("created_by") != user_id:
        raise ValueError("Можно удалить только свой черновик")
    now = datetime.now(_HANDLER_TZ)
    clean_reason = _text(reason) or "Удалено пользователем"
    with conn.cursor() as cur:
        cur.execute(
            """UPDATE app.defect_handler_event
                  SET is_source_present=false, deleted_at=%s, deleted_by=%s,
                      delete_reason=%s, updated_at=now()
                WHERE id=%s""", (now, user_id, clean_reason, event_id))
        cur.execute(
            """INSERT INTO app.defect_handler_event_edit
                 (event_id, edited_by, before_data, after_data, changed_fields)
               VALUES (%s,%s,%s::jsonb,%s::jsonb,%s)""",
            (event_id, user_id,
             json.dumps({"is_source_present": True}, ensure_ascii=False),
             json.dumps({"is_source_present": False,
                         "deleted_at": now.isoformat(),
                         "delete_reason": clean_reason}, ensure_ascii=False),
             ["is_source_present", "deleted_at", "delete_reason"]))
    conn.commit()


def handler_monitoring(conn: psycopg.Connection, date_from: date,
                       date_to: date) -> dict[str, Any]:
    if date_to < date_from:
        raise ValueError("Конечная дата не может быть раньше начальной")
    if (date_to - date_from).days > 366:
        raise ValueError("Интервал мониторинга не может быть больше 366 дней")
    start = datetime.combine(date_from, datetime.min.time(), _HANDLER_TZ)
    finish = datetime.combine(date_to, datetime.min.time(), _HANDLER_TZ)
    finish = finish + timedelta(days=1)
    params = (start, finish)
    base = """FROM app.defect_handler_event e
              LEFT JOIN app.app_user pu ON pu.id=e.processor_user_id
             WHERE e.is_source_present AND e.deleted_at IS NULL
               AND coalesce(e.completed_at, e.processed_at, e.created_at) >= %s
               AND coalesce(e.completed_at, e.processed_at, e.created_at) < %s"""
    with conn.cursor() as cur:
        cur.execute(
            """SELECT coalesce(pu.full_name, nullif(e.processor,''), 'Не указан') AS processor,
                      e.processor_user_id,
                      count(*) AS total,
                      count(*) FILTER (WHERE e.workflow_status='draft') AS drafts,
                      count(*) FILTER (WHERE e.workflow_status='completed') AS completed,
                      count(*) FILTER (WHERE e.workflow_status='imported') AS imported,
                      count(DISTINCT coalesce(e.external_unit_key,
                                             e.product_id::text)) AS unique_units,
                      coalesce(sum(e.unit_cost), 0) AS cost_total,
                      min(coalesce(e.completed_at, e.processed_at, e.created_at)) AS first_at,
                      max(coalesce(e.completed_at, e.processed_at, e.created_at)) AS last_at,
                      round(avg(extract(epoch FROM (e.completed_at-e.created_at))/60)
                        FILTER (WHERE e.workflow_status='completed'
                                      AND e.completed_at IS NOT NULL)) AS avg_minutes
              """ + base + """
             GROUP BY e.processor_user_id,
                      coalesce(pu.full_name, nullif(e.processor,''), 'Не указан')
             ORDER BY completed DESC, total DESC, processor""", params)
        rows = cur.fetchall()
        cur.execute(
            """SELECT count(*) AS total,
                      count(*) FILTER (WHERE e.workflow_status='draft') AS drafts,
                      count(*) FILTER (WHERE e.workflow_status='completed') AS completed,
                      count(*) FILTER (WHERE e.workflow_status='imported') AS imported,
                      count(DISTINCT coalesce(e.external_unit_key,
                                             e.product_id::text)) AS unique_units,
                      coalesce(sum(e.unit_cost), 0) AS cost_total
              """ + base, params)
        totals = cur.fetchone()
    return {"rows": rows, "totals": totals, "date_from": date_from,
            "date_to": date_to}


def handler_summary(conn: psycopg.Connection) -> dict[str, Any]:
    with conn.cursor() as cur:
        cur.execute(
            """SELECT count(*) FILTER (
                        WHERE is_source_present AND deleted_at IS NULL) AS total,
                      count(DISTINCT external_unit_key) FILTER (
                        WHERE is_source_present AND deleted_at IS NULL
                          AND external_unit_key IS NOT NULL) AS unique_units,
                      (SELECT coalesce(sum(repeated.count - 1), 0)
                         FROM (SELECT count(*)
                                 FROM app.defect_handler_event r
                                WHERE r.is_source_present
                                  AND r.deleted_at IS NULL
                                  AND r.external_unit_key IS NOT NULL
                                GROUP BY r.external_unit_key
                               HAVING count(*) > 1) repeated) AS reprocessed_events,
                      count(*) FILTER (WHERE is_source_present AND deleted_at IS NULL
                        AND match_status IN
                        ('exact_current','unit_key_current')) AS matched_current,
                      count(*) FILTER (WHERE is_source_present AND deleted_at IS NULL
                        AND match_status='not_in_current_stock') AS not_current,
                      count(*) FILTER (WHERE is_source_present AND deleted_at IS NULL
                        AND match_status='ambiguous_current') AS ambiguous,
                      min(processed_at) FILTER (
                        WHERE is_source_present AND deleted_at IS NULL) AS date_min,
                      max(processed_at) FILTER (
                        WHERE is_source_present AND deleted_at IS NULL) AS date_max
                 FROM app.defect_handler_event""")
        return cur.fetchone()


def handler_filters(conn: psycopg.Connection) -> dict[str, list[dict[str, Any]]]:
    with conn.cursor() as cur:
        cur.execute(
            """SELECT processor AS value, count(*) AS count
                 FROM app.defect_handler_event
                WHERE is_source_present AND deleted_at IS NULL
                  AND processor IS NOT NULL
                GROUP BY processor ORDER BY processor""")
        processors = cur.fetchall()
        cur.execute(
            """SELECT m.id, m.name, count(e.id) AS count
                 FROM app.defect_manager m
                 JOIN app.defect_handler_event e
                   ON coalesce(e.assigned_manager_id, e.current_manager_id)=m.id
                WHERE e.is_source_present AND e.deleted_at IS NULL
                GROUP BY m.id, m.name ORDER BY m.name""")
        managers = cur.fetchall()
    return {"handler_processors": processors, "handler_managers": managers}


def handler_search(conn: psycopg.Connection, query: str = "",
                   match_status: str = "", processor: str = "",
                   manager_id: int | None = None,
                   workflow_status: str = "", owner_only: bool = False,
                   user_id: int | None = None, page: int = 1,
                   per_page: int = 100) -> dict[str, Any]:
    text = query.strip()
    pattern = f"%{text}%"
    status = match_status if match_status in HANDLER_MATCH_LABELS else ""
    workflow = (workflow_status
                if workflow_status in HANDLER_WORKFLOW_LABELS else "")
    page = max(1, page)
    per_page = max(20, min(per_page, 200))
    where = """WHERE e.is_source_present AND e.deleted_at IS NULL
                  AND (%s='' OR e.external_name ILIKE %s
                       OR coalesce(e.external_unit_key,'') ILIKE %s
                       OR coalesce(e.defect_description,'') ILIKE %s
                       OR coalesce(e.serial_number,'') ILIKE %s)
                  AND (%s='' OR e.match_status=%s)
                  AND (%s='' OR e.processor=%s)
                  AND (%s::bigint IS NULL OR
                       coalesce(e.assigned_manager_id, e.current_manager_id)=%s::bigint)
                  AND (%s='' OR e.workflow_status=%s)
                  AND (NOT %s::boolean OR e.created_by=%s::bigint)"""
    params = (text, pattern, pattern, pattern, pattern, status, status,
              processor, processor, manager_id, manager_id,
              workflow, workflow, owner_only, user_id)
    joins = """FROM app.defect_handler_event e
                 LEFT JOIN app.defect_manager m
                   ON m.id=coalesce(e.assigned_manager_id, e.current_manager_id)"""
    with conn.cursor() as cur:
        cur.execute("SELECT count(*) AS total " + joins + " " + where, params)
        total = cur.fetchone()["total"]
        pages = max(1, (total + per_page - 1) // per_page)
        page = min(page, pages)
        cur.execute(
            """SELECT e.*, cm.name AS current_manager,
                      am.name AS assigned_manager,
                      coalesce(am.name, cm.name) AS effective_manager,
                      p.name AS current_product_name,
                      (SELECT count(*) FROM app.defect_handler_photo hp
                        WHERE hp.event_id=e.id) AS photo_count,
                      CASE
                        WHEN e.workflow_status='completed' THEN 'processed'
                        WHEN e.workflow_status='draft' THEN 'needs_attention'
                        WHEN e.processed_at IS NOT NULL
                         AND nullif(btrim(coalesce(e.processor,'')), '') IS NOT NULL
                         AND coalesce(nullif(btrim(e.defect_type), ''),
                                      nullif(btrim(e.defect_description), ''),
                                      nullif(btrim(e.work_type), ''),
                                      nullif(btrim(e.service_conclusion), '')) IS NOT NULL
                          THEN 'processed'
                        ELSE 'needs_attention'
                      END AS completion_status
                 FROM app.defect_handler_event e
                 LEFT JOIN app.defect_manager cm ON cm.id=e.current_manager_id
                 LEFT JOIN app.defect_manager am ON am.id=e.assigned_manager_id
                 LEFT JOIN app.defect_product p ON p.id=e.product_id
              """ + where + """
                ORDER BY e.processed_at DESC NULLS LAST, e.source_row DESC
                LIMIT %s OFFSET %s""",
            (*params, per_page, (page - 1) * per_page))
        return {"rows": cur.fetchall(), "total": total, "page": page,
                "pages": pages, "per_page": per_page}
