"""
Финансы: аренда и услуги связи (24.09.2026, миграция 86).

Задание заказчика: «новый блок Финансы, Аренда — необходимо место работы с
договорами аренды, фиксации текущих затрат». Согласованное устройство:

  * договор — контрагент, номер и дата, предмет (объект и адрес или услуга),
    депо, срок и пролонгация, день оплаты, сумма в месяц и валюта;
  * начисление за месяц создаётся само по действующим договорам; валютная
    сумма переводится в BYN по курсу НБРБ, курс вносится вручную;
  * оплата — отдельная отметка: дата, сумма в BYN, номер платёжки;
  * сводка по депо, вкладкам и месяцам, долг на дату, выгрузка в Excel;
  * задачи на доске Администрации: договор заканчивается, день оплаты
    прошёл без оплаты, нет курса.

Раздел целиком у Администрации ООУРЦ: права `finance.read` и
`finance.write`, политики таблиц — по ним же.

Правила расчёта, чтобы не искать их по коду:

  * сумма месяца — та, что действует на последний день месяца (последняя
    строка истории сумм с датой начала не позже него). Допсоглашение «с
    15-го» меняет сумму всего месяца; неполный месяц правится вручную с
    причиной;
  * срок оплаты — день оплаты в том же месяце или в следующем (по
    договору); дня нет в месяце — последний день месяца;
  * курс: правило «на дату начисления» — курс на первое число месяца;
    правило «на дату оплаты» — курс на дату первой оплаты, а пока оплаты нет —
    на срок оплаты;
  * остаток по договору = начислено в BYN − оплачено. Минус — переплата.
    Начисление без курса в остаток не входит и помечается отдельно.
"""

from __future__ import annotations

import calendar
from datetime import date, timedelta
from decimal import Decimal, InvalidOperation, ROUND_HALF_UP
from typing import Any

import psycopg

KIND_RU = {"rent": "Аренда", "telecom": "Услуги связи"}
KINDS = tuple(KIND_RU)
CURRENCIES = ("BYN", "USD", "EUR")
RATE_RULE_RU = {
    "payment": "по курсу НБРБ на дату оплаты",
    "accrual": "по курсу НБРБ на дату начисления (1-е число месяца)",
}
PAY_MONTH_RU = {"current": "в том же месяце", "next": "в следующем месяце"}
DOC_KIND_RU = {"contract": "договор", "addendum": "допсоглашение", "other": "другое"}

#  За сколько дней до окончания договора ставить задачу на доску
EXPIRY_LEAD = 30

OPERATION_RU = {
    "finance_contract": "Договор заведён",
    "finance_contract_edit": "Договор исправлен",
    "finance_amount": "Новая сумма по договору",
    "finance_close": "Договор закрыт",
    "finance_reopen": "Договор возвращён в работу",
    "finance_renew": "Договор продлён автоматически",
    "finance_accrual_edit": "Начисление исправлено",
    "finance_payment": "Оплата отмечена",
    "finance_payment_drop": "Оплата удалена",
    "finance_rate": "Курс НБРБ внесён",
    "finance_rate_drop": "Курс НБРБ удалён",
    "finance_file": "Скан приложен",
    "finance_file_drop": "Скан удалён",
}


# ---------------------------------------------------------------------------
#  Разбор значений формы
# ---------------------------------------------------------------------------

def has_finance(conn: psycopg.Connection) -> bool:
    """Накатана ли миграция 86. Без неё раздел показывает пояснение, а не 500."""
    with conn.cursor() as cur:
        cur.execute("SELECT to_regclass('app.finance_contract') IS NOT NULL AS ok")
        return bool(cur.fetchone()["ok"])


def money(text: Any, what: str, *, allow_zero: bool = False) -> Decimal:
    raw = str(text or "").strip().replace(" ", "").replace(" ", "").replace(",", ".")
    if not raw:
        raise ValueError(f"Укажите {what}")
    try:
        value = Decimal(raw)
    except InvalidOperation:
        raise ValueError(f"{what[:1].upper() + what[1:]} — это число, например 1250,50")
    if value < 0 or (value == 0 and not allow_zero):
        raise ValueError(f"{what[:1].upper() + what[1:]} должна быть больше нуля")
    return value.quantize(Decimal("0.01"), rounding=ROUND_HALF_UP)


def rate_value(text: Any) -> Decimal:
    raw = str(text or "").strip().replace(",", ".")
    try:
        value = Decimal(raw)
    except InvalidOperation:
        raise ValueError("Курс — это число, например 3,2715")
    if value <= 0:
        raise ValueError("Курс должен быть больше нуля")
    return value.quantize(Decimal("0.0001"), rounding=ROUND_HALF_UP)


def month_start(day: date) -> date:
    return day.replace(day=1)


def add_months(day: date, n: int) -> date:
    y, m = divmod(day.month - 1 + n, 12)
    y += day.year
    last = calendar.monthrange(y, m + 1)[1]
    return date(y, m + 1, min(day.day, last))


def month_end(day: date) -> date:
    return day.replace(day=calendar.monthrange(day.year, day.month)[1])


def due_date(period: date, pay_day: int, pay_month: str) -> date:
    """Срок оплаты за месяц `period`."""
    base = add_months(period, 1) if pay_month == "next" else period
    last = calendar.monthrange(base.year, base.month)[1]
    return base.replace(day=min(pay_day, last))


def _log(cur, user_id: int | None, unit_id: int | None, operation: str,
         message: str, period: date | None = None) -> None:
    cur.execute(
        "INSERT INTO app.operation_log (user_id, operation, org_unit_id, "
        "period_month, status, rows_affected, message) "
        "VALUES (%s, %s, %s, %s, 'ok', 1, %s)",
        (user_id, operation, unit_id, period, message))


# ---------------------------------------------------------------------------
#  Справочное
# ---------------------------------------------------------------------------

def units(conn: psycopg.Connection) -> list[dict[str, Any]]:
    """Куда можно отнести договор: депо и Администрация («Минск»)."""
    with conn.cursor() as cur:
        cur.execute(
            "SELECT id, code, name, kind FROM app.org_unit "
            " WHERE is_active AND kind IN ('rc', 'hq') "
            " ORDER BY (kind = 'hq') DESC, name")
        return cur.fetchall()


def rates(conn: psycopg.Connection, limit: int = 40) -> list[dict[str, Any]]:
    with conn.cursor() as cur:
        cur.execute(
            "SELECT r.*, u.full_name AS entered_by_name FROM app.finance_rate r "
            "  LEFT JOIN app.app_user u ON u.id = r.entered_by "
            " ORDER BY r.rate_date DESC, r.currency LIMIT %s", (limit,))
        return cur.fetchall()


