DROP PROCEDURE IF EXISTS `sign_up`;
DELIMITER ;;
CREATE PROCEDURE `sign_up`(
    IN p_email VARCHAR(50),
    IN p_password VARCHAR(100),
    IN p_name VARCHAR(50),
    IN p_username VARCHAR(50),
    IN p_location VARCHAR(200),
    IN p_bio VARCHAR(200)
)
proc:BEGIN
    IF (SELECT COUNT(email) FROM users WHERE email = p_email) != 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'sign_up: Email already used';
        LEAVE proc;
    END IF;
    IF (SELECT COUNT(username) FROM users WHERE username = p_username) != 0 THEN
        SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = 'sign_up: Username already used';
        LEAVE proc;
    END IF;
    SELECT SHA2(p_password, 256) INTO @password_sha256;
    SELECT LOWER(HEX(RANDOM_BYTES(32))) INTO @confirmation_token;
    INSERT INTO users
    (`type`, `email`, `password`, `name`, `username`, `location`, `bio`, `confirmation_token`, `password_reset_token`, `archived`)
    VALUES
    (2, p_email, @password_sha256, p_name ,p_username, p_location, p_bio, @confirmation_token, NULL, 0);
    SELECT 0 AS 'status', @confirmation_token AS 'confirmation_token', 'sign_up' AS 'proc';
END ;;
DELIMITER ;