"""Запросы, нужные веб-интерфейсу. Вся логика — в rules/totals/compare/cells."""

from __future__ import annotations

import calendar
from datetime import date
from collections.abc import Sequence
from typing import Any

import psycopg
from psycopg.rows import dict_row

from .cells import FactCell, PlanCell
from .rules import PlanHours
from .totals import FactDay


# ---------------------------------------------------------------------------
#  Вход и текущий пользователь
# ---------------------------------------------------------------------------

def authenticate(conn: psycopg.Connection, email: str, password: str) -> int | None:
    with conn.cursor() as cur:
        cur.execute("SELECT app.login(%s, %s) AS uid", (email.strip().lower(), password))
        uid = cur.fetchone()["uid"]
    conn.commit()
    return uid


def set_session_user(conn: psycopg.Connection, user_id: int) -> None:
    with conn.cursor() as cur:
        cur.execute("SELECT set_config('app.user_id', %s, false)", (str(user_id),))


def user_is_active(conn: psycopg.Connection, user_id: int) -> bool:
    """
    Жива ли учётка. Спрашивается на каждом запросе: отключённый пользователь
    не должен дорабатывать свою сессию до конца (23.09.2026).

    30.09.2026 (вычитка 1.24): заодно лениво закрывает учётку, чей день
    увольнения наступил, — при записи будущей даты доступ не гасили, его
    закрывает эта функция в первый запрос дня увольнения. Коммит нужен,
    чтобы закрытие пережило 401: иначе оно откатилось бы вместе с отказом.
    """
    with conn.cursor() as cur:
        cur.execute(
            "SELECT app.expire_terminated_account(%s) AS ok", (user_id,))
        ok = bool(cur.fetchone()["ok"])
    conn.commit()
    return ok


def clear_session_user(conn: psycopg.Connection) -> None:
    """
    Забыть пользователя сессии. Обязательно при возврате соединения в пул:
    от app.user_id зависит RLS, и оставленное значение досталось бы
    следующему запросу — то есть другому человеку.

    Настройка именно сессионная, а не транзакционная: внутри одного запроса
    мы несколько раз коммитим, а транзакционная настройка на коммите пропала
    бы и RLS перестал бы видеть пользователя посреди работы.
    """
    with conn.cursor() as cur:
        cur.execute("SELECT set_config('app.user_id', '', false)")


def current_user(conn: psycopg.Connection, user_id: int) -> dict[str, Any] | None:
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT u.id, u.email, u.full_name,
                   coalesce(array_agg(DISTINCT r.code) FILTER (WHERE r.code IS NOT NULL),
                            '{}') AS roles,
                   --  «Все депо» — только у ролей с доступом к депо. Роль
                   --  личного кабинета выдаётся без депо, но это не «все»
                   coalesce(bool_or(ur.org_unit_id IS NULL AND r.unit_access),
                            false) AS all_units,
                   --  Пункт меню «Финансы» (24.09.2026): раздел только у
                   --  Администрации ООУРЦ, а меню рисуется на каждой странице
                   --  из этих данных. До миграции 86 права нет — и пункта нет
                   app.has_perm('finance.read') AS can_finance
            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
            WHERE u.id = %s AND u.is_active
            GROUP BY u.id, u.email, u.full_name
            """,
            (user_id,),
        )
        row = cur.fetchone()
    if row is not None:
        row["read_only"] = is_read_only(row)
        row["start_page"] = start_page(row)
    return row


#  02.10.2026: учётки «только на просмотр». Наблюдатель и кабинеты служб
#  (ОЗ, ОТ, Руководство) видят данные депо, но ничего в них не меняют и
#  рабочих досок задач не получают — у них своя страница-кабинет.
#  Учётка с ещё одной, рабочей ролью — уже не «только на просмотр».
READ_ONLY_ROLES = frozenset({"viewer", "oz", "ot", "management"})

#  Где учётка начинает работу после входа: рабочим ролям — доска задач,
#  службам — свой кабинет, наблюдателю — «Сотрудники»
_START_BY_ROLE = (("oz", "/purchase"), ("ot", "/safety"),
                  ("management", "/board"), ("viewer", "/staff"))


def is_read_only(user: dict[str, Any] | None) -> bool:
    roles = set((user or {}).get("roles") or [])
    return bool(roles) and roles <= READ_ONLY_ROLES


def start_page(user: dict[str, Any] | None) -> str:
    if not is_read_only(user):
        return "/kanban?span=day"
    roles = set(user.get("roles") or [])
    for code, page in _START_BY_ROLE:
        if code in roles:
            return page
    return "/staff"


def bulk_op(conn: psycopg.Connection) -> None:
    """
    Массовая правка графика в этой транзакции (копирование месяца, очистка,
    отмена приёма): построчный журнал клеток молчит, итог операция пишет
    одной строкой сама. Флаг живёт до конца транзакции.
    """
    with conn.cursor() as cur:
        cur.execute("SELECT set_config('app.bulk_op', '1', true)")


def system_op(conn: psycopg.Connection, name: str) -> None:
    """Пометить транзакцию как служебную операцию (например, запись отпуска)."""
    with conn.cursor() as cur:
        cur.execute("SELECT set_config('app.system_op', %s, true)", (name,))


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


def is_admin(conn: psycopg.Connection) -> bool:
    """
    Действующая роль «Администратор» у текущего пользователя сессии.

    30.09.2026: сведения об отправке паролей в журнале «Сотрудников»
    видит только Администратор — право user.manage есть и у Администрации
    ООУРЦ, поэтому проверяем именно роль.
    """
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT EXISTS (
                SELECT 1
                FROM app.user_role ur
                JOIN app.role r ON r.id = ur.role_id
                WHERE ur.user_id = current_setting('app.user_id')::bigint
                  AND ur.revoked_at IS NULL
                  AND r.code = 'admin') AS ok
            """)
        return bool(cur.fetchone()["ok"])


