"""
Запросы кадрового блока: база сотрудников, карточка, заявки депо.

Права нигде не проверяются здесь — это делают RLS и функции в базе. Здесь
только чтение и вызовы функций, чтобы веб-слой не собирал SQL руками.
"""

from __future__ import annotations

import calendar
from datetime import date, timedelta
from typing import Any

import psycopg

FIO_FULL = (
    "e.last_name || ' ' || e.first_name || "
    "coalesce(' ' || e.middle_name, '')"
)

#  Уволенный, по которому всё закрыто. Условие живёт одной строкой, потому
#  что его спрашивают в трёх местах: список уволенных, счётчики вкладок и
#  список ожидающих расчёта. Разойдись они — человек пропал бы разом отовсюду
#  или оказался бы в двух вкладках сразу.
#
#  `coalesce` нужен на время: пока не накатана миграция 70, колонки нет, а
#  условие «не отправлено» должно оставаться истинным для всех уволенных.
_SETTLED = "em.settlement_state IN ('none', 'accepted')"
_SETTLING = "em.settlement_state IN ('pending', 'submitted')"

#  Как называется каждое состояние расчёта на экране
SETTLEMENT_RU = {
    "none": "не требуется",
    "pending": "окончательный расчёт",
    "submitted": "расчёт отправлен",
    "accepted": "расчёт принят",
}


def positions(conn: psycopg.Connection) -> list[dict[str, Any]]:
    with conn.cursor() as cur:
        cur.execute(
            """
            --  `id` обязателен: форма кандидата шлёт именно его
            --  (`position_id`). Без него в списке должностей у каждого
            --  варианта стояло value="" — браузер отправлял пустое, и
            --  добавление кандидата падало «Field required» (22.09.2026)
            SELECT p.id, p.code, p.name, pc.code AS category, pc.sort_order
            FROM app.position p
            JOIN app.position_category pc ON pc.id = p.category_id
            WHERE p.is_active
            ORDER BY pc.sort_order, p.name
            """
        )
        return cur.fetchall()


def termination_reasons(
    conn: psycopg.Connection, non_renewal: bool = False
) -> list[dict[str, Any]]:
    """
    Причины увольнения. С `non_renewal` — только те, что годятся для
    непродления контракта.

    Ответ О10 от 21.09.2026: «Перевод» и «Стажер (до двух месяцев)» в список
    причин непродления не входят — перевод контракт не прекращает, а стажёр
    до двух месяцев его не заключал. Признак стоит у самой причины
    (миграция 57): это те же причины, а не второй справочник из восьми
    одинаковых строк.
    """
    where = "WHERE is_active" + (" AND for_non_renewal" if non_renewal else "")
    with conn.cursor() as cur:
        cur.execute(
            #  `needs_non_renewal` уезжает в форму: по нему она понимает, что
            #  после этой причины надо спросить вторую (22.09.2026)
            f"SELECT id, name, needs_non_renewal FROM app.termination_reason "
            f"{where} ORDER BY name"
        )
        return cur.fetchall()


def staff_list(
    conn: psycopg.Connection,
    unit_id: int | None = None,
    status: str = "employed",
    query: str | None = None,
) -> list[dict[str, Any]]:
    """
    Сотрудники в разрезе трудоустроенных или уволенных.

    unit_id = None означает «все доступные подразделения»: администрация
    видит так все депо, начальник РЦ — всё равно только своё, потому что
    остальные отрежет RLS.
    """
    where = ["1 = 1"]
    params: dict[str, Any] = {}

    if status == "employed":
        where.append("em.terminated_at IS NULL")
    elif status == "dismissed":
        #  Уволен — это «расчёт принят», а не «дата проставлена» (22.09.2026):
        #  «сотрудник не попадает в "Уволенные", а остаётся в "Заявки"…
        #  только тогда сотрудник попадает во вкладку "Уволенные"». Пока
        #  расчёт не принят, человек висит в «Заявках», и показывать его
        #  одновременно в двух местах значит потерять его в обоих
        where.append("em.terminated_at IS NOT NULL")
        where.append(_SETTLED)

    if unit_id:
        where.append("em.org_unit_id = %(unit)s")
        params["unit"] = unit_id

    if query:
        where.append(
            f"({FIO_FULL} ILIKE %(q)s OR e.personnel_no LIKE %(q)s)"
        )
        params["q"] = f"%{query.strip()}%"

    with conn.cursor() as cur:
        cur.execute(
            f"""
            SELECT em.id            AS employment_id,
                   e.id             AS employee_id,
                   e.personnel_no,
                   {FIO_FULL}       AS fio_full,
                   p.name           AS position_name,
                   p.id             AS position_id,
                   pc.code          AS category,
                   ou.name          AS unit_name,
                   ou.code          AS unit_code,
                   em.hired_at,
                   em.terminated_at,
                   em.direction,
                   em.created_at    AS added_at,
                   au.full_name     AS added_by,
                   tr.name          AS termination_reason,
                   (SELECT count(*) FROM app.staff_request sr
                     WHERE sr.employment_id = em.id AND sr.status = 'new')
                                    AS open_requests,

                   -- Краткая сводка по карточке: то, из-за чего в карточку и
                   -- заходят. В списке видно сразу, у кого просрочен допуск,
                   -- а не после десяти открытых карточек по очереди.
                   e.phone, e.work_email, e.birth_date,
                   (SELECT c.ends_at FROM app.employee_contract c
                     WHERE c.employee_id = e.id
                     ORDER BY c.extended_at DESC, c.id DESC LIMIT 1)
                                    AS contract_ends,
                   -- Медосмотры, инструктажи и свидетельства идут отдельными
                   -- запросами — по колонке на каждый вид из справочника
                   -- (due_by_type, briefing_by_type): одна «ближайшая дата»
                   -- не отвечала на вопрос, что именно просрочено
                   (SELECT f.liters_month FROM app.employee_fuel_norm f
                     WHERE f.employee_id = e.id
                       AND f.valid_from <= current_date
                       AND (f.valid_to IS NULL OR f.valid_to > current_date)
                     ORDER BY f.valid_from DESC, f.id DESC LIMIT 1)
                                    AS fuel_liters,
                   -- логин ТСД в списке не показываем, только факт наличия;
                   -- у начальника РЦ нет права personal_data.read, и RLS
                   -- вернёт ему ноль — это тоже правильный ответ «не вижу»
                   (SELECT count(*) FROM app.tsd_account t
                     WHERE t.employee_id = e.id AND t.revoked_at IS NULL)
                                    AS tsd_count,
                   (SELECT count(*) FROM app.ppe_issue i
                     WHERE i.employee_id = e.id AND i.status = 'issued')
                                    AS ppe_count,
                   (SELECT count(*) FROM app.employee_photo ph
                     WHERE ph.employee_id = e.id) AS has_photo
            FROM app.employment em
            JOIN app.employee e            ON e.id = em.employee_id
            JOIN app.position p            ON p.id = em.position_id
            JOIN app.position_category pc  ON pc.id = p.category_id
            JOIN app.org_unit ou           ON ou.id = em.org_unit_id
            LEFT JOIN app.app_user au      ON au.id = em.created_by
            LEFT JOIN app.termination_reason tr ON tr.id = em.termination_reason_id
            WHERE {' AND '.join(where)}
            ORDER BY em.terminated_at DESC NULLS FIRST,
                     pc.sort_order, e.last_name, e.first_name
            """,
            params,
        )
        return cur.fetchall()


#  Таблица и колонка с датой начала: у медосмотра это дата прохождения, у
#  свидетельства — дата выдачи. Нужна, чтобы отличить «записи нет» от
#  «запись есть, но срок в ней не задан»: у свидетельства водителя погрузчика
#  в файле депо есть номер и дата выдачи, а даты окончания нет.
DUE_TABLES = {
    "medical": ("app.employee_medical_exam", "passed_at"),
    "certificate": ("app.employee_certificate", "issued_at"),
}


def due_by_type(
    conn: psycopg.Connection, kind: str
) -> dict[int, dict[int, dict[str, date | None]]]:
    """
    Даты «действует до» по каждому виду: сотрудник → вид → дата.

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

    Фильтра по подразделению нет намеренно: доступ отрезает RLS, а строк тут
    столько же, сколько людей, помноженное на число видов.
    """
    table, since_col = DUE_TABLES[kind]
    out: dict[int, dict[int, dict[str, date | None]]] = {}
    with conn.cursor() as cur:
        cur.execute(
            f"SELECT employee_id, type_id, valid_until, {since_col} AS since "
            f"  FROM {table}"
        )
        for r in cur.fetchall():
            out.setdefault(r["employee_id"], {})[r["type_id"]] = {
                "until": r["valid_until"], "since": r["since"],
            }
    return out


#  Виды СИЗ, по которым в ООУРЦ следят за сроком носки. Порядок — как в
#  списке сотрудников: сначала комплекты, потом каска и перчатки, потом обувь.
PPE_GROUPS = [
    ("summer_kit", "Комплект лето"),
    ("winter_kit", "Комплект зима"),
    ("helmet", "Каска"),
    ("gloves", "Перчатки"),
    ("boots_winter", "Обувь зима"),
    ("boots_summer", "Обувь лето"),
]


def ppe_due_by_group(
    conn: psycopg.Connection,
) -> dict[int, dict[str, dict[str, Any]]]:
    """
    Срок носки по каждому виду СИЗ: сотрудник → вид → дата окончания.

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

    «Заменить до» пустое означает «до износа» — так выдают каску и перчатки.
    Такой вид тоже показывается, но без даты: срока у него нет, а видеть, что
    он на руках, надо.

    Фильтра по подразделению нет намеренно: доступ отрезает RLS, а строк тут
    столько же, сколько людей, помноженное на шесть.
    """
    out: dict[int, dict[str, dict[str, Any]]] = {}
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT DISTINCT ON (i.employee_id, grp)
                   i.employee_id,
                   --  25.09.2026: у позиций, заведённых при выдаче, вида для
                   --  колонок не было — берём его тем же правилом, что и
                   --  миграции 23 и 87
                   coalesce(it.track_group,
                            app.ppe_item_track_group(it.kind, it.name)) AS grp,
                   i.replace_at, i.issued_at, it.name, it.size
              FROM app.ppe_issue i
              JOIN app.ppe_item it ON it.id = i.ppe_item_id
             WHERE i.status = 'issued'
             ORDER BY i.employee_id, grp, i.issued_at DESC, i.id DESC
            """
        )
        for r in cur.fetchall():
            out.setdefault(r["employee_id"], {})[r["grp"]] = {
                "until": r["replace_at"],
                "since": r["issued_at"],
                "name": r["name"],
                "size": r["size"],
            }
    return out


def staff_counts(conn: psycopg.Connection, unit_id: int | None = None) -> dict[str, int]:
    params: dict[str, Any] = {}
    unit_filter = ""
    if unit_id:
        unit_filter = "AND em.org_unit_id = %(unit)s"
        params["unit"] = unit_id
    with conn.cursor() as cur:
        cur.execute(
            f"""
            SELECT count(*) FILTER (WHERE em.terminated_at IS NULL)     AS employed,
                   count(*) FILTER (WHERE em.terminated_at IS NOT NULL
                                      AND {_SETTLED})                   AS dismissed,
                   count(*) FILTER (WHERE {_SETTLING})                  AS settling,
                   (SELECT count(*) FROM app.staff_request sr
                     WHERE sr.status = 'new'
                       {unit_filter.replace('em.', 'sr.')})             AS requests
            FROM app.employment em
            WHERE 1 = 1 {unit_filter}
            """,
            params,
        )
        row = cur.fetchone()
    out = {k: int(v or 0) for k, v in row.items()}
    #  Счётчик «Заявок» показывает всё, что там лежит: и заявки, ждущие
    #  решения, и расчёты, ждущие отправки или приёма. Цифра, которая
    #  считает половину содержимого вкладки, хуже, чем никакой
    out["requests"] += out["settling"]
    return out


def card(conn: psycopg.Connection, employment_id: int) -> dict[str, Any] | None:
    """Основная часть карточки: текущее назначение и личные данные."""
    with conn.cursor() as cur:
        cur.execute(
            f"""
            SELECT em.id           AS employment_id,
                   e.id            AS employee_id,
                   e.personnel_no,
                   {FIO_FULL}      AS fio_full,
                   e.last_name, e.first_name, e.middle_name,
                   e.birth_date, e.phone, e.work_email, e.gender,
                   --  02.10.2026: телефонные графы начальника депо
                   --  (правка заказчика): общий депо, личный, резервный
                   --  интернет. Рабочий — e.phone выше
                   e.phone_depot, e.phone_personal, e.reserve_internet,
                   --  Размеры (23.09.2026): ведёт депо, в личном кабинете
                   --  их увидит сам сотрудник
                   e.clothing_size, e.shoe_size, e.height_cm, e.headwear_size,
                   e.sizes_updated_at,
                   p.name          AS position_name,
                   p.id            AS position_id,
                   p.code          AS position_code,
                   pc.name         AS category_name,
                   pc.code         AS category_code,
                   pc.is_piecework,
                   ou.id           AS unit_id,
                   ou.code         AS unit_code,
                   ou.name         AS unit_name,
                   em.hired_at, em.terminated_at, em.direction, em.comment,
                   em.confirmed_at,
                   --  Где увольнение на пути к «Уволенным» (миграция 70)
                   em.settlement_state, em.settlement_note,
                   em.created_at   AS added_at,
                   au.full_name    AS added_by,
                   tr.name         AS termination_reason,
                   em.transfer_to_text,
                   tu.name         AS transfer_to_unit,
                   tu.code         AS transfer_to_unit_code,
                   em.transferred_from_id,
                   -- Назначение, открытое переводом отсюда: по нему видно,
                   -- что откат ещё возможен
                   (SELECT nx.id FROM app.employment nx
                     WHERE nx.transferred_from_id = em.id
                       AND nx.terminated_at IS NULL
                     ORDER BY nx.id LIMIT 1) AS transferred_to_id
            FROM app.employment em
            JOIN app.employee e           ON e.id = em.employee_id
            JOIN app.position p           ON p.id = em.position_id
            JOIN app.position_category pc ON pc.id = p.category_id
            JOIN app.org_unit ou          ON ou.id = em.org_unit_id
            LEFT JOIN app.app_user au     ON au.id = em.created_by
            LEFT JOIN app.org_unit tu     ON tu.id = em.transfer_to_unit_id
            LEFT JOIN app.termination_reason tr ON tr.id = em.termination_reason_id
            WHERE em.id = %s
            """,
            (employment_id,),
        )
        return cur.fetchone()


def history(conn: psycopg.Connection, employee_id: int) -> list[dict[str, Any]]:
    """Все назначения человека — приёмы, переводы, увольнения."""
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT em.id AS employment_id,
                   ou.name AS unit_name, p.name AS position_name,
                   em.hired_at, em.terminated_at, em.direction, em.comment,
                   tr.name AS termination_reason,
                   au.full_name AS added_by, em.created_at AS added_at,
                   em.transfer_to_text, tu.name AS transfer_to_unit,
                   em.transferred_from_id
            FROM app.employment em
            JOIN app.org_unit ou ON ou.id = em.org_unit_id
            JOIN app.position p  ON p.id = em.position_id
            LEFT JOIN app.org_unit tu ON tu.id = em.transfer_to_unit_id
            LEFT JOIN app.termination_reason tr ON tr.id = em.termination_reason_id
            LEFT JOIN app.app_user au ON au.id = em.created_by
            WHERE em.employee_id = %s
            ORDER BY em.hired_at DESC, em.id DESC
            """,
            (employee_id,),
        )
        return cur.fetchall()


