import asyncio import asyncpg import logging from config import DB_CONFIG logger = logging.getLogger(__name__) db_pool = None # 🔧 ФИКС (24.09.2026): get_pool_race — защита от гонки при первом обращении. _pool_lock = asyncio.Lock() async def get_pool(): global db_pool if db_pool is not None: return db_pool # 🔧 Двойная проверка: сначала без лока, потом внутри лока async with _pool_lock: if db_pool is None: db_pool = await asyncpg.create_pool(**DB_CONFIG, min_size=2, max_size=10) return db_pool async def init_db(): pool = await get_pool() async with pool.acquire() as conn: # 🔧 ФИКС (24.09.2026): init_db_no_transaction — # оборачиваем ВСЮ инициализацию в одну транзакцию. # Если упадёт на середине — БД вернётся в исходное состояние. async with conn.transaction(): # ============ users ============ await conn.execute(""" CREATE TABLE IF NOT EXISTS users ( id SERIAL PRIMARY KEY, telegram_id BIGINT UNIQUE NOT NULL, username TEXT, app_language TEXT DEFAULT 'pl', current_level TEXT DEFAULT 'A1', last_completed_level INTEGER DEFAULT 0, created_at TIMESTAMP DEFAULT NOW() ) """) # 🔧 ФИКС (30.09.2026): last_completed_by_level — # per-level счётчик прогресса. Раньше был один last_completed_level, # который смешивал A1+A2+B1 → юзер не мог открыть уровень выше max. col_exists = await conn.fetchval(""" SELECT 1 FROM information_schema.columns WHERE table_name = 'users' AND column_name = 'last_completed_by_level' """) if not col_exists: await conn.execute(""" ALTER TABLE users ADD COLUMN last_completed_by_level JSONB DEFAULT '{}'::jsonb """) logger.info("Added last_completed_by_level column to users") # Backfill из user_progress await conn.execute(""" UPDATE users u SET last_completed_by_level = COALESCE(sub.by_level, '{}'::jsonb) FROM ( SELECT user_id, jsonb_object_agg(level, max_num) AS by_level FROM ( SELECT up.user_id, up.level, MAX(l.level_number) AS max_num FROM user_progress up JOIN levels l ON l.id = up.level_id WHERE up.completed_at IS NOT NULL GROUP BY up.user_id, up.level ) inner_sub GROUP BY user_id ) sub WHERE u.telegram_id = sub.user_id """) logger.info("Backfilled last_completed_by_level from user_progress") # ============ Авто-миграция: добавляем current_level в users ============ col_exists = await conn.fetchval(""" SELECT 1 FROM information_schema.columns WHERE table_name = 'users' AND column_name = 'current_level' """) if not col_exists: await conn.execute(""" ALTER TABLE users ADD COLUMN current_level TEXT DEFAULT 'A1' """) logger.info("Added current_level column to users") # ============ levels ============ await conn.execute(""" CREATE TABLE IF NOT EXISTS levels ( id SERIAL PRIMARY KEY, level_number INTEGER NOT NULL, grid_size INTEGER NOT NULL, game_type TEXT DEFAULT 'wordsearch', level TEXT DEFAULT 'A1', grid_letters JSONB NOT NULL DEFAULT '[]', word_paths JSONB NOT NULL DEFAULT '{}' ) """) # ============ Убираем старый UNIQUE constraint с levels.level_number ============ await conn.execute(""" ALTER TABLE levels DROP CONSTRAINT IF EXISTS levels_level_number_key """) # ============ Добавляем составной UNIQUE (level, level_number) ============ await conn.execute(""" DO $$ BEGIN IF NOT EXISTS ( SELECT 1 FROM pg_constraint WHERE conname = 'levels_level_number_unique' ) THEN ALTER TABLE levels ADD CONSTRAINT levels_level_number_unique UNIQUE (level, level_number); END IF; END $$; """) # ============ level_words ============ await conn.execute(""" CREATE TABLE IF NOT EXISTS level_words ( id SERIAL PRIMARY KEY, level_id INTEGER REFERENCES levels(id) ON DELETE CASCADE, word_pl TEXT NOT NULL, translation_ru TEXT DEFAULT '', translation_uk TEXT DEFAULT '', translation_en TEXT DEFAULT '', level TEXT DEFAULT 'A1' ) """) # ============ Авто-миграция: добавляем level в level_words ============ col_exists = await conn.fetchval(""" SELECT 1 FROM information_schema.columns WHERE table_name = 'level_words' AND column_name = 'level' """) if not col_exists: await conn.execute(""" ALTER TABLE level_words ADD COLUMN level TEXT DEFAULT 'A1' """) logger.info("Added level column to level_words") # ============ user_progress ============ existing = await conn.fetchrow(""" SELECT data_type FROM information_schema.columns WHERE table_name = 'user_progress' AND column_name = 'user_id' """) if existing and existing['data_type'] != 'bigint': logger.info("Migrating user_progress.user_id from INTEGER to BIGINT") # 🔧 ФИКС (24.09.2026): drop_constraint_cascade — # убран CASCADE, чтобы не снести FK из других таблиц. # Перечисляем зависимые FK явно (пересоздадим ниже). await conn.execute(""" ALTER TABLE user_progress DROP CONSTRAINT IF EXISTS user_progress_pkey """) await conn.execute(""" ALTER TABLE user_progress DROP CONSTRAINT IF EXISTS user_progress_user_id_fkey """) await conn.execute(""" ALTER TABLE user_progress ALTER COLUMN user_id TYPE BIGINT """) # 🔧 ФИКС (25.09.2026): duplicate_delete_keeps_wrong_row — # оставляем строку с максимальным completed_at, затем score. # Удаляем все остальные дубли по (user_id, level_id). await conn.execute(""" DELETE FROM user_progress WHERE ctid NOT IN ( SELECT DISTINCT ON (user_id, level_id) ctid FROM user_progress ORDER BY user_id, level_id, completed_at DESC NULLS LAST, score DESC ) """) await conn.execute(""" ALTER TABLE user_progress ADD PRIMARY KEY (user_id, level_id) """) await conn.execute(""" ALTER TABLE user_progress ADD FOREIGN KEY (user_id) REFERENCES users(telegram_id) ON DELETE CASCADE """) elif not existing: await conn.execute(""" CREATE TABLE IF NOT EXISTS user_progress ( user_id BIGINT REFERENCES users(telegram_id) ON DELETE CASCADE, level_id INTEGER REFERENCES levels(id) ON DELETE CASCADE, completed_at TIMESTAMP, hints_used INTEGER DEFAULT 0, score INTEGER DEFAULT 0, level TEXT DEFAULT 'A1', PRIMARY KEY (user_id, level_id) ) """) # ============ Авто-миграция: добавляем level в user_progress ============ col_exists = await conn.fetchval(""" SELECT 1 FROM information_schema.columns WHERE table_name = 'user_progress' AND column_name = 'level' """) if not col_exists: await conn.execute(""" ALTER TABLE user_progress ADD COLUMN level TEXT DEFAULT 'A1' """) logger.info("Added level column to user_progress") # 🔧 ФИКС (23.09.2026): client_controlled_achievement_values — # серверный perfect_streak. Раньше считался на клиенте и приходил # в body — можно было подделать. col_exists = await conn.fetchval(""" SELECT 1 FROM information_schema.columns WHERE table_name = 'user_progress' AND column_name = 'current_streak' """) if not col_exists: await conn.execute(""" ALTER TABLE user_progress ADD COLUMN current_streak INT DEFAULT 0 """) logger.info("Added current_streak column to user_progress") # ============ sentences ============ await conn.execute(""" CREATE TABLE IF NOT EXISTS sentences ( id SERIAL PRIMARY KEY, sentence_number INTEGER NOT NULL, sentence_pl TEXT NOT NULL, translation_ru TEXT DEFAULT '', translation_uk TEXT DEFAULT '', translation_en TEXT DEFAULT '', difficulty INTEGER DEFAULT 1, level TEXT DEFAULT 'A1' ) """) # ============ Убираем старый UNIQUE constraint с sentences.sentence_number ============ await conn.execute(""" ALTER TABLE sentences DROP CONSTRAINT IF EXISTS sentences_sentence_number_key """) # ============ Добавляем составной UNIQUE (level, sentence_number) ============ await conn.execute(""" DO $$ BEGIN IF NOT EXISTS ( SELECT 1 FROM pg_constraint WHERE conname = 'sentences_sentence_number_unique' ) THEN ALTER TABLE sentences ADD CONSTRAINT sentences_sentence_number_unique UNIQUE (level, sentence_number); END IF; END $$; """) # ============ sentence_progress ============ await conn.execute(""" CREATE TABLE IF NOT EXISTS sentence_progress ( user_id BIGINT REFERENCES users(telegram_id) ON DELETE CASCADE, sentence_id INTEGER REFERENCES sentences(id) ON DELETE CASCADE, completed_at TIMESTAMP, score INTEGER DEFAULT 0, level TEXT DEFAULT 'A1', PRIMARY KEY (user_id, sentence_id) ) """) # ============ Авто-миграция: добавляем level в sentence_progress ============ col_exists = await conn.fetchval(""" SELECT 1 FROM information_schema.columns WHERE table_name = 'sentence_progress' AND column_name = 'level' """) if not col_exists: await conn.execute(""" ALTER TABLE sentence_progress ADD COLUMN level TEXT DEFAULT 'A1' """) logger.info("Added level column to sentence_progress") # ============ typing_progress ============ await conn.execute(""" CREATE TABLE IF NOT EXISTS typing_progress ( user_id BIGINT REFERENCES users(telegram_id) ON DELETE CASCADE, sentence_id INTEGER REFERENCES sentences(id) ON DELETE CASCADE, completed_at TIMESTAMP DEFAULT NOW(), PRIMARY KEY (user_id, sentence_id) ) """) # 🔧 ФИКС (23.09.2026): duplicate_index_typing_progress — индекс idx_typing_progress_user # уже создаётся в общем блоке индексов ниже. Убираем дубль здесь. # await conn.execute("CREATE INDEX IF NOT EXISTS idx_typing_progress_user ON typing_progress(user_id)") # ============ chat_history ============ await conn.execute(""" CREATE TABLE IF NOT EXISTS chat_history ( id SERIAL PRIMARY KEY, user_id BIGINT REFERENCES users(telegram_id) ON DELETE CASCADE, message TEXT NOT NULL, is_user BOOLEAN DEFAULT TRUE, created_at TIMESTAMP DEFAULT NOW() ) """) # ============ Индекс для chat_history ============ await conn.execute("CREATE INDEX IF NOT EXISTS idx_chat_history_user ON chat_history(user_id, created_at DESC)") # ============ chat_usage ============ await conn.execute(""" CREATE TABLE IF NOT EXISTS chat_usage ( id SERIAL PRIMARY KEY, user_id BIGINT REFERENCES users(telegram_id) ON DELETE CASCADE, usage_date DATE DEFAULT CURRENT_DATE, message_count INTEGER DEFAULT 0, UNIQUE (user_id, usage_date) ) """) # ============ Индекс для chat_usage ============ await conn.execute("CREATE INDEX IF NOT EXISTS idx_chat_usage_user ON chat_usage(user_id, usage_date DESC)") # ============ learning_levels ============ await conn.execute(""" CREATE TABLE IF NOT EXISTS learning_levels ( id SERIAL PRIMARY KEY, code VARCHAR(10) UNIQUE NOT NULL, name_pl TEXT NOT NULL, name_ru TEXT NOT NULL, name_uk TEXT NOT NULL, name_en TEXT NOT NULL, sort_order INT NOT NULL, is_active BOOLEAN DEFAULT TRUE ) """) # ============ learning_topics ============ # 🔧 ОБНОВЛЕНО (04.10.2026): track — разделение аудиторий. # 'pl' — для поляков (учёба на польском) # 'ru_uk' — для славян (RU/UK ↔ PL, ОБЩИЙ track) # 'en' — для англоязычных await conn.execute(""" CREATE TABLE IF NOT EXISTS learning_topics ( id SERIAL PRIMARY KEY, topic_id VARCHAR(100) UNIQUE NOT NULL, level_code VARCHAR(10) NOT NULL, order_number INT NOT NULL, track VARCHAR(10) NOT NULL DEFAULT 'ru_uk', -- 🔧 НОВОЕ name_pl TEXT NOT NULL, name_ru TEXT NOT NULL, name_uk TEXT NOT NULL, name_en TEXT NOT NULL, description_pl TEXT, description_ru TEXT, description_uk TEXT, description_en TEXT, is_active BOOLEAN DEFAULT TRUE, UNIQUE(level_code, order_number) ) """) # 🔧 НОВОЕ (04.10.2026): track_auto_migration — # для существующих БД добавляем колонку, если её нет. col_exists = await conn.fetchval(""" SELECT 1 FROM information_schema.columns WHERE table_name = 'learning_topics' AND column_name = 'track' """) if not col_exists: await conn.execute(""" ALTER TABLE learning_topics ADD COLUMN track VARCHAR(10) NOT NULL DEFAULT 'ru_uk' """) logger.info("Added track column to learning_topics") # Backfill: все существующие темы → 'ru_uk' (славянский track) await conn.execute(""" UPDATE learning_topics SET track = 'ru_uk' WHERE track IS NULL OR track = '' """) logger.info("Backfilled track='ru_uk' for existing topics") # ============ learning_lessons ============ await conn.execute(""" CREATE TABLE IF NOT EXISTS learning_lessons ( id SERIAL PRIMARY KEY, lesson_id VARCHAR(120) UNIQUE NOT NULL, topic_id VARCHAR(100) NOT NULL REFERENCES learning_topics(topic_id), order_number INT NOT NULL, skill_tag VARCHAR(100) NOT NULL, title_pl TEXT NOT NULL, title_ru TEXT NOT NULL, title_uk TEXT NOT NULL, title_en TEXT NOT NULL, objective_pl TEXT, objective_ru TEXT, objective_uk TEXT, objective_en TEXT, explanation_pl TEXT, explanation_ru TEXT, explanation_uk TEXT, explanation_en TEXT, is_active BOOLEAN DEFAULT TRUE, UNIQUE(topic_id, order_number) ) """) # ============ lesson_examples ============ await conn.execute(""" CREATE TABLE IF NOT EXISTS lesson_examples ( id SERIAL PRIMARY KEY, lesson_id VARCHAR(120) NOT NULL REFERENCES learning_lessons(lesson_id), order_number INT NOT NULL, text_pl TEXT NOT NULL, text_ru TEXT, text_uk TEXT, text_en TEXT, audio_url TEXT ) """) # ============ learning_questions ============ await conn.execute(""" CREATE TABLE IF NOT EXISTS learning_questions ( id SERIAL PRIMARY KEY, question_id VARCHAR(150) UNIQUE NOT NULL, lesson_id VARCHAR(120) REFERENCES learning_lessons(lesson_id), topic_id VARCHAR(100) NOT NULL REFERENCES learning_topics(topic_id), context_type VARCHAR(50) NOT NULL, question_type VARCHAR(50) NOT NULL, skill_tag VARCHAR(100) NOT NULL, difficulty INT NOT NULL CHECK (difficulty BETWEEN 1 AND 3), prompt_pl TEXT, prompt_ru TEXT, prompt_uk TEXT, prompt_en TEXT, correct_answer TEXT, hint_type VARCHAR(50), hint_value TEXT, is_test_question BOOLEAN DEFAULT FALSE, test_order INT, options_language TEXT DEFAULT 'pl' ) """) # ============ Авто-миграция: добавляем options_language ============ col_exists = await conn.fetchval(""" SELECT 1 FROM information_schema.columns WHERE table_name = 'learning_questions' AND column_name = 'options_language' """) if not col_exists: await conn.execute(""" ALTER TABLE learning_questions ADD COLUMN options_language TEXT DEFAULT 'pl' """) logger.info("Added options_language column to learning_questions") # ============ question_options ============ await conn.execute(""" CREATE TABLE IF NOT EXISTS question_options ( id SERIAL PRIMARY KEY, question_id VARCHAR(150) NOT NULL REFERENCES learning_questions(question_id), option_key VARCHAR(20) NOT NULL, text_pl TEXT, text_ru TEXT, text_uk TEXT, text_en TEXT, is_correct BOOLEAN NOT NULL, wrong_because VARCHAR(100), UNIQUE(question_id, option_key) ) """) # ============ learning_sessions ============ await conn.execute(""" CREATE TABLE IF NOT EXISTS learning_sessions ( id BIGSERIAL PRIMARY KEY, user_id BIGINT NOT NULL, level_code VARCHAR(10) NOT NULL, topic_id VARCHAR(100) NOT NULL, session_type VARCHAR(30) NOT NULL, started_at TIMESTAMP NOT NULL DEFAULT NOW(), completed_at TIMESTAMP, is_completed BOOLEAN DEFAULT FALSE ) """) # ============ user_learning_answers ============ # 🔧 ОБНОВЛЕНО (02.10.2026): добавлены revised и revision_of_id # прямо в определение — на свежих БД создаются сразу. await conn.execute(""" CREATE TABLE IF NOT EXISTS user_learning_answers ( id BIGSERIAL PRIMARY KEY, user_id BIGINT NOT NULL, session_id BIGINT NOT NULL REFERENCES learning_sessions(id), level_code VARCHAR(10) NOT NULL, topic_id VARCHAR(100) NOT NULL, lesson_id VARCHAR(120), question_id VARCHAR(150) NOT NULL, selected_answer TEXT, correct BOOLEAN, skill_tag VARCHAR(100) NOT NULL, question_type VARCHAR(50) NOT NULL, context_type VARCHAR(50) NOT NULL, difficulty INT NOT NULL, time_taken_ms INT, attempt_number INT DEFAULT 1, hint_used BOOLEAN DEFAULT FALSE, wrong_because VARCHAR(100), is_final_answer BOOLEAN DEFAULT TRUE, revised BOOLEAN DEFAULT FALSE, -- 🔧 НОВОЕ revision_of_id BIGINT, -- 🔧 НОВОЕ created_at TIMESTAMP DEFAULT NOW() ) """) # 🔧 НОВОЕ (02.10.2026): revised — фиксируем, что ученик # поменял ответ после «Повторить урок». col_exists = await conn.fetchval(""" SELECT 1 FROM information_schema.columns WHERE table_name = 'user_learning_answers' AND column_name = 'revised' """) if not col_exists: await conn.execute(""" ALTER TABLE user_learning_answers ADD COLUMN revised BOOLEAN DEFAULT FALSE """) logger.info("Added revised column to user_learning_answers") # 🔧 НОВОЕ (02.10.2026): revision_of_id — ссылка на предыдущий # ответ этого же вопроса в этой же сессии. Позволяет строить # цепочку: первый ответ → второй (после повтора) → третий. col_exists = await conn.fetchval(""" SELECT 1 FROM information_schema.columns WHERE table_name = 'user_learning_answers' AND column_name = 'revision_of_id' """) if not col_exists: await conn.execute(""" ALTER TABLE user_learning_answers ADD COLUMN revision_of_id BIGINT """) logger.info("Added revision_of_id column to user_learning_answers") # Индекс для быстрого поиска цепочек повторов await conn.execute(""" CREATE INDEX IF NOT EXISTS idx_learning_answers_revision ON user_learning_answers(revision_of_id) WHERE revision_of_id IS NOT NULL """) # ============ user_lesson_progress ============ await conn.execute(""" CREATE TABLE IF NOT EXISTS user_lesson_progress ( id BIGSERIAL PRIMARY KEY, user_id BIGINT NOT NULL, lesson_id VARCHAR(120) NOT NULL, started_at TIMESTAMP, completed_at TIMESTAMP, attempts_count INT DEFAULT 0, correct_count INT DEFAULT 0, best_score NUMERIC(5,2), average_time_ms INT, completed BOOLEAN DEFAULT FALSE, UNIQUE(user_id, lesson_id) ) """) # ============ user_topic_progress ============ await conn.execute(""" CREATE TABLE IF NOT EXISTS user_topic_progress ( id BIGSERIAL PRIMARY KEY, user_id BIGINT NOT NULL, topic_id VARCHAR(100) NOT NULL, lessons_total INT DEFAULT 0, lessons_completed INT DEFAULT 0, test_started BOOLEAN DEFAULT FALSE, test_completed BOOLEAN DEFAULT FALSE, test_score NUMERIC(5,2), topic_mastery NUMERIC(5,2), confidence NUMERIC(5,2), completed_at TIMESTAMP, UNIQUE(user_id, topic_id) ) """) # ============ user_skill_mastery (Analytics 2.0 + Этап 2) ============ await conn.execute(""" CREATE TABLE IF NOT EXISTS user_skill_mastery ( id BIGSERIAL PRIMARY KEY, user_id BIGINT NOT NULL, skill_tag VARCHAR(100) NOT NULL, attempts_count INT DEFAULT 0, correct_count INT DEFAULT 0, recognition_attempts INT DEFAULT 0, recognition_correct INT DEFAULT 0, recognition_score NUMERIC(5,2), production_attempts INT DEFAULT 0, production_correct INT DEFAULT 0, production_score NUMERIC(5,2), application_attempts INT DEFAULT 0, application_correct INT DEFAULT 0, application_score NUMERIC(5,2), retention_score NUMERIC(5,2) DEFAULT 0, hint_dependency NUMERIC(5,2) DEFAULT 0, average_time_ms INT DEFAULT 0, median_time_ms INT DEFAULT 0, median_correct_time_ms INT DEFAULT 0, median_wrong_time_ms INT DEFAULT 0, recent_accuracy NUMERIC(5,2) DEFAULT 0, last_3_accuracy NUMERIC(5,2), last_5_accuracy NUMERIC(5,2), last_10_accuracy NUMERIC(5,2), long_term_accuracy NUMERIC(5,2) DEFAULT 0, mastery_score NUMERIC(5,2) DEFAULT 0, previous_mastery_score NUMERIC(5,2) DEFAULT 0, mastery_delta NUMERIC(5,2) DEFAULT 0, trend TEXT DEFAULT 'insufficient_data', retention_status TEXT DEFAULT 'fresh', fluency_score NUMERIC(5,2) DEFAULT 0, passive_knowledge_score NUMERIC(5,2) DEFAULT 0, active_knowledge_score NUMERIC(5,2) DEFAULT 0, confidence NUMERIC(5,2) DEFAULT 0, error_count INT DEFAULT 0, repeated_error_count INT DEFAULT 0, days_since_last_attempt INT DEFAULT 0, last_attempt_at TIMESTAMP, UNIQUE(user_id, skill_tag) ) """) # ============ Авто-миграции для user_skill_mastery (Этап 2) ============ # 🔧 ФИКС (24.09.2026): fstring_sql_migration — # whitelist-проверка. col_name — идентификатор, # col_type — из разрешённого набора (NUMERIC, TEXT, INT, ...). import re as _re _SAFE_NAME = _re.compile(r'^[a-zA-Z_][a-zA-Z0-9_]*$') _SAFE_TYPE = _re.compile( r'^(NUMERIC|TEXT|INTEGER|INT|VARCHAR|BOOLEAN|TIMESTAMP|JSONB|BIGINT|SMALLINT)' r'(\s*\(\s*\d+(\s*,\s*\d+)?\s*\))?' r'(\s+DEFAULT\s+[\'"]?[\w\.\-\+\s]*[\'"]?)?' r'(\s+NOT\s+NULL)?' r'$', _re.IGNORECASE, ) stage2_columns = { "previous_mastery_score": "NUMERIC(5,2) DEFAULT 0", "mastery_delta": "NUMERIC(5,2) DEFAULT 0", "trend": "TEXT DEFAULT 'insufficient_data'", "retention_status": "TEXT DEFAULT 'fresh'", "fluency_score": "NUMERIC(5,2) DEFAULT 0", "passive_knowledge_score": "NUMERIC(5,2) DEFAULT 0", "active_knowledge_score": "NUMERIC(5,2) DEFAULT 0", } for col_name, col_type in stage2_columns.items(): if not _SAFE_NAME.match(col_name): logger.error(f"Unsafe column name skipped: {col_name}") continue if not _SAFE_TYPE.match(col_type): logger.error(f"Unsafe column type skipped: {col_name} → {col_type}") continue col_exists = await conn.fetchval(""" SELECT 1 FROM information_schema.columns WHERE table_name = 'user_skill_mastery' AND column_name = $1 """, col_name) if not col_exists: await conn.execute(f""" ALTER TABLE user_skill_mastery ADD COLUMN {col_name} {col_type} """) logger.info(f"Added {col_name} column to user_skill_mastery") # ============ ai_user_context ============ await conn.execute(""" CREATE TABLE IF NOT EXISTS ai_user_context ( user_id BIGINT PRIMARY KEY, current_level VARCHAR(10), completed_topics INT DEFAULT 0, level_topics_total INT DEFAULT 0, level_completion_percent NUMERIC(5,2) DEFAULT 0, completed_lessons INT DEFAULT 0, overall_mastery NUMERIC(5,2) DEFAULT 0, strong_skills JSONB DEFAULT '[]', weak_skills JSONB DEFAULT '[]', uncertain_skills JSONB DEFAULT '[]', recurring_errors JSONB DEFAULT '[]', recommended_topics JSONB DEFAULT '[]', recommended_lessons JSONB DEFAULT '[]', last_analysis_at TIMESTAMP, analytics_enabled BOOLEAN DEFAULT FALSE ) """) # ============ Авто-миграция: добавляем level_topics_total ============ col_exists = await conn.fetchval(""" SELECT 1 FROM information_schema.columns WHERE table_name = 'ai_user_context' AND column_name = 'level_topics_total' """) if not col_exists: await conn.execute(""" ALTER TABLE ai_user_context ADD COLUMN level_topics_total INT DEFAULT 0 """) logger.info("Added level_topics_total column to ai_user_context") # ============ Авто-миграция: добавляем level_completion_percent ============ col_exists = await conn.fetchval(""" SELECT 1 FROM information_schema.columns WHERE table_name = 'ai_user_context' AND column_name = 'level_completion_percent' """) if not col_exists: await conn.execute(""" ALTER TABLE ai_user_context ADD COLUMN level_completion_percent NUMERIC(5,2) DEFAULT 0 """) logger.info("Added level_completion_percent column to ai_user_context") # ============ achievements ============ await conn.execute(""" CREATE TABLE IF NOT EXISTS achievements ( id SERIAL PRIMARY KEY, code TEXT UNIQUE NOT NULL, category TEXT DEFAULT 'general', name_pl TEXT NOT NULL, name_ru TEXT NOT NULL, name_uk TEXT NOT NULL, name_en TEXT NOT NULL, description_pl TEXT DEFAULT '', description_ru TEXT DEFAULT '', description_uk TEXT DEFAULT '', description_en TEXT DEFAULT '', icon TEXT DEFAULT '🏆', requirement_type TEXT NOT NULL, requirement_value INTEGER DEFAULT 0 ) """) # 🔧 ФИКС (25.09.2026): ensure_achievements_code_key — # фикс init_achievements_n_plus_1 использует ON CONFLICT (code). # UNIQUE-инфраструктура для колонки code должна существовать. # В CREATE TABLE выше уже есть UNIQUE NOT NULL — но если таблица # была создана старой версией кода без UNIQUE, добавляем констрейнт. await conn.execute(""" DO $$ BEGIN IF NOT EXISTS ( SELECT 1 FROM pg_constraint WHERE conname = 'achievements_code_key' ) AND NOT EXISTS ( SELECT 1 FROM pg_constraint WHERE conrelid = 'achievements'::regclass AND contype IN ('u', 'p') AND conkey = ARRAY[ (SELECT attnum FROM pg_attribute WHERE attrelid = 'achievements'::regclass AND attname = 'code') ] ) THEN ALTER TABLE achievements ADD CONSTRAINT achievements_code_key UNIQUE (code); END IF; END $$; """) # ============ user_achievements ============ await conn.execute(""" CREATE TABLE IF NOT EXISTS user_achievements ( user_id BIGINT REFERENCES users(telegram_id) ON DELETE CASCADE, achievement_id INTEGER REFERENCES achievements(id) ON DELETE CASCADE, unlocked_at TIMESTAMP DEFAULT NOW(), PRIMARY KEY (user_id, achievement_id) ) """) # ============ daily_logins ============ await conn.execute(""" CREATE TABLE IF NOT EXISTS daily_logins ( user_id BIGINT REFERENCES users(telegram_id) ON DELETE CASCADE, login_date DATE DEFAULT CURRENT_DATE, PRIMARY KEY (user_id, login_date) ) """) # ============ word_quiz_progress ============ await conn.execute(""" CREATE TABLE IF NOT EXISTS word_quiz_progress ( user_id BIGINT REFERENCES users(telegram_id) ON DELETE CASCADE, words_guessed INTEGER DEFAULT 0, total_score INTEGER DEFAULT 0, PRIMARY KEY (user_id) ) """) # ============ Авто-миграция: добавляем correct_streak ============ col_exists = await conn.fetchval(""" SELECT 1 FROM information_schema.columns WHERE table_name = 'word_quiz_progress' AND column_name = 'correct_streak' """) if not col_exists: await conn.execute(""" ALTER TABLE word_quiz_progress ADD COLUMN correct_streak INTEGER DEFAULT 0 """) logger.info("Added correct_streak column to word_quiz_progress") # ============ Авто-миграция: добавляем recent_words ============ col_exists = await conn.fetchval(""" SELECT 1 FROM information_schema.columns WHERE table_name = 'word_quiz_progress' AND column_name = 'recent_words' """) if not col_exists: await conn.execute(""" ALTER TABLE word_quiz_progress ADD COLUMN recent_words TEXT[] DEFAULT '{}' """) logger.info("Added recent_words column to word_quiz_progress") # ============ Авто-миграция: добавляем level в word_quiz_progress ============ col_exists = await conn.fetchval(""" SELECT 1 FROM information_schema.columns WHERE table_name = 'word_quiz_progress' AND column_name = 'level' """) if not col_exists: await conn.execute(""" ALTER TABLE word_quiz_progress ADD COLUMN level TEXT DEFAULT 'A1' """) logger.info("Added level column to word_quiz_progress") # 🔧 ФИКС (24.09.2026): client_controlled_mistakes_wordquiz — # серверный счётчик mistakes для текущего слова. col_exists = await conn.fetchval(""" SELECT 1 FROM information_schema.columns WHERE table_name = 'word_quiz_progress' AND column_name = 'current_mistakes' """) if not col_exists: await conn.execute(""" ALTER TABLE word_quiz_progress ADD COLUMN current_mistakes INT DEFAULT 0 """) logger.info("Added current_mistakes column to word_quiz_progress") # ============ bubble_words_progress ============ await conn.execute(""" CREATE TABLE IF NOT EXISTS bubble_words_progress ( user_id BIGINT REFERENCES users(telegram_id) ON DELETE CASCADE, levels_completed INTEGER DEFAULT 0, total_score INTEGER DEFAULT 0, PRIMARY KEY (user_id) ) """) # ============ Авто-миграция: добавляем level в bubble_words_progress ============ col_exists = await conn.fetchval(""" SELECT 1 FROM information_schema.columns WHERE table_name = 'bubble_words_progress' AND column_name = 'level' """) if not col_exists: await conn.execute(""" ALTER TABLE bubble_words_progress ADD COLUMN level TEXT DEFAULT 'A1' """) logger.info("Added level column to bubble_words_progress") # 🔧 ФИКС (24.09.2026): client_controlled_hints_bubblewords — # серверный hints_used для текущего уровня пузырей. col_exists = await conn.fetchval(""" SELECT 1 FROM information_schema.columns WHERE table_name = 'bubble_words_progress' AND column_name = 'current_hints_used' """) if not col_exists: await conn.execute(""" ALTER TABLE bubble_words_progress ADD COLUMN current_hints_used INT DEFAULT 0 """) logger.info("Added current_hints_used column to bubble_words_progress") # ============ wrong_because_explanations ============ await conn.execute(""" CREATE TABLE IF NOT EXISTS wrong_because_explanations ( id SERIAL PRIMARY KEY, wrong_because VARCHAR(100) UNIQUE NOT NULL, explanation_pl TEXT NOT NULL, explanation_ru TEXT NOT NULL, explanation_uk TEXT NOT NULL, explanation_en TEXT NOT NULL ) """) # ============ teacher_reactions ============ await conn.execute(""" CREATE TABLE IF NOT EXISTS teacher_reactions ( id SERIAL PRIMARY KEY, reaction_type VARCHAR(50) NOT NULL, text_pl TEXT NOT NULL, text_ru TEXT NOT NULL, text_uk TEXT NOT NULL, text_en TEXT NOT NULL ) """) # ============ Индекс для teacher_reactions ============ await conn.execute("CREATE INDEX IF NOT EXISTS idx_teacher_reactions_type ON teacher_reactions(reaction_type)") # ============ teacher_chat_history ============ await conn.execute(""" CREATE TABLE IF NOT EXISTS teacher_chat_history ( id BIGSERIAL PRIMARY KEY, user_id BIGINT NOT NULL UNIQUE, messages JSONB NOT NULL DEFAULT '[]', updated_at TIMESTAMP DEFAULT NOW() ) """) # ============ teacher_ai_usage ============ await conn.execute(""" CREATE TABLE IF NOT EXISTS teacher_ai_usage ( id BIGSERIAL PRIMARY KEY, user_id BIGINT NOT NULL, usage_date DATE DEFAULT CURRENT_DATE, command_count INTEGER DEFAULT 0, UNIQUE(user_id, usage_date) ) """) # ============ lectures — лекции (архивы) ============ # 🔧 ОБНОВЛЕНО (27.09.2026): добавлены lecture_language и display_language. # title_pl и title_ru/uk/en теперь nullable — фронт забирает заголовок # из display_language, а не из title_pl. await conn.execute(""" CREATE TABLE IF NOT EXISTS lectures ( id BIGSERIAL PRIMARY KEY, level_code VARCHAR(10) NOT NULL, lecture_language VARCHAR(10) NOT NULL DEFAULT 'ru', display_language VARCHAR(10) NOT NULL DEFAULT 'ru', title_pl TEXT, title_ru TEXT, title_uk TEXT, title_en TEXT, description_pl TEXT, description_ru TEXT, description_uk TEXT, description_en TEXT, file_path TEXT NOT NULL, file_type VARCHAR(20) NOT NULL, file_size_bytes INT, order_number INT NOT NULL DEFAULT 0, is_active BOOLEAN DEFAULT TRUE, created_at TIMESTAMP DEFAULT NOW(), edited_at TIMESTAMP ) """) # ============ Авто-миграция: lecture_language + display_language ============ col_exists = await conn.fetchval(""" SELECT 1 FROM information_schema.columns WHERE table_name = 'lectures' AND column_name = 'lecture_language' """) if not col_exists: await conn.execute(""" ALTER TABLE lectures ADD COLUMN lecture_language VARCHAR(10) DEFAULT 'ru' """) logger.info("Added lecture_language column to lectures") col_exists = await conn.fetchval(""" SELECT 1 FROM information_schema.columns WHERE table_name = 'lectures' AND column_name = 'display_language' """) if not col_exists: await conn.execute(""" ALTER TABLE lectures ADD COLUMN display_language VARCHAR(10) DEFAULT 'ru' """) logger.info("Added display_language column to lectures") await conn.execute(""" CREATE INDEX IF NOT EXISTS idx_lectures_level ON lectures(level_code, order_number) """) # 🔧 НОВОЕ (27.09.2026): составной индекс по языкам лекции/отображения. await conn.execute(""" CREATE INDEX IF NOT EXISTS idx_lectures_lang ON lectures(level_code, lecture_language, order_number) """) logger.info("Table lectures initialized") # ============ daily_phrases — фраза дня ============ await conn.execute(""" CREATE TABLE IF NOT EXISTS daily_phrases ( id SERIAL PRIMARY KEY, phrase_date DATE UNIQUE NOT NULL, phrase_pl TEXT NOT NULL, translations JSONB NOT NULL DEFAULT '{}', grammar_breakdowns JSONB NOT NULL DEFAULT '{}', created_at TIMESTAMP DEFAULT NOW() ) """) # ============ Индекс для daily_phrases ============ await conn.execute("CREATE INDEX IF NOT EXISTS idx_daily_phrases_date ON daily_phrases(phrase_date DESC)") # ============ СИСТЕМА ПОДДЕРЖКИ ============ # ============ support_tickets ============ await conn.execute(""" CREATE TABLE IF NOT EXISTS support_tickets ( id BIGSERIAL PRIMARY KEY, user_id BIGINT NOT NULL, status TEXT DEFAULT 'open', created_at TIMESTAMP DEFAULT NOW(), updated_at TIMESTAMP DEFAULT NOW() ) """) # ============ Индекс для support_tickets ============ await conn.execute("CREATE INDEX IF NOT EXISTS idx_support_tickets_user ON support_tickets(user_id)") await conn.execute("CREATE INDEX IF NOT EXISTS idx_support_tickets_status ON support_tickets(status)") # ============ support_messages ============ await conn.execute(""" CREATE TABLE IF NOT EXISTS support_messages ( id BIGSERIAL PRIMARY KEY, ticket_id BIGINT NOT NULL REFERENCES support_tickets(id) ON DELETE CASCADE, sender_id BIGINT NOT NULL, message TEXT NOT NULL, is_read BOOLEAN DEFAULT FALSE, created_at TIMESTAMP DEFAULT NOW() ) """) # ============ Индекс для support_messages ============ await conn.execute("CREATE INDEX IF NOT EXISTS idx_support_messages_ticket ON support_messages(ticket_id)") await conn.execute("CREATE INDEX IF NOT EXISTS idx_support_messages_sender ON support_messages(sender_id)") await conn.execute("CREATE INDEX IF NOT EXISTS idx_support_messages_unread ON support_messages(ticket_id, is_read)") # ============ 🔧 НОВОЕ (18.09.2026): feed_posts — посты для карусели ============ await conn.execute(""" CREATE TABLE IF NOT EXISTS feed_posts ( id BIGSERIAL PRIMARY KEY, section TEXT NOT NULL, source_language TEXT NOT NULL DEFAULT 'ru', title_pl TEXT, title_ru TEXT, title_uk TEXT, title_en TEXT, body_pl TEXT, body_ru TEXT, body_uk TEXT, body_en TEXT, link_url TEXT, telegram_url TEXT, author_id BIGINT NOT NULL, is_pinned BOOLEAN DEFAULT FALSE, is_published BOOLEAN DEFAULT TRUE, created_at TIMESTAMP DEFAULT NOW(), updated_at TIMESTAMP DEFAULT NOW(), edited_at TIMESTAMP, expires_at TIMESTAMP ) """) # ============ Индексы для feed_posts ============ await conn.execute(""" CREATE INDEX IF NOT EXISTS idx_feed_posts_section ON feed_posts(section, created_at DESC) """) await conn.execute(""" CREATE INDEX IF NOT EXISTS idx_feed_posts_pinned ON feed_posts(section, is_pinned DESC, created_at DESC) """) await conn.execute(""" CREATE INDEX IF NOT EXISTS idx_feed_posts_published ON feed_posts(section, is_published) """) # ============ 🔧 НОВОЕ (18.09.2026): feed_attachments — картинки к постам (до 3) ============ await conn.execute(""" CREATE TABLE IF NOT EXISTS feed_attachments ( id BIGSERIAL PRIMARY KEY, post_id BIGINT NOT NULL REFERENCES feed_posts(id) ON DELETE CASCADE, file_path TEXT NOT NULL, file_type TEXT DEFAULT 'image', order_number INT NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT NOW() ) """) # ============ Индекс для feed_attachments ============ await conn.execute(""" CREATE INDEX IF NOT EXISTS idx_feed_attachments_post ON feed_attachments(post_id, order_number) """) # ============ user_learning_answers_archive ============ await conn.execute(""" CREATE TABLE IF NOT EXISTS user_learning_answers_archive ( id BIGSERIAL PRIMARY KEY, user_id BIGINT NOT NULL, skill_tag VARCHAR(100) NOT NULL, correct BOOLEAN, wrong_because VARCHAR(100), created_at DATE, archived_at TIMESTAMP DEFAULT NOW() ) """) # ============ Индексы для архива ============ await conn.execute("CREATE INDEX IF NOT EXISTS idx_archive_user_date ON user_learning_answers_archive(user_id, created_at)") await conn.execute("CREATE INDEX IF NOT EXISTS idx_archive_skill_tag ON user_learning_answers_archive(skill_tag)") # 🔧 ФИКС (24.09.2026): sentence_hint_no_limit + typing_hint_no_limit — # таблицы для лимита подсказок (1 раз на предложение). await conn.execute(""" CREATE TABLE IF NOT EXISTS sentence_hints_used ( user_id BIGINT REFERENCES users(telegram_id) ON DELETE CASCADE, sentence_id INTEGER REFERENCES sentences(id) ON DELETE CASCADE, used_at TIMESTAMP DEFAULT NOW(), PRIMARY KEY (user_id, sentence_id) ) """) await conn.execute(""" CREATE TABLE IF NOT EXISTS typing_hints_used ( user_id BIGINT REFERENCES users(telegram_id) ON DELETE CASCADE, sentence_id INTEGER REFERENCES sentences(id) ON DELETE CASCADE, used_at TIMESTAMP DEFAULT NOW(), PRIMARY KEY (user_id, sentence_id) ) """) # ============ Индексы ============ # 🔧 ФИКС (24.09.2026): redundant_index_users_telegram — # убраны дубли на UNIQUE-колонках: # - idx_users_telegram (telegram_id уже UNIQUE) # - idx_typing_progress_user (user_id+sentence_id уже PK) # - idx_user_achievements_user (user_id+achievement_id уже PK) # - idx_daily_logins_user (user_id+login_date уже PK) # - idx_skill_mastery_user (user_id+skill_tag уже UNIQUE) # - idx_user_progress_user (user_id+level_id уже PK) # - idx_sentence_progress_user (user_id+sentence_id уже PK) # # 🔧 ФИКС (25.09.2026): redundant_indexes_levels_sentences — # составной индекс (level, level_number) покрывает и level, # и level_number. Убраны дубли: # idx_levels_number, idx_levels_level, # idx_sentences_number, idx_sentences_level. # DROP на случай, если они уже созданы в предыдущих деплоях. await conn.execute("DROP INDEX IF EXISTS idx_levels_number") await conn.execute("DROP INDEX IF EXISTS idx_levels_level") await conn.execute("DROP INDEX IF EXISTS idx_sentences_number") await conn.execute("DROP INDEX IF EXISTS idx_sentences_level") await conn.execute("CREATE INDEX IF NOT EXISTS idx_levels_level_number ON levels(level, level_number)") await conn.execute("CREATE INDEX IF NOT EXISTS idx_level_words_level_id ON level_words(level_id)") await conn.execute("CREATE INDEX IF NOT EXISTS idx_level_words_level ON level_words(level)") # ============ Авто-миграция: wordsearch builder (29.09.2026) ============ # is_active — черновик (FALSE) / опубликован (TRUE) # created_by — telegram_id админа, создавшего вручную # created_at — когда создан col_exists = await conn.fetchval(""" SELECT 1 FROM information_schema.columns WHERE table_name = 'levels' AND column_name = 'is_active' """) if not col_exists: await conn.execute(""" ALTER TABLE levels ADD COLUMN is_active BOOLEAN DEFAULT TRUE """) logger.info("Added is_active column to levels") col_exists = await conn.fetchval(""" SELECT 1 FROM information_schema.columns WHERE table_name = 'levels' AND column_name = 'created_by' """) if not col_exists: await conn.execute(""" ALTER TABLE levels ADD COLUMN created_by BIGINT """) logger.info("Added created_by column to levels") col_exists = await conn.fetchval(""" SELECT 1 FROM information_schema.columns WHERE table_name = 'levels' AND column_name = 'created_at' """) if not col_exists: await conn.execute(""" ALTER TABLE levels ADD COLUMN created_at TIMESTAMP DEFAULT NOW() """) logger.info("Added created_at column to levels") # Индекс для быстрого поиска опубликованных ручных уровней await conn.execute(""" CREATE INDEX IF NOT EXISTS idx_levels_active_manual ON levels(level, is_active, created_by) WHERE is_active = TRUE AND created_by IS NOT NULL """) # ============ csv_words — слова из CSV для конструктора ============ await conn.execute(""" CREATE TABLE IF NOT EXISTS csv_words ( id SERIAL PRIMARY KEY, level_code VARCHAR(10) NOT NULL, word_pl TEXT NOT NULL, translation_ru TEXT DEFAULT '', translation_uk TEXT DEFAULT '', translation_en TEXT DEFAULT '', UNIQUE(level_code, word_pl) ) """) await conn.execute(""" CREATE INDEX IF NOT EXISTS idx_csv_words_level ON csv_words(level_code) """) logger.info("Table csv_words initialized") # ============ word_usage — счётчик использований слов ============ await conn.execute(""" CREATE TABLE IF NOT EXISTS word_usage ( id SERIAL PRIMARY KEY, level_code VARCHAR(10) NOT NULL, word_pl TEXT NOT NULL, usage_count INT DEFAULT 0, last_used_at TIMESTAMP DEFAULT NOW(), UNIQUE(level_code, word_pl) ) """) await conn.execute(""" CREATE INDEX IF NOT EXISTS idx_word_usage_level_word ON word_usage(level_code, word_pl) """) logger.info("Table word_usage initialized") # ============ 🔧 НОВОЕ (07.10.2026): pleily_auth — # поддержка авторизации мобильного приложения Pleily. # - source: откуда пришёл юзер ('telegram' | 'pleily_device' | 'pleily_apple') # - device_id: UUID устройства (анонимный вход) # - apple_id: Apple ID (потом, для привязки прогресса) # - app_user_id_seq: sequence для отрицательных user_id (Pleily-юзеры) # - auth_identities: связка provider+provider_uid → user_id # ============================================================ # source col_exists = await conn.fetchval(""" SELECT 1 FROM information_schema.columns WHERE table_name = 'users' AND column_name = 'source' """) if not col_exists: await conn.execute(""" ALTER TABLE users ADD COLUMN source TEXT DEFAULT 'telegram' """) await conn.execute(""" UPDATE users SET source = 'telegram' WHERE source IS NULL """) logger.info("Added source column to users (Pleily)") # device_id col_exists = await conn.fetchval(""" SELECT 1 FROM information_schema.columns WHERE table_name = 'users' AND column_name = 'device_id' """) if not col_exists: await conn.execute(""" ALTER TABLE users ADD COLUMN device_id UUID """) await conn.execute(""" CREATE UNIQUE INDEX IF NOT EXISTS idx_users_device_id ON users(device_id) WHERE device_id IS NOT NULL """) logger.info("Added device_id column to users (Pleily)") # apple_id col_exists = await conn.fetchval(""" SELECT 1 FROM information_schema.columns WHERE table_name = 'users' AND column_name = 'apple_id' """) if not col_exists: await conn.execute(""" ALTER TABLE users ADD COLUMN apple_id TEXT """) await conn.execute(""" CREATE UNIQUE INDEX IF NOT EXISTS idx_users_apple_id ON users(apple_id) WHERE apple_id IS NOT NULL """) logger.info("Added apple_id column to users (Pleily)") # app_user_id_seq — sequence для отрицательных user_id await conn.execute(""" CREATE SEQUENCE IF NOT EXISTS app_user_id_seq START -1 INCREMENT -1 MINVALUE -9223372036854775808 MAXVALUE -1 """) logger.info("Table app_user_id_seq initialized (Pleily)") # auth_identities — связка provider+provider_uid → user_id await conn.execute(""" CREATE TABLE IF NOT EXISTS auth_identities ( id BIGSERIAL PRIMARY KEY, user_id BIGINT NOT NULL REFERENCES users(telegram_id) ON DELETE CASCADE, provider TEXT NOT NULL, provider_uid TEXT NOT NULL, created_at TIMESTAMP DEFAULT NOW(), UNIQUE(provider, provider_uid) ) """) await conn.execute(""" CREATE INDEX IF NOT EXISTS idx_auth_identities_user ON auth_identities(user_id) """) # Backfill: для существующих telegram-юзеров создаём identity await conn.execute(""" INSERT INTO auth_identities (user_id, provider, provider_uid) SELECT telegram_id, 'telegram', telegram_id::text FROM users WHERE telegram_id > 0 ON CONFLICT DO NOTHING """) logger.info("Table auth_identities initialized (Pleily)") # ============================================================ # 🔧 НОВОЕ (08.10.2026): XP-фундамент Pleily. # user_profile, xp_log, choose_progress, user_seen_levels, # user_game_progress, wave_results, bubble_levels, # user_seen_bubble_levels, user_word_stats. # + расширение sentences (tokens, alt_orders, word_count). # ============================================================ # ---------- user_profile ---------- await conn.execute(""" CREATE TABLE IF NOT EXISTS user_profile ( user_id BIGINT PRIMARY KEY REFERENCES users(telegram_id) ON DELETE CASCADE, nickname TEXT, avatar_id INTEGER DEFAULT 0, total_xp INTEGER DEFAULT 0, weekly_xp INTEGER DEFAULT 0, week_started_at DATE DEFAULT CURRENT_DATE, updated_at TIMESTAMP DEFAULT NOW() ) """) await conn.execute(""" CREATE INDEX IF NOT EXISTS idx_user_profile_weekly ON user_profile(weekly_xp DESC) """) logger.info("Table user_profile initialized (XP)") # ---------- xp_log ---------- await conn.execute(""" CREATE TABLE IF NOT EXISTS xp_log ( id BIGSERIAL PRIMARY KEY, user_id BIGINT NOT NULL REFERENCES users(telegram_id) ON DELETE CASCADE, game TEXT NOT NULL, stars INTEGER NOT NULL, base_points INTEGER NOT NULL, multiplier NUMERIC(3,2) NOT NULL DEFAULT 1.0, points INTEGER NOT NULL, created_at TIMESTAMP DEFAULT NOW() ) """) await conn.execute(""" CREATE INDEX IF NOT EXISTS idx_xp_log_user_date ON xp_log(user_id, created_at DESC) """) logger.info("Table xp_log initialized (XP)") # ---------- choose_progress (серии в "Выбери слово") ---------- await conn.execute(""" CREATE TABLE IF NOT EXISTS choose_progress ( user_id BIGINT PRIMARY KEY REFERENCES users(telegram_id) ON DELETE CASCADE, current_streak INTEGER DEFAULT 0, best_streak INTEGER DEFAULT 0, updated_at TIMESTAMP DEFAULT NOW() ) """) logger.info("Table choose_progress initialized") # ---------- user_seen_levels (филворд: какие уровни видел) ---------- await conn.execute(""" CREATE TABLE IF NOT EXISTS user_seen_levels ( user_id BIGINT NOT NULL REFERENCES users(telegram_id) ON DELETE CASCADE, level_id INTEGER NOT NULL REFERENCES levels(id) ON DELETE CASCADE, seen_at TIMESTAMP DEFAULT NOW(), PRIMARY KEY (user_id, level_id) ) """) await conn.execute(""" CREATE INDEX IF NOT EXISTS idx_user_seen_levels_user ON user_seen_levels(user_id, seen_at DESC) """) logger.info("Table user_seen_levels initialized") # ---------- user_game_progress (сессии филворда) ---------- await conn.execute(""" CREATE TABLE IF NOT EXISTS user_game_progress ( user_id BIGINT PRIMARY KEY REFERENCES users(telegram_id) ON DELETE CASCADE, session_number INTEGER DEFAULT 1, session_level_number INTEGER DEFAULT 1, global_level_number INTEGER DEFAULT 1, time_limit_seconds INTEGER, updated_at TIMESTAMP DEFAULT NOW() ) """) logger.info("Table user_game_progress initialized") # ---------- wave_results (итоги волны) ---------- await conn.execute(""" CREATE TABLE IF NOT EXISTS wave_results ( id BIGSERIAL PRIMARY KEY, user_id BIGINT NOT NULL REFERENCES users(telegram_id) ON DELETE CASCADE, wave_number INTEGER NOT NULL, stars_earned INTEGER DEFAULT 0, medal TEXT DEFAULT 'none', completed_at TIMESTAMP DEFAULT NOW(), UNIQUE (user_id, wave_number) ) """) await conn.execute(""" CREATE INDEX IF NOT EXISTS idx_wave_results_user ON wave_results(user_id, wave_number DESC) """) logger.info("Table wave_results initialized") # ---------- bubble_levels ---------- await conn.execute(""" CREATE TABLE IF NOT EXISTS bubble_levels ( id BIGSERIAL PRIMARY KEY, word_ids INTEGER[] NOT NULL, difficulty INTEGER DEFAULT 1, created_at TIMESTAMP DEFAULT NOW() ) """) logger.info("Table bubble_levels initialized") # ---------- user_seen_bubble_levels ---------- await conn.execute(""" CREATE TABLE IF NOT EXISTS user_seen_bubble_levels ( user_id BIGINT NOT NULL REFERENCES users(telegram_id) ON DELETE CASCADE, bubble_level_id BIGINT NOT NULL REFERENCES bubble_levels(id) ON DELETE CASCADE, seen_at TIMESTAMP DEFAULT NOW(), PRIMARY KEY (user_id, bubble_level_id) ) """) logger.info("Table user_seen_bubble_levels initialized") # ---------- user_word_stats (повторение слов) ---------- await conn.execute(""" CREATE TABLE IF NOT EXISTS user_word_stats ( user_id BIGINT NOT NULL REFERENCES users(telegram_id) ON DELETE CASCADE, word_pl TEXT NOT NULL, correct INTEGER DEFAULT 0, wrong INTEGER DEFAULT 0, last_seen_at TIMESTAMP DEFAULT NOW(), PRIMARY KEY (user_id, word_pl) ) """) await conn.execute(""" CREATE INDEX IF NOT EXISTS idx_user_word_stats_user ON user_word_stats(user_id, last_seen_at DESC) """) logger.info("Table user_word_stats initialized") # ---------- sentences: tokens, alt_orders, word_count ---------- for col_name, col_type in ( ("tokens", "JSONB DEFAULT '[]'::jsonb"), ("alt_orders", "JSONB DEFAULT '[]'::jsonb"), ("word_count", "INTEGER DEFAULT 0"), ): col_exists = await conn.fetchval(""" SELECT 1 FROM information_schema.columns WHERE table_name = 'sentences' AND column_name = $1 """, col_name) if not col_exists: await conn.execute( f"ALTER TABLE sentences ADD COLUMN {col_name} {col_type}" ) logger.info(f"Added {col_name} column to sentences") # Backfill: word_count для существующих предложений await conn.execute(""" UPDATE sentences SET word_count = array_length( regexp_split_to_array(trim(sentence_pl), '\\s+'), 1 ) WHERE word_count = 0 OR word_count IS NULL """) logger.info("Backfilled sentences.word_count") # ---------- Backfill: user_profile для существующих юзеров ---------- await conn.execute(""" INSERT INTO user_profile (user_id, total_xp, weekly_xp) SELECT u.telegram_id, 0, 0 FROM users u WHERE NOT EXISTS ( SELECT 1 FROM user_profile p WHERE p.user_id = u.telegram_id ) ON CONFLICT (user_id) DO NOTHING """) logger.info("Backfilled user_profile for existing users") await conn.execute("CREATE INDEX IF NOT EXISTS idx_sentences_level_number ON sentences(level, sentence_number)") # Оставляем score-индексы (не дубли) await conn.execute("CREATE INDEX IF NOT EXISTS idx_user_progress_score ON user_progress(score DESC)") await conn.execute("CREATE INDEX IF NOT EXISTS idx_sentence_progress_score ON sentence_progress(score DESC)") await conn.execute("CREATE INDEX IF NOT EXISTS idx_daily_logins_date ON daily_logins(login_date DESC)") await conn.execute("CREATE INDEX IF NOT EXISTS idx_learning_topics_level ON learning_topics(level_code)") await conn.execute("CREATE INDEX IF NOT EXISTS idx_learning_lessons_topic ON learning_lessons(topic_id)") await conn.execute("CREATE INDEX IF NOT EXISTS idx_learning_questions_lesson ON learning_questions(lesson_id)") await conn.execute("CREATE INDEX IF NOT EXISTS idx_learning_questions_topic ON learning_questions(topic_id)") await conn.execute("CREATE INDEX IF NOT EXISTS idx_learning_answers_user ON user_learning_answers(user_id, created_at DESC)") # конец транзакции logger.info("Database initialized")