DROP PROCEDURE IF EXISTS `reset_password`;
DELIMITER ;;
CREATE PROCEDURE `reset_password` (
    IN p_password_reset_token CHAR(64),
    IN p_new_password VARCHAR(100)
)
proc:BEGIN
    IF (SELECT COUNT(password_reset_token) FROM users WHERE password_reset_token = LOWER(p_password_reset_token)) = 0 OR p_password_reset_token IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'reset_password: Invalid token';
        LEAVE proc;
    END IF;
    IF p_new_password = '' OR p_new_password IS NULL THEN
        SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = 'reset_password: No password provided';
        LEAVE proc;
    END IF;
    SELECT SHA2(p_new_password, 256) INTO @p_new_password_sha256;
    UPDATE users SET user_password = @p_new_password_sha256 WHERE password_reset_token = LOWER(p_password_reset_token);
    UPDATE users SET password_reset_token = NULL WHERE password_reset_token = LOWER(p_password_reset_token);
    SELECT 0 AS 'status', 'reset_password' AS 'proc';
END ;;
DELIMITER ;
