"""
Запросы раздела «Базовые настройки».

Здесь живут вводные данные расчёта, которые нельзя вывести из кода:

  * норма рабочего времени по месяцам — её на календарный год устанавливает
    Министерство труда и соцзащиты (статья 124 ТК), поэтому это ввод, а не
    формула;
  * особые дни года — праздничные и предпраздничные;
  * ставки за выполненные операции;
  * показатели KPI: направление, шкала, веса по категориям;
  * оклады по категориям должностей.

Всё историчное хранится периодами действия (valid_from / valid_to), а не
перезаписью: расчёт зарплаты за прошлый месяц должен считаться по ставкам,
которые действовали тогда. Поэтому «изменить ставку» — это закрыть прежний
период и открыть новый, а не UPDATE.

Права здесь не проверяются: это делает веб-слой (reference.write) и RLS.
"""

from __future__ import annotations

import re
from datetime import date
from typing import Any

import psycopg
import psycopg.sql


# ---------------------------------------------------------------------------
#  Норма рабочего времени
# ---------------------------------------------------------------------------

def norm_years(conn: psycopg.Connection) -> list[int]:
    """Годы, по которым норма хоть частично заведена."""
    with conn.cursor() as cur:
        cur.execute(
            "SELECT DISTINCT year FROM app.production_calendar ORDER BY year DESC"
        )
        return [r["year"] for r in cur.fetchall()]


NORM_SQL = """
    SELECT m.month,
           pc.days_calendar, pc.days_work, pc.days_preholiday,
           pc.days_off, pc.days_holiday,
           pc.norm_hours_40, pc.norm_hours_35,
           {extra}
    FROM generate_series(1, 12) AS m(month)
    LEFT JOIN app.production_calendar pc
           ON pc.year = %s AND pc.month = m.month
    {join}
    ORDER BY m.month
"""

NORM_EXTRA = "pc.source, pc.note, pc.updated_at, u.full_name AS updated_by_name"
NORM_JOIN = "LEFT JOIN app.app_user u ON u.id = pc.updated_by"

# Заглушки на случай старой базы: шаблон ждёт эти поля у каждой строки
NORM_EXTRA_STUB = {
    "source": None, "note": None, "updated_at": None, "updated_by_name": None,
}


def norm_year(conn: psycopg.Connection, year: int) -> list[dict[str, Any]]:
    """
    Двенадцать строк года — включая незаполненные месяцы.

    Незаполненные приходят с пустыми значениями, а не пропускаются: иначе в
    форме не было бы куда вписать норму нового года.

    Колонки source / note / updated_by появились миграцией 07. Если её ещё не
    накатили — а обновление кода и обновление базы это два разных действия, —
    страница всё равно должна открыться и сказать, что делать, а не отдать
    «Internal Server Error». Поэтому спрашиваем без них.
    """
    with conn.cursor() as cur:
        try:
            cur.execute(NORM_SQL.format(extra=NORM_EXTRA, join=NORM_JOIN), (year,))
            return cur.fetchall()
        except psycopg.errors.UndefinedColumn:
            # Прерванная транзакция отравляет весь остаток запроса
            conn.rollback()

    with conn.cursor() as cur:
        cur.execute(NORM_SQL.format(extra="NULL AS pad", join=""), (year,))
        return [{**r, **NORM_EXTRA_STUB} for r in cur.fetchall()]


def norm_is_editable(conn: psycopg.Connection) -> bool:
    """
    Можно ли записывать норму: есть ли в базе колонки миграции 07.

    Без них сохранение упало бы уже после нажатия кнопки — лучше не показывать
    кнопку вовсе и объяснить, что база отстала от кода.
    """
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT count(*) AS n
            FROM information_schema.columns
            WHERE table_schema = 'app'
              AND table_name = 'production_calendar'
              AND column_name IN ('source', 'note', 'updated_at', 'updated_by')
            """
        )
        return (cur.fetchone() or {}).get("n", 0) == 4


def save_norm_month(
    conn: psycopg.Connection,
    user_id: int,
    year: int,
    month: int,
    *,
    norm_hours_40: float,
    norm_hours_35: float,
    days_calendar: int,
    days_work: int,
    days_preholiday: int,
    days_off: int,
    days_holiday: int,
    note: str | None,
) -> None:
    """
    Записать норму месяца от имени пользователя.

    source становится 'manual', а updated_by — правившим: по этой отметке
    следующая миграция с официальными значениями обойдёт строку стороной и
    не затрёт решение администрации.
    """
    with conn.cursor() as cur:
        cur.execute(
            """
            INSERT INTO app.production_calendar
                (year, month, days_calendar, days_work, days_preholiday,
                 days_off, days_holiday, norm_hours_40, norm_hours_35,
                 source, note, updated_at, updated_by)
            VALUES (%(year)s, %(month)s, %(days_calendar)s, %(days_work)s,
                    %(days_preholiday)s, %(days_off)s, %(days_holiday)s,
                    %(n40)s, %(n35)s, 'manual', %(note)s, now(), %(uid)s)
            ON CONFLICT (year, month) DO UPDATE
               SET days_calendar   = EXCLUDED.days_calendar,
                   days_work       = EXCLUDED.days_work,
                   days_preholiday = EXCLUDED.days_preholiday,
                   days_off        = EXCLUDED.days_off,
                   days_holiday    = EXCLUDED.days_holiday,
                   norm_hours_40   = EXCLUDED.norm_hours_40,
                   norm_hours_35   = EXCLUDED.norm_hours_35,
                   source          = 'manual',
                   note            = EXCLUDED.note,
                   updated_at      = now(),
                   updated_by      = EXCLUDED.updated_by
            """,
            {
                "year": year, "month": month,
                "days_calendar": days_calendar, "days_work": days_work,
                "days_preholiday": days_preholiday, "days_off": days_off,
                "days_holiday": days_holiday,
                "n40": norm_hours_40, "n35": norm_hours_35,
                "note": note, "uid": user_id,
            },
        )


def special_days(conn: psycopg.Connection, year: int) -> list[dict[str, Any]]:
    """Праздничные и предпраздничные дни года — то, что отличается от обычного."""
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT day, day_type, source
            FROM app.calendar_day
            WHERE extract(year FROM day) = %s
              AND day_type IN ('holiday', 'preholiday')
            ORDER BY day
            """,
            (year,),
        )
        return cur.fetchall()


def set_day_type(conn: psycopg.Connection, day: date, day_type: str) -> None:
    with conn.cursor() as cur:
        cur.execute(
            """
            INSERT INTO app.calendar_day (day, day_type, source)
            VALUES (%s, %s, 'manual')
            ON CONFLICT (day) DO UPDATE
               SET day_type = EXCLUDED.day_type, source = 'manual'
            """,
            (day, day_type),
        )


def reset_day_type(conn: psycopg.Connection, day: date) -> None:
    """Вернуть дню обычный вид: рабочий или выходной по календарю."""
    with conn.cursor() as cur:
        cur.execute(
            """
            UPDATE app.calendar_day
               SET day_type = CASE WHEN extract(isodow FROM day) IN (6, 7)
                                   THEN 'weekend' ELSE 'work' END,
                   source = 'manual'
             WHERE day = %s
            """,
            (day,),
        )


# ---------------------------------------------------------------------------
#  Ставки за выполненные операции
# ---------------------------------------------------------------------------

UNIT_RU = {"qty": "за единицу", "weight": "за килограмм", "volume": "за м³"}


def operation_types(conn: psycopg.Connection) -> list[dict[str, Any]]:
    with conn.cursor() as cur:
        cur.execute(
            "SELECT id, code, name, has_weight, has_volume "
            "FROM app.operation_type ORDER BY name"
        )
        return cur.fetchall()


def operation_rates(conn: psycopg.Connection, on_date: date) -> list[dict[str, Any]]:
    """Действующие на дату ставки. NULL в депо означает «для всех депо»."""
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT r.id, r.unit, r.rate, r.valid_from, r.valid_to,
                   ot.id AS operation_type_id, ot.code, ot.name,
                   ou.name AS unit_name
            FROM app.operation_rate r
            JOIN app.operation_type ot ON ot.id = r.operation_type_id
            LEFT JOIN app.org_unit ou  ON ou.id = r.org_unit_id
            WHERE r.valid_from <= %(d)s
              AND (r.valid_to IS NULL OR r.valid_to > %(d)s)
            ORDER BY ot.name, r.unit, ou.name NULLS FIRST
            """,
            {"d": on_date},
        )
        return cur.fetchall()


def set_operation_rate(
    conn: psycopg.Connection,
    *,
    operation_type_id: int,
    unit: str,
    org_unit_id: int | None,
    rate: float,
    valid_from: date,
) -> None:
    """
    Новая ставка с даты: прежний период закрывается этой же датой.

    Так работает и ограничение в базе — периоды одной ставки не могут
    пересекаться, поэтому просто вставить новую строку нельзя.
    """
    with conn.cursor() as cur:
        cur.execute(
            """
            UPDATE app.operation_rate
               SET valid_to = %(from)s
             WHERE operation_type_id = %(op)s
               AND unit = %(unit)s
               AND coalesce(org_unit_id, 0) = coalesce(%(ou)s, 0)
               AND valid_from < %(from)s
               AND (valid_to IS NULL OR valid_to > %(from)s)
            """,
            {"op": operation_type_id, "unit": unit, "ou": org_unit_id,
             "from": valid_from},
        )
        cur.execute(
            """
            DELETE FROM app.operation_rate
             WHERE operation_type_id = %(op)s
               AND unit = %(unit)s
               AND coalesce(org_unit_id, 0) = coalesce(%(ou)s, 0)
               AND valid_from >= %(from)s
            """,
            {"op": operation_type_id, "unit": unit, "ou": org_unit_id,
             "from": valid_from},
        )
        cur.execute(
            """
            INSERT INTO app.operation_rate
                (operation_type_id, unit, org_unit_id, rate, valid_from)
            VALUES (%(op)s, %(unit)s, %(ou)s, %(rate)s, %(from)s)
            """,
            {"op": operation_type_id, "unit": unit, "ou": org_unit_id,
             "rate": rate, "from": valid_from},
        )


# ---------------------------------------------------------------------------
#  Оклады по категориям должностей
# ---------------------------------------------------------------------------

def position_categories(conn: psycopg.Connection) -> list[dict[str, Any]]:
    with conn.cursor() as cur:
        cur.execute(
            "SELECT id, code, name, sort_order FROM app.position_category "
            "ORDER BY sort_order, name"
        )
        return cur.fetchall()


def salaries(conn: psycopg.Connection, on_date: date) -> list[dict[str, Any]]:
    """Оклад каждой категории: действующий на дату и когда установлен."""
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT pc.id, pc.code, pc.name, pc.sort_order,
                   s.amount, s.valid_from, s.valid_to
            FROM app.position_category pc
            LEFT JOIN app.position_base_salary s
                   ON s.position_category_id = pc.id
                  AND s.valid_from <= %(d)s
                  AND (s.valid_to IS NULL OR s.valid_to > %(d)s)
            ORDER BY pc.sort_order, pc.name
            """,
            {"d": on_date},
        )
        return cur.fetchall()


