"""Операционный контур «Рабочего места брака» (РМБ).

Модуль не меняет остатки и не подменяет складской обработчик. Он связывает
завершённую обработку с делом, решением менеджера и исполнительской задачей.
"""

from __future__ import annotations

import hashlib
import csv
import io
import json
import re
from datetime import date, datetime, timedelta
from zoneinfo import ZoneInfo
from decimal import Decimal, InvalidOperation
from typing import Any
from urllib.parse import parse_qs, urlparse

import httpx
import psycopg


ROLE_LABELS = {
    "manager": "Менеджер",
    "specialist": "Специалист",
    "leader": "Руководитель",
}

CASE_STATUS_LABELS = {
    "awaiting_distribution": "Ожидает решения",
    "assigned": "Назначено",
    "in_progress": "В работе",
    "waiting": "Ожидание",
    "manager_decision": "Требуется решение",
    "reprocessing": "Повторная обработка",
    "completed": "Завершено",
    "cancelled": "Отменено",
}

TASK_STATUS_LABELS = {
    "queued": "Общая очередь",
    "claimed": "Принято",
    "in_progress": "В работе",
    "waiting": "Ожидание",
    "rework_requested": "Ожидает дообработки",
    "completed": "Завершено",
    "cancelled": "Отменено",
}

DIRECTION_LABELS = {
    "supplier_return": "Возврат поставщику",
    "service": "Отправка в СЦ",
    "discount": "Уценка",
    "writeoff": "Утилизация",
    "defect_sale": "Продажа брака",
    "sorting": "Разбраковка",
    "completion_request": "Запрос / доукомплектация",
    "other": "Другое",
    "return_new": "Возврат в новые товары",
    "compensation": "Компенсация",
}
TIMING_LABELS = {"decision": "Требует решения", **DIRECTION_LABELS}
DUE_SQL = "(CASE WHEN c.deadline_mode='policy' THEN c.deadline ELSE coalesce(c.deadline,t.due_at) END)"


def timing_policy(conn) -> dict[str, int | None]:
    with conn.cursor() as cur:
        cur.execute("SELECT code,days FROM app.defect_rmb_timing_policy")
        return {r["code"]: r["days"] for r in cur.fetchall()}


def policy_due(policy: dict, code: str, started: date) -> date | None:
    days = policy.get(code)
    return started + timedelta(days=days) if days is not None else None


def recalculate_policy_deadlines(conn, case_id: int | None = None) -> int:
    with conn.cursor() as cur:
        cur.execute("""UPDATE app.defect_rmb_case c SET deadline=
            (coalesce(c.direction_entered_at,c.opened_at) AT TIME ZONE 'Europe/Moscow')::date+p.days,
            updated_at=now(),version=version+1
            FROM app.defect_rmb_timing_policy p
            WHERE p.code=coalesce(c.direction,'decision') AND c.deadline_mode='policy'
              AND c.case_status NOT IN ('completed','cancelled')
              AND (%s::bigint IS NULL OR c.id=%s)
              AND c.deadline IS DISTINCT FROM
                (coalesce(c.direction_entered_at,c.opened_at) AT TIME ZONE 'Europe/Moscow')::date+p.days""",
                    (case_id, case_id))
        count = cur.rowcount
        cur.execute("""UPDATE app.defect_rmb_task t SET due_at=c.deadline,updated_at=now()
            FROM app.defect_rmb_case c WHERE t.id=c.current_task_id AND c.deadline_mode='policy'
            AND c.case_status NOT IN ('completed','cancelled')
            AND t.task_status NOT IN ('completed','cancelled')
            AND (%s::bigint IS NULL OR c.id=%s) AND t.due_at IS DISTINCT FROM c.deadline""",
                    (case_id, case_id))
    return count


def save_timing_policy(conn, user_id: int, values: dict, recalculate: bool = True) -> int:
    parsed = {}
    for code in TIMING_LABELS:
        value = values.get(code)
        raw = "" if value is None else str(value).strip()
        if raw and (not raw.isdecimal() or not 0 <= int(raw) <= 3650):
            raise ValueError("Срок должен быть целым числом от 0 до 3650 календарных дней")
        parsed[code] = int(raw) if raw else None
    old = timing_policy(conn)
    changes = {code: {"before": old.get(code), "after": value}
               for code, value in parsed.items() if old.get(code) != value}
    with conn.cursor() as cur:
        for code, value in parsed.items():
            cur.execute("""UPDATE app.defect_rmb_timing_policy SET days=%s,updated_by=%s,updated_at=now()
                           WHERE code=%s""", (value, user_id, code))
        count = recalculate_policy_deadlines(conn) if recalculate else 0
        cur.execute("INSERT INTO app.defect_rmb_timing_audit(changed_by,changes,recalculated) VALUES(%s,%s,%s)",
                    (user_id, json.dumps(changes), count))
    conn.commit()
    return count


def manager_lane(row: dict) -> str:
    if row["case_status"] in {"completed", "cancelled"}:
        return "archive"
    return row.get("direction") or "decision"


def can_choose_direction(row: dict) -> bool:
    if row["case_status"] in {"completed", "cancelled", "reprocessing"}:
        return False
    if row.get("direction") in {"service", "supplier_return"}:
        return row["case_status"] == "manager_decision"
    return True

PRIORITY_LABELS = {
    "low": "Низкий",
    "normal": "Обычный",
    "high": "Высокий",
    "critical": "Критический",
}

LOCATION_LABELS = {
    "own_warehouse": "Собственный склад",
    "supplier": "У поставщика",
    "service": "Сервисный центр",
    "in_transit": "В пути",
    "external": "Внешнее место",
    "unknown": "Требует сверки",
}

SERVICE_STATUS_LABELS = {
    "SC-01": "Ожидает принятия специалистом",
    "SC-02": "Подготовка к отправке",
    "SC-03": "Ожидает отправки",
    "SC-04": "В пути в сервис",
    "SC-05": "Ожидает подтверждения приёма",
    "SC-06": "Принят сервисным центром",
    "SC-07": "Диагностика / ремонт",
    "SC-08": "Ожидание результата или ответа",
    "SC-09": "Результат сервиса получен",
    "SC-10": "Ожидает обратной отправки",
    "SC-11": "В пути на наш склад",
    "SC-12": "Ожидает складской приёмки",
    "SC-13": "Проверка результата и состояния",
    "SC-14": "Определение дальнейшего направления",
    "SC-15": "Готова к закрытию",
    "SC-16": "Завершена",
    "SC-17": "Приостановлена из-за исключения",
    "SC-18": "Отменена",
}

SERVICE_RESULT_LABELS = {
    "repaired": "Отремонтирован",
    "not_repairable": "Неремонтопригоден",
    "refused": "Отказ в обслуживании",
    "defect_not_confirmed": "Дефект не подтверждён",
    "paid_repair": "Предложен платный ремонт",
    "replacement": "Замена",
    "other": "Другой результат",
}

SUPPLIER_STATUS_LABELS = {
    "SR-01":"Ожидает принятия", "SR-02":"Подготовка обращения",
    "SR-03":"Обращение отправлено", "SR-04":"Ожидание ответа",
    "SR-05":"Возврат согласован", "SR-06":"В возврате отказано",
    "SR-07":"Подготовка документов", "SR-08":"Ожидает передачи",
    "SR-09":"Передан / в пути", "SR-10":"Принят поставщиком",
    "SR-11":"Результат получен, готово к закрытию", "SR-12":"Завершён",
    "SR-13":"Приостановлен", "SR-14":"Отменён",
}
SUPPLIER_OUTCOME_LABELS = {
    "returned":"Товар возвращён поставщику", "compensation":"Получена компенсация",
    "replacement":"Получена замена", "rejected":"Поставщик отказал",
    "other":"Другой результат",
}


def _text(value: object) -> str | None:
    text = str(value or "").strip()
    return text or None


def user_roles(conn: psycopg.Connection, user_id: int) -> set[str]:
    with conn.cursor() as cur:
        cur.execute(
            """SELECT role_code FROM app.defect_rmb_user_role
                WHERE user_id=%s""", (user_id,))
        return {row["role_code"] for row in cur.fetchall()}


def can_manage(roles: set[str]) -> bool:
    return bool(roles & {"manager", "leader"})


def can_execute(roles: set[str]) -> bool:
    return bool(roles & {"specialist", "leader"})


def ensure_case_from_handler(conn: psycopg.Connection, event_id: int,
                             actor_user_id: int) -> int | None:
    """Создать вход РМБ или завершить связанную повторную обработку."""
    with conn.cursor() as cur:
        cur.execute(
            """SELECT r.id, r.case_id, r.task_id, t.assigned_user_id
                 FROM app.defect_rmb_rework_request r
                 JOIN app.defect_rmb_task t ON t.id=r.task_id
                WHERE r.rework_handler_event_id=%s
                  AND r.request_status='pending'
                FOR UPDATE""", (event_id,))
        rework = cur.fetchone()
        if rework:
            task_status = "in_progress" if rework["assigned_user_id"] else "queued"
            case_status = "in_progress" if rework["assigned_user_id"] else "assigned"
            cur.execute(
                """UPDATE app.defect_rmb_rework_request
                      SET request_status='completed', completed_at=now()
                    WHERE id=%s""", (rework["id"],))
            cur.execute(
                """UPDATE app.defect_rmb_task
                      SET task_status=%s, waiting_reason=NULL, updated_at=now(),
                          started_at=CASE WHEN %s='in_progress'
                                          THEN coalesce(started_at, now())
                                          ELSE started_at END
                    WHERE id=%s""", (task_status, task_status, rework["task_id"]))
            cur.execute(
                """UPDATE app.defect_rmb_case c
                      SET case_status=%s,
                          physical_location_text=coalesce(h.source_warehouse,
                                                         c.physical_location_text),
                          location_confirmed_at=coalesce(h.completed_at, now()),
                          updated_at=now(), last_action_at=now(), version=version+1
                     FROM app.defect_handler_event h
                    WHERE c.id=%s AND h.id=%s""",
                (case_status, rework["case_id"], event_id))
            cur.execute(
                """INSERT INTO app.defect_rmb_event
                     (case_id, task_id, event_type, from_status, to_status,
                      actor_user_id, comment, details)
                   VALUES (%s,%s,'rework_completed','reprocessing',%s,%s,
                           'Повторная складская обработка завершена',
                           jsonb_build_object('handler_event_id', %s))""",
                (rework["case_id"], rework["task_id"], case_status,
                 actor_user_id, event_id))
            conn.commit()
            return rework["case_id"]

        cur.execute(
            """SELECT e.id, e.product_id, e.external_unit_key,
                      coalesce(e.assigned_manager_id, e.current_manager_id) AS manager_ref,
                      e.source_warehouse, e.completed_at, e.workflow_status,
                      mm.manager_user_id
                 FROM app.defect_handler_event e
                 LEFT JOIN app.defect_rmb_manager_map mm
                   ON mm.defect_manager_id=coalesce(e.assigned_manager_id,
                                                     e.current_manager_id)
                  AND mm.active_to IS NULL
                WHERE e.id=%s AND e.deleted_at IS NULL""", (event_id,))
        event = cur.fetchone()
        if not event or event["workflow_status"] != "completed":
            return None
        cur.execute(
            """INSERT INTO app.defect_rmb_case
                 (handler_event_id, product_id, external_unit_key,
                  manager_reference_id, manager_user_id,
                  physical_location_type, physical_location_text,
                  location_confirmed_at)
               VALUES (%s,%s,%s,%s,%s,'own_warehouse',%s,%s)
               ON CONFLICT (handler_event_id) DO NOTHING
               RETURNING id""",
            (event_id, event["product_id"], event["external_unit_key"],
             event["manager_ref"], event["manager_user_id"],
             event["source_warehouse"], event["completed_at"]))
        inserted = cur.fetchone()
        if not inserted:
            cur.execute(
                "SELECT id FROM app.defect_rmb_case WHERE handler_event_id=%s",
                (event_id,))
            existing = cur.fetchone()
            return existing["id"] if existing else None
        case_id = inserted["id"]
        recalculate_policy_deadlines(conn, case_id)
        cur.execute(
            """INSERT INTO app.defect_rmb_event
                 (case_id, event_type, to_status, actor_user_id, comment,
                  details)
               VALUES (%s,'case_opened','awaiting_distribution',%s,
                       'Складская обработка завершена; создан вход РМБ',
                       jsonb_build_object('handler_event_id', %s))""",
            (case_id, actor_user_id, event_id))
    conn.commit()
    return case_id


