create table project.admini(
	id_admin integer primary key,
	ime varchar(50) not null,
	prezime varchar(50) not null,
	email varchar(50) not null,
	password varchar(300) not null
);

create table project.nastavnici(
	id_nastavnik serial primary key,
	ime varchar(50) not null,
	prezime varchar(50) not null,
	opis varchar(500) not null,
	email varchar(50) not null,
	password varchar(300) not null,
	telefonski_broj varchar(15),
	enabled bool,
	id_admin integer not null,
	constraint fk_admin foreign key(id_admin) references project.admini(id_admin)
  	on delete cascade on update cascade
);

create table project.kvalifikacii (
	id_kvalifikacija serial primary key,
	dokument text,
	id_nastavnik integer not null,
	id_admin integer not null,
	constraint fk_admin foreign key(id_admin) references project.admini(id_admin)
  	on delete cascade on update cascade,
	constraint fk_nastavnik foreign key (id_nastavnik) references project.nastavnici(id_nastavnik)
  	on delete cascade on update cascade
);

create table project.predmeti (
	id_predmet serial primary key,
	ime varchar(100) not null,
	id_admin integer not null,
	constraint fk_admin foreign key(id_admin) references project.admini(id_admin)
  	on delete cascade on update cascade
);

create table project.ucenici (
	id_ucenik serial primary key,
	opis varchar(500),
	ime varchar(50) not null,
	prezime varchar(50) not null,
	email varchar(50) not null,
	password varchar(300) not null,
	telefonski_broj varchar(15)
);

create table project.casovi (
	id_cas serial primary key,
	vreme_pocetok timestamp,
	tema varchar(100) not null,
	id_nastavnik integer,
	id_predmet integer,
	constraint fk_nastavnik foreign key (id_nastavnik) references project.nastavnici(id_nastavnik)
  	on delete cascade on update cascade,
	constraint fk_predmet foreign key (id_predmet) references project.predmeti(id_predmet)
  	on delete cascade on update cascade
);

create table project.zainteresiran_za (
	id_predmet integer,
	id_ucenik integer,
	datum date,
	constraint pk_zainteresiran_za primary key (id_ucenik,id_predmet),
	constraint fk_predmet foreign key (id_predmet) references project.predmeti(id_predmet)
  	on delete cascade on update cascade,
	constraint fk_ucenik foreign key (id_ucenik) references project.ucenici(id_ucenik)
  	on delete cascade on update cascade
);

create table project.domasni_zadaci (
	id_domasno serial primary key,
	opis varchar(500),
	id_nastavnik integer not null,
	id_cas integer not null,
	constraint fk_cas foreign key (id_cas) references project.casovi(id_cas)
  	on delete cascade on update cascade,
	constraint fk_nastavnik foreign key (id_nastavnik) references project.nastavnici(id_nastavnik)
  	on delete cascade on update cascade
);


create table project.ucenici_domasni (
	id_ucenik integer,
	id_domasno integer,
	dali_zavrsena bool,
	constraint pk_ucenik_domasno primary key (id_ucenik,id_domasno),
	constraint fk_domasno foreign key (id_domasno) references project.domasni_zadaci(id_domasno)
  	on delete cascade on update cascade,
	constraint fk_ucenik foreign key (id_ucenik) references project.ucenici(id_ucenik)
  	on delete cascade on update cascade
);

create table project.plakjanja (
	id_plakjanja serial primary key,
	iznos integer not null,
	id_nastavnik integer not null,
	constraint fk_nastavnik foreign key (id_nastavnik) references project.nastavnici(id_nastavnik)
);

create table project.slusanje (
	id_cas integer,
	id_slusanje integer,
	id_plakjanja integer,
	id_ucenik integer,
	plateno boolean,
	constraint pk_slusanje primary key (id_cas, id_slusanje),
	constraint fk_cas foreign key(id_cas) references project.casovi(id_cas)
  	on delete cascade on update cascade,
	constraint fk_plakjanje foreign key (id_plakjanja) references project.plakjanja(id_plakjanja)
  	on delete cascade on update cascade,
	constraint fk_ucenik foreign key (id_ucenik) references project.ucenici(id_ucenik)
  on delete cascade on update cascade
);


create table project.predava_predmet (
	id_nastavnik integer,
	id_predmet integer,
	opis varchar(500) not null,
	
	constraint pk_predava_predmet primary key (id_nastavnik, id_predmet),
	constraint fk_nastavnik foreign key (id_nastavnik) references project.nastavnici(id_nastavnik)
  	on delete cascade on update cascade,
	constraint fk_predmet foreign key (id_predmet) references project.predmeti(id_predmet)
  	on delete cascade on update cascade
);

create table project.predava_na (
	id_nastavnik integer,
	id_ucenik integer,
	cena_po_cas integer not null,
	broj_casovi_po_dogovor integer not null,
	rejting float,
	komentar varchar(500),
	hasrated boolean,
	constraint pk_predava_na primary key (id_nastavnik, id_ucenik),
	constraint fk_nastavnik foreign key (id_nastavnik) references project.nastavnici(id_nastavnik)
  	on delete cascade on update cascade,
	constraint fk_ucenik foreign key (id_ucenik) references project.ucenici(id_ucenik)
  	on delete cascade on update cascade
);

-- brisenje tabeli
drop table project.predava_na ;
drop table project.predava_predmet ;
drop table project.slusanje ;
drop table project.plakjanja ;
drop table project.ucenici_domasni ;
drop table project.zainteresiran_za ;
drop table project.ucenici ;
drop table project.domasni_zadaci ;
drop table project.casovi ;
drop table project.kvalifikacii ;
drop table project.nastavnici ;
drop table project.predmeti ;
drop table project.admini ;


