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

delimiter ;^

	drop procedure if exists tables_remove;^
	create procedure tables_remove() begin 
		
		-- Level 2.
		drop table if exists Scores;
		drop table if exists Exceptions;
		drop table if exists Logs;
	
		-- Level 1.
		drop table if exists Sessions;
		drop table if exists Procedures;
	
		-- Level 0.
		drop table if exists Modes;
		drop table if exists Nicks;
		drop table if exists Parameters;
		drop table if exists Messages;
		drop table if exists `Databases`;
		drop table if exists UserAgents;
		drop table if exists Ips;
		
	end;^

	drop procedure if exists tables_create;^
	create procedure tables_create() begin 
		
		-- Level 0.
		create table if not exists Ips(
			id integer not null auto_increment, 
			address varchar(39) not null, 
			date_in datetime not null default now(), 
			date_out datetime, 
			constraint ips_id primary key(id)
		);
		
		create table if not exists UserAgents(
			id integer not null auto_increment, 
			`data` text not null, 
			date_in datetime not null default now(), 
			date_out datetime, 
			constraint user_agents_id primary key(id)
		);
	
		create table if not exists `Databases`(
			id integer not null auto_increment, 
			name varchar(64) not null, 
			date_in datetime not null default now(), 
			date_out datetime, 
			constraint databases_id primary key(id)
		);
	
		create table if not exists Parameters(
			id integer not null auto_increment, 
			`data` text,  
			date_in datetime not null default now(), 
			date_out datetime, 
			constraint parameters_id primary key(id)
		);
	
		create table if not exists Messages(
			id integer not null auto_increment, 
			`text` text,  
			date_in datetime not null default now(), 
			date_out datetime, 
			constraint messages_id primary key(id)
		);
	
		create table if not exists Nicks(
			id integer not null auto_increment, 
			name varchar(32) not null,  
			date_in datetime not null default now(), 
			date_out datetime, 
			constraint nicks_id primary key(id)
		);
	
		create table if not exists Modes( -- For different game settings.
			id integer not null auto_increment, 
			name varchar(32) not null, 
			description text, 
			date_in datetime not null default now(), 
			date_out datetime, 
			constraint nicks_id primary key(id)
		);
	
		-- Level 1.
		create table if not exists Sessions(
			id integer not null auto_increment,
			ip integer not null, 
			user_agent integer not null, 
			date_in datetime not null default now(),
			date_last datetime not null default now(), 
			date_out datetime, 
			constraint sessions_id primary key(id), 
			constraint sessions_ip foreign key(ip) references Ips(id), 
			constraint sessions_user_agent foreign key(user_agent) references UserAgents(id)
		);
	
		create table if not exists Procedures(
			id integer not null auto_increment,
			`database` integer not null, 
			name varchar(64) not null, 
			date_in datetime not null default now(),
			date_out datetime, 
			constraint procedures_id primary key(id), 
			constraint procedures_database foreign key(`database`) references `Databases`(id)
		);
	
		-- Level 2.
		create table if not exists Logs(
			id integer not null auto_increment, 
			`session` integer, 
			`procedure` integer not null, 
			parameters integer not null, 
			error bigint, 
			date_in datetime not null default now(), 
			date_out datetime, 
			constraint logs_id primary key(id), 
			constraint logs_session foreign key(`session`) references Sessions(id), 
			constraint logs_procedure foreign key(`procedure`) references Procedures(id), 
			constraint logs_parameters foreign key(parameters) references Parameters(id)
		);
	
		create table if not exists Exceptions(
			id integer not null auto_increment, 
			`session` integer, 
			`procedure` integer not null, 
			parameters integer not null, 
			error bigint, 
			message integer not null, 
			status varchar(16), 
			code integer, 
			date_in datetime not null default now(), 
			date_out datetime, 
			constraint exceptions_id primary key(id), 
			constraint exceptions_session foreign key(`session`) references Sessions(id), 
			constraint exceptions_procedure foreign key(`procedure`) references Procedures(id), 
			constraint exceptions_parameters foreign key(parameters) references Parameters(id), 
			constraint exceptions_message foreign key(message) references Messages(id)
		);
	
		create table if not exists Scores(
			id integer not null auto_increment, 
			`session` integer not null, 
			`procedure` integer not null,  
			mode integer not null, 
			nick integer not null, 
			score integer not null, 
			date_in datetime not null default now(), 
			date_out datetime, 
			constraint scores_id primary key(id), 
			constraint scores_session foreign key(`session`) references Sessions(id), 
			constraint scores_procedure foreign key(`procedure`) references Procedures(id), 
			constraint scores_mode foreign key(mode) references Modes(id), 
			constraint scores_nick foreign key(nick) references Nicks(id)
		);
		
	end;^

	drop procedure if exists tables_update;^
	create procedure tables_update() begin 
		
		declare `$database` varchar(64) default 'DPTW';
	
		-- Level 0.
		if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Ips' && column_name = 'address' limit 1) is null then 
			alter table Ips add column address varchar(39) not null after id;
		end if;
	
		if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'UserAgents' && column_name = 'data' limit 1) is null then 
			alter table UserAgents add column `data` text not null after id;
		end if;
	
		if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Databases' && column_name = 'name' limit 1) is null then 
			alter table `Databases` add column name varchar(64) not null after id;
		end if;
	
		if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Parameters' && column_name = 'data' limit 1) is null then 
			alter table Parameters add column `data` text not null after id;
		end if;
	
		if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Messages' && column_name = 'text' limit 1) is null then 
			alter table Parameters add column `text` text not null after id;
		end if;
	
		if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Nicks' && column_name = 'name' limit 1) is null then 
			alter table Nicks add column name varchar(32) not null after id;
		end if;
	
		if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Modes' && column_name = 'name' limit 1) is null then 
			alter table Modes add column name varchar(32) not null after id;
		end if;
		if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Modes' && column_name = 'description' limit 1) is null then 
			alter table Modes add column description text not null after name;
		end if;
	
		-- Level 1.
		if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Sessions' && column_name = 'ip' limit 1) is null then begin 
			alter table Sessions add column ip integer not null after id;
			alter table Sessions add constraint sessions_ip foreign key(ip) references Ips(id);
		end;end if;
		if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Sessions' && column_name = 'user_agent' limit 1) is null then begin 
			alter table Sessions add column ip integer not null after ip;
			alter table Sessions add constraint sessions_user_agent foreign key(user_agent) references UserAgents(id);
		end;end if;
		if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Sessions' && column_name = 'date_last' limit 1) is null then 
			alter table Sessions add column date_last datetime not null default now() after date_in;
		end if;
	
		if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Procedures' && column_name = 'database' limit 1) is null then begin 
			alter table Procedures add column `database` integer not null after id;
			alter table Procedures add constraint procedures_database foreign key(`database`) references `Databases`(id);
		end;end if;
		if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Procedures' && column_name = 'name' limit 1) is null then 
			alter table Procedures add column name varchar(64) not null after `database`;
		end if;
	
		-- Level 2.
		if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Logs' && column_name = 'parameters' limit 1) is null then begin 
			alter table Logs add column parameters integer not null after `procedure`;
			alter table Logs add constraint logs_parameters foreign key(parameters) references Parameters(id);
		end;end if;
		if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Logs' && column_name = 'error' limit 1) is null then 
			alter table Logs add column error bigint not null after parameters;
		end if;
	
		if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Exceptions' && column_name = 'parameters' limit 1) is null then begin 
			alter table Exceptions add column parameters integer not null after `procedure`;
			alter table Exceptions add constraint exception_parameters foreign key(parameters) references Parameters(id);
		end;end if;
		if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Exceptions' && column_name = 'error' limit 1) is null then 
			alter table Exceptions add column error bigint not null after parameters;
		end if;
		if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Exceptions' && column_name = 'message' limit 1) is null then begin 
			alter table Exceptions add column message integer not null after error;
			alter table Exceptions add constraint exception_message foreign key(message) references Messages(id);
		end;end if;
		if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Exceptions' && column_name = 'status' limit 1) is null then 
			alter table Exceptions add column status varchar(16) after message;
		end if;
		if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Exceptions' && column_name = 'code' limit 1) is null then 
			alter table Exceptions add column code integer after message;
		end if;
	
		if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Scores' && column_name = 'mode' limit 1) is null then begin 
			alter table Scores add column mode integer not null after `procedure`;
			alter table Scores add constraint exception_mode foreign key(mode) references Modes(id);
		end;end if;
		if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Scores' && column_name = 'nick' limit 1) is null then begin 
			alter table Scores add column nick integer not null after mode;
			alter table Scores add constraint exception_nick foreign key(nick) references Nicks(id);
		end;end if;
		if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Scores' && column_name = 'score' limit 1) is null then 
			alter table Scores add column score integer not null after nick;
		end if;
		
	end;^
	
	call tables_remove();^
	call tables_create();^
	call tables_update();^

delimiter ;