exambox schema

CREATE TABLE role (
	id INTEGER NOT NULL,
	name VARCHAR(80),
	description VARCHAR(255),
	PRIMARY KEY (id),
	UNIQUE (name)
)

CREATE TABLE degree (
	id INTEGER NOT NULL,
	name VARCHAR(50) NOT NULL,
	en_name VARCHAR(50),
	es_acronym VARCHAR(50),
	en_acronym VARCHAR(50),
	PRIMARY KEY (id),
	UNIQUE (name),
	UNIQUE (en_name),
	UNIQUE (es_acronym),
	UNIQUE (en_acronym)
)

CREATE TABLE tag (
	id INTEGER NOT NULL,
	name VARCHAR(50) NOT NULL,
	en_name VARCHAR(50),
	PRIMARY KEY (id)
)

CREATE TABLE user (
	id INTEGER NOT NULL,
	email VARCHAR(25),
	password VARCHAR(255),
	active BOOLEAN,
	confirmed_at DATETIME,
	remaining_invites INTEGER,
	PRIMARY KEY (id),
	UNIQUE (email),
	CHECK (active IN (0, 1))
)

CREATE TABLE roles_users (
	user_id INTEGER,
	role_id INTEGER,
	FOREIGN KEY(user_id) REFERENCES user (id),
	FOREIGN KEY(role_id) REFERENCES role (id)
)

CREATE TABLE bucket (
	id INTEGER NOT NULL,
	name VARCHAR(80),
	description VARCHAR(255),
	user_id INTEGER,
	PRIMARY KEY (id),
	UNIQUE (name),
	FOREIGN KEY(user_id) REFERENCES user (id)
)

CREATE TABLE invitation (
	id INTEGER NOT NULL,
	from_user_id INTEGER,
	to_user_id INTEGER,
	token VARCHAR(50),
	date DATETIME,
	PRIMARY KEY (id),
	FOREIGN KEY(from_user_id) REFERENCES user (id),
	FOREIGN KEY(to_user_id) REFERENCES user (id)
)

CREATE TABLE subject (
	id INTEGER NOT NULL,
	name VARCHAR(50) NOT NULL,
	year INTEGER,
	en_name VARCHAR(50),
	es_acronym VARCHAR(10),
	en_acronym VARCHAR(10),
	degree_id INTEGER,
	PRIMARY KEY (id),
	UNIQUE (name),
	UNIQUE (en_name),
	UNIQUE (es_acronym),
	UNIQUE (en_acronym),
	FOREIGN KEY(degree_id) REFERENCES degree (id)
)

CREATE TABLE bug (
	id INTEGER NOT NULL,
	description VARCHAR(1500) NOT NULL,
	user_id INTEGER,
	PRIMARY KEY (id),
	FOREIGN KEY(user_id) REFERENCES user (id)
)


CREATE TABLE file (
	id INTEGER NOT NULL,
	name VARCHAR(80) NOT NULL,
	path VARCHAR(120),
	date DATETIME,
	verified BOOLEAN,
	subject_id INTEGER,
	user_id INTEGER,
	PRIMARY KEY (id),
	UNIQUE (name),
	UNIQUE (path),
	CHECK (verified IN (0, 1)),
	FOREIGN KEY(subject_id) REFERENCES subject (id),
	FOREIGN KEY(user_id) REFERENCES user (id)
)

CREATE TABLE bucket_files (
	bucket_id INTEGER,
	file_id INTEGER,
	FOREIGN KEY(bucket_id) REFERENCES bucket (id),
	FOREIGN KEY(file_id) REFERENCES file (id)
)

CREATE TABLE tags (
	tag_id INTEGER,
	file_id INTEGER,
	FOREIGN KEY(tag_id) REFERENCES tag (id),
	FOREIGN KEY(file_id) REFERENCES file (id)
)