2 changed files with 27 additions and 30 deletions
+5 -16
View File
@@ -258,31 +258,20 @@ async def init_db():
)
""")
# ============ Убираем составной UNIQUE (level, sentence_number) ============
# ============ Убираем старый UNIQUE constraint с sentences.sentence_number ============
await conn.execute("""
ALTER TABLE sentences DROP CONSTRAINT IF EXISTS sentences_sentence_number_unique
ALTER TABLE sentences DROP CONSTRAINT IF EXISTS sentences_sentence_number_key
""")
# ============ Пересчёт номеров: единый пул 1..N ============
await conn.execute("""
UPDATE sentences s
SET sentence_number = sub.rn
FROM (
SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS rn
FROM sentences
) sub
WHERE s.id = sub.id
""")
# ============ Добавляем UNIQUE (sentence_number) ============
# ============ Добавляем составной UNIQUE (level, sentence_number) ============
await conn.execute("""
DO $$
BEGIN
IF NOT EXISTS (
SELECT 1 FROM pg_constraint
WHERE conname = 'sentences_sentence_number_key'
WHERE conname = 'sentences_sentence_number_unique'
) THEN
ALTER TABLE sentences ADD CONSTRAINT sentences_sentence_number_key UNIQUE (sentence_number);
ALTER TABLE sentences ADD CONSTRAINT sentences_sentence_number_unique UNIQUE (level, sentence_number);
END IF;
END $$;
""")
+22 -14
View File
@@ -65,7 +65,6 @@ async def init_sentences():
("B1", "sentences_b1.csv"),
]
offset = 0
for level, filename in level_files:
sentences = load_sentences_from_csv(filename)
for i, s in enumerate(sentences):
@@ -75,12 +74,11 @@ async def init_sentences():
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 (sentence_number) DO NOTHING""",
offset + i + 1, s["sentence_pl"],
ON CONFLICT (level, sentence_number) DO NOTHING""",
i + 1, s["sentence_pl"],
s["translation_ru"], s["translation_uk"], s["translation_en"],
difficulty, level
)
offset += len(sentences)
def normalize_word_for_display(word: str) -> str:
@@ -102,23 +100,25 @@ async def get_next_sentence(request: Request):
pool = await get_pool()
async with pool.acquire() as conn:
user = await conn.fetchrow(
"SELECT app_language, difficulty FROM users WHERE telegram_id = $1", telegram_id
"SELECT app_language, current_level, difficulty 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"
difficulty = user["difficulty"] or "medium"
# Находим случайное непройденное предложение (единый пул)
# Находим случайное непройденное предложение нужного уровня
row = await conn.fetchrow("""
SELECT s.* FROM sentences s
WHERE s.id NOT IN (
SELECT sentence_id FROM sentence_progress WHERE user_id = $1 AND completed_at IS NOT NULL
WHERE s.level = $1
AND s.id NOT IN (
SELECT sentence_id FROM sentence_progress WHERE user_id = $2 AND completed_at IS NOT NULL
)
ORDER BY RANDOM()
LIMIT 1
""", telegram_id)
""", current_level, telegram_id)
if not row:
return {"completed": True, "message": "All sentences completed!"}
@@ -410,14 +410,21 @@ async def get_sentence_stats(request: Request):
pool = await get_pool()
async with pool.acquire() as conn:
total = await conn.fetchval("SELECT COUNT(*) FROM sentences")
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 completed_at IS NOT NULL",
telegram_id
"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 completed_at IS NOT NULL",
telegram_id
"SELECT COALESCE(SUM(score), 0) FROM sentence_progress WHERE user_id = $1 AND level = $2",
telegram_id, current_level
)
return {
@@ -426,4 +433,5 @@ async def get_sentence_stats(request: Request):
"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,
}