Aaeafh/Finalbot

1import sqlite32import sys3import os4sys.path.insert(0, os.path.dirname(__file__))5from config import DB_PATH6 7 8def get_db():9    conn = sqlite3.connect(DB_PATH)10    conn.row_factory = sqlite3.Row11    _init_tables(conn)12    _migrate(conn)13    return conn14 15 16def _migrate(conn):17    """إضافة أعمدة جديدة للجداول القديمة بدون حذف البيانات."""18    c = conn.cursor()19    for sql in [20        "ALTER TABLE questions ADD COLUMN username TEXT DEFAULT ''",21        "ALTER TABLE questions ADD COLUMN first_name TEXT DEFAULT ''",22    ]:23        try:24            c.execute(sql)25        except Exception:26            pass27    conn.commit()28 29 30def _init_tables(conn):31    c = conn.cursor()32    c.executescript("""33        CREATE TABLE IF NOT EXISTS sections (34            id INTEGER PRIMARY KEY AUTOINCREMENT,35            name TEXT NOT NULL,36            chat_id INTEGER NOT NULL,37            thread_id INTEGER,38            creator_id INTEGER39        );40        CREATE TABLE IF NOT EXISTS questions (41            id INTEGER PRIMARY KEY AUTOINCREMENT,42            section_id INTEGER NOT NULL,43            user_id INTEGER,44            username TEXT DEFAULT '',45            first_name TEXT DEFAULT '',46            quiz_num INTEGER DEFAULT 0,47            question_text TEXT DEFAULT '',48            answer_text TEXT DEFAULT '',49            question_image TEXT DEFAULT '',50            answer_image TEXT DEFAULT ''51        );52        CREATE TABLE IF NOT EXISTS sessions (53            user_id INTEGER NOT NULL,54            chat_id INTEGER NOT NULL,55            thread_id INTEGER,56            state TEXT,57            section_id INTEGER,58            draft_q_id INTEGER,59            base_msg_id INTEGER,60            PRIMARY KEY (user_id, chat_id)61        );62    """)63    conn.commit()64 65 66def get_section(chat_id, thread_id=None):67    conn = get_db()68    c = conn.cursor()69    if thread_id:70        c.execute("SELECT * FROM sections WHERE chat_id=? AND thread_id=?", (chat_id, thread_id))71    else:72        c.execute("SELECT * FROM sections WHERE chat_id=? AND thread_id IS NULL", (chat_id,))73    res = c.fetchone()74    conn.close()75    return res76 77 78def get_section_by_id(sid):79    conn = get_db()80    c = conn.cursor()81    c.execute("SELECT * FROM sections WHERE id=?", (sid,))82    res = c.fetchone()83    conn.close()84    return res85 86 87def upsert_section(name, chat_id, thread_id, creator_id):88    """89    كل مادة+كويز = قسم مستقل تماماً.90    - إذا كان نفس الاسم موجوداً في هذا الجروب → أعد استخدامه وحدّث التوبيك الخاص به.91    - إذا كان اسماً جديداً → أنشئ قسماً جديداً.92    هكذا تغيير المادة في نفس التوبيك لا يُخلط الأسئلة القديمة بالجديدة.93    """94    conn = get_db()95    c = conn.cursor()96 97    # ابحث بـ (chat_id, name) — كل مادة كيانٌ مستقل98    c.execute("SELECT id FROM sections WHERE chat_id=? AND name=?", (chat_id, name))99    row = c.fetchone()100 101    if row:102        # المادة موجودة — فقط حدّث التوبيك المرتبط بها103        c.execute("UPDATE sections SET thread_id=? WHERE id=?", (thread_id, row["id"]))104        sid = row["id"]105    else:106        # مادة جديدة — أنشئ قسماً مستقلاً107        c.execute(108            "INSERT INTO sections (name, chat_id, thread_id, creator_id) VALUES (?, ?, ?, ?)",109            (name, chat_id, thread_id, creator_id)110        )111        sid = c.lastrowid112 113    conn.commit()114    conn.close()115    return sid116 117 118def get_user_session(user_id, chat_id):119    conn = get_db()120    c = conn.cursor()121    c.execute("SELECT * FROM sessions WHERE user_id=? AND chat_id=?", (user_id, chat_id))122    res = c.fetchone()123    conn.close()124    return res125 126 127def get_session(user_id, chat_id):128    return get_user_session(user_id, chat_id)129 130 131def set_session(user_id, chat_id, thread_id=None, state=None,132                section_id=None, draft_q_id=None, base_msg_id=None):133    conn = get_db()134    c = conn.cursor()135    c.execute("SELECT * FROM sessions WHERE user_id=? AND chat_id=?", (user_id, chat_id))136    row = c.fetchone()137    if row:138        u_state    = state       if state       is not None else row["state"]139        u_tid      = thread_id   if thread_id   is not None else row["thread_id"]140        u_sid      = section_id  if section_id  is not None else row["section_id"]141        u_dqid     = draft_q_id  if draft_q_id  is not None else row["draft_q_id"]142        u_bmsg     = base_msg_id if base_msg_id is not None else row["base_msg_id"]143        c.execute(144            "UPDATE sessions SET state=?, thread_id=?, section_id=?, draft_q_id=?, base_msg_id=? "145            "WHERE user_id=? AND chat_id=?",146            (u_state, u_tid, u_sid, u_dqid, u_bmsg, user_id, chat_id)147        )148    else:149        c.execute(150            "INSERT INTO sessions (user_id, chat_id, thread_id, state, section_id, draft_q_id, base_msg_id) "151            "VALUES (?, ?, ?, ?, ?, ?, ?)",152            (user_id, chat_id, thread_id, state, section_id, draft_q_id, base_msg_id)153        )154    conn.commit()155    conn.close()156 157 158def clear_session(user_id, chat_id):159    conn = get_db()160    c = conn.cursor()161    c.execute("DELETE FROM sessions WHERE user_id=? AND chat_id=?", (user_id, chat_id))162    conn.commit()163    conn.close()164 165 166def create_draft(section_id, user_id, username="", first_name=""):167    conn = get_db()168    c = conn.cursor()169    # نحسب عدد الأسئلة المحفوظة فقط (quiz_num > 0) لنحدد رقم السؤال الجديد170    c.execute("SELECT COUNT(*) FROM questions WHERE section_id=? AND quiz_num > 0", (section_id,))171    next_num = c.fetchone()[0] + 1172    c.execute(173        "INSERT INTO questions (section_id, user_id, username, first_name, quiz_num, question_text, answer_text, question_image, answer_image) "174        "VALUES (?, ?, ?, ?, ?, '', '', '', '')",175        (section_id, user_id, username or "", first_name or "", next_num)176    )177    qid = c.lastrowid178    conn.commit()179    conn.close()180    return qid181 182 183def update_question(qid, **kwargs):184    if not kwargs:185        return186    conn = get_db()187    c = conn.cursor()188    fields = []189    vals = []190    for k, v in kwargs.items():191        fields.append(f"{k}=?")192        vals.append(v if v is not None else "")193    vals.append(qid)194    query = f"UPDATE questions SET {', '.join(fields)} WHERE id=?"195    c.execute(query, tuple(vals))196    conn.commit()197    conn.close()198 199 200def get_question(qid):201    conn = get_db()202    c = conn.cursor()203    c.execute("SELECT * FROM questions WHERE id=?", (qid,))204    res = c.fetchone()205    conn.close()206    return res207 208 209def delete_question(qid):210    conn = get_db()211    c = conn.cursor()212    c.execute("DELETE FROM questions WHERE id=?", (qid,))213    conn.commit()214    conn.close()215 216 217def get_all_questions(section_id):218    conn = get_db()219    c = conn.cursor()220    c.execute("SELECT * FROM questions WHERE section_id=? ORDER BY quiz_num ASC", (section_id,))221    res = c.fetchall()222    conn.close()223    return res224 225 226def count_questions(section_id):227    conn = get_db()228    c = conn.cursor()229    c.execute("SELECT COUNT(*) FROM questions WHERE section_id=?", (section_id,))230    res = c.fetchone()[0]231    conn.close()232    return res233 234 235def renumber_questions(section_id):236    conn = get_db()237    c = conn.cursor()238    c.execute("SELECT id FROM questions WHERE section_id=? ORDER BY quiz_num ASC, id ASC", (section_id,))239    rows = c.fetchall()240    for idx, row in enumerate(rows, 1):241        c.execute("UPDATE questions SET quiz_num=? WHERE id=?", (idx, row["id"]))242    conn.commit()243    conn.close()244 245 246def get_all_sections_with_counts():247    conn = get_db()248    c = conn.cursor()249    c.execute("""250        SELECT s.id, s.name, s.chat_id, s.thread_id, COUNT(q.id) as count251        FROM sections s252        LEFT JOIN questions q ON s.id = q.section_id253        GROUP BY s.id254        ORDER BY s.name255    """)256    res = c.fetchall()257    conn.close()258    return res259