-- query performed by IIS to bulk load project details
DROP TABLE project_details;
CREATE TABLE project_details AS (
	SELECT
		p.id               AS projectid,
		p.acronym          AS acronym,
		p.code             AS code,
		p.optional1        AS optional1,
		p.optional2        AS optional2,
		p.jsonextrainfo    AS jsonextrainfo,
		array_agg(fp.path) AS fundingpath
	FROM projects p
		LEFT OUTER JOIN project_fundingpath pf ON (pf.project = p.id)
		LEFT OUTER JOIN fundingpaths fp ON (fp.id = pf.funding)
	GROUP BY p.id
);

GRANT SELECT ON project_details TO dnetapi;


-- used to serve queries from api tsv exporter
DROP TABLE projects_tsv;
CREATE TABLE projects_tsv AS (
	SELECT
		p.id,
		p.code,
		p.acronym,
		p.title,
		p.call_identifier,
		p.startdate,
		p.enddate,
		p.ec_sc39,
		p.oa_mandate_for_publications,
		p.ec_article29_3 AS oa_mandate_for_datasets,
		(array_agg(f.id))[1] AS fundingpathid,
		(array_agg(f.description))[1] AS description,
		(array_agg(org.legalname))[1] AS legalname,
		(array_agg(org.countryclass))[1] AS country,
		(array_agg(CASE WHEN po.participantnumber = 1
			THEN 'coordinator'
		ELSE '' END))[1] AS role,
		(array_agg(pe.firstname))[1] AS firstname,
		(array_agg(pe.secondnames))[1] AS secondnames,
		(array_agg(pe.email))[1] AS email
	FROM projects p
		LEFT OUTER JOIN project_organization po ON (p.id = po.project)
		LEFT OUTER JOIN persons pe ON (po.contactperson = pe.id)
		LEFT OUTER JOIN organizations org ON (po.resporganization = org.id)
		LEFT OUTER JOIN project_fundingpath pf ON (p.id = pf.project)
		LEFT OUTER JOIN fundingpaths f ON (pf.funding = f.id)
	GROUP BY p.id
	ORDER BY p.acronym
);

CREATE INDEX projects_tsv_fundingpathid_idx ON projects_tsv (fundingpathid);
CREATE INDEX projects_tsv_oa_mandate_for_datasets_idx ON projects_tsv (oa_mandate_for_datasets);
GRANT SELECT ON projects_tsv TO dnetapi;

-- used to serve queries from dspace and eprints exporter
DROP TABLE projects_api;
CREATE TABLE projects_api AS (
	SELECT
		p.id,
		p.acronym          AS acronym,
		p.title            AS title,
		p.code             AS code,
		p.startdate        AS startdate,
		p.enddate          AS enddate,
		(array_agg(org.legalshortname))[1] AS funder,
		(array_agg(fp.jurisdiction))[1]    AS jurisdiction,
		(array_agg(fp.id))[1]              AS fundingpathid
	FROM projects p
		LEFT OUTER JOIN project_fundingpath pfp ON (p.id = pfp.project)
		LEFT OUTER JOIN fundingpaths fp ON (pfp.funding = fp.id)
		LEFT OUTER JOIN organizations org ON (org.id = fp.funder)
	GROUP BY p.id);

CREATE INDEX projects_api_fundingpathid_idx ON projects_api (fundingpathid);
CREATE INDEX projects_api_startdate_idx ON projects_api (startdate);
CREATE INDEX projects_api_enddate_idx ON projects_api (enddate);
GRANT SELECT ON projects_api TO dnetapi;

-- used to serve queries from the repo admin dashboard
DROP MATERIALIZED VIEW dashboard_ds;
CREATE MATERIALIZED VIEW dashboard_ds AS (
	SELECT
		ds.id,
		ds.officialname,
		ds.englishname,
		ds.websiteurl,
		ds.logourl,
		ds.contactemail,
		ds.latitude,
		ds.longitude,
		ds.timezone,
		ds.namespaceprefix,
		ds.collectedfrom,
		ds.dateofvalidation,
		ds.registeredby,
		ds.datasourceclass,
		ds.provenanceactionclass,
		ds.dateofcollection,
		ds.typology,
		ds.activationid,
		ds.description,
		ds.missionstatementurl,
		ds.aggregator,
		ds.issn,
		ds.eissn,
		ds.lissn,
		(array_agg(o.legalname)) [1]             AS organization,
		(array_agg(ccl.code)) [1]                AS countrycode,
		(array_agg(ccl.name)) [1]                AS countryname,
		array_agg(DISTINCT ag.accessinfopackage) AS accessinfopackage,
		to_char(now(), 'YYYY-MM-DDThh24:mi:ssZ') AS now

	FROM datasources ds
		LEFT OUTER JOIN datasource_organization dso ON ds.id = dso.datasource
		LEFT OUTER JOIN organizations o ON dso.organization = o.id
		LEFT OUTER JOIN class ccl ON ccl.code = o.countryclass
		LEFT OUTER JOIN (
			                SELECT
				                api.datasource,
				                api.contentdescriptionclass,
				                api.compatibilityclass,
				                COALESCE(api.id, '') || '<==1==>' ||
				                COALESCE(api.typologyclass, '') || '<==2==>' ||
				                COALESCE(api.compatibilityclass, '') || '<==3==>' ||
				                COALESCE(api.contentdescriptionclass, '') || '<==4==>' ||
				                COALESCE(api.protocolclass, '') || '<==5==>' ||
				                COALESCE(api.active, FALSE) || '<==6==>' ||
				                COALESCE(api.removable, FALSE) || '<==7==>' ||
				                array_to_string(
						                array_agg(DISTINCT
								                COALESCE(ac.accessparam, TRUE) || '###' ||
								                COALESCE(ac.param, '') || '###' ||
								                COALESCE(ac.value, '') || '###'), '@@@') AS accessinfopackage
			                FROM api
				                LEFT OUTER JOIN (
					                                SELECT
						                                api,
						                                param,
						                                accessparam,
						                                CASE WHEN edited IS NULL
							                                THEN original
						                                ELSE edited END AS value
					                                FROM apicollections
				                                ) AS ac ON (ac.api = api.id)
			                GROUP BY
				                api.id,
				                api.datasource,
				                api.contentdescriptionclass,
				                api.typologyclass,
				                api.compatibilityclass,
				                api.protocolclass,
				                api.active,
				                api.removable
		                ) AS ag ON (ag.datasource = ds.id)
	GROUP BY
		ds.id,
		ds.officialname,
		ds.englishname,
		ds.websiteurl,
		ds.logourl,
		ds.contactemail,
		ds.latitude,
		ds.longitude,
		ds.timezone,
		ds.namespaceprefix,
		ds.collectedfrom,
		ds.dateofvalidation,
		ds.registeredby,
		ds.datasourceclass,
		ds.provenanceactionclass,
		ds.dateofcollection,
		ds.typology,
		ds.activationid,
		ds.description,
		ds.aggregator,
		ds.issn,
		ds.eissn,
		ds.lissn
);
CREATE INDEX dashboard_ds_id_idx ON dashboard_ds (id);

