
set search_path = project;



-- отстранување на функции од шема

drop function if exists fn_Proveri_data_na_otkrivanje_dijabeticar_pred_datum_na_ragjanje;
drop function if exists fn_validiraj_medikament_disjoint;
drop function if exists fn_Validiraj_datoteka_metapodatoci_disjoint;

-- отстранување на шемата

drop schema if exists project;
 


-- креирање на шемата

create schema project;

-- се поставува патека односно шема во која ќе се извршуваат командите

set search_path = project;





-- tip_dijabetes definition

-- Drop table



CREATE TABLE tip_dijabetes (
	id serial4 NOT NULL,
	ime varchar(256) NOT NULL,
	CONSTRAINT "PK_tip_dijabetes_id" PRIMARY KEY (id),
	CONSTRAINT "UNQ_Tip_dijabetes_ime" UNIQUE (ime)
);

-- tip_insulin definition

-- Drop table



CREATE TABLE tip_insulin (
	id serial4 NOT NULL,
	ime varchar(256) NOT NULL,
	CONSTRAINT "PK_Tip_insulin_id" PRIMARY KEY (id),
	CONSTRAINT "UNQ_Tip_insulin_ime" UNIQUE (ime)
);

-- nacin_prigotvuvanje_hrana definition

-- Drop table



CREATE TABLE nacin_prigotvuvanje_hrana (
	id serial4 NOT NULL,
	ime varchar(256) NOT NULL,
	CONSTRAINT "PK_Nacin_prigotvuvanje_hrana_id" PRIMARY KEY (id),
	CONSTRAINT "UNQ_Nacin_prigotvuvanje_hrana_ime" UNIQUE (ime)
);


-- Drop table



CREATE TABLE covek (
	id serial4 NOT NULL,
	embg char(13) NOT NULL,
	ime varchar(256) NOT NULL,
	prezime varchar(256) NOT NULL,
	pol bpchar(1) NOT NULL,
	datum_na_ragjanje date NOT NULL,
	
	CONSTRAINT "PK_covek_id" PRIMARY KEY (id),
	CONSTRAINT "UNQ_covek_embg" unique(embg),
	CONSTRAINT "CHK_covek_pol" CHECK ((pol = ANY (ARRAY['M'::bpchar, 'Z'::bpchar])))
);

-- doktor definition

-- Drop table



CREATE TABLE doktor (
	id int4 NOT NULL,
	CONSTRAINT "PK_doktor_id" PRIMARY KEY (id),
	CONSTRAINT "FK_doktor_id" FOREIGN KEY (id) REFERENCES covek(id)
);

-- dijabeticar definition

-- Drop table



CREATE TABLE dijabeticar (
	id int4 NOT NULL,
	data_na_otkrivanje date NOT NULL,
	tip_dijabetes_id int4 NOT NULL,
	CONSTRAINT "PK_dijabeticar_id" PRIMARY KEY (id),
	CONSTRAINT "FK_dijabeticar_id" FOREIGN KEY (id) REFERENCES covek(id),
	CONSTRAINT "FK_dijabeticar_tip_dijabetes_id" FOREIGN KEY (tip_dijabetes_id) REFERENCES tip_dijabetes(id)
);



-- datoteka_metapodatoci definition

-- Drop table



CREATE TABLE datoteka_metapodatoci (
	id serial4 NOT NULL,
	ime varchar(400) NOT NULL,
	hash varchar(400) NOT NULL,
	opis varchar(1000) NOT null default '',
	mime varchar(300) NOT NULL,
	tip_datoteka bpchar(1) NOT NULL,
	
	CONSTRAINT "PK_datoteka_metapodatoci_id" PRIMARY KEY (id),
	CONSTRAINT "UNQ_datoteka_metapodatoci_hash" UNIQUE (hash),
	CONSTRAINT "CHK_datoteka_metapodatoci_tip_datoteka" CHECK ((tip_datoteka = ANY (ARRAY['F'::bpchar, 'Z'::bpchar])))
);

-- faksimil definition

-- Drop table



CREATE TABLE faksimil (
	id int4 NOT NULL,
	doktor_id int4 NOT NULL,
	dijabeticar_id int4 NOT NULL,
	CONSTRAINT "PK_faksimil_id" PRIMARY KEY (id),
	CONSTRAINT "FK_dijabeticar_id" FOREIGN KEY (dijabeticar_id) REFERENCES dijabeticar(id),
	CONSTRAINT "FK_doktor_id" FOREIGN KEY (doktor_id) REFERENCES doktor(id),
	CONSTRAINT "FK_faksimil_id" FOREIGN KEY (id) REFERENCES datoteka_metapodatoci(id)
);

-- zdrastven_rezultal definition

-- Drop table



