--drop schema if exists project cascade;
--create schema project;

drop table vozaci_ima_kategorii;
drop table rezervacii_ima_za_vozila;
drop table vozaci_e_na_klienti;
drop table Avtomobili;
drop table Motori;
drop table Kombinja;
drop table MSteti;
drop table Parkirani;
drop table Servisi;
drop table ServisKompBr;
drop table ServisKomp;
drop table Vozila;
drop table Kategorii;
drop table IzdavSaloniBr;
drop table Lokacii;
drop table FizickiLBr;
drop table FizickiL;
drop table PravniL;
drop table Rezervacii;
drop table Naplati;
drop table Smetki;
drop table Dogovori;
drop table Klienti;
drop table VraboteniBr;
drop table Prodavachi;
drop table Menadzeri;
drop table Vraboteni;
drop table IzdavSaloni;
drop table VozaciBr;
drop table Vozaci;

-------------------------------

create table Kategorii (
 	idKat serial primary key,
 	tip varchar(3)
);

create table Vozila (
 	idVozi integer primary key,
 	idKat integer,
 	model varchar(255),
 	marka varchar(255),
 	regTab varchar(255),
 	cenaVozi decimal,
 	constraint fk_idKat foreign key (idKat) references Kategorii (idKat)
);

create table Avtomobili (
 	idVoziA integer primary key,
 	brSedista integer,
	constraint fk_idVozi foreign key (idVoziA) references Vozila(idVozi)
);

create table Motori (
 	idVoziM integer primary key, 
 	kubikaza integer,
	constraint fk_idVozi foreign key (idVoziM) references Vozila(idVozi)
);

create table Kombinja (
 	idVoziK integer primary key,
 	brSedista integer,
 	nosivost integer,
	constraint fk_idVozi foreign key (idVoziK) references Vozila(idVozi)
);

create table IzdavSaloni (
 	idIS serial primary key
);

create table IzdavSaloniBr (
 	idIS integer,
	telBrIs char(9),
	constraint pk_IzdavSaloniBr primary key (idIs, telBrIs),
	constraint fk_IzdavSaloniBr foreign key (idIS) references IzdavSaloni(idIS)
);

create table Klienti (
 	EMBGK char(13) primary key,
	imeK varchar(255),
 	adresaK varchar(255),
 	primiumK boolean
);

create table FizickiL (
 	EMBGKFL char(13) primary key,
 	constraint fk_FizickiL foreign key (EMBGKFL) references Klienti (EMBGK)
);

create table FizickiLBr (
 	EMBGKFL char(13),
 	telBrFL char(9),
 	constraint pk_FizickiLBr primary key (EMBGKFL, telBrFL),
	constraint fk_FizickiLBr foreign key (EMBGKFL) references FizickiL(EMBGKFL)
);

create table PravniL (
 	EMBGKPL char(13) primary key,
 	direktor varchar(255),
 	constraint fk_PravniL foreign key (EMBGKPL) references Klienti (EMBGK)
);

create table Dogovori (
 	idDog serial primary key,
 	EMBGK char(13) references Klienti(EMBGK),
 	idIS integer references IzdavSaloni(idIS),
 	cenaDog decimal,
 	sklucenNa date,
 	vaziDo date
);

create table Lokacii (
 	idLok serial primary key,
 	idIS integer references IzdavSaloni(idIS),
 	adresaLok varchar(255),
 	gradLok varchar(255)
);

create table Vozaci (
 	EMBGVoza char(13) primary key, 
	imeVoza varchar(255),
 	adresaVoza varchar(255),
 	vozackaD char(8)
);

create table VozaciBr (
 	EMBGVoza char(13) references Vozaci(EMBGVoza),
 	telBrVoza char(9),
	constraint pk_VozaciBr primary key (EMBGVoza, telBrVoza)
);

create table MSteti (
 	idMS serial primary key,
 	idVozi integer references Vozila(idVozi),
 	EMBGVoza char(13) references Vozaci(EMBGVoza),
 	cenaMS decimal
);

create table Vraboteni (
 	EMBGV char(13) primary key,
 	idIS integer references IzdavSaloni(idIS),
 	imeV varchar(255)
);

create table VraboteniBr (
 	EMBGVBr char(13) references Vraboteni(EMBGV),
 	telBrV char(9),
	constraint pk_VraboteniBr primary key (EMBGVBr, telBrV)
);

create table Menadzeri (
 	EMBGVM char(13) primary key,
 	constraint fk_Menadzeri foreign key (EMBGVM) references Vraboteni(EMBGV)
);
 
create table Prodavachi (
 	EMBGVP char(13) primary key,
 	EMBGVM char(13) references Menadzeri(EMBGVM),
 	constraint fk_Prodavachi foreign key (EMBGVP) references Vraboteni(EMBGV)
);

create table Rezervacii (
 	idRez serial primary key,
 	EMBGVoza char(13) references Vozaci(EMBGVoza),
 	idDog integer references Dogovori(idDog),
 	naDatumRez date,
 	doDatumRez date
);

create table ServisKomp (
 	brSK integer primary key,
 	imeSK varchar(255),
	adresaSK varchar(255)
);

create table ServisKompBr (
 	brSK integer references ServisKomp(brSK), 
	telBrSK char(9),
 	constraint pk_ServisKompBr primary key (brSK, telBrSK)
);

create table Servisi (
 	brS serial primary key,
 	brSk integer references ServisKomp(brSK),
 	idVozi integer references Vozila(idVozi),
 	cenaS decimal,
 	naDatumS date
);

create table Smetki (
 	brSm serial primary key,
 	idDog integer references Dogovori(idDog), 
	cenaSm decimal,
 	datumSm date
);

create table Parkirani (
 	od date,
 	doD date,
	idVozi integer references Vozila(idVozi),
 	idIS integer references IzdavSaloni(idIS),
 	constraint pk_Parkirani primary key (od, idVozi, idIS)
);

create table Naplati (
 	idNap serial,
 	brSM integer references Smetki(brSm),
 	EMBGVP char(13) references Prodavachi(EMBGVP),
 	kesh boolean,
	kartica boolean,
	datumNap date,
 	constraint pk_Naplati primary key (idNap, brSM)
);

create table rezervacii_ima_za_vozila (
 	idRez integer references Rezervacii(idRez),
 	idVozi integer references Vozila (idVozi),
 	constraint pk_rez_ima_za_vozi primary key (idRez, IdVozi)
); 

create table vozaci_e_na_klienti (
 	EMBGVoza char(13) references Vozaci(EMBGVoza),
 	EMBGK char(13) references Klienti(EMBGK),
 	constraint pk_voz_e_na_klienti primary key (EMBGVoza, EMBGK)
);

create table vozaci_ima_kategorii (
 	EMBGVoza char(13) references Vozaci(EMBGVoza),
 	idKat integer references Kategorii(idKat),
 	constraint pk_voz_ima_kat primary key (EMBGVoza, idKat)
);

alter table izdavsaloni
add imeSal varchar(255);

alter table fizickilbr
alter column telbrfl type varchar(20);

alter table izdavsalonibr
alter column telbris type varchar(20);

alter table serviskompbr
alter column telbrsk type varchar(20);

alter table vrabotenibr
alter column telbrv type varchar(20);

alter table vozacibr
alter column telbrvoza type varchar(20);