Files
filwordspl-backend/routers/admin_users.py

242 lines
8.3 KiB
Python
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
"""
Admin Users Router — управление пользователями.
Доступ: только для ADMIN_IDS.
Эндпоинты:
- GET /admin/users?page=1&limit=50&level=&lang=&q=&active_24h= — список
- GET /admin/users/stats — сводка по уровням/языкам
🔧 ФИКС (30.09.2026): total_score = сумма 4 игр (wordsearch + sentences + quiz + bubbles).
"""
import logging
from typing import Optional
from fastapi import APIRouter, Request, HTTPException
from auth import require_auth
from database import get_pool
from config import ADMIN_IDS
logger = logging.getLogger(__name__)
router = APIRouter(prefix="/admin/users", tags=["admin-users"])
LEVELS = ["A1", "A2", "B1", "B2", "C1", "C2"]
LANGUAGES = ["pl", "ru", "uk", "en"]
def _require_admin(telegram_id: int) -> None:
if telegram_id not in ADMIN_IDS:
raise HTTPException(403, "Access denied")
@router.get("")
async def list_users(
request: Request,
page: int = 1,
limit: int = 50,
level: Optional[str] = None,
lang: Optional[str] = None,
q: Optional[str] = None,
active_24h: bool = False,
):
"""
🔧 НОВОЕ (30.09.2026): список пользователей с пагинацией.
Возвращает:
{
"total": 15,
"page": 1,
"limit": 50,
"pages": 1,
"users": [
{
"telegram_id": 123,
"username": "user",
"app_language": "ru",
"current_level": "A1",
"last_completed_level": 5,
"last_completed_by_level": {"A1": 5, "A2": 3},
"created_at": "...",
"last_login": "...",
"total_score": 1234,
"wordsearch_completed": 5,
"sentence_completed": 10,
"quiz_guessed": 20,
"bubble_completed": 8,
"levels_completed": 5
}
]
}
"""
telegram_id, _ = await require_auth(request)
_require_admin(telegram_id)
if page < 1:
page = 1
if limit < 1:
limit = 1
elif limit > 200:
limit = 200
offset = (page - 1) * limit
pool = await get_pool()
async with pool.acquire() as conn:
conditions = []
params: list = []
idx = 1
if level and level in LEVELS:
conditions.append(f"u.current_level = ${idx}")
params.append(level)
idx += 1
if lang and lang in LANGUAGES:
conditions.append(f"u.app_language = ${idx}")
params.append(lang)
idx += 1
if q and q.strip():
conditions.append(f"""(
CAST(u.telegram_id AS TEXT) ILIKE ${idx} OR
u.username ILIKE ${idx}
)""")
params.append(f"%{q.strip()}%")
idx += 1
if active_24h:
conditions.append(f"""
EXISTS (
SELECT 1 FROM daily_logins dl
WHERE dl.user_id = u.telegram_id
AND dl.login_date >= CURRENT_DATE - INTERVAL '1 day'
)
""")
where = ("WHERE " + " AND ".join(conditions)) if conditions else ""
# Total
total = await conn.fetchval(f"""
SELECT COUNT(*) FROM users u {where}
""", *params) or 0
# Page с агрегатами
rows = await conn.fetch(f"""
SELECT
u.telegram_id,
u.username,
u.app_language,
u.current_level,
u.last_completed_level,
u.last_completed_by_level,
u.created_at,
(SELECT MAX(login_date) FROM daily_logins WHERE user_id = u.telegram_id) AS last_login,
-- 🔧 ФИКС (30.09.2026): total_score теперь = сумма 4 игр
COALESCE((SELECT SUM(score) FROM user_progress
WHERE user_id = u.telegram_id AND completed_at IS NOT NULL), 0)
+ COALESCE((SELECT SUM(score) FROM sentence_progress
WHERE user_id = u.telegram_id AND completed_at IS NOT NULL), 0)
+ COALESCE((SELECT total_score FROM word_quiz_progress
WHERE user_id = u.telegram_id), 0)
+ COALESCE((SELECT total_score FROM bubble_words_progress
WHERE user_id = u.telegram_id), 0) AS total_score,
-- Уровни по играм
COALESCE((SELECT COUNT(*) FROM user_progress
WHERE user_id = u.telegram_id AND completed_at IS NOT NULL), 0) AS wordsearch_completed,
COALESCE((SELECT COUNT(*) FROM sentence_progress
WHERE user_id = u.telegram_id AND completed_at IS NOT NULL), 0) AS sentence_completed,
COALESCE((SELECT words_guessed FROM word_quiz_progress
WHERE user_id = u.telegram_id), 0) AS quiz_guessed,
COALESCE((SELECT levels_completed FROM bubble_words_progress
WHERE user_id = u.telegram_id), 0) AS bubble_completed
FROM users u
{where}
ORDER BY u.created_at DESC
LIMIT ${idx} OFFSET ${idx + 1}
""", *params, limit, offset)
pages = (total + limit - 1) // limit if limit > 0 else 1
return {
"total": total,
"page": page,
"limit": limit,
"pages": pages,
"users": [
{
"telegram_id": r["telegram_id"],
"username": r["username"],
"app_language": r["app_language"] or "pl",
"current_level": r["current_level"] or "A1",
"last_completed_level": r["last_completed_level"] or 0,
"last_completed_by_level": r["last_completed_by_level"] or {},
"created_at": r["created_at"].isoformat() if r["created_at"] else None,
"last_login": r["last_login"].isoformat() if r["last_login"] else None,
"total_score": r["total_score"] or 0,
"wordsearch_completed": r["wordsearch_completed"] or 0,
"sentence_completed": r["sentence_completed"] or 0,
"quiz_guessed": r["quiz_guessed"] or 0,
"bubble_completed": r["bubble_completed"] or 0,
"levels_completed": r["wordsearch_completed"] or 0, # backward compat
}
for r in rows
],
}
@router.get("/stats")
async def users_stats(request: Request):
"""
Сводка: пользователи по уровням и языкам.
"""
telegram_id, _ = await require_auth(request)
_require_admin(telegram_id)
pool = await get_pool()
async with pool.acquire() as conn:
# По уровням
by_level_rows = await conn.fetch("""
SELECT COALESCE(current_level, 'A1') AS level, COUNT(*) AS cnt
FROM users
GROUP BY current_level
ORDER BY
CASE current_level
WHEN 'A1' THEN 1 WHEN 'A2' THEN 2
WHEN 'B1' THEN 3 WHEN 'B2' THEN 4
WHEN 'C1' THEN 5 WHEN 'C2' THEN 6
ELSE 7
END
""")
by_level = {r["level"]: r["cnt"] for r in by_level_rows}
# По языкам
by_lang_rows = await conn.fetch("""
SELECT COALESCE(app_language, 'pl') AS lang, COUNT(*) AS cnt
FROM users
GROUP BY app_language
ORDER BY cnt DESC
""")
by_lang = {r["lang"]: r["cnt"] for r in by_lang_rows}
# Всего
total = await conn.fetchval("SELECT COUNT(*) FROM users") or 0
# Активных за 24ч
active_24h = await conn.fetchval("""
SELECT COUNT(DISTINCT user_id) FROM daily_logins
WHERE login_date >= CURRENT_DATE - INTERVAL '1 day'
""") or 0
# Новых сегодня
new_today = await conn.fetchval("""
SELECT COUNT(*) FROM users WHERE created_at >= CURRENT_DATE
""") or 0
return {
"total": total,
"by_level": by_level,
"by_lang": by_lang,
"active_24h": active_24h,
"new_today": new_today,
}