def tsd(conn: psycopg.Connection, employee_id: int) -> list[dict[str, Any]]:
    """
    Учётные записи ТСД. Таблица закрыта политикой на право personal_data.read,
    поэтому у начальника РЦ вернётся пустой список — это не ошибка.
    """
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT id, login, password, issued_at, revoked_at
            FROM app.tsd_account
            WHERE employee_id = %s
            ORDER BY issued_at DESC, id DESC
            """,
            (employee_id,),
        )
        return cur.fetchall()


def timesheet_summary(
    conn: psycopg.Connection, employment_id: int, months: int = 6
) -> list[dict[str, Any]]:
    """Сводка по табелю: сколько отработано по месяцам и норма месяца."""
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT t.period_month,
                   t.status,
                   count(*) FILTER (WHERE d.hours_day + d.hours_night > 0)
                       AS worked_days,
                   coalesce(sum(d.hours_day + d.hours_night), 0) AS hours_total,
                   coalesce(sum(d.hours_night), 0)     AS hours_night,
                   coalesce(sum(d.hours_weekend), 0)   AS hours_weekend,
                   coalesce(sum(d.hours_overtime), 0)  AS hours_overtime,
                   count(*) FILTER (WHERE d.day_code IS NOT NULL) AS coded_days,
                   n.norm_hours_40 AS norm
            FROM app.timesheet_day d
            JOIN app.timesheet t ON t.id = d.timesheet_id
            LEFT JOIN app.v_month_norm n ON n.period_month = t.period_month
            WHERE d.employment_id = %s
            GROUP BY t.period_month, t.status, n.norm_hours_40
            ORDER BY t.period_month DESC
            LIMIT %s
            """,
            (employment_id, months),
        )
        return cur.fetchall()


def requests(
    conn: psycopg.Connection,
    unit_id: int | None = None,
    employment_id: int | None = None,
    only_open: bool = False,
) -> list[dict[str, Any]]:
    where = ["1 = 1"]
    params: dict[str, Any] = {}
    if unit_id:
        where.append("sr.org_unit_id = %(unit)s")
        params["unit"] = unit_id
    if employment_id:
        where.append("sr.employment_id = %(emp)s")
        params["emp"] = employment_id
    if only_open:
        where.append("sr.status = 'new'")

    with conn.cursor() as cur:
        cur.execute(
            f"""
            SELECT sr.id, sr.employment_id, sr.kind, sr.status,
                   sr.effective_date, sr.direction, sr.new_phone, sr.comment,
                   sr.fix_field, sr.fix_value,
                   sr.created_at, sr.decided_at, sr.decision_comment,
                   -- Для заявки на должность показываем название, а не код
                   fp.name AS fix_position_name,
                   tr.name AS reason_name,
                   cu.full_name AS created_by_name,
                   du.full_name AS decided_by_name,
                   ou.name AS unit_name, ou.code AS unit_code,
                   tu.name AS transfer_unit_name,
                   {FIO_FULL} AS fio_full, e.personnel_no
            FROM app.staff_request sr
            JOIN app.employment em ON em.id = sr.employment_id
            JOIN app.employee e    ON e.id = em.employee_id
            JOIN app.org_unit ou   ON ou.id = sr.org_unit_id
            LEFT JOIN app.org_unit tu ON tu.id = sr.transfer_unit_id
            LEFT JOIN app.termination_reason tr ON tr.id = sr.termination_reason_id
            LEFT JOIN app.app_user cu ON cu.id = sr.created_by
            LEFT JOIN app.app_user du ON du.id = sr.decided_by
            LEFT JOIN app.position fp ON fp.code = sr.fix_value
                                     AND sr.fix_field = 'position'
            WHERE {' AND '.join(where)}
            ORDER BY (sr.status = 'new') DESC, sr.created_at DESC
            """,
            params,
        )
        return cur.fetchall()


def settlements(conn: psycopg.Connection,
                unit_id: int | None = None) -> list[dict[str, Any]]:
    """
    Уволенные, по которым окончательный расчёт ещё не принят.

    Живут во вкладке «Заявки» — так решил заказчик 22.09.2026: «сотрудник не
    попадает в "Уволенные", а остаётся в "Заявки", и у него меняется
    состояние на кнопку "окончательный расчёт"». В «Уволенных» их пока нет,
    и другого места, где о них вспомнят, тоже нет.

    Вместе со строкой едет признак `has_run`: без посчитанного расчёта
    отправлять нечего, и кнопка должна вести на расчёт, а не в отказ.
    """
    where = [_SETTLING]
    params: dict[str, Any] = {}
    if unit_id:
        where.append("em.org_unit_id = %(unit)s")
        params["unit"] = unit_id

    with conn.cursor() as cur:
        cur.execute(
            f"""
            SELECT em.id AS employment_id, e.id AS employee_id,
                   e.personnel_no, {FIO_FULL} AS fio_full,
                   p.name  AS position_name,
                   ou.name AS unit_name, ou.code AS unit_code,
                   em.terminated_at, em.settlement_state, em.settlement_note,
                   em.settlement_sent_at,
                   tr.name AS termination_reason,
                   su.full_name AS settlement_sent_by_name,
                   EXISTS (SELECT 1 FROM app.payroll_run r
                            WHERE r.kind = 'final'
                              AND r.employment_id = em.id
                              AND r.status <> 'superseded') AS has_run,
                   --  24.09.2026: что уволенный ещё не сдал — пока список
                   --  не пуст, расчёт не отправляется
                   (SELECT string_agg(o.title, '; ')
                      FROM app.settlement_outstanding(em.id) o) AS outstanding
            FROM app.employment em
            JOIN app.employee e   ON e.id = em.employee_id
            JOIN app.position p   ON p.id = em.position_id
            JOIN app.org_unit ou  ON ou.id = em.org_unit_id
            LEFT JOIN app.termination_reason tr ON tr.id = em.termination_reason_id
            LEFT JOIN app.app_user su ON su.id = em.settlement_sent_by
            WHERE {' AND '.join(where)}
            ORDER BY em.terminated_at, e.last_name
            """,
            params,
        )
        return cur.fetchall()


# ---------------------------------------------------------------------------
#  Изменения — через функции базы, они же проверяют права
# ---------------------------------------------------------------------------

def submit_settlement(conn: psycopg.Connection, employment_id: int,
                      note: str | None = None) -> None:
    with conn.cursor() as cur:
        cur.execute("SELECT app.submit_settlement(%s, %s)",
                    (employment_id, note))
    conn.commit()


def settlement_outstanding(conn: psycopg.Connection,
                           employment_id: int) -> list[dict[str, Any]]:
    """СИЗ, техника и ТСД, которые уволенный ещё не сдал (24.09.2026)."""
    with conn.cursor() as cur:
        cur.execute("SELECT kind, title FROM app.settlement_outstanding(%s)",
                    (employment_id,))
        return cur.fetchall()


def confirm_tsd_return(conn: psycopg.Connection, employment_id: int) -> None:
    """Депо подтверждает, что уволенный сдал терминал ТСД."""
    with conn.cursor() as cur:
        cur.execute("SELECT app.confirm_tsd_return(%s)", (employment_id,))
    conn.commit()


def accept_settlement(conn: psycopg.Connection, employment_id: int) -> None:
    with conn.cursor() as cur:
        cur.execute("SELECT app.accept_settlement(%s)", (employment_id,))
    conn.commit()


def return_settlement(conn: psycopg.Connection, employment_id: int,
                      reason: str) -> None:
    with conn.cursor() as cur:
        cur.execute("SELECT app.return_settlement(%s, %s)",
                    (employment_id, reason))
    conn.commit()


def set_sizes(conn: psycopg.Connection, employee_id: int, clothing: str | None,
              shoe: str | None, height: int | None,
              headwear: str | None) -> None:
    """Размеры одежды и обуви. Права и доступ к депо проверяет база."""
    with conn.cursor() as cur:
        cur.execute("SELECT app.set_employee_sizes(%s, %s, %s, %s::smallint, %s)",
                    (employee_id, clothing, shoe, height, headwear))
    conn.commit()


def hire(conn: psycopg.Connection, commit: bool = True, **kw) -> int:
    """
    Принять сотрудника. `commit=False` — часть одной операции с отметкой
    кандидата (02.10.2026): сбой на втором шаге откатывает и приём, и
    человек не остаётся «принятым» при кандидате «в подборе».
    """
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT app.hire_employee(
                %(last_name)s, %(first_name)s, %(middle_name)s, %(personnel_no)s,
                %(unit_code)s, %(position_code)s, %(hired_at)s, %(birth_date)s,
                %(phone)s, %(work_email)s, %(tsd_login)s, %(tsd_password)s
            ) AS id
            """,
            kw,
        )
        emp_id = cur.fetchone()["id"]
    if commit:
        conn.commit()
    return int(emp_id)


def update_draft_hire(conn: psycopg.Connection, employment_id: int,
                      **kw) -> None:
    """
    Депо правит карточку приёма, возвращённую на доработку.

    Отдельной кнопки «отправить заново» нет: сохранение снимает пометку о
    доработке и возвращает карточку в очередь администрации. Две кнопки
    подряд означают, что про вторую забудут, и исправленная карточка
    останется лежать помеченной как испорченная.
    """
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT app.update_draft_hire(
                %(employment_id)s, %(last_name)s, %(first_name)s,
                %(middle_name)s, %(personnel_no)s, %(position_code)s,
                %(hired_at)s, %(birth_date)s, %(phone)s, %(work_email)s)
            """,
            dict(kw, employment_id=employment_id),
        )
    conn.commit()


def terminate(
    conn: psycopg.Connection,
    employment_id: int,
    when: date,
    reason_id: int,
    direction: str = "dismissal",
    comment: str | None = None,
    non_renewal_id: int | None = None,
) -> None:
    """
    Прямое увольнение администрацией.

    `non_renewal_id` — почему не продлили контракт. Спрашивается там же, где
    и в заявке депо (22.09.2026): увольняют двумя путями, и правило у них
    одно. Держит его база — функция `app.check_non_renewal`.
    """
    with conn.cursor() as cur:
        cur.execute(
            "SELECT app.terminate_employment(%s, %s, %s, %s, %s, %s)",
            (employment_id, when, reason_id, direction, comment,
             non_renewal_id),
        )
    conn.commit()


def request_create(conn: psycopg.Connection, **kw) -> int:
    """
    Заявка депо: увольнение, перевод или исправление данных.

    Для исправления передаются fix_field и fix_value — что именно поправить и
    на что. Значение проверяется в базе сразу при подаче: иначе ошибку в
    формате даты увидела бы администрация через неделю, а искать причину
    пришлось бы тому, кто подавал.
    """
    kw.setdefault("fix_field", None)
    kw.setdefault("fix_value", None)
    kw.setdefault("new_phone", None)
    #  Почему не продлили контракт — спрашивается только у «Окончания
    #  контракта» (22.09.2026). Проверку держит база: форма показывает поле,
    #  а требует его функция
    kw.setdefault("non_renewal_id", None)
    #  30.09.2026: заявке-переводу — код нового РЦ (вычитка 2.25)
    kw.setdefault("transfer_unit", None)
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT app.staff_request_create(
                %(employment_id)s, %(kind)s, %(effective_date)s, %(reason_id)s,
                %(direction)s, %(new_phone)s, %(comment)s,
                %(fix_field)s, %(fix_value)s, %(non_renewal_id)s,
                %(transfer_unit)s
            ) AS id
            """,
            kw,
        )
        rid = cur.fetchone()["id"]
    conn.commit()
    return int(rid)


def request_decide(
    conn: psycopg.Connection, request_id: int, approve: bool, comment: str | None
) -> None:
    with conn.cursor() as cur:
        cur.execute(
            "SELECT app.staff_request_decide(%s, %s, %s)",
            (request_id, approve, comment),
        )
    conn.commit()


def transfer(
    conn: psycopg.Connection,
    employment_id: int,
    when: date,
    to_unit_code: str | None,
    to_position: str | None = None,
    to_text: str | None = None,
    comment: str | None = None,
) -> int | None:
    """
    Перевод сотрудника. Возвращает id нового назначения или None.

    to_unit_code задан — перевод внутри отдела: назначение в прежнем РЦ
    закрывается днём раньше даты перевода, в целевом открывается новое, и
    человек продолжает попадать в табель, только уже там.

    to_unit_code = None — перевод внутри компании за пределы отдела: нового
    назначения нет, а куда именно ушёл человек, остаётся в to_text.
    """
    with conn.cursor() as cur:
        cur.execute(
            "SELECT app.transfer_employee(%s, %s, %s, %s, %s, %s) AS id",
            (employment_id, when, to_unit_code, to_position, to_text, comment),
        )
        row = cur.fetchone()
    conn.commit()
    new_id = row["id"] if row else None
    return int(new_id) if new_id else None


def undo_close(conn: psycopg.Connection, employment_id: int) -> str:
    """
    Откатить увольнение или перевод. Возвращает словами, что отменено.

    Запись, созданную переводом, база удалит только если в ней ещё нет
    заполненного табеля — иначе откат стёр бы учтённые часы и поднимет ошибку.
    """
    with conn.cursor() as cur:
        cur.execute(
            "SELECT app.undo_employment_close(%s) AS what", (employment_id,)
        )
        row = cur.fetchone()
    conn.commit()
    return (row or {}).get("what") or "кадровое действие"


def set_position(
    conn: psycopg.Connection, employment_id: int, position_code: str
) -> None:
    """
    Исправить должность в действующем назначении — без истории.

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

    Право проверяется здесь явно. RLS на employment пускает всех, у кого есть
    доступ к подразделению, — то есть и начальника РЦ. А менять должность
    задним числом может только администрация: у неё право employee.write.
    """
    with conn.cursor() as cur:
        cur.execute("SELECT app.has_perm('employee.write') AS ok")
        if not (cur.fetchone() or {}).get("ok"):
            raise ValueError("Менять должность может только администрация")

        cur.execute(
            """
            UPDATE app.employment em
               SET position_id = p.id
              FROM app.position p
             WHERE em.id = %s AND p.code = %s AND p.is_active
               AND em.terminated_at IS NULL
            """,
            (employment_id, position_code),
        )
        if cur.rowcount == 0:
            raise ValueError(
                "Должность не изменена: назначение закрыто, должность "
                "выключена или её нет в справочнике"
            )
    conn.commit()


def units_for_transfer(conn: psycopg.Connection) -> list[dict[str, Any]]:
    """Куда можно перевести внутри отдела — действующие РЦ по алфавиту."""
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT id, code, name, city
            FROM app.org_unit
            WHERE kind = 'rc' AND is_active
            ORDER BY name
            """
        )
        return cur.fetchall()


