Apteki

create table gradovi
(
	id_grad serial not null
		constraint gradovi_pk
			primary key,
	ime_grad varchar(80)
);

alter table gradovi owner to postgres;

create table apteki
(
	id_apteka integer not null
		constraint apteki_pk
			primary key,
	ime varchar(100),
	id_grad integer
		constraint apteki_gradovi__fk
			references gradovi
);

alter table apteki owner to postgres;

grant select on apteki to student;

create unique index apteki_id_uindex
	on apteki (id_apteka);

grant select on gradovi to student;

create unique index gradovi_id_grad_uindex
	on gradovi (id_grad);

create table lekovi
(
	id_lek integer not null
		constraint lekovi_pk
			primary key,
	atc text,
	ime text,
	generik text,
	opis text,
	cena double precision
);

alter table lekovi owner to postgres;

grant select on lekovi to student;

create table pacienti
(
	id_pacient integer not null
		constraint pacienti_pkey
			primary key,
	ime varchar(50) not null,
	prezime varchar(50) not null,
	grad integer
);

alter table pacienti owner to postgres;

grant select on pacienti to student;

create unique index pacienti_id_uindex
	on pacienti (id_pacient);

create table recepti
(
	id_recept integer not null
		constraint recepti_pkey
			primary key,
	id_pacient integer,
	atc_code varchar(15)
);

alter table recepti owner to postgres;

grant select on recepti to student;

create unique index recepti_id_recept_uindex
	on recepti (id_recept);

create table smetki
(
	id_smetka integer not null
		constraint smetki_pkey
			primary key,
	id_apteka integer
		constraint smetki_apteki__fk
			references apteki,
	datum_prodazba timestamp
);

alter table smetki owner to postgres;

create table prodazba
(
	id_smetka integer
		constraint "prodazba_apteki.smetki__fk"
			references smetki,
	id_recept integer
		constraint "prodazba_apteki.recepti__fk"
			references recepti,
	id_lek integer
		constraint fk_prodazba_lekovi
			references lekovi
				on update cascade
);

alter table prodazba owner to postgres;

grant select on prodazba to student;

grant select on smetki to student;

create unique index smetki_id_smetka_uindex
	on smetki (id_smetka);

create table prodazba_lekovi
(
	id integer,
	ime_lek text,
	atc text,
	ime_apteka text,
	cena integer,
	datum timestamp
);

alter table prodazba_lekovi owner to postgres;

grant select on prodazba_lekovi to student;