def missing_rates(conn: psycopg.Connection,
                  today: date | None = None) -> list[dict[str, Any]]:
    """
    Курсы, которых не хватает: валютные начисления, чья дата курса уже
    наступила, а курса на неё нет. Будущие даты не в счёт — курса на них
    НБРБ ещё не объявил.
    """
    day = today or date.today()
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT a.currency, a.rate_date, count(*) AS n,
                   array_agg(DISTINCT c.id) AS contract_ids
              FROM app.finance_accrual a
              JOIN app.finance_contract c ON c.id = a.contract_id
             WHERE a.currency <> 'BYN' AND a.amount_byn IS NULL
               AND a.rate_date IS NOT NULL AND a.rate_date <= %s
             GROUP BY a.currency, a.rate_date
             ORDER BY a.rate_date, a.currency
            """, (day,))
        return cur.fetchall()


# ---------------------------------------------------------------------------
#  Начисления: создание и пересчёт
# ---------------------------------------------------------------------------

def _amount_for(cur, contract_id: int, period: date) -> Decimal | None:
    cur.execute(
        "SELECT amount FROM app.finance_contract_amount "
        " WHERE contract_id = %s AND valid_from <= %s "
        " ORDER BY valid_from DESC LIMIT 1",
        (contract_id, month_end(period)))
    row = cur.fetchone()
    return row["amount"] if row else None


def _recalc(cur, accrual_id: int) -> None:
    """
    Пересчитать дату курса, курс и сумму в BYN одного начисления. Сумму в
    валюте не трогает: её меняют история сумм и ручная правка.
    """
    cur.execute(
        """
        SELECT a.id, a.period_month, a.amount_cur, a.currency, a.due_date,
               c.rate_rule,
               (SELECT min(p.paid_on) FROM app.finance_payment p
                 WHERE p.accrual_id = a.id) AS first_paid
          FROM app.finance_accrual a
          JOIN app.finance_contract c ON c.id = a.contract_id
         WHERE a.id = %s
        """, (accrual_id,))
    a = cur.fetchone()
    if a is None:
        return
    if a["currency"] == "BYN":
        cur.execute(
            "UPDATE app.finance_accrual SET rate_date = NULL, rate = NULL, "
            "amount_byn = amount_cur WHERE id = %s", (accrual_id,))
        return
    if a["rate_rule"] == "accrual":
        rate_day = a["period_month"]
    else:
        rate_day = a["first_paid"] or a["due_date"]
    cur.execute(
        "SELECT rate FROM app.finance_rate WHERE currency = %s AND rate_date = %s",
        (a["currency"], rate_day))
    row = cur.fetchone()
    rate = row["rate"] if row else None
    byn = ((a["amount_cur"] * rate).quantize(Decimal("0.01"), rounding=ROUND_HALF_UP)
           if rate is not None else None)
    cur.execute(
        "UPDATE app.finance_accrual SET rate_date = %s, rate = %s, amount_byn = %s "
        " WHERE id = %s", (rate_day, rate, byn, accrual_id))


def _last_month(c: dict[str, Any]) -> date | None:
    """Последний месяц, за который по договору положено начисление."""
    ends = [d for d in (c["ends_on"], c["closed_on"]) if d is not None]
    return month_start(min(ends)) if ends else None


def ensure(conn: psycopg.Connection, today: date | None = None,
           user_id: int | None = None, commit: bool = True) -> dict[str, int]:
    """
    Привести начисления в порядок на сегодня. Вызывается при открытии раздела
    и доски Администрации, поэтому обязана быть дешёвой и повторяемой:

      1. договоры с автопролонгацией, чей срок вышел, продлеваются на срок
         пролонгации (сколько раз нужно) — с записью в журнал;
      2. по каждому действующему договору заводятся недостающие начисления с
         «начислять с» по текущий месяц, но не дальше окончания или закрытия;
      3. валютные начисления без суммы в BYN пересчитываются — вдруг курс
         уже внесли.
    """
    day = today or date.today()
    made = renewed = 0
    with conn.cursor() as cur:
        cur.execute(
            "SELECT * FROM app.finance_contract "
            " WHERE closed_on IS NULL AND auto_renew AND ends_on IS NOT NULL "
            "   AND ends_on < %s", (day,))
        for c in cur.fetchall():
            new_end = c["ends_on"]
            while new_end < day:
                new_end = add_months(new_end, int(c["renew_months"]))
            cur.execute("UPDATE app.finance_contract SET ends_on = %s WHERE id = %s",
                        (new_end, c["id"]))
            _log(cur, user_id, c["org_unit_id"], "finance_renew",
                 f"{KIND_RU[c['kind']]}: {c['counterparty']}, договор №{c['contract_no']} "
                 f"продлён до {new_end:%d.%m.%Y}")
            renewed += 1

        current = month_start(day)
        cur.execute("SELECT * FROM app.finance_contract")
        for c in cur.fetchall():
            first = max(c["accrue_from"], month_start(c["starts_on"]))
            last = _last_month(c)
            upto = min(current, last) if last else current
            if first > upto:
                continue
            cur.execute("SELECT period_month FROM app.finance_accrual WHERE contract_id = %s",
                        (c["id"],))
            have = {r["period_month"] for r in cur.fetchall()}
            m = first
            while m <= upto:
                if m not in have:
                    amount = _amount_for(cur, c["id"], m)
                    if amount is not None:
                        cur.execute(
                            """
                            INSERT INTO app.finance_accrual
                                (contract_id, period_month, amount_cur, currency,
                                 amount_byn, due_date)
                            VALUES (%s, %s, %s, %s, %s, %s)
                            ON CONFLICT (contract_id, period_month) DO NOTHING
                            RETURNING id
                            """,
                            (c["id"], m, amount, c["currency"],
                             amount if c["currency"] == "BYN" else None,
                             due_date(m, c["pay_day"], c["pay_month"])))
                        row = cur.fetchone()
                        if row:
                            _recalc(cur, row["id"])
                            made += 1
                m = add_months(m, 1)

        cur.execute("SELECT id FROM app.finance_accrual "
                    " WHERE currency <> 'BYN' AND amount_byn IS NULL")
        for r in cur.fetchall():
            _recalc(cur, r["id"])
    #  Доска вызывает внутри своей транзакции — там фиксирует она сама
    if commit:
        conn.commit()
    return {"made": made, "renewed": renewed}


# ---------------------------------------------------------------------------
#  Договоры
# ---------------------------------------------------------------------------

_BALANCE_SQL = """
    SELECT c.id,
           coalesce(sum(a.amount_byn), 0) AS accrued_byn,
           coalesce(sum(a.amount_byn) FILTER (WHERE a.due_date <= %(today)s), 0) AS due_byn,
           count(*) FILTER (WHERE a.id IS NOT NULL AND a.amount_byn IS NULL) AS no_rate,
           (SELECT coalesce(sum(p.amount_byn), 0) FROM app.finance_payment p
              JOIN app.finance_accrual x ON x.id = p.accrual_id
             WHERE x.contract_id = c.id) AS paid_byn
      FROM app.finance_contract c
      LEFT JOIN app.finance_accrual a ON a.contract_id = c.id
     GROUP BY c.id
