DROP DATABASE IF EXISTS library;
CREATE DATABASE library;
USE library;

CREATE TABLE Authors (
    author_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE Users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) UNIQUE NOT NULL,
    hashed_password VARCHAR(255) NOT NULL
);

CREATE TABLE Genres (
    genre_id INT AUTO_INCREMENT PRIMARY KEY,
    genre_name VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE Publishers (
    publisher_id INT AUTO_INCREMENT PRIMARY KEY,
    publisher_name VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE Books (
    book_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    page_count INT NOT NULL,
    year_published INT NOT NULL,
    publisher_id INT NOT NULL,
    genre_id INT NOT NULL,
    issue_number VARCHAR(50) DEFAULT NULL,
    is_available BOOLEAN NOT NULL DEFAULT TRUE,
    frequency_status VARCHAR(50) DEFAULT NULL,
    FOREIGN KEY (publisher_id) REFERENCES Publishers(publisher_id),
    FOREIGN KEY (genre_id) REFERENCES Genres(genre_id)
);

CREATE TABLE Ratings (
    rating_id INT AUTO_INCREMENT PRIMARY KEY,
    book_id INT NOT NULL,
    user_id INT NOT NULL,
    rating INT NOT NULL,
    FOREIGN KEY (book_id) REFERENCES Books(book_id),
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

CREATE TABLE UsersReads (
    read_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    book_id INT NOT NULL,
    progress INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES Users(user_id),
    FOREIGN KEY (book_id) REFERENCES Books(book_id)
);

CREATE TABLE Books_Authors (
    book_id INT NOT NULL,
    author_id INT NOT NULL,
    FOREIGN KEY (book_id) REFERENCES Books(book_id),
    FOREIGN KEY (author_id) REFERENCES Authors(author_id),
    PRIMARY KEY (book_id, author_id)
);

CREATE TABLE Comments (
    comment_id INT AUTO_INCREMENT PRIMARY KEY,
    book_id INT NOT NULL,
    user_id INT NOT NULL,
    comment TEXT NOT NULL,
    comment_date DATETIME NOT NULL,
    FOREIGN KEY (book_id) REFERENCES Books(book_id),
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

CREATE TABLE Lends (
    lend_id INT AUTO_INCREMENT PRIMARY KEY,
    book_id INT NOT NULL,
    user_id INT NOT NULL,
    checkout_date DATE NOT NULL,
    due_date DATE NOT NULL,
    return_date DATE,
    status ENUM('lend', 'returned', 'overdue') NOT NULL,
    FOREIGN KEY (book_id) REFERENCES Books(book_id),
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

/* Add data */

/* Hash password before add user */
DELIMITER $$

CREATE TRIGGER HashPasswordBeforeInsert BEFORE INSERT ON Users
FOR EACH ROW
BEGIN
    SET NEW.hashed_password = SHA2(NEW.hashed_password, 256);
END$$

DELIMITER ;

INSERT INTO Authors (name) VALUES
('J.K. Rowling'),
('George R.R. Martin'),
('Agatha Christie');

INSERT INTO Users (username, hashed_password) VALUES
('user1', 'pass1'),
('user2', 'pass2'),
('user3', 'pass3');

INSERT INTO Genres (genre_name) VALUES
('Fantasy'),
('Mystery'),
('Science Fiction'),
('Fiction'),
('Thriller'),
('Crime'),
('Art'),
('Novel');

INSERT INTO Publishers (publisher_name) VALUES
('Bloomsbury'),
('HarperCollins'),
('Penguin Random House'),
('Helikon'),
('Hermes');

INSERT INTO Books (title, page_count, year_published, publisher_id, genre_id, issue_number) VALUES
('The Girl with the Dragon Tattoo', 309, 2005, 1, 1, null),
('A Game of Thrones', 694, 1996, 2, 1, null),
('Murder on the Orient Express', 256, 1934, 3, 2, null),
('Science Today', 120, 2023, 3, 3, 'Part 3'),
('And Then There Were None', 500, 2017, 4, 6, null);

INSERT INTO Ratings (book_id, user_id, rating) VALUES
(1, 1, 5),
(1, 1, 6),
(2, 1, 4),
(3, 2, 5);

INSERT INTO UsersReads (user_id, book_id, progress) VALUES
(1, 1, 100),
(2, 2, 150),
(3, 3, 50);

INSERT INTO Books_Authors (book_id, author_id) VALUES
(1, 1),
(2, 2),
(3, 3);

INSERT INTO Comments (book_id, user_id, comment, comment_date) VALUES
(1, 1, 'Много интересна книга!', '2024-01-01 12:00:00'),
(2, 2, 'Интригуваща инстория. Препоръчвам!', '2024-01-02 13:30:00'),
(3, 3, 'Класическа мистерия.', '2024-01-03 15:45:00');

/* 2 */
SELECT *
FROM Books
WHERE year_published > 2000;

/* 3 */
SELECT book_id, AVG(rating) AS average_rating
FROM Ratings
GROUP BY book_id;

/* 4 */
SELECT Books.title, Authors.name
FROM Books
INNER JOIN Books_Authors ON Books.book_id = Books_Authors.book_id
INNER JOIN Authors ON Books_Authors.author_id = Authors.author_id;

/* 5 */
SELECT Books.title, AVG(Ratings.rating) AS average_rating
FROM Books
LEFT OUTER JOIN Ratings ON Books.book_id = Ratings.book_id
GROUP BY Books.title;

/* 6 */
SELECT book_id, AVG(rating) AS average_rating
FROM Ratings
GROUP BY book_id
HAVING AVG(rating) > (
    SELECT AVG(rating) FROM Ratings
)
ORDER BY average_rating DESC;

/* 7 */
SELECT Books.title, Publishers.publisher_name, AVG(Ratings.rating) AS average_rating
FROM Books
JOIN Publishers ON Books.publisher_id = Publishers.publisher_id
LEFT JOIN Ratings ON Books.book_id = Ratings.book_id
GROUP BY Books.title, Publishers.publisher_name
ORDER BY average_rating DESC;

/* 8 */
DELIMITER $$

CREATE TRIGGER UpdateBookStatusOut BEFORE INSERT ON Lends
FOR EACH ROW
BEGIN
    IF NEW.return_date IS NULL THEN
        UPDATE Books SET is_available = FALSE WHERE book_id = NEW.book_id;
    END IF;
END$$

DELIMITER ;

DELIMITER $$

CREATE TRIGGER UpdateBookStatusReturn BEFORE UPDATE ON Lends
FOR EACH ROW
BEGIN
    IF OLD.status = 'lend' AND NEW.status = 'returned' THEN
        UPDATE Books SET is_available = TRUE WHERE book_id = NEW.book_id;
    END IF;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE LendBook(IN bookID INT, IN userID INT, IN dueDate DATE)
BEGIN
    DECLARE available BOOLEAN;
    SELECT is_available INTO available FROM Books WHERE book_id = bookID;
    IF available THEN
        INSERT INTO Lends (book_id, user_id, checkout_date, due_date, status)
        VALUES (bookID, userID, CURDATE(), dueDate, 'lend');
    ELSE
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'This book is currently unavailable.';
    END IF;
END$$

DELIMITER ;

CALL LendBook(1, 1, '2024-05-01');

DELIMITER $$

CREATE PROCEDURE ReturnBook(IN lendID INT)
BEGIN
    UPDATE Lends
    SET return_date = CURDATE(), status = 'returned'
    WHERE lend_id = lendID;
END$$

DELIMITER ;

CALL ReturnBook(1);

/* 9 */
DELIMITER $$

CREATE PROCEDURE UpdateBookFrequency()
BEGIN
    DECLARE v_book_id INT;
    DECLARE v_count INT;
    DECLARE finished INT DEFAULT 0;

    DECLARE book_cursor CURSOR FOR
        SELECT book_id, COUNT(*) FROM Lends
        WHERE checkout_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 YEAR) AND CURDATE()
        GROUP BY book_id;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

    OPEN book_cursor;

    fetch_loop: LOOP
        FETCH book_cursor INTO v_book_id, v_count;
        IF finished = 1 THEN
            LEAVE fetch_loop;
        END IF;

        /* Update status of the book */
        IF v_count > 10 THEN
            UPDATE Books SET frequency_status = 'често изнасяни' WHERE book_id = v_book_id;
        ELSEIF v_count > 5 THEN
            UPDATE Books SET frequency_status = 'редовно изнасяни' WHERE book_id = v_book_id;
        ELSE
            UPDATE Books SET frequency_status = 'рядко изнасяни' WHERE book_id = v_book_id;
        END IF;

    END LOOP;

    CLOSE book_cursor;
END$$

DELIMITER ;

CALL UpdateBookFrequency();