# ---------------------------------------------------------------------------
#  Данные карточки: контракт, охрана труда, медосмотры, свидетельства, топливо
# ---------------------------------------------------------------------------

def contract(conn: psycopg.Connection, employee_id: int) -> dict[str, Any] | None:
    """Действующее продление контракта — с наибольшей датой продления."""
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT c.id, c.extended_at, c.term_months, c.ends_at, c.note,
                   c.updated_at, u.full_name AS updated_by_name
            FROM app.employee_contract c
            LEFT JOIN app.app_user u ON u.id = c.updated_by
            WHERE c.employee_id = %s
            ORDER BY c.extended_at DESC, c.id DESC
            LIMIT 1
            """,
            (employee_id,),
        )
        return cur.fetchone()


def contract_history(conn: psycopg.Connection, employee_id: int) -> list[dict[str, Any]]:
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT extended_at, term_months, ends_at, note
            FROM app.employee_contract
            WHERE employee_id = %s
            ORDER BY extended_at DESC, id DESC
            """,
            (employee_id,),
        )
        return cur.fetchall()


def save_contract(
    conn: psycopg.Connection,
    *,
    employee_id: int,
    extended_at: date,
    term_months: int,
    note: str | None,
) -> date:
    """
    Записать продление контракта. Дату окончания считаем и храним.

    Срок вводится руками — так решено заказчиком: сроки бывают разные, и
    зашивать «плюс год» нельзя. Окончание — последний день срока, поэтому
    минус день: контракт, продлённый 1 марта на 12 месяцев, заканчивается
    28 февраля, а не 1 марта.
    """
    ends_at = _contract_end(extended_at, term_months)
    with conn.cursor() as cur:
        cur.execute(
            """
            INSERT INTO app.employee_contract
                (employee_id, extended_at, term_months, ends_at, note,
                 updated_by, updated_at)
            VALUES (%s, %s, %s, %s, %s,
                    nullif(current_setting('app.user_id', true), '')::bigint, now())
            """,
            (employee_id, extended_at, term_months, ends_at,
             (note or "").strip() or None),
        )
    conn.commit()
    return ends_at


def _add_months(day: date, months: int) -> date:
    """Дата плюс месяцы, с поправкой на короткий месяц: 31.01 + 1 = 28.02."""
    total = day.month - 1 + months
    year = day.year + total // 12
    month = total % 12 + 1
    last = calendar.monthrange(year, month)[1]
    return date(year, month, min(day.day, last))


def _contract_end(extended_at: date, term_months: int) -> date:
    """
    Конец срока контракта (решение от 30.09.2026): тот же день через N
    месяцев минус один день; если дня продления в последнем месяце нет
    (31.01 + 1 мес), срок накрывает короткий месяц целиком и кончается
    его последним днём — 28.02, без вычитания дня. Прежний расчёт давал
    27.02: сначала прижимал 31-е к концу февраля, потом отнимал день.
    """
    target = _add_months(extended_at, term_months)
    last = calendar.monthrange(target.year, target.month)[1]
    if extended_at.day > last:
        return target
    return target - timedelta(days=1)


def briefing_types(conn: psycopg.Connection) -> list[dict[str, Any]]:
    """Виды инструктажей по охране труда — по порядку из справочника."""
    with conn.cursor() as cur:
        cur.execute(
            #  `once_only` — вводный и первичный: они проводятся один раз за
            #  приём, и форма записи для них исчезает, как только запись есть
            #  (разбор сбоя 22.09.2026)
            "SELECT id, code, name, note, period_months, is_active, once_only "
            "  FROM app.briefing_type WHERE is_active "
            " ORDER BY sort_order, name"
        )
        return cur.fetchall()


def briefing_conductors(
    conn: psycopg.Connection, unit_id: int
) -> list[str]:
    """
    Кто может значиться проводившим инструктаж в этом депо.

    Ответ заказчика от 21.09.2026 (О6): «сделать список в выпадающем списке
    только из начальника и заместителей (мастеров убрать)». Берём по
    категории должности, а не по учётным записям: инструктаж проводит
    должностное лицо, и у него может не быть входа в приложение.
    """
    with conn.cursor() as cur:
        cur.execute(
            f"""
            SELECT DISTINCT {FIO_FULL} AS fio, pc.sort_order, e.last_name
              FROM app.employment em
              JOIN app.employee e ON e.id = em.employee_id
              JOIN app.position p ON p.id = em.position_id
              JOIN app.position_category pc ON pc.id = p.category_id
             WHERE em.org_unit_id = %s
               AND em.terminated_at IS NULL
               AND pc.code IN ('head', 'deputy')
             ORDER BY pc.sort_order, e.last_name
            """,
            (unit_id,),
        )
        return [r["fio"] for r in cur.fetchall()]


def briefings(conn: psycopg.Connection, employee_id: int) -> list[dict[str, Any]]:
    """
    Инструктажи по охране труда: по виду — свежий сверху.

    Виды разные по смыслу: вводный и первичный проводятся один раз, повторный
    не реже раза в полгода, внеплановый и целевой — по событию. Поэтому это не
    один список «инструктаж», а список по каждому виду.
    """
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT b.id, b.held_at, b.valid_days, b.valid_until, b.held_by,
                   t.id AS type_id, t.code, t.name, t.note AS type_note,
                   t.period_months, t.once_only,
                   --  Кто внёс запись — отдельно от того, кто провёл
                   --  (ответ О6 от 21.09.2026)
                   u.full_name AS recorded_by_name
            FROM app.safety_briefing b
            LEFT JOIN app.briefing_type t ON t.id = b.type_id
            LEFT JOIN app.app_user u ON u.id = b.recorded_by
            WHERE b.employee_id = %s
            ORDER BY t.sort_order NULLS LAST, b.held_at DESC, b.id DESC
            """,
            (employee_id,),
        )
        return cur.fetchall()


def briefing_by_type(
    conn: psycopg.Connection
) -> dict[int, dict[int, dict[str, date | None]]]:
    """
    Последний инструктаж каждого вида по каждому сотруднику — для списка.

    Формат тот же, что у due_by_type: «until» — «действует до» (есть только у
    повторного), «since» — дата проведения. Так одна клетка списка умеет
    показать и срок с подсветкой, и просто дату.
    """
    out: dict[int, dict[int, dict[str, date | None]]] = {}
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT DISTINCT ON (b.employee_id, b.type_id)
                   b.employee_id, b.type_id, b.held_at, b.valid_until
            FROM app.safety_briefing b
            WHERE b.type_id IS NOT NULL
            ORDER BY b.employee_id, b.type_id, b.held_at DESC, b.id DESC
            """
        )
        for r in cur.fetchall():
            out.setdefault(r["employee_id"], {})[r["type_id"]] = {
                "until": r["valid_until"], "since": r["held_at"],
            }
    return out


def save_briefing(
    conn: psycopg.Connection,
    *,
    employee_id: int,
    type_id: int,
    held_at: date,
    valid_until: date | None,
    held_by: str | None = None,
    recorded_by: int | None = None,
) -> None:
    """
    Инструктаж: вид, дата проведения и «действует до».

    Двое в одной записи (ответ О6 от 21.09.2026): `held_by` — кто провёл,
    выбранный из списка начальника и заместителей депо; `recorded_by` — кто
    внёс запись. Начальник может записать инструктаж, который провёл его
    заместитель, и в журнале должны остаться оба.

    В таблице «действует до» — вычисляемая колонка от срока в днях, поэтому
    записываем срок: valid_until − held_at. Так обе даты, введённые руками,
    сохраняются точно, и рассогласования между ними не возникает.

    Если срок не введён, он берётся из периодичности вида: у повторного это
    6 месяцев. У видов без периодичности (вводный, первичный, внеплановый,
    целевой) срока нет вовсе — там остаётся только дата проведения, и
    выдумывать ей дату окончания нельзя.
    """
    if valid_until is not None and valid_until < held_at:
        raise ValueError("«Действует до» раньше даты проведения инструктажа")

    with conn.cursor() as cur:
        cur.execute(
            "SELECT name, period_months FROM app.briefing_type WHERE id = %s",
            (type_id,),
        )
        kind = cur.fetchone()
        if kind is None:
            raise ValueError("Вид инструктажа не найден — обновите страницу")

        #  02.10.2026 (вычитка 2.25): у периодического вида срок всегда
        #  считается от даты проведения — ровно через период (повторный —
        #  через 6 месяцев). Введённое руками «действует до» у него больше не
        #  расходится с правилом на 1–3 дня
        if kind["period_months"]:
            days = (_add_months(held_at, kind["period_months"]) - held_at).days
        elif valid_until is not None:
            days = (valid_until - held_at).days
        else:
            days = None

        cur.execute(
            """
            INSERT INTO app.safety_briefing
                (employee_id, type_id, briefing_type, held_at, valid_days,
                 held_by, recorded_by)
            VALUES (%s, %s, %s, %s, %s, %s, %s)
            """,
            (employee_id, type_id, kind["name"], held_at, days,
             (held_by or "").strip() or None, recorded_by),
        )
    conn.commit()


def drop_briefing(conn: psycopg.Connection, briefing_id: int) -> None:
    """
    Убрать задвоенную запись об инструктаже.

    Разбор 22.09.2026: у человека оказалось два вводных — верный от даты
    приёма и второй, появившийся при попытке поправить первый. Повторения
    таких записей теперь не бывает (триггер `safety_briefing_once`), но уже
    заведённые надо чем-то убрать: какая из двух верная, знает только тот,
    кто их вносил, и угадывать за него приложение не станет.

    Удаление, а не отметка «неверна»: запись об инструктаже, которого не
    было, — не история, а мусор. Само удаление попадает в журнал: на таблице
    стоит аудит (миграция 49).
    """
    with conn.cursor() as cur:
        cur.execute("DELETE FROM app.safety_briefing WHERE id = %s RETURNING id",
                    (briefing_id,))
        if cur.fetchone() is None:
            raise ValueError("Запись не найдена — обновите страницу")
    conn.commit()


def update_briefing(
    conn: psycopg.Connection,
    *,
    briefing_id: int,
    held_at: date,
    held_by: str | None = None,
    recorded_by: int | None = None,
) -> None:
    """
    Исправить запись об инструктаже, введённую с ошибкой.

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

    Срок «действует до» пересчитывается от новой даты: он вычисляемая колонка
    от `valid_days`, а сами дни считаются от периодичности вида.

    В журнал правка попадает сама — на таблице стоит аудит (миграция 49).
    """
    with conn.cursor() as cur:
        #  02.10.2026: срок пересчитывается от новой даты по периоду вида.
        #  Раньше сохранялось прежнее число дней, и «действует до» после
        #  исправления даты расходилось с «плюс 6 месяцев» на 1–3 дня
        cur.execute(
            """
            SELECT t.period_months FROM app.safety_briefing b
            LEFT JOIN app.briefing_type t ON t.id = b.type_id
            WHERE b.id = %s
            """, (briefing_id,))
        row = cur.fetchone()
        period = row["period_months"] if row else None
        new_days = ((_add_months(held_at, period) - held_at).days
                    if period else None)
        cur.execute(
            """
            UPDATE app.safety_briefing
               SET held_at = %s,
                   held_by = coalesce(%s, held_by),
                   recorded_by = coalesce(%s, recorded_by),
                   valid_days = CASE WHEN %s::int IS NOT NULL THEN %s::int
                                     ELSE valid_days END
             WHERE id = %s
            """,
            (held_at, (held_by or "").strip() or None, recorded_by,
             new_days, new_days, briefing_id),
        )
        if cur.rowcount == 0:
            raise ValueError("Запись об инструктаже не найдена — обновите страницу")
    conn.commit()


def medical_exams(conn: psycopg.Connection, employee_id: int) -> list[dict[str, Any]]:
    """Медосмотры по всем видам работ — включая непройденные."""
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT t.id AS type_id, t.code, t.name, t.note AS type_note,
                   m.id, m.passed_at, m.valid_until, m.note,
                   m.updated_at, u.full_name AS updated_by_name
            FROM app.medical_exam_type t
            LEFT JOIN app.employee_medical_exam m
                   ON m.type_id = t.id AND m.employee_id = %s
            LEFT JOIN app.app_user u ON u.id = m.updated_by
            WHERE t.is_active
            ORDER BY t.sort_order, t.name
            """,
            (employee_id,),
        )
        return cur.fetchall()


def save_medical_exam(
    conn: psycopg.Connection,
    *,
    employee_id: int,
    type_id: int,
    passed_at: date | None,
    valid_until: date,
    note: str | None,
) -> None:
    with conn.cursor() as cur:
        cur.execute(
            """
            INSERT INTO app.employee_medical_exam
                (employee_id, type_id, passed_at, valid_until, note,
                 updated_by, updated_at)
            VALUES (%s, %s, %s, %s, %s,
                    nullif(current_setting('app.user_id', true), '')::bigint, now())
            ON CONFLICT (employee_id, type_id) DO UPDATE
               SET passed_at   = EXCLUDED.passed_at,
                   valid_until = EXCLUDED.valid_until,
                   note        = EXCLUDED.note,
                   updated_by  = EXCLUDED.updated_by,
                   updated_at  = now()
            """,
            (employee_id, type_id, passed_at, valid_until,
             (note or "").strip() or None),
        )
    conn.commit()


def certificates(conn: psycopg.Connection, employee_id: int) -> list[dict[str, Any]]:
    """Свидетельства и удостоверения по всем видам — включая незаполненные."""
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT t.id AS type_id, t.code, t.name, t.note AS type_note,
                   c.id, c.number, c.issued_at, c.valid_until, c.note,
                   c.updated_at, u.full_name AS updated_by_name
            FROM app.certificate_type t
            LEFT JOIN app.employee_certificate c
                   ON c.type_id = t.id AND c.employee_id = %s
            LEFT JOIN app.app_user u ON u.id = c.updated_by
            WHERE t.is_active
            ORDER BY t.sort_order, t.name
            """,
            (employee_id,),
        )
        return cur.fetchall()