"""


def status_of(c: dict[str, Any], today: date | None = None) -> tuple[str, str]:
    """(код, подпись) состояния договора."""
    day = today or date.today()
    if c.get("closed_on"):
        return "closed", f"закрыт {c['closed_on']:%d.%m.%Y}"
    if c.get("starts_on") and c["starts_on"] > day:
        return "future", f"с {c['starts_on']:%d.%m.%Y}"
    end = c.get("ends_on")
    if end is None:
        return "active", "бессрочный"
    if end < day:
        return "ended", f"истёк {end:%d.%m.%Y}"
    if (end - day).days <= EXPIRY_LEAD:
        tail = ", продлится сам" if c.get("auto_renew") else ""
        return "expiring", f"до {end:%d.%m.%Y}{tail}"
    return "active", f"до {end:%d.%m.%Y}"


def contracts(conn: psycopg.Connection, kind: str,
              show_closed: bool = False,
              today: date | None = None) -> list[dict[str, Any]]:
    day = today or date.today()
    with conn.cursor() as cur:
        cur.execute(
            f"""
            WITH bal AS ({_BALANCE_SQL})
            SELECT c.*, ou.name AS unit_name,
                   (SELECT amount FROM app.finance_contract_amount x
                     WHERE x.contract_id = c.id AND x.valid_from <= %(today)s
                     ORDER BY x.valid_from DESC LIMIT 1) AS amount_now,
                   (SELECT amount FROM app.finance_contract_amount x
                     WHERE x.contract_id = c.id ORDER BY x.valid_from LIMIT 1) AS amount_first,
                   (SELECT min(a.due_date) FROM app.finance_accrual a
                     WHERE a.contract_id = c.id
                       AND coalesce(a.amount_byn, 1) >
                           (SELECT coalesce(sum(p.amount_byn), 0)
                              FROM app.finance_payment p WHERE p.accrual_id = a.id)) AS next_due,
                   bal.accrued_byn, bal.due_byn, bal.paid_byn, bal.no_rate
              FROM app.finance_contract c
              JOIN app.org_unit ou ON ou.id = c.org_unit_id
              JOIN bal ON bal.id = c.id
             WHERE c.kind = %(kind)s
               AND (%(closed)s OR c.closed_on IS NULL)
             ORDER BY (c.closed_on IS NOT NULL), ou.name, c.counterparty, c.contract_date
            """, {"kind": kind, "closed": show_closed, "today": day})
        rows = cur.fetchall()
    for r in rows:
        r["status"], r["status_label"] = status_of(r, day)
        r["balance"] = r["accrued_byn"] - r["paid_byn"]
        r["amount_show"] = r["amount_now"] if r["amount_now"] is not None else r["amount_first"]
    return rows


def contract(conn: psycopg.Connection, contract_id: int,
             today: date | None = None) -> dict[str, Any] | None:
    day = today or date.today()
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT c.*, ou.name AS unit_name, u1.full_name AS created_by_name,
                   u2.full_name AS updated_by_name
              FROM app.finance_contract c
              JOIN app.org_unit ou ON ou.id = c.org_unit_id
              LEFT JOIN app.app_user u1 ON u1.id = c.created_by
              LEFT JOIN app.app_user u2 ON u2.id = c.updated_by
             WHERE c.id = %s
            """, (contract_id,))
        c = cur.fetchone()
        if c is None:
            return None
        cur.execute(
            "SELECT x.*, u.full_name AS created_by_name "
            "  FROM app.finance_contract_amount x "
            "  LEFT JOIN app.app_user u ON u.id = x.created_by "
            " WHERE x.contract_id = %s ORDER BY x.valid_from DESC", (contract_id,))
        c["amounts"] = cur.fetchall()
        cur.execute(
            """
            SELECT a.*, u.full_name AS updated_by_name,
                   coalesce((SELECT sum(p.amount_byn) FROM app.finance_payment p
                              WHERE p.accrual_id = a.id), 0) AS paid_byn
              FROM app.finance_accrual a
              LEFT JOIN app.app_user u ON u.id = a.updated_by
             WHERE a.contract_id = %s
             ORDER BY a.period_month DESC
            """, (contract_id,))
        c["accruals"] = cur.fetchall()
        cur.execute(
            """
            SELECT p.*, a.period_month, u.full_name AS created_by_name
              FROM app.finance_payment p
              JOIN app.finance_accrual a ON a.id = p.accrual_id
              LEFT JOIN app.app_user u ON u.id = p.created_by
             WHERE a.contract_id = %s
             ORDER BY p.paid_on DESC, p.id DESC
            """, (contract_id,))
        c["payments"] = cur.fetchall()
        cur.execute(
            "SELECT f.*, u.full_name AS uploaded_by_name FROM app.finance_file f "
            "  LEFT JOIN app.app_user u ON u.id = f.uploaded_by "
            " WHERE f.contract_id = %s ORDER BY f.doc_date DESC NULLS LAST, f.id DESC",
            (contract_id,))
        c["files"] = cur.fetchall()
    for a in c["accruals"]:
        a["left_byn"] = (a["amount_byn"] - a["paid_byn"]) if a["amount_byn"] is not None else None
        a["overdue"] = (a["due_date"] <= day and
                        (a["left_byn"] is None and a["paid_byn"] == 0
                         or (a["left_byn"] or 0) > 0))
    known = [a for a in c["accruals"] if a["amount_byn"] is not None]
    c["accrued_byn"] = sum((a["amount_byn"] for a in known), Decimal(0))
    c["paid_byn"] = sum((p["amount_byn"] for p in c["payments"]), Decimal(0))
    c["balance"] = c["accrued_byn"] - c["paid_byn"]
    c["no_rate"] = sum(1 for a in c["accruals"] if a["amount_byn"] is None)
    c["amount_now"] = next((x["amount"] for x in c["amounts"] if x["valid_from"] <= day),
                           c["amounts"][-1]["amount"] if c["amounts"] else None)
    c["status"], c["status_label"] = status_of(c, day)
    return c


