"""
Кабинеты ОТ и Руководства — каркас (вычитка LOGIKA_PROSTO, волна 4в).

Подробные требования заказчик ещё не собрал, поэтому оба кабинета собраны
из данных, которые в приложении уже есть: ОТ — допуски людей по всем депо
(медосмотры, инструктажи, удостоверения), Руководство — численность,
аренда и здоровье очередей. Что́ заказчик попросит — ляжет на те же
страницы.
"""

from __future__ import annotations

from datetime import date
from typing import Any

import psycopg

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

#  Три вида допусков из карточки: медосмотр, инструктаж, удостоверение.
#  Один запрос на все три — считаем по последней записи вида, как доска
#  задач (комментарий kanbandb._people_deadlines). Колонки дат у видов
#  разные: passed_at / held_at / issued_at
PERMIT_SOURCES = [
    ("medical", "медосмотр", "app.employee_medical_exam", "valid_until",
     "passed_at"),
    ("briefing", "инструктаж", "app.safety_briefing", "valid_until",
     "held_at"),
    ("certificate", "удостоверение", "app.employee_certificate",
     "valid_until", "issued_at"),
]
PERMIT_KIND_RU = {code: ru for code, ru, *_ in PERMIT_SOURCES}


def has_role_tables(conn: psycopg.Connection) -> bool:
    """Накатана ли миграция 100 — без неё разделы открываются с пояснением."""
    with conn.cursor() as cur:
        cur.execute("SELECT count(*) AS n FROM app.role "
                    "WHERE code IN ('ot', 'management')")
        return bool(cur.fetchone()["n"])


def ot_units(conn: psycopg.Connection) -> list[dict[str, Any]]:
    """
    Сводка охраны труда по депо: сколько людей и у скольких просрочен или
    скоро истекает каждый из трёх допусков.
    """
    with conn.cursor() as cur:
        sub = []
        for code, _, table, col, when in PERMIT_SOURCES:
            sub.append(f"""
            (SELECT count(*) FROM (
                SELECT DISTINCT ON (em.employee_id) em.employee_id, x.{col} AS due
                  FROM {table} x
                  JOIN app.employment em ON em.employee_id = x.employee_id
                 WHERE em.org_unit_id = ou.id
                   AND (em.terminated_at IS NULL OR em.terminated_at > current_date)
                 ORDER BY em.employee_id, x.{when} DESC NULLS LAST, x.id DESC
             ) last WHERE last.due IS NOT NULL AND last.due < current_date
            ) AS over_{code},
            (SELECT count(*) FROM (
                SELECT DISTINCT ON (em.employee_id) em.employee_id, x.{col} AS due
                  FROM {table} x
                  JOIN app.employment em ON em.employee_id = x.employee_id
                 WHERE em.org_unit_id = ou.id
                   AND (em.terminated_at IS NULL OR em.terminated_at > current_date)
                 ORDER BY em.employee_id, x.{when} DESC NULLS LAST, x.id DESC
             ) soon WHERE soon.due BETWEEN current_date AND current_date + 30
            ) AS soon_{code}
            """)
        cur.execute(f"""
            SELECT ou.id, ou.name, ou.code,
                   (SELECT count(*) FROM app.employment em
                     WHERE em.org_unit_id = ou.id
                       AND (em.terminated_at IS NULL
                            OR em.terminated_at > current_date)) AS people,
                   {', '.join(sub)}
            FROM app.org_unit ou
            WHERE ou.kind = 'rc' AND ou.is_active
            ORDER BY ou.name
        """)
        rows = cur.fetchall()
    for r in rows:
        r["overdue"] = sum(int(r[f"over_{c}"]) for c, *_ in PERMIT_SOURCES)
        r["soon"] = sum(int(r[f"soon_{c}"]) for c, *_ in PERMIT_SOURCES)
    return rows


