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

delimiter ;^

	drop view if exists ProceduresView;^
	create view ProceduresView as select 
		`databases`.id as database_id, 
		`databases`.name as `database`, 
		procedures.id as procedure_id, 
		procedures.name as `procedure` 
	from Procedures procedures 
	join `Databases` `databases` on procedures.`database` = `databases`.id;^
	
	drop view if exists SessionsView;^
	create view SessionsView as select 
		sessions.id as id, 
		ips.id as ip_id, 
		ips.address as ip, 
		user_agents.id as user_agent_id, 
		user_agents.`data` as user_agent, 
		sessions.date_in as date_in, 
		sessions.date_last as date_last, 
		sessions.date_out as date_out 
	from Sessions sessions 
	join Ips ips on sessions.ip = ips.id 
	join UserAgents user_agents on sessions.user_agent = user_agents.id;^
	
	drop view if exists LogsView;^
	create view LogsView as select 
		logs.id as id, 
		sessions.id as session_id, 
		sessions.ip_id as ip_id, 
		sessions.ip as ip, 
		sessions.user_agent_id as user_agent_id, 
		sessions.user_agent as user_agent, 
		procedures.database_id as database_id, 
		procedures.`database` as `database`, 
		procedures.procedure_id as procedure_id, 
		procedures.`procedure` as `procedure`, 
		parameters.id as parameters_id, 
		parameters.`data` as parameters, 
		logs.error as error, 
		logs.date_in as date_in
	from Logs logs 
	left join SessionsView sessions on logs.`session` = sessions.id 
	join ProceduresView procedures on logs.`procedure` = procedures.procedure_id 
	join Parameters parameters on logs.parameters = parameters.id;^
	
	drop view if exists ExceptionsView;^
	create view ExceptionsView as select 
		exceptions.id as id, 
		sessions.id as session_id, 
		sessions.ip_id as ip_id, 
		sessions.ip as ip, 
		sessions.user_agent_id as user_agent_id, 
		sessions.user_agent as user_agent, 
		procedures.database_id as database_id, 
		procedures.`database` as `database`, 
		procedures.procedure_id as procedure_id, 
		procedures.`procedure` as `procedure`, 
		parameters.id as parameters_id, 
		parameters.`data` as parameters, 
		exceptions.error as error, 
		messages.id as message_id, 
		messages.`text` as message, 
		exceptions.status as status, 
		exceptions.code as code, 
		exceptions.date_in as date_in
	from Exceptions exceptions 
	left join SessionsView sessions on exceptions.`session` = sessions.id 
	join ProceduresView procedures on exceptions.`procedure` = procedures.procedure_id 
	join Parameters parameters on exceptions.parameters = parameters.id
	join Messages messages on exceptions.message = messages.id;^
	
	drop view if exists ScoresView;^
	create view ScoresView as select 
		scores.id as id, 
		modes.id as mode_id, 
		modes.name as mode, 
		nicks.id as nick_id, 
		nicks.name as nick, 
		scores.score as score, 
		scores.date_in as date_in 
	from Scores scores 
	join Modes modes on scores.mode = modes.id 
	join Nicks nicks on scores.nick = nicks.id
	where 
		scores.date_out is null && 
		modes.date_out is null && 
		nicks.date_out is null
	order by 
		scores.score desc, 
		scores.date_in asc;^
	
	drop view if exists ScoresFullView;^
	create view ScoresFullView as select 
		scores.id as id, 
		sessions.id as session_id, 
		sessions.ip_id as ip_id, 
		sessions.ip as ip, 
		sessions.user_agent_id as user_agent_id, 
		sessions.user_agent as user_agent, 
		procedures.database_id as database_id, 
		procedures.`database` as `database`, 
		procedures.procedure_id as procedure_id, 
		procedures.`procedure` as `procedure`, 
		modes.id as mode_id, 
		modes.name as mode, 
		nicks.id as nick_id, 
		nicks.name as nick, 
		scores.score as score, 
		scores.date_in as date_in 
	from Scores scores 
	join SessionsView sessions on scores.`session` = sessions.id 
	join ProceduresView procedures on scores.`procedure` = procedures.procedure_id 
	join Modes modes on scores.mode = modes.id 
	join Nicks nicks on scores.nick = nicks.id;^

delimiter ;