---

-- ============================================
-- 1. ФУНКЦИЯ И ПРОЦЕДУРА ПОПУЛЯРНОСТИ КНИГИ
-- ============================================

DELIMITER $$

DROP FUNCTION IF EXISTS GetBookPopularity $$
CREATE FUNCTION GetBookPopularity(p_book_id INT)
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE total INT;

    SELECT COUNT(*)
    INTO total
    FROM issues
    WHERE book_id = p_book_id;

    RETURN total;
END $$

DROP PROCEDURE IF EXISTS ShowBookPopularity $$
CREATE PROCEDURE ShowBookPopularity(
    IN p_book_id INT
)
BEGIN
    DECLARE cnt INT;

    SELECT COUNT(*)
    INTO cnt
    FROM books
    WHERE id = p_book_id;

    IF cnt > 0 THEN

        DROP TEMPORARY TABLE IF EXISTS temp_popularity;

        CREATE TEMPORARY TABLE temp_popularity(
            book_id INT,
            popularity INT
        );

        INSERT INTO temp_popularity
        VALUES(
            p_book_id,
            GetBookPopularity(p_book_id)
        );

        SELECT * FROM temp_popularity;

    ELSE

        SELECT 'Книга не существует' AS Ошибка;

    END IF;
END $$

DELIMITER ;

-- ПРОВЕРКА

CALL ShowBookPopularity(1);
CALL ShowBookPopularity(100);

-- ============================================
-- 2. КАТЕГОРИЯ ВОЗРАСТА ЧИТАТЕЛЯ
-- ============================================

DELIMITER $$

DROP FUNCTION IF EXISTS GetAgeCategory $$
CREATE FUNCTION GetAgeCategory(p_age INT)
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN

    DECLARE result_age VARCHAR(20);

    IF p_age < 25 THEN
        SET result_age = 'Молодой';

    ELSEIF p_age BETWEEN 25 AND 60 THEN
        SET result_age = 'Взрослый';

    ELSE
        SET result_age = 'Пенсионер';

    END IF;

    RETURN result_age;

END $$

DROP PROCEDURE IF EXISTS UpdateAgeCategories $$
CREATE PROCEDURE UpdateAgeCategories()
BEGIN

    ALTER TABLE readers
    ADD COLUMN age_category VARCHAR(20);

    UPDATE readers
    SET age_category = GetAgeCategory(age);

    SELECT * FROM readers;

END $$

DELIMITER ;

-- ПРОВЕРКА

CALL UpdateAgeCategories();

-- ============================================
-- 3. РАСЧЁТ ШТРАФОВ
-- ============================================

DELIMITER $$

DROP FUNCTION IF EXISTS CalculateFine $$
CREATE FUNCTION CalculateFine(
    p_issue_date DATE,
    p_return_date DATE
)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN

    DECLARE total_days INT;
    DECLARE fine_result DECIMAL(10,2);

    IF p_return_date IS NULL THEN
        SET total_days = DATEDIFF(CURDATE(), p_issue_date);
    ELSE
        SET total_days = DATEDIFF(p_return_date, p_issue_date);
    END IF;

    IF total_days <= 7 THEN
        SET fine_result = 0;
    ELSE
        SET fine_result = total_days - 7;
    END IF;

    RETURN fine_result;

END $$

DROP PROCEDURE IF EXISTS UpdateFines $$
CREATE PROCEDURE UpdateFines()
BEGIN

    UPDATE issues
    SET fine = CalculateFine(issue_date, return_date)
    WHERE return_date IS NULL
    OR DATEDIFF(return_date, issue_date) > 7;

    SELECT * FROM issues;

END $$

DELIMITER ;

-- ПРОВЕРКА

CALL UpdateFines();

-- ============================================
-- 4. СРЕДНИЙ РЕЙТИНГ АВТОРОВ
-- ============================================

DELIMITER $$