def save_certificate(
    conn: psycopg.Connection,
    *,
    employee_id: int,
    type_id: int,
    number: str | None,
    issued_at: date,
    valid_until: date | None,
    note: str | None,
) -> None:
    with conn.cursor() as cur:
        cur.execute(
            """
            INSERT INTO app.employee_certificate
                (employee_id, type_id, number, issued_at, valid_until, note,
                 updated_by, updated_at)
            VALUES (%s, %s, %s, %s, %s, %s,
                    nullif(current_setting('app.user_id', true), '')::bigint, now())
            ON CONFLICT (employee_id, type_id) DO UPDATE
               SET number      = EXCLUDED.number,
                   issued_at   = EXCLUDED.issued_at,
                   valid_until = EXCLUDED.valid_until,
                   note        = EXCLUDED.note,
                   updated_by  = EXCLUDED.updated_by,
                   updated_at  = now()
            """,
            (employee_id, type_id, (number or "").strip() or None,
             issued_at, valid_until, (note or "").strip() or None),
        )
    conn.commit()


def fuel_norm(conn: psycopg.Connection, employee_id: int) -> dict[str, Any] | None:
    """Действующая месячная норма компенсации топливом."""
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT f.id, f.card_number, f.liters_month, f.amount_month,
                   f.valid_from, f.valid_to, f.note,
                   f.updated_at, u.full_name AS updated_by_name
            FROM app.employee_fuel_norm f
            LEFT JOIN app.app_user u ON u.id = f.updated_by
            WHERE f.employee_id = %s
              AND f.valid_from <= current_date
              AND (f.valid_to IS NULL OR f.valid_to > current_date)
            ORDER BY f.valid_from DESC, f.id DESC
            LIMIT 1
            """,
            (employee_id,),
        )
        return cur.fetchone()


def save_fuel_norm(
    conn: psycopg.Connection,
    *,
    employee_id: int,
    card_number: str | None,
    liters_month: float,
    amount_month: float,
    valid_from: date,
    note: str | None,
) -> None:
    """
    Новая норма с даты: прежняя закрывается этой же датой.

    Норма историчная, как ставки: расчёт за прошлый месяц должен считаться по
    той норме, которая тогда действовала.
    """
    with conn.cursor() as cur:
        cur.execute(
            """
            UPDATE app.employee_fuel_norm
               SET valid_to = %(from)s
             WHERE employee_id = %(emp)s AND valid_from < %(from)s
               AND (valid_to IS NULL OR valid_to > %(from)s)
            """,
            {"emp": employee_id, "from": valid_from},
        )
        cur.execute(
            "DELETE FROM app.employee_fuel_norm "
            " WHERE employee_id = %(emp)s AND valid_from >= %(from)s",
            {"emp": employee_id, "from": valid_from},
        )
        cur.execute(
            """
            INSERT INTO app.employee_fuel_norm
                (employee_id, card_number, liters_month, amount_month,
                 valid_from, note, updated_by, updated_at)
            VALUES (%s, %s, %s, %s, %s, %s,
                    nullif(current_setting('app.user_id', true), '')::bigint, now())
            """,
            (employee_id, (card_number or "").strip() or None,
             liters_month, amount_month, valid_from,
             (note or "").strip() or None),
        )
    conn.commit()


# ---------------------------------------------------------------------------
#  Фотография
# ---------------------------------------------------------------------------

# По каким первым байтам узнаём картинку. Верить заявленному типу файла
# нельзя: браузер берёт его из расширения, а расширение переименовывается.
PHOTO_SIGNATURES = (
    (b"\xff\xd8\xff", "image/jpeg"),
    (b"\x89PNG\r\n\x1a\n", "image/png"),
)
PHOTO_MAX_BYTES = 4 * 1024 * 1024


def detect_image(data: bytes) -> str:
    """
    Определить тип картинки по содержимому. Ошибка, если это не картинка.
    """
    for magic, mime in PHOTO_SIGNATURES:
        if data.startswith(magic):
            return mime
    if data[:4] == b"RIFF" and data[8:12] == b"WEBP":
        return "image/webp"
    raise ValueError(
        "Это не изображение. Подойдёт JPEG, PNG или WebP — "
        "обычная фотография с телефона или скан"
    )


def photo_meta(conn: psycopg.Connection, employee_id: int) -> dict[str, Any] | None:
    """
    Есть ли фотография и когда менялась — без самих байтов.

    Карточка рисуется этим запросом, а снимок браузер забирает отдельным
    адресом и кладёт в кеш. Тянуть его в каждый запрос страницы незачем.
    """
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT p.mime_type, p.size_bytes, p.updated_at,
                   u.full_name AS updated_by_name
            FROM app.employee_photo p
            LEFT JOIN app.app_user u ON u.id = p.updated_by
            WHERE p.employee_id = %s
            """,
            (employee_id,),
        )
        return cur.fetchone()


def photo_bytes(conn: psycopg.Connection, employee_id: int):
    """Сами байты снимка — для отдачи браузеру."""
    with conn.cursor() as cur:
        cur.execute(
            "SELECT mime_type, data, updated_at FROM app.employee_photo "
            " WHERE employee_id = %s",
            (employee_id,),
        )
        return cur.fetchone()


def save_photo(
    conn: psycopg.Connection, employee_id: int, data: bytes
) -> tuple[str, int]:
    """Записать фотографию, заменив прежнюю. Возвращает тип и размер."""
    if not data:
        raise ValueError("Файл пустой")
    if len(data) > PHOTO_MAX_BYTES:
        raise ValueError(
            f"Файл {len(data) // 1024 // 1024} МБ — это слишком много. "
            f"Предел 4 МБ; обычно снимок уменьшается сам при отправке"
        )
    mime = detect_image(data)
    with conn.cursor() as cur:
        cur.execute(
            """
            INSERT INTO app.employee_photo
                (employee_id, mime_type, size_bytes, data, updated_at, updated_by)
            VALUES (%s, %s, %s, %s, now(),
                    nullif(current_setting('app.user_id', true), '')::bigint)
            ON CONFLICT (employee_id) DO UPDATE
               SET mime_type  = EXCLUDED.mime_type,
                   size_bytes = EXCLUDED.size_bytes,
                   data       = EXCLUDED.data,
                   updated_at = now(),
                   updated_by = EXCLUDED.updated_by
            """,
            (employee_id, mime, len(data), data),
        )
    conn.commit()
    return mime, len(data)


def delete_photo(conn: psycopg.Connection, employee_id: int) -> bool:
    with conn.cursor() as cur:
        cur.execute(
            "DELETE FROM app.employee_photo WHERE employee_id = %s", (employee_id,)
        )
        removed = cur.rowcount > 0
    conn.commit()
    return removed


def ppe_issued(conn: psycopg.Connection, employee_id: int) -> list[dict[str, Any]]:
    """
    Что выдано сотруднику из СИЗ и спецодежды.

    По каждой строке видно оба документа: ведомость, по которой средство
    выдали, и ведомость, по которой сдали. Отсюда же берутся признаки
    «можно отметить на выдачу» и «можно отметить на сдачу»: сдают то, что на
    руках, — независимо от того, вошло ли оно уже в ведомость на выдачу.
    """
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT i.id, it.name, it.kind, it.size, it.classification,
                   i.issued_at, i.replace_at, i.qty, i.wear_percent,
                   i.status, i.returned_at, i.annul_reason, i.annulled_at,
                   st.number  AS statement_number,
                   ret.number AS return_number,
                   --  Износ (вычитка 30.09.2026, п. 5.17): ноль при
                   --  выдаче, сто к «заменить до», акт продления
                   --  обнуляет. Сданное — значение, зафиксированное при
                   --  сдаче. (В комментарии не должно быть знака процента:
                   --  psycopg ищет плейсхолдеры и в комментарии тоже)
                   app.ppe_wear_of(i.id, current_date) AS wear_now,
                   (i.statement_id IS NULL AND i.status = 'issued')
                       AS can_pick_issue,
                   (i.return_statement_id IS NULL AND i.status = 'issued')
                       AS can_pick_return,
                   --  «На продление» (25.09.2026): только то, что на руках и
                   --  со сроком носки; «до износа» продлевать нечего
                   (i.status = 'issued' AND i.replace_at IS NOT NULL)
                       AS can_pick_extend,
                   (SELECT a.extended_to FROM app.ppe_extension_line l
                      JOIN app.ppe_extension_act a ON a.id = l.act_id
                     WHERE l.issue_id = i.id AND a.annulled_at IS NULL
                     ORDER BY a.act_date DESC, a.id DESC LIMIT 1) AS extended_to
            FROM app.ppe_issue i
            JOIN app.ppe_item it ON it.id = i.ppe_item_id
            LEFT JOIN app.ppe_statement st  ON st.id = i.statement_id
            LEFT JOIN app.ppe_statement ret ON ret.id = i.return_statement_id
            WHERE i.employee_id = %s
            ORDER BY i.issued_at DESC, i.id DESC
            """,
            (employee_id,),
        )
        return cur.fetchall()


def ppe_norms(conn: psycopg.Connection, employment_id: int) -> list[dict[str, Any]]:
    """
    Что положено по норме — по категории должности этого назначения.

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

    Рядом с каждой строкой — сколько такого уже выдано и на руках. По этому
    видно, что осталось выдать, а не только что положено вообще.
    """
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT n.id, n.ppe_kind, n.item_name, n.classification, n.qty,
                   n.wear_period_months, n.condition_note, n.valid_from,
                   coalesce(n.no_size, false) AS no_size,
                   (SELECT count(*)
                      FROM app.ppe_issue i
                      JOIN app.ppe_item it ON it.id = i.ppe_item_id
                      JOIN app.employment e2 ON e2.id = em.id
                     WHERE i.employee_id = e2.employee_id
                       AND i.status = 'issued'
                       AND it.name = n.item_name) AS issued_now
            FROM app.ppe_norm n
            JOIN app.position p ON p.category_id = n.position_category_id
            JOIN app.employment em ON em.position_id = p.id
            WHERE em.id = %s
              AND n.valid_from <= current_date
              AND (n.valid_to IS NULL OR n.valid_to > current_date)
            ORDER BY n.sort_order, n.item_name
            """,
            (employment_id,),
        )
        return cur.fetchall()


#  Вид нормы → группы «Базы СИЗ» (вычитка 30.09.2026): «летняя» норма ищет
#  летние комплекты, «обувь» — зимнюю и летнюю, «прочее» — каски и перчатки
NORM_KIND_BASE_GROUPS = {
    "summer": ("summer_suit",),
    "winter": ("winter_suit",),
    "footwear": ("winter_shoes", "summer_shoes"),
    "other": ("helmet", "gloves"),
}


def depot_ppe_stock(conn: psycopg.Connection, org_unit_id: int) -> list[dict[str, Any]]:
    """Остатки «Базы СИЗ» депо (выгрузка из 1С УТ): что доступно к выдаче.

    Показывается в окне «Ведомость на выдачу» карточки сотрудника
    (06.10.2026, правка заказчика)."""
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT s.id AS stock_id, i.id AS item_id, i.name, i.base_group,
                   s.size, s.qty
              FROM app.ppe_base_stock s
              JOIN app.ppe_item i ON i.id = s.ppe_item_id
             WHERE s.org_unit_id = %s AND s.qty > 0
             ORDER BY i.name, s.size
            """,
            (org_unit_id,),
        )
        return cur.fetchall()


def ppe_request_add(conn: psycopg.Connection, employee_id: int,
                    ppe_item_id: int | None, item_name: str,
                    size: str | None, note: str | None) -> None:
    """Заявка на недостающий комплект СИЗ — открытая, она задача на доске
    Администрации ООУРЦ (06.10.2026, правка заказчика)."""
    with conn.cursor() as cur:
        cur.execute(
            """
            INSERT INTO app.ppe_request
                   (employee_id, ppe_item_id, item_name, size, note,
                    requested_by)
            VALUES (%s, %s, %s, %s, %s,
                    nullif(current_setting('app.user_id', true), '')::bigint)
            """,
            (employee_id, ppe_item_id, item_name, size or None, note or None),
        )


def ppe_requests_close_for(conn: psycopg.Connection, employee_id: int,
                           ppe_item_id: int) -> int:
    """Заявка гаснет сама, когда комплект выдан сотруднику: совпали
    сотрудник и позиция номенклатуры."""
    with conn.cursor() as cur:
        cur.execute(
            """
            UPDATE app.ppe_request SET closed_at = now()
             WHERE employee_id = %s AND ppe_item_id = %s
               AND closed_at IS NULL
            """,
            (employee_id, ppe_item_id),
        )
        return cur.rowcount


def ppe_base_variants(conn: psycopg.Connection, org_unit_id: int,
                      norms: list[dict[str, Any]]) -> dict[int, list]:
    """
    Какие размеры каждой нормы есть в «Базе СИЗ» депо (п. 5.17, вычитка).

    Вместо поля «размер» при выдаче по норме показывается список имеющихся
    на депо комплектов. Сначала — точное совпадение наименования позиции
    базы с наименованием нормы; если такого нет — вся группа вида
    (летний/зимний комплект, обувь, каски и перчатки).
    """
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT s.id AS stock_id, s.size, s.qty, it.name, it.base_group
            FROM app.ppe_base_stock s
            JOIN app.ppe_item it ON it.id = s.ppe_item_id
            WHERE s.org_unit_id = %s
            ORDER BY it.name, s.size
            """,
            (org_unit_id,),
        )
        stock = cur.fetchall()
    out: dict[int, list] = {}
    for n in norms:
        exact = [r for r in stock if r["name"] == n["item_name"]]
        rows = exact or [r for r in stock
                         if r["base_group"] in NORM_KIND_BASE_GROUPS.get(
                             n["ppe_kind"], ())]
        out[n["id"]] = rows
    return out




