""" Sincc.on: Foydalanuvchilar va So'rovlar Statistikasi (SQLite). Yuqori yuklama uchun WAL rejimi, xavfsiz timeout va normallashtirilgan kesh bilan. """ import sqlite3 import pathlib import datetime import hashlib import json import re DB_PATH = pathlib.Path(__file__).resolve().parent.parent / "data" / "bot_stats.db" def get_db_connection() -> sqlite3.Connection: """Ko'p oqimli rejimda blokirovkaga tushmasligi uchun 25 soniyalik timeout bilan ulanish beradi.""" conn = sqlite3.connect(DB_PATH, timeout=25.0) conn.execute("PRAGMA busy_timeout = 25000;") return conn def init_db(): DB_PATH.parent.mkdir(parents=True, exist_ok=True) with get_db_connection() as conn: cursor = conn.cursor() # Yuqori yuklamada blokirovkalar bo'lmasligi uchun WAL rejimi cursor.execute("PRAGMA journal_mode=WAL;") cursor.execute("PRAGMA synchronous=NORMAL;") cursor.execute(""" CREATE TABLE IF NOT EXISTS users ( user_id INTEGER PRIMARY KEY, username TEXT, first_name TEXT, joined_at TEXT, last_active TEXT, total_queries INTEGER DEFAULT 0, is_admin INTEGER DEFAULT 0 ) """) cursor.execute(""" CREATE TABLE IF NOT EXISTS queries ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER, query_type TEXT, question_preview TEXT, timestamp TEXT, verified INTEGER DEFAULT 0 ) """) cursor.execute(""" CREATE TABLE IF NOT EXISTS solution_cache ( query_hash TEXT PRIMARY KEY, question_text TEXT, solution_json TEXT, created_at TEXT, hits INTEGER DEFAULT 1 ) """) cursor.execute(""" CREATE TABLE IF NOT EXISTS feedbacks ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER, username TEXT, first_name TEXT, message TEXT, reply_context TEXT, timestamp TEXT ) """) cursor.execute(""" CREATE TABLE IF NOT EXISTS chat_history ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER, role TEXT, content TEXT, timestamp TEXT ) """) cursor.execute(""" CREATE TABLE IF NOT EXISTS banned_users ( user_id INTEGER PRIMARY KEY, username TEXT, first_name TEXT, reason TEXT, banned_at TEXT, banned_by INTEGER ) """) conn.commit() def record_user_activity(user_id: int, username: str = None, first_name: str = None): now_str = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S") with get_db_connection() as conn: cursor = conn.cursor() cursor.execute("SELECT user_id, total_queries FROM users WHERE user_id = ?", (user_id,)) row = cursor.fetchone() if row is None: cursor.execute( "INSERT INTO users (user_id, username, first_name, joined_at, last_active, total_queries) VALUES (?, ?, ?, ?, ?, ?)", (user_id, username or "", first_name or "", now_str, now_str, 0) ) else: cursor.execute( "UPDATE users SET username = ?, first_name = ?, last_active = ? WHERE user_id = ?", (username or "", first_name or "", now_str, user_id) ) conn.commit() def log_query(user_id: int, query_type: str, question: str, verified: bool = False): now_str = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S") preview = (question[:80] + "...") if len(question) > 80 else question with get_db_connection() as conn: cursor = conn.cursor() cursor.execute( "INSERT INTO queries (user_id, query_type, question_preview, timestamp, verified) VALUES (?, ?, ?, ?, ?)", (user_id, query_type, preview, now_str, 1 if verified else 0) ) cursor.execute( "UPDATE users SET total_queries = total_queries + 1, last_active = ? WHERE user_id = ?", (now_str, user_id) ) conn.commit() def log_feedback(user_id: int, username: str, first_name: str, message: str, reply_context: str = ""): now_str = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S") with get_db_connection() as conn: cursor = conn.cursor() cursor.execute(""" INSERT INTO feedbacks (user_id, username, first_name, message, reply_context, timestamp) VALUES (?, ?, ?, ?, ?, ?) """, (user_id, username or "", first_name or "", message, reply_context or "", now_str)) conn.commit() def get_recent_feedbacks(limit: int = 5): with get_db_connection() as conn: cursor = conn.cursor() cursor.execute(""" SELECT id, user_id, username, first_name, message, reply_context, timestamp FROM feedbacks ORDER BY id DESC LIMIT ? """, (limit,)) return cursor.fetchall() def set_user_admin(user_id: int, is_admin: bool = True): with get_db_connection() as conn: cursor = conn.cursor() cursor.execute("UPDATE users SET is_admin = ? WHERE user_id = ?", (1 if is_admin else 0, user_id)) conn.commit() def is_user_admin(user_id: int) -> bool: with get_db_connection() as conn: cursor = conn.cursor() cursor.execute("SELECT is_admin FROM users WHERE user_id = ?", (user_id,)) row = cursor.fetchone() return bool(row and row[0] == 1) def ban_user(user_id: int, username: str = "", first_name: str = "", reason: str = "Xavfsizlik qoidabuzarligi", banned_by: int = 0): """Foydalanuvchini bloklanganlar ro'yxatiga qo'shadi.""" now_str = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S") with get_db_connection() as conn: cursor = conn.cursor() cursor.execute(""" INSERT OR REPLACE INTO banned_users (user_id, username, first_name, reason, banned_at, banned_by) VALUES (?, ?, ?, ?, ?, ?) """, (user_id, username or "", first_name or "", reason, now_str, banned_by)) conn.commit() def unban_user(user_id: int) -> bool: """Foydalanuvchini blokdan chiqaradi.""" with get_db_connection() as conn: cursor = conn.cursor() cursor.execute("DELETE FROM banned_users WHERE user_id = ?", (user_id,)) changes = conn.total_changes conn.commit() return changes > 0 def is_user_banned(user_id: int) -> tuple[bool, str]: """Foydalanuvchi bloklanganligini tekshiradi.""" with get_db_connection() as conn: cursor = conn.cursor() cursor.execute("SELECT reason, banned_at FROM banned_users WHERE user_id = ?", (user_id,)) row = cursor.fetchone() if row: return True, row[0] or "Xavfsizlik qoidabuzarligi" return False, "" def get_banned_users(limit: int = 20) -> list[dict]: """Bloklangan foydalanuvchilar ro'yxatini qaytaradi.""" with get_db_connection() as conn: cursor = conn.cursor() cursor.execute(""" SELECT user_id, username, first_name, reason, banned_at, banned_by FROM banned_users ORDER BY banned_at DESC LIMIT ? """, (limit,)) return [ { "user_id": r[0], "username": r[1], "first_name": r[2], "reason": r[3], "banned_at": r[4], "banned_by": r[5] } for r in cursor.fetchall() ] def get_statistics(): today_str = datetime.datetime.now().strftime("%Y-%m-%d") with get_db_connection() as conn: cursor = conn.cursor() cursor.execute("SELECT COUNT(*) FROM users") total_users = cursor.fetchone()[0] cursor.execute("SELECT COUNT(*) FROM users WHERE last_active LIKE ?", (f"{today_str}%",)) active_today = cursor.fetchone()[0] cursor.execute("SELECT COUNT(*) FROM queries") total_queries = cursor.fetchone()[0] cursor.execute("SELECT COUNT(*) FROM queries WHERE query_type = 'photo'") photo_queries = cursor.fetchone()[0] cursor.execute("SELECT COUNT(*) FROM queries WHERE query_type = 'voice'") voice_queries = cursor.fetchone()[0] cursor.execute("SELECT COUNT(*) FROM queries WHERE query_type = 'text'") text_queries = cursor.fetchone()[0] cursor.execute("SELECT COUNT(*) FROM feedbacks") total_feedbacks = cursor.fetchone()[0] cursor.execute("SELECT COUNT(*) FROM banned_users") total_banned = cursor.fetchone()[0] cursor.execute(""" SELECT user_id, username, first_name, total_queries, last_active FROM users ORDER BY last_active DESC LIMIT 10 """) recent_users = cursor.fetchall() return { "total_users": total_users, "active_today": active_today, "total_queries": total_queries, "photo_queries": photo_queries, "voice_queries": voice_queries, "text_queries": text_queries, "total_feedbacks": total_feedbacks, "total_banned": total_banned, "recent_users": recent_users } def get_all_user_ids(): with get_db_connection() as conn: cursor = conn.cursor() cursor.execute("SELECT user_id FROM users") return [row[0] for row in cursor.fetchall()] _NORM_NOISE_PHRASES = ( "tenglamaning ildizlarini toping", "tenglamani yeching", "tengsizlikni yeching", "ifodani soddalashtiring", "qiymatini hisoblang", "ildizlarini toping", "ni yeching", "ni hisoblang", "ni toping", "yeching", "hisoblang", "тенгламани ечинг", "ечинг", "илдизларини топинг", "ҳисобланг", ) def hash_query(text: str) -> str: """Matematik masalalarni semantik jihatdan tozalab keshlaydi.""" s = text.lower().strip() for phrase in _NORM_NOISE_PHRASES: s = s.replace(phrase, " ") s = re.sub(r"[\s\?\!\.\,]+", " ", s).strip() cleaned = "".join(s.split()) if not cleaned: cleaned = "".join(text.lower().split()) return hashlib.md5(cleaned.encode("utf-8")).hexdigest() def add_chat_message(user_id: int, role: str, content: str): """Foydalanuvchi bilan suhbat tarixini saqlaydi (xotira uchun).""" if not content: return now_str = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S") with get_db_connection() as conn: cursor = conn.cursor() cursor.execute( "INSERT INTO chat_history (user_id, role, content, timestamp) VALUES (?, ?, ?, ?)", (user_id, role, content[:2500], now_str) ) cursor.execute(""" DELETE FROM chat_history WHERE user_id = ? AND id NOT IN ( SELECT id FROM chat_history WHERE user_id = ? ORDER BY id DESC LIMIT 20 ) """, (user_id, user_id)) conn.commit() def get_recent_chat_history(user_id: int, limit: int = 8) -> list[dict]: """Foydalanuvchining oxirgi suhbat xabarlarini AI ga uzatish uchun qaytaradi.""" with get_db_connection() as conn: cursor = conn.cursor() cursor.execute(""" SELECT role, content FROM ( SELECT id, role, content FROM chat_history WHERE user_id = ? ORDER BY id DESC LIMIT ? ) ORDER BY id ASC """, (user_id, limit)) return [{"role": r[0], "content": r[1]} for r in cursor.fetchall()] def clear_chat_history(user_id: int): """Foydalanuvchi suhbat xotirasini tozalaydi.""" with get_db_connection() as conn: cursor = conn.cursor() cursor.execute("DELETE FROM chat_history WHERE user_id = ?", (user_id,)) conn.commit() def get_cached_solution(text: str) -> dict | None: """Agar masala avval SymPy bilan tasdiqlanib yechilgan bo'lsa, keshdan tayyor javobni oladi.""" q_hash = hash_query(text) with get_db_connection() as conn: cursor = conn.cursor() cursor.execute("SELECT solution_json, hits FROM solution_cache WHERE query_hash = ?", (q_hash,)) row = cursor.fetchone() if row: try: data = json.loads(row[0]) if not data.get("verified") or data.get("intent") in ("chat", "incomplete", "theory"): return None cursor.execute("UPDATE solution_cache SET hits = hits + 1 WHERE query_hash = ?", (q_hash,)) conn.commit() data["from_cache"] = True return data except Exception: return None return None def save_cached_solution(text: str, solution: dict): """Faqat SymPy bilan tasdiqlangan aniq matematik yechimlarni keshga saqlaydi.""" if not solution or not solution.get("verified") or not solution.get("final_answer"): return if solution.get("intent", solution.get("type")) in ("chat", "incomplete", "theory"): return q_hash = hash_query(text) now_str = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S") clean_sol = dict(solution) clean_sol.pop("from_cache", None) with get_db_connection() as conn: cursor = conn.cursor() cursor.execute(""" INSERT OR REPLACE INTO solution_cache (query_hash, question_text, solution_json, created_at, hits) VALUES (?, ?, ?, ?, COALESCE((SELECT hits FROM solution_cache WHERE query_hash = ?), 1)) """, (q_hash, text[:150], json.dumps(clean_sol, ensure_ascii=False), now_str, q_hash)) conn.commit() def purge_bad_cache(): """Eski noto'g'ri yoki tasdiqlanmagan kesh yozuvlarini tozalaydi.""" with get_db_connection() as conn: cursor = conn.cursor() cursor.execute("SELECT query_hash, solution_json FROM solution_cache") rows = cursor.fetchall() for qh, sj in rows: try: d = json.loads(sj) if not d.get("verified") or d.get("type") in ("incomplete", "theory") or any( "[Konsensus" in str(s) or "Identify coefficients" in str(s) for s in d.get("steps", []) ): cursor.execute("DELETE FROM solution_cache WHERE query_hash = ?", (qh,)) except Exception: cursor.execute("DELETE FROM solution_cache WHERE query_hash = ?", (qh,)) conn.commit() init_db() purge_bad_cache()