
-- drop them in the right order
DROP VIEW IF EXISTS
	view_dc_name
;
DROP VIEW IF EXISTS
	view_dcs
;
DROP VIEW IF EXISTS
	view_profile
;
DROP VIEW IF EXISTS
	view_data_category
;
DROP VIEW IF EXISTS
	view_text
;
DROP VIEW IF EXISTS
	view_name
;
DROP VIEW IF EXISTS
	view_owner
;
DROP VIEW IF EXISTS
	view_user
;
DROP VIEW IF EXISTS
	view_dc_type
;
DROP VIEW IF EXISTS
	view_message
;
DROP VIEW IF EXISTS
	view_dc_profiles
;
DROP VIEW IF EXISTS
	view_link_dcs_access
;
DROP VIEW IF EXISTS
	view_cr_decision_group_tdg
;
DROP VIEW IF EXISTS
	view_cr_decision_group_dcrb
;
DROP VIEW IF EXISTS
	view_dc_access
;
DROP VIEW IF EXISTS
	view_dcs_access
;
DROP VIEW IF EXISTS
	view_dcs_dc
;

CREATE OR REPLACE VIEW
	view_dc_name
AS SELECT
	core_data_category.id,
	generic_name.name,
	generic_name.status,
	generic_name.source,
	core_language.name AS language,
	core_language.tag
FROM
	core_data_category
JOIN
	core_language_section
ON
	core_language_section.dc = core_data_category.id
JOIN
	core_language
ON
	core_language.id = core_language_section.language
JOIN
	generic_name
ON
	generic_name.owner = core_language_section.id
WHERE
	generic_name.owner_type = 'language section'
ORDER BY generic_name.id
;

CREATE OR REPLACE VIEW
	view_owner
AS SELECT
	core_owner.id AS id,
	COALESCE(core_thematic_domain_group.name, user_name(core_user.id), NULL) AS name
FROM
	core_owner
LEFT OUTER JOIN
	core_thematic_domain_group
ON
	core_thematic_domain_group.id = core_owner.id
LEFT OUTER JOIN
	core_user
ON
	core_user.id = core_owner.id
;

CREATE OR REPLACE VIEW
	view_user
AS SELECT
	*,
	user_name(id) AS name
FROM
	core_user
;

CREATE OR REPLACE VIEW
	view_dc_type
AS SELECT
	core_data_category.id AS id,
	dc_type.type AS "type"
FROM
	core_data_category
LEFT OUTER JOIN
(
	SELECT
		id,
		'open' AS "type"
	FROM
	(
		SELECT
			id
		FROM
			core_open_data_category

		EXCEPT
		
		SELECT
			id
		FROM
			core_constrained_data_category
	) AS open
	
	UNION

	SELECT
		core_constrained_data_category.id AS id,
		'constrained' AS "type"
	FROM
		core_constrained_data_category

	UNION

	SELECT
		core_closed_data_category.id AS id,
		'closed' AS "type"
	FROM
		core_closed_data_category
		
	UNION

	SELECT
		core_simple_data_category.id AS id,
		'simple' AS "type"
	FROM
		core_simple_data_category
		
	UNION

	SELECT
		core_container_data_category.id AS id,
		'container' AS "type"
	FROM
		core_container_data_category
) AS dc_type
ON
	dc_type.id = core_data_category.id
;

CREATE OR REPLACE VIEW
	view_dc_profiles
AS SELECT
	dc AS id,
	concat(DISTINCT CAST(profile AS varchar)) AS profiles
FROM
	link_profile_members
GROUP BY
	dc
;

CREATE OR REPLACE VIEW
	view_cd_type
AS SELECT
	core_conceptual_domain.id AS id,
	cd_type.type AS "type"
FROM
	core_conceptual_domain
JOIN
(
	SELECT
		core_open_conceptual_domain.id AS id,
		'open' AS "type"
	FROM
		core_open_conceptual_domain

	UNION

	SELECT
		core_conceptual_domain_rule.id AS id,
		'constrained' AS "type"
	FROM
		core_conceptual_domain_rule

	UNION

	SELECT
		core_value_domain.id AS id,
		'closed' AS "type"
	FROM
		core_value_domain
) AS cd_type
ON
	cd_type.id = core_conceptual_domain.id
;

CREATE OR REPLACE VIEW
	view_data_category
AS SELECT
	core_data_category.id,
	core_data_category.pid,
	core_data_category.name,
	view_owner.id AS owner_id,
	view_owner.name AS owner,
	core_data_category.identifier AS identifier,
	core_data_category.version AS version,
	core_data_category.branch AS branch,
	core_data_category.revision AS revision,
	core_data_category.sidebranches AS sidebranches,
	core_data_category.administration_status,
	core_data_category.registration_status,
	core_data_category.justification,
	core_data_category.origin,
	core_data_category.effective_date,
	core_data_category.until_date,
	core_data_category.previous,
	core_data_category.next,
	core_data_category.private AS private,
	view_dc_type.type AS type,
--	view_dc_profiles.profiles AS profiles,
	core_data_category.check,
	link_simple_data_category_super.super AS super