def set_salary(
    conn: psycopg.Connection,
    *,
    category_id: int,
    amount: float,
    valid_from: date,
) -> None:
    """Новый оклад категории с даты; прежний закрывается этой датой."""
    with conn.cursor() as cur:
        cur.execute(
            """
            UPDATE app.position_base_salary
               SET valid_to = %(from)s
             WHERE position_category_id = %(cat)s
               AND valid_from < %(from)s
               AND (valid_to IS NULL OR valid_to > %(from)s)
            """,
            {"cat": category_id, "from": valid_from},
        )
        cur.execute(
            "DELETE FROM app.position_base_salary "
            " WHERE position_category_id = %(cat)s AND valid_from >= %(from)s",
            {"cat": category_id, "from": valid_from},
        )
        cur.execute(
            """
            INSERT INTO app.position_base_salary
                (position_category_id, amount, valid_from)
            VALUES (%(cat)s, %(amount)s, %(from)s)
            """,
            {"cat": category_id, "amount": amount, "from": valid_from},
        )


# ---------------------------------------------------------------------------
#  Показатели KPI
# ---------------------------------------------------------------------------

BETTER_RU = {"lower": "меньше — лучше", "higher": "больше — лучше"}


def kpi_list(conn: psycopg.Connection, on_date: date) -> list[dict[str, Any]]:
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT k.id, k.code, k.name, k.description, k.better_when,
                   k.source, k.is_active,
                   (SELECT count(*) FROM app.kpi_scale s
                     WHERE s.kpi_id = k.id
                       AND s.valid_from <= %(d)s
                       AND (s.valid_to IS NULL OR s.valid_to > %(d)s)) AS steps
            FROM app.kpi k
            ORDER BY k.is_active DESC, k.name
            """,
            {"d": on_date},
        )
        return cur.fetchall()


def kpi_scale(conn: psycopg.Connection, kpi_id: int, on_date: date) -> list[dict[str, Any]]:
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT step_no, threshold, coef_manager, coef_piecework,
                   valid_from, valid_to
            FROM app.kpi_scale
            WHERE kpi_id = %(k)s
              AND valid_from <= %(d)s
              AND (valid_to IS NULL OR valid_to > %(d)s)
            ORDER BY step_no
            """,
            {"k": kpi_id, "d": on_date},
        )
        return cur.fetchall()


def kpi_one(conn: psycopg.Connection, kpi_id: int) -> dict[str, Any] | None:
    """Один показатель — для формы правки."""
    with conn.cursor() as cur:
        cur.execute(
            "SELECT id, code, name, description, better_when, source, is_active "
            "  FROM app.kpi WHERE id = %s",
            (kpi_id,),
        )
        return cur.fetchone()


def set_kpi_scale(
    conn: psycopg.Connection,
    *,
    kpi_id: int,
    steps: list[tuple[float, float, float]],
    valid_from: date,
) -> int:
    """
    Заменить шкалу показателя с указанной даты.

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

    Порог идёт по возрастанию: у показателя «меньше — лучше» это границы
    «результат не выше», и коэффициент от ступени к ступени не растёт; у
    «больше — лучше» порог тоже растёт, а коэффициент вместе с ним.
    """
    if not steps:
        raise ValueError("В шкале нужна хотя бы одна ступень")
    if len(steps) > 20:
        raise ValueError("Ступеней в шкале не больше двадцати")

    prev = None
    for i, (threshold, c_man, c_pie) in enumerate(steps, start=1):
        if prev is not None and threshold <= prev:
            raise ValueError(
                f"Ступень {i}: порог {threshold:g} не больше предыдущего "
                f"{prev:g}. Пороги идут по возрастанию."
            )
        prev = threshold
        for who, c in (("руководителю", c_man), ("сдельщику", c_pie)):
            if not 0 <= c <= 2:
                raise ValueError(
                    f"Ступень {i}: коэффициент {who} — от 0 до 2, а не {c:g}"
                )

    with conn.cursor() as cur:
        cur.execute(
            """
            UPDATE app.kpi_scale SET valid_to = %(from)s
             WHERE kpi_id = %(k)s AND valid_from < %(from)s
               AND (valid_to IS NULL OR valid_to > %(from)s)
            """,
            {"k": kpi_id, "from": valid_from},
        )
        cur.execute(
            "DELETE FROM app.kpi_scale "
            " WHERE kpi_id = %(k)s AND valid_from >= %(from)s",
            {"k": kpi_id, "from": valid_from},
        )
        for step_no, (threshold, c_man, c_pie) in enumerate(steps, start=1):
            cur.execute(
                """
                INSERT INTO app.kpi_scale
                    (kpi_id, step_no, threshold, coef_manager,
                     coef_piecework, valid_from)
                VALUES (%(k)s, %(n)s, %(t)s, %(cm)s, %(cp)s, %(from)s)
                """,
                {"k": kpi_id, "n": step_no, "t": threshold,
                 "cm": c_man, "cp": c_pie, "from": valid_from},
            )
    return len(steps)


# ---------------------------------------------------------------------------
#  Системные настройки
#
#  Просьба заказчика от 14.09.2026: решения о поведении заставки должны
#  настраиваться, а не быть зашиты в код. Название, пояснение и допустимые
#  значения живут здесь, рядом друг с другом: их читает человек, и меняются
#  они вместе с поведением, а не отдельно от него.
# ---------------------------------------------------------------------------

APP_SETTINGS = [
    {
        "code": "login_animation",
        "name": "Движение на экране входа",
        "note": "Падающие столбцы на заставке. «По настройке системы» — самый "
                "бережный вариант: Windows и браузер умеют сообщать, что "
                "человек просил не анимировать, и для части людей движущийся "
                "фон означает головную боль.",
        "options": [
            ("auto", "По настройке системы пользователя"),
            ("on", "Всегда включено"),
            ("off", "Выключено, неподвижный кадр"),
        ],
    },
    {
        "code": "login_pause_hidden",
        "name": "Останавливать рисование в свёрнутой вкладке",
        "note": "Пока вкладку не смотрят, рисовать незачем — это чужой заряд "
                "батареи. Выключать стоит разве что для показа на стенде, "
                "где окно уходит на второй план, а картинка должна жить.",
        "options": [
            ("on", "Останавливать"),
            ("off", "Рисовать всегда"),
        ],
    },
    {
        "code": "login_frame_fallback",
        "name": "Запасной таймер, если браузер не выдаёт кадры",
        "note": "Встречаются окружения, где кадры анимации не приходят вовсе: "
                "встроенные панели просмотра, киоски, терминалы. Там заставка "
                "застывает на первом кадре, и понять, что она сломана, не по "
                "чему. Запасной таймер это лечит ценой чуть большей нагрузки.",
        "options": [
            ("on", "Включён"),
            ("off", "Выключен"),
        ],
    },
]

#  23.10.2026 13:26 МСК: визуально это тележка контроля качества отдела
#  брака; внутренний код сохранён для совместимости с действующей настройкой.
APP_SETTINGS.append({
    "code": "header_forklift",
    "name": "Знак отдела брака в шапке",
    "note": "При переходе и обновлении страницы тележка контроля качества "
            "едет по фирменной линии к выбранному разделу. «По настройке "
            "системы» — не двигается у тех, кто в Windows или браузере "
            "попросил обходиться без анимации.",
    "options": [
        ("auto", "По настройке системы пользователя"),
        ("on", "Всегда выезжает"),
        ("still", "Стоит на месте, без движения"),
        ("off", "Не показывать"),
    ],
})

APP_SETTING_DEFAULTS = {s["code"]: s["options"][0][0] for s in APP_SETTINGS}


def app_settings(conn: psycopg.Connection) -> dict[str, str]:
    """
    Системные настройки: код → значение.

    Чего в базе нет — берётся из значений по умолчанию. Так приложение
    работает и до накатывания миграции 33, и если строку кто-то удалил.
    """
    out = dict(APP_SETTING_DEFAULTS)
    with conn.cursor() as cur:
        cur.execute("SELECT to_regclass('app.app_setting') IS NOT NULL AS ok")
        if not cur.fetchone()["ok"]:
            return out
        cur.execute("SELECT code, value FROM app.app_setting")
        for row in cur.fetchall():
            if row["code"] in out:
                out[row["code"]] = row["value"]
    return out


def set_app_setting(conn: psycopg.Connection, code: str, value: str,
                    user_id: int | None = None) -> None:
    """
    Сохранить настройку. Неизвестный код или значение вне списка не
    записываются: в базу должно попадать только то, что приложение умеет
    прочитать обратно.
    """
    known = {s["code"]: [v for v, _ in s["options"]] for s in APP_SETTINGS}
    if code not in known:
        raise ValueError(f"Неизвестная настройка: {code}")
    if value not in known[code]:
        raise ValueError(
            f"Недопустимое значение «{value}» для настройки «{code}»")
    with conn.cursor() as cur:
        cur.execute(
            """
            INSERT INTO app.app_setting (code, value, updated_by)
            VALUES (%s, %s, %s)
            ON CONFLICT (code) DO UPDATE
               SET value = EXCLUDED.value,
                   updated_by = EXCLUDED.updated_by,
                   updated_at = now()
            """,
            (code, value, user_id),
        )


def app_settings_view(conn: psycopg.Connection) -> list[dict[str, Any]]:
    """То же, но с названиями и кто менял — для экрана."""
    current = app_settings(conn)
    changed: dict[str, dict[str, Any]] = {}
    with conn.cursor() as cur:
        cur.execute("SELECT to_regclass('app.app_setting') IS NOT NULL AS ok")
        if cur.fetchone()["ok"]:
            cur.execute(
                """
                SELECT s.code, s.updated_at, u.full_name AS updated_by_name
                FROM app.app_setting s
                LEFT JOIN app.app_user u ON u.id = s.updated_by
                """
            )
            changed = {r["code"]: r for r in cur.fetchall()}

    return [
        {**item,
         "value": current[item["code"]],
         "updated_at": (changed.get(item["code"]) or {}).get("updated_at"),
         "updated_by_name": (changed.get(item["code"]) or {}).get("updated_by_name")}
        for item in APP_SETTINGS
    ]


