jbe@619: CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@619:   SELECT * FROM (VALUES ('4.2.0-incomplete-update', 4, 2, -1))
jbe@619:   AS "subquery"("string", "major", "minor", "revision");
jbe@619: 
jbe@619: ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'posting_created';
jbe@619: 
jbe@619: BEGIN;
jbe@619: 
jbe@619: CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@619:   SELECT * FROM (VALUES ('4.2.1', 4, 2, 1))
jbe@619:   AS "subquery"("string", "major", "minor", "revision");
jbe@619: 
jbe@619: DROP VIEW "newsletter_to_send";
jbe@619: DROP VIEW "scheduled_notification_to_send";
jbe@619: DROP VIEW "member_contingent_left";
jbe@619: DROP VIEW "member_contingent";
jbe@619: DROP VIEW "expired_snapshot";
jbe@619: DROP VIEW "current_draft";
jbe@619: DROP VIEW "opening_draft";
jbe@619: DROP VIEW "area_with_unaccepted_issues";
jbe@619: DROP VIEW "member_to_notify";
jbe@619: DROP VIEW "member_eligible_to_be_notified";
jbe@619: 
jbe@619: DROP FUNCTION "text_search_query" (TEXT);
jbe@619: 
jbe@619: ALTER TABLE "system_setting" DROP COLUMN "snapshot_retention";
jbe@619: 
jbe@619: CREATE TABLE "file" (
jbe@619:         "id"                    SERIAL8         PRIMARY KEY,
jbe@619:         UNIQUE ("content_type", "hash"),
jbe@619:         "content_type"          TEXT            NOT NULL,
jbe@619:         "hash"                  TEXT            NOT NULL,
jbe@619:         "data"                  BYTEA           NOT NULL,
jbe@619:         "preview_content_type"  TEXT,
jbe@619:         "preview_data"          BYTEA );
jbe@619: 
jbe@619: COMMENT ON TABLE "file" IS 'Table holding file contents for draft attachments';
jbe@619: 
jbe@619: COMMENT ON COLUMN "file"."content_type"         IS 'Content type of "data"';
jbe@619: COMMENT ON COLUMN "file"."hash"                 IS 'Hash of "data" to avoid storing duplicates where content-type and data is identical';
jbe@619: COMMENT ON COLUMN "file"."data"                 IS 'Binary content';
jbe@619: COMMENT ON COLUMN "file"."preview_content_type" IS 'Content type of "preview_data"';
jbe@619: COMMENT ON COLUMN "file"."preview_data"         IS 'Preview (e.g. preview image)';
jbe@619: 
jbe@619: ALTER TABLE "member" DROP COLUMN "text_search_data";
jbe@619: DROP TRIGGER "update_text_search_data" ON "member";
jbe@619: 
jbe@619: CREATE INDEX "member_useterms_member_id_contract_identifier" ON "member_useterms" ("member_id", "contract_identifier");
jbe@619: 
jbe@619: ALTER TABLE "member_profile" DROP COLUMN "text_search_data";
jbe@619: DROP TRIGGER "update_text_search_data" ON "member_profile";
jbe@619: 
jbe@619: ALTER TABLE "contact" ADD COLUMN "following" BOOLEAN NOT NULL DEFAULT TRUE;
jbe@619: 
jbe@619: COMMENT ON COLUMN "contact"."following" IS 'TRUE = actions of contact are shown in personal timeline';
jbe@619: 
jbe@619: ALTER TABLE "unit" DROP COLUMN "text_search_data";
jbe@619: DROP TRIGGER "update_text_search_data" ON "unit";
jbe@619: 
jbe@619: ALTER TABLE "area" DROP COLUMN "text_search_data";
jbe@619: DROP TRIGGER "update_text_search_data" ON "area";
jbe@619: 
jbe@619: DROP INDEX "issue_accepted_idx";
jbe@619: DROP INDEX "issue_half_frozen_idx";
jbe@619: DROP INDEX "issue_fully_frozen_idx";
jbe@619: ALTER INDEX "issue_created_idx_open" RENAME TO "issue_open_created_idx";
jbe@619: DROP INDEX "issue_closed_idx_canceled";
jbe@619: ALTER INDEX "issue_latest_snapshot_id" RENAME TO "issue_latest_snapshot_id_idx";
jbe@619: ALTER INDEX "issue_admission_snapshot_id" RENAME TO "issue_admission_snapshot_id_idx";
jbe@619: ALTER INDEX "issue_half_freeze_snapshot_id" RENAME TO "issue_half_freeze_snapshot_id_idx";
jbe@619: ALTER INDEX "issue_full_freeze_snapshot_id" RENAME TO "issue_full_freeze_snapshot_id_idx";
jbe@619: 
jbe@619: ALTER TABLE "initiative" ADD COLUMN "content" TEXT;
jbe@619: ALTER TABLE "initiative" DROP COLUMN "text_search_data";
jbe@619: ALTER TABLE "initiative" DROP COLUMN "draft_text_search_data";
jbe@619: DROP INDEX "initiative_revoked_idx";
jbe@619: DROP TRIGGER "update_text_search_data" ON "initiative";
jbe@619: 
jbe@619: COMMENT ON COLUMN "initiative"."content" IS 'Initiative text (automatically copied from most recent draft)';
jbe@619: 
jbe@619: ALTER TABLE "battle" DROP CONSTRAINT "initiative_ids_not_equal";
jbe@619: ALTER TABLE "battle" ADD CONSTRAINT "initiative_ids_not_equal" CHECK (
jbe@619:   "winning_initiative_id" != "losing_initiative_id" AND
jbe@619:   ("winning_initiative_id" NOTNULL OR "losing_initiative_id" NOTNULL) );
jbe@619: 
jbe@619: ALTER TABLE "draft" DROP COLUMN "text_search_data";
jbe@619: DROP TRIGGER "update_text_search_data" ON "draft";
jbe@619: 
jbe@619: CREATE TABLE "draft_attachment" (
jbe@619:         "id"                    SERIAL8         PRIMARY KEY,
jbe@619:         "draft_id"              INT8            REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@619:         "file_id"               INT8            REFERENCES "file" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@619:         "title"                 TEXT,
jbe@619:         "description"           TEXT );
jbe@619: 
jbe@619: COMMENT ON TABLE "draft_attachment" IS 'Binary attachments for a draft (images, PDF file, etc.); Implicitly ordered through ''id'' column';
jbe@619: 
jbe@619: ALTER TABLE "suggestion" DROP COLUMN "text_search_data";
jbe@619: DROP TRIGGER "update_text_search_data" ON "suggestion";
jbe@619: 
jbe@619: ALTER TABLE "direct_voter" DROP COLUMN "text_search_data";
jbe@619: DROP TRIGGER "update_text_search_data" ON "direct_voter";
jbe@619: 
jbe@619: CREATE TABLE "posting" (
jbe@619:         UNIQUE ("author_id", "id"),  -- index needed for foreign-key on table "posting_lexeme"
jbe@619:         "id"                    SERIAL8         PRIMARY KEY,
jbe@619:         "author_id"             INT4            NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@619:         "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
jbe@619:         "message"               TEXT            NOT NULL,
jbe@619:         "unit_id"               INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@619:         "area_id"               INT4,
jbe@619:         FOREIGN KEY ("unit_id", "area_id") REFERENCES "area" ("unit_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@619:         "policy_id"             INT4            REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@619:         "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@619:         FOREIGN KEY ("area_id", "issue_id") REFERENCES "issue" ("area_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@619:         FOREIGN KEY ("policy_id", "issue_id") REFERENCES "issue" ("policy_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@619:         "initiative_id"         INT4,
jbe@619:         "suggestion_id"         INT8,
jbe@619:         -- NOTE: no referential integrity for suggestions because those are
jbe@619:         --       actually deleted
jbe@619:         -- FOREIGN KEY ("initiative_id", "suggestion_id")
jbe@619:         --   REFERENCES "suggestion" ("initiative_id", "id")
jbe@619:         --   ON DELETE CASCADE ON UPDATE CASCADE,
jbe@619:         CONSTRAINT "area_requires_unit" CHECK (
jbe@619:           "area_id" ISNULL OR "unit_id" NOTNULL ),
jbe@619:         CONSTRAINT "policy_set_when_issue_set" CHECK (
jbe@619:           ("policy_id" NOTNULL) = ("issue_id" NOTNULL) ),
jbe@619:         CONSTRAINT "issue_requires_area" CHECK (
jbe@619:           "issue_id" ISNULL OR "area_id" NOTNULL ),
jbe@619:         CONSTRAINT "initiative_requires_issue" CHECK (
jbe@619:           "initiative_id" ISNULL OR "issue_id" NOTNULL ),
jbe@619:         CONSTRAINT "suggestion_requires_initiative" CHECK (
jbe@619:           "suggestion_id" ISNULL OR "initiative_id" NOTNULL ) );
jbe@619: COMMENT ON TABLE "posting" IS 'Text postings of members; a text posting may optionally be associated to a unit, area, policy, issue, initiative, or suggestion';
jbe@619: 
jbe@619: CREATE TABLE "posting_lexeme" (
jbe@619:         PRIMARY KEY ("posting_id", "lexeme"),
jbe@619:         FOREIGN KEY ("posting_id", "author_id") REFERENCES "posting" ("id", "author_id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@619:         "posting_id"            INT8,
jbe@619:         "lexeme"                TEXT,
jbe@619:         "author_id"             INT4 );
jbe@619: 
jbe@619: COMMENT ON TABLE "posting_lexeme" IS 'Helper table to allow searches for hashtags.';
jbe@619: 
jbe@619: ALTER TABLE "event" ADD COLUMN "posting_id" INT8 REFERENCES "posting" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
jbe@619: ALTER TABLE "event" DROP CONSTRAINT "constr_for_issue_state_changed";
jbe@619: ALTER TABLE "event" DROP CONSTRAINT "constr_for_initiative_creation_or_revocation_or_new_draft";
jbe@619: ALTER TABLE "event" DROP CONSTRAINT "constr_for_suggestion_creation";
jbe@619: ALTER TABLE "event" DROP CONSTRAINT "constr_for_suggestion_removal";
jbe@619: ALTER TABLE "event" DROP CONSTRAINT "constr_for_value_less_member_event";
jbe@619: ALTER TABLE "event" DROP CONSTRAINT "constr_for_member_active";
jbe@619: ALTER TABLE "event" DROP CONSTRAINT "constr_for_member_name_updated";
jbe@619: ALTER TABLE "event" DROP CONSTRAINT "constr_for_interest";
jbe@619: ALTER TABLE "event" DROP CONSTRAINT "constr_for_initiator";
jbe@619: ALTER TABLE "event" DROP CONSTRAINT "constr_for_support";
jbe@619: ALTER TABLE "event" DROP CONSTRAINT "constr_for_support_updated";
jbe@619: ALTER TABLE "event" DROP CONSTRAINT "constr_for_suggestion_rated";
jbe@619: ALTER TABLE "event" DROP CONSTRAINT "constr_for_delegation";
jbe@619: ALTER TABLE "event" DROP CONSTRAINT "constr_for_contact";
jbe@619: ALTER TABLE "event" ADD
jbe@619:         CONSTRAINT "constr_for_issue_state_changed" CHECK (
jbe@619:           "event" != 'issue_state_changed' OR (
jbe@619:             "posting_id"      ISNULL  AND
jbe@619:             "member_id"       ISNULL  AND
jbe@619:             "other_member_id" ISNULL  AND
jbe@619:             "scope"           ISNULL  AND
jbe@619:             "unit_id"         NOTNULL AND
jbe@619:             "area_id"         NOTNULL AND
jbe@619:             "policy_id"       NOTNULL AND
jbe@619:             "issue_id"        NOTNULL AND
jbe@619:             "state"           NOTNULL AND
jbe@619:             "initiative_id"   ISNULL  AND
jbe@619:             "draft_id"        ISNULL  AND
jbe@619:             "suggestion_id"   ISNULL  AND
jbe@619:             "boolean_value"   ISNULL  AND
jbe@619:             "numeric_value"   ISNULL  AND
jbe@619:             "text_value"      ISNULL  AND
jbe@619:             "old_text_value"  ISNULL ));
jbe@619: ALTER TABLE "event" ADD
jbe@619:         CONSTRAINT "constr_for_initiative_creation_or_revocation_or_new_draft" CHECK (
jbe@619:           "event" NOT IN (
jbe@619:             'initiative_created_in_new_issue',
jbe@619:             'initiative_created_in_existing_issue',
jbe@619:             'initiative_revoked',
jbe@619:             'new_draft_created'
jbe@619:           ) OR (
jbe@619:             "posting_id"      ISNULL  AND
jbe@619:             "member_id"       NOTNULL AND
jbe@619:             "other_member_id" ISNULL  AND
jbe@619:             "scope"           ISNULL  AND
jbe@619:             "unit_id"         NOTNULL AND
jbe@619:             "area_id"         NOTNULL AND
jbe@619:             "policy_id"       NOTNULL AND
jbe@619:             "issue_id"        NOTNULL AND
jbe@619:             "state"           NOTNULL AND
jbe@619:             "initiative_id"   NOTNULL AND
jbe@619:             "draft_id"        NOTNULL AND
jbe@619:             "suggestion_id"   ISNULL  AND
jbe@619:             "boolean_value"   ISNULL  AND
jbe@619:             "numeric_value"   ISNULL  AND
jbe@619:             "text_value"      ISNULL  AND
jbe@619:             "old_text_value"  ISNULL ));
jbe@619: ALTER TABLE "event" ADD
jbe@619:         CONSTRAINT "constr_for_suggestion_creation" CHECK (
jbe@619:           "event" != 'suggestion_created' OR (
jbe@619:             "posting_id"      ISNULL  AND
jbe@619:             "member_id"       NOTNULL AND
jbe@619:             "other_member_id" ISNULL  AND
jbe@619:             "scope"           ISNULL  AND
jbe@619:             "unit_id"         NOTNULL AND
jbe@619:             "area_id"         NOTNULL AND
jbe@619:             "policy_id"       NOTNULL AND
jbe@619:             "issue_id"        NOTNULL AND
jbe@619:             "state"           NOTNULL AND
jbe@619:             "initiative_id"   NOTNULL AND
jbe@619:             "draft_id"        ISNULL  AND
jbe@619:             "suggestion_id"   NOTNULL AND
jbe@619:             "boolean_value"   ISNULL  AND
jbe@619:             "numeric_value"   ISNULL  AND
jbe@619:             "text_value"      ISNULL  AND
jbe@619:             "old_text_value"  ISNULL ));
jbe@619: ALTER TABLE "event" ADD
jbe@619:         CONSTRAINT "constr_for_suggestion_removal" CHECK (
jbe@619:           "event" != 'suggestion_deleted' OR (
jbe@619:             "posting_id"      ISNULL  AND
jbe@619:             "member_id"       ISNULL AND
jbe@619:             "other_member_id" ISNULL  AND
jbe@619:             "scope"           ISNULL  AND
jbe@619:             "unit_id"         NOTNULL AND
jbe@619:             "area_id"         NOTNULL AND
jbe@619:             "policy_id"       NOTNULL AND
jbe@619:             "issue_id"        NOTNULL AND
jbe@619:             "state"           NOTNULL AND
jbe@619:             "initiative_id"   NOTNULL AND
jbe@619:             "draft_id"        ISNULL  AND
jbe@619:             "suggestion_id"   NOTNULL AND
jbe@619:             "boolean_value"   ISNULL  AND
jbe@619:             "numeric_value"   ISNULL  AND
jbe@619:             "text_value"      ISNULL  AND
jbe@619:             "old_text_value"  ISNULL ));
jbe@619: ALTER TABLE "event" ADD
jbe@619:         CONSTRAINT "constr_for_value_less_member_event" CHECK (
jbe@619:           "event" NOT IN (
jbe@619:             'member_activated',
jbe@619:             'member_deleted',
jbe@619:             'member_profile_updated',
jbe@619:             'member_image_updated'
jbe@619:           ) OR (
jbe@619:             "posting_id"      ISNULL  AND
jbe@619:             "member_id"       NOTNULL AND
jbe@619:             "other_member_id" ISNULL  AND
jbe@619:             "scope"           ISNULL  AND
jbe@619:             "unit_id"         ISNULL  AND
jbe@619:             "area_id"         ISNULL  AND
jbe@619:             "policy_id"       ISNULL  AND
jbe@619:             "issue_id"        ISNULL  AND
jbe@619:             "state"           ISNULL  AND
jbe@619:             "initiative_id"   ISNULL  AND
jbe@619:             "draft_id"        ISNULL  AND
jbe@619:             "suggestion_id"   ISNULL  AND
jbe@619:             "boolean_value"   ISNULL  AND
jbe@619:             "numeric_value"   ISNULL  AND
jbe@619:             "text_value"      ISNULL  AND
jbe@619:             "old_text_value"  ISNULL ));
jbe@619: ALTER TABLE "event" ADD
jbe@619:         CONSTRAINT "constr_for_member_active" CHECK (
jbe@619:           "event" != 'member_active' OR (
jbe@619:             "posting_id"      ISNULL  AND
jbe@619:             "member_id"       NOTNULL AND
jbe@619:             "other_member_id" ISNULL  AND
jbe@619:             "scope"           ISNULL  AND
jbe@619:             "unit_id"         ISNULL  AND
jbe@619:             "area_id"         ISNULL  AND
jbe@619:             "policy_id"       ISNULL  AND
jbe@619:             "issue_id"        ISNULL  AND
jbe@619:             "state"           ISNULL  AND
jbe@619:             "initiative_id"   ISNULL  AND
jbe@619:             "draft_id"        ISNULL  AND
jbe@619:             "suggestion_id"   ISNULL  AND
jbe@619:             "boolean_value"   NOTNULL AND
jbe@619:             "numeric_value"   ISNULL  AND
jbe@619:             "text_value"      ISNULL  AND
jbe@619:             "old_text_value"  ISNULL ));
jbe@619: ALTER TABLE "event" ADD
jbe@619:         CONSTRAINT "constr_for_member_name_updated" CHECK (
jbe@619:           "event" != 'member_name_updated' OR (
jbe@619:             "posting_id"      ISNULL  AND
jbe@619:             "member_id"       NOTNULL AND
jbe@619:             "other_member_id" ISNULL  AND
jbe@619:             "scope"           ISNULL  AND
jbe@619:             "unit_id"         ISNULL  AND
jbe@619:             "area_id"         ISNULL  AND
jbe@619:             "policy_id"       ISNULL  AND
jbe@619:             "issue_id"        ISNULL  AND
jbe@619:             "state"           ISNULL  AND
jbe@619:             "initiative_id"   ISNULL  AND
jbe@619:             "draft_id"        ISNULL  AND
jbe@619:             "suggestion_id"   ISNULL  AND
jbe@619:             "boolean_value"   ISNULL  AND
jbe@619:             "numeric_value"   ISNULL  AND
jbe@619:             "text_value"      NOTNULL AND
jbe@619:             "old_text_value"  NOTNULL ));
jbe@619: ALTER TABLE "event" ADD
jbe@619:         CONSTRAINT "constr_for_interest" CHECK (
jbe@619:           "event" != 'interest' OR (
jbe@619:             "posting_id"      ISNULL  AND
jbe@619:             "member_id"       NOTNULL AND
jbe@619:             "other_member_id" ISNULL  AND
jbe@619:             "scope"           ISNULL  AND
jbe@619:             "unit_id"         NOTNULL AND
jbe@619:             "area_id"         NOTNULL AND
jbe@619:             "policy_id"       NOTNULL AND
jbe@619:             "issue_id"        NOTNULL AND
jbe@619:             "state"           NOTNULL AND
jbe@619:             "initiative_id"   ISNULL  AND
jbe@619:             "draft_id"        ISNULL  AND
jbe@619:             "suggestion_id"   ISNULL  AND
jbe@619:             "boolean_value"   NOTNULL AND
jbe@619:             "numeric_value"   ISNULL  AND
jbe@619:             "text_value"      ISNULL  AND
jbe@619:             "old_text_value"  ISNULL ));
jbe@619: ALTER TABLE "event" ADD
jbe@619:         CONSTRAINT "constr_for_initiator" CHECK (
jbe@619:           "event" != 'initiator' OR (
jbe@619:             "posting_id"      ISNULL  AND
jbe@619:             "member_id"       NOTNULL AND
jbe@619:             "other_member_id" ISNULL  AND
jbe@619:             "scope"           ISNULL  AND
jbe@619:             "unit_id"         NOTNULL AND
jbe@619:             "area_id"         NOTNULL AND
jbe@619:             "policy_id"       NOTNULL AND
jbe@619:             "issue_id"        NOTNULL AND
jbe@619:             "state"           NOTNULL AND
jbe@619:             "initiative_id"   NOTNULL AND
jbe@619:             "draft_id"        ISNULL  AND
jbe@619:             "suggestion_id"   ISNULL  AND
jbe@619:             "boolean_value"   NOTNULL AND
jbe@619:             "numeric_value"   ISNULL  AND
jbe@619:             "text_value"      ISNULL  AND
jbe@619:             "old_text_value"  ISNULL ));
jbe@619: ALTER TABLE "event" ADD
jbe@619:         CONSTRAINT "constr_for_support" CHECK (
jbe@619:           "event" != 'support' OR (
jbe@619:             "posting_id"      ISNULL  AND
jbe@619:             "member_id"       NOTNULL AND
jbe@619:             "other_member_id" ISNULL  AND
jbe@619:             "scope"           ISNULL  AND
jbe@619:             "unit_id"         NOTNULL AND
jbe@619:             "area_id"         NOTNULL AND
jbe@619:             "policy_id"       NOTNULL AND
jbe@619:             "issue_id"        NOTNULL AND
jbe@619:             "state"           NOTNULL AND
jbe@619:             "initiative_id"   NOTNULL AND
jbe@619:             ("draft_id" NOTNULL) = ("boolean_value" = TRUE) AND
jbe@619:             "suggestion_id"   ISNULL  AND
jbe@619:             "boolean_value"   NOTNULL AND
jbe@619:             "numeric_value"   ISNULL  AND
jbe@619:             "text_value"      ISNULL  AND
jbe@619:             "old_text_value"  ISNULL ));
jbe@619: ALTER TABLE "event" ADD
jbe@619:         CONSTRAINT "constr_for_support_updated" CHECK (
jbe@619:           "event" != 'support_updated' OR (
jbe@619:             "posting_id"      ISNULL  AND
jbe@619:             "member_id"       NOTNULL AND
jbe@619:             "other_member_id" ISNULL  AND
jbe@619:             "scope"           ISNULL  AND
jbe@619:             "unit_id"         NOTNULL AND
jbe@619:             "area_id"         NOTNULL AND
jbe@619:             "policy_id"       NOTNULL AND
jbe@619:             "issue_id"        NOTNULL AND
jbe@619:             "state"           NOTNULL AND
jbe@619:             "initiative_id"   NOTNULL AND
jbe@619:             "draft_id"        NOTNULL AND
jbe@619:             "suggestion_id"   ISNULL  AND
jbe@619:             "boolean_value"   ISNULL  AND
jbe@619:             "numeric_value"   ISNULL  AND
jbe@619:             "text_value"      ISNULL  AND
jbe@619:             "old_text_value"  ISNULL ));
jbe@619: ALTER TABLE "event" ADD
jbe@619:         CONSTRAINT "constr_for_suggestion_rated" CHECK (
jbe@619:           "event" != 'suggestion_rated' OR (
jbe@619:             "posting_id"      ISNULL  AND
jbe@619:             "member_id"       NOTNULL AND
jbe@619:             "other_member_id" ISNULL  AND
jbe@619:             "scope"           ISNULL  AND
jbe@619:             "unit_id"         NOTNULL AND
jbe@619:             "area_id"         NOTNULL AND
jbe@619:             "policy_id"       NOTNULL AND
jbe@619:             "issue_id"        NOTNULL AND
jbe@619:             "state"           NOTNULL AND
jbe@619:             "initiative_id"   NOTNULL AND
jbe@619:             "draft_id"        ISNULL  AND
jbe@619:             "suggestion_id"   NOTNULL AND
jbe@619:             ("boolean_value" NOTNULL) = ("numeric_value" != 0) AND
jbe@619:             "numeric_value"   NOTNULL AND
jbe@619:             "numeric_value" IN (-2, -1, 0, 1, 2) AND
jbe@619:             "text_value"      ISNULL  AND
jbe@619:             "old_text_value"  ISNULL ));
jbe@619: ALTER TABLE "event" ADD
jbe@619:         CONSTRAINT "constr_for_delegation" CHECK (
jbe@619:           "event" != 'delegation' OR (
jbe@619:             "posting_id"      ISNULL  AND
jbe@619:             "member_id"       NOTNULL AND
jbe@619:             (("other_member_id" ISNULL) OR ("boolean_value" = TRUE)) AND
jbe@619:             "scope"           NOTNULL AND
jbe@619:             "unit_id"         NOTNULL AND
jbe@619:             ("area_id"  NOTNULL) = ("scope" != 'unit'::"delegation_scope") AND
jbe@619:             "policy_id"       ISNULL  AND
jbe@619:             ("issue_id" NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND
jbe@619:             ("state"    NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND
jbe@619:             "initiative_id"   ISNULL  AND
jbe@619:             "draft_id"        ISNULL  AND
jbe@619:             "suggestion_id"   ISNULL  AND
jbe@619:             "boolean_value"   NOTNULL AND
jbe@619:             "numeric_value"   ISNULL  AND
jbe@619:             "text_value"      ISNULL  AND
jbe@619:             "old_text_value"  ISNULL ));
jbe@619: ALTER TABLE "event" ADD
jbe@619:         CONSTRAINT "constr_for_contact" CHECK (
jbe@619:           "event" != 'contact' OR (
jbe@619:             "posting_id"      ISNULL  AND
jbe@619:             "member_id"       NOTNULL AND
jbe@619:             "other_member_id" NOTNULL AND
jbe@619:             "scope"           ISNULL  AND
jbe@619:             "unit_id"         ISNULL  AND
jbe@619:             "area_id"         ISNULL  AND
jbe@619:             "policy_id"       ISNULL  AND
jbe@619:             "issue_id"        ISNULL  AND
jbe@619:             "state"           ISNULL  AND
jbe@619:             "initiative_id"   ISNULL  AND
jbe@619:             "draft_id"        ISNULL  AND
jbe@619:             "suggestion_id"   ISNULL  AND
jbe@619:             "boolean_value"   NOTNULL AND
jbe@619:             "numeric_value"   ISNULL  AND
jbe@619:             "text_value"      ISNULL  AND
jbe@619:             "old_text_value"  ISNULL ));
jbe@619: ALTER TABLE "event" ADD
jbe@619:         CONSTRAINT "constr_for_posting_created" CHECK (
jbe@619:           "event" != 'posting_created' OR (
jbe@619:             "posting_id"      NOTNULL AND
jbe@619:             "member_id"       NOTNULL AND
jbe@619:             "other_member_id" ISNULL  AND
jbe@619:             "scope"           ISNULL  AND
jbe@619:             "state"           ISNULL  AND
jbe@619:             ("area_id" ISNULL OR "unit_id" NOTNULL) AND
jbe@619:             ("policy_id" NOTNULL) = ("issue_id" NOTNULL) AND
jbe@619:             ("issue_id" ISNULL OR "area_id" NOTNULL) AND
jbe@619:             ("state" NOTNULL) = ("issue_id" NOTNULL) AND
jbe@619:             ("initiative_id" ISNULL OR "issue_id" NOTNULL) AND
jbe@619:             "draft_id"        ISNULL  AND
jbe@619:             ("suggestion_id" ISNULL OR "initiative_id" NOTNULL) AND
jbe@619:             "boolean_value"   ISNULL  AND
jbe@619:             "numeric_value"   ISNULL  AND
jbe@619:             "text_value"      ISNULL  AND
jbe@619:             "old_text_value"  ISNULL ));
jbe@619: 
jbe@619: CREATE OR REPLACE FUNCTION "highlight"
jbe@619:   ( "body_p"       TEXT,
jbe@619:     "query_text_p" TEXT )
jbe@619:   RETURNS TEXT
jbe@619:   LANGUAGE 'plpgsql' IMMUTABLE AS $$
jbe@619:     BEGIN
jbe@619:       RETURN ts_headline(
jbe@619:         replace(replace("body_p", e'\\', e'\\\\'), '*', e'\\*'),
jbe@619:         "plainto_tsquery"("query_text_p"),
jbe@619:         'StartSel=* StopSel=* HighlightAll=TRUE' );
jbe@619:     END;
jbe@619:   $$;
jbe@619: 
jbe@619: CREATE FUNCTION "to_tsvector"("member") RETURNS TSVECTOR
jbe@619:   LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ',
jbe@619:     $1."name",
jbe@619:     $1."identification"
jbe@619:   )) $$;
jbe@619: CREATE INDEX "member_to_tsvector_idx" ON "member" USING gin
jbe@619:   (("to_tsvector"("member".*)));
jbe@619: 
jbe@619: CREATE FUNCTION "to_tsvector"("member_profile") RETURNS TSVECTOR
jbe@619:   LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ',
jbe@619:     $1."statement",
jbe@619:     $1."profile_text_data"
jbe@619:   )) $$;
jbe@619: CREATE INDEX "member_profile_to_tsvector_idx" ON "member_profile" USING gin
jbe@619:   (("to_tsvector"("member_profile".*)));
jbe@619: 
jbe@619: CREATE FUNCTION "to_tsvector"("unit") RETURNS TSVECTOR
jbe@619:   LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ',
jbe@619:     $1."name",
jbe@619:     $1."description"
jbe@619:   )) $$;
jbe@619: CREATE INDEX "unit_to_tsvector_idx" ON "unit" USING gin
jbe@619:   (("to_tsvector"("unit".*)));
jbe@619: 
jbe@619: CREATE FUNCTION "to_tsvector"("area") RETURNS TSVECTOR
jbe@619:   LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ',
jbe@619:     $1."name",
jbe@619:     $1."description"
jbe@619:   )) $$;
jbe@619: CREATE INDEX "area_to_tsvector_idx" ON "area" USING gin
jbe@619:   (("to_tsvector"("area".*)));
jbe@619: 
jbe@619: CREATE FUNCTION "to_tsvector"("initiative") RETURNS TSVECTOR
jbe@619:   LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ',
jbe@619:     $1."name",
jbe@619:     $1."content"
jbe@619:   )) $$;
jbe@619: CREATE INDEX "initiative_to_tsvector_idx" ON "initiative" USING gin
jbe@619:   (("to_tsvector"("initiative".*)));
jbe@619: 
jbe@619: CREATE FUNCTION "to_tsvector"("draft") RETURNS TSVECTOR
jbe@619:   LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ',
jbe@619:     $1."content"
jbe@619:   )) $$;
jbe@619: CREATE INDEX "draft_to_tsvector_idx" ON "draft" USING gin
jbe@619:   (("to_tsvector"("draft".*)));
jbe@619: 
jbe@619: CREATE FUNCTION "to_tsvector"("suggestion") RETURNS TSVECTOR
jbe@619:   LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ',
jbe@619:     $1."name",
jbe@619:     $1."content"
jbe@619:   )) $$;
jbe@619: CREATE INDEX "suggestion_to_tsvector_idx" ON "suggestion" USING gin
jbe@619:   (("to_tsvector"("suggestion".*)));
jbe@619: 
jbe@619: CREATE FUNCTION "to_tsvector"("direct_voter") RETURNS TSVECTOR
jbe@619:   LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ',
jbe@619:     $1."comment"
jbe@619:   )) $$;
jbe@619: CREATE INDEX "direct_voter_to_tsvector_idx" ON "direct_voter" USING gin
jbe@619:   (("to_tsvector"("direct_voter".*)));
jbe@619: 
jbe@619: CREATE FUNCTION "update_posting_lexeme_trigger"()
jbe@619:   RETURNS TRIGGER
jbe@619:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@619:     DECLARE
jbe@619:       "lexeme_v" TEXT;
jbe@619:     BEGIN
jbe@619:       IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
jbe@619:         DELETE FROM "posting_lexeme" WHERE "posting_id" = OLD."id";
jbe@619:       END IF;
jbe@619:       IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
jbe@619:         FOR "lexeme_v" IN
jbe@619:           SELECT regexp_matches[1]
jbe@619:           FROM regexp_matches(NEW."message", '#[^\s.,;:]+')
jbe@619:         LOOP
jbe@619:           INSERT INTO "posting_lexeme" ("posting_id", "author_id", "lexeme")
jbe@619:             VALUES (
jbe@619:               NEW."id",
jbe@619:               NEW."author_id",
jbe@619:               "lexeme_v" )
jbe@619:             ON CONFLICT ("posting_id", "lexeme") DO NOTHING;
jbe@619:         END LOOP;
jbe@619:       END IF;
jbe@619:       RETURN NULL;
jbe@619:     END;
jbe@619:   $$;
jbe@619: 
jbe@619: CREATE TRIGGER "update_posting_lexeme"
jbe@619:   AFTER INSERT OR UPDATE OR DELETE ON "posting"
jbe@619:   FOR EACH ROW EXECUTE PROCEDURE "update_posting_lexeme_trigger"();
jbe@619: 
jbe@619: COMMENT ON FUNCTION "update_posting_lexeme_trigger"()  IS 'Implementation of trigger "update_posting_lexeme" on table "posting"';
jbe@619: COMMENT ON TRIGGER "update_posting_lexeme" ON "posting" IS 'Keeps table "posting_lexeme" up to date';
jbe@619: 
jbe@619: CREATE FUNCTION "write_event_posting_trigger"()
jbe@619:   RETURNS TRIGGER
jbe@619:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@619:     BEGIN
jbe@619:       INSERT INTO "event" (
jbe@619:           "event", "posting_id", "member_id",
jbe@619:           "unit_id", "area_id", "policy_id",
jbe@619:           "issue_id", "initiative_id", "suggestion_id"
jbe@619:         ) VALUES (
jbe@619:           'posting_created', NEW."id", NEW."author_id",
jbe@619:           NEW."unit_id", NEW."area_id", NEW."policy_id",
jbe@619:           NEW."issue_id", NEW."initiative_id", NEW."suggestion_id"
jbe@619:         );
jbe@619:       RETURN NULL;
jbe@619:     END;
jbe@619:   $$;
jbe@619: 
jbe@619: CREATE TRIGGER "write_event_posting"
jbe@619:   AFTER INSERT ON "posting" FOR EACH ROW EXECUTE PROCEDURE
jbe@619:   "write_event_posting_trigger"();
jbe@619: 
jbe@619: COMMENT ON FUNCTION "write_event_posting_trigger"()   IS 'Implementation of trigger "write_event_posting" on table "posting"';
jbe@619: COMMENT ON TRIGGER "write_event_posting" ON "posting" IS 'Create entry in "event" table when creating a new posting';
jbe@619: 
jbe@619: CREATE FUNCTION "file_requires_reference_trigger"()
jbe@619:   RETURNS TRIGGER
jbe@619:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@619:     BEGIN
jbe@619:       IF NOT EXISTS (
jbe@619:         SELECT NULL FROM "draft_attachment" WHERE "file_id" = NEW."id"
jbe@619:       ) THEN
jbe@619:         RAISE EXCEPTION 'Cannot create an unreferenced file.' USING
jbe@619:           ERRCODE = 'integrity_constraint_violation',
jbe@619:           HINT    = 'Create file and its reference in another table within the same transaction.';
jbe@619:       END IF;
jbe@619:       RETURN NULL;
jbe@619:     END;
jbe@619:   $$;
jbe@619: 
jbe@619: CREATE CONSTRAINT TRIGGER "file_requires_reference"
jbe@619:   AFTER INSERT OR UPDATE ON "file" DEFERRABLE INITIALLY DEFERRED
jbe@619:   FOR EACH ROW EXECUTE PROCEDURE
jbe@619:   "file_requires_reference_trigger"();
jbe@619: 
jbe@619: COMMENT ON FUNCTION "file_requires_reference_trigger"() IS 'Implementation of trigger "file_requires_reference" on table "file"';
jbe@619: COMMENT ON TRIGGER "file_requires_reference" ON "file"  IS 'Ensure that files are always referenced';
jbe@619: 
jbe@619: CREATE FUNCTION "last_reference_deletes_file_trigger"()
jbe@619:   RETURNS TRIGGER
jbe@619:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@619:     DECLARE
jbe@619:       "reference_lost" BOOLEAN;
jbe@619:     BEGIN
jbe@619:       IF TG_OP = 'DELETE' THEN
jbe@619:         "reference_lost" := TRUE;
jbe@619:       ELSE
jbe@619:         "reference_lost" := NEW."file_id" != OLD."file_id";
jbe@619:       END IF;
jbe@619:       IF
jbe@619:         "reference_lost" AND NOT EXISTS (
jbe@619:           SELECT NULL FROM "draft_attachment" WHERE "file_id" = OLD."file_id"
jbe@619:         )
jbe@619:       THEN
jbe@619:         DELETE FROM "file" WHERE "id" = OLD."file_id";
jbe@619:       END IF;
jbe@619:       RETURN NULL;
jbe@619:     END;
jbe@619:   $$;
jbe@619: 
jbe@619: CREATE CONSTRAINT TRIGGER "last_reference_deletes_file"
jbe@619:   AFTER UPDATE OR DELETE ON "draft_attachment" DEFERRABLE INITIALLY DEFERRED
jbe@619:   FOR EACH ROW EXECUTE PROCEDURE
jbe@619:   "last_reference_deletes_file_trigger"();
jbe@619: 
jbe@619: COMMENT ON FUNCTION "last_reference_deletes_file_trigger"()            IS 'Implementation of trigger "last_reference_deletes_file" on table "draft_attachment"';
jbe@619: COMMENT ON TRIGGER "last_reference_deletes_file" ON "draft_attachment" IS 'Removing the last reference to a file deletes the file';
jbe@619: 
jbe@619: CREATE OR REPLACE FUNCTION "copy_current_draft_data"
jbe@619:   ("initiative_id_p" "initiative"."id"%TYPE )
jbe@619:   RETURNS VOID
jbe@619:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@619:     BEGIN
jbe@619:       PERFORM NULL FROM "initiative" WHERE "id" = "initiative_id_p"
jbe@619:         FOR UPDATE;
jbe@619:       UPDATE "initiative" SET
jbe@619:         "location" = "draft"."location",
jbe@619:         "content"  = "draft"."content"
jbe@619:         FROM "current_draft" AS "draft"
jbe@619:         WHERE "initiative"."id" = "initiative_id_p"
jbe@619:         AND "draft"."initiative_id" = "initiative_id_p";
jbe@619:     END;
jbe@619:   $$;
jbe@619: 
jbe@619: CREATE VIEW "follower" AS
jbe@619:   SELECT
jbe@619:     "id" AS "follower_id",
jbe@619:     ( SELECT ARRAY["member"."id"] || array_agg("contact"."other_member_id")
jbe@619:       FROM "contact"
jbe@619:       WHERE "contact"."member_id" = "member"."id" AND "contact"."following" )
jbe@619:       AS "following_ids"
jbe@619:   FROM "member";
jbe@619: 
jbe@619: COMMENT ON VIEW "follower" IS 'Provides the contacts of each member that are being followed (including the member itself) as an array of IDs';
jbe@619: 
jbe@619: CREATE OR REPLACE FUNCTION "check_issue"
jbe@619:   ( "issue_id_p" "issue"."id"%TYPE,
jbe@619:     "persist"    "check_issue_persistence" )
jbe@619:   RETURNS "check_issue_persistence"
jbe@619:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@619:     DECLARE
jbe@619:       "issue_row"         "issue"%ROWTYPE;
jbe@619:       "last_calculated_v" "snapshot"."calculated"%TYPE;
jbe@619:       "policy_row"        "policy"%ROWTYPE;
jbe@619:       "initiative_row"    "initiative"%ROWTYPE;
jbe@619:       "state_v"           "issue_state";
jbe@619:     BEGIN
jbe@619:       PERFORM "require_transaction_isolation"();
jbe@619:       IF "persist" ISNULL THEN
jbe@619:         SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
jbe@619:           FOR UPDATE;
jbe@619:         SELECT "calculated" INTO "last_calculated_v"
jbe@619:           FROM "snapshot" JOIN "snapshot_issue"
jbe@619:           ON "snapshot"."id" = "snapshot_issue"."snapshot_id"
jbe@619:           WHERE "snapshot_issue"."issue_id" = "issue_id_p"
jbe@619:           ORDER BY "snapshot"."id" DESC;
jbe@619:         IF "issue_row"."closed" NOTNULL THEN
jbe@619:           RETURN NULL;
jbe@619:         END IF;
jbe@619:         "persist"."state" := "issue_row"."state";
jbe@619:         IF
jbe@619:           ( "issue_row"."state" = 'admission' AND "last_calculated_v" >=
jbe@619:             "issue_row"."created" + "issue_row"."max_admission_time" ) OR
jbe@619:           ( "issue_row"."state" = 'discussion' AND now() >=
jbe@619:             "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
jbe@619:           ( "issue_row"."state" = 'verification' AND now() >=
jbe@619:             "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR
jbe@619:           ( "issue_row"."state" = 'voting' AND now() >=
jbe@619:             "issue_row"."fully_frozen" + "issue_row"."voting_time" )
jbe@619:         THEN
jbe@619:           "persist"."phase_finished" := TRUE;
jbe@619:         ELSE
jbe@619:           "persist"."phase_finished" := FALSE;
jbe@619:         END IF;
jbe@619:         IF
jbe@619:           NOT EXISTS (
jbe@619:             -- all initiatives are revoked
jbe@619:             SELECT NULL FROM "initiative"
jbe@619:             WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@619:           ) AND (
jbe@619:             -- and issue has not been accepted yet
jbe@619:             "persist"."state" = 'admission' OR
jbe@619:             -- or verification time has elapsed
jbe@619:             ( "persist"."state" = 'verification' AND
jbe@619:               "persist"."phase_finished" ) OR
jbe@619:             -- or no initiatives have been revoked lately
jbe@619:             NOT EXISTS (
jbe@619:               SELECT NULL FROM "initiative"
jbe@619:               WHERE "issue_id" = "issue_id_p"
jbe@619:               AND now() < "revoked" + "issue_row"."verification_time"
jbe@619:             )
jbe@619:           )
jbe@619:         THEN
jbe@619:           "persist"."issue_revoked" := TRUE;
jbe@619:         ELSE
jbe@619:           "persist"."issue_revoked" := FALSE;
jbe@619:         END IF;
jbe@619:         IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
jbe@619:           UPDATE "issue" SET "phase_finished" = now()
jbe@619:             WHERE "id" = "issue_row"."id";
jbe@619:           RETURN "persist";
jbe@619:         ELSIF
jbe@619:           "persist"."state" IN ('admission', 'discussion', 'verification')
jbe@619:         THEN
jbe@619:           RETURN "persist";
jbe@619:         ELSE
jbe@619:           RETURN NULL;
jbe@619:         END IF;
jbe@619:       END IF;
jbe@619:       IF
jbe@619:         "persist"."state" IN ('admission', 'discussion', 'verification') AND
jbe@619:         coalesce("persist"."snapshot_created", FALSE) = FALSE
jbe@619:       THEN
jbe@619:         IF "persist"."state" != 'admission' THEN
jbe@619:           PERFORM "take_snapshot"("issue_id_p");
jbe@619:           PERFORM "finish_snapshot"("issue_id_p");
jbe@619:         ELSE
jbe@619:           UPDATE "issue" SET "issue_quorum" = "issue_quorum"."issue_quorum"
jbe@619:             FROM "issue_quorum"
jbe@619:             WHERE "id" = "issue_id_p"
jbe@619:             AND "issue_quorum"."issue_id" = "issue_id_p";
jbe@619:         END IF;
jbe@619:         "persist"."snapshot_created" = TRUE;
jbe@619:         IF "persist"."phase_finished" THEN
jbe@619:           IF "persist"."state" = 'admission' THEN
jbe@619:             UPDATE "issue" SET "admission_snapshot_id" = "latest_snapshot_id"
jbe@619:               WHERE "id" = "issue_id_p";
jbe@619:           ELSIF "persist"."state" = 'discussion' THEN
jbe@619:             UPDATE "issue" SET "half_freeze_snapshot_id" = "latest_snapshot_id"
jbe@619:               WHERE "id" = "issue_id_p";
jbe@619:           ELSIF "persist"."state" = 'verification' THEN
jbe@619:             UPDATE "issue" SET "full_freeze_snapshot_id" = "latest_snapshot_id"
jbe@619:               WHERE "id" = "issue_id_p";
jbe@619:             SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@619:             FOR "initiative_row" IN
jbe@619:               SELECT * FROM "initiative"
jbe@619:               WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@619:               FOR UPDATE
jbe@619:             LOOP
jbe@619:               IF
jbe@619:                 "initiative_row"."polling" OR
jbe@619:                 "initiative_row"."satisfied_supporter_count" >=
jbe@619:                 "issue_row"."initiative_quorum"
jbe@619:               THEN
jbe@619:                 UPDATE "initiative" SET "admitted" = TRUE
jbe@619:                   WHERE "id" = "initiative_row"."id";
jbe@619:               ELSE
jbe@619:                 UPDATE "initiative" SET "admitted" = FALSE
jbe@619:                   WHERE "id" = "initiative_row"."id";
jbe@619:               END IF;
jbe@619:             END LOOP;
jbe@619:           END IF;
jbe@619:         END IF;
jbe@619:         RETURN "persist";
jbe@619:       END IF;
jbe@619:       IF
jbe@619:         "persist"."state" IN ('admission', 'discussion', 'verification') AND
jbe@619:         coalesce("persist"."harmonic_weights_set", FALSE) = FALSE
jbe@619:       THEN
jbe@619:         PERFORM "set_harmonic_initiative_weights"("issue_id_p");
jbe@619:         "persist"."harmonic_weights_set" = TRUE;
jbe@619:         IF
jbe@619:           "persist"."phase_finished" OR
jbe@619:           "persist"."issue_revoked" OR
jbe@619:           "persist"."state" = 'admission'
jbe@619:         THEN
jbe@619:           RETURN "persist";
jbe@619:         ELSE
jbe@619:           RETURN NULL;
jbe@619:         END IF;
jbe@619:       END IF;
jbe@619:       IF "persist"."issue_revoked" THEN
jbe@619:         IF "persist"."state" = 'admission' THEN
jbe@619:           "state_v" := 'canceled_revoked_before_accepted';
jbe@619:         ELSIF "persist"."state" = 'discussion' THEN
jbe@619:           "state_v" := 'canceled_after_revocation_during_discussion';
jbe@619:         ELSIF "persist"."state" = 'verification' THEN
jbe@619:           "state_v" := 'canceled_after_revocation_during_verification';
jbe@619:         END IF;
jbe@619:         UPDATE "issue" SET
jbe@619:           "state"          = "state_v",
jbe@619:           "closed"         = "phase_finished",
jbe@619:           "phase_finished" = NULL
jbe@619:           WHERE "id" = "issue_id_p";
jbe@619:         RETURN NULL;
jbe@619:       END IF;
jbe@619:       IF "persist"."state" = 'admission' THEN
jbe@619:         SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
jbe@619:           FOR UPDATE;
jbe@619:         IF "issue_row"."phase_finished" NOTNULL THEN
jbe@619:           UPDATE "issue" SET
jbe@619:             "state"          = 'canceled_issue_not_accepted',
jbe@619:             "closed"         = "phase_finished",
jbe@619:             "phase_finished" = NULL
jbe@619:             WHERE "id" = "issue_id_p";
jbe@619:         END IF;
jbe@619:         RETURN NULL;
jbe@619:       END IF;
jbe@619:       IF "persist"."phase_finished" THEN
jbe@619:         IF "persist"."state" = 'discussion' THEN
jbe@619:           UPDATE "issue" SET
jbe@619:             "state"          = 'verification',
jbe@619:             "half_frozen"    = "phase_finished",
jbe@619:             "phase_finished" = NULL
jbe@619:             WHERE "id" = "issue_id_p";
jbe@619:           RETURN NULL;
jbe@619:         END IF;
jbe@619:         IF "persist"."state" = 'verification' THEN
jbe@619:           SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
jbe@619:             FOR UPDATE;
jbe@619:           SELECT * INTO "policy_row" FROM "policy"
jbe@619:             WHERE "id" = "issue_row"."policy_id";
jbe@619:           IF EXISTS (
jbe@619:             SELECT NULL FROM "initiative"
jbe@619:             WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
jbe@619:           ) THEN
jbe@619:             UPDATE "issue" SET
jbe@619:               "state"          = 'voting',
jbe@619:               "fully_frozen"   = "phase_finished",
jbe@619:               "phase_finished" = NULL
jbe@619:               WHERE "id" = "issue_id_p";
jbe@619:           ELSE
jbe@619:             UPDATE "issue" SET
jbe@619:               "state"          = 'canceled_no_initiative_admitted',
jbe@619:               "fully_frozen"   = "phase_finished",
jbe@619:               "closed"         = "phase_finished",
jbe@619:               "phase_finished" = NULL
jbe@619:               WHERE "id" = "issue_id_p";
jbe@619:             -- NOTE: The following DELETE statements have effect only when
jbe@619:             --       issue state has been manipulated
jbe@619:             DELETE FROM "direct_voter"     WHERE "issue_id" = "issue_id_p";
jbe@619:             DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
jbe@619:             DELETE FROM "battle"           WHERE "issue_id" = "issue_id_p";
jbe@619:           END IF;
jbe@619:           RETURN NULL;
jbe@619:         END IF;
jbe@619:         IF "persist"."state" = 'voting' THEN
jbe@619:           IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN
jbe@619:             PERFORM "close_voting"("issue_id_p");
jbe@619:             "persist"."closed_voting" = TRUE;
jbe@619:             RETURN "persist";
jbe@619:           END IF;
jbe@619:           PERFORM "calculate_ranks"("issue_id_p");
jbe@619:           RETURN NULL;
jbe@619:         END IF;
jbe@619:       END IF;
jbe@619:       RAISE WARNING 'should not happen';
jbe@619:       RETURN NULL;
jbe@619:     END;
jbe@619:   $$;
jbe@619: 
jbe@619: CREATE OR REPLACE FUNCTION "check_everything"()
jbe@619:   RETURNS VOID
jbe@619:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@619:     DECLARE
jbe@619:       "area_id_v"     "area"."id"%TYPE;
jbe@619:       "snapshot_id_v" "snapshot"."id"%TYPE;
jbe@619:       "issue_id_v"    "issue"."id"%TYPE;
jbe@619:       "persist_v"     "check_issue_persistence";
jbe@619:     BEGIN
jbe@619:       RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';
jbe@619:       DELETE FROM "expired_session";
jbe@619:       DELETE FROM "expired_token";
jbe@619:       DELETE FROM "unused_snapshot";
jbe@619:       PERFORM "check_activity"();
jbe@619:       PERFORM "calculate_member_counts"();
jbe@619:       FOR "area_id_v" IN SELECT "id" FROM "area_with_unaccepted_issues" LOOP
jbe@619:         SELECT "take_snapshot"(NULL, "area_id_v") INTO "snapshot_id_v";
jbe@619:         PERFORM "finish_snapshot"("issue_id") FROM "snapshot_issue"
jbe@619:           WHERE "snapshot_id" = "snapshot_id_v";
jbe@619:         LOOP
jbe@619:           EXIT WHEN "issue_admission"("area_id_v") = FALSE;
jbe@619:         END LOOP;
jbe@619:       END LOOP;
jbe@619:       FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
jbe@619:         "persist_v" := NULL;
jbe@619:         LOOP
jbe@619:           "persist_v" := "check_issue"("issue_id_v", "persist_v");
jbe@619:           EXIT WHEN "persist_v" ISNULL;
jbe@619:         END LOOP;
jbe@619:       END LOOP;
jbe@619:       DELETE FROM "unused_snapshot";
jbe@619:       RETURN;
jbe@619:     END;
jbe@619:   $$;
jbe@619: 
jbe@619: CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
jbe@619:   RETURNS VOID
jbe@619:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@619:     BEGIN
jbe@619:       UPDATE "member" SET
jbe@619:         "last_login"                   = NULL,
jbe@619:         "last_delegation_check"        = NULL,
jbe@619:         "login"                        = NULL,
jbe@619:         "password"                     = NULL,
jbe@619:         "authority"                    = NULL,
jbe@619:         "authority_uid"                = NULL,
jbe@619:         "authority_login"              = NULL,
jbe@619:         "deleted"                      = coalesce("deleted", now()),
jbe@619:         "locked"                       = TRUE,
jbe@619:         "active"                       = FALSE,
jbe@619:         "notify_email"                 = NULL,
jbe@619:         "notify_email_unconfirmed"     = NULL,
jbe@619:         "notify_email_secret"          = NULL,
jbe@619:         "notify_email_secret_expiry"   = NULL,
jbe@619:         "notify_email_lock_expiry"     = NULL,
jbe@619:         "disable_notifications"        = TRUE,
jbe@619:         "notification_counter"         = DEFAULT,
jbe@619:         "notification_sample_size"     = 0,
jbe@619:         "notification_dow"             = NULL,
jbe@619:         "notification_hour"            = NULL,
jbe@619:         "notification_sent"            = NULL,
jbe@619:         "login_recovery_expiry"        = NULL,
jbe@619:         "password_reset_secret"        = NULL,
jbe@619:         "password_reset_secret_expiry" = NULL,
jbe@619:         "location"                     = NULL
jbe@619:         WHERE "id" = "member_id_p";
jbe@619:       DELETE FROM "member_settings"    WHERE "member_id" = "member_id_p";
jbe@619:       DELETE FROM "member_profile"     WHERE "member_id" = "member_id_p";
jbe@619:       DELETE FROM "rendered_member_statement" WHERE "member_id" = "member_id_p";
jbe@619:       DELETE FROM "member_image"       WHERE "member_id" = "member_id_p";
jbe@619:       DELETE FROM "contact"            WHERE "member_id" = "member_id_p";
jbe@619:       DELETE FROM "ignored_member"     WHERE "member_id" = "member_id_p";
jbe@619:       DELETE FROM "session"            WHERE "member_id" = "member_id_p";
jbe@619:       DELETE FROM "member_application" WHERE "member_id" = "member_id_p";
jbe@619:       DELETE FROM "token"              WHERE "member_id" = "member_id_p";
jbe@619:       DELETE FROM "subscription"       WHERE "member_id" = "member_id_p";
jbe@619:       DELETE FROM "ignored_area"       WHERE "member_id" = "member_id_p";
jbe@619:       DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
jbe@619:       DELETE FROM "delegation"         WHERE "truster_id" = "member_id_p";
jbe@619:       DELETE FROM "non_voter"          WHERE "member_id" = "member_id_p";
jbe@619:       DELETE FROM "direct_voter" USING "issue"
jbe@619:         WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@619:         AND "issue"."closed" ISNULL
jbe@619:         AND "member_id" = "member_id_p";
jbe@619:       DELETE FROM "notification_initiative_sent" WHERE "member_id" = "member_id_p";
jbe@619:       RETURN;
jbe@619:     END;
jbe@619:   $$;
jbe@619: 
jbe@619: CREATE OR REPLACE FUNCTION "delete_private_data"()
jbe@619:   RETURNS VOID
jbe@619:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@619:     BEGIN
jbe@619:       DELETE FROM "temporary_transaction_data";
jbe@619:       DELETE FROM "temporary_suggestion_counts";
jbe@619:       DELETE FROM "member" WHERE "activated" ISNULL;
jbe@619:       UPDATE "member" SET
jbe@619:         "invite_code"                  = NULL,
jbe@619:         "invite_code_expiry"           = NULL,
jbe@619:         "admin_comment"                = NULL,
jbe@619:         "last_login"                   = NULL,
jbe@619:         "last_delegation_check"        = NULL,
jbe@619:         "login"                        = NULL,
jbe@619:         "password"                     = NULL,
jbe@619:         "authority"                    = NULL,
jbe@619:         "authority_uid"                = NULL,
jbe@619:         "authority_login"              = NULL,
jbe@619:         "lang"                         = NULL,
jbe@619:         "notify_email"                 = NULL,
jbe@619:         "notify_email_unconfirmed"     = NULL,
jbe@619:         "notify_email_secret"          = NULL,
jbe@619:         "notify_email_secret_expiry"   = NULL,
jbe@619:         "notify_email_lock_expiry"     = NULL,
jbe@619:         "disable_notifications"        = TRUE,
jbe@619:         "notification_counter"         = DEFAULT,
jbe@619:         "notification_sample_size"     = 0,
jbe@619:         "notification_dow"             = NULL,
jbe@619:         "notification_hour"            = NULL,
jbe@619:         "notification_sent"            = NULL,
jbe@619:         "login_recovery_expiry"        = NULL,
jbe@619:         "password_reset_secret"        = NULL,
jbe@619:         "password_reset_secret_expiry" = NULL,
jbe@619:         "location"                     = NULL;
jbe@619:       DELETE FROM "verification";
jbe@619:       DELETE FROM "member_settings";
jbe@619:       DELETE FROM "member_useterms";
jbe@619:       DELETE FROM "member_profile";
jbe@619:       DELETE FROM "rendered_member_statement";
jbe@619:       DELETE FROM "member_image";
jbe@619:       DELETE FROM "contact";
jbe@619:       DELETE FROM "ignored_member";
jbe@619:       DELETE FROM "session";
jbe@619:       DELETE FROM "system_application";
jbe@619:       DELETE FROM "system_application_redirect_uri";
jbe@619:       DELETE FROM "dynamic_application_scope";
jbe@619:       DELETE FROM "member_application";
jbe@619:       DELETE FROM "token";
jbe@619:       DELETE FROM "subscription";
jbe@619:       DELETE FROM "ignored_area";
jbe@619:       DELETE FROM "ignored_initiative";
jbe@619:       DELETE FROM "non_voter";
jbe@619:       DELETE FROM "direct_voter" USING "issue"
jbe@619:         WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@619:         AND "issue"."closed" ISNULL;
jbe@619:       DELETE FROM "event_processed";
jbe@619:       DELETE FROM "notification_initiative_sent";
jbe@619:       DELETE FROM "newsletter";
jbe@619:       RETURN;
jbe@619:     END;
jbe@619:   $$;
jbe@619: 
jbe@619: CREATE VIEW "member_eligible_to_be_notified" AS
jbe@619:   SELECT * FROM "member"
jbe@619:   WHERE "activated" NOTNULL AND "locked" = FALSE;
jbe@619: 
jbe@619: 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@619: 
jbe@619: CREATE VIEW "member_to_notify" AS
jbe@619:   SELECT * FROM "member_eligible_to_be_notified"
jbe@619:   WHERE "disable_notifications" = FALSE;
jbe@619: 
jbe@619: 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@619: 
jbe@619: CREATE VIEW "area_with_unaccepted_issues" AS
jbe@619:   SELECT DISTINCT ON ("area"."id") "area".*
jbe@619:   FROM "area" JOIN "issue" ON "area"."id" = "issue"."area_id"
jbe@619:   WHERE "issue"."state" = 'admission';
jbe@619: 
jbe@619: COMMENT ON VIEW "area_with_unaccepted_issues" IS 'All areas with unaccepted open issues (needed for issue admission system)';
jbe@619: 
jbe@619: CREATE VIEW "opening_draft" AS
jbe@619:   SELECT DISTINCT ON ("initiative_id") * FROM "draft"
jbe@619:   ORDER BY "initiative_id", "id";
jbe@619: 
jbe@619: COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
jbe@619: 
jbe@619: CREATE VIEW "current_draft" AS
jbe@619:   SELECT DISTINCT ON ("initiative_id") * FROM "draft"
jbe@619:   ORDER BY "initiative_id", "id" DESC;
jbe@619: 
jbe@619: COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
jbe@619: 
jbe@619: CREATE VIEW "member_contingent" AS
jbe@619:   SELECT
jbe@619:     "member"."id" AS "member_id",
jbe@619:     "contingent"."polling",
jbe@619:     "contingent"."time_frame",
jbe@619:     CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
jbe@619:       (
jbe@619:         SELECT count(1) FROM "draft"
jbe@619:         JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
jbe@619:         WHERE "draft"."author_id" = "member"."id"
jbe@619:         AND "initiative"."polling" = "contingent"."polling"
jbe@619:         AND "draft"."created" > now() - "contingent"."time_frame"
jbe@619:       ) + (
jbe@619:         SELECT count(1) FROM "suggestion"
jbe@619:         JOIN "initiative" ON "initiative"."id" = "suggestion"."initiative_id"
jbe@619:         WHERE "suggestion"."author_id" = "member"."id"
jbe@619:         AND "contingent"."polling" = FALSE
jbe@619:         AND "suggestion"."created" > now() - "contingent"."time_frame"
jbe@619:       )
jbe@619:     ELSE NULL END AS "text_entry_count",
jbe@619:     "contingent"."text_entry_limit",
jbe@619:     CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
jbe@619:       SELECT count(1) FROM "opening_draft" AS "draft"
jbe@619:         JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
jbe@619:       WHERE "draft"."author_id" = "member"."id"
jbe@619:       AND "initiative"."polling" = "contingent"."polling"
jbe@619:       AND "draft"."created" > now() - "contingent"."time_frame"
jbe@619:     ) ELSE NULL END AS "initiative_count",
jbe@619:     "contingent"."initiative_limit"
jbe@619:   FROM "member" CROSS JOIN "contingent";
jbe@619: 
jbe@619: COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
jbe@619: 
jbe@619: COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
jbe@619: COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
jbe@619: 
jbe@619: CREATE VIEW "member_contingent_left" AS
jbe@619:   SELECT
jbe@619:     "member_id",
jbe@619:     "polling",
jbe@619:     max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
jbe@619:     max("initiative_limit" - "initiative_count") AS "initiatives_left"
jbe@619:   FROM "member_contingent" GROUP BY "member_id", "polling";
jbe@619: 
jbe@619: COMMENT ON VIEW "member_contingent_left" IS 'Amount of text entries or initiatives which can be posted now instantly by a member. This view should be used by a frontend to determine, if the contingent for posting is exhausted.';
jbe@619: 
jbe@619: CREATE VIEW "scheduled_notification_to_send" AS
jbe@619:   SELECT * FROM (
jbe@619:     SELECT
jbe@619:       "id" AS "recipient_id",
jbe@619:       now() - CASE WHEN "notification_dow" ISNULL THEN
jbe@619:         ( "notification_sent"::DATE + CASE
jbe@619:           WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
jbe@619:           THEN 0 ELSE 1 END
jbe@619:         )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
jbe@619:       ELSE
jbe@619:         ( "notification_sent"::DATE +
jbe@619:           ( 7 + "notification_dow" -
jbe@619:             EXTRACT(DOW FROM
jbe@619:               ( "notification_sent"::DATE + CASE
jbe@619:                 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
jbe@619:                 THEN 0 ELSE 1 END
jbe@619:               )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
jbe@619:             )::INTEGER
jbe@619:           ) % 7 +
jbe@619:           CASE
jbe@619:             WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
jbe@619:             THEN 0 ELSE 1
jbe@619:           END
jbe@619:         )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
jbe@619:       END AS "pending"
jbe@619:     FROM (
jbe@619:       SELECT
jbe@619:         "id",
jbe@619:         COALESCE("notification_sent", "activated") AS "notification_sent",
jbe@619:         "notification_dow",
jbe@619:         "notification_hour"
jbe@619:       FROM "member_to_notify"
jbe@619:       WHERE "notification_hour" NOTNULL
jbe@619:     ) AS "subquery1"
jbe@619:   ) AS "subquery2"
jbe@619:   WHERE "pending" > '0'::INTERVAL;
jbe@619: 
jbe@619: COMMENT ON VIEW "scheduled_notification_to_send" IS 'Set of members where a scheduled notification mail is pending';
jbe@619: 
jbe@619: COMMENT ON COLUMN "scheduled_notification_to_send"."recipient_id" IS '"id" of the member who needs to receive a notification mail';
jbe@619: COMMENT ON COLUMN "scheduled_notification_to_send"."pending"      IS 'Duration for which the notification mail has already been pending';
jbe@619: 
jbe@619: CREATE VIEW "newsletter_to_send" AS
jbe@619:   SELECT
jbe@619:     "member"."id" AS "recipient_id",
jbe@619:     "newsletter"."id" AS "newsletter_id",
jbe@619:     "newsletter"."published"
jbe@619:   FROM "newsletter" CROSS JOIN "member_eligible_to_be_notified" AS "member"
jbe@619:   LEFT JOIN "privilege" ON
jbe@619:     "privilege"."member_id" = "member"."id" AND
jbe@619:     "privilege"."unit_id" = "newsletter"."unit_id" AND
jbe@619:     "privilege"."voting_right" = TRUE
jbe@619:   LEFT JOIN "subscription" ON
jbe@619:     "subscription"."member_id" = "member"."id" AND
jbe@619:     "subscription"."unit_id" = "newsletter"."unit_id"
jbe@619:   WHERE "newsletter"."published" <= now()
jbe@619:   AND "newsletter"."sent" ISNULL
jbe@619:   AND (
jbe@619:     "member"."disable_notifications" = FALSE OR
jbe@619:     "newsletter"."include_all_members" = TRUE )
jbe@619:   AND (
jbe@619:     "newsletter"."unit_id" ISNULL OR
jbe@619:     "privilege"."member_id" NOTNULL OR
jbe@619:     "subscription"."member_id" NOTNULL );
jbe@619: 
jbe@619: COMMENT ON VIEW "newsletter_to_send" IS 'List of "newsletter_id"s for each member that are due to be sent out';
jbe@619: 
jbe@619: COMMENT ON COLUMN "newsletter"."published" IS 'Timestamp when the newsletter was supposed to be sent out (can be used for ordering)';
jbe@619: 
jbe@619: SELECT "copy_current_draft_data" ("id") FROM "initiative";
jbe@619: 
jbe@619: COMMIT;
jbe@619: BEGIN;
jbe@619: 
jbe@619: CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@619:   SELECT * FROM (VALUES ('4.2.1', 4, 2, 1))
jbe@619:   AS "subquery"("string", "major", "minor", "revision");
jbe@619: 
jbe@619: ALTER TABLE "unit" ADD COLUMN "attr" JSONB NOT NULL DEFAULT '{}' CHECK (jsonb_typeof("attr") = 'object');
jbe@619: COMMENT ON COLUMN "unit"."attr" IS 'Opaque data structure to store any extended attributes used by frontend or middleware';
jbe@619: 
jbe@619: ALTER TABLE "unit" ADD COLUMN "member_weight" INT4;
jbe@619: COMMENT ON COLUMN "unit"."member_weight" IS 'Sum of active members'' voting weight';
jbe@619: 
jbe@619: ALTER TABLE "snapshot_population" ADD COLUMN "weight" INT4 NOT NULL DEFAULT 1;
jbe@619: ALTER TABLE "snapshot_population" ALTER COLUMN "weight" DROP DEFAULT;
jbe@619:  
jbe@619: ALTER TABLE "privilege" ADD COLUMN "weight" INT4 NOT NULL DEFAULT 1 CHECK ("weight" >= 0);
jbe@619: COMMENT ON COLUMN "privilege"."weight"           IS 'Voting weight of member in unit';
jbe@619: 
jbe@619: CREATE TABLE "issue_privilege" (
jbe@619:         PRIMARY KEY ("issue_id", "member_id"),
jbe@619:         "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@619:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@619:         "initiative_right"      BOOLEAN,
jbe@619:         "voting_right"          BOOLEAN,
jbe@619:         "polling_right"         BOOLEAN,
jbe@619:         "weight"                INT4            CHECK ("weight" >= 0) );
jbe@619: CREATE INDEX "issue_privilege_idx" ON "issue_privilege" ("member_id");
jbe@619: COMMENT ON TABLE "issue_privilege" IS 'Override of "privilege" table for rights of members in certain issues';
jbe@619:  
jbe@619: ALTER TABLE "direct_interest_snapshot" ADD COLUMN "ownweight" INT4 NOT NULL DEFAULT 1;
jbe@619: ALTER TABLE "direct_interest_snapshot" ALTER COLUMN "ownweight" DROP DEFAULT;
jbe@619: COMMENT ON COLUMN "direct_interest_snapshot"."ownweight" IS 'Own voting weight of member, disregading delegations';
jbe@619: COMMENT ON COLUMN "direct_interest_snapshot"."weight"    IS 'Voting weight of member according to own weight and "delegating_interest_snapshot"';
jbe@619:  
jbe@619: ALTER TABLE "delegating_interest_snapshot" ADD COLUMN "ownweight" INT4 NOT NULL DEFAULT 1;
jbe@619: ALTER TABLE "delegating_interest_snapshot" ALTER COLUMN "ownweight" DROP DEFAULT;
jbe@619: COMMENT ON COLUMN "delegating_interest_snapshot"."ownweight" IS 'Own voting weight of member, disregading delegations';
jbe@619: COMMENT ON COLUMN "delegating_interest_snapshot"."weight"    IS 'Intermediate voting weight considering incoming delegations';
jbe@619: 
jbe@619: ALTER TABLE "direct_voter" ADD COLUMN "ownweight" INT4 DEFAULT 1;
jbe@619: ALTER TABLE "direct_voter" ALTER COLUMN "ownweight" DROP DEFAULT;
jbe@619: COMMENT ON COLUMN "direct_voter"."ownweight" IS 'Own voting weight of member, disregarding delegations';
jbe@619: COMMENT ON COLUMN "direct_voter"."weight"    IS 'Voting weight of member according to own weight and "delegating_interest_snapshot"';
jbe@619: 
jbe@619: ALTER TABLE "delegating_voter" ADD COLUMN "ownweight" INT4 NOT NULL DEFAULT 1;
jbe@619: ALTER TABLE "delegating_voter" ALTER COLUMN "ownweight" DROP DEFAULT;
jbe@619: COMMENT ON COLUMN "delegating_voter"."ownweight" IS 'Own voting weight of member, disregarding delegations';
jbe@619: COMMENT ON COLUMN "delegating_voter"."weight"    IS 'Intermediate voting weight considering incoming delegations';
jbe@619: 
jbe@619: ALTER TABLE "posting" ADD FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id");
jbe@619: 
jbe@619: DROP VIEW "issue_delegation";
jbe@619: CREATE VIEW "issue_delegation" AS
jbe@619:   SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
jbe@619:     "issue"."id" AS "issue_id",
jbe@619:     "delegation"."id",
jbe@619:     "delegation"."truster_id",
jbe@619:     "delegation"."trustee_id",
jbe@619:     COALESCE("issue_privilege"."weight", "privilege"."weight") AS "weight",
jbe@619:     "delegation"."scope"
jbe@619:   FROM "issue"
jbe@619:   JOIN "area"
jbe@619:     ON "area"."id" = "issue"."area_id"
jbe@619:   JOIN "delegation"
jbe@619:     ON "delegation"."unit_id" = "area"."unit_id"
jbe@619:     OR "delegation"."area_id" = "area"."id"
jbe@619:     OR "delegation"."issue_id" = "issue"."id"
jbe@619:   JOIN "member"
jbe@619:     ON "delegation"."truster_id" = "member"."id"
jbe@619:   LEFT JOIN "privilege"
jbe@619:     ON "area"."unit_id" = "privilege"."unit_id"
jbe@619:     AND "delegation"."truster_id" = "privilege"."member_id"
jbe@619:   LEFT JOIN "issue_privilege"
jbe@619:     ON "issue"."id" = "issue_privilege"."issue_id"
jbe@619:     AND "delegation"."truster_id" = "issue_privilege"."member_id"
jbe@619:   WHERE "member"."active"
jbe@619:   AND COALESCE("issue_privilege"."voting_right", "privilege"."voting_right")
jbe@619:   ORDER BY
jbe@619:     "issue"."id",
jbe@619:     "delegation"."truster_id",
jbe@619:     "delegation"."scope" DESC;
jbe@619: COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
jbe@619: 
jbe@619: CREATE OR REPLACE VIEW "unit_member" AS
jbe@619:   SELECT
jbe@619:     "privilege"."unit_id" AS "unit_id",
jbe@619:     "member"."id"         AS "member_id",
jbe@619:     "privilege"."weight"
jbe@619:   FROM "privilege" JOIN "member" ON "member"."id" = "privilege"."member_id"
jbe@619:   WHERE "privilege"."voting_right" AND "member"."active";
jbe@619: 
jbe@619: CREATE OR REPLACE VIEW "unit_member_count" AS
jbe@619:   SELECT
jbe@619:     "unit"."id" AS "unit_id",
jbe@619:     count("unit_member"."member_id") AS "member_count",
jbe@619:     sum("unit_member"."weight") AS "member_weight"
jbe@619:   FROM "unit" LEFT JOIN "unit_member"
jbe@619:   ON "unit"."id" = "unit_member"."unit_id"
jbe@619:   GROUP BY "unit"."id";
jbe@619: 
jbe@619: CREATE OR REPLACE VIEW "event_for_notification" AS
jbe@619:   SELECT
jbe@619:     "member"."id" AS "recipient_id",
jbe@619:     "event".*
jbe@619:   FROM "member" CROSS JOIN "event"
jbe@619:   JOIN "issue" ON "issue"."id" = "event"."issue_id"
jbe@619:   JOIN "area" ON "area"."id" = "issue"."area_id"
jbe@619:   LEFT JOIN "privilege" ON
jbe@619:     "privilege"."member_id" = "member"."id" AND
jbe@619:     "privilege"."unit_id" = "area"."unit_id"
jbe@619:   LEFT JOIN "issue_privilege" ON
jbe@619:     "issue_privilege"."member_id" = "member"."id" AND
jbe@619:     "issue_privilege"."issue_id" = "event"."issue_id"
jbe@619:   LEFT JOIN "subscription" ON
jbe@619:     "subscription"."member_id" = "member"."id" AND
jbe@619:     "subscription"."unit_id" = "area"."unit_id"
jbe@619:   LEFT JOIN "ignored_area" ON
jbe@619:     "ignored_area"."member_id" = "member"."id" AND
jbe@619:     "ignored_area"."area_id" = "issue"."area_id"
jbe@619:   LEFT JOIN "interest" ON
jbe@619:     "interest"."member_id" = "member"."id" AND
jbe@619:     "interest"."issue_id" = "event"."issue_id"
jbe@619:   LEFT JOIN "supporter" ON
jbe@619:     "supporter"."member_id" = "member"."id" AND
jbe@619:     "supporter"."initiative_id" = "event"."initiative_id"
jbe@619:   WHERE (
jbe@619:     COALESCE("issue_privilege"."voting_right", "privilege"."voting_right") OR
jbe@619:     "subscription"."member_id" NOTNULL
jbe@619:   ) AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL)
jbe@619:   AND (
jbe@619:     "event"."event" = 'issue_state_changed'::"event_type" OR
jbe@619:     ( "event"."event" = 'initiative_revoked'::"event_type" AND
jbe@619:       "supporter"."member_id" NOTNULL ) );
jbe@619: 
jbe@619: CREATE OR REPLACE FUNCTION "featured_initiative"
jbe@619:   ( "recipient_id_p" "member"."id"%TYPE,
jbe@619:     "area_id_p"      "area"."id"%TYPE )
jbe@619:   RETURNS SETOF "initiative"."id"%TYPE
jbe@619:   LANGUAGE 'plpgsql' STABLE AS $$
jbe@619:     DECLARE
jbe@619:       "counter_v"         "member"."notification_counter"%TYPE;
jbe@619:       "sample_size_v"     "member"."notification_sample_size"%TYPE;
jbe@619:       "initiative_id_ary" INT4[];  --"initiative"."id"%TYPE[]
jbe@619:       "match_v"           BOOLEAN;
jbe@619:       "member_id_v"       "member"."id"%TYPE;
jbe@619:       "seed_v"            TEXT;
jbe@619:       "initiative_id_v"   "initiative"."id"%TYPE;
jbe@619:     BEGIN
jbe@619:       SELECT "notification_counter", "notification_sample_size"
jbe@619:         INTO "counter_v", "sample_size_v"
jbe@619:         FROM "member" WHERE "id" = "recipient_id_p";
jbe@619:       IF COALESCE("sample_size_v" <= 0, TRUE) THEN
jbe@619:         RETURN;
jbe@619:       END IF;
jbe@619:       "initiative_id_ary" := '{}';
jbe@619:       LOOP
jbe@619:         "match_v" := FALSE;
jbe@619:         FOR "member_id_v", "seed_v" IN
jbe@619:           SELECT * FROM (
jbe@619:             SELECT DISTINCT
jbe@619:               "supporter"."member_id",
jbe@619:               md5(
jbe@619:                 "recipient_id_p" || '-' ||
jbe@619:                 "counter_v"      || '-' ||
jbe@619:                 "area_id_p"      || '-' ||
jbe@619:                 "supporter"."member_id"
jbe@619:               ) AS "seed"
jbe@619:             FROM "supporter"
jbe@619:             JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
jbe@619:             JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@619:             WHERE "supporter"."member_id" != "recipient_id_p"
jbe@619:             AND "issue"."area_id" = "area_id_p"
jbe@619:             AND "issue"."state" IN ('admission', 'discussion', 'verification')
jbe@619:           ) AS "subquery"
jbe@619:           ORDER BY "seed"
jbe@619:         LOOP
jbe@619:           SELECT "initiative"."id" INTO "initiative_id_v"
jbe@619:             FROM "initiative"
jbe@619:             JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@619:             JOIN "area" ON "area"."id" = "issue"."area_id"
jbe@619:             JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
jbe@619:             LEFT JOIN "supporter" AS "self_support" ON
jbe@619:               "self_support"."initiative_id" = "initiative"."id" AND
jbe@619:               "self_support"."member_id" = "recipient_id_p"
jbe@619:             LEFT JOIN "privilege" ON
jbe@619:               "privilege"."member_id" = "recipient_id_p" AND
jbe@619:               "privilege"."unit_id" = "area"."unit_id"
jbe@619:             LEFT JOIN "issue_privilege" ON
jbe@619:               "issue_privilege"."member_id" = "recipient_id_p" AND
jbe@619:               "issue_privilege"."issue_id" = "initiative"."issue_id"
jbe@619:             LEFT JOIN "subscription" ON
jbe@619:               "subscription"."member_id" = "recipient_id_p" AND
jbe@619:               "subscription"."unit_id" = "area"."unit_id"
jbe@619:             LEFT JOIN "ignored_initiative" ON
jbe@619:               "ignored_initiative"."member_id" = "recipient_id_p" AND
jbe@619:               "ignored_initiative"."initiative_id" = "initiative"."id"
jbe@619:             WHERE "supporter"."member_id" = "member_id_v"
jbe@619:             AND "issue"."area_id" = "area_id_p"
jbe@619:             AND "issue"."state" IN ('admission', 'discussion', 'verification')
jbe@619:             AND "initiative"."revoked" ISNULL
jbe@619:             AND "self_support"."member_id" ISNULL
jbe@619:             AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
jbe@619:             AND (
jbe@619:               COALESCE(
jbe@619:                 "issue_privilege"."voting_right", "privilege"."voting_right"
jbe@619:               ) OR "subscription"."member_id" NOTNULL )
jbe@619:             AND "ignored_initiative"."member_id" ISNULL
jbe@619:             AND NOT EXISTS (
jbe@619:               SELECT NULL FROM "draft"
jbe@619:               JOIN "ignored_member" ON
jbe@619:                 "ignored_member"."member_id" = "recipient_id_p" AND
jbe@619:                 "ignored_member"."other_member_id" = "draft"."author_id"
jbe@619:               WHERE "draft"."initiative_id" = "initiative"."id"
jbe@619:             )
jbe@619:             ORDER BY md5("seed_v" || '-' || "initiative"."id")
jbe@619:             LIMIT 1;
jbe@619:           IF FOUND THEN
jbe@619:             "match_v" := TRUE;
jbe@619:             RETURN NEXT "initiative_id_v";
jbe@619:             IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN
jbe@619:               RETURN;
jbe@619:             END IF;
jbe@619:             "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";
jbe@619:           END IF;
jbe@619:         END LOOP;
jbe@619:         EXIT WHEN NOT "match_v";
jbe@619:       END LOOP;
jbe@619:       RETURN;
jbe@619:     END;
jbe@619:   $$;
jbe@619: 
jbe@619: CREATE OR REPLACE FUNCTION "delegation_chain"
jbe@619:   ( "member_id_p"           "member"."id"%TYPE,
jbe@619:     "unit_id_p"             "unit"."id"%TYPE,
jbe@619:     "area_id_p"             "area"."id"%TYPE,
jbe@619:     "issue_id_p"            "issue"."id"%TYPE,
jbe@619:     "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
jbe@619:     "simulate_default_p"    BOOLEAN            DEFAULT FALSE )
jbe@619:   RETURNS SETOF "delegation_chain_row"
jbe@619:   LANGUAGE 'plpgsql' STABLE AS $$
jbe@619:     DECLARE
jbe@619:       "scope_v"            "delegation_scope";
jbe@619:       "unit_id_v"          "unit"."id"%TYPE;
jbe@619:       "area_id_v"          "area"."id"%TYPE;
jbe@619:       "issue_row"          "issue"%ROWTYPE;
jbe@619:       "visited_member_ids" INT4[];  -- "member"."id"%TYPE[]
jbe@619:       "loop_member_id_v"   "member"."id"%TYPE;
jbe@619:       "output_row"         "delegation_chain_row";
jbe@619:       "output_rows"        "delegation_chain_row"[];
jbe@619:       "simulate_v"         BOOLEAN;
jbe@619:       "simulate_here_v"    BOOLEAN;
jbe@619:       "delegation_row"     "delegation"%ROWTYPE;
jbe@619:       "row_count"          INT4;
jbe@619:       "i"                  INT4;
jbe@619:       "loop_v"             BOOLEAN;
jbe@619:     BEGIN
jbe@619:       IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN
jbe@619:         RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';
jbe@619:       END IF;
jbe@619:       IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN
jbe@619:         "simulate_v" := TRUE;
jbe@619:       ELSE
jbe@619:         "simulate_v" := FALSE;
jbe@619:       END IF;
jbe@619:       IF
jbe@619:         "unit_id_p" NOTNULL AND
jbe@619:         "area_id_p" ISNULL AND
jbe@619:         "issue_id_p" ISNULL
jbe@619:       THEN
jbe@619:         "scope_v" := 'unit';
jbe@619:         "unit_id_v" := "unit_id_p";
jbe@619:       ELSIF
jbe@619:         "unit_id_p" ISNULL AND
jbe@619:         "area_id_p" NOTNULL AND
jbe@619:         "issue_id_p" ISNULL
jbe@619:       THEN
jbe@619:         "scope_v" := 'area';
jbe@619:         "area_id_v" := "area_id_p";
jbe@619:         SELECT "unit_id" INTO "unit_id_v"
jbe@619:           FROM "area" WHERE "id" = "area_id_v";
jbe@619:       ELSIF
jbe@619:         "unit_id_p" ISNULL AND
jbe@619:         "area_id_p" ISNULL AND
jbe@619:         "issue_id_p" NOTNULL
jbe@619:       THEN
jbe@619:         SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
jbe@619:         IF "issue_row"."id" ISNULL THEN
jbe@619:           RETURN;
jbe@619:         END IF;
jbe@619:         IF "issue_row"."closed" NOTNULL THEN
jbe@619:           IF "simulate_v" THEN
jbe@619:             RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
jbe@619:           END IF;
jbe@619:           FOR "output_row" IN
jbe@619:             SELECT * FROM
jbe@619:             "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
jbe@619:           LOOP
jbe@619:             RETURN NEXT "output_row";
jbe@619:           END LOOP;
jbe@619:           RETURN;
jbe@619:         END IF;
jbe@619:         "scope_v" := 'issue';
jbe@619:         SELECT "area_id" INTO "area_id_v"
jbe@619:           FROM "issue" WHERE "id" = "issue_id_p";
jbe@619:         SELECT "unit_id" INTO "unit_id_v"
jbe@619:           FROM "area"  WHERE "id" = "area_id_v";
jbe@619:       ELSE
jbe@619:         RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
jbe@619:       END IF;
jbe@619:       "visited_member_ids" := '{}';
jbe@619:       "loop_member_id_v"   := NULL;
jbe@619:       "output_rows"        := '{}';
jbe@619:       "output_row"."index"         := 0;
jbe@619:       "output_row"."member_id"     := "member_id_p";
jbe@619:       "output_row"."member_valid"  := TRUE;
jbe@619:       "output_row"."participation" := FALSE;
jbe@619:       "output_row"."overridden"    := FALSE;
jbe@619:       "output_row"."disabled_out"  := FALSE;
jbe@619:       "output_row"."scope_out"     := NULL;
jbe@619:       LOOP
jbe@619:         IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
jbe@619:           "loop_member_id_v" := "output_row"."member_id";
jbe@619:         ELSE
jbe@619:           "visited_member_ids" :=
jbe@619:             "visited_member_ids" || "output_row"."member_id";
jbe@619:         END IF;
jbe@619:         IF "output_row"."participation" ISNULL THEN
jbe@619:           "output_row"."overridden" := NULL;
jbe@619:         ELSIF "output_row"."participation" THEN
jbe@619:           "output_row"."overridden" := TRUE;
jbe@619:         END IF;
jbe@619:         "output_row"."scope_in" := "output_row"."scope_out";
jbe@619:         "output_row"."member_valid" := EXISTS (
jbe@619:           SELECT NULL FROM "member"
jbe@619:           LEFT JOIN "privilege"
jbe@619:           ON "privilege"."member_id" = "member"."id"
jbe@619:           AND "privilege"."unit_id" = "unit_id_v"
jbe@619:           LEFT JOIN "issue_privilege"
jbe@619:           ON "issue_privilege"."member_id" = "member"."id"
jbe@619:           AND "issue_privilege"."issue_id" = "issue_id_p"
jbe@619:           WHERE "id" = "output_row"."member_id"
jbe@619:           AND "member"."active"
jbe@619:           AND COALESCE(
jbe@619:             "issue_privilege"."voting_right", "privilege"."voting_right")
jbe@619:         );
jbe@619:         "simulate_here_v" := (
jbe@619:           "simulate_v" AND
jbe@619:           "output_row"."member_id" = "member_id_p"
jbe@619:         );
jbe@619:         "delegation_row" := ROW(NULL);
jbe@619:         IF "output_row"."member_valid" OR "simulate_here_v" THEN
jbe@619:           IF "scope_v" = 'unit' THEN
jbe@619:             IF NOT "simulate_here_v" THEN
jbe@619:               SELECT * INTO "delegation_row" FROM "delegation"
jbe@619:                 WHERE "truster_id" = "output_row"."member_id"
jbe@619:                 AND "unit_id" = "unit_id_v";
jbe@619:             END IF;
jbe@619:           ELSIF "scope_v" = 'area' THEN
jbe@619:             IF "simulate_here_v" THEN
jbe@619:               IF "simulate_trustee_id_p" ISNULL THEN
jbe@619:                 SELECT * INTO "delegation_row" FROM "delegation"
jbe@619:                   WHERE "truster_id" = "output_row"."member_id"
jbe@619:                   AND "unit_id" = "unit_id_v";
jbe@619:               END IF;
jbe@619:             ELSE
jbe@619:               SELECT * INTO "delegation_row" FROM "delegation"
jbe@619:                 WHERE "truster_id" = "output_row"."member_id"
jbe@619:                 AND (
jbe@619:                   "unit_id" = "unit_id_v" OR
jbe@619:                   "area_id" = "area_id_v"
jbe@619:                 )
jbe@619:                 ORDER BY "scope" DESC;
jbe@619:             END IF;
jbe@619:           ELSIF "scope_v" = 'issue' THEN
jbe@619:             IF "issue_row"."fully_frozen" ISNULL THEN
jbe@619:               "output_row"."participation" := EXISTS (
jbe@619:                 SELECT NULL FROM "interest"
jbe@619:                 WHERE "issue_id" = "issue_id_p"
jbe@619:                 AND "member_id" = "output_row"."member_id"
jbe@619:               );
jbe@619:             ELSE
jbe@619:               IF "output_row"."member_id" = "member_id_p" THEN
jbe@619:                 "output_row"."participation" := EXISTS (
jbe@619:                   SELECT NULL FROM "direct_voter"
jbe@619:                   WHERE "issue_id" = "issue_id_p"
jbe@619:                   AND "member_id" = "output_row"."member_id"
jbe@619:                 );
jbe@619:               ELSE
jbe@619:                 "output_row"."participation" := NULL;
jbe@619:               END IF;
jbe@619:             END IF;
jbe@619:             IF "simulate_here_v" THEN
jbe@619:               IF "simulate_trustee_id_p" ISNULL THEN
jbe@619:                 SELECT * INTO "delegation_row" FROM "delegation"
jbe@619:                   WHERE "truster_id" = "output_row"."member_id"
jbe@619:                   AND (
jbe@619:                     "unit_id" = "unit_id_v" OR
jbe@619:                     "area_id" = "area_id_v"
jbe@619:                   )
jbe@619:                   ORDER BY "scope" DESC;
jbe@619:               END IF;
jbe@619:             ELSE
jbe@619:               SELECT * INTO "delegation_row" FROM "delegation"
jbe@619:                 WHERE "truster_id" = "output_row"."member_id"
jbe@619:                 AND (
jbe@619:                   "unit_id" = "unit_id_v" OR
jbe@619:                   "area_id" = "area_id_v" OR
jbe@619:                   "issue_id" = "issue_id_p"
jbe@619:                 )
jbe@619:                 ORDER BY "scope" DESC;
jbe@619:             END IF;
jbe@619:           END IF;
jbe@619:         ELSE
jbe@619:           "output_row"."participation" := FALSE;
jbe@619:         END IF;
jbe@619:         IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN
jbe@619:           "output_row"."scope_out" := "scope_v";
jbe@619:           "output_rows" := "output_rows" || "output_row";
jbe@619:           "output_row"."member_id" := "simulate_trustee_id_p";
jbe@619:         ELSIF "delegation_row"."trustee_id" NOTNULL THEN
jbe@619:           "output_row"."scope_out" := "delegation_row"."scope";
jbe@619:           "output_rows" := "output_rows" || "output_row";
jbe@619:           "output_row"."member_id" := "delegation_row"."trustee_id";
jbe@619:         ELSIF "delegation_row"."scope" NOTNULL THEN
jbe@619:           "output_row"."scope_out" := "delegation_row"."scope";
jbe@619:           "output_row"."disabled_out" := TRUE;
jbe@619:           "output_rows" := "output_rows" || "output_row";
jbe@619:           EXIT;
jbe@619:         ELSE
jbe@619:           "output_row"."scope_out" := NULL;
jbe@619:           "output_rows" := "output_rows" || "output_row";
jbe@619:           EXIT;
jbe@619:         END IF;
jbe@619:         EXIT WHEN "loop_member_id_v" NOTNULL;
jbe@619:         "output_row"."index" := "output_row"."index" + 1;
jbe@619:       END LOOP;
jbe@619:       "row_count" := array_upper("output_rows", 1);
jbe@619:       "i"      := 1;
jbe@619:       "loop_v" := FALSE;
jbe@619:       LOOP
jbe@619:         "output_row" := "output_rows"["i"];
jbe@619:         EXIT WHEN "output_row" ISNULL;  -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
jbe@619:         IF "loop_v" THEN
jbe@619:           IF "i" + 1 = "row_count" THEN
jbe@619:             "output_row"."loop" := 'last';
jbe@619:           ELSIF "i" = "row_count" THEN
jbe@619:             "output_row"."loop" := 'repetition';
jbe@619:           ELSE
jbe@619:             "output_row"."loop" := 'intermediate';
jbe@619:           END IF;
jbe@619:         ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
jbe@619:           "output_row"."loop" := 'first';
jbe@619:           "loop_v" := TRUE;
jbe@619:         END IF;
jbe@619:         IF "scope_v" = 'unit' THEN
jbe@619:           "output_row"."participation" := NULL;
jbe@619:         END IF;
jbe@619:         RETURN NEXT "output_row";
jbe@619:         "i" := "i" + 1;
jbe@619:       END LOOP;
jbe@619:       RETURN;
jbe@619:     END;
jbe@619:   $$;
jbe@619: 
jbe@619: CREATE OR REPLACE FUNCTION "calculate_member_counts"()
jbe@619:   RETURNS VOID
jbe@619:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@619:     BEGIN
jbe@619:       PERFORM "require_transaction_isolation"();
jbe@619:       DELETE FROM "member_count";
jbe@619:       INSERT INTO "member_count" ("total_count")
jbe@619:         SELECT "total_count" FROM "member_count_view";
jbe@619:       UPDATE "unit" SET
jbe@619:         "member_count" = "view"."member_count",
jbe@619:         "member_weight" = "view"."member_weight"
jbe@619:         FROM "unit_member_count" AS "view"
jbe@619:         WHERE "view"."unit_id" = "unit"."id";
jbe@619:       RETURN;
jbe@619:     END;
jbe@619:   $$;
jbe@619: COMMENT ON FUNCTION "calculate_member_counts"() IS 'Updates "member_count" table and "member_count" and "member_weight" columns of table "area" by materializing data from views "member_count_view" and "unit_member_count"';
jbe@619:  
jbe@619: CREATE OR REPLACE FUNCTION "weight_of_added_delegations_for_snapshot"
jbe@619:   ( "snapshot_id_p"         "snapshot"."id"%TYPE,
jbe@619:     "issue_id_p"            "issue"."id"%TYPE,
jbe@619:     "member_id_p"           "member"."id"%TYPE,
jbe@619:     "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
jbe@619:   RETURNS "direct_interest_snapshot"."weight"%TYPE
jbe@619:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@619:     DECLARE
jbe@619:       "issue_delegation_row"  "issue_delegation"%ROWTYPE;
jbe@619:       "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
jbe@619:       "weight_v"              INT4;
jbe@619:       "sub_weight_v"          INT4;
jbe@619:     BEGIN
jbe@619:       PERFORM "require_transaction_isolation"();
jbe@619:       "weight_v" := 0;
jbe@619:       FOR "issue_delegation_row" IN
jbe@619:         SELECT * FROM "issue_delegation"
jbe@619:         WHERE "trustee_id" = "member_id_p"
jbe@619:         AND "issue_id" = "issue_id_p"
jbe@619:       LOOP
jbe@619:         IF NOT EXISTS (
jbe@619:           SELECT NULL FROM "direct_interest_snapshot"
jbe@619:           WHERE "snapshot_id" = "snapshot_id_p"
jbe@619:           AND "issue_id" = "issue_id_p"
jbe@619:           AND "member_id" = "issue_delegation_row"."truster_id"
jbe@619:         ) AND NOT EXISTS (
jbe@619:           SELECT NULL FROM "delegating_interest_snapshot"
jbe@619:           WHERE "snapshot_id" = "snapshot_id_p"
jbe@619:           AND "issue_id" = "issue_id_p"
jbe@619:           AND "member_id" = "issue_delegation_row"."truster_id"
jbe@619:         ) THEN
jbe@619:           "delegate_member_ids_v" :=
jbe@619:             "member_id_p" || "delegate_member_ids_p";
jbe@619:           INSERT INTO "delegating_interest_snapshot" (
jbe@619:               "snapshot_id",
jbe@619:               "issue_id",
jbe@619:               "member_id",
jbe@619:               "ownweight",
jbe@619:               "scope",
jbe@619:               "delegate_member_ids"
jbe@619:             ) VALUES (
jbe@619:               "snapshot_id_p",
jbe@619:               "issue_id_p",
jbe@619:               "issue_delegation_row"."truster_id",
jbe@619:               "issue_delegation_row"."weight",
jbe@619:               "issue_delegation_row"."scope",
jbe@619:               "delegate_member_ids_v"
jbe@619:             );
jbe@619:           "sub_weight_v" := "issue_delegation_row"."weight" +
jbe@619:             "weight_of_added_delegations_for_snapshot"(
jbe@619:               "snapshot_id_p",
jbe@619:               "issue_id_p",
jbe@619:               "issue_delegation_row"."truster_id",
jbe@619:               "delegate_member_ids_v"
jbe@619:             );
jbe@619:           UPDATE "delegating_interest_snapshot"
jbe@619:             SET "weight" = "sub_weight_v"
jbe@619:             WHERE "snapshot_id" = "snapshot_id_p"
jbe@619:             AND "issue_id" = "issue_id_p"
jbe@619:             AND "member_id" = "issue_delegation_row"."truster_id";
jbe@619:           "weight_v" := "weight_v" + "sub_weight_v";
jbe@619:         END IF;
jbe@619:       END LOOP;
jbe@619:       RETURN "weight_v";
jbe@619:     END;
jbe@619:   $$;
jbe@619: 
jbe@619: CREATE OR REPLACE FUNCTION "take_snapshot"
jbe@619:   ( "issue_id_p" "issue"."id"%TYPE,
jbe@619:     "area_id_p"  "area"."id"%TYPE = NULL )
jbe@619:   RETURNS "snapshot"."id"%TYPE
jbe@619:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@619:     DECLARE
jbe@619:       "area_id_v"     "area"."id"%TYPE;
jbe@619:       "unit_id_v"     "unit"."id"%TYPE;
jbe@619:       "snapshot_id_v" "snapshot"."id"%TYPE;
jbe@619:       "issue_id_v"    "issue"."id"%TYPE;
jbe@619:       "member_id_v"   "member"."id"%TYPE;
jbe@619:     BEGIN
jbe@619:       IF "issue_id_p" NOTNULL AND "area_id_p" NOTNULL THEN
jbe@619:         RAISE EXCEPTION 'One of "issue_id_p" and "area_id_p" must be NULL';
jbe@619:       END IF;
jbe@619:       PERFORM "require_transaction_isolation"();
jbe@619:       IF "issue_id_p" ISNULL THEN
jbe@619:         "area_id_v" := "area_id_p";
jbe@619:       ELSE
jbe@619:         SELECT "area_id" INTO "area_id_v"
jbe@619:           FROM "issue" WHERE "id" = "issue_id_p";
jbe@619:       END IF;
jbe@619:       SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
jbe@619:       INSERT INTO "snapshot" ("area_id", "issue_id")
jbe@619:         VALUES ("area_id_v", "issue_id_p")
jbe@619:         RETURNING "id" INTO "snapshot_id_v";
jbe@619:       INSERT INTO "snapshot_population" ("snapshot_id", "member_id", "weight")
jbe@619:         SELECT
jbe@619:           "snapshot_id_v",
jbe@619:           "member"."id",
jbe@619:           COALESCE("issue_privilege"."weight", "privilege"."weight")
jbe@619:         FROM "member"
jbe@619:         LEFT JOIN "privilege"
jbe@619:         ON "privilege"."unit_id" = "unit_id_v"
jbe@619:         AND "privilege"."member_id" = "member"."id"
jbe@619:         LEFT JOIN "issue_privilege"
jbe@619:         ON "issue_privilege"."issue_id" = "issue_id_p"
jbe@619:         AND "issue_privilege"."member_id" = "member"."id"
jbe@619:         WHERE "member"."active" AND COALESCE(
jbe@619:           "issue_privilege"."voting_right", "privilege"."voting_right");
jbe@619:       UPDATE "snapshot" SET
jbe@619:         "population" = (
jbe@619:           SELECT sum("weight") FROM "snapshot_population"
jbe@619:           WHERE "snapshot_id" = "snapshot_id_v"
jbe@619:         ) WHERE "id" = "snapshot_id_v";
jbe@619:       FOR "issue_id_v" IN
jbe@619:         SELECT "id" FROM "issue"
jbe@619:         WHERE CASE WHEN "issue_id_p" ISNULL THEN
jbe@619:           "area_id" = "area_id_p" AND
jbe@619:           "state" = 'admission'
jbe@619:         ELSE
jbe@619:           "id" = "issue_id_p"
jbe@619:         END
jbe@619:       LOOP
jbe@619:         INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")
jbe@619:           VALUES ("snapshot_id_v", "issue_id_v");
jbe@619:         INSERT INTO "direct_interest_snapshot"
jbe@619:           ("snapshot_id", "issue_id", "member_id", "ownweight")
jbe@619:           SELECT
jbe@619:             "snapshot_id_v" AS "snapshot_id",
jbe@619:             "issue_id_v"    AS "issue_id",
jbe@619:             "member"."id"   AS "member_id",
jbe@619:             COALESCE(
jbe@619:               "issue_privilege"."weight", "privilege"."weight"
jbe@619:             ) AS "ownweight"
jbe@619:           FROM "issue"
jbe@619:           JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@619:           JOIN "interest" ON "issue"."id" = "interest"."issue_id"
jbe@619:           JOIN "member" ON "interest"."member_id" = "member"."id"
jbe@619:           LEFT JOIN "privilege"
jbe@619:             ON "privilege"."unit_id" = "area"."unit_id"
jbe@619:             AND "privilege"."member_id" = "member"."id"
jbe@619:           LEFT JOIN "issue_privilege"
jbe@619:             ON "issue_privilege"."issue_id" = "issue_id_v"
jbe@619:             AND "issue_privilege"."member_id" = "member"."id"
jbe@619:           WHERE "issue"."id" = "issue_id_v"
jbe@619:           AND "member"."active" AND COALESCE(
jbe@619:             "issue_privilege"."voting_right", "privilege"."voting_right");
jbe@619:         FOR "member_id_v" IN
jbe@619:           SELECT "member_id" FROM "direct_interest_snapshot"
jbe@619:           WHERE "snapshot_id" = "snapshot_id_v"
jbe@619:           AND "issue_id" = "issue_id_v"
jbe@619:         LOOP
jbe@619:           UPDATE "direct_interest_snapshot" SET
jbe@619:             "weight" = "ownweight" +
jbe@619:               "weight_of_added_delegations_for_snapshot"(
jbe@619:                 "snapshot_id_v",
jbe@619:                 "issue_id_v",
jbe@619:                 "member_id_v",
jbe@619:                 '{}'
jbe@619:               )
jbe@619:             WHERE "snapshot_id" = "snapshot_id_v"
jbe@619:             AND "issue_id" = "issue_id_v"
jbe@619:             AND "member_id" = "member_id_v";
jbe@619:         END LOOP;
jbe@619:         INSERT INTO "direct_supporter_snapshot"
jbe@619:           ( "snapshot_id", "issue_id", "initiative_id", "member_id",
jbe@619:             "draft_id", "informed", "satisfied" )
jbe@619:           SELECT
jbe@619:             "snapshot_id_v"         AS "snapshot_id",
jbe@619:             "issue_id_v"            AS "issue_id",
jbe@619:             "initiative"."id"       AS "initiative_id",
jbe@619:             "supporter"."member_id" AS "member_id",
jbe@619:             "supporter"."draft_id"  AS "draft_id",
jbe@619:             "supporter"."draft_id" = "current_draft"."id" AS "informed",
jbe@619:             NOT EXISTS (
jbe@619:               SELECT NULL FROM "critical_opinion"
jbe@619:               WHERE "initiative_id" = "initiative"."id"
jbe@619:               AND "member_id" = "supporter"."member_id"
jbe@619:             ) AS "satisfied"
jbe@619:           FROM "initiative"
jbe@619:           JOIN "supporter"
jbe@619:           ON "supporter"."initiative_id" = "initiative"."id"
jbe@619:           JOIN "current_draft"
jbe@619:           ON "initiative"."id" = "current_draft"."initiative_id"
jbe@619:           JOIN "direct_interest_snapshot"
jbe@619:           ON "snapshot_id_v" = "direct_interest_snapshot"."snapshot_id"
jbe@619:           AND "supporter"."member_id" = "direct_interest_snapshot"."member_id"
jbe@619:           AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
jbe@619:           WHERE "initiative"."issue_id" = "issue_id_v";
jbe@619:         DELETE FROM "temporary_suggestion_counts";
jbe@619:         INSERT INTO "temporary_suggestion_counts"
jbe@619:           ( "id",
jbe@619:             "minus2_unfulfilled_count", "minus2_fulfilled_count",
jbe@619:             "minus1_unfulfilled_count", "minus1_fulfilled_count",
jbe@619:             "plus1_unfulfilled_count", "plus1_fulfilled_count",
jbe@619:             "plus2_unfulfilled_count", "plus2_fulfilled_count" )
jbe@619:           SELECT
jbe@619:             "suggestion"."id",
jbe@619:             ( SELECT coalesce(sum("di"."weight"), 0)
jbe@619:               FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@619:               ON "di"."snapshot_id" = "snapshot_id_v"
jbe@619:               AND "di"."issue_id" = "issue_id_v"
jbe@619:               AND "di"."member_id" = "opinion"."member_id"
jbe@619:               WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@619:               AND "opinion"."degree" = -2
jbe@619:               AND "opinion"."fulfilled" = FALSE
jbe@619:             ) AS "minus2_unfulfilled_count",
jbe@619:             ( SELECT coalesce(sum("di"."weight"), 0)
jbe@619:               FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@619:               ON "di"."snapshot_id" = "snapshot_id_v"
jbe@619:               AND "di"."issue_id" = "issue_id_v"
jbe@619:               AND "di"."member_id" = "opinion"."member_id"
jbe@619:               WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@619:               AND "opinion"."degree" = -2
jbe@619:               AND "opinion"."fulfilled" = TRUE
jbe@619:             ) AS "minus2_fulfilled_count",
jbe@619:             ( SELECT coalesce(sum("di"."weight"), 0)
jbe@619:               FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@619:               ON "di"."snapshot_id" = "snapshot_id_v"
jbe@619:               AND "di"."issue_id" = "issue_id_v"
jbe@619:               AND "di"."member_id" = "opinion"."member_id"
jbe@619:               WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@619:               AND "opinion"."degree" = -1
jbe@619:               AND "opinion"."fulfilled" = FALSE
jbe@619:             ) AS "minus1_unfulfilled_count",
jbe@619:             ( SELECT coalesce(sum("di"."weight"), 0)
jbe@619:               FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@619:               ON "di"."snapshot_id" = "snapshot_id_v"
jbe@619:               AND "di"."issue_id" = "issue_id_v"
jbe@619:               AND "di"."member_id" = "opinion"."member_id"
jbe@619:               WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@619:               AND "opinion"."degree" = -1
jbe@619:               AND "opinion"."fulfilled" = TRUE
jbe@619:             ) AS "minus1_fulfilled_count",
jbe@619:             ( SELECT coalesce(sum("di"."weight"), 0)
jbe@619:               FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@619:               ON "di"."snapshot_id" = "snapshot_id_v"
jbe@619:               AND "di"."issue_id" = "issue_id_v"
jbe@619:               AND "di"."member_id" = "opinion"."member_id"
jbe@619:               WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@619:               AND "opinion"."degree" = 1
jbe@619:               AND "opinion"."fulfilled" = FALSE
jbe@619:             ) AS "plus1_unfulfilled_count",
jbe@619:             ( SELECT coalesce(sum("di"."weight"), 0)
jbe@619:               FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@619:               ON "di"."snapshot_id" = "snapshot_id_v"
jbe@619:               AND "di"."issue_id" = "issue_id_v"
jbe@619:               AND "di"."member_id" = "opinion"."member_id"
jbe@619:               WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@619:               AND "opinion"."degree" = 1
jbe@619:               AND "opinion"."fulfilled" = TRUE
jbe@619:             ) AS "plus1_fulfilled_count",
jbe@619:             ( SELECT coalesce(sum("di"."weight"), 0)
jbe@619:               FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@619:               ON "di"."snapshot_id" = "snapshot_id_v"
jbe@619:               AND "di"."issue_id" = "issue_id_v"
jbe@619:               AND "di"."member_id" = "opinion"."member_id"
jbe@619:               WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@619:               AND "opinion"."degree" = 2
jbe@619:               AND "opinion"."fulfilled" = FALSE
jbe@619:             ) AS "plus2_unfulfilled_count",
jbe@619:             ( SELECT coalesce(sum("di"."weight"), 0)
jbe@619:               FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@619:               ON "di"."snapshot_id" = "snapshot_id_v"
jbe@619:               AND "di"."issue_id" = "issue_id_v"
jbe@619:               AND "di"."member_id" = "opinion"."member_id"
jbe@619:               WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@619:               AND "opinion"."degree" = 2
jbe@619:               AND "opinion"."fulfilled" = TRUE
jbe@619:             ) AS "plus2_fulfilled_count"
jbe@619:             FROM "suggestion" JOIN "initiative"
jbe@619:             ON "suggestion"."initiative_id" = "initiative"."id"
jbe@619:             WHERE "initiative"."issue_id" = "issue_id_v";
jbe@619:       END LOOP;
jbe@619:       RETURN "snapshot_id_v";
jbe@619:     END;
jbe@619:   $$;
jbe@619: 
jbe@619: CREATE OR REPLACE FUNCTION "weight_of_added_vote_delegations"
jbe@619:   ( "issue_id_p"            "issue"."id"%TYPE,
jbe@619:     "member_id_p"           "member"."id"%TYPE,
jbe@619:     "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
jbe@619:   RETURNS "direct_voter"."weight"%TYPE
jbe@619:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@619:     DECLARE
jbe@619:       "issue_delegation_row"  "issue_delegation"%ROWTYPE;
jbe@619:       "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
jbe@619:       "weight_v"              INT4;
jbe@619:       "sub_weight_v"          INT4;
jbe@619:     BEGIN
jbe@619:       PERFORM "require_transaction_isolation"();
jbe@619:       "weight_v" := 0;
jbe@619:       FOR "issue_delegation_row" IN
jbe@619:         SELECT * FROM "issue_delegation"
jbe@619:         WHERE "trustee_id" = "member_id_p"
jbe@619:         AND "issue_id" = "issue_id_p"
jbe@619:       LOOP
jbe@619:         IF NOT EXISTS (
jbe@619:           SELECT NULL FROM "direct_voter"
jbe@619:           WHERE "member_id" = "issue_delegation_row"."truster_id"
jbe@619:           AND "issue_id" = "issue_id_p"
jbe@619:         ) AND NOT EXISTS (
jbe@619:           SELECT NULL FROM "delegating_voter"
jbe@619:           WHERE "member_id" = "issue_delegation_row"."truster_id"
jbe@619:           AND "issue_id" = "issue_id_p"
jbe@619:         ) THEN
jbe@619:           "delegate_member_ids_v" :=
jbe@619:             "member_id_p" || "delegate_member_ids_p";
jbe@619:           INSERT INTO "delegating_voter" (
jbe@619:               "issue_id",
jbe@619:               "member_id",
jbe@619:               "ownweight",
jbe@619:               "scope",
jbe@619:               "delegate_member_ids"
jbe@619:             ) VALUES (
jbe@619:               "issue_id_p",
jbe@619:               "issue_delegation_row"."truster_id",
jbe@619:               "issue_delegation_row"."weight",
jbe@619:               "issue_delegation_row"."scope",
jbe@619:               "delegate_member_ids_v"
jbe@619:             );
jbe@619:           "sub_weight_v" := "issue_delegation_row"."weight" +
jbe@619:             "weight_of_added_vote_delegations"(
jbe@619:               "issue_id_p",
jbe@619:               "issue_delegation_row"."truster_id",
jbe@619:               "delegate_member_ids_v"
jbe@619:             );
jbe@619:           UPDATE "delegating_voter"
jbe@619:             SET "weight" = "sub_weight_v"
jbe@619:             WHERE "issue_id" = "issue_id_p"
jbe@619:             AND "member_id" = "issue_delegation_row"."truster_id";
jbe@619:           "weight_v" := "weight_v" + "sub_weight_v";
jbe@619:         END IF;
jbe@619:       END LOOP;
jbe@619:       RETURN "weight_v";
jbe@619:     END;
jbe@619:   $$;
jbe@619: 
jbe@619: CREATE OR REPLACE FUNCTION "add_vote_delegations"
jbe@619:   ( "issue_id_p" "issue"."id"%TYPE )
jbe@619:   RETURNS VOID
jbe@619:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@619:     DECLARE
jbe@619:       "member_id_v" "member"."id"%TYPE;
jbe@619:     BEGIN
jbe@619:       PERFORM "require_transaction_isolation"();
jbe@619:       FOR "member_id_v" IN
jbe@619:         SELECT "member_id" FROM "direct_voter"
jbe@619:         WHERE "issue_id" = "issue_id_p"
jbe@619:       LOOP
jbe@619:         UPDATE "direct_voter" SET
jbe@619:           "weight" = "ownweight" + "weight_of_added_vote_delegations"(
jbe@619:             "issue_id_p",
jbe@619:             "member_id_v",
jbe@619:             '{}'
jbe@619:           )
jbe@619:           WHERE "member_id" = "member_id_v"
jbe@619:           AND "issue_id" = "issue_id_p";
jbe@619:       END LOOP;
jbe@619:       RETURN;
jbe@619:     END;
jbe@619:   $$;
jbe@619: 
jbe@619: CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
jbe@619:   RETURNS VOID
jbe@619:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@619:     DECLARE
jbe@619:       "area_id_v"   "area"."id"%TYPE;
jbe@619:       "unit_id_v"   "unit"."id"%TYPE;
jbe@619:       "member_id_v" "member"."id"%TYPE;
jbe@619:     BEGIN
jbe@619:       PERFORM "require_transaction_isolation"();
jbe@619:       SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
jbe@619:       SELECT "unit_id" INTO "unit_id_v" FROM "area"  WHERE "id" = "area_id_v";
jbe@619:       -- override protection triggers:
jbe@619:       INSERT INTO "temporary_transaction_data" ("key", "value")
jbe@619:         VALUES ('override_protection_triggers', TRUE::TEXT);
jbe@619:       -- delete timestamp of voting comment:
jbe@619:       UPDATE "direct_voter" SET "comment_changed" = NULL
jbe@619:         WHERE "issue_id" = "issue_id_p";
jbe@619:       -- delete delegating votes (in cases of manual reset of issue state):
jbe@619:       DELETE FROM "delegating_voter"
jbe@619:         WHERE "issue_id" = "issue_id_p";
jbe@619:       -- delete votes from non-privileged voters:
jbe@619:       DELETE FROM "direct_voter"
jbe@619:         USING (
jbe@619:           SELECT "direct_voter"."member_id"
jbe@619:           FROM "direct_voter"
jbe@619:           JOIN "member" ON "direct_voter"."member_id" = "member"."id"
jbe@619:           LEFT JOIN "privilege"
jbe@619:           ON "privilege"."unit_id" = "unit_id_v"
jbe@619:           AND "privilege"."member_id" = "direct_voter"."member_id"
jbe@619:           LEFT JOIN "issue_privilege"
jbe@619:           ON "issue_privilege"."issue_id" = "issue_id_p"
jbe@619:           AND "issue_privilege"."member_id" = "direct_voter"."member_id"
jbe@619:           WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
jbe@619:             "member"."active" = FALSE OR
jbe@619:             COALESCE(
jbe@619:               "issue_privilege"."voting_right",
jbe@619:               "privilege"."voting_right",
jbe@619:               FALSE
jbe@619:             ) = FALSE
jbe@619:           )
jbe@619:         ) AS "subquery"
jbe@619:         WHERE "direct_voter"."issue_id" = "issue_id_p"
jbe@619:         AND "direct_voter"."member_id" = "subquery"."member_id";
jbe@619:       -- consider voting weight and delegations:
jbe@619:       UPDATE "direct_voter" SET "ownweight" = "privilege"."weight"
jbe@619:         FROM "privilege"
jbe@619:         WHERE "issue_id" = "issue_id_p"
jbe@619:         AND "privilege"."unit_id" = "unit_id_v"
jbe@619:         AND "privilege"."member_id" = "direct_voter"."member_id";
jbe@619:       UPDATE "direct_voter" SET "ownweight" = "issue_privilege"."weight"
jbe@619:         FROM "issue_privilege"
jbe@619:         WHERE "direct_voter"."issue_id" = "issue_id_p"
jbe@619:         AND "issue_privilege"."issue_id" = "issue_id_p"
jbe@619:         AND "issue_privilege"."member_id" = "direct_voter"."member_id";
jbe@619:       PERFORM "add_vote_delegations"("issue_id_p");
jbe@619:       -- mark first preferences:
jbe@619:       UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
jbe@619:         FROM (
jbe@619:           SELECT
jbe@619:             "vote"."initiative_id",
jbe@619:             "vote"."member_id",
jbe@619:             CASE WHEN "vote"."grade" > 0 THEN
jbe@619:               CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
jbe@619:             ELSE NULL
jbe@619:             END AS "first_preference"
jbe@619:           FROM "vote"
jbe@619:           JOIN "initiative"  -- NOTE: due to missing index on issue_id
jbe@619:           ON "vote"."issue_id" = "initiative"."issue_id"
jbe@619:           JOIN "vote" AS "agg"
jbe@619:           ON "initiative"."id" = "agg"."initiative_id"
jbe@619:           AND "vote"."member_id" = "agg"."member_id"
jbe@619:           GROUP BY "vote"."initiative_id", "vote"."member_id", "vote"."grade"
jbe@619:         ) AS "subquery"
jbe@619:         WHERE "vote"."issue_id" = "issue_id_p"
jbe@619:         AND "vote"."initiative_id" = "subquery"."initiative_id"
jbe@619:         AND "vote"."member_id" = "subquery"."member_id";
jbe@619:       -- finish overriding protection triggers (avoids garbage):
jbe@619:       DELETE FROM "temporary_transaction_data"
jbe@619:         WHERE "key" = 'override_protection_triggers';
jbe@619:       -- materialize battle_view:
jbe@619:       -- NOTE: "closed" column of issue must be set at this point
jbe@619:       DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@619:       INSERT INTO "battle" (
jbe@619:         "issue_id",
jbe@619:         "winning_initiative_id", "losing_initiative_id",
jbe@619:         "count"
jbe@619:       ) SELECT
jbe@619:         "issue_id",
jbe@619:         "winning_initiative_id", "losing_initiative_id",
jbe@619:         "count"
jbe@619:         FROM "battle_view" WHERE "issue_id" = "issue_id_p";
jbe@619:       -- set voter count:
jbe@619:       UPDATE "issue" SET
jbe@619:         "voter_count" = (
jbe@619:           SELECT coalesce(sum("weight"), 0)
jbe@619:           FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
jbe@619:         )
jbe@619:         WHERE "id" = "issue_id_p";
jbe@619:       -- copy "positive_votes" and "negative_votes" from "battle" table:
jbe@619:       -- NOTE: "first_preference_votes" is set to a default of 0 at this step
jbe@619:       UPDATE "initiative" SET
jbe@619:         "first_preference_votes" = 0,
jbe@619:         "positive_votes" = "battle_win"."count",
jbe@619:         "negative_votes" = "battle_lose"."count"
jbe@619:         FROM "battle" AS "battle_win", "battle" AS "battle_lose"
jbe@619:         WHERE
jbe@619:           "battle_win"."issue_id" = "issue_id_p" AND
jbe@619:           "battle_win"."winning_initiative_id" = "initiative"."id" AND
jbe@619:           "battle_win"."losing_initiative_id" ISNULL AND
jbe@619:           "battle_lose"."issue_id" = "issue_id_p" AND
jbe@619:           "battle_lose"."losing_initiative_id" = "initiative"."id" AND
jbe@619:           "battle_lose"."winning_initiative_id" ISNULL;
jbe@619:       -- calculate "first_preference_votes":
jbe@619:       -- NOTE: will only set values not equal to zero
jbe@619:       UPDATE "initiative" SET "first_preference_votes" = "subquery"."sum"
jbe@619:         FROM (
jbe@619:           SELECT "vote"."initiative_id", sum("direct_voter"."weight")
jbe@619:           FROM "vote" JOIN "direct_voter"
jbe@619:           ON "vote"."issue_id" = "direct_voter"."issue_id"
jbe@619:           AND "vote"."member_id" = "direct_voter"."member_id"
jbe@619:           WHERE "vote"."first_preference"
jbe@619:           GROUP BY "vote"."initiative_id"
jbe@619:         ) AS "subquery"
jbe@619:         WHERE "initiative"."issue_id" = "issue_id_p"
jbe@619:         AND "initiative"."admitted"
jbe@619:         AND "initiative"."id" = "subquery"."initiative_id";
jbe@619:     END;
jbe@619:   $$;
jbe@619: 
jbe@619: COMMIT;