 
 
 
---------------------------------------------------
--- Set returning function for delegation chains --
---------------------------------------------------
+------------------------------------------------------
+-- Row set returning function for delegation chains --
+------------------------------------------------------
 
 
 CREATE TYPE "delegation_chain_loop_tag" AS ENUM

    "area"."id"%TYPE,
    "issue"."id"%TYPE,
    "member"."id"%TYPE )
  IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';


CREATE FUNCTION "delegation_chain"
  ( "member_id_p" "member"."id"%TYPE,
    "unit_id_p"   "unit"."id"%TYPE,
    "area_id_p"   "area"."id"%TYPE,
    "issue_id_p"  "issue"."id"%TYPE )
  RETURNS SETOF "delegation_chain_row"
  LANGUAGE 'plpgsql' STABLE AS $$
    DECLARE
      "result_row" "delegation_chain_row";
    BEGIN
      FOR "result_row" IN
        SELECT * FROM "delegation_chain"(
          "member_id_p", "unit_id_p", "area_id_p", "issue_id_p", NULL
        )
      LOOP
        RETURN NEXT "result_row";
      END LOOP;
      RETURN;
    END;
  $$;

COMMENT ON FUNCTION "delegation_chain"
  ( "member"."id"%TYPE,
    "unit"."id"%TYPE,
    "area"."id"%TYPE,
    "issue"."id"%TYPE )
  IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';



------------------------------
-- Delegation info function --
------------------------------


CREATE TYPE "delegation_info_type" AS (
        "own_participation"     BOOLEAN,
        "first_trustee_id"      INT4,
        "first_trustee_participation" BOOLEAN,
        "first_trustee_ellipsis" BOOLEAN,
        "other_trustee_id"      INT4,
        "other_trustee_participation" BOOLEAN,
        "other_trustee_ellipsis" BOOLEAN );

CREATE FUNCTION "delegation_info"
  ( "member_id_p" "member"."id"%TYPE,
    "issue_id_p" "issue"."id"%TYPE )
  RETURNS "delegation_info_type"
  LANGUAGE 'plpgsql' STABLE AS $$
    DECLARE
      "issue_row"            "issue"%ROWTYPE;
      "current_row"          "delegation_chain_row";
      "direct_voter_row"     "direct_voter"%ROWTYPE;
      "delegating_voter_row" "delegating_voter"%ROWTYPE;
      "result"               "delegation_info_type";
    BEGIN
      SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
      IF
        "issue_row"."fully_frozen" ISNULL AND
        "issue_row"."closed" ISNULL
      THEN
        FOR "current_row" IN
          SELECT *
          FROM "delegation_chain"("member_id_p", NULL, NULL, "issue_id_p")
        LOOP
          IF
            "result"."own_participation" ISNULL
          THEN
            "result"."own_participation" := "current_row"."participation";
          ELSIF
            "current_row"."member_valid" AND
            ( "current_row"."loop" ISNULL OR
              "current_row"."loop" != 'repetition' )
          THEN
            IF "result"."first_trustee_id" ISNULL THEN
              "result"."first_trustee_id" := "current_row"."member_id";
              "result"."first_trustee_participation" :=
                "current_row"."participation" AND
                NOT "current_row"."overridden";
              "result"."first_trustee_ellipsis" := FALSE;
            ELSIF "result"."other_trustee_id" ISNULL THEN
              IF
                "current_row"."participation" AND
                NOT "current_row"."overridden"
              THEN
                "result"."other_trustee_id" := "current_row"."member_id";
                "result"."other_trustee_participation" :=
                  "current_row"."participation" AND
                  NOT "current_row"."overridden";
                "result"."other_trustee_ellipsis" := FALSE;
              ELSE
                "result"."first_trustee_ellipsis" := TRUE;
              END IF;
            ELSE
              "result"."other_trustee_ellipsis" := TRUE;
            END IF;
          END IF;
        END LOOP;
      ELSIF "issue_row"."closed" ISNULL THEN
        "result"."own_participation" := EXISTS (
          SELECT NULL FROM "direct_voter"
          WHERE "issue_id" = "issue_id_p" AND "member_id" = "member_id_p"
        );
        SELECT INTO "result"."first_trustee_id" "trustee_id" FROM "issue_delegation"
          WHERE "issue_id" = "issue_id_p" AND "truster_id" = "member_id_p";
        IF "result"."first_trustee_id" NOTNULL THEN
          "result"."first_trustee_participation" := FALSE;
          "result"."first_trustee_ellipsis" := EXISTS (
            SELECT NULL FROM "issue_delegation"
            WHERE "issue_id" = "issue_id_p"
            AND "truster_id" = "result"."first_trustee_id"
            AND "trustee_id" NOTNULL
          );
        END IF;
      ELSE
        SELECT INTO "direct_voter_row" * FROM "direct_voter"
          WHERE "issue_id" = "issue_id_p" AND "member_id" = "member_id_p";
        SELECT INTO "delegating_voter_row" * FROM "delegating_voter"
          WHERE "issue_id" = "issue_id_p" AND "member_id" = "member_id_p";
        IF "direct_voter_row"."member_id" NOTNULL THEN
          "result"."own_participation" := TRUE;
        ELSIF "delegating_voter_row" NOTNULL THEN
          "result"."own_participation" := FALSE;
          IF
            array_upper("delegating_voter_row"."delegate_member_ids", 1) > 2
          THEN
            "result"."first_trustee_id" := "delegating_voter_row"."delegate_member_ids"[1];
            "result"."first_trustee_participation" := FALSE;
            "result"."first_trustee_ellipsis" := TRUE;
            "result"."other_trustee_id" := "delegating_voter_row"."delegate_member_ids"[array_upper("delegating_voter_row"."delegate_member_ids", 1)];
            "result"."other_trustee_participation" := TRUE;
            "result"."other_trustee_ellipsis" := FALSE;
          ELSIF
            array_upper("delegating_voter_row"."delegate_member_ids", 1) = 2
          THEN
            "result"."first_trustee_id" := "delegating_voter_row"."delegate_member_ids"[1];
            "result"."first_trustee_participation" := FALSE;
            "result"."first_trustee_ellipsis" := FALSE;
            "result"."other_trustee_id" := "delegating_voter_row"."delegate_member_ids"[2];
            "result"."other_trustee_participation" := TRUE;
            "result"."other_trustee_ellipsis" := FALSE;
          ELSE
            "result"."first_trustee_id" := "delegating_voter_row"."delegate_member_ids"[1];
            "result"."first_trustee_participation" := TRUE;
            "result"."first_trustee_ellipsis" := FALSE;
          END IF;
        ELSE
          "result"."own_participation" := FALSE;
        END IF;
      END IF;
      RETURN "result";
    END;
  $$;

        "area_id_p" ISNULL AND
        "issue_id_p" NOTNULL
      THEN
        SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
        IF "issue_row"."id" ISNULL THEN
          RETURN;
        END IF;
        IF "issue_row"."closed" NOTNULL THEN
          IF "simulate_trustee_id_p" NOTNULL THEN
            RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
          END IF;
          FOR "output_row" IN
            SELECT * FROM
            "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
          LOOP
            RETURN NEXT "output_row";
          END LOOP;
          RETURN;
        END IF;
        "scope_v" := 'issue';
        SELECT "area_id" INTO "area_id_v"
          FROM "issue" WHERE "id" = "issue_id_p";
        SELECT "unit_id" INTO "unit_id_v"