def accessible_units(conn: psycopg.Connection) -> list[dict[str, Any]]:
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT id, code, name, full_name
            FROM app.org_unit
            WHERE kind = 'rc' AND is_active AND app.has_unit_access(id)
            ORDER BY name
            """
        )
        return cur.fetchall()


def has_table(conn: psycopg.Connection, qualified: str) -> bool:
    """
    Есть ли таблица в базе. Нужно там, где страница обязана открыться и до
    накатывания миграции: LEFT JOIN к несуществующей таблице рвёт всю
    транзакцию, и экран отдаёт 500 вместо того, чтобы показать остальное.
    """
    with conn.cursor() as cur:
        cur.execute("SELECT to_regclass(%s) IS NOT NULL AS ok", (qualified,))
        return bool(cur.fetchone()["ok"])


def units_overview(conn: psycopg.Connection, period: date) -> list[dict[str, Any]]:
    """Состояние месяца по каждому доступному подразделению — для главной."""
    last_day = date(period.year, period.month,
                    calendar.monthrange(period.year, period.month)[1])
    #  Состояние графика появилось миграцией 27. Пока её не накатили, колонку
    #  подставляем черновиком, а join не делаем вовсе
    sched = has_table(conn, "app.schedule_status")
    sched_col = ("coalesce(ss.status, 'draft')" if sched else "'draft'")
    sched_join = ("""LEFT JOIN app.schedule_status ss
                   ON ss.org_unit_id = ou.id AND ss.period_month = %(first)s"""
                  if sched else "")
    with conn.cursor() as cur:
        cur.execute(
            f"""
            SELECT ou.id, ou.code, ou.name,
                   t.id AS timesheet_id,
                   coalesce(t.status, 'none') AS status,
                   (SELECT count(*) FROM app.employment em
                     WHERE em.org_unit_id = ou.id
                       AND em.hired_at <= %(last)s
                       AND (em.terminated_at IS NULL OR em.terminated_at >= %(first)s)
                   ) AS people,
                   (SELECT count(*) FROM app.timesheet_day d
                     WHERE d.timesheet_id = t.id) AS filled_days,
                   (SELECT coalesce(sum(d.hours_day + d.hours_night), 0)
                      FROM app.timesheet_day d WHERE d.timesheet_id = t.id) AS hours,
                   (SELECT count(*) FROM app.schedule_discrepancy sd
                     WHERE sd.org_unit_id = ou.id
                       AND sd.period_month = %(first)s
                       AND sd.resolved_at IS NULL) AS discrepancies,
                   (SELECT count(*) FROM app.schedule_plan sp
                     WHERE sp.org_unit_id = ou.id
                       AND sp.work_date BETWEEN %(first)s AND %(last)s
                       AND sp.shift_start IS NOT NULL) AS planned_shifts,
                   --  Кто и когда сдал — это нужно окну статусов перед
                   --  расчётом ЗП: «сдан» без имени и даты ничего не говорит
                   t.submitted_at,
                   us.full_name AS submitted_by_name,
                   {sched_col} AS schedule_status
            FROM app.org_unit ou
            LEFT JOIN app.timesheet t
                   ON t.org_unit_id = ou.id AND t.period_month = %(first)s
            LEFT JOIN app.app_user us ON us.id = t.submitted_by
            {sched_join}
            WHERE ou.kind = 'rc' AND ou.is_active AND app.has_unit_access(ou.id)
            ORDER BY ou.name
            """,
            {"first": period, "last": last_day},
        )
        return cur.fetchall()


def open_discrepancies(conn: psycopg.Connection, unit_id: int, period: date) -> int:
    """
    Сколько нерешённых расхождений с графиком отложено с прошлой сверки.

    Число берём готовым из schedule_discrepancy: его записывает страница
    сверки. Значит, это состояние на момент последнего захода туда, а не
    пересчёт на сейчас — считать заново на каждой странице графика и табеля
    было бы дорого. Для значка на вкладке этого достаточно: он говорит «туда
    стоит зайти», а точную картину показывает сама сверка.
    """
    try:
        with conn.cursor() as cur:
            cur.execute(
                """
                SELECT count(*) AS n FROM app.schedule_discrepancy
                WHERE org_unit_id = %s AND period_month = %s
                  AND resolved_at IS NULL
                """,
                (unit_id, period.replace(day=1)),
            )
            row = cur.fetchone()
    except psycopg.errors.UndefinedTable:
        conn.rollback()
        return 0
    return int(row["n"]) if row else 0


def open_staff_requests(conn: psycopg.Connection) -> int:
    """
    Сколько кадровых заявок ждёт решения — для отметки на вкладке.

    Если кадровая миграция ещё не накатана, таблицы нет. Из-за этого падал
    весь табель: одна отсутствующая таблица рвала транзакцию, и страница
    отдавала 500. Отметка на вкладке не стоит того, чтобы ронять рабочий
    экран, поэтому здесь и только здесь отсутствие таблицы считается нулём.
    """
    try:
        with conn.cursor() as cur:
            cur.execute(
                "SELECT count(*) AS n FROM app.staff_request WHERE status = 'new'"
            )
            return int(cur.fetchone()["n"])
    except psycopg.errors.UndefinedTable:
        # Транзакция после ошибки не работает — её надо откатить, иначе
        # следующий же запрос этой страницы упадёт «current transaction is aborted»
        conn.rollback()
        return 0


def can_access_unit(conn: psycopg.Connection, unit_id: int) -> bool:
    with conn.cursor() as cur:
        cur.execute("SELECT app.has_unit_access(%s) AS ok", (unit_id,))
        return bool(cur.fetchone()["ok"])


def unit_by_code(conn: psycopg.Connection, code: str) -> dict[str, Any] | None:
    with conn.cursor() as cur:
        cur.execute(
            "SELECT id, code, name, full_name FROM app.org_unit WHERE code = %s", (code,)
        )
        return cur.fetchone()


# ---------------------------------------------------------------------------
#  Месяц и табель
# ---------------------------------------------------------------------------

def timesheet(conn: psycopg.Connection, unit_id: int, period: date) -> dict[str, Any] | None:
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT t.*, us.full_name AS submitted_by_name,
                   ua.full_name AS approved_by_name,
                   ur.full_name AS review_by_name,
                   ub.full_name AS returned_by_name
            FROM app.timesheet t
            LEFT JOIN app.app_user us ON us.id = t.submitted_by
            LEFT JOIN app.app_user ua ON ua.id = t.approved_by
            LEFT JOIN app.app_user ur ON ur.id = t.review_by
            LEFT JOIN app.app_user ub ON ub.id = t.returned_by
            WHERE t.org_unit_id = %s AND t.period_month = %s
            """,
            (unit_id, period),
        )
        return cur.fetchone()


