jbe@478: BEGIN;
jbe@478: 
jbe@478: CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@478:   SELECT * FROM (VALUES ('3.2.0', 3, 2, 0))
jbe@478:   AS "subquery"("string", "major", "minor", "revision");
jbe@478: 
jbe@499: -- TODO: preliminary script
jbe@499: 
jbe@478: ALTER TABLE "member" ADD COLUMN "disable_notifications" BOOLEAN NOT NULL DEFAULT FALSE;
jbe@492: ALTER TABLE "member" ADD COLUMN "notification_counter" INT4 NOT NULL DEFAULT 1;
jbe@492: ALTER TABLE "member" ADD COLUMN "notification_sample_size" INT4 NOT NULL DEFAULT 3;
jbe@492: ALTER TABLE "member" ADD COLUMN "notification_dow" INT4 CHECK ("notification_dow" BETWEEN 0 AND 6);
jbe@492: ALTER TABLE "member" ADD COLUMN "notification_hour" INT4 CHECK ("notification_hour" BETWEEN 0 AND 23);
jbe@478: 
jbe@478: UPDATE "member" SET "disable_notifications" = TRUE WHERE "notify_level" = 'none';
jbe@499: 
jbe@500: DROP VIEW "selected_event_seen_by_member";
jbe@500: DROP VIEW "event_seen_by_member";
jbe@499: ALTER TABLE "member" DROP COLUMN "notify_level";
jbe@499: DROP TYPE "notify_level";
jbe@478:  
jbe@478: CREATE TABLE "subscription" (
jbe@478:         PRIMARY KEY ("member_id", "unit_id"),
jbe@478:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@478:         "unit_id"               INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@478: CREATE INDEX "subscription_unit_id_idx" ON "subscription" ("unit_id");
jbe@492: 
jbe@492: COMMENT ON TABLE "subscription" IS 'An entry in this table denotes that the member wishes to receive notifications regardless of his/her privileges in the given unit';
jbe@492: 
jbe@492: CREATE TABLE "ignored_area" (
jbe@492:         PRIMARY KEY ("member_id", "area_id"),
jbe@492:         "member_id"             INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@492:         "area_id"               INT4            REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@492: CREATE INDEX "ignored_area_area_id_idx" ON "ignored_area" ("area_id");
jbe@492: 
jbe@492: COMMENT ON TABLE "ignored_area" IS 'An entry in this table denotes that the member does not wish to receive notifications for the given subject area unless he/she declared interested in a particular issue';
jbe@492: 
jbe@492: CREATE TABLE "initiative_notification_sent" (
jbe@492:         PRIMARY KEY ("member_id", "initiative_id"),
jbe@492:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@492:         "initiative_id"         INT4            REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@492:         "last_draft_id"         INT8            NOT NULL,
jbe@495:         "last_suggestion_id"    INT8 );
jbe@492: CREATE INDEX "initiative_notification_sent_initiative_idx" ON "initiative_notification_sent" ("initiative_id");
jbe@478: 
jbe@496: CREATE TABLE "newsletter" (
jbe@496:         "id"                    SERIAL4         PRIMARY KEY,
jbe@496:         "published"             TIMESTAMPTZ     NOT NULL,
jbe@496:         "unit_id"               INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@496:         "include_all_members"   BOOLEAN         NOT NULL,
jbe@496:         "sent"                  TIMESTAMPTZ,
jbe@496:         "subject"               TEXT            NOT NULL,
jbe@496:         "content"               TEXT            NOT NULL );
jbe@496: CREATE INDEX "newsletter_unit_id_idx" ON "newsletter" ("unit_id", "published");
jbe@496: CREATE INDEX "newsletter_all_units_published_idx" ON "newsletter" ("published") WHERE "unit_id" ISNULL;
jbe@496: CREATE INDEX "newsletter_published_idx" ON "newsletter" ("published");
jbe@496: 
jbe@499: CREATE VIEW "event_for_notification" AS
jbe@499:   SELECT
jbe@499:     "member"."id" AS "recipient_id",
jbe@499:     "event".*
jbe@499:   FROM "member" CROSS JOIN "event"
jbe@499:   JOIN "issue" ON "issue"."id" = "event"."issue_id"
jbe@499:   JOIN "area" ON "area"."id" = "issue"."area_id"
jbe@499:   LEFT JOIN "privilege" ON
jbe@499:     "privilege"."member_id" = "member"."id" AND
jbe@499:     "privilege"."unit_id" = "area"."unit_id" AND
jbe@499:     "privilege"."voting_right" = TRUE
jbe@499:   LEFT JOIN "subscription" ON
jbe@499:     "subscription"."member_id" = "member"."id" AND
jbe@499:     "subscription"."unit_id" = "area"."unit_id"
jbe@499:   LEFT JOIN "ignored_area" ON
jbe@499:     "ignored_area"."member_id" = "member"."id" AND
jbe@499:     "ignored_area"."area_id" = "issue"."area_id"
jbe@499:   LEFT JOIN "interest" ON
jbe@499:     "interest"."member_id" = "member"."id" AND
jbe@499:     "interest"."issue_id" = "event"."issue_id"
jbe@499:   LEFT JOIN "supporter" ON
jbe@499:     "supporter"."member_id" = "member"."id" AND
jbe@499:     "supporter"."initiative_id" = "event"."initiative_id"
jbe@499:   WHERE ("privilege"."member_id" NOTNULL OR "subscription"."member_id" NOTNULL)
jbe@499:   AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL)
jbe@499:   AND (
jbe@499:     "event"."event" = 'issue_state_changed'::"event_type" OR
jbe@499:     ( "event"."event" = 'initiative_revoked'::"event_type" AND
jbe@499:       "supporter"."member_id" NOTNULL ) );
jbe@499: 
jbe@478: CREATE VIEW "updated_initiative" AS
jbe@478:   SELECT
jbe@499:     "supporter"."member_id" AS "recipient_id",
jbe@478:     FALSE AS "featured",
jbe@499:     "supporter"."initiative_id"
jbe@499:   FROM "supporter"
jbe@499:   JOIN "initiative" ON "supporter"."initiative_id" = "initiative"."id"
jbe@478:   JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@499:   LEFT JOIN "initiative_notification_sent" AS "sent" ON
jbe@499:     "sent"."member_id" = "supporter"."member_id" AND
jbe@499:     "sent"."initiative_id" = "supporter"."initiative_id"
jbe@499:   LEFT JOIN "ignored_initiative" ON
jbe@499:     "ignored_initiative"."member_id" = "supporter"."member_id" AND
jbe@499:     "ignored_initiative"."initiative_id" = "supporter"."initiative_id"
jbe@480:   WHERE "issue"."state" IN ('admission', 'discussion')
jbe@503:   AND "initiative"."revoked" ISNULL
jbe@499:   AND "ignored_initiative"."member_id" ISNULL
jbe@478:   AND (
jbe@478:     EXISTS (
jbe@478:       SELECT NULL FROM "draft"
jbe@499:       LEFT JOIN "ignored_member" ON
jbe@499:         "ignored_member"."member_id" = "supporter"."member_id" AND
jbe@499:         "ignored_member"."other_member_id" = "draft"."author_id"
jbe@499:       WHERE "draft"."initiative_id" = "supporter"."initiative_id"
jbe@478:       AND "draft"."id" > "supporter"."draft_id"
jbe@499:       AND "ignored_member"."member_id" ISNULL
jbe@478:     ) OR EXISTS (
jbe@478:       SELECT NULL FROM "suggestion"
jbe@492:       LEFT JOIN "opinion" ON
jbe@492:         "opinion"."member_id" = "supporter"."member_id" AND
jbe@492:         "opinion"."suggestion_id" = "suggestion"."id"
jbe@499:       LEFT JOIN "ignored_member" ON
jbe@499:         "ignored_member"."member_id" = "supporter"."member_id" AND
jbe@499:         "ignored_member"."other_member_id" = "suggestion"."author_id"
jbe@499:       WHERE "suggestion"."initiative_id" = "supporter"."initiative_id"
jbe@492:       AND "opinion"."member_id" ISNULL
jbe@499:       AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
jbe@499:       AND "ignored_member"."member_id" ISNULL
jbe@478:     )
jbe@478:   );
jbe@478: 
jbe@478: CREATE FUNCTION "featured_initiative"
jbe@499:   ( "recipient_id_p" "member"."id"%TYPE,
jbe@499:     "area_id_p"      "area"."id"%TYPE )
jbe@499:   RETURNS SETOF "initiative"."id"%TYPE
jbe@478:   LANGUAGE 'plpgsql' STABLE AS $$
jbe@478:     DECLARE
jbe@499:       "counter_v"         "member"."notification_counter"%TYPE;
jbe@499:       "sample_size_v"     "member"."notification_sample_size"%TYPE;
jbe@499:       "initiative_id_ary" INT4[];  --"initiative"."id"%TYPE[]
jbe@499:       "match_v"           BOOLEAN;
jbe@478:       "member_id_v"       "member"."id"%TYPE;
jbe@478:       "seed_v"            TEXT;
jbe@499:       "initiative_id_v"   "initiative"."id"%TYPE;
jbe@478:     BEGIN
jbe@499:       SELECT "notification_counter", "notification_sample_size"
jbe@499:         INTO "counter_v", "sample_size_v"
jbe@499:         FROM "member" WHERE "id" = "recipient_id_p";
jbe@478:       "initiative_id_ary" := '{}';
jbe@478:       LOOP
jbe@478:         "match_v" := FALSE;
jbe@478:         FOR "member_id_v", "seed_v" IN
jbe@478:           SELECT * FROM (
jbe@478:             SELECT DISTINCT
jbe@478:               "supporter"."member_id",
jbe@499:               md5(
jbe@499:                 "recipient_id_p" || '-' ||
jbe@499:                 "counter_v"      || '-' ||
jbe@499:                 "area_id_p"      || '-' ||
jbe@499:                 "supporter"."member_id"
jbe@499:               ) AS "seed"
jbe@478:             FROM "supporter"
jbe@478:             JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
jbe@478:             JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@499:             WHERE "supporter"."member_id" != "recipient_id_p"
jbe@478:             AND "issue"."area_id" = "area_id_p"
jbe@478:             AND "issue"."state" IN ('admission', 'discussion', 'verification')
jbe@478:           ) AS "subquery"
jbe@478:           ORDER BY "seed"
jbe@478:         LOOP
jbe@499:           SELECT "initiative"."id" INTO "initiative_id_v"
jbe@478:             FROM "initiative"
jbe@478:             JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@499:             JOIN "area" ON "area"."id" = "issue"."area_id"
jbe@478:             JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
jbe@478:             LEFT JOIN "supporter" AS "self_support" ON
jbe@478:               "self_support"."initiative_id" = "initiative"."id" AND
jbe@499:               "self_support"."member_id" = "recipient_id_p"
jbe@499:             LEFT JOIN "privilege" ON
jbe@499:               "privilege"."member_id" = "recipient_id_p" AND
jbe@499:               "privilege"."unit_id" = "area"."unit_id" AND
jbe@499:               "privilege"."voting_right" = TRUE
jbe@499:             LEFT JOIN "subscription" ON
jbe@499:               "subscription"."member_id" = "recipient_id_p" AND
jbe@499:               "subscription"."unit_id" = "area"."unit_id"
jbe@499:             LEFT JOIN "ignored_initiative" ON
jbe@499:               "ignored_initiative"."member_id" = "recipient_id_p" AND
jbe@499:               "ignored_initiative"."initiative_id" = "initiative"."id"
jbe@478:             WHERE "supporter"."member_id" = "member_id_v"
jbe@478:             AND "issue"."area_id" = "area_id_p"
jbe@478:             AND "issue"."state" IN ('admission', 'discussion', 'verification')
jbe@503:             AND "initiative"."revoked" ISNULL
jbe@478:             AND "self_support"."member_id" ISNULL
jbe@478:             AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
jbe@499:             AND (
jbe@499:               "privilege"."member_id" NOTNULL OR
jbe@499:               "subscription"."member_id" NOTNULL )
jbe@499:             AND "ignored_initiative"."member_id" ISNULL
jbe@499:             AND NOT EXISTS (
jbe@499:               SELECT NULL FROM "draft"
jbe@499:               JOIN "ignored_member" ON
jbe@499:                 "ignored_member"."member_id" = "recipient_id_p" AND
jbe@499:                 "ignored_member"."other_member_id" = "draft"."author_id"
jbe@499:               WHERE "draft"."initiative_id" = "initiative"."id"
jbe@499:             )
jbe@478:             ORDER BY md5("seed_v" || '-' || "initiative"."id")
jbe@478:             LIMIT 1;
jbe@478:           IF FOUND THEN
jbe@478:             "match_v" := TRUE;
jbe@499:             RETURN NEXT "initiative_id_v";
jbe@499:             IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN
jbe@478:               RETURN;
jbe@478:             END IF;
jbe@499:             "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";
jbe@478:           END IF;
jbe@478:         END LOOP;
jbe@478:         EXIT WHEN NOT "match_v";
jbe@478:       END LOOP;
jbe@478:       RETURN;
jbe@478:     END;
jbe@478:   $$;
jbe@478: 
jbe@478: CREATE VIEW "updated_or_featured_initiative" AS
jbe@478:   SELECT
jbe@499:     "subquery".*,
jbe@478:     NOT EXISTS (
jbe@478:       SELECT NULL FROM "initiative" AS "better_initiative"
jbe@499:       WHERE "better_initiative"."issue_id" = "initiative"."issue_id"
jbe@484:       AND
jbe@502:         ( COALESCE("better_initiative"."supporter_count", -1),
jbe@484:           -"better_initiative"."id" ) >
jbe@502:         ( COALESCE("initiative"."supporter_count", -1),
jbe@485:           -"initiative"."id" )
jbe@499:     ) AS "leading"
jbe@499:   FROM (
jbe@499:     SELECT * FROM "updated_initiative"
jbe@499:     UNION ALL
jbe@499:     SELECT
jbe@499:       "member"."id" AS "recipient_id",
jbe@499:       TRUE AS "featured",
jbe@499:       "featured_initiative_id" AS "initiative_id"
jbe@499:     FROM "member" CROSS JOIN "area"
jbe@499:     CROSS JOIN LATERAL
jbe@499:       "featured_initiative"("member"."id", "area"."id") AS "featured_initiative_id"
jbe@499:     JOIN "initiative" ON "initiative"."id" = "featured_initiative_id"
jbe@499:   ) AS "subquery"
jbe@499:   JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id";
jbe@478: 
jbe@478: CREATE VIEW "leading_complement_initiative" AS
jbe@478:   SELECT * FROM (
jbe@499:     SELECT DISTINCT ON ("uf_initiative"."recipient_id", "initiative"."issue_id")
jbe@499:       "uf_initiative"."recipient_id",
jbe@478:       FALSE AS "featured",
jbe@499:       "uf_initiative"."initiative_id",
jbe@499:       TRUE AS "leading"
jbe@492:     FROM "updated_or_featured_initiative" AS "uf_initiative"
jbe@499:     JOIN "initiative" AS "uf_initiative_full" ON
jbe@499:       "uf_initiative_full"."id" = "uf_initiative"."initiative_id"
jbe@492:     JOIN "initiative" ON
jbe@499:       "initiative"."issue_id" = "uf_initiative_full"."issue_id"
jbe@503:     WHERE "initiative"."revoked" ISNULL
jbe@478:     ORDER BY
jbe@499:       "uf_initiative"."recipient_id",
jbe@478:       "initiative"."issue_id",
jbe@502:       "initiative"."supporter_count" DESC,
jbe@478:       "initiative"."id"
jbe@478:   ) AS "subquery"
jbe@478:   WHERE NOT EXISTS (
jbe@478:     SELECT NULL FROM "updated_or_featured_initiative" AS "other"
jbe@499:     WHERE "other"."recipient_id" = "subquery"."recipient_id"
jbe@499:     AND "other"."initiative_id" = "subquery"."initiative_id"
jbe@478:   );
jbe@478: 
jbe@492: CREATE VIEW "unfiltered_initiative_for_notification" AS
jbe@499:   SELECT
jbe@499:     "subquery".*,
jbe@499:     "supporter"."member_id" NOTNULL AS "supported",
jbe@499:     CASE WHEN "supporter"."member_id" NOTNULL THEN
jbe@499:       EXISTS (
jbe@499:         SELECT NULL FROM "draft"
jbe@499:         WHERE "draft"."initiative_id" = "subquery"."initiative_id"
jbe@499:         AND "draft"."id" > "supporter"."draft_id"
jbe@499:       )
jbe@499:     ELSE
jbe@499:       EXISTS (
jbe@499:         SELECT NULL FROM "draft"
jbe@499:         WHERE "draft"."initiative_id" = "subquery"."initiative_id"
jbe@499:         AND COALESCE("draft"."id" > "sent"."last_draft_id", TRUE)
jbe@499:       )
jbe@499:     END AS "new_draft",
jbe@499:     CASE WHEN "supporter"."member_id" NOTNULL THEN
jbe@499:       ( SELECT count(1) FROM "suggestion"
jbe@499:         LEFT JOIN "opinion" ON
jbe@499:           "opinion"."member_id" = "supporter"."member_id" AND
jbe@499:           "opinion"."suggestion_id" = "suggestion"."id"
jbe@499:         WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"
jbe@499:         AND "opinion"."member_id" ISNULL
jbe@499:         AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
jbe@499:       )
jbe@499:     ELSE
jbe@499:       ( SELECT count(1) FROM "suggestion"
jbe@499:         WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"
jbe@499:         AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
jbe@499:       )
jbe@499:     END AS "new_suggestion_count"
jbe@499:   FROM (
jbe@499:     SELECT * FROM "updated_or_featured_initiative"
jbe@499:     UNION ALL
jbe@499:     SELECT * FROM "leading_complement_initiative"
jbe@499:   ) AS "subquery"
jbe@499:   LEFT JOIN "supporter" ON
jbe@499:     "supporter"."member_id" = "subquery"."recipient_id" AND
jbe@499:     "supporter"."initiative_id" = "subquery"."initiative_id"
jbe@499:   LEFT JOIN "initiative_notification_sent" AS "sent" ON
jbe@499:     "sent"."member_id" = "subquery"."recipient_id" AND
jbe@499:     "sent"."initiative_id" = "subquery"."initiative_id";
jbe@478: 
jbe@492: CREATE VIEW "initiative_for_notification" AS
jbe@499:   SELECT "unfiltered1".*
jbe@499:   FROM "unfiltered_initiative_for_notification" "unfiltered1"
jbe@499:   JOIN "initiative" AS "initiative1" ON
jbe@499:     "initiative1"."id" = "unfiltered1"."initiative_id"
jbe@499:   JOIN "issue" AS "issue1" ON "issue1"."id" = "initiative1"."issue_id"
jbe@492:   WHERE EXISTS (
jbe@492:     SELECT NULL
jbe@499:     FROM "unfiltered_initiative_for_notification" "unfiltered2"
jbe@499:     JOIN "initiative" AS "initiative2" ON
jbe@499:       "initiative2"."id" = "unfiltered2"."initiative_id"
jbe@499:     JOIN "issue" AS "issue2" ON "issue2"."id" = "initiative2"."issue_id"
jbe@499:     WHERE "unfiltered1"."recipient_id" = "unfiltered2"."recipient_id"
jbe@492:     AND "issue1"."area_id" = "issue2"."area_id"
jbe@499:     AND ("unfiltered2"."new_draft" OR "unfiltered2"."new_suggestion_count" > 0 )
jbe@492:   );
jbe@492: 
jbe@497: CREATE VIEW "newsletter_to_send" AS
jbe@497:   SELECT
jbe@499:     "member"."id" AS "recipient_id",
jbe@499:     "newsletter"."id" AS "newsletter_id"
jbe@497:   FROM "newsletter" CROSS JOIN "member"
jbe@497:   LEFT JOIN "privilege" ON
jbe@497:     "privilege"."member_id" = "member"."id" AND
jbe@497:     "privilege"."unit_id" = "newsletter"."unit_id" AND
jbe@497:     "privilege"."voting_right" = TRUE
jbe@497:   LEFT JOIN "subscription" ON
jbe@497:     "subscription"."member_id" = "member"."id" AND
jbe@497:     "subscription"."unit_id" = "newsletter"."unit_id"
jbe@498:   WHERE "newsletter"."published" <= now()
jbe@497:   AND "newsletter"."sent" ISNULL
jbe@497:   AND "member"."locked" = FALSE
jbe@497:   AND (
jbe@497:     "member"."disable_notifications" = FALSE OR
jbe@497:     "newsletter"."include_all_members" = TRUE )
jbe@497:   AND (
jbe@497:     "newsletter"."unit_id" ISNULL OR
jbe@497:     "privilege"."member_id" NOTNULL OR
jbe@497:     "subscription"."member_id" NOTNULL );
jbe@497: 
jbe@492: CREATE FUNCTION "get_initiatives_for_notification"
jbe@501:   ( "recipient_id_p" "member"."id"%TYPE )
jbe@492:   RETURNS SETOF "initiative_for_notification"
jbe@492:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@492:     DECLARE
jbe@492:       "result_row"           "initiative_for_notification"%ROWTYPE;
jbe@492:       "last_draft_id_v"      "draft"."id"%TYPE;
jbe@492:       "last_suggestion_id_v" "suggestion"."id"%TYPE;
jbe@492:     BEGIN
jbe@492:       PERFORM "require_transaction_isolation"();
jbe@501:       PERFORM NULL FROM "member" WHERE "id" = "recipient_id_p" FOR UPDATE;
jbe@492:       FOR "result_row" IN
jbe@492:         SELECT * FROM "initiative_for_notification"
jbe@501:         WHERE "recipient_id" = "recipient_id_p"
jbe@492:       LOOP
jbe@492:         SELECT "id" INTO "last_draft_id_v" FROM "draft"
jbe@499:           WHERE "draft"."initiative_id" = "result_row"."initiative_id"
jbe@492:           ORDER BY "id" DESC LIMIT 1;
jbe@492:         SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
jbe@499:           WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"
jbe@492:           ORDER BY "id" DESC LIMIT 1;
jbe@492:         INSERT INTO "initiative_notification_sent"
jbe@492:           ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
jbe@492:           VALUES (
jbe@501:             "recipient_id_p",
jbe@499:             "result_row"."initiative_id",
jbe@493:             "last_draft_id_v",
jbe@493:             "last_suggestion_id_v" )
jbe@492:           ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
jbe@492:             "last_draft_id" = CASE
jbe@494:               WHEN "initiative_notification_sent"."last_draft_id" > "last_draft_id_v"
jbe@494:               THEN "initiative_notification_sent"."last_draft_id"
jbe@492:               ELSE "last_draft_id_v"
jbe@492:             END,
jbe@492:             "last_suggestion_id" = CASE
jbe@494:               WHEN "initiative_notification_sent"."last_suggestion_id" > "last_suggestion_id_v"
jbe@494:               THEN "initiative_notification_sent"."last_suggestion_id"
jbe@492:               ELSE "last_suggestion_id_v"
jbe@492:             END;
jbe@492:         RETURN NEXT "result_row";
jbe@492:       END LOOP;
jbe@492:       DELETE FROM "initiative_notification_sent"
jbe@492:         USING "initiative", "issue"
jbe@501:         WHERE "initiative_notification_sent"."member_id" = "recipient_id_p"
jbe@492:         AND "initiative"."id" = "initiative_notification_sent"."initiative_id"
jbe@492:         AND "issue"."id" = "initiative"."issue_id"
jbe@492:         AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
jbe@492:       UPDATE "member" SET "notification_counter" = "notification_counter" + 1
jbe@501:         WHERE "id" = "recipient_id_p";
jbe@492:       RETURN;
jbe@492:     END;
jbe@492:   $$;
jbe@492: 
jbe@478: COMMIT;