xat-chop / database /models.py
abbosovshahboz12-dot
Fix tg_file_id column migration error and enlarge book covers with spacious studio layout
1f99d38
Raw History Blame Contribute Delete
6.44 kB
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