def ensure_timesheet(conn: psycopg.Connection, unit_id: int, period: date) -> dict[str, Any]:
    """
    Табель месяца; если его ещё нет — открыть и разнести отпуска.

    Разнос обязателен из-за пункта 21: отпуск больше не пишется в плановый
    график, а в табель попадает только в существующий документ. График
    отпусков составляют в декабре на год вперёд, табеля будущих месяцев тогда
    ещё нет — без разноса при открытии «О» не появилась бы нигде.
    """
    ts = timesheet(conn, unit_id, period)
    if ts:
        return ts
    #  02.10.2026 (вычитка 3.22): открыть месяц может только тот, кто ведёт
    #  табель. Наблюдатель и службы раньше получали «Недостаточно прав…» при
    #  первом заходе в новый месяц — теперь видят пустой табель как есть
    if not has_perm(conn, "timesheet.write"):
        return {"id": None, "org_unit_id": unit_id, "period_month": period,
                "status": "open", "return_reason": None, "virtual": True}
    with conn.cursor() as cur:
        cur.execute("SELECT app.open_month(%s, %s)", (period, unit_id))
    conn.commit()

    #  Импорт внутри функции: vacationdb сам ходит в табель, и на уровне
    #  модуля это был бы круг
    from . import vacationdb

    placed = vacationdb.place_for_month(conn, unit_id, period)
    if placed["fact"] or placed["holiday"]:
        log_operation(
            conn, "month_open", unit_id, period,
            placed["fact"],
            f"Месяц открыт, разнесено отпусков: {placed['vacations']}, "
            f"дней в табеле: {placed['fact']}",
        )
    return timesheet(conn, unit_id, period)


# ---------------------------------------------------------------------------
#  Состояние планового графика
#
#  График живёт двумя состояниями: «составляется» и «сдан» (выгружен в 1С
#  ЗУП). Сданный правится — новых сотрудников принимают и в середине месяца,
#  и смены им проставлять надо, — но каждая правка после сдачи пишется в
#  журнал операций триггером schedule_plan_journal. Решение заказчика от
#  14.09.2026.
# ---------------------------------------------------------------------------