FROM
	core_data_category
JOIN
	view_owner
ON
	view_owner.id = core_data_category.owner 
JOIN
	view_dc_type
ON
	view_dc_type.id = core_data_category.id
--JOIN
--	view_dc_profiles
--ON
--	view_dc_profiles.id = core_data_category.id
LEFT OUTER JOIN
	link_simple_data_category_super
ON
	link_simple_data_category_super.dc = core_data_category.id
;

CREATE OR REPLACE VIEW
	view_dcs_dc
AS SELECT
	dcs.dcs AS dcs,
	dcs.dc AS dc,
	core_data_category.owner AS owner
FROM
(
	SELECT
		core_selected_data_category.dcs AS dcs,
		COALESCE(core_selected_container_data_category.dc, core_selected_complex_data_category.dc, core_selected_simple_data_category.dc, NULL) AS dc
	FROM
		core_selected_data_category
	LEFT OUTER JOIN
		core_selected_container_data_category
	ON
		core_selected_container_data_category.id = core_selected_data_category.id
	LEFT OUTER JOIN
		core_selected_complex_data_category
	ON
		core_selected_complex_data_category.id = core_selected_data_category.id
	LEFT OUTER JOIN
		core_selected_simple_data_category
	ON
		core_selected_simple_data_category.id = core_selected_data_category.id
) AS dcs
JOIN
	core_data_category
ON
	core_data_category.id = dcs.dc
;

CREATE OR REPLACE VIEW
	view_dcs
AS SELECT
	dcs.dcs AS dcs,
	dcs.dc AS dc,
	core_data_category.pid AS pid,
	core_data_category.name AS name,
	view_owner.id AS owner_id,
	view_owner.name AS owner,
	core_data_category.identifier AS identifier,
	core_data_category.version AS version,
	core_data_category.branch AS branch,
	core_data_category.revision AS revision,
	core_data_category.sidebranches AS sidebranches,
	core_data_category.administration_status,
	core_data_category.registration_status,
	core_data_category.justification,
	core_data_category.origin,
	core_data_category.effective_date,
	core_data_category.until_date,
	core_data_category.previous,
	core_data_category.next,
	core_data_category.private AS private,
	view_dc_type.type AS type,
	core_data_category.check,
	link_simple_data_category_super.super AS super
FROM
(
	SELECT
		core_selected_data_category.dcs AS dcs,
		COALESCE(core_selected_complex_data_category.dc, core_selected_simple_data_category.dc, core_selected_container_data_category.dc, NULL) AS dc
	FROM
		core_selected_data_category
	LEFT OUTER JOIN
		core_selected_container_data_category
	ON
		core_selected_container_data_category.id = core_selected_data_category.id
	LEFT OUTER JOIN
		core_selected_complex_data_category
	ON
		core_selected_complex_data_category.id = core_selected_data_category.id
	LEFT OUTER JOIN
		core_selected_simple_data_category
	ON
		core_selected_simple_data_category.id = core_selected_data_category.id
) AS dcs
JOIN
	core_data_category
ON
	core_data_category.id = dcs.dc
JOIN
	view_owner
ON
	view_owner.id = core_data_category.owner 
JOIN
	view_dc_type
ON
	view_dc_type.id = core_data_category.id
LEFT OUTER JOIN
	link_simple_data_category_super
ON
	link_simple_data_category_super.dc = core_data_category.id
;

CREATE OR REPLACE VIEW
	view_profile
AS SELECT
	link_profile_members.profile AS profile,
	link_profile_members.dc AS dc,
	core_data_category.pid AS pid,
	core_data_category.name AS name,
	view_owner.id AS owner_id,
	view_owner.name AS owner,
	core_data_category.identifier AS identifier,
	core_data_category.version AS version,
	core_data_category.administration_status,
	core_data_category.registration_status,
	core_data_category.justification,
	core_data_category.origin,
	core_data_category.effective_date,
	core_data_category.until_date,
	core_data_category.previous,
	core_data_category.private AS private,
	view_dc_type.type AS type,
	core_data_category.check,
	link_simple_data_category_super.super AS super
FROM
	link_profile_members
JOIN
	core_data_category
ON
	core_data_category.id = link_profile_members.dc
JOIN
	view_owner
ON
	view_owner.id = core_data_category.owner 
JOIN
	view_dc_type
ON
	view_dc_type.id = core_data_category.id
LEFT OUTER JOIN
	link_simple_data_category_super
ON
	link_simple_data_category_super.dc = core_data_category.id
;

CREATE OR REPLACE VIEW
	view_text
AS SELECT
	generic_text.id,
	owner,
	generic_text.owner_type,
	language,
	date,
	timestamp,
	source,
	text,
	generic_text.text_type,
	"user"
FROM
	generic_text
;

CREATE OR REPLACE VIEW
	view_name
AS SELECT
	generic_name.id,
	owner,
	generic_name.name_type,
	source,
	generic_name.status,
	name,
	generic_name.owner_type
FROM
	generic_name
;

CREATE OR REPLACE VIEW
	view_message
