---

CREATE DATABASE library;
USE library;
CREATE TABLE authors(
	id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    country VARCHAR(50),
    birth_year INT
);

CREATE TABLE readers(
	id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    age INT,
    city VARCHAR(50),
    registration_date DATE
);

CREATE TABLE books(
	id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200),
    pages INT,
    price DECIMAL(10,2),
    genre VARCHAR(50),
    pub_year INT
);

CREATE TABLE book_authors(
	book_id INT,
    author_id INT,
    PRIMARY KEY (book_id, author_id),
    FOREIGN KEY (book_id) REFERENCES books(id),
    FOREIGN KEY (author_id) REFERENCES authors(id)
);

CREATE TABLE issues(
	id INT PRIMARY KEY AUTO_INCREMENT,
    book_id INT,
    reader_id INT,
    issue_date DATE,
    return_date DATE,
    rating INT,
    fine DECIMAL(10,2),
    FOREIGN KEY (book_id) REFERENCES books(id),
    FOREIGN KEY (reader_id) REFERENCES readers(id)
);

INSERT INTO authors(name, country, birth_year) VALUES
('Лев Толстой', 'RU', 1828),
('Фёдор Достоевский', 'RU', 1821),
('Джейн Остин', 'UK', 1775),
('Дж. Р. Р. Толкин', 'UK', 1892),
('Дж. К. Роулинг', 'UK', 1965),
('Антуан де Сент-Экзюпери', 'FR', 1900),
('Уильям Шекспир', 'UK', 1564),
('Марк Твен', 'US', 1835);

INSERT INTO books(title, pages, price, genre, pub_year) VALUES
('Война и мир', 1400, 25.50, 'Роман', 1869),
('Преступление и наказание', 670, 18.00, 'Роман', 1866),
('Гордость и предубеждение', 432, 15.99, 'Роман', 1813),
('Властелин колец', 1178, 35.00, 'Фэнтези', 1954),
('Гарри Поттер и философский камень', 223, 12.50, 'Фэнтези', 1997),
('Маленький принц', 96, 8.99, 'Сказка', 1943),
('Гамлет', 350, 10.50, 'Драма', 1603),
('Приключения Гекльберри Финна', 366, 14.00, 'Приключения', 1884);

INSERT INTO book_authors(book_id, author_id) VALUES
(1, 1),
(1, 2),
(2, 2),
(3, 3),
(4, 4),
(5, 5),
(6, 6),
(7, 7),
(8, 8);

INSERT INTO readers(name, age, city, registration_date) VALUES
('Иван Петров', 25, 'Москва', '2024-01-15'),
('Мария Сидорова', 32, 'СПб', '2023-11-20'),
('Алексей Иванов', 28, 'Казань', '2024-03-10'),
('Елена Козлова', 22, 'Москва', '2025-09-01'),
('Дмитрий Смирнов', 45, 'Екатеринбург', '2023-05-12');

INSERT INTO issues(id, book_id, reader_id, issue_date, return_date, rating, fine) VALUES
(1, 1, 1, '2026-01-10', '2026-02-05', 10, 0.00),
(2, 2, 2, '2026-02-01', NULL, NULL, 5.00),
(3, 3, 1, '2026-03-15', '2026-04-10', 8, 0.00),
(4, 4, 3, '2026-04-01', NULL, NULL, 0.00),
(5, 5, 4, '2026-03-20', '2026-04-15', 9, 2.50),
(6, 1, 5, '2026-01-20', '2026-02-10', 7, 0.00),
(7, 5, 5, '2026-01-09', NULL, NULL, 0.00),
(8, 4, 1, '2025-06-15', NULL, 3, 8.03),
(9, 7, 5, '2025-06-06', '2026-03-25', 1, 1.15),
(10, 8, 4, '2026-01-14', NULL, NULL, 0.77),
(11, 2, 4, '2025-12-10', NULL, NULL, 0.00),
(12, 6, 5, '2026-04-12', NULL, 1, 0.00),
(13, 2, 2, '2025-10-01', NULL, 7, 0.58),
(14, 4, 1, '2025-10-14', NULL, 4, 0.00),
(15, 4, 4, '2025-06-17', NULL, 5, 0.00);

-- Задание 1
DELIMITER //
CREATE FUNCTION выдачи(id_книги int)
RETURNS INT
DETERMINISTIC
BEGIN
	DECLARE количество INT DEFAULT 0;
    SELECT COUNT(book_id) INTO количество
    FROM issues
    GROUP BY book_id
    HAVING book_id = id_книги;
    RETURN количество;    
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE табл_выдачи(in id_книги int)
BEGIN
	IF EXISTS (SELECT 1 FROM books WHERE id = id_книги)
    THEN CREATE TABLE IF NOT EXISTS temp_popularity(
			book_id INT PRIMARY KEY,
            popularity INT,
            FOREIGN KEY (book_id) REFERENCES books(id));
    INSERT INTO temp_popularity(book_id, popularity) VALUES
    (id_книги, выдачи(id_книги));
    SELECT * FROM temp_popularity;
    ELSE SELECT 'Ошибка' AS 'Ошибка';
    END IF;
END //
DELIMITER ;

-- Задание 2
DELIMITER //
CREATE FUNCTION возр_чит(возраст int)
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
	DECLARE категория VARCHAR(20);
    IF возраст < 25 THEN SET категория = 'Молодой';
    ELSEIF возраст BETWEEN 25 AND 60 THEN SET категория = 'Взрослый';
    ELSE SET категория = 'Пенсионер';
    END IF;
    RETURN категория;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE обн_таб()
BEGIN
	IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'readers' AND COLUMN_NAME = 'age_category')
    THEN ALTER TABLE readers
		 ADD COLUMN age_category VARCHAR(20);
	END IF;
    UPDATE readers
    SET age_category = возр_чит(age);
    SELECT * FROM readers;
END //
DELIMITER ;

-- Задание 3
DELIMITER //
CREATE FUNCTION штраф(дата_выдачи date, дата_возврата date)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
	DECLARE сумма_штрафа DECIMAL(10,2) DEFAULT 0;
    IF дата_возврата IS NULL THEN SET сумма_штрафа = 1 * (DATEDIFF(CURRENT_DATE, дата_выдачи) - 7);
    ELSEIF DATEDIFF(дата_возврата, дата_выдачи) <= 7 THEN SET сумма_штрафа = 0;
    ELSE SET сумма_штрафа = 1 * (DATEDIFF(дата_возврата, дата_выдачи) - 7);
    END IF;
    RETURN сумма_штрафа;
END //
DELIMITER ;

-- Задание 4
DELIMITER //
CREATE FUNCTION рейтинг(id_автор int)
RETURNS DECIMAL(5,2)
DETERMINISTIC
BEGIN
	DECLARE рейтинг DECIMAL(5,2) DEFAULT 0;
    SELECT AVG(rating) INTO рейтинг
    FROM issues
    WHERE reader_id = id_автор
    GROUP BY (reader_id);
    RETURN рейтинг;
END //
DELIMITER ;

-- Задание 6
DELIMITER //
CREATE FUNCTION цены_книг(год INT)
RETURNS DECIMAL(10, 2)
DETERMINISTIC
BEGIN
	DECLARE цена DECIMAL(10,2) DEFAULT 0;
	SELECT SUM(price) INTO цена
    FROM books
    WHERE pub_year > год;
    RETURN цена;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE статистика(in год int)
BEGIN
	IF EXISTS ()
	CREATE TABLE IF NOT EXISTS library_stats(
		year INT,
        stats DECIMAL(10,2)
	);
    
END //
DELIMITER ;