#spCheckPwd
DROP PROCEDURE spCheckPwd;
DELIMITER //
CREATE PROCEDURE spCheckPwd (IN in_username VARCHAR(50), IN in_password CHAR(32), OUT access INT(10))
BEGIN
DECLARE id INT DEFAULT -1;
DECLARE admin INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET id=-1, admin=0;
SELECT user_id INTO id FROM USER WHERE email = in_username AND password = md5(in_password) AND deleted = 0;
SELECT is_admin INTO admin FROM USER WHERE email = in_username AND password = md5(in_password) AND deleted = 0;
CASE
WHEN admin = 1 THEN
SELECT 2 INTO access;
WHEN id > -1 THEN
SELECT 1 INTO access;
ELSE
SELECT 0 INTO access;
END CASE;
END;
//
DELIMITER ;

#spUpdateUser:
DROP PROCEDURE spUpdateUser;
DELIMITER //
CREATE PROCEDURE spUpdateUser (IN in_username VARCHAR(20), IN in_password CHAR(32), IN target_username VARCHAR(20), IN in_name VARCHAR(20), IN in_gender INT(10), IN in_email VARCHAR(32), IN in_address_street VARCHAR(32), IN in_address_zipcode VARCHAR(32), IN in_address_city VARCHAR(32), IN in_hobbies VARCHAR(100), IN in_friendship BOOLEAN, IN in_romance BOOLEAN)
BEGIN
DECLARE user_access INT;
DECLARE calling_uname VARCHAR(20);
CALL spCheckPwd(in_username, in_password, user_access);
CALL spGetUsername(in_username, in_password, calling_uname);
CASE
WHEN user_access = 2 THEN
UPDATE USER
SET name = in_name, gender = in_gender, email = in_email, address_street = in_address_street, address_zipcode = in_address_zipcode, address_city = in_address_city, hobbies = in_hobbies, friendship = in_friendship, romance = in_romance
WHERE username = target_username;
WHEN user_access = 1 AND calling_uname = target_username THEN
UPDATE USER
SET name = in_name, gender = in_gender, email = in_email, address_street = in_address_street, address_zipcode = in_address_zipcode, address_city = in_address_city, hobbies = in_hobbies, friendship = in_friendship, romance = in_romance
WHERE username = target_username;
END CASE;
END;
//
DELIMITER ;