def ot_people(conn: psycopg.Connection, unit_id: int | None = None,
              limit: int = 100) -> list[dict[str, Any]]:
    """
    Люди с просроченным или истекающим допуском — кто именно и каким
    допуском. Отсюда ссылка в карточку: разговор о допусках начинается с
    человека, а не с депо.
    """
    with conn.cursor() as cur:
        parts = []
        for code, ru, table, col, when in PERMIT_SOURCES:
            unit_filter = ("AND em.org_unit_id = %(unit)s" if unit_id else "")
            parts.append(f"""
            SELECT DISTINCT ON (em.employee_id) '{code}' AS kind,
                   em.employee_id, em.id AS employment_id, em.org_unit_id,
                   ou.name AS unit_name, ou.code AS unit_code,
                   {FIO} AS fio, p.position_name, last.due
              FROM (SELECT employee_id, {col} AS due, {when} AS held
                      FROM {table}
                     WHERE {col} IS NOT NULL) last
              JOIN app.employment em ON em.employee_id = last.employee_id
              JOIN app.employee e ON e.id = em.employee_id
              JOIN app.org_unit ou ON ou.id = em.org_unit_id
              LEFT JOIN LATERAL (
                  SELECT p2.name AS position_name
                    FROM app.employment em2
                    JOIN app.position p2 ON p2.id = em2.position_id
                   WHERE em2.employee_id = em.employee_id
                     AND (em2.terminated_at IS NULL
                          OR em2.terminated_at > current_date)
                   ORDER BY em2.id DESC LIMIT 1) p ON true
             WHERE (em.terminated_at IS NULL OR em.terminated_at > current_date)
               AND last.due < current_date + 30
               {unit_filter}
             ORDER BY em.employee_id, last.held DESC NULLS LAST
            """)
        params: dict[str, Any] = {"lim": limit}
        if unit_id:
            params["unit"] = unit_id
        #  каждая часть несёт свой ORDER BY — в UNION такие ветки берут
        #  в скобки, иначе парсер видит ORDER BY у всего объединения
        cur.execute(" SELECT * FROM (" +
                    " UNION ALL ".join(f"({p})" for p in parts) +
                    ") all_rows ORDER BY due, fio LIMIT %(lim)s",
                    params)
        return cur.fetchall()


def board_units(conn: psycopg.Connection) -> list[dict[str, Any]]:
    """
    Метрики Руководства по депо: численность, аренда в месяц, долг по
    аренде и открытые задачи доски. Каркас: когда заказчик соберёт
    требования, здесь лягут его метрики.
    """
    with conn.cursor() as cur:
        cur.execute("""
            SELECT ou.id, ou.name, ou.code,
                   (SELECT count(*) FROM app.employment em
                     WHERE em.org_unit_id = ou.id
                       AND (em.terminated_at IS NULL
                            OR em.terminated_at > current_date)) AS people,
                   coalesce((SELECT sum(a.amount_byn)
                     FROM app.finance_accrual a
                     JOIN app.finance_contract c ON c.id = a.contract_id
                    WHERE c.org_unit_id = ou.id
                      AND a.period_month = date_trunc('month',
                                                      current_date)), 0
                   ) AS rent_month,
                   coalesce((SELECT sum(greatest(a.amount_byn
                              - (SELECT coalesce(sum(p.amount_byn), 0)
                                   FROM app.finance_payment p
                                  WHERE p.accrual_id = a.id), 0))
                     FROM app.finance_accrual a
                     JOIN app.finance_contract c ON c.id = a.contract_id
                    WHERE c.org_unit_id = ou.id
                      AND a.due_date < current_date), 0) AS rent_debt,
                   (SELECT count(*) FROM app.kanban_task k
                    WHERE k.org_unit_id = ou.id AND k.status = 'open'
                      AND k.week_start = date_trunc('week', current_date)::date
                   ) AS tasks_open
            FROM app.org_unit ou
            WHERE ou.kind = 'rc' AND ou.is_active
            ORDER BY ou.name
        """)
        return cur.fetchall()


def board_totals(conn: psycopg.Connection) -> dict[str, Any]:
    """Общие цифры отдела: люди, аренда, незакрытые задачи."""
    rows = board_units(conn)
    return {
        "units": rows,
        "people": sum(int(r["people"]) for r in rows),
        "rent_month": sum(float(r["rent_month"] or 0) for r in rows),
        "rent_debt": sum(float(r["rent_debt"] or 0) for r in rows),
        "tasks_open": sum(int(r["tasks_open"] or 0) for r in rows),
    }
