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@478: ALTER TABLE "member" ADD COLUMN "disable_notifications" BOOLEAN NOT NULL DEFAULT FALSE;
jbe@478: ALTER TABLE "member" ADD COLUMN "notification_counter" INT4 NOT NULL DEFAULT 0;
jbe@478: ALTER TABLE "member" ADD COLUMN "sample_size" INT4 NOT NULL DEFAULT 3;
jbe@478: ALTER TABLE "member" ADD COLUMN "last_notified_suggestion_id" INT8;
jbe@478: 
jbe@478: UPDATE "member" SET "disable_notifications" = TRUE WHERE "notify_level" = 'none';
jbe@478:  
jbe@478: CREATE TABLE "subscription_time" (
jbe@478:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@478:         "day_of_week"           INT2            CONSTRAINT "day_of_week_range" CHECK ("day_of_week" BETWEEN 0 AND 6),
jbe@478:         "time_of_day"           TIME            NOT NULL );
jbe@478: CREATE UNIQUE INDEX "subscription_time_all_days_of_week_time_of_day_idx" ON "subscription_time" ("time_of_day", "member_id") WHERE ("day_of_week" ISNULL);
jbe@478: CREATE UNIQUE INDEX "subscription_time_day_of_week_time_of_day_idx" ON "subscription_time" ("day_of_week", "time_of_day", "member_id");
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@478:  
jbe@478: DROP VIEW "selected_event_seen_by_member";
jbe@478: 
jbe@478: CREATE VIEW "updated_initiative" AS
jbe@478:   SELECT
jbe@478:     "member"."id" AS "seen_by_member_id",
jbe@478:     TRUE AS "supported",
jbe@478:     EXISTS (
jbe@478:       SELECT NULL FROM "draft"
jbe@478:       WHERE "draft"."initiative_id" = "initiative"."id"
jbe@478:       AND "draft"."id" > "supporter"."draft_id"
jbe@478:     ) AS "new_draft",
jbe@478:     ( SELECT count(1) FROM "suggestion"
jbe@478:       WHERE "suggestion"."initiative_id" = "initiative"."id"
jbe@478:       AND COALESCE(
jbe@478:         "suggestion"."id" > "member"."last_notified_suggestion_id",
jbe@478:         TRUE
jbe@478:       )
jbe@478:     ) AS "new_suggestion_count",
jbe@478:     FALSE AS "featured",
jbe@478:     NOT EXISTS (
jbe@478:       SELECT NULL FROM "initiative" AS "better_initiative"
jbe@478:       WHERE
jbe@484:         "better_initiative"."issue_id" = "initiative"."issue_id"
jbe@484:       AND
jbe@484:         ( COALESCE("better_initiative"."harmonic_weight", -1),
jbe@484:           -"better_initiative"."id" ) >
jbe@484:         ( COALESCE("initiative"."harmonic_weight", -1),
jbe@484:           -"better_initiative"."id" )
jbe@478:     ) AS "leading",
jbe@478:     "initiative".*
jbe@478:   FROM "member" CROSS JOIN "initiative"
jbe@478:   JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@478:   JOIN "supporter" ON
jbe@478:     "supporter"."member_id" = "member"."id" AND
jbe@478:     "supporter"."initiative_id" = "initiative"."id"
jbe@480:   WHERE "issue"."state" IN ('admission', 'discussion')
jbe@478:   AND (
jbe@478:     EXISTS (
jbe@478:       SELECT NULL FROM "draft"
jbe@478:       WHERE "draft"."initiative_id" = "initiative"."id"
jbe@478:       AND "draft"."id" > "supporter"."draft_id"
jbe@478:     ) OR EXISTS (
jbe@478:       SELECT NULL FROM "suggestion"
jbe@478:       WHERE "suggestion"."initiative_id" = "initiative"."id"
jbe@478:       AND COALESCE(
jbe@478:         "suggestion"."id" > "member"."last_notified_suggestion_id",
jbe@478:         TRUE
jbe@478:       )
jbe@478:     )
jbe@478:   );
jbe@478: 
jbe@478: CREATE FUNCTION "featured_initiative"
jbe@478:   ( "member_id_p" "member"."id"%TYPE,
jbe@478:     "area_id_p"   "area"."id"%TYPE )
jbe@478:   RETURNS SETOF "initiative"
jbe@478:   LANGUAGE 'plpgsql' STABLE AS $$
jbe@478:     DECLARE
jbe@482:       "member_row"        "member"%ROWTYPE;
jbe@478:       "member_id_v"       "member"."id"%TYPE;
jbe@478:       "seed_v"            TEXT;
jbe@478:       "result_row"        "initiative"%ROWTYPE;
jbe@478:       "match_v"           BOOLEAN;
jbe@478:       "initiative_id_ary" INT4[];  --"initiative"."id"%TYPE[]
jbe@478:     BEGIN
jbe@482:       SELECT INTO "member_row" * FROM "member" WHERE "id" = "member_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@482:               md5("member_id_p" || '-' || "member_row"."notification_counter" || '-' || "area_id_p" || '-' || "supporter"."member_id") AS "seed"
jbe@478:             FROM "supporter"
jbe@478:             JOIN "member" ON "member"."id" = "supporter"."member_id"
jbe@478:             JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
jbe@478:             JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@478:             WHERE "supporter"."member_id" != "member_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@478:           SELECT "initiative".* INTO "result_row"
jbe@478:             FROM "initiative"
jbe@478:             JOIN "issue" ON "issue"."id" = "initiative"."issue_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@478:               "self_support"."member_id" = "member_id_p"
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@478:             AND "self_support"."member_id" ISNULL
jbe@478:             AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
jbe@478:             ORDER BY md5("seed_v" || '-' || "initiative"."id")
jbe@478:             LIMIT 1;
jbe@478:           IF FOUND THEN
jbe@478:             "match_v" := TRUE;
jbe@478:             "initiative_id_ary" := "initiative_id_ary" || "result_row"."id";
jbe@478:             RETURN NEXT "result_row";
jbe@483:             IF array_length("initiative_id_ary", 1) >= "member_row"."sample_size" THEN
jbe@478:               RETURN;
jbe@478:             END IF;
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 * FROM "updated_initiative"
jbe@478:   UNION ALL
jbe@478:   SELECT
jbe@478:     "member"."id" AS "seen_by_member_id",
jbe@478:     FALSE AS "supported",
jbe@478:     NULL::BOOLEAN AS "new_draft",
jbe@478:     NULL::INTEGER AS "new_suggestion_count",
jbe@478:     TRUE AS "featured",
jbe@478:     NOT EXISTS (
jbe@478:       SELECT NULL FROM "initiative" AS "better_initiative"
jbe@478:       WHERE
jbe@484:         "better_initiative"."issue_id" = "initiative"."issue_id"
jbe@484:       AND
jbe@484:         ( COALESCE("better_initiative"."harmonic_weight", -1),
jbe@484:           -"better_initiative"."id" ) >
jbe@484:         ( COALESCE("initiative"."harmonic_weight", -1),
jbe@484:           -"better_initiative"."id" )
jbe@478:     ) AS "leading",
jbe@478:     "initiative".*
jbe@478:   FROM "member" CROSS JOIN "area"
jbe@478:   CROSS JOIN LATERAL
jbe@478:     "featured_initiative"("member"."id", "area"."id") AS "initiative";
jbe@478: 
jbe@478: CREATE VIEW "leading_complement_initiative" AS
jbe@478:   SELECT * FROM (
jbe@478:     SELECT DISTINCT ON ("seen_by_member_id", "initiative"."issue_id")
jbe@478:       "updated_or_featured_initiative"."seen_by_member_id",
jbe@478:       FALSE AS "supported",
jbe@478:       NULL::BOOLEAN AS "new_draft",
jbe@478:       NULL::INTEGER AS "new_suggestion_count",
jbe@478:       FALSE AS "featured",
jbe@478:       TRUE AS "leading",
jbe@478:       "initiative".*
jbe@478:     FROM "updated_or_featured_initiative"
jbe@478:     JOIN "initiative"
jbe@478:     ON "updated_or_featured_initiative"."issue_id" = "initiative"."issue_id"
jbe@478:     ORDER BY
jbe@478:       "seen_by_member_id",
jbe@478:       "initiative"."issue_id",
jbe@478:       "initiative"."harmonic_weight" 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@478:     WHERE "other"."seen_by_member_id" = "subquery"."seen_by_member_id"
jbe@478:     AND "other"."id" = "subquery"."id"
jbe@478:   );
jbe@478: 
jbe@478: CREATE VIEW "initiative_for_notification" AS
jbe@478:   SELECT * FROM "updated_or_featured_initiative"
jbe@478:   UNION ALL
jbe@478:   SELECT * FROM "leading_complement_initiative";
jbe@478: 
jbe@478: COMMIT;