def _visible_rows(conn: psycopg.Connection, user_id: int, view: str,
                  status: str = "", direction: str = "",
                  manager: str = "", specialist: str = "", *,
                  filters: dict | None = None, case_id: int | None = None) -> list[dict[str, Any]]:
    roles = user_roles(conn, user_id)
    leader = "leader" in roles
    params: list[Any] = []
    if view == "manager":
        if not can_manage(roles):
            raise PermissionError("Рабочее место менеджера недоступно")
        scope = "TRUE" if leader else "c.manager_user_id=%s"
        if not leader:
            params.append(user_id)
    elif view == "specialist":
        if not can_execute(roles):
            raise PermissionError("Рабочее место специалиста недоступно")
        if leader:
            scope = "TRUE"
        else:
            scope = """(t.assigned_user_id=%s OR
                         (t.task_status='queued' AND EXISTS (
                            SELECT 1 FROM app.defect_rmb_team_member tm
                             WHERE tm.manager_user_id=t.manager_user_id
                               AND tm.specialist_user_id=%s
                               AND tm.active_to IS NULL)))"""
            params.extend([user_id, user_id])
    else:
        raise ValueError("Неизвестное рабочее место")

    status_sql = ""
    if status and status in CASE_STATUS_LABELS:
        status_sql = " AND c.case_status=%s"
        params.append(status)
    if direction and direction in DIRECTION_LABELS:
        status_sql += " AND c.direction=%s"
        params.append(direction)
    if manager:
        manager_kind, separator, raw_id = manager.partition(":")
        if not separator or manager_kind not in {"ref", "user"}:
            raise ValueError("Неверный фильтр менеджера")
        try:
            manager_id = int(raw_id)
        except ValueError as exc:
            raise ValueError("Неверный фильтр менеджера") from exc
        if manager_id <= 0:
            raise ValueError("Неверный фильтр менеджера")
        column = ("c.manager_reference_id" if manager_kind == "ref"
                  else "c.manager_user_id")
        status_sql += f" AND {column}=%s"
        params.append(manager_id)
    if specialist:
        if specialist == "none":
            status_sql += " AND t.assigned_user_id IS NULL"
        else:
            try:
                specialist_id = int(specialist)
            except ValueError as exc:
                raise ValueError("Неверный фильтр специалиста") from exc
            if specialist_id <= 0:
                raise ValueError("Неверный фильтр специалиста")
            status_sql += " AND t.assigned_user_id=%s"
            params.append(specialist_id)
    extra_sql, extra_params = workspace_filter_sql(filters or {})
    status_sql += extra_sql
    params.extend(extra_params)
    if case_id is not None:
        status_sql += " AND c.id=%s"
        params.append(case_id)
    with conn.cursor() as cur:
        cur.execute(
            f"""SELECT c.*, h.external_name, h.defect_type,
                       h.defect_description, h.supplier_name, h.unit_cost,
                       h.condition_name, h.factory_packaging, h.completeness,
                       h.instruction_name, h.manufacturer_warranty, h.si_presence,
                       h.ukz_condition, h.missing_ukz_act, h.service_conclusion, h.serial_number,
                       h.source_warehouse, h.completed_at AS handler_completed_at,
                       p.name AS product_name, dm.name AS manager_reference_name,
                       mu.full_name AS manager_name,
                       t.task_status, t.assigned_user_id, t.due_at,
                       t.waiting_reason, t.result_text, t.instruction AS task_instruction,
                       t.external_action_confirmed, t.external_system,
                       t.external_action_confirmed_at,
                       ecu.full_name AS external_action_confirmed_by_name,
                       sc.service_center, sc.service_status,
                       sc.expected_result_at, sc.sent_at,
                       sc.accepted_by_service_at, sc.last_contact_at,
                       sc.result_received_at, sc.return_sent_at,
                       sc.warehouse_received_at, sc.result_type AS service_result_type,
                       sc.conclusion AS service_conclusion_text,
                       sc.document_reference AS service_document_reference,
                       extract(day FROM now()-sc.created_at)::int AS service_stage_days,
                       CASE WHEN sc.accepted_by_service_at IS NULL THEN NULL
                            ELSE extract(day FROM coalesce(sc.return_sent_at,now())
                                                  -sc.accepted_by_service_at)::int END
                         AS physically_in_service_days,
                       extract(day FROM now()-coalesce(sc.last_contact_at,
                                                       sc.updated_at))::int
                         AS service_idle_days,
                       sr.supplier_name AS return_supplier_name,
                       sr.return_status, sr.request_sent_at AS return_request_sent_at,
                       sr.response_received_at AS return_response_received_at,
                       sr.approved AS return_approved,
                       sr.documents_ready_at AS return_documents_ready_at,
                       sr.transferred_at AS return_transferred_at,
                       sr.supplier_received_at AS return_supplier_received_at,
                       sr.result_received_at AS return_result_received_at,
                       sr.last_contact_at AS return_last_contact_at,
                       sr.outcome AS return_outcome, sr.conclusion AS return_conclusion,
                       sr.document_reference AS return_document_reference,
                       au.full_name AS assigned_user_name,
                       rr.rework_handler_event_id,
                       extract(day FROM now()-c.opened_at)::int AS age_days,
                       extract(day FROM now()-c.last_action_at)::int AS idle_days,
                       {DUE_SQL} AS effective_due_at,
                       ({DUE_SQL} < current_date
                        AND c.case_status NOT IN ('completed','cancelled')) AS is_overdue
                  FROM app.defect_rmb_case c
                  JOIN app.defect_handler_event h ON h.id=c.handler_event_id
                  LEFT JOIN app.defect_product p ON p.id=c.product_id
                  LEFT JOIN app.defect_manager dm ON dm.id=c.manager_reference_id
                  LEFT JOIN app.app_user mu ON mu.id=c.manager_user_id
                  LEFT JOIN app.defect_rmb_task t ON t.id=c.current_task_id
                  LEFT JOIN app.app_user au ON au.id=t.assigned_user_id
                  LEFT JOIN app.app_user ecu ON ecu.id=t.external_action_confirmed_by
                  LEFT JOIN app.defect_rmb_service sc ON sc.task_id=t.id
                  LEFT JOIN app.defect_rmb_supplier_return sr ON sr.task_id=t.id
                  LEFT JOIN app.defect_rmb_rework_request rr
                    ON rr.case_id=c.id AND rr.request_status='pending'
                 WHERE {scope}{status_sql}
                 ORDER BY
                   ({DUE_SQL} < current_date
                    AND c.case_status NOT IN ('completed','cancelled')) DESC,
                   CASE c.priority WHEN 'critical' THEN 0 WHEN 'high' THEN 1
                        WHEN 'normal' THEN 2 ELSE 3 END,
                   c.last_action_at ASC
                 LIMIT 500""", params)
        return cur.fetchall()


def workspace(conn: psycopg.Connection, user_id: int, view: str,
              status: str = "", direction: str = "", manager: str = "",
              specialist: str = "", *, filters: dict | None = None) -> dict[str, Any]:
    option_rows = _visible_rows(conn, user_id, view, status, direction)
    rows = _visible_rows(conn, user_id, view, status, direction,
                         manager, specialist, filters=filters)
    for row in rows:
        deadline = row.get("effective_due_at")
        row["days_left"] = ((deadline - date.today()).days if deadline else None)
        row["product_code"] = product_code(row.get("external_unit_key"))
        row["manager_lane"] = manager_lane(row)
        row["can_choose_direction"] = can_choose_direction(row)
        row["is_rerouting"] = row["case_status"] in {"assigned", "in_progress", "waiting"}
        started = row.get("direction_entered_at") or row["opened_at"]
        row["stage_days"] = (datetime.now(ZoneInfo("Europe/Moscow")).date() - started.astimezone(ZoneInfo("Europe/Moscow")).date()).days
    active = [r for r in rows if r["case_status"] not in {"completed", "cancelled"}]
    kpis = {
        "total": len(active),
        "awaiting": sum(r["case_status"] == "awaiting_distribution" for r in active),
        "overdue": sum(bool(r["is_overdue"]) for r in active),
        "idle": sum((r["idle_days"] or 0) >= 7 for r in active),
        "service": sum(r["direction"] == "service" for r in active),
        "reprocessing": sum(r["case_status"] == "reprocessing" for r in active),
    }
    with conn.cursor() as cur:
        cur.execute(
            """SELECT u.id, u.full_name, u.email
                 FROM app.app_user u
                 JOIN app.defect_rmb_user_role r
                   ON r.user_id=u.id AND r.role_code IN ('specialist','leader')
                WHERE u.is_active
                GROUP BY u.id, u.full_name, u.email
                ORDER BY u.full_name""")
        specialists = cur.fetchall()
    managers = _manager_filter_options(option_rows)
    policy = timing_policy(conn)
    if view == "manager":
        lanes = [("decision", "Требует решения")]
        lanes += [(code, label) for code, label in DIRECTION_LABELS.items()
                  if code not in {"return_new", "compensation"} or any(r["manager_lane"] == code for r in rows)]
        if any(r["manager_lane"] == "archive" for r in rows):
            lanes.append(("archive", "Архив"))
    else:
        lanes = [("attention", "Требует решения"), ("waiting", "Ожидает решения"),
                 ("progress", "В работе"), ("decided", "Решение принято"), ("done", "Завершено")]
        lane_statuses = {"manager_decision": "attention", "reprocessing": "attention",
                         "awaiting_distribution": "waiting", "waiting": "waiting",
                         "in_progress": "progress", "assigned": "decided", "completed": "done", "cancelled": "done"}
        for row in rows:
            row["specialist_lane"] = lane_statuses[row["case_status"]]
    return {"rows": rows, "kpis": kpis, "specialists": specialists,
            "managers": managers, "roles": user_roles(conn, user_id), "lanes": lanes, "timing_policy": policy}


def product_code(unit_key: str | None) -> str:
    match = re.match(r"^\s*(\d{1,12})(?:\s*[-–]|\s*$)", unit_key or "")
    return match.group(1) if match else ""


