DROP PROCEDURE IF EXISTS `login`;
DELIMITER ;;
CREATE PROCEDURE `login`(
    IN p_username VARCHAR(50),
    IN p_password VARCHAR(100)
)
proc:BEGIN
    SELECT SHA2(p_password, 256) INTO @password_sha256;
    IF (SELECT COUNT(username) FROM users WHERE username = p_username) = 0 OR p_username IS NULL THEN
        -- SELECT -1 AS 'status', 'login' AS 'proc';
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'login: ';
        LEAVE proc;
    END IF;
    IF (SELECT password FROM users WHERE username = p_username) != @password_sha256 THEN
        -- SELECT -1 AS 'status', 'login' AS 'proc';
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'login: ';
        LEAVE proc;
    END IF;
    SELECT id, type INTO @user_id, @user_type FROM users WHERE username = p_username AND password = @password_sha256;
    
    /* Check if account is confirmed, abort if not */
    IF (SELECT confirmation_token FROM users WHERE id = @user_id) IS NOT NULL THEN
        -- SELECT -2 AS 'status', 'Account not yet confirmed' AS 'msg', 'login' AS 'proc';
        SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = 'login: Account not yet confirmed';
        LEAVE proc;
    END IF;
    
    /* If a password reset was attempted but the account logged in successfully */
    IF (SELECT password_reset_token FROM users WHERE id = @user_id) IS NOT NULL THEN
        UPDATE users SET password_reset_token = NULL WHERE id = @user_id;
    END IF;
    
    SELECT LOWER(HEX(RANDOM_BYTES(32))) INTO @session_id;
    IF (SELECT COUNT(user_id) FROM sessions WHERE user_id = @user_id) != 0 THEN
        UPDATE sessions SET session_id = @session_id WHERE user_id = @user_id;
    ELSE
        INSERT INTO sessions VALUES (@session_id, @user_id, @user_type);
    END IF;
    SELECT 0 AS 'status', @session_id AS 'session_id', @user_type AS 'user_type', 'login' AS 'proc';
END ;;
DELIMITER ;