jbe@360: BEGIN;
jbe@360: 
jbe@360: CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@360:   SELECT * FROM (VALUES ('2.2.1', 2, 2, 1))
jbe@360:   AS "subquery"("string", "major", "minor", "revision");
jbe@360: 
jbe@360: ALTER TABLE "initiative" ADD COLUMN "final_suggestion_order_calculated" BOOLEAN NOT NULL DEFAULT FALSE;
jbe@360: COMMENT ON COLUMN "initiative"."final_suggestion_order_calculated" IS 'Set to TRUE, when "proportional_order" of suggestions has been calculated the last time';
jbe@360: 
jbe@360: ALTER TABLE "suggestion" ADD COLUMN "proportional_order" INT4;
jbe@378: COMMENT ON COLUMN "suggestion"."proportional_order" IS 'To be used for sorting suggestions within an initiative; NULL values sort last; updated by "lf_update_suggestion_order"';
jbe@360: 
jbe@360: CREATE VIEW "initiative_suggestion_order_calculation" AS
jbe@360:   SELECT
jbe@360:     "initiative"."id" AS "initiative_id",
jbe@360:     ("issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL) AS "final"
jbe@360:   FROM "initiative" JOIN "issue"
jbe@360:   ON "initiative"."issue_id" = "issue"."id"
jbe@360:   WHERE ("issue"."closed" ISNULL AND "issue"."fully_frozen" ISNULL)
jbe@360:   OR ("initiative"."final_suggestion_order_calculated" = FALSE);
jbe@360: COMMENT ON VIEW "initiative_suggestion_order_calculation" IS 'Initiatives, where the "proportional_order" of its suggestions has to be calculated';
jbe@360: COMMENT ON COLUMN "initiative_suggestion_order_calculation"."final" IS 'Set to TRUE, if the issue is fully frozen or closed, and the calculation has to be done only once for one last time';
jbe@360: 
jbe@360: CREATE VIEW "individual_suggestion_ranking" AS
jbe@360:   SELECT
jbe@360:     "opinion"."initiative_id",
jbe@360:     "opinion"."member_id",
jbe@360:     "direct_interest_snapshot"."weight",
jbe@360:     CASE WHEN
jbe@360:       ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR
jbe@360:       ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE)
jbe@360:     THEN 1 ELSE
jbe@360:       CASE WHEN
jbe@360:         ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR
jbe@360:         ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE)
jbe@360:       THEN 2 ELSE
jbe@360:         CASE WHEN
jbe@360:           ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR
jbe@360:           ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE)
jbe@360:         THEN 3 ELSE 4 END
jbe@360:       END
jbe@360:     END AS "preference",
jbe@360:     "opinion"."suggestion_id"
jbe@360:   FROM "opinion"
jbe@360:   JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id"
jbe@360:   JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@360:   JOIN "direct_interest_snapshot"
jbe@360:     ON  "direct_interest_snapshot"."issue_id" = "issue"."id"
jbe@360:     AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"
jbe@360:     AND "direct_interest_snapshot"."member_id" = "opinion"."member_id";
jbe@360: COMMENT ON VIEW "individual_suggestion_ranking" IS 'Helper view for "lf_update_suggestion_order" to allow a proportional ordering of suggestions within an initiative';
jbe@360: 
jbe@360: COMMIT;