def kpi_weights(conn: psycopg.Connection, on_date: date) -> list[dict[str, Any]]:
    """Веса показателей по категориям должностей: чей результат чем считается."""
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT w.id, w.weight, w.valid_from, w.valid_to,
                   k.id AS kpi_id, k.code AS kpi_code, k.name AS kpi_name,
                   pc.id AS category_id, pc.name AS category_name, pc.sort_order
            FROM app.kpi_weight w
            JOIN app.kpi k ON k.id = w.kpi_id
            JOIN app.position_category pc ON pc.id = w.position_category_id
            WHERE w.valid_from <= %(d)s
              AND (w.valid_to IS NULL OR w.valid_to > %(d)s)
            ORDER BY pc.sort_order, k.name
            """,
            {"d": on_date},
        )
        return cur.fetchall()


class WeightOverflow(ValueError):
    """Удельные веса категории в сумме перевалили за 100 %."""


def kpi_weight_sum(conn: psycopg.Connection, category_id: int, on_date: date,
                   *, exclude_kpi: int | None = None) -> float:
    """
    Сумма действующих на дату весов категории должностей.

    Нужна двум местам: проверке при сохранении и подсказке на экране —
    сколько уже роздано и сколько осталось.
    """
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT coalesce(sum(weight), 0) AS total
            FROM app.kpi_weight
            WHERE position_category_id = %(cat)s
              AND valid_from <= %(d)s
              AND (valid_to IS NULL OR valid_to > %(d)s)
              AND (%(skip)s::bigint IS NULL OR kpi_id <> %(skip)s)
            """,
            {"cat": category_id, "d": on_date, "skip": exclude_kpi},
        )
        return float(cur.fetchone()["total"])


def set_kpi_weight(
    conn: psycopg.Connection,
    *,
    kpi_id: int,
    category_id: int,
    weight: float,
    valid_from: date,
) -> None:
    """
    Удельный вес показателя в премии категории должностей, с даты действия.

    Пункт 32 плана: при перераспределении вес не может увести сумму за 100 %.
    Премия делится между показателями, и сумма больше единицы означала бы,
    что депо раздали больше фонда, чем в нём есть. Проверяем до записи и
    отказываем с цифрами, а не просто «нельзя»: человек должен видеть, на
    сколько он вышел за предел и у какого показателя отнять.
    """
    others = kpi_weight_sum(conn, category_id, valid_from, exclude_kpi=kpi_id)
    #  Копейки округления допускаем: веса вводят как 0,3333 — три таких дают
    #  0,9999, и придираться к четвёртому знаку здесь не к чему
    if round(others + weight, 4) > 1.0001:
        raise WeightOverflow(
            f"Сумма удельных весов вышла за 100 %: остальные показатели "
            f"занимают {others * 100:.2f} %, этот добавляет {weight * 100:.2f} %, "
            f"вместе {(others + weight) * 100:.2f} %. "
            f"Свободно {max(0.0, 1 - others) * 100:.2f} % — "
            f"сначала уменьшите вес другого показателя."
        )

    with conn.cursor() as cur:
        cur.execute(
            """
            UPDATE app.kpi_weight
               SET valid_to = %(from)s
             WHERE kpi_id = %(k)s AND position_category_id = %(cat)s
               AND valid_from < %(from)s
               AND (valid_to IS NULL OR valid_to > %(from)s)
            """,
            {"k": kpi_id, "cat": category_id, "from": valid_from},
        )
        cur.execute(
            "DELETE FROM app.kpi_weight "
            " WHERE kpi_id = %(k)s AND position_category_id = %(cat)s "
            "   AND valid_from >= %(from)s",
            {"k": kpi_id, "cat": category_id, "from": valid_from},
        )
        if weight > 0:
            cur.execute(
                """
                INSERT INTO app.kpi_weight
                    (kpi_id, position_category_id, weight, valid_from)
                VALUES (%(k)s, %(cat)s, %(w)s, %(from)s)
                """,
                {"k": kpi_id, "cat": category_id, "w": weight, "from": valid_from},
            )


def kpi_funds(conn: psycopg.Connection, on_date: date) -> list[dict[str, Any]]:
    """
    Фонды КПИ: максимум премии за все показатели вместе, по паре
    «депо + категория должностей».

    Именно пары, а не одной должности: базовые оклады во всех двенадцати
    депо одинаковы, а фонды различаются четырьмя группами. В действующем
    файле это видно только тем, кто откроет лист «Мотивация» и сравнит
    двенадцать блоков по тридцать строк вручную.
    """
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT f.id, f.amount, f.valid_from, f.valid_to,
                   ou.id AS unit_id, ou.code AS unit_code, ou.name AS unit_name,
                   pc.id AS category_id, pc.name AS category_name, pc.sort_order
            FROM app.kpi_fund f
            JOIN app.org_unit ou           ON ou.id = f.org_unit_id
            JOIN app.position_category pc  ON pc.id = f.position_category_id
            WHERE f.valid_from <= %(d)s
              AND (f.valid_to IS NULL OR f.valid_to > %(d)s)
            ORDER BY ou.name, pc.sort_order
            """,
            {"d": on_date},
        )
        return cur.fetchall()


def set_kpi_fund(
    conn: psycopg.Connection,
    *,
    unit_id: int,
    category_id: int,
    amount: float,
    valid_from: date,
) -> None:
    """
    Новый фонд с даты. Прежний закрывается этой же датой, а не переписывается:
    расчёт за прошлый месяц обязан остаться со своим фондом.
    """
    with conn.cursor() as cur:
        cur.execute(
            """
            UPDATE app.kpi_fund
               SET valid_to = %(from)s
             WHERE org_unit_id = %(u)s AND position_category_id = %(cat)s
               AND valid_from < %(from)s
               AND (valid_to IS NULL OR valid_to > %(from)s)
            """,
            {"u": unit_id, "cat": category_id, "from": valid_from},
        )
        cur.execute(
            "DELETE FROM app.kpi_fund "
            " WHERE org_unit_id = %(u)s AND position_category_id = %(cat)s "
            "   AND valid_from >= %(from)s",
            {"u": unit_id, "cat": category_id, "from": valid_from},
        )
        cur.execute(
            """
            INSERT INTO app.kpi_fund
                (org_unit_id, position_category_id, amount, valid_from)
            VALUES (%(u)s, %(cat)s, %(a)s, %(from)s)
            """,
            {"u": unit_id, "cat": category_id, "a": amount, "from": valid_from},
        )


# ---------------------------------------------------------------------------
#  Должности и справочники видов
# ---------------------------------------------------------------------------

def positions(conn: psycopg.Connection) -> list[dict[str, Any]]:
    """Все должности, включая выключенные, — со счётчиком занятых мест."""
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT p.id, p.code, p.name, p.note, p.is_active, p.sort_order,
                   pc.id AS category_id, pc.name AS category_name,
                   pc.code AS category_code, pc.is_piecework, pc.sort_order AS cat_order,
                   (SELECT count(*) FROM app.employment em
                     WHERE em.position_id = p.id AND em.terminated_at IS NULL)
                       AS employed
            FROM app.position p
            JOIN app.position_category pc ON pc.id = p.category_id
            ORDER BY pc.sort_order, p.sort_order, p.name
            """
        )
        return cur.fetchall()


def delete_position(
    conn: psycopg.Connection, *, position_id: int, move_to_id: int
) -> tuple[str, int]:
    """
    Убрать должность из справочника, переведя всех на другую должность.

    Просто удалить нельзя: на должность ссылаются назначения, расчёты и
    кандидаты. Поэтому сначала все ссылки переводятся на указанную должность,
    и только потом строка удаляется — история при этом не теряется, меняется
    только название места.

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

    Возвращает название убранной должности и число переведённых назначений.
    """
    if position_id == move_to_id:
        raise ValueError("Переводить нужно на другую должность")

    with conn.cursor() as cur:
        cur.execute(
            "SELECT id, name, category_id FROM app.position WHERE id = ANY(%s)",
            ([position_id, move_to_id],),
        )
        found = {r["id"]: r for r in cur.fetchall()}
        gone, keep = found.get(position_id), found.get(move_to_id)
        if gone is None or keep is None:
            raise ValueError("Должность не найдена — обновите страницу")
        if gone["category_id"] != keep["category_id"]:
            raise ValueError(
                f"«{keep['name']}» из другой категории должностей. "
                "Внутри справочника перевод возможен только в своей категории: "
                "от неё зависят оклад и премия."
            )

        cur.execute(
            """
            SELECT count(*) AS n FROM app.employment
             WHERE position_id = %s AND terminated_at IS NULL
            """,
            (position_id,),
        )
        moved = int(cur.fetchone()["n"])

        # Список таблиц со ссылкой на должность берём из каталога внешних
        # ключей: если завтра появится новая таблица, удаление не сломается
        cur.execute(
            """
            SELECT c.conrelid::regclass::text AS tbl, a.attname AS col
              FROM pg_constraint c
              JOIN unnest(c.conkey) AS k(attnum) ON true
              JOIN pg_attribute a
                ON a.attrelid = c.conrelid AND a.attnum = k.attnum
             WHERE c.contype = 'f'
               AND c.confrelid = 'app.position'::regclass
            """
        )
        for ref in cur.fetchall():
            cur.execute(
                psycopg.sql.SQL("UPDATE {} SET {} = %s WHERE {} = %s").format(
                    psycopg.sql.SQL(ref["tbl"]),
                    psycopg.sql.Identifier(ref["col"]),
                    psycopg.sql.Identifier(ref["col"]),
                ),
                (move_to_id, position_id),
            )

        cur.execute("DELETE FROM app.position WHERE id = %s", (position_id,))
        if cur.rowcount == 0:
            raise ValueError("Должность не удалилась — обновите страницу")

    return gone["name"], moved


def save_position(
    conn: psycopg.Connection,
    *,
    position_id: int | None,
    code: str,
    name: str,
    category_id: int,
    note: str | None,
    is_active: bool,
    sort_order: int,
) -> None:
    """
    Добавить или поправить должность.

    Категория задаёт, сдельная это должность или окладная, и от неё же берётся
    оклад — поэтому она обязательна. Ненужную должность либо выключают, и она
    перестаёт появляться в выборе при приёме и переводе, либо убирают совсем
    через delete_position() — с переводом людей на другую должность
    категории, потому что на должность ссылаются назначения и расчёты.
    """
    if not code.strip() or not name.strip():
        raise ValueError("Нужны и код, и название должности")
    with conn.cursor() as cur:
        if position_id:
            cur.execute(
                """
                UPDATE app.position
                   SET code = %(code)s, name = %(name)s,
                       category_id = %(cat)s, note = %(note)s,
                       is_active = %(act)s, sort_order = %(sort)s
                 WHERE id = %(id)s
                """,
                {"id": position_id, "code": code.strip(), "name": name.strip(),
                 "cat": category_id, "note": note, "act": is_active,
                 "sort": sort_order},
            )
        else:
            cur.execute(
                """
                INSERT INTO app.position (code, name, category_id, note,
                                          is_active, sort_order)
                VALUES (%(code)s, %(name)s, %(cat)s, %(note)s, %(act)s, %(sort)s)
                """,
                {"code": code.strip(), "name": name.strip(), "cat": category_id,
                 "note": note, "act": is_active, "sort": sort_order},
            )


