jbe@520: BEGIN;
jbe@520: 
jbe@520: CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@520:   SELECT * FROM (VALUES ('3.2.2', 3, 2, 2))
jbe@520:   AS "subquery"("string", "major", "minor", "revision");
jbe@520: 
jbe@523: UPDATE "member" SET
jbe@523:   "disable_notifications"    = TRUE,
jbe@523:   "notification_counter"     = DEFAULT,
jbe@523:   "notification_sample_size" = 0,
jbe@523:   "notification_dow"         = NULL,
jbe@523:   "notification_hour"        = NULL
jbe@523:   WHERE "last_login" ISNULL
jbe@523:   AND "login" ISNULL
jbe@523:   AND "authority_login" ISNULL
jbe@523:   AND "locked" = TRUE
jbe@523:   AND "active" = FALSE;
jbe@523: 
jbe@524: CREATE VIEW "member_eligible_to_be_notified" AS
jbe@524:   SELECT * FROM "member"
jbe@524:   WHERE "activated" NOTNULL AND "locked" = FALSE;
jbe@524: 
jbe@524: COMMENT ON VIEW "member_eligible_to_be_notified" IS 'Filtered "member" table containing only activated and non-locked members (used as helper view for "member_to_notify" and "newsletter_to_send")';
jbe@524: 
jbe@524: CREATE VIEW "member_to_notify" AS
jbe@524:   SELECT * FROM "member_eligible_to_be_notified"
jbe@524:   WHERE "disable_notifications" = FALSE;
jbe@524: 
jbe@524: COMMENT ON VIEW "member_to_notify" IS 'Filtered "member" table containing only members that are eligible to and wish to receive notifications; NOTE: "notify_email" may still be NULL and might need to be checked by frontend (this allows other means of messaging)';
jbe@524: 
jbe@520: CREATE OR REPLACE FUNCTION "featured_initiative"
jbe@520:   ( "recipient_id_p" "member"."id"%TYPE,
jbe@520:     "area_id_p"      "area"."id"%TYPE )
jbe@520:   RETURNS SETOF "initiative"."id"%TYPE
jbe@520:   LANGUAGE 'plpgsql' STABLE AS $$
jbe@520:     DECLARE
jbe@520:       "counter_v"         "member"."notification_counter"%TYPE;
jbe@520:       "sample_size_v"     "member"."notification_sample_size"%TYPE;
jbe@520:       "initiative_id_ary" INT4[];  --"initiative"."id"%TYPE[]
jbe@520:       "match_v"           BOOLEAN;
jbe@520:       "member_id_v"       "member"."id"%TYPE;
jbe@520:       "seed_v"            TEXT;
jbe@520:       "initiative_id_v"   "initiative"."id"%TYPE;
jbe@520:     BEGIN
jbe@520:       SELECT "notification_counter", "notification_sample_size"
jbe@520:         INTO "counter_v", "sample_size_v"
jbe@520:         FROM "member" WHERE "id" = "recipient_id_p";
jbe@520:       IF COALESCE("sample_size_v" <= 0, TRUE) THEN
jbe@520:         RETURN;
jbe@520:       END IF;
jbe@520:       "initiative_id_ary" := '{}';
jbe@520:       LOOP
jbe@520:         "match_v" := FALSE;
jbe@520:         FOR "member_id_v", "seed_v" IN
jbe@520:           SELECT * FROM (
jbe@520:             SELECT DISTINCT
jbe@520:               "supporter"."member_id",
jbe@520:               md5(
jbe@520:                 "recipient_id_p" || '-' ||
jbe@520:                 "counter_v"      || '-' ||
jbe@520:                 "area_id_p"      || '-' ||
jbe@520:                 "supporter"."member_id"
jbe@520:               ) AS "seed"
jbe@520:             FROM "supporter"
jbe@520:             JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
jbe@520:             JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@520:             WHERE "supporter"."member_id" != "recipient_id_p"
jbe@520:             AND "issue"."area_id" = "area_id_p"
jbe@520:             AND "issue"."state" IN ('admission', 'discussion', 'verification')
jbe@520:           ) AS "subquery"
jbe@520:           ORDER BY "seed"
jbe@520:         LOOP
jbe@520:           SELECT "initiative"."id" INTO "initiative_id_v"
jbe@520:             FROM "initiative"
jbe@520:             JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@520:             JOIN "area" ON "area"."id" = "issue"."area_id"
jbe@520:             JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
jbe@520:             LEFT JOIN "supporter" AS "self_support" ON
jbe@520:               "self_support"."initiative_id" = "initiative"."id" AND
jbe@520:               "self_support"."member_id" = "recipient_id_p"
jbe@520:             LEFT JOIN "privilege" ON
jbe@520:               "privilege"."member_id" = "recipient_id_p" AND
jbe@520:               "privilege"."unit_id" = "area"."unit_id" AND
jbe@520:               "privilege"."voting_right" = TRUE
jbe@520:             LEFT JOIN "subscription" ON
jbe@520:               "subscription"."member_id" = "recipient_id_p" AND
jbe@520:               "subscription"."unit_id" = "area"."unit_id"
jbe@520:             LEFT JOIN "ignored_initiative" ON
jbe@520:               "ignored_initiative"."member_id" = "recipient_id_p" AND
jbe@520:               "ignored_initiative"."initiative_id" = "initiative"."id"
jbe@520:             WHERE "supporter"."member_id" = "member_id_v"
jbe@520:             AND "issue"."area_id" = "area_id_p"
jbe@520:             AND "issue"."state" IN ('admission', 'discussion', 'verification')
jbe@520:             AND "initiative"."revoked" ISNULL
jbe@520:             AND "self_support"."member_id" ISNULL
jbe@520:             AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
jbe@520:             AND (
jbe@520:               "privilege"."member_id" NOTNULL OR
jbe@520:               "subscription"."member_id" NOTNULL )
jbe@520:             AND "ignored_initiative"."member_id" ISNULL
jbe@520:             AND NOT EXISTS (
jbe@520:               SELECT NULL FROM "draft"
jbe@520:               JOIN "ignored_member" ON
jbe@520:                 "ignored_member"."member_id" = "recipient_id_p" AND
jbe@520:                 "ignored_member"."other_member_id" = "draft"."author_id"
jbe@520:               WHERE "draft"."initiative_id" = "initiative"."id"
jbe@520:             )
jbe@520:             ORDER BY md5("seed_v" || '-' || "initiative"."id")
jbe@520:             LIMIT 1;
jbe@520:           IF FOUND THEN
jbe@520:             "match_v" := TRUE;
jbe@520:             RETURN NEXT "initiative_id_v";
jbe@520:             IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN
jbe@520:               RETURN;
jbe@520:             END IF;
jbe@520:             "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";
jbe@520:           END IF;
jbe@520:         END LOOP;
jbe@520:         EXIT WHEN NOT "match_v";
jbe@520:       END LOOP;
jbe@520:       RETURN;
jbe@520:     END;
jbe@520:   $$;
jbe@520: 
jbe@521: CREATE OR REPLACE VIEW "scheduled_notification_to_send" AS
jbe@521:   SELECT * FROM (
jbe@521:     SELECT
jbe@521:       "id" AS "recipient_id",
jbe@521:       now() - CASE WHEN "notification_dow" ISNULL THEN
jbe@521:         ( "notification_sent"::DATE + CASE
jbe@521:           WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
jbe@521:           THEN 0 ELSE 1 END
jbe@521:         )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
jbe@521:       ELSE
jbe@521:         ( "notification_sent"::DATE +
jbe@521:           ( 7 + "notification_dow" -
jbe@521:             EXTRACT(DOW FROM
jbe@521:               ( "notification_sent"::DATE + CASE
jbe@521:                 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
jbe@521:                 THEN 0 ELSE 1 END
jbe@521:               )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
jbe@521:             )::INTEGER
jbe@521:           ) % 7 +
jbe@521:           CASE
jbe@521:             WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
jbe@521:             THEN 0 ELSE 1
jbe@521:           END
jbe@521:         )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
jbe@521:       END AS "pending"
jbe@521:     FROM (
jbe@521:       SELECT
jbe@521:         "id",
jbe@521:         COALESCE("notification_sent", "activated") AS "notification_sent",
jbe@521:         "notification_dow",
jbe@521:         "notification_hour"
jbe@524:       FROM "member_to_notify"
jbe@524:       WHERE "notification_hour" NOTNULL
jbe@521:     ) AS "subquery1"
jbe@521:   ) AS "subquery2"
jbe@521:   WHERE "pending" > '0'::INTERVAL;
jbe@521: 
jbe@524: CREATE OR REPLACE VIEW "newsletter_to_send" AS
jbe@524:   SELECT
jbe@524:     "member"."id" AS "recipient_id",
jbe@524:     "newsletter"."id" AS "newsletter_id",
jbe@524:     "newsletter"."published"
jbe@524:   FROM "newsletter" CROSS JOIN "member_eligible_to_be_notified" AS "member"
jbe@524:   LEFT JOIN "privilege" ON
jbe@524:     "privilege"."member_id" = "member"."id" AND
jbe@524:     "privilege"."unit_id" = "newsletter"."unit_id" AND
jbe@524:     "privilege"."voting_right" = TRUE
jbe@524:   LEFT JOIN "subscription" ON
jbe@524:     "subscription"."member_id" = "member"."id" AND
jbe@524:     "subscription"."unit_id" = "newsletter"."unit_id"
jbe@524:   WHERE "newsletter"."published" <= now()
jbe@524:   AND "newsletter"."sent" ISNULL
jbe@524:   AND (
jbe@524:     "member"."disable_notifications" = FALSE OR
jbe@524:     "newsletter"."include_all_members" = TRUE )
jbe@524:   AND (
jbe@524:     "newsletter"."unit_id" ISNULL OR
jbe@524:     "privilege"."member_id" NOTNULL OR
jbe@524:     "subscription"."member_id" NOTNULL );
jbe@524: 
jbe@522: CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
jbe@522:   RETURNS VOID
jbe@522:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@522:     BEGIN
jbe@522:       UPDATE "member" SET
jbe@522:         "last_login"                   = NULL,
jbe@522:         "last_delegation_check"        = NULL,
jbe@522:         "login"                        = NULL,
jbe@522:         "password"                     = NULL,
jbe@522:         "authority"                    = NULL,
jbe@522:         "authority_uid"                = NULL,
jbe@522:         "authority_login"              = NULL,
jbe@522:         "locked"                       = TRUE,
jbe@522:         "active"                       = FALSE,
jbe@522:         "notify_email"                 = NULL,
jbe@522:         "notify_email_unconfirmed"     = NULL,
jbe@522:         "notify_email_secret"          = NULL,
jbe@522:         "notify_email_secret_expiry"   = NULL,
jbe@522:         "notify_email_lock_expiry"     = NULL,
jbe@522:         "disable_notifications"        = TRUE,
jbe@522:         "notification_counter"         = DEFAULT,
jbe@522:         "notification_sample_size"     = 0,
jbe@522:         "notification_dow"             = NULL,
jbe@522:         "notification_hour"            = NULL,
jbe@522:         "login_recovery_expiry"        = NULL,
jbe@522:         "password_reset_secret"        = NULL,
jbe@522:         "password_reset_secret_expiry" = NULL,
jbe@522:         "organizational_unit"          = NULL,
jbe@522:         "internal_posts"               = NULL,
jbe@522:         "realname"                     = NULL,
jbe@522:         "birthday"                     = NULL,
jbe@522:         "address"                      = NULL,
jbe@522:         "email"                        = NULL,
jbe@522:         "xmpp_address"                 = NULL,
jbe@522:         "website"                      = NULL,
jbe@522:         "phone"                        = NULL,
jbe@522:         "mobile_phone"                 = NULL,
jbe@522:         "profession"                   = NULL,
jbe@522:         "external_memberships"         = NULL,
jbe@522:         "external_posts"               = NULL,
jbe@522:         "statement"                    = NULL
jbe@522:         WHERE "id" = "member_id_p";
jbe@522:       -- "text_search_data" is updated by triggers
jbe@522:       DELETE FROM "setting"            WHERE "member_id" = "member_id_p";
jbe@522:       DELETE FROM "setting_map"        WHERE "member_id" = "member_id_p";
jbe@522:       DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
jbe@522:       DELETE FROM "member_image"       WHERE "member_id" = "member_id_p";
jbe@522:       DELETE FROM "contact"            WHERE "member_id" = "member_id_p";
jbe@522:       DELETE FROM "ignored_member"     WHERE "member_id" = "member_id_p";
jbe@522:       DELETE FROM "session"            WHERE "member_id" = "member_id_p";
jbe@522:       DELETE FROM "area_setting"       WHERE "member_id" = "member_id_p";
jbe@522:       DELETE FROM "issue_setting"      WHERE "member_id" = "member_id_p";
jbe@522:       DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
jbe@522:       DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
jbe@522:       DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
jbe@522:       DELETE FROM "membership"         WHERE "member_id" = "member_id_p";
jbe@522:       DELETE FROM "delegation"         WHERE "truster_id" = "member_id_p";
jbe@522:       DELETE FROM "non_voter"          WHERE "member_id" = "member_id_p";
jbe@522:       DELETE FROM "direct_voter" USING "issue"
jbe@522:         WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@522:         AND "issue"."closed" ISNULL
jbe@522:         AND "member_id" = "member_id_p";
jbe@522:       RETURN;
jbe@522:     END;
jbe@522:   $$;
jbe@522: 
jbe@522: CREATE OR REPLACE FUNCTION "delete_private_data"()
jbe@522:   RETURNS VOID
jbe@522:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@522:     BEGIN
jbe@522:       DELETE FROM "temporary_transaction_data";
jbe@522:       DELETE FROM "member" WHERE "activated" ISNULL;
jbe@522:       UPDATE "member" SET
jbe@522:         "invite_code"                  = NULL,
jbe@522:         "invite_code_expiry"           = NULL,
jbe@522:         "admin_comment"                = NULL,
jbe@522:         "last_login"                   = NULL,
jbe@522:         "last_delegation_check"        = NULL,
jbe@522:         "login"                        = NULL,
jbe@522:         "password"                     = NULL,
jbe@522:         "authority"                    = NULL,
jbe@522:         "authority_uid"                = NULL,
jbe@522:         "authority_login"              = NULL,
jbe@522:         "lang"                         = NULL,
jbe@522:         "notify_email"                 = NULL,
jbe@522:         "notify_email_unconfirmed"     = NULL,
jbe@522:         "notify_email_secret"          = NULL,
jbe@522:         "notify_email_secret_expiry"   = NULL,
jbe@522:         "notify_email_lock_expiry"     = NULL,
jbe@522:         "disable_notifications"        = TRUE,
jbe@522:         "notification_counter"         = DEFAULT,
jbe@522:         "notification_sample_size"     = 0,
jbe@522:         "notification_dow"             = NULL,
jbe@522:         "notification_hour"            = NULL,
jbe@522:         "login_recovery_expiry"        = NULL,
jbe@522:         "password_reset_secret"        = NULL,
jbe@522:         "password_reset_secret_expiry" = NULL,
jbe@522:         "organizational_unit"          = NULL,
jbe@522:         "internal_posts"               = NULL,
jbe@522:         "realname"                     = NULL,
jbe@522:         "birthday"                     = NULL,
jbe@522:         "address"                      = NULL,
jbe@522:         "email"                        = NULL,
jbe@522:         "xmpp_address"                 = NULL,
jbe@522:         "website"                      = NULL,
jbe@522:         "phone"                        = NULL,
jbe@522:         "mobile_phone"                 = NULL,
jbe@522:         "profession"                   = NULL,
jbe@522:         "external_memberships"         = NULL,
jbe@522:         "external_posts"               = NULL,
jbe@522:         "formatting_engine"            = NULL,
jbe@522:         "statement"                    = NULL;
jbe@522:       -- "text_search_data" is updated by triggers
jbe@522:       DELETE FROM "setting";
jbe@522:       DELETE FROM "setting_map";
jbe@522:       DELETE FROM "member_relation_setting";
jbe@522:       DELETE FROM "member_image";
jbe@522:       DELETE FROM "contact";
jbe@522:       DELETE FROM "ignored_member";
jbe@522:       DELETE FROM "session";
jbe@522:       DELETE FROM "area_setting";
jbe@522:       DELETE FROM "issue_setting";
jbe@522:       DELETE FROM "ignored_initiative";
jbe@522:       DELETE FROM "initiative_setting";
jbe@522:       DELETE FROM "suggestion_setting";
jbe@522:       DELETE FROM "non_voter";
jbe@522:       DELETE FROM "direct_voter" USING "issue"
jbe@522:         WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@522:         AND "issue"."closed" ISNULL;
jbe@522:       RETURN;
jbe@522:     END;
jbe@522:   $$;
jbe@522: 
jbe@520: COMMIT;