SCHEDULE_DRAFT: dict[str, Any] = {
    "status": "draft", "submitted_by_name": None, "submitted_at": None,
}


def schedule_status(conn: psycopg.Connection, unit_id: int,
                    period: date) -> dict[str, Any]:
    """
    Состояние графика месяца. Нет записи — значит, график ещё составляется.

    Миграция 27 могла быть не накатана: тогда считаем график черновиком и не
    роняем страницу.
    """
    if not has_table(conn, "app.schedule_status"):
        return dict(SCHEDULE_DRAFT)
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT ss.status, ss.submitted_at, u.full_name AS submitted_by_name
            FROM app.schedule_status ss
            LEFT JOIN app.app_user u ON u.id = ss.submitted_by
            WHERE ss.org_unit_id = %s AND ss.period_month = %s
            """,
            (unit_id, period.replace(day=1)),
        )
        row = cur.fetchone()
    return row or dict(SCHEDULE_DRAFT)


def submit_schedule(conn: psycopg.Connection, unit_id: int, period: date) -> None:
    with conn.cursor() as cur:
        cur.execute("SELECT app.submit_schedule(%s, %s)",
                    (unit_id, period.replace(day=1)))
    conn.commit()


def reopen_schedule(conn: psycopg.Connection, unit_id: int, period: date,
                    reason: str) -> None:
    with conn.cursor() as cur:
        cur.execute("SELECT app.reopen_schedule(%s, %s, %s)",
                    (unit_id, period.replace(day=1), reason))
    conn.commit()


def employment_dates(conn: psycopg.Connection, employment_id: int) -> dict[str, Any]:
    with conn.cursor() as cur:
        cur.execute("SELECT hired_at, terminated_at FROM app.employment "
                    "WHERE id = %s", (employment_id,))
        return cur.fetchone() or {}


def month_norm(conn: psycopg.Connection, period: date) -> float | None:
    with conn.cursor() as cur:
        cur.execute(
            "SELECT norm_hours_40 AS n FROM app.v_month_norm WHERE period_month = %s",
            (period,),
        )
        row = cur.fetchone()
    return float(row["n"]) if row else None


def people(conn: psycopg.Connection, unit_id: int, period: date) -> list[dict[str, Any]]:
    """Все действующие в этом месяце назначения подразделения."""
    last_day = date(period.year, period.month,
                    calendar.monthrange(period.year, period.month)[1])
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT em.id AS employment_id,
                   e.id  AS employee_id,
                   e.personnel_no,
                   e.last_name || ' ' || e.first_name || ' ' ||
                       coalesce(e.middle_name, '')          AS fio_full,
                   e.last_name || ' ' || left(e.first_name, 1) || '.' ||
                       coalesce(left(e.middle_name, 1) || '.', '') AS fio_short,
                   -- В сетке ФИО стоит в две строки: фамилия и имя сверху,
                   -- отчество снизу. Инициалами длинная фамилия всё равно не
                   -- умещалась в столбец и обрезалась многоточием
                   e.last_name || ' ' || e.first_name         AS fio_name,
                   coalesce(e.middle_name, '')                AS middle_name,
                   p.name AS position_name,
                   pc.code AS category,
                   em.hired_at,
                   em.terminated_at
            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.hired_at <= %s
              AND (em.terminated_at IS NULL OR em.terminated_at >= %s)
            ORDER BY pc.sort_order, e.last_name, e.first_name
            """,
            (unit_id, last_day, period),
        )
        return cur.fetchall()


def fact_cells(conn: psycopg.Connection, ts_id: int) -> dict[tuple[int, date], FactCell]:
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT employment_id, work_date, day_code,
                   hours_day, hours_night, hours_weekend, hours_overtime
            FROM app.timesheet_day WHERE timesheet_id = %s
            """,
            (ts_id,),
        )
        rows = cur.fetchall()
    return {
        (r["employment_id"], r["work_date"]): FactCell(
            day_code=r["day_code"],
            hours_day=float(r["hours_day"]),
            hours_night=float(r["hours_night"]),
            hours_weekend=float(r["hours_weekend"]),
            hours_overtime=float(r["hours_overtime"]),
        )
        for r in rows
    }


def fact_days(conn: psycopg.Connection, ts_id: int) -> dict[int, list[FactDay]]:
    out: dict[int, list[FactDay]] = {}
    for (emp_id, day), cell in sorted(fact_cells(conn, ts_id).items(), key=lambda kv: kv[0][1]):
        out.setdefault(emp_id, []).append(
            FactDay(
                work_date=day,
                day_code=cell.day_code,
                hours_day=cell.hours_day,
                hours_night=cell.hours_night,
                hours_weekend=cell.hours_weekend,
                hours_overtime=cell.hours_overtime,
            )
        )
    return out


def plan_cells(
    conn: psycopg.Connection, unit_id: int, period: date
) -> dict[tuple[int, date], PlanCell]:
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT employment_id, work_date, shift_start, shift_end, day_code,
                   shift2_start, shift2_end
            FROM app.schedule_plan
            WHERE org_unit_id = %s
              AND work_date >= %s AND work_date < (%s::date + interval '1 month')
            """,
            (unit_id, period, period),
        )
        rows = cur.fetchall()
    return {
        (r["employment_id"], r["work_date"]): PlanCell(
            day_code=r["day_code"],
            shift_start=r["shift_start"],
            shift_end=r["shift_end"],
            shift2_start=r["shift2_start"],
            shift2_end=r["shift2_end"],
        )
        for r in rows
    }


