1.1 --- a/core.sql	Sun Apr 03 20:57:44 2016 +0200
     1.2 +++ b/core.sql	Mon Apr 04 18:42:56 2016 +0200
     1.3 @@ -89,12 +89,6 @@
     1.4  COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';
     1.5  
     1.6  
     1.7 -CREATE TYPE "notify_level" AS ENUM
     1.8 -  ('none', 'voting', 'verification', 'discussion', 'all');
     1.9 -
    1.10 -COMMENT ON TYPE "notify_level" IS 'Level of notification: ''none'' = no notifications, ''voting'' = notifications about finished issues and issues in voting, ''verification'' = notifications about finished issues, issues in voting and verification phase, ''discussion'' = notifications about everything except issues in admission phase, ''all'' = notifications about everything';
    1.11 -
    1.12 -
    1.13  CREATE TABLE "member" (
    1.14          "id"                    SERIAL4         PRIMARY KEY,
    1.15          "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
    1.16 @@ -2339,135 +2333,96 @@
    1.17  COMMENT ON VIEW "member_contingent_left" IS 'Amount of text entries or initiatives which can be posted now instantly by a member. This view should be used by a frontend to determine, if the contingent for posting is exhausted.';
    1.18  
    1.19  
    1.20 -CREATE VIEW "event_seen_by_member" AS
    1.21 +CREATE VIEW "event_for_notification" AS
    1.22    SELECT
    1.23 -    "member"."id" AS "seen_by_member_id",
    1.24 -    CASE WHEN "event"."state" IN (
    1.25 -      'voting',
    1.26 -      'finished_without_winner',
    1.27 -      'finished_with_winner'
    1.28 -    ) THEN
    1.29 -      'voting'::"notify_level"
    1.30 -    ELSE
    1.31 -      CASE WHEN "event"."state" IN (
    1.32 -        'verification',
    1.33 -        'canceled_after_revocation_during_verification',
    1.34 -        'canceled_no_initiative_admitted'
    1.35 -      ) THEN
    1.36 -        'verification'::"notify_level"
    1.37 -      ELSE
    1.38 -        CASE WHEN "event"."state" IN (
    1.39 -          'discussion',
    1.40 -          'canceled_after_revocation_during_discussion'
    1.41 -        ) THEN
    1.42 -          'discussion'::"notify_level"
    1.43 -        ELSE
    1.44 -          'all'::"notify_level"
    1.45 -        END
    1.46 -      END
    1.47 -    END AS "notify_level",
    1.48 +    "member"."id" AS "recipient_id",
    1.49      "event".*
    1.50    FROM "member" CROSS JOIN "event"
    1.51 -  LEFT JOIN "issue"
    1.52 -    ON "event"."issue_id" = "issue"."id"
    1.53 -  LEFT JOIN "membership"
    1.54 -    ON "member"."id" = "membership"."member_id"
    1.55 -    AND "issue"."area_id" = "membership"."area_id"
    1.56 -  LEFT JOIN "interest"
    1.57 -    ON "member"."id" = "interest"."member_id"
    1.58 -    AND "event"."issue_id" = "interest"."issue_id"
    1.59 -  LEFT JOIN "ignored_member"
    1.60 -    ON "member"."id" = "ignored_member"."member_id"
    1.61 -    AND "event"."member_id" = "ignored_member"."other_member_id"
    1.62 -  LEFT JOIN "ignored_initiative"
    1.63 -    ON "member"."id" = "ignored_initiative"."member_id"
    1.64 -    AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
    1.65 -  WHERE (
    1.66 -    "interest"."member_id" NOTNULL OR
    1.67 -    ( "membership"."member_id" NOTNULL AND
    1.68 -      "event"."event" IN (
    1.69 -        'issue_state_changed',
    1.70 -        'initiative_created_in_new_issue',
    1.71 -        'initiative_created_in_existing_issue',
    1.72 -        'initiative_revoked' ) ) )
    1.73 -  AND "ignored_member"."member_id" ISNULL
    1.74 -  AND "ignored_initiative"."member_id" ISNULL;
    1.75 -
    1.76 -COMMENT ON VIEW "event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests and support, but ignoring members "notify_level"';
    1.77 +  JOIN "issue" ON "issue"."id" = "event"."issue_id"
    1.78 +  JOIN "area" ON "area"."id" = "issue"."area_id"
    1.79 +  LEFT JOIN "privilege" ON
    1.80 +    "privilege"."member_id" = "member"."id" AND
    1.81 +    "privilege"."unit_id" = "area"."unit_id" AND
    1.82 +    "privilege"."voting_right" = TRUE
    1.83 +  LEFT JOIN "subscription" ON
    1.84 +    "subscription"."member_id" = "member"."id" AND
    1.85 +    "subscription"."unit_id" = "area"."unit_id"
    1.86 +  LEFT JOIN "ignored_area" ON
    1.87 +    "ignored_area"."member_id" = "member"."id" AND
    1.88 +    "ignored_area"."area_id" = "issue"."area_id"
    1.89 +  LEFT JOIN "interest" ON
    1.90 +    "interest"."member_id" = "member"."id" AND
    1.91 +    "interest"."issue_id" = "event"."issue_id"
    1.92 +  LEFT JOIN "supporter" ON
    1.93 +    "supporter"."member_id" = "member"."id" AND
    1.94 +    "supporter"."initiative_id" = "event"."initiative_id"
    1.95 +  WHERE ("privilege"."member_id" NOTNULL OR "subscription"."member_id" NOTNULL)
    1.96 +  AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL)
    1.97 +  AND (
    1.98 +    "event"."event" = 'issue_state_changed'::"event_type" OR
    1.99 +    ( "event"."event" = 'initiative_revoked'::"event_type" AND
   1.100 +      "supporter"."member_id" NOTNULL ) );
   1.101 +
   1.102 +COMMENT ON VIEW "event_for_notification" IS 'TODO: documentation';
   1.103  
   1.104  
   1.105  CREATE VIEW "updated_initiative" AS
   1.106    SELECT
   1.107 -    "supporter"."member_id" AS "seen_by_member_id",
   1.108 -    TRUE AS "supported",
   1.109 -    EXISTS (
   1.110 -      SELECT NULL FROM "draft"
   1.111 -      WHERE "draft"."initiative_id" = "initiative"."id"
   1.112 -      AND "draft"."id" > "supporter"."draft_id"
   1.113 -    ) AS "new_draft",
   1.114 -    ( SELECT count(1) FROM "suggestion"
   1.115 -      LEFT JOIN "opinion" ON
   1.116 -        "opinion"."member_id" = "supporter"."member_id" AND
   1.117 -        "opinion"."suggestion_id" = "suggestion"."id"
   1.118 -      WHERE "suggestion"."initiative_id" = "initiative"."id"
   1.119 -      AND "opinion"."member_id" ISNULL
   1.120 -      AND COALESCE(
   1.121 -        "suggestion"."id" > "sent"."last_suggestion_id",
   1.122 -        TRUE
   1.123 -      )
   1.124 -    ) AS "new_suggestion_count",
   1.125 +    "supporter"."member_id" AS "recipient_id",
   1.126      FALSE AS "featured",
   1.127 -    NOT EXISTS (
   1.128 -      SELECT NULL FROM "initiative" AS "better_initiative"
   1.129 -      WHERE
   1.130 -        "better_initiative"."issue_id" = "initiative"."issue_id"
   1.131 -      AND
   1.132 -        ( COALESCE("better_initiative"."harmonic_weight", -1),
   1.133 -          -"better_initiative"."id" ) >
   1.134 -        ( COALESCE("initiative"."harmonic_weight", -1),
   1.135 -          -"initiative"."id" )
   1.136 -    ) AS "leading",
   1.137 -    "initiative".*
   1.138 -  FROM "supporter" JOIN "initiative"
   1.139 -  ON "supporter"."initiative_id" = "initiative"."id"
   1.140 -  LEFT JOIN "initiative_notification_sent" AS "sent"
   1.141 -    ON "sent"."member_id" = "supporter"."member_id"
   1.142 -    AND "sent"."initiative_id" = "initiative"."id"
   1.143 +    "supporter"."initiative_id"
   1.144 +  FROM "supporter"
   1.145 +  JOIN "initiative" ON "supporter"."initiative_id" = "initiative"."id"
   1.146    JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
   1.147 +  LEFT JOIN "initiative_notification_sent" AS "sent" ON
   1.148 +    "sent"."member_id" = "supporter"."member_id" AND
   1.149 +    "sent"."initiative_id" = "supporter"."initiative_id"
   1.150 +  LEFT JOIN "ignored_initiative" ON
   1.151 +    "ignored_initiative"."member_id" = "supporter"."member_id" AND
   1.152 +    "ignored_initiative"."initiative_id" = "supporter"."initiative_id"
   1.153    WHERE "issue"."state" IN ('admission', 'discussion')
   1.154 +  AND "ignored_initiative"."member_id" ISNULL
   1.155    AND (
   1.156      EXISTS (
   1.157        SELECT NULL FROM "draft"
   1.158 -      WHERE "draft"."initiative_id" = "initiative"."id"
   1.159 +      LEFT JOIN "ignored_member" ON
   1.160 +        "ignored_member"."member_id" = "supporter"."member_id" AND
   1.161 +        "ignored_member"."other_member_id" = "draft"."author_id"
   1.162 +      WHERE "draft"."initiative_id" = "supporter"."initiative_id"
   1.163        AND "draft"."id" > "supporter"."draft_id"
   1.164 +      AND "ignored_member"."member_id" ISNULL
   1.165      ) OR EXISTS (
   1.166        SELECT NULL FROM "suggestion"
   1.167        LEFT JOIN "opinion" ON
   1.168          "opinion"."member_id" = "supporter"."member_id" AND
   1.169          "opinion"."suggestion_id" = "suggestion"."id"
   1.170 -      WHERE "suggestion"."initiative_id" = "initiative"."id"
   1.171 +      LEFT JOIN "ignored_member" ON
   1.172 +        "ignored_member"."member_id" = "supporter"."member_id" AND
   1.173 +        "ignored_member"."other_member_id" = "suggestion"."author_id"
   1.174 +      WHERE "suggestion"."initiative_id" = "supporter"."initiative_id"
   1.175        AND "opinion"."member_id" ISNULL
   1.176 -      AND COALESCE(
   1.177 -        "suggestion"."id" > "sent"."last_suggestion_id",
   1.178 -        TRUE
   1.179 -      )
   1.180 +      AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
   1.181 +      AND "ignored_member"."member_id" ISNULL
   1.182      )
   1.183    );
   1.184  
   1.185  CREATE FUNCTION "featured_initiative"
   1.186 -  ( "member_id_p" "member"."id"%TYPE,
   1.187 -    "area_id_p"   "area"."id"%TYPE )
   1.188 -  RETURNS SETOF "initiative"
   1.189 +  ( "recipient_id_p" "member"."id"%TYPE,
   1.190 +    "area_id_p"      "area"."id"%TYPE )
   1.191 +  RETURNS SETOF "initiative"."id"%TYPE
   1.192    LANGUAGE 'plpgsql' STABLE AS $$
   1.193      DECLARE
   1.194 -      "member_row"        "member"%ROWTYPE;
   1.195 +      "counter_v"         "member"."notification_counter"%TYPE;
   1.196 +      "sample_size_v"     "member"."notification_sample_size"%TYPE;
   1.197 +      "initiative_id_ary" INT4[];  --"initiative"."id"%TYPE[]
   1.198 +      "match_v"           BOOLEAN;
   1.199        "member_id_v"       "member"."id"%TYPE;
   1.200        "seed_v"            TEXT;
   1.201 -      "result_row"        "initiative"%ROWTYPE;
   1.202 -      "match_v"           BOOLEAN;
   1.203 -      "initiative_id_ary" INT4[];  --"initiative"."id"%TYPE[]
   1.204 +      "initiative_id_v"   "initiative"."id"%TYPE;
   1.205      BEGIN
   1.206 -      SELECT INTO "member_row" * FROM "member" WHERE "id" = "member_id_p";
   1.207 +      SELECT "notification_counter", "notification_sample_size"
   1.208 +        INTO "counter_v", "sample_size_v"
   1.209 +        FROM "member" WHERE "id" = "recipient_id_p";
   1.210        "initiative_id_ary" := '{}';
   1.211        LOOP
   1.212          "match_v" := FALSE;
   1.213 @@ -2475,38 +2430,64 @@
   1.214            SELECT * FROM (
   1.215              SELECT DISTINCT
   1.216                "supporter"."member_id",
   1.217 -              md5("member_id_p" || '-' || "member_row"."notification_counter" || '-' || "area_id_p" || '-' || "supporter"."member_id") AS "seed"
   1.218 +              md5(
   1.219 +                "recipient_id_p" || '-' ||
   1.220 +                "counter_v"      || '-' ||
   1.221 +                "area_id_p"      || '-' ||
   1.222 +                "supporter"."member_id"
   1.223 +              ) AS "seed"
   1.224              FROM "supporter"
   1.225 -            JOIN "member" ON "member"."id" = "supporter"."member_id"
   1.226              JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
   1.227              JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
   1.228 -            WHERE "supporter"."member_id" != "member_id_p"
   1.229 +            WHERE "supporter"."member_id" != "recipient_id_p"
   1.230              AND "issue"."area_id" = "area_id_p"
   1.231              AND "issue"."state" IN ('admission', 'discussion', 'verification')
   1.232            ) AS "subquery"
   1.233            ORDER BY "seed"
   1.234          LOOP
   1.235 -          SELECT "initiative".* INTO "result_row"
   1.236 +          SELECT "initiative"."id" INTO "initiative_id_v"
   1.237              FROM "initiative"
   1.238              JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
   1.239 +            JOIN "area" ON "area"."id" = "issue"."area_id"
   1.240              JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
   1.241              LEFT JOIN "supporter" AS "self_support" ON
   1.242                "self_support"."initiative_id" = "initiative"."id" AND
   1.243 -              "self_support"."member_id" = "member_id_p"
   1.244 +              "self_support"."member_id" = "recipient_id_p"
   1.245 +            LEFT JOIN "privilege" ON
   1.246 +              "privilege"."member_id" = "recipient_id_p" AND
   1.247 +              "privilege"."unit_id" = "area"."unit_id" AND
   1.248 +              "privilege"."voting_right" = TRUE
   1.249 +            LEFT JOIN "subscription" ON
   1.250 +              "subscription"."member_id" = "recipient_id_p" AND
   1.251 +              "subscription"."unit_id" = "area"."unit_id"
   1.252 +            LEFT JOIN "ignored_initiative" ON
   1.253 +              "ignored_initiative"."member_id" = "recipient_id_p" AND
   1.254 +              "ignored_initiative"."initiative_id" = "initiative"."id"
   1.255              WHERE "supporter"."member_id" = "member_id_v"
   1.256              AND "issue"."area_id" = "area_id_p"
   1.257              AND "issue"."state" IN ('admission', 'discussion', 'verification')
   1.258              AND "self_support"."member_id" ISNULL
   1.259              AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
   1.260 +            AND (
   1.261 +              "privilege"."member_id" NOTNULL OR
   1.262 +              "subscription"."member_id" NOTNULL )
   1.263 +            AND "ignored_initiative"."member_id" ISNULL
   1.264 +            AND NOT EXISTS (
   1.265 +              SELECT NULL FROM "draft"
   1.266 +              JOIN "ignored_member" ON
   1.267 +                "ignored_member"."member_id" = "recipient_id_p" AND
   1.268 +                "ignored_member"."other_member_id" = "draft"."author_id"
   1.269 +              WHERE "draft"."initiative_id" = "initiative"."id"
   1.270 +            )
   1.271              ORDER BY md5("seed_v" || '-' || "initiative"."id")
   1.272              LIMIT 1;
   1.273            IF FOUND THEN
   1.274              "match_v" := TRUE;
   1.275 -            "initiative_id_ary" := "initiative_id_ary" || "result_row"."id";
   1.276 -            RETURN NEXT "result_row";
   1.277 -            IF array_length("initiative_id_ary", 1) >= "member_row"."notification_sample_size" THEN
   1.278 +            RETURN NEXT "initiative_id_v";
   1.279 +            IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN
   1.280                RETURN;
   1.281              END IF;
   1.282 +            "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";
   1.283            END IF;
   1.284          END LOOP;
   1.285          EXIT WHEN NOT "match_v";
   1.286 @@ -2516,115 +2497,120 @@
   1.287    $$;
   1.288  
   1.289  CREATE VIEW "updated_or_featured_initiative" AS
   1.290 -  SELECT * FROM "updated_initiative"
   1.291 -  UNION ALL
   1.292    SELECT
   1.293 -    "member"."id" AS "seen_by_member_id",
   1.294 -    FALSE AS "supported",
   1.295 -    EXISTS (
   1.296 -      SELECT NULL FROM "draft"
   1.297 -      WHERE "draft"."initiative_id" = "initiative"."id"
   1.298 -      AND COALESCE(
   1.299 -        "draft"."id" > "sent"."last_draft_id",
   1.300 -        TRUE
   1.301 -      )
   1.302 -    ) AS "new_draft",
   1.303 -    ( SELECT count(1) FROM "suggestion"
   1.304 -      WHERE "suggestion"."initiative_id" = "initiative"."id"
   1.305 -      AND COALESCE(
   1.306 -        "suggestion"."id" > "sent"."last_suggestion_id",
   1.307 -        TRUE
   1.308 -      )
   1.309 -    ) AS "new_suggestion_count",
   1.310 -    TRUE AS "featured",
   1.311 +    "subquery".*,
   1.312      NOT EXISTS (
   1.313        SELECT NULL FROM "initiative" AS "better_initiative"
   1.314 -      WHERE
   1.315 -        "better_initiative"."issue_id" = "initiative"."issue_id"
   1.316 +      WHERE "better_initiative"."issue_id" = "initiative"."issue_id"
   1.317        AND
   1.318          ( COALESCE("better_initiative"."harmonic_weight", -1),
   1.319            -"better_initiative"."id" ) >
   1.320          ( COALESCE("initiative"."harmonic_weight", -1),
   1.321            -"initiative"."id" )
   1.322 -    ) AS "leading",
   1.323 -    "initiative".*
   1.324 -  FROM "member" CROSS JOIN "area"
   1.325 -  CROSS JOIN LATERAL
   1.326 -    "featured_initiative"("member"."id", "area"."id") AS "initiative"
   1.327 -  LEFT JOIN "initiative_notification_sent" AS "sent"
   1.328 -    ON "sent"."member_id" = "member"."id"
   1.329 -    AND "sent"."initiative_id" = "initiative"."id";
   1.330 +    ) AS "leading"
   1.331 +  FROM (
   1.332 +    SELECT * FROM "updated_initiative"
   1.333 +    UNION ALL
   1.334 +    SELECT
   1.335 +      "member"."id" AS "recipient_id",
   1.336 +      TRUE AS "featured",
   1.337 +      "featured_initiative_id" AS "initiative_id"
   1.338 +    FROM "member" CROSS JOIN "area"
   1.339 +    CROSS JOIN LATERAL
   1.340 +      "featured_initiative"("member"."id", "area"."id") AS "featured_initiative_id"
   1.341 +    JOIN "initiative" ON "initiative"."id" = "featured_initiative_id"
   1.342 +  ) AS "subquery"
   1.343 +  JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id";
   1.344  
   1.345  CREATE VIEW "leading_complement_initiative" AS
   1.346    SELECT * FROM (
   1.347 -    SELECT DISTINCT ON ("seen_by_member_id", "initiative"."issue_id")
   1.348 -      "uf_initiative"."seen_by_member_id",
   1.349 -      "supporter"."member_id" NOTNULL AS "supported",
   1.350 -      CASE WHEN "supporter"."member_id" NOTNULL THEN FALSE ELSE
   1.351 -        EXISTS (
   1.352 -          SELECT NULL FROM "draft"
   1.353 -          WHERE "draft"."initiative_id" = "initiative"."id"
   1.354 -          AND COALESCE(
   1.355 -            "draft"."id" > "sent"."last_draft_id",
   1.356 -            TRUE
   1.357 -          )
   1.358 -        )
   1.359 -      END AS "new_draft",
   1.360 -      CASE WHEN "supporter"."member_id" NOTNULL THEN 0 ELSE
   1.361 -        ( SELECT count(1) FROM "suggestion"
   1.362 -          WHERE "suggestion"."initiative_id" = "initiative"."id"
   1.363 -          AND COALESCE(
   1.364 -            "suggestion"."id" > "sent"."last_suggestion_id",
   1.365 -            TRUE
   1.366 -          )
   1.367 -        )
   1.368 -      END AS "new_suggestion_count",
   1.369 +    SELECT DISTINCT ON ("uf_initiative"."recipient_id", "initiative"."issue_id")
   1.370 +      "uf_initiative"."recipient_id",
   1.371        FALSE AS "featured",
   1.372 -      TRUE AS "leading",
   1.373 -      "initiative".*
   1.374 +      "uf_initiative"."initiative_id",
   1.375 +      TRUE AS "leading"
   1.376      FROM "updated_or_featured_initiative" AS "uf_initiative"
   1.377 +    JOIN "initiative" AS "uf_initiative_full" ON
   1.378 +      "uf_initiative_full"."id" = "uf_initiative"."initiative_id"
   1.379      JOIN "initiative" ON
   1.380 -      "uf_initiative"."issue_id" = "initiative"."issue_id"
   1.381 -    LEFT JOIN "supporter" ON
   1.382 -      "supporter"."member_id" = "uf_initiative"."seen_by_member_id" AND
   1.383 -      "supporter"."initiative_id" = "initiative"."id"
   1.384 -    LEFT JOIN "initiative_notification_sent" AS "sent"
   1.385 -      ON "sent"."member_id" = "uf_initiative"."seen_by_member_id"
   1.386 -      AND "sent"."initiative_id" = "initiative"."id"
   1.387 +      "initiative"."issue_id" = "uf_initiative_full"."issue_id"
   1.388      ORDER BY
   1.389 -      "seen_by_member_id",
   1.390 +      "uf_initiative"."recipient_id",
   1.391        "initiative"."issue_id",
   1.392        "initiative"."harmonic_weight" DESC,
   1.393        "initiative"."id"
   1.394    ) AS "subquery"
   1.395    WHERE NOT EXISTS (
   1.396      SELECT NULL FROM "updated_or_featured_initiative" AS "other"
   1.397 -    WHERE "other"."seen_by_member_id" = "subquery"."seen_by_member_id"
   1.398 -    AND "other"."id" = "subquery"."id"
   1.399 +    WHERE "other"."recipient_id" = "subquery"."recipient_id"
   1.400 +    AND "other"."initiative_id" = "subquery"."initiative_id"
   1.401    );
   1.402  
   1.403  CREATE VIEW "unfiltered_initiative_for_notification" AS
   1.404 -  SELECT * FROM "updated_or_featured_initiative"
   1.405 -  UNION ALL
   1.406 -  SELECT * FROM "leading_complement_initiative";
   1.407 +  SELECT
   1.408 +    "subquery".*,
   1.409 +    "supporter"."member_id" NOTNULL AS "supported",
   1.410 +    CASE WHEN "supporter"."member_id" NOTNULL THEN
   1.411 +      EXISTS (
   1.412 +        SELECT NULL FROM "draft"
   1.413 +        WHERE "draft"."initiative_id" = "subquery"."initiative_id"
   1.414 +        AND "draft"."id" > "supporter"."draft_id"
   1.415 +      )
   1.416 +    ELSE
   1.417 +      EXISTS (
   1.418 +        SELECT NULL FROM "draft"
   1.419 +        WHERE "draft"."initiative_id" = "subquery"."initiative_id"
   1.420 +        AND COALESCE("draft"."id" > "sent"."last_draft_id", TRUE)
   1.421 +      )
   1.422 +    END AS "new_draft",
   1.423 +    CASE WHEN "supporter"."member_id" NOTNULL THEN
   1.424 +      ( SELECT count(1) FROM "suggestion"
   1.425 +        LEFT JOIN "opinion" ON
   1.426 +          "opinion"."member_id" = "supporter"."member_id" AND
   1.427 +          "opinion"."suggestion_id" = "suggestion"."id"
   1.428 +        WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"
   1.429 +        AND "opinion"."member_id" ISNULL
   1.430 +        AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
   1.431 +      )
   1.432 +    ELSE
   1.433 +      ( SELECT count(1) FROM "suggestion"
   1.434 +        WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"
   1.435 +        AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
   1.436 +      )
   1.437 +    END AS "new_suggestion_count"
   1.438 +  FROM (
   1.439 +    SELECT * FROM "updated_or_featured_initiative"
   1.440 +    UNION ALL
   1.441 +    SELECT * FROM "leading_complement_initiative"
   1.442 +  ) AS "subquery"
   1.443 +  LEFT JOIN "supporter" ON
   1.444 +    "supporter"."member_id" = "subquery"."recipient_id" AND
   1.445 +    "supporter"."initiative_id" = "subquery"."initiative_id"
   1.446 +  LEFT JOIN "initiative_notification_sent" AS "sent" ON
   1.447 +    "sent"."member_id" = "subquery"."recipient_id" AND
   1.448 +    "sent"."initiative_id" = "subquery"."initiative_id";
   1.449  
   1.450  CREATE VIEW "initiative_for_notification" AS
   1.451 -  SELECT "initiative1".*
   1.452 -  FROM "unfiltered_initiative_for_notification" "initiative1"
   1.453 -  JOIN "issue" AS "issue1" ON "initiative1"."issue_id" = "issue1"."id"
   1.454 +  SELECT "unfiltered1".*
   1.455 +  FROM "unfiltered_initiative_for_notification" "unfiltered1"
   1.456 +  JOIN "initiative" AS "initiative1" ON
   1.457 +    "initiative1"."id" = "unfiltered1"."initiative_id"
   1.458 +  JOIN "issue" AS "issue1" ON "issue1"."id" = "initiative1"."issue_id"
   1.459    WHERE EXISTS (
   1.460      SELECT NULL
   1.461 -    FROM "unfiltered_initiative_for_notification" "initiative2"
   1.462 -    JOIN "issue" AS "issue2" ON "initiative2"."issue_id" = "issue2"."id"
   1.463 -    WHERE "initiative1"."seen_by_member_id" = "initiative2"."seen_by_member_id"
   1.464 +    FROM "unfiltered_initiative_for_notification" "unfiltered2"
   1.465 +    JOIN "initiative" AS "initiative2" ON
   1.466 +      "initiative2"."id" = "unfiltered2"."initiative_id"
   1.467 +    JOIN "issue" AS "issue2" ON "issue2"."id" = "initiative2"."issue_id"
   1.468 +    WHERE "unfiltered1"."recipient_id" = "unfiltered2"."recipient_id"
   1.469      AND "issue1"."area_id" = "issue2"."area_id"
   1.470 -    AND ( "initiative2"."new_draft" OR "initiative2"."new_suggestion_count" > 0 )
   1.471 +    AND ("unfiltered2"."new_draft" OR "unfiltered2"."new_suggestion_count" > 0 )
   1.472    );
   1.473  
   1.474  CREATE VIEW "newsletter_to_send" AS
   1.475    SELECT
   1.476 -    "newsletter"."id" AS "newsletter_id",
   1.477 -    "member"."id" AS "member_id"
   1.478 +    "member"."id" AS "recipient_id",
   1.479 +    "newsletter"."id" AS "newsletter_id"
   1.480    FROM "newsletter" CROSS JOIN "member"
   1.481    LEFT JOIN "privilege" ON
   1.482      "privilege"."member_id" = "member"."id" AND
   1.483 @@ -3146,19 +3132,19 @@
   1.484        PERFORM NULL FROM "member" WHERE "id" = "member_id_p" FOR UPDATE;
   1.485        FOR "result_row" IN
   1.486          SELECT * FROM "initiative_for_notification"
   1.487 -        WHERE "seen_by_member_id" = "member_id_p"
   1.488 +        WHERE "member_id" = "member_id_p"
   1.489        LOOP
   1.490          SELECT "id" INTO "last_draft_id_v" FROM "draft"
   1.491 -          WHERE "draft"."initiative_id" = "result_row"."id"
   1.492 +          WHERE "draft"."initiative_id" = "result_row"."initiative_id"
   1.493            ORDER BY "id" DESC LIMIT 1;
   1.494          SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
   1.495 -          WHERE "suggestion"."initiative_id" = "result_row"."id"
   1.496 +          WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"
   1.497            ORDER BY "id" DESC LIMIT 1;
   1.498          INSERT INTO "initiative_notification_sent"
   1.499            ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
   1.500            VALUES (
   1.501              "member_id_p",
   1.502 -            "result_row"."id",
   1.503 +            "result_row"."initiative_id",
   1.504              "last_draft_id_v",
   1.505              "last_suggestion_id_v" )
   1.506            ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
   1.507 @@ -4979,6 +4965,11 @@
   1.508          "notify_email_secret"          = NULL,
   1.509          "notify_email_secret_expiry"   = NULL,
   1.510          "notify_email_lock_expiry"     = NULL,
   1.511 +        "disable_notifications"        = NULL,
   1.512 +        "notification_counter"         = NULL,
   1.513 +        "notification_sample_size"     = NULL,
   1.514 +        "notification_dow"             = NULL,
   1.515 +        "notification_hour"            = NULL,
   1.516          "login_recovery_expiry"        = NULL,
   1.517          "password_reset_secret"        = NULL,
   1.518          "password_reset_secret_expiry" = NULL,
   1.519 @@ -5047,7 +5038,11 @@
   1.520          "notify_email_secret"          = NULL,
   1.521          "notify_email_secret_expiry"   = NULL,
   1.522          "notify_email_lock_expiry"     = NULL,
   1.523 -        "notify_level"                 = NULL,
   1.524 +        "disable_notifications"        = NULL,
   1.525 +        "notification_counter"         = NULL,
   1.526 +        "notification_sample_size"     = NULL,
   1.527 +        "notification_dow"             = NULL,
   1.528 +        "notification_hour"            = NULL,
   1.529          "login_recovery_expiry"        = NULL,
   1.530          "password_reset_secret"        = NULL,
   1.531          "password_reset_secret_expiry" = NULL,