1213 lines
46 KiB
Python
1213 lines
46 KiB
Python
"""
|
||
Admin Content Router — контент-менеджер для редактирования уроков, слов и текстов.
|
||
|
||
Доступ: только для ADMIN_IDS.
|
||
Логика: препод (ты) правит контент через дашборд, без захода в код.
|
||
|
||
Разделы:
|
||
- GET /admin/content/summary — сводка по уровням
|
||
- GET /admin/content/words/levels — уровни слов (счётчики)
|
||
- GET /admin/content/words/level/{level} — список слов уровня
|
||
- GET /admin/content/words/word/{word_id} — одно слово
|
||
- PUT /admin/content/words/word/{word_id} — обновить слово
|
||
- POST /admin/content/words/level/{level} — добавить слово
|
||
- GET /admin/content/texts/levels — уровни текстов
|
||
- GET /admin/content/texts/level/{level} — список текстов
|
||
- GET /admin/content/texts/text/{text_id} — один текст
|
||
- PUT /admin/content/texts/text/{text_id} — обновить текст
|
||
- POST /admin/content/texts/level/{level} — добавить текст
|
||
- GET /admin/content/lessons/levels — уровни уроков (счётчики тем)
|
||
- GET /admin/content/lessons/level/{level}/topics — темы уровня
|
||
- GET /admin/content/lessons/topic/{topic_id} — тема + уроки + тест
|
||
- GET /admin/content/lessons/lesson/{lesson_id} — урок целиком (WYSIWYG)
|
||
- PUT /admin/content/lessons/lesson/{lesson_id} — сохранить урок (Вариант B)
|
||
- GET /admin/content/wrong-because — справочник wrong_because
|
||
|
||
🔧 ФИКС (19.09.2026): в words-эндпоинтах фильтруем только строки с level_id IS NULL.
|
||
Причина: в level_words хранятся ДВЕ категории строк:
|
||
1. Слова уровня для Word Quiz / Bubble Words → level_id IS NULL.
|
||
2. Привязки слов к игровым уровням Филвордов → level_id != NULL.
|
||
В дашборде преподу нужны только "чистые" слова уровня (level_id IS NULL).
|
||
Строки с level_id != NULL используются игрой и остаются нетронутыми.
|
||
При обновлении слова (PUT) — обновляем ВСЕ строки с этим (level, word_pl),
|
||
чтобы Филворды получили актуальный перевод.
|
||
|
||
🔧 НОВОЕ (19.09.2026, вечер): раздел «Уроки» — 6 эндпоинтов для LessonsEditor.
|
||
- Уровни → Темы → Уроки (иерархия как в словах/текстах).
|
||
- WYSIWYG-модалка редактирует: title, objective, explanation, examples, questions, options.
|
||
- Сохранение урока целиком (Вариант B) — одна транзакция.
|
||
- option_key генерируется бэкендом: A, B, C, D... по порядку массива.
|
||
- correct_answer в learning_questions = option_key того варианта, где is_correct=true.
|
||
- test_order пересчитывается 1..N по порядку для теста темы (is_test_question=TRUE).
|
||
- level_code НЕ трогаем — он вычисляется через topic_id → learning_topics.level_code.
|
||
"""
|
||
import logging
|
||
import re
|
||
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/content", tags=["admin-content"])
|
||
|
||
LEVELS = ["A1", "A2", "B1", "B2", "C1", "C2"]
|
||
|
||
|
||
def _require_admin(telegram_id: int) -> None:
|
||
if telegram_id not in ADMIN_IDS:
|
||
raise HTTPException(403, "Access denied")
|
||
|
||
|
||
# ============ SUMMARY ============
|
||
|
||
@router.get("/summary")
|
||
async def get_content_summary(request: Request):
|
||
"""
|
||
Сводка по всем уровням: темы, уроки, слова, тексты, последняя правка.
|
||
Слова считаются только level_id IS NULL (чистые слова уровня).
|
||
"""
|
||
telegram_id, _ = await require_auth(request)
|
||
_require_admin(telegram_id)
|
||
|
||
pool = await get_pool()
|
||
async with pool.acquire() as conn:
|
||
level_rows = await conn.fetch("""
|
||
SELECT code, name_ru, sort_order
|
||
FROM learning_levels
|
||
WHERE is_active = TRUE
|
||
ORDER BY sort_order
|
||
""")
|
||
|
||
result = []
|
||
for lvl in level_rows:
|
||
code = lvl["code"]
|
||
|
||
topics_total = await conn.fetchval("""
|
||
SELECT COUNT(*) FROM learning_topics WHERE level_code = $1
|
||
""", code) or 0
|
||
|
||
lessons_total = await conn.fetchval("""
|
||
SELECT COUNT(*) FROM learning_lessons l
|
||
JOIN learning_topics t ON l.topic_id = t.topic_id
|
||
WHERE t.level_code = $1
|
||
""", code) or 0
|
||
|
||
# 🔧 ФИКС: только level_id IS NULL
|
||
words_total = await conn.fetchval("""
|
||
SELECT COUNT(*) FROM level_words WHERE level = $1 AND level_id IS NULL
|
||
""", code) or 0
|
||
|
||
texts_total = await conn.fetchval("""
|
||
SELECT COUNT(*) FROM sentences WHERE level = $1
|
||
""", code) or 0
|
||
|
||
last_edited = await conn.fetchval("""
|
||
SELECT MAX(edited_at) FROM (
|
||
SELECT edited_at FROM learning_topics WHERE level_code = $1
|
||
UNION ALL
|
||
SELECT l.edited_at FROM learning_lessons l
|
||
JOIN learning_topics t ON l.topic_id = t.topic_id
|
||
WHERE t.level_code = $1
|
||
UNION ALL
|
||
SELECT edited_at FROM level_words WHERE level = $1 AND level_id IS NULL
|
||
UNION ALL
|
||
SELECT edited_at FROM sentences WHERE level = $1
|
||
) sub
|
||
""", code)
|
||
|
||
result.append({
|
||
"code": code,
|
||
"name_ru": lvl["name_ru"],
|
||
"topics_total": topics_total,
|
||
"lessons_total": lessons_total,
|
||
"words_total": words_total,
|
||
"texts_total": texts_total,
|
||
"last_edited": last_edited.isoformat() if last_edited else None,
|
||
})
|
||
|
||
return {"levels": result}
|
||
|
||
|
||
# ============ WORDS ============
|
||
|
||
@router.get("/words/levels")
|
||
async def get_word_levels(request: Request):
|
||
"""Уровни A1–C2 с количеством слов (только level_id IS NULL)."""
|
||
telegram_id, _ = await require_auth(request)
|
||
_require_admin(telegram_id)
|
||
|
||
pool = await get_pool()
|
||
async with pool.acquire() as conn:
|
||
level_rows = await conn.fetch("""
|
||
SELECT code, name_ru, sort_order
|
||
FROM learning_levels
|
||
WHERE is_active = TRUE
|
||
ORDER BY sort_order
|
||
""")
|
||
|
||
result = []
|
||
for lvl in level_rows:
|
||
code = lvl["code"]
|
||
# 🔧 ФИКС: только level_id IS NULL
|
||
count = await conn.fetchval(
|
||
"SELECT COUNT(*) FROM level_words WHERE level = $1 AND level_id IS NULL", code
|
||
) or 0
|
||
last_edited = await conn.fetchval(
|
||
"SELECT MAX(edited_at) FROM level_words WHERE level = $1 AND level_id IS NULL", code
|
||
)
|
||
result.append({
|
||
"code": code,
|
||
"name_ru": lvl["name_ru"],
|
||
"words_count": count,
|
||
"last_edited": last_edited.isoformat() if last_edited else None,
|
||
})
|
||
|
||
return {"levels": result}
|
||
|
||
|
||
@router.get("/words/level/{level}")
|
||
async def get_level_words(
|
||
request: Request,
|
||
level: str,
|
||
q: Optional[str] = None,
|
||
limit: int = 1000,
|
||
):
|
||
"""
|
||
Список слов уровня — только строки с level_id IS NULL.
|
||
Поиск по word_pl / translation_ru / translation_uk / translation_en.
|
||
"""
|
||
telegram_id, _ = await require_auth(request)
|
||
_require_admin(telegram_id)
|
||
|
||
if level not in LEVELS:
|
||
raise HTTPException(400, f"Invalid level: {level}")
|
||
|
||
if limit < 1:
|
||
limit = 1
|
||
elif limit > 2000:
|
||
limit = 2000
|
||
|
||
pool = await get_pool()
|
||
async with pool.acquire() as conn:
|
||
if q and q.strip():
|
||
pattern = f"%{q.strip()}%"
|
||
rows = await conn.fetch("""
|
||
SELECT id, word_pl, translation_ru, translation_uk, translation_en,
|
||
edited_at
|
||
FROM level_words
|
||
WHERE level = $1
|
||
AND level_id IS NULL
|
||
AND (
|
||
word_pl ILIKE $2
|
||
OR translation_ru ILIKE $2
|
||
OR translation_uk ILIKE $2
|
||
OR translation_en ILIKE $2
|
||
)
|
||
ORDER BY word_pl ASC
|
||
LIMIT $3
|
||
""", level, pattern, limit)
|
||
else:
|
||
rows = await conn.fetch("""
|
||
SELECT id, word_pl, translation_ru, translation_uk, translation_en,
|
||
edited_at
|
||
FROM level_words
|
||
WHERE level = $1
|
||
AND level_id IS NULL
|
||
ORDER BY word_pl ASC
|
||
LIMIT $2
|
||
""", level, limit)
|
||
|
||
return {
|
||
"level": level,
|
||
"count": len(rows),
|
||
"words": [
|
||
{
|
||
"id": r["id"],
|
||
"word_pl": r["word_pl"],
|
||
"translation_ru": r["translation_ru"] or "",
|
||
"translation_uk": r["translation_uk"] or "",
|
||
"translation_en": r["translation_en"] or "",
|
||
"edited_at": r["edited_at"].isoformat() if r["edited_at"] else None,
|
||
}
|
||
for r in rows
|
||
],
|
||
}
|
||
|
||
|
||
@router.get("/words/word/{word_id}")
|
||
async def get_word(request: Request, word_id: int):
|
||
"""Одно слово по id."""
|
||
telegram_id, _ = await require_auth(request)
|
||
_require_admin(telegram_id)
|
||
|
||
pool = await get_pool()
|
||
async with pool.acquire() as conn:
|
||
row = await conn.fetchrow("""
|
||
SELECT id, word_pl, translation_ru, translation_uk, translation_en, level, edited_at
|
||
FROM level_words WHERE id = $1
|
||
""", word_id)
|
||
|
||
if not row:
|
||
raise HTTPException(404, "Word not found")
|
||
|
||
return {
|
||
"id": row["id"],
|
||
"word_pl": row["word_pl"],
|
||
"translation_ru": row["translation_ru"] or "",
|
||
"translation_uk": row["translation_uk"] or "",
|
||
"translation_en": row["translation_en"] or "",
|
||
"level": row["level"],
|
||
"edited_at": row["edited_at"].isoformat() if row["edited_at"] else None,
|
||
}
|
||
|
||
|
||
@router.put("/words/word/{word_id}")
|
||
async def update_word(request: Request, word_id: int):
|
||
"""
|
||
Обновить слово.
|
||
Обновляем ВСЕ строки с этим (level, word_pl) — включая привязки к игровым
|
||
уровням Филвордов (level_id != NULL), чтобы игра получила новый перевод.
|
||
"""
|
||
telegram_id, _ = await require_auth(request)
|
||
_require_admin(telegram_id)
|
||
|
||
body = await request.json()
|
||
word_pl = (body.get("word_pl") or "").strip()
|
||
translation_ru = (body.get("translation_ru") or "").strip()
|
||
translation_uk = (body.get("translation_uk") or "").strip()
|
||
translation_en = (body.get("translation_en") or "").strip()
|
||
|
||
if not word_pl:
|
||
raise HTTPException(400, "word_pl is required")
|
||
|
||
pool = await get_pool()
|
||
async with pool.acquire() as conn:
|
||
existing = await conn.fetchrow("""
|
||
SELECT level, word_pl FROM level_words WHERE id = $1
|
||
""", word_id)
|
||
|
||
if not existing:
|
||
raise HTTPException(404, "Word not found")
|
||
|
||
old_word_pl = existing["word_pl"]
|
||
level = existing["level"]
|
||
|
||
# 🔧 ФИКС: обновляем все строки с этим (level, old_word_pl)
|
||
result = await conn.execute("""
|
||
UPDATE level_words
|
||
SET word_pl = $1,
|
||
translation_ru = $2,
|
||
translation_uk = $3,
|
||
translation_en = $4,
|
||
edited_at = NOW()
|
||
WHERE level = $5 AND word_pl = $6
|
||
""", word_pl, translation_ru, translation_uk, translation_en, level, old_word_pl)
|
||
|
||
affected = int(result.split()[-1])
|
||
|
||
logger.info(
|
||
f"[admin_content] word '{old_word_pl}' -> '{word_pl}' in {level}: "
|
||
f"{affected} rows updated by admin {telegram_id}"
|
||
)
|
||
|
||
return {"status": "ok", "word_id": word_id, "rows_updated": affected}
|
||
|
||
|
||
@router.post("/words/level/{level}")
|
||
async def create_word(request: Request, level: str):
|
||
"""Добавить слово в уровень (level_id = NULL)."""
|
||
telegram_id, _ = await require_auth(request)
|
||
_require_admin(telegram_id)
|
||
|
||
if level not in LEVELS:
|
||
raise HTTPException(400, f"Invalid level: {level}")
|
||
|
||
body = await request.json()
|
||
word_pl = (body.get("word_pl") or "").strip()
|
||
translation_ru = (body.get("translation_ru") or "").strip()
|
||
translation_uk = (body.get("translation_uk") or "").strip()
|
||
translation_en = (body.get("translation_en") or "").strip()
|
||
|
||
if not word_pl:
|
||
raise HTTPException(400, "word_pl is required")
|
||
|
||
pool = await get_pool()
|
||
async with pool.acquire() as conn:
|
||
existing = await conn.fetchval("""
|
||
SELECT id FROM level_words
|
||
WHERE level = $1 AND level_id IS NULL AND LOWER(word_pl) = LOWER($2)
|
||
""", level, word_pl)
|
||
if existing:
|
||
raise HTTPException(409, f"Word '{word_pl}' already exists in {level}")
|
||
|
||
row = await conn.fetchrow("""
|
||
INSERT INTO level_words
|
||
(level_id, word_pl, translation_ru, translation_uk, translation_en, level, edited_at)
|
||
VALUES (NULL, $1, $2, $3, $4, $5, NOW())
|
||
RETURNING id
|
||
""", word_pl, translation_ru, translation_uk, translation_en, level)
|
||
|
||
logger.info(
|
||
f"[admin_content] word created: #{row['id']} '{word_pl}' in {level} "
|
||
f"by admin {telegram_id}"
|
||
)
|
||
|
||
return {"status": "ok", "word_id": row["id"]}
|
||
|
||
|
||
# ============ TEXTS (sentences) ============
|
||
|
||
@router.get("/texts/levels")
|
||
async def get_text_levels(request: Request):
|
||
"""Уровни A1–C2 с количеством текстов и последней правкой."""
|
||
telegram_id, _ = await require_auth(request)
|
||
_require_admin(telegram_id)
|
||
|
||
pool = await get_pool()
|
||
async with pool.acquire() as conn:
|
||
level_rows = await conn.fetch("""
|
||
SELECT code, name_ru, sort_order
|
||
FROM learning_levels
|
||
WHERE is_active = TRUE
|
||
ORDER BY sort_order
|
||
""")
|
||
|
||
result = []
|
||
for lvl in level_rows:
|
||
code = lvl["code"]
|
||
count = await conn.fetchval(
|
||
"SELECT COUNT(*) FROM sentences WHERE level = $1", code
|
||
) or 0
|
||
last_edited = await conn.fetchval(
|
||
"SELECT MAX(edited_at) FROM sentences WHERE level = $1", code
|
||
)
|
||
result.append({
|
||
"code": code,
|
||
"name_ru": lvl["name_ru"],
|
||
"texts_count": count,
|
||
"last_edited": last_edited.isoformat() if last_edited else None,
|
||
})
|
||
|
||
return {"levels": result}
|
||
|
||
|
||
@router.get("/texts/level/{level}")
|
||
async def get_level_texts(
|
||
request: Request,
|
||
level: str,
|
||
q: Optional[str] = None,
|
||
limit: int = 1000,
|
||
):
|
||
"""Список текстов уровня с опциональным поиском."""
|
||
telegram_id, _ = await require_auth(request)
|
||
_require_admin(telegram_id)
|
||
|
||
if level not in LEVELS:
|
||
raise HTTPException(400, f"Invalid level: {level}")
|
||
|
||
if limit < 1:
|
||
limit = 1
|
||
elif limit > 2000:
|
||
limit = 2000
|
||
|
||
pool = await get_pool()
|
||
async with pool.acquire() as conn:
|
||
if q and q.strip():
|
||
pattern = f"%{q.strip()}%"
|
||
rows = await conn.fetch("""
|
||
SELECT id, sentence_number, sentence_pl,
|
||
translation_ru, translation_uk, translation_en,
|
||
difficulty, edited_at
|
||
FROM sentences
|
||
WHERE level = $1
|
||
AND (
|
||
sentence_pl ILIKE $2
|
||
OR translation_ru ILIKE $2
|
||
OR translation_uk ILIKE $2
|
||
OR translation_en ILIKE $2
|
||
)
|
||
ORDER BY sentence_number ASC
|
||
LIMIT $3
|
||
""", level, pattern, limit)
|
||
else:
|
||
rows = await conn.fetch("""
|
||
SELECT id, sentence_number, sentence_pl,
|
||
translation_ru, translation_uk, translation_en,
|
||
difficulty, edited_at
|
||
FROM sentences
|
||
WHERE level = $1
|
||
ORDER BY sentence_number ASC
|
||
LIMIT $2
|
||
""", level, limit)
|
||
|
||
return {
|
||
"level": level,
|
||
"count": len(rows),
|
||
"texts": [
|
||
{
|
||
"id": r["id"],
|
||
"sentence_number": r["sentence_number"],
|
||
"sentence_pl": r["sentence_pl"],
|
||
"translation_ru": r["translation_ru"] or "",
|
||
"translation_uk": r["translation_uk"] or "",
|
||
"translation_en": r["translation_en"] or "",
|
||
"difficulty": r["difficulty"],
|
||
"edited_at": r["edited_at"].isoformat() if r["edited_at"] else None,
|
||
}
|
||
for r in rows
|
||
],
|
||
}
|
||
|
||
|
||
@router.get("/texts/text/{text_id}")
|
||
async def get_text(request: Request, text_id: int):
|
||
"""Один текст по id."""
|
||
telegram_id, _ = await require_auth(request)
|
||
_require_admin(telegram_id)
|
||
|
||
pool = await get_pool()
|
||
async with pool.acquire() as conn:
|
||
row = await conn.fetchrow("""
|
||
SELECT id, sentence_number, sentence_pl,
|
||
translation_ru, translation_uk, translation_en,
|
||
difficulty, level, edited_at
|
||
FROM sentences WHERE id = $1
|
||
""", text_id)
|
||
|
||
if not row:
|
||
raise HTTPException(404, "Text not found")
|
||
|
||
return {
|
||
"id": row["id"],
|
||
"sentence_number": row["sentence_number"],
|
||
"sentence_pl": row["sentence_pl"],
|
||
"translation_ru": row["translation_ru"] or "",
|
||
"translation_uk": row["translation_uk"] or "",
|
||
"translation_en": row["translation_en"] or "",
|
||
"difficulty": row["difficulty"],
|
||
"level": row["level"],
|
||
"edited_at": row["edited_at"].isoformat() if row["edited_at"] else None,
|
||
}
|
||
|
||
|
||
def _compute_difficulty(sentence_pl: str) -> int:
|
||
"""Пересчитывает difficulty по количеству слов (как в load_sentences.py)."""
|
||
words_count = len(sentence_pl.split())
|
||
if words_count <= 4:
|
||
return 1
|
||
elif words_count <= 6:
|
||
return 2
|
||
return 3
|
||
|
||
|
||
@router.put("/texts/text/{text_id}")
|
||
async def update_text(request: Request, text_id: int):
|
||
"""
|
||
Обновить текст.
|
||
Difficulty пересчитывается автоматически по количеству слов в sentence_pl.
|
||
"""
|
||
telegram_id, _ = await require_auth(request)
|
||
_require_admin(telegram_id)
|
||
|
||
body = await request.json()
|
||
sentence_pl = (body.get("sentence_pl") or "").strip()
|
||
translation_ru = (body.get("translation_ru") or "").strip()
|
||
translation_uk = (body.get("translation_uk") or "").strip()
|
||
translation_en = (body.get("translation_en") or "").strip()
|
||
|
||
if not sentence_pl:
|
||
raise HTTPException(400, "sentence_pl is required")
|
||
|
||
difficulty = _compute_difficulty(sentence_pl)
|
||
|
||
pool = await get_pool()
|
||
async with pool.acquire() as conn:
|
||
exists = await conn.fetchval("SELECT 1 FROM sentences WHERE id = $1", text_id)
|
||
if not exists:
|
||
raise HTTPException(404, "Text not found")
|
||
|
||
await conn.execute("""
|
||
UPDATE sentences
|
||
SET sentence_pl = $1,
|
||
translation_ru = $2,
|
||
translation_uk = $3,
|
||
translation_en = $4,
|
||
difficulty = $5,
|
||
edited_at = NOW()
|
||
WHERE id = $6
|
||
""", sentence_pl, translation_ru, translation_uk, translation_en, difficulty, text_id)
|
||
|
||
logger.info(f"[admin_content] text #{text_id} updated by admin {telegram_id}")
|
||
|
||
return {"status": "ok", "text_id": text_id, "difficulty": difficulty}
|
||
|
||
|
||
@router.post("/texts/level/{level}")
|
||
async def create_text(request: Request, level: str):
|
||
"""
|
||
Добавить текст в уровень.
|
||
Нумерация: max(sentence_number) + 1 для этого уровня.
|
||
"""
|
||
telegram_id, _ = await require_auth(request)
|
||
_require_admin(telegram_id)
|
||
|
||
if level not in LEVELS:
|
||
raise HTTPException(400, f"Invalid level: {level}")
|
||
|
||
body = await request.json()
|
||
sentence_pl = (body.get("sentence_pl") or "").strip()
|
||
translation_ru = (body.get("translation_ru") or "").strip()
|
||
translation_uk = (body.get("translation_uk") or "").strip()
|
||
translation_en = (body.get("translation_en") or "").strip()
|
||
|
||
if not sentence_pl:
|
||
raise HTTPException(400, "sentence_pl is required")
|
||
|
||
difficulty = _compute_difficulty(sentence_pl)
|
||
|
||
pool = await get_pool()
|
||
async with pool.acquire() as conn:
|
||
existing = await conn.fetchval("""
|
||
SELECT id FROM sentences WHERE level = $1 AND LOWER(sentence_pl) = LOWER($2)
|
||
""", level, sentence_pl)
|
||
if existing:
|
||
raise HTTPException(409, f"This sentence already exists in {level}")
|
||
|
||
next_num = await conn.fetchval("""
|
||
SELECT COALESCE(MAX(sentence_number), 0) + 1 FROM sentences WHERE level = $1
|
||
""", level) or 1
|
||
|
||
row = await conn.fetchrow("""
|
||
INSERT INTO sentences
|
||
(sentence_number, sentence_pl, translation_ru, translation_uk, translation_en, difficulty, level, edited_at)
|
||
VALUES ($1, $2, $3, $4, $5, $6, $7, NOW())
|
||
RETURNING id
|
||
""", next_num, sentence_pl, translation_ru, translation_uk, translation_en, difficulty, level)
|
||
|
||
logger.info(
|
||
f"[admin_content] text created: #{row['id']} in {level} by admin {telegram_id}"
|
||
)
|
||
|
||
return {"status": "ok", "text_id": row["id"], "sentence_number": next_num}
|
||
|
||
|
||
# ============ LESSONS ============
|
||
|
||
@router.get("/lessons/levels")
|
||
async def get_lesson_levels(request: Request):
|
||
"""
|
||
Уровни A1–C2 с количеством тем и последней правкой.
|
||
Считаем только активные темы.
|
||
"""
|
||
telegram_id, _ = await require_auth(request)
|
||
_require_admin(telegram_id)
|
||
|
||
pool = await get_pool()
|
||
async with pool.acquire() as conn:
|
||
level_rows = await conn.fetch("""
|
||
SELECT code, name_ru, sort_order
|
||
FROM learning_levels
|
||
WHERE is_active = TRUE
|
||
ORDER BY sort_order
|
||
""")
|
||
|
||
result = []
|
||
for lvl in level_rows:
|
||
code = lvl["code"]
|
||
|
||
topics_count = await conn.fetchval("""
|
||
SELECT COUNT(*) FROM learning_topics
|
||
WHERE level_code = $1 AND is_active = TRUE
|
||
""", code) or 0
|
||
|
||
lessons_count = await conn.fetchval("""
|
||
SELECT COUNT(*) FROM learning_lessons l
|
||
JOIN learning_topics t ON l.topic_id = t.topic_id
|
||
WHERE t.level_code = $1 AND l.is_active = TRUE
|
||
""", code) or 0
|
||
|
||
last_edited = await conn.fetchval("""
|
||
SELECT MAX(edited_at) FROM (
|
||
SELECT t.edited_at FROM learning_topics t
|
||
WHERE t.level_code = $1
|
||
UNION ALL
|
||
SELECT l.edited_at FROM learning_lessons l
|
||
JOIN learning_topics t ON l.topic_id = t.topic_id
|
||
WHERE t.level_code = $1
|
||
) sub
|
||
""", code)
|
||
|
||
result.append({
|
||
"code": code,
|
||
"name_ru": lvl["name_ru"],
|
||
"topics_count": topics_count,
|
||
"lessons_count": lessons_count,
|
||
"last_edited": last_edited.isoformat() if last_edited else None,
|
||
})
|
||
|
||
return {"levels": result}
|
||
|
||
|
||
@router.get("/lessons/level/{level}/topics")
|
||
async def get_lesson_topics(request: Request, level: str):
|
||
"""
|
||
Темы уровня с количеством уроков и вопросов в тесте.
|
||
Сортировка: order_number ASC.
|
||
"""
|
||
telegram_id, _ = await require_auth(request)
|
||
_require_admin(telegram_id)
|
||
|
||
if level not in LEVELS:
|
||
raise HTTPException(400, f"Invalid level: {level}")
|
||
|
||
pool = await get_pool()
|
||
async with pool.acquire() as conn:
|
||
rows = await conn.fetch("""
|
||
SELECT
|
||
topic_id,
|
||
order_number,
|
||
name_pl, name_ru, name_uk, name_en,
|
||
is_active
|
||
FROM learning_topics
|
||
WHERE level_code = $1 AND is_active = TRUE
|
||
ORDER BY order_number ASC
|
||
""", level)
|
||
|
||
result = []
|
||
for r in rows:
|
||
topic_id = r["topic_id"]
|
||
|
||
lessons_count = await conn.fetchval("""
|
||
SELECT COUNT(*) FROM learning_lessons
|
||
WHERE topic_id = $1 AND is_active = TRUE
|
||
""", topic_id) or 0
|
||
|
||
test_questions_count = await conn.fetchval("""
|
||
SELECT COUNT(*) FROM learning_questions
|
||
WHERE topic_id = $1 AND is_test_question = TRUE
|
||
""", topic_id) or 0
|
||
|
||
result.append({
|
||
"topic_id": topic_id,
|
||
"order_number": r["order_number"],
|
||
"name_pl": r["name_pl"],
|
||
"name_ru": r["name_ru"],
|
||
"name_uk": r["name_uk"],
|
||
"name_en": r["name_en"],
|
||
"lessons_count": lessons_count,
|
||
"test_questions_count": test_questions_count,
|
||
})
|
||
|
||
return {"level": level, "topics": result}
|
||
|
||
|
||
@router.get("/lessons/topic/{topic_id}")
|
||
async def get_lesson_topic(request: Request, topic_id: str):
|
||
"""
|
||
Тема целиком: инфо о теме + список уроков + информация о тесте темы.
|
||
Уроки сортируются по order_number ASC.
|
||
"""
|
||
telegram_id, _ = await require_auth(request)
|
||
_require_admin(telegram_id)
|
||
|
||
pool = await get_pool()
|
||
async with pool.acquire() as conn:
|
||
topic = await conn.fetchrow("""
|
||
SELECT topic_id, level_code, order_number,
|
||
name_pl, name_ru, name_uk, name_en,
|
||
description_pl, description_ru, description_uk, description_en
|
||
FROM learning_topics
|
||
WHERE topic_id = $1
|
||
""", topic_id)
|
||
|
||
if not topic:
|
||
raise HTTPException(404, "Topic not found")
|
||
|
||
lessons_rows = await conn.fetch("""
|
||
SELECT lesson_id, order_number, skill_tag,
|
||
title_pl, title_ru, title_uk, title_en,
|
||
is_active
|
||
FROM learning_lessons
|
||
WHERE topic_id = $1 AND is_active = TRUE
|
||
ORDER BY order_number ASC
|
||
""", topic_id)
|
||
|
||
lessons = []
|
||
for l in lessons_rows:
|
||
lessons.append({
|
||
"lesson_id": l["lesson_id"],
|
||
"order_number": l["order_number"],
|
||
"skill_tag": l["skill_tag"],
|
||
"title_pl": l["title_pl"],
|
||
"title_ru": l["title_ru"],
|
||
"title_uk": l["title_uk"],
|
||
"title_en": l["title_en"],
|
||
})
|
||
|
||
# Считаем вопросы теста темы
|
||
test_questions_count = await conn.fetchval("""
|
||
SELECT COUNT(*) FROM learning_questions
|
||
WHERE topic_id = $1 AND is_test_question = TRUE
|
||
""", topic_id) or 0
|
||
|
||
# Проверяем, есть ли вообще тест-вопросы
|
||
has_test = test_questions_count > 0
|
||
|
||
return {
|
||
"topic": {
|
||
"topic_id": topic["topic_id"],
|
||
"level_code": topic["level_code"],
|
||
"order_number": topic["order_number"],
|
||
"name_pl": topic["name_pl"],
|
||
"name_ru": topic["name_ru"],
|
||
"name_uk": topic["name_uk"],
|
||
"name_en": topic["name_en"],
|
||
"description_pl": topic["description_pl"],
|
||
"description_ru": topic["description_ru"],
|
||
"description_uk": topic["description_uk"],
|
||
"description_en": topic["description_en"],
|
||
},
|
||
"lessons": lessons,
|
||
"has_test": has_test,
|
||
"test_questions_count": test_questions_count,
|
||
}
|
||
|
||
|
||
@router.get("/lessons/lesson/{lesson_id}")
|
||
async def get_lesson(request: Request, lesson_id: str):
|
||
"""
|
||
Урок целиком для WYSIWYG-редактора:
|
||
- lesson: title, objective, explanation (4 языка) + метаданные
|
||
- examples: список примеров (order_number ASC)
|
||
- questions: список вопросов урока (is_test_question = FALSE)
|
||
- options: список вариантов
|
||
- wrong_because_text: человекочитаемый текст для неправильных вариантов
|
||
"""
|
||
telegram_id, _ = await require_auth(request)
|
||
_require_admin(telegram_id)
|
||
|
||
pool = await get_pool()
|
||
async with pool.acquire() as conn:
|
||
lesson = await conn.fetchrow("""
|
||
SELECT lesson_id, topic_id, order_number, skill_tag,
|
||
title_pl, title_ru, title_uk, title_en,
|
||
objective_pl, objective_ru, objective_uk, objective_en,
|
||
explanation_pl, explanation_ru, explanation_uk, explanation_en,
|
||
is_active
|
||
FROM learning_lessons
|
||
WHERE lesson_id = $1
|
||
""", lesson_id)
|
||
|
||
if not lesson:
|
||
raise HTTPException(404, "Lesson not found")
|
||
|
||
# Примеры
|
||
examples_rows = await conn.fetch("""
|
||
SELECT id, order_number, text_pl, text_ru, text_uk, text_en, audio_url
|
||
FROM lesson_examples
|
||
WHERE lesson_id = $1
|
||
ORDER BY order_number ASC
|
||
""", lesson_id)
|
||
|
||
examples = [
|
||
{
|
||
"id": e["id"],
|
||
"order_number": e["order_number"],
|
||
"text_pl": e["text_pl"] or "",
|
||
"text_ru": e["text_ru"] or "",
|
||
"text_uk": e["text_uk"] or "",
|
||
"text_en": e["text_en"] or "",
|
||
"audio_url": e["audio_url"],
|
||
}
|
||
for e in examples_rows
|
||
]
|
||
|
||
# Вопросы урока (не тест темы)
|
||
questions_rows = await conn.fetch("""
|
||
SELECT question_id, context_type, question_type, skill_tag, difficulty,
|
||
prompt_pl, prompt_ru, prompt_uk, prompt_en,
|
||
correct_answer, hint_type, hint_value, options_language,
|
||
is_test_question, test_order
|
||
FROM learning_questions
|
||
WHERE lesson_id = $1 AND is_test_question = FALSE
|
||
ORDER BY id ASC
|
||
""", lesson_id)
|
||
|
||
# Справочник wrong_because → человекочитаемый текст (RU)
|
||
wb_rows = await conn.fetch("""
|
||
SELECT wrong_because, explanation_ru
|
||
FROM wrong_because_explanations
|
||
""")
|
||
wb_map = {w["wrong_because"]: w["explanation_ru"] for w in wb_rows}
|
||
|
||
questions = []
|
||
for q in questions_rows:
|
||
options_rows = await conn.fetch("""
|
||
SELECT option_key, text_pl, text_ru, text_uk, text_en,
|
||
is_correct, wrong_because
|
||
FROM question_options
|
||
WHERE question_id = $1
|
||
ORDER BY option_key ASC
|
||
""", q["question_id"])
|
||
|
||
options = []
|
||
for o in options_rows:
|
||
wb_code = o["wrong_because"]
|
||
options.append({
|
||
"option_key": o["option_key"],
|
||
"text_pl": o["text_pl"] or "",
|
||
"text_ru": o["text_ru"] or "",
|
||
"text_uk": o["text_uk"] or "",
|
||
"text_en": o["text_en"] or "",
|
||
"is_correct": o["is_correct"],
|
||
"wrong_because": wb_code,
|
||
"wrong_because_text": wb_map.get(wb_code, "") if wb_code else "",
|
||
})
|
||
|
||
questions.append({
|
||
"question_id": q["question_id"],
|
||
"context_type": q["context_type"],
|
||
"question_type": q["question_type"],
|
||
"skill_tag": q["skill_tag"],
|
||
"difficulty": q["difficulty"],
|
||
"prompt_pl": q["prompt_pl"] or "",
|
||
"prompt_ru": q["prompt_ru"] or "",
|
||
"prompt_uk": q["prompt_uk"] or "",
|
||
"prompt_en": q["prompt_en"] or "",
|
||
"correct_answer": q["correct_answer"] or "",
|
||
"hint_type": q["hint_type"],
|
||
"hint_value": q["hint_value"],
|
||
"options_language": q["options_language"] or "pl",
|
||
"options": options,
|
||
})
|
||
|
||
return {
|
||
"lesson": {
|
||
"lesson_id": lesson["lesson_id"],
|
||
"topic_id": lesson["topic_id"],
|
||
"order_number": lesson["order_number"],
|
||
"skill_tag": lesson["skill_tag"],
|
||
"title_pl": lesson["title_pl"] or "",
|
||
"title_ru": lesson["title_ru"] or "",
|
||
"title_uk": lesson["title_uk"] or "",
|
||
"title_en": lesson["title_en"] or "",
|
||
"objective_pl": lesson["objective_pl"] or "",
|
||
"objective_ru": lesson["objective_ru"] or "",
|
||
"objective_uk": lesson["objective_uk"] or "",
|
||
"objective_en": lesson["objective_en"] or "",
|
||
"explanation_pl": lesson["explanation_pl"] or "",
|
||
"explanation_ru": lesson["explanation_ru"] or "",
|
||
"explanation_uk": lesson["explanation_uk"] or "",
|
||
"explanation_en": lesson["explanation_en"] or "",
|
||
},
|
||
"examples": examples,
|
||
"questions": questions,
|
||
}
|
||
|
||
|
||
@router.put("/lessons/lesson/{lesson_id}")
|
||
async def update_lesson(request: Request, lesson_id: str):
|
||
"""
|
||
Сохранить урок целиком (Вариант B) — одна транзакция:
|
||
1. UPDATE learning_lessons (title, objective, explanation, edited_at).
|
||
2. DELETE + INSERT lesson_examples.
|
||
3. UPDATE learning_questions (prompt, correct_answer, hint_type, hint_value, edited_at).
|
||
4. DELETE + INSERT question_options (option_key генерируется A/B/C/D/...).
|
||
|
||
Тело запроса:
|
||
{
|
||
"title_pl": "...", "title_en": "...", "title_ru": "...", "title_uk": "...",
|
||
"objective_pl": "...", "objective_en": "...", "objective_ru": "...", "objective_uk": "...",
|
||
"explanation_pl": "...", "explanation_en": "...", "explanation_ru": "...", "explanation_uk": "...",
|
||
"examples": [
|
||
{"text_pl": "...", "text_en": "...", "text_ru": "...", "text_uk": "..."}
|
||
],
|
||
"questions": [
|
||
{
|
||
"question_id": "a1_greetings_l01_q1",
|
||
"prompt_pl": "...", "prompt_en": "...", "prompt_ru": "...", "prompt_uk": "...",
|
||
"hint_type": "first_letter" | null,
|
||
"hint_value": "j" | null,
|
||
"options": [
|
||
{"text_pl": "...", "text_en": "...", "text_ru": "...", "text_uk": "...",
|
||
"is_correct": true, "wrong_because": null},
|
||
{"text_pl": "...", "text_en": "...", "text_ru": "...", "text_uk": "...",
|
||
"is_correct": false, "wrong_because": "wrong_case"}
|
||
]
|
||
}
|
||
]
|
||
}
|
||
"""
|
||
telegram_id, _ = await require_auth(request)
|
||
_require_admin(telegram_id)
|
||
|
||
body = await request.json()
|
||
|
||
# ============ 1. Валидация ============
|
||
|
||
title_pl = (body.get("title_pl") or "").strip()
|
||
title_ru = (body.get("title_ru") or "").strip()
|
||
title_uk = (body.get("title_uk") or "").strip()
|
||
title_en = (body.get("title_en") or "").strip()
|
||
|
||
if not title_pl:
|
||
raise HTTPException(400, "title_pl is required")
|
||
|
||
objective_pl = (body.get("objective_pl") or "").strip()
|
||
objective_ru = (body.get("objective_ru") or "").strip()
|
||
objective_uk = (body.get("objective_uk") or "").strip()
|
||
objective_en = (body.get("objective_en") or "").strip()
|
||
|
||
explanation_pl = (body.get("explanation_pl") or "").strip()
|
||
explanation_ru = (body.get("explanation_ru") or "").strip()
|
||
explanation_uk = (body.get("explanation_uk") or "").strip()
|
||
explanation_en = (body.get("explanation_en") or "").strip()
|
||
|
||
examples_raw = body.get("examples") or []
|
||
if not isinstance(examples_raw, list):
|
||
raise HTTPException(400, "examples must be a list")
|
||
|
||
examples = []
|
||
for ex in examples_raw:
|
||
text_pl = (ex.get("text_pl") or "").strip()
|
||
if not text_pl:
|
||
continue # пропускаем пустые примеры
|
||
examples.append({
|
||
"text_pl": text_pl,
|
||
"text_ru": (ex.get("text_ru") or "").strip(),
|
||
"text_uk": (ex.get("text_uk") or "").strip(),
|
||
"text_en": (ex.get("text_en") or "").strip(),
|
||
})
|
||
|
||
questions_raw = body.get("questions") or []
|
||
if not isinstance(questions_raw, list):
|
||
raise HTTPException(400, "questions must be a list")
|
||
|
||
# Валидация вопросов
|
||
questions = []
|
||
for q in questions_raw:
|
||
question_id = (q.get("question_id") or "").strip()
|
||
if not question_id:
|
||
raise HTTPException(400, "question_id is required for every question")
|
||
|
||
prompt_pl = (q.get("prompt_pl") or "").strip()
|
||
if not prompt_pl:
|
||
raise HTTPException(400, f"prompt_pl is required for question {question_id}")
|
||
|
||
options_raw = q.get("options") or []
|
||
if not isinstance(options_raw, list) or len(options_raw) == 0:
|
||
raise HTTPException(400, f"At least one option required for question {question_id}")
|
||
|
||
# Проверка: ровно один правильный вариант
|
||
correct_count = sum(1 for o in options_raw if o.get("is_correct") is True)
|
||
if correct_count != 1:
|
||
raise HTTPException(
|
||
400,
|
||
f"Question {question_id}: exactly one option must be is_correct=true "
|
||
f"(got {correct_count})"
|
||
)
|
||
|
||
# Генерируем option_key A, B, C, D...
|
||
options = []
|
||
correct_answer = None
|
||
for idx, o in enumerate(options_raw):
|
||
option_key = chr(ord('A') + idx) # A, B, C, D...
|
||
|
||
text_pl_opt = (o.get("text_pl") or "").strip()
|
||
if not text_pl_opt:
|
||
raise HTTPException(
|
||
400,
|
||
f"Option {option_key} of question {question_id}: text_pl is required"
|
||
)
|
||
|
||
is_correct = bool(o.get("is_correct"))
|
||
if is_correct:
|
||
correct_answer = option_key
|
||
|
||
wrong_because = o.get("wrong_because")
|
||
if is_correct:
|
||
wrong_because = None # у правильного wrong_because нет
|
||
elif not wrong_because:
|
||
wrong_because = None # оставляем None если пусто
|
||
|
||
options.append({
|
||
"option_key": option_key,
|
||
"text_pl": text_pl_opt,
|
||
"text_ru": (o.get("text_ru") or "").strip(),
|
||
"text_uk": (o.get("text_uk") or "").strip(),
|
||
"text_en": (o.get("text_en") or "").strip(),
|
||
"is_correct": is_correct,
|
||
"wrong_because": wrong_because,
|
||
})
|
||
|
||
hint_type = q.get("hint_type")
|
||
if hint_type:
|
||
hint_type = str(hint_type).strip() or None
|
||
|
||
hint_value = q.get("hint_value")
|
||
if hint_value:
|
||
hint_value = str(hint_value).strip() or None
|
||
|
||
questions.append({
|
||
"question_id": question_id,
|
||
"prompt_pl": prompt_pl,
|
||
"prompt_ru": (q.get("prompt_ru") or "").strip(),
|
||
"prompt_uk": (q.get("prompt_uk") or "").strip(),
|
||
"prompt_en": (q.get("prompt_en") or "").strip(),
|
||
"correct_answer": correct_answer,
|
||
"hint_type": hint_type,
|
||
"hint_value": hint_value,
|
||
"options": options,
|
||
})
|
||
|
||
# ============ 2. Транзакция ============
|
||
|
||
pool = await get_pool()
|
||
async with pool.acquire() as conn:
|
||
async with conn.transaction():
|
||
# Проверяем, что урок существует
|
||
exists = await conn.fetchval(
|
||
"SELECT 1 FROM learning_lessons WHERE lesson_id = $1", lesson_id
|
||
)
|
||
if not exists:
|
||
raise HTTPException(404, "Lesson not found")
|
||
|
||
# --- 2.1. UPDATE learning_lessons ---
|
||
await conn.execute("""
|
||
UPDATE learning_lessons
|
||
SET title_pl = $1,
|
||
title_ru = $2,
|
||
title_uk = $3,
|
||
title_en = $4,
|
||
objective_pl = $5,
|
||
objective_ru = $6,
|
||
objective_uk = $7,
|
||
objective_en = $8,
|
||
explanation_pl = $9,
|
||
explanation_ru = $10,
|
||
explanation_uk = $11,
|
||
explanation_en = $12,
|
||
edited_at = NOW()
|
||
WHERE lesson_id = $13
|
||
""",
|
||
title_pl, title_ru, title_uk, title_en,
|
||
objective_pl, objective_ru, objective_uk, objective_en,
|
||
explanation_pl, explanation_ru, explanation_uk, explanation_en,
|
||
lesson_id,
|
||
)
|
||
|
||
# --- 2.2. DELETE + INSERT lesson_examples ---
|
||
await conn.execute(
|
||
"DELETE FROM lesson_examples WHERE lesson_id = $1", lesson_id
|
||
)
|
||
|
||
for idx, ex in enumerate(examples, start=1):
|
||
await conn.execute("""
|
||
INSERT INTO lesson_examples
|
||
(lesson_id, order_number, text_pl, text_ru, text_uk, text_en)
|
||
VALUES ($1, $2, $3, $4, $5, $6)
|
||
""",
|
||
lesson_id, idx,
|
||
ex["text_pl"], ex["text_ru"], ex["text_uk"], ex["text_en"],
|
||
)
|
||
|
||
# --- 2.3. UPDATE learning_questions + DELETE + INSERT question_options ---
|
||
for q in questions:
|
||
# Проверяем, что вопрос принадлежит этому уроку
|
||
q_exists = await conn.fetchval("""
|
||
SELECT 1 FROM learning_questions
|
||
WHERE question_id = $1 AND lesson_id = $2
|
||
""", q["question_id"], lesson_id)
|
||
|
||
if not q_exists:
|
||
raise HTTPException(
|
||
400,
|
||
f"Question {q['question_id']} does not belong to lesson {lesson_id}"
|
||
)
|
||
|
||
await conn.execute("""
|
||
UPDATE learning_questions
|
||
SET prompt_pl = $1,
|
||
prompt_ru = $2,
|
||
prompt_uk = $3,
|
||
prompt_en = $4,
|
||
correct_answer = $5,
|
||
hint_type = $6,
|
||
hint_value = $7,
|
||
edited_at = NOW()
|
||
WHERE question_id = $8
|
||
""",
|
||
q["prompt_pl"], q["prompt_ru"], q["prompt_uk"], q["prompt_en"],
|
||
q["correct_answer"], q["hint_type"], q["hint_value"],
|
||
q["question_id"],
|
||
)
|
||
|
||
# Удаляем старые опции
|
||
await conn.execute(
|
||
"DELETE FROM question_options WHERE question_id = $1",
|
||
q["question_id"],
|
||
)
|
||
|
||
# Вставляем новые
|
||
for o in q["options"]:
|
||
await conn.execute("""
|
||
INSERT INTO question_options
|
||
(question_id, option_key, text_pl, text_ru, text_uk, text_en,
|
||
is_correct, wrong_because)
|
||
VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
|
||
""",
|
||
q["question_id"], o["option_key"],
|
||
o["text_pl"], o["text_ru"], o["text_uk"], o["text_en"],
|
||
o["is_correct"], o["wrong_because"],
|
||
)
|
||
|
||
logger.info(
|
||
f"[admin_content] lesson '{lesson_id}' updated by admin {telegram_id}: "
|
||
f"{len(examples)} examples, {len(questions)} questions"
|
||
)
|
||
|
||
return {
|
||
"status": "ok",
|
||
"lesson_id": lesson_id,
|
||
"examples_count": len(examples),
|
||
"questions_count": len(questions),
|
||
}
|
||
|
||
|
||
# ============ WRONG BECAUSE (справочник) ============
|
||
|
||
@router.get("/wrong-because")
|
||
async def get_wrong_because(request: Request):
|
||
"""
|
||
Справочник wrong_because — 7 записей.
|
||
Отдаём код + 4 языка, чтобы фронт мог показать человекочитаемый текст
|
||
на языке интерфейса препода.
|
||
"""
|
||
telegram_id, _ = await require_auth(request)
|
||
_require_admin(telegram_id)
|
||
|
||
pool = await get_pool()
|
||
async with pool.acquire() as conn:
|
||
rows = await conn.fetch("""
|
||
SELECT wrong_because, explanation_pl, explanation_ru,
|
||
explanation_uk, explanation_en
|
||
FROM wrong_because_explanations
|
||
ORDER BY wrong_because ASC
|
||
""")
|
||
|
||
return {
|
||
"items": [
|
||
{
|
||
"wrong_because": r["wrong_because"],
|
||
"explanation_pl": r["explanation_pl"] or "",
|
||
"explanation_ru": r["explanation_ru"] or "",
|
||
"explanation_uk": r["explanation_uk"] or "",
|
||
"explanation_en": r["explanation_en"] or "",
|
||
}
|
||
for r in rows
|
||
]
|
||
} |