jbe@61: BEGIN;
jbe@61: 
jbe@61: CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@61:   SELECT * FROM (VALUES ('1.2.2', 1, 2, 2))
jbe@61:   AS "subquery"("string", "major", "minor", "revision");
jbe@61: 
jbe@61: ALTER TABLE "issue" DROP CONSTRAINT "clean_restriction";
jbe@61: 
jbe@61: ALTER TABLE "issue" ADD CONSTRAINT "only_closed_issues_may_be_cleaned"
jbe@61:   CHECK ("cleaned" ISNULL OR "closed" NOTNULL);
jbe@61: 
jbe@61: ALTER VIEW "battle" RENAME TO "battle_view";
jbe@61: 
jbe@61: CREATE TABLE "battle" (
jbe@61:         PRIMARY KEY ("issue_id", "winning_initiative_id", "losing_initiative_id"),
jbe@61:         "issue_id"              INT4,
jbe@61:         "winning_initiative_id" INT4,
jbe@61:         FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@61:         "losing_initiative_id"  INT4,
jbe@61:         FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@61:         "count"                 INT4            NOT NULL);
jbe@61: 
jbe@61: COMMENT ON TABLE "battle" IS 'Number of members preferring one initiative to another; Filled by "battle_view" when closing an issue';
jbe@61: 
jbe@61: CREATE OR REPLACE VIEW "battle_view" AS
jbe@61:   SELECT
jbe@61:     "issue"."id" AS "issue_id",
jbe@61:     "winning_initiative"."id" AS "winning_initiative_id",
jbe@61:     "losing_initiative"."id" AS "losing_initiative_id",
jbe@61:     sum(
jbe@61:       CASE WHEN
jbe@61:         coalesce("better_vote"."grade", 0) >
jbe@61:         coalesce("worse_vote"."grade", 0)
jbe@61:       THEN "direct_voter"."weight" ELSE 0 END
jbe@61:     ) AS "count"
jbe@61:   FROM "issue"
jbe@61:   LEFT JOIN "direct_voter"
jbe@61:   ON "issue"."id" = "direct_voter"."issue_id"
jbe@61:   JOIN "initiative" AS "winning_initiative"
jbe@61:     ON "issue"."id" = "winning_initiative"."issue_id"
jbe@61:     AND "winning_initiative"."agreed"
jbe@61:   JOIN "initiative" AS "losing_initiative"
jbe@61:     ON "issue"."id" = "losing_initiative"."issue_id"
jbe@61:     AND "losing_initiative"."agreed"
jbe@61:   LEFT JOIN "vote" AS "better_vote"
jbe@61:     ON "direct_voter"."member_id" = "better_vote"."member_id"
jbe@61:     AND "winning_initiative"."id" = "better_vote"."initiative_id"
jbe@61:   LEFT JOIN "vote" AS "worse_vote"
jbe@61:     ON "direct_voter"."member_id" = "worse_vote"."member_id"
jbe@61:     AND "losing_initiative"."id" = "worse_vote"."initiative_id"
jbe@61:   WHERE "issue"."closed" NOTNULL
jbe@61:   AND "issue"."cleaned" ISNULL
jbe@61:   AND "winning_initiative"."id" != "losing_initiative"."id"
jbe@61:   GROUP BY
jbe@61:     "issue"."id",
jbe@61:     "winning_initiative"."id",
jbe@61:     "losing_initiative"."id";
jbe@61: 
jbe@61: COMMENT ON VIEW "battle_view" IS 'Number of members preferring one initiative to another; Used to fill "battle" table';
jbe@61: 
jbe@61: INSERT INTO "battle" (
jbe@61:   "issue_id",
jbe@61:   "winning_initiative_id",
jbe@61:   "losing_initiative_id",
jbe@61:   "count"
jbe@61: ) SELECT
jbe@61:   "issue_id",
jbe@61:   "winning_initiative_id", "losing_initiative_id",
jbe@61:   "count"
jbe@61:   FROM "battle_view";
jbe@61: 
jbe@61: CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
jbe@61:   RETURNS VOID
jbe@61:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@61:     DECLARE
jbe@61:       "issue_row"   "issue"%ROWTYPE;
jbe@61:       "member_id_v" "member"."id"%TYPE;
jbe@61:     BEGIN
jbe@61:       PERFORM "global_lock"();
jbe@61:       SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@61:       DELETE FROM "delegating_voter"
jbe@61:         WHERE "issue_id" = "issue_id_p";
jbe@61:       DELETE FROM "direct_voter"
jbe@61:         WHERE "issue_id" = "issue_id_p"
jbe@61:         AND "autoreject" = TRUE;
jbe@61:       DELETE FROM "direct_voter" USING "member"
jbe@61:         WHERE "direct_voter"."member_id" = "member"."id"
jbe@61:         AND "direct_voter"."issue_id" = "issue_id_p"
jbe@61:         AND "member"."active" = FALSE;
jbe@61:       UPDATE "direct_voter" SET "weight" = 1
jbe@61:         WHERE "issue_id" = "issue_id_p";
jbe@61:       PERFORM "add_vote_delegations"("issue_id_p");
jbe@61:       FOR "member_id_v" IN
jbe@61:         SELECT "interest"."member_id"
jbe@61:           FROM "interest"
jbe@61:           LEFT JOIN "direct_voter"
jbe@61:             ON "interest"."member_id" = "direct_voter"."member_id"
jbe@61:             AND "interest"."issue_id" = "direct_voter"."issue_id"
jbe@61:           LEFT JOIN "delegating_voter"
jbe@61:             ON "interest"."member_id" = "delegating_voter"."member_id"
jbe@61:             AND "interest"."issue_id" = "delegating_voter"."issue_id"
jbe@61:           WHERE "interest"."issue_id" = "issue_id_p"
jbe@61:           AND "interest"."autoreject" = TRUE
jbe@61:           AND "direct_voter"."member_id" ISNULL
jbe@61:           AND "delegating_voter"."member_id" ISNULL
jbe@61:         UNION SELECT "membership"."member_id"
jbe@61:           FROM "membership"
jbe@61:           LEFT JOIN "interest"
jbe@61:             ON "membership"."member_id" = "interest"."member_id"
jbe@61:             AND "interest"."issue_id" = "issue_id_p"
jbe@61:           LEFT JOIN "direct_voter"
jbe@61:             ON "membership"."member_id" = "direct_voter"."member_id"
jbe@61:             AND "direct_voter"."issue_id" = "issue_id_p"
jbe@61:           LEFT JOIN "delegating_voter"
jbe@61:             ON "membership"."member_id" = "delegating_voter"."member_id"
jbe@61:             AND "delegating_voter"."issue_id" = "issue_id_p"
jbe@61:           WHERE "membership"."area_id" = "issue_row"."area_id"
jbe@61:           AND "membership"."autoreject" = TRUE
jbe@61:           AND "interest"."autoreject" ISNULL
jbe@61:           AND "direct_voter"."member_id" ISNULL
jbe@61:           AND "delegating_voter"."member_id" ISNULL
jbe@61:       LOOP
jbe@61:         INSERT INTO "direct_voter"
jbe@61:           ("member_id", "issue_id", "weight", "autoreject") VALUES
jbe@61:           ("member_id_v", "issue_id_p", 1, TRUE);
jbe@61:         INSERT INTO "vote" (
jbe@61:           "member_id",
jbe@61:           "issue_id",
jbe@61:           "initiative_id",
jbe@61:           "grade"
jbe@61:           ) SELECT
jbe@61:             "member_id_v" AS "member_id",
jbe@61:             "issue_id_p"  AS "issue_id",
jbe@61:             "id"          AS "initiative_id",
jbe@61:             -1            AS "grade"
jbe@61:           FROM "initiative" WHERE "issue_id" = "issue_id_p";
jbe@61:       END LOOP;
jbe@61:       PERFORM "add_vote_delegations"("issue_id_p");
jbe@61:       UPDATE "issue" SET
jbe@61:         "closed" = now(),
jbe@61:         "voter_count" = (
jbe@61:           SELECT coalesce(sum("weight"), 0)
jbe@61:           FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
jbe@61:         )
jbe@61:         WHERE "id" = "issue_id_p";
jbe@61:       UPDATE "initiative" SET
jbe@61:         "positive_votes" = "vote_counts"."positive_votes",
jbe@61:         "negative_votes" = "vote_counts"."negative_votes",
jbe@61:         "agreed" = CASE WHEN "majority_strict" THEN
jbe@61:           "vote_counts"."positive_votes" * "majority_den" >
jbe@61:           "majority_num" *
jbe@61:           ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
jbe@61:         ELSE
jbe@61:           "vote_counts"."positive_votes" * "majority_den" >=
jbe@61:           "majority_num" *
jbe@61:           ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
jbe@61:         END
jbe@61:         FROM
jbe@61:           ( SELECT
jbe@61:               "initiative"."id" AS "initiative_id",
jbe@61:               coalesce(
jbe@61:                 sum(
jbe@61:                   CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
jbe@61:                 ),
jbe@61:                 0
jbe@61:               ) AS "positive_votes",
jbe@61:               coalesce(
jbe@61:                 sum(
jbe@61:                   CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
jbe@61:                 ),
jbe@61:                 0
jbe@61:               ) AS "negative_votes"
jbe@61:             FROM "initiative"
jbe@61:             JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
jbe@61:             JOIN "policy" ON "issue"."policy_id" = "policy"."id"
jbe@61:             LEFT JOIN "direct_voter"
jbe@61:               ON "direct_voter"."issue_id" = "initiative"."issue_id"
jbe@61:             LEFT JOIN "vote"
jbe@61:               ON "vote"."initiative_id" = "initiative"."id"
jbe@61:               AND "vote"."member_id" = "direct_voter"."member_id"
jbe@61:             WHERE "initiative"."issue_id" = "issue_id_p"
jbe@61:             AND "initiative"."admitted"  -- NOTE: NULL case is handled too
jbe@61:             GROUP BY "initiative"."id"
jbe@61:           ) AS "vote_counts",
jbe@61:           "issue",
jbe@61:           "policy"
jbe@61:         WHERE "vote_counts"."initiative_id" = "initiative"."id"
jbe@61:         AND "issue"."id" = "initiative"."issue_id"
jbe@61:         AND "policy"."id" = "issue"."policy_id";
jbe@61:       -- NOTE: "closed" column of issue must be set at this point
jbe@61:       DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@61:       INSERT INTO "battle" (
jbe@61:         "issue_id",
jbe@61:         "winning_initiative_id", "losing_initiative_id",
jbe@61:         "count"
jbe@61:       ) SELECT
jbe@61:         "issue_id",
jbe@61:         "winning_initiative_id", "losing_initiative_id",
jbe@61:         "count"
jbe@61:         FROM "battle_view" WHERE "issue_id" = "issue_id_p";
jbe@61:     END;
jbe@61:   $$;
jbe@61: 
jbe@61: COMMIT;