def parse_day(text: str) -> date:
    """Дата из поля формы: `2026-09-24` (поле даты) или `24.09.2026`."""
    raw = (text or "").strip()
    try:
        if len(raw) == 7 and raw[4] == "-":     # поле месяца: 2026-09
            return date(int(raw[:4]), int(raw[5:]), 1)
        if "." in raw:
            d, m, y = raw.split(".")
            return date(int(y), int(m), int(d))
        return date.fromisoformat(raw)
    except (ValueError, TypeError):
        raise ValueError(f"Не разобрал дату «{raw}»")


def _contract_fields(data: dict[str, Any], valid_units: set[int]) -> dict[str, Any]:
    def text(name: str, what: str, required: bool = True) -> str | None:
        v = (data.get(name) or "").strip()
        if required and not v:
            raise ValueError(f"Укажите {what}")
        return v or None

    def day(name: str, what: str, required: bool = True) -> date | None:
        v = (data.get(name) or "").strip()
        if not v:
            if required:
                raise ValueError(f"Укажите {what}")
            return None
        try:
            return parse_day(v)
        except ValueError:
            raise ValueError(f"Не разобрал дату: {what}")

    kind = data.get("kind")
    if kind not in KINDS:
        raise ValueError("Вид договора не выбран")
    try:
        unit_id = int(data.get("org_unit_id") or 0)
    except ValueError:
        unit_id = 0
    if unit_id not in valid_units:
        raise ValueError("Выберите депо, к которому относится договор")
    currency = data.get("currency") or "BYN"
    if currency not in CURRENCIES:
        raise ValueError("Валюта — BYN, USD или EUR")
    rate_rule = data.get("rate_rule") or "payment"
    if rate_rule not in RATE_RULE_RU:
        raise ValueError("Выберите, по какому курсу переводить валюту")
    pay_month = data.get("pay_month") or "current"
    if pay_month not in PAY_MONTH_RU:
        raise ValueError("Выберите, в каком месяце платёж")
    try:
        pay_day = int(data.get("pay_day") or 0)
    except ValueError:
        pay_day = 0
    if not 1 <= pay_day <= 31:
        raise ValueError("День оплаты — число от 1 до 31")
    starts = day("starts_on", "дату начала действия")
    ends = day("ends_on", "дату окончания", required=False)
    if ends and ends < starts:
        raise ValueError("Договор не может закончиться раньше, чем начался")
    auto_renew = bool(data.get("auto_renew"))
    try:
        renew_months = int(data.get("renew_months") or 12)
    except ValueError:
        renew_months = 12
    if not 1 <= renew_months <= 120:
        raise ValueError("Срок пролонгации — от 1 до 120 месяцев")
    if auto_renew and ends is None:
        raise ValueError("Пролонгация бывает только у договора с датой окончания")
    accrue = day("accrue_from", "с какого месяца начислять", required=False)
    area = (data.get("area_m2") or "").strip()
    return {
        "kind": kind, "org_unit_id": unit_id,
        "counterparty": text("counterparty", "контрагента"),
        "contract_no": text("contract_no", "номер договора"),
        "contract_date": day("contract_date", "дату договора"),
        "subject": text("subject", "объект аренды" if kind == "rent" else "услугу"),
        "address": text("address", "адрес", required=False),
        "area_m2": money(area, "площадь") if area else None,
        "starts_on": starts, "ends_on": ends,
        "auto_renew": auto_renew, "renew_months": renew_months,
        "pay_day": pay_day, "pay_month": pay_month,
        "currency": currency, "rate_rule": rate_rule,
        "accrue_from": month_start(accrue) if accrue else None,
        "note": text("note", "", required=False),
    }


def save_contract(conn: psycopg.Connection, data: dict[str, Any],
                  user_id: int | None, contract_id: int | None = None) -> int:
    """
    Завести договор или исправить реквизиты. Сумма у нового договора
    обязательна и становится первой строкой истории сумм; у действующего
    сумма меняется отдельно (`add_amount`) — чтобы прежняя осталась историей.
    """
    fields = _contract_fields(data, {u["id"] for u in units(conn)})
    today = date.today()
    with conn.cursor() as cur:
        if contract_id is None:
            amount = money(data.get("amount"), "сумму в месяц")
            if fields["accrue_from"] is None:
                fields["accrue_from"] = max(month_start(fields["starts_on"]),
                                            month_start(today))
            cols = list(fields) + ["created_by"]
            cur.execute(
                f"INSERT INTO app.finance_contract ({', '.join(cols)}) "
                f"VALUES ({', '.join(['%s'] * len(cols))}) RETURNING id",
                [fields[k] for k in fields] + [user_id])
            cid = cur.fetchone()["id"]
            cur.execute(
                "INSERT INTO app.finance_contract_amount "
                "(contract_id, valid_from, amount, reason, created_by) "
                "VALUES (%s, %s, %s, 'сумма по договору', %s)",
                (cid, fields["starts_on"], amount, user_id))
            _log(cur, user_id, fields["org_unit_id"], "finance_contract",
                 f"{KIND_RU[fields['kind']]}: {fields['counterparty']}, №{fields['contract_no']} "
                 f"от {fields['contract_date']:%d.%m.%Y}, {amount} {fields['currency']} в месяц")
        else:
            cur.execute("SELECT * FROM app.finance_contract WHERE id = %s", (contract_id,))
            old = cur.fetchone()
            if old is None:
                raise ValueError("Договор не найден — обновите страницу")
            if fields["accrue_from"] is None:
                fields["accrue_from"] = old["accrue_from"]
            fields["kind"] = old["kind"]
            sets = ", ".join(f"{k} = %s" for k in fields)
            cur.execute(
                f"UPDATE app.finance_contract SET {sets}, updated_by = %s, "
                f"updated_at = now() WHERE id = %s",
                [fields[k] for k in fields] + [user_id, contract_id])
            cid = contract_id
            #  Сменились день оплаты, валюта или правило курса — начисления
            #  без оплат приводим к новым реквизитам
            if (old["pay_day"], old["pay_month"], old["currency"], old["rate_rule"]) != \
               (fields["pay_day"], fields["pay_month"], fields["currency"], fields["rate_rule"]):
                cur.execute("SELECT id, period_month FROM app.finance_accrual "
                            " WHERE contract_id = %s", (cid,))
                for a in cur.fetchall():
                    cur.execute(
                        "UPDATE app.finance_accrual SET due_date = %s, currency = %s, "
                        "amount_byn = CASE WHEN %s = 'BYN' THEN amount_cur ELSE NULL END "
                        "WHERE id = %s",
                        (due_date(a["period_month"], fields["pay_day"], fields["pay_month"]),
                         fields["currency"], fields["currency"], a["id"]))
                    _recalc(cur, a["id"])
            #  Начисления за месяцы, которые теперь вне срока договора и без
            #  оплат, убираем: их больше не должно быть
            _drop_outside(cur, cid)
            _log(cur, user_id, fields["org_unit_id"], "finance_contract_edit",
                 f"{KIND_RU[old['kind']]}: {fields['counterparty']}, №{fields['contract_no']}")
    conn.commit()
    ensure(conn, user_id=user_id)
    return cid


