CREATE TABLE user (
	id INTEGER PRIMARY KEY,
	username TEXT NOT NULL,
	role TEXT NOT NULL,
	hash TEXT NOT NULL,
	email_addr TEXT,
	desc TEXT,
	creation_date TEXT NOT NULL,
	last_login TEXT,
	first_name TEXT,
	surname TEXT NOT NULL,
	latitude DOUBLE PRECISION,
	longitude DOUBLE PRECISION,
	active BOOLEAN NOT NULL DEFAULT true
);

CREATE TABLE role (
	role TEXT NOT NULL,
	level INTEGER NOT NULL,
	PRIMARY KEY (role, level)
);

CREATE TABLE pending_registration (
	code TEXT,
	username TEXT,
	role TEXT,
	hash TEXT,
	email_addr TEXT,
	desc TEXT,
	creation_date TEXT
);

CREATE TABLE tag (
	id INTEGER PRIMARY KEY,
	name TEXT NOT NULL
);

CREATE TABLE followed_tag (
	user_id INTEGER REFERENCES user (id) ON UPDATE CASCADE ON DELETE CASCADE,
	tag_id INTEGER REFERENCES tag (id) ON UPDATE CASCADE ON DELETE RESTRICT,
	PRIMARY KEY (user_id, tag_id)
);

CREATE TABLE event_time (
	id INTEGER PRIMARY KEY,
	event_id INTEGER NOT NULL REFERENCES event (id) ON UPDATE CASCADE ON DELETE CASCADE,
	start_time INTEGER NOT NULL,
	end_time INTEGER NOT NULL,
	timezone TEXT NOT NULL,
	cancelled BOOLEAN NOT NULL DEFAULT false
);

CREATE TABLE event (
	id INTEGER PRIMARY KEY,
	name TEXT NOT NULL,
	location TEXT,
	latitude DOUBLE PRECISION,
	longitude DOUBLE PRECISION,
	description TEXT,
	cancelled BOOLEAN NOT NULL DEFAULT false
);

CREATE TABLE event_tag (
	event_id INTEGER REFERENCES event (id) ON UPDATE CASCADE ON DELETE CASCADE,
	tag_id INTEGER REFERENCES tag (id) ON UPDATE CASCADE ON DELETE RESTRICT,
	PRIMARY KEY (event_id, tag_id)
)

CREATE TABLE recent_location (
	user_id INTEGER NOT NULL REFERENCES user (id) ON UPDATE CASCADE ON DELETE CASCADE,
	name TEXT NOT NULL,
	latitude DOUBLE PRECISION,
	longitude DOUBLE PRECISION
);

CREATE TABLE organizer (
	id INTEGER PRIMARY KEY,
	name TEXT NOT NULL,
	description TEXT
);

CREATE TABLE organizer_role (
	user_id INTEGER REFERENCES user (id) ON UPDATE CASCADE ON DELETE CASCADE,
	organizer_id INTEGER REFERENCES organizer (id) ON UPDATE CASCADE ON DELETE CASCADE,
	role INTEGER NOT NULL DEFAULT 0,
	PRIMARY KEY (user_id, organizer_id)
);