AS SELECT
	core_message.id,
	core_message.from,
	core_message.to,
	core_message.subject,
	core_message.message,
	core_message.created,
	core_message.read,
	core_message.type,
	core_message.status
FROM
	core_message
;

CREATE OR REPLACE VIEW
	view_dcs_access
AS SELECT
  access.dcs,
  access.account,
  access.type,
  MAX(access.rights) AS rights,
  MAX(access.propagate) AS propagate
FROM
(
( -- the owner with access rights
SELECT
	id AS dcs,
	"owner" AS account,
	'rud'::access_rights_enum AS rights,
	NULL AS propagate,
	'owner' AS "type"
FROM
	core_data_category_selection
)
UNION
( -- all users with access rights
SELECT
	link_dcs_access.*,
	'user' AS "type"
FROM
	link_dcs_access
JOIN
	core_user
ON
	link_dcs_access.account = core_user.id
)
UNION
( -- all groups with access rights
SELECT
	link_dcs_access.*,
	'group' AS "type"
FROM
	link_dcs_access
JOIN
	core_group
ON
	link_dcs_access.account = core_group.id
)
UNION
( -- all group members with access rights
SELECT
	link_dcs_access.dcs,
	link_group_members.member,
	link_dcs_access.rights,
	link_dcs_access.propagate,
	'user' AS "type"
FROM
	link_group_members
JOIN
	core_group
ON
	link_group_members.group = core_group.id
JOIN
	link_dcs_access
ON
	link_dcs_access.account = core_group.id
WHERE
	link_group_members.status = 'accepted'
)
) AS access
GROUP BY
  access.dcs,
  access.account,
  access.type
;

CREATE OR REPLACE VIEW
	view_dc_access
AS SELECT access.dc, access.account, access.type, max(access.rights) AS rights
   FROM (
           (
		SELECT id AS dc,"owner" AS account, 'ru'::access_rights_enum AS rights, 'owner' AS type
		FROM core_data_category
           )
        UNION
           ( -- user/group access rights directly on the DC
                   (
			SELECT link_dc_access.dc, link_dc_access.account, link_dc_access.rights, 'user' AS type
                          FROM link_dc_access
			  JOIN core_user ON link_dc_access.account = core_user.id
                    UNION 
                        SELECT link_dc_access.dc, link_dc_access.account, link_dc_access.rights, 'group' AS type
                          FROM link_dc_access
                          JOIN core_group ON link_dc_access.account = core_group.id
                   )
                   UNION -- group members access rights directly on the DC
                         SELECT link_dc_access.dc, link_group_members.member AS account, link_dc_access.rights, 'user' AS type
                           FROM link_group_members
			   JOIN core_group ON link_group_members."group" = core_group.id
                           JOIN link_dc_access ON link_dc_access.account = core_group.id
                           WHERE link_group_members.status::text = 'accepted'::text
           )
        UNION -- user/group access indirectly, i.e., by a DCS, on the DC
		SELECT oig.dc, view_dcs_access.account, view_dcs_access.propagate, view_dcs_access.type
		  FROM view_dcs_access
		  JOIN (
			SELECT DISTINCT view_dcs_dc.dcs, view_dcs_dc.dc
			  FROM view_dcs_dc
			  JOIN link_dcs_access ON link_dcs_access.dcs = view_dcs_dc.dcs AND link_dcs_access.propagate IS NOT NULL -- the access rights of the DCS indicate rights propagation
			  JOIN link_group_members ON link_group_members.member = view_dcs_dc.owner AND link_group_members."group" = link_dcs_access.account -- the owner of DC is a member of a group and the group has access to the DCS
			  WHERE link_group_members.status::text = 'accepted'::text -- only accepted members incl. owner play a role
		  ) oig ON oig.dcs = view_dcs_access.dcs  AND view_dcs_access.type != 'owner' -- skip group owners, they are also included as users
  ) access
  GROUP BY access.dc, access.account, access.type;
;


CREATE OR REPLACE VIEW
  view_link_dcs_access
AS SELECT
  link_dcs_access.*,
  core_data_category_selection.name
FROM
  link_dcs_access
JOIN
  core_data_category_selection
ON
  link_dcs_access.dcs = core_data_category_selection.id
;

CREATE OR REPLACE VIEW
  view_cr_submission_group
AS SELECT
  core_change_request.id AS cr,
  link_group_members.member,
  link_group_members.role
FROM
  core_change_request
JOIN
  link_group_members
ON
  link_group_members.group = core_change_request.submission_group
;

CREATE OR REPLACE VIEW
  view_cr_decision_group_tdg
AS SELECT
  core_change_request.id AS cr,
  link_group_members.member,
  link_group_members.role
FROM
  core_change_request
JOIN
  link_group_members
ON
  link_group_members.group = core_change_request.decision_group_tdg
;

CREATE OR REPLACE VIEW
  view_cr_decision_group_dcrb
AS SELECT
  core_change_request.id AS cr,
  link_group_members.member,
  link_group_members.role
FROM
  core_change_request
JOIN
  link_group_members
ON
  link_group_members.group = core_change_request.decision_group_dcrb
;