jbe@315: BEGIN;
jbe@315: 
jbe@315: CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@315:   SELECT * FROM (VALUES ('2.1.1', 2, 1, 1))
jbe@315:   AS "subquery"("string", "major", "minor", "revision");
jbe@315: 
jbe@315: ALTER TABLE "initiative" ADD COLUMN "harmonic_weight" NUMERIC(12, 3);
jbe@320: COMMENT ON COLUMN "initiative"."harmonic_weight" IS 'Indicates the relevancy of the initiative, calculated from the potential supporters weighted with the harmonic series to avoid a large number of clones affecting other initiative''s sorting positions too much; shall be used as secondary sorting key after "admitted" as primary sorting key';
jbe@315: 
jbe@315: ALTER TABLE "suggestion" ADD COLUMN "harmonic_weight" NUMERIC(12, 3);
jbe@315: COMMENT ON COLUMN "suggestion"."harmonic_weight" IS 'Indicates the relevancy of the suggestion, calculated from the supporters (positive "degree") of the suggestion weighted with the harmonic series to avoid a large number of clones affecting other suggestion''s sortings position too much';
jbe@315: 
jbe@315: CREATE VIEW "remaining_harmonic_supporter_weight" AS
jbe@315:   SELECT
jbe@315:     "direct_interest_snapshot"."issue_id",
jbe@315:     "direct_interest_snapshot"."event",
jbe@315:     "direct_interest_snapshot"."member_id",
jbe@315:     "direct_interest_snapshot"."weight" AS "weight_num",
jbe@315:     count("initiative"."id") AS "weight_den"
jbe@315:   FROM "issue"
jbe@315:   JOIN "direct_interest_snapshot"
jbe@315:     ON "issue"."id" = "direct_interest_snapshot"."issue_id"
jbe@315:     AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event"
jbe@315:   JOIN "direct_supporter_snapshot"
jbe@315:     ON "direct_interest_snapshot"."issue_id" = "direct_supporter_snapshot"."issue_id"
jbe@315:     AND "direct_interest_snapshot"."event" = "direct_supporter_snapshot"."event"
jbe@315:     AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"
jbe@315:   JOIN "initiative"
jbe@315:     ON "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
jbe@321:     AND (
jbe@321:       "direct_supporter_snapshot"."satisfied" = TRUE OR
jbe@321:       coalesce("initiative"."admitted", FALSE) = FALSE
jbe@321:     )
jbe@315:     AND "initiative"."harmonic_weight" ISNULL
jbe@315:   GROUP BY
jbe@315:     "direct_interest_snapshot"."issue_id",
jbe@315:     "direct_interest_snapshot"."event",
jbe@315:     "direct_interest_snapshot"."member_id",
jbe@315:     "direct_interest_snapshot"."weight";
jbe@315: 
jbe@315: COMMENT ON VIEW "remaining_harmonic_supporter_weight" IS 'Helper view for function "set_harmonic_initiative_weights"';
jbe@315: 
jbe@315: CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS
jbe@315:   SELECT
jbe@315:     "initiative"."issue_id",
jbe@315:     "initiative"."id" AS "initiative_id",
jbe@320:     "initiative"."admitted",
jbe@315:     sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
jbe@315:     "remaining_harmonic_supporter_weight"."weight_den"
jbe@315:   FROM "remaining_harmonic_supporter_weight"
jbe@315:   JOIN "direct_supporter_snapshot"
jbe@315:     ON "remaining_harmonic_supporter_weight"."issue_id" = "direct_supporter_snapshot"."issue_id"
jbe@315:     AND "remaining_harmonic_supporter_weight"."event" = "direct_supporter_snapshot"."event"
jbe@315:     AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"
jbe@315:   JOIN "initiative"
jbe@315:     ON "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
jbe@321:     AND (
jbe@321:       "direct_supporter_snapshot"."satisfied" = TRUE OR
jbe@321:       coalesce("initiative"."admitted", FALSE) = FALSE
jbe@321:     )
jbe@315:     AND "initiative"."harmonic_weight" ISNULL
jbe@315:   GROUP BY
jbe@315:     "initiative"."issue_id",
jbe@315:     "initiative"."id",
jbe@320:     "initiative"."admitted",
jbe@315:     "remaining_harmonic_supporter_weight"."weight_den";
jbe@315: 
jbe@315: COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"';
jbe@315: 
jbe@315: CREATE FUNCTION "set_harmonic_initiative_weights"
jbe@315:   ( "issue_id_p" "issue"."id"%TYPE )
jbe@315:   RETURNS VOID
jbe@315:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@315:     DECLARE
jbe@315:       "weight_row"   "remaining_harmonic_initiative_weight_summands"%ROWTYPE;
jbe@315:       "i"            INT4;
jbe@315:       "count_v"      INT4;
jbe@315:       "summand_v"    FLOAT;
jbe@315:       "id_ary"       INT4[];
jbe@315:       "weight_ary"   FLOAT[];
jbe@315:       "min_weight_v" FLOAT;
jbe@315:     BEGIN
jbe@315:       UPDATE "initiative" SET "harmonic_weight" = NULL
jbe@315:         WHERE "issue_id" = "issue_id_p";
jbe@315:       LOOP
jbe@315:         "min_weight_v" := NULL;
jbe@315:         "i" := 0;
jbe@315:         "count_v" := 0;
jbe@315:         FOR "weight_row" IN
jbe@315:           SELECT * FROM "remaining_harmonic_initiative_weight_summands"
jbe@315:           WHERE "issue_id" = "issue_id_p"
jbe@320:           AND (
jbe@320:             coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
jbe@320:               SELECT NULL FROM "initiative"
jbe@320:               WHERE "issue_id" = "issue_id_p"
jbe@320:               AND "harmonic_weight" ISNULL
jbe@320:               AND coalesce("admitted", FALSE) = FALSE
jbe@320:             )
jbe@320:           )
jbe@315:           ORDER BY "initiative_id" DESC, "weight_den" DESC
jbe@320:           -- NOTE: non-admitted initiatives placed first (at last positions),
jbe@320:           --       latest initiatives treated worse in case of tie
jbe@315:         LOOP
jbe@315:           "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
jbe@315:           IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN
jbe@315:             "i" := "i" + 1;
jbe@315:             "count_v" := "i";
jbe@315:             "id_ary"["i"] := "weight_row"."initiative_id";
jbe@315:             "weight_ary"["i"] := "summand_v";
jbe@315:           ELSE
jbe@315:             "weight_ary"["i"] := "weight_ary"["i"] + "summand_v";
jbe@315:           END IF;
jbe@315:         END LOOP;
jbe@315:         EXIT WHEN "count_v" = 0;
jbe@315:         "i" := 1;
jbe@315:         LOOP
jbe@315:           "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3);
jbe@315:           IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN
jbe@315:             "min_weight_v" := "weight_ary"["i"];
jbe@315:           END IF;
jbe@315:           "i" := "i" + 1;
jbe@315:           EXIT WHEN "i" > "count_v";
jbe@315:         END LOOP;
jbe@315:         "i" := 1;
jbe@315:         LOOP
jbe@315:           IF "weight_ary"["i"] = "min_weight_v" THEN
jbe@315:             UPDATE "initiative" SET "harmonic_weight" = "min_weight_v"
jbe@315:               WHERE "id" = "id_ary"["i"];
jbe@315:             EXIT;
jbe@315:           END IF;
jbe@315:           "i" := "i" + 1;
jbe@315:         END LOOP;
jbe@315:       END LOOP;
jbe@316:       UPDATE "initiative" SET "harmonic_weight" = 0
jbe@316:         WHERE "issue_id" = "issue_id_p" AND "harmonic_weight" ISNULL;
jbe@315:     END;
jbe@315:   $$;
jbe@315: 
jbe@315: COMMENT ON FUNCTION "set_harmonic_initiative_weights"
jbe@315:   ( "issue"."id"%TYPE )
jbe@315:   IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue';
jbe@315: 
jbe@315: CREATE VIEW "remaining_harmonic_opinion_weight" AS
jbe@315:   SELECT
jbe@315:     "initiative"."issue_id",
jbe@315:     "opinion"."initiative_id",
jbe@315:     "direct_interest_snapshot"."member_id",
jbe@315:     "direct_interest_snapshot"."weight" AS "weight_num",
jbe@315:     count("opinion"."suggestion_id") AS "weight_den"
jbe@315:   FROM "issue"
jbe@315:   JOIN "direct_interest_snapshot"
jbe@315:     ON "issue"."id" = "direct_interest_snapshot"."issue_id"
jbe@315:     AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event"
jbe@315:   JOIN "initiative"
jbe@315:     ON "direct_interest_snapshot"."issue_id" = "initiative"."issue_id"
jbe@315:   JOIN "opinion"
jbe@315:     ON "direct_interest_snapshot"."member_id" = "opinion"."member_id"
jbe@315:     AND "initiative"."id" = "opinion"."initiative_id"
jbe@317:     AND (
jbe@317:       ("opinion"."degree" > 0 AND "opinion"."fulfilled" = FALSE) OR
jbe@317:       ("opinion"."degree" < 0 AND "opinion"."fulfilled" = TRUE)
jbe@317:     )
jbe@318:   JOIN "suggestion"
jbe@318:     ON "opinion"."suggestion_id" = "suggestion"."id"
jbe@318:     AND "suggestion"."harmonic_weight" ISNULL
jbe@315:   GROUP BY
jbe@315:     "initiative"."issue_id",
jbe@315:     "opinion"."initiative_id",
jbe@315:     "direct_interest_snapshot"."member_id",
jbe@315:     "direct_interest_snapshot"."weight";
jbe@315: 
jbe@315: COMMENT ON VIEW "remaining_harmonic_opinion_weight" IS 'Helper view for function "set_harmonic_suggestion_weights"';
jbe@315: 
jbe@315: CREATE VIEW "remaining_harmonic_suggestion_weight_summands" AS
jbe@315:   SELECT
jbe@315:     "suggestion"."initiative_id",
jbe@315:     "opinion"."suggestion_id",
jbe@315:     sum("remaining_harmonic_opinion_weight"."weight_num") AS "weight_num",
jbe@315:     "remaining_harmonic_opinion_weight"."weight_den"
jbe@315:   FROM "remaining_harmonic_opinion_weight"
jbe@315:   JOIN "opinion"
jbe@315:     ON "remaining_harmonic_opinion_weight"."initiative_id" = "opinion"."initiative_id"
jbe@315:     AND "remaining_harmonic_opinion_weight"."member_id" = "opinion"."member_id"
jbe@319:     AND (
jbe@319:       ("opinion"."degree" > 0 AND "opinion"."fulfilled" = FALSE) OR
jbe@319:       ("opinion"."degree" < 0 AND "opinion"."fulfilled" = TRUE)
jbe@319:     )
jbe@315:   JOIN "suggestion"
jbe@315:     ON "opinion"."suggestion_id" = "suggestion"."id"
jbe@315:     AND "suggestion"."harmonic_weight" ISNULL
jbe@315:   GROUP BY
jbe@315:     "suggestion"."initiative_id",
jbe@315:     "opinion"."suggestion_id",
jbe@315:     "remaining_harmonic_opinion_weight"."weight_den";
jbe@315: 
jbe@315: COMMENT ON VIEW "remaining_harmonic_suggestion_weight_summands" IS 'Helper view for function "set_harmonic_suggestion_weights"';
jbe@315: 
jbe@315: CREATE FUNCTION "set_harmonic_suggestion_weights"
jbe@315:   ( "initiative_id_p" "initiative"."id"%TYPE )
jbe@315:   RETURNS VOID
jbe@315:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@315:     DECLARE
jbe@315:       "weight_row"   "remaining_harmonic_suggestion_weight_summands"%ROWTYPE;
jbe@315:       "i"            INT4;
jbe@315:       "count_v"      INT4;
jbe@315:       "summand_v"    FLOAT;
jbe@315:       "id_ary"       INT4[];
jbe@315:       "weight_ary"   FLOAT[];
jbe@315:       "min_weight_v" FLOAT;
jbe@315:     BEGIN
jbe@315:       UPDATE "suggestion" SET "harmonic_weight" = NULL
jbe@315:         WHERE "initiative_id" = "initiative_id_p";
jbe@315:       LOOP
jbe@315:         "min_weight_v" := NULL;
jbe@315:         "i" := 0;
jbe@315:         "count_v" := 0;
jbe@315:         FOR "weight_row" IN
jbe@315:           SELECT * FROM "remaining_harmonic_suggestion_weight_summands"
jbe@315:           WHERE "initiative_id" = "initiative_id_p"
jbe@315:           ORDER BY "suggestion_id" DESC, "weight_den" DESC
jbe@320:           -- NOTE: latest suggestions treated worse in case of tie
jbe@315:         LOOP
jbe@315:           "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
jbe@315:           IF "i" = 0 OR "weight_row"."suggestion_id" != "id_ary"["i"] THEN
jbe@315:             "i" := "i" + 1;
jbe@315:             "count_v" := "i";
jbe@315:             "id_ary"["i"] := "weight_row"."suggestion_id";
jbe@315:             "weight_ary"["i"] := "summand_v";
jbe@315:           ELSE
jbe@315:             "weight_ary"["i"] := "weight_ary"["i"] + "summand_v";
jbe@315:           END IF;
jbe@315:         END LOOP;
jbe@315:         EXIT WHEN "count_v" = 0;
jbe@315:         "i" := 1;
jbe@315:         LOOP
jbe@315:           "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3);
jbe@315:           IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN
jbe@315:             "min_weight_v" := "weight_ary"["i"];
jbe@315:           END IF;
jbe@315:           "i" := "i" + 1;
jbe@315:           EXIT WHEN "i" > "count_v";
jbe@315:         END LOOP;
jbe@315:         "i" := 1;
jbe@315:         LOOP
jbe@315:           IF "weight_ary"["i"] = "min_weight_v" THEN
jbe@315:             UPDATE "suggestion" SET "harmonic_weight" = "min_weight_v"
jbe@315:               WHERE "id" = "id_ary"["i"];
jbe@315:             EXIT;
jbe@315:           END IF;
jbe@315:           "i" := "i" + 1;
jbe@315:         END LOOP;
jbe@315:       END LOOP;
jbe@316:       UPDATE "suggestion" SET "harmonic_weight" = 0
jbe@316:         WHERE "initiative_id" = "initiative_id_p" AND "harmonic_weight" ISNULL;
jbe@315:     END;
jbe@315:   $$;
jbe@315: 
jbe@315: COMMENT ON FUNCTION "set_harmonic_suggestion_weights"
jbe@315:   ( "issue"."id"%TYPE )
jbe@315:   IS 'Calculates and sets "harmonic_weight" of suggestions in a given initiative';
jbe@315: 
jbe@315: CREATE OR REPLACE FUNCTION "create_snapshot"
jbe@315:   ( "issue_id_p" "issue"."id"%TYPE )
jbe@315:   RETURNS VOID
jbe@315:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@315:     DECLARE
jbe@315:       "initiative_id_v"    "initiative"."id"%TYPE;
jbe@315:       "suggestion_id_v"    "suggestion"."id"%TYPE;
jbe@315:     BEGIN
jbe@315:       PERFORM "lock_issue"("issue_id_p");
jbe@315:       PERFORM "create_population_snapshot"("issue_id_p");
jbe@315:       PERFORM "create_interest_snapshot"("issue_id_p");
jbe@315:       UPDATE "issue" SET
jbe@315:         "snapshot" = now(),
jbe@315:         "latest_snapshot_event" = 'periodic',
jbe@315:         "population" = (
jbe@315:           SELECT coalesce(sum("weight"), 0)
jbe@315:           FROM "direct_population_snapshot"
jbe@315:           WHERE "issue_id" = "issue_id_p"
jbe@315:           AND "event" = 'periodic'
jbe@315:         )
jbe@315:         WHERE "id" = "issue_id_p";
jbe@315:       FOR "initiative_id_v" IN
jbe@315:         SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
jbe@315:       LOOP
jbe@315:         UPDATE "initiative" SET
jbe@315:           "supporter_count" = (
jbe@315:             SELECT coalesce(sum("di"."weight"), 0)
jbe@315:             FROM "direct_interest_snapshot" AS "di"
jbe@315:             JOIN "direct_supporter_snapshot" AS "ds"
jbe@315:             ON "di"."member_id" = "ds"."member_id"
jbe@315:             WHERE "di"."issue_id" = "issue_id_p"
jbe@315:             AND "di"."event" = 'periodic'
jbe@315:             AND "ds"."initiative_id" = "initiative_id_v"
jbe@315:             AND "ds"."event" = 'periodic'
jbe@315:           ),
jbe@315:           "informed_supporter_count" = (
jbe@315:             SELECT coalesce(sum("di"."weight"), 0)
jbe@315:             FROM "direct_interest_snapshot" AS "di"
jbe@315:             JOIN "direct_supporter_snapshot" AS "ds"
jbe@315:             ON "di"."member_id" = "ds"."member_id"
jbe@315:             WHERE "di"."issue_id" = "issue_id_p"
jbe@315:             AND "di"."event" = 'periodic'
jbe@315:             AND "ds"."initiative_id" = "initiative_id_v"
jbe@315:             AND "ds"."event" = 'periodic'
jbe@315:             AND "ds"."informed"
jbe@315:           ),
jbe@315:           "satisfied_supporter_count" = (
jbe@315:             SELECT coalesce(sum("di"."weight"), 0)
jbe@315:             FROM "direct_interest_snapshot" AS "di"
jbe@315:             JOIN "direct_supporter_snapshot" AS "ds"
jbe@315:             ON "di"."member_id" = "ds"."member_id"
jbe@315:             WHERE "di"."issue_id" = "issue_id_p"
jbe@315:             AND "di"."event" = 'periodic'
jbe@315:             AND "ds"."initiative_id" = "initiative_id_v"
jbe@315:             AND "ds"."event" = 'periodic'
jbe@315:             AND "ds"."satisfied"
jbe@315:           ),
jbe@315:           "satisfied_informed_supporter_count" = (
jbe@315:             SELECT coalesce(sum("di"."weight"), 0)
jbe@315:             FROM "direct_interest_snapshot" AS "di"
jbe@315:             JOIN "direct_supporter_snapshot" AS "ds"
jbe@315:             ON "di"."member_id" = "ds"."member_id"
jbe@315:             WHERE "di"."issue_id" = "issue_id_p"
jbe@315:             AND "di"."event" = 'periodic'
jbe@315:             AND "ds"."initiative_id" = "initiative_id_v"
jbe@315:             AND "ds"."event" = 'periodic'
jbe@315:             AND "ds"."informed"
jbe@315:             AND "ds"."satisfied"
jbe@315:           )
jbe@315:           WHERE "id" = "initiative_id_v";
jbe@315:         FOR "suggestion_id_v" IN
jbe@315:           SELECT "id" FROM "suggestion"
jbe@315:           WHERE "initiative_id" = "initiative_id_v"
jbe@315:         LOOP
jbe@315:           UPDATE "suggestion" SET
jbe@315:             "minus2_unfulfilled_count" = (
jbe@315:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@315:               FROM "issue" CROSS JOIN "opinion"
jbe@315:               JOIN "direct_interest_snapshot" AS "snapshot"
jbe@315:               ON "snapshot"."issue_id" = "issue"."id"
jbe@315:               AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@315:               AND "snapshot"."member_id" = "opinion"."member_id"
jbe@315:               WHERE "issue"."id" = "issue_id_p"
jbe@315:               AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@315:               AND "opinion"."degree" = -2
jbe@315:               AND "opinion"."fulfilled" = FALSE
jbe@315:             ),
jbe@315:             "minus2_fulfilled_count" = (
jbe@315:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@315:               FROM "issue" CROSS JOIN "opinion"
jbe@315:               JOIN "direct_interest_snapshot" AS "snapshot"
jbe@315:               ON "snapshot"."issue_id" = "issue"."id"
jbe@315:               AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@315:               AND "snapshot"."member_id" = "opinion"."member_id"
jbe@315:               WHERE "issue"."id" = "issue_id_p"
jbe@315:               AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@315:               AND "opinion"."degree" = -2
jbe@315:               AND "opinion"."fulfilled" = TRUE
jbe@315:             ),
jbe@315:             "minus1_unfulfilled_count" = (
jbe@315:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@315:               FROM "issue" CROSS JOIN "opinion"
jbe@315:               JOIN "direct_interest_snapshot" AS "snapshot"
jbe@315:               ON "snapshot"."issue_id" = "issue"."id"
jbe@315:               AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@315:               AND "snapshot"."member_id" = "opinion"."member_id"
jbe@315:               WHERE "issue"."id" = "issue_id_p"
jbe@315:               AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@315:               AND "opinion"."degree" = -1
jbe@315:               AND "opinion"."fulfilled" = FALSE
jbe@315:             ),
jbe@315:             "minus1_fulfilled_count" = (
jbe@315:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@315:               FROM "issue" CROSS JOIN "opinion"
jbe@315:               JOIN "direct_interest_snapshot" AS "snapshot"
jbe@315:               ON "snapshot"."issue_id" = "issue"."id"
jbe@315:               AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@315:               AND "snapshot"."member_id" = "opinion"."member_id"
jbe@315:               WHERE "issue"."id" = "issue_id_p"
jbe@315:               AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@315:               AND "opinion"."degree" = -1
jbe@315:               AND "opinion"."fulfilled" = TRUE
jbe@315:             ),
jbe@315:             "plus1_unfulfilled_count" = (
jbe@315:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@315:               FROM "issue" CROSS JOIN "opinion"
jbe@315:               JOIN "direct_interest_snapshot" AS "snapshot"
jbe@315:               ON "snapshot"."issue_id" = "issue"."id"
jbe@315:               AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@315:               AND "snapshot"."member_id" = "opinion"."member_id"
jbe@315:               WHERE "issue"."id" = "issue_id_p"
jbe@315:               AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@315:               AND "opinion"."degree" = 1
jbe@315:               AND "opinion"."fulfilled" = FALSE
jbe@315:             ),
jbe@315:             "plus1_fulfilled_count" = (
jbe@315:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@315:               FROM "issue" CROSS JOIN "opinion"
jbe@315:               JOIN "direct_interest_snapshot" AS "snapshot"
jbe@315:               ON "snapshot"."issue_id" = "issue"."id"
jbe@315:               AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@315:               AND "snapshot"."member_id" = "opinion"."member_id"
jbe@315:               WHERE "issue"."id" = "issue_id_p"
jbe@315:               AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@315:               AND "opinion"."degree" = 1
jbe@315:               AND "opinion"."fulfilled" = TRUE
jbe@315:             ),
jbe@315:             "plus2_unfulfilled_count" = (
jbe@315:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@315:               FROM "issue" CROSS JOIN "opinion"
jbe@315:               JOIN "direct_interest_snapshot" AS "snapshot"
jbe@315:               ON "snapshot"."issue_id" = "issue"."id"
jbe@315:               AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@315:               AND "snapshot"."member_id" = "opinion"."member_id"
jbe@315:               WHERE "issue"."id" = "issue_id_p"
jbe@315:               AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@315:               AND "opinion"."degree" = 2
jbe@315:               AND "opinion"."fulfilled" = FALSE
jbe@315:             ),
jbe@315:             "plus2_fulfilled_count" = (
jbe@315:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@315:               FROM "issue" CROSS JOIN "opinion"
jbe@315:               JOIN "direct_interest_snapshot" AS "snapshot"
jbe@315:               ON "snapshot"."issue_id" = "issue"."id"
jbe@315:               AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@315:               AND "snapshot"."member_id" = "opinion"."member_id"
jbe@315:               WHERE "issue"."id" = "issue_id_p"
jbe@315:               AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@315:               AND "opinion"."degree" = 2
jbe@315:               AND "opinion"."fulfilled" = TRUE
jbe@315:             )
jbe@315:             WHERE "suggestion"."id" = "suggestion_id_v";
jbe@315:         END LOOP;
jbe@315:         PERFORM "set_harmonic_suggestion_weights"("initiative_id_v");
jbe@315:       END LOOP;
jbe@315:       PERFORM "set_harmonic_initiative_weights"("issue_id_p");
jbe@315:       RETURN;
jbe@315:     END;
jbe@315:   $$;
jbe@315: 
jbe@315: SELECT "set_harmonic_initiative_weights"("id") FROM "issue";
jbe@315: SELECT "set_harmonic_suggestion_weights"("id") FROM "initiative";
jbe@315: 
jbe@315: COMMIT;