jbe@436: BEGIN;
jbe@436: 
jbe@436: CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@436:   SELECT * FROM (VALUES ('3.0.3', 3, 0, 3))
jbe@436:   AS "subquery"("string", "major", "minor", "revision");
jbe@436: 
jbe@436: CREATE FUNCTION "update3_0_3_add_columns_if_missing"()
jbe@436:   RETURNS VOID
jbe@436:   LANGUAGE 'plpgsql' AS $$
jbe@436:     BEGIN
jbe@436:       BEGIN
jbe@436:         ALTER TABLE "initiative" ADD COLUMN "first_preference_votes" INT4;
jbe@436:       EXCEPTION
jbe@436:         WHEN duplicate_column THEN
jbe@436:           RAISE NOTICE 'column "first_preference_votes" of relation "initiative" already exists, skipping';
jbe@436:       END;
jbe@436:       BEGIN
jbe@436:         ALTER TABLE "vote" ADD COLUMN "first_preference" BOOLEAN;
jbe@436:       EXCEPTION
jbe@436:         WHEN duplicate_column THEN
jbe@436:           RAISE NOTICE 'column "first_preference" of relation "vote" already exists, skipping';
jbe@436:       END;
jbe@436:       RETURN;
jbe@436:     END;
jbe@436:   $$;
jbe@436: 
jbe@436: SELECT "update3_0_3_add_columns_if_missing"();
jbe@436: 
jbe@436: DROP FUNCTION "update3_0_3_add_columns_if_missing"();
jbe@436: 
jbe@436: ALTER TABLE "initiative" DROP CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results";
jbe@436: ALTER TABLE "initiative" ADD CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
jbe@436:           ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
jbe@436:           ( "first_preference_votes" ISNULL AND
jbe@436:             "positive_votes" ISNULL AND "negative_votes" ISNULL AND
jbe@436:             "direct_majority" ISNULL AND "indirect_majority" ISNULL AND
jbe@436:             "schulze_rank" ISNULL AND
jbe@436:             "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND
jbe@436:             "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND
jbe@436:             "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) );
jbe@436: 
jbe@436: COMMENT ON COLUMN "initiative"."first_preference_votes" IS 'Number of direct and delegating voters who ranked this initiative as their first choice';
jbe@436: COMMENT ON COLUMN "initiative"."positive_votes"         IS 'Number of direct and delegating voters who ranked this initiative better than the status quo';
jbe@436: COMMENT ON COLUMN "initiative"."negative_votes"         IS 'Number of direct and delegating voters who ranked this initiative worse than the status quo';
jbe@436: 
jbe@436: -- UPDATE TABLE "vote" SET "grade" = 0 WHERE "grade" ISNULL;  -- should not be necessary
jbe@436: ALTER TABLE "vote" ALTER COLUMN "grade" SET NOT NULL;
jbe@436: 
jbe@436: ALTER TABLE "vote" DROP CONSTRAINT IF EXISTS "first_preference_flag_only_set_on_positive_grades";
jbe@436: ALTER TABLE "vote" ADD
jbe@436:         CONSTRAINT "first_preference_flag_only_set_on_positive_grades"
jbe@436:         CHECK ("grade" > 0 OR "first_preference" ISNULL);
jbe@436: 
jbe@436: COMMENT ON COLUMN "vote"."first_preference" IS 'Value is automatically set after voting is finished. For positive grades, this value is set to true for the highest (i.e. best) grade.';
jbe@436:  
jbe@436: INSERT INTO "temporary_transaction_data" ("key", "value")
jbe@436:   VALUES ('override_protection_triggers', TRUE::TEXT);
jbe@436: 
jbe@436: UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
jbe@436:   FROM (
jbe@436:     SELECT
jbe@436:       "vote"."initiative_id",
jbe@436:       "vote"."member_id",
jbe@436:       CASE WHEN "vote"."grade" > 0 THEN
jbe@436:         CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
jbe@436:       ELSE NULL
jbe@436:       END AS "first_preference"
jbe@436:     FROM "vote"
jbe@436:     JOIN "initiative"  -- NOTE: due to missing index on issue_id
jbe@436:     ON "vote"."issue_id" = "initiative"."issue_id"
jbe@436:     JOIN "vote" AS "agg"
jbe@436:     ON "initiative"."id" = "agg"."initiative_id"
jbe@436:     AND "vote"."member_id" = "agg"."member_id"
jbe@436:     GROUP BY "vote"."initiative_id", "vote"."member_id"
jbe@436:   ) AS "subquery"
jbe@436:   WHERE "vote"."initiative_id" = "subquery"."initiative_id"
jbe@436:   AND "vote"."member_id" = "subquery"."member_id";
jbe@436: 
jbe@436: DELETE FROM "temporary_transaction_data"
jbe@436:   WHERE "key" = 'override_protection_triggers';
jbe@436: 
jbe@436: UPDATE "initiative" SET "first_preference_votes" = NULL
jbe@436:   WHERE "first_preference_votes" = 0;
jbe@436: 
jbe@436: UPDATE "initiative"
jbe@436:   SET "first_preference_votes" = "subquery"."sum"
jbe@436:   FROM (
jbe@436:     SELECT "vote"."initiative_id", sum("direct_voter"."weight")
jbe@436:     FROM "vote" JOIN "direct_voter"
jbe@436:     ON "vote"."issue_id" = "direct_voter"."issue_id"
jbe@436:     AND "vote"."member_id" = "direct_voter"."member_id"
jbe@436:     WHERE "vote"."first_preference"
jbe@436:     GROUP BY "vote"."initiative_id"
jbe@436:   ) AS "subquery"
jbe@436:   WHERE "initiative"."admitted"
jbe@436:   AND "initiative"."id" = "subquery"."initiative_id"
jbe@436:   AND "initiative"."first_preference_votes" ISNULL;
jbe@436: 
jbe@436: UPDATE "initiative" SET "first_preference_votes" = 0
jbe@436:   WHERE "positive_votes" NOTNULL
jbe@436:   AND "first_preference_votes" ISNULL;
jbe@436: 
jbe@436: -- reconstruct battle data (originating from LiquidFeedback Core before v2.0.0)
jbe@436: -- to avoid future data loss when executing "clean_issue" to delete voting data:
jbe@436: INSERT INTO "battle" (
jbe@436:     "issue_id",
jbe@436:     "winning_initiative_id",
jbe@436:     "losing_initiative_id",
jbe@436:     "count"
jbe@436:   ) SELECT
jbe@436:     "battle_view"."issue_id",
jbe@436:     "battle_view"."winning_initiative_id",
jbe@436:     "battle_view"."losing_initiative_id",
jbe@436:     "battle_view"."count"
jbe@436:   FROM (
jbe@436:     SELECT
jbe@436:       "issue"."id" AS "issue_id",
jbe@436:       "winning_initiative"."id" AS "winning_initiative_id",
jbe@436:       "losing_initiative"."id" AS "losing_initiative_id",
jbe@436:       sum(
jbe@436:         CASE WHEN
jbe@436:           coalesce("better_vote"."grade", 0) >
jbe@436:           coalesce("worse_vote"."grade", 0)
jbe@436:         THEN "direct_voter"."weight" ELSE 0 END
jbe@436:       ) AS "count"
jbe@436:     FROM "issue"
jbe@436:     LEFT JOIN "direct_voter"
jbe@436:     ON "issue"."id" = "direct_voter"."issue_id"
jbe@436:     JOIN "battle_participant" AS "winning_initiative"
jbe@436:       ON "issue"."id" = "winning_initiative"."issue_id"
jbe@436:     JOIN "battle_participant" AS "losing_initiative"
jbe@436:       ON "issue"."id" = "losing_initiative"."issue_id"
jbe@436:     LEFT JOIN "vote" AS "better_vote"
jbe@436:       ON "direct_voter"."member_id" = "better_vote"."member_id"
jbe@436:       AND "winning_initiative"."id" = "better_vote"."initiative_id"
jbe@436:     LEFT JOIN "vote" AS "worse_vote"
jbe@436:       ON "direct_voter"."member_id" = "worse_vote"."member_id"
jbe@436:       AND "losing_initiative"."id" = "worse_vote"."initiative_id"
jbe@436:     WHERE "issue"."state" IN ('finished_with_winner', 'finished_without_winner')
jbe@436:     AND "winning_initiative"."id" != "losing_initiative"."id"
jbe@436:     -- NOTE: comparisons with status-quo are intentionally omitted to mark
jbe@436:     --       issues that were counted prior LiquidFeedback Core v2.0.0
jbe@436:     GROUP BY
jbe@436:       "issue"."id",
jbe@436:       "winning_initiative"."id",
jbe@436:       "losing_initiative"."id"
jbe@436:   ) AS "battle_view"
jbe@436:   LEFT JOIN "battle"
jbe@436:   ON "battle_view"."winning_initiative_id" = "battle"."winning_initiative_id"
jbe@436:   AND "battle_view"."losing_initiative_id" = "battle"."losing_initiative_id"
jbe@436:   WHERE "battle" ISNULL;
jbe@436: 
jbe@436: CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
jbe@436:   RETURNS VOID
jbe@436:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@436:     DECLARE
jbe@436:       "area_id_v"   "area"."id"%TYPE;
jbe@436:       "unit_id_v"   "unit"."id"%TYPE;
jbe@436:       "member_id_v" "member"."id"%TYPE;
jbe@436:     BEGIN
jbe@436:       PERFORM "require_transaction_isolation"();
jbe@436:       SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
jbe@436:       SELECT "unit_id" INTO "unit_id_v" FROM "area"  WHERE "id" = "area_id_v";
jbe@436:       -- override protection triggers:
jbe@436:       INSERT INTO "temporary_transaction_data" ("key", "value")
jbe@436:         VALUES ('override_protection_triggers', TRUE::TEXT);
jbe@436:       -- delete timestamp of voting comment:
jbe@436:       UPDATE "direct_voter" SET "comment_changed" = NULL
jbe@436:         WHERE "issue_id" = "issue_id_p";
jbe@436:       -- delete delegating votes (in cases of manual reset of issue state):
jbe@436:       DELETE FROM "delegating_voter"
jbe@436:         WHERE "issue_id" = "issue_id_p";
jbe@436:       -- delete votes from non-privileged voters:
jbe@436:       DELETE FROM "direct_voter"
jbe@436:         USING (
jbe@436:           SELECT
jbe@436:             "direct_voter"."member_id"
jbe@436:           FROM "direct_voter"
jbe@436:           JOIN "member" ON "direct_voter"."member_id" = "member"."id"
jbe@436:           LEFT JOIN "privilege"
jbe@436:           ON "privilege"."unit_id" = "unit_id_v"
jbe@436:           AND "privilege"."member_id" = "direct_voter"."member_id"
jbe@436:           WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
jbe@436:             "member"."active" = FALSE OR
jbe@436:             "privilege"."voting_right" ISNULL OR
jbe@436:             "privilege"."voting_right" = FALSE
jbe@436:           )
jbe@436:         ) AS "subquery"
jbe@436:         WHERE "direct_voter"."issue_id" = "issue_id_p"
jbe@436:         AND "direct_voter"."member_id" = "subquery"."member_id";
jbe@436:       -- consider delegations:
jbe@436:       UPDATE "direct_voter" SET "weight" = 1
jbe@436:         WHERE "issue_id" = "issue_id_p";
jbe@436:       PERFORM "add_vote_delegations"("issue_id_p");
jbe@436:       -- mark first preferences:
jbe@436:       UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
jbe@436:         FROM (
jbe@436:           SELECT
jbe@436:             "vote"."initiative_id",
jbe@436:             "vote"."member_id",
jbe@436:             CASE WHEN "vote"."grade" > 0 THEN
jbe@436:               CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
jbe@436:             ELSE NULL
jbe@436:             END AS "first_preference"
jbe@436:           FROM "vote"
jbe@436:           JOIN "initiative"  -- NOTE: due to missing index on issue_id
jbe@436:           ON "vote"."issue_id" = "initiative"."issue_id"
jbe@436:           JOIN "vote" AS "agg"
jbe@436:           ON "initiative"."id" = "agg"."initiative_id"
jbe@436:           AND "vote"."member_id" = "agg"."member_id"
jbe@436:           GROUP BY "vote"."initiative_id", "vote"."member_id", "vote"."grade"
jbe@436:         ) AS "subquery"
jbe@436:         WHERE "vote"."issue_id" = "issue_id_p"
jbe@436:         AND "vote"."initiative_id" = "subquery"."initiative_id"
jbe@436:         AND "vote"."member_id" = "subquery"."member_id";
jbe@436:       -- finish overriding protection triggers (avoids garbage):
jbe@436:       DELETE FROM "temporary_transaction_data"
jbe@436:         WHERE "key" = 'override_protection_triggers';
jbe@436:       -- materialize battle_view:
jbe@436:       -- NOTE: "closed" column of issue must be set at this point
jbe@436:       DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@436:       INSERT INTO "battle" (
jbe@436:         "issue_id",
jbe@436:         "winning_initiative_id", "losing_initiative_id",
jbe@436:         "count"
jbe@436:       ) SELECT
jbe@436:         "issue_id",
jbe@436:         "winning_initiative_id", "losing_initiative_id",
jbe@436:         "count"
jbe@436:         FROM "battle_view" WHERE "issue_id" = "issue_id_p";
jbe@436:       -- set voter count:
jbe@436:       UPDATE "issue" SET
jbe@436:         "voter_count" = (
jbe@436:           SELECT coalesce(sum("weight"), 0)
jbe@436:           FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
jbe@436:         )
jbe@436:         WHERE "id" = "issue_id_p";
jbe@437:       -- copy "positive_votes" and "negative_votes" from "battle" table:
jbe@437:       -- NOTE: "first_preference_votes" is set to a default of 0 at this step
jbe@437:       UPDATE "initiative" SET
jbe@437:         "first_preference_votes" = 0,
jbe@437:         "positive_votes" = "battle_win"."count",
jbe@437:         "negative_votes" = "battle_lose"."count"
jbe@437:         FROM "battle" AS "battle_win", "battle" AS "battle_lose"
jbe@437:         WHERE
jbe@437:           "battle_win"."issue_id" = "issue_id_p" AND
jbe@437:           "battle_win"."winning_initiative_id" = "initiative"."id" AND
jbe@437:           "battle_win"."losing_initiative_id" ISNULL AND
jbe@437:           "battle_lose"."issue_id" = "issue_id_p" AND
jbe@437:           "battle_lose"."losing_initiative_id" = "initiative"."id" AND
jbe@437:           "battle_lose"."winning_initiative_id" ISNULL;
jbe@436:       -- calculate "first_preference_votes":
jbe@437:       -- NOTE: will only set values not equal to zero
jbe@437:       UPDATE "initiative" SET "first_preference_votes" = "subquery"."sum"
jbe@436:         FROM (
jbe@436:           SELECT "vote"."initiative_id", sum("direct_voter"."weight")
jbe@436:           FROM "vote" JOIN "direct_voter"
jbe@436:           ON "vote"."issue_id" = "direct_voter"."issue_id"
jbe@436:           AND "vote"."member_id" = "direct_voter"."member_id"
jbe@436:           WHERE "vote"."first_preference"
jbe@436:           GROUP BY "vote"."initiative_id"
jbe@436:         ) AS "subquery"
jbe@436:         WHERE "initiative"."issue_id" = "issue_id_p"
jbe@436:         AND "initiative"."admitted"
jbe@436:         AND "initiative"."id" = "subquery"."initiative_id";
jbe@436:     END;
jbe@436:   $$;
jbe@436: 
jbe@436: COMMIT;