import sqlite3 import logging from datetime import datetime from config import DB_PATH logger = logging.getLogger("KitobAlmash.DB") def get_connection() -> sqlite3.Connection: conn = sqlite3.connect(DB_PATH, timeout=15.0, check_same_thread=False) conn.row_factory = sqlite3.Row conn.execute("PRAGMA foreign_keys = ON;") conn.execute("PRAGMA journal_mode = WAL;") return conn def _safe_add_column(cur: sqlite3.Cursor, table: str, column_def: str): try: cur.execute(f"ALTER TABLE {table} ADD COLUMN {column_def}") if cur.connection: cur.connection.commit() except sqlite3.OperationalError: pass def init_db(): conn = get_connection() cur = conn.cursor() # 1. Foydalanuvchilar jadvali cur.execute(""" CREATE TABLE IF NOT EXISTS users ( user_id INTEGER PRIMARY KEY, full_name TEXT NOT NULL, username TEXT DEFAULT '', region TEXT DEFAULT 'Toshkent', district TEXT DEFAULT 'Yunusobod tumani', school TEXT DEFAULT '20-IDUM', grade TEXT DEFAULT '8-"A" sinf', trust_score REAL DEFAULT 5.0, ziyo_coins INTEGER DEFAULT 100, books_shared INTEGER DEFAULT 0, books_read INTEGER DEFAULT 0, referred_by INTEGER DEFAULT NULL, is_banned INTEGER DEFAULT 0, is_onboarded INTEGER DEFAULT 0, created_at TEXT NOT NULL ) """) _safe_add_column(cur, "users", "is_onboarded INTEGER DEFAULT 0") _safe_add_column(cur, "users", "phone TEXT DEFAULT ''") _safe_add_column(cur, "users", "referral_code TEXT DEFAULT ''") # Mavjud foydalanuvchilarga shaxsiy unikal referral_code biriktirish import secrets cur.execute("SELECT user_id, referral_code FROM users WHERE referral_code IS NULL OR referral_code = ''") rows = cur.fetchall() alphabet = "ABCDEFGHJKLMNPQRSTUVWXYZ23456789" for r in rows: uid = r["user_id"] rand_code = "XC" + "".join(secrets.choice(alphabet) for _ in range(5)) cur.execute("UPDATE users SET referral_code = ? WHERE user_id = ?", (rand_code, uid)) # 2. Kitoblar jadvali cur.execute(""" CREATE TABLE IF NOT EXISTS books ( id INTEGER PRIMARY KEY AUTOINCREMENT, owner_id INTEGER NOT NULL, title TEXT NOT NULL, author TEXT NOT NULL, genre TEXT DEFAULT 'Badiiy', condition TEXT DEFAULT 'Yaxshi', exchange_type TEXT DEFAULT 'loan', loan_days INTEGER DEFAULT 14, description TEXT DEFAULT '', cover_emoji TEXT DEFAULT 'tone-emerald', cover_image TEXT DEFAULT '', photo_extra TEXT DEFAULT '', custom_contact TEXT DEFAULT '', region TEXT DEFAULT 'Toshkent', district TEXT DEFAULT 'Yunusobod tumani', school TEXT DEFAULT '20-IDUM', grade TEXT DEFAULT '8-"A" sinf', latitude REAL DEFAULT NULL, longitude REAL DEFAULT NULL, channel_message_id INTEGER DEFAULT NULL, tg_file_id TEXT DEFAULT '', status TEXT DEFAULT 'available', current_holder_id INTEGER DEFAULT NULL, read_count INTEGER DEFAULT 1, created_at TEXT NOT NULL, FOREIGN KEY (owner_id) REFERENCES users(user_id) ) """) _safe_add_column(cur, "users", "latitude REAL DEFAULT NULL") _safe_add_column(cur, "users", "longitude REAL DEFAULT NULL") _safe_add_column(cur, "books", "cover_image TEXT DEFAULT ''") _safe_add_column(cur, "books", "photo_extra TEXT DEFAULT ''") _safe_add_column(cur, "books", "custom_contact TEXT DEFAULT ''") _safe_add_column(cur, "books", "grade TEXT DEFAULT ''") _safe_add_column(cur, "books", "latitude REAL DEFAULT NULL") _safe_add_column(cur, "books", "longitude REAL DEFAULT NULL") _safe_add_column(cur, "books", "channel_message_id INTEGER DEFAULT NULL") _safe_add_column(cur, "books", "tg_file_id TEXT DEFAULT ''") conn.commit() # 3. Kitob Pasporti (Kitobning o'qilish tarixi va o'quvchilar zanjiri) cur.execute(""" CREATE TABLE IF NOT EXISTS book_passport ( id INTEGER PRIMARY KEY AUTOINCREMENT, book_id INTEGER NOT NULL, reader_id INTEGER NOT NULL, reader_name TEXT NOT NULL, school TEXT DEFAULT '', rating INTEGER DEFAULT 5, review TEXT DEFAULT '', date_read TEXT NOT NULL, FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE ) """) # 4. Bitimlar va Omonat taymeri (Deals & Loans) cur.execute(""" CREATE TABLE IF NOT EXISTS deals ( id INTEGER PRIMARY KEY AUTOINCREMENT, book_id INTEGER NOT NULL, owner_id INTEGER NOT NULL, requester_id INTEGER NOT NULL, deal_type TEXT DEFAULT 'loan', loan_days INTEGER DEFAULT 14, offered_book_title TEXT DEFAULT '', message TEXT DEFAULT '', status TEXT DEFAULT 'pending', handover_at TEXT DEFAULT NULL, due_date TEXT DEFAULT NULL, reminded_2days INTEGER DEFAULT 0, reminded_due INTEGER DEFAULT 0, created_at TEXT NOT NULL, completed_at TEXT DEFAULT NULL, FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE ) """) _safe_add_column(cur, "deals", "loan_days INTEGER DEFAULT 14") _safe_add_column(cur, "deals", "handover_at TEXT DEFAULT NULL") _safe_add_column(cur, "deals", "reminded_2days INTEGER DEFAULT 0") _safe_add_column(cur, "deals", "reminded_due INTEGER DEFAULT 0") # 5. Kitob Ovchisi (WishList Radari) cur.execute(""" CREATE TABLE IF NOT EXISTS wishlist ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER NOT NULL, book_query TEXT NOT NULL, author_query TEXT DEFAULT '', is_fulfilled INTEGER DEFAULT 0, matched_book_id INTEGER DEFAULT NULL, created_at TEXT NOT NULL, FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ) """) _safe_add_column(cur, "wishlist", "matched_book_id INTEGER DEFAULT NULL") conn.commit() # Boshlang'ich namuna ma'lumotlarni tekshirish va бойitish _seed_initial_data(cur, conn) conn.close() def _seed_initial_data(cur: sqlite3.Cursor, conn: sqlite3.Connection): """ Toza boshlang'ich holat: Fake demo kitoblar va soxta foydalanuvchilar qo'shilmaydi. Baza 100% real foydalanuvchilar va haqiqiy kitoblar uchun tayyor. """ pass