def ppe_catalog(conn: psycopg.Connection) -> list[dict[str, Any]]:
    """
    Справочник позиций СИЗ: что уже выдавали хоть раз.

    Нужен подсказкой при выдаче: наименования в файле депо длинные, с кодом
    номенклатуры и размером, и набирать их заново означает получить два
    написания одной куртки.
    """
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT id, code, name, kind, size, classification
            FROM app.ppe_item
            WHERE is_active
            ORDER BY name
            """
        )
        return cur.fetchall()


def _ppe_item_id(
    conn: psycopg.Connection,
    *,
    name: str,
    code: str | None,
    kind: str,
    size: str | None,
    classification: str | None,
) -> int:
    """
    Найти позицию справочника по коду или названию, иначе добавить.

    Код номенклатуры уникален, поэтому опознаём по нему; без кода — по
    названию И размеру (вычитка 30.09.2026, п. 5.20: «позиция ищется только
    по наименованию, без размера — второму сотруднику с другим размером
    запишется размер первого; необходимо доработать алгоритм»). Иначе одна и
    та же куртка 56-58 и 62-62 стали бы одной позицией, и размер первого
    выданного доставался всем следующим.
    """
    name = name.strip()
    size = (size or "").strip() or None
    code = (code or "").strip() or None
    with conn.cursor() as cur:
        if code:
            cur.execute("SELECT id FROM app.ppe_item WHERE code = %s", (code,))
        else:
            cur.execute(
                "SELECT id FROM app.ppe_item "
                " WHERE code IS NULL AND name = %s"
                "   AND coalesce(size, '') = coalesce(%s, '')",
                (name[:200], size),
            )
        found = cur.fetchone()
        if found:
            return found["id"]

        cur.execute(
            """
            INSERT INTO app.ppe_item (code, name, kind, size, classification)
            VALUES (%s, %s, %s, %s, %s) RETURNING id
            """,
            (code, name[:200], kind, size,
             (classification or "").strip() or None),
        )
        return cur.fetchone()["id"]


def add_ppe_issue(
    conn: psycopg.Connection,
    *,
    employee_id: int,
    org_unit_id: int,
    item_id: int | None,
    name: str,
    code: str | None,
    kind: str,
    size: str | None,
    classification: str | None,
    qty: float,
    issued_at: date,
    replace_at: date | None,
    wear_period_months: int | None,
) -> int:
    """
    Записать выдачу СИЗ работнику. Возвращает номер записи о выдаче.

    «Заменить до» либо вводится, либо считается от срока носки по норме.
    Пусто и там и там — средство выдано до износа, и даты замены у него нет:
    подставлять её нельзя, иначе оно сразу окажется просроченным.
    """
    name = name.strip()
    if item_id is None and not name:
        raise ValueError("Выберите позицию из справочника или введите наименование")
    if qty <= 0:
        raise ValueError("Количество должно быть больше нуля")
    if replace_at is not None and replace_at <= issued_at:
        raise ValueError("«Заменить до» должно быть позже даты выдачи")

    with conn.cursor() as cur:
        if item_id:
            cur.execute(
                "SELECT id, name FROM app.ppe_item WHERE id = %s", (item_id,)
            )
            if cur.fetchone() is None:
                raise ValueError("Позиция СИЗ не найдена — обновите страницу")
        else:
            item_id = _ppe_item_id(
                conn, name=name, code=code, kind=kind, size=size,
                classification=classification,
            )

        if replace_at is None and wear_period_months:
            replace_at = _add_months(issued_at, wear_period_months)

        cur.execute(
            """
            INSERT INTO app.ppe_issue
                (employee_id, org_unit_id, ppe_item_id, issued_at, replace_at, qty)
            VALUES (%s, %s, %s, %s, %s, %s)
            RETURNING id
            """,
            (employee_id, org_unit_id, item_id, issued_at, replace_at, qty),
        )
        return cur.fetchone()["id"]


def drop_ppe_issue(conn: psycopg.Connection, issue_id: int, employee_id: int) -> None:
    """
    Убрать запись о выдаче — только пока она не попала ни в одну ведомость.

    Ведомость подписана работником, и строка из неё — документ: удалять её
    нельзя. Ошибку до подписи убрать надо, иначе она останется в «выдано»
    навсегда.
    """
    with conn.cursor() as cur:
        cur.execute(
            "SELECT statement_id, return_statement_id FROM app.ppe_issue "
            " WHERE id = %s AND employee_id = %s",
            (issue_id, employee_id),
        )
        row = cur.fetchone()
        if row is None:
            raise ValueError("Запись о выдаче не найдена — обновите страницу")
        if row["statement_id"] or row["return_statement_id"]:
            raise ValueError(
                "Позиция вошла в ведомость: убрать её нельзя, "
                "оформите сдачу ведомостью на сдачу"
            )
        cur.execute("DELETE FROM app.ppe_issue WHERE id = %s", (issue_id,))


def annul_ppe_issue(conn: psycopg.Connection, issue_id: int, employee_id: int,
                    reason: str, user_id: int | None) -> None:
    """
    Аннулировать выданную позицию («Доработка», п. 3, 24.09.2026).

    Не удаление: строка остаётся в истории с причиной, датой и тем, кто
    аннулировал, но с рук уходит — ни в «на руках», ни в норме её больше нет.
    Если позиция стояла в ведомости, из ведомости она выходит.
    """
    if not (reason or "").strip():
        raise ValueError("Укажите причину аннулирования")
    with conn.cursor() as cur:
        cur.execute("SELECT status, org_unit_id FROM app.ppe_issue "
                    " WHERE id = %s AND employee_id = %s", (issue_id, employee_id))
        row = cur.fetchone()
        if row is None:
            raise ValueError("Запись о выдаче не найдена — обновите страницу")
        if row["status"] != "issued":
            raise ValueError("Аннулировать можно только то, что числится на руках")
        cur.execute(
            """
            UPDATE app.ppe_issue
               SET status = 'annulled', annulled_at = now(), annulled_by = %s,
                   annul_reason = %s, statement_id = NULL
             WHERE id = %s
            """,
            (user_id, reason.strip(), issue_id))
        cur.execute(
            "INSERT INTO app.operation_log (user_id, operation, org_unit_id, status,"
            " rows_affected, message) VALUES (%s, 'ppe_annul', %s, 'ok', 1, %s)",
            (user_id, row["org_unit_id"], f"Выдача СИЗ №{issue_id} аннулирована: {reason.strip()}"))


def annul_ppe_statement(conn: psycopg.Connection, statement_id: int,
                        employee_id: int, reason: str,
                        user_id: int | None) -> int:
    """
    Аннулировать ведомость: её позиции освобождаются и могут войти в новую
    (например, с правильной датой). Сама ведомость остаётся в списке с
    пометкой «аннулирована». Возвращает число освобождённых позиций.
    """
    if not (reason or "").strip():
        raise ValueError("Укажите причину аннулирования")
    with conn.cursor() as cur:
        cur.execute("SELECT org_unit_id, number, annulled_at FROM app.ppe_statement "
                    " WHERE id = %s AND employee_id = %s", (statement_id, employee_id))
        st = cur.fetchone()
        if st is None:
            raise ValueError("Ведомость не найдена — обновите страницу")
        if st["annulled_at"]:
            raise ValueError("Ведомость уже аннулирована")
        cur.execute("UPDATE app.ppe_statement SET annulled_at = now(), annulled_by = %s,"
                    " annul_reason = %s WHERE id = %s",
                    (user_id, reason.strip(), statement_id))
        cur.execute("UPDATE app.ppe_issue SET statement_id = NULL WHERE statement_id = %s",
                    (statement_id,))
        freed = cur.rowcount
        cur.execute(
            "INSERT INTO app.operation_log (user_id, operation, org_unit_id, status,"
            " rows_affected, message) VALUES (%s, 'ppe_statement_annul', %s, 'ok', %s, %s)",
            (user_id, st["org_unit_id"], freed,
             f"Ведомость {st['number']} аннулирована: {reason.strip()}"))
    return freed


def ppe_statements(conn: psycopg.Connection, employee_id: int) -> list[dict[str, Any]]:
    """Ведомости работника: номер, дата, сколько позиций вошло."""
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT st.id, st.number, st.kind, st.statement_date,
                   st.issuer_position, st.issuer_name, st.created_at,
                   st.annulled_at, st.annul_reason,
                   (SELECT full_name FROM app.app_user WHERE id = st.annulled_by)
                       AS annulled_by_name,
                   au.full_name AS created_by_name,
                   (SELECT count(*) FROM app.ppe_issue i
                     WHERE i.statement_id = st.id
                        OR i.return_statement_id = st.id) AS lines
            FROM app.ppe_statement st
            LEFT JOIN app.app_user au ON au.id = st.created_by
            WHERE st.employee_id = %s
            ORDER BY st.statement_date DESC, st.id DESC
            """,
            (employee_id,),
        )
        return cur.fetchall()


def statement_number(personnel_no: str, on_date: date) -> str:
    """
    Номер ведомости: табельный номер и дата составления без точек.

    Так решил заказчик: номер сам говорит, на кого ведомость и когда
    составлена, и второй такой в депо не появится.
    """
    return f"{(personnel_no or '').strip()}-{on_date:%d%m%Y}"


def create_ppe_statement(
    conn: psycopg.Connection,
    *,
    employee_id: int,
    org_unit_id: int,
    issue_ids: list[int],
    statement_date: date,
    kind: str,
    user_id: int | None,
    issuer_employee_id: int | None = None,
) -> dict[str, Any]:
    """
    Собрать ведомость из отмеченных позиций.

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

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

    Номер — из табельного номера и даты составления. Если в этот день на
    работника уже оформляли ведомость, к номеру добавляется «/2», «/3»:
    номер в депо уникален, и без этого вторая ведомость за день не
    сохранилась бы вовсе.
    """
    if kind not in ("issue", "return"):
        raise ValueError("Ведомость бывает на выдачу или на сдачу")
    if not issue_ids:
        raise ValueError("Отметьте, какие позиции включить в ведомость")
    returning = kind == "return"

    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT e.personnel_no, ou.chief_position, ou.chief_name
            FROM app.employee e
            JOIN app.org_unit ou ON ou.id = %s
            WHERE e.id = %s
            """,
            (org_unit_id, employee_id),
        )
        who = cur.fetchone()
        if who is None:
            raise ValueError("Работник не найден — обновите страницу")

        #  06.10.2026 (правка заказчика): «Лицо, выдавшее СИЗ» — тот, кто
        #  выдал физически, выбирается в окне выдачи из трудоустроенных.
        #  Учётная запись, сформировавшая ведомость, по-прежнему в created_by.
        #  Не выбрано — как раньше: начальник подразделения.
        issuer_position, issuer_name = who["chief_position"], who["chief_name"]
        if issuer_employee_id:
            cur.execute(
                f"""
                SELECT {FIO_FULL} AS fio, p.name AS position_name
                  FROM app.employee e
                  LEFT JOIN app.employment em
                         ON em.employee_id = e.id AND em.terminated_at IS NULL
                  LEFT JOIN app.position p ON p.id = em.position_id
                 WHERE e.id = %s
                """,
                (issuer_employee_id,),
            )
            man = cur.fetchone()
            if man is None:
                raise ValueError("Сотрудник, выдавший СИЗ, не найден")
            issuer_name = man["fio"]
            issuer_position = man["position_name"] or who["chief_position"]

        #  Отмеченные позиции должны принадлежать этому работнику и ещё не
        #  входить в ведомость того же вида: одна выдача — одна подпись,
        #  одна сдача — одна подпись
        cur.execute(
            """
            SELECT id, status, issued_at, statement_id, return_statement_id
              FROM app.ppe_issue
             WHERE id = ANY(%s) AND employee_id = %s
            """,
            (issue_ids, employee_id),
        )
        rows = cur.fetchall()
        if len(rows) != len(set(issue_ids)):
            raise ValueError("Часть отмеченных позиций уже изменилась — обновите страницу")

        key = "return_statement_id" if returning else "statement_id"
        busy = [r["id"] for r in rows if r[key]]
        if busy:
            what = "ведомость на сдачу" if returning else "ведомость на выдачу"
            raise ValueError(
                f"Позиций, уже вошедших в {what}: {len(busy)}. "
                "Снимите с них отметку"
            )
        if not returning:
            #  «Доработка», п. 3 (24.09.2026): одна ведомость — один день
            #  выдачи, и дата ведомости совпадает с ним. Раньше позиции от
            #  23.09 и 24.09 попадали в одну ведомость, и в бланке всё стояло
            #  датой составления
            days = sorted({r["issued_at"] for r in rows})
            if len(days) > 1:
                raise ValueError(
                    "Выбраны позиции с разными днями выдачи ("
                    + ", ".join(d.strftime("%d.%m.%Y") for d in days)
                    + "). В одну ведомость — только позиции одного дня")
            if days and days[0] != statement_date:
                raise ValueError(
                    f"Дата составления ведомости должна совпадать с днём "
                    f"выдачи: {days[0]:%d.%m.%Y}")
            if any(r["status"] != "issued" for r in rows):
                raise ValueError("В ведомость на выдачу — только то, что на руках")
        if returning:
            gone = [r["id"] for r in rows if r["status"] != "issued"]
            if gone:
                raise ValueError(
                    f"Позиций, которых уже нет на руках: {len(gone)}. "
                    "Сдать можно только выданное"
                )
            late = [r for r in rows if statement_date < r["issued_at"]]
            if late:
                raise ValueError(
                    "Дата составления раньше даты выдачи: сдать средство "
                    "до того, как его выдали, нельзя"
                )

        base = statement_number(who["personnel_no"], statement_date)
        number = base
        for n in range(2, 100):
            cur.execute(
                "SELECT 1 FROM app.ppe_statement "
                " WHERE org_unit_id = %s AND number = %s",
                (org_unit_id, number),
            )
            if cur.fetchone() is None:
                break
            number = f"{base}/{n}"

        cur.execute(
            """
            INSERT INTO app.ppe_statement
                (number, org_unit_id, employee_id, kind, statement_date,
                 issuer_position, issuer_name, created_by, issuer_employee_id)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
            RETURNING id, number
            """,
            (number, org_unit_id, employee_id, kind, statement_date,
             issuer_position, issuer_name, user_id, issuer_employee_id),
        )
        made = cur.fetchone()

        if returning:
            #  Сдача закрывает строку: средства у работника больше нет,
            #  и в норме оно снова показывается как не выданное
            cur.execute(
                """
                UPDATE app.ppe_issue
                   SET return_statement_id = %s,
                       returned_at = %s,
                       status = 'returned'
                 WHERE id = ANY(%s)
                """,
                (made["id"], statement_date, issue_ids),
            )
        else:
            cur.execute(
                "UPDATE app.ppe_issue SET statement_id = %s WHERE id = ANY(%s)",
                (made["id"], issue_ids),
            )
        return made


def mark_returned(
    conn: psycopg.Connection, *, employee_id: int, issue_ids: list[int],
    returned_at: date, user_id: int | None = None,
) -> int:
    """
    Провести сдачу СИЗ — без отдельной ведомости.

    Заказчик 22.09.2026: «переименовать кнопку "ведомость на сдачу" в кнопку
    "Провести сдачу", после нажатия саму ведомость формировать не нужно.
    Система должна понять и отметить, что сотрудник это сдал, сам бланк
    печатается 1 раз, в нём уже есть столбец с отметкой даты сдачи».

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

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

    Возвращает, сколько позиций проведено.
    """
    if not issue_ids:
        raise ValueError("Отметьте, что именно сдаёт работник")

    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT id, status, issued_at
              FROM app.ppe_issue
             WHERE id = ANY(%s) AND employee_id = %s
            """,
            (issue_ids, employee_id),
        )
        rows = cur.fetchall()
        if len(rows) != len(set(issue_ids)):
            raise ValueError(
                "Часть отмеченных позиций уже изменилась — обновите страницу")

        gone = [r["id"] for r in rows if r["status"] != "issued"]
        if gone:
            raise ValueError(
                f"Позиций, которых уже нет на руках: {len(gone)}. "
                "Сдать можно только выданное")

        early = [r for r in rows if returned_at < r["issued_at"]]
        if early:
            raise ValueError(
                "Дата сдачи раньше даты выдачи: сдать средство до того, как "
                "его выдали, нельзя")

        cur.execute(
            """
            UPDATE app.ppe_issue
               SET returned_at = %s, status = 'returned',
                   wear_percent = app.ppe_wear_of(id, %s)
             WHERE id = ANY(%s)
            """,
            (returned_at, returned_at, issue_ids),
        )
        done = cur.rowcount
        #  Сданный экземпляр уходит в «Базу СИЗ» депо с зафиксированным
        #  износом (вычитка 30.09.2026, п. 5.17): «сдал с 50% износа —
        #  комплект должен отображаться в списке с % износа». Позиция базы
        #  ищется по наименованию; не нашлась — строка ссылается на позицию
        #  самой выдачи и показывается в группе «без группы»
        cur.execute(
            """
            INSERT INTO app.ppe_base_worn
                (org_unit_id, ppe_item_id, size, issue_id, employee_id,
                 worn_pct, returned_at)
            SELECT i.org_unit_id,
                   coalesce((SELECT b.id FROM app.ppe_item b
                              WHERE b.name = it.name AND b.base_group IS NOT NULL
                              ORDER BY b.is_active DESC, b.id LIMIT 1), it.id),
                   coalesce(it.size, ''), i.id, i.employee_id,
                   i.wear_percent, i.returned_at
              FROM app.ppe_issue i
              JOIN app.ppe_item it ON it.id = i.ppe_item_id
             WHERE i.id = ANY(%s) AND i.status = 'returned'
            """,
            (issue_ids,),
        )
    conn.commit()
    return done


