jbe@253: BEGIN;
jbe@253: 
jbe@253: CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@253:   SELECT * FROM (VALUES ('2.0.10', 2, 0, 10))
jbe@253:   AS "subquery"("string", "major", "minor", "revision");
jbe@253: 
jbe@253: DROP FUNCTION "delegation_info"
jbe@253:   ( "member"."id"%TYPE,
jbe@253:     "unit"."id"%TYPE,
jbe@253:     "area"."id"%TYPE,
jbe@253:     "issue"."id"%TYPE,
jbe@253:     "member"."id"%TYPE );
jbe@253: 
jbe@253: DROP TYPE "delegation_info_type";
jbe@253: 
jbe@253: 
jbe@253: CREATE TYPE "delegation_info_type" AS (
jbe@253:         "own_participation"           BOOLEAN,
jbe@253:         "own_delegation_scope"        "delegation_scope",
jbe@253:         "first_trustee_id"            INT4,
jbe@253:         "first_trustee_participation" BOOLEAN,
jbe@253:         "first_trustee_ellipsis"      BOOLEAN,
jbe@253:         "other_trustee_id"            INT4,
jbe@253:         "other_trustee_participation" BOOLEAN,
jbe@253:         "other_trustee_ellipsis"      BOOLEAN,
jbe@253:         "delegation_loop"             "delegation_info_loop_type",
jbe@253:         "participating_member_id"     INT4 );
jbe@253: 
jbe@253: COMMENT ON TYPE "delegation_info_type" IS 'Type of result returned by "delegation_info" function; For meaning of "participation" check comment on "delegation_chain_row" type';
jbe@253: 
jbe@253: COMMENT ON COLUMN "delegation_info_type"."own_participation"           IS 'Member is directly participating';
jbe@253: COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope"        IS 'Delegation scope of member';
jbe@253: COMMENT ON COLUMN "delegation_info_type"."first_trustee_id"            IS 'Direct trustee of member';
jbe@253: COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating';
jbe@253: COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis"      IS 'Ellipsis in delegation chain after "first_trustee"';
jbe@253: COMMENT ON COLUMN "delegation_info_type"."other_trustee_id"            IS 'Another relevant trustee (due to participation)';
jbe@253: COMMENT ON COLUMN "delegation_info_type"."other_trustee_participation" IS 'Another trustee is participating (redundant field: if "other_trustee_id" is set, then "other_trustee_participation" is always TRUE, else "other_trustee_participation" is NULL)';
jbe@253: COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis"      IS 'Ellipsis in delegation chain after "other_trustee"';
jbe@253: COMMENT ON COLUMN "delegation_info_type"."delegation_loop"             IS 'Non-NULL value, if delegation chain contains a circle; See comment on "delegation_info_loop_type" for details';
jbe@253: COMMENT ON COLUMN "delegation_info_type"."participating_member_id"     IS 'First participating member in delegation chain';
jbe@253: 
jbe@253: 
jbe@253: CREATE FUNCTION "delegation_info"
jbe@253:   ( "member_id_p"           "member"."id"%TYPE,
jbe@253:     "unit_id_p"             "unit"."id"%TYPE,
jbe@253:     "area_id_p"             "area"."id"%TYPE,
jbe@253:     "issue_id_p"            "issue"."id"%TYPE,
jbe@253:     "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL )
jbe@253:   RETURNS "delegation_info_type"
jbe@253:   LANGUAGE 'plpgsql' STABLE AS $$
jbe@253:     DECLARE
jbe@253:       "current_row" "delegation_chain_row";
jbe@253:       "result"      "delegation_info_type";
jbe@253:     BEGIN
jbe@253:       "result"."own_participation" := FALSE;
jbe@253:       FOR "current_row" IN
jbe@253:         SELECT * FROM "delegation_chain"(
jbe@253:           "member_id_p",
jbe@253:           "unit_id_p", "area_id_p", "issue_id_p",
jbe@253:           "simulate_trustee_id_p")
jbe@253:       LOOP
jbe@253:         IF
jbe@253:           "result"."participating_member_id" ISNULL AND
jbe@253:           "current_row"."participation"
jbe@253:         THEN
jbe@253:           "result"."participating_member_id" := "current_row"."member_id";
jbe@253:         END IF;
jbe@253:         IF "current_row"."member_id" = "member_id_p" THEN
jbe@253:           "result"."own_participation"    := "current_row"."participation";
jbe@253:           "result"."own_delegation_scope" := "current_row"."scope_out";
jbe@253:           IF "current_row"."loop" = 'first' THEN
jbe@253:             "result"."delegation_loop" := 'own';
jbe@253:           END IF;
jbe@253:         ELSIF
jbe@253:           "current_row"."member_valid" AND
jbe@253:           ( "current_row"."loop" ISNULL OR
jbe@253:             "current_row"."loop" != 'repetition' )
jbe@253:         THEN
jbe@253:           IF "result"."first_trustee_id" ISNULL THEN
jbe@253:             "result"."first_trustee_id"            := "current_row"."member_id";
jbe@253:             "result"."first_trustee_participation" := "current_row"."participation";
jbe@253:             "result"."first_trustee_ellipsis"      := FALSE;
jbe@253:             IF "current_row"."loop" = 'first' THEN
jbe@253:               "result"."delegation_loop" := 'first';
jbe@253:             END IF;
jbe@253:           ELSIF "result"."other_trustee_id" ISNULL THEN
jbe@253:             IF "current_row"."participation" AND NOT "current_row"."overridden" THEN
jbe@253:               "result"."other_trustee_id"            := "current_row"."member_id";
jbe@253:               "result"."other_trustee_participation" := TRUE;
jbe@253:               "result"."other_trustee_ellipsis"      := FALSE;
jbe@253:               IF "current_row"."loop" = 'first' THEN
jbe@253:                 "result"."delegation_loop" := 'other';
jbe@253:               END IF;
jbe@253:             ELSE
jbe@253:               "result"."first_trustee_ellipsis" := TRUE;
jbe@253:               IF "current_row"."loop" = 'first' THEN
jbe@253:                 "result"."delegation_loop" := 'first_ellipsis';
jbe@253:               END IF;
jbe@253:             END IF;
jbe@253:           ELSE
jbe@253:             "result"."other_trustee_ellipsis" := TRUE;
jbe@253:             IF "current_row"."loop" = 'first' THEN
jbe@253:               "result"."delegation_loop" := 'other_ellipsis';
jbe@253:             END IF;
jbe@253:           END IF;
jbe@253:         END IF;
jbe@253:       END LOOP;
jbe@253:       RETURN "result";
jbe@253:     END;
jbe@253:   $$;
jbe@253: 
jbe@253: COMMENT ON FUNCTION "delegation_info"
jbe@253:   ( "member"."id"%TYPE,
jbe@253:     "unit"."id"%TYPE,
jbe@253:     "area"."id"%TYPE,
jbe@253:     "issue"."id"%TYPE,
jbe@253:     "member"."id"%TYPE )
jbe@253:   IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information';
jbe@253: 
jbe@253: 
jbe@253: COMMIT;