
-- query performed by IIS to bulk load project details
DROP MATERIALIZED VIEW project_details;
CREATE MATERIALIZED VIEW 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
);

-- used to serve queries from api tsv exporter
DROP MATERIALIZED VIEW projects_tsv;
CREATE MATERIALIZED VIEW projects_tsv AS (
		SELECT
			pr.code,
			pr.acronym,
			pr.title,
			pr.call_identifier,
			pr.startdate,
			pr.enddate,
			pr.ec_sc39,
			pr.oa_mandate_for_publications,
			pr.ec_article29_3,
			f.id AS fundingpathid,
			f.description,
			org.legalname,
			org.countryclass,
			CASE WHEN po.participantnumber = 1 THEN 'coordinator' ELSE '' END AS role,
			pe.firstname,
			pe.secondnames,
			pe.email
		FROM projects pr
			LEFT OUTER JOIN project_organization po ON (pr.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 (pr.id = pf.project)
			LEFT OUTER JOIN fundingpaths f ON (pf.funding = f.id)
		ORDER BY pr.acronym);
CREATE INDEX projects_tsv_fundingpathid_idx ON projects_tsv (fundingpathid);
CREATE INDEX projects_tsv_ec_article29_3_idx ON projects_tsv (ec_article29_3);

-- used to serve queries from dspace and eprints exporter
DROP MATERIALIZED VIEW projects_api;
CREATE MATERIALIZED VIEW projects_api AS (
		SELECT
			org.legalshortname  AS funder,
			fp.jurisdiction     AS jurisdiction,
			p.acronym           AS acronym,
			p.title             AS title,
			p.code              AS code,
			p.startdate         AS startdate,
			p.enddate           AS enddate,
			fp.id               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));
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);

-- 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,
			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,
			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 projects_api TO dnetapi;
GRANT SELECT ON projects_tsv TO dnetapi;
GRANT SELECT ON project_details TO dnetapi;
GRANT SELECT ON dashboard_ds TO dnetapi;