def _drop_outside(cur, contract_id: int) -> int:
    cur.execute("SELECT * FROM app.finance_contract WHERE id = %s", (contract_id,))
    c = cur.fetchone()
    first = max(c["accrue_from"], month_start(c["starts_on"]))
    last = _last_month(c)
    cur.execute(
        """
        DELETE FROM app.finance_accrual a
         WHERE a.contract_id = %s
           AND (a.period_month < %s OR (%s::date IS NOT NULL AND a.period_month > %s))
           AND NOT EXISTS (SELECT 1 FROM app.finance_payment p WHERE p.accrual_id = a.id)
        """, (contract_id, first, last, last))
    return cur.rowcount


def add_amount(conn: psycopg.Connection, contract_id: int, valid_from: date,
               amount: Decimal, reason: str, user_id: int | None) -> None:
    """
    Новая сумма по договору с даты (допсоглашение). Начисления с этого месяца
    пересчитываются, кроме исправленных вручную: ручная правка — решение
    человека, и тихо перетирать её нельзя.
    """
    reason = (reason or "").strip()
    if not reason:
        raise ValueError("Укажите основание: например, допсоглашение №2 от 01.10.2026")
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM app.finance_contract WHERE id = %s", (contract_id,))
        c = cur.fetchone()
        if c is None:
            raise ValueError("Договор не найден — обновите страницу")
        cur.execute(
            """
            INSERT INTO app.finance_contract_amount
                (contract_id, valid_from, amount, reason, created_by)
            VALUES (%s, %s, %s, %s, %s)
            ON CONFLICT (contract_id, valid_from)
            DO UPDATE SET amount = EXCLUDED.amount, reason = EXCLUDED.reason,
                          created_by = EXCLUDED.created_by, created_at = now()
            """, (contract_id, valid_from, amount, reason, user_id))
        cur.execute(
            "SELECT id, period_month FROM app.finance_accrual "
            " WHERE contract_id = %s AND NOT is_manual AND period_month >= %s",
            (contract_id, month_start(valid_from)))
        for a in cur.fetchall():
            new = _amount_for(cur, contract_id, a["period_month"])
            cur.execute("UPDATE app.finance_accrual SET amount_cur = %s, "
                        "amount_byn = CASE WHEN currency = 'BYN' THEN %s ELSE amount_byn END "
                        "WHERE id = %s", (new, new, a["id"]))
            _recalc(cur, a["id"])
        _log(cur, user_id, c["org_unit_id"], "finance_amount",
             f"{c['counterparty']}, №{c['contract_no']}: {amount} {c['currency']} "
             f"с {valid_from:%d.%m.%Y} — {reason}")
    conn.commit()
    ensure(conn, user_id=user_id)


def close_contract(conn: psycopg.Connection, contract_id: int, closed_on: date,
                   reason: str, user_id: int | None) -> None:
    reason = (reason or "").strip()
    if not reason:
        raise ValueError("Укажите причину закрытия: расторжение, окончание, замена договора")
    with conn.cursor() as cur:
        cur.execute("UPDATE app.finance_contract SET closed_on = %s, close_reason = %s, "
                    "updated_by = %s, updated_at = now() WHERE id = %s RETURNING *",
                    (closed_on, reason, user_id, contract_id))
        c = cur.fetchone()
        if c is None:
            raise ValueError("Договор не найден — обновите страницу")
        dropped = _drop_outside(cur, contract_id)
        _log(cur, user_id, c["org_unit_id"], "finance_close",
             f"{c['counterparty']}, №{c['contract_no']}: закрыт с {closed_on:%d.%m.%Y} — {reason}"
             + (f"; снято будущих начислений: {dropped}" if dropped else ""))
    conn.commit()


def reopen_contract(conn: psycopg.Connection, contract_id: int,
                    user_id: int | None) -> None:
    with conn.cursor() as cur:
        cur.execute("UPDATE app.finance_contract SET closed_on = NULL, close_reason = NULL, "
                    "updated_by = %s, updated_at = now() WHERE id = %s RETURNING *",
                    (user_id, contract_id))
        c = cur.fetchone()
        if c is None:
            raise ValueError("Договор не найден — обновите страницу")
        _log(cur, user_id, c["org_unit_id"], "finance_reopen",
             f"{c['counterparty']}, №{c['contract_no']}")
    conn.commit()
    ensure(conn, user_id=user_id)


# ---------------------------------------------------------------------------
#  Начисления: ручная правка
# ---------------------------------------------------------------------------

def _accrual_head(cur, accrual_id: int) -> dict[str, Any]:
    cur.execute(
        "SELECT a.*, c.org_unit_id, c.counterparty, c.contract_no, c.id AS cid "
        "  FROM app.finance_accrual a JOIN app.finance_contract c ON c.id = a.contract_id "
        " WHERE a.id = %s", (accrual_id,))
    a = cur.fetchone()
    if a is None:
        raise ValueError("Начисление не найдено — обновите страницу")
    return a


def edit_accrual(conn: psycopg.Connection, accrual_id: int, amount_cur: Decimal,
                 reason: str, user_id: int | None) -> int:
    """Поправить сумму начисления в валюте договора — только с причиной."""
    reason = (reason or "").strip()
    if not reason:
        raise ValueError("Укажите причину правки: неполный месяц, перерасчёт, скидка")
    with conn.cursor() as cur:
        a = _accrual_head(cur, accrual_id)
        cur.execute(
            "UPDATE app.finance_accrual SET amount_cur = %s, "
            "amount_byn = CASE WHEN currency = 'BYN' THEN %s ELSE amount_byn END, "
            "is_manual = true, manual_reason = %s, updated_by = %s, updated_at = now() "
            "WHERE id = %s", (amount_cur, amount_cur, reason, user_id, accrual_id))
        _recalc(cur, accrual_id)
        _log(cur, user_id, a["org_unit_id"], "finance_accrual_edit",
             f"{a['counterparty']}, №{a['contract_no']} за {a['period_month']:%m.%Y}: "
             f"{a['amount_cur']} → {amount_cur} {a['currency']} — {reason}",
             a["period_month"])
    conn.commit()
    return a["cid"]


