DROP PROCEDURE IF EXISTS `forgot_password`;
DELIMITER ;;
CREATE PROCEDURE `forgot_password` (
    IN p_user_email VARCHAR(100)
)
proc:BEGIN
    IF (SELECT COUNT(user_email) FROM users WHERE user_email = p_user_email) = 0 OR p_user_email IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'forgot_password: Invalid email';
        LEAVE proc;
    END IF;
    SELECT user_username INTO @username FROM users WHERE user_email = p_user_email;
    SELECT LOWER(HEX(RANDOM_BYTES(32))) INTO @password_reset_token;
    UPDATE users SET password_reset_token = @password_reset_token WHERE user_email = p_user_email;
    SELECT 0 AS 'status', @username AS 'username', @password_reset_token AS 'password_reset_token', 'forgot_password' AS 'proc';
END ;;
DELIMITER ;