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

delimiter ;^

	drop function if exists enrollments_add_validate;^
	create function enrollments_add_validate(
		`$year` smallint, 
		`$type` varchar(32), 
		$partner char(10), 
		$course_code char(7), 
		$course_name varchar(256), 
		$center_code integer, 
		$center_name varchar(256), 
		$score tinyint
	) returns integer begin 
		return (
			((case 
				when `$year` is null then 1 << 0 
				when `$year` < 1985 then 1 << 1 
				when `$year` > year(now()) then 1 << 1 
			else 0 end) << 0) | 
			((case 
				when `$type` is null then 1 << 0 
				when `$type` = '' then 1 << 1 
			else 0 end) << 2) | 
			((case 
				when $partner is null then 1 << 0 
				when $partner = '' then 1 << 1 
			else 0 end) << 4) | 
			((case 
				when $course_code is null then 1 << 0 
				when $course_code = '' then 1 << 1 
			else 0 end) << 6) | 
			((case 
				when $course_name is null then 1 << 0 
				when $course_name = '' then 1 << 1 
			else 0 end) << 8) | 
			((case 
				when $center_code is null then 1 << 0 
				when $center_code < 0 then 1 << 1 
			else 0 end) << 10) | 
			((case 
				when $center_name is null then 1 << 0 
				when $center_name = '' then 1 << 1 
			else 0 end) << 12) | 
			((case 
				when $score is null then 1 << 0 
				when $score < 0 then 1 << 1 
				when $score > 55 then 1 << 2 
			else 0 end) << 14) | 
		0);
	end;^
	
	drop procedure if exists enrollments_add;^
	create procedure enrollments_add(
		in `$session` text, 
		in `$year` smallint, 
		in `$type` varchar(32), 
		in $partner char(10), 
		in $course_code char(7), 
		in $course_name varchar(256), 
		in $center_code integer, 
		in $center_name varchar(256), 
		in $score tinyint, 
		out $error varchar(512), 
		out $id integer
	) begin 
		
		declare `$database` varchar(64) default AnP.settings_get('database_fpdam2023', false);
		declare `$procedure` varchar(64) default 'enrollments_add';
		declare $procedure_id integer;
		declare $parameters text default json_set('{}', 
			'$.year', `$year`, 
			'$.type', `$type`, 
			'$.partner', $partner, 
			'$.course_code', $course_code, 
			'$.course_name', $course_name, 
			'$.center_code', $center_code, 
			'$.center_name', $center_name, 
			'$.score', $score, 
			'$.id', 0
		);
		declare $session_id integer;
		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;
			rollback;
			set $error := AnP.errors_set($error, 1 << 0, 0, 0);
			call AnP.exceptions_set($session_id, `$database`, `$procedure`, $ip_id, $user_agent_id, $error, $parameters, @message, @_status, @_code);
		end;
	
		start transaction;
		
		set $id := 0;
		if ifnull(`$session`, '') = '' then 
			set $session_id := AnP.sessions_get_machine();
		else 
			call sessions_validate_full(`$session`, '["administrator"]', $error, $session_id, $ip_id, $user_agent_id);
		end if;
		set $error := AnP.errors_set($error, enrollments_add_validate(
			`$year`, `$type`, $partner, $course_code, $course_name, $center_code, $center_name, $score
		), 30, 0);
	
		if ifnull($error, '') = '' then begin 
			
			declare $type_id integer default (select id from EnrollmentsTypes where date_out is null && name = `$type` limit 1);
			declare $partner_id integer default (select id from Partners where date_out is null && code = $partner limit 1);
			declare $course_id integer default (select id from Courses where date_out is null && code = $course_code limit 1);
			declare $center_id integer default (select id from Centers where date_out is null && code = $center_code limit 1);
			
			call AnP.procedures_get(`$database`, `$procedure`, $procedure_id);
		
			if $type_id is null then begin 
				insert into EnrollmentsTypes(`session`, `procedure`, name) values
					($session_id, $procedure_id, `$type`);
				set $type_id := last_insert_id();
			end;end if;
		
			if $partner_id is null then begin 
				insert into Partners(`session`, `procedure`, code) values
					($session_id, $procedure_id, $partner);
				set $partner_id := last_insert_id();
			end;end if;
		
			if $course_id is null then begin 
				insert into Courses(`session`, `procedure`, code, name) values
					($session_id, $procedure_id, $course_code, $course_name);
				set $course_id := last_insert_id();
			end;end if;
		
			if $center_id is null then begin 
				insert into Centers(`session`, `procedure`, code, name) values
					($session_id, $procedure_id, $center_code, $center_name);
				set $center_id := last_insert_id();
			end;end if;
		
			insert into Enrollments(`session`, `procedure`, `year`, `type`, partner, course, center, score) values
				($session_id, $procedure_id, `$year`, $type_id, $partner_id, $course_id, $center_id, $score);
			set $id := last_insert_id();
		
			set $parameters := json_set($parameters, '$.id', $id);
		
		end;end if;
	
		commit;
	
		call AnP.logs_set($session_id, `$database`, `$procedure`, $ip_id, $user_agent_id, $error, $parameters, 1 << 1);
		
	end;^

delimiter ;