def employee_of(conn: psycopg.Connection, employment_id: int) -> int | None:
    """
    Чей это назначение. Нужен там, где данные ведутся по человеку, а сетка
    работает с назначением: отпуск остаётся за человеком и при переводе.
    """
    with conn.cursor() as cur:
        cur.execute(
            "SELECT employee_id FROM app.employment WHERE id = %s",
            (employment_id,),
        )
        row = cur.fetchone()
        return row["employee_id"] if row else None


def plan_hours_stale(
    conn: psycopg.Connection, unit_id: int, period: date, version: str
) -> bool:
    """
    Посчитаны ли часы месяца устаревшим правилом.

    Правило обеда менялось, и сохранённые цифры «к отработке» после этого
    неверны: сверка показывала «не оформлено 1 ч» там, где всё в порядке.
    Пересчитывать месяц на каждом открытии страницы дорого, поэтому рядом с
    часами лежит версия правила — и месяц пересчитывается, только если она
    чужая.
    """
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT 1 FROM app.schedule_plan_hours
             WHERE org_unit_id = %s
               AND source_date >= %s
               AND source_date < (%s::date + interval '1 month')
               AND (rule_version IS DISTINCT FROM %s)
             LIMIT 1
            """,
            (unit_id, period, period, version),
        )
        return cur.fetchone() is not None


def plan_hours_map(
    conn: psycopg.Connection, unit_id: int, period: date
) -> dict[int, list[PlanHours]]:
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT employment_id, work_date, hours_day, hours_night,
                   worked_day, worked_night, source_date
            FROM app.schedule_plan_hours
            WHERE org_unit_id = %s
              AND work_date >= %s AND work_date < (%s::date + interval '1 month')
            """,
            (unit_id, period, period),
        )
        rows = cur.fetchall()
    out: dict[int, list[PlanHours]] = {}
    for r in rows:
        out.setdefault(r["employment_id"], []).append(
            PlanHours(
                work_date=r["work_date"],
                hours_day=float(r["hours_day"]),
                hours_night=float(r["hours_night"]),
                source_date=r["source_date"],
                worked_day=(None if r["worked_day"] is None
                            else float(r["worked_day"])),
                worked_night=(None if r["worked_night"] is None
                              else float(r["worked_night"])),
            )
        )
    return out


