Refactor problem response priority into its own model

This moves the response priority values from a cobrand-specific method to a full
DB model, and includes management screens in the admin for administering them.

For mysociety/fixmystreetforcouncils#66

3 files changed, 44 insertions, 1 deletions

diff --git a/db/downgrade_0045---0044.sql b/db/downgrade_0045---0044.sql
new file mode 100644
index 000000000..7ab7ab6fd
--- /dev/null
+++ b/db/downgrade_0045---0044.sql
@@ -0,0 +1,7 @@
+BEGIN;
+
+ALTER TABLE problem DROP COLUMN response_priority_id;
+DROP TABLE contact_response_priorities;
+DROP TABLE response_priorities;
+
+COMMIT;
diff --git a/db/schema.sql b/db/schema.sql
index c96059fb1..27f4bad13 100644
--- a/db/schema.sql
+++ b/db/schema.sql
@@ -127,6 +127,15 @@ create trigger contacts_update_trigger after update on contacts
 create trigger contacts_insert_trigger after insert on contacts
     for each row execute procedure contacts_updated();
 
+-- Problems can have priorities. This table must be created before problem.
+CREATE TABLE response_priorities (
+    id serial not null primary key,
+    body_id int references body(id) not null,
+    deleted boolean not null default 'f',
+    name text not null,
+    unique(body_id, name)
+);
+
 -- Problems reported by users of site
 create table problem (
     id serial not null primary key,
@@ -185,7 +194,8 @@ create table problem (
     extra text, -- extra fields required for open311
     flagged boolean not null default 'f',
     geocode bytea,
-    
+    response_priority_id int REFERENCES response_priorities(id),
+
     -- logging sending failures (used by webservices)
     send_fail_count integer not null default 0, 
     send_fail_reason text, 
@@ -482,3 +492,9 @@ CREATE TABLE contact_response_templates (
     contact_id int REFERENCES contacts(id) NOT NULL,
     response_template_id int REFERENCES response_templates(id) NOT NULL
 );
+
+CREATE TABLE contact_response_priorities (
+    id serial NOT NULL PRIMARY KEY,
+    contact_id int REFERENCES contacts(id) NOT NULL,
+    response_priority_id int REFERENCES response_priorities(id) NOT NULL
+);
diff --git a/db/schema_0045-response-priorities.sql b/db/schema_0045-response-priorities.sql
new file mode 100644
index 000000000..16215e40c
--- /dev/null
+++ b/db/schema_0045-response-priorities.sql
@@ -0,0 +1,20 @@
+BEGIN;
+
+CREATE TABLE response_priorities (
+    id serial not null primary key,
+    body_id int references body(id) not null,
+    name text not null,
+    deleted boolean not null default 'f',
+    unique(body_id, name)
+);
+
+CREATE TABLE contact_response_priorities (
+    id serial NOT NULL PRIMARY KEY,
+    contact_id int REFERENCES contacts(id) NOT NULL,
+    response_priority_id int REFERENCES response_priorities(id) NOT NULL
+);
+
+ALTER TABLE problem
+    ADD COLUMN response_priority_id int REFERENCES response_priorities(id);
+
+COMMIT;