# HG changeset patch
# User jbe
# Date 1459027463 -3600
# Node ID c39ff9540f4df54db896947305c319dc5e6bc436
# Parent  1e7e8b025346e5457b28e1b8110048f80cf8e9a1
Work on "issue_for_notification" table

diff -r 1e7e8b025346 -r c39ff9540f4d core.sql
--- a/core.sql	Sat Mar 26 02:13:42 2016 +0100
+++ b/core.sql	Sat Mar 26 22:24:23 2016 +0100
@@ -2393,7 +2393,7 @@
 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 "issues_for_notification" AS
+CREATE VIEW "issue_for_notification" AS
   SELECT
     "member"."id" AS "member_id",
     "issue"."id" AS "issue_id"
@@ -2405,11 +2405,21 @@
   LEFT JOIN "subscription"
     ON "subscription"."member_id" = "member"."id"
     AND "subscription"."unit_id" = "area"."unit_id"
+  LEFT JOIN "interest"
+    ON "interest"."member_id" = "member"."id"
+    AND "interest"."issue_id" = "issue"."id"
+  LEFT JOIN "ignored_area"
+    ON "ignored_area"."member_id" = "member"."id"
+    AND "ignored_area"."area_id" = "issue"."area_id"
   WHERE
     ( "privilege"."initiative_right" OR "privilege"."voting_right" OR
-      "subscription"."member_id" NOTNULL );  -- TODO: add further conditions
-
-COMMENT ON VIEW "issues_for_notification" IS 'Issues that are considered in notifications sent to the member';
+      "subscription"."member_id" NOTNULL ) AND
+    ( ( "issue"."state" IN ('admission', 'discussion', 'verification') AND
+        "interest"."member_id" NOTNULL ) OR
+      ( "issue"."state" IN ('discussion', 'verification') AND
+        "ignored_area"."member_id" ISNULL ) );  -- TODO: add certain issues in admission phase
+
+COMMENT ON VIEW "issue_for_notification" IS 'Issues that are considered in notifications sent to the member';