D-Net

DROP VIEW organizations_view;

DROP VIEW organizations_simple_view;

DROP TABLE IF EXISTS user_countries;

DROP TABLE IF EXISTS users;

DROP TABLE IF EXISTS other_ids;

DROP TABLE IF EXISTS other_names;

DROP TABLE IF EXISTS acronyms;

DROP TABLE IF EXISTS relationships;

DROP TABLE IF EXISTS types;

DROP TABLE IF EXISTS urls;

DROP TABLE IF EXISTS openaire_simrels;

DROP TABLE IF EXISTS organizations;

DROP TABLE IF EXISTS org_types;

DROP TABLE IF EXISTS id_types;

DROP TABLE IF EXISTS languages;

DROP TABLE IF EXISTS countries;

CREATE TABLE org_types (val text PRIMARY KEY);

INSERT INTO org_types VALUES ('Archive'), ('Company'), ('Education'), ('Facility'), ('Government'), ('Healthcare'), ('Nonprofit'), ('Other');

CREATE TABLE id_types (val text PRIMARY KEY);

INSERT INTO id_types VALUES ('CNRS'), ('FundRef'), ('HESA'), ('ISNI'), ('OrgRef'), ('UCAS'), ('UKPRN'), ('Wikidata'), ('grid.ac');

CREATE TABLE languages (val text PRIMARY KEY);

INSERT INTO languages VALUES ('UNKNOWN'), ('aa'), ('af'), ('am'), ('ar'), ('as'), ('az'), ('ba'), ('be'), ('bg'), ('bn'), ('br'), ('bs'), ('ca'), ('ch'), ('co'), ('cs'), ('cy'), ('da'),

			('de'), ('dv'), ('dz'), ('el'), ('en'), ('eo'), ('es'), ('et'), ('eu'), ('fa'), ('fi'), ('fr'), ('fy'), ('ga'), ('gd'), ('gl'), ('gu'), ('he'), ('hi'), ('hr'), ('hu'), ('hy'), ('id'), ('is'),

			('it'), ('iu'), ('ja'), ('jv'), ('ka'), ('kk'), ('kl'), ('km'), ('kn'), ('ko'), ('ku'), ('ky'), ('la'), ('lb'), ('lo'), ('lt'), ('lv'), ('mg'), ('mi'), ('mk'), ('ml'), ('mn'), ('mr'), ('ms'),

			('mt'), ('my'), ('nb'), ('ne'), ('nl'), ('nn'), ('no'), ('oc'), ('or'), ('pa'), ('pl'), ('ps'), ('pt'), ('rm'), ('ro'), ('ru'), ('rw'), ('sa'), ('sd'), ('si'), ('sk'), ('sl'), ('so'), ('sq'),

			('sr'), ('sv'), ('sw'), ('ta'), ('te'), ('tg'), ('th'), ('tk'), ('tl'), ('tr'), ('tt'), ('ug'), ('uk'), ('ur'), ('uz'), ('vi'), ('xh'), ('yo'), ('zh'), ('zu');

CREATE TABLE countries (val text PRIMARY KEY);

INSERT INTO countries VALUES ('UNKNOWN'), ('AD'), ('AE'), ('AF'), ('AG'), ('AL'), ('AM'), ('AO'), ('AR'), ('AT'), ('AU'), ('AW'), ('AX'), ('AZ'), ('BA'), ('BB'), ('BD'), ('BE'), ('BF'), ('BG'), ('BH'), ('BI'),

			('BJ'), ('BM'), ('BN'), ('BO'), ('BQ'), ('BR'), ('BS'), ('BT'), ('BW'), ('BY'), ('BZ'), ('CA'), ('CD'), ('CF'), ('CG'), ('CH'), ('CI'), ('CL'), ('CM'), ('CN'), ('CO'), ('CR'),

			('CU'), ('CV'), ('CW'), ('CY'), ('CZ'), ('DE'), ('DJ'), ('DK'), ('DM'), ('DO'), ('DZ'), ('EC'), ('EE'), ('EG'), ('EH'), ('ER'), ('ES'), ('ET'), ('FI'), ('FJ'), ('FM'), ('FO'),

			('FR'), ('GA'), ('GB'), ('GD'), ('GE'), ('GF'), ('GH'), ('GI'), ('GL'), ('GM'), ('GN'), ('GP'), ('GQ'), ('GR'), ('GT'), ('GW'), ('GY'), ('HN'), ('HR'), ('HT'), ('HU'), ('ID'),

			('IE'), ('IL'), ('IM'), ('IN'), ('IQ'), ('IR'), ('IS'), ('IT'), ('JE'), ('JM'), ('JO'), ('JP'), ('KE'), ('KG'), ('KH'), ('KN'), ('KP'), ('KR'), ('KW'), ('KY'), ('KZ'), ('LA'),

			('LB'), ('LC'), ('LI'), ('LK'), ('LR'), ('LS'), ('LT'), ('LU'), ('LV'), ('LY'), ('MA'), ('MC'), ('MD'), ('ME'), ('MG'), ('MK'), ('ML'), ('MM'), ('MN'), ('MO'), ('MQ'), ('MR'),

			('MS'), ('MT'), ('MU'), ('MV'), ('MW'), ('MX'), ('MY'), ('MZ'), ('NA'), ('NC'), ('NE'), ('NG'), ('NI'), ('NL'), ('NO'), ('NP'), ('NU'), ('NZ'), ('OM'), ('PA'), ('PE'), ('PF'),

			('PG'), ('PH'), ('PK'), ('PL'), ('PS'), ('PT'), ('PW'), ('PY'), ('QA'), ('RE'), ('RO'), ('RS'), ('RU'), ('RW'), ('SA'), ('SB'), ('SC'), ('SD'), ('SE'), ('SG'), ('SI'), ('SJ'),

			('SK'), ('SL'), ('SM'), ('SN'), ('SO'), ('SR'), ('SS'), ('ST'), ('SV'), ('SX'), ('SY'), ('SZ'), ('TC'), ('TD'), ('TG'), ('TH'), ('TJ'), ('TL'), ('TM'), ('TN'), ('TO'), ('TR'),

			('TT'), ('TV'), ('TW'), ('TZ'), ('UA'), ('UG'), ('US'), ('UY'), ('UZ'), ('VA'), ('VC'), ('VE'), ('VG'), ('VN'), ('WS'), ('XK'), ('YE'), ('ZA'), ('ZM'), ('ZW');