def statement_for_print(
    conn: psycopg.Connection, statement_id: int
) -> dict[str, Any] | None:
    """Всё, что нужно печатной форме: шапка, работник и строки."""
    with conn.cursor() as cur:
        cur.execute(
            f"""
            SELECT st.id, st.number, st.kind, st.statement_date,
                   st.issuer_position, st.issuer_name,
                   ou.full_name AS unit_full_name, ou.name AS unit_name,
                   ou.code AS unit_code,
                   e.personnel_no, {FIO_FULL} AS fio_full,
                   p.name AS position_name
            FROM app.ppe_statement st
            JOIN app.org_unit ou ON ou.id = st.org_unit_id
            JOIN app.employee e  ON e.id = st.employee_id
            LEFT JOIN app.employment em
                   ON em.employee_id = e.id AND em.terminated_at IS NULL
            LEFT JOIN app.position p ON p.id = em.position_id
            WHERE st.id = %s
            """,
            (statement_id,),
        )
        head = cur.fetchone()
        if head is None:
            return None

        #  Строки берутся по своей ссылке: у ведомости на выдачу — те, что
        #  ею выданы, у ведомости на сдачу — те, что ею сданы. Один и тот же
        #  комплект попадает в две ведомости, и путать их нельзя
        link = ("i.return_statement_id" if head["kind"] == "return"
                else "i.statement_id")
        cur.execute(
            f"""
            SELECT it.code, it.name, it.size, it.classification,
                   i.qty, i.issued_at, i.replace_at, i.returned_at
            FROM app.ppe_issue i
            JOIN app.ppe_item it ON it.id = i.ppe_item_id
            WHERE {link} = %s
            ORDER BY i.issued_at, it.name
            """,
            (statement_id,),
        )
        head["rows"] = cur.fetchall()
        return head


def update_contacts(conn: psycopg.Connection, **kw) -> None:
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT app.update_employee_contacts(
                %(employee_id)s, %(phone)s, %(work_email)s, %(birth_date)s,
                %(tsd_login)s, %(tsd_password)s,
                %(phone_depot)s, %(phone_personal)s, %(reserve_internet)s
            )
            """,
            kw,
        )
    conn.commit()


def create_depot_account(conn: psycopg.Connection, employment_id: int,
                         login: str) -> int:
    """
    02.10.2026 (правка заказчика, вопрос 2.3): учётку новому сотруднику
    заводит начальник/заместитель — она лежит отключённой, подтверждение
    трудоустройства её включает. Проверки и заводка — в app.create_depot_account
    (миграция 104): у депо нет прав на app_user, RLS их прячет.
    """
    with conn.cursor() as cur:
        cur.execute("SELECT app.create_depot_account(%s, %s) AS uid",
                    (employment_id, login))
        uid = cur.fetchone()["uid"]
    conn.commit()
    return uid


def depot_account_of(conn: psycopg.Connection, employment_id: int):
    """Состояние учётки сотрудника для карточки депо (или None)."""
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM app.depot_account_of(%s)", (employment_id,))
        return cur.fetchone()


def update_tsd(conn: psycopg.Connection, *, employee_id: int,
               tsd_login: str, tsd_password: str | None) -> None:
    """
    30.09.2026 (вычитка 2.25): только учётка ТСД — её правят и начальник
    с заместителем (tsd.write), личные данные им менять по-прежнему нельзя.
    """
    with conn.cursor() as cur:
        cur.execute("SELECT app.update_employee_tsd(%s, %s, %s)",
                    (employee_id, tsd_login, tsd_password))
    conn.commit()


# ---------------------------------------------------------------------------
#  Учётная запись приложения — уточнение заказчика от 15.09.2026
#
#  «В карточке сотрудника присваивается логин и пароль от учётной записи, а
#   также фиксируется роль по приложению; если есть необходимость забрать или
#   изменить роль, отозвать доступ — это возможно выполнить через карточку».
#
#  Вся охрана — в функциях базы (`sql/43_app_access_from_card.sql`): право
#  `user.manage`, запрет выдавать доступ уволенному, запрет отозвать доступ
#  себе. Здесь только вызовы: правило, проверяемое в приложении, обходится
#  вторым обработчиком, а правило в базе — ничем.
# ---------------------------------------------------------------------------

# ---------------------------------------------------------------------------
#  Закреплённые за сотрудником единицы ТМЦ и техники
#
#  Подтверждение заказчика от 16.09.2026: «возвращаем СИЗ. А ТСД, если оно
#  было закреплено в карточке сотрудника, как его ТМЦ». Закрепления не было
#  вовсе — техника числилась за депо, и «вернуть» при увольнении было нечего.
# ---------------------------------------------------------------------------

# ---------------------------------------------------------------------------
#  Кандидаты — вопрос 8 второго круга (16.09.2026)
#
#  «Необходим минимальный блок работы, для предварительного внесения
#   информации в депо, по потенциальным кандидатам… Необходимо добавить
#   вкладку Кандидаты, в сотрудниках».
#
#  Ведёт депо, чужих кандидатов не видит — за этим следит политика таблицы.
# ---------------------------------------------------------------------------

#  Четыре этапа в том порядке, в котором их проходят
CANDIDATE_STAGES = [
    ("interview", "Собеседование"),
    ("internship", "Стажировка"),
    ("medical", "Медосмотр"),
    ("documents", "Документы для ТУ"),
]
CANDIDATE_STATUS_RU = {"active": "В подборе", "hired": "Трудоустроен",
                       "archived": "В архиве"}


def candidates(conn: psycopg.Connection, unit_id: int | None,
               status: str = "active") -> list[dict[str, Any]]:
    """
    Кандидаты депо вместе с их этапами.

    Этапы приходят одним запросом и раскладываются здесь: четыре отдельных
    обращения на каждого кандидата превратили бы список из десяти строк в
    сорок запросов.
    """
    where = ["c.status = %(status)s"]
    args: dict[str, Any] = {"status": status}
    if unit_id:
        where.append("c.org_unit_id = %(unit)s")
        args["unit"] = unit_id

    with conn.cursor() as cur:
        cur.execute(
            f"""
            SELECT c.*, p.name AS position_name, o.name AS unit_name,
                   u.full_name AS created_by_name,
                   --  Карточка ТУ, заполненная депо: состояние проверки видно
                   --  прямо в подборе, потому что кандидат из подбора никуда
                   --  не уходит, пока администрация её не подтвердит
                   em.return_reason, em.returned_at, em.hired_at,
                   em.confirmed_at, ep.personnel_no, ep.birth_date,
                   ep.work_email, ep.phone AS emp_phone,
                   --  ФИО берём из карточки приёма, а не из подбора: депо
                   --  могло поправить опечатку по замечанию администрации, и
                   --  форма доработки обязана показывать последнее, а не то,
                   --  что записали на собеседовании
                   ep.last_name   AS emp_last_name,
                   ep.first_name  AS emp_first_name,
                   ep.middle_name AS emp_middle_name,
                   hp.code AS hire_position_code
            FROM app.candidate c
            LEFT JOIN app.position p  ON p.id = c.position_id
            JOIN app.org_unit o       ON o.id = c.org_unit_id
            LEFT JOIN app.app_user u  ON u.id = c.created_by
            LEFT JOIN app.employment em ON em.id = c.employment_id
            LEFT JOIN app.employee ep   ON ep.id = em.employee_id
            LEFT JOIN app.position hp   ON hp.id = em.position_id
            WHERE {' AND '.join(where)}
            ORDER BY c.request_date NULLS LAST, c.last_name, c.first_name
            """,
            args,
        )
        rows = cur.fetchall()
        if not rows:
            return []

        cur.execute(
            """
            SELECT s.*, u.full_name AS set_by_name
            FROM app.candidate_stage s
            LEFT JOIN app.app_user u ON u.id = s.set_by
            WHERE s.candidate_id = ANY(%s)
            """,
            ([r["id"] for r in rows],),
        )
        by_id: dict[int, dict[str, Any]] = {}
        for st in cur.fetchall():
            by_id.setdefault(st["candidate_id"], {})[st["stage"]] = st

    for r in rows:
        r["stages"] = by_id.get(r["id"], {})
        #  Кандидат готов к приёму, когда все четыре этапа пройдены
        r["ready"] = all(
            (r["stages"].get(code) or {}).get("passed") is True
            for code, _ in CANDIDATE_STAGES
        )
    return rows


def candidate_counts(conn: psycopg.Connection,
                     unit_id: int | None) -> dict[str, int]:
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT status, count(*) AS n FROM app.candidate
            WHERE (%s::bigint IS NULL OR org_unit_id = %s)
            GROUP BY status
            """,
            (unit_id, unit_id),
        )
        got = {r["status"]: int(r["n"]) for r in cur.fetchall()}
    return {k: got.get(k, 0) for k in ("active", "review", "hired", "archived")}


def add_candidate(conn: psycopg.Connection, **kw) -> int:
    with conn.cursor() as cur:
        cur.execute(
            """
            INSERT INTO app.candidate
                (org_unit_id, position_id, last_name, first_name, middle_name,
                 phone, request_no, request_date, note, created_by)
            VALUES (%(unit)s, %(position)s, %(last)s, %(first)s, %(middle)s,
                    %(phone)s, %(req_no)s, %(req_date)s, %(note)s,
                    app.current_user_id())
            RETURNING id
            """,
            kw,
        )
        new_id = cur.fetchone()["id"]
    conn.commit()
    return new_id


def set_candidate_stage(conn: psycopg.Connection, candidate_id: int,
                        stage: str, on_date, passed: bool | None,
                        note: str | None) -> None:
    """
    Отметка по этапу. Непройденный этап обязан быть пояснён — это требование
    заказчика («комментарий при непрохождении») и проверка самой таблицы.
    """
    if stage not in dict(CANDIDATE_STAGES):
        raise ValueError(f"Неизвестный этап: {stage}")
    #  Отказ проговаривается словами, а не нарушением ограничения таблицы
    #  (22.09.2026). Проверка на таблице остаётся: она держит правило и для
    #  запроса, отправленного мимо формы
    if passed is False and not (note or "").strip():
        raise ValueError(
            "Напишите, почему кандидат не прошёл этап: «не прошёл» без "
            "объяснения не отвечает на вопрос, почему")
    with conn.cursor() as cur:
        cur.execute(
            """
            INSERT INTO app.candidate_stage
                (candidate_id, stage, on_date, passed, note, set_by)
            VALUES (%s, %s, %s, %s, %s, app.current_user_id())
            ON CONFLICT (candidate_id, stage) DO UPDATE
               SET on_date = EXCLUDED.on_date,
                   passed = EXCLUDED.passed,
                   note = EXCLUDED.note,
                   set_by = EXCLUDED.set_by,
                   set_at = now()
            """,
            (candidate_id, stage, on_date, passed, (note or "").strip() or None),
        )
    conn.commit()


def archive_candidate(conn: psycopg.Connection, candidate_id: int,
                      note: str) -> None:
    with conn.cursor() as cur:
        cur.execute("SELECT app.candidate_archive(%s, %s)",
                    (candidate_id, note))
    conn.commit()


def candidate(conn: psycopg.Connection, candidate_id: int) -> dict[str, Any] | None:
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT c.*, p.code AS position_code, o.code AS unit_code
            FROM app.candidate c
            LEFT JOIN app.position p ON p.id = c.position_id
            JOIN app.org_unit o      ON o.id = c.org_unit_id
            WHERE c.id = %s
            """,
            (candidate_id,),
        )
        return cur.fetchone()


def position_active(conn: psycopg.Connection, position_code: str) -> bool:
    with conn.cursor() as cur:
        cur.execute("SELECT is_active FROM app.position WHERE code = %s",
                    (position_code,))
        row = cur.fetchone()
    return bool(row and row["is_active"])


def candidate_hired(conn: psycopg.Connection, candidate_id: int,
                    employment_id: int, commit: bool = True) -> None:
    with conn.cursor() as cur:
        cur.execute("SELECT app.candidate_hired(%s, %s)",
                    (candidate_id, employment_id))
    if commit:
        conn.commit()


def assigned_assets(conn: psycopg.Connection,
                    employee_id: int) -> list[dict[str, Any]]:
    """Что за человеком числится и что он уже сдал."""
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT aa.id, aa.issued_at, aa.returned_at, aa.note,
                   a.inventory_no, a.name, a.model, a.id AS asset_id,
                   c.name AS category_name,
                   ui.full_name AS issued_by_name,
                   ur.full_name AS returned_by_name
            FROM app.asset_assignment aa
            JOIN app.asset a          ON a.id = aa.asset_id
            LEFT JOIN app.asset_category c ON c.id = a.category_id
            LEFT JOIN app.app_user ui ON ui.id = aa.issued_by
            LEFT JOIN app.app_user ur ON ur.id = aa.returned_by
            WHERE aa.employee_id = %s
            ORDER BY aa.returned_at NULLS FIRST, aa.issued_at DESC
            """,
            (employee_id,),
        )
        return cur.fetchall()


