DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `login`(IN usr VARCHAR(16),IN pwd VARCHAR(40))
	LANGUAGE SQL
	NOT DETERMINISTIC
	CONTAINS SQL
	SQL SECURITY DEFINER
	COMMENT ''
    READS SQL DATA
BEGIN
	DECLARE l_staff_id TINYINT(3);
	DECLARE l_usr VARCHAR(16);
	DECLARE l_usr_no_pwd VARCHAR(16);

	SELECT staff_id FROM staff WHERE username = usr INTO l_staff_id;
	SELECT username FROM staff WHERE password = pwd AND username = usr INTO l_usr;
	SELECT username FROM staff WHERE password IS NULL AND username = usr INTO l_usr_no_pwd;

	IF l_staff_id IS NULL
	THEN INSERT INTO LoginHistory(staff_id, date_time, is_success) VALUES (1, NOW(), 0);
	ELSEIF l_usr IS NULL AND l_usr_no_pwd
	THEN INSERT INTO LoginHistory(staff_id, date_time, is_success) VALUES (l_staff_id, NOW(), 0);
	ELSEIF l_usr IS NOT NULL
	THEN INSERT INTO LoginHistory(staff_id, date_time, is_success) VALUES (l_staff_id, NOW(), 1);
	ELSEIF l_usr_no_pwd IS NOT NULL
	THEN IF l_pwd = ""
		THEN INSERT INTO LoginHistory(staff_id, date_time, is_success) VALUES (l_staff_id, NOW(), 1);
		ELSEIF l_pwd != ""
		THEN INSERT INTO LoginHistory(staff_id, date_time, is_success) VALUES (l_staff_id, NOW(), 0);
		END IF;
	END IF;
END;