def day_sheet(
    conn: psycopg.Connection, unit_id: int, ts_id: int, day: date
) -> list[dict[str, Any]]:
    """
    Одна смена целиком: кто числится в депо на эту дату, что стоит в плане
    и что уже проставлено в табеле. Это рабочий экран начальника РЦ.
    """
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT em.id AS employment_id,
                   e.personnel_no,
                   e.last_name || ' ' || e.first_name || ' ' ||
                       coalesce(e.middle_name, '')          AS fio_full,
                   e.last_name || ' ' || left(e.first_name, 1) || '.' ||
                       coalesce(left(e.middle_name, 1) || '.', '') AS fio_short,
                   p.name  AS position_name,
                   pc.code AS category,
                   sp.shift_start, sp.shift_end,
                   sp.shift2_start, sp.shift2_end,
                   sp.day_code AS plan_code,
                   sph.hours_day   AS plan_hours_day,
                   sph.hours_night AS plan_hours_night,
                   td.id           AS fact_id,
                   td.day_code,
                   td.hours_day, td.hours_night, td.hours_weekend,
                   td.hours_overtime,
                   td.note, td.updated_at,
                   uu.full_name AS updated_by_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
            LEFT JOIN app.schedule_plan sp
                   ON sp.employment_id = em.id AND sp.work_date = %(day)s
            --  02.10.2026 (вычитка 3.22): часы плана — к отработке, без
            --  обеда (worked_*), и одной суммой на сутки. Раньше брались
            --  часы с обедом (смена 08–20 давала 12 вместо 11), а хвост
            --  вчерашней ночной и новая смена давали две строки на человека
            LEFT JOIN LATERAL (
                SELECT sum(coalesce(h.worked_day, h.hours_day))     AS hours_day,
                       sum(coalesce(h.worked_night, h.hours_night)) AS hours_night
                  FROM app.schedule_plan_hours h
                 WHERE h.employment_id = em.id AND h.work_date = %(day)s
            ) sph ON true
            LEFT JOIN app.timesheet_day td
                   ON td.employment_id = em.id AND td.work_date = %(day)s
                  AND td.timesheet_id = %(ts)s
            LEFT JOIN app.app_user uu ON uu.id = td.updated_by
            WHERE em.org_unit_id = %(unit)s
              AND em.hired_at <= %(day)s
              AND (em.terminated_at IS NULL OR em.terminated_at >= %(day)s)
            ORDER BY pc.sort_order, e.last_name, e.first_name
            """,
            {"unit": unit_id, "ts": ts_id, "day": day},
        )
        rows = cur.fetchall()

    out: list[dict[str, Any]] = []
    for r in rows:
        plan = PlanCell(
            day_code=r["plan_code"],
            shift_start=r["shift_start"],
            shift_end=r["shift_end"],
            shift2_start=r["shift2_start"],
            shift2_end=r["shift2_end"],
        )
        fact = None
        if r["fact_id"] is not None:
            fact = FactCell(
                day_code=r["day_code"],
                hours_day=float(r["hours_day"]),
                hours_night=float(r["hours_night"]),
                hours_weekend=float(r["hours_weekend"]),
                hours_overtime=float(r["hours_overtime"]),
            )
        out.append({
            "employment_id": r["employment_id"],
            "personnel_no": r["personnel_no"],
            "fio_full": (r["fio_full"] or "").strip(),
            "fio_short": r["fio_short"],
            "position_name": r["position_name"],
            "category": r["category"],
            "plan": plan,
            "plan_total": round(
                float(r["plan_hours_day"] or 0) + float(r["plan_hours_night"] or 0), 2
            ),
            "plan_night": float(r["plan_hours_night"] or 0),
            "fact": fact,
            "note": r["note"],
            "updated_at": r["updated_at"],
            "updated_by_name": r["updated_by_name"],
        })
    return out


def day_code_reference(conn: psycopg.Connection) -> list[dict[str, Any]]:
    """
    Справочник обозначений табеля для панели на странице.

    Если миграция 05 ещё не накатана, дополнительных полей в таблице нет —
    тогда отдаём пустой список, а не роняем страницу.
    """
    try:
        with conn.cursor() as cur:
            cur.execute(
                """
                SELECT code, name, group_name, kind, entry, note,
                       counts_as_worked, counts_in_norm
                FROM app.day_code
                ORDER BY sort_order, code
                """
            )
            return cur.fetchall()
    except psycopg.errors.UndefinedColumn:
        conn.rollback()
        return []


def norm_excusing_codes(conn: psycopg.Connection) -> frozenset[str] | None:
    """
    Какие обозначения уменьшают персональную норму — по справочнику.

    Правило законодательства: из расчётной нормы работника исключается время
    отсутствия **по уважительным причинам**, приходящееся на часы по его
    графику; при отсутствии без уважительной причины норма не корректируется
    (постановление Минтруда № 95, порядок применения; статья 133 ТК о составе
    фактического рабочего времени). Уважительность — вопрос не кода, а
    приказа нанимателя, поэтому набор живёт в справочнике и правится в
    «Базовых настройках», а не зашит в код.

    Возвращает None, если справочник ещё не заполнен или колонки нет — тогда
    вызывающий берёт встроенный набор из rules.NORM_EXCUSING_CODES.
    """
    try:
        with conn.cursor() as cur:
            cur.execute(
                "SELECT code FROM app.day_code WHERE counts_in_norm IS TRUE"
            )
            rows = cur.fetchall()
    except (psycopg.errors.UndefinedColumn, psycopg.errors.UndefinedTable):
        conn.rollback()
        return None
    return frozenset(r["code"] for r in rows) or None


def calendar_days(conn: psycopg.Connection, period: date) -> dict[date, str]:
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT day, day_type FROM app.calendar_day
            WHERE day >= %s AND day < (%s::date + interval '1 month')
            """,
            (period, period),
        )
        return {r["day"]: r["day_type"] for r in cur.fetchall()}


# ---------------------------------------------------------------------------
#  Запись клеток
# ---------------------------------------------------------------------------

def save_fact_cell(
    conn: psycopg.Connection,
    ts_id: int,
    employment_id: int,
    work_date: date,
    cell: FactCell,
    note: str | None = None,
    set_note: bool = False,
) -> None:
    """
    Сохранить день табеля. Примечание перезаписывается только если явно
    передан set_note — сетка табеля примечаний не знает и не должна их стирать.
    """
    note_clause = "note = EXCLUDED.note," if set_note else ""
    with conn.cursor() as cur:
        if cell.is_empty:
            cur.execute(
                """DELETE FROM app.timesheet_day
                    WHERE timesheet_id = %s AND employment_id = %s AND work_date = %s""",
                (ts_id, employment_id, work_date),
            )
        else:
            cur.execute(
                f"""
                INSERT INTO app.timesheet_day
                    (timesheet_id, employment_id, work_date, day_code,
                     hours_day, hours_night, hours_weekend, hours_overtime,
                     note, updated_by, updated_at)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s,
                        nullif(current_setting('app.user_id', true), '')::bigint, now())
                ON CONFLICT (timesheet_id, employment_id, work_date) DO UPDATE
                   SET day_code = EXCLUDED.day_code,
                       hours_day = EXCLUDED.hours_day,
                       hours_night = EXCLUDED.hours_night,
                       hours_weekend = EXCLUDED.hours_weekend,
                       hours_overtime = EXCLUDED.hours_overtime,
                       {note_clause}
                       updated_by = EXCLUDED.updated_by,
                       updated_at = now()
                """,
                (
                    ts_id, employment_id, work_date, cell.day_code,
                    cell.hours_day, cell.hours_night, cell.hours_weekend,
                    cell.hours_overtime,
                    (note or "").strip() or None,
                ),
            )
    conn.commit()