# ---------------------------------------------------------------------------
#  Оплаты
# ---------------------------------------------------------------------------

def add_payment(conn: psycopg.Connection, accrual_id: int, paid_on: date,
                amount_byn: Decimal, doc_no: str, note: str | None,
                user_id: int | None) -> int:
    doc_no = (doc_no or "").strip()
    if not doc_no:
        raise ValueError("Укажите номер платёжного поручения")
    if paid_on > date.today():
        raise ValueError("Оплату будущим числом не отмечают")
    with conn.cursor() as cur:
        a = _accrual_head(cur, accrual_id)
        cur.execute(
            "INSERT INTO app.finance_payment (accrual_id, paid_on, amount_byn, doc_no, "
            "note, created_by) VALUES (%s, %s, %s, %s, %s, %s)",
            (accrual_id, paid_on, amount_byn, doc_no, (note or "").strip() or None, user_id))
        _recalc(cur, accrual_id)
        _log(cur, user_id, a["org_unit_id"], "finance_payment",
             f"{a['counterparty']}, №{a['contract_no']} за {a['period_month']:%m.%Y}: "
             f"{amount_byn} BYN, п/п №{doc_no} от {paid_on:%d.%m.%Y}", a["period_month"])
    conn.commit()
    return a["cid"]


def drop_payment(conn: psycopg.Connection, payment_id: int,
                 user_id: int | None) -> int:
    with conn.cursor() as cur:
        cur.execute("SELECT accrual_id, amount_byn, doc_no, paid_on "
                    "  FROM app.finance_payment WHERE id = %s", (payment_id,))
        p = cur.fetchone()
        if p is None:
            raise ValueError("Оплата не найдена — обновите страницу")
        a = _accrual_head(cur, p["accrual_id"])
        cur.execute("DELETE FROM app.finance_payment WHERE id = %s", (payment_id,))
        _recalc(cur, p["accrual_id"])
        _log(cur, user_id, a["org_unit_id"], "finance_payment_drop",
             f"{a['counterparty']}, №{a['contract_no']} за {a['period_month']:%m.%Y}: "
             f"{p['amount_byn']} BYN, п/п №{p['doc_no']} от {p['paid_on']:%d.%m.%Y}",
             a["period_month"])
    conn.commit()
    return a["cid"]


# ---------------------------------------------------------------------------
#  Курсы НБРБ
# ---------------------------------------------------------------------------

def set_rate(conn: psycopg.Connection, currency: str, rate_date: date,
             rate: Decimal, user_id: int | None) -> int:
    """Внести или исправить курс. Возвращает число пересчитанных начислений."""
    if currency not in ("USD", "EUR"):
        raise ValueError("Курс вносится для USD или EUR")
    if rate_date > date.today() + timedelta(days=1):
        raise ValueError("Курс НБРБ объявляется не дальше чем на завтра")
    with conn.cursor() as cur:
        cur.execute(
            """
            INSERT INTO app.finance_rate (currency, rate_date, rate, entered_by)
            VALUES (%s, %s, %s, %s)
            ON CONFLICT (currency, rate_date)
            DO UPDATE SET rate = EXCLUDED.rate, entered_by = EXCLUDED.entered_by,
                          entered_at = now()
            """, (currency, rate_date, rate, user_id))
        cur.execute("SELECT id FROM app.finance_accrual WHERE currency = %s AND rate_date = %s",
                    (currency, rate_date))
        ids = [r["id"] for r in cur.fetchall()]
        for i in ids:
            _recalc(cur, i)
        _log(cur, user_id, None, "finance_rate",
             f"{currency} на {rate_date:%d.%m.%Y}: {rate} BYN"
             + (f"; пересчитано начислений: {len(ids)}" if ids else ""))
    conn.commit()
    return len(ids)


def drop_rate(conn: psycopg.Connection, currency: str, rate_date: date,
              user_id: int | None) -> None:
    with conn.cursor() as cur:
        cur.execute("DELETE FROM app.finance_rate WHERE currency = %s AND rate_date = %s "
                    "RETURNING rate", (currency, rate_date))
        row = cur.fetchone()
        if row is None:
            raise ValueError("Курс не найден — обновите страницу")
        cur.execute("SELECT id FROM app.finance_accrual WHERE currency = %s AND rate_date = %s",
                    (currency, rate_date))
        for r in cur.fetchall():
            _recalc(cur, r["id"])
        _log(cur, user_id, None, "finance_rate_drop",
             f"{currency} на {rate_date:%d.%m.%Y}: {row['rate']} BYN")
    conn.commit()


# ---------------------------------------------------------------------------
#  Сканы
# ---------------------------------------------------------------------------

def add_file(conn: psycopg.Connection, contract_id: int, doc_kind: str,
             doc_date: date | None, file_name: str, data: bytes,
             note: str | None, user_id: int | None) -> None:
    from . import filestore

    if doc_kind not in DOC_KIND_RU:
        raise ValueError("Выберите, что это за документ")
    with conn.cursor() as cur:
        cur.execute("SELECT org_unit_id, counterparty, contract_no "
                    "  FROM app.finance_contract WHERE id = %s", (contract_id,))
        c = cur.fetchone()
        if c is None:
            raise ValueError("Договор не найден — обновите страницу")
    stored = filestore.save(data, "finance", doc_date)
    try:
        with conn.cursor() as cur:
            cur.execute(
                """
                INSERT INTO app.finance_file
                    (contract_id, doc_kind, doc_date, file_name, stored_name,
                     mime_type, size_bytes, sha256, note, uploaded_by)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                """,
                (contract_id, doc_kind, doc_date, (file_name or "скан")[:200],
                 stored.stored_name, stored.mime_type, stored.size_bytes,
                 stored.sha256, (note or "").strip() or None, user_id))
            _log(cur, user_id, c["org_unit_id"], "finance_file",
                 f"{c['counterparty']}, №{c['contract_no']}: {DOC_KIND_RU[doc_kind]} "
                 f"«{file_name}»")
        conn.commit()
    except Exception:
        conn.rollback()
        filestore.remove(stored.stored_name)
        raise


