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

delimiter ;^

	drop procedure if exists tables_remove;^
	create procedure tables_remove() begin 
		
		-- Level 1.
		drop table if exists Enrollments;
		
		-- Level 0.
		drop table if exists Partners;
		drop table if exists Courses;
		drop table if exists Centers;
		
	end;^

	drop procedure if exists tables_create;^
	create procedure tables_create() begin 
		
		-- Level 0.
		create table if not exists Partners(
			id integer not null auto_increment, 
			`session` integer not null, 
			`procedure` integer not null, 
			code char(10) not null, 
			date_in datetime not null default now(), 
			date_out datetime, 
			constraint partners_id primary key(id), 
			constraint partners_session foreign key(`session`) references AnP.Sessions(id), 
			constraint partners_procedure foreign key(`procedure`) references AnP.Procedures(id)
		);
		
		create table if not exists Courses(
			id integer not null auto_increment,  
			`session` integer not null, 
			`procedure` integer not null, 
			code char(7) not null, 
			name varchar(256) not null,
			date_in datetime not null default now(), 
			date_out datetime, 
			constraint courses_id primary key(id), 
			constraint courses_session foreign key(`session`) references AnP.Sessions(id), 
			constraint courses_procedure foreign key(`procedure`) references AnP.Procedures(id)
		);
		
		create table if not exists Centers(
			id integer not null auto_increment,  
			`session` integer not null, 
			`procedure` integer not null, 
			code integer not null, 
			name varchar(256) not null,
			date_in datetime not null default now(), 
			date_out datetime, 
			constraint centers_id primary key(id), 
			constraint centers_session foreign key(`session`) references AnP.Sessions(id), 
			constraint centers_procedure foreign key(`procedure`) references AnP.Procedures(id)
		);
	
		create table if not exists EnrollmentsTypes(
			id integer not null auto_increment,  
			`session` integer not null, 
			`procedure` integer not null, 
			name varchar(32) not null, 
			date_in datetime not null default now(), 
			date_out datetime, 
			constraint enrollments_types_id primary key(id), 
			constraint enrollments_types_session foreign key(`session`) references AnP.Sessions(id), 
			constraint enrollments_types_procedure foreign key(`procedure`) references AnP.Procedures(id)
		);
		
		-- Level 1.
		create table if not exists Enrollments(
			id integer not null auto_increment,  
			`session` integer not null, 
			`procedure` integer not null, 
			`year` smallint not null, 
			`type` integer not null, 
			partner integer not null, 
			center integer not null, 
			course integer not null, 
			score tinyint not null, 
			date_in datetime not null default now(), 
			date_out datetime, 
			constraint enrollments_id primary key(id), 
			constraint enrollments_session foreign key(`session`) references AnP.Sessions(id), 
			constraint enrollments_procedure foreign key(`procedure`) references AnP.Procedures(id), 
			constraint enrollments_type foreign key(`type`) references EnrollmentsTypes(id), 
			constraint enrollments_partner foreign key(partner) references Partners(id), 
			constraint enrollments_center foreign key(center) references Centers(id), 
			constraint enrollments_course foreign key(course) references Courses(id)
		);
		
	end;^
	
	drop procedure if exists tables_update;^
	create procedure tables_update() begin 
		
		declare `$database` varchar(64) default AnP.settings_get('database_fpdam2023', false);
		
		-- Partners.
		if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Partners' && column_name = 'code' limit 1) is null then 
			alter table Partners add column code char(10) not null;
		end if;
		
		-- Courses.
		if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Courses' && column_name = 'code' limit 1) is null then 
			alter table Courses add column code char(7) not null;
		end if;
		if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Courses' && column_name = 'name' limit 1) is null then 
			alter table Courses add column name varchar(256) not null;
		end if;
		
		-- Centers.
		if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Centers' && column_name = 'code' limit 1) is null then 
			alter table Centers add column code integer not null;
		end if;
		if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Centers' && column_name = 'name' limit 1) is null then 
			alter table Centers add column name varchar(256) not null;
		end if;
	
		-- EnrollmentsTypes.
		if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'EnrollmentsTypes' && column_name = 'name' limit 1) is null then 
			alter table EnrollmentsTypes add column name varchar(32) not null;
		end if;
	
		-- Enrollments.
		if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Enrollments' && column_name = 'year' limit 1) is null then 
			alter table Enrollments add column `year` smallint not null;
		end if;
		if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Enrollments' && column_name = 'type' limit 1) is null then begin 
			alter table Enrollments add column `type` integer not null;
			alter table Enrollments add constraint enrollments_type foreign key(`type`) references EnrollmentsTypes(id);
		end;end if;
		if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Enrollments' && column_name = 'partner' limit 1) is null then begin 
			alter table Enrollments add column partner integer not null;
			alter table Enrollments add constraint enrollments_partner foreign key(partner) references Partners(id);
		end;end if;
		if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Enrollments' && column_name = 'center' limit 1) is null then begin 
			alter table Enrollments add column center integer not null;
			alter table Enrollments add constraint enrollments_center foreign key(center) references Centers(id);
		end;end if;
		if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Enrollments' && column_name = 'course' limit 1) is null then begin 
			alter table Enrollments add column course integer not null;
			alter table Enrollments add constraint enrollments_course foreign key(course) references Courses(id);
		end;end if;
		if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Enrollments' && column_name = 'score' limit 1) is null then 
			alter table Enrollments add column score tinyint not null;
		end if;
		
	end;^
	
	drop procedure if exists tables_fill;^
	create procedure tables_fill() begin 
		
	end;^
	
	call tables_remove();^
	call tables_create();^
	call tables_update();^
	call tables_fill();^

delimiter ;