DROP PROCEDURE IF EXISTS so984_testowa.UpdateBestsellers;

DELIMITER $$

CREATE PROCEDURE so984_testowa.UpdateBestsellers()
BEGIN
	DECLARE BOOKSREAD, DAYS, BK_ID, RDR_ID INT;
    DECLARE BOOKSPERMONTH DECIMAL(5,2);
    DECLARE FINISHED INT DEFAULT 0;
    DECLARE ALL_READERS CURSOR FOR SELECT READER_ID FROM READERS;
    DECLARE ALL_BOOKS CURSOR FOR SELECT so984_testowa.BOOKS.BOOK_ID FROM so984_testowa.BOOKS;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET FINISHED = 1;
    OPEN ALL_BOOKS;
    OPEN ALL_READERS;

    WHILE (FINISHED = 0) DO
		FETCH ALL_BOOKS INTO BK_ID;
        FETCH ALL_READERS INTO RDR_ID;
        IF (FINISHED = 0) THEN
			SELECT COUNT(*) FROM so984_testowa.RENTS
				WHERE so984_testowa.RENTS.READER_ID = RDR_ID
				INTO BOOKSREAD;

			SELECT DATEDIFF(MAX(so984_testowa.RENTS.RENT_DATE), MIN(so984_testowa.RENTS.RENT_DATE)) + 1 FROM so984_testowa.RENTS
				WHERE so984_testowa.RENTS.READER_ID = RDR_ID
				INTO DAYS;
			SET BOOKSPERMONTH = BOOKSREAD / DAYS * 30;

            IF BOOKSPERMONTH > 2 THEN
				UPDATE so984_testowa.BOOKS SET BESTSELLER = TRUE
					WHERE BOOK_ID = BK_ID;
            ELSE
				UPDATE so984_testowa.BOOKS SET BESTSELLER = FALSE
					WHERE BOOK_ID = BK_ID;
            END IF;
			COMMIT;
        END IF;
    END WHILE;

	SELECT BOOKSREAD, DAYS, BOOKSPERMONTH;
END $$

DELIMITER ;

CALL so984_testowa.UpdateBestsellers();

SELECT * FROM so984_testowa.BOOKS;