Add customisable defect types.

Problems can have an associated defect type, that can be assigned during
an inspection. Include an admin interface for managing these types, that
can also be assigned on a per-category basis, currently available to the
Oxfordshire cobrand.

(Also include 'TM' in traffic management Exor RDI output.)

2 files changed, 39 insertions, 0 deletions

diff --git a/db/schema.sql b/db/schema.sql
index 23db82b65..18c1533d9 100644
--- a/db/schema.sql
+++ b/db/schema.sql
@@ -502,3 +502,21 @@ CREATE TABLE contact_response_priorities (
     contact_id int REFERENCES contacts(id) NOT NULL,
     response_priority_id int REFERENCES response_priorities(id) NOT NULL
 );
+
+CREATE TABLE defect_types (
+    id serial not null primary key,
+    body_id int references body(id) not null,
+    name text not null,
+    description text not null,
+    extra text,
+    unique(body_id, name)
+);
+
+CREATE TABLE contact_defect_types (
+    id serial NOT NULL PRIMARY KEY,
+    contact_id int REFERENCES contacts(id) NOT NULL,
+    defect_type_id int REFERENCES defect_types(id) NOT NULL
+);
+
+ALTER TABLE problem
+    ADD COLUMN defect_type_id int REFERENCES defect_types(id);
diff --git a/db/schema_0050-add-defect-type-table.sql b/db/schema_0050-add-defect-type-table.sql
new file mode 100644
index 000000000..d65e17940
--- /dev/null
+++ b/db/schema_0050-add-defect-type-table.sql
@@ -0,0 +1,21 @@
+BEGIN;
+
+CREATE TABLE defect_types (
+    id serial not null primary key,
+    body_id int references body(id) not null,
+    name text not null,
+    description text not null,
+    extra text,
+    unique(body_id, name)
+);
+
+CREATE TABLE contact_defect_types (
+    id serial NOT NULL PRIMARY KEY,
+    contact_id int REFERENCES contacts(id) NOT NULL,
+    defect_type_id int REFERENCES defect_types(id) NOT NULL
+);
+
+ALTER TABLE problem
+    ADD COLUMN defect_type_id int REFERENCES defect_types(id);
+
+COMMIT;