CREATE TABLE zdrastven_rezultat (
	id int4 NOT NULL,
	dijabeticar_id int4 NOT NULL,
	CONSTRAINT "PK_zdrastven_rezultat_id" PRIMARY KEY (id),
	CONSTRAINT "FK_zdrastven_rezultat_dijabeticar_id" FOREIGN KEY (dijabeticar_id) REFERENCES dijabeticar(id),
	CONSTRAINT "FK_zdrastven_rezultat_id" FOREIGN KEY (id) REFERENCES datoteka_metapodatoci(id)
);

-- medikament definition

-- Drop table



CREATE TABLE medikament (
	id serial4 NOT NULL,
	ime varchar(256) NOT NULL,
	proizvoditel varchar(256) NOT NULL,
	tip_medikament bpchar(1) NOT NULL,
	CONSTRAINT "CHK_Medikament_tip_medikament" CHECK ((tip_medikament = ANY (ARRAY['L'::bpchar, 'I'::bpchar]))),
	CONSTRAINT "PK_Medikament_id" PRIMARY KEY (id),
	constraint "UNQ_medikament_ime" unique(ime) 
);

-- lenti_merenje_shekjer definition

-- Drop table



CREATE TABLE lenti_merenje_shekjer (
	id int4 NOT NULL,
	CONSTRAINT "PK_Lenti_merenje_shekjer_id" PRIMARY KEY (id),
	CONSTRAINT "FK_Lenti_merenje_shekjer_id_Medikament_id" FOREIGN KEY (id) REFERENCES medikament(id)
);


-- insulin definition

-- Drop table



CREATE TABLE insulin (
	id int4 NOT NULL,
	tip_insulin_id int4 NULL,
	CONSTRAINT "PK_Insulin_id" PRIMARY KEY (id),
	CONSTRAINT "FK_Insulin_id_Medikament_id" FOREIGN KEY (id) REFERENCES medikament(id),
	CONSTRAINT "FK_Insulin_tip_insulin_id_Tip_insulin_id" FOREIGN KEY (tip_insulin_id) REFERENCES tip_insulin(id)
);

-- inventar definition

-- Drop table



CREATE TABLE inventar (
	dijabeticar_id int4 NOT NULL,
	medikament_id int4 NOT NULL,
	rok_na_traenje date NOT NULL,
	kolicina int4 NOT null check(kolicina > 0),
	CONSTRAINT "PK_inventar_id" PRIMARY KEY (dijabeticar_id, medikament_id, rok_na_traenje),
	CONSTRAINT "FK_dijabeticar_id" FOREIGN KEY (dijabeticar_id) REFERENCES dijabeticar(id),
	CONSTRAINT "FK_medikament_id" FOREIGN KEY (medikament_id) REFERENCES medikament(id)
);

-- zapis definition

-- Drop table



CREATE TABLE zapis (
	dijabeticar_id int4 NOT NULL,
	"data" date NOT NULL,
	CONSTRAINT "PK_zapis_id" PRIMARY KEY (dijabeticar_id, data),
	CONSTRAINT "FK_zapis_id" FOREIGN KEY (dijabeticar_id) REFERENCES dijabeticar(id)
);

-- zapis_insulin definition

-- Drop table



CREATE TABLE zapis_insulin (
	dijabeticar_id int4 NOT NULL,
	"data" date NOT NULL,
	CONSTRAINT "PK_zapis_insulin_id" PRIMARY KEY (dijabeticar_id, data),
	CONSTRAINT "FK_zapis_insulin_id" FOREIGN KEY (dijabeticar_id,"data") REFERENCES zapis(dijabeticar_id,"data")
);

-- zapis_insulin_doziran_insulin definition

-- Drop table



CREATE TABLE zapis_insulin_doziran_insulin (
	dijabeticar_id int4 NOT NULL,
	"data" date NOT NULL,
	insulin_id int4 NOT NULL,
	kolicina int4 NOT null check(kolicina > 0),
	CONSTRAINT "PK_zapis_insulin_doziran_insulin" PRIMARY KEY (dijabeticar_id, data, insulin_id),
	CONSTRAINT "FK_zapis_insulin_doziran_insulin" FOREIGN KEY (dijabeticar_id,"data") REFERENCES zapis_insulin(dijabeticar_id,"data"),
	CONSTRAINT "FK_zapis_insulin_doziran_insulin_id" FOREIGN KEY (insulin_id) REFERENCES insulin(id)
);

-- zapis_so_izmeren_sekjer definition

-- Drop table



CREATE TABLE zapis_so_izmeren_sekjer (
	dijabeticar_id int not null,
	data date not null,
	vrednost decimal(4,1) not null check(vrednost > 0),
	
	constraint "PK_zapis_so_izmeren_sekjer_id" primary key(dijabeticar_id, data),
	constraint "FK_zapis_so_izmeren_sekjer_id" foreign key(dijabeticar_id, data) references zapis(dijabeticar_id, data)
);


-- zapis_hrana definition

-- Drop table