REF_TABLES = {
    "certificate": ("app.certificate_type", "виды свидетельств"),
    "medical": ("app.medical_exam_type", "виды медосмотров"),
    "briefing": ("app.briefing_type", "виды инструктажей по ОТ"),
}


# ---------------------------------------------------------------------------
#  Нормы выдачи СИЗ
# ---------------------------------------------------------------------------

PPE_KINDS = [
    ("summer", "летняя"),
    ("winter", "зимняя"),
    ("footwear", "обувь"),
    ("other", "прочее"),
]


def ppe_norms(conn: psycopg.Connection, on_date: date) -> list[dict[str, Any]]:
    """
    Нормы выдачи СИЗ, действующие на дату, по всем категориям должностей.

    Отдаются подряд, а не по категориям: группировкой занимается шаблон, ему
    же нужен и порядок «категория → смысловой номер строки». Закрытые нормы
    (valid_to в прошлом) сюда не попадают — они видны только в истории.
    """
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT n.id, n.position_category_id, pc.code AS category_code,
                   pc.name AS category_name, pc.sort_order AS category_order,
                   n.ppe_kind, n.item_name, n.classification, n.qty,
                   n.wear_period_months, n.condition_note, n.sort_order,
                   coalesce(n.no_size, false) AS no_size,
                   n.valid_from, n.valid_to
            FROM app.ppe_norm n
            JOIN app.position_category pc ON pc.id = n.position_category_id
            WHERE n.valid_from <= %(d)s
              AND (n.valid_to IS NULL OR n.valid_to > %(d)s)
            ORDER BY pc.sort_order, n.sort_order, n.item_name
            """,
            {"d": on_date},
        )
        return cur.fetchall()


def ppe_norm_one(conn: psycopg.Connection, norm_id: int) -> dict[str, Any] | None:
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT n.id, n.position_category_id, n.ppe_kind, n.item_name,
                   n.classification, n.qty, n.wear_period_months,
                   coalesce(n.no_size, false) AS no_size,
                   n.condition_note, n.sort_order, n.valid_from, n.valid_to
            FROM app.ppe_norm n WHERE n.id = %s
            """,
            (norm_id,),
        )
        return cur.fetchone()


def save_ppe_norm(
    conn: psycopg.Connection,
    *,
    norm_id: int | None,
    category_id: int,
    ppe_kind: str,
    item_name: str,
    classification: str | None,
    qty: float,
    wear_period_months: int | None,
    condition_note: str | None,
    sort_order: int,
    valid_from: date,
    no_size: bool = False,
) -> None:
    """
    Добавить строку нормы или поправить существующую.

    Срок носки пустой — «до износа»: в базе это NULL, и подставлять вместо
    него ноль нельзя, иначе выданное сразу окажется просроченным.

    Проверки идут до записи, чтобы в ответ приходило понятное сообщение, а
    не текст ошибки базы.
    """
    item_name = item_name.strip()
    if not item_name:
        raise ValueError("Нужно наименование средства индивидуальной защиты")
    if ppe_kind not in dict(PPE_KINDS):
        raise ValueError("Неизвестный вид СИЗ")
    if qty <= 0:
        raise ValueError("Количество должно быть больше нуля")
    if wear_period_months is not None and not 1 <= wear_period_months <= 240:
        raise ValueError("Срок носки — от 1 до 240 месяцев или пусто (до износа)")

    params = {
        "id": norm_id, "cat": category_id, "kind": ppe_kind,
        "item": item_name, "cls": (classification or "").strip() or None,
        "qty": qty, "months": wear_period_months,
        "cond": (condition_note or "").strip() or None,
        "sort": sort_order, "from": valid_from, "nosize": bool(no_size),
    }
    with conn.cursor() as cur:
        if norm_id:
            cur.execute(
                """
                UPDATE app.ppe_norm
                   SET position_category_id = %(cat)s, ppe_kind = %(kind)s,
                       item_name = %(item)s, classification = %(cls)s,
                       qty = %(qty)s, wear_period_months = %(months)s,
                       condition_note = %(cond)s, sort_order = %(sort)s,
                       valid_from = %(from)s, no_size = %(nosize)s
                 WHERE id = %(id)s
                """,
                params,
            )
        else:
            cur.execute(
                """
                INSERT INTO app.ppe_norm
                    (position_category_id, ppe_kind, item_name, classification,
                     qty, wear_period_months, condition_note, sort_order,
                     valid_from, no_size)
                VALUES (%(cat)s, %(kind)s, %(item)s, %(cls)s, %(qty)s,
                        %(months)s, %(cond)s, %(sort)s, %(from)s, %(nosize)s)
                ON CONFLICT (position_category_id, item_name, valid_from)
                DO UPDATE SET ppe_kind = excluded.ppe_kind,
                              classification = excluded.classification,
                              qty = excluded.qty,
                              wear_period_months = excluded.wear_period_months,
                              condition_note = excluded.condition_note,
                              sort_order = excluded.sort_order,
                              no_size = excluded.no_size,
                              valid_to = NULL
                """,
                params,
            )


def close_ppe_norm(
    conn: psycopg.Connection, norm_id: int, valid_to: date
) -> None:
    """
    Закрыть строку нормы датой, а не удалить.

    Норма — основание выдачи: по ней объясняется, почему работнику выдали
    именно это и на какой срок. Удалённая строка оставила бы выданное без
    основания, поэтому она закрывается датой и уходит из действующих.
    """
    with conn.cursor() as cur:
        cur.execute(
            "SELECT valid_from FROM app.ppe_norm WHERE id = %s", (norm_id,)
        )
        row = cur.fetchone()
        if row is None:
            raise ValueError("Строка нормы не найдена — обновите страницу")
        if valid_to <= row["valid_from"]:
            raise ValueError("Дата окончания не позже даты начала действия")
        cur.execute(
            "UPDATE app.ppe_norm SET valid_to = %s WHERE id = %s",
            (valid_to, norm_id),
        )


def units_with_chief(conn: psycopg.Connection) -> list[dict[str, Any]]:
    """Депо и кто на них подписывает ведомости на выдачу СИЗ."""
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT id, code, name, full_name, chief_position, chief_name
            FROM app.org_unit
            WHERE kind = 'rc' AND is_active
            ORDER BY name
            """
        )
        return cur.fetchall()


def set_unit_chief(
    conn: psycopg.Connection,
    *,
    unit_id: int,
    chief_position: str | None,
    chief_name: str | None,
) -> None:
    """Должность и ФИО лица, выдающего СИЗ на депо."""
    with conn.cursor() as cur:
        cur.execute(
            """
            UPDATE app.org_unit
               SET chief_position = %s, chief_name = %s
             WHERE id = %s AND kind = 'rc'
            """,
            ((chief_position or "").strip() or None,
             (chief_name or "").strip() or None, unit_id),
        )
        if cur.rowcount == 0:
            raise ValueError("Депо не найдено — обновите страницу")


def ref_types(conn: psycopg.Connection, kind: str) -> list[dict[str, Any]]:
    """Справочник видов свидетельств или медосмотров."""
    table = REF_TABLES[kind][0]
    with conn.cursor() as cur:
        cur.execute(
            f"SELECT id, code, name, note, sort_order, is_active "
            f"FROM {table} ORDER BY sort_order, name"
        )
        return cur.fetchall()


def briefing_matrix(conn: psycopg.Connection) -> set[tuple[int, int]]:
    """
    Пары «вид инструктажа — должность», по которым инструктаж требуется.

    Множеством, а не списком: шаблон спрашивает «есть ли отметка в этой
    клетке», и это ровно проверка на вхождение.
    """
    with conn.cursor() as cur:
        cur.execute(
            "SELECT briefing_type_id, position_id FROM app.briefing_position"
        )
        return {(r["briefing_type_id"], r["position_id"]) for r in cur.fetchall()}


def save_briefing_matrix(
    conn: psycopg.Connection, pairs: set[tuple[int, int]], user_id: int | None
) -> int:
    """
    Записать матрицу целиком: что отмечено — то и требуется.

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

    Возвращает число изменённых клеток.
    """
    with conn.cursor() as cur:
        cur.execute(
            "SELECT briefing_type_id, position_id FROM app.briefing_position"
        )
        was = {(r["briefing_type_id"], r["position_id"]) for r in cur.fetchall()}

        for type_id, position_id in pairs - was:
            cur.execute(
                "INSERT INTO app.briefing_position "
                "       (briefing_type_id, position_id, updated_by) "
                "VALUES (%s, %s, %s) ON CONFLICT DO NOTHING",
                (type_id, position_id, user_id),
            )
        for type_id, position_id in was - pairs:
            cur.execute(
                "DELETE FROM app.briefing_position "
                " WHERE briefing_type_id = %s AND position_id = %s",
                (type_id, position_id),
            )
    return len(pairs ^ was)


def save_ref_type(
    conn: psycopg.Connection,
    kind: str,
    *,
    type_id: int | None,
    code: str,
    name: str,
    note: str | None,
    is_active: bool,
    sort_order: int,
) -> None:
    """Добавить или поправить вид свидетельства либо медосмотра."""
    table = REF_TABLES[kind][0]
    if not code.strip() or not name.strip():
        raise ValueError("Нужны и код, и название")
    with conn.cursor() as cur:
        if type_id:
            cur.execute(
                f"""
                UPDATE {table}
                   SET code = %(code)s, name = %(name)s, note = %(note)s,
                       is_active = %(act)s, sort_order = %(sort)s
                 WHERE id = %(id)s
                """,
                {"id": type_id, "code": code.strip(), "name": name.strip(),
                 "note": note, "act": is_active, "sort": sort_order},
            )
        else:
            cur.execute(
                f"""
                INSERT INTO {table} (code, name, note, is_active, sort_order)
                VALUES (%(code)s, %(name)s, %(note)s, %(act)s, %(sort)s)
                """,
                {"code": code.strip(), "name": name.strip(), "note": note,
                 "act": is_active, "sort": sort_order},
            )


def day_codes(conn: psycopg.Connection) -> list[dict[str, Any]]:
    """Справочник обозначений табеля с признаками, влияющими на расчёт."""
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT code, name, note, group_name, kind, entry,
                   counts_as_worked, counts_in_norm
            FROM app.day_code
            ORDER BY sort_order, code
            """
        )
        return cur.fetchall()


