DROP PROCEDURE IF EXISTS udp_login;

DELIMITER $$

CREATE PROCEDURE  udp_login(
IN uname VARCHAR(30),
IN pass VARCHAR(30)
)
BEGIN

IF uname NOT IN (select username from users)
THEN
	SIGNAL SQLSTATE '45000'
	SET MESSAGE_TEXT = 'Username does not exist!';
ELSEIF uname IN (select username from users) and pass NOT IN (select password from users)
THEN
	SIGNAL SQLSTATE '45000'
	SET MESSAGE_TEXT = 'Password is incorrect!';
ELSEIF uname IN (select username from logged_in_users)
THEN
	SIGNAL SQLSTATE '45000'
	SET MESSAGE_TEXT = 'User is already logged in!';
END IF;

START TRANSACTION;

INSERT INTO logged_in_users
	SELECT  users.id, users.username, users.password, users.email from users WHERE users.username=uname AND users.password=pass;

IF row_count()<>1
THEN
	ROLLBACK;
	SIGNAL SQLSTATE '45000'
	SET MESSAGE_TEXT = 'Cannot login. Try later!';
ELSE
	COMMIT;
END IF;

END $$

DELIMITER ;