# HG changeset patch
# User jbe
# Date 1459280774 -7200
# Node ID 234c9760589dd97e7218bfd08bb92d0cfdbc1de7
# Parent  0fa0d2daa54a6007470da4832b5a1991b362d36d
New view "updated_initiative"

diff -r 0fa0d2daa54a -r 234c9760589d core.sql
--- a/core.sql	Mon Mar 28 14:41:28 2016 +0200
+++ b/core.sql	Tue Mar 29 21:46:14 2016 +0200
@@ -120,6 +120,7 @@
         "notify_email_secret_expiry"   TIMESTAMPTZ,
         "notify_email_lock_expiry"     TIMESTAMPTZ,
         "disable_notifications" BOOLEAN         NOT NULL DEFAULT FALSE,
+        "last_notified_suggestion_id" INT8,
         "login_recovery_expiry"        TIMESTAMPTZ,
         "password_reset_secret"        TEXT     UNIQUE,
         "password_reset_secret_expiry" TIMESTAMPTZ,
@@ -2380,6 +2381,32 @@
 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"';
 
 
+CREATE VIEW "updated_initiative" AS
+  SELECT
+    "member"."id" AS "seen_by_member_id",
+    "initiative".*
+  FROM "member" CROSS JOIN "initiative"
+  JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
+  JOIN "supporter" ON
+    "supporter"."member_id" = "member"."id" AND
+    "supporter"."initiative_id" = "initiative"."id"
+  WHERE "issue"."half_frozen" ISNULL AND "issue"."closed" ISNULL
+  AND (
+    EXISTS (
+      SELECT NULL FROM "draft"
+      WHERE "draft"."initiative_id" = "initiative"."id"
+      AND "draft"."id" > "supporter"."draft_id"
+    ) OR EXISTS (
+      SELECT NULL FROM "suggestion"
+      WHERE "suggestion"."initiative_id" = "initiative"."id"
+      AND COALESCE(
+        "suggestion"."id" > "member"."last_notified_suggestion_id",
+        TRUE
+      )
+    )
+  );
+
+
 
 ------------------------------------------------------
 -- Row set returning function for delegation chains --