SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;


-- MAIN ENTITIES --

CREATE TABLE document (
    id          text PRIMARY KEY,
    title       text,
    abstract    text,
    doctype     text,
    pmcid       text,
    repository  text,
    doi         text,
    pubyear     text
);

CREATE TABLE project (
    id            text PRIMARY KEY,
    grantid       text,
    title         text,
    acronym       text,
    funder        text,
    fundinglevel0 text,
    fundinglevel1 text,
    fundinglevel2 text,
    callid        text,
    rcn           text,
    startdate     text,
    enddate       text,
    websiteurl    text, 
	keywords      text, 
	contracttype  text,
	ec_sc39                     boolean,
	oa_mandate_for_publications boolean,
	ec_article29_3              boolean 
);

CREATE TABLE author (
    id          text PRIMARY KEY,
    firstname   text,
    lastname    text,
    middlename  text,
    affiliation text
);

CREATE TABLE journal (
    id         text PRIMARY KEY,
    issn       text UNIQUE,
    shorttitle text,
    title      text
);

CREATE TABLE conference (
    id               text PRIMARY KEY,
    seriestitle      text,
    description      text,
    seriesid         text,
    conferencenumber text,
    acronym          text,
    acronymbase      text
);

CREATE TABLE citation (
    id        text PRIMARY KEY,
    reference text
);

CREATE TABLE organization (
	id                                       text PRIMARY KEY,
	originalid                               text,
	name                                     text,
	shortname                                text,
	country                                  text,
	street                                   text,
	city                                     text,
	postcode                                 text,
	url                                      text,
	ec_legalbody                             boolean,
	ec_legalperson                           boolean,
	ec_nonprofit                             boolean,
	ec_researchorganization                  boolean,
	ec_highereducation                       boolean,
	ec_internationalorganizationeurinterests boolean,
	ec_internationalorganization             boolean,
	ec_enterprise                            boolean,
	ec_smevalidated                          boolean,
	ec_nutscode                              boolean
);

CREATE TABLE topic (
	id        text PRIMARY KEY,
    title     text,
    category  text
);

-- RELATIONS BETWEEN ENTITIES --

CREATE TABLE docauthor (
    docid     text NOT NULL REFERENCES document(id),
    authorid  text NOT NULL REFERENCES author(id),
    profileid text,
    PRIMARY KEY (docid, authorid)
);

CREATE TABLE docproject (
    docid     text NOT NULL REFERENCES document(id),
    projectid text NOT NULL REFERENCES project(id),
    PRIMARY KEY (docid, projectid)
);

CREATE TABLE docjournal (
    docid text NOT NULL REFERENCES document(id),
    issn  text NOT NULL REFERENCES journal(issn),
    PRIMARY KEY (docid, issn)
);

CREATE TABLE docconference (
    docid        text NOT NULL REFERENCES document(id),
    conferenceid text NOT NULL REFERENCES conference(id),
    acronymbase  text,
    PRIMARY KEY (docid, conferenceid)
);

CREATE TABLE doccitation (
    docid      text NOT NULL REFERENCES document(id),
    citationid text NOT NULL REFERENCES citation(id),
    PRIMARY KEY (docid, citationid)
);

CREATE TABLE projectorganization (
	projectid              text NOT NULL REFERENCES project(id),
	orgid                  text NOT NULL REFERENCES organization(id),
	role                   text,
	activitytype           text,
	endofparticipation     text,
	eccontribution         text,
	contacttype            text,
	contacttitle           text,
	contactfirstnames      text,
	contactlastnames       text,
	contactfunction        text,
	contacttelephonenumber text,
	contactfaxnumber       text,
	contactform            text,
	PRIMARY KEY (projectid,orgid)
);


CREATE TABLE doctopic (
    docid   text NOT NULL REFERENCES document(id),
    topicid text NOT NULL REFERENCES topic(id),
	PRIMARY KEY (docid, topicid)
);


-- ADDITIONAL TABLES --

CREATE TABLE docfulltext (
    docid    text PRIMARY KEY REFERENCES document(id),
    fulltext text NOT NULL
);

CREATE TABLE dockeyword (
    docid   text NOT NULL REFERENCES document(id),
    keyword text NOT NULL,
    PRIMARY KEY (docid, keyword)
);

CREATE TABLE docpdbcode (
    docid   text NOT NULL REFERENCES document(id),
    pdbcode text NOT NULL,
    PRIMARY KEY (docid, pdbcode)
);

CREATE TABLE companymetrics (
	orgid                 text PRIMARY KEY REFERENCES organization(id),
	data_gathered         boolean,
    tangible_pre_market   int,
    tangible_market       int,
    intangible_pre_market int,
    intangible_market     int,
    innovation            boolean
)

