"""Чтение данных пользователей для админки (Блок 4.3).

Баланс — контракт ``SUM(bot.money.amount)``; яички — ``SUM(bot.eggs.amount)``.
Подписка на рассылки — отсутствие строки в ``bot.unsubscribe``.
"""

from __future__ import annotations

from typing import Any

from common.db.pool import get_pool

#: Общая часть выборки: агрегаты по сообщениям, деньгам, яичкам и очищениям.
_FROM = """
    FROM bot.tg_users AS tg
        LEFT JOIN bot.de_users AS du ON du.id_user = tg.id_user
        LEFT JOIN bot.titles AS t ON t.id_title = du.id_title
        LEFT JOIN (
            SELECT user_id, sum(count) AS cnt, max(day) AS last_dt
            FROM analytics.messages_daily GROUP BY user_id
        ) AS msg ON msg.user_id = tg.id_user
        LEFT JOIN (
            SELECT id_user, sum(amount) AS balance
            FROM bot.money GROUP BY id_user
        ) AS money ON money.id_user = tg.id_user
        LEFT JOIN (
            SELECT id_user, sum(amount) AS eggs
            FROM bot.eggs GROUP BY id_user
        ) AS eggs ON eggs.id_user = tg.id_user
        LEFT JOIN (
            SELECT id_user, count(*) AS cnt, max(dt) AS last_dt
            FROM bot.purifications GROUP BY id_user
        ) AS pur ON pur.id_user = tg.id_user
        LEFT JOIN bot.unsubscribe AS unsub ON unsub.id_user = tg.id_user
"""

_SELECT = """
    SELECT tg.id_user, tg.username, tg.first_name, tg.last_name,
           du.nick, du.lvl, du.expr, du.expr_streak, du.dt_create,
           du.id_title,
           t.title,
           coalesce(msg.cnt, 0) AS msg_count,
           msg.last_dt AS last_message_at,
           coalesce(money.balance, 0) AS balance,
           coalesce(eggs.eggs, 0) AS eggs,
           coalesce(pur.cnt, 0) AS purification_count,
           pur.last_dt AS last_purification_at,
           (unsub.id_user IS NULL) AS subscribed
"""

#: Колонки, по которым идёт поиск (в порядке подстановки параметров).
_SEARCH_COLUMNS = ("tg.username", "tg.first_name", "tg.last_name", "du.nick")


def _search_clause(term: str) -> tuple[str, list[Any]]:
    """WHERE по нику, username, имени и Telegram-ID."""
    term = term.strip()
    if not term:
        return "", []

    like = f"%{term}%"
    clauses = [f"{column} ILIKE %s" for column in _SEARCH_COLUMNS]
    params: list[Any] = [like] * len(_SEARCH_COLUMNS)

    telegram_id = term.lstrip("@")
    if telegram_id.isdigit():
        clauses.append("tg.id_user = %s")
        params.append(int(telegram_id))

    return " WHERE (" + " OR ".join(clauses) + ")", params


async def count_users(search: str = "") -> int:
    """Сколько пользователей подходит под поиск."""
    where, params = _search_clause(search)
    async with get_pool().connection() as conn:
        cur = await conn.execute("SELECT count(*) AS total " + _FROM + where, tuple(params))
        row = await cur.fetchone()
    return int(row["total"]) if row else 0


async def list_users(search: str = "", *, limit: int = 25, offset: int = 0) -> list[dict[str, Any]]:
    """Страница списка пользователей (с поиском)."""
    where, params = _search_clause(search)
    async with get_pool().connection() as conn:
        cur = await conn.execute(
            _SELECT + _FROM + where + " ORDER BY du.nick NULLS LAST, tg.id_user LIMIT %s OFFSET %s",
            (*params, limit, offset),
        )
        return await cur.fetchall()


async def get_user(user_id: int) -> dict[str, Any] | None:
    """Карточка пользователя со сводными показателями."""
    async with get_pool().connection() as conn:
        cur = await conn.execute(_SELECT + _FROM + " WHERE tg.id_user = %s", (user_id,))
        return await cur.fetchone()


async def money_history(user_id: int, *, limit: int = 10) -> list[dict[str, Any]]:
    """Последние операции по балансу с названием действия."""
    async with get_pool().connection() as conn:
        cur = await conn.execute(
            """
            SELECT m.id_money, m.amount, m.dt_action, a.title AS action
            FROM bot.money AS m
                LEFT JOIN bot.actions AS a ON a.id_action = m.id_action
            WHERE m.id_user = %s
            ORDER BY m.dt_action DESC, m.id_money DESC
            LIMIT %s
            """,
            (user_id, limit),
        )
        return await cur.fetchall()


# --- Управление пользователем (Блок 4.3) -------------------------------------

#: Действие «Корректировка админом» из V18 (ручное изменение баланса).
ADMIN_ACTION_ID = 6

#: Ограничение длины ника (как в команде /ник).
MAX_NICK_LENGTH = 32

#: Ограничение длины комментария к корректировке баланса.
MAX_COMMENT_LENGTH = 200


