jbe@396: BEGIN;
jbe@396: 
jbe@396: CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@412:   SELECT * FROM (VALUES ('3.0.0', 3, 0, 0))
jbe@396:   AS "subquery"("string", "major", "minor", "revision");
jbe@396: 
jbe@410: CREATE TABLE "issue_order_in_admission_state" (
jbe@400:         "id"                    INT8            PRIMARY KEY, --REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@410:         "order_in_area"         INT4,
jbe@410:         "order_in_unit"         INT4 );
jbe@396: 
jbe@410: COMMENT ON TABLE "issue_order_in_admission_state" IS 'Ordering information for issues that are not stored in the "issue" table to avoid locking of multiple issues at once; Filled/updated by "lf_update_issue_order"';
jbe@398: 
jbe@410: COMMENT ON COLUMN "issue_order_in_admission_state"."id"            IS 'References "issue" ("id") but has no referential integrity trigger associated, due to performance/locking issues';
jbe@410: COMMENT ON COLUMN "issue_order_in_admission_state"."order_in_area" IS 'Order of issues in admission state within a single area; NULL values sort last';
jbe@410: COMMENT ON COLUMN "issue_order_in_admission_state"."order_in_unit" IS 'Order of issues in admission state within all areas of a unit; NULL values sort last';
jbe@396: 
jbe@396: CREATE VIEW "issue_supporter_in_admission_state" AS
jbe@396:   SELECT DISTINCT
jbe@410:     "area"."unit_id",
jbe@396:     "issue"."area_id",
jbe@396:     "issue"."id" AS "issue_id",
jbe@396:     "supporter"."member_id",
jbe@396:     "direct_interest_snapshot"."weight"
jbe@396:   FROM "issue"
jbe@410:   JOIN "area" ON "area"."id" = "issue"."area_id"
jbe@396:   JOIN "supporter" ON "supporter"."issue_id" = "issue"."id"
jbe@396:   JOIN "direct_interest_snapshot"
jbe@396:     ON  "direct_interest_snapshot"."issue_id" = "issue"."id"
jbe@396:     AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"
jbe@396:     AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"
jbe@396:   WHERE "issue"."state" = 'admission'::"issue_state";
jbe@396: 
jbe@396: COMMENT ON VIEW "issue_supporter_in_admission_state" IS 'Helper view for "lf_update_issue_order" to allow a (proportional) ordering of issues within an area';
jbe@396: 
jbe@411: COMMENT ON COLUMN "initiative"."schulze_rank"           IS 'Schulze-Ranking';
jbe@411: COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo';
jbe@411: COMMENT ON COLUMN "initiative"."worse_than_status_quo"  IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (DEPRECATED, since schulze-ranking is unique per issue; use "better_than_status_quo"=FALSE)';
jbe@411: COMMENT ON COLUMN "initiative"."winner"                 IS 'Winner is the "eligible" initiative with best "schulze_rank"';
jbe@411: 
jbe@411: CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
jbe@411:   RETURNS VOID
jbe@411:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@411:     DECLARE
jbe@411:       "issue_row"         "issue"%ROWTYPE;
jbe@411:       "policy_row"        "policy"%ROWTYPE;
jbe@411:       "dimension_v"       INTEGER;
jbe@411:       "vote_matrix"       INT4[][];  -- absolute votes
jbe@411:       "matrix"            INT8[][];  -- defeat strength / best paths
jbe@411:       "i"                 INTEGER;
jbe@411:       "j"                 INTEGER;
jbe@411:       "k"                 INTEGER;
jbe@411:       "battle_row"        "battle"%ROWTYPE;
jbe@411:       "rank_ary"          INT4[];
jbe@411:       "rank_v"            INT4;
jbe@411:       "initiative_id_v"   "initiative"."id"%TYPE;
jbe@411:     BEGIN
jbe@411:       PERFORM "require_transaction_isolation"();
jbe@411:       SELECT * INTO "issue_row"
jbe@411:         FROM "issue" WHERE "id" = "issue_id_p";
jbe@411:       SELECT * INTO "policy_row"
jbe@411:         FROM "policy" WHERE "id" = "issue_row"."policy_id";
jbe@411:       SELECT count(1) INTO "dimension_v"
jbe@411:         FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
jbe@411:       -- Create "vote_matrix" with absolute number of votes in pairwise
jbe@411:       -- comparison:
jbe@411:       "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
jbe@411:       "i" := 1;
jbe@411:       "j" := 2;
jbe@411:       FOR "battle_row" IN
jbe@411:         SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
jbe@411:         ORDER BY
jbe@411:         "winning_initiative_id" NULLS FIRST,
jbe@411:         "losing_initiative_id" NULLS FIRST
jbe@411:       LOOP
jbe@411:         "vote_matrix"["i"]["j"] := "battle_row"."count";
jbe@411:         IF "j" = "dimension_v" THEN
jbe@411:           "i" := "i" + 1;
jbe@411:           "j" := 1;
jbe@411:         ELSE
jbe@411:           "j" := "j" + 1;
jbe@411:           IF "j" = "i" THEN
jbe@411:             "j" := "j" + 1;
jbe@411:           END IF;
jbe@411:         END IF;
jbe@411:       END LOOP;
jbe@411:       IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
jbe@411:         RAISE EXCEPTION 'Wrong battle count (should not happen)';
jbe@411:       END IF;
jbe@411:       -- Store defeat strengths in "matrix" using "defeat_strength"
jbe@411:       -- function:
jbe@411:       "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
jbe@411:       "i" := 1;
jbe@411:       LOOP
jbe@411:         "j" := 1;
jbe@411:         LOOP
jbe@411:           IF "i" != "j" THEN
jbe@411:             "matrix"["i"]["j"] := "defeat_strength"(
jbe@411:               "vote_matrix"["i"]["j"],
jbe@411:               "vote_matrix"["j"]["i"]
jbe@411:             );
jbe@411:           END IF;
jbe@411:           EXIT WHEN "j" = "dimension_v";
jbe@411:           "j" := "j" + 1;
jbe@411:         END LOOP;
jbe@411:         EXIT WHEN "i" = "dimension_v";
jbe@411:         "i" := "i" + 1;
jbe@411:       END LOOP;
jbe@411:       -- Find best paths:
jbe@411:       "i" := 1;
jbe@411:       LOOP
jbe@411:         "j" := 1;
jbe@411:         LOOP
jbe@411:           IF "i" != "j" THEN
jbe@411:             "k" := 1;
jbe@411:             LOOP
jbe@411:               IF "i" != "k" AND "j" != "k" THEN
jbe@411:                 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
jbe@411:                   IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
jbe@411:                     "matrix"["j"]["k"] := "matrix"["j"]["i"];
jbe@411:                   END IF;
jbe@411:                 ELSE
jbe@411:                   IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
jbe@411:                     "matrix"["j"]["k"] := "matrix"["i"]["k"];
jbe@411:                   END IF;
jbe@411:                 END IF;
jbe@411:               END IF;
jbe@411:               EXIT WHEN "k" = "dimension_v";
jbe@411:               "k" := "k" + 1;
jbe@411:             END LOOP;
jbe@411:           END IF;
jbe@411:           EXIT WHEN "j" = "dimension_v";
jbe@411:           "j" := "j" + 1;
jbe@411:         END LOOP;
jbe@411:         EXIT WHEN "i" = "dimension_v";
jbe@411:         "i" := "i" + 1;
jbe@411:       END LOOP;
jbe@411:       -- Determine order of winners:
jbe@411:       "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
jbe@411:       "rank_v" := 1;
jbe@411:       LOOP
jbe@411:         "i" := 1;
jbe@411:         LOOP
jbe@411:           IF "rank_ary"["i"] ISNULL THEN
jbe@411:             "j" := 1;
jbe@411:             LOOP
jbe@411:               IF
jbe@411:                 "i" != "j" AND
jbe@411:                 "rank_ary"["j"] ISNULL AND
jbe@411:                 ( "matrix"["j"]["i"] > "matrix"["i"]["j"] OR
jbe@411:                   -- tie-breaking by "id"
jbe@411:                   ( "matrix"["j"]["i"] = "matrix"["i"]["j"] AND
jbe@411:                     "j" < "i" ) )
jbe@411:               THEN
jbe@411:                 -- someone else is better
jbe@411:                 EXIT;
jbe@411:               END IF;
jbe@411:               "j" := "j" + 1;
jbe@411:               IF "j" = "dimension_v" + 1 THEN
jbe@411:                 -- noone is better
jbe@411:                 "rank_ary"["i"] := "rank_v";
jbe@411:                 EXIT;
jbe@411:               END IF;
jbe@411:             END LOOP;
jbe@411:             EXIT WHEN "j" = "dimension_v" + 1;
jbe@411:           END IF;
jbe@411:           "i" := "i" + 1;
jbe@411:           IF "i" > "dimension_v" THEN
jbe@411:             RAISE EXCEPTION 'Schulze ranking does not compute (should not happen)';
jbe@411:           END IF;
jbe@411:         END LOOP;
jbe@411:         EXIT WHEN "rank_v" = "dimension_v";
jbe@411:         "rank_v" := "rank_v" + 1;
jbe@411:       END LOOP;
jbe@411:       -- write preliminary results:
jbe@411:       "i" := 2;  -- omit status quo with "i" = 1
jbe@411:       FOR "initiative_id_v" IN
jbe@411:         SELECT "id" FROM "initiative"
jbe@411:         WHERE "issue_id" = "issue_id_p" AND "admitted"
jbe@411:         ORDER BY "id"
jbe@411:       LOOP
jbe@411:         UPDATE "initiative" SET
jbe@411:           "direct_majority" =
jbe@411:             CASE WHEN "policy_row"."direct_majority_strict" THEN
jbe@411:               "positive_votes" * "policy_row"."direct_majority_den" >
jbe@411:               "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
jbe@411:             ELSE
jbe@411:               "positive_votes" * "policy_row"."direct_majority_den" >=
jbe@411:               "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
jbe@411:             END
jbe@411:             AND "positive_votes" >= "policy_row"."direct_majority_positive"
jbe@411:             AND "issue_row"."voter_count"-"negative_votes" >=
jbe@411:                 "policy_row"."direct_majority_non_negative",
jbe@411:             "indirect_majority" =
jbe@411:             CASE WHEN "policy_row"."indirect_majority_strict" THEN
jbe@411:               "positive_votes" * "policy_row"."indirect_majority_den" >
jbe@411:               "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
jbe@411:             ELSE
jbe@411:               "positive_votes" * "policy_row"."indirect_majority_den" >=
jbe@411:               "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
jbe@411:             END
jbe@411:             AND "positive_votes" >= "policy_row"."indirect_majority_positive"
jbe@411:             AND "issue_row"."voter_count"-"negative_votes" >=
jbe@411:                 "policy_row"."indirect_majority_non_negative",
jbe@411:           "schulze_rank"           = "rank_ary"["i"],
jbe@411:           "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"[1],
jbe@411:           "worse_than_status_quo"  = "rank_ary"["i"] > "rank_ary"[1],
jbe@411:           "multistage_majority"    = "rank_ary"["i"] >= "rank_ary"[1],
jbe@411:           "reverse_beat_path"      = "matrix"[1]["i"] >= 0,
jbe@411:           "eligible"               = FALSE,
jbe@411:           "winner"                 = FALSE,
jbe@411:           "rank"                   = NULL  -- NOTE: in cases of manual reset of issue state
jbe@411:           WHERE "id" = "initiative_id_v";
jbe@411:         "i" := "i" + 1;
jbe@411:       END LOOP;
jbe@411:       IF "i" != "dimension_v" + 1 THEN
jbe@411:         RAISE EXCEPTION 'Wrong winner count (should not happen)';
jbe@411:       END IF;
jbe@411:       -- take indirect majorities into account:
jbe@411:       LOOP
jbe@411:         UPDATE "initiative" SET "indirect_majority" = TRUE
jbe@411:           FROM (
jbe@411:             SELECT "new_initiative"."id" AS "initiative_id"
jbe@411:             FROM "initiative" "old_initiative"
jbe@411:             JOIN "initiative" "new_initiative"
jbe@411:               ON "new_initiative"."issue_id" = "issue_id_p"
jbe@411:               AND "new_initiative"."indirect_majority" = FALSE
jbe@411:             JOIN "battle" "battle_win"
jbe@411:               ON "battle_win"."issue_id" = "issue_id_p"
jbe@411:               AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
jbe@411:               AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
jbe@411:             JOIN "battle" "battle_lose"
jbe@411:               ON "battle_lose"."issue_id" = "issue_id_p"
jbe@411:               AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
jbe@411:               AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
jbe@411:             WHERE "old_initiative"."issue_id" = "issue_id_p"
jbe@411:             AND "old_initiative"."indirect_majority" = TRUE
jbe@411:             AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
jbe@411:               "battle_win"."count" * "policy_row"."indirect_majority_den" >
jbe@411:               "policy_row"."indirect_majority_num" *
jbe@411:               ("battle_win"."count"+"battle_lose"."count")
jbe@411:             ELSE
jbe@411:               "battle_win"."count" * "policy_row"."indirect_majority_den" >=
jbe@411:               "policy_row"."indirect_majority_num" *
jbe@411:               ("battle_win"."count"+"battle_lose"."count")
jbe@411:             END
jbe@411:             AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
jbe@411:             AND "issue_row"."voter_count"-"battle_lose"."count" >=
jbe@411:                 "policy_row"."indirect_majority_non_negative"
jbe@411:           ) AS "subquery"
jbe@411:           WHERE "id" = "subquery"."initiative_id";
jbe@411:         EXIT WHEN NOT FOUND;
jbe@411:       END LOOP;
jbe@411:       -- set "multistage_majority" for remaining matching initiatives:
jbe@411:       UPDATE "initiative" SET "multistage_majority" = TRUE
jbe@411:         FROM (
jbe@411:           SELECT "losing_initiative"."id" AS "initiative_id"
jbe@411:           FROM "initiative" "losing_initiative"
jbe@411:           JOIN "initiative" "winning_initiative"
jbe@411:             ON "winning_initiative"."issue_id" = "issue_id_p"
jbe@411:             AND "winning_initiative"."admitted"
jbe@411:           JOIN "battle" "battle_win"
jbe@411:             ON "battle_win"."issue_id" = "issue_id_p"
jbe@411:             AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
jbe@411:             AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
jbe@411:           JOIN "battle" "battle_lose"
jbe@411:             ON "battle_lose"."issue_id" = "issue_id_p"
jbe@411:             AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
jbe@411:             AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
jbe@411:           WHERE "losing_initiative"."issue_id" = "issue_id_p"
jbe@411:           AND "losing_initiative"."admitted"
jbe@411:           AND "winning_initiative"."schulze_rank" <
jbe@411:               "losing_initiative"."schulze_rank"
jbe@411:           AND "battle_win"."count" > "battle_lose"."count"
jbe@411:           AND (
jbe@411:             "battle_win"."count" > "winning_initiative"."positive_votes" OR
jbe@411:             "battle_lose"."count" < "losing_initiative"."negative_votes" )
jbe@411:         ) AS "subquery"
jbe@411:         WHERE "id" = "subquery"."initiative_id";
jbe@411:       -- mark eligible initiatives:
jbe@411:       UPDATE "initiative" SET "eligible" = TRUE
jbe@411:         WHERE "issue_id" = "issue_id_p"
jbe@411:         AND "initiative"."direct_majority"
jbe@411:         AND "initiative"."indirect_majority"
jbe@411:         AND "initiative"."better_than_status_quo"
jbe@411:         AND (
jbe@411:           "policy_row"."no_multistage_majority" = FALSE OR
jbe@411:           "initiative"."multistage_majority" = FALSE )
jbe@411:         AND (
jbe@411:           "policy_row"."no_reverse_beat_path" = FALSE OR
jbe@411:           "initiative"."reverse_beat_path" = FALSE );
jbe@411:       -- mark final winner:
jbe@411:       UPDATE "initiative" SET "winner" = TRUE
jbe@411:         FROM (
jbe@411:           SELECT "id" AS "initiative_id"
jbe@411:           FROM "initiative"
jbe@411:           WHERE "issue_id" = "issue_id_p" AND "eligible"
jbe@411:           ORDER BY
jbe@411:             "schulze_rank",
jbe@411:             "id"
jbe@411:           LIMIT 1
jbe@411:         ) AS "subquery"
jbe@411:         WHERE "id" = "subquery"."initiative_id";
jbe@411:       -- write (final) ranks:
jbe@411:       "rank_v" := 1;
jbe@411:       FOR "initiative_id_v" IN
jbe@411:         SELECT "id"
jbe@411:         FROM "initiative"
jbe@411:         WHERE "issue_id" = "issue_id_p" AND "admitted"
jbe@411:         ORDER BY
jbe@411:           "winner" DESC,
jbe@411:           "eligible" DESC,
jbe@411:           "schulze_rank",
jbe@411:           "id"
jbe@411:       LOOP
jbe@411:         UPDATE "initiative" SET "rank" = "rank_v"
jbe@411:           WHERE "id" = "initiative_id_v";
jbe@411:         "rank_v" := "rank_v" + 1;
jbe@411:       END LOOP;
jbe@411:       -- set schulze rank of status quo and mark issue as finished:
jbe@411:       UPDATE "issue" SET
jbe@411:         "status_quo_schulze_rank" = "rank_ary"[1],
jbe@411:         "state" =
jbe@411:           CASE WHEN EXISTS (
jbe@411:             SELECT NULL FROM "initiative"
jbe@411:             WHERE "issue_id" = "issue_id_p" AND "winner"
jbe@411:           ) THEN
jbe@411:             'finished_with_winner'::"issue_state"
jbe@411:           ELSE
jbe@411:             'finished_without_winner'::"issue_state"
jbe@411:           END,
jbe@411:         "closed" = "phase_finished",
jbe@411:         "phase_finished" = NULL
jbe@411:         WHERE "id" = "issue_id_p";
jbe@411:       RETURN;
jbe@411:     END;
jbe@411:   $$;
jbe@411: 
jbe@396: COMMIT;