def workspace_filter_sql(filters: dict) -> tuple[str, list]:
    """All filters apply before the workspace limit; values are bound parameters."""
    clauses, params = [], []
    for prefix, expression in (("created", "c.opened_at::date"), ("due", DUE_SQL)):
        bounds = []
        for suffix, operator in (("from", ">="), ("to", "<=")):
            raw = filters.get(f"{prefix}_{suffix}")
            try:
                value = date.fromisoformat(raw) if raw else None
            except (ValueError, TypeError) as exc:
                raise ValueError("Неверная дата фильтра") from exc
            bounds.append(value)
            if value:
                clauses.append(f"{expression} {operator} %s")
                params.append(value)
        if all(bounds) and bounds[0] > bounds[1]:
            raise ValueError("Начало периода должно быть не позже окончания")
    deadline = DUE_SQL
    active = "c.case_status NOT IN ('completed','cancelled')"
    overdue = filters.get("overdue", "")
    conditions = {
        "overdue": f"{deadline} < current_date AND {active}",
        "soon": f"{deadline} BETWEEN current_date AND current_date+3 AND {active}",
        "on_time": f"{deadline} >= current_date AND {active}",
        "no_deadline": f"{deadline} IS NULL AND {active}",
    }
    if overdue:
        if overdue not in conditions:
            raise ValueError("Неверный фильтр просрочки")
        clauses.append("(" + conditions[overdue] + ")")
    query = str(filters.get("q", "")).strip()[:200]
    if query:
        pattern = "%" + query.replace("\\", "\\\\").replace("%", "\\%").replace("_", "\\_") + "%"
        clauses.append("(concat_ws(' ',c.id::text,'РМБ-'||c.id::text,c.external_unit_key,p.name,h.external_name,h.supplier_name) ILIKE %s)")
        params.append(pattern)
    return "".join(" AND " + clause for clause in clauses), params


def case_history(conn, user_id: int, view: str, case_id: int) -> list[dict]:
    if not _visible_rows(conn, user_id, view, case_id=case_id):
        raise PermissionError("Дело недоступно")
    with conn.cursor() as cur:
        cur.execute("""SELECT e.id, e.event_type, e.from_status, e.to_status,
                              e.comment, e.details, e.happened_at AS created_at, u.full_name AS actor
                         FROM app.defect_rmb_event e
                         LEFT JOIN app.app_user u ON u.id=e.actor_user_id
                        WHERE e.case_id=%s ORDER BY e.happened_at DESC, e.id DESC
                        LIMIT 200""", (case_id,))
        return cur.fetchall()


def _manager_filter_options(option_rows: list[dict[str, Any]]) -> list[dict[str, str]]:
    """Полные учётные имена имеют приоритет над сокращениями из 1С.

    У связанного дела оба идентификатора являются одной сущностью. Поэтому
    вариант из ``defect_manager`` не должен дублировать учётную запись в
    фильтре. Сокращение остаётся доступно только для ещё не сопоставленных
    записей, чтобы администратор мог их обнаружить и закрепить.
    """
    options: dict[str, str] = {}
    for row in option_rows:
        if row.get("manager_user_id") and row.get("manager_name"):
            options[f"user:{row['manager_user_id']}"] = row["manager_name"]
        elif row.get("manager_reference_id") and row.get("manager_reference_name"):
            options[f"ref:{row['manager_reference_id']}"] = (
                f"{row['manager_reference_name']} · не сопоставлен")
    return [
        {"value": value, "label": label}
        for value, label in sorted(options.items(),
                                   key=lambda item: item[1].casefold())
    ]


def bulk_update_cases(conn: psycopg.Connection, case_ids: list[int],
                      user_id: int, priority: str | None = None,
                      due_at: date | None = None,
                      specialist_user_id: int | None = None) -> dict[str, int]:
    """Безопасно обновить общие поля выбранных дел менеджером.

    Групповая операция намеренно не меняет направление и не завершает дела.
    Специалист меняется только у активной исполнительской задачи.
    """
    roles = user_roles(conn, user_id)
    if not can_manage(roles):
        raise PermissionError("Групповые действия доступны менеджеру")
    unique_ids = list(dict.fromkeys(case_ids))
    if not unique_ids:
        raise ValueError("Выберите хотя бы одно дело")
    if len(unique_ids) > 200:
        raise ValueError("За один раз можно изменить не более 200 дел")
    if priority is not None and priority not in PRIORITY_LABELS:
        raise ValueError("Неверный приоритет")
    if priority is None and due_at is None and specialist_user_id is None:
        raise ValueError("Выберите хотя бы одно групповое изменение")
    if specialist_user_id is not None:
        with conn.cursor() as cur:
            cur.execute(
                """SELECT 1 FROM app.app_user u
                     JOIN app.defect_rmb_user_role r ON r.user_id=u.id
                    WHERE u.id=%s AND u.is_active
                      AND r.role_code IN ('specialist','leader')""",
                (specialist_user_id,))
            if not cur.fetchone():
                raise ValueError("Специалист не найден или не имеет роли РМБ")

    with conn.cursor() as cur:
        cur.execute(
            """SELECT c.id, c.manager_user_id, c.current_task_id,
                      t.task_status, t.assigned_user_id
                 FROM app.defect_rmb_case c
                 LEFT JOIN app.defect_rmb_task t ON t.id=c.current_task_id
                WHERE c.id=ANY(%s)
                FOR UPDATE OF c""",
            (unique_ids,))
        cases = cur.fetchall()
        if len(cases) != len(unique_ids):
            raise ValueError("Часть выбранных дел не найдена")
        if "leader" not in roles and any(
                row["manager_user_id"] not in (None, user_id) for row in cases):
            raise PermissionError("В выборке есть дело другого менеджера")

        assigned = 0
        assignment_skipped = 0
        for row in cases:
            updates: list[str] = []
            values: list[Any] = []
            if priority is not None:
                updates.append("priority=%s")
                values.append(priority)
            if due_at is not None:
                updates.append("deadline=%s")
                values.append(due_at)
                updates.append("deadline_mode='manual'")
            if updates:
                updates.extend([
                    "updated_at=now()", "last_action_at=now()",
                    "version=version+1",
                ])
                values.append(row["id"])
                cur.execute(
                    f"UPDATE app.defect_rmb_case SET {', '.join(updates)} "
                    "WHERE id=%s", values)
            if due_at is not None and row["current_task_id"]:
                cur.execute(
                    """UPDATE app.defect_rmb_task
                          SET due_at=%s, updated_at=now()
                        WHERE id=%s AND task_status NOT IN ('completed','cancelled')""",
                    (due_at, row["current_task_id"]))
            if specialist_user_id is not None:
                if (not row["current_task_id"] or row["task_status"]
                        not in {"queued", "claimed", "in_progress", "waiting"}):
                    assignment_skipped += 1
                else:
                    cur.execute(
                        """UPDATE app.defect_rmb_task
                              SET assigned_user_id=%s,
                                  task_status=CASE WHEN task_status='queued'
                                                   THEN 'claimed' ELSE task_status END,
                                  claimed_at=coalesce(claimed_at,now()),
                                  updated_at=now()
                            WHERE id=%s""",
                        (specialist_user_id, row["current_task_id"]))
                    assigned += 1
            cur.execute(
                """INSERT INTO app.defect_rmb_event
                     (case_id, task_id, event_type, actor_user_id, comment, details)
                   VALUES (%s,%s,'bulk_case_update',%s,
                           'Групповое изменение в рабочем месте менеджера',
                           %s::jsonb)""",
                (row["id"], row["current_task_id"], user_id,
                 json.dumps({
                     "priority": priority,
                     "due_at": due_at.isoformat() if due_at else None,
                     "specialist_user_id": specialist_user_id,
                 }, ensure_ascii=False)))
    conn.commit()
    return {"selected": len(cases), "assigned": assigned,
            "assignment_skipped": assignment_skipped}


def _manager_case(conn: psycopg.Connection, case_id: int,
                  user_id: int) -> dict[str, Any]:
    roles = user_roles(conn, user_id)
    if not can_manage(roles):
        raise PermissionError("Действие доступно менеджеру")
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM app.defect_rmb_case WHERE id=%s FOR UPDATE",
                    (case_id,))
        case = cur.fetchone()
    if not case:
        raise ValueError("Дело РМБ не найдено")
    if "leader" not in roles and case["manager_user_id"] not in (None, user_id):
        raise PermissionError("Это дело другого менеджера")
    return case


def decide_case(conn: psycopg.Connection, case_id: int, user_id: int,
                direction: str, priority: str, specialist_user_id: int | None,
                due_at: date | None, instruction: str | None,
                reason: str | None, service_center: str | None = None,
                supplier_name: str | None = None, *, commit: bool = True,
                expected_version: int | None = None) -> int:
    if direction not in DIRECTION_LABELS:
        raise ValueError("Выберите направление")
    if priority not in PRIORITY_LABELS:
        raise ValueError("Неверный приоритет")
    service_center = _text(service_center)
    if direction == "service" and not service_center:
        raise ValueError("Укажите сервисный центр")
    supplier_name = _text(supplier_name)
    if direction == "supplier_return" and not supplier_name:
        raise ValueError("Укажите поставщика для возврата")
    case = _manager_case(conn, case_id, user_id)
    if expected_version is not None and case["version"] != expected_version:
        raise ValueError("Дело уже изменилось. Обновите доску и повторите решение")
    if not can_choose_direction(case):
        raise ValueError("Сначала завершите текущий сервисный этап, возврат или складскую дообработку")
    rerouting = case["case_status"] in {"assigned", "in_progress", "waiting"}
    if rerouting and (expected_version is None or not _text(reason)):
        raise ValueError("Для смены направления укажите основание и обновите карточку дела")
    if rerouting and case["direction"] == direction:
        raise ValueError("Это направление уже выбрано")
    deadline_mode = "manual" if due_at is not None else "policy"
    if due_at is None:
        due_at = policy_due(timing_policy(conn), direction, datetime.now(ZoneInfo("Europe/Moscow")).date())
    manager_user_id = case["manager_user_id"] or user_id
    if specialist_user_id:
        with conn.cursor() as cur:
            cur.execute(
                """SELECT 1 FROM app.app_user u
                    JOIN app.defect_rmb_user_role r ON r.user_id=u.id
                   WHERE u.id=%s AND u.is_active
                     AND r.role_code IN ('specialist','leader')""",
                (specialist_user_id,))
            if not cur.fetchone():
                raise ValueError("Специалист не найден или не имеет роли РМБ")
    with conn.cursor() as cur:
        if case["current_task_id"]:
            cur.execute(
                """UPDATE app.defect_rmb_task SET task_status='cancelled',
                          updated_at=now()
                    WHERE id=%s AND task_status NOT IN ('completed','cancelled')""",
                (case["current_task_id"],))
        cur.execute("SELECT coalesce(max(stage_no),0)+1 AS n FROM app.defect_rmb_task WHERE case_id=%s",
                    (case_id,))
        stage_no = cur.fetchone()["n"]
        task_status = "claimed" if specialist_user_id else "queued"
        cur.execute(
            """INSERT INTO app.defect_rmb_task
                 (case_id, stage_no, direction, task_status, manager_user_id,
                  assigned_user_id, instruction, due_at, created_by, claimed_at)
               VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,
                       CASE WHEN %s::bigint IS NULL THEN NULL ELSE now() END)
               RETURNING id""",
            (case_id, stage_no, direction, task_status, manager_user_id,
             specialist_user_id, _text(instruction), due_at, user_id,
             specialist_user_id))
        task_id = cur.fetchone()["id"]
        if direction == "service":
            cur.execute(
                """INSERT INTO app.defect_rmb_service
                     (task_id, service_center, expected_result_at, updated_by)
                   VALUES (%s,%s,%s,%s)""",
                (task_id, service_center, due_at, user_id))
        if direction == "supplier_return":
            cur.execute(
                """INSERT INTO app.defect_rmb_supplier_return
                     (task_id,supplier_name,expected_result_at,updated_by)
                   VALUES (%s,%s,%s,%s)""",
                (task_id,supplier_name,due_at,user_id))
        cur.execute(
            """UPDATE app.defect_rmb_case
                  SET manager_user_id=%s, direction=%s, priority=%s,
                      deadline=%s, instruction=%s, decision_reason=%s,
                      deadline_mode=%s, direction_entered_at=now(),
                      current_task_id=%s, case_status='assigned',
                      updated_at=now(), last_action_at=now(), version=version+1
                WHERE id=%s""",
            (manager_user_id, direction, priority, due_at, _text(instruction),
             _text(reason), deadline_mode, task_id, case_id))
        cur.execute(
            """INSERT INTO app.defect_rmb_event
                 (case_id, task_id, event_type, from_status, to_status,
                  actor_user_id, comment, details)
               VALUES (%s,%s,'manager_decision',%s,'assigned',%s,%s,
                       jsonb_build_object('direction',%s::text,
                                          'priority',%s::text,
                                          'previous_direction',%s::text,
                                          'rerouting',%s::boolean,
                                          'assigned_user_id',%s::bigint))""",
            (case_id, task_id, case["case_status"], user_id, _text(reason),
             direction, priority, case["direction"], rerouting, specialist_user_id))
    if commit:
        conn.commit()
    return task_id


