Files
filwordspl-backend/routers/sentences.py
Drobysh 84927b063f feat: XP foundation + waves + featured game + profile + leaderboard by total_xp
- services/xp.py: award_xp, level_for, RANKS (48), featured_game
- services/waves.py: wave_plan, grid_size_for, wave_medal
- routers/featured.py: GET /featured (игра дня ×1.5)
- routers/users.py: POST /set-nickname, POST /set-avatar
- routers/app_auth.py: /auth/me с XP-полями (level, rank_key, rank_name, xp)
- routers/game.py: award_xp + 3 штрафа + POST /game/next (волны)
- routers/wordquiz.py: award_xp + серии в choose_progress
- routers/bubblewords.py: award_xp + 4 штрафа
- routers/sentences.py: award_xp по длине предложения
- routers/leaderboard.py: сортировка по total_xp, XP-поля в ответе
- database.py: миграции user_profile, xp_log, choose_progress, user_seen_levels, user_game_progress, wave_results, bubble_levels, user_word_stats
- main.py: подключение routers/featured

Проверено:
- /auth/me отдаёт level=1, rank_00, xp_in_level
- /featured отдаёт wordsearch + seconds_left
- /game/complete-level начисляет XP через award_xp
- xp_log, user_profile заполняются
- /game/next выдаёт уровень по волнам
- /leaderboard сортирует по total_xp
2026-10-09 08:12:19 +00:00

