-- 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.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
     nextval('projects_tsv_ids') AS rowid,
    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,
    p.contactfullname AS contactfullname,
    p.contactemail AS contactemail,
    org.legalname AS legalname,
    org.country AS country,
    (array_agg(f.id))[1] AS fundingpathid,
    (array_agg(f.description))[1] AS description,
    (array_agg(CASE WHEN po.participantnumber = 1
      THEN 'coordinator'
    ELSE '' END))[1] AS role

  FROM projects p
    LEFT OUTER JOIN project_organization po ON (p.id = po.project)
    LEFT OUTER JOIN dsm_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.code,
  	p.acronym,
    p.title,
    p.call_identifier,
    p.startdate,
    p.enddate,
    p.ec_sc39,
    p.oa_mandate_for_publications,
    p.ec_article29_3,
    p.contactfullname,
    p.contactemail,
    org.legalname,
    org.country
  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 dsm_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;


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.lastupdate                                                                                               AS dateoftransformation,
		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,
		p.summary                                                                                                  AS summary,
		p.currency                                                                                                 AS currency,
		p.totalcost                                                                                                AS totalcost,
        p.fundedamount                                                                                             AS fundedamount,
		dc.id                                                                                                      AS collectedfromid,
		dc.officialname                                                                                            AS collectedfromname,
		p.contracttype || '@@@' || p.contracttypename || '@@@' || p.contracttypescheme || '@@@' || p.contracttypescheme     AS contracttype,
		pac.code || '@@@' || pac.name || '@@@' || pas.code || '@@@' || pas.name                                    AS provenanceaction,
		array_agg(DISTINCT i.pid || '###' || i.issuertype)                                                         AS pid,
		array_agg(DISTINCT s.name || '###' || sc.code || '@@@' || sc.name || '@@@' || ss.code || '@@@' || ss.name) AS subjects,
		array_agg(DISTINCT fp.path)                                                                                AS fundingtree
	FROM projects p
		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 dsm_identities i ON (i.pid = pp.pid)

		LEFT OUTER JOIN dsm_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,
		p.contracttype,
		p.summary,
		p.currency,
		p.totalcost,
		p.fundedamount,
		dc.id,
		dc.officialname,
		pac.code, pac.name, pas.code, pas.name
);
CREATE INDEX projects_mv_id_idx	ON projects_mv (projectid);

GRANT SELECT ON fundingpaths TO dnetapi;