drop procedure if exists get_user_credentials;

delimiter #

create procedure get_user_credentials
(
in p_username varbinary(32),
in p_password varchar(32),
in p_session_id char(32),
in p_user_ip varchar(16)
)

proc_main:begin

declare p_user_id int unsigned default null;
declare p_status_id tinyint default -1;
declare p_today datetime default now();
declare p_reason varchar(255) default null;
declare p_ban_id int default null;

	-- ban checks

	select u.user_id, u.status_id into p_user_id, p_status_id
		from users u where u.username = p_username and u.password = p_password and u.user_id > 1;

	select ban_id, reason into p_ban_id, p_reason
		from IP_ban where ip_num = inet_aton(p_user_ip) and p_today >= created_date and p_today <= expiry_date;

	if p_ban_id is not null then

		-- banned user login attempt

		call insert_user_log(5, p_user_id, p_session_id, p_user_ip);

		select 0 as user_id, 1 as err_id, p_reason as msg;

		leave proc_main;

	end if;

	select ban_id, reason into p_ban_id, p_reason
		from username_ban where username = p_username and p_today >= created_date and p_today <= expiry_date;

	if p_ban_id is not null then

		-- banned user login attempt

		call insert_user_log(5, p_user_id, p_session_id, p_user_ip);

		select 0 as user_id, 1 as err_id, p_reason as msg;

		leave proc_main;

	end if;

	if p_user_id is null then

		-- invalid user login

		call insert_user_log(3, p_user_id, p_session_id, p_user_ip);

		select 0 as user_id, e.* from error_msg e where e.err_id = 102;

		leave proc_main;

	end if;

	if p_status_id <= 0 then

		-- suspended, banned etc

		call insert_user_log(4, p_user_id, p_session_id, p_user_ip);

		call insert_user_ip(p_user_id, p_session_id, p_user_ip);

		select 0 as user_id, s.msg from user_status s where s.status_id = p_status_id;

		leave proc_main;

	end if;

	-- valid user let 'em in

	update users u set u.last_login_date = now() where u.user_id = p_user_id;

	call insert_user_log(1, p_user_id, p_session_id, p_user_ip);

	call insert_user_ip(p_user_id, p_session_id, p_user_ip);

	select
		u.user_id,u.username,u.role_id,u.role_name,u.status_id,
		u.status_name,u.theme_id,u.theme_name,game_id,
		u.last_login_date_fmt, warn_count
	from
		users_view u where u.user_id = p_user_id;


end proc_main #

delimiter ;