def set_day_code_flags(
    conn: psycopg.Connection,
    *,
    code: str,
    counts_as_worked: bool,
    counts_in_norm: bool,
) -> None:
    """
    Изменить признаки обозначения.

    Два признака и их смысл:
      counts_as_worked — идёт в отработанное время;
      counts_in_norm   — уменьшает персональную норму месяца, то есть
                         плановые часы этого дня из нормы исключаются.

    Второй признак и есть ответ на вопрос «уважительное ли это отсутствие»:
    по законодательству норма корректируется только на часы, пропущенные по
    уважительной причине. Решение это принимает наниматель приказом об учёте
    рабочего времени — поэтому оно здесь, а не в коде.

    Признака «оплата» у обозначений нет намеренно: зарплата считается по
    часам из клеток табеля, а буквы нужны только для расчёта нормы.
    """
    with conn.cursor() as cur:
        cur.execute(
            """
            UPDATE app.day_code
               SET counts_as_worked = %(worked)s,
                   counts_in_norm = %(norm)s
             WHERE code = %(code)s
            """,
            {"code": code, "worked": counts_as_worked, "norm": counts_in_norm},
        )
        if cur.rowcount == 0:
            raise ValueError(f"Обозначение «{code}» не найдено в справочнике")


def set_kpi(
    conn: psycopg.Connection,
    *,
    kpi_id: int | None,
    code: str,
    name: str,
    description: str | None,
    better_when: str,
    source: str,
    is_active: bool,
) -> int:
    """
    Создать или изменить показатель. Возвращает его id — чтобы сразу после
    добавления открыть шкалу этого показателя, а не искать его в списке.
    """
    if not re.fullmatch(r"[a-z][a-z0-9_]{1,30}", code):
        raise ValueError(
            f"Код «{code}»: только латинские строчные буквы, цифры и "
            "подчёркивание, начинается с буквы — например plan_done"
        )
    with conn.cursor() as cur:
        cur.execute(
            "SELECT name FROM app.kpi WHERE code = %s AND id <> %s",
            (code, kpi_id or 0),
        )
        busy = cur.fetchone()
        if busy:
            raise ValueError(f"Код «{code}» уже занят показателем «{busy['name']}»")
        if kpi_id:
            cur.execute(
                """
                UPDATE app.kpi
                   SET code = %(code)s, name = %(name)s,
                       description = %(descr)s, better_when = %(bw)s,
                       source = %(src)s, is_active = %(act)s
                 WHERE id = %(id)s
                """,
                {"id": kpi_id, "code": code, "name": name, "descr": description,
                 "bw": better_when, "src": source, "act": is_active},
            )
            if cur.rowcount == 0:
                raise ValueError("Показатель не найден — возможно, его удалили")
            return kpi_id
        cur.execute(
            """
            INSERT INTO app.kpi (code, name, description, better_when,
                                 source, is_active)
            VALUES (%(code)s, %(name)s, %(descr)s, %(bw)s, %(src)s, %(act)s)
            RETURNING id
            """,
            {"code": code, "name": name, "descr": description,
             "bw": better_when, "src": source, "act": is_active},
        )
        return int(cur.fetchone()["id"])


# ---------------------------------------------------------------------------
#  Роли и права — уточнение заказчика от 15.09.2026
#
#  «Администратор — может всё, Ведущие специалисты — могут к примеру
#   редактировать это, имеют доступ к этому, видят в приложении это,
#   Начальник РЦ своё, его заместители и мастера своё».
#
#  Роли и права в базе были с самого начала и работали — не было экрана, на
#  котором это видно. Пока его нет, единственный способ узнать, что может
#  начальник РЦ, — читать SQL; а единственный способ поправить — писать SQL.
# ---------------------------------------------------------------------------

#  Права сгруппированы по разделам приложения: человек думает «кто ведёт
#  табель», а не «у кого timesheet.write». Порядок групп — как в меню.
PERMISSION_GROUPS: tuple[tuple[str, tuple[str, ...]], ...] = (
    #  02.10.2026 (вычитка 1.24): в матрице только права, которые приложение
    #  действительно проверяет. «Вести найм», «Вести коммуналку», «Смотреть
    #  сотрудников», «Смотреть табель» и «Журналы» ни на что не влияли —
    #  что видно, решает депо учётки, — и снятая галочка вводила в
    #  заблуждение. Добавлены права, которые работали, но на экране не были
    ("Сотрудники", (
        "candidate.write", "hiring.draft", "hiring.confirm", "employee.write",
        "staff_request.write", "card.write", "personal_data.read",
        "tsd.read", "tsd.write", "ppe.write", "penalty.read", "penalty.write",
    )),
    ("Расписание", (
        "schedule.write", "schedule.submit", "schedule.reopen",
        "timesheet.write", "timesheet.fill", "timesheet.submit",
        "timesheet.approve", "timesheet.export", "vacation.write",
        "workorder.write",
    )),
    ("Обеспечение", (
        "asset.read", "asset.write", "asset.writeoff", "maintenance.do",
        "maintenance.plan", "repair.open", "repair.hq",
        "supply.oz", "supply.receive",
    )),
    ("Мотивация и расчёт", (
        "payroll.read", "payroll.write", "payroll.calc", "payroll.confirm",
        "payroll.close", "kpi.write", "adjustment.delete", "budget.money",
    )),
    ("Финансы и кабинеты", (
        "finance.read", "finance.write", "ot.read", "board.read",
    )),
    ("Справочники и доступ", (
        "reference.write", "user.manage", "access.grant_below", "audit.read",
    )),
)

#  Права, которые приложение не проверяет (вычитка 1.24): в матрице их не
#  показываем, чтобы снятая галочка не обещала того, чего не делает.
#  employee.read остаётся в базе — на нём держится подача заявки на
#  численность, но смысл его «есть у всех, кто видит депо»
#  05.10.2026: «Просмотр журналов» (audit.read) вернулся на экран — на нём
#  держится вкладка «История» в карточке, бездействующим оно не было
INERT_PERMISSIONS = frozenset({"hiring.write", "utility.write",
                               "timesheet.read", "employee.read"})

#  Роль, которая по определению может всё. Её строка в матрице показывается
#  заполненной и не правится: см. sql/42_admin_all_permissions.sql
ADMIN_ROLE = "admin"


def roles_matrix(conn: psycopg.Connection) -> dict[str, Any]:
    """
    Матрица «право × роль» для экрана настроек.

    Права по строкам, роли по столбцам: ролей девять, а прав двадцать три —
    иначе таблица не помещается по ширине, а по высоте помещается всегда.
    """
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT r.id, r.code, r.name, r.description,
                   (SELECT count(*) FROM app.user_role ur
                     WHERE ur.role_id = r.id AND ur.revoked_at IS NULL) AS people
            FROM app.role r ORDER BY r.id
            """
        )
        roles = cur.fetchall()

        cur.execute("SELECT id, code, name FROM app.permission ORDER BY code")
        perms = {r["code"]: r for r in cur.fetchall()}

        cur.execute(
            """
            SELECT r.code AS role_code, p.code AS perm_code
            FROM app.role_permission rp
            JOIN app.role r       ON r.id = rp.role_id
            JOIN app.permission p ON p.id = rp.permission_id
            """
        )
        granted = {(r["role_code"], r["perm_code"]) for r in cur.fetchall()}

    groups = []
    seen: set[str] = set()
    for title, codes in PERMISSION_GROUPS:
        rows = []
        for code in codes:
            perm = perms.get(code)
            if not perm:
                continue          # право ещё не заведено — не наша забота
            seen.add(code)
            rows.append({
                "code": code,
                "name": perm["name"],
                "cells": [{"role": r["code"],
                           "on": (r["code"], code) in granted,
                           "locked": r["code"] == ADMIN_ROLE}
                          for r in roles],
            })
        if rows:
            groups.append({"title": title, "rows": rows})

    #  Право, не попавшее ни в одну группу, всё равно должно быть видно:
    #  иначе добавленное завтра право молча исчезнет с экрана
    rest = [c for c in perms if c not in seen and c not in INERT_PERMISSIONS]
    if rest:
        groups.append({
            "title": "Прочее",
            "rows": [{
                "code": code, "name": perms[code]["name"],
                "cells": [{"role": r["code"],
                           "on": (r["code"], code) in granted,
                           "locked": r["code"] == ADMIN_ROLE}
                          for r in roles],
            } for code in sorted(rest)],
        })

    return {"roles": roles, "groups": groups, "admin": ADMIN_ROLE}


def save_role_permissions(conn: psycopg.Connection,
                          wanted: set[tuple[str, str]]) -> int:
    """
    Записать матрицу прав целиком.

    `wanted` — пары «код роли, код права», отмеченные на экране. Пишем
    разницей, а не «удалить всё и вставить заново»: журнал операций тогда
    показывал бы полную перезапись при снятии одной галочки, а история прав —
    это то, к чему возвращаются после вопроса «кто это разрешил».

    Роль администратора не трогаем вовсе: она может всё, и база это
    подтверждает триггером.
    """
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT r.code AS role_code, p.code AS perm_code
            FROM app.role_permission rp
            JOIN app.role r       ON r.id = rp.role_id
            JOIN app.permission p ON p.id = rp.permission_id
            WHERE r.code <> %s
            """,
            (ADMIN_ROLE,),
        )
        #  Скрытых прав на экране нет, значит, и галочек по ним не придёт:
        #  их не трогаем, иначе первое же сохранение сняло бы их у всех
        #  ролей (05.10.2026)
        now = {(r["role_code"], r["perm_code"]) for r in cur.fetchall()
               if r["perm_code"] not in INERT_PERMISSIONS}
        wanted = {pair for pair in wanted
                  if pair[0] != ADMIN_ROLE and pair[1] not in INERT_PERMISSIONS}

        add = wanted - now
        drop = now - wanted
        for role_code, perm_code in sorted(add):
            cur.execute(
                """
                INSERT INTO app.role_permission (role_id, permission_id)
                SELECT r.id, p.id FROM app.role r, app.permission p
                 WHERE r.code = %s AND p.code = %s
                ON CONFLICT DO NOTHING
                """,
                (role_code, perm_code),
            )
        for role_code, perm_code in sorted(drop):
            cur.execute(
                """
                DELETE FROM app.role_permission rp
                 USING app.role r, app.permission p
                 WHERE rp.role_id = r.id AND rp.permission_id = p.id
                   AND r.code = %s AND p.code = %s
                """,
                (role_code, perm_code),
            )
    conn.commit()
    return len(add) + len(drop)