def file_row(conn: psycopg.Connection, file_id: int) -> dict[str, Any] | None:
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM app.finance_file WHERE id = %s", (file_id,))
        return cur.fetchone()


def drop_file(conn: psycopg.Connection, file_id: int, user_id: int | None) -> int:
    from . import filestore

    with conn.cursor() as cur:
        cur.execute(
            "DELETE FROM app.finance_file f USING app.finance_contract c "
            " WHERE f.id = %s AND c.id = f.contract_id "
            "RETURNING f.stored_name, f.file_name, f.contract_id, c.org_unit_id, "
            "          c.counterparty, c.contract_no", (file_id,))
        row = cur.fetchone()
        if row is None:
            raise ValueError("Скан не найден — обновите страницу")
        _log(cur, user_id, row["org_unit_id"], "finance_file_drop",
             f"{row['counterparty']}, №{row['contract_no']}: «{row['file_name']}»")
    conn.commit()
    filestore.remove(row["stored_name"])
    return row["contract_id"]


# ---------------------------------------------------------------------------
#  Сводка и выгрузка
# ---------------------------------------------------------------------------

def summary(conn: psycopg.Connection, first: date, last: date,
            kind: str | None = None, on_day: date | None = None) -> dict[str, Any]:
    """
    Начислено и оплачено по депо и вкладкам за месяцы [first, last] и долг на
    дату `on_day`: всё начисленное со сроком оплаты не позже неё минус всё
    оплаченное не позже неё. Начисления без курса в суммы не входят и
    считаются отдельно.
    """
    day = on_day or date.today()
    first, last = month_start(first), month_start(last)
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT ou.name AS unit_name, c.kind, a.period_month,
                   sum(a.amount_byn) AS accrued,
                   count(*) FILTER (WHERE a.amount_byn IS NULL) AS no_rate,
                   coalesce(sum((SELECT sum(p.amount_byn) FROM app.finance_payment p
                                  WHERE p.accrual_id = a.id)), 0) AS paid
              FROM app.finance_accrual a
              JOIN app.finance_contract c ON c.id = a.contract_id
              JOIN app.org_unit ou ON ou.id = c.org_unit_id
             WHERE a.period_month BETWEEN %(first)s AND %(last)s
               AND (%(kind)s::text IS NULL OR c.kind = %(kind)s)
             GROUP BY ou.name, c.kind, a.period_month
             ORDER BY ou.name, c.kind, a.period_month
            """, {"first": first, "last": last, "kind": kind})
        cells = cur.fetchall()
        cur.execute(
            """
            SELECT ou.name AS unit_name, c.kind,
                   coalesce(sum(a.amount_byn) FILTER (WHERE a.due_date <= %(day)s), 0) AS due,
                   coalesce(sum((SELECT sum(p.amount_byn) FROM app.finance_payment p
                                  WHERE p.accrual_id = a.id AND p.paid_on <= %(day)s)), 0) AS paid
              FROM app.finance_accrual a
              JOIN app.finance_contract c ON c.id = a.contract_id
              JOIN app.org_unit ou ON ou.id = c.org_unit_id
             WHERE (%(kind)s::text IS NULL OR c.kind = %(kind)s)
             GROUP BY ou.name, c.kind
            """, {"day": day, "kind": kind})
        debts = {(r["unit_name"], r["kind"]): r["due"] - r["paid"] for r in cur.fetchall()}

    months = []
    m = first
    while m <= last:
        months.append(m)
        m = add_months(m, 1)
    rows: dict[tuple[str, str], dict[str, Any]] = {}
    for c in cells:
        key = (c["unit_name"], c["kind"])
        row = rows.setdefault(key, {"unit_name": key[0], "kind": key[1],
                                    "kind_ru": KIND_RU[key[1]], "by_month": {},
                                    "accrued": Decimal(0), "paid": Decimal(0),
                                    "no_rate": 0})
        row["by_month"][c["period_month"]] = c
        row["accrued"] += c["accrued"] or 0
        row["paid"] += c["paid"] or 0
        row["no_rate"] += c["no_rate"]
    for key, debt in debts.items():
        if key in rows:
            rows[key]["debt"] = debt
        elif debt:
            rows[key] = {"unit_name": key[0], "kind": key[1], "kind_ru": KIND_RU[key[1]],
                         "by_month": {}, "accrued": Decimal(0), "paid": Decimal(0),
                         "no_rate": 0, "debt": debt}
    out = sorted(rows.values(), key=lambda r: (r["unit_name"], r["kind"]))
    for r in out:
        r.setdefault("debt", Decimal(0))
    totals = {
        "accrued": sum((r["accrued"] for r in out), Decimal(0)),
        "paid": sum((r["paid"] for r in out), Decimal(0)),
        "debt": sum((r["debt"] for r in out), Decimal(0)),
        "no_rate": sum(r["no_rate"] for r in out),
        "by_month": {m: {"accrued": sum((r["by_month"].get(m, {}).get("accrued") or 0
                                         for r in out), Decimal(0)),
                         "paid": sum((r["by_month"].get(m, {}).get("paid") or 0
                                      for r in out), Decimal(0))}
                     for m in months},
    }
    return {"rows": out, "months": months, "totals": totals, "on_day": day,
            "first": first, "last": last, "kind": kind}


def accrual_list(conn: psycopg.Connection, first: date, last: date,
                 kind: str | None = None) -> list[dict[str, Any]]:
    """Все начисления периода построчно — второй лист выгрузки."""
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT ou.name AS unit_name, c.kind, c.counterparty, c.contract_no,
                   c.contract_date, c.subject, a.period_month, a.amount_cur, a.currency,
                   a.rate, a.rate_date, a.amount_byn, a.due_date, a.is_manual,
                   a.manual_reason,
                   coalesce((SELECT sum(p.amount_byn) FROM app.finance_payment p
                              WHERE p.accrual_id = a.id), 0) AS paid,
                   (SELECT string_agg('п/п №' || p.doc_no || ' от ' ||
                                      to_char(p.paid_on, 'DD.MM.YYYY'), '; '
                                      ORDER BY p.paid_on)
                      FROM app.finance_payment p WHERE p.accrual_id = a.id) AS docs
              FROM app.finance_accrual a
              JOIN app.finance_contract c ON c.id = a.contract_id
              JOIN app.org_unit ou ON ou.id = c.org_unit_id
             WHERE a.period_month BETWEEN %(first)s AND %(last)s
               AND (%(kind)s::text IS NULL OR c.kind = %(kind)s)
             ORDER BY a.period_month, ou.name, c.kind, c.counterparty
            """, {"first": month_start(first), "last": month_start(last), "kind": kind})
        return cur.fetchall()


