jbe@244: BEGIN;
jbe@244: 
jbe@244: CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@244:   SELECT * FROM (VALUES ('2.0.9', 2, 0, 9))
jbe@244:   AS "subquery"("string", "major", "minor", "revision");
jbe@244: 
jbe@249: -- Bugfix of error in update script to v2.0.0:
jbe@249: ALTER TABLE "battle" ALTER COLUMN "winning_initiative_id" DROP NOT NULL;
jbe@249: ALTER TABLE "battle" ALTER COLUMN "losing_initiative_id" DROP NOT NULL;
jbe@249: 
jbe@248: CREATE OR REPLACE VIEW "unit_member_count" AS
jbe@248:   SELECT
jbe@248:     "unit"."id" AS "unit_id",
jbe@248:     count("member"."id") AS "member_count"
jbe@248:   FROM "unit"
jbe@248:   LEFT JOIN "privilege"
jbe@248:   ON "privilege"."unit_id" = "unit"."id" 
jbe@248:   AND "privilege"."voting_right"
jbe@248:   LEFT JOIN "member"
jbe@248:   ON "member"."id" = "privilege"."member_id"
jbe@248:   AND "member"."active"
jbe@248:   GROUP BY "unit"."id";
jbe@248: 
jbe@244: COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain" function';
jbe@244: 
jbe@244: CREATE FUNCTION "delegation_chain_for_closed_issue"
jbe@244:   ( "member_id_p"           "member"."id"%TYPE,
jbe@244:     "issue_id_p"            "issue"."id"%TYPE )
jbe@244:   RETURNS SETOF "delegation_chain_row"
jbe@244:   LANGUAGE 'plpgsql' STABLE AS $$
jbe@244:     DECLARE
jbe@244:       "output_row"           "delegation_chain_row";
jbe@244:       "direct_voter_row"     "direct_voter"%ROWTYPE;
jbe@244:       "delegating_voter_row" "delegating_voter"%ROWTYPE;
jbe@244:     BEGIN
jbe@244:       "output_row"."index"         := 0;
jbe@244:       "output_row"."member_id"     := "member_id_p";
jbe@244:       "output_row"."member_valid"  := TRUE;
jbe@244:       "output_row"."participation" := FALSE;
jbe@244:       "output_row"."overridden"    := FALSE;
jbe@244:       "output_row"."disabled_out"  := FALSE;
jbe@244:       LOOP
jbe@244:         SELECT INTO "direct_voter_row" * FROM "direct_voter"
jbe@244:           WHERE "issue_id" = "issue_id_p"
jbe@244:           AND "member_id" = "output_row"."member_id";
jbe@244:         IF "direct_voter_row"."member_id" NOTNULL THEN
jbe@244:           "output_row"."participation" := TRUE;
jbe@244:           "output_row"."scope_out"     := NULL;
jbe@244:           "output_row"."disabled_out"  := NULL;
jbe@244:           RETURN NEXT "output_row";
jbe@244:           RETURN;
jbe@244:         END IF;
jbe@244:         SELECT INTO "delegating_voter_row" * FROM "delegating_voter"
jbe@244:           WHERE "issue_id" = "issue_id_p"
jbe@244:           AND "member_id" = "output_row"."member_id";
jbe@244:         IF "delegating_voter_row"."member_id" ISNULL THEN
jbe@244:           RETURN;
jbe@244:         END IF;
jbe@244:         "output_row"."scope_out" := "delegating_voter_row"."scope";
jbe@244:         RETURN NEXT "output_row";
jbe@244:         "output_row"."member_id" := "delegating_voter_row"."delegate_member_ids"[1];
jbe@244:         "output_row"."scope_in"  := "output_row"."scope_out";
jbe@244:       END LOOP;
jbe@244:     END;
jbe@244:   $$;
jbe@244: 
jbe@244: COMMENT ON FUNCTION "delegation_chain_for_closed_issue"
jbe@244:   ( "member"."id"%TYPE,
jbe@244:     "member"."id"%TYPE )
jbe@244:   IS 'Helper function for "delegation_chain" function, handling the special case of closed issues after voting';
jbe@244: 
jbe@244: DROP FUNCTION "delegation_chain"
jbe@244:   ( "member"."id"%TYPE,
jbe@244:     "unit"."id"%TYPE,
jbe@244:     "area"."id"%TYPE,
jbe@244:     "issue"."id"%TYPE );
jbe@244: 
jbe@244: DROP FUNCTION "delegation_chain"
jbe@244:   ( "member"."id"%TYPE,
jbe@244:     "unit"."id"%TYPE,
jbe@244:     "area"."id"%TYPE,
jbe@244:     "issue"."id"%TYPE,
jbe@244:     "member"."id"%TYPE );
jbe@244: 
jbe@244: CREATE FUNCTION "delegation_chain"
jbe@244:   ( "member_id_p"           "member"."id"%TYPE,
jbe@244:     "unit_id_p"             "unit"."id"%TYPE,
jbe@244:     "area_id_p"             "area"."id"%TYPE,
jbe@244:     "issue_id_p"            "issue"."id"%TYPE,
jbe@244:     "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL )
jbe@244:   RETURNS SETOF "delegation_chain_row"
jbe@244:   LANGUAGE 'plpgsql' STABLE AS $$
jbe@244:     DECLARE
jbe@244:       "scope_v"            "delegation_scope";
jbe@244:       "unit_id_v"          "unit"."id"%TYPE;
jbe@244:       "area_id_v"          "area"."id"%TYPE;
jbe@244:       "issue_row"          "issue"%ROWTYPE;
jbe@244:       "visited_member_ids" INT4[];  -- "member"."id"%TYPE[]
jbe@244:       "loop_member_id_v"   "member"."id"%TYPE;
jbe@244:       "output_row"         "delegation_chain_row";
jbe@244:       "output_rows"        "delegation_chain_row"[];
jbe@244:       "delegation_row"     "delegation"%ROWTYPE;
jbe@244:       "row_count"          INT4;
jbe@244:       "i"                  INT4;
jbe@244:       "loop_v"             BOOLEAN;
jbe@244:     BEGIN
jbe@244:       IF
jbe@244:         "unit_id_p" NOTNULL AND
jbe@244:         "area_id_p" ISNULL AND
jbe@244:         "issue_id_p" ISNULL
jbe@244:       THEN
jbe@244:         "scope_v" := 'unit';
jbe@244:         "unit_id_v" := "unit_id_p";
jbe@244:       ELSIF
jbe@244:         "unit_id_p" ISNULL AND
jbe@244:         "area_id_p" NOTNULL AND
jbe@244:         "issue_id_p" ISNULL
jbe@244:       THEN
jbe@244:         "scope_v" := 'area';
jbe@244:         "area_id_v" := "area_id_p";
jbe@244:         SELECT "unit_id" INTO "unit_id_v"
jbe@244:           FROM "area" WHERE "id" = "area_id_v";
jbe@244:       ELSIF
jbe@244:         "unit_id_p" ISNULL AND
jbe@244:         "area_id_p" ISNULL AND
jbe@244:         "issue_id_p" NOTNULL
jbe@244:       THEN
jbe@244:         SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
jbe@244:         IF "issue_row"."id" ISNULL THEN
jbe@244:           RETURN;
jbe@244:         END IF;
jbe@244:         IF "issue_row"."closed" NOTNULL THEN
jbe@244:           IF "simulate_trustee_id_p" NOTNULL THEN
jbe@244:             RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
jbe@244:           END IF;
jbe@244:           FOR "output_row" IN
jbe@244:             SELECT * FROM
jbe@244:             "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
jbe@244:           LOOP
jbe@244:             RETURN NEXT "output_row";
jbe@244:           END LOOP;
jbe@244:           RETURN;
jbe@244:         END IF;
jbe@244:         "scope_v" := 'issue';
jbe@244:         SELECT "area_id" INTO "area_id_v"
jbe@244:           FROM "issue" WHERE "id" = "issue_id_p";
jbe@244:         SELECT "unit_id" INTO "unit_id_v"
jbe@244:           FROM "area"  WHERE "id" = "area_id_v";
jbe@244:       ELSE
jbe@244:         RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
jbe@244:       END IF;
jbe@244:       "visited_member_ids" := '{}';
jbe@244:       "loop_member_id_v"   := NULL;
jbe@244:       "output_rows"        := '{}';
jbe@244:       "output_row"."index"         := 0;
jbe@244:       "output_row"."member_id"     := "member_id_p";
jbe@244:       "output_row"."member_valid"  := TRUE;
jbe@244:       "output_row"."participation" := FALSE;
jbe@244:       "output_row"."overridden"    := FALSE;
jbe@244:       "output_row"."disabled_out"  := FALSE;
jbe@244:       "output_row"."scope_out"     := NULL;
jbe@244:       LOOP
jbe@244:         IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
jbe@244:           "loop_member_id_v" := "output_row"."member_id";
jbe@244:         ELSE
jbe@244:           "visited_member_ids" :=
jbe@244:             "visited_member_ids" || "output_row"."member_id";
jbe@244:         END IF;
jbe@244:         IF "output_row"."participation" ISNULL THEN
jbe@244:           "output_row"."overridden" := NULL;
jbe@244:         ELSIF "output_row"."participation" THEN
jbe@244:           "output_row"."overridden" := TRUE;
jbe@244:         END IF;
jbe@244:         "output_row"."scope_in" := "output_row"."scope_out";
jbe@244:         IF EXISTS (
jbe@244:           SELECT NULL FROM "member" JOIN "privilege"
jbe@244:           ON "privilege"."member_id" = "member"."id"
jbe@244:           AND "privilege"."unit_id" = "unit_id_v"
jbe@244:           WHERE "id" = "output_row"."member_id"
jbe@244:           AND "member"."active" AND "privilege"."voting_right"
jbe@244:         ) THEN
jbe@244:           IF "scope_v" = 'unit' THEN
jbe@244:             SELECT * INTO "delegation_row" FROM "delegation"
jbe@244:               WHERE "truster_id" = "output_row"."member_id"
jbe@244:               AND "unit_id" = "unit_id_v";
jbe@244:           ELSIF "scope_v" = 'area' THEN
jbe@244:             "output_row"."participation" := EXISTS (
jbe@244:               SELECT NULL FROM "membership"
jbe@244:               WHERE "area_id" = "area_id_p"
jbe@244:               AND "member_id" = "output_row"."member_id"
jbe@244:             );
jbe@244:             SELECT * INTO "delegation_row" FROM "delegation"
jbe@244:               WHERE "truster_id" = "output_row"."member_id"
jbe@244:               AND (
jbe@244:                 "unit_id" = "unit_id_v" OR
jbe@244:                 "area_id" = "area_id_v"
jbe@244:               )
jbe@244:               ORDER BY "scope" DESC;
jbe@244:           ELSIF "scope_v" = 'issue' THEN
jbe@244:             IF "issue_row"."fully_frozen" ISNULL THEN
jbe@244:               "output_row"."participation" := EXISTS (
jbe@244:                 SELECT NULL FROM "interest"
jbe@244:                 WHERE "issue_id" = "issue_id_p"
jbe@244:                 AND "member_id" = "output_row"."member_id"
jbe@244:               );
jbe@244:             ELSE
jbe@244:               IF "output_row"."member_id" = "member_id_p" THEN
jbe@244:                 "output_row"."participation" := EXISTS (
jbe@244:                   SELECT NULL FROM "direct_voter"
jbe@244:                   WHERE "issue_id" = "issue_id_p"
jbe@244:                   AND "member_id" = "output_row"."member_id"
jbe@244:                 );
jbe@244:               ELSE
jbe@244:                 "output_row"."participation" := NULL;
jbe@244:               END IF;
jbe@244:             END IF;
jbe@244:             SELECT * INTO "delegation_row" FROM "delegation"
jbe@244:               WHERE "truster_id" = "output_row"."member_id"
jbe@244:               AND (
jbe@244:                 "unit_id" = "unit_id_v" OR
jbe@244:                 "area_id" = "area_id_v" OR
jbe@244:                 "issue_id" = "issue_id_p"
jbe@244:               )
jbe@244:               ORDER BY "scope" DESC;
jbe@244:           END IF;
jbe@244:         ELSE
jbe@244:           "output_row"."member_valid"  := FALSE;
jbe@244:           "output_row"."participation" := FALSE;
jbe@244:           "output_row"."scope_out"     := NULL;
jbe@244:           "delegation_row" := ROW(NULL);
jbe@244:         END IF;
jbe@244:         IF
jbe@244:           "output_row"."member_id" = "member_id_p" AND
jbe@244:           "simulate_trustee_id_p" NOTNULL
jbe@244:         THEN
jbe@244:           "output_row"."scope_out" := "scope_v";
jbe@244:           "output_rows" := "output_rows" || "output_row";
jbe@244:           "output_row"."member_id" := "simulate_trustee_id_p";
jbe@244:         ELSIF "delegation_row"."trustee_id" NOTNULL THEN
jbe@244:           "output_row"."scope_out" := "delegation_row"."scope";
jbe@244:           "output_rows" := "output_rows" || "output_row";
jbe@244:           "output_row"."member_id" := "delegation_row"."trustee_id";
jbe@244:         ELSIF "delegation_row"."scope" NOTNULL THEN
jbe@244:           "output_row"."scope_out" := "delegation_row"."scope";
jbe@244:           "output_row"."disabled_out" := TRUE;
jbe@244:           "output_rows" := "output_rows" || "output_row";
jbe@244:           EXIT;
jbe@244:         ELSE
jbe@244:           "output_row"."scope_out" := NULL;
jbe@244:           "output_rows" := "output_rows" || "output_row";
jbe@244:           EXIT;
jbe@244:         END IF;
jbe@244:         EXIT WHEN "loop_member_id_v" NOTNULL;
jbe@244:         "output_row"."index" := "output_row"."index" + 1;
jbe@244:       END LOOP;
jbe@244:       "row_count" := array_upper("output_rows", 1);
jbe@244:       "i"      := 1;
jbe@244:       "loop_v" := FALSE;
jbe@244:       LOOP
jbe@244:         "output_row" := "output_rows"["i"];
jbe@244:         EXIT WHEN "output_row" ISNULL;  -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
jbe@244:         IF "loop_v" THEN
jbe@244:           IF "i" + 1 = "row_count" THEN
jbe@244:             "output_row"."loop" := 'last';
jbe@244:           ELSIF "i" = "row_count" THEN
jbe@244:             "output_row"."loop" := 'repetition';
jbe@244:           ELSE
jbe@244:             "output_row"."loop" := 'intermediate';
jbe@244:           END IF;
jbe@244:         ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
jbe@244:           "output_row"."loop" := 'first';
jbe@244:           "loop_v" := TRUE;
jbe@244:         END IF;
jbe@244:         IF "scope_v" = 'unit' THEN
jbe@244:           "output_row"."participation" := NULL;
jbe@244:         END IF;
jbe@244:         RETURN NEXT "output_row";
jbe@244:         "i" := "i" + 1;
jbe@244:       END LOOP;
jbe@244:       RETURN;
jbe@244:     END;
jbe@244:   $$;
jbe@244: 
jbe@244: COMMENT ON FUNCTION "delegation_chain"
jbe@244:   ( "member"."id"%TYPE,
jbe@244:     "unit"."id"%TYPE,
jbe@244:     "area"."id"%TYPE,
jbe@244:     "issue"."id"%TYPE,
jbe@244:     "member"."id"%TYPE )
jbe@244:   IS 'Shows a delegation chain for unit, area, or issue; See "delegation_chain_row" type for more information';
jbe@244: 
jbe@244: CREATE TYPE "delegation_info_loop_type" AS ENUM
jbe@244:   ('own', 'first', 'first_ellipsis', 'other', 'other_ellipsis');
jbe@244: 
jbe@244: COMMENT ON TYPE "delegation_info_loop_type" IS 'Type of "delegation_loop" in "delegation_info_type"; ''own'' means loop to self, ''first'' means loop to first trustee, ''first_ellipsis'' means loop to ellipsis after first trustee, ''other'' means loop to other trustee, ''other_ellipsis'' means loop to ellipsis after other trustee''';
jbe@244: 
jbe@244: CREATE TYPE "delegation_info_type" AS (
jbe@244:         "own_participation"           BOOLEAN,
jbe@244:         "own_delegation_scope"        "delegation_scope",
jbe@244:         "first_trustee_id"            INT4,
jbe@244:         "first_trustee_participation" BOOLEAN,
jbe@244:         "first_trustee_ellipsis"      BOOLEAN,
jbe@244:         "other_trustee_id"            INT4,
jbe@244:         "other_trustee_participation" BOOLEAN,
jbe@244:         "other_trustee_ellipsis"      BOOLEAN,
jbe@244:         "delegation_loop"             "delegation_info_loop_type");
jbe@244: 
jbe@244: 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@244: 
jbe@244: COMMENT ON COLUMN "delegation_info_type"."own_participation"           IS 'Member is directly participating';
jbe@244: COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope"        IS 'Delegation scope of member';
jbe@244: COMMENT ON COLUMN "delegation_info_type"."first_trustee_id"            IS 'Direct trustee of member';
jbe@244: COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating';
jbe@244: COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis"      IS 'Ellipsis in delegation chain after "first_trustee"';
jbe@244: COMMENT ON COLUMN "delegation_info_type"."other_trustee_id"            IS 'Another relevant trustee (due to participation)';
jbe@244: 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@244: COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis"      IS 'Ellipsis in delegation chain after "other_trustee"';
jbe@244: 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@244: 
jbe@244: CREATE FUNCTION "delegation_info"
jbe@244:   ( "member_id_p"           "member"."id"%TYPE,
jbe@244:     "unit_id_p"             "unit"."id"%TYPE,
jbe@244:     "area_id_p"             "area"."id"%TYPE,
jbe@244:     "issue_id_p"            "issue"."id"%TYPE,
jbe@244:     "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL )
jbe@244:   RETURNS "delegation_info_type"
jbe@244:   LANGUAGE 'plpgsql' STABLE AS $$
jbe@244:     DECLARE
jbe@244:       "current_row" "delegation_chain_row";
jbe@244:       "result"      "delegation_info_type";
jbe@244:     BEGIN
jbe@244:       "result"."own_participation" := FALSE;
jbe@244:       FOR "current_row" IN
jbe@244:         SELECT * FROM "delegation_chain"(
jbe@244:           "member_id_p",
jbe@244:           "unit_id_p", "area_id_p", "issue_id_p",
jbe@244:           "simulate_trustee_id_p")
jbe@244:       LOOP
jbe@244:         IF "current_row"."member_id" = "member_id_p" THEN
jbe@244:           "result"."own_participation"    := "current_row"."participation";
jbe@244:           "result"."own_delegation_scope" := "current_row"."scope_out";
jbe@244:           IF "current_row"."loop" = 'first' THEN
jbe@244:             "result"."delegation_loop" := 'own';
jbe@244:           END IF;
jbe@244:         ELSIF
jbe@244:           "current_row"."member_valid" AND
jbe@244:           ( "current_row"."loop" ISNULL OR
jbe@244:             "current_row"."loop" != 'repetition' )
jbe@244:         THEN
jbe@244:           IF "result"."first_trustee_id" ISNULL THEN
jbe@244:             "result"."first_trustee_id"            := "current_row"."member_id";
jbe@244:             "result"."first_trustee_participation" := "current_row"."participation";
jbe@244:             "result"."first_trustee_ellipsis"      := FALSE;
jbe@244:             IF "current_row"."loop" = 'first' THEN
jbe@244:               "result"."delegation_loop" := 'first';
jbe@244:             END IF;
jbe@244:           ELSIF "result"."other_trustee_id" ISNULL THEN
jbe@247:             IF "current_row"."participation" AND NOT "current_row"."overridden" THEN
jbe@244:               "result"."other_trustee_id"            := "current_row"."member_id";
jbe@244:               "result"."other_trustee_participation" := TRUE;
jbe@244:               "result"."other_trustee_ellipsis"      := FALSE;
jbe@244:               IF "current_row"."loop" = 'first' THEN
jbe@244:                 "result"."delegation_loop" := 'other';
jbe@244:               END IF;
jbe@244:             ELSE
jbe@244:               "result"."first_trustee_ellipsis" := TRUE;
jbe@244:               IF "current_row"."loop" = 'first' THEN
jbe@244:                 "result"."delegation_loop" := 'first_ellipsis';
jbe@244:               END IF;
jbe@244:             END IF;
jbe@244:           ELSE
jbe@244:             "result"."other_trustee_ellipsis" := TRUE;
jbe@244:             IF "current_row"."loop" = 'first' THEN
jbe@244:               "result"."delegation_loop" := 'other_ellipsis';
jbe@244:             END IF;
jbe@244:           END IF;
jbe@244:         END IF;
jbe@244:       END LOOP;
jbe@244:       RETURN "result";
jbe@244:     END;
jbe@244:   $$;
jbe@244: 
jbe@244: COMMENT ON FUNCTION "delegation_info"
jbe@244:   ( "member"."id"%TYPE,
jbe@244:     "unit"."id"%TYPE,
jbe@244:     "area"."id"%TYPE,
jbe@244:     "issue"."id"%TYPE,
jbe@244:     "member"."id"%TYPE )
jbe@244:   IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information';
jbe@244: 
jbe@250: CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
jbe@250:   RETURNS VOID
jbe@250:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@250:     DECLARE
jbe@250:       "issue_row"         "issue"%ROWTYPE;
jbe@250:       "policy_row"        "policy"%ROWTYPE;
jbe@250:       "dimension_v"       INTEGER;
jbe@250:       "vote_matrix"       INT4[][];  -- absolute votes
jbe@250:       "matrix"            INT8[][];  -- defeat strength / best paths
jbe@250:       "i"                 INTEGER;
jbe@250:       "j"                 INTEGER;
jbe@250:       "k"                 INTEGER;
jbe@250:       "battle_row"        "battle"%ROWTYPE;
jbe@250:       "rank_ary"          INT4[];
jbe@250:       "rank_v"            INT4;
jbe@250:       "done_v"            INTEGER;
jbe@250:       "winners_ary"       INTEGER[];
jbe@250:       "initiative_id_v"   "initiative"."id"%TYPE;
jbe@250:     BEGIN
jbe@250:       SELECT * INTO "issue_row"
jbe@250:         FROM "issue" WHERE "id" = "issue_id_p"
jbe@250:         FOR UPDATE;
jbe@250:       SELECT * INTO "policy_row"
jbe@250:         FROM "policy" WHERE "id" = "issue_row"."policy_id";
jbe@250:       SELECT count(1) INTO "dimension_v"
jbe@250:         FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
jbe@250:       -- Create "vote_matrix" with absolute number of votes in pairwise
jbe@250:       -- comparison:
jbe@250:       "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
jbe@250:       "i" := 1;
jbe@250:       "j" := 2;
jbe@250:       FOR "battle_row" IN
jbe@250:         SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
jbe@250:         ORDER BY
jbe@250:         "winning_initiative_id" NULLS LAST,
jbe@250:         "losing_initiative_id" NULLS LAST
jbe@250:       LOOP
jbe@250:         "vote_matrix"["i"]["j"] := "battle_row"."count";
jbe@250:         IF "j" = "dimension_v" THEN
jbe@250:           "i" := "i" + 1;
jbe@250:           "j" := 1;
jbe@250:         ELSE
jbe@250:           "j" := "j" + 1;
jbe@250:           IF "j" = "i" THEN
jbe@250:             "j" := "j" + 1;
jbe@250:           END IF;
jbe@250:         END IF;
jbe@250:       END LOOP;
jbe@250:       IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
jbe@250:         RAISE EXCEPTION 'Wrong battle count (should not happen)';
jbe@250:       END IF;
jbe@250:       -- Store defeat strengths in "matrix" using "defeat_strength"
jbe@250:       -- function:
jbe@250:       "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
jbe@250:       "i" := 1;
jbe@250:       LOOP
jbe@250:         "j" := 1;
jbe@250:         LOOP
jbe@250:           IF "i" != "j" THEN
jbe@250:             "matrix"["i"]["j"] := "defeat_strength"(
jbe@250:               "vote_matrix"["i"]["j"],
jbe@250:               "vote_matrix"["j"]["i"]
jbe@250:             );
jbe@250:           END IF;
jbe@250:           EXIT WHEN "j" = "dimension_v";
jbe@250:           "j" := "j" + 1;
jbe@250:         END LOOP;
jbe@250:         EXIT WHEN "i" = "dimension_v";
jbe@250:         "i" := "i" + 1;
jbe@250:       END LOOP;
jbe@250:       -- Find best paths:
jbe@250:       "i" := 1;
jbe@250:       LOOP
jbe@250:         "j" := 1;
jbe@250:         LOOP
jbe@250:           IF "i" != "j" THEN
jbe@250:             "k" := 1;
jbe@250:             LOOP
jbe@250:               IF "i" != "k" AND "j" != "k" THEN
jbe@250:                 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
jbe@250:                   IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
jbe@250:                     "matrix"["j"]["k"] := "matrix"["j"]["i"];
jbe@250:                   END IF;
jbe@250:                 ELSE
jbe@250:                   IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
jbe@250:                     "matrix"["j"]["k"] := "matrix"["i"]["k"];
jbe@250:                   END IF;
jbe@250:                 END IF;
jbe@250:               END IF;
jbe@250:               EXIT WHEN "k" = "dimension_v";
jbe@250:               "k" := "k" + 1;
jbe@250:             END LOOP;
jbe@250:           END IF;
jbe@250:           EXIT WHEN "j" = "dimension_v";
jbe@250:           "j" := "j" + 1;
jbe@250:         END LOOP;
jbe@250:         EXIT WHEN "i" = "dimension_v";
jbe@250:         "i" := "i" + 1;
jbe@250:       END LOOP;
jbe@250:       -- Determine order of winners:
jbe@250:       "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
jbe@250:       "rank_v" := 1;
jbe@250:       "done_v" := 0;
jbe@250:       LOOP
jbe@250:         "winners_ary" := '{}';
jbe@250:         "i" := 1;
jbe@250:         LOOP
jbe@250:           IF "rank_ary"["i"] ISNULL THEN
jbe@250:             "j" := 1;
jbe@250:             LOOP
jbe@250:               IF
jbe@250:                 "i" != "j" AND
jbe@250:                 "rank_ary"["j"] ISNULL AND
jbe@250:                 "matrix"["j"]["i"] > "matrix"["i"]["j"]
jbe@250:               THEN
jbe@250:                 -- someone else is better
jbe@250:                 EXIT;
jbe@250:               END IF;
jbe@250:               IF "j" = "dimension_v" THEN
jbe@250:                 -- noone is better
jbe@250:                 "winners_ary" := "winners_ary" || "i";
jbe@250:                 EXIT;
jbe@250:               END IF;
jbe@250:               "j" := "j" + 1;
jbe@250:             END LOOP;
jbe@250:           END IF;
jbe@250:           EXIT WHEN "i" = "dimension_v";
jbe@250:           "i" := "i" + 1;
jbe@250:         END LOOP;
jbe@250:         "i" := 1;
jbe@250:         LOOP
jbe@250:           "rank_ary"["winners_ary"["i"]] := "rank_v";
jbe@250:           "done_v" := "done_v" + 1;
jbe@250:           EXIT WHEN "i" = array_upper("winners_ary", 1);
jbe@250:           "i" := "i" + 1;
jbe@250:         END LOOP;
jbe@250:         EXIT WHEN "done_v" = "dimension_v";
jbe@250:         "rank_v" := "rank_v" + 1;
jbe@250:       END LOOP;
jbe@250:       -- write preliminary results:
jbe@250:       "i" := 1;
jbe@250:       FOR "initiative_id_v" IN
jbe@250:         SELECT "id" FROM "initiative"
jbe@250:         WHERE "issue_id" = "issue_id_p" AND "admitted"
jbe@250:         ORDER BY "id"
jbe@250:       LOOP
jbe@250:         UPDATE "initiative" SET
jbe@250:           "direct_majority" =
jbe@250:             CASE WHEN "policy_row"."direct_majority_strict" THEN
jbe@250:               "positive_votes" * "policy_row"."direct_majority_den" >
jbe@250:               "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
jbe@250:             ELSE
jbe@250:               "positive_votes" * "policy_row"."direct_majority_den" >=
jbe@250:               "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
jbe@250:             END
jbe@250:             AND "positive_votes" >= "policy_row"."direct_majority_positive"
jbe@250:             AND "issue_row"."voter_count"-"negative_votes" >=
jbe@250:                 "policy_row"."direct_majority_non_negative",
jbe@250:             "indirect_majority" =
jbe@250:             CASE WHEN "policy_row"."indirect_majority_strict" THEN
jbe@250:               "positive_votes" * "policy_row"."indirect_majority_den" >
jbe@250:               "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
jbe@250:             ELSE
jbe@250:               "positive_votes" * "policy_row"."indirect_majority_den" >=
jbe@250:               "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
jbe@250:             END
jbe@250:             AND "positive_votes" >= "policy_row"."indirect_majority_positive"
jbe@250:             AND "issue_row"."voter_count"-"negative_votes" >=
jbe@250:                 "policy_row"."indirect_majority_non_negative",
jbe@250:           "schulze_rank"           = "rank_ary"["i"],
jbe@250:           "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"],
jbe@250:           "worse_than_status_quo"  = "rank_ary"["i"] > "rank_ary"["dimension_v"],
jbe@250:           "multistage_majority"    = "rank_ary"["i"] >= "rank_ary"["dimension_v"],
jbe@250:           "reverse_beat_path"      = "matrix"["dimension_v"]["i"] >= 0,
jbe@250:           "eligible"               = FALSE,
jbe@250:           "winner"                 = FALSE,
jbe@250:           "rank"                   = NULL  -- NOTE: in cases of manual reset of issue state
jbe@250:           WHERE "id" = "initiative_id_v";
jbe@250:         "i" := "i" + 1;
jbe@250:       END LOOP;
jbe@250:       IF "i" != "dimension_v" THEN
jbe@250:         RAISE EXCEPTION 'Wrong winner count (should not happen)';
jbe@250:       END IF;
jbe@250:       -- take indirect majorities into account:
jbe@250:       LOOP
jbe@250:         UPDATE "initiative" SET "indirect_majority" = TRUE
jbe@250:           FROM (
jbe@250:             SELECT "new_initiative"."id" AS "initiative_id"
jbe@250:             FROM "initiative" "old_initiative"
jbe@250:             JOIN "initiative" "new_initiative"
jbe@250:               ON "new_initiative"."issue_id" = "issue_id_p"
jbe@250:               AND "new_initiative"."indirect_majority" = FALSE
jbe@250:             JOIN "battle" "battle_win"
jbe@250:               ON "battle_win"."issue_id" = "issue_id_p"
jbe@250:               AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
jbe@250:               AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
jbe@250:             JOIN "battle" "battle_lose"
jbe@250:               ON "battle_lose"."issue_id" = "issue_id_p"
jbe@250:               AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
jbe@250:               AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
jbe@250:             WHERE "old_initiative"."issue_id" = "issue_id_p"
jbe@250:             AND "old_initiative"."indirect_majority" = TRUE
jbe@250:             AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
jbe@250:               "battle_win"."count" * "policy_row"."indirect_majority_den" >
jbe@250:               "policy_row"."indirect_majority_num" *
jbe@250:               ("battle_win"."count"+"battle_lose"."count")
jbe@250:             ELSE
jbe@250:               "battle_win"."count" * "policy_row"."indirect_majority_den" >=
jbe@250:               "policy_row"."indirect_majority_num" *
jbe@250:               ("battle_win"."count"+"battle_lose"."count")
jbe@250:             END
jbe@250:             AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
jbe@250:             AND "issue_row"."voter_count"-"battle_lose"."count" >=
jbe@250:                 "policy_row"."indirect_majority_non_negative"
jbe@250:           ) AS "subquery"
jbe@250:           WHERE "id" = "subquery"."initiative_id";
jbe@250:         EXIT WHEN NOT FOUND;
jbe@250:       END LOOP;
jbe@250:       -- set "multistage_majority" for remaining matching initiatives:
jbe@250:       UPDATE "initiative" SET "multistage_majority" = TRUE
jbe@250:         FROM (
jbe@250:           SELECT "losing_initiative"."id" AS "initiative_id"
jbe@250:           FROM "initiative" "losing_initiative"
jbe@250:           JOIN "initiative" "winning_initiative"
jbe@250:             ON "winning_initiative"."issue_id" = "issue_id_p"
jbe@250:             AND "winning_initiative"."admitted"
jbe@250:           JOIN "battle" "battle_win"
jbe@250:             ON "battle_win"."issue_id" = "issue_id_p"
jbe@250:             AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
jbe@250:             AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
jbe@250:           JOIN "battle" "battle_lose"
jbe@250:             ON "battle_lose"."issue_id" = "issue_id_p"
jbe@250:             AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
jbe@250:             AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
jbe@250:           WHERE "losing_initiative"."issue_id" = "issue_id_p"
jbe@250:           AND "losing_initiative"."admitted"
jbe@250:           AND "winning_initiative"."schulze_rank" <
jbe@250:               "losing_initiative"."schulze_rank"
jbe@250:           AND "battle_win"."count" > "battle_lose"."count"
jbe@250:           AND (
jbe@250:             "battle_win"."count" > "winning_initiative"."positive_votes" OR
jbe@250:             "battle_lose"."count" < "losing_initiative"."negative_votes" )
jbe@250:         ) AS "subquery"
jbe@250:         WHERE "id" = "subquery"."initiative_id";
jbe@250:       -- mark eligible initiatives:
jbe@250:       UPDATE "initiative" SET "eligible" = TRUE
jbe@250:         WHERE "issue_id" = "issue_id_p"
jbe@250:         AND "initiative"."direct_majority"
jbe@250:         AND "initiative"."indirect_majority"
jbe@250:         AND "initiative"."better_than_status_quo"
jbe@250:         AND (
jbe@250:           "policy_row"."no_multistage_majority" = FALSE OR
jbe@250:           "initiative"."multistage_majority" = FALSE )
jbe@250:         AND (
jbe@250:           "policy_row"."no_reverse_beat_path" = FALSE OR
jbe@250:           "initiative"."reverse_beat_path" = FALSE );
jbe@250:       -- mark final winner:
jbe@250:       UPDATE "initiative" SET "winner" = TRUE
jbe@250:         FROM (
jbe@250:           SELECT "id" AS "initiative_id"
jbe@250:           FROM "initiative"
jbe@250:           WHERE "issue_id" = "issue_id_p" AND "eligible"
jbe@250:           ORDER BY
jbe@250:             "schulze_rank",
jbe@250:             "vote_ratio"("positive_votes", "negative_votes"),
jbe@250:             "id"
jbe@250:           LIMIT 1
jbe@250:         ) AS "subquery"
jbe@250:         WHERE "id" = "subquery"."initiative_id";
jbe@250:       -- write (final) ranks:
jbe@250:       "rank_v" := 1;
jbe@250:       FOR "initiative_id_v" IN
jbe@250:         SELECT "id"
jbe@250:         FROM "initiative"
jbe@250:         WHERE "issue_id" = "issue_id_p" AND "admitted"
jbe@250:         ORDER BY
jbe@250:           "winner" DESC,
jbe@250:           "eligible" DESC,
jbe@250:           "schulze_rank",
jbe@250:           "vote_ratio"("positive_votes", "negative_votes"),
jbe@250:           "id"
jbe@250:       LOOP
jbe@250:         UPDATE "initiative" SET "rank" = "rank_v"
jbe@250:           WHERE "id" = "initiative_id_v";
jbe@250:         "rank_v" := "rank_v" + 1;
jbe@250:       END LOOP;
jbe@250:       -- set schulze rank of status quo and mark issue as finished:
jbe@250:       UPDATE "issue" SET
jbe@250:         "status_quo_schulze_rank" = "rank_ary"["dimension_v"],
jbe@250:         "state" =
jbe@250:           CASE WHEN EXISTS (
jbe@250:             SELECT NULL FROM "initiative"
jbe@250:             WHERE "issue_id" = "issue_id_p" AND "winner"
jbe@250:           ) THEN
jbe@250:             'finished_with_winner'::"issue_state"
jbe@250:           ELSE
jbe@250:             'finished_without_winner'::"issue_state"
jbe@250:           END,
jbe@250:         "ranks_available" = TRUE
jbe@250:         WHERE "id" = "issue_id_p";
jbe@250:       RETURN;
jbe@250:     END;
jbe@250:   $$;
jbe@250: 
jbe@244: COMMIT;