async def list_titles() -> list[dict[str, Any]]:
    """Доступные звания для выбора в карточке."""
    async with get_pool().connection() as conn:
        cur = await conn.execute("SELECT id_title, title FROM bot.titles WHERE visible ORDER BY price DESC, id_title")
        return await cur.fetchall()


async def user_history(user_id: int, *, limit: int = 20) -> list[dict[str, Any]]:
    """История ручных изменений пользователя."""
    async with get_pool().connection() as conn:
        cur = await conn.execute(
            """
            SELECT id, field, old_value, new_value, comment, editor_id, source, changed_at
            FROM bot.user_changes
            WHERE id_user = %s
            ORDER BY changed_at DESC
            LIMIT %s
            """,
            (user_id, limit),
        )
        return await cur.fetchall()


async def _log_change(
    conn: Any,
    user_id: int,
    field: str,
    old_value: str | None,
    new_value: str | None,
    editor_id: int | None,
    comment: str | None = None,
    source: str = "admin",
) -> None:
    await conn.execute(
        """
        INSERT INTO bot.user_changes (id_user, field, old_value, new_value, comment, editor_id, source)
        VALUES (%s, %s, %s, %s, %s, %s, %s)
        """,
        (user_id, field, old_value, new_value, comment, editor_id, source),
    )


async def set_nick(user_id: int, nick: str, *, editor_id: int | None = None) -> bool:
    """Сменить ник (создаёт запись de_users, если её ещё нет)."""
    async with get_pool().connection() as conn:
        cur = await conn.execute("SELECT nick FROM bot.de_users WHERE id_user = %s FOR UPDATE", (user_id,))
        row = await cur.fetchone()

        if row is None:
            await conn.execute("INSERT INTO bot.de_users (id_user, nick) VALUES (%s, %s)", (user_id, nick))
            old_nick = None
        else:
            old_nick = row["nick"]
            if old_nick == nick:
                return True
            await conn.execute("UPDATE bot.de_users SET nick = %s WHERE id_user = %s", (nick, user_id))

        await _log_change(conn, user_id, "nick", old_nick, nick, editor_id)
        return True


async def set_title(user_id: int, title_id: int, *, editor_id: int | None = None) -> bool:
    """Сменить звание пользователя. False — если пользователя или звания нет."""
    async with get_pool().connection() as conn:
        cur = await conn.execute(
            """
            SELECT du.id_title, coalesce(t.title, '') AS title
            FROM bot.de_users AS du
                LEFT JOIN bot.titles AS t ON t.id_title = du.id_title
            WHERE du.id_user = %s
            FOR UPDATE OF du
            """,
            (user_id,),
        )
        current = await cur.fetchone()
        if current is None:
            return False

        cur = await conn.execute("SELECT title FROM bot.titles WHERE id_title = %s", (title_id,))
        title = await cur.fetchone()
        if title is None:
            return False

        if current["id_title"] == title_id:
            return True

        await conn.execute("UPDATE bot.de_users SET id_title = %s WHERE id_user = %s", (title_id, user_id))
        await _log_change(conn, user_id, "title", current["title"], title["title"], editor_id)
        return True


async def set_subscription(user_id: int, subscribed: bool, *, editor_id: int | None = None) -> bool:
    """Подписать или отписать пользователя от рассылок."""
    async with get_pool().connection() as conn:
        cur = await conn.execute("SELECT 1 FROM bot.tg_users WHERE id_user = %s", (user_id,))
        if await cur.fetchone() is None:
            return False

        cur = await conn.execute("SELECT 1 FROM bot.unsubscribe WHERE id_user = %s", (user_id,))
        was_subscribed = await cur.fetchone() is None
        if was_subscribed == subscribed:
            return True

        if subscribed:
            await conn.execute("DELETE FROM bot.unsubscribe WHERE id_user = %s", (user_id,))
        else:
            await conn.execute(
                "INSERT INTO bot.unsubscribe (id_user) VALUES (%s) ON CONFLICT DO NOTHING",
                (user_id,),
            )

        await _log_change(
            conn,
            user_id,
            "subscription",
            "да" if was_subscribed else "нет",
            "да" if subscribed else "нет",
            editor_id,
        )
        return True


async def adjust_balance(
    user_id: int,
    amount: int,
    reason: str,
    *,
    editor_id: int | None = None,
) -> int | None:
    """Изменить баланс записью в money. Возвращает новый баланс или None."""
    async with get_pool().connection() as conn:
        cur = await conn.execute("SELECT 1 FROM bot.tg_users WHERE id_user = %s", (user_id,))
        if await cur.fetchone() is None:
            return None

        cur = await conn.execute(
            "SELECT coalesce(sum(amount), 0) AS balance FROM bot.money WHERE id_user = %s", (user_id,)
        )
        row = await cur.fetchone()
        old_balance = int(row["balance"])

        await conn.execute(
            "INSERT INTO bot.money (id_user, amount, id_action) VALUES (%s, %s, %s)",
            (user_id, amount, ADMIN_ACTION_ID),
        )
        new_balance = old_balance + amount
        await _log_change(
            conn,
            user_id,
            "balance",
            str(old_balance),
            str(new_balance),
            editor_id,
            comment=reason,
        )
        return new_balance