def export_xlsx(path: str, data: dict[str, Any], rows: list[dict[str, Any]]) -> str:
    """Выгрузка сводки: лист «Сводка» по депо и месяцам, лист «Начисления»."""
    from openpyxl import Workbook
    from openpyxl.styles import Alignment, Font, PatternFill
    from openpyxl.utils import get_column_letter

    months_ru = ["янв", "фев", "мар", "апр", "май", "июн", "июл", "авг",
                 "сен", "окт", "ноя", "дек"]
    bold = Font(bold=True)
    head_fill = PatternFill("solid", fgColor="F2E6F7")
    fmt = '#,##0.00'

    wb = Workbook()
    ws = wb.active
    ws.title = "Сводка"
    kind_label = KIND_RU.get(data["kind"], "Аренда и услуги связи")
    ws["A1"] = (f"Финансы — {kind_label}: {data['first']:%m.%Y}–{data['last']:%m.%Y}, "
                f"долг на {data['on_day']:%d.%m.%Y}, BYN")
    ws["A1"].font = Font(bold=True, size=12)
    head = ["Депо", "Вид"]
    for m in data["months"]:
        head += [f"{months_ru[m.month - 1]} {m.year} начислено",
                 f"{months_ru[m.month - 1]} {m.year} оплачено"]
    head += ["Итого начислено", "Итого оплачено", f"Долг на {data['on_day']:%d.%m.%Y}",
             "Без курса, начислений"]
    ws.append([])
    ws.append(head)
    for i, _ in enumerate(head, 1):
        c = ws.cell(row=3, column=i)
        c.font = bold
        c.fill = head_fill
        c.alignment = Alignment(wrap_text=True, vertical="top")
    for r in data["rows"]:
        line = [r["unit_name"], r["kind_ru"]]
        for m in data["months"]:
            cell = r["by_month"].get(m) or {}
            line += [float(cell.get("accrued") or 0), float(cell.get("paid") or 0)]
        line += [float(r["accrued"]), float(r["paid"]), float(r["debt"]), r["no_rate"]]
        ws.append(line)
    total = ["Итого", ""]
    for m in data["months"]:
        t = data["totals"]["by_month"][m]
        total += [float(t["accrued"]), float(t["paid"])]
    total += [float(data["totals"]["accrued"]), float(data["totals"]["paid"]),
              float(data["totals"]["debt"]), data["totals"]["no_rate"]]
    ws.append(total)
    for c in ws[ws.max_row]:
        c.font = bold
    for row in ws.iter_rows(min_row=4, min_col=3, max_col=len(head) - 1):
        for c in row:
            c.number_format = fmt
    ws.column_dimensions["A"].width = 18
    ws.column_dimensions["B"].width = 14
    for i in range(3, len(head) + 1):
        ws.column_dimensions[get_column_letter(i)].width = 14
    ws.freeze_panes = "C4"

    ws2 = wb.create_sheet("Начисления")
    head2 = ["Месяц", "Депо", "Вид", "Контрагент", "Договор", "Предмет", "Сумма",
             "Валюта", "Курс", "Дата курса", "Начислено, BYN", "Срок оплаты",
             "Оплачено, BYN", "Остаток, BYN", "Платёжки", "Правка"]
    ws2.append(head2)
    for c in ws2[1]:
        c.font = bold
        c.fill = head_fill
    for r in rows:
        left = (r["amount_byn"] - r["paid"]) if r["amount_byn"] is not None else None
        ws2.append([
            f"{r['period_month']:%m.%Y}", r["unit_name"], KIND_RU[r["kind"]],
            r["counterparty"], f"№{r['contract_no']} от {r['contract_date']:%d.%m.%Y}",
            r["subject"], float(r["amount_cur"]), r["currency"],
            float(r["rate"]) if r["rate"] is not None else None,
            r["rate_date"], float(r["amount_byn"]) if r["amount_byn"] is not None else "нет курса",
            r["due_date"], float(r["paid"]),
            float(left) if left is not None else None,
            r["docs"] or "", r["manual_reason"] or "",
        ])
    for row in ws2.iter_rows(min_row=2):
        for idx in (6, 10, 12, 13):
            row[idx].number_format = fmt
        for idx in (9, 11):
            row[idx].number_format = "DD.MM.YYYY"
    widths = [9, 16, 13, 28, 24, 28, 12, 8, 9, 11, 14, 11, 13, 13, 30, 28]
    for i, w in enumerate(widths, 1):
        ws2.column_dimensions[get_column_letter(i)].width = w
    ws2.freeze_panes = "A2"
    wb.save(path)
    return path


# ---------------------------------------------------------------------------
#  Для доски Администрации
# ---------------------------------------------------------------------------

def board_items(conn: psycopg.Connection,
                today: date | None = None) -> dict[str, list[dict[str, Any]]]:
    """Три очереди для доски «Минск»: окончание договоров, оплаты, курсы."""
    day = today or date.today()
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT c.id, c.kind, c.counterparty, c.contract_no, c.ends_on,
                   c.auto_renew, ou.name AS unit_name
              FROM app.finance_contract c
              JOIN app.org_unit ou ON ou.id = c.org_unit_id
             WHERE c.closed_on IS NULL AND c.ends_on IS NOT NULL
               AND c.ends_on BETWEEN %s AND %s
             ORDER BY c.ends_on
            """, (day, day + timedelta(days=EXPIRY_LEAD)))
        ending = cur.fetchall()
        cur.execute(
            """
            SELECT a.id, a.contract_id, a.period_month, a.due_date, a.amount_byn,
                   a.amount_cur, a.currency, c.kind, c.counterparty, c.contract_no,
                   ou.name AS unit_name,
                   coalesce((SELECT sum(p.amount_byn) FROM app.finance_payment p
                              WHERE p.accrual_id = a.id), 0) AS paid
              FROM app.finance_accrual a
              JOIN app.finance_contract c ON c.id = a.contract_id
              JOIN app.org_unit ou ON ou.id = c.org_unit_id
             WHERE a.due_date <= %s AND a.amount_cur > 0
             ORDER BY a.due_date
            """, (day,))
        unpaid = [r for r in cur.fetchall()
                  if (r["amount_byn"] is None and r["paid"] == 0)
                  or (r["amount_byn"] is not None and r["paid"] < r["amount_byn"])]
    return {"ending": ending, "unpaid": unpaid, "rates": missing_rates(conn, day)}
