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

delimiter ;^
	
	drop view if exists EnrollmentsView;^
	create view EnrollmentsView as select 
		enrollments.id as id,
		procedures.database_id as database_id, 
		procedures.id as procedure_id, 
		procedures.`database` as `database`, 
		procedures.`procedure` as `procedure`, 
		sessions.id as session_id, 
		sessions.user_id as user_id, 
		sessions.nick as nick, 
		sessions.ip_id as ip_id, 
		sessions.ip as ip, 
		enrollments.`year` as `year`, 
		types.id as type_id, 
		types.name as type_name, 
		partners.id as partner_id, 
		partners.code as partner_code, 
		courses.id as course_id, 
		courses.code as course_code, 
		courses.name as course_name, 
		centers.id as center_id, 
		centers.code as center_code, 
		centers.name as center_name, 
		enrollments.score as score, 
		enrollments.date_in as date_in 
	from Enrollments enrollments 
	join AnP.SessionsFullView sessions on enrollments.`session` = sessions.id 
	join AnP.ProceduresFullView procedures on enrollments.`procedure` = procedures.id 
	join EnrollmentsTypes types on enrollments.`type` = types.id 
	join Partners partners on enrollments.partner = partners.id 
	join Courses courses on enrollments.course = courses.id 
	join Centers centers on enrollments.center = centers.id 
	where 
		types.date_out is null && 
		enrollments.date_out is null && 
		partners.date_out is null && 
		courses.date_out is null && 
		centers.date_out is null;^

delimiter ;