def clear_fact_month(
    conn: psycopg.Connection,
    ts_id: int,
    employment_id: int | None = None,
) -> int:
    """
    Стереть заполненный табель: весь месяц или одного сотрудника.

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

    Удаление уходит в audit_log триггером на timesheet_day, поэтому «кто
    стёр» останется в базе.
    """
    params: list[Any] = [ts_id]
    where = "timesheet_id = %s"
    if employment_id is not None:
        where += " AND employment_id = %s"
        params.append(employment_id)
    with conn.cursor() as cur:
        cur.execute(f"DELETE FROM app.timesheet_day WHERE {where}", params)
        removed = cur.rowcount
    conn.commit()
    return removed


def log_operation(
    conn: psycopg.Connection,
    operation: str,
    unit_id: int | None,
    period: date | None,
    rows: int,
    message: str,
    status: str = "ok",
) -> None:
    """Отметка в журнале операций: что сделали, кто и сколько строк задело."""
    with conn.cursor() as cur:
        cur.execute(
            """
            INSERT INTO app.operation_log
                (user_id, operation, org_unit_id, period_month,
                 status, rows_affected, message)
            VALUES (nullif(current_setting('app.user_id', true), '')::bigint,
                    %s, %s, %s, %s, %s, %s)
            """,
            (operation, unit_id, period, status, rows, message),
        )
    conn.commit()


def save_plan_cell(
    conn: psycopg.Connection,
    unit_id: int,
    employment_id: int,
    work_date: date,
    cell: PlanCell,
) -> None:
    with conn.cursor() as cur:
        if cell.is_empty:
            cur.execute(
                """DELETE FROM app.schedule_plan
                    WHERE employment_id = %s AND work_date = %s""",
                (employment_id, work_date),
            )
        else:
            cur.execute(
                """
                INSERT INTO app.schedule_plan
                    (employment_id, org_unit_id, work_date, shift_start, shift_end,
                     shift2_start, shift2_end,
                     day_code, updated_by, updated_at)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s,
                        nullif(current_setting('app.user_id', true), '')::bigint, now())
                ON CONFLICT (employment_id, work_date) DO UPDATE
                   SET shift_start = EXCLUDED.shift_start,
                       shift_end = EXCLUDED.shift_end,
                       shift2_start = EXCLUDED.shift2_start,
                       shift2_end = EXCLUDED.shift2_end,
                       day_code = EXCLUDED.day_code,
                       updated_by = EXCLUDED.updated_by,
                       updated_at = now()
                """,
                (
                    employment_id, unit_id, work_date,
                    cell.shift_start, cell.shift_end,
                    cell.shift2_start, cell.shift2_end, cell.day_code,
                ),
            )
    conn.commit()


# ---------------------------------------------------------------------------
#  Переходы статуса и служебное
# ---------------------------------------------------------------------------

def call_transition(conn: psycopg.Connection, fn: str, *args) -> None:
    placeholders = ", ".join(["%s"] * len(args))
    with conn.cursor() as cur:
        cur.execute(f"SELECT app.{fn}({placeholders})", args)
    conn.commit()


