2 changed files with 27 additions and 32 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(""" 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 ============ # ============ Добавляем составной UNIQUE (level, sentence_number) ============
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) ============
await conn.execute(""" await conn.execute("""
DO $$ DO $$
BEGIN BEGIN
IF NOT EXISTS ( IF NOT EXISTS (
SELECT 1 FROM pg_constraint SELECT 1 FROM pg_constraint
WHERE conname = 'sentences_sentence_number_key' WHERE conname = 'sentences_sentence_number_unique'
) THEN ) 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 IF;
END $$; END $$;
""") """)
+22 -16
View File
@@ -65,7 +65,6 @@ async def init_sentences():
("B1", "sentences_b1.csv"), ("B1", "sentences_b1.csv"),
] ]
offset = 0
for level, filename in level_files: for level, filename in level_files:
sentences = load_sentences_from_csv(filename) sentences = load_sentences_from_csv(filename)
for i, s in enumerate(sentences): for i, s in enumerate(sentences):
@@ -75,12 +74,11 @@ async def init_sentences():
await conn.execute( await conn.execute(
"""INSERT INTO sentences (sentence_number, sentence_pl, translation_ru, translation_uk, translation_en, difficulty, level) """INSERT INTO sentences (sentence_number, sentence_pl, translation_ru, translation_uk, translation_en, difficulty, level)
VALUES ($1, $2, $3, $4, $5, $6, $7) VALUES ($1, $2, $3, $4, $5, $6, $7)
ON CONFLICT (sentence_number) DO NOTHING""", ON CONFLICT (level, sentence_number) DO NOTHING""",
offset + i + 1, s["sentence_pl"], i + 1, s["sentence_pl"],
s["translation_ru"], s["translation_uk"], s["translation_en"], s["translation_ru"], s["translation_uk"], s["translation_en"],
difficulty, level difficulty, level
) )
offset += len(sentences)
def normalize_word_for_display(word: str) -> str: def normalize_word_for_display(word: str) -> str:
@@ -102,23 +100,24 @@ async def get_next_sentence(request: Request):
pool = await get_pool() pool = await get_pool()
async with pool.acquire() as conn: async with pool.acquire() as conn:
user = await conn.fetchrow( user = await conn.fetchrow(
"SELECT app_language, difficulty FROM users WHERE telegram_id = $1", telegram_id "SELECT app_language, current_level FROM users WHERE telegram_id = $1", telegram_id
) )
if not user: if not user:
raise HTTPException(401, "User not registered") raise HTTPException(401, "User not registered")
lang = user["app_language"] or "pl" lang = user["app_language"] or "pl"
difficulty = user["difficulty"] or "medium" current_level = user["current_level"] or "A1"
# Находим случайное непройденное предложение (единый пул) # Находим случайное непройденное предложение нужного уровня
row = await conn.fetchrow(""" row = await conn.fetchrow("""
SELECT s.* FROM sentences s SELECT s.* FROM sentences s
WHERE s.id NOT IN ( WHERE s.level = $1
SELECT sentence_id FROM sentence_progress WHERE user_id = $1 AND completed_at IS NOT NULL AND s.id NOT IN (
SELECT sentence_id FROM sentence_progress WHERE user_id = $2 AND completed_at IS NOT NULL
) )
ORDER BY RANDOM() ORDER BY RANDOM()
LIMIT 1 LIMIT 1
""", telegram_id) """, current_level, telegram_id)
if not row: if not row:
return {"completed": True, "message": "All sentences completed!"} return {"completed": True, "message": "All sentences completed!"}
@@ -148,7 +147,6 @@ async def get_next_sentence(request: Request):
"correct_words": display_words, "correct_words": display_words,
"translation": translation, "translation": translation,
"difficulty": row["difficulty"], "difficulty": row["difficulty"],
"user_difficulty": difficulty,
"level": row["level"], "level": row["level"],
} }
@@ -410,14 +408,21 @@ async def get_sentence_stats(request: Request):
pool = await get_pool() pool = await get_pool()
async with pool.acquire() as conn: 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( completed = await conn.fetchval(
"SELECT COUNT(*) FROM sentence_progress WHERE user_id = $1 AND completed_at IS NOT NULL", "SELECT COUNT(*) FROM sentence_progress WHERE user_id = $1 AND level = $2",
telegram_id telegram_id, current_level
) )
total_score = await conn.fetchval( total_score = await conn.fetchval(
"SELECT COALESCE(SUM(score), 0) FROM sentence_progress WHERE user_id = $1 AND completed_at IS NOT NULL", "SELECT COALESCE(SUM(score), 0) FROM sentence_progress WHERE user_id = $1 AND level = $2",
telegram_id telegram_id, current_level
) )
return { return {
@@ -426,4 +431,5 @@ async def get_sentence_stats(request: Request):
"remaining": total - (completed or 0), "remaining": total - (completed or 0),
"total_score": total_score or 0, "total_score": total_score or 0,
"progress_percent": round(((completed or 0) / total * 100), 1) if total > 0 else 0, "progress_percent": round(((completed or 0) / total * 100), 1) if total > 0 else 0,
"level": current_level,
} }