290 lines
12 KiB
Python
290 lines
12 KiB
Python
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")
|
|
|
|
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()
|
|
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)
|
|
if word_count <= 4:
|
|
score = 50
|
|
elif word_count <= 6:
|
|
score = 75
|
|
else:
|
|
score = 100
|
|
|
|
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"],
|
|
)
|
|
|
|
from routers.achievements import check_and_award_achievements
|
|
new_achievements = []
|
|
|
|
sentences_completed = await conn.fetchval(
|
|
"SELECT COUNT(*) FROM sentence_progress WHERE user_id = $1",
|
|
telegram_id
|
|
)
|
|
ach = await check_and_award_achievements(telegram_id, "sentences_completed", sentences_completed)
|
|
new_achievements.extend(ach)
|
|
|
|
if completion_time is not None and completion_time > 0:
|
|
ach = await check_and_award_achievements(telegram_id, "speed_sentence", int(completion_time))
|
|
new_achievements.extend(ach)
|
|
|
|
# Общий счёт по ВСЕМ играм
|
|
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
|
|
ach = await check_and_award_achievements(telegram_id, "total_score", grand_total)
|
|
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
|
|
}
|
|
|
|
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,
|
|
} |