✅ Решения с разбором логики

✅ Решение 1. Просмотр книг в наличии

Нумерация начинается с 1 и не совпадает с id из базы. Если книг нет — функция завершается раньше, чтобы не выводить пустой заголовок.

def view_available_books(connection):
    with connection.cursor() as cursor:
        cursor.execute("USE bookstore")
        cursor.execute("SELECT title, author, price, stock FROM books WHERE stock > 0")
        books = cursor.fetchall()

    if not books:
        print("No books available.")
        return

    print("Available books:")
    for i, (title, author, price, stock) in enumerate(books, start=1):
        print(f"{i}. {title} by {author} — ${price:.2f} ({stock} in stock)")

✅ Решение 2. Поиск по названию

Параметр LIKE формируется в Python, а сам запрос — параметризованный. Это безопасно: спецсимволы ввода не сломают SQL.

def search_books_by_title(connection):
    keyword = input("Enter book title: ").strip()

    with connection.cursor() as cursor:
        cursor.execute("USE bookstore")
        cursor.execute("""
            SELECT title, author, price, stock
            FROM books
            WHERE stock > 0 AND LOWER(title) LIKE %s
        """, (f"%{keyword.lower()}%",))
        results = cursor.fetchall()

    if not results:
        print("No matching books found.")
        return

    print("Search results:")
    for i, (title, author, price, stock) in enumerate(results, start=1):
        print(f"{i}. {title} by {author} — ${price:.2f} ({stock} in stock)")

✅ Решение 3. Покупка книги

Guard clauses позволяют быстро выйти при ошибке. Все изменения выполняются внутри одного курсора и фиксируются одним commit().

def purchase_book(connection, user_id):
    with connection.cursor() as cursor:
        cursor.execute("USE bookstore")
        cursor.execute("SELECT id, title, author, price, stock FROM books WHERE stock > 0")
        books = cursor.fetchall()

    if not books:
        print("No books available.")
        return

    print("Available books:")
    for i, (book_id, title, author, price, stock) in enumerate(books, start=1):
        print(f"{i}. {title} by {author} — ${price:.2f} ({stock} in stock)")

    try:
        index = int(input("Enter book number: ")) - 1
        quantity = int(input("Enter quantity: "))
    except ValueError:
        print("Invalid input.")
        return

    if not (0 <= index < len(books)):
        print("Invalid book number.")
        return

    book_id, title, author, price, stock = books[index]

    if quantity > stock:
        print("Not enough books in stock.")
        return

    with connection.cursor() as cursor:
        cursor.execute("SELECT balance FROM users WHERE id = %s", (user_id,))
        balance = cursor.fetchone()[0]
        total_price = price * quantity

        if total_price > balance:
            print("Insufficient funds.")
            return

        cursor.execute(
            "UPDATE books SET stock = stock - %s WHERE id = %s",
            (quantity, book_id),
        )
        cursor.execute(
            "UPDATE users SET balance = balance - %s WHERE id = %s",
            (total_price, user_id),
        )
        connection.commit()
        print("Purchase successful.")

✅ Решение 4. Таблица purchases

purchases_query = """
    CREATE TABLE IF NOT EXISTS purchases (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT,
        book_id INT,
        quantity INT,
        purchase_date DATE,
        FOREIGN KEY (user_id) REFERENCES users(id),
        FOREIGN KEY (book_id) REFERENCES books(id)
    )
"""

# Внутри purchase_book, после UPDATE:
cursor.execute("""
    INSERT INTO purchases (user_id, book_id, quantity, purchase_date)
    VALUES (%s, %s, %s, CURDATE())
""", (user_id, book_id, quantity))

✅ Решение 5. Логирование поисков

from pymongo import MongoClient

mongo_client = MongoClient(
    "mongodb://ich_editor:verystrongpassword"
    "@mongo.itcareerhub.de/?readPreference=primary"
    "&ssl=false&authMechanism=DEFAULT&authSource=ich_edit"
)
searches_collection = mongo_client["ich_edit"]["bookstore_logs_searches"]


def log_search_query(query: str) -> None:
    if query.strip():
        searches_collection.insert_one({"query": query.strip()})

✅ Решение 6. Топ популярных запросов

Counter-вариант:

from collections import Counter


def show_popular_queries(limit: int = 10) -> None:
    queries = [
        doc["query"].strip().lower()
        for doc in searches_collection.find()
        if doc.get("query")
    ]
    counter = Counter(queries)
    top = counter.most_common(limit)

    if not top:
        print("No search data found.")
        return

    print("Most frequent search queries:")
    for i, (query, count) in enumerate(top, start=1):
        print(f"{i}. {query} — {count} times")

✅ Решение 7. Разделение на модули

Главное правило: в main.py минимум логики, максимум импортов и запуска. Вспомогательные модули не должны печатать меню или запрашивать ввод при импорте.

# main.py
from db_setup import create_database_and_tables
from auth import login_user, register_user
from book_importer import import_books_from_csv
from user_actions import user_menu


def main():
    connection = create_database_and_tables()
    import_books_from_csv(connection, "books.csv")

    while True:
        print("\n--- Bookstore ---")
        print("1. Register")
        print("2. Login")
        print("0. Exit")
        choice = input("Choose action: ")

        if choice == "1":
            register_user(connection)
        elif choice == "2":
            user_id = login_user(connection)
            if user_id:
                user_menu(connection, user_id)
        elif choice == "0":
            break


if __name__ == "__main__":
    main()