drop view if exists parovi;
drop view if exists rpcou;
drop view if exists citingu;
drop view if exists citiraniu;
drop view if exists radoviu;

drop table if exists utca;
create table utca (
	id serial,
	ut text,
	ca text
);

drop table if exists cited;
create table cited (
	id serial,
	ca text,
	cr_auth text,
	au text,
	ut text,
	cr_full text,
	cr_year text,
	cr_ref text, 
	cr_doi text
);

drop table if exists authors;
create table authors (
	id serial,
	ut text,
	au text,
	af text,
	ca text
);

drop table if exists radovi;
create table radovi (
	ca text,
	ut text,
	pt text,
	au text,
	af text,
	ti text,
	so text,
	la text,
	dt text,
	de text,
	id text,
	c1 text,
	rp text,
	nr integer,
	tc integer,
	pi text,
	py integer,
	di text,
	sc text
);

drop table if exists radoviau;
create table radoviau (
	ut text,
	au text
);

drop table if exists citing;
create table citing (
	ut text,
	pt text,
	au text,
	af text,
	ti text,
	so text,
	la text,
	dt text,
	de text,
	id text,
	c1 text,
	rp text,
	nr integer,
	tc integer,
	pi text,
	py integer,
	di text,
	sc text
);

drop table if exists rpco;
create table rpco (
	ut text,
	rp text,
	rpco text
);


drop table if exists citirani;
create table citirani (
	id serial,
	ca text,
	cr_auth text,
	cr_ref text,
	cr_year text,
	cr_vol text,
	cr_page text,
	catc integer,
	cr_full text,
	isi text,
	camb integer
);

create view citingu as select distinct ut,pt,au,so,la,dt,nr,tc,pi,py,di,sc,rp from citing ;

create view radoviu as select distinct ut,pt,au,so,la,dt,nr,tc,pi,py,di,sc,rp from radovi ;

create view rpcou as select distinct * from rpco ;

create view parovi as select distinct citirani.ca,
	citirani.cr_full as cr,
        cited.cr_full,
        cited.ut,
	citingu.pt,
	citingu.au,
	citingu.so,
	citingu.la,
	citingu.dt,
	citingu.nr,
	citingu.tc,
	citingu.pi,
	citingu.py,
	citingu.sc,
	citingu.rp
from citirani
left join cited on citirani.cr_full = cited.cr_full
left join citingu on cited.ut = citingu.ut
left join rpcou on cited.ut = rpcou.ut
;

create view citiraniu as select distinct cr_auth,cr_ref,cr_year,cr_vol,cr_page,catc,cr_full,isi from citirani ;


drop table if exists cropsy;
create table cropsy (
	id serial,
	ut text,
	au text,
	c1 text,
	rp text,
	tc integer,
	py integer
);

drop table if exists jcr;
create table jcr (
	id serial,
	abbt text,
	issn text,
	tc text,
	impf text,
	impf5 text,
	ii text,
	noart text,
	hlife text,
	eigen text,
	infl text
);	

drop table if exists izbori;
create table izbori (
	id serial,
	autor text,
	casopis text,
	godina text,
	sd numeric
);

-- CREATE AGGREGATE array_accum (anyelement)
-- (
--     sfunc = array_append,
--     stype = anyarray,
--     initcond = '{}'
-- );

-- select d, count(*),
--	array_to_string(array_accum('+'::text),'') as graph
--	from hits group by 1 order by 1 asc;


-- create index cited_au on cited(au);
-- create index cited_cited on cited(cited);

