-- Creacio de la base de dades.
DROP SCHEMA IF EXISTS scp2;
CREATE SCHEMA scp2;
USE scp2;

-- Taula per guardar els rols.
-- Inicialment n'hi haurá 3: professor, alumne i administrador
CREATE TABLE role (
	role_id INT NOT NULL AUTO_INCREMENT,
	name VARCHAR(255) NOT NULL,
	webFriendlyName VARCHAR(255) NOT NULL,
	PRIMARY KEY (role_id)
);

-- Taula per guardar els usuaris
CREATE TABLE user (
	username VARCHAR(255),
	password VARCHAR(255) NOT NULL,
	role INT NOT NULL,
	first_name VARCHAR(255) NOT NULL,
	last_name VARCHAR(255) NOT NULL,
	PRIMARY KEY (username),
	FOREIGN KEY (role) REFERENCES role(role_id)
);

-- Taula per guardar les assignatures
CREATE TABLE subject (
	subject_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
	title VARCHAR(255),
	PRIMARY KEY (subject_id)
);

CREATE TABLE languaje (
	languaje_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
	name VARCHAR(255) NOT NULL,
	file_extension VARCHAR(255) NOT NULL,
	compiled_interpreted BOOLEAN NOT NULL,
	compile_instruction VARCHAR(25),
	executor_program VARCHAR(25),
	need_name_file BOOLEAN NOT NULL,
	PRIMARY KEY (languaje_id)
);

-- Taula per guardar les practiques
CREATE TABLE task (
	task_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
	title VARCHAR(255) NOT NULL,
	start_date DATE NOT NULL,
	end_date DATE NOT NULL,
	description TEXT NOT NULL,
	owner VARCHAR(255) NOT NULL,
	compiler_arguments VARCHAR(1024) NOT NULL,
	subject_id BIGINT UNSIGNED NOT NULL REFERENCES subject(subject_id) ON DELETE cascade ON UPDATE cascade,
	languaje_id BIGINT UNSIGNED NOT NULL REFERENCES languaje(languaje_id) ON DELETE cascade ON UPDATE cascade,
	has_code_teacher BOOLEAN,
	name_file VARCHAR(25),
	
    has_makefile BOOLEAN default FALSE,
    valid_files INT default 1,
	PRIMARY KEY (task_id),
	FOREIGN KEY (owner) REFERENCES user(username)
);

-- Taula per guardar els fitxers de les practiques
CREATE TABLE task_file (
    task_file_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    task_id BIGINT UNSIGNED NOT NULL,
	filename VARCHAR(255) NOT NULL,
	file_path TEXT,
    shared BOOLEAN default FALSE,
	PRIMARY KEY (task_file_id),
	FOREIGN KEY (task_id) REFERENCES task(task_id) ON DELETE cascade ON UPDATE cascade
);

-- Taula per guardar les entregues
CREATE TABLE submission (
	submission_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
	task_id BIGINT UNSIGNED NOT NULL,
	mark INT NOT NULL,
	user VARCHAR(255) NOT NULL,
	PRIMARY KEY (submission_id),
	FOREIGN KEY (task_id) REFERENCES task(task_id),
	FOREIGN KEY (user) REFERENCES user(username)
);

-- Taula per guardar els fitxers de les entregues
CREATE TABLE submission_file (
    submission_file_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    submission_id BIGINT UNSIGNED NOT NULL,
	filename VARCHAR(255) NOT NULL,
	file_path TEXT,
	PRIMARY KEY (submission_file_id),
	FOREIGN KEY (submission_id) REFERENCES submission(submission_id) ON DELETE cascade ON UPDATE cascade
);


-- Taula per guardar quins alumnes pertanyen a quines assignatures i poder saber quines práctiques han d'entregar

CREATE TABLE user_subject (
	username VARCHAR(255) NOT NULL REFERENCES user(username),
	subject_id BIGINT UNSIGNED NOT NULL REFERENCES subject(subject_id),
	PRIMARY KEY (username, subject_id)
);

CREATE TABLE test (
	test_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
	task_id BIGINT UNSIGNED NOT NULL REFERENCES task(task_id) ON DELETE cascade ON UPDATE cascade,
	arguments VARCHAR(1024),
	output TEXT NOT NULL,
	PRIMARY KEY (test_id)
);

CREATE TABLE testresult(
	test_result_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
	submission_id BIGINT UNSIGNED NOT NULL,   
	arguments VARCHAR(1024),
	output TEXT NOT NULL,
	excepted_output TEXT,
	style VARCHAR(1024),
	PRIMARY KEY (test_result_id),
	FOREIGN KEY (submission_id) REFERENCES submission(submission_id) ON DELETE cascade ON UPDATE cascade
);

insert into languaje (languaje_id, name, file_extension,compiled_interpreted,compile_instruction,executor_program,need_name_file) values (1, "C", ".c",1,"gcc", NULL,0);
insert into languaje (languaje_id, name, file_extension,compiled_interpreted,compile_instruction,executor_program,need_name_file) values (2, "JAVA", ".java",1,"javac", "java",1);
insert into languaje (languaje_id, name, file_extension,compiled_interpreted,compile_instruction,executor_program,need_name_file) values (3, "PYTHON", ".py",1,"python","python",0);
-- Dades per defecte: 3 rols i 1 usuari administrador
insert into role (name, webFriendlyName) values ('ROLE_TEACHER', 'Teacher');
insert into role (name, webFriendlyName) values ('ROLE_STUDENT', 'Student');
insert into role (name, webFriendlyName) values ('ROLE_ADMIN', 'Administrator');

-- Creem els usuris inicials
insert into user (username, role, first_name, last_name, password) values ('admin', 3, 'admin', 'admin', '1234');

insert into user (username, role, first_name, last_name, password) values ('teacher', 1, 'teacher', 'teacher', '1234');
insert into user (username, role, first_name, last_name, password) values ('student', 2, 'student', 'student', '1234');

-- Creem una assignatura i li assignem dos usuaris
insert into subject (title) values ('subject');

insert into user_subject (username, subject_id) values ('teacher', 1);
insert into user_subject (username, subject_id) values ('student', 1);
