create database EsamiLIA;

create table esaminatore (
	id_esaminatore 	serial not null,
	username 	varchar(20) not null,
	password 	char(64) not null, --MD5
	cognome 	varchar(30) not null,
	nome 		varchar(20) not null,
	email 		varchar(64) not null,
	groups 		char(1) not null,
	
	constraint IDesaminatore primary key (id_esaminatore),
	constraint IDesaminatore_1 unique (username),
	constraint IDesaminatore_2 unique (email),
	
	constraint mail check (email like '%@%.%')
);

create table directory_doc (
	id_dir 		serial not null,
	nome_dir 	varchar(30) not null,
	
	constraint IDdirectory_doc primary key (id_dir),
	constraint IDdirectory_doc_1 unique (nome_dir)
);

create table esaminatore_dir (
	id_esaminatore int references esaminatore on delete cascade,
	id_directory int references directory_doc on delete cascade,
	
	constraint IDesaminatore_dir primary key (id_esaminatore, id_directory)
);

create table esame (
	id_esame serial not null,
	id_docente int not null references esaminatore on delete no action,
	id_creatore int references esaminatore,
	data_esame date not null,
	turno int not null,
	turni_tot int not null,
	domande char(1) not null,
	gruppi int not null,
	stato_esame int, -- 0=esame creato, 1=esame iniziato, 2=esame terminato
	nome_esame varchar(255), --del tutto superfluo, una cosa del tipo "Fondamenti di Informatica L-B"
	orario varchar(5), --l'orario del turno (utile solo per la visualizzazione del compito, inutile per tutto il resto)
	gruppo_studenti varchar(40) --determina il gruppo di studenti che puņ essere "studenti A-K", oppure solo "A-K", o anche "Informatici"
	
	constraint IDesame primary key (id_esame),
	constraint IDesame_1 unique (id_docente, data_esame, turno),
	
	constraint flag_domande check (domande in ('Y','N')),
	constraint valore_stato_esame check (stato_esame between 0 and 2)
);

create table permessi_ip (
	id_esame int not null references esame on delete cascade,
	ipaddress varchar(15) not null,
	
	constraint IDpermessi_ip primary key (id_esame, ipaddress),
	
	constraint struttura_ip check (ipaddress like '%.%.%.%')
);