# ---------------------------------------------------------------------------
#  Подрядчики и причины непродления контракта
#
#  Данные заказчика от 21.09.2026. Оба справочника до сих пор жили в файле
#  управления РЦ и в голове у администрации.
# ---------------------------------------------------------------------------

def contractors(conn: psycopg.Connection) -> list[dict[str, Any]]:
    """Справочник подрядчиков: организация, телефон, почта."""
    with conn.cursor() as cur:
        cur.execute(
            "SELECT id, name, phone, email, note, is_active, sort_order "
            "  FROM app.contractor ORDER BY sort_order, name"
        )
        return cur.fetchall()


def save_contractor(
    conn: psycopg.Connection, *, contractor_id: int | None, name: str,
    phone: str = "", email: str = "", note: str = "", is_active: bool = True,
) -> int:
    """
    Завести или поправить подрядчика.

    Телефон и почта — два поля, а не одно: у «ФЛАРС» и «Высококачественных
    полимеров» есть только почта, и вписывать её в графу телефона значило бы
    портить справочник ради экономии одного столбца.
    """
    if not (name or "").strip():
        raise ValueError("Без названия организации запись не нужна")
    fields = {
        "name": name.strip(),
        "phone": (phone or "").strip() or None,
        "email": (email or "").strip() or None,
        "note": (note or "").strip() or None,
        "is_active": is_active,
    }
    with conn.cursor() as cur:
        if contractor_id:
            sets = ", ".join(f"{k} = %({k})s" for k in fields)
            cur.execute(f"UPDATE app.contractor SET {sets} WHERE id = %(id)s "
                        "RETURNING id", {**fields, "id": contractor_id})
        else:
            cols = ", ".join(fields)
            vals = ", ".join(f"%({k})s" for k in fields)
            cur.execute(f"INSERT INTO app.contractor ({cols}) VALUES ({vals}) "
                        "RETURNING id", fields)
        row = cur.fetchone()
        if row is None:
            raise ValueError("Запись не найдена — обновите страницу")
    conn.commit()
    return row["id"]


def termination_reasons_all(conn: psycopg.Connection) -> list[dict[str, Any]]:
    """
    Причины увольнения с признаком «годится для непродления контракта».

    Ответ О10 от 21.09.2026: «Перевод» и «Стажер (до двух месяцев)» причинами
    непродления не считаются — перевод контракт не прекращает, а стажёр до
    двух месяцев его не заключал.
    """
    with conn.cursor() as cur:
        cur.execute(
            "SELECT id, name, is_active, for_non_renewal "
            "  FROM app.termination_reason ORDER BY name"
        )
        return cur.fetchall()


def set_reason_non_renewal(
    conn: psycopg.Connection, reason_id: int, value: bool
) -> None:
    """Отметить или снять признак «годится для непродления контракта»."""
    with conn.cursor() as cur:
        cur.execute(
            "UPDATE app.termination_reason SET for_non_renewal = %s WHERE id = %s",
            (value, reason_id),
        )
    conn.commit()


# ---------------------------------------------------------------------------
#  Реестр ТМЦ: виды и производители
#
#  Заказчик 22.09.2026: «сам реестр менять может администратор ООУРЦ и админ».
#  Право — `reference.write`, то же, что у остальных справочников.
# ---------------------------------------------------------------------------

WAREHOUSE_TECH = "warehouse_tech"


def add_asset_kind(
    conn: psycopg.Connection, *, group_code: str, name: str,
    has_meter: bool = False,
) -> int:
    """
    Завести вид в реестре.

    «Ведём поштучно» берётся у группы, а не спрашивается отдельно: складская
    техника поштучна вся, ТМЦ — нет, и давать здесь выбор значило бы позволить
    завести штабелёр, который считают количеством.

    Код собирается из порядкового номера: латиницей его никто не читает, а
    придумывать осмысленный для каждой новой тележки — работа без пользы.
    """
    name = (name or "").strip()
    if not name:
        raise ValueError("Укажите название вида")
    #  24.09.2026: складская техника — только со счётчиком моточасов,
    #  галочки на экране для неё нет (миграция 84 держит то же правило)
    if group_code == WAREHOUSE_TECH:
        has_meter = True

    with conn.cursor() as cur:
        cur.execute(
            "SELECT is_equipment FROM app.asset_group WHERE code = %s",
            (group_code,),
        )
        group = cur.fetchone()
        if group is None:
            raise ValueError("Группа реестра не найдена — обновите страницу")

        cur.execute(
            "SELECT 1 FROM app.asset_category WHERE lower(name) = lower(%s)",
            (name,),
        )
        if cur.fetchone():
            raise ValueError(f"Вид «{name}» уже есть в реестре")

        cur.execute("SELECT coalesce(max(id), 0) + 1 AS n FROM app.asset_category")
        code = f"kind_{cur.fetchone()['n']}"
        cur.execute(
            """
            INSERT INTO app.asset_category
                (code, name, has_meter, is_equipment, group_code, sort_order)
            VALUES (%s, %s, %s, %s, %s,
                    (SELECT coalesce(max(sort_order), 0) + 1
                       FROM app.asset_category WHERE group_code = %s))
            RETURNING id
            """,
            (code, name, has_meter, group["is_equipment"], group_code, group_code),
        )
        made = cur.fetchone()["id"]
    conn.commit()
    return made


def set_asset_kind_active(
    conn: psycopg.Connection, category_id: int, value: bool
) -> None:
    """
    Закрыть вид или вернуть его.

    Удаления нет намеренно: на вид ссылается заведённое имущество, и стирать
    его значило бы обрывать эти ссылки. Закрытый вид не предлагается при
    заведении, а всё, что уже заведено, остаётся на месте.
    """
    with conn.cursor() as cur:
        cur.execute(
            "UPDATE app.asset_category SET is_active = %s WHERE id = %s",
            (value, category_id),
        )
        if cur.rowcount == 0:
            raise ValueError("Вид не найден — обновите страницу")
    conn.commit()


def add_asset_model(
    conn: psycopg.Connection, *, group_code: str, name: str
) -> None:
    """Добавить производителя в список группы."""
    name = (name or "").strip()
    if not name:
        raise ValueError("Укажите производителя и модель")
    with conn.cursor() as cur:
        cur.execute(
            """
            INSERT INTO app.asset_model (group_code, name,
                   sort_order)
            VALUES (%s, %s,
                    (SELECT coalesce(max(sort_order), 0) + 10
                       FROM app.asset_model WHERE group_code = %s))
            ON CONFLICT (group_code, name) DO UPDATE SET is_active = true
            """,
            (group_code, name, group_code),
        )
    conn.commit()


def drop_asset_model(
    conn: psycopg.Connection, *, group_code: str, name: str
) -> None:
    """
    Убрать производителя из списка.

    Запись закрывается, а не удаляется: имя уже могло уйти в заведённую
    единицу, и по нему её ищут. В выпадающем списке закрытого не будет.
    """
    with conn.cursor() as cur:
        cur.execute(
            "UPDATE app.asset_model SET is_active = false "
            " WHERE group_code = %s AND name = %s",
            (group_code, name),
        )
        if cur.rowcount == 0:
            raise ValueError("Запись не найдена — обновите страницу")
    conn.commit()


def all_asset_kinds(conn: psycopg.Connection) -> list[dict[str, Any]]:
    """
    Все виды реестра, включая закрытые.

    В «Обеспечении» закрытые не предлагаются, а здесь их видно: иначе
    закрытый вид некуда было бы вернуть.
    """
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT k.id, k.code, k.name, k.has_meter, k.is_equipment,
                   k.group_code, k.is_active, k.sort_order
              FROM app.asset_category k
              LEFT JOIN app.asset_group g ON g.code = k.group_code
             ORDER BY g.sort_order NULLS LAST, k.sort_order, k.name
            """
        )
        return cur.fetchall()


# ---------------------------------------------------------------------------
#  Комиссия по осмотру СИЗ (25.09.2026, «Доработка 2», миграция 87)
#
#  По каждому депо — председатель и члены комиссии. Подставляются в акт
#  осмотра СИЗ на продление срока носки. Ведёт Администрация.
# ---------------------------------------------------------------------------

def ppe_commissions(conn: psycopg.Connection) -> list[dict[str, Any]]:
    """Депо с комиссией: [{id, name, chair: {...}|None, members: [...]}]."""
    with conn.cursor() as cur:
        cur.execute("SELECT to_regclass('app.ppe_commission') IS NOT NULL AS ok")
        if not cur.fetchone()["ok"]:
            return []
        cur.execute(
            "SELECT id, name, full_name FROM app.org_unit "
            " WHERE kind = 'rc' AND is_active ORDER BY name")
        units = cur.fetchall()
        cur.execute(
            "SELECT org_unit_id, role, full_name, position FROM app.ppe_commission "
            " ORDER BY org_unit_id, role, sort_order, id")
        rows = cur.fetchall()
    for u in units:
        u["chair"] = next((r for r in rows
                           if r["org_unit_id"] == u["id"] and r["role"] == "chair"), None)
        u["members"] = [r for r in rows
                        if r["org_unit_id"] == u["id"] and r["role"] == "member"]
    return units


def ppe_commission_of(conn: psycopg.Connection, unit_id: int) -> dict[str, Any]:
    """Комиссия одного депо: {chair, members}."""
    with conn.cursor() as cur:
        cur.execute(
            "SELECT role, full_name, position FROM app.ppe_commission "
            " WHERE org_unit_id = %s ORDER BY role, sort_order, id", (unit_id,))
        rows = cur.fetchall()
    return {"chair": next((r for r in rows if r["role"] == "chair"), None),
            "members": [r for r in rows if r["role"] == "member"]}


def save_ppe_commission(conn: psycopg.Connection, unit_id: int,
                        chair_name: str, chair_position: str,
                        members: list[tuple[str, str]]) -> None:
    """
    Сохранить комиссию депо целиком. Пустая строка члена комиссии — строка
    убрана. Председатель обязателен, если в комиссии кто-то есть.
    """
    chair_name = (chair_name or "").strip()
    chair_position = (chair_position or "").strip()
    members = [((n or "").strip(), (p or "").strip()) for n, p in members]
    members = [(n, p) for n, p in members if n]
    if members and not chair_name:
        raise ValueError("Укажите председателя комиссии")
    with conn.cursor() as cur:
        cur.execute("DELETE FROM app.ppe_commission WHERE org_unit_id = %s", (unit_id,))
        if chair_name:
            cur.execute(
                "INSERT INTO app.ppe_commission (org_unit_id, role, sort_order, full_name, position) "
                "VALUES (%s, 'chair', 0, %s, %s)", (unit_id, chair_name, chair_position))
        for i, (n, p) in enumerate(members, 1):
            cur.execute(
                "INSERT INTO app.ppe_commission (org_unit_id, role, sort_order, full_name, position) "
                "VALUES (%s, 'member', %s, %s, %s)", (unit_id, i, n, p))
    conn.commit()


# ===========================================================================
#  База СИЗ: справочник позиций (вычитка LOGIKA_PROSTO 30.09.2026, 4а)
# ===========================================================================
#  «В учётку администрации в Базовых настройках добавить в Нормы выдачи
#  СИЗ список базы СИЗ, разделённый на летние комплекты, зимние комплекты,
#  зимняя обувь, летняя обувь, каски, перчатки. Возможность менять и
#  добавлять позиции». Справочник тот же, что у выдач (ppe_item): второй
#  плодил бы дубли наименований, — а группа базы задаётся здесь

PPE_BASE_GROUPS = [
    ("summer_suit", "Летние комплекты"),
    ("winter_suit", "Зимние комплекты"),
    ("winter_shoes", "Зимняя обувь"),
    ("summer_shoes", "Летняя обувь"),
    ("helmet", "Каски"),
    ("gloves", "Перчатки"),
]


def ppe_base_items(conn: psycopg.Connection) -> list[dict[str, Any]]:
    """Позиции справочника базы: с группой, включая закрытые."""
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT it.id, it.name, it.code, it.base_group, it.is_active,
                   coalesce((SELECT sum(s.qty) FROM app.ppe_base_stock s
                              WHERE s.ppe_item_id = it.id), 0) AS stock_qty,
                   coalesce((SELECT count(*) FROM app.ppe_issue i
                              WHERE i.ppe_item_id = it.id
                                AND i.status = 'issued'), 0) AS issued_qty
            FROM app.ppe_item it
            WHERE it.base_group IS NOT NULL
            ORDER BY it.base_group, it.name
            """)
        return cur.fetchall()


