DROP PROCEDURE IF EXISTS `get_notifications`;
DELIMITER ;;
CREATE PROCEDURE `get_notifications` (
    IN p_session_id CHAR(64)
)
proc:BEGIN
    DECLARE user_id_var INT;
    IF (SELECT COUNT(user.id) FROM users AS user INNER JOIN sessions AS sess ON user.id = sess.user_id WHERE sess.session_id = p_session_id) = 0 OR p_session_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'get_notifications: Login Required';
        LEAVE proc;
    END IF;

    SELECT user.id INTO user_id_var FROM users AS user INNER JOIN sessions AS sess ON user.id = sess.user_id WHERE sess.session_id =p_session_id;
    UPDATE notifications SET is_seen = 1 WHERE user_id = user_id_var AND is_seen = 0;
    
    SELECT N.notification_id, N.timestamp as notification_timestamp, N.notification_type as notification_type, P.id as product_id, P.name as product_name, N.is_seen AS is_seen
    FROM notifications AS N
    LEFT JOIN products AS P ON N.product_id = P.id
    WHERE  N.user_id = user_id_var ORDER BY N.timestamp DESC;
END ;;
DELIMITER ;