"""
Урок 88. Презентация проекта — весь код примеров одним файлом.

Источник: subjects/python-fundamentals/course/lessons/88-project-presentation/examples.html
Файл собран автоматически (tools/build_lesson_examples.py): правьте страницу урока.
Запуск: python lesson-88.py
"""



# ====================================================================
# 📁 Структура модулей проекта
# ====================================================================
from mysql_connector import search_by_keyword, search_by_genre_year, get_genres, get_year_range
from log_writer import write_log
from log_stats import most_popular, latest_unique
from formatter import print_films

def main():
    while True:
        print("\n=== Поиск фильмов ===")
        print("1. Поиск по ключевому слову")
        print("2. Поиск по жанру и годам")
        print("3. Популярные запросы")
        print("4. Последние запросы")
        print("0. Выход")
        choice = input("Выберите пункт: ").strip()

        if choice == "1":
            keyword = input("Введите ключевое слово: ").strip()
            results = search_by_keyword(keyword)
            print_films(results)
            write_log("keyword", {"keyword": keyword}, len(results))
        elif choice == "2":
            print("Жанры:", ", ".join(get_genres()))
            min_year, max_year = get_year_range()
            print(f"Годы выпуска: {min_year}–{max_year}")
            genre = input("Жанр: ").strip()
            year_from = int(input("С года: "))
            year_to = int(input("По год: "))
            results = search_by_genre_year(genre, year_from, year_to)
            print_films(results)
            write_log("genre_year", {"genre": genre, "year_from": year_from, "year_to": year_to}, len(results))
        elif choice == "3":
            for item in most_popular():
                print(item)
        elif choice == "4":
            for item in latest_unique():
                print(item)
        elif choice == "0":
            break
        else:
            print("Неверный выбор. Попробуйте снова.")

if __name__ == "__main__":
    main()


# ====================================================================
# 📁 Структура модулей проекта
# ====================================================================
import pymysql
from pymysql.cursors import DictCursor

def get_connection():
    return pymysql.connect(
        host="mysql.itcareerhub.de",
        user="ich_editor",
        password="verystrongpassword",
        database="sakila",
        cursorclass=DictCursor,
    )

def search_by_keyword(keyword, page_size=10, page=0):
    query = """
        SELECT film_id, title, release_year, description
        FROM film
        WHERE title LIKE %s
        ORDER BY title
        LIMIT %s OFFSET %s
    """
    with get_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute(query, (f"%{keyword}%", page_size, page * page_size))
            return cursor.fetchall()

def search_by_genre_year(genre, year_from, year_to, page_size=10, page=0):
    query = """
        SELECT f.film_id, f.title, c.name AS genre, f.release_year
        FROM film f
        JOIN film_category fc ON f.film_id = fc.film_id
        JOIN category c ON fc.category_id = c.category_id
        WHERE c.name = %s AND f.release_year BETWEEN %s AND %s
        ORDER BY f.release_year, f.title
        LIMIT %s OFFSET %s
    """
    with get_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute(query, (genre, year_from, year_to, page_size, page * page_size))
            return cursor.fetchall()

def get_genres():
    query = "SELECT name FROM category ORDER BY name"
    with get_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute(query)
            return [row["name"] for row in cursor.fetchall()]

def get_year_range():
    query = "SELECT MIN(release_year) AS min_year, MAX(release_year) AS max_year FROM film"
    with get_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute(query)
            row = cursor.fetchone()
            return row["min_year"], row["max_year"]


# ====================================================================
# 📁 Структура модулей проекта
# ====================================================================
from datetime import datetime
from pymongo import MongoClient

def _get_collection():
    client = MongoClient(
        "mongodb://ich_editor:verystrongpassword"
        "@mongo.itcareerhub.de/?authSource=ich_edit"
    )
    db = client["ich_edit"]
    return client, db["final_project_pd_ivan_ivanov"]

def write_log(search_type, params, results_count):
    client, collection = _get_collection()
    try:
        collection.insert_one({
            "timestamp": datetime.now().isoformat(timespec="seconds"),
            "search_type": search_type,
            "params": params,
            "results_count": results_count,
        })
    finally:
        client.close()


# ====================================================================
# 📁 Структура модулей проекта
# ====================================================================
from pymongo import MongoClient

def _get_collection():
    client = MongoClient(
        "mongodb://ich_editor:verystrongpassword"
        "@mongo.itcareerhub.de/?authSource=ich_edit"
    )
    db = client["ich_edit"]
    return client, db["final_project_pd_ivan_ivanov"]

def most_popular(limit=5):
    _, collection = _get_collection()
    pipeline = [
        {
            "$group": {
                "_id": {"search_type": "$search_type", "params": "$params"},
                "count": {"$sum": 1}
            }
        },
        {"$sort": {"count": -1}},
        {"$limit": limit}
    ]
    return list(collection.aggregate(pipeline))

def latest_unique(limit=5):
    _, collection = _get_collection()
    pipeline = [
        {"$sort": {"timestamp": -1}},
        {
            "$group": {
                "_id": {"search_type": "$search_type", "params": "$params"},
                "timestamp": {"$first": "$timestamp"}
            }
        },
        {"$sort": {"timestamp": -1}},
        {"$limit": limit}
    ]
    return list(collection.aggregate(pipeline))


# ====================================================================
# 📁 Структура модулей проекта
# ====================================================================
def print_films(films):
    if not films:
        print("Ничего не найдено.")
        return
    print(f"{'ID':<5} {'Год':<6} {'Название':<30} Описание")
    print("-" * 80)
    for film in films:
        title = film["title"][:28]
        desc = (film.get("description") or "")[:40]
        print(f"{film['film_id']:<5} {film['release_year']:<6} {title:<30} {desc}")


# ====================================================================
# 🔍 Пример SQL-запроса с LIKE и LIMIT/OFFSET
# ====================================================================
keyword = "love"
offset = 0  # для второй страницы — 10, третьей — 20
query = """
    SELECT film_id, title, release_year
    FROM film
    WHERE title LIKE %s
    ORDER BY title
    LIMIT 10 OFFSET %s
"""
cursor.execute(query, (f"%{keyword}%", offset))


# ====================================================================
# 🍃 Пример записи лога
# ====================================================================
write_log(
    search_type="keyword",
    params={"keyword": "matrix"},
    results_count=3
)


# ====================================================================
# 📊 Пример агрегации популярных запросов
# ====================================================================
[
    {"$group": {"_id": "$params.keyword", "count": {"$sum": 1}}},
    {"$sort": {"count": -1}},
    {"$limit": 5}
]