CHARSET utf8;

CREATE DATABASE events;

USE events;

CREATE TABLE IF NOT EXISTS event_categories(
	auto_id INT NOT NULL AUTO_INCREMENT,
	id INT NOT NULL,
	lang ENUM('pt','en','es') NOT NULL,
	name VARCHAR(50) NOT NULL,
	description TEXT,
	unique(id, lang),
	PRIMARY KEY(auto_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;


CREATE TABLE IF NOT EXISTS events(
	auto_id INT NOT NULL AUTO_INCREMENT,
	id INT NOT NULL,
	lang ENUM('pt','en','es') NOT NULL,
	id_org_unit INT,
	id_category INT NOT NULL,
	name VARCHAR(255) NOT NULL,
	description TEXT,
	start_day DATE NOT NULL,
	start_time TIME,
	end_day DATE,
	end_time TIME,
	address VARCHAR(100),
	UNIQUE(id, lang),
	UNIQUE(lang, name, start_day, start_time, address),
	FOREIGN KEY(id_category, lang) REFERENCES event_categories(id, lang) ON DELETE CASCADE,
	PRIMARY KEY(auto_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS event_media_resources(
	auto_id INT NOT NULL AUTO_INCREMENT,
	id INT NOT NULL,
	id_event INT NOT NULL,
	lang ENUM('pt','en','es') NOT NULL,
	label VARCHAR(100),
	type ENUM('image', 'video', 'audio', 'link','other') NOT NULL,
	description VARCHAR(250),
	url VARCHAR(250) NOT NULL,
	FOREIGN KEY(id_event, lang) REFERENCES events(id, lang) ON DELETE CASCADE,
	UNIQUE(id, lang),
	PRIMARY KEY(auto_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS persons(
	id INT NOT NULL AUTO_INCREMENT,
	name VARCHAR(100) NOT NULL,
	PRIMARY KEY(id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;


CREATE TABLE IF NOT EXISTS person_infos(
	id_person INT NOT NULL,
	id INT NOT NULL AUTO_INCREMENT,
	lang ENUM('pt','en','es') NOT NULL,
	name VARCHAR(50) NOT NULL,
	content TEXT,
	FOREIGN KEY(id_person) REFERENCES persons(id) ON DELETE CASCADE,
	UNIQUE(id, lang),
	PRIMARY KEY(id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;


CREATE TABLE org_units(
	auto_id INT NOT NULL AUTO_INCREMENT,
	id INT NOT NULL,
	lang ENUM('pt','en','es') NOT NULL,
	name VARCHAR(100) NOT NULL,
	UNIQUE(id, lang),
	PRIMARY KEY(auto_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;



CREATE TABLE IF NOT EXISTS events_have_persons(
	id_event INT NOT NULL,
	id_person INT NOT NULL,
	FOREIGN KEY(id_event) REFERENCES events(id) ON DELETE CASCADE,
	FOREIGN KEY(id_person) REFERENCES persons(id) ON DELETE CASCADE,
	PRIMARY KEY(id_event, id_person)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


ALTER TABLE events ADD FOREIGN KEY(id_org_unit, lang) REFERENCES org_units(id, lang) ON DELETE CASCADE;