DROP VIEW organizations_view;
DROP VIEW organizations_info_view;
DROP VIEW organizations_simple_view;
DROP VIEW users_view;
DROP VIEW conflicts_view;

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 urls;
DROP TABLE IF EXISTS openaire_simrels;
DROP TABLE IF EXISTS organizations;
DROP TABLE IF EXISTS org_types;

DROP TABLE IF EXISTS user_countries;
DROP TABLE IF EXISTS users;

DROP TABLE IF EXISTS user_roles;
DROP TABLE IF EXISTS countries;
DROP TABLE IF EXISTS id_types;
DROP TABLE IF EXISTS languages;

DROP SEQUENCE IF EXISTS organizations_id_seq;

CREATE TABLE org_types (val text PRIMARY KEY);
INSERT INTO org_types VALUES ('Archive'), ('Company'), ('Education'), ('Facility'), ('Government'), ('Healthcare'), ('Nonprofit'), ('Other'), ('UNKNOWN');

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 user_roles(role text PRIMARY KEY);
INSERT INTO user_roles VALUES ('ADMIN'), ('NATIONAL_ADMIN'), ('USER'), ('PENDING'), ('NOT_AUTHORIZED');

CREATE TABLE users (
	email     text PRIMARY KEY,
	valid     boolean DEFAULT true,
	role      text NOT NULL default 'USER' REFERENCES user_roles(role)
);

CREATE TABLE user_countries (
	email     text REFERENCES users(email),
	country   text  REFERENCES countries(val),
	PRIMARY KEY(email, country)	
);

CREATE SEQUENCE organizations_id_seq;

CREATE TABLE organizations (
    id                text PRIMARY KEY DEFAULT 'openorgs____::'||lpad(nextval('organizations_id_seq')::text,10,'0'),
    name              text,
   	type              text NOT NULL DEFAULT 'UNKNOWN' REFERENCES org_types(val),
    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 INDEX organizations_type_idx ON organizations(type);
CREATE INDEX organizations_country_idx ON organizations(country);

CREATE TABLE other_ids (
	id      text REFERENCES organizations(id) ON UPDATE CASCADE,
	otherid text,
	type    text REFERENCES id_types(val),
	PRIMARY KEY (id, otherid, type)
);
CREATE INDEX other_ids_id_idx ON other_ids(id);

CREATE TABLE other_names (
	id    text REFERENCES organizations(id) ON UPDATE CASCADE,
	name  text,
	lang  text REFERENCES languages(val),
	PRIMARY KEY (id, name, lang)
);
CREATE INDEX other_names_id_idx ON other_names(id);

CREATE TABLE acronyms (
	id text  REFERENCES organizations(id) ON UPDATE CASCADE,
	acronym text,
	PRIMARY KEY (id, acronym)
);
CREATE INDEX acronyms_id_idx ON acronyms(id);

CREATE TABLE relationships (
	id1     text REFERENCES organizations(id) ON UPDATE CASCADE,
	reltype text,
	id2     text REFERENCES organizations(id) ON UPDATE CASCADE,
    PRIMARY KEY (id1, reltype, id2)
);
CREATE INDEX relationships_id1_idx ON relationships(id1);
CREATE INDEX relationships_id2_idx ON relationships(id2);

CREATE TABLE urls (
	id  text REFERENCES organizations(id) ON UPDATE CASCADE,
	url text,
	PRIMARY KEY (id, url)
);
CREATE INDEX urls_id_idx ON urls(id);

CREATE TABLE openaire_simrels (
	local_id         text REFERENCES organizations(id) ON UPDATE CASCADE,
	oa_original_id   text NOT NULL,
	oa_name          text NOT NULL,
	oa_acronym       text,
	oa_country       text,
	oa_url           text,
	oa_collectedfrom text,
	reltype          text NOT NULL DEFAULT 'suggested',
	PRIMARY KEY (local_id, oa_original_id)
);
CREATE INDEX openaire_simrels_local_id_idx ON openaire_simrels(local_id);

CREATE VIEW organizations_view AS SELECT
    org.id,
    org.name,
    org.type,
    org.lat,
	org.lng,
	org.city,
	org.country,
	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 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 urls u           ON (org.id = u.id)
GROUP BY
    org.id,
    org.name,
    org.type,
    org.lat,
	org.lng,
	org.city,
	org.country;
	
CREATE VIEW organizations_info_view AS SELECT
	org.id,
	org.created_by,
	org.creation_date,
	org.modified_by,
	org.modification_date,
	count(DISTINCT r.id2)      as n_rels,
	count(DISTINCT e.oa_original_id) as n_enrichments,
	count(DISTINCT c.oa_original_id) as n_conflicts
FROM organizations org
	LEFT OUTER JOIN relationships r    ON (org.id = r.id1)
	LEFT OUTER JOIN openaire_simrels e ON (org.id = e.local_id AND e.oa_original_id NOT LIKE 'openorgs____::%')
	LEFT OUTER JOIN openaire_simrels c ON (org.id = c.local_id AND c.oa_original_id LIKE 'openorgs____::%')
GROUP BY org.id;
	
CREATE VIEW organizations_simple_view AS SELECT
    org.id,
    org.name,
    org.type,
	org.city,
	org.country,
	array_agg(DISTINCT a.acronym) FILTER (WHERE a.acronym IS NOT NULL) AS acronyms
FROM
    organizations org
    LEFT OUTER JOIN acronyms a ON (org.id = a.id)
GROUP BY
    org.id,
    org.name,
    org.type,
	org.city,
	org.country;
	
CREATE VIEW	users_view AS SELECT
	u.email,
	u.valid,
	u.role,
	array_agg(uc.country) FILTER (WHERE uc.country IS NOT NULL) AS countries
FROM
	users u
	LEFT OUTER JOIN user_countries uc ON (u.email = uc.email)
GROUP BY u.email, u.valid, u.role
ORDER BY u.email;

CREATE VIEW conflicts_view AS SELECT
    o1.id      AS id_1,
    o1.name    AS name_1,
    o1.type    AS type_1,
	o1.city    AS city_1,
	o1.country AS country_1,
    o2.id      AS id_2,
    o2.name    AS name_2,
    o2.type    AS type_2,
	o2.city    AS city_2,
	o2.country AS country_2
FROM
	openaire_simrels s
	LEFT OUTER JOIN organizations o1 ON (s.local_id = o1.id)
    LEFT OUTER JOIN organizations o2 ON (s.oa_original_id = o2.id)
WHERE
	s.oa_original_id LIKE 'openorgs____::%' AND o1.id IS NOT NULL AND O2.id IS NOT NULL;