def assignable_assets(conn: psycopg.Connection,
                      unit_id: int) -> list[dict[str, Any]]:
    """
    Что можно закрепить: единицы этого депо, свободные сейчас.

    Занятая единица в списке не показывается — иначе её «закрепят» за вторым
    человеком, и оба будут уверены, что она у них.
    """
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT a.id, a.inventory_no, a.name, a.model
            FROM app.asset a
            WHERE a.org_unit_id = %s
              AND a.status = 'ok'
              AND NOT EXISTS (SELECT 1 FROM app.asset_assignment aa
                               WHERE aa.asset_id = a.id
                                 AND aa.returned_at IS NULL)
            ORDER BY a.name, a.inventory_no
            """,
            (unit_id,),
        )
        return cur.fetchall()


def assign_asset(conn: psycopg.Connection, asset_id: int, employee_id: int,
                 note: str | None) -> None:
    with conn.cursor() as cur:
        cur.execute(
            """
            INSERT INTO app.asset_assignment (asset_id, employee_id, note,
                                              issued_by)
            VALUES (%s, %s, %s, app.current_user_id())
            """,
            (asset_id, employee_id, note or None),
        )
    conn.commit()


def return_asset(conn: psycopg.Connection, assignment_id: int) -> None:
    with conn.cursor() as cur:
        cur.execute(
            """
            UPDATE app.asset_assignment
               SET returned_at = current_date,
                   returned_by = app.current_user_id()
             WHERE id = %s AND returned_at IS NULL
            """,
            (assignment_id,),
        )
        if cur.rowcount == 0:
            raise ValueError("Эта единица уже принята обратно")
    conn.commit()


def card_gaps(conn: psycopg.Connection, employee_id: int) -> list[dict[str, Any]]:
    """
    Чего не хватает в карточке. Считает база — тот же список, что на доске.

    Два списка «чего не хватает» неминуемо разошлись бы, и спорить с человеком
    пришлось бы о том, какой из них прав.
    """
    with conn.cursor() as cur:
        cur.execute("SELECT kind, title FROM app.card_gaps(%s)", (employee_id,))
        return cur.fetchall()


def provisions(conn: psycopg.Connection, employee_id: int) -> dict[str, Any]:
    """Отметки «предусмотрено / не предусмотрено» по видам."""
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT cp.kind, cp.provided, cp.note, cp.set_at,
                   u.full_name AS set_by_name
            FROM app.card_provision cp
            LEFT JOIN app.app_user u ON u.id = cp.set_by
            WHERE cp.employee_id = %s
            """,
            (employee_id,),
        )
        return {r["kind"]: r for r in cur.fetchall()}


def provision_allowed(conn: psycopg.Connection, category_code: str,
                      kind: str) -> bool:
    with conn.cursor() as cur:
        cur.execute("SELECT app.provision_allowed(%s, %s) AS ok",
                    (category_code, kind))
        return bool(cur.fetchone()["ok"])


def set_provision(conn: psycopg.Connection, employee_id: int, kind: str,
                  provided: bool, note: str | None) -> None:
    """
    Поставить отметку. Запись идёт напрямую: политика таблицы уже требует
    права `card.write` и доступа к депо этого человека.
    """
    with conn.cursor() as cur:
        cur.execute(
            """
            INSERT INTO app.card_provision (employee_id, kind, provided, note,
                                            set_by)
            VALUES (%s, %s, %s, %s, app.current_user_id())
            ON CONFLICT (employee_id, kind) DO UPDATE
               SET provided = EXCLUDED.provided,
                   note = EXCLUDED.note,
                   set_by = EXCLUDED.set_by,
                   set_at = now()
            """,
            (employee_id, kind, provided, note or None),
        )
    conn.commit()


def card_history(conn: psycopg.Connection, employee_id: int,
                 limit: int = 300) -> list[dict[str, Any]]:
    """
    История карточки: кто, когда и что изменил — по всем её таблицам.

    Собирается из общего журнала изменений по признаку «эта запись про этого
    человека». Отдельной таблицы истории нет намеренно: она была бы вторым
    описанием тех же событий и однажды разошлась бы с первым.
    """
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT a.at, a.action, a.entity, a.entity_id,
                   a.before_data, a.after_data,
                   u.full_name AS who
            FROM app.audit_log a
            LEFT JOIN app.app_user u ON u.id = a.user_id
            WHERE (a.after_data ->> 'employee_id')::bigint = %(e)s
               OR (a.before_data ->> 'employee_id')::bigint = %(e)s
               OR (a.entity = 'employee' AND a.entity_id = %(e)s::text)
            ORDER BY a.at DESC, a.id DESC
            LIMIT %(n)s
            """,
            {"e": employee_id, "n": limit},
        )
        return cur.fetchall()


#  Справочники, на которые ссылается журнал. Ключ — имя столбца, значение —
#  откуда брать название. `type_id` зависит от сущности: у инструктажа свой
#  справочник видов, у медосмотра свой.
HISTORY_REF_TABLE = {
    "position_id": "app.position",
    "org_unit_id": "app.org_unit",
    "ppe_item_id": "app.ppe_item",
    "role_id": "app.role",
    "category_id": "app.position_category",
}
HISTORY_TYPE_TABLE = {
    "safety_briefing": "app.briefing_type",
    "employee_medical_exam": "app.medical_exam_type",
    "employee_certificate": "app.certificate_type",
    "maintenance": "app.maintenance_type",
}


def history_names(conn: psycopg.Connection,
                  rows: list[dict[str, Any]]) -> dict[str, str]:
    """
    Названия справочников, на которые ссылается журнал изменений.

    В журнале лежат номера: `type_id: 1`, `position_id: 4`. Читать такую
    строку человеку нечем — по разбору 21.09.2026 (КР-12) история должна
    говорить «Свидетельство — водитель погрузчика», а не «type_id: 1».

    Собираем одним запросом на справочник, а не по строке: строк в истории
    сотни, справочников — пять.
    """
    need: dict[str, set[int]] = {}
    for r in rows:
        for data in (r.get("before_data"), r.get("after_data")):
            if not data:
                continue
            for col, val in data.items():
                if val is None:
                    continue
                table = HISTORY_REF_TABLE.get(col)
                if col == "type_id":
                    table = HISTORY_TYPE_TABLE.get(r.get("entity"))
                if not table:
                    continue
                try:
                    need.setdefault(table, set()).add(int(val))
                except (TypeError, ValueError):
                    continue

    out: dict[str, str] = {}
    with conn.cursor() as cur:
        for table, ids in need.items():
            #  Имя таблицы подставляется из своего же списка выше, а не из
            #  запроса: в строку запроса попадает только проверенное значение
            cur.execute(
                f"SELECT id, name FROM {table} WHERE id = ANY(%s)",
                (list(ids),),
            )
            for r in cur.fetchall():
                out[f"{table}:{r['id']}"] = r["name"]
    return out


def app_account(conn: psycopg.Connection, employee_id: int) -> dict[str, Any] | None:
    """
    Учётная запись сотрудника вместе с действующей ролью.

    Пусто и когда учётки нет, и когда её не видно по правам: политика на
    `app_user` показывает чужие записи только с правом `user.manage`. Для
    экрана разницы нет — распорядиться ей всё равно нельзя.
    """
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT u.id, u.email, u.full_name, u.is_active,
                   u.must_change_pwd, u.last_login_at, u.created_at,
                   u.locked_until,
                   r.code AS role_code, r.name AS role_name,
                   r.description AS role_note,
                   ur.org_unit_id, o.name AS unit_name,
                   ur.granted_at,
                   g.full_name AS granted_by_name
            FROM app.app_user u
            LEFT JOIN app.user_role ur
                   ON ur.user_id = u.id AND ur.revoked_at IS NULL
            LEFT JOIN app.role r     ON r.id = ur.role_id
            LEFT JOIN app.org_unit o ON o.id = ur.org_unit_id
            LEFT JOIN app.app_user g ON g.id = ur.granted_by
            WHERE u.employee_id = %s
            ORDER BY ur.granted_at DESC NULLS LAST
            LIMIT 1
            """,
            (employee_id,),
        )
        return cur.fetchone()


def app_account_by_user(conn: psycopg.Connection,
                        user_id: int) -> dict[str, Any] | None:
    """Логин и ФИО по учётной записи — чтобы знать, кому и куда слать письмо."""
    with conn.cursor() as cur:
        cur.execute(
            "SELECT id, email, full_name, employee_id, is_active "
            "FROM app.app_user WHERE id = %s",
            (user_id,),
        )
        return cur.fetchone()


def role_history(conn: psycopg.Connection, user_id: int) -> list[dict[str, Any]]:
    """
    Что у человека было до нынешней роли.

    Роли не удаляются, а закрываются датой — иначе на вопрос «кто и когда дал
    ему доступ к расчёту» ответа бы не было.
    """
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT r.name AS role_name, o.name AS unit_name,
                   ur.granted_at, ur.revoked_at,
                   g.full_name AS granted_by_name
            FROM app.user_role ur
            JOIN app.role r          ON r.id = ur.role_id
            LEFT JOIN app.org_unit o ON o.id = ur.org_unit_id
            LEFT JOIN app.app_user g ON g.id = ur.granted_by
            WHERE ur.user_id = %s
            ORDER BY ur.granted_at DESC, ur.id DESC
            """,
            (user_id,),
        )
        return cur.fetchall()


def roles_for_grant(conn: psycopg.Connection) -> list[dict[str, Any]]:
    """
    Роли для выбора в карточке.

    Роль администратора в списке есть только у администратора — решение
    заказчика от 15.09.2026: «роль администратора раздаёт только
    администратор, Администрация ООУРЦ — все, кроме роли Администратор».

    Список тут не охрана, а удобство: запрет держит база
    (`app.check_can_grant_role`). Но показывать то, что всё равно откажут, —
    значит обещать несделанное.

    Признак `unit_scoped` уезжает в форму: у ролей депо город обязателен, и
    форма должна убрать «все депо» из выбора ещё до отправки, а не показывать
    отказ базы после неё (требование от 22.09.2026).
    """
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT r.code, r.name, r.description, r.unit_scoped,
                   (SELECT count(*) FROM app.role_permission rp
                     WHERE rp.role_id = r.id) AS perms
            FROM app.role r
            WHERE (r.code <> 'admin' OR app.is_admin())
              --  Роль, место которой ещё не открыто (личный кабинет), в
              --  выборе не показываем: выдать её база всё равно не даст
              AND r.is_grantable
            ORDER BY r.id
            """
        )
        return cur.fetchall()


def is_admin(conn: psycopg.Connection) -> bool:
    with conn.cursor() as cur:
        cur.execute("SELECT app.is_admin() AS ok")
        return bool(cur.fetchone()["ok"])


#  Пароль придумывает приложение, а не человек — решение заказчика от
#  16.09.2026: «логин и пароль создаёт учётка Администрации ООУРЦ… на ящик
#  отправляется письмо».
#
#  Алфавит без похожих знаков: ноль и «O», единица, «l» и «I» в письме
#  различимы, а в разговоре — нет, и пароль передают в том числе голосом.
PASSWORD_ALPHABET = "ABCDEFGHJKLMNPQRSTUVWXYZabcdefghijkmnopqrstuvwxyz23456789"


def new_password(length: int = 12) -> str:
    """Временный пароль. Двенадцать знаков при этом алфавите — 71 бит."""
    import secrets
    return "".join(secrets.choice(PASSWORD_ALPHABET) for _ in range(length))


def open_reset_requests(conn: psycopg.Connection) -> list[dict[str, Any]]:
    """
    Заявки «Забыли пароль?», которые ждут разбора.

    Показываются и те, по которым учётки не нашлось: человек мог ошибиться в
    адресе, и об этом надо знать — молчащая форма входа выглядит как поломка.
    """
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT q.id, q.email, q.created_at, q.user_id,
                   u.full_name, u.is_active,
                   em.id AS employment_id
            FROM app.password_reset_request q
            LEFT JOIN app.app_user u ON u.id = q.user_id
            LEFT JOIN LATERAL (
                SELECT e.id FROM app.employment e
                 WHERE e.employee_id = u.employee_id AND e.terminated_at IS NULL
                 ORDER BY e.id DESC LIMIT 1
            ) em ON true
            WHERE q.status = 'new'
            ORDER BY q.created_at
            """
        )
        return cur.fetchall()


def reset_requests_count(conn: psycopg.Connection) -> int:
    with conn.cursor() as cur:
        cur.execute("SELECT count(*) AS n FROM app.password_reset_request "
                    "WHERE status = 'new'")
        return int(cur.fetchone()["n"])


def close_reset_requests_for(conn: psycopg.Connection, user_id: int) -> int:
    """
    Закрыть заявки этого человека — вызывается после смены пароля.

    Отдельной кнопки «заявка разобрана» нет намеренно: разобрана она ровно
    тогда, когда пароль сменён, и лишняя кнопка означала бы список, который
    расходится с делом.
    """
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT id FROM app.password_reset_request
             WHERE status = 'new' AND user_id = %s
            """,
            (user_id,),
        )
        ids = [r["id"] for r in cur.fetchall()]
        for rid in ids:
            cur.execute("SELECT app.close_password_reset(%s, 'done', %s)",
                        (rid, "Пароль сменён и выслан"))
    return len(ids)


def reject_reset_request(conn: psycopg.Connection, request_id: int,
                         note: str) -> None:
    with conn.cursor() as cur:
        cur.execute("SELECT app.close_password_reset(%s, 'rejected', %s)",
                    (request_id, note))
    conn.commit()


def ask_password_reset(conn: psycopg.Connection, email: str) -> None:
    """Заявка со страницы входа. Пользователя сессии здесь нет."""
    with conn.cursor() as cur:
        cur.execute("SELECT app.ask_password_reset(%s)", (email,))
    conn.commit()


def unconfirmed(conn: psycopg.Connection) -> list[dict[str, Any]]:
    """Трудоустройства, которые ждут проверки администрацией."""
    with conn.cursor() as cur:
        cur.execute(
            f"""
            SELECT em.id AS employment_id, {FIO_FULL} AS fio_full,
                   e.personnel_no, p.name AS position_name,
                   o.name AS unit_name, em.hired_at, em.created_at,
                   au.full_name AS created_by_name,
                   em.return_reason, em.returned_at, em.comment
            FROM app.employment em
            JOIN app.employee e   ON e.id = em.employee_id
            JOIN app.position p   ON p.id = em.position_id
            JOIN app.org_unit o   ON o.id = em.org_unit_id
            LEFT JOIN app.app_user au ON au.id = em.created_by
            WHERE em.confirmed_at IS NULL AND em.terminated_at IS NULL
            ORDER BY em.created_at
            """
        )
        return cur.fetchall()


