create database if not exists DPTW character set utf8mb4 collate utf8mb4_general_ci;
use DPTW;

delimiter ;^

	drop procedure if exists sessions_full_validate;^
	create procedure sessions_full_validate(
		in `$session` text, 
		out $error bigint, 
		out $id integer, 
		out $ip_id integer, 
		out $user_agent_id integer 
	) begin 
	
		set $error := (case 
			when `$session` is null then 1 << 0 
			when `$session` = '' then 1 << 1 
			when !position(':' in `$session`) then 1 << 2 
		else 0 end) << 1;
	
		if !$error then begin 
			
			declare $separator_i integer default position(':' in `$session`);
			declare $id_string varchar(16) default substring(`$session`, 1, $separator_i - 1);
		
			set $error := (case 
				when $id_string = '' then 1 << 0 
				when $id_string != 'null' && $id_string not regexp '^[\-\+]?[0-9]+$' then 1 << 1 
			else 0 end) << 4;
			
			if !$error then begin 
		
				set $id := if($id_string = 'null', null, cast($id_string as signed));
				set `$session` := substring(`$session`, $separator_i + 1, length(`$session`));
				set $separator_i := position(':' in `$session`);
				set $error := (
					if($separator_i, 0, 1 << 0) | 
					(sessions_validate($id) & ~(1 << 0) << 1) | 
				0) << 6;
			
				if !$error then begin 
					
					declare $ip varchar(39) default substring(`$session`, 1, $separator_i - 1);
					declare $user_agent text default substring(`$session`, $separator_i + 1, length(`$session`));
				
					set $error := ( 
						if($ip = '', 1 << 0, 0) | 
						if($user_agent = '', 1 << 0, 0) | 
					0) << 8;
				
					if !$error then begin 
					
						set $ip_id := (select id from Ips where address = $ip limit 1);
						set $user_agent_id := (select id from UserAgents where `data` = $user_agent limit 1);
				
						if $ip_id is null then begin 
							insert into Ips(address) values($ip);
							set $ip_id := last_insert_id();
						end;end if;
					
						if $user_agent_id is null then begin 
							insert into UserAgents(`data`) values($user_agent);
							set $user_agent_id := last_insert_id();
						end;end if;
						
						set $error := if($id is null, 0, ifnull((
							select (
								if(ip = $ip_id, 0, 1 << 1) | 
								if(user_agent = $user_agent_id, 0, 1 << 2) | 
							0) from Sessions where id = $id limit 1 
						), 1 << 0) << 10);
					
					end;end if;
				
				end;end if;
			
			end;end if;
		
		end;end if;
		
	end;^

	drop procedure if exists sessions_update;^
	create procedure sessions_update(
		in `$session` text, 
		out $error bigint, 
		out $id integer 
	) begin 
		
		declare `$database` varchar(64) default 'DPTW';
		declare `$procedure` varchar(64) default 'sessions_update';
		declare $parameters text default '{}';
		declare $ip_id integer;
		declare $user_agent_id integer;
		declare exit handler for sqlexception begin
			get diagnostics condition 1
				@_status = returned_sqlstate,
				@_code = mysql_errno,
				@message = message_text;
			set $error := $error | 1 << 0;
			call exceptions_set($id, `$database`, `$procedure`, $parameters, $error, @message, @_status, @_code);
		end;
	
		call sessions_full_validate(`$session`, $error, $id, $ip_id, $user_agent_id);
		set $error := $error & ~(1 << 7);
	
		if !($error & ~(1 << 11)) then 
			if $error || $id is null then begin 
				insert into Sessions(ip, user_agent) values($ip_id, $user_agent_id);
				set $id := last_insert_id();
			end;else begin 
				select $id as id;
				update Sessions set date_last := now() where id = $id;
			end;end if;
		end if;
	
		call logs_set($id, `$database`, `$procedure`, $parameters, $error, !!$error);
		
	end;^

delimiter ;