DROP FUNCTION IF EXISTS get_dc_identifier(dcid integer);
CREATE OR REPLACE FUNCTION get_dc_identifier(dcid integer) RETURNS varchar AS $$
DECLARE
	dc RECORD;
BEGIN
	SELECT
		*
	INTO
		dc
	FROM
		core_data_category
	WHERE
		core_data_category.id = dcid
	;
	RETURN dc.identifier;
END;
$$ LANGUAGE plpgsql;

DROP FUNCTION IF EXISTS get_dc_name_status(dcid integer,dcstatus varchar);
CREATE OR REPLACE FUNCTION get_dc_name_status(dcid integer,dcstatus varchar) RETURNS varchar AS $$
DECLARE
	dc RECORD;
BEGIN
	SELECT
		*
	INTO
		dc
	FROM
		view_dc_name
	WHERE
		view_dc_name.id = dcid
	  AND
		view_dc_name.status = dcstatus
	  AND
		view_dc_name.name IS NOT NULL
	  AND
		view_dc_name.name != ''
	;
	RETURN dc.name;
END;
$$ LANGUAGE plpgsql;

DROP FUNCTION IF EXISTS get_dc_name_status_language(dcid integer,dcstatus varchar,lang varchar);
CREATE OR REPLACE FUNCTION get_dc_name_status_language(dcid integer,dcstatus varchar,lang varchar) RETURNS varchar AS $$
DECLARE
	dc RECORD;
BEGIN
	SELECT
		*
	INTO
		dc
	FROM
		view_dc_name
	WHERE
		view_dc_name.id = dcid
	  AND
		view_dc_name.status = dcstatus
	  AND
		view_dc_name.name IS NOT NULL
	  AND
		view_dc_name.name != ''
	  AND
		view_dc_name.tag = lang
	;
	RETURN dc.name;
END;
$$ LANGUAGE plpgsql;

DROP FUNCTION IF EXISTS get_dc_name(dcid integer) CASCADE;
CREATE OR REPLACE FUNCTION get_dc_name(dcid integer) RETURNS varchar AS $$
DECLARE
	datcat RECORD;
BEGIN
	SELECT
		core_data_category.id AS dc,
		COALESCE(get_dc_name_status_language(dcid,'preferred name','en'), get_dc_name_status_language(dcid,'standardized name','en'), get_dc_name_status_language(dcid,'admitted name','en'), identifier, 'ERROR') AS name
	INTO
		datcat
	FROM
		core_data_category
	WHERE
		core_data_category.id = dcid
	;
	RETURN datcat.name;
END;
$$ LANGUAGE plpgsql;

DROP FUNCTION IF EXISTS profile_path(prof integer) CASCADE;
CREATE OR REPLACE FUNCTION profile_path(prof integer) RETURNS SETOF integer AS $$
DECLARE
    parent RECORD;
    ancestor RECORD;
BEGIN
    SELECT
       *
    INTO
       parent
    FROM
       core_profile
    WHERE
       id =  prof
    ;
    IF 
       parent.parent IS NOT NULL
    THEN
       FOR ancestor IN SELECT * FROM profile_path(parent.parent) LOOP
          RETURN NEXT ancestor.profile_path;
       END LOOP;
    END IF;
    RETURN NEXT prof;
END;
$$ LANGUAGE plpgsql;

DROP FUNCTION IF EXISTS profile_self_or_descendants(prof integer) CASCADE;
CREATE OR REPLACE FUNCTION profile_self_or_descendants(prof integer) RETURNS SETOF integer AS $$
DECLARE
    child RECORD;
    descendant RECORD;
BEGIN
    FOR child IN SELECT * FROM core_profile WHERE parent=prof LOOP
       FOR descendant IN SELECT * FROM profile_self_or_descendants(child.id) LOOP
          RETURN NEXT descendant.profile_self_or_descendants;
       END LOOP;
    END LOOP;
    RETURN NEXT prof;
END;
$$ LANGUAGE plpgsql;

DROP FUNCTION IF EXISTS dc_profiles(dcid integer) CASCADE;
CREATE OR REPLACE FUNCTION dc_profiles(dcid integer) RETURNS SETOF integer AS $$
DECLARE
    profile RECORD;
    path RECORD;
BEGIN
    FOR profile IN SELECT DISTINCT link_profile_members.profile FROM link_profile_members WHERE link_profile_members.dc = dcid LOOP
       FOR path IN SELECT * FROM profile_path(profile.profile) LOOP
          RETURN NEXT path.profile_path;
       END LOOP;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

DROP FUNCTION IF EXISTS dcs_self_or_descendants(dcs integer) CASCADE;
CREATE OR REPLACE FUNCTION dcs_self_or_descendants(dcs integer) RETURNS SETOF integer AS $$
DECLARE
    child RECORD;
    descendant RECORD;
BEGIN
    FOR child IN SELECT * FROM core_data_category_selection WHERE parent=dcs LOOP
       FOR descendant IN SELECT * FROM dcs_self_or_descendants(child.id) LOOP
          RETURN NEXT descendant.dcs_self_or_descendants;
       END LOOP;
    END LOOP;
    RETURN NEXT dcs;
END;
$$ LANGUAGE plpgsql;

DROP FUNCTION IF EXISTS user_name(uid integer) CASCADE;
CREATE OR REPLACE FUNCTION user_name(uid integer) RETURNS varchar AS $$
DECLARE
    profile RECORD;
    ancestor RECORD;
