jbe@262: BEGIN;
jbe@262: 
jbe@262: CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@262:   SELECT * FROM (VALUES ('2.1.0', 2, 1, 0))
jbe@262:   AS "subquery"("string", "major", "minor", "revision");
jbe@262: 
jbe@262: ALTER TABLE "policy" ADD COLUMN "polling" BOOLEAN NOT NULL DEFAULT FALSE;
jbe@262: ALTER TABLE "policy" ALTER COLUMN "admission_time"    DROP NOT NULL;
jbe@262: ALTER TABLE "policy" ALTER COLUMN "discussion_time"   DROP NOT NULL;
jbe@262: ALTER TABLE "policy" ALTER COLUMN "verification_time" DROP NOT NULL;
jbe@262: ALTER TABLE "policy" ALTER COLUMN "voting_time"       DROP NOT NULL;
jbe@262: ALTER TABLE "policy" ADD CONSTRAINT "timing" CHECK (
jbe@262:           ( "polling" = FALSE AND
jbe@262:             "admission_time" NOTNULL AND "discussion_time" NOTNULL AND
jbe@262:             "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR
jbe@262:           ( "polling" = TRUE AND
jbe@263:             "admission_time" ISNULL AND "discussion_time" NOTNULL AND
jbe@262:             "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR
jbe@262:           ( "polling" = TRUE AND
jbe@262:             "admission_time" ISNULL AND "discussion_time" ISNULL AND
jbe@262:             "verification_time" ISNULL AND "voting_time" ISNULL ) );
jbe@263: COMMENT ON COLUMN "policy"."polling" IS 'TRUE = special policy for non-user-generated issues, i.e. polls ("admission_time" MUST be set to NULL, the other timings may be set to NULL altogether, allowing individual timing for issues)';
jbe@262: 
jbe@262: ALTER TABLE "initiative" ADD COLUMN "polling" BOOLEAN NOT NULL DEFAULT FALSE;
jbe@262: COMMENT ON COLUMN "initiative"."polling" IS 'Initiative is an option for a poll (see "policy"."polling"), and does not need to pass the initiative quorum';
jbe@262: 
jbe@262: ALTER TABLE "privilege" RENAME COLUMN "voting_right_manager" TO "member_manager";
jbe@262: ALTER TABLE "privilege" ADD COLUMN "initiative_right" BOOLEAN NOT NULL DEFAULT TRUE;
jbe@262: ALTER TABLE "privilege" ADD COLUMN "polling_right"    BOOLEAN NOT NULL DEFAULT FALSE;
jbe@262: UPDATE "privilege" SET "initiative_right" = "voting_right";
jbe@262: COMMENT ON COLUMN "privilege"."admin_manager"    IS 'Grant/revoke any privileges to/from other members';
jbe@262: COMMENT ON COLUMN "privilege"."member_manager"   IS 'Adding/removing members from the unit, granting or revoking "initiative_right" and "voting_right"';
jbe@262: COMMENT ON COLUMN "privilege"."initiative_right" IS 'Right to create an initiative';
jbe@262: COMMENT ON COLUMN "privilege"."voting_right"     IS 'Right to support initiatives, create and rate suggestions, and to vote';
jbe@262: COMMENT ON COLUMN "privilege"."polling_right"    IS 'Right to create polls (see "policy"."polling" and "initiative"."polling")';
jbe@262: 
jbe@262: DROP TABLE "rendered_issue_comment";
jbe@262: DROP TABLE "issue_comment";
jbe@262: 
jbe@262: CREATE OR REPLACE FUNCTION "freeze_after_snapshot"
jbe@262:   ( "issue_id_p" "issue"."id"%TYPE )
jbe@262:   RETURNS VOID
jbe@262:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@262:     DECLARE
jbe@262:       "issue_row"      "issue"%ROWTYPE;
jbe@262:       "policy_row"     "policy"%ROWTYPE;
jbe@262:       "initiative_row" "initiative"%ROWTYPE;
jbe@262:     BEGIN
jbe@262:       SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@262:       SELECT * INTO "policy_row"
jbe@262:         FROM "policy" WHERE "id" = "issue_row"."policy_id";
jbe@262:       PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
jbe@262:       FOR "initiative_row" IN
jbe@262:         SELECT * FROM "initiative"
jbe@262:         WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@262:       LOOP
jbe@262:         IF
jbe@262:           "initiative_row"."polling" OR (
jbe@262:             "initiative_row"."satisfied_supporter_count" > 0 AND
jbe@262:             "initiative_row"."satisfied_supporter_count" *
jbe@262:             "policy_row"."initiative_quorum_den" >=
jbe@262:             "issue_row"."population" * "policy_row"."initiative_quorum_num"
jbe@262:           )
jbe@262:         THEN
jbe@262:           UPDATE "initiative" SET "admitted" = TRUE
jbe@262:             WHERE "id" = "initiative_row"."id";
jbe@262:         ELSE
jbe@262:           UPDATE "initiative" SET "admitted" = FALSE
jbe@262:             WHERE "id" = "initiative_row"."id";
jbe@262:         END IF;
jbe@262:       END LOOP;
jbe@262:       IF EXISTS (
jbe@262:         SELECT NULL FROM "initiative"
jbe@262:         WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
jbe@262:       ) THEN
jbe@262:         UPDATE "issue" SET
jbe@262:           "state"        = 'voting',
jbe@262:           "accepted"     = coalesce("accepted", now()),
jbe@262:           "half_frozen"  = coalesce("half_frozen", now()),
jbe@262:           "fully_frozen" = now()
jbe@262:           WHERE "id" = "issue_id_p";
jbe@262:       ELSE
jbe@262:         UPDATE "issue" SET
jbe@262:           "state"           = 'canceled_no_initiative_admitted',
jbe@262:           "accepted"        = coalesce("accepted", now()),
jbe@262:           "half_frozen"     = coalesce("half_frozen", now()),
jbe@262:           "fully_frozen"    = now(),
jbe@262:           "closed"          = now(),
jbe@262:           "ranks_available" = TRUE
jbe@262:           WHERE "id" = "issue_id_p";
jbe@262:         -- NOTE: The following DELETE statements have effect only when
jbe@262:         --       issue state has been manipulated
jbe@262:         DELETE FROM "direct_voter"     WHERE "issue_id" = "issue_id_p";
jbe@262:         DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
jbe@262:         DELETE FROM "battle"           WHERE "issue_id" = "issue_id_p";
jbe@262:       END IF;
jbe@262:       RETURN;
jbe@262:     END;
jbe@262:   $$;
jbe@262: 
jbe@262: CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
jbe@262:   RETURNS VOID
jbe@262:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@262:     DECLARE
jbe@262:       "issue_row" "issue"%ROWTYPE;
jbe@262:     BEGIN
jbe@262:       SELECT * INTO "issue_row"
jbe@262:         FROM "issue" WHERE "id" = "issue_id_p"
jbe@262:         FOR UPDATE;
jbe@262:       IF "issue_row"."cleaned" ISNULL THEN
jbe@262:         UPDATE "issue" SET
jbe@262:           "state"           = 'voting',
jbe@262:           "closed"          = NULL,
jbe@262:           "ranks_available" = FALSE
jbe@262:           WHERE "id" = "issue_id_p";
jbe@262:         DELETE FROM "voting_comment"
jbe@262:           WHERE "issue_id" = "issue_id_p";
jbe@262:         DELETE FROM "delegating_voter"
jbe@262:           WHERE "issue_id" = "issue_id_p";
jbe@262:         DELETE FROM "direct_voter"
jbe@262:           WHERE "issue_id" = "issue_id_p";
jbe@262:         DELETE FROM "delegating_interest_snapshot"
jbe@262:           WHERE "issue_id" = "issue_id_p";
jbe@262:         DELETE FROM "direct_interest_snapshot"
jbe@262:           WHERE "issue_id" = "issue_id_p";
jbe@262:         DELETE FROM "delegating_population_snapshot"
jbe@262:           WHERE "issue_id" = "issue_id_p";
jbe@262:         DELETE FROM "direct_population_snapshot"
jbe@262:           WHERE "issue_id" = "issue_id_p";
jbe@262:         DELETE FROM "non_voter"
jbe@262:           WHERE "issue_id" = "issue_id_p";
jbe@262:         DELETE FROM "delegation"
jbe@262:           WHERE "issue_id" = "issue_id_p";
jbe@262:         DELETE FROM "supporter"
jbe@262:           WHERE "issue_id" = "issue_id_p";
jbe@262:         UPDATE "issue" SET
jbe@262:           "state"           = "issue_row"."state",
jbe@262:           "closed"          = "issue_row"."closed",
jbe@262:           "ranks_available" = "issue_row"."ranks_available",
jbe@262:           "cleaned"         = now()
jbe@262:           WHERE "id" = "issue_id_p";
jbe@262:       END IF;
jbe@262:       RETURN;
jbe@262:     END;
jbe@262:   $$;
jbe@262: 
jbe@262: COMMIT;