DROP MATERIALIZED VIEW projects_mv;
CREATE MATERIALIZED VIEW projects_mv AS (
	SELECT
		p.id                                                                                                       AS projectid,
		p.code                                                                                                     AS code,
		p.websiteurl                                                                                               AS websiteurl,
		p.acronym                                                                                                  AS acronym,
		p.title                                                                                                    AS title,
		p.startdate                                                                                                AS startdate,
		p.enddate                                                                                                  AS enddate,
		p.call_identifier                                                                                          AS callidentifier,
		p.keywords                                                                                                 AS keywords,
		p.duration                                                                                                 AS duration,
		p.ec_sc39                                                                                                  AS ecsc39,
		p.oa_mandate_for_publications                                                                              AS oamandatepublications,
		p.ec_article29_3                                                                                           AS ecarticle29_3,
		p.dateofcollection                                                                                         AS dateofcollection,
		p.inferred                                                                                                 AS inferred,
		p.deletedbyinference                                                                                       AS deletedbyinference,
		p.trust                                                                                                    AS trust,
		p.inferenceprovenance                                                                                      AS inferenceprovenance,
		p.optional1                                                                                                AS optional1,
		p.optional2                                                                                                AS optional2,
		p.jsonextrainfo                                                                                            AS jsonextrainfo,
		p.contactfullname                                                                                          AS contactfullname,
		p.contactfax                                                                                               AS contactfax,
		p.contactphone                                                                                             AS contactphone,
		p.contactemail                                                                                             AS contactemail,
		dc.id                                                                                                      AS collectedfromid,
		dc.officialname                                                                                            AS collectedfromname,
		cc.code || '@@@' || cc.name || '@@@' || cs.code || '@@@' || cs.name                                        AS contracttype,
		pac.code || '@@@' || pac.name || '@@@' || pas.code || '@@@' || pas.name                                    AS provenanceaction,
		array_agg(DISTINCT i.pid || '###' || i.issuertypeclass)                                                    AS pid,
		array_agg(DISTINCT s.name || '###' || sc.code || '@@@' || sc.name || '@@@' || ss.code || '@@@' || ss.name) AS subjects,
		array_agg(fp.path)                                                                                         AS fundingtree
	FROM projects p
		LEFT OUTER JOIN class cc ON (cc.code = p.contracttypeclass)
		LEFT OUTER JOIN scheme cs ON (cs.code = p.contracttypescheme)

		LEFT OUTER JOIN class pac ON (pac.code = p.provenanceactionclass)
		LEFT OUTER JOIN scheme pas ON (pas.code = p.provenanceactionscheme)

		LEFT OUTER JOIN projectpids pp ON (pp.project = p.id)
		LEFT OUTER JOIN identities i ON (i.pid = pp.pid)

		LEFT OUTER JOIN datasources dc ON (dc.id = p.collectedfrom)

		LEFT OUTER JOIN project_fundingpath pf ON (pf.project = p.id)
		LEFT OUTER JOIN fundingpaths fp ON (fp.id = pf.funding)

		LEFT OUTER JOIN project_subject ps ON (ps.project = p.id)
		LEFT OUTER JOIN subjects s ON (s.id = ps.subject)

		LEFT OUTER JOIN class sc ON (sc.code = s.semanticclass)
		LEFT OUTER JOIN scheme ss ON (ss.code = s.semanticscheme)

	GROUP BY
		p.id,
		p.code,
		p.websiteurl,
		p.acronym,
		p.title,
		p.startdate,
		p.enddate,
		p.call_identifier,
		p.keywords,
		p.duration,
		p.ec_sc39,
		p.oa_mandate_for_publications,
		p.ec_article29_3,
		p.dateofcollection,
		p.inferred,
		p.deletedbyinference,
		p.trust,
		p.inferenceprovenance,
		p.contactfullname,
		p.contactfax,
		p.contactphone,
		p.contactemail,
		dc.id,
		dc.officialname,
		cc.code, cc.name, cs.code, cs.name,
		pac.code, pac.name, pas.code, pas.name
);
CREATE INDEX projects_mv_id_idx	ON projects_mv (projectid);

GRANT SELECT ON dashboard_ds TO dnetapi;
GRANT SELECT ON fundingpaths TO dnetapi;