def ppe_base_add_item(conn: psycopg.Connection, name: str,
                      group: str) -> None:
    """
    Добавить позицию в справочник базы — или открыть существующую.

    Позиции выдач создаются сами при первой выдаче; если наименование уже
    есть, ему просто проставляется группа, и выдачи с ним тут же попадают
    в выбор при внесении наличия.
    """
    name = name.strip()
    group = group.strip()
    if not name:
        raise ValueError("Укажите наименование позиции")
    if group not in dict(PPE_BASE_GROUPS):
        raise ValueError("Выберите группу из списка")
    with conn.cursor() as cur:
        cur.execute(
            "SELECT id FROM app.ppe_item WHERE name = %s ORDER BY id LIMIT 1",
            (name[:200],))
        found = cur.fetchone()
        if found:
            cur.execute("UPDATE app.ppe_item SET base_group = %s, "
                        "is_active = true WHERE id = %s",
                        (group, found["id"]))
        else:
            cur.execute(
                """
                INSERT INTO app.ppe_item (name, kind, base_group)
                VALUES (%s, 'other', %s)
                """, (name[:200], group))
    conn.commit()


def ppe_base_set_group(conn: psycopg.Connection, item_id: int,
                       group: str | None) -> None:
    """Перенести позицию в другую группу или убрать из базы (NULL)."""
    if group is not None and group not in dict(PPE_BASE_GROUPS):
        raise ValueError("Неизвестная группа")
    with conn.cursor() as cur:
        cur.execute("UPDATE app.ppe_item SET base_group = %s WHERE id = %s",
                    (group, item_id))
        if cur.rowcount == 0:
            raise ValueError("Позиция не найдена — обновите страницу")
    conn.commit()


def ppe_base_toggle(conn: psycopg.Connection, item_id: int,
                    active: bool) -> None:
    """
    Закрыть или открыть позицию. Закрытая не выбирается при внесении
    наличия; закрыть позицию с наличием нельзя — сначала раздайте её.
    """
    with conn.cursor() as cur:
        cur.execute(
            "SELECT coalesce(sum(qty), 0) AS q FROM app.ppe_base_stock "
            " WHERE ppe_item_id = %s", (item_id,))
        if int(cur.fetchone()["q"]) > 0 and not active:
            raise ValueError(
                "На депо ещё есть эта позиция: сначала раздайте её или "
                "обнулите остаток в «Базе СИЗ»")
        cur.execute("UPDATE app.ppe_item SET is_active = %s WHERE id = %s",
                    (active, item_id))
        if cur.rowcount == 0:
            raise ValueError("Позиция не найдена — обновите страницу")
    conn.commit()


# ===========================================================================
#  База СИЗ: загрузка остатков из 1С УТ файлом (05.10.2026, правка
#  заказчика). Файл — выгрузка 1С УТ «Остатки товаров на складах»: в шапке
#  склады «МБП ДЕПО <Город>» (по колонке на город), ниже позиции с
#  количеством. На входе позиции фильтруются по словам из
#  ppe_base_filter_word — без них в остатках МБП «много всего» и в Базу
#  СИЗ попадает не СИЗ. Загрузка действует до следующей: прежние файловые
#  остатки замещаются, но снимок прошлой загрузки остаётся в истории.
# ===========================================================================

DEFAULT_FILTER_WORDS = [
    ("кроссовки", "summer_shoes"),
    ("ботинки", "winter_shoes"),
    ("полуботинки", "winter_shoes"),
    ("костюм зимний", "winter_suit"),
    ("костюм летний", "summer_suit"),
    ("комплект летний", "summer_suit"),
    ("комплект зимний", "winter_suit"),
    ("каска", "helmet"),
    ("перчатки латексные", "gloves"),
    ("перчатки", "gloves"),
]


def ppe_base_filter_words(conn) -> list[dict[str, Any]]:
    with conn.cursor() as cur:
        cur.execute(
            """SELECT id, keyword, base_group FROM app.ppe_base_filter_word
                ORDER BY base_group, keyword""")
        return cur.fetchall()


def ppe_base_filter_add(conn, keyword: str, group: str) -> None:
    keyword = (keyword or "").strip().lower()
    if not keyword:
        raise ValueError("Введите слово фильтра")
    if group not in dict(PPE_BASE_GROUPS):
        raise ValueError("Выберите группу Базы СИЗ")
    with conn.cursor() as cur:
        cur.execute(
            """INSERT INTO app.ppe_base_filter_word (keyword, base_group)
                VALUES (%s, %s)
                ON CONFLICT (keyword) DO UPDATE SET base_group = EXCLUDED.base_group""",
            (keyword, group))
    conn.commit()


def ppe_base_filter_delete(conn, word_id: int) -> None:
    with conn.cursor() as cur:
        cur.execute("DELETE FROM app.ppe_base_filter_word WHERE id = %s",
                    (word_id,))
        if cur.rowcount == 0:
            raise ValueError("Слово не найдено — обновите страницу")
    conn.commit()


#  Карточки Базы СИЗ с матрицами типоразмеров (06.10.2026, правка заказчика):
#  семь вкладок, в карточке — рост × размер × артикул 1С:УТ. В приложении
#  правится только артикул, состав строк ведут миграции и загрузка данных
#  карточки
def ppe_size_cards(conn) -> list[dict[str, Any]]:
    with conn.cursor() as cur:
        cur.execute(
            """SELECT code, title, subtitle, order_name, order_name_tail
                 FROM app.ppe_size_card ORDER BY sort""")
        return cur.fetchall()


def ppe_size_rows(conn, card_code: str) -> list[dict[str, Any]]:
    with conn.cursor() as cur:
        cur.execute(
            """SELECT id, height, size, chest, article
                 FROM app.ppe_size_row
                WHERE card_code = %s
                ORDER BY sort, id""",
            (card_code,))
        return cur.fetchall()


def ppe_size_article_set(conn, row_id: int, article: str) -> bool:
    with conn.cursor() as cur:
        cur.execute(
            "UPDATE app.ppe_size_row SET article = %s WHERE id = %s",
            ((article or "").strip(), row_id))
        return cur.rowcount > 0


def ppe_size_card_order_name_set(conn, card_code: str, order_name: str,
                                 tail: str = "") -> bool:
    """Наименование товара по 1С:УТ — из него собирается строка заказа
    «артикул, наименование размер (обхват) рост [дополнение]»
    (06.10.2026). Дополнение (order_name_tail) идёт после роста: у части
    номенклатуры 1С размер вставляется в середину имени."""
    with conn.cursor() as cur:
        cur.execute(
            """UPDATE app.ppe_size_card
                  SET order_name = %s, order_name_tail = %s
                WHERE code = %s""",
            ((order_name or "").strip(), (tail or "").strip(), card_code))
        return cur.rowcount > 0


def ppe_size_matrix(rows: list[dict[str, Any]]) -> dict[str, Any]:
    """Матрица подбора: артикул по строкам и размерам (столбцам). У одежды
    строка — рост, у обуви роста нет и строкой служит цвет (chest).
    Порядок строк и размеров — как в файле заказчика: первый раз строка
    встретилась — на том месте и стоит. Пустая комбинация — «не выпускается»."""
    by_height = any(r["height"] for r in rows)

    def row_of(r: dict[str, Any]) -> str:
        return r["height"] if by_height else (r["chest"] or "—")

    row_keys: list[str] = []
    sizes: list[str] = []
    cells: dict[tuple[str, str], str] = {}
    for r in rows:
        k = row_of(r)
        if k not in row_keys:
            row_keys.append(k)
        if r["size"] not in sizes:
            sizes.append(r["size"])
        cells[(k, r["size"])] = r["article"]
    return {"rows": row_keys, "sizes": sizes, "cells": cells,
            "by_height": by_height,
            "row_label": "Рост, см" if by_height else "Цвет"}


def ppe_base_loads(conn, limit: int = 20) -> list[dict[str, Any]]:
    """История загрузок остатков — последние двадцать."""
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT l.id, l.filename, l.positions, l.uploaded_at,
                   coalesce(u.full_name, '—') AS uploaded_by_name,
                   (SELECT count(DISTINCT org_unit_id)
                      FROM app.ppe_base_load_item i
                     WHERE i.load_id = l.id) AS cities
            FROM app.ppe_base_load l
            LEFT JOIN app.app_user u ON u.id = l.uploaded_by
            ORDER BY l.uploaded_at DESC LIMIT %s
            """, (limit,))
        return cur.fetchall()


def ppe_base_load_items(conn, load_id: int) -> list[dict[str, Any]]:
    """Снимок остатков загрузки: позиция, город, количество."""
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT i.item_name, i.base_group, i.qty,
                   coalesce(o.name, 'склад не опознан') AS unit_name
            FROM app.ppe_base_load_item i
            LEFT JOIN app.org_unit o ON o.id = i.org_unit_id
            WHERE i.load_id = %s
            ORDER BY o.name NULLS LAST, i.item_name
            """, (load_id,))
        return cur.fetchall()