CREATE TABLE zapis_hrana (
	dijabeticar_id int not null,
	data date not null,
	
	constraint "PK_zapis_hrana_id" primary key(dijabeticar_id, data),
	constraint "FK_zapis_hrana_id" foreign key(dijabeticar_id, data) references zapis(dijabeticar_id, data)
);
-- hrana definition

-- Drop table





CREATE TABLE hrana (
	id serial not null,
	ime varchar(500) not null,
	proizvoditel varchar(1000) not null default '',
	glikemiski_indeks int not null check (glikemiski_indeks>=0 and glikemiski_indeks<=100),
	proteini decimal(3,1) not null check (proteini>=0 and proteini<=100),
	shekjeri decimal(3,1) not null check (shekjeri>=0 and shekjeri<=100),
	masti decimal(3,1) not null check (masti>=0 and masti<=100),
	jaglehidrati decimal(3,1) not null check (jaglehidrati>=0 and jaglehidrati<=100),
	kalorii int not null check (kalorii>=0),
	nacin_prigotvuvanje_hrana_id int not null,
	
	constraint "PK_hrana_id" primary key(id),
	constraint "FK_hrana_nacin_prigotvuvanje_hrana_id" foreign key(nacin_prigotvuvanje_hrana_id) references nacin_prigotvuvanje_hrana(id),
	constraint "CHK_hrana_shekejeri" check (shekjeri <= jaglehidrati),
	CONSTRAINT "CHK_hrana_proteini_jaglehidrati_masti" check(proteini+jaglehidrati+masti > 0 AND proteini+jaglehidrati+masti <= 100)
);


-- obrok definition

-- Drop table



CREATE TABLE obrok (
	dijabeticar_id int not null,
	data date not null,
	hrana_id int not null,
	kolicina int not null check (kolicina>0),
	
	constraint "PK_obrok_id" primary key(dijabeticar_id, data, hrana_id),
	constraint "FK_obrok_dijabeticar" foreign key(dijabeticar_id, data) references zapis_hrana(dijabeticar_id, data),
	constraint "FK_obrok_hrana_id" foreign key(hrana_id) references hrana(id)
);

-- DISJOINT CHECK CONSTRAINT ZA DATOTEKA_METAPODATOCI

create function fn_validiraj_medikament_disjoint(
	p_id int, 
	p_tip_medikament char
)
returns boolean 
language sql
	returns null on null input
	as 
	$$
	select coalesce ((select 1 from medikament m where m.id = p_id and tip_medikament  = p_tip_medikament), 0) = 1;
	$$;

alter table lenti_merenje_shekjer add constraint "CHK_Validiraj_kreiran_prethoden_zapis_vo_medikament_lenti_merenje_shekjer"
check (fn_validiraj_medikament_disjoint(lenti_merenje_shekjer.id, 'L'));

alter table insulin add constraint "CHK_Validiraj_kreiran_prethoden_zapis_vo_medikament_insulin"
check (fn_validiraj_medikament_disjoint(insulin.id, 'I'));


-- DISJOINT CHECK CONSTRAINT ZA DATOTEKA_METAPODATOCI

create function fn_Validiraj_datoteka_metapodatoci_disjoint(
	p_faksimil_id int
	, p_tip_datoteka char
)
returns int
as
$$
	select coalesce (
		(select 1
		from datoteka_metapodatoci
		where id = p_faksimil_id and tip_datoteka = p_tip_datoteka
		), 0
	)
$$
language sql
RETURNS NULL ON NULL INPUT;

alter table faksimil add constraint "CHK_Validiraj_kreiran_prethoden_zapis_vo_datoteka_metapodatoci_faksimil" check(fn_Validiraj_datoteka_metapodatoci_disjoint(id, 'F')=1);
alter table zdrastven_rezultat add constraint "CHK_Validiraj_kreiran_prethoden_zapis_vo_datoteka_metapodatoci_za_zdravstven_rezultat" check(fn_Validiraj_datoteka_metapodatoci_disjoint(id, 'Z')=1);

-- DATUM NA OTKRIVANJE DIJABETES ZA DIJABETICAR E PRED DATUM NA RAGJANJE NA TOJ COVEK

CREATE function fn_Proveri_data_na_otkrivanje_dijabeticar_pred_datum_na_ragjanje(
	p_dijabeticar_id int,
	p_data_na_otkrivanje date
)
RETURNS	boolean
AS
$$
	SELECT c.datum_na_ragjanje <= p_data_na_otkrivanje
	FROM covek c
	WHERE c.id = p_dijabeticar_id;
$$
LANGUAGE SQL
RETURNS NULL ON NULL INPUT;


alter table dijabeticar add constraint "CHK_Proveri_data_na_otkrivanje_dijabeticar_pred_datum_na_ragjanje" check (fn_Proveri_data_na_otkrivanje_dijabeticar_pred_datum_na_ragjanje(dijabeticar.id, dijabeticar.data_na_otkrivanje));






































