# HG changeset patch
# User jbe
# Date 1458954822 -3600
# Node ID 1e7e8b025346e5457b28e1b8110048f80cf8e9a1
# Parent  c6344e3a3c4a70dc65f526c5d62dc56eb724ca02
Stub VIEW that selects issues which are to be included in notifications

diff -r c6344e3a3c4a -r 1e7e8b025346 core.sql
--- a/core.sql	Sat Mar 26 01:34:36 2016 +0100
+++ b/core.sql	Sat Mar 26 02:13:42 2016 +0100
@@ -1276,7 +1276,7 @@
 COMMENT ON INDEX "notification_sent_singleton_idx" IS 'This index ensures that "notification_sent" only contains one row maximum.';
 
 
-CREATE TABLE "advertisement" (
+CREATE TABLE "advertisement" (  -- TODO: two tables: one for initiatives, one for suggestions
         PRIMARY KEY ("time_serial", "initiative_id", "member_id"),
         "time_serial"           SERIAL8,
         "initiative_id"         INT4            NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
@@ -2393,6 +2393,25 @@
 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
+  SELECT
+    "member"."id" AS "member_id",
+    "issue"."id" AS "issue_id"
+  FROM "member" CROSS JOIN "issue"
+  JOIN "area" ON "area"."id" = "issue"."area_id"
+  LEFT JOIN "privilege"
+    ON "privilege"."member_id" = "member"."id"
+    AND "privilege"."unit_id" = "area"."unit_id"
+  LEFT JOIN "subscription"
+    ON "subscription"."member_id" = "member"."id"
+    AND "subscription"."unit_id" = "area"."unit_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';
+
+
 
 ------------------------------------------------------
 -- Row set returning function for delegation chains --