342 lines
14 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.
import csv
import random
from fastapi import APIRouter, Request, HTTPException
from auth import require_auth
from database import get_pool
router = APIRouter(prefix="/sentences", tags=["sentences"])
@router.get("/{sentence_id}/hint")
async def get_sentence_hint(sentence_id: int, request: Request):
"""Получить правильное предложение в качестве подсказки (одноразово)"""
telegram_id, _ = await require_auth(request)
pool = await get_pool()
async with pool.acquire() as conn:
row = await conn.fetchrow(
"SELECT sentence_pl FROM sentences WHERE id = $1", sentence_id
)
if not row:
raise HTTPException(404, "Sentence not found")
# 🔧 ФИКС (24.09.2026): sentence_hint_no_limit — лимит 1 раз на предложение.
already_used = await conn.fetchval(
"SELECT 1 FROM sentence_hints_used WHERE user_id = $1 AND sentence_id = $2",
telegram_id, sentence_id
)
if already_used:
raise HTTPException(403, "Hint already used for this sentence")
# Записываем использование
await conn.execute("""
INSERT INTO sentence_hints_used (user_id, sentence_id)
VALUES ($1, $2)
ON CONFLICT (user_id, sentence_id) DO NOTHING
""", telegram_id, sentence_id)
return {
"sentence_pl": row["sentence_pl"],
}
def load_sentences_from_csv(filename):
"""Загружает предложения из CSV файла (формат: polish,english,russian,ukrainian)."""
sentences = []
with open(filename, 'r', encoding='utf-8') as f:
reader = csv.DictReader(f)
for row in reader:
sentences.append({
"sentence_pl": row["polish"].strip(),
"translation_ru": row.get("russian", ""),
"translation_uk": row.get("ukrainian", ""),
"translation_en": row.get("english", ""),
})
return sentences
async def init_sentences():
"""Инициализирует таблицу предложений из CSV файлов по уровням."""
pool = await get_pool()
async with pool.acquire() as conn:
level_files = [
("A1", "sentences_a1.csv"),
("A2", "sentences_a2.csv"),
("B1", "sentences_b1.csv"),
]
for level, filename in level_files:
sentences = load_sentences_from_csv(filename)
for i, s in enumerate(sentences):
words_count = len(s["sentence_pl"].split())
difficulty = 1 if words_count <= 4 else 2 if words_count <= 6 else 3
await conn.execute(
"""INSERT INTO sentences (sentence_number, sentence_pl, translation_ru, translation_uk, translation_en, difficulty, level)
VALUES ($1, $2, $3, $4, $5, $6, $7)
ON CONFLICT (level, sentence_number) DO NOTHING""",
i + 1, s["sentence_pl"],
s["translation_ru"], s["translation_uk"], s["translation_en"],
difficulty, level
)
def normalize_word_for_display(word: str) -> str:
"""Убирает знаки конца предложения и приводит к нижнему регистру.
Запятые, точки с запятой, двоеточия и кавычки ОСТАЮТСЯ — игрок должен их видеть."""
return word.strip('.!?…').lower()
def normalize_word_for_check(word: str) -> str:
"""Убирает ВСЮ пунктуацию для сравнения — игрок не обязан расставлять запятые."""
return word.strip('.,!?;:()[]{}"\'-…').lower()
@router.get("/next")
async def get_next_sentence(request: Request):
"""Получить следующее непройденное предложение для выбранного уровня"""
telegram_id, _ = await require_auth(request)
pool = await get_pool()
async with pool.acquire() as conn:
user = await conn.fetchrow(
"SELECT app_language, current_level FROM users WHERE telegram_id = $1", telegram_id
)
if not user:
raise HTTPException(401, "User not registered")
lang = user["app_language"] or "pl"
current_level = user["current_level"] or "A1"
# Находим случайное непройденное предложение нужного уровня
row = await conn.fetchrow("""
SELECT s.* FROM sentences s
WHERE s.level = $1
AND s.id NOT IN (
SELECT sentence_id FROM sentence_progress WHERE user_id = $2
)
ORDER BY RANDOM()
LIMIT 1
""", current_level, telegram_id)
if not row:
return {"completed": True, "message": "All sentences completed!"}
# Оригинальные слова (для проверки)
correct_words = row["sentence_pl"].split()
# Слова для ОТОБРАЖЕНИЯ — запятые остаются, убираем только точки/воскл/вопрос
display_words = [normalize_word_for_display(w) for w in correct_words]
shuffled_words = display_words.copy()
random.shuffle(shuffled_words)
translation_field = f"translation_{lang}"
translation = row.get(translation_field, "") or row.get("translation_en", "")
return {
"id": row["id"],
"sentence_number": row["sentence_number"],
"words": shuffled_words,
"correct_words": display_words,
"translation": translation,
"difficulty": row["difficulty"],
"level": row["level"],
}
@router.post("/check")
async def check_sentence(request: Request):
"""Проверка предложения с защитой от повторного прохождения и выдачей достижений"""
telegram_id, _ = await require_auth(request)
body = await request.json()
sentence_id = body.get("sentence_id")
user_words = body.get("words", [])
completion_time = body.get("completion_time")
if not sentence_id or not user_words:
raise HTTPException(400, "sentence_id and words required")
if len(user_words) > 20:
raise HTTPException(400, "Too many words")
pool = await get_pool()
# 🔧 НОВОЕ (08.10.2026): XP-система — результат award_xp.
xp_result = None
async with pool.acquire() as conn:
user_exists = await conn.fetchval(
"SELECT 1 FROM users WHERE telegram_id = $1", telegram_id
)
if not user_exists:
raise HTTPException(401, "User not registered")
row = await conn.fetchrow(
"SELECT id, sentence_pl, level FROM sentences WHERE id = $1", sentence_id
)
if not row:
raise HTTPException(404, "Sentence not found")
existing_progress = await conn.fetchrow(
"SELECT score, completed_at FROM sentence_progress WHERE user_id = $1 AND sentence_id = $2",
telegram_id, sentence_id
)
if existing_progress:
return {
"correct": True,
"correct_words": [normalize_word_for_display(w) for w in row["sentence_pl"].split()],
"already_completed": True,
"score": existing_progress["score"],
"completed_at": existing_progress["completed_at"].isoformat() if existing_progress["completed_at"] else None,
"new_achievements": []
}
# Защита от читерства: минимальное время 3 секунды
if completion_time is not None and completion_time < 3:
return {
"correct": False,
"correct_words": [normalize_word_for_display(w) for w in row["sentence_pl"].split()],
"already_completed": False,
"new_achievements": [],
"error": "Too fast. Please take your time."
}
# Нормализуем для СРАВНЕНИЯ — без запятых и прочей пунктуации
correct_words_original = row["sentence_pl"].split()
correct_words_normalized = [normalize_word_for_check(w) for w in correct_words_original]
user_words_normalized = [normalize_word_for_check(w) for w in user_words]
is_correct = user_words_normalized == correct_words_normalized
if is_correct:
word_count = len(correct_words_original)
# 🆕 Расчёт звёзд для "Составь предложение"
# Старт 5. Штрафы:
# -1 за каждую неверную проверку (клиент шлёт wrong_checks)
# -1 если time > лимита (по длине: 3-4 → 30с, 5-6 → 45с, 7-8 → 60с)
wrong_checks = int(body.get("wrong_checks", 0) or 0)
if word_count <= 4:
time_limit = 30
elif word_count <= 6:
time_limit = 45
else:
time_limit = 60
time_penalty = 1 if (completion_time and completion_time > time_limit) else 0
wrong_penalty = min(max(0, wrong_checks), 3)
stars = max(1, 5 - wrong_penalty - time_penalty)
# Начисляем XP
from services.xp import award_xp
xp_result = await award_xp(conn, telegram_id, "sentence", stars)
score = xp_result["points"]
await conn.execute(
"""INSERT INTO sentence_progress (user_id, sentence_id, completed_at, score, level)
VALUES ($1, $2, NOW(), $3, $4)""",
telegram_id, sentence_id, score, row["level"],
)
# 🔧 ФИКС (23.09.2026): achievements_nested_pool_acquire_sentences —
# раньше check_and_award_achievements вызывался ВНУТРИ async with pool.acquire(),
# что могло привести к дедлоку пула. Теперь собираем данные внутри блока,
# а сами вызовы делаем ПОСЛЕ выхода (паттерн из game.py).
sentences_completed = await conn.fetchval(
"SELECT COUNT(*) FROM sentence_progress WHERE user_id = $1",
telegram_id
)
wordsearch_score = await conn.fetchval(
"SELECT COALESCE(SUM(score), 0) FROM user_progress WHERE user_id = $1 AND completed_at IS NOT NULL",
telegram_id
) or 0
sentence_score = await conn.fetchval(
"SELECT COALESCE(SUM(score), 0) FROM sentence_progress WHERE user_id = $1 AND completed_at IS NOT NULL",
telegram_id
) or 0
wordquiz_score = await conn.fetchval(
"SELECT COALESCE(total_score, 0) FROM word_quiz_progress WHERE user_id = $1",
telegram_id
) or 0
bubblewords_score = await conn.fetchval(
"SELECT COALESCE(total_score, 0) FROM bubble_words_progress WHERE user_id = $1",
telegram_id
) or 0
grand_total = wordsearch_score + sentence_score + wordquiz_score + bubblewords_score
# Собираем «что проверить» — вызовы достижений вне блока
achievements_to_check = [
("sentences_completed", sentences_completed),
("total_score", grand_total),
]
if completion_time is not None and completion_time > 0:
achievements_to_check.append(("speed_sentence", int(completion_time)))
# 🔧 ФИКС (23.09.2026): вызовы достижений ВНЕ блока async with pool.acquire()
if is_correct:
from routers.achievements import check_and_award_achievements
new_achievements = []
for ach_type, value in achievements_to_check:
ach = await check_and_award_achievements(telegram_id, ach_type, value)
new_achievements.extend(ach)
return {
"correct": True,
"correct_words": [normalize_word_for_display(w) for w in correct_words_original],
"already_completed": False,
"score": score,
"new_achievements": new_achievements,
# 🆕 XP
"stars": xp_result["stars"],
"base_points": xp_result["base_points"],
"multiplier": xp_result["multiplier"],
"points": xp_result["points"],
"featured": xp_result["featured"],
"level_up": xp_result["level_up"],
"total_xp": xp_result["total_xp"],
"level": xp_result["level"],
"rank_key": xp_result["rank_key"],
"xp_in_level": xp_result["xp_in_level"],
"xp_needed": xp_result["xp_needed"],
}
return {
"correct": False,
"correct_words": [normalize_word_for_display(w) for w in correct_words_original],
"already_completed": False,
"new_achievements": []
}
@router.get("/stats")
async def get_sentence_stats(request: Request):
"""Статистика по предложениям для выбранного уровня"""
telegram_id, _ = await require_auth(request)
pool = await get_pool()
async with pool.acquire() as conn:
user = await conn.fetchrow(
"SELECT current_level FROM users WHERE telegram_id = $1", telegram_id
)
current_level = user["current_level"] if user else "A1"
total = await conn.fetchval(
"SELECT COUNT(*) FROM sentences WHERE level = $1", current_level
)
completed = await conn.fetchval(
"SELECT COUNT(*) FROM sentence_progress WHERE user_id = $1 AND level = $2",
telegram_id, current_level
)
total_score = await conn.fetchval(
"SELECT COALESCE(SUM(score), 0) FROM sentence_progress WHERE user_id = $1 AND level = $2",
telegram_id, current_level
)
return {
"total_sentences": total,
"completed": completed or 0,
"remaining": total - (completed or 0),
"total_score": total_score or 0,
"progress_percent": round(((completed or 0) / total * 100), 1) if total > 0 else 0,
"level": current_level,
}