def _norm_ppe_name(value: str) -> str:
    s = (value or "").replace("ё", "е").replace("Ё", "Е").lower()
    return re.sub(r"\s+", " ", s).strip()


def _warehouse_columns(ws) -> tuple[int, list[tuple[int, str, str]]]:
    """Шапка складов в файле остатков.

    Возвращает (номер строки шапки, колонки). Колонка склада — «МБП ДЕПО
    <Город>» (депо города) или «МБП РЦ» (Минский склад, 07.10.2026).
    Строка шапки ищется в первых десяти: новый файл 1С печатает её второй
    строкой, прежний — четвёртой.
    """
    out: list[tuple[int, str, str]] = []
    header_row = 0
    for r in range(1, min(ws.max_row or 0, 10) + 1):
        row_out: list[tuple[int, str, str]] = []
        for c in range(1, min(ws.max_column or 0, 60) + 1):
            v = ws.cell(row=r, column=c).value
            if not v:
                continue
            text = str(v).strip()
            if text.startswith("МБП ДЕПО "):
                row_out.append((c, "depot", text[len("МБП ДЕПО "):].strip()))
            elif text == "МБП РЦ":
                row_out.append((c, "hq", "Минск"))
        if row_out:
            header_row = r
            out = row_out
            break
    return header_row, out


def _mbp_card_map(conn) -> dict[str, dict[str, str]]:
    """Артикул 1С -> карточка Базы СИЗ (что внесено в «Базовых настройках»).

    №2 правки от 07.10.2026: загрузка остатков больше не фильтрует по
    словам — код номенклатуры из левой колонки файла (артикул) сверяется
    с артикулами карточек, и позиция ложится в свою карточку. 07.10,
    третья правка: карточка «Иное» — перечень произвольной номенклатуры
    (ppe_other_item), её артикулы сопоставляются наравне с типоразмерными."""
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT DISTINCT r.article, c.code AS card_code, c.title
              FROM app.ppe_size_row r
              JOIN app.ppe_size_card c ON c.code = r.card_code
             WHERE nullif(btrim(r.article), '') IS NOT NULL
            """)
        out = {r["article"].strip(): r for r in cur.fetchall()}
        cur.execute("SELECT article FROM app.ppe_other_item")
        for r in cur.fetchall():
            out.setdefault(r["article"].strip(), {
                "article": r["article"].strip(),
                "card_code": "other", "title": "Иное"})
        return out


def _card_base_group(card_code: str) -> str | None:
    """Базовая группа позиции по коду карточки (для старых фильтров выдачи)."""
    return {
        "summer_kit_sk": "summer_suit",
        "summer_costume_adm": "summer_suit",
        "winter_kit_sk": "winter_suit",
        "winter_costume_adm": "winter_suit",
        "shoes_summer": "summer_shoes",
        "shoes_winter": "winter_shoes",
    }.get(card_code)


def ppe_other_items(conn) -> list[dict[str, Any]]:
    """Перечень номенклатуры карточки «Иное»: артикул + наименование."""
    with conn.cursor() as cur:
        cur.execute(
            """SELECT id, article, name FROM app.ppe_other_item
                ORDER BY name""")
        return cur.fetchall()


def ppe_other_add(conn, article: str, name: str) -> None:
    article = (article or "").strip()
    name = (name or "").strip()
    if not article:
        raise ValueError("Введите код номенклатуры (артикул 1С)")
    if not name:
        raise ValueError("Введите наименование")
    with conn.cursor() as cur:
        cur.execute(
            """INSERT INTO app.ppe_other_item (article, name)
                VALUES (%s, %s)
                ON CONFLICT (article) DO UPDATE SET name = EXCLUDED.name""",
            (article, name))
    conn.commit()


def ppe_other_delete(conn, item_id: int) -> None:
    with conn.cursor() as cur:
        cur.execute("DELETE FROM app.ppe_other_item WHERE id = %s", (item_id,))
        if cur.rowcount == 0:
            raise ValueError("Позиция не найдена — обновите страницу")
    conn.commit()


def load_ppe_base_1c(conn, file_storage, filename: str,
                     user_id: int | None) -> dict[str, Any]:
    """
    Разобрать файл остатков 1С УТ и загрузить Базу СИЗ.

    07.10.2026 (правка заказчика): словесного фильтра больше нет. В левой
    колонке файла — код номенклатуры (артикул 1С): он сверяется с
    артикулами карточек Базы СИЗ («Базовых настроек»), и позиция ложится
    в свою карточку. Что не сопоставилось — в Базу не попадает. Остатки
    по складам («МБП ДЕПО <Город>» и «МБП РЦ» — Минский) замещают прежние
    файловые; внесённые депо руками и сданные «б/у» не трогаются.
    Возвращается отчёт.
    """
    import io as _io
    import openpyxl

    wb = openpyxl.load_workbook(_io.BytesIO(file_storage), data_only=True,
                                read_only=True)
    ws = wb[wb.sheetnames[0]]

    header_row, warehouses = _warehouse_columns(ws)
    if not warehouses:
        wb.close()
        raise ValueError("В шапке файла не найдено ни одного склада "
                         "«МБП ДЕПО <Город>» или «МБП РЦ»")

    #  колонки артукула и наименования — по шапке, с прежними значениями
    #  по умолчанию (артикул — левая колонка, наименование — третья)
    col_article, col_name = 1, 3
    for c in range(1, min(ws.max_column or 0, 60) + 1):
        v = ws.cell(row=header_row, column=c).value
        text = str(v).strip().lower() if v else ""
        if text == "артикул":
            col_article = c
        elif text == "наименование":
            col_name = c

    #  склад -> id подразделения: депо по городу, «МБП РЦ» — Минск
    city_units: dict[str, int] = {}
    hq_unit: int | None = None
    with conn.cursor() as cur:
        cur.execute("SELECT id, code, name FROM app.org_unit "
                     "WHERE kind = 'rc' AND is_active")
        for r in cur.fetchall():
            city_units[_norm_ppe_name(r["name"])] = r["id"]
        cur.execute("SELECT id FROM app.org_unit WHERE code = 'hq'")
        row = cur.fetchone()
        hq_unit = row["id"] if row else None

    col_unit: dict[int, int] = {}
    unknown_cities: list[str] = []
    for col, kind, city in warehouses:
        unit_id = hq_unit if kind == "hq" else city_units.get(_norm_ppe_name(city))
        if unit_id:
            col_unit[col] = unit_id
        else:
            unknown_cities.append(city)

    #  сопоставление по Базовым настройкам: артикул -> карточка
    cards = _mbp_card_map(conn)
    if not cards:
        wb.close()
        raise ValueError("В карточках Базы СИЗ не заведён ни один артикул — "
                         "заполните их в «Базовых настройках»")

    passed: dict[str, dict[str, Any]] = {}
    skipped = 0
    for r in range(header_row + 2, (ws.max_row or 0) + 1):
        #  строка «Количество» и пустые шапки пропускаются сами:
        #  без артикула или наименования строки не несут позиции
        art = ws.cell(row=r, column=col_article).value
        art = str(art).strip() if art is not None else ""
        name = ws.cell(row=r, column=col_name).value
        name = str(name).strip() if name else ""
        if not art or not name:
            continue
        card = cards.get(art)
        if card is None:
            skipped += 1
            continue
        qty_by_col = {}
        for col, unit_id in col_unit.items():
            v = ws.cell(row=r, column=col).value
            if isinstance(v, (int, float)) and v > 0:
                qty_by_col[unit_id] = int(v)
        if not qty_by_col:
            continue
        row = passed.setdefault(art, {
            "name": name, "card_code": card["card_code"],
            "card_title": card["title"], "qty": {}})
        for unit_id, q in qty_by_col.items():
            row["qty"][unit_id] = row["qty"].get(unit_id, 0) + q
    wb.close()

    #  города, по которым есть остатки в файле
    ok_cities = sorted(set(col_unit.values()))

    #  фиксируем загрузку в историю
    with conn.cursor() as cur:
        cur.execute(
            """
            INSERT INTO app.ppe_base_load (filename, positions, uploaded_by)
            VALUES (%s, %s,
                    nullif(current_setting('app.user_id', true), '')::bigint)
            RETURNING id
            """,
            (filename, sum(len(cities) for cities in
                           (row["qty"] for row in passed.values()))))
        load_id = cur.fetchone()["id"]

    #  прежние файловые остатки депо из файла замещаются новыми; внесённые
    #  депо руками остатки и сданные «б/у» не трогаются
    for unit_id in ok_cities:
        with conn.cursor() as cur:
            cur.execute(
                """
                DELETE FROM app.ppe_base_stock
                 WHERE org_unit_id = %s
                   AND ppe_item_id IN (SELECT id FROM app.ppe_item
                                        WHERE from_import)
                """, (unit_id,))

    to_rows = 0
    for key, row in sorted(passed.items()):
        group = _card_base_group(row["card_code"])
        with conn.cursor() as cur:
            cur.execute(
                "SELECT id FROM app.ppe_item WHERE code = %s ORDER BY id "
                "LIMIT 1", (key,))
            found = cur.fetchone()
            if found:
                item_id = found["id"]
                cur.execute(
                    """UPDATE app.ppe_item
                          SET name = %s,
                              from_import = true,
                              base_group = %s,
                              is_active = true
                        WHERE id = %s""",
                    (row["name"], group, item_id))
            else:
                cur.execute(
                    """INSERT INTO app.ppe_item
                         (code, name, kind, base_group, from_import)
                       VALUES (%s, %s, 'other', %s, true) RETURNING id""",
                    (key, row["name"], group))
                item_id = cur.fetchone()["id"]
            for unit_id, qty in row["qty"].items():
                cur.execute(
                    """
                    INSERT INTO app.ppe_base_stock
                        (org_unit_id, ppe_item_id, size, qty)
                    VALUES (%s, %s, '', %s)
                    ON CONFLICT (org_unit_id, ppe_item_id, size)
                    DO UPDATE SET qty = EXCLUDED.qty,
                                  updated_at = now()
                    """, (unit_id, item_id, qty))
                with conn.cursor() as snap:
                    snap.execute(
                        """INSERT INTO app.ppe_base_load_item
                             (load_id, org_unit_id, item_name, base_group, qty)
                           VALUES (%s, %s, %s, %s, %s)""",
                        (load_id, unit_id, row["card_title"], row["card_code"],
                         qty))
                to_rows += 1

    conn.commit()
    return {"load_id": load_id,
            "positions": len(passed),
            "cities": len(ok_cities),
            "filtered_out": skipped,
            "unknown_cities": unknown_cities,
            "no_unit_cities": [c for c in unknown_cities]}