def completeness_gaps(conn: psycopg.Connection, ts_id: int) -> list[dict[str, Any]]:
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT c.personnel_no, c.from_date, c.to_date,
                   c.days_expected, c.days_filled,
                   c.employment_id,
                   -- ФИО: по десятизначному номеру человека не узнать, а
                   -- этот блок прямо запрещает сдать табель
                   e.last_name || ' ' || left(e.first_name, 1) || '.' ||
                       coalesce(left(e.middle_name, 1) || '.', '') AS fio_short,
                   p.name AS position_name
            FROM app.v_timesheet_completeness c
            JOIN app.employment em ON em.id = c.employment_id
            JOIN app.employee e    ON e.id = em.employee_id
            JOIN app.position p    ON p.id = em.position_id
            WHERE c.timesheet_id = %s AND c.days_filled < c.days_expected
            ORDER BY e.last_name, e.first_name
            """,
            (ts_id,),
        )
        return cur.fetchall()


def journal(conn: psycopg.Connection, patterns: Sequence[str],
            unit_id: int | None, period: date | None,
            limit: int = 200) -> list[dict[str, Any]]:
    """
    Журнал операций одного блока работ, в разрезе месяцев.

    Раньше журнал был один на всё приложение и жил только на «Сверке»:
    кадровая запись и правка табеля лежали вперемешку, а найти «что делали с
    этим человеком» было негде. Теперь каждый блок показывает свои операции
    (пункт 16).

    Отбор идёт по образцам имени операции (`timesheet%`), а не по перечню
    кодов: новая операция блока попадает в его журнал сама, без правки списка
    в двух местах.

    Период задан — показываем этот месяц. Не задан — последние записи как
    есть; страница «Сотрудники» ведётся не месяцем, но месяц у записи всё
    равно есть, и группировка по нему остаётся.
    """
    where = ["ol.operation LIKE ANY(%(pats)s)"]
    args: dict[str, Any] = {"pats": list(patterns), "limit": limit}
    if unit_id is not None:
        where.append("(ol.org_unit_id = %(unit)s OR ol.org_unit_id IS NULL)")
        args["unit"] = unit_id
    if period is not None:
        where.append(
            "(ol.period_month = %(period)s"
            " OR (ol.period_month IS NULL"
            "     AND date_trunc('month', ol.at)::date = %(period)s))")
        args["period"] = period.replace(day=1)

    with conn.cursor() as cur:
        cur.execute(
            f"""
            SELECT ol.at, ol.operation, ol.status, ol.rows_affected, ol.message,
                   u.full_name,
                   date_trunc('month', coalesce(ol.period_month, ol.at))::date AS month
            FROM app.operation_log ol
            LEFT JOIN app.app_user u ON u.id = ol.user_id
            WHERE {' AND '.join(where)}
            ORDER BY ol.at DESC
            LIMIT %(limit)s
            """,
            args,
        )
        return cur.fetchall()


def recent_operations(conn: psycopg.Connection, unit_id: int, period: date, limit: int = 20):
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT ol.at, ol.operation, ol.status, ol.message, u.full_name
            FROM app.operation_log ol
            LEFT JOIN app.app_user u ON u.id = ol.user_id
            WHERE (ol.org_unit_id = %s OR ol.org_unit_id IS NULL)
              AND (ol.period_month = %s OR ol.period_month IS NULL)
            ORDER BY ol.at DESC
            LIMIT %s
            """,
            (unit_id, period, limit),
        )
        return cur.fetchall()


#  Годовые пределы привлечения (23.09.2026): «вызовов в выходной день в год
#  может быть 12 раз… сверхурочной работы не больше 180 часов в год». Столбцы
#  «Вызовы» и «Сверхур.» табеля показывают, сколько из предела уже выбрано.
CALLS_YEAR_LIMIT = 12
OVERTIME_YEAR_LIMIT = 180


def year_limits(conn: psycopg.Connection, employment_ids: list[int],
                until: date) -> dict[int, dict[str, float]]:
    """
    Сколько вызовов в выходной и сверхурочных часов у человека с 1 января по
    `until` включительно.

    Считается по человеку, а не по назначению: предел — его, и перевод из
    депо в депо в середине года счёт не обнуляет. Вызов — день, в котором
    есть часы работы в выходной (клетка «РВ»), сверхурочные — сумма часов
    «С». Считаем до конца открытого месяца, а не до сегодня: в августовском
    табеле «4/12» должно значить «на конец августа», а не «с учётом того, что
    будет в сентябре».

    Депо видит только свои табели (RLS): если человек в этом году работал в
    другом депо, те дни в счёт не войдут. Администрация видит все.
    """
    if not employment_ids:
        return {}
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT em0.id AS employment_id,
                   count(DISTINCT td.work_date)
                       FILTER (WHERE td.hours_weekend > 0) AS calls,
                   coalesce(sum(td.hours_overtime), 0) AS overtime
            FROM app.employment em0
            JOIN app.employment em   ON em.employee_id = em0.employee_id
            JOIN app.timesheet_day td ON td.employment_id = em.id
            WHERE em0.id = ANY(%s)
              AND td.work_date BETWEEN %s AND %s
            GROUP BY em0.id
            """,
            (employment_ids, date(until.year, 1, 1), until),
        )
        got = {r["employment_id"]: {"calls": int(r["calls"]),
                                    "overtime": float(r["overtime"])}
               for r in cur.fetchall()}
    return {e: got.get(e, {"calls": 0, "overtime": 0.0}) for e in employment_ids}


def limits_text(v: dict[str, float]) -> dict[str, str]:
    """«1/12» и «6/180» — как в столбцах файла управления."""
    ov = v["overtime"]
    ov_s = str(int(ov)) if abs(ov - round(ov)) < 0.01 else f"{ov:.1f}"
    return {"calls_year": f"{v['calls']}/{CALLS_YEAR_LIMIT}",
            "overtime_year": f"{ov_s}/{OVERTIME_YEAR_LIMIT}"}