def bulk_decide_cases(conn: psycopg.Connection, case_ids: list[int],
                      user_id: int, direction: str, priority: str,
                      specialist_user_id: int | None, due_at: date | None,
                      instruction: str | None, reason: str | None,
                      service_center: str | None = None,
                      supplier_name: str | None = None, *,
                      expected_versions: dict[int, int] | None = None) -> dict[str, int]:
    """Применить один шаблон решения к нескольким делам атомарно."""
    unique_ids = list(dict.fromkeys(case_ids))
    if not unique_ids:
        raise ValueError("Выберите хотя бы одну позицию")
    if len(unique_ids) > 200:
        raise ValueError("За один раз можно обработать не более 200 позиций")
    created = 0
    for case_id in unique_ids:
        decide_case(
            conn, case_id, user_id, direction, priority,
            specialist_user_id, due_at, instruction, reason,
            service_center, supplier_name, commit=False,
            expected_version=(expected_versions or {}).get(case_id))
        created += 1
    conn.commit()
    return {"selected": len(unique_ids), "created": created}


def claim_task(conn: psycopg.Connection, task_id: int, user_id: int,
               *, commit: bool = True) -> None:
    roles = user_roles(conn, user_id)
    if not can_execute(roles):
        raise PermissionError("Задачу может принять специалист")
    with conn.cursor() as cur:
        cur.execute(
            """SELECT t.*, c.case_status
                 FROM app.defect_rmb_task t
                 JOIN app.defect_rmb_case c ON c.id=t.case_id
                WHERE t.id=%s FOR UPDATE OF t, c""", (task_id,))
        task = cur.fetchone()
        if not task:
            raise ValueError("Задача не найдена")
        if task["task_status"] != "queued" or task["assigned_user_id"]:
            raise ValueError("Задачу уже принял другой специалист")
        if "leader" not in roles:
            cur.execute(
                """SELECT 1 FROM app.defect_rmb_team_member
                    WHERE manager_user_id=%s AND specialist_user_id=%s
                      AND active_to IS NULL""",
                (task["manager_user_id"], user_id))
            if not cur.fetchone():
                raise PermissionError("Задача относится к другой группе")
        cur.execute(
            """UPDATE app.defect_rmb_task
                  SET assigned_user_id=%s, task_status='claimed',
                      claimed_at=now(), updated_at=now()
                WHERE id=%s""", (user_id, task_id))
        cur.execute(
            """UPDATE app.defect_rmb_case
                  SET case_status='assigned', updated_at=now(),
                      last_action_at=now(), version=version+1
                WHERE id=%s""", (task["case_id"],))
        cur.execute(
            """INSERT INTO app.defect_rmb_event
                 (case_id, task_id, event_type, from_status, to_status,
                  actor_user_id, comment)
               VALUES (%s,%s,'task_claimed','queued','claimed',%s,
                       'Специалист принял задачу')""",
            (task["case_id"], task_id, user_id))
    if commit:
        conn.commit()


def _owned_task(conn: psycopg.Connection, task_id: int,
                user_id: int) -> dict[str, Any]:
    roles = user_roles(conn, user_id)
    if not can_execute(roles):
        raise PermissionError("Действие доступно специалисту")
    with conn.cursor() as cur:
        cur.execute(
            """SELECT t.*, c.handler_event_id, c.case_status
                 FROM app.defect_rmb_task t
                 JOIN app.defect_rmb_case c ON c.id=t.case_id
                WHERE t.id=%s FOR UPDATE OF t, c""", (task_id,))
        task = cur.fetchone()
    if not task:
        raise ValueError("Задача не найдена")
    if "leader" not in roles and task["assigned_user_id"] != user_id:
        raise PermissionError("Задача назначена другому специалисту")
    return task


def task_action(conn: psycopg.Connection, task_id: int, user_id: int,
                action: str, comment: str | None = None,
                external_confirmed: bool = False, *,
                commit: bool = True) -> int | None:
    if action == "claim_start":
        # Both operations share the caller's transaction and retain their audit events.
        claim_task(conn, task_id, user_id, commit=False)
        task_action(conn, task_id, user_id, "start", comment, commit=False)
        if commit:
            conn.commit()
        return None
    if action == "claim":
        claim_task(conn, task_id, user_id, commit=commit)
        return None
    task = _owned_task(conn, task_id, user_id)
    comment = _text(comment)
    transitions = {
        "start": ({"claimed", "waiting"}, "in_progress", "in_progress"),
        "wait": ({"claimed", "in_progress"}, "waiting", "waiting"),
        "resume": ({"waiting"}, "in_progress", "in_progress"),
        "complete": ({"claimed", "in_progress", "waiting"}, "completed",
                     "manager_decision"),
    }
    if action == "rework":
        return request_rework(conn, task, user_id, comment, commit=commit)
    if action not in transitions:
        raise ValueError("Неизвестное действие")
    allowed, task_status, case_status = transitions[action]
    if task["task_status"] not in allowed:
        raise ValueError("Это действие недоступно в текущем статусе")
    if action in {"wait", "complete"} and not comment:
        raise ValueError("Укажите причину или результат")
    if (action == "complete" and task["direction"] == "discount"
            and not external_confirmed):
        raise ValueError(
            "Подтвердите, что сведения об уценке внесены в 1С. "
            "Сверка с ClickHouse не выполняется.")
    if action == "complete" and task["direction"] == "service":
        with conn.cursor() as cur:
            cur.execute(
                """SELECT service_status, warehouse_received_at,
                          result_type, conclusion
                     FROM app.defect_rmb_service WHERE task_id=%s""",
                (task_id,))
            service = cur.fetchone()
        if (not service or service["service_status"] != "SC-15"
                or not service["warehouse_received_at"]
                or not service["result_type"] or not service["conclusion"]):
            raise ValueError(
                "Сервисный этап можно завершить после результата, возврата "
                "на склад и перевода обращения в «Готова к закрытию».")
    if action == "complete" and task["direction"] == "supplier_return":
        with conn.cursor() as cur:
            cur.execute(
                "SELECT return_status,outcome,conclusion FROM app.defect_rmb_supplier_return WHERE task_id=%s",
                (task_id,))
            returned = cur.fetchone()
        if (not returned or returned["return_status"] != "SR-11"
                or not returned["outcome"] or not returned["conclusion"]):
            raise ValueError("Возврат можно завершить после зафиксированного результата")
    event_type = {
        "start": "task_started", "wait": "task_waiting",
        "resume": "task_resumed", "complete": "task_completed",
    }[action]
    with conn.cursor() as cur:
        cur.execute(
            """UPDATE app.defect_rmb_task
                  SET task_status=%s,
                      started_at=CASE WHEN %s='in_progress'
                                      THEN coalesce(started_at,now()) ELSE started_at END,
                      waiting_reason=CASE WHEN %s='waiting' THEN %s ELSE NULL END,
                      result_text=CASE WHEN %s='completed' THEN %s ELSE result_text END,
                      completed_at=CASE WHEN %s='completed' THEN now() ELSE NULL END,
                      external_action_confirmed=CASE
                        WHEN %s='completed' AND direction='discount' THEN %s
                        ELSE external_action_confirmed END,
                      external_system=CASE
                        WHEN %s='completed' AND direction='discount' THEN '1С'
                        ELSE external_system END,
                      external_action_confirmed_at=CASE
                        WHEN %s='completed' AND direction='discount' THEN now()
                        ELSE external_action_confirmed_at END,
                      external_action_confirmed_by=CASE
                        WHEN %s='completed' AND direction='discount' THEN %s
                        ELSE external_action_confirmed_by END,
                      confirmation_method=CASE
                        WHEN %s='completed' AND direction='discount'
                          THEN 'specialist_confirmation'
                        ELSE confirmation_method END,
                      updated_at=now()
                WHERE id=%s""",
            (task_status, task_status, task_status, comment, task_status,
             comment, task_status, task_status, external_confirmed,
             task_status, task_status, task_status, user_id, task_status,
             task_id))
        cur.execute(
            """UPDATE app.defect_rmb_case
                  SET case_status=%s, updated_at=now(), last_action_at=now(),
                      version=version+1
                WHERE id=%s""", (case_status, task["case_id"]))
        if action == "complete" and task["direction"] == "service":
            cur.execute(
                """UPDATE app.defect_rmb_service
                      SET service_status='SC-16', completed_at=now(),
                          updated_at=now(), updated_by=%s
                    WHERE task_id=%s""", (user_id, task_id))
        if action == "complete" and task["direction"] == "supplier_return":
            cur.execute(
                """UPDATE app.defect_rmb_supplier_return
                      SET return_status='SR-12',completed_at=now(),updated_at=now(),updated_by=%s
                    WHERE task_id=%s""", (user_id,task_id))
        cur.execute(
            """INSERT INTO app.defect_rmb_event
                 (case_id, task_id, event_type, from_status, to_status,
                  actor_user_id, comment, details)
               VALUES (%s,%s,%s,%s,%s,%s,%s,
                       jsonb_build_object(
                         'external_action_confirmed',%s::boolean,
                         'external_system',
                         CASE WHEN %s::boolean THEN '1С' ELSE NULL END,
                         'verification','specialist_confirmation_only'))""",
            (task["case_id"], task_id, event_type, task["task_status"],
             task_status, user_id, comment,
             bool(action == "complete" and task["direction"] == "discount"
                  and external_confirmed),
             bool(action == "complete" and task["direction"] == "discount"
                  and external_confirmed)))
    if commit:
        conn.commit()
    return None


