Files
filwordspl-backend/routers/learning.py
Drobysh 02b3eb7b40 feat(ai): hybrid models — MiniMax M3 + DeepSeek V4 Pro
- teacher.py: TEACHER_MODEL=DeepSeek V4 Pro, reasoning_effort=none (фраза дня 2-3 сек вместо 28)
- support.py: SUPPORT_MODEL=DeepSeek V4 Pro (перевод сообщений)
- feed.py: TRANSLATE_MODEL=DeepSeek V4 Pro (перевод постов)
- generate_daily_phrase.py: TEACHER_MODEL=DeepSeek V4 Pro
- learning.py: ANALYTICS_MODEL=DeepSeek V4 Pro (reasoning для аналитики)

V4 Pro отключён reasoning (reasoning_effort=none) — быстро и дёшево.
Для аналитики reasoning остаётся (по умолчанию).
2026-10-05 13:00:56 +00:00

1234 lines
54 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.
"""
Learning Router — система уроков Filwords PL.
Уровни → Темы → Уроки → Финальный тест → Аналитика.
Данные в отдельных файлах: learning_data/level_*.py
"""
import logging
import json
import statistics
from datetime import datetime, timezone
import httpx
import re
from fastapi import APIRouter, Request, HTTPException
from auth import require_auth
from database import get_pool
from config import TOGETHER_API_KEY
from .learning_profile import get_weak_skills, get_strong_skills
logger = logging.getLogger(__name__)
router = APIRouter(prefix="/learning", tags=["learning"])
TOGETHER_API_URL = "https://api.together.xyz/v1/chat/completions"
ANALYTICS_MODEL = "deepseek-ai/DeepSeek-V4-Pro-0813"
# Доступные функции приложения (для AI — чтобы не галлюцинировал)
AVAILABLE_FEATURES = [
"lessons",
"grammar_rules",
"cheat_sheets",
"word_game",
"sentence_game",
"choose_word",
"word_bubbles",
"pronunciation",
"ai_chat",
]
# Понятные названия разделов для пользователя
AVAILABLE_FEATURES_DESCRIPTION = """
Доступные разделы приложения (называй их ТАК, как они называются в интерфейсе пользователя):
- "Уроки" — уроки и тесты
- "Филворды" — игра в слова (найди слова в сетке букв)
- "Составь предложение" — составление предложений из слов
- "Выбери слово" — выбор правильного перевода
- "Пузыри слов" — сбор слов из пузырей
- "Произношение" — практика произношения
- "Грамматика" — правила и шпаргалки
- "AI-помощник" — чат с ИИ
НИКОГДА не используй технические названия: word_game, sentence_game, choose_word, word_bubbles, ai_chat, lessons, pronunciation, grammar_rules, cheat_sheets.
"""
async def get_skill_names(conn):
"""Загружает понятные названия навыков из БД (title_ru уроков)."""
rows = await conn.fetch("""
SELECT DISTINCT ON (skill_tag) skill_tag, title_ru
FROM learning_lessons
WHERE is_active = TRUE
ORDER BY skill_tag, order_number
""")
return {r["skill_tag"]: r["title_ru"] for r in rows}
async def get_weak_lessons(conn, skill_tags):
"""Находит уроки для перепрохождения по слабым навыкам."""
rows = await conn.fetch("""
SELECT DISTINCT ON (l.skill_tag) l.lesson_id, l.skill_tag, l.title_ru, t.name_ru as topic_name
FROM learning_lessons l
JOIN learning_topics t ON l.topic_id = t.topic_id
WHERE l.skill_tag = ANY($1) AND l.is_active = TRUE
ORDER BY l.skill_tag, l.order_number
""", skill_tags)
return [dict(r) for r in rows]
async def update_skill_mastery(conn, user_id: int, skill_tag: str):
"""Рассчитывает и обновляет мастерство по навыку на основе всех ответов пользователя"""
# Все ответы по навыку с question_type и wrong_because
answers = await conn.fetch("""
SELECT correct, time_taken_ms, hint_used, question_type, wrong_because, is_final_answer, created_at
FROM user_learning_answers
WHERE user_id = $1 AND skill_tag = $2
ORDER BY created_at ASC
""", user_id, skill_tag)
if not answers:
return None
total_attempts = len(answers)
correct_count = sum(1 for a in answers if a["correct"])
accuracy = (correct_count / total_attempts * 100) if total_attempts > 0 else 0
# Последние попытки
recent = answers[-5:]
recent_correct = sum(1 for a in recent if a["correct"])
recent_accuracy = (recent_correct / len(recent) * 100) if recent else 0
# last_3 / last_5 / last_10 accuracy
def calc_last_n(n):
last_n = answers[-n:] if len(answers) >= n else None
if not last_n or len(last_n) < n:
return None
correct = sum(1 for a in last_n if a["correct"])
return round((correct / n * 100), 2)
last_3_accuracy = calc_last_n(3)
last_5_accuracy = calc_last_n(5)
last_10_accuracy = calc_last_n(10)
# Время: среднее, медиана
all_times = [a["time_taken_ms"] for a in answers if a["time_taken_ms"] and a["time_taken_ms"] > 0]
correct_times = [a["time_taken_ms"] for a in answers if a["correct"] and a["time_taken_ms"] and a["time_taken_ms"] > 0]
wrong_times = [a["time_taken_ms"] for a in answers if not a["correct"] and a["time_taken_ms"] and a["time_taken_ms"] > 0]
avg_time_ms = int(sum(correct_times) / len(correct_times)) if correct_times else 0
median_time_ms = int(statistics.median(all_times)) if all_times else 0
median_correct_time_ms = int(statistics.median(correct_times)) if correct_times else 0
median_wrong_time_ms = int(statistics.median(wrong_times)) if wrong_times else 0
# Зависимость от подсказок
hints_used = sum(1 for a in answers if a["hint_used"])
hint_dependency = (hints_used / total_attempts * 100) if total_attempts > 0 else 0
# Разделение recognition / production / application
recognition_answers = [a for a in answers if a["question_type"] == "recognition"]
production_answers = [a for a in answers if a["question_type"] == "production"]
application_answers = [a for a in answers if a["question_type"] == "application"]
recognition_attempts = len(recognition_answers)
recognition_correct = sum(1 for a in recognition_answers if a["correct"])
production_attempts = len(production_answers)
production_correct = sum(1 for a in production_answers if a["correct"])
application_attempts = len(application_answers)
application_correct = sum(1 for a in application_answers if a["correct"])
recognition_score = round((recognition_correct / recognition_attempts * 100), 2) if recognition_attempts > 0 else None
production_score = round((production_correct / production_attempts * 100), 2) if production_attempts > 0 else None
application_score = round((application_correct / application_attempts * 100), 2) if application_attempts > 0 else None
# Passive / Active knowledge
passive_knowledge = recognition_score if recognition_score is not None else 0
active_scores = [s for s in [production_score, application_score] if s is not None]
active_knowledge = round(sum(active_scores) / len(active_scores), 2) if active_scores else 0
# Ошибки
error_count = sum(1 for a in answers if not a["correct"] and a["wrong_because"])
error_types = {}
for a in answers:
if not a["correct"] and a["wrong_because"]:
error_types[a["wrong_because"]] = error_types.get(a["wrong_because"], 0) + 1
repeated_error_count = sum(1 for count in error_types.values() if count >= 2)
# Дней с последней попытки
last_attempt = answers[-1]["created_at"]
if last_attempt:
now = datetime.now(timezone.utc)
if last_attempt.tzinfo is None:
last_attempt = last_attempt.replace(tzinfo=timezone.utc)
days_since = (now - last_attempt).days
else:
days_since = 0
# Мастерство
no_hint_score = max(0, 100 - hint_dependency)
mastery = round(accuracy * 0.4 + recent_accuracy * 0.3 + no_hint_score * 0.3, 2)
# Получаем прошлый mastery для расчёта delta
previous_row = await conn.fetchrow("""
SELECT mastery_score, previous_mastery_score FROM user_skill_mastery
WHERE user_id = $1 AND skill_tag = $2
""", user_id, skill_tag)
previous_mastery = float(previous_row["mastery_score"]) if previous_row and previous_row["mastery_score"] else mastery
mastery_delta = round(mastery - previous_mastery, 2)
# Trend
if total_attempts < 3:
trend = "insufficient_data"
elif last_3_accuracy is not None and last_10_accuracy is not None:
if last_3_accuracy > last_10_accuracy + 5:
trend = "improving"
elif last_3_accuracy < last_10_accuracy - 5:
trend = "declining"
else:
trend = "stable"
elif last_3_accuracy is not None and accuracy is not None:
if last_3_accuracy > accuracy + 5:
trend = "improving"
elif last_3_accuracy < accuracy - 5:
trend = "declining"
else:
trend = "stable"
else:
trend = "stable"
# Retention status
if days_since == 0:
retention_status = "fresh"
elif days_since <= 3:
retention_status = "fresh"
elif days_since <= 7:
retention_status = "aging"
elif days_since <= 14:
retention_status = "needs_review"
else:
retention_status = "overdue"
# Fluency score: скорость + точность + автоматизация
speed_score = 100 if median_correct_time_ms and median_correct_time_ms < 5000 else (50 if median_correct_time_ms and median_correct_time_ms < 15000 else 20)
fluency = round(accuracy * 0.4 + active_knowledge * 0.3 + speed_score * 0.3, 2)
# Уверенность
if len(recent) >= 3:
if all(a["correct"] for a in recent[-3:]):
confidence = min(100, mastery + 15)
elif not any(a["correct"] for a in recent[-3:]):
confidence = max(0, mastery - 20)
else:
confidence = mastery
else:
confidence = mastery
confidence = round(min(100, max(0, confidence)), 2)
# Сохраняем в user_skill_mastery
await conn.execute("""
INSERT INTO user_skill_mastery (
user_id, skill_tag, attempts_count, correct_count,
recognition_attempts, recognition_correct, recognition_score,
production_attempts, production_correct, production_score,
application_attempts, application_correct, application_score,
retention_score, hint_dependency, average_time_ms, median_time_ms,
median_correct_time_ms, median_wrong_time_ms,
recent_accuracy, last_3_accuracy, last_5_accuracy, last_10_accuracy,
long_term_accuracy, mastery_score, previous_mastery_score, mastery_delta,
trend, retention_status, fluency_score, passive_knowledge_score, active_knowledge_score,
confidence, error_count, repeated_error_count, days_since_last_attempt, last_attempt_at
) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, 0, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, NOW())
ON CONFLICT (user_id, skill_tag) DO UPDATE SET
attempts_count = $3,
correct_count = $4,
recognition_attempts = $5,
recognition_correct = $6,
recognition_score = $7,
production_attempts = $8,
production_correct = $9,
production_score = $10,
application_attempts = $11,
application_correct = $12,
application_score = $13,
hint_dependency = $14,
average_time_ms = $15,
median_time_ms = $16,
median_correct_time_ms = $17,
median_wrong_time_ms = $18,
recent_accuracy = $19,
last_3_accuracy = $20,
last_5_accuracy = $21,
last_10_accuracy = $22,
long_term_accuracy = $23,
mastery_score = $24,
previous_mastery_score = $25,
mastery_delta = $26,
trend = $27,
retention_status = $28,
fluency_score = $29,
passive_knowledge_score = $30,
active_knowledge_score = $31,
confidence = $32,
error_count = $33,
repeated_error_count = $34,
days_since_last_attempt = $35,
last_attempt_at = NOW()
""", user_id, skill_tag, total_attempts, correct_count, recognition_attempts, recognition_correct, recognition_score, production_attempts, production_correct, production_score, application_attempts, application_correct, application_score, hint_dependency, avg_time_ms, median_time_ms, median_correct_time_ms, median_wrong_time_ms, recent_accuracy, last_3_accuracy, last_5_accuracy, last_10_accuracy, accuracy, mastery, previous_mastery, mastery_delta, trend, retention_status, fluency, passive_knowledge, active_knowledge, confidence, error_count, repeated_error_count, days_since)
return {
"skill_tag": skill_tag,
"attempts": total_attempts,
"accuracy": round(accuracy, 1),
"recent_accuracy": round(recent_accuracy, 1),
"recognition_score": recognition_score,
"production_score": production_score,
"application_score": application_score,
"mastery": mastery,
"mastery_delta": mastery_delta,
"trend": trend,
"retention_status": retention_status,
"fluency": fluency,
"passive_knowledge": passive_knowledge,
"active_knowledge": active_knowledge,
"confidence": confidence,
}
async def update_topic_progress(conn, user_id: int, topic_id: str):
"""Обновляет прогресс темы: lessons_total, lessons_completed, topic_mastery"""
# Общее количество уроков в теме
lessons_total = await conn.fetchval("""
SELECT COUNT(*) FROM learning_lessons WHERE topic_id = $1 AND is_active = TRUE
""", topic_id) or 0
# Количество завершённых уроков
lessons_completed = await conn.fetchval("""
SELECT COUNT(*) FROM user_lesson_progress WHERE user_id = $1 AND lesson_id IN (
SELECT lesson_id FROM learning_lessons WHERE topic_id = $2
) AND completed = TRUE
""", user_id, topic_id) or 0
# Тестовый результат
test_row = await conn.fetchrow("""
SELECT test_completed, test_score FROM user_topic_progress WHERE user_id = $1 AND topic_id = $2
""", user_id, topic_id)
test_completed = test_row["test_completed"] if test_row else False
test_score = float(test_row["test_score"]) if test_row and test_row["test_score"] else 0
# Среднее мастерство по навыкам темы
skills_avg = await conn.fetchval("""
SELECT COALESCE(AVG(mastery_score), 0) FROM user_skill_mastery
WHERE user_id = $1 AND skill_tag IN (
SELECT DISTINCT skill_tag FROM learning_questions WHERE topic_id = $2
)
""", user_id, topic_id) or 0
# Мастерство темы
if test_completed and test_score > 0:
topic_mastery = round(float(skills_avg) * 0.4 + float(test_score) * 0.6, 2)
else:
topic_mastery = round(float(skills_avg), 2)
# Обновляем
await conn.execute("""
INSERT INTO user_topic_progress (user_id, topic_id, lessons_total, lessons_completed, test_completed, test_score, topic_mastery)
VALUES ($1, $2, $3, $4, $5, $6, $7)
ON CONFLICT (user_id, topic_id) DO UPDATE SET
lessons_total = $3,
lessons_completed = $4,
test_completed = $5,
test_score = $6,
topic_mastery = $7
""", user_id, topic_id, lessons_total, lessons_completed, test_completed, test_score, topic_mastery)
return {
"lessons_total": lessons_total,
"lessons_completed": lessons_completed,
"test_completed": test_completed,
"test_score": round(test_score, 1),
"topic_mastery": topic_mastery,
}
async def update_ai_context(conn, user_id: int):
"""Обновляет AI-контекст пользователя: сильные/слабые навыки, общее мастерство, прогресс уровня"""
# Получаем текущий уровень
current_level = await conn.fetchval("""
SELECT current_level FROM users WHERE telegram_id = $1
""", user_id) or "A1"
# Все темы пользователя
topics = await conn.fetch("""
SELECT topic_id, topic_mastery, test_completed FROM user_topic_progress WHERE user_id = $1
""", user_id)
completed_topics = sum(1 for t in topics if t["test_completed"])
completed_lessons = await conn.fetchval("""
SELECT COUNT(*) FROM user_lesson_progress WHERE user_id = $1 AND completed = TRUE
""", user_id) or 0
# Всего тем в уровне
level_topics_total = await conn.fetchval("""
SELECT COUNT(*) FROM learning_topics WHERE level_code = $1 AND is_active = TRUE
""", current_level) or 0
# Процент завершения уровня
level_completion_percent = round((completed_topics / level_topics_total * 100), 2) if level_topics_total > 0 else 0
# Общее мастерство
overall_mastery = await conn.fetchval("""
SELECT COALESCE(AVG(topic_mastery), 0) FROM user_topic_progress WHERE user_id = $1
""", user_id) or 0
overall_mastery = float(overall_mastery) if overall_mastery else 0
# Сильные и слабые навыки — используем общий источник
strong_skills = await get_strong_skills(conn, user_id, limit=8)
weak_skills = await get_weak_skills(conn, user_id, limit=5)
strong_skill_tags = [s["skill_tag"] for s in strong_skills]
weak_skill_tags = [s["skill_tag"] for s in weak_skills]
# Частые ошибки
errors = await conn.fetch("""
SELECT wrong_because, COUNT(*) as cnt
FROM user_learning_answers
WHERE user_id = $1 AND wrong_because IS NOT NULL AND correct = FALSE
GROUP BY wrong_because
ORDER BY cnt DESC
LIMIT 5
""", user_id)
recurring_errors = [e["wrong_because"] for e in errors]
# Обновляем ai_user_context
await conn.execute("""
INSERT INTO ai_user_context (user_id, current_level, completed_topics, level_topics_total, level_completion_percent, completed_lessons, overall_mastery, strong_skills, weak_skills, recurring_errors, analytics_enabled, last_analysis_at)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, TRUE, NOW())
ON CONFLICT (user_id) DO UPDATE SET
current_level = $2,
completed_topics = $3,
level_topics_total = $4,
level_completion_percent = $5,
completed_lessons = $6,
overall_mastery = $7,
strong_skills = $8,
weak_skills = $9,
recurring_errors = $10,
analytics_enabled = TRUE,
last_analysis_at = NOW()
""", user_id, current_level, completed_topics, level_topics_total, level_completion_percent, completed_lessons, round(float(overall_mastery), 2), json.dumps(strong_skill_tags), json.dumps(weak_skill_tags), json.dumps(recurring_errors))
return {
"completed_topics": completed_topics,
"level_topics_total": level_topics_total,
"level_completion_percent": level_completion_percent,
"completed_lessons": completed_lessons,
"overall_mastery": round(float(overall_mastery), 2),
"strong_skills": strong_skill_tags,
"weak_skills": weak_skill_tags,
}
@router.get("/levels")
async def get_learning_levels(request: Request):
"""Получить все уровни обучения"""
await require_auth(request)
pool = await get_pool()
async with pool.acquire() as conn:
rows = await conn.fetch("SELECT * FROM learning_levels WHERE is_active = TRUE ORDER BY sort_order")
return {"levels": [dict(row) for row in rows]}
@router.get("/topics")
async def get_topics(request: Request, level: str = "A1"):
"""
Получить темы для уровня.
🔧 ОБНОВЛЕНО (04.10.2026): track_filter —
фильтруем темы по языку юзера:
- pl → track='pl'
- ru/uk → track='ru_uk' (общий для славян)
- en → track='en'
"""
telegram_id, _ = await require_auth(request)
pool = await get_pool()
async with pool.acquire() as conn:
# Определяем track из app_language юзера
user_lang = await conn.fetchval(
"SELECT app_language FROM users WHERE telegram_id = $1",
telegram_id,
) or "ru"
if user_lang == "pl":
track = "pl"
elif user_lang == "en":
track = "en"
else:
# ru, uk → общий track
track = "ru_uk"
rows = await conn.fetch("""
SELECT * FROM learning_topics
WHERE level_code = $1
AND is_active = TRUE
AND track = $2
ORDER BY order_number
""", level, track)
return {"topics": [dict(row) for row in rows], "track": track}
@router.get("/topics/{topic_id}")
async def get_topic(request: Request, topic_id: str):
"""Получить информацию о теме и список уроков"""
telegram_id, _ = await require_auth(request)
pool = await get_pool()
async with pool.acquire() as conn:
topic = await conn.fetchrow("SELECT * FROM learning_topics WHERE topic_id = $1", topic_id)
if not topic:
raise HTTPException(404, "Topic not found")
lessons = await conn.fetch("SELECT * FROM learning_lessons WHERE topic_id = $1 AND is_active = TRUE ORDER BY order_number", topic_id)
progress = await conn.fetchrow("SELECT * FROM user_topic_progress WHERE user_id = $1 AND topic_id = $2", telegram_id, topic_id)
lesson_progress = await conn.fetch("""
SELECT lesson_id, completed FROM user_lesson_progress WHERE user_id = $1
""", telegram_id)
progress_map = {lp["lesson_id"]: lp["completed"] for lp in lesson_progress}
lessons_data = []
for lesson in lessons:
l = dict(lesson)
l["completed"] = progress_map.get(lesson["lesson_id"], False)
lessons_data.append(l)
return {
"topic": dict(topic),
"lessons": lessons_data,
"progress": dict(progress) if progress else None,
}
@router.get("/lessons/{lesson_id}")
async def get_lesson(request: Request, lesson_id: str):
"""Получить урок с примерами и вопросами"""
telegram_id, _ = await require_auth(request)
pool = await get_pool()
async with pool.acquire() as conn:
lesson = await conn.fetchrow("SELECT * FROM learning_lessons WHERE lesson_id = $1", lesson_id)
if not lesson:
raise HTTPException(404, "Lesson not found")
examples = await conn.fetch("SELECT * FROM lesson_examples WHERE lesson_id = $1 ORDER BY order_number", lesson_id)
questions = await conn.fetch("SELECT * FROM learning_questions WHERE lesson_id = $1 AND is_test_question = FALSE ORDER BY id", lesson_id)
# 🔧 ФИКС (24.09.2026): learning_get_lesson_n_plus_1 —
# раньше N+1 запрос на options. Теперь один ANY + группировка в Python.
question_ids = [q["question_id"] for q in questions]
all_options = []
if question_ids:
all_options = await conn.fetch("""
SELECT * FROM question_options
WHERE question_id = ANY($1::text[])
""", question_ids)
# Группируем options по question_id
options_by_question: dict[str, list] = {}
for o in all_options:
options_by_question.setdefault(o["question_id"], []).append(dict(o))
questions_data = []
for q in questions:
questions_data.append({
"question": dict(q),
"options": options_by_question.get(q["question_id"], []),
})
progress = await conn.fetchrow("SELECT * FROM user_lesson_progress WHERE user_id = $1 AND lesson_id = $2", telegram_id, lesson_id)
return {
"lesson": dict(lesson),
"examples": [dict(e) for e in examples],
"questions": questions_data,
"progress": dict(progress) if progress else None,
}
@router.get("/test/{topic_id}")
async def get_topic_test(request: Request, topic_id: str):
"""Получить вопросы финального теста"""
await require_auth(request)
pool = await get_pool()
async with pool.acquire() as conn:
questions = await conn.fetch("SELECT * FROM learning_questions WHERE topic_id = $1 AND is_test_question = TRUE ORDER BY test_order", topic_id)
# 🔧 ФИКС (24.09.2026): learning_get_lesson_n_plus_1 (тот же паттерн) —
# заодно оптимизируем get_topic_test.
question_ids = [q["question_id"] for q in questions]
all_options = []
if question_ids:
all_options = await conn.fetch("""
SELECT * FROM question_options
WHERE question_id = ANY($1::text[])
""", question_ids)
options_by_question: dict[str, list] = {}
for o in all_options:
options_by_question.setdefault(o["question_id"], []).append(dict(o))
questions_data = []
for q in questions:
questions_data.append({
"question": dict(q),
"options": options_by_question.get(q["question_id"], []),
})
return {"test": questions_data, "total": len(questions_data)}
@router.post("/session/start")
async def start_session(request: Request):
"""Начать новую сессию обучения"""
telegram_id, _ = await require_auth(request)
body = await request.json()
level_code = body.get("level_code", "A1")
topic_id = body.get("topic_id", "")
session_type = body.get("session_type", "lesson")
pool = await get_pool()
async with pool.acquire() as conn:
# 🔧 ФИКС (25.09.2026): hardcoded_a1_level_from_client —
# клиент присылает 'A1' хардкодом (LessonViewPage, TestViewPage).
# Сервер должен сам достать level_code из темы, а не доверять клиенту.
# Если тема не найдена (например, practice без topic_id) — оставляем
# клиентский level_code как fallback.
if topic_id:
real_level = await conn.fetchval("""
SELECT level_code FROM learning_topics WHERE topic_id = $1
""", topic_id)
if real_level:
level_code = real_level
row = await conn.fetchrow("""
INSERT INTO learning_sessions (user_id, level_code, topic_id, session_type)
VALUES ($1, $2, $3, $4) RETURNING id
""", telegram_id, level_code, topic_id, session_type)
return {"session_id": row["id"]}
# 🔧 ОБНОВЛЕНО (02.10.2026): attempt_number + revised —
# если ученик отвечает на вопрос повторно (после «Повторить урок»),
# создаётся НОВАЯ запись с attempt_number+1, revised=TRUE,
# revision_of_id=предыдущий ответ. История сохраняется полностью.
@router.post("/answer")
async def save_answer(request: Request):
"""
Сохранить ответ пользователя.
🔧 ОБНОВЛЕНО (02.10.2026): attempt_number + revised —
если ученик отвечает на вопрос повторно (после «Повторить урок»),
создаётся НОВАЯ запись с attempt_number+1, revised=TRUE,
revision_of_id=предыдущий ответ. История сохраняется полностью.
Возвращает:
{
"correct": bool,
"wrong_because": str | None,
"attempt_number": int, # какая это попытка
"show_correct_answer": bool, # TRUE только если attempt >= 3
"correct_answer": str | None # только при show_correct_answer
}
"""
telegram_id, _ = await require_auth(request)
body = await request.json()
session_id = body.get("session_id")
question_id = body.get("question_id")
selected_answer = body.get("answer", "")
time_taken_ms = body.get("time_taken_ms", 0)
is_final = body.get("is_final_answer", True)
hint_used = body.get("hint_used", False)
# 🔧 НОВОЕ: клиент сообщает, что это повторная попытка
is_retry = body.get("is_retry", False)
if not session_id or not question_id:
raise HTTPException(400, "session_id and question_id required")
pool = await get_pool()
async with pool.acquire() as conn:
question = await conn.fetchrow(
"SELECT * FROM learning_questions WHERE question_id = $1", question_id
)
if not question:
raise HTTPException(404, "Question not found")
# 🔧 НОВОЕ: считаем текущее количество попыток по этому вопросу в сессии
prev_attempts = await conn.fetch(
"""
SELECT id, attempt_number, correct
FROM user_learning_answers
WHERE session_id = $1 AND question_id = $2
ORDER BY attempt_number ASC
""",
session_id, question_id,
)
next_attempt = (prev_attempts[-1]["attempt_number"] + 1) if prev_attempts else 1
prev_id = prev_attempts[-1]["id"] if prev_attempts else None
correct = (
selected_answer.strip().lower()
== (question["correct_answer"] or "").strip().lower()
)
wrong_because = None
if not correct:
option = await conn.fetchrow("""
SELECT wrong_because FROM question_options
WHERE question_id = $1 AND option_key = $2
""", question_id, selected_answer)
if option:
wrong_because = option["wrong_because"]
session = await conn.fetchrow(
"SELECT * FROM learning_sessions WHERE id = $1", session_id
)
if not session:
raise HTTPException(404, "Session not found")
if session["user_id"] != telegram_id:
raise HTTPException(403, "Session belongs to another user")
# 🔧 Логика revised: если это повторная попытка — фиксируем
revised = is_retry or next_attempt > 1
# 🔧 is_final_answer: только ПОСЛЕДНЯЯ попытка считается финальной
# (для статистики). Промежуточные — revision-записи, не идут в total.
# Если клиент сказал is_final=False (подсказка) — уважаем.
if is_retry:
# Сначала снимаем флаг is_final_answer у предыдущей записи
await conn.execute(
"""
UPDATE user_learning_answers
SET is_final_answer = FALSE
WHERE session_id = $1 AND question_id = $2
""",
session_id, question_id,
)
await conn.execute(
"""
INSERT INTO user_learning_answers (
user_id, session_id, level_code, topic_id, lesson_id,
question_id, selected_answer, correct, skill_tag,
question_type, context_type, difficulty, time_taken_ms,
attempt_number, hint_used, wrong_because, is_final_answer,
revised, revision_of_id
)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13,
$14, $15, $16, $17, $18, $19)
""",
telegram_id, session_id, session["level_code"], session["topic_id"],
question["lesson_id"], question_id, selected_answer, correct,
question["skill_tag"], question["question_type"], question["context_type"],
question["difficulty"], time_taken_ms, next_attempt, hint_used,
wrong_because, is_final, revised, prev_id,
)
# 🔧 Решаем, показывать ли правильный ответ
# Первая попытка — НЕ показываем
# Вторая — НЕ показываем
# Третья и выше — показываем
show_correct_answer = next_attempt >= 3
# Правильный ответ — текст варианта (для recognition)
correct_answer_text = None
if show_correct_answer:
correct_option = await conn.fetchrow(
"""
SELECT option_key, text_pl, text_ru, text_uk, text_en
FROM question_options
WHERE question_id = $1 AND is_correct = TRUE
LIMIT 1
""",
question_id,
)
if correct_option:
correct_answer_text = (
f"{correct_option['option_key']} — {correct_option['text_pl']}"
)
else:
# Для production — просто correct_answer из вопроса
correct_answer_text = question["correct_answer"]
return {
"correct": correct,
"wrong_because": wrong_because,
"attempt_number": next_attempt,
"show_correct_answer": show_correct_answer,
"correct_answer": correct_answer_text,
}
# 🔧 ОБНОВЛЕНО (02.10.2026): session_report —
# без AI формируем человеческий отчёт для экрана «Тема пройдена»:
# - total / correct / percent
# - message («Отлично!» / «Стоит повторить»)
# - weak_skills этой темы (с понятными названиями)
# - next_topic
@router.post("/session/complete")
async def complete_session(request: Request):
"""
Завершить сессию: обновляет прогресс, мастерство, AI-контекст
и возвращает отчёт для экрана «Тема пройдена».
🔧 ОБНОВЛЕНО (02.10.2026): session_report —
без AI формируем человеческий отчёт:
- total / correct / percent
- message («Отлично!» / «Стоит повторить»)
- weak_skills этой темы (с понятными названиями)
- next_topic
"""
telegram_id, _ = await require_auth(request)
body = await request.json()
session_id = body.get("session_id")
# 🔧 НОВОЕ: язык для формирования отчёта
lang = body.get("lang", "ru")
pool = await get_pool()
async with pool.acquire() as conn:
session = await conn.fetchrow(
"SELECT * FROM learning_sessions WHERE id = $1 AND user_id = $2",
session_id, telegram_id,
)
if not session:
raise HTTPException(404, "Session not found")
await conn.execute(
"UPDATE learning_sessions SET is_completed = TRUE, completed_at = NOW() WHERE id = $1",
session_id,
)
# 🔧 Считаем только ПОСЛЕДНИЕ (is_final_answer=TRUE) ответы на каждый вопрос
stats = await conn.fetchrow(
"""
WITH last_answers AS (
SELECT DISTINCT ON (question_id) question_id, correct, wrong_because, skill_tag
FROM user_learning_answers
WHERE session_id = $1
ORDER BY question_id, attempt_number DESC
)
SELECT
COUNT(*) AS total,
COALESCE(SUM(CASE WHEN correct THEN 1 ELSE 0 END), 0) AS correct_count
FROM last_answers
""",
session_id,
)
# Слабые навыки этой темы (те, где были ошибки)
weak_rows = await conn.fetch(
"""
WITH last_answers AS (
SELECT DISTINCT ON (question_id) question_id, correct, skill_tag
FROM user_learning_answers
WHERE session_id = $1
ORDER BY question_id, attempt_number DESC
)
SELECT
la.skill_tag,
COUNT(*) FILTER (WHERE NOT la.correct) AS errors,
COALESCE(ll.title_ru, la.skill_tag) AS name_ru,
COALESCE(ll.title_pl, la.skill_tag) AS name_pl,
COALESCE(ll.title_uk, la.skill_tag) AS name_uk,
COALESCE(ll.title_en, la.skill_tag) AS name_en
FROM last_answers la
LEFT JOIN learning_lessons ll ON ll.skill_tag = la.skill_tag
WHERE NOT la.correct
GROUP BY la.skill_tag, ll.title_ru, ll.title_pl, ll.title_uk, ll.title_en
HAVING COUNT(*) FILTER (WHERE NOT la.correct) > 0
ORDER BY errors DESC
LIMIT 5
""",
session_id,
)
# Информация о теме
topic_id = session["topic_id"]
topic_row = await conn.fetchrow(
"SELECT * FROM learning_topics WHERE topic_id = $1", topic_id
)
# Следующая тема того же уровня
next_topic = None
if topic_row:
next_topic = await conn.fetchrow(
"""
SELECT topic_id, name_ru, name_pl, name_uk, name_en
FROM learning_topics
WHERE level_code = $1
AND order_number > $2
AND is_active = TRUE
ORDER BY order_number
LIMIT 1
""",
topic_row["level_code"], topic_row["order_number"],
)
# ============ Обновляем прогресс ============
# (всё что было — оставляем)
if session["session_type"] == "lesson":
lesson_row = await conn.fetchrow(
"""
SELECT DISTINCT lesson_id FROM user_learning_answers
WHERE session_id = $1 AND lesson_id IS NOT NULL
""",
session_id,
)
if lesson_row and lesson_row["lesson_id"]:
lesson_id = lesson_row["lesson_id"]
score = (
stats["correct_count"] / stats["total"] * 100
) if stats["total"] > 0 else 0
await conn.execute(
"""
INSERT INTO user_lesson_progress (
user_id, lesson_id, started_at, completed_at,
attempts_count, correct_count, best_score, completed
)
VALUES ($1, $2, NOW(), NOW(), 1, $3, $4, TRUE)
ON CONFLICT (user_id, lesson_id) DO UPDATE
SET completed_at = NOW(),
attempts_count = user_lesson_progress.attempts_count + 1,
correct_count = user_lesson_progress.correct_count + $3,
best_score = GREATEST(user_lesson_progress.best_score, $4),
completed = TRUE
""",
telegram_id, lesson_id, stats["correct_count"], score,
)
if session["session_type"] == "topic_test":
test_score = (
stats["correct_count"] / stats["total"] * 100
) if stats["total"] > 0 else 0
await conn.execute(
"""
INSERT INTO user_topic_progress (
user_id, topic_id, test_started, test_completed, test_score, completed_at
)
VALUES ($1, $2, TRUE, TRUE, $3, NOW())
ON CONFLICT (user_id, topic_id) DO UPDATE
SET test_completed = TRUE,
test_score = $3,
completed_at = NOW()
""",
telegram_id, topic_id, test_score,
)
skill_tags = await conn.fetch(
"SELECT DISTINCT skill_tag FROM user_learning_answers WHERE session_id = $1",
session_id,
)
for st in skill_tags:
await update_skill_mastery(conn, telegram_id, st["skill_tag"])
if session["session_type"] in ("lesson", "topic_test") and topic_id:
await update_topic_progress(conn, telegram_id, topic_id)
await update_ai_context(conn, telegram_id)
# ============ Формируем отчёт ============
total = stats["total"] or 0
correct_count = stats["correct_count"] or 0
percent = round(correct_count / total * 100) if total > 0 else 0
# Человеческое сообщение (без AI, чистая математика)
if percent >= 90:
message = {
"ru": "Отлично! Ты прекрасно усвоил тему! 🎉",
"pl": "Świetnie! Doskonale opanowałeś temat! 🎉",
"uk": "Чудово! Ти чудово засвоїв тему! 🎉",
"en": "Excellent! You've mastered this topic! 🎉",
}.get(lang, "Отлично! 🎉")
elif percent >= 75:
message = {
"ru": "Хороший результат! Так держать! 💪",
"pl": "Dobry wynik! Tak trzymaj! 💪",
"uk": "Гарний результат! Так тримати! 💪",
"en": "Good result! Keep it up! 💪",
}.get(lang, "Хороший результат! 💪")
elif percent >= 60:
message = {
"ru": "Неплохо! Но есть над чем поработать.",
"pl": "Nieźle! Ale jest nad czym popracować.",
"uk": "Непогано! Але є над чим попрацювати.",
"en": "Not bad! But there's room to improve.",
}.get(lang, "Неплохо!")
else:
message = {
"ru": "Стоит пройти тему ещё раз — много ошибок.",
"pl": "Warto powtórzyć temat — dużo błędów.",
"uk": "Варто пройти тему ще раз — багато помилок.",
"en": "Worth repeating this topic — many mistakes.",
}.get(lang, "Стоит повторить.")
# Слабые навыки на нужном языке
name_field = f"name_{lang}"
weak_skills_out = []
for w in weak_rows:
weak_skills_out.append({
"skill_tag": w["skill_tag"],
"name": w.get(name_field) or w["name_ru"],
"errors": w["errors"],
})
# Информация о следующей теме
next_out = None
if next_topic:
next_out = {
"topic_id": next_topic["topic_id"],
"name": next_topic.get(name_field) or next_topic["name_ru"],
}
# Название текущей темы
topic_name = None
if topic_row:
topic_name = topic_row.get(name_field) or topic_row["name_ru"]
return {
"status": "ok",
"total": total,
"correct_count": correct_count,
"report": {
"topic_id": topic_id,
"topic_name": topic_name,
"percent": percent,
"message": message,
"weak_skills": weak_skills_out,
"next_topic": next_out,
},
}
@router.get("/progress")
async def get_learning_progress(request: Request):
"""Получить прогресс пользователя по всем темам"""
telegram_id, _ = await require_auth(request)
pool = await get_pool()
async with pool.acquire() as conn:
topics = await conn.fetch("""
SELECT tp.*, up.lessons_completed, up.lessons_total, up.test_completed, up.test_score, up.topic_mastery
FROM learning_topics tp
LEFT JOIN user_topic_progress up ON tp.topic_id = up.topic_id AND up.user_id = $1
ORDER BY tp.level_code, tp.order_number
""", telegram_id)
return {"progress": [dict(t) for t in topics]}
@router.post("/analytics")
async def get_analytics(request: Request):
"""Анализирует результаты пользователя через AI на основе user_skill_mastery и user_topic_progress"""
telegram_id, _ = await require_auth(request)
if not TOGETHER_API_KEY:
raise HTTPException(500, "AI API key not configured")
pool = await get_pool()
async with pool.acquire() as conn:
# Получаем слабые навыки из ОБЩЕГО источника
weak_skills = await get_weak_skills(conn, telegram_id, limit=5)
if not weak_skills:
return {"analytics_enabled": False, "message": "Отличная работа! У тебя нет слабых навыков."}
# Загружаем названия навыков из БД
skill_names = await get_skill_names(conn)
skill_tag_lines = '\n'.join([f'- {tag} → "{name}"' for tag, name in skill_names.items()])
# Находим уроки для перепрохождения
weak_skill_tags = [s["skill_tag"] for s in weak_skills]
weak_lessons = await get_weak_lessons(conn, weak_skill_tags)
weak_lessons_lines = '\n'.join([f'- {l["title_ru"]} (тема: {l["topic_name"]})' for l in weak_lessons])
# Формируем профиль
weak_skills_json = []
for s in weak_skills:
weak_skills_json.append({
"skill": s["skill_tag"],
"mastery": round(float(s["mastery_score"]), 1) if s["mastery_score"] else 0,
"repeated_errors": s["repeated_error_count"] or 0,
"retention": s["retention_status"] or "fresh",
"trend": s["trend"] or "stable",
"recent_accuracy": round(float(s["recent_accuracy"]), 1) if s["recent_accuracy"] else 0,
})
user_profile = {
"weak_skills": weak_skills_json,
"weak_lessons": [dict(l) for l in weak_lessons],
}
system_prompt = f"""Ты — AI-наставник польского языка в приложении Filwords PL.
Проанализируй слабые стороны пользователя и скажи что нужно перепройти.
Понятные названия навыков (используй ИХ в ответе, а не технические ID):
{skill_tag_lines}
ПРАВИЛА:
1. Не используй markdown
2. Отвечай на языке пользователя
3. НЕ говори о сильных сторонах — только о слабых
4. Говори ТОЛЬКО о тех навыках, которые перечислены в weak_skills
5. Для каждого слабого навыка укажи конкретный урок для перепрохождения
6. НАЗВАНИЯ НАВЫКОВ: всегда заменяй технические ID на понятные названия
7. ОБРАЩАЙСЯ К ПОЛЬЗОВАТЕЛЮ НА "ТЫ"
8. НИКОГДА не пиши имена метрик: mastery, recent_accuracy, retention_status и т.п. — только русский перевод
9. НЕ упоминай процент завершения уровня или количество пройденных тем
10. В конце предложи нажать кнопку "Потренировать слабые темы" — она уже есть под этим сообщением."""
user_prompt = f"""Слабые навыки пользователя:
{json.dumps(user_profile, ensure_ascii=False, indent=2)}
Напиши:
1. Какие темы ты не понял (конкретные навыки, понятными названиями)
2. Какие уроки нужно перепройти:
{weak_lessons_lines}
3. Нажми кнопку "Потренировать слабые темы" для практики
В конце напиши: "Готов потренироваться? Нажимай кнопку ниже!\""""
async with httpx.AsyncClient(timeout=90.0) as client:
response = await client.post(
TOGETHER_API_URL,
headers={
"Authorization": f"Bearer {TOGETHER_API_KEY}",
"Content-Type": "application/json",
},
json={
"model": ANALYTICS_MODEL,
"messages": [
{"role": "system", "content": system_prompt},
{"role": "user", "content": user_prompt},
],
"max_tokens": 3000,
"temperature": 0.7,
},
)
if response.status_code != 200:
raise HTTPException(500, f"AI API error: {response.status_code}")
data = response.json()
ai_response = data["choices"][0]["message"]["content"].strip()
ai_response = ai_response.replace("**", "").replace("*", "").replace("#", "")
# Убираем технические ID в скобках
ai_response = re.sub(r'\s*\([a-z_]+_[a-z_]+\)', '', ai_response)
# Если ответ пустой - запасной текст
if not ai_response or len(ai_response) < 10:
ai_response = "Перепройди уроки по слабым темам. Нажми кнопку ниже, чтобы потренироваться!"
return {
"analytics_enabled": True,
"response": ai_response,
"weak_skills": weak_skills_json,
"has_practice_available": len(weak_skills_json) > 0,
"cta_label": "Потренировать слабые темы",
}
@router.get("/ai/context")
async def get_ai_context(request: Request):
"""Получить AI-контекст пользователя"""
telegram_id, _ = await require_auth(request)
pool = await get_pool()
async with pool.acquire() as conn:
context = await conn.fetchrow("SELECT * FROM ai_user_context WHERE user_id = $1", telegram_id)
if not context:
return {"analytics_enabled": False, "message": "Пройди первую тему, и я смогу начать анализировать твой прогресс."}
return {"context": dict(context)}
@router.post("/practice/start")
async def start_weak_skills_practice(request: Request):
"""Собирает сессию практики из реального банка вопросов по слабым навыкам.
Ноль обращений к LLM - только SQL."""
telegram_id, _ = await require_auth(request)
pool = await get_pool()
async with pool.acquire() as conn:
weak = await get_weak_skills(conn, telegram_id, limit=5)
if not weak:
return {"has_weak_skills": False, "message": "Нет слабых навыков - отличная работа!"}
skill_tags = [s["skill_tag"] for s in weak]
# Забираем краткое объяснение правила для каждого слабого навыка
explanations = await conn.fetch("""
SELECT DISTINCT ON (skill_tag) skill_tag, explanation_ru, explanation_uk, explanation_en, explanation_pl
FROM learning_lessons
WHERE skill_tag = ANY($1)
ORDER BY skill_tag, order_number
""", skill_tags)
explanations_map = {e["skill_tag"]: dict(e) for e in explanations}
# Вопросы вперемешку по всем слабым навыкам, только не тестовые
questions = await conn.fetch("""
SELECT * FROM learning_questions
WHERE skill_tag = ANY($1) AND is_test_question = FALSE
ORDER BY random()
LIMIT 10
""", skill_tags)
if not questions:
return {"has_weak_skills": True, "questions": [], "message": "Для этих навыков пока нет вопросов в базе."}
# Получаем уровень пользователя
user_level = await conn.fetchval("""
SELECT current_level FROM users WHERE telegram_id = $1
""", telegram_id) or "A1"
session = await conn.fetchrow("""
INSERT INTO learning_sessions (user_id, level_code, topic_id, session_type)
VALUES ($1, $2, '', 'weak_skills_practice') RETURNING id
""", telegram_id, user_level)
# 🔧 ФИКС (24.09.2026): learning_get_lesson_n_plus_1 (тот же паттерн) —
# оптимизируем и practice/start: N+1 → один ANY + группировка.
question_ids = [q["question_id"] for q in questions]
all_options = []
if question_ids:
all_options = await conn.fetch("""
SELECT * FROM question_options
WHERE question_id = ANY($1::text[])
""", question_ids)
options_by_question: dict[str, list] = {}
for o in all_options:
options_by_question.setdefault(o["question_id"], []).append(dict(o))
questions_data = []
for q in questions:
questions_data.append({
"question": dict(q),
"options": options_by_question.get(q["question_id"], []),
})
# Загружаем понятные названия навыков из БД
skill_names = await get_skill_names(conn)
return {
"has_weak_skills": True,
"session_id": session["id"],
"explanations": [
{"skill_tag": tag, "name": skill_names.get(tag, tag), **explanations_map.get(tag, {})}
for tag in skill_tags
],
"questions": questions_data,
}