jbe@256: BEGIN;
jbe@256: 
jbe@256: CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@256:   SELECT * FROM (VALUES ('2.0.11', 2, 0, 11))
jbe@256:   AS "subquery"("string", "major", "minor", "revision");
jbe@256: 
jbe@256: DROP FUNCTION "delegation_info"
jbe@256:   ( "member"."id"%TYPE,
jbe@256:     "unit"."id"%TYPE,
jbe@256:     "area"."id"%TYPE,
jbe@256:     "issue"."id"%TYPE,
jbe@256:     "member"."id"%TYPE );
jbe@256: 
jbe@256: DROP FUNCTION "delegation_chain"
jbe@256:   ( "member"."id"%TYPE,
jbe@256:     "unit"."id"%TYPE,
jbe@256:     "area"."id"%TYPE,
jbe@256:     "issue"."id"%TYPE,
jbe@256:     "member"."id"%TYPE );
jbe@256: 
jbe@256: CREATE FUNCTION "delegation_chain"
jbe@256:   ( "member_id_p"           "member"."id"%TYPE,
jbe@256:     "unit_id_p"             "unit"."id"%TYPE,
jbe@256:     "area_id_p"             "area"."id"%TYPE,
jbe@256:     "issue_id_p"            "issue"."id"%TYPE,
jbe@256:     "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
jbe@256:     "simulate_default_p"    BOOLEAN            DEFAULT FALSE )
jbe@256:   RETURNS SETOF "delegation_chain_row"
jbe@256:   LANGUAGE 'plpgsql' STABLE AS $$
jbe@256:     DECLARE
jbe@256:       "scope_v"            "delegation_scope";
jbe@256:       "unit_id_v"          "unit"."id"%TYPE;
jbe@256:       "area_id_v"          "area"."id"%TYPE;
jbe@256:       "issue_row"          "issue"%ROWTYPE;
jbe@256:       "visited_member_ids" INT4[];  -- "member"."id"%TYPE[]
jbe@256:       "loop_member_id_v"   "member"."id"%TYPE;
jbe@256:       "output_row"         "delegation_chain_row";
jbe@256:       "output_rows"        "delegation_chain_row"[];
jbe@256:       "simulate_v"         BOOLEAN;
jbe@256:       "simulate_here_v"    BOOLEAN;
jbe@256:       "delegation_row"     "delegation"%ROWTYPE;
jbe@256:       "row_count"          INT4;
jbe@256:       "i"                  INT4;
jbe@256:       "loop_v"             BOOLEAN;
jbe@256:     BEGIN
jbe@256:       IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN
jbe@256:         RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';
jbe@256:       END IF;
jbe@256:       IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN
jbe@256:         "simulate_v" := TRUE;
jbe@256:       ELSE
jbe@256:         "simulate_v" := FALSE;
jbe@256:       END IF;
jbe@256:       IF
jbe@256:         "unit_id_p" NOTNULL AND
jbe@256:         "area_id_p" ISNULL AND
jbe@256:         "issue_id_p" ISNULL
jbe@256:       THEN
jbe@256:         "scope_v" := 'unit';
jbe@256:         "unit_id_v" := "unit_id_p";
jbe@256:       ELSIF
jbe@256:         "unit_id_p" ISNULL AND
jbe@256:         "area_id_p" NOTNULL AND
jbe@256:         "issue_id_p" ISNULL
jbe@256:       THEN
jbe@256:         "scope_v" := 'area';
jbe@256:         "area_id_v" := "area_id_p";
jbe@256:         SELECT "unit_id" INTO "unit_id_v"
jbe@256:           FROM "area" WHERE "id" = "area_id_v";
jbe@256:       ELSIF
jbe@256:         "unit_id_p" ISNULL AND
jbe@256:         "area_id_p" ISNULL AND
jbe@256:         "issue_id_p" NOTNULL
jbe@256:       THEN
jbe@256:         SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
jbe@256:         IF "issue_row"."id" ISNULL THEN
jbe@256:           RETURN;
jbe@256:         END IF;
jbe@256:         IF "issue_row"."closed" NOTNULL THEN
jbe@256:           IF "simulate_v" THEN
jbe@256:             RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
jbe@256:           END IF;
jbe@256:           FOR "output_row" IN
jbe@256:             SELECT * FROM
jbe@256:             "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
jbe@256:           LOOP
jbe@256:             RETURN NEXT "output_row";
jbe@256:           END LOOP;
jbe@256:           RETURN;
jbe@256:         END IF;
jbe@256:         "scope_v" := 'issue';
jbe@256:         SELECT "area_id" INTO "area_id_v"
jbe@256:           FROM "issue" WHERE "id" = "issue_id_p";
jbe@256:         SELECT "unit_id" INTO "unit_id_v"
jbe@256:           FROM "area"  WHERE "id" = "area_id_v";
jbe@256:       ELSE
jbe@256:         RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
jbe@256:       END IF;
jbe@256:       "visited_member_ids" := '{}';
jbe@256:       "loop_member_id_v"   := NULL;
jbe@256:       "output_rows"        := '{}';
jbe@256:       "output_row"."index"         := 0;
jbe@256:       "output_row"."member_id"     := "member_id_p";
jbe@256:       "output_row"."member_valid"  := TRUE;
jbe@256:       "output_row"."participation" := FALSE;
jbe@256:       "output_row"."overridden"    := FALSE;
jbe@256:       "output_row"."disabled_out"  := FALSE;
jbe@256:       "output_row"."scope_out"     := NULL;
jbe@256:       LOOP
jbe@256:         IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
jbe@256:           "loop_member_id_v" := "output_row"."member_id";
jbe@256:         ELSE
jbe@256:           "visited_member_ids" :=
jbe@256:             "visited_member_ids" || "output_row"."member_id";
jbe@256:         END IF;
jbe@256:         IF "output_row"."participation" ISNULL THEN
jbe@256:           "output_row"."overridden" := NULL;
jbe@256:         ELSIF "output_row"."participation" THEN
jbe@256:           "output_row"."overridden" := TRUE;
jbe@256:         END IF;
jbe@256:         "output_row"."scope_in" := "output_row"."scope_out";
jbe@256:         "output_row"."member_valid" := EXISTS (
jbe@256:           SELECT NULL FROM "member" JOIN "privilege"
jbe@256:           ON "privilege"."member_id" = "member"."id"
jbe@256:           AND "privilege"."unit_id" = "unit_id_v"
jbe@256:           WHERE "id" = "output_row"."member_id"
jbe@256:           AND "member"."active" AND "privilege"."voting_right"
jbe@256:         );
jbe@256:         "simulate_here_v" := (
jbe@256:           "simulate_v" AND
jbe@256:           "output_row"."member_id" = "member_id_p"
jbe@256:         );
jbe@256:         "delegation_row" := ROW(NULL);
jbe@256:         IF "output_row"."member_valid" OR "simulate_here_v" THEN
jbe@256:           IF "scope_v" = 'unit' THEN
jbe@256:             IF NOT "simulate_here_v" THEN
jbe@256:               SELECT * INTO "delegation_row" FROM "delegation"
jbe@256:                 WHERE "truster_id" = "output_row"."member_id"
jbe@256:                 AND "unit_id" = "unit_id_v";
jbe@256:             END IF;
jbe@256:           ELSIF "scope_v" = 'area' THEN
jbe@256:             "output_row"."participation" := EXISTS (
jbe@256:               SELECT NULL FROM "membership"
jbe@256:               WHERE "area_id" = "area_id_p"
jbe@256:               AND "member_id" = "output_row"."member_id"
jbe@256:             );
jbe@256:             IF "simulate_here_v" THEN
jbe@256:               IF "simulate_trustee_id_p" ISNULL THEN
jbe@256:                 SELECT * INTO "delegation_row" FROM "delegation"
jbe@256:                   WHERE "truster_id" = "output_row"."member_id"
jbe@256:                   AND "unit_id" = "unit_id_v";
jbe@256:               END IF;
jbe@256:             ELSE
jbe@256:               SELECT * INTO "delegation_row" FROM "delegation"
jbe@256:                 WHERE "truster_id" = "output_row"."member_id"
jbe@256:                 AND (
jbe@256:                   "unit_id" = "unit_id_v" OR
jbe@256:                   "area_id" = "area_id_v"
jbe@256:                 )
jbe@256:                 ORDER BY "scope" DESC;
jbe@256:             END IF;
jbe@256:           ELSIF "scope_v" = 'issue' THEN
jbe@256:             IF "issue_row"."fully_frozen" ISNULL THEN
jbe@256:               "output_row"."participation" := EXISTS (
jbe@256:                 SELECT NULL FROM "interest"
jbe@256:                 WHERE "issue_id" = "issue_id_p"
jbe@256:                 AND "member_id" = "output_row"."member_id"
jbe@256:               );
jbe@256:             ELSE
jbe@256:               IF "output_row"."member_id" = "member_id_p" THEN
jbe@256:                 "output_row"."participation" := EXISTS (
jbe@256:                   SELECT NULL FROM "direct_voter"
jbe@256:                   WHERE "issue_id" = "issue_id_p"
jbe@256:                   AND "member_id" = "output_row"."member_id"
jbe@256:                 );
jbe@256:               ELSE
jbe@256:                 "output_row"."participation" := NULL;
jbe@256:               END IF;
jbe@256:             END IF;
jbe@256:             IF "simulate_here_v" THEN
jbe@256:               IF "simulate_trustee_id_p" ISNULL THEN
jbe@256:                 SELECT * INTO "delegation_row" FROM "delegation"
jbe@256:                   WHERE "truster_id" = "output_row"."member_id"
jbe@256:                   AND (
jbe@256:                     "unit_id" = "unit_id_v" OR
jbe@256:                     "area_id" = "area_id_v"
jbe@256:                   )
jbe@256:                   ORDER BY "scope" DESC;
jbe@256:               END IF;
jbe@256:             ELSE
jbe@256:               SELECT * INTO "delegation_row" FROM "delegation"
jbe@256:                 WHERE "truster_id" = "output_row"."member_id"
jbe@256:                 AND (
jbe@256:                   "unit_id" = "unit_id_v" OR
jbe@256:                   "area_id" = "area_id_v" OR
jbe@256:                   "issue_id" = "issue_id_p"
jbe@256:                 )
jbe@256:                 ORDER BY "scope" DESC;
jbe@256:             END IF;
jbe@256:           END IF;
jbe@256:         ELSE
jbe@256:           "output_row"."participation" := FALSE;
jbe@256:         END IF;
jbe@256:         IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN
jbe@256:           "output_row"."scope_out" := "scope_v";
jbe@256:           "output_rows" := "output_rows" || "output_row";
jbe@256:           "output_row"."member_id" := "simulate_trustee_id_p";
jbe@256:         ELSIF "delegation_row"."trustee_id" NOTNULL THEN
jbe@256:           "output_row"."scope_out" := "delegation_row"."scope";
jbe@256:           "output_rows" := "output_rows" || "output_row";
jbe@256:           "output_row"."member_id" := "delegation_row"."trustee_id";
jbe@256:         ELSIF "delegation_row"."scope" NOTNULL THEN
jbe@256:           "output_row"."scope_out" := "delegation_row"."scope";
jbe@256:           "output_row"."disabled_out" := TRUE;
jbe@256:           "output_rows" := "output_rows" || "output_row";
jbe@256:           EXIT;
jbe@256:         ELSE
jbe@256:           "output_row"."scope_out" := NULL;
jbe@256:           "output_rows" := "output_rows" || "output_row";
jbe@256:           EXIT;
jbe@256:         END IF;
jbe@256:         EXIT WHEN "loop_member_id_v" NOTNULL;
jbe@256:         "output_row"."index" := "output_row"."index" + 1;
jbe@256:       END LOOP;
jbe@256:       "row_count" := array_upper("output_rows", 1);
jbe@256:       "i"      := 1;
jbe@256:       "loop_v" := FALSE;
jbe@256:       LOOP
jbe@256:         "output_row" := "output_rows"["i"];
jbe@256:         EXIT WHEN "output_row" ISNULL;  -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
jbe@256:         IF "loop_v" THEN
jbe@256:           IF "i" + 1 = "row_count" THEN
jbe@256:             "output_row"."loop" := 'last';
jbe@256:           ELSIF "i" = "row_count" THEN
jbe@256:             "output_row"."loop" := 'repetition';
jbe@256:           ELSE
jbe@256:             "output_row"."loop" := 'intermediate';
jbe@256:           END IF;
jbe@256:         ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
jbe@256:           "output_row"."loop" := 'first';
jbe@256:           "loop_v" := TRUE;
jbe@256:         END IF;
jbe@256:         IF "scope_v" = 'unit' THEN
jbe@256:           "output_row"."participation" := NULL;
jbe@256:         END IF;
jbe@256:         RETURN NEXT "output_row";
jbe@256:         "i" := "i" + 1;
jbe@256:       END LOOP;
jbe@256:       RETURN;
jbe@256:     END;
jbe@256:   $$;
jbe@256: 
jbe@256: COMMENT ON FUNCTION "delegation_chain"
jbe@256:   ( "member"."id"%TYPE,
jbe@256:     "unit"."id"%TYPE,
jbe@256:     "area"."id"%TYPE,
jbe@256:     "issue"."id"%TYPE,
jbe@256:     "member"."id"%TYPE,
jbe@256:     BOOLEAN )
jbe@256:   IS 'Shows a delegation chain for unit, area, or issue; See "delegation_chain_row" type for more information';
jbe@256: 
jbe@256: CREATE FUNCTION "delegation_info"
jbe@256:   ( "member_id_p"           "member"."id"%TYPE,
jbe@256:     "unit_id_p"             "unit"."id"%TYPE,
jbe@256:     "area_id_p"             "area"."id"%TYPE,
jbe@256:     "issue_id_p"            "issue"."id"%TYPE,
jbe@256:     "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
jbe@256:     "simulate_default_p"    BOOLEAN            DEFAULT FALSE )
jbe@256:   RETURNS "delegation_info_type"
jbe@256:   LANGUAGE 'plpgsql' STABLE AS $$
jbe@256:     DECLARE
jbe@256:       "current_row" "delegation_chain_row";
jbe@256:       "result"      "delegation_info_type";
jbe@256:     BEGIN
jbe@256:       "result"."own_participation" := FALSE;
jbe@256:       FOR "current_row" IN
jbe@256:         SELECT * FROM "delegation_chain"(
jbe@256:           "member_id_p",
jbe@256:           "unit_id_p", "area_id_p", "issue_id_p",
jbe@256:           "simulate_trustee_id_p", "simulate_default_p")
jbe@256:       LOOP
jbe@256:         IF
jbe@256:           "result"."participating_member_id" ISNULL AND
jbe@256:           "current_row"."participation"
jbe@256:         THEN
jbe@256:           "result"."participating_member_id" := "current_row"."member_id";
jbe@256:         END IF;
jbe@256:         IF "current_row"."member_id" = "member_id_p" THEN
jbe@256:           "result"."own_participation"    := "current_row"."participation";
jbe@256:           "result"."own_delegation_scope" := "current_row"."scope_out";
jbe@256:           IF "current_row"."loop" = 'first' THEN
jbe@256:             "result"."delegation_loop" := 'own';
jbe@256:           END IF;
jbe@256:         ELSIF
jbe@256:           "current_row"."member_valid" AND
jbe@256:           ( "current_row"."loop" ISNULL OR
jbe@256:             "current_row"."loop" != 'repetition' )
jbe@256:         THEN
jbe@256:           IF "result"."first_trustee_id" ISNULL THEN
jbe@256:             "result"."first_trustee_id"            := "current_row"."member_id";
jbe@256:             "result"."first_trustee_participation" := "current_row"."participation";
jbe@256:             "result"."first_trustee_ellipsis"      := FALSE;
jbe@256:             IF "current_row"."loop" = 'first' THEN
jbe@256:               "result"."delegation_loop" := 'first';
jbe@256:             END IF;
jbe@256:           ELSIF "result"."other_trustee_id" ISNULL THEN
jbe@256:             IF "current_row"."participation" AND NOT "current_row"."overridden" THEN
jbe@256:               "result"."other_trustee_id"            := "current_row"."member_id";
jbe@256:               "result"."other_trustee_participation" := TRUE;
jbe@256:               "result"."other_trustee_ellipsis"      := FALSE;
jbe@256:               IF "current_row"."loop" = 'first' THEN
jbe@256:                 "result"."delegation_loop" := 'other';
jbe@256:               END IF;
jbe@256:             ELSE
jbe@256:               "result"."first_trustee_ellipsis" := TRUE;
jbe@256:               IF "current_row"."loop" = 'first' THEN
jbe@256:                 "result"."delegation_loop" := 'first_ellipsis';
jbe@256:               END IF;
jbe@256:             END IF;
jbe@256:           ELSE
jbe@256:             "result"."other_trustee_ellipsis" := TRUE;
jbe@256:             IF "current_row"."loop" = 'first' THEN
jbe@256:               "result"."delegation_loop" := 'other_ellipsis';
jbe@256:             END IF;
jbe@256:           END IF;
jbe@256:         END IF;
jbe@256:       END LOOP;
jbe@256:       RETURN "result";
jbe@256:     END;
jbe@256:   $$;
jbe@256: 
jbe@256: COMMENT ON FUNCTION "delegation_info"
jbe@256:   ( "member"."id"%TYPE,
jbe@256:     "unit"."id"%TYPE,
jbe@256:     "area"."id"%TYPE,
jbe@256:     "issue"."id"%TYPE,
jbe@256:     "member"."id"%TYPE,
jbe@256:     BOOLEAN )
jbe@256:   IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information';
jbe@256: 
jbe@256: COMMIT;