1.1 --- a/core.sql	Sun Feb 10 19:14:29 2013 +0100
     1.2 +++ b/core.sql	Sun Feb 10 23:23:17 2013 +0100
     1.3 @@ -491,7 +491,6 @@
     1.4          'canceled_issue_not_accepted',
     1.5          'canceled_after_revocation_during_discussion',
     1.6          'canceled_after_revocation_during_verification',
     1.7 -        'calculation',
     1.8          'canceled_no_initiative_admitted',
     1.9          'finished_without_winner', 'finished_with_winner');
    1.10  
    1.11 @@ -503,12 +502,12 @@
    1.12          "area_id"               INT4            NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.13          "policy_id"             INT4            NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
    1.14          "state"                 "issue_state"   NOT NULL DEFAULT 'admission',
    1.15 +        "phase_finished"        TIMESTAMPTZ,
    1.16          "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
    1.17          "accepted"              TIMESTAMPTZ,
    1.18          "half_frozen"           TIMESTAMPTZ,
    1.19          "fully_frozen"          TIMESTAMPTZ,
    1.20          "closed"                TIMESTAMPTZ,
    1.21 -        "ranks_available"       BOOLEAN         NOT NULL DEFAULT FALSE,
    1.22          "cleaned"               TIMESTAMPTZ,
    1.23          "admission_time"        INTERVAL,
    1.24          "discussion_time"       INTERVAL        NOT NULL,
    1.25 @@ -522,15 +521,10 @@
    1.26          CONSTRAINT "admission_time_not_null_unless_instantly_accepted" CHECK (
    1.27            "admission_time" NOTNULL OR ("accepted" NOTNULL AND "accepted" = "created") ),
    1.28          CONSTRAINT "valid_state" CHECK ((
    1.29 -          ("accepted" ISNULL  AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
    1.30 -          ("accepted" ISNULL  AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
    1.31 -          ("accepted" NOTNULL AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
    1.32 -          ("accepted" NOTNULL AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
    1.33 -          ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL  AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
    1.34 -          ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL  AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
    1.35 -          ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
    1.36 -          ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
    1.37 -          ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE)) AND (
    1.38 +          ("accepted" ISNULL  AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL ) OR
    1.39 +          ("accepted" NOTNULL AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL ) OR
    1.40 +          ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL ) OR
    1.41 +          ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL)) AND (
    1.42            ("state" = 'admission'    AND "closed" ISNULL AND "accepted" ISNULL) OR
    1.43            ("state" = 'discussion'   AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
    1.44            ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
    1.45 @@ -539,11 +533,12 @@
    1.46            ("state" = 'canceled_issue_not_accepted'                   AND "closed" NOTNULL AND "accepted" ISNULL) OR
    1.47            ("state" = 'canceled_after_revocation_during_discussion'   AND "closed" NOTNULL AND "half_frozen"  ISNULL) OR
    1.48            ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
    1.49 -          ("state" = 'calculation'                     AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = FALSE) OR
    1.50 -          ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
    1.51 -          ("state" = 'finished_without_winner'         AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
    1.52 -          ("state" = 'finished_with_winner'            AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE)
    1.53 +          ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" = "fully_frozen") OR
    1.54 +          ("state" = 'finished_without_winner'         AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen") OR
    1.55 +          ("state" = 'finished_with_winner'            AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen")
    1.56            )),
    1.57 +        CONSTRAINT "phase_finished_only_when_not_closed" CHECK (
    1.58 +          "phase_finished" ISNULL OR "closed" ISNULL ),
    1.59          CONSTRAINT "state_change_order" CHECK (
    1.60            "created"      <= "accepted" AND
    1.61            "accepted"     <= "half_frozen" AND
    1.62 @@ -569,11 +564,11 @@
    1.63  
    1.64  COMMENT ON TABLE "issue" IS 'Groups of initiatives';
    1.65  
    1.66 +COMMENT ON COLUMN "issue"."phase_finished"          IS 'Set to a value NOTNULL, if the current phase has finished, but calculations are pending; No changes in this issue shall be made by the frontend or API when this value is set';
    1.67  COMMENT ON COLUMN "issue"."accepted"                IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
    1.68  COMMENT ON COLUMN "issue"."half_frozen"             IS 'Point in time, when "discussion_time" has elapsed; Frontends must ensure that for half_frozen issues a) initiatives are not revoked, b) no new drafts are created, c) no initiators are added or removed.';
    1.69  COMMENT ON COLUMN "issue"."fully_frozen"            IS 'Point in time, when "verification_time" has elapsed and voting has started; Frontends must ensure that for fully_frozen issues additionally to the restrictions for half_frozen issues a) initiatives are not created, b) no interest is created or removed, c) no supporters are added or removed, d) no opinions are created, changed or deleted.';
    1.70  COMMENT ON COLUMN "issue"."closed"                  IS 'Point in time, when "admission_time" or "voting_time" have elapsed, and issue is no longer active; Frontends must ensure that for closed issues additionally to the restrictions for half_frozen and fully_frozen issues a) no voter is added or removed to/from the direct_voter table, b) no votes are added, modified or removed.';
    1.71 -COMMENT ON COLUMN "issue"."ranks_available"         IS 'TRUE = ranks have been calculated';
    1.72  COMMENT ON COLUMN "issue"."cleaned"                 IS 'Point in time, when discussion data and votes had been deleted';
    1.73  COMMENT ON COLUMN "issue"."admission_time"          IS 'Copied from "policy" table at creation of issue';
    1.74  COMMENT ON COLUMN "issue"."discussion_time"         IS 'Copied from "policy" table at creation of issue';
    1.75 @@ -1112,7 +1107,7 @@
    1.76          "event"                 "event_type"    NOT NULL,
    1.77          "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
    1.78          "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.79 -        "state"                 "issue_state"   CHECK ("state" != 'calculation'),
    1.80 +        "state"                 "issue_state",
    1.81          "initiative_id"         INT4,
    1.82          "draft_id"              INT8,
    1.83          "suggestion_id"         INT8,
    1.84 @@ -1207,7 +1202,7 @@
    1.85    RETURNS TRIGGER
    1.86    LANGUAGE 'plpgsql' VOLATILE AS $$
    1.87      BEGIN
    1.88 -      IF NEW."state" != OLD."state" AND NEW."state" != 'calculation' THEN
    1.89 +      IF NEW."state" != OLD."state" THEN
    1.90          INSERT INTO "event" ("event", "issue_id", "state")
    1.91            VALUES ('issue_state_changed', NEW."id", NEW."state");
    1.92        END IF;
    1.93 @@ -2057,7 +2052,8 @@
    1.94    LEFT JOIN "vote" AS "worse_vote"
    1.95      ON "direct_voter"."member_id" = "worse_vote"."member_id"
    1.96      AND "losing_initiative"."id" = "worse_vote"."initiative_id"
    1.97 -  WHERE "issue"."closed" NOTNULL
    1.98 +  WHERE "issue"."state" = 'voting'
    1.99 +  AND "issue"."phase_finished" NOTNULL
   1.100    AND "issue"."cleaned" ISNULL
   1.101    AND (
   1.102      "winning_initiative"."id" != "losing_initiative"."id" OR
   1.103 @@ -2087,15 +2083,6 @@
   1.104  COMMENT ON VIEW "open_issue" IS 'All open issues';
   1.105  
   1.106  
   1.107 -CREATE VIEW "issue_with_ranks_missing" AS
   1.108 -  SELECT * FROM "issue"
   1.109 -  WHERE "fully_frozen" NOTNULL
   1.110 -  AND "closed" NOTNULL
   1.111 -  AND "ranks_available" = FALSE;
   1.112 -
   1.113 -COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
   1.114 -
   1.115 -
   1.116  CREATE VIEW "member_contingent" AS
   1.117    SELECT
   1.118      "member"."id" AS "member_id",