CREATE VIEW dsm_datasources_view AS SELECT 
	ds.id                                                                    AS id,
	ds.officialname                                                          AS name,
	ds.englishname                                                           AS other_name,
	ds.namespaceprefix                                                       AS nsprefix,
	ds.websiteurl                                                            AS url,
	ds.consenttermsofuse                                                     AS consenttermsofuse,
	ds.fulltextdownload                                                      AS fulltextdownload,
	ds.eosc_datasource_type                                                  AS type,
	scf.officialname                                                         AS collectedfrom,
	json_agg(DISTINCT jsonb_build_object(
		'name'     , o.legalname,
		'country'  , o.country
	)) AS organizations,
	json_agg(DISTINCT jsonb_build_object(
		'id'        , a.id,
		'protocol'  , a.protocol,
		'compliance', coalesce(a.compatibility_override, a.compatibility),
		'active'    , a.active,
		'aggrDate'  , coalesce(a.last_aggregation_date::text, a.last_download_date::text, ''),
		'aggrTotal' , coalesce(a.last_aggregation_total, a.last_download_total, 0)
	)) AS apis,
	array_agg(distinct o.country)    AS hidden_countries,
	array_agg(distinct a.protocol)   AS hidden_protocols,
	array_agg(distinct coalesce(a.compatibility_override, a.compatibility)) AS hidden_compliances,
	array_agg(distinct a.active)     AS hidden_actives
FROM dsm_services ds
	LEFT OUTER JOIN dsm_api a ON (a.service = ds.id)
	LEFT OUTER JOIN dsm_service_organization dsorg ON (ds.id = dsorg.service)
	LEFT OUTER JOIN dsm_organizations o ON (dsorg.organization = o.id)
	LEFT OUTER JOIN dsm_services scf ON (ds.collectedfrom = scf.id)
WHERE ds.dedup_main_service = true AND ds.eosc_type = 'Data Source'
GROUP BY
	ds.id,
	ds.officialname,
	ds.namespaceprefix,
	ds.websiteurl,
	ds.consenttermsofuse,
	ds.fulltextdownload,
	ds.eosc_datasource_type,
	scf.officialname;
