DROP TABLE IF EXISTS E_Users;
CREATE TABLE E_Users (
	username		VARCHAR(50) NOT NULL,
	permission		ENUM("member","event_organiser","moderator","administrator") NOT NULL,
	PRIMARY KEY(username)
);
	
DROP TABLE IF EXISTS E_Accounts;
CREATE TABLE E_Accounts (
	username		VARCHAR(50) NOT NULL,
	password		VARCHAR(20) NOT NULL,
	firstname		VARCHAR(50) NOT NULL,
	lastname		VARCHAR(100) NOT NULL,
	telephone		VARCHAR(30),
	mobile			VARCHAR(30),
	emailaddress	VARCHAR(100) NOT NULL,
	homepage		VARCHAR(70),
	avatar			VARCHAR(70),
	description		TEXT,
	PRIMARY KEY(username),
	FOREIGN KEY(username) REFERENCES Users(username)
);

DROP TABLE IF EXISTS E_UserDetails;
CREATE TABLE E_UserDetails (
	detailsID		VARCHAR(50) NOT NULL,
	dateOfBirth 	DATE,
	sex				ENUM("male","female","other") NOT NULL,
	PRIMARY KEY(detailsID),
	FOREIGN KEY(detailsID) REFERENCES Users(username)
);
	
DROP TABLE IF EXISTS E_Details;
CREATE TABLE E_Details (
	detailsID		VARCHAR(50) NOT NULL,
	street			VARCHAR(100),
	nr				INTEGER,
	bus				VARCHAR(10),
	postalcode		INTEGER,
	city			VARCHAR(50),
	province		VARCHAR(50),
	country			VARCHAR(40),
	PRIMARY KEY(detailsID),
	FOREIGN KEY(detailsID) REFERENCES Users(username),	
	FOREIGN KEY(detailsID) REFERENCES Events(eventID)	
);

DROP TABLE IF EXISTS E_Preferences;
CREATE TABLE E_Preferences (
	preferenceID	VARCHAR(50) NOT NULL,
	keyword			VARCHAR(30) NOT NULL,
	FOREIGN KEY(preferenceID) REFERENCES Users(username),	
	FOREIGN KEY(preferenceID) REFERENCES Events(eventID)	
);

DROP TABLE IF EXISTS E_Events;
CREATE TABLE E_Events (
	eventID			VARCHAR(50) NOT NULL,
	name			VARCHAR(70) NOT NULL,
	author			VARCHAR(50) NOT NULL,
	PRIMARY KEY(eventID),
	FOREIGN KEY(author) REFERENCES Users(username)
);
	
DROP TABLE IF EXISTS E_EventDetails;
CREATE TABLE E_EventDetails (
	detailsID		VARCHAR(50) NOT NULL,
	startDate		DATETIME NOT NULL,
	endDate			DATETIME,
	PRIMARY KEY(detailsID),
	FOREIGN KEY(detailsID) REFERENCES Events(eventID)
);

DROP TABLE IF EXISTS E_Comments;
CREATE TABLE E_Comments (
	eventID			VARCHAR(50) NOT NULL,
	author			VARCHAR(50) NOT NULL,
	subject			VARCHAR(100) NOT NULL,
	date			DATETIME NOT NULL,
	content			TEXT NOT NULL,
	category		ENUM("offer ticket","want ticket","offer ride","want ride","other") NOT NULL,
	FOREIGN KEY(eventID) REFERENCES Events(eventID),
	FOREIGN KEY(author) REFERENCES Users(username)	
);
	
DROP TABLE IF EXISTS E_Messages;
CREATE TABLE E_Messages (
	eventID			VARCHAR(50),
	username		VARCHAR(50),
	FOREIGN KEY(eventID) REFERENCES Events(eventID),
	FOREIGN KEY(username) REFERENCES Users(username)	
);

DROP TABLE IF EXISTS E_Subscriptions;
CREATE TABLE E_Subscriptions (
	sender			VARCHAR(50),
	receiver		VARCHAR(50),
	subject			VARCHAR(100) NOT NULL,
	date			DATETIME NOT NULL,
	content			TEXT NOT NULL,
	FOREIGN KEY(sender) REFERENCES Users(username),
	FOREIGN KEY(receiver) REFERENCES Users(username)
);