1.1 --- a/update/core-update.v3.0.1-v3.0.2.sql	Thu May 22 05:13:22 2014 +0200
     1.2 +++ b/update/core-update.v3.0.1-v3.0.2.sql	Thu May 22 05:29:28 2014 +0200
     1.3 @@ -4,6 +4,529 @@
     1.4    SELECT * FROM (VALUES ('3.0.2', 3, 0, 2))
     1.5    AS "subquery"("string", "major", "minor", "revision");
     1.6  
     1.7 -TODO
     1.8 +
     1.9 +CREATE TYPE "defeat_strength" AS ENUM ('simple', 'tuple');
    1.10 +
    1.11 +COMMENT ON TYPE "defeat_strength" IS 'How pairwise defeats are measured for the Schulze method: ''simple'' = only the number of winning votes, ''tuple'' = primarily the number of winning votes, secondarily the number of losing votes';
    1.12 +
    1.13 +
    1.14 +CREATE TYPE "tie_breaking" AS ENUM ('simple', 'variant1', 'variant2');
    1.15 +
    1.16 +COMMENT ON TYPE "tie_breaking" IS 'Tie-breaker for the Schulze method: ''simple'' = only initiative ids are used, ''variant1'' = use initiative ids in variant 1 for tie breaking of the links (TBRL) and sequentially forbid shared links, ''variant2'' = use initiative ids in variant 2 for tie breaking of the links (TBRL) and sequentially forbid shared links';
    1.17 +
    1.18 +
    1.19 +ALTER TABLE "policy" ADD COLUMN "defeat_strength" "defeat_strength" NOT NULL DEFAULT 'tuple';
    1.20 +ALTER TABLE "policy" ADD COLUMN "tie_breaking"    "tie_breaking"    NOT NULL DEFAULT 'variant1';
    1.21 +
    1.22 +ALTER TABLE "policy" ADD
    1.23 +  CONSTRAINT "no_reverse_beat_path_requires_tuple_defeat_strength" CHECK (
    1.24 +    ("defeat_strength" = 'tuple'::"defeat_strength" OR "no_reverse_beat_path" = FALSE)
    1.25 +  );
    1.26 +
    1.27 +COMMENT ON COLUMN "policy"."defeat_strength"       IS 'How pairwise defeats are measured for the Schulze method; see type "defeat_strength"; ''tuple'' is the recommended setting';
    1.28 +COMMENT ON COLUMN "policy"."tie_breaking"          IS 'Tie-breaker for the Schulze method; see type "tie_breaking"; ''variant1'' or ''variant2'' are recommended';
    1.29 +COMMENT ON COLUMN "initiative"."reverse_beat_path"      IS 'TRUE, if there is a beat path (may include ties) from this initiative to the status quo; set to NULL if "policy"."defeat_strength" is set to ''simple''';
    1.30 + 
    1.31 +
    1.32 +DROP FUNCTION "calculate_ranks"("issue"."id"%TYPE);
    1.33 +DROP FUNCTION "defeat_strength"(INT4, INT4);
    1.34 +
    1.35 +
    1.36 +CREATE FUNCTION "defeat_strength"
    1.37 +  ( "positive_votes_p"  INT4,
    1.38 +    "negative_votes_p"  INT4,
    1.39 +    "defeat_strength_p" "defeat_strength" )
    1.40 +  RETURNS INT8
    1.41 +  LANGUAGE 'plpgsql' IMMUTABLE AS $$
    1.42 +    BEGIN
    1.43 +      IF "defeat_strength_p" = 'simple'::"defeat_strength" THEN
    1.44 +        IF "positive_votes_p" > "negative_votes_p" THEN
    1.45 +          RETURN "positive_votes_p";
    1.46 +        ELSE
    1.47 +          RETURN 0;
    1.48 +        END IF;
    1.49 +      ELSE
    1.50 +        IF "positive_votes_p" > "negative_votes_p" THEN
    1.51 +          RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
    1.52 +        ELSIF "positive_votes_p" = "negative_votes_p" THEN
    1.53 +          RETURN 0;
    1.54 +        ELSE
    1.55 +          RETURN -1;
    1.56 +        END IF;
    1.57 +      END IF;
    1.58 +    END;
    1.59 +  $$;
    1.60 +
    1.61 +COMMENT ON FUNCTION "defeat_strength"(INT4, INT4, "defeat_strength") IS 'Calculates defeat strength (INT8!) according to the "defeat_strength" option (see comment on type "defeat_strength")';
    1.62 +
    1.63 +
    1.64 +CREATE FUNCTION "secondary_link_strength"
    1.65 +  ( "initiative1_ord_p" INT4,
    1.66 +    "initiative2_ord_p" INT4,
    1.67 +    "tie_breaking_p"   "tie_breaking" )
    1.68 +  RETURNS INT8
    1.69 +  LANGUAGE 'plpgsql' IMMUTABLE AS $$
    1.70 +    BEGIN
    1.71 +      IF "initiative1_ord_p" = "initiative2_ord_p" THEN
    1.72 +        RAISE EXCEPTION 'Identical initiative ids passed to "secondary_link_strength" function (should not happen)';
    1.73 +      END IF;
    1.74 +      RETURN (
    1.75 +        CASE WHEN "tie_breaking_p" = 'simple'::"tie_breaking" THEN
    1.76 +          0
    1.77 +        ELSE
    1.78 +          CASE WHEN "initiative1_ord_p" < "initiative2_ord_p" THEN
    1.79 +            1::INT8 << 62
    1.80 +          ELSE 0 END
    1.81 +          +
    1.82 +          CASE WHEN "tie_breaking_p" = 'variant2'::"tie_breaking" THEN
    1.83 +            ("initiative2_ord_p"::INT8 << 31) - "initiative1_ord_p"::INT8
    1.84 +          ELSE
    1.85 +            "initiative2_ord_p"::INT8 - ("initiative1_ord_p"::INT8 << 31)
    1.86 +          END
    1.87 +        END
    1.88 +      );
    1.89 +    END;
    1.90 +  $$;
    1.91 +
    1.92 +COMMENT ON FUNCTION "secondary_link_strength"(INT4, INT4, "tie_breaking") IS 'Calculates a secondary criterion for the defeat strength (tie-breaking of the links)';
    1.93 +
    1.94 +
    1.95 +CREATE TYPE "link_strength" AS (
    1.96 +        "primary"               INT8,
    1.97 +        "secondary"             INT8 );
    1.98 +
    1.99 +COMMENT ON TYPE "link_strength" IS 'Type to store the defeat strength of a link between two candidates plus a secondary criterion to create unique link strengths between the candidates (needed for tie-breaking ''variant1'' and ''variant2'')';
   1.100 +
   1.101 +
   1.102 +CREATE FUNCTION "find_best_paths"("matrix_d" "link_strength"[][])
   1.103 +  RETURNS "link_strength"[][]
   1.104 +  LANGUAGE 'plpgsql' IMMUTABLE AS $$
   1.105 +    DECLARE
   1.106 +      "dimension_v" INT4;
   1.107 +      "matrix_p"    "link_strength"[][];
   1.108 +      "i"           INT4;
   1.109 +      "j"           INT4;
   1.110 +      "k"           INT4;
   1.111 +    BEGIN
   1.112 +      "dimension_v" := array_upper("matrix_d", 1);
   1.113 +      "matrix_p" := "matrix_d";
   1.114 +      "i" := 1;
   1.115 +      LOOP
   1.116 +        "j" := 1;
   1.117 +        LOOP
   1.118 +          IF "i" != "j" THEN
   1.119 +            "k" := 1;
   1.120 +            LOOP
   1.121 +              IF "i" != "k" AND "j" != "k" THEN
   1.122 +                IF "matrix_p"["j"]["i"] < "matrix_p"["i"]["k"] THEN
   1.123 +                  IF "matrix_p"["j"]["i"] > "matrix_p"["j"]["k"] THEN
   1.124 +                    "matrix_p"["j"]["k"] := "matrix_p"["j"]["i"];
   1.125 +                  END IF;
   1.126 +                ELSE
   1.127 +                  IF "matrix_p"["i"]["k"] > "matrix_p"["j"]["k"] THEN
   1.128 +                    "matrix_p"["j"]["k"] := "matrix_p"["i"]["k"];
   1.129 +                  END IF;
   1.130 +                END IF;
   1.131 +              END IF;
   1.132 +              EXIT WHEN "k" = "dimension_v";
   1.133 +              "k" := "k" + 1;
   1.134 +            END LOOP;
   1.135 +          END IF;
   1.136 +          EXIT WHEN "j" = "dimension_v";
   1.137 +          "j" := "j" + 1;
   1.138 +        END LOOP;
   1.139 +        EXIT WHEN "i" = "dimension_v";
   1.140 +        "i" := "i" + 1;
   1.141 +      END LOOP;
   1.142 +      RETURN "matrix_p";
   1.143 +    END;
   1.144 +  $$;
   1.145 +
   1.146 +COMMENT ON FUNCTION "find_best_paths"("link_strength"[][]) IS 'Computes the strengths of the best beat-paths from a square matrix';
   1.147 + 
   1.148 +
   1.149 +CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
   1.150 +  RETURNS VOID
   1.151 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.152 +    DECLARE
   1.153 +      "issue_row"       "issue"%ROWTYPE;
   1.154 +      "policy_row"      "policy"%ROWTYPE;
   1.155 +      "dimension_v"     INT4;
   1.156 +      "matrix_a"        INT4[][];  -- absolute votes
   1.157 +      "matrix_d"        "link_strength"[][];  -- defeat strength (direct)
   1.158 +      "matrix_p"        "link_strength"[][];  -- defeat strength (best path)
   1.159 +      "matrix_t"        "link_strength"[][];  -- defeat strength (tie-breaking)
   1.160 +      "matrix_f"        BOOLEAN[][];  -- forbidden link (tie-breaking)
   1.161 +      "matrix_b"        BOOLEAN[][];  -- final order (who beats who)
   1.162 +      "i"               INT4;
   1.163 +      "j"               INT4;
   1.164 +      "m"               INT4;
   1.165 +      "n"               INT4;
   1.166 +      "battle_row"      "battle"%ROWTYPE;
   1.167 +      "rank_ary"        INT4[];
   1.168 +      "rank_v"          INT4;
   1.169 +      "initiative_id_v" "initiative"."id"%TYPE;
   1.170 +    BEGIN
   1.171 +      PERFORM "require_transaction_isolation"();
   1.172 +      SELECT * INTO "issue_row"
   1.173 +        FROM "issue" WHERE "id" = "issue_id_p";
   1.174 +      SELECT * INTO "policy_row"
   1.175 +        FROM "policy" WHERE "id" = "issue_row"."policy_id";
   1.176 +      SELECT count(1) INTO "dimension_v"
   1.177 +        FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
   1.178 +      -- create "matrix_a" with absolute number of votes in pairwise
   1.179 +      -- comparison:
   1.180 +      "matrix_a" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
   1.181 +      "i" := 1;
   1.182 +      "j" := 2;
   1.183 +      FOR "battle_row" IN
   1.184 +        SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
   1.185 +        ORDER BY
   1.186 +        "winning_initiative_id" NULLS FIRST,
   1.187 +        "losing_initiative_id" NULLS FIRST
   1.188 +      LOOP
   1.189 +        "matrix_a"["i"]["j"] := "battle_row"."count";
   1.190 +        IF "j" = "dimension_v" THEN
   1.191 +          "i" := "i" + 1;
   1.192 +          "j" := 1;
   1.193 +        ELSE
   1.194 +          "j" := "j" + 1;
   1.195 +          IF "j" = "i" THEN
   1.196 +            "j" := "j" + 1;
   1.197 +          END IF;
   1.198 +        END IF;
   1.199 +      END LOOP;
   1.200 +      IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
   1.201 +        RAISE EXCEPTION 'Wrong battle count (should not happen)';
   1.202 +      END IF;
   1.203 +      -- store direct defeat strengths in "matrix_d" using "defeat_strength"
   1.204 +      -- and "secondary_link_strength" functions:
   1.205 +      "matrix_d" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
   1.206 +      "i" := 1;
   1.207 +      LOOP
   1.208 +        "j" := 1;
   1.209 +        LOOP
   1.210 +          IF "i" != "j" THEN
   1.211 +            "matrix_d"["i"]["j"] := (
   1.212 +              "defeat_strength"(
   1.213 +                "matrix_a"["i"]["j"],
   1.214 +                "matrix_a"["j"]["i"],
   1.215 +                "policy_row"."defeat_strength"
   1.216 +              ),
   1.217 +              "secondary_link_strength"(
   1.218 +                "i",
   1.219 +                "j",
   1.220 +                "policy_row"."tie_breaking"
   1.221 +              )
   1.222 +            )::"link_strength";
   1.223 +          END IF;
   1.224 +          EXIT WHEN "j" = "dimension_v";
   1.225 +          "j" := "j" + 1;
   1.226 +        END LOOP;
   1.227 +        EXIT WHEN "i" = "dimension_v";
   1.228 +        "i" := "i" + 1;
   1.229 +      END LOOP;
   1.230 +      -- find best paths:
   1.231 +      "matrix_p" := "find_best_paths"("matrix_d");
   1.232 +      -- create partial order:
   1.233 +      "matrix_b" := array_fill(NULL::BOOLEAN, ARRAY["dimension_v", "dimension_v"]);
   1.234 +      "i" := 1;
   1.235 +      LOOP
   1.236 +        "j" := "i" + 1;
   1.237 +        LOOP
   1.238 +          IF "i" != "j" THEN
   1.239 +            IF "matrix_p"["i"]["j"] > "matrix_p"["j"]["i"] THEN
   1.240 +              "matrix_b"["i"]["j"] := TRUE;
   1.241 +              "matrix_b"["j"]["i"] := FALSE;
   1.242 +            ELSIF "matrix_p"["i"]["j"] < "matrix_p"["j"]["i"] THEN
   1.243 +              "matrix_b"["i"]["j"] := FALSE;
   1.244 +              "matrix_b"["j"]["i"] := TRUE;
   1.245 +            END IF;
   1.246 +          END IF;
   1.247 +          EXIT WHEN "j" = "dimension_v";
   1.248 +          "j" := "j" + 1;
   1.249 +        END LOOP;
   1.250 +        EXIT WHEN "i" = "dimension_v" - 1;
   1.251 +        "i" := "i" + 1;
   1.252 +      END LOOP;
   1.253 +      -- tie-breaking by forbidding shared weakest links in beat-paths
   1.254 +      -- (unless "tie_breaking" is set to 'simple', in which case tie-breaking
   1.255 +      -- is performed later by initiative id):
   1.256 +      IF "policy_row"."tie_breaking" != 'simple'::"tie_breaking" THEN
   1.257 +        "m" := 1;
   1.258 +        LOOP
   1.259 +          "n" := "m" + 1;
   1.260 +          LOOP
   1.261 +            -- only process those candidates m and n, which are tied:
   1.262 +            IF "matrix_b"["m"]["n"] ISNULL THEN
   1.263 +              -- start with beat-paths prior tie-breaking:
   1.264 +              "matrix_t" := "matrix_p";
   1.265 +              -- start with all links allowed:
   1.266 +              "matrix_f" := array_fill(FALSE, ARRAY["dimension_v", "dimension_v"]);
   1.267 +              LOOP
   1.268 +                -- determine (and forbid) that link that is the weakest link
   1.269 +                -- in both the best path from candidate m to candidate n and
   1.270 +                -- from candidate n to candidate m:
   1.271 +                "i" := 1;
   1.272 +                <<forbid_one_link>>
   1.273 +                LOOP
   1.274 +                  "j" := 1;
   1.275 +                  LOOP
   1.276 +                    IF "i" != "j" THEN
   1.277 +                      IF "matrix_d"["i"]["j"] = "matrix_t"["m"]["n"] THEN
   1.278 +                        "matrix_f"["i"]["j"] := TRUE;
   1.279 +                        -- exit for performance reasons,
   1.280 +                        -- as exactly one link will be found:
   1.281 +                        EXIT forbid_one_link;
   1.282 +                      END IF;
   1.283 +                    END IF;
   1.284 +                    EXIT WHEN "j" = "dimension_v";
   1.285 +                    "j" := "j" + 1;
   1.286 +                  END LOOP;
   1.287 +                  IF "i" = "dimension_v" THEN
   1.288 +                    RAISE EXCEPTION 'Did not find shared weakest link for tie-breaking (should not happen)';
   1.289 +                  END IF;
   1.290 +                  "i" := "i" + 1;
   1.291 +                END LOOP;
   1.292 +                -- calculate best beat-paths while ignoring forbidden links:
   1.293 +                "i" := 1;
   1.294 +                LOOP
   1.295 +                  "j" := 1;
   1.296 +                  LOOP
   1.297 +                    IF "i" != "j" THEN
   1.298 +                      "matrix_t"["i"]["j"] := CASE
   1.299 +                         WHEN "matrix_f"["i"]["j"]
   1.300 +                         THEN (-1::INT8) << 63  -- worst possible value
   1.301 +                         ELSE "matrix_d"["i"]["j"] END;
   1.302 +                    END IF;
   1.303 +                    EXIT WHEN "j" = "dimension_v";
   1.304 +                    "j" := "j" + 1;
   1.305 +                  END LOOP;
   1.306 +                  EXIT WHEN "i" = "dimension_v";
   1.307 +                  "i" := "i" + 1;
   1.308 +                END LOOP;
   1.309 +                "matrix_t" := "find_best_paths"("matrix_t");
   1.310 +                -- extend partial order, if tie-breaking was successful:
   1.311 +                IF "matrix_t"["m"]["n"] > "matrix_t"["n"]["m"] THEN
   1.312 +                  "matrix_b"["m"]["n"] := TRUE;
   1.313 +                  "matrix_b"["n"]["m"] := FALSE;
   1.314 +                  EXIT;
   1.315 +                ELSIF "matrix_t"["m"]["n"] < "matrix_t"["n"]["m"] THEN
   1.316 +                  "matrix_b"["m"]["n"] := FALSE;
   1.317 +                  "matrix_b"["n"]["m"] := TRUE;
   1.318 +                  EXIT;
   1.319 +                END IF;
   1.320 +              END LOOP;
   1.321 +            END IF;
   1.322 +            EXIT WHEN "n" = "dimension_v";
   1.323 +            "n" := "n" + 1;
   1.324 +          END LOOP;
   1.325 +          EXIT WHEN "m" = "dimension_v" - 1;
   1.326 +          "m" := "m" + 1;
   1.327 +        END LOOP;
   1.328 +      END IF;
   1.329 +      -- store a unique ranking in "rank_ary":
   1.330 +      "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
   1.331 +      "rank_v" := 1;
   1.332 +      LOOP
   1.333 +        "i" := 1;
   1.334 +        <<assign_next_rank>>
   1.335 +        LOOP
   1.336 +          IF "rank_ary"["i"] ISNULL THEN
   1.337 +            "j" := 1;
   1.338 +            LOOP
   1.339 +              IF
   1.340 +                "i" != "j" AND
   1.341 +                "rank_ary"["j"] ISNULL AND
   1.342 +                ( "matrix_b"["j"]["i"] OR
   1.343 +                  -- tie-breaking by "id"
   1.344 +                  ( "matrix_b"["j"]["i"] ISNULL AND
   1.345 +                    "j" < "i" ) )
   1.346 +              THEN
   1.347 +                -- someone else is better
   1.348 +                EXIT;
   1.349 +              END IF;
   1.350 +              IF "j" = "dimension_v" THEN
   1.351 +                -- noone is better
   1.352 +                "rank_ary"["i"] := "rank_v";
   1.353 +                EXIT assign_next_rank;
   1.354 +              END IF;
   1.355 +              "j" := "j" + 1;
   1.356 +            END LOOP;
   1.357 +          END IF;
   1.358 +          "i" := "i" + 1;
   1.359 +          IF "i" > "dimension_v" THEN
   1.360 +            RAISE EXCEPTION 'Schulze ranking does not compute (should not happen)';
   1.361 +          END IF;
   1.362 +        END LOOP;
   1.363 +        EXIT WHEN "rank_v" = "dimension_v";
   1.364 +        "rank_v" := "rank_v" + 1;
   1.365 +      END LOOP;
   1.366 +      -- write preliminary results:
   1.367 +      "i" := 2;  -- omit status quo with "i" = 1
   1.368 +      FOR "initiative_id_v" IN
   1.369 +        SELECT "id" FROM "initiative"
   1.370 +        WHERE "issue_id" = "issue_id_p" AND "admitted"
   1.371 +        ORDER BY "id"
   1.372 +      LOOP
   1.373 +        UPDATE "initiative" SET
   1.374 +          "direct_majority" =
   1.375 +            CASE WHEN "policy_row"."direct_majority_strict" THEN
   1.376 +              "positive_votes" * "policy_row"."direct_majority_den" >
   1.377 +              "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
   1.378 +            ELSE
   1.379 +              "positive_votes" * "policy_row"."direct_majority_den" >=
   1.380 +              "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
   1.381 +            END
   1.382 +            AND "positive_votes" >= "policy_row"."direct_majority_positive"
   1.383 +            AND "issue_row"."voter_count"-"negative_votes" >=
   1.384 +                "policy_row"."direct_majority_non_negative",
   1.385 +            "indirect_majority" =
   1.386 +            CASE WHEN "policy_row"."indirect_majority_strict" THEN
   1.387 +              "positive_votes" * "policy_row"."indirect_majority_den" >
   1.388 +              "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
   1.389 +            ELSE
   1.390 +              "positive_votes" * "policy_row"."indirect_majority_den" >=
   1.391 +              "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
   1.392 +            END
   1.393 +            AND "positive_votes" >= "policy_row"."indirect_majority_positive"
   1.394 +            AND "issue_row"."voter_count"-"negative_votes" >=
   1.395 +                "policy_row"."indirect_majority_non_negative",
   1.396 +          "schulze_rank"           = "rank_ary"["i"],
   1.397 +          "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"[1],
   1.398 +          "worse_than_status_quo"  = "rank_ary"["i"] > "rank_ary"[1],
   1.399 +          "multistage_majority"    = "rank_ary"["i"] >= "rank_ary"[1],
   1.400 +          "reverse_beat_path"      = CASE WHEN "policy_row"."defeat_strength" = 'simple'::"defeat_strength"
   1.401 +                                     THEN NULL
   1.402 +                                     ELSE "matrix_p"[1]["i"]."primary" >= 0 END,
   1.403 +          "eligible"               = FALSE,
   1.404 +          "winner"                 = FALSE,
   1.405 +          "rank"                   = NULL  -- NOTE: in cases of manual reset of issue state
   1.406 +          WHERE "id" = "initiative_id_v";
   1.407 +        "i" := "i" + 1;
   1.408 +      END LOOP;
   1.409 +      IF "i" != "dimension_v" + 1 THEN
   1.410 +        RAISE EXCEPTION 'Wrong winner count (should not happen)';
   1.411 +      END IF;
   1.412 +      -- take indirect majorities into account:
   1.413 +      LOOP
   1.414 +        UPDATE "initiative" SET "indirect_majority" = TRUE
   1.415 +          FROM (
   1.416 +            SELECT "new_initiative"."id" AS "initiative_id"
   1.417 +            FROM "initiative" "old_initiative"
   1.418 +            JOIN "initiative" "new_initiative"
   1.419 +              ON "new_initiative"."issue_id" = "issue_id_p"
   1.420 +              AND "new_initiative"."indirect_majority" = FALSE
   1.421 +            JOIN "battle" "battle_win"
   1.422 +              ON "battle_win"."issue_id" = "issue_id_p"
   1.423 +              AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
   1.424 +              AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
   1.425 +            JOIN "battle" "battle_lose"
   1.426 +              ON "battle_lose"."issue_id" = "issue_id_p"
   1.427 +              AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
   1.428 +              AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
   1.429 +            WHERE "old_initiative"."issue_id" = "issue_id_p"
   1.430 +            AND "old_initiative"."indirect_majority" = TRUE
   1.431 +            AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
   1.432 +              "battle_win"."count" * "policy_row"."indirect_majority_den" >
   1.433 +              "policy_row"."indirect_majority_num" *
   1.434 +              ("battle_win"."count"+"battle_lose"."count")
   1.435 +            ELSE
   1.436 +              "battle_win"."count" * "policy_row"."indirect_majority_den" >=
   1.437 +              "policy_row"."indirect_majority_num" *
   1.438 +              ("battle_win"."count"+"battle_lose"."count")
   1.439 +            END
   1.440 +            AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
   1.441 +            AND "issue_row"."voter_count"-"battle_lose"."count" >=
   1.442 +                "policy_row"."indirect_majority_non_negative"
   1.443 +          ) AS "subquery"
   1.444 +          WHERE "id" = "subquery"."initiative_id";
   1.445 +        EXIT WHEN NOT FOUND;
   1.446 +      END LOOP;
   1.447 +      -- set "multistage_majority" for remaining matching initiatives:
   1.448 +      UPDATE "initiative" SET "multistage_majority" = TRUE
   1.449 +        FROM (
   1.450 +          SELECT "losing_initiative"."id" AS "initiative_id"
   1.451 +          FROM "initiative" "losing_initiative"
   1.452 +          JOIN "initiative" "winning_initiative"
   1.453 +            ON "winning_initiative"."issue_id" = "issue_id_p"
   1.454 +            AND "winning_initiative"."admitted"
   1.455 +          JOIN "battle" "battle_win"
   1.456 +            ON "battle_win"."issue_id" = "issue_id_p"
   1.457 +            AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
   1.458 +            AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
   1.459 +          JOIN "battle" "battle_lose"
   1.460 +            ON "battle_lose"."issue_id" = "issue_id_p"
   1.461 +            AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
   1.462 +            AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
   1.463 +          WHERE "losing_initiative"."issue_id" = "issue_id_p"
   1.464 +          AND "losing_initiative"."admitted"
   1.465 +          AND "winning_initiative"."schulze_rank" <
   1.466 +              "losing_initiative"."schulze_rank"
   1.467 +          AND "battle_win"."count" > "battle_lose"."count"
   1.468 +          AND (
   1.469 +            "battle_win"."count" > "winning_initiative"."positive_votes" OR
   1.470 +            "battle_lose"."count" < "losing_initiative"."negative_votes" )
   1.471 +        ) AS "subquery"
   1.472 +        WHERE "id" = "subquery"."initiative_id";
   1.473 +      -- mark eligible initiatives:
   1.474 +      UPDATE "initiative" SET "eligible" = TRUE
   1.475 +        WHERE "issue_id" = "issue_id_p"
   1.476 +        AND "initiative"."direct_majority"
   1.477 +        AND "initiative"."indirect_majority"
   1.478 +        AND "initiative"."better_than_status_quo"
   1.479 +        AND (
   1.480 +          "policy_row"."no_multistage_majority" = FALSE OR
   1.481 +          "initiative"."multistage_majority" = FALSE )
   1.482 +        AND (
   1.483 +          "policy_row"."no_reverse_beat_path" = FALSE OR
   1.484 +          coalesce("initiative"."reverse_beat_path", FALSE) = FALSE );
   1.485 +      -- mark final winner:
   1.486 +      UPDATE "initiative" SET "winner" = TRUE
   1.487 +        FROM (
   1.488 +          SELECT "id" AS "initiative_id"
   1.489 +          FROM "initiative"
   1.490 +          WHERE "issue_id" = "issue_id_p" AND "eligible"
   1.491 +          ORDER BY
   1.492 +            "schulze_rank",
   1.493 +            "id"
   1.494 +          LIMIT 1
   1.495 +        ) AS "subquery"
   1.496 +        WHERE "id" = "subquery"."initiative_id";
   1.497 +      -- write (final) ranks:
   1.498 +      "rank_v" := 1;
   1.499 +      FOR "initiative_id_v" IN
   1.500 +        SELECT "id"
   1.501 +        FROM "initiative"
   1.502 +        WHERE "issue_id" = "issue_id_p" AND "admitted"
   1.503 +        ORDER BY
   1.504 +          "winner" DESC,
   1.505 +          "eligible" DESC,
   1.506 +          "schulze_rank",
   1.507 +          "id"
   1.508 +      LOOP
   1.509 +        UPDATE "initiative" SET "rank" = "rank_v"
   1.510 +          WHERE "id" = "initiative_id_v";
   1.511 +        "rank_v" := "rank_v" + 1;
   1.512 +      END LOOP;
   1.513 +      -- set schulze rank of status quo and mark issue as finished:
   1.514 +      UPDATE "issue" SET
   1.515 +        "status_quo_schulze_rank" = "rank_ary"[1],
   1.516 +        "state" =
   1.517 +          CASE WHEN EXISTS (
   1.518 +            SELECT NULL FROM "initiative"
   1.519 +            WHERE "issue_id" = "issue_id_p" AND "winner"
   1.520 +          ) THEN
   1.521 +            'finished_with_winner'::"issue_state"
   1.522 +          ELSE
   1.523 +            'finished_without_winner'::"issue_state"
   1.524 +          END,
   1.525 +        "closed" = "phase_finished",
   1.526 +        "phase_finished" = NULL
   1.527 +        WHERE "id" = "issue_id_p";
   1.528 +      RETURN;
   1.529 +    END;
   1.530 +  $$;
   1.531 +
   1.532  
   1.533  COMMIT;