BEGIN
    SELECT
       *
    INTO
       profile
    FROM
       core_user
    WHERE
       id =  uid
    ;
    IF
    	profile.last_name IS NOT NULL AND TRIM(profile.last_name) != ''
    THEN
	    IF 
	       profile.first_name IS NOT NULL AND TRIM(profile.first_name) != '' 
	    THEN
	    	RETURN TRIM(profile.last_name) || ', ' || TRIM(profile.first_name);
	    ELSE
	    	RETURN TRIM(profile.last_name);
	    END IF;
    ELSE
    	RETURN profile.login;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION concat(text, text) RETURNS text
   immutable
   language sql
AS '
SELECT CASE WHEN $1 = '''' THEN $2 ELSE $1 || '' '' || $2 END
';

DROP AGGREGATE IF EXISTS concat(text) CASCADE;
CREATE AGGREGATE concat 
(
   basetype = text,
   stype = text,
   sfunc = concat,
   initcond = ''
);

DROP FUNCTION IF EXISTS get_dc_type(dc integer) CASCADE;
CREATE OR REPLACE FUNCTION get_dc_type(dc integer) RETURNS varchar AS $$
DECLARE
	datcat RECORD;
BEGIN
	SELECT
		id
	FROM
		core_simple_data_category
	WHERE
		id =  dc
	INTO
		datcat
	;
	IF
		datcat IS NULL
	THEN
		SELECT
			id
		FROM
			core_container_data_category
		WHERE
			id =  dc
		INTO
			datcat
		;
		IF
			datcat IS NULL
		THEN
			SELECT
				id
			FROM
				core_closed_data_category
			WHERE
				id = dc
			INTO
				datcat
			;
			IF
				datcat IS NULL
			THEN
				SELECT
					id
				FROM
					core_constrained_data_category
				WHERE
					id = dc
				INTO
					datcat
				;
				IF
					datcat IS NULL
				THEN
					SELECT
						id
					FROM
						core_open_data_category
					WHERE
						id = dc
					INTO
						datcat
					;
					IF
						datcat IS NULL
					THEN
						RETURN 'unknown';
					ELSE
						RETURN 'open';
					END IF;
				ELSE
					RETURN 'constrained';
				END IF;
			ELSE
				RETURN 'closed';
			END IF;
		ELSE
			RETURN 'container';
		END IF;
	ELSE
		RETURN 'simple';
	END IF;
END;
$$ LANGUAGE plpgsql;

DROP FUNCTION IF EXISTS merge_accounts(keep integer, remove integer) CASCADE;
CREATE OR REPLACE FUNCTION merge_accounts(keep integer, remove integer) RETURNS integer AS $$
DECLARE
BEGIN
	-- owned DCs
	UPDATE core_data_category SET owner = keep WHERE owner = remove;
	-- owned DCSs
	UPDATE core_data_category_selection SET owner = keep WHERE owner = remove;
	-- owned groups
	UPDATE core_adhoc_group SET owner = keep WHERE owner = remove;
	-- owned submissions
	UPDATE core_submission_group SET owner = keep WHERE owner = remove;
	-- shared DCs
	DELETE FROM
		link_dc_access
	USING
		link_dc_access AS checker
	WHERE
		link_dc_access.dc = checker.dc
	AND
		checker.account = keep
	AND
		link_dc_access.account = remove
	;
	UPDATE link_dc_access SET account = keep WHERE account = remove;
	-- shared DCSs
	DELETE FROM
		link_dcs_access
	USING
		link_dcs_access AS checker
	WHERE
		link_dcs_access.dcs = checker.dcs
	AND
		checker.account = keep
	AND
		link_dcs_access.account = remove
	;
	UPDATE link_dcs_access SET account = keep WHERE account = remove;
	-- votes
	UPDATE core_change_request_vote SET judge = keep WHERE judge = remove;
	-- DC status changes
	UPDATE core_dc_status_log SET "user" = keep WHERE "user" = remove;
	-- messages send
	UPDATE core_message SET "from" = keep WHERE "from" = remove;
	-- messages received
	UPDATE core_message SET "to" = keep WHERE "to" = remove;
	-- texts
	UPDATE generic_text SET "user" = keep WHERE "user" = remove;
	-- group memberships
	DELETE FROM
		link_group_members
	USING
		link_group_members AS checker
	WHERE
		link_group_members."group" = checker."group"
	AND
		checker.member= keep
	AND
		link_group_members.member = remove
	;
	UPDATE link_group_members SET member = keep WHERE member = remove;

	-- SYNTAX residu
	UPDATE link_data_category_selection_users SET "user" = keep WHERE "user" = remove;

	-- delete owner
	DELETE FROM core_owner WHERE id = remove;
	-- delete account
	DELETE FROM core_account WHERE id = remove;
	RETURN keep;
END;
$$ LANGUAGE plpgsql;

DROP FUNCTION IF EXISTS dc_super_cycle(path integer[],super integer) CASCADE;
CREATE OR REPLACE FUNCTION dc_super_cycle(path integer[],super integer) RETURNS boolean AS $$
DECLARE
    isa RECORD;
BEGIN
    IF
        (path @> ARRAY[super])
    THEN
	RAISE NOTICE 'dc_super_cycle(path[%],super[%]) found a cycle',path,super;
        RETURN true;
    ELSE
        SELECT
		  *
        FROM
		  link_simple_data_category_super
        WHERE
		  dc = dc_super_cycle.super
	INTO
		  isa
	;
	IF
	   isa IS NULL
	THEN
	   RETURN false;
	ELSE
	   RETURN dc_super_cycle(path || super,isa.super);
	END IF;
    END IF;
END;
$$ LANGUAGE plpgsql;