def bulk_task_action(conn: psycopg.Connection, task_ids: list[int],
                     user_id: int, action: str, comment: str | None = None,
                     external_confirmed: bool = False) -> dict[str, int]:
    """Одинаковое действие специалиста над выбранными задачами.

    Все задачи изменяются в одной транзакции: если хотя бы одна позиция не
    допускает выбранный переход или требует дополнительных данных, изменения
    не применяются ни к одной позиции.
    """
    unique_ids = list(dict.fromkeys(task_ids))
    if not unique_ids:
        raise ValueError("Выберите хотя бы одну задачу")
    if len(unique_ids) > 200:
        raise ValueError("За один раз можно обработать не более 200 задач")
    if action not in {"claim", "claim_start", "start", "wait", "resume", "complete", "rework"}:
        raise ValueError("Неизвестное групповое действие")
    rework_created = 0
    for task_id in unique_ids:
        result = task_action(
            conn, task_id, user_id, action, comment,
            external_confirmed, commit=False)
        rework_created += int(result is not None)
    conn.commit()
    return {"selected": len(unique_ids), "rework_created": rework_created}


def service_action(conn: psycopg.Connection, task_id: int, user_id: int,
                   action: str, comment: str | None = None,
                   result_type: str | None = None,
                   document_reference: str | None = None) -> None:
    """Зафиксировать подтверждаемое событие сервисного обращения."""
    task = _owned_task(conn, task_id, user_id)
    if task["direction"] != "service":
        raise ValueError("Это не сервисная задача")
    comment = _text(comment)
    result_type = _text(result_type)
    document_reference = _text(document_reference)
    targets = {
        "prepare": "SC-02", "sent": "SC-04", "accepted": "SC-06",
        "repair": "SC-07", "contact": "SC-08", "result": "SC-09",
        "return_sent": "SC-11", "warehouse_received": "SC-13",
        "ready": "SC-15", "pause": "SC-17",
    }
    if action not in targets:
        raise ValueError("Неизвестное сервисное действие")
    if action in {"contact", "result", "pause"} and not comment:
        raise ValueError("Добавьте комментарий к сервисному событию")
    if action == "result" and result_type not in SERVICE_RESULT_LABELS:
        raise ValueError("Выберите результат сервиса")
    with conn.cursor() as cur:
        cur.execute(
            "SELECT * FROM app.defect_rmb_service WHERE task_id=%s FOR UPDATE",
            (task_id,))
        service = cur.fetchone()
        if not service:
            raise ValueError("Сервисное обращение не найдено")
        if service["service_status"] in {"SC-16", "SC-18"}:
            raise ValueError("Сервисное обращение уже закрыто")
        if action == "ready" and (not service["warehouse_received_at"]
                                  or not service["result_type"]
                                  or not service["conclusion"]):
            raise ValueError(
                "Для готовности к закрытию нужны результат сервиса и "
                "подтверждённая складская приёмка")
        target = targets[action]
        cur.execute(
            """UPDATE app.defect_rmb_service
                  SET service_status=%s,
                      sent_at=CASE WHEN %s='sent' THEN coalesce(sent_at,now())
                                   ELSE sent_at END,
                      accepted_by_service_at=CASE WHEN %s='accepted'
                                   THEN coalesce(accepted_by_service_at,now())
                                   ELSE accepted_by_service_at END,
                      last_contact_at=CASE WHEN %s IN ('contact','result')
                                   THEN now() ELSE last_contact_at END,
                      result_received_at=CASE WHEN %s='result'
                                   THEN coalesce(result_received_at,now())
                                   ELSE result_received_at END,
                      return_sent_at=CASE WHEN %s='return_sent'
                                   THEN coalesce(return_sent_at,now())
                                   ELSE return_sent_at END,
                      warehouse_received_at=CASE WHEN %s='warehouse_received'
                                   THEN coalesce(warehouse_received_at,now())
                                   ELSE warehouse_received_at END,
                      result_type=CASE WHEN %s='result' THEN %s ELSE result_type END,
                      conclusion=CASE WHEN %s='result' THEN %s ELSE conclusion END,
                      document_reference=coalesce(%s,document_reference),
                      updated_at=now(), updated_by=%s
                WHERE task_id=%s""",
            (target, action, action, action, action, action, action,
             action, result_type, action, comment, document_reference,
             user_id, task_id))
        location_type = None
        location_text = None
        if action == "sent":
            location_type, location_text = "in_transit", f"В пути в {service['service_center']}"
        elif action == "accepted":
            location_type, location_text = "service", service["service_center"]
        elif action == "return_sent":
            location_type, location_text = "in_transit", "В пути на наш склад"
        elif action == "warehouse_received":
            location_type, location_text = "own_warehouse", "Складская приёмка подтверждена вручную"
        if location_type:
            cur.execute(
                """UPDATE app.defect_rmb_case
                      SET physical_location_type=%s, physical_location_text=%s,
                          location_confirmed_at=now(), updated_at=now(),
                          last_action_at=now(), version=version+1
                    WHERE id=%s""",
                (location_type, location_text, task["case_id"]))
        else:
            cur.execute(
                """UPDATE app.defect_rmb_case
                      SET updated_at=now(), last_action_at=now(), version=version+1
                    WHERE id=%s""", (task["case_id"],))
        cur.execute(
            """INSERT INTO app.defect_rmb_event
                 (case_id, task_id, event_type, from_status, to_status,
                  actor_user_id, comment, details)
               VALUES (%s,%s,'service_action',%s,%s,%s,%s,
                       jsonb_build_object('action',%s::text,
                                          'result_type',%s::text,
                                          'document_reference',%s::text))""",
            (task["case_id"], task_id, service["service_status"], target,
             user_id, comment, action, result_type, document_reference))
    conn.commit()


def supplier_return_action(conn: psycopg.Connection, task_id: int, user_id: int,
                           action: str, comment: str | None = None,
                           outcome: str | None = None,
                           document_reference: str | None = None) -> None:
    task = _owned_task(conn,task_id,user_id)
    if task["direction"] != "supplier_return":
        raise ValueError("Это не задача возврата поставщику")
    comment,outcome,document_reference = (_text(comment),_text(outcome),
                                           _text(document_reference))
    targets={"prepare":"SR-02","request":"SR-03","waiting":"SR-04",
             "approved":"SR-05","rejected":"SR-06","documents":"SR-07",
             "transfer":"SR-09","received":"SR-10","result":"SR-11",
             "pause":"SR-13"}
    if action not in targets: raise ValueError("Неизвестное действие возврата")
    if action in {"request","rejected","result","pause"} and not comment:
        raise ValueError("Добавьте комментарий")
    if action=="result" and outcome not in SUPPLIER_OUTCOME_LABELS:
        raise ValueError("Выберите результат возврата")
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM app.defect_rmb_supplier_return WHERE task_id=%s FOR UPDATE",(task_id,))
        row=cur.fetchone()
        if not row: raise ValueError("Обращение к поставщику не найдено")
        if row["return_status"] in {"SR-12","SR-14"}: raise ValueError("Возврат уже закрыт")
        if action=="result" and outcome!="rejected" and not row["supplier_received_at"]:
            raise ValueError("Сначала подтвердите приём товара поставщиком")
        target=targets[action]
        cur.execute("""UPDATE app.defect_rmb_supplier_return SET return_status=%s,
          request_sent_at=CASE WHEN %s='request' THEN coalesce(request_sent_at,now()) ELSE request_sent_at END,
          response_received_at=CASE WHEN %s IN ('approved','rejected') THEN now() ELSE response_received_at END,
          approved=CASE WHEN %s='approved' THEN true WHEN %s='rejected' THEN false ELSE approved END,
          documents_ready_at=CASE WHEN %s='documents' THEN now() ELSE documents_ready_at END,
          transferred_at=CASE WHEN %s='transfer' THEN now() ELSE transferred_at END,
          supplier_received_at=CASE WHEN %s='received' THEN now() ELSE supplier_received_at END,
          result_received_at=CASE WHEN %s='result' THEN now() ELSE result_received_at END,
          last_contact_at=CASE WHEN %s IN ('request','approved','rejected','waiting','result') THEN now() ELSE last_contact_at END,
          outcome=CASE WHEN %s='result' THEN %s ELSE outcome END,
          conclusion=CASE WHEN %s='result' THEN %s ELSE conclusion END,
          document_reference=coalesce(%s,document_reference),updated_at=now(),updated_by=%s
          WHERE task_id=%s""",
          (target,action,action,action,action,action,action,action,action,action,
           action,outcome,action,comment,document_reference,user_id,task_id))
        if action=="transfer":
            cur.execute("""UPDATE app.defect_rmb_case SET physical_location_type='in_transit',
              physical_location_text=%s,location_confirmed_at=now(),updated_at=now(),last_action_at=now(),version=version+1
              WHERE id=%s""",(f"В пути к поставщику: {row['supplier_name']}",task["case_id"]))
        elif action=="received":
            cur.execute("""UPDATE app.defect_rmb_case SET physical_location_type='supplier',
              physical_location_text=%s,location_confirmed_at=now(),updated_at=now(),last_action_at=now(),version=version+1
              WHERE id=%s""",(row["supplier_name"],task["case_id"]))
        else:
            cur.execute("UPDATE app.defect_rmb_case SET updated_at=now(),last_action_at=now(),version=version+1 WHERE id=%s",(task["case_id"],))
        cur.execute("""INSERT INTO app.defect_rmb_event
          (case_id,task_id,event_type,from_status,to_status,actor_user_id,comment,details)
          VALUES (%s,%s,'supplier_return_action',%s,%s,%s,%s,
          jsonb_build_object('action',%s::text,'outcome',%s::text,'document_reference',%s::text))""",
          (task["case_id"],task_id,row["return_status"],target,user_id,comment,
           action,outcome,document_reference))
    conn.commit()