def headcount_on(conn: psycopg.Connection, unit_code: str,
                 position_code: str, on_date: date) -> dict[str, Any]:
    """
    Численность по должности в депо на дату приёма (02.10.2026, правка
    заказчика, вопрос 2.2): занято на эту дату и план месяца.

    Занято — назначения, пересекающие дату: приняты не позже и не уволены
    раньше неё, поэтому «увольнение 20-го и приём 18-го» превышением не
    считается. План — согласованная численность месяца приёма из
    «Бюджетных данных» (headcount_plan, план_kind 'budget'). Плана на
    месяц нет — проверка не применяется (численность не согласовывали,
    ограничивать нечем); такой приём виден в журнале без пометки.
    """
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT count(*) AS occupied
            FROM app.employment em
            JOIN app.position p ON p.id = em.position_id
            JOIN app.org_unit ou ON ou.id = em.org_unit_id
            JOIN app.position_category pc ON pc.id = p.category_id
            WHERE ou.code = %(unit)s AND pc.code = %(cat)s
              AND em.hired_at <= %(d)s
              AND (em.terminated_at IS NULL OR em.terminated_at >= %(d)s)
            """,
            {"unit": unit_code, "d": on_date,
             "cat": _category_code(conn, position_code)},
        )
        occupied = cur.fetchone()["occupied"]
        cur.execute(
            """
            SELECT hp.headcount
            FROM app.headcount_plan hp
            JOIN app.org_unit ou ON ou.id = hp.org_unit_id
            JOIN app.position_category pc ON pc.id = hp.position_category_id
            WHERE ou.code = %(unit)s AND pc.code = %(cat)s
              AND hp.period_month = date_trunc('month', %(d)s::date)
              AND hp.plan_kind = 'budget'
            """,
            {"unit": unit_code, "d": on_date,
             "cat": _category_code(conn, position_code)},
        )
        row = cur.fetchone()
    return {
        "occupied": occupied,
        "plan": (int(row["headcount"]) if row and row["headcount"]
                 is not None else None),
        "has_plan": bool(row),
    }


def _category_code(conn: psycopg.Connection, position_code: str) -> str:
    with conn.cursor() as cur:
        cur.execute(
            "SELECT pc.code AS category_code FROM app.position p "
            "JOIN app.position_category pc ON pc.id = p.category_id "
            "WHERE p.code = %s", (position_code,))
        row = cur.fetchone()
    if not row:
        raise ValueError(f"Должность «{position_code}» не найдена")
    return row["category_code"]


def headcount_for_employment(conn: psycopg.Connection,
                             employment_id: int) -> dict[str, Any] | None:
    """
    Численность для уже оформленного назначения (02.10.2026): занято на
    дату приёма без него самого и план месяца. `over` — превышение.
    """
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT ou.code AS unit_code, p.code AS position_code, em.hired_at
            FROM app.employment em
            JOIN app.org_unit ou ON ou.id = em.org_unit_id
            JOIN app.position p ON p.id = em.position_id
            WHERE em.id = %s
            """, (employment_id,))
        row = cur.fetchone()
    if not row:
        return None
    hc = headcount_on(conn, row["unit_code"], row["position_code"],
                      row["hired_at"])
    others = max(hc["occupied"] - 1, 0)
    return {"plan": hc["plan"], "occupied": others, "date": row["hired_at"],
            "over": bool(hc["has_plan"] and hc["plan"] is not None
                         and others + 1 > hc["plan"])}


def mark_headcount_over(conn: psycopg.Connection, employment_id: int,
                        note: str, commit: bool = True) -> None:
    """Пометка «приём сверх численности» в комментарии назначения — её
    видит Администрация во вкладке «На проверке»."""
    with conn.cursor() as cur:
        cur.execute(
            "UPDATE app.employment SET comment = %s WHERE id = %s",
            (note, employment_id))
    if commit:
        conn.commit()


def confirm_employment(conn: psycopg.Connection, employment_id: int) -> None:
    with conn.cursor() as cur:
        cur.execute("SELECT app.confirm_employment(%s)", (employment_id,))
    conn.commit()


def cancel_unconfirmed_hire(conn: psycopg.Connection, employment_id: int,
                            reason: str) -> None:
    """Отменить неподтверждённый приём целиком (вычитка 2.25, миграция 92)."""
    with conn.cursor() as cur:
        cur.execute("SELECT app.cancel_unconfirmed_hire(%s, %s)",
                    (employment_id, reason))
    conn.commit()


def update_employee_fio(conn: psycopg.Connection, *, employee_id: int,
                        last_name: str, first_name: str,
                        middle_name: str) -> None:
    """Исправить ФИО (вычитка 2.6, миграция 93)."""
    with conn.cursor() as cur:
        cur.execute("SELECT app.update_employee_fio(%s, %s, %s, %s)",
                    (employee_id, last_name, first_name, middle_name))
    conn.commit()


def return_employment(conn: psycopg.Connection, employment_id: int,
                      reason: str) -> None:
    with conn.cursor() as cur:
        cur.execute("SELECT app.return_employment(%s, %s)",
                    (employment_id, reason))
    conn.commit()


def grant_access(conn: psycopg.Connection, employee_id: int, email: str,
                 password: str, role_code: str,
                 org_unit_id: int | None) -> int:
    with conn.cursor() as cur:
        cur.execute(
            "SELECT app.grant_app_access(%s, %s, %s, %s, %s) AS uid",
            (employee_id, email, password, role_code, org_unit_id),
        )
        uid = cur.fetchone()["uid"]
    conn.commit()
    return uid


def set_role(conn: psycopg.Connection, user_id: int, role_code: str,
             org_unit_id: int | None) -> None:
    with conn.cursor() as cur:
        cur.execute("SELECT app.set_user_role(%s, %s, %s)",
                    (user_id, role_code, org_unit_id))
    conn.commit()


def set_user_email(conn: psycopg.Connection, user_id: int,
                   email: str) -> None:
    """
    Сменить логин.

    Отдельно от «отозвать и завести заново»: заново — значит потерять историю
    ролей человека. Повод завести это был будничный: в логине оказалась
    опечатка в домене, и войти по нему было нельзя.
    """
    with conn.cursor() as cur:
        cur.execute("SELECT app.set_user_email(%s, %s)", (user_id, email))
    conn.commit()


def set_user_password(conn: psycopg.Connection, user_id: int,
                      password: str) -> None:
    with conn.cursor() as cur:
        cur.execute("SELECT app.set_password(%s, %s)", (user_id, password))
    conn.commit()


def revoke_access(conn: psycopg.Connection, user_id: int) -> None:
    with conn.cursor() as cur:
        cur.execute("SELECT app.revoke_app_access(%s)", (user_id,))
    conn.commit()


def restore_access(conn: psycopg.Connection, user_id: int, role_code: str,
                   org_unit_id: int | None) -> None:
    with conn.cursor() as cur:
        cur.execute("SELECT app.restore_app_access(%s, %s, %s)",
                    (user_id, role_code, org_unit_id))
    conn.commit()


def restore_access_with_password(conn: psycopg.Connection, user_id: int,
                                 role_code: str, org_unit_id: int | None,
                                 password: str) -> None:
    """
    30.09.2026 (вычитка 1.24): вернуть доступ и сразу сменить пароль —
    одной транзакцией. Раньше шаги коммитились порознь: если смена пароля
    срывалась, доступ уже был возвращён со старым паролем, а решение
    от 16.09.2026 требует новый пароль всегда.
    """
    with conn.cursor() as cur:
        cur.execute("SELECT app.restore_app_access(%s, %s, %s)",
                    (user_id, role_code, org_unit_id))
        cur.execute("SELECT app.set_password(%s, %s)", (user_id, password))
    conn.commit()



# ---------------------------------------------------------------------------
#  Продление срока носки СИЗ (25.09.2026, «Доработка 2», миграция 87)
#
#  Комплект доносили до конца срока, а он ещё в хорошем состоянии — комиссия
#  осматривает его и продлевает срок актом. Правила:
#    * продлить можно только выданное и со сроком носки;
#    * «продлено до» — позже даты составления и не дальше года от неё;
#    * новый срок позже нынешнего срока замены — иначе это не продление;
#    * срок «Заменить до» переносится на дату продления, прежний хранится в
#      строке акта: отмена акта его вернёт.
# ---------------------------------------------------------------------------

def add_one_year(day: date) -> date:
    try:
        return day.replace(year=day.year + 1)
    except ValueError:              # 29 февраля
        return day.replace(year=day.year + 1, day=28)


def create_extension_act(conn: psycopg.Connection, *, employee_id: int,
                         org_unit_id: int, issue_ids: list[int],
                         act_date: date, extended_to: date,
                         user_id: int | None) -> int:
    if not issue_ids:
        raise ValueError("Отметьте позиции в колонке «На продление»")
    if extended_to <= act_date:
        raise ValueError("Дата продления должна быть позже даты составления акта")
    if extended_to > add_one_year(act_date):
        raise ValueError(
            f"Продлить можно не больше чем на год от даты составления — "
            f"не позже {add_one_year(act_date):%d.%m.%Y}")
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT i.id, i.replace_at, i.status, it.name
              FROM app.ppe_issue i JOIN app.ppe_item it ON it.id = i.ppe_item_id
             WHERE i.id = ANY(%s) AND i.employee_id = %s
            """, (issue_ids, employee_id))
        rows = cur.fetchall()
        if len(rows) != len(set(issue_ids)):
            raise ValueError("Позиция не найдена у этого работника — обновите страницу")
        for r in rows:
            if r["status"] != "issued":
                raise ValueError(f"«{r['name']}» уже не на руках — продлевать нечего")
            if r["replace_at"] is None:
                raise ValueError(f"«{r['name']}» носится до износа — срока для продления нет")
            if extended_to <= r["replace_at"]:
                raise ValueError(
                    f"«{r['name']}»: срок замены и так {r['replace_at']:%d.%m.%Y} — "
                    f"продление должно быть позже него")
        cur.execute(
            """
            INSERT INTO app.ppe_extension_act
                (org_unit_id, employee_id, act_date, extended_to, created_by)
            VALUES (%s, %s, %s, %s, %s) RETURNING id
            """, (org_unit_id, employee_id, act_date, extended_to, user_id))
        act_id = cur.fetchone()["id"]
        for r in rows:
            cur.execute(
                "INSERT INTO app.ppe_extension_line (act_id, issue_id, prev_replace_at) "
                "VALUES (%s, %s, %s)", (act_id, r["id"], r["replace_at"]))
            cur.execute("UPDATE app.ppe_issue SET replace_at = %s WHERE id = %s",
                        (extended_to, r["id"]))
        cur.execute(
            "INSERT INTO app.operation_log (user_id, operation, org_unit_id, status, "
            "rows_affected, message) VALUES (%s, 'ppe_extend', %s, 'ok', %s, %s)",
            (user_id, org_unit_id, len(rows),
             f"Акт осмотра СИЗ от {act_date:%d.%m.%Y}: продлено до {extended_to:%d.%m.%Y} — "
             + ", ".join(r["name"] for r in rows)))
    conn.commit()
    return act_id


def extension_acts(conn: psycopg.Connection, employee_id: int) -> list[dict[str, Any]]:
    """Акты продления работника — журнал в карточке, как ведомости."""
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT a.*, u.full_name AS created_by_name,
                   (SELECT full_name FROM app.app_user WHERE id = a.annulled_by)
                       AS annulled_by_name,
                   (SELECT string_agg(it.name, ', ' ORDER BY it.name)
                      FROM app.ppe_extension_line l
                      JOIN app.ppe_issue i ON i.id = l.issue_id
                      JOIN app.ppe_item it ON it.id = i.ppe_item_id
                     WHERE l.act_id = a.id) AS lines_text,
                   -- НЕ «items»: у dict в Jinja items — метод, шаблон
                   -- напечатал бы «<built-in method items …>»
                   (SELECT count(*) FROM app.ppe_extension_line l
                     WHERE l.act_id = a.id) AS lines
              FROM app.ppe_extension_act a
              LEFT JOIN app.app_user u ON u.id = a.created_by
             WHERE a.employee_id = %s
             ORDER BY a.act_date DESC, a.id DESC
            """, (employee_id,))
        return cur.fetchall()


def extension_act_for_print(conn: psycopg.Connection,
                            act_id: int) -> dict[str, Any] | None:
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT a.*, ou.name AS unit_name,
                   trim(e.last_name || ' ' || e.first_name || ' ' ||
                        coalesce(e.middle_name, '')) AS fio_full,
                   e.personnel_no
              FROM app.ppe_extension_act a
              JOIN app.org_unit ou ON ou.id = a.org_unit_id
              JOIN app.employee e ON e.id = a.employee_id
             WHERE a.id = %s
            """, (act_id,))
        act = cur.fetchone()
        if not act:
            return None
        cur.execute(
            """
            SELECT it.name, i.issued_at
              FROM app.ppe_extension_line l
              JOIN app.ppe_issue i ON i.id = l.issue_id
              JOIN app.ppe_item it ON it.id = i.ppe_item_id
             WHERE l.act_id = %s
             ORDER BY i.issued_at, it.name
            """, (act_id,))
        act["lines"] = cur.fetchall()
    return act


def annul_extension_act(conn: psycopg.Connection, act_id: int, reason: str,
                        user_id: int | None) -> None:
    """
    Аннулировать акт: срок замены позиций возвращается к прежнему — если его
    с тех пор не меняли другим актом.
    """
    reason = (reason or "").strip()
    if not reason:
        raise ValueError("Укажите причину аннулирования")
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM app.ppe_extension_act WHERE id = %s", (act_id,))
        act = cur.fetchone()
        if not act:
            raise ValueError("Акт не найден — обновите страницу")
        if act["annulled_at"]:
            raise ValueError("Акт уже аннулирован")
        cur.execute("SELECT issue_id, prev_replace_at FROM app.ppe_extension_line "
                    " WHERE act_id = %s", (act_id,))
        for l in cur.fetchall():
            cur.execute(
                "UPDATE app.ppe_issue SET replace_at = %s "
                " WHERE id = %s AND replace_at = %s",
                (l["prev_replace_at"], l["issue_id"], act["extended_to"]))
        cur.execute(
            "UPDATE app.ppe_extension_act SET annulled_at = now(), annulled_by = %s, "
            "annul_reason = %s WHERE id = %s", (user_id, reason, act_id))
        cur.execute(
            "INSERT INTO app.operation_log (user_id, operation, org_unit_id, status, "
            "rows_affected, message) VALUES (%s, 'ppe_extend_annul', %s, 'ok', 1, %s)",
            (user_id, act["org_unit_id"],
             f"Акт осмотра СИЗ от {act['act_date']:%d.%m.%Y} аннулирован — {reason}"))
    conn.commit()



def set_gender(conn: psycopg.Connection, employment_id: int, gender: str,
               commit: bool = True) -> None:
    """Пол сотрудника (25.09.2026): «male» или «female». Права проверяет база."""
    with conn.cursor() as cur:
        cur.execute("SELECT app.set_employee_gender(%s, %s)", (employment_id, gender))
    if commit:
        conn.commit()
