jbe@36: BEGIN;
jbe@36: 
jbe@36: CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@36:   SELECT * FROM (VALUES ('beta23', NULL, NULL, NULL))
jbe@36:   AS "subquery"("string", "major", "minor", "revision");
jbe@36: 
jbe@36: CREATE OR REPLACE FUNCTION "create_snapshot"
jbe@36:   ( "issue_id_p" "issue"."id"%TYPE )
jbe@36:   RETURNS VOID
jbe@36:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@36:     DECLARE
jbe@36:       "initiative_id_v"    "initiative"."id"%TYPE;
jbe@36:       "suggestion_id_v"    "suggestion"."id"%TYPE;
jbe@36:     BEGIN
jbe@36:       PERFORM "global_lock"();
jbe@36:       PERFORM "create_population_snapshot"("issue_id_p");
jbe@36:       PERFORM "create_interest_snapshot"("issue_id_p");
jbe@36:       UPDATE "issue" SET
jbe@36:         "snapshot" = now(),
jbe@36:         "latest_snapshot_event" = 'periodic',
jbe@36:         "population" = (
jbe@36:           SELECT coalesce(sum("weight"), 0)
jbe@36:           FROM "direct_population_snapshot"
jbe@36:           WHERE "issue_id" = "issue_id_p"
jbe@36:           AND "event" = 'periodic'
jbe@36:         ),
jbe@36:         "vote_now" = (
jbe@36:           SELECT coalesce(sum("weight"), 0)
jbe@36:           FROM "direct_interest_snapshot"
jbe@36:           WHERE "issue_id" = "issue_id_p"
jbe@36:           AND "event" = 'periodic'
jbe@36:           AND "voting_requested" = TRUE
jbe@36:         ),
jbe@36:         "vote_later" = (
jbe@36:           SELECT coalesce(sum("weight"), 0)
jbe@36:           FROM "direct_interest_snapshot"
jbe@36:           WHERE "issue_id" = "issue_id_p"
jbe@36:           AND "event" = 'periodic'
jbe@36:           AND "voting_requested" = FALSE
jbe@36:         )
jbe@36:         WHERE "id" = "issue_id_p";
jbe@36:       FOR "initiative_id_v" IN
jbe@36:         SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
jbe@36:       LOOP
jbe@36:         UPDATE "initiative" SET
jbe@36:           "supporter_count" = (
jbe@36:             SELECT coalesce(sum("di"."weight"), 0)
jbe@36:             FROM "direct_interest_snapshot" AS "di"
jbe@36:             JOIN "direct_supporter_snapshot" AS "ds"
jbe@36:             ON "di"."member_id" = "ds"."member_id"
jbe@36:             WHERE "di"."issue_id" = "issue_id_p"
jbe@36:             AND "di"."event" = 'periodic'
jbe@36:             AND "ds"."initiative_id" = "initiative_id_v"
jbe@36:             AND "ds"."event" = 'periodic'
jbe@36:           ),
jbe@36:           "informed_supporter_count" = (
jbe@36:             SELECT coalesce(sum("di"."weight"), 0)
jbe@36:             FROM "direct_interest_snapshot" AS "di"
jbe@36:             JOIN "direct_supporter_snapshot" AS "ds"
jbe@36:             ON "di"."member_id" = "ds"."member_id"
jbe@36:             WHERE "di"."issue_id" = "issue_id_p"
jbe@36:             AND "di"."event" = 'periodic'
jbe@36:             AND "ds"."initiative_id" = "initiative_id_v"
jbe@36:             AND "ds"."event" = 'periodic'
jbe@36:             AND "ds"."informed"
jbe@36:           ),
jbe@36:           "satisfied_supporter_count" = (
jbe@36:             SELECT coalesce(sum("di"."weight"), 0)
jbe@36:             FROM "direct_interest_snapshot" AS "di"
jbe@36:             JOIN "direct_supporter_snapshot" AS "ds"
jbe@36:             ON "di"."member_id" = "ds"."member_id"
jbe@36:             WHERE "di"."issue_id" = "issue_id_p"
jbe@36:             AND "di"."event" = 'periodic'
jbe@36:             AND "ds"."initiative_id" = "initiative_id_v"
jbe@36:             AND "ds"."event" = 'periodic'
jbe@36:             AND "ds"."satisfied"
jbe@36:           ),
jbe@36:           "satisfied_informed_supporter_count" = (
jbe@36:             SELECT coalesce(sum("di"."weight"), 0)
jbe@36:             FROM "direct_interest_snapshot" AS "di"
jbe@36:             JOIN "direct_supporter_snapshot" AS "ds"
jbe@36:             ON "di"."member_id" = "ds"."member_id"
jbe@36:             WHERE "di"."issue_id" = "issue_id_p"
jbe@36:             AND "di"."event" = 'periodic'
jbe@36:             AND "ds"."initiative_id" = "initiative_id_v"
jbe@36:             AND "ds"."event" = 'periodic'
jbe@36:             AND "ds"."informed"
jbe@36:             AND "ds"."satisfied"
jbe@36:           )
jbe@36:           WHERE "id" = "initiative_id_v";
jbe@36:         FOR "suggestion_id_v" IN
jbe@36:           SELECT "id" FROM "suggestion"
jbe@36:           WHERE "initiative_id" = "initiative_id_v"
jbe@36:         LOOP
jbe@36:           UPDATE "suggestion" SET
jbe@36:             "minus2_unfulfilled_count" = (
jbe@36:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36:               FROM "issue" CROSS JOIN "opinion"
jbe@36:               JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36:               ON "snapshot"."issue_id" = "issue"."id"
jbe@36:               AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36:               AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36:               WHERE "issue"."id" = "issue_id_p"
jbe@36:               AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@36:               AND "opinion"."degree" = -2
jbe@36:               AND "opinion"."fulfilled" = FALSE
jbe@36:             ),
jbe@36:             "minus2_fulfilled_count" = (
jbe@36:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36:               FROM "issue" CROSS JOIN "opinion"
jbe@36:               JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36:               ON "snapshot"."issue_id" = "issue"."id"
jbe@36:               AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36:               AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36:               WHERE "issue"."id" = "issue_id_p"
jbe@36:               AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@36:               AND "opinion"."degree" = -2
jbe@36:               AND "opinion"."fulfilled" = TRUE
jbe@36:             ),
jbe@36:             "minus1_unfulfilled_count" = (
jbe@36:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36:               FROM "issue" CROSS JOIN "opinion"
jbe@36:               JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36:               ON "snapshot"."issue_id" = "issue"."id"
jbe@36:               AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36:               AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36:               WHERE "issue"."id" = "issue_id_p"
jbe@36:               AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@36:               AND "opinion"."degree" = -1
jbe@36:               AND "opinion"."fulfilled" = FALSE
jbe@36:             ),
jbe@36:             "minus1_fulfilled_count" = (
jbe@36:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36:               FROM "issue" CROSS JOIN "opinion"
jbe@36:               JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36:               ON "snapshot"."issue_id" = "issue"."id"
jbe@36:               AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36:               AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36:               WHERE "issue"."id" = "issue_id_p"
jbe@36:               AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@36:               AND "opinion"."degree" = -1
jbe@36:               AND "opinion"."fulfilled" = TRUE
jbe@36:             ),
jbe@36:             "plus1_unfulfilled_count" = (
jbe@36:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36:               FROM "issue" CROSS JOIN "opinion"
jbe@36:               JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36:               ON "snapshot"."issue_id" = "issue"."id"
jbe@36:               AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36:               AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36:               WHERE "issue"."id" = "issue_id_p"
jbe@36:               AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@36:               AND "opinion"."degree" = 1
jbe@36:               AND "opinion"."fulfilled" = FALSE
jbe@36:             ),
jbe@36:             "plus1_fulfilled_count" = (
jbe@36:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36:               FROM "issue" CROSS JOIN "opinion"
jbe@36:               JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36:               ON "snapshot"."issue_id" = "issue"."id"
jbe@36:               AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36:               AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36:               WHERE "issue"."id" = "issue_id_p"
jbe@36:               AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@36:               AND "opinion"."degree" = 1
jbe@36:               AND "opinion"."fulfilled" = TRUE
jbe@36:             ),
jbe@36:             "plus2_unfulfilled_count" = (
jbe@36:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36:               FROM "issue" CROSS JOIN "opinion"
jbe@36:               JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36:               ON "snapshot"."issue_id" = "issue"."id"
jbe@36:               AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36:               AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36:               WHERE "issue"."id" = "issue_id_p"
jbe@36:               AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@36:               AND "opinion"."degree" = 2
jbe@36:               AND "opinion"."fulfilled" = FALSE
jbe@36:             ),
jbe@36:             "plus2_fulfilled_count" = (
jbe@36:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36:               FROM "issue" CROSS JOIN "opinion"
jbe@36:               JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36:               ON "snapshot"."issue_id" = "issue"."id"
jbe@36:               AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36:               AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36:               WHERE "issue"."id" = "issue_id_p"
jbe@36:               AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@36:               AND "opinion"."degree" = 2
jbe@36:               AND "opinion"."fulfilled" = TRUE
jbe@36:             )
jbe@36:             WHERE "suggestion"."id" = "suggestion_id_v";
jbe@36:         END LOOP;
jbe@36:       END LOOP;
jbe@36:       RETURN;
jbe@36:     END;
jbe@36:   $$;
jbe@36: 
jbe@36: COMMIT;