DROP FUNCTION IF EXISTS GetAuthorAverageRating $$
CREATE FUNCTION GetAuthorAverageRating(
    p_author_id INT
)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN

    DECLARE avg_rating DECIMAL(10,2);

    SELECT IFNULL(AVG(i.rating), 0)
    INTO avg_rating
    FROM issues i
    JOIN books b ON i.book_id = b.id
    JOIN book_authors ba ON b.id = ba.book_id
    WHERE ba.author_id = p_author_id
    AND i.rating IS NOT NULL;

    RETURN avg_rating;

END $$

DROP PROCEDURE IF EXISTS BuildAuthorRatings $$
CREATE PROCEDURE BuildAuthorRatings()
BEGIN

    DROP TABLE IF EXISTS author_ratings;

    CREATE TABLE author_ratings(
        author_id INT,
        author_name VARCHAR(100),
        avg_rating DECIMAL(10,2)
    );

    INSERT INTO author_ratings
    SELECT
        a.id,
        a.name,
        GetAuthorAverageRating(a.id)
    FROM authors a;

    SELECT * FROM author_ratings;

END $$

DELIMITER ;

-- ПРОВЕРКА

CALL BuildAuthorRatings();

-- ============================================
-- 5. РЕКОМЕНДАЦИИ КНИГ ЧИТАТЕЛЮ
-- ============================================

DELIMITER $$

DROP FUNCTION IF EXISTS CountGenreReads $$
CREATE FUNCTION CountGenreReads(
    p_reader_id INT,
    p_genre VARCHAR(50)
)
RETURNS INT
DETERMINISTIC
BEGIN

    DECLARE total INT;

    SELECT COUNT(*)
    INTO total
    FROM issues i
    JOIN books b ON i.book_id = b.id
    WHERE i.reader_id = p_reader_id
    AND b.genre = p_genre;

    RETURN total;

END $$

DROP PROCEDURE IF EXISTS RecommendBooks $$
CREATE PROCEDURE RecommendBooks(
    IN p_reader_id INT
)
BEGIN

    DECLARE cnt INT;

    SELECT COUNT(*)
    INTO cnt
    FROM readers
    WHERE id = p_reader_id;

    IF cnt > 0 THEN

        SELECT
            b.id,
            b.title,
            b.genre,
            CountGenreReads(p_reader_id, b.genre) AS genre_score
        FROM books b
        ORDER BY genre_score DESC
        LIMIT 3;

    ELSE

        SELECT 'Читатель не найден' AS Ошибка;

    END IF;

END $$

DELIMITER ;

-- ПРОВЕРКА

CALL RecommendBooks(1);
CALL RecommendBooks(100);

-- ============================================
-- 6. СТОИМОСТЬ КНИГ ПОСЛЕ ГОДА
-- ============================================

DELIMITER $$

DROP FUNCTION IF EXISTS SumBookPricesAfterYear $$
CREATE FUNCTION SumBookPricesAfterYear(
    p_year INT
)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN

    DECLARE total_price DECIMAL(10,2);

    SELECT IFNULL(SUM(price), 0)
    INTO total_price
    FROM books
    WHERE pub_year > p_year;

    RETURN total_price;

END $$

DROP PROCEDURE IF EXISTS CreateLibraryStats $$
CREATE PROCEDURE CreateLibraryStats(
    IN p_year INT
)
BEGIN

    DECLARE cnt INT;

    SELECT COUNT(*)
    INTO cnt
    FROM books
    WHERE pub_year > p_year;

    IF cnt > 0 THEN

        CREATE TABLE IF NOT EXISTS library_stats(
            stat_year INT,
            total_price DECIMAL(10,2)
        );

        INSERT INTO library_stats
        VALUES(
            p_year,
            SumBookPricesAfterYear(p_year)
        );

        SELECT * FROM library_stats;

    ELSE

        SELECT 'Подходящих книг нет' AS Результат;

    END IF;

END $$

DELIMITER ;

-- ПРОВЕРКА

CALL CreateLibraryStats(1900);
CALL CreateLibraryStats(3000);