def request_rework(conn: psycopg.Connection, task: dict[str, Any],
                   user_id: int, reason: str | None, *,
                   commit: bool = True) -> int:
    if task["task_status"] not in {"claimed", "in_progress", "waiting"}:
        raise ValueError("Дообработка недоступна в текущем статусе")
    if not reason:
        raise ValueError("Укажите, что именно должен доработать склад")
    with conn.cursor() as cur:
        cur.execute(
            """SELECT e.* FROM app.defect_handler_event e
                WHERE e.id=%s""", (task["handler_event_id"],))
        source = cur.fetchone()
        if not source:
            raise ValueError("Исходная складская обработка не найдена")
        cur.execute("SELECT nextval('app.defect_handler_local_row_seq') AS n")
        source_row = cur.fetchone()["n"]
        fingerprint = hashlib.sha256(
            f"rmb:{task['case_id']}:{task['id']}:{source_row}:{reason}"
            .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, assigned_manager_id,
                  match_status, unit_cost, classification, processor,
                  source_description, 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, repeat_of_event_id,
                  assignment_note)
               VALUES
                 ('local-app','LOCAL',%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,NULL,
                  %s,%s,%s,%s,%s,true,'draft','local_app',%s,%s,%s,NULL,%s,%s)
               RETURNING id""",
            (source_row, fingerprint, source["external_name"],
             source["external_unit_key"], source["product_id"],
             source["current_manager_id"], source["assigned_manager_id"],
             source["match_status"], source["unit_cost"], source["classification"],
             source["source_description"], source["product_kind"], source["weight"],
             source["supplier_name"], source["source_manager"],
             source["source_stock_line_id"], source["source_warehouse"],
             user_id, source["id"], reason))
        rework_event_id = cur.fetchone()["id"]
        cur.execute(
            """INSERT INTO app.defect_rmb_rework_request
                 (case_id, task_id, source_handler_event_id,
                  rework_handler_event_id, reason, requested_by)
               VALUES (%s,%s,%s,%s,%s,%s)""",
            (task["case_id"], task["id"], source["id"], rework_event_id,
             reason, user_id))
        cur.execute(
            """UPDATE app.defect_rmb_task
                  SET task_status='rework_requested', waiting_reason=%s,
                      updated_at=now()
                WHERE id=%s""", (reason, task["id"]))
        cur.execute(
            """UPDATE app.defect_rmb_case
                  SET case_status='reprocessing', updated_at=now(),
                      last_action_at=now(), version=version+1
                WHERE id=%s""", (task["case_id"],))
        cur.execute(
            """INSERT INTO app.defect_rmb_event
                 (case_id, task_id, event_type, from_status, to_status,
                  actor_user_id, comment, details)
               VALUES (%s,%s,'rework_requested',%s,'rework_requested',%s,%s,
                       jsonb_build_object('handler_event_id',%s))""",
            (task["case_id"], task["id"], task["task_status"], user_id,
             reason, rework_event_id))
    if commit:
        conn.commit()
    return rework_event_id


def close_case(conn: psycopg.Connection, case_id: int, user_id: int,
               result: str | None) -> None:
    case = _manager_case(conn, case_id, user_id)
    result = _text(result)
    if case["case_status"] != "manager_decision":
        raise ValueError("Закрыть можно дело, по которому специалист завершил этап")
    if not result:
        raise ValueError("Укажите окончательный результат")
    with conn.cursor() as cur:
        cur.execute(
            """UPDATE app.defect_rmb_case
                  SET case_status='completed', closed_at=now(), updated_at=now(),
                      last_action_at=now(), decision_reason=%s, version=version+1
                WHERE id=%s""", (result, case_id))
        cur.execute(
            """INSERT INTO app.defect_rmb_event
                 (case_id, task_id, event_type, from_status, to_status,
                  actor_user_id, comment)
               VALUES (%s,%s,'case_closed','manager_decision','completed',%s,%s)""",
            (case_id, case["current_task_id"], user_id, result))
    conn.commit()


def settings_data(conn: psycopg.Connection) -> dict[str, Any]:
    with conn.cursor() as cur:
        cur.execute(
            """SELECT u.id, u.full_name, u.email,
                      coalesce(array_agg(r.role_code ORDER BY r.role_code)
                               FILTER (WHERE r.role_code IS NOT NULL),'{}') AS rmb_roles
                 FROM app.app_user u
                 LEFT JOIN app.defect_rmb_user_role r ON r.user_id=u.id
                WHERE u.is_active
                GROUP BY u.id, u.full_name, u.email
                ORDER BY u.full_name""")
        users = cur.fetchall()
        cur.execute(
            """SELECT dm.id, dm.name, mm.manager_user_id,
                      u.full_name AS manager_user_name
                 FROM app.defect_manager dm
                 LEFT JOIN app.defect_rmb_manager_map mm
                   ON mm.defect_manager_id=dm.id AND mm.active_to IS NULL
                 LEFT JOIN app.app_user u ON u.id=mm.manager_user_id
                ORDER BY dm.name""")
        mappings = cur.fetchall()
        cur.execute(
            """SELECT tm.id, tm.manager_user_id, tm.specialist_user_id,
                      m.full_name AS manager_name, s.full_name AS specialist_name
                 FROM app.defect_rmb_team_member tm
                 JOIN app.app_user m ON m.id=tm.manager_user_id
                 JOIN app.app_user s ON s.id=tm.specialist_user_id
                WHERE tm.active_to IS NULL
                ORDER BY m.full_name, s.full_name""")
        team = cur.fetchall()
    return {"users": users, "mappings": mappings, "team": team,
            "manager_sources": manager_sources_data(conn)}


def set_user_role(conn: psycopg.Connection, actor_user_id: int, user_id: int,
                  role_code: str, enabled: bool) -> None:
    if role_code not in ROLE_LABELS:
        raise ValueError("Неизвестная роль РМБ")
    with conn.cursor() as cur:
        if enabled:
            cur.execute(
                """INSERT INTO app.defect_rmb_user_role
                     (user_id, role_code, assigned_by)
                   SELECT id,%s,%s FROM app.app_user WHERE id=%s AND is_active
                   ON CONFLICT DO NOTHING""",
                (role_code, actor_user_id, user_id))
            if not cur.rowcount:
                cur.execute("SELECT 1 FROM app.app_user WHERE id=%s AND is_active",
                            (user_id,))
                if not cur.fetchone():
                    raise ValueError("Пользователь не найден")
        else:
            cur.execute(
                "DELETE FROM app.defect_rmb_user_role WHERE user_id=%s AND role_code=%s",
                (user_id, role_code))
    conn.commit()


def set_manager_mapping(conn: psycopg.Connection, actor_user_id: int,
                        defect_manager_id: int, manager_user_id: int | None) -> None:
    with conn.cursor() as cur:
        cur.execute(
            """UPDATE app.defect_rmb_manager_map SET active_to=now()
                WHERE defect_manager_id=%s AND active_to IS NULL""",
            (defect_manager_id,))
        if manager_user_id:
            cur.execute(
                """SELECT 1 FROM app.defect_rmb_user_role
                    WHERE user_id=%s AND role_code IN ('manager','leader')""",
                (manager_user_id,))
            if not cur.fetchone():
                raise ValueError("Сначала назначьте пользователю роль менеджера РМБ")
            cur.execute(
                """INSERT INTO app.defect_rmb_manager_map
                     (defect_manager_id, manager_user_id, created_by)
                   VALUES (%s,%s,%s)""",
                (defect_manager_id, manager_user_id, actor_user_id))
            cur.execute(
                """UPDATE app.defect_rmb_case SET manager_user_id=%s,
                          updated_at=now(), version=version+1
                    WHERE manager_reference_id=%s AND manager_user_id IS NULL
                      AND case_status NOT IN ('completed','cancelled')""",
                (manager_user_id, defect_manager_id))
    conn.commit()


def set_team_member(conn: psycopg.Connection, actor_user_id: int,
                    manager_user_id: int, specialist_user_id: int,
                    enabled: bool) -> None:
    with conn.cursor() as cur:
        if enabled:
            cur.execute(
                """SELECT role_code, user_id FROM app.defect_rmb_user_role
                    WHERE user_id IN (%s,%s)""",
                (manager_user_id, specialist_user_id))
            by_user: dict[int, set[str]] = {}
            for row in cur.fetchall():
                by_user.setdefault(row["user_id"], set()).add(row["role_code"])
            if not by_user.get(manager_user_id, set()) & {"manager", "leader"}:
                raise ValueError("У первого пользователя нет роли менеджера")
            if not by_user.get(specialist_user_id, set()) & {"specialist", "leader"}:
                raise ValueError("У второго пользователя нет роли специалиста")
            cur.execute(
                """INSERT INTO app.defect_rmb_team_member
                     (manager_user_id, specialist_user_id, created_by)
                   VALUES (%s,%s,%s) ON CONFLICT DO NOTHING""",
                (manager_user_id, specialist_user_id, actor_user_id))
        else:
            cur.execute(
                """UPDATE app.defect_rmb_team_member SET active_to=now()
                    WHERE manager_user_id=%s AND specialist_user_id=%s
                      AND active_to IS NULL""",
                (manager_user_id, specialist_user_id))
    conn.commit()


def create_specialist(conn: psycopg.Connection, actor_user_id: int,
                      full_name: str, email: str, password: str,
                      manager_user_id: int | None = None) -> int:
    """Создать отдельную учётную запись специалиста РМБ и, при выборе,
    сразу включить её в группу менеджера.

    Пароль передаётся только в ``app.set_password`` и открытым в базе не
    хранится. Его доставку или одноразовый показ выполняет веб-слой.
    """
    name = _text(full_name)
    login = str(email or "").strip().lower()
    if not name or len(name) < 3:
        raise ValueError("Укажите имя специалиста")
    if not re.fullmatch(r"[^\s@]+@[^\s@]+\.[^\s@]+", login):
        raise ValueError("Укажите корректный логин в формате электронной почты")
    if len(password) < 10:
        raise ValueError("Пароль должен содержать не менее 10 символов")
    with conn.cursor() as cur:
        cur.execute("SELECT 1 FROM app.app_user WHERE email=%s", (login,))
        if cur.fetchone():
            raise ValueError("Учётная запись с таким логином уже существует")
        if manager_user_id:
            cur.execute(
                """SELECT 1 FROM app.defect_rmb_user_role
                    WHERE user_id=%s AND role_code IN ('manager','leader')""",
                (manager_user_id,))
            if not cur.fetchone():
                raise ValueError("Выбранный пользователь не является менеджером РМБ")
        cur.execute(
            """INSERT INTO app.app_user (email, full_name, is_active)
               VALUES (%s,%s,true) RETURNING id""", (login, name))
        specialist_id = cur.fetchone()["id"]
        cur.execute("SELECT app.set_password(%s,%s)", (specialist_id, password))
        cur.execute(
            """INSERT INTO app.defect_rmb_user_role
                 (user_id, role_code, assigned_by)
               VALUES (%s,'specialist',%s)""",
            (specialist_id, actor_user_id))
        if manager_user_id:
            cur.execute(
                """INSERT INTO app.defect_rmb_team_member
                     (manager_user_id, specialist_user_id, created_by)
                   VALUES (%s,%s,%s)""",
                (manager_user_id, specialist_id, actor_user_id))
    conn.commit()
    return specialist_id


# Состав из рабочего списка отдела. Короткие имена в defect_manager — это
# значения из выгрузки остатков, полные имена и почта — из переданного списка.
DEFAULT_TEAM_ROSTER = (
    ("ЯскевичА", "Яскевич Анна", "an.yaskevich@21vek.by",
     "Моторя Ольга", "o.motorya@21vek.by"),
    ("Волкова", "Волкова Екатерина", "e.volkova@21vek.by",
     "Печко Юлия", "yu.pechko@21vek.by"),
    ("Хатляник НА", "Хатляник Наташа", "n.khatlyanik@21vek.by",
     "Олесиюк Полина", "p.olesiyuk@21vek.by"),
    ("Шарко НП", "Шарко Надежда", "n.sharko@21vek.by",
     "Фирс Ангелина", "a.firs@21vek.by"),
    ("Габрияник ПО", "Габрияник Полина", "p.gabriyanik@21vek.by",
     "Глущеня Артём", "a.gluschenya@21vek.by"),
    ("Босько МА", "Босько Мария", "m.bosko@21vek.by",
     "Воронцова Ольга", "o.vorontsova@21vek.by"),
)


def sync_default_team_roster(conn: psycopg.Connection, actor_user_id: int,
                             password_factory) -> dict[str, Any]:
    """Идемпотентно создать недостающие учётки и закрепить пары.

    Пароли возвращаются только для новых учётных записей. Веб-слой кладёт их
    в одноразовую записку; повторный запуск пароли существующих людей не меняет.
    """
    created: list[dict[str, str]] = []
    touched: set[int] = set()
    with conn.cursor() as cur:
        for manager_ref, manager_name, manager_email, specialist_name, specialist_email in DEFAULT_TEAM_ROSTER:
            ids: dict[str, int] = {}
            for kind, full_name, email, role_code in (
                ("manager", manager_name, manager_email, "manager"),
                ("specialist", specialist_name, specialist_email, "specialist"),
            ):
                cur.execute(
                    "SELECT id, is_active FROM app.app_user WHERE email=%s",
                    (email,))
                person = cur.fetchone()
                if person and not person["is_active"]:
                    raise ValueError(
                        f"Учётная запись {email} отключена; включите её отдельно")
                if person:
                    user_id = person["id"]
                else:
                    password = password_factory()
                    cur.execute(
                        """INSERT INTO app.app_user (email, full_name, is_active)
                           VALUES (%s,%s,true) RETURNING id""",
                        (email, full_name))
                    user_id = cur.fetchone()["id"]
                    cur.execute("SELECT app.set_password(%s,%s)",
                                (user_id, password))
                    created.append({"email": email, "password": password,
                                    "full_name": full_name})
                ids[kind] = user_id
                touched.add(user_id)
                cur.execute(
                    """INSERT INTO app.defect_rmb_user_role
                         (user_id, role_code, assigned_by)
                       VALUES (%s,%s,%s) ON CONFLICT DO NOTHING""",
                    (user_id, role_code, actor_user_id))

            # Помощники из этого списка не являются менеджерами. Это также
            # исправляет две ранние демонстрационные привязки помощников.
            cur.execute(
                """DELETE FROM app.defect_rmb_user_role
                    WHERE user_id=%s AND role_code='manager'""",
                (ids["specialist"],))

            cur.execute("SELECT id FROM app.defect_manager WHERE name=%s",
                        (manager_ref,))
            ref = cur.fetchone()
            if not ref:
                raise ValueError(f"Менеджер из остатков не найден: {manager_ref}")
            cur.execute(
                """UPDATE app.defect_rmb_manager_map SET active_to=now()
                    WHERE defect_manager_id=%s AND active_to IS NULL
                      AND manager_user_id<>%s""",
                (ref["id"], ids["manager"]))
            cur.execute(
                """INSERT INTO app.defect_rmb_manager_map
                     (defect_manager_id, manager_user_id, created_by)
                   SELECT %s,%s,%s
                    WHERE NOT EXISTS (
                      SELECT 1 FROM app.defect_rmb_manager_map
                       WHERE defect_manager_id=%s AND manager_user_id=%s
                         AND active_to IS NULL)""",
                (ref["id"], ids["manager"], actor_user_id,
                 ref["id"], ids["manager"]))
            cur.execute(
                """UPDATE app.defect_rmb_case SET manager_user_id=%s,
                          updated_at=now(), version=version+1
                    WHERE manager_reference_id=%s
                      AND manager_user_id IS DISTINCT FROM %s
                      AND case_status NOT IN ('completed','cancelled')""",
                (ids["manager"], ref["id"], ids["manager"]))
            cur.execute(
                """INSERT INTO app.defect_rmb_team_member
                     (manager_user_id, specialist_user_id, created_by)
                   SELECT %s,%s,%s
                    WHERE NOT EXISTS (
                      SELECT 1 FROM app.defect_rmb_team_member
                       WHERE manager_user_id=%s AND specialist_user_id=%s
                         AND active_to IS NULL)""",
                (ids["manager"], ids["specialist"], actor_user_id,
                 ids["manager"], ids["specialist"]))
    conn.commit()
    return {"created": created, "users_total": len(touched),
            "pairs_total": len(DEFAULT_TEAM_ROSTER)}


_MANAGER_UNIT_RE = re.compile(r"(?i)(\d+\s*-\s*[а-яa-z]+\s*-\s*\d+)")
_MANAGER_SHEET_ID_RE = re.compile(r"^[A-Za-z0-9_-]+$")
_MANAGER_FINAL_RESULTS = {
    "утиль (списано)", "распродажа (выставлено)", "компенсация",
    "возвращено", "разбраковано", "в бое (списано)",
}


def _manager_unit_key(value: object) -> str | None:
    source = _text(value)
    if not source:
        return None
    match = _MANAGER_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 parse_manager_sheet_url(value: str) -> tuple[str, int, str]:
    parsed = urlparse(str(value or "").strip())
    if parsed.scheme != "https" or parsed.netloc != "docs.google.com":
        raise ValueError("Укажите ссылку Google Sheets на вкладку «РМ»")
    match = re.fullmatch(r"/spreadsheets/d/([A-Za-z0-9_-]+)(?:/.*)?",
                         parsed.path)
    if not match or not _MANAGER_SHEET_ID_RE.fullmatch(match.group(1)):
        raise ValueError("Не удалось определить идентификатор Google Sheets")
    query = parse_qs(parsed.query)
    fragment = parse_qs(parsed.fragment)
    gid_raw = (query.get("gid") or fragment.get("gid") or [None])[0]
    if gid_raw is None or not str(gid_raw).isdigit():
        raise ValueError("Откройте вкладку «РМ» и скопируйте ссылку с gid")
    gid = int(gid_raw)
    spreadsheet_id = match.group(1)
    canonical = (f"https://docs.google.com/spreadsheets/d/{spreadsheet_id}/"
                 f"edit?gid={gid}#gid={gid}")
    return spreadsheet_id, gid, canonical


def register_manager_source(conn: psycopg.Connection, actor_user_id: int,
                            manager_user_id: int, source_url: str,
                            source_title: str) -> int:
    spreadsheet_id, gid, canonical = parse_manager_sheet_url(source_url)
    title = _text(source_title) or "Рабочее место менеджера"
    with conn.cursor() as cur:
        cur.execute(
            """SELECT 1 FROM app.defect_rmb_user_role
                WHERE user_id=%s AND role_code IN ('manager','leader')""",
            (manager_user_id,))
        if not cur.fetchone():
            raise ValueError("Выбранный пользователь не является менеджером РМБ")
        cur.execute(
            """SELECT id, manager_user_id FROM app.defect_rmb_manager_source
                WHERE source_spreadsheet_id=%s AND source_gid=%s""",
            (spreadsheet_id, gid))
        existing = cur.fetchone()
        if existing and existing["manager_user_id"] != manager_user_id:
            raise ValueError("Эта вкладка уже закреплена за другим менеджером")
        cur.execute(
            """UPDATE app.defect_rmb_manager_source SET is_active=false,
                      updated_at=now()
                WHERE manager_user_id=%s AND is_active
                  AND NOT (source_spreadsheet_id=%s AND source_gid=%s)""",
            (manager_user_id, spreadsheet_id, gid))
        if existing:
            cur.execute(
                """UPDATE app.defect_rmb_manager_source
                      SET source_title=%s, source_url=%s, source_sheet='РМ',
                          is_active=true, updated_at=now()
                    WHERE id=%s RETURNING id""",
                (title, canonical, existing["id"]))
        else:
            cur.execute(
                """INSERT INTO app.defect_rmb_manager_source
                     (manager_user_id, source_spreadsheet_id, source_title,
                      source_sheet, source_gid, source_url, created_by)
                   VALUES (%s,%s,%s,'РМ',%s,%s,%s) RETURNING id""",
                (manager_user_id, spreadsheet_id, title, gid, canonical,
                 actor_user_id))
        source_id = cur.fetchone()["id"]
    conn.commit()
    return source_id


def manager_sources_data(conn: psycopg.Connection) -> list[dict[str, Any]]:
    with conn.cursor() as cur:
        cur.execute(
            """SELECT s.*, u.full_name AS manager_name, u.email AS manager_email,
                      coalesce((SELECT string_agg(sp.full_name, ', ' ORDER BY sp.full_name)
                                  FROM app.defect_rmb_team_member tm
                                  JOIN app.app_user sp ON sp.id=tm.specialist_user_id
                                 WHERE tm.manager_user_id=s.manager_user_id
                                   AND tm.active_to IS NULL),'') AS specialists,
                      count(i.id) FILTER (WHERE i.is_source_present) AS rows_total,
                      count(i.id) FILTER (WHERE i.is_source_present
                                           AND i.normalized_state='active') AS active_total,
                      count(i.id) FILTER (WHERE i.is_source_present
                                           AND i.normalized_state='completed') AS completed_total
                 FROM app.defect_rmb_manager_source s
                 JOIN app.app_user u ON u.id=s.manager_user_id
                 LEFT JOIN app.defect_rmb_manager_item i ON i.source_id=s.id
                WHERE s.is_active
                GROUP BY s.id, u.id
                ORDER BY u.full_name""")
        return cur.fetchall()


def manager_sources_for_user(conn: psycopg.Connection,
                             user_id: int) -> list[dict[str, Any]]:
    """Источники, доступные менеджеру, его специалистам или руководителю."""
    roles = user_roles(conn, user_id)
    sources = manager_sources_data(conn)
    if "leader" in roles:
        return sources
    with conn.cursor() as cur:
        cur.execute(
            """SELECT manager_user_id FROM app.defect_rmb_team_member
                WHERE specialist_user_id=%s AND active_to IS NULL""", (user_id,))
        team_managers = {row["manager_user_id"] for row in cur.fetchall()}
    return [source for source in sources
            if source["manager_user_id"] == user_id
            or source["manager_user_id"] in team_managers]


def manager_source_data(conn: psycopg.Connection, source_id: int) -> dict[str, Any]:
    with conn.cursor() as cur:
        cur.execute(
            """SELECT s.*, u.full_name AS manager_name, u.email AS manager_email
                 FROM app.defect_rmb_manager_source s
                 JOIN app.app_user u ON u.id=s.manager_user_id
                WHERE s.id=%s AND s.is_active""", (source_id,))
        source = cur.fetchone()
    if not source:
        raise ValueError("Источник рабочего места не найден")
    return source


def download_manager_source_csv(spreadsheet_id: str, gid: int,
                                client: httpx.Client | None = None) -> bytes:
    if not _MANAGER_SHEET_ID_RE.fullmatch(str(spreadsheet_id)) or int(gid) < 0:
        raise ValueError("Некорректный источник рабочего места")
    url = (f"https://docs.google.com/spreadsheets/d/{spreadsheet_id}/"
           f"export?format=csv&gid={int(gid)}")
    own_client = client is None
    if client is None:
        client = httpx.Client(
            follow_redirects=True,
            timeout=httpx.Timeout(150.0, connect=15.0),
            headers={"User-Agent": "OOuRC RMB manager workspace sync/1.0"})
    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 > 50 * 1024 * 1024:
                    raise ValueError("Экспорт вкладки «РМ» больше 50 МБ")
                chunks.append(chunk)
    except httpx.HTTPError as exc:
        raise ValueError(
            "Не удалось скачать вкладку «РМ». Проверьте доступ по ссылке") from exc
    finally:
        if own_client:
            client.close()
    data = b"".join(chunks)
    if not data or "text/html" in content_type or data.lstrip().startswith(b"<"):
        raise ValueError("Google Sheets запросил вход или вернул пустой файл")
    return data


def _manager_header(value: object) -> str:
    return " ".join(str(value or "").replace("\ufeff", "").split()).casefold()


def _manager_decimal(value: object) -> Decimal | None:
    source = str(value or "").strip().replace("\xa0", "").replace(" ", "")
    if not source or source.startswith("="):
        return None
    try:
        return Decimal(source.replace(",", ".")).quantize(Decimal("0.01"))
    except InvalidOperation:
        return None


def _manager_date(value: object) -> tuple[date | None, str | None]:
    source = _text(value)
    if not source:
        return None, None
    for pattern in ("%d.%m.%Y", "%d.%m.%y", "%Y-%m-%d"):
        try:
            return datetime.strptime(source, pattern).date(), source
        except ValueError:
            pass
    return None, source


def _manager_direction(plan: object) -> str | None:
    value = (_text(plan) or "").casefold()
    if not value:
        return None
    if "сервис" in value or "белвти" in value:
        return "service"
    if "распродаж" in value or "уцен" in value:
        return "discount"
    if any(part in value for part in
           ("утиль", "датком", "мет", "пластик", "бой склада")):
        return "writeoff"
    if "возврат" in value:
        return "supplier_return"
    if "разбраков" in value or "пересорт" in value:
        return "sorting"
    if "компенсац" in value or "претензи" in value:
        return "compensation"
    return "other"


def parse_manager_source_csv(data: bytes) -> dict[str, Any]:
    try:
        text = data.decode("utf-8-sig")
    except UnicodeDecodeError as exc:
        raise ValueError("Вкладка «РМ» должна быть экспортирована в UTF-8") from exc
    reader = csv.reader(io.StringIO(text))
    try:
        headers = next(reader)
    except StopIteration as exc:
        raise ValueError("Вкладка «РМ» пуста") from exc
    normalized: dict[str, list[int]] = {}
    display_headers: list[str] = []
    seen: dict[str, int] = {}
    for index, raw in enumerate(headers):
        display = " ".join(str(raw or "").split()) or f"Колонка {index + 1}"
        key = _manager_header(display)
        normalized.setdefault(key, []).append(index)
        seen[display] = seen.get(display, 0) + 1
        display_headers.append(
            display if seen[display] == 1 else f"{display} #{seen[display]}")

    def index_of(*aliases: str, occurrence: int = 0) -> int | None:
        for alias in aliases:
            found = normalized.get(_manager_header(alias), [])
            if len(found) > occurrence:
                return found[occurrence]
        return None

    columns = {
        "external_name": index_of("Наименование", "Номенклатура"),
        "unit_cost": index_of("СС", "Себестоимость"),
        "supplier_1c": index_of("Последний поставщик 1С"),
        "classification": index_of("Класификация брака", "Классификация брака"),
        "source_description": index_of("Описание 1С"),
        "primary_description": index_of("ПЕРВИЧНОЕ описание дефектов обработчиком"),
        "final_description": index_of("ФИНАЛЬНОЕ описание дефектов обработчиком"),
        "service_conclusion": index_of("Заключение СЦ"),
        "defect_type": index_of("Вид брака"),
        "plan_auto": index_of("План Авто"),
        "plan": index_of("План"),
        "plan_comment": index_of("Комментарий, почему расхождение между Палн авто и Статус"),
        "source_date": index_of("Дата", occurrence=0),
        "comments": index_of("Комментарии"),
        "supplier": index_of("Поставщик"),
        "quantity": index_of("Кол-во"),
        "status": index_of("Статус"),
        "result": index_of("Результат"),
        "result_date": index_of("Дата", occurrence=1),
        "location": index_of("Местонахождение товра", "Местонахождение товара"),
    }
    if columns["external_name"] is None:
        raise ValueError("На вкладке «РМ» не найдена колонка «Наименование»")

    rows: list[dict[str, Any]] = []
    for row_no, values in enumerate(reader, start=2):
        values += [""] * max(0, len(headers) - len(values))

        def get(field: str) -> str | None:
            ix = columns.get(field)
            return _text(values[ix]) if ix is not None and ix < len(values) else None

        name = get("external_name")
        if not name:
            continue
        source_date, source_date_raw = _manager_date(get("source_date"))
        result_date, result_date_raw = _manager_date(get("result_date"))
        result = get("result")
        state = ("completed" if (result or "").casefold() in _MANAGER_FINAL_RESULTS
                 else "active")
        raw = {display_headers[i]: _text(values[i])
               for i in range(len(headers)) if i < len(values) and _text(values[i])}
        final_description = get("final_description") or get("primary_description")
        stable = {
            "source_row": row_no, "external_name": name,
            "external_unit_key": _manager_unit_key(name),
            "unit_cost": str(_manager_decimal(get("unit_cost")) or ""),
            "classification": get("classification"),
            "defect_description": final_description,
            "source_plan": get("plan"), "source_status": get("status"),
            "source_result": result, "source_date_raw": source_date_raw,
            "result_date_raw": result_date_raw, "source_location": get("location"),
            "source_supplier": get("supplier") or get("supplier_1c"),
            "raw_data": raw,
        }
        fingerprint = hashlib.sha256(
            json.dumps(stable, ensure_ascii=False, sort_keys=True,
                       default=str).encode("utf-8")).hexdigest()
        rows.append({
            **stable, "row_fingerprint": fingerprint,
            "unit_cost": _manager_decimal(get("unit_cost")),
            "normalized_direction": _manager_direction(get("plan")),
            "normalized_state": state,
            "source_date": source_date, "result_date": result_date,
        })
    if not rows:
        raise ValueError("На вкладке «РМ» нет строк номенклатуры")
    return {"rows": rows, "headers": display_headers,
            "file_sha256": hashlib.sha256(data).hexdigest()}


def sync_manager_source(conn: psycopg.Connection, source_id: int,
                        actor_user_id: int, data: bytes) -> dict[str, Any]:
    source = manager_source_data(conn, source_id)
    parsed = parse_manager_source_csv(data)
    rows = parsed["rows"]
    keys = sorted({row["external_unit_key"] for row in rows
                   if row["external_unit_key"]})
    linked: dict[str, dict[str, Any]] = {}
    with conn.cursor() as cur:
        if keys:
            cur.execute(
                """SELECT DISTINCT ON (external_unit_key)
                          external_unit_key, id, product_id
                     FROM app.defect_handler_event
                    WHERE external_unit_key=ANY(%s) AND deleted_at IS NULL
                    ORDER BY external_unit_key,
                             coalesce(completed_at, processed_at, updated_at) DESC,
                             id DESC""", (keys,))
            linked = {row["external_unit_key"]: row for row in cur.fetchall()}
        cur.execute(
            """UPDATE app.defect_rmb_manager_item SET is_source_present=false
                WHERE source_id=%s""", (source_id,))
        params = []
        for row in rows:
            match = linked.get(row["external_unit_key"] or "", {})
            params.append((
                source_id, row["source_row"], row["row_fingerprint"],
                row["external_name"], row["external_unit_key"],
                match.get("product_id"), match.get("id"),
                source["manager_user_id"], row["unit_cost"],
                row["classification"], row["defect_description"],
                row["source_plan"], row["normalized_direction"],
                row["source_status"], row["source_result"],
                row["normalized_state"], row["source_date"],
                row["source_date_raw"], row["result_date"],
                row["result_date_raw"], row["source_location"],
                row["source_supplier"],
                json.dumps(row["raw_data"], ensure_ascii=False)))
        cur.executemany(
            """INSERT INTO app.defect_rmb_manager_item
                 (source_id, source_row, row_fingerprint, external_name,
                  external_unit_key, product_id, handler_event_id,
                  manager_user_id, unit_cost, classification,
                  defect_description, source_plan, normalized_direction,
                  source_status, source_result, normalized_state,
                  source_date, source_date_raw, result_date, result_date_raw,
                  source_location, source_supplier, raw_data)
               VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,
                       %s,%s,%s,%s,%s,%s,%s::jsonb)
               ON CONFLICT (source_id, source_row) DO UPDATE SET
                  row_fingerprint=excluded.row_fingerprint,
                  external_name=excluded.external_name,
                  external_unit_key=excluded.external_unit_key,
                  product_id=excluded.product_id,
                  handler_event_id=excluded.handler_event_id,
                  manager_user_id=excluded.manager_user_id,
                  unit_cost=excluded.unit_cost,
                  classification=excluded.classification,
                  defect_description=excluded.defect_description,
                  source_plan=excluded.source_plan,
                  normalized_direction=excluded.normalized_direction,
                  source_status=excluded.source_status,
                  source_result=excluded.source_result,
                  normalized_state=excluded.normalized_state,
                  source_date=excluded.source_date,
                  source_date_raw=excluded.source_date_raw,
                  result_date=excluded.result_date,
                  result_date_raw=excluded.result_date_raw,
                  source_location=excluded.source_location,
                  source_supplier=excluded.source_supplier,
                  raw_data=excluded.raw_data,
                  is_source_present=true, last_seen_at=now()""", params)
        result = {
            "rows_total": len(rows),
            "active_total": sum(r["normalized_state"] == "active" for r in rows),
            "completed_total": sum(r["normalized_state"] == "completed" for r in rows),
            "linked_handler": sum(bool(linked.get(r["external_unit_key"] or ""))
                                  for r in rows),
            "unknown_direction": sum(r["normalized_direction"] is None for r in rows),
            "headers_total": len(parsed["headers"]),
        }
        cur.execute(
            """UPDATE app.defect_rmb_manager_source
                  SET last_synced_at=now(), last_file_sha256=%s,
                      last_result=%s::jsonb, updated_at=now()
                WHERE id=%s""",
            (parsed["file_sha256"],
             json.dumps(result, ensure_ascii=False), source_id))
    conn.commit()
    return result


def manager_source_rows(conn: psycopg.Connection, source_id: int,
                        q: str = "", state: str = "", page: int = 1,
                        per_page: int = 100) -> dict[str, Any]:
    source = manager_source_data(conn, source_id)
    if state not in {"", "active", "completed"}:
        state = ""
    q = str(q or "").strip()
    pattern = f"%{q}%"
    page = max(1, page)
    per_page = max(20, min(per_page, 200))
    where = """WHERE i.source_id=%s AND i.is_source_present
                 AND (%s='' OR i.normalized_state=%s)
                 AND (%s='' OR i.external_name ILIKE %s
                              OR coalesce(i.external_unit_key,'') ILIKE %s
                              OR coalesce(i.source_plan,'') ILIKE %s
                              OR coalesce(i.source_status,'') ILIKE %s
                              OR coalesce(i.source_result,'') ILIKE %s)"""
    params = (source_id, state, state, q, pattern, pattern, pattern, pattern, pattern)
    with conn.cursor() as cur:
        cur.execute("SELECT count(*) AS total FROM app.defect_rmb_manager_item i " + where,
                    params)
        total = cur.fetchone()["total"]
        pages = max(1, (total + per_page - 1) // per_page)
        page = min(page, pages)
        cur.execute(
            """SELECT i.* FROM app.defect_rmb_manager_item i """ + where +
            " ORDER BY i.source_row LIMIT %s OFFSET %s",
            params + (per_page, (page - 1) * per_page))
        rows = cur.fetchall()
    return {"source": source, "rows": rows, "total": total,
            "page": page, "pages": pages, "state": state, "q": q}
