Compare commits
2
Commits
| Author | SHA1 | Date | |
|---|---|---|---|
|
|
bcf4db0ddb | ||
|
|
ce9f68ae51 |
+5
-16
@@ -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
-14
@@ -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,25 @@ 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, difficulty 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"
|
||||||
|
current_level = user["current_level"] or "A1"
|
||||||
difficulty = user["difficulty"] or "medium"
|
difficulty = user["difficulty"] or "medium"
|
||||||
|
|
||||||
# Находим случайное непройденное предложение (единый пул)
|
# Находим случайное непройденное предложение нужного уровня
|
||||||
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!"}
|
||||||
@@ -410,14 +410,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 +433,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,
|
||||||
}
|
}
|
||||||
Reference in New Issue
Block a user