DROP DATABASE IF EXISTS service_community;
CREATE DATABASE service_community CHARACTER SET 'utf8';
USE service_community;

-- Table users

CREATE TABLE users (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	mail VARCHAR(30) NOT NULL,
	passweird VARCHAR(20) NOT NULL,
	nickname VARCHAR(20) NOT NULL,
	adress VARCHAR(60),
	zip_code VARCHAR(15),
	city VARCHAR(40),
	country VARCHAR(30),
	cellphone VARCHAR(20),
	phone VARCHAR(20),
	date_inscription DATE,
	PRIMARY KEY (id)
)
ENGINE = INNODB;

-- Table services

CREATE TABLE services (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	id_user_from INT UNSIGNED NOT NULL,
	name VARCHAR(20) NOT NULL,
	description TEXT NOT NULL,
	adress VARCHAR(30) NOT NULL,
	zip_code VARCHAR(15) NOT NULL,
	city VARCHAR(40) NOT NULL,
	country VARCHAR(30) NOT NULL,
	date_time DATETIME NOT NULL,
	additional_information TEXT,
	PRIMARY KEY (id)
)
ENGINE = INNODB;

-- Table services-users

CREATE TABLE services_users (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	id_service INT UNSIGNED NOT NULL,
	id_user INT UNSIGNED NOT NULL,
	date_time_inscription DATETIME NOT NULL,
	PRIMARY KEY (id)
)
ENGINE = INNODB;

-- Table messages

CREATE TABLE messages (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	id_user_from INT UNSIGNED NOT NULL,
	id_user_to INT UNSIGNED NOT NULL,
	content TEXT NOT NULL,
	date_time_sended DATETIME NOT NULL,
	PRIMARY KEY (id)
)
ENGINE = INNODB;

-- Users creation

INSERT INTO users (mail, passweird, nickname)
VALUE ('testytasty@toast.com', 'apassweird', 'Tasty'),
	('unmail@free.ru', 'aweirdpassweird', 'Toast'),
	('thetoast@toaster.fr', '1234', 'John'),
	('amail@mail.com', '4256', 'Banana'),
	('thisisnotamail@mail.com', '6969', 'Caraba'),
	('banana@gree.com', '666', 'Veratisen'),
	('stupefix@snape.avada', '934', 'Giiny'),
	('alohomora@severus.keda', 'crucio', 'Lockhart'),
	('potterXdraco@tyrion.com', '0000', 'Sprout'),
	('slughornXjedusor@granger.ron', 'mangetesmangesmorts', 'Dumblehumbledore');

-- Messages creation

INSERT INTO messages (id_user_from, id_user_to, content, date_time_sended)
VALUE (1, 2, 'This is a message.', '2016-10-06 15:55:45'),
	(4, 3, 'This is a message.', '2016-10-06 15:56:45'),
	(9, 2, 'This is a message.', '2016-10-06 15:57:45'),
	(5, 9, 'This is a message.', '2016-10-06 15:58:45'),
	(1, 4, 'This is a message.', '2016-10-06 15:54:45'),
	(8, 7, 'This is a message.', '2016-10-06 15:30:45'),
	(3, 2, 'This is a message.', '2016-10-06 16:54:45'),
	(6, 4, 'This is a message.', '2016-10-06 13:54:45'),
	(5, 9, 'This is a message.', '2016-10-06 12:54:45'),
	(3, 7, 'This is a message.', '2016-10-06 11:54:45'),
	(9, 6, 'This is a message.', '2016-10-06 09:11:45'),
	(9, 1, 'This is a message.', '2016-10-06 01:54:00'),
	(6, 4, 'This is a message.', '2016-10-06 13:44:45'),
	(3, 7, 'This is a message.', '2016-10-06 19:54:45'),
	(2, 5, 'This is a message.', '2016-10-06 10:15:45'),
	(3, 4, 'This is a message.', '2016-10-06 17:33:45'),
	(8, 5, 'This is a message.', '2016-10-06 13:52:45'),
	(4, 9, 'This is a message.', '2016-10-06 14:54:45'),
	(2, 4, 'This is a message.', '2016-10-06 16:50:45'),
	(3, 7, 'This is a message.', '2016-10-06 18:53:45'),
	(4, 9, 'This is a message.', '2016-10-06 19:13:45'),
	(3, 10, 'This is a message.', '2016-10-06 15:34:45'),
	(1, 10, 'This is a message.', '2016-10-06 13:56:45'),
	(10, 1, 'This is a message.', '2016-10-06 14:49:45'),
	(10, 3, 'This is a message.', '2016-10-06 20:01:45');

-- Update of profiles

UPDATE users
SET adress = '4 Privet drive', zip_code = '93666', city = 'Little Whinging', country = 'Sweden', cellphone = '06666666', phone = '011111111', date_inscription= '2015-05-15'
WHERE id = 1;
UPDATE users
SET adress = '4 Privet drive', zip_code = '93666', city = 'Little Whinging', country = 'Sweden', cellphone = '06666666', phone = '011111111', date_inscription= '2015-05-15'
WHERE id = 2;
UPDATE users
SET adress = '4 Privet drive', zip_code = '93666', city = 'Little Whinging', country = 'Sweden', cellphone = '06666666', phone = '011111111', date_inscription= '2015-05-15'
WHERE id = 3;
UPDATE users
SET adress = '4 Privet drive', zip_code = '93666', city = 'Little Whinging', country = 'Sweden', cellphone = '06666666', phone = '011111111', date_inscription= '2015-05-15'
WHERE id = 4;
UPDATE users
SET adress = '4 Privet drive', zip_code = '93666', city = 'Little Whinging', country = 'Sweden', cellphone = '06666666', phone = '011111111', date_inscription= '2015-05-15'
WHERE id = 5;
UPDATE users
SET adress = '4 Privet drive', zip_code = '93666', city = 'Little Whinging', country = 'Sweden', cellphone = '06666666', phone = '011111111', date_inscription= '2015-05-15'
WHERE id = 6;
UPDATE users
SET adress = '4 Privet drive', zip_code = '93666', city = 'Little Whinging', country = 'Sweden', cellphone = '06666666', phone = '011111111', date_inscription= '2015-05-15'
WHERE id = 7;
UPDATE users
SET adress = '4 Privet drive', zip_code = '93666', city = 'Little Whinging', country = 'Sweden', cellphone = '06666666', phone = '011111111', date_inscription= '2015-05-15'
WHERE id = 8;
UPDATE users
SET adress = '4 Privet drive', zip_code = '93666', city = 'Little Whinging', country = 'Sweden', cellphone = '06666666', phone = '011111111', date_inscription= '2015-05-15'
WHERE id = 9;
UPDATE users
SET adress = '4 Privet drive', zip_code = '93666', city = 'Little Whinging', country = 'Sweden', cellphone = '06666666', phone = '011111111', date_inscription= '2015-05-15'
WHERE id = 10;

-- Display all conversations from one user (here 10)

SELECT * FROM messages
WHERE id_user_from = 10
OR id_user_to = 10
ORDER BY date_time_sended DESC;

-- Display all messages between two users

SELECT * FROM messages
WHERE (id_user_from = 10 AND id_user_to = 3)
OR (id_user_from = 3 AND id_user_to = 10);