CREATE TABLE users (

	email     text PRIMARY KEY,

	password  text NOT NULL,

	valid     boolean default true,

	superuser boolean default false

);

CREATE TABLE user_countries (

	email     text REFERENCES users(email),

	country   text  REFERENCES countries(val),

	PRIMARY KEY(email, country)	

);

CREATE TABLE organizations (

    id                text PRIMARY KEY,

    name              text,

    lat               double precision,

	lng               double precision,

	city              text,

	country           text REFERENCES countries(val),

	created_by        text,

	creation_date     timestamp with time zone DEFAULT now(),

	modified_by       text,

	modification_date timestamp with time zone DEFAULT now()

);

CREATE TABLE other_ids (

	id      text REFERENCES organizations(id),

	otherid text,

	type    text REFERENCES id_types(val),

	PRIMARY KEY (id, otherid, type)

);

CREATE TABLE other_names (

	id    text REFERENCES organizations(id),

	name  text,

	lang  text REFERENCES languages(val),

	PRIMARY KEY (id, name, lang)

);

CREATE TABLE acronyms (

	id text,

	acronym text,

	PRIMARY KEY (id, acronym)

);

CREATE TABLE relationships (

	id1     text REFERENCES organizations(id),

	reltype text,

	id2     text REFERENCES organizations(id),

    PRIMARY KEY (id1, reltype, id2)

);

CREATE TABLE types (

	id   text REFERENCES organizations(id),

	type text REFERENCES org_types(val),

	PRIMARY KEY (id, type)

);

CREATE TABLE urls (

	id  text REFERENCES organizations(id),

	url text,

	PRIMARY KEY (id, url)

);

CREATE TABLE openaire_simrels (

	local_id         text REFERENCES organizations(id),

	oa_id            text,

	oa_original_id   text,

	oa_name          text,

	oa_acronym       text,

	oa_country       text,

	oa_url           text,

	oa_collectedfrom text,

	reltype          text  NOT NULL DEFAULT 'suggested',

	PRIMARY KEY (local_id, oa_id)

);

CREATE VIEW organizations_view AS SELECT

    org.id,

    org.name,

    org.lat,

	org.lng,

	org.city,

	org.country,

	org.created_by,

	org.creation_date,

	org.modified_by,

	org.modification_date,

	COALESCE(jsonb_agg(DISTINCT jsonb_build_object('id', oid.otherid, 'type', oid.type)) FILTER (WHERE oid.otherid IS NOT NULL), '[]') AS other_ids,

	COALESCE(jsonb_agg(DISTINCT jsonb_build_object('name', n.name, 'lang', n.lang))      FILTER (WHERE n.name      IS NOT NULL), '[]') AS other_names,

	COALESCE(jsonb_agg(DISTINCT a.acronym)                                               FILTER (WHERE a.acronym   IS NOT NULL), '[]') AS acronyms,	

	COALESCE(jsonb_agg(DISTINCT t.type::text)                                            FILTER (WHERE t.type      IS NOT NULL), '[]') AS types,

	COALESCE(jsonb_agg(DISTINCT u.url)                                                   FILTER (WHERE u.url       IS NOT NULL), '[]') AS urls

FROM

    organizations org

    LEFT OUTER JOIN other_ids oid    ON (org.id = oid.id)

    LEFT OUTER JOIN other_names n    ON (org.id = n.id)

    LEFT OUTER JOIN acronyms a       ON (org.id = a.id)

    LEFT OUTER JOIN types t          ON (org.id = t.id)

    LEFT OUTER JOIN urls u           ON (org.id = u.id)

GROUP BY

    org.id,

    org.name,

    org.lat,

	org.lng,

	org.city,

	org.country,

	org.created_by,

	org.creation_date,

	org.modified_by,

	org.modification_date;

CREATE VIEW organizations_simple_view AS SELECT

    org.id,

    org.name,

	org.city,

	org.country,

	array_agg(DISTINCT a.acronym)    FILTER (WHERE a.acronym IS NOT NULL) AS acronyms,	

	array_agg(DISTINCT t.type::text) FILTER (WHERE t.type    IS NOT NULL) AS types

FROM

    organizations org

    LEFT OUTER JOIN acronyms a       ON (org.id = a.id)

    LEFT OUTER JOIN types t          ON (org.id = t.id)

GROUP BY

    org.id,

    org.name,

	org.city,

	org.country;