DROP DATABASE IF EXISTS event_database;
CREATE DATABASE event_database;
USE event_database;

CREATE TABLE events(
	id INT AUTO_INCREMENT PRIMARY KEY,
	address varchar(255) NOT NULL,
	event_data datetime  NOT NULL,
	event_time time NOT NULL,
	event_subject varchar(255) NOT NULL,
	duratio INT NOT NULL
);

CREATE TABLE roles(
	id INT AUTO_INCREMENT PRIMARY KEY,
	role varchar(255)
);

CREATE TABLE users (
	id INT AUTO_INCREMENT PRIMARY KEY,
	names varchar(255) NOT NULL,
	address varchar(255) NOT NULL,
	telephone varchar(15) NOT NULL,
	email varchar(255) NOT NULL,
	roleId INT NOT NULL,
	CONSTRAINT FOREIGN KEY (roleId) REFERENCES roles(id)
);

CREATE TABLE users_events(
	id INT AUTO_INCREMENT PRIMARY KEY,
	userId INT NOT NULL,
	eventId INT NOT NULL,
	CONSTRAINT FOREIGN KEY (userId) REFERENCES users(id),
	CONSTRAINT FOREIGN KEY (eventId) REFERENCES events(id)
);

INSERT INTO events(address, event_data, event_time, event_subject, duratio)
VALUES("Sofia, Bulgaria", DATE '2017-05-21', '10:30', "Flight To America", 15),
		("Ocen City, Maryland", DATE '2017-07-17', '20:00', "Lubo's Birthday", 12),
	  	("Miami, Florida", DATE '2017-09-25', '10:30', "Flight To Bulgaria", 15),
	  	("Sofia, Bulgaria", DATE '2017-05-21', '10:00', "Bulgarian Airport", 2),
	  	("Earth", DATE '9999-05-21', '10:00', "Hell Event", 20);

INSERT INTO roles(role)
VALUES("creator"), ("participant");

INSERT INTO users(names, address, telephone, email, roleId)
VALUES("Luboslav Ivanov Ivanov", "Sofia, Bulgaria", "0899994453", "lubo.ivanov96@gmail.com", 1),
		("Martin Georgiev Turnev", "Sofia, Bulgaria", "0873425735", "martin4oo97@gmail.com", 2),
		("Dimitur Georgiev Andreev", "Sofia, Bulgaria", "0899994453", "d.andreev@gmail.com", 2),
		("Simona Mitkova Grigorova", "Sofia, Bulgaria", "0895747313", "simonka97@gmail.com", 2),
		("Magdalena Grigorova Spasova ", "Sofia, Bulgaria", "0842743413", "magiToo97@gmail.com", 2),
		("Kamlia Jikova Doncheva ", "Sofia, Bulgaria", "0894237313", "kamitoo97@gmail.com", 2),
		("Emilia Trifonova Ivanova", "Sofia, Bulgaria", "0899994450", "emi_t@gmail.com", 2),
		("Ivan Bojidarov Ivanov", "Sofia, Bulgaria", "0898591498", "farma@gmail.com", 2);

INSERT INTO users_events(userId, eventId)
VALUES(1,1), (2,1), (3,1),
		(1,2), (2,2), (3,2), (4,2), (5,2), (6,2),
		(1, 3), (2, 3), (3, 3), (4, 3),
		(1,4), (2,4), (3,4), (7,4), (8,4);


###SelectExamples###

##List all people with family name Ivanov## N:2

SELECT names AS Names, address AS Address, telephone as Phone, email AS Email
FROM users
where names LIKE "% Ivanov%";

##List how many participant were registered to every event## N:3

SELECT event_subject AS Event_Subject, address AS Address, event_data AS Data, event_time as Time, COUNT(*) as Participants
FROM users_events
INNER JOIN events ON users_events.eventId = events.id
GROUP BY eventId
ORDER BY event_data, event_time;

##List N:4

SELECT event_subject as Event_Subject, names AS Participants, event_data as Data
FROM users_events
INNER JOIN users ON users_events.userId = users.id
RIGHT OUTER JOIN events ON users_events.eventId = events.id
ORDER BY COUNT(*);




##List the people how will be on the airport when its time to go to USA.