DROP PROCEDURE IF EXISTS `update_password`;
DELIMITER ;;
CREATE PROCEDURE `update_password` (
    IN p_current_password VARCHAR(100),
    IN p_new_password VARCHAR(100),
    IN p_session_id CHAR(64)
)
proc:BEGIN
    IF (SELECT COUNT(session_id) FROM sessions WHERE session_id = LOWER(p_session_id)) = 0 OR p_session_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'update_password: No user logged in';
        LEAVE proc;
    END IF;
    IF p_current_password = '' OR p_current_password IS NULL THEN
        SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = 'update_password: Incorrect password';
        LEAVE proc;
    END IF;
    IF p_new_password = '' OR p_new_password IS NULL THEN
        SIGNAL SQLSTATE '45002' SET MESSAGE_TEXT = 'update_password: Password cannot be empty';
        LEAVE proc;
    END IF;
    SELECT user_id INTO @user_id FROM sessions WHERE session_id = LOWER(p_session_id);
    SELECT SHA2(p_current_password, 256) INTO @p_current_password_sha256;
    SELECT password INTO @current_password_sha256 FROM users WHERE id = @user_id;
    IF @current_password_sha256 != @p_current_password_sha256 THEN
        SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = 'update_password: Incorrect password';
        LEAVE proc;
    END IF;
    SELECT SHA2(p_new_password, 256) INTO @new_password_sha256;
    UPDATE users SET password = @new_password_sha256 WHERE id = @user_id;
    SELECT 0 AS 'status';
END ;;
DELIMITER ;