Add inactive state to categories.

A new 'state' column replaces confirmed and deleted, allowing
categories to be unconfirmed, confirmed, deleted or inactive.

28 files changed, 204 insertions, 147 deletions

diff --git a/db/downgrade_0051---0050.sql b/db/downgrade_0051---0050.sql
new file mode 100644
index 000000000..424420e29
--- /dev/null
+++ b/db/downgrade_0051---0050.sql
@@ -0,0 +1,36 @@
+BEGIN;
+
+ALTER TABLE contacts ADD confirmed boolean;
+ALTER TABLE contacts ADD deleted boolean;
+
+UPDATE contacts SET confirmed='t', deleted='t' WHERE state = 'deleted';
+UPDATE contacts SET confirmed='f', deleted='t' WHERE state = 'inactive';
+UPDATE contacts SET confirmed='t', deleted='f' WHERE state = 'confirmed';
+UPDATE contacts SET confirmed='f', deleted='f' WHERE state = 'unconfirmed';
+
+ALTER TABLE contacts ALTER COLUMN confirmed SET NOT NULL;
+ALTER TABLE contacts ALTER COLUMN deleted SET NOT NULL;
+ALTER TABLE contacts DROP COLUMN state;
+
+ALTER TABLE contacts_history ADD confirmed boolean;
+ALTER TABLE contacts_history ADD deleted boolean;
+
+UPDATE contacts_history SET confirmed='t', deleted='t' WHERE state = 'deleted';
+UPDATE contacts_history SET confirmed='f', deleted='t' WHERE state = 'inactive';
+UPDATE contacts_history SET confirmed='t', deleted='f' WHERE state = 'confirmed';
+UPDATE contacts_history SET confirmed='f', deleted='f' WHERE state = 'unconfirmed';
+
+ALTER TABLE contacts_history ALTER COLUMN confirmed SET NOT NULL;
+ALTER TABLE contacts_history ALTER COLUMN deleted SET NOT NULL;
+ALTER TABLE contacts_history DROP COLUMN state;
+
+CREATE OR REPLACE FUNCTION contacts_updated()
+    returns trigger as '
+    begin
+        insert into contacts_history (contact_id, body_id, category, email, editor, whenedited, note, confirmed, deleted) values (new.id, new.body_id, new.category, new.email, new.editor, new.whenedited, new.note, new.confirmed, new.deleted);
+        return new;
+    end;
+' language 'plpgsql';
+
+COMMIT;
+
diff --git a/db/schema.sql b/db/schema.sql
index 18c1533d9..d35071c0f 100644
--- a/db/schema.sql
+++ b/db/schema.sql
@@ -69,8 +69,12 @@ create table contacts (
     body_id integer not null references body(id),
     category text not null default 'Other',
     email text not null,
-    confirmed boolean not null,
-    deleted boolean not null,
+    state text not null check (
+        state = 'unconfirmed'
+        or state = 'confirmed'
+        or state = 'inactive'
+        or state = 'deleted'
+    ),
 
     -- last editor
     editor text not null,
@@ -102,8 +106,12 @@ create table contacts_history (
     body_id integer not null,
     category text not null default 'Other',
     email text not null,
-    confirmed boolean not null,
-    deleted boolean not null,
+    state text not null check (
+        state = 'unconfirmed'
+        or state = 'confirmed'
+        or state = 'inactive'
+        or state = 'deleted'
+    ),
 
     -- editor
     editor text not null,
@@ -118,7 +126,7 @@ create table contacts_history (
 create function contacts_updated()
     returns trigger as '
     begin
-        insert into contacts_history (contact_id, body_id, category, email, editor, whenedited, note, confirmed, deleted) values (new.id, new.body_id, new.category, new.email, new.editor, new.whenedited, new.note, new.confirmed, new.deleted);
+        insert into contacts_history (contact_id, body_id, category, email, editor, whenedited, note, state) values (new.id, new.body_id, new.category, new.email, new.editor, new.whenedited, new.note, new.state);
         return new;
     end;
 ' language 'plpgsql';
diff --git a/db/schema_0051-inactive-contact-state.sql b/db/schema_0051-inactive-contact-state.sql
new file mode 100644
index 000000000..f29455f18
--- /dev/null
+++ b/db/schema_0051-inactive-contact-state.sql
@@ -0,0 +1,45 @@
+BEGIN;
+
+ALTER TABLE contacts ADD state text;
+
+ALTER TABLE contacts ADD CONSTRAINT contacts_state_check CHECK (
+    state = 'unconfirmed'
+    or state = 'confirmed'
+    or state = 'inactive'
+    or state = 'deleted'
+);
+
+UPDATE contacts SET state = 'deleted' WHERE deleted;
+UPDATE contacts SET state = 'confirmed' WHERE confirmed AND NOT deleted;
+UPDATE contacts SET state = 'unconfirmed' WHERE NOT confirmed AND NOT deleted;
+
+ALTER TABLE contacts ALTER COLUMN state SET NOT NULL;
+ALTER TABLE contacts DROP COLUMN confirmed;
+ALTER TABLE contacts DROP COLUMN deleted;
+
+ALTER TABLE contacts_history ADD state text;
+
+ALTER TABLE contacts_history ADD CONSTRAINT contacts_history_state_check CHECK (
+    state = 'unconfirmed'
+    or state = 'confirmed'
+    or state = 'inactive'
+    or state = 'deleted'
+);
+
+UPDATE contacts_history SET state = 'deleted' WHERE deleted;
+UPDATE contacts_history SET state = 'confirmed' WHERE confirmed AND NOT deleted;
+UPDATE contacts_history SET state = 'unconfirmed' WHERE NOT confirmed AND NOT deleted;
+
+ALTER TABLE contacts_history ALTER COLUMN state SET NOT NULL;
+ALTER TABLE contacts_history DROP COLUMN confirmed;
+ALTER TABLE contacts_history DROP COLUMN deleted;
+
+CREATE OR REPLACE FUNCTION contacts_updated()
+    returns trigger as '
+    begin
+        insert into contacts_history (contact_id, body_id, category, email, editor, whenedited, note, state) values (new.id, new.body_id, new.category, new.email, new.editor, new.whenedited, new.note, new.state);
+        return new;
+    end;
+' language 'plpgsql';
+
+COMMIT;
diff --git a/perllib/FixMyStreet/App/Controller/Admin.pm b/perllib/FixMyStreet/App/Controller/Admin.pm
index d354e6929..fc21aeedb 100644
--- a/perllib/FixMyStreet/App/Controller/Admin.pm
+++ b/perllib/FixMyStreet/App/Controller/Admin.pm
@@ -96,11 +96,11 @@ sub index : Path : Args(0) {
     my $contacts = $c->model('DB::Contact')->summary_count();
 
     my %contact_counts =
-      map { $_->confirmed => $_->get_column('confirmed_count') } $contacts->all;
+      map { $_->state => $_->get_column('state_count') } $contacts->all;
 
-    $contact_counts{0} ||= 0;
-    $contact_counts{1} ||= 0;
-    $contact_counts{total} = $contact_counts{0} + $contact_counts{1};
+    $contact_counts{confirmed} ||= 0;
+    $contact_counts{unconfirmed} ||= 0;
+    $contact_counts{total} = $contact_counts{confirmed} + $contact_counts{unconfirmed};
 
     $c->stash->{contacts} = \%contact_counts;
 
@@ -264,8 +264,8 @@ sub bodies : Path('bodies') : Args(0) {
     my $contacts = $c->model('DB::Contact')->search(
         undef,
         {
-            select => [ 'body_id', { count => 'id' }, { count => \'case when deleted then 1 else null end' },
-            { count => \'case when confirmed then 1 else null end' } ],
+            select => [ 'body_id', { count => 'id' }, { count => \'case when state = \'deleted\' then 1 else null end' },
+            { count => \'case when state = \'confirmed\' then 1 else null end' } ],
             as => [qw/body_id c deleted confirmed/],
             group_by => [ 'body_id' ],
             result_class => 'DBIx::Class::ResultClass::HashRefInflator'
@@ -364,8 +364,7 @@ sub update_contacts : Private {
         }
 
         $contact->email( $email );
-        $contact->confirmed( $c->get_param('confirmed') ? 1 : 0 );
-        $contact->deleted( $c->get_param('deleted') ? 1 : 0 );
+        $contact->state( $c->get_param('state') );
         $contact->non_public( $c->get_param('non_public') ? 1 : 0 );
         $contact->note( $c->get_param('note') );
         $contact->whenedited( \'current_timestamp' );
@@ -420,7 +419,7 @@ sub update_contacts : Private {
 
         $contacts->update(
             {
-                confirmed => 1,
+                state => 'confirmed',
                 whenedited => \'current_timestamp',
                 note => 'Confirmed',
                 editor => $editor,
@@ -484,8 +483,8 @@ sub fetch_contacts : Private {
 
     my $contacts = $c->stash->{body}->contacts->search(undef, { order_by => [ 'category' ] } );
     $c->stash->{contacts} = $contacts;
-    $c->stash->{live_contacts} = $contacts->search({ deleted => 0 });
-    $c->stash->{any_not_confirmed} = $contacts->search({ confirmed => 0 })->count;
+    $c->stash->{live_contacts} = $contacts->search({ state => { '!=' => 'deleted' } });
+    $c->stash->{any_not_confirmed} = $contacts->search({ state => 'unconfirmed' })->count;
 
     if ( $c->get_param('text') && $c->get_param('text') eq '1' ) {
         $c->stash->{template} = 'admin/council_contacts.txt';
diff --git a/perllib/FixMyStreet/DB/Factories.pm b/perllib/FixMyStreet/DB/Factories.pm
index acf88063e..ec4dd630a 100644
--- a/perllib/FixMyStreet/DB/Factories.pm
+++ b/perllib/FixMyStreet/DB/Factories.pm
@@ -91,8 +91,7 @@ __PACKAGE__->fields({
         (my $email = lc $_) =~ s/ /-/g;
         lc $category . '@example.org';
     }),
-    confirmed => 1,
-    deleted => 0,
+    state => 'confirmed',
     editor => 'Factory',
     whenedited => \'current_timestamp',
     note => 'Created by factory',
diff --git a/perllib/FixMyStreet/DB/Result/Contact.pm b/perllib/FixMyStreet/DB/Result/Contact.pm
index a620b7358..3454c5806 100644
--- a/perllib/FixMyStreet/DB/Result/Contact.pm
+++ b/perllib/FixMyStreet/DB/Result/Contact.pm
@@ -11,8 +11,17 @@ use base 'DBIx::Class::Core';
 __PACKAGE__->load_components("FilterColumn", "InflateColumn::DateTime", "EncodedColumn");
 __PACKAGE__->table("contacts");
 __PACKAGE__->add_columns(
+  "id",
+  {
+    data_type         => "integer",
+    is_auto_increment => 1,
+    is_nullable       => 0,
+    sequence          => "contacts_id_seq",
+  },
   "body_id",
   { data_type => "integer", is_foreign_key => 1, is_nullable => 0 },
+  "category",
+  { data_type => "text", default_value => "Other", is_nullable => 0 },
   "email",
   { data_type => "text", is_nullable => 0 },
   "editor",
@@ -21,19 +30,6 @@ __PACKAGE__->add_columns(
   { data_type => "timestamp", is_nullable => 0 },
   "note",
   { data_type => "text", is_nullable => 0 },
-  "confirmed",
-  { data_type => "boolean", is_nullable => 0 },
-  "category",
-  { data_type => "text", default_value => "Other", is_nullable => 0 },
-  "deleted",
-  { data_type => "boolean", is_nullable => 0 },
-  "id",
-  {
-    data_type         => "integer",
-    is_auto_increment => 1,
-    is_nullable       => 0,
-    sequence          => "contacts_id_seq",
-  },
   "extra",
   { data_type => "text", is_nullable => 1 },
   "non_public",
@@ -46,6 +42,8 @@ __PACKAGE__->add_columns(
   { data_type => "text", default_value => "", is_nullable => 1 },
   "send_method",
   { data_type => "text", is_nullable => 1 },
+  "state",
+  { data_type => "text", is_nullable => 0 },
 );
 __PACKAGE__->set_primary_key("id");
 __PACKAGE__->add_unique_constraint("contacts_body_id_category_idx", ["body_id", "category"]);
@@ -75,8 +73,8 @@ __PACKAGE__->has_many(
 );
 
 
-# Created by DBIx::Class::Schema::Loader v0.07035 @ 2017-02-13 15:11:11
-# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:f9VepR/oPyr3z6PUpJ4w2A
+# Created by DBIx::Class::Schema::Loader v0.07035 @ 2017-07-08 20:45:04
+# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:t/VtPP11R8bbqPZdEVXffw
 
 __PACKAGE__->load_components("+FixMyStreet::DB::RABXColumn");
 __PACKAGE__->rabx_column('extra');
diff --git a/perllib/FixMyStreet/DB/Result/ContactsHistory.pm b/perllib/FixMyStreet/DB/Result/ContactsHistory.pm
index 7126d91c9..c90bb9d66 100644
--- a/perllib/FixMyStreet/DB/Result/ContactsHistory.pm
+++ b/perllib/FixMyStreet/DB/Result/ContactsHistory.pm
@@ -26,22 +26,20 @@ __PACKAGE__->add_columns(
   { data_type => "text", default_value => "Other", is_nullable => 0 },
   "email",
   { data_type => "text", is_nullable => 0 },
-  "confirmed",
-  { data_type => "boolean", is_nullable => 0 },
-  "deleted",
-  { data_type => "boolean", is_nullable => 0 },
   "editor",
   { data_type => "text", is_nullable => 0 },
   "whenedited",
   { data_type => "timestamp", is_nullable => 0 },
   "note",
   { data_type => "text", is_nullable => 0 },
+  "state",
+  { data_type => "text", is_nullable => 0 },
 );
 __PACKAGE__->set_primary_key("contacts_history_id");
 
 
-# Created by DBIx::Class::Schema::Loader v0.07017 @ 2012-12-12 16:37:16
-# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:sxflEBBn0Mn0s3MroWnWFA
+# Created by DBIx::Class::Schema::Loader v0.07035 @ 2017-07-08 20:45:04
+# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:HTt0g29yXTM/WyHKN179FA
 
 
 # You can replace this text with custom code or comments, and it will be preserved on regeneration
diff --git a/perllib/FixMyStreet/DB/ResultSet/Contact.pm b/perllib/FixMyStreet/DB/ResultSet/Contact.pm
index f402b5461..d32453942 100644
--- a/perllib/FixMyStreet/DB/ResultSet/Contact.pm
+++ b/perllib/FixMyStreet/DB/ResultSet/Contact.pm
@@ -10,13 +10,13 @@ sub me { join('.', shift->current_source_alias, shift || q{})  }
 
     $rs = $rs->not_deleted();
 
-Filter down to not deleted contacts - which have C<deleted> set to false;
+Filter down to not deleted contacts (so active or inactive).
 
 =cut
 
 sub not_deleted {
     my $rs = shift;
-    return $rs->search( { $rs->me('deleted') => 0 } );
+    return $rs->search( { $rs->me('state') => { '!=' => 'deleted' } } );
 }
 
 sub summary_count {
@@ -25,9 +25,9 @@ sub summary_count {
     return $rs->search(
         $restriction,
         {
-            group_by => ['confirmed'],
-            select   => [ 'confirmed', { count => 'id' } ],
-            as       => [qw/confirmed confirmed_count/]
+            group_by => ['state'],
+            select   => [ 'state', { count => 'id' } ],
+            as       => [qw/state state_count/]
         }
     );
 }
diff --git a/perllib/FixMyStreet/SendReport/Email.pm b/perllib/FixMyStreet/SendReport/Email.pm
index 28f3411d0..eefb14553 100644
--- a/perllib/FixMyStreet/SendReport/Email.pm
+++ b/perllib/FixMyStreet/SendReport/Email.pm
@@ -12,15 +12,14 @@ sub build_recipient_list {
     my $all_confirmed = 1;
     foreach my $body ( @{ $self->bodies } ) {
 
-        my $contact = $row->result_source->schema->resultset("Contact")->find( {
-            deleted => 0,
+        my $contact = $row->result_source->schema->resultset("Contact")->not_deleted->find( {
             body_id => $body->id,
             category => $row->category
         } );
 
-        my ($body_email, $confirmed, $note) = ( $contact->email, $contact->confirmed, $contact->note );
+        my ($body_email, $state, $note) = ( $contact->email, $contact->state, $contact->note );
 
-        unless ($confirmed) {
+        unless ($state eq 'confirmed') {
             $all_confirmed = 0;
             $note = 'Body ' . $row->bodies_str . ' deleted'
                 unless $note;
diff --git a/perllib/FixMyStreet/SendReport/Open311.pm b/perllib/FixMyStreet/SendReport/Open311.pm
index 059690612..eaa223bb2 100644
--- a/perllib/FixMyStreet/SendReport/Open311.pm
+++ b/perllib/FixMyStreet/SendReport/Open311.pm
@@ -35,8 +35,7 @@ sub send {
 
         # Try and fill in some ones that we've been asked for, but not asked the user for
 
-        my $contact = $row->result_source->schema->resultset("Contact")->find( {
-            deleted => 0,
+        my $contact = $row->result_source->schema->resultset("Contact")->not_deleted->find( {
             body_id => $body->id,
             category => $row->category
         } );
diff --git a/perllib/FixMyStreet/TestMech.pm b/perllib/FixMyStreet/TestMech.pm
index 92588a598..eef767ce6 100644
--- a/perllib/FixMyStreet/TestMech.pm
+++ b/perllib/FixMyStreet/TestMech.pm
@@ -656,8 +656,7 @@ sub delete_defect_type {
 sub create_contact_ok {
     my $self = shift;
     my %contact_params = (
-        confirmed => 1,
-        deleted => 0,
+        state => 'confirmed',
         editor => 'Test',
         whenedited => \'current_timestamp',
         note => 'Created for test',
diff --git a/perllib/Open311/PopulateServiceList.pm b/perllib/Open311/PopulateServiceList.pm
index c5f17334b..540425bf1 100644
--- a/perllib/Open311/PopulateServiceList.pm
+++ b/perllib/Open311/PopulateServiceList.pm
@@ -131,14 +131,13 @@ sub _handle_existing_contact {
 
     print $self->_current_body->id . " already has a contact for service code " . $self->_current_service->{service_code} . "\n" if $self->verbose >= 2;
 
-    if ( $contact->deleted || $service_name ne $contact->category || $self->_current_service->{service_code} ne $contact->email ) {
+    if ( $contact->state eq 'deleted' || $service_name ne $contact->category || $self->_current_service->{service_code} ne $contact->email ) {
         eval {
             $contact->update(
                 {
                     category => $service_name,
                     email => $self->_current_service->{service_code},
-                    confirmed => 1,
-                    deleted => 0,
+                    state => 'confirmed',
                     editor => $0,
                     whenedited => \'current_timestamp',
                     note => 'automatically undeleted by script',
@@ -175,8 +174,7 @@ sub _create_contact {
                 email => $self->_current_service->{service_code},
                 body_id => $self->_current_body->id,
                 category => $service_name,
-                confirmed => 1,
-                deleted => 0,
+                state => 'confirmed',
                 editor => $0,
                 whenedited => \'current_timestamp',
                 note => 'created automatically by script',
@@ -278,11 +276,10 @@ sub _normalize_service_name {
 sub _delete_contacts_not_in_service_list {
     my $self = shift;
 
-    my $found_contacts = $self->schema->resultset('Contact')->search(
+    my $found_contacts = $self->schema->resultset('Contact')->not_deleted->search(
         {
             email => { -not_in => $self->found_contacts },
             body_id => $self->_current_body->id,
-            deleted => 0,
         }
     );
 
@@ -299,7 +296,7 @@ sub _delete_contacts_not_in_service_list {
 
     $found_contacts->update(
         {
-            deleted => 1,
+            state => 'deleted',
             editor  => $0,
             whenedited => \'current_timestamp',
             note => 'automatically marked as deleted by script'
diff --git a/t/app/controller/admin.t b/t/app/controller/admin.t
index 4384123bd..db7f9fc3c 100644
--- a/t/app/controller/admin.t
+++ b/t/app/controller/admin.t
@@ -157,13 +157,13 @@ subtest 'check contact creation' => sub {
         email      => 'test@example.com',
         note       => 'test note',
         non_public => undef,
-        confirmed  => 0,
+        state => 'unconfirmed',
     } } );
 
     $mech->content_contains( 'test category' );
     $mech->content_contains( 'test@example.com' );
     $mech->content_contains( '<td>test note' );
-    $mech->content_contains( 'Private:&nbsp;No' );
+    $mech->content_like( qr/<td>\s*unconfirmed\s*<\/td>/ ); # No private
 
     $mech->submit_form_ok( { with_fields => { 
         category   => 'private category',
@@ -173,7 +173,7 @@ subtest 'check contact creation' => sub {
     } } );
 
     $mech->content_contains( 'private category' );
-    $mech->content_contains( 'Private:&nbsp;Yes' );
+    $mech->content_like( qr{test\@example.com\s*</td>\s*<td>\s*confirmed\s*<br>\s*<small>\s*Private\s*</small>\s*</td>} );
 
     $mech->submit_form_ok( { with_fields => {
         category => 'test/category',
@@ -195,9 +195,8 @@ subtest 'check contact editing' => sub {
     } } );
 
     $mech->content_contains( 'test category' );
-    $mech->content_contains( 'test2@example.com' );
+    $mech->content_like( qr{test2\@example.com\s*</td>\s*<td>\s*unconfirmed\s*</td>} );
     $mech->content_contains( '<td>test2 note' );
-    $mech->content_contains( 'Private:&nbsp;No' );
 
     $mech->get_ok('/admin/body/' . $body->id . '/test%20category');
     $mech->submit_form_ok( { with_fields => {
@@ -210,14 +209,13 @@ subtest 'check contact editing' => sub {
     $mech->get_ok('/admin/body/' . $body->id . '/test%20category');
     $mech->content_contains( '<td><strong>test2@example.com,test3@example.com' );
 
-    $mech->get_ok('/admin/body/' . $body->id . '/test%20category');
     $mech->submit_form_ok( { with_fields => {
         email    => 'test2@example.com',
         note     => 'test2 note',
         non_public => 'on',
     } } );
 
-    $mech->content_contains( 'Private:&nbsp;Yes' );
+    $mech->content_like( qr{test2\@example.com\s*</td>\s*<td>\s*unconfirmed\s*<br>\s*<small>\s*Private\s*</small>\s*</td>} );
 
     $mech->get_ok('/admin/body/' . $body->id . '/test%20category');
     $mech->content_contains( '<td><strong>test2@example.com' );
@@ -225,7 +223,7 @@ subtest 'check contact editing' => sub {
 
 subtest 'check contact updating' => sub {
     $mech->get_ok('/admin/body/' . $body->id . '/test%20category');
-    $mech->content_like(qr{test2\@example.com</strong>[^<]*</td>[^<]*<td>No}s);
+    $mech->content_like(qr{test2\@example.com</strong>[^<]*</td>[^<]*<td>unconfirmed}s);
 
     $mech->get_ok('/admin/body/' . $body->id);
 
@@ -233,9 +231,9 @@ subtest 'check contact updating' => sub {
     $mech->tick( 'confirmed', 'test category' );
     $mech->submit_form_ok({form_number => 1});
 
-    $mech->content_like(qr'test2@example.com</td>[^<]*<td>\s*Confirmed:&nbsp;Yes's);
+    $mech->content_like(qr'test2@example.com</td>[^<]*<td>\s*confirmed's);
     $mech->get_ok('/admin/body/' . $body->id . '/test%20category');
-    $mech->content_like(qr{test2\@example.com[^<]*</td>[^<]*<td><strong>Yes}s);
+    $mech->content_like(qr{test2\@example.com[^<]*</td>[^<]*<td><strong>confirmed}s);
 };
 
 $body->update({ send_method => undef }); 
diff --git a/t/app/controller/dashboard.t b/t/app/controller/dashboard.t
index 9713d2d2c..9d424c1ae 100644
--- a/t/app/controller/dashboard.t
+++ b/t/app/controller/dashboard.t
@@ -59,9 +59,8 @@ FixMyStreet::override_config {
                 body_id    => $body->id,
                 category   => $contact,
                 email      => "$contact\@example.org",
-                confirmed  => 1,
+                state => 'confirmed',
                 whenedited => DateTime->now,
-                deleted    => 0,
                 editor     => 'test',
                 note       => 'test',
             }
diff --git a/t/app/controller/reports.t b/t/app/controller/reports.t
index 209266940..159095853 100644
--- a/t/app/controller/reports.t
+++ b/t/app/controller/reports.t
@@ -213,7 +213,7 @@ subtest "test greenwich all reports page" => sub {
             body_id => $body->id,
             category => 'Deleted',
             email => 'deleted@example.com',
-            deleted => 1
+            state => 'deleted',
         );
         ok $mech->host("greenwich.fixmystreet.com"), 'change host to greenwich';
         $mech->get_ok('/reports/Royal+Borough+of+Greenwich');
diff --git a/t/app/model/extra.t b/t/app/model/extra.t
index c073ae161..4e2a8ed37 100644
--- a/t/app/model/extra.t
+++ b/t/app/model/extra.t
@@ -13,8 +13,7 @@ sub get_test_contact {
         category => "Testing ${serial}",
         body => $body,
         email => 'test@example.com',
-        confirmed => 1,
-        deleted => 0,
+        state => 'confirmed',
         editor => 'test script',
         note => 'test script',
         whenedited => DateTime->now(),
diff --git a/t/app/model/problem.t b/t/app/model/problem.t
index 142262ae9..76b7c476a 100644
--- a/t/app/model/problem.t
+++ b/t/app/model/problem.t
@@ -425,7 +425,7 @@ for my $contact ( {
     category => 'Graffiti',
     email => 'highways@example.net',
 }, {
-    confirmed => 0,
+    state => 'unconfirmed',
     body_id => $body_ids{2636}, # Isle of Wight
     category => 'potholes',
     email => '2636@example.com',
diff --git a/t/app/sendreport/email.t b/t/app/sendreport/email.t
index 0cdf49378..718b0a495 100644
--- a/t/app/sendreport/email.t
+++ b/t/app/sendreport/email.t
@@ -56,7 +56,7 @@ foreach my $test ( {
 ) {
     subtest $test->{desc} => sub {
         my $e = FixMyStreet::SendReport::Email->new;
-        $contact->update( { confirmed => 0 } ) if $test->{unconfirmed};
+        $contact->update( { state => 'unconfirmed' } ) if $test->{unconfirmed};
         $contact->update( { note => $test->{note} } ) if $test->{note};
         $e->add_body( $body ) if $test->{add_council};
         is $e->build_recipient_list( $row, {} ), $test->{count}, 'correct recipient list count';
diff --git a/t/app/sendreport/open311.t b/t/app/sendreport/open311.t
index b60b199b0..8f933065c 100644
--- a/t/app/sendreport/open311.t
+++ b/t/app/sendreport/open311.t
@@ -167,10 +167,9 @@ sub test_overrides {
             $body->update({ can_be_devolved => 1 });
 
             my $contact = $body->contacts->find_or_create(
-                confirmed => 1,
+                state => 'confirmed',
                 email => 'ZZ',
                 category => 'ZZ',
-                deleted => 0,
                 editor => 'test suite',
                 note => '',
                 whenedited => DateTime->now,
diff --git a/t/cobrand/zurich.t b/t/cobrand/zurich.t
index be5dcbb48..64d754663 100644
--- a/t/cobrand/zurich.t
+++ b/t/cobrand/zurich.t
@@ -796,8 +796,7 @@ subtest "photo must be supplied for categories that require it" => sub {
         body => $division,
         category => "Graffiti - photo required",
         email => "graffiti\@example.org",
-        confirmed => 1,
-        deleted => 0,
+        state => 'confirmed',
         editor => "editor",
         whenedited => DateTime->now(),
         note => "note for graffiti",
diff --git a/t/open311/populate-service-list.t b/t/open311/populate-service-list.t
index 3ebe8e535..04740a9e8 100644
--- a/t/open311/populate-service-list.t
+++ b/t/open311/populate-service-list.t
@@ -49,8 +49,7 @@ subtest 'check non open311 contacts marked as deleted' => sub {
             body_id => 1,
             email =>   'contact@example.com',
             category => 'An old category',
-            confirmed => 1,
-            deleted => 0,
+            state => 'confirmed',
             editor => $0,
             whenedited => \'current_timestamp',
             note => 'test contact',
@@ -66,7 +65,7 @@ subtest 'check non open311 contacts marked as deleted' => sub {
     my $contact_count = FixMyStreet::DB->resultset('Contact')->search( { body_id => 1 } )->count();
     is $contact_count, 4, 'correct number of contacts';
 
-    $contact_count = FixMyStreet::DB->resultset('Contact')->search( { body_id => 1, deleted => 1 } )->count();
+    $contact_count = FixMyStreet::DB->resultset('Contact')->search( { body_id => 1, state => 'deleted' } )->count();
     is $contact_count, 1, 'correct number of deleted contacts';
 };
 
@@ -78,8 +77,7 @@ subtest 'check email changed if matching category' => sub {
             body_id => 1,
             email =>   '009',
             category => 'Cans left out 24x7',
-            confirmed => 1,
-            deleted => 0,
+            state => 'confirmed',
             editor => $0,
             whenedited => \'current_timestamp',
             note => 'test contact',
@@ -96,8 +94,7 @@ subtest 'check email changed if matching category' => sub {
 
     $contact->discard_changes;
     is $contact->email, '001', 'email unchanged';
-    is $contact->confirmed, 1, 'contact still confirmed';
-    is $contact->deleted, 0, 'contact still not deleted';
+    is $contact->state, 'confirmed', 'contact still confirmed';
 
     my $contact_count = FixMyStreet::DB->resultset('Contact')->search( { body_id => 1 } )->count();
     is $contact_count, 3, 'correct number of contacts';
@@ -111,8 +108,7 @@ subtest 'check category name changed if updated' => sub {
             body_id => 1,
             email =>   '001',
             category => 'Bins left out 24x7',
-            confirmed => 1,
-            deleted => 0,
+            state => 'confirmed',
             editor => $0,
             whenedited => \'current_timestamp',
             note => 'test contact',
@@ -130,8 +126,7 @@ subtest 'check category name changed if updated' => sub {
     $contact->discard_changes;
     is $contact->email, '001', 'email unchanged';
     is $contact->category, 'Cans left out 24x7', 'category changed';
-    is $contact->confirmed, 1, 'contact still confirmed';
-    is $contact->deleted, 0, 'contact still not deleted';
+    is $contact->state, 'confirmed', 'contact still confirmed';
 
     my $contact_count = FixMyStreet::DB->resultset('Contact')->search( { body_id => 1 } )->count();
     is $contact_count, 3, 'correct number of contacts';
@@ -145,8 +140,7 @@ subtest 'check conflicting contacts not changed' => sub {
             body_id => 1,
             email =>   'existing@example.com',
             category => 'Cans left out 24x7',
-            confirmed => 1,
-            deleted => 0,
+            state => 'confirmed',
             editor => $0,
             whenedited => \'current_timestamp',
             note => 'test contact',
@@ -160,8 +154,7 @@ subtest 'check conflicting contacts not changed' => sub {
             body_id => 1,
             email =>   '001',
             category => 'Bins left out 24x7',
-            confirmed => 1,
-            deleted => 0,
+            state => 'confirmed',
             editor => $0,
             whenedited => \'current_timestamp',
             note => 'test contact',
@@ -179,14 +172,12 @@ subtest 'check conflicting contacts not changed' => sub {
     $contact->discard_changes;
     is $contact->email, 'existing@example.com', 'first contact email unchanged';
     is $contact->category, 'Cans left out 24x7', 'first contact category unchanged';
-    is $contact->confirmed, 1, 'first contact contact still confirmed';
-    is $contact->deleted, 0, 'first contact contact still not deleted';
+    is $contact->state, 'confirmed', 'first contact still confirmed';
 
     $contact2->discard_changes;
     is $contact2->email, '001', 'second contact email unchanged';
     is $contact2->category, 'Bins left out 24x7', 'second contact category unchanged';
-    is $contact2->confirmed, 1, 'second contact contact still confirmed';
-    is $contact2->deleted, 0, 'second contact contact still not deleted';
+    is $contact2->state, 'confirmed', 'second contact still confirmed';
 
     my $contact_count = FixMyStreet::DB->resultset('Contact')->search( { body_id => 1 } )->count();
     is $contact_count, 4, 'correct number of contacts';
@@ -217,8 +208,7 @@ subtest 'check meta data population' => sub {
             body_id => 1,
             email =>   '001',
             category => 'Bins left out 24x7',
-            confirmed => 1,
-            deleted => 0,
+            state => 'confirmed',
             editor => $0,
             whenedited => \'current_timestamp',
             note => 'test contact',
@@ -397,8 +387,7 @@ for my $test (
                 body_id => 1,
                 email =>   '100',
                 category => 'Cans left out 24x7',
-                confirmed => 1,
-                deleted => 0,
+                state => 'confirmed',
                 editor => $0,
                 whenedited => \'current_timestamp',
                 note => 'test contact',
@@ -471,8 +460,7 @@ subtest 'check attribute ordering' => sub {
             body_id => 1,
             email =>   '001',
             category => 'Bins left out 24x7',
-            confirmed => 1,
-            deleted => 0,
+            state => 'confirmed',
             editor => $0,
             whenedited => \'current_timestamp',
             note => 'test contact',
@@ -573,8 +561,7 @@ subtest 'check bromely skip code' => sub {
             body_id => 1,
             email =>   '001',
             category => 'Bins left out 24x7',
-            confirmed => 1,
-            deleted => 0,
+            state => 'confirmed',
             editor => $0,
             whenedited => \'current_timestamp',
             note => 'test contact',
diff --git a/templates/web/base/admin/body.html b/templates/web/base/admin/body.html
index 5e8c6a164..82032c0c3 100644
--- a/templates/web/base/admin/body.html
+++ b/templates/web/base/admin/body.html
@@ -70,7 +70,7 @@
   <table cellspacing="0" cellpadding="2" border="1">
     <tr>
       <th>[% loc('Category') %]</th>
-      <th colspan=2>[% loc('State') %]</th>
+      <th>[% loc('State') %]</th>
       <th>[% loc('Last editor') %]</th>
       <th>[% loc('Note') %]</th>
     [% IF any_not_confirmed %]
@@ -78,20 +78,20 @@
     [% END %]
     </tr>
     [% WHILE ( cat = contacts.next ) %]
-      <tr [% IF cat.deleted %]class="is-deleted"[% END %]>
+      <tr [% IF cat.state == 'deleted' %]class="is-deleted"[% END %]>
         <td class="contact-category"><a href="[% c.uri_for( 'body', body_id, cat.category ) %]">[% cat.category | html %]</a>
             <br>[% cat.email | html %]</td>
         <td>
-            [% loc('Confirmed') %]:&nbsp;
-            [%- IF cat.confirmed %][% loc('Yes') %][% ELSE %][% loc('No') %][% END %]
-            <br>[% loc('Deleted') %]:&nbsp;
-            [%- IF cat.deleted %]<strong>[% loc('Yes') %]</strong>[% ELSE %][% loc('No') %][% END %]
-        </td>
-        <td>
-            [% loc('Private') %]:&nbsp;
-            [%- cat.non_public ? loc('Yes') : loc('No') %]
-            <br>[% loc('Devolved') %]:&nbsp;
-            [%- IF body.can_be_devolved AND cat.send_method %][% loc('Yes') %][% ELSE %][% loc('No') %][% END %]
+            [% cat.state %]
+          [% IF cat.non_public OR (body.can_be_devolved AND cat.send_method) %]
+            <br><small>
+            [% IF cat.non_public %][% loc('Private') %][% END %]
+              [% IF cat.non_public AND (body.can_be_devolved AND cat.send_method) %]
+                /
+              [% END %]
+            [% IF body.can_be_devolved AND cat.send_method %][% loc('Devolved') %][% END %]
+            </small>
+          [% END %]
         </td>
         <td>[% cat.editor | html %]
             <br><small>[% PROCESS format_time time=cat.whenedited %]</small></td>
diff --git a/templates/web/base/admin/category_edit.html b/templates/web/base/admin/category_edit.html
index 5eb3c943e..ea3fbaa79 100644
--- a/templates/web/base/admin/category_edit.html
+++ b/templates/web/base/admin/category_edit.html
@@ -51,8 +51,7 @@
     <tr>
         <th>[% loc('When edited') %]</th>
         <th>[% loc('Email') %]</th>
-        <th>[% loc('Confirmed') %]</th>
-        <th>[% loc('Deleted') %]</th>
+        <th>[% loc('State') %]</th>
         <th>[% loc('Editor') %]</th>
         <th>[% loc('Note') %]</th>
     </tr>
@@ -61,8 +60,7 @@
     <tr>
         <td>[% contact.whenedited.ymd _ ' ' _ contact.whenedited.hms %]</td>
         <td>[% PROCESS highlightchanged old=prev new=contact value='email' %]</td>
-        <td>[% PROCESS highlightchanged_yesno old=prev new=contact value='confirmed' %]</td>
-        <td>[% PROCESS highlightchanged_yesno old=prev new=contact value='deleted' %]</td>
+        <td>[% PROCESS highlightchanged old=prev new=contact value='state' %]</td>
         <td>[% contact.editor | html %]</td>
         <td>[% contact.note | html %]</td>
     </tr>
diff --git a/templates/web/base/admin/contact-form.html b/templates/web/base/admin/contact-form.html
index 493d8020f..0694459cb 100644
--- a/templates/web/base/admin/contact-form.html
+++ b/templates/web/base/admin/contact-form.html
@@ -38,24 +38,23 @@
 
   <div class="admin-hint">
     <p>
-        [% loc("Check <strong>confirmed</strong> to indicate that this contact has been confirmed as correct.
-                If you are not sure of the origin or validity of the contact, leave this unchecked.") %]
+        [%
+loc("Use <strong>confirmed</strong> to indicate that this contact has been
+confirmed as correct. If you are not sure of the origin or validity of the
+contact, use <strong>unconfirmed</strong>. <strong>inactive</strong> will
+remove the category from use when reporting problems, but keep it available in
+map filters, and <strong>deleted</strong> will remove the category from there
+as well.") %]
     </p>
   </div>
   <p>
-    <input type="checkbox" name="confirmed" value="1" id="confirmed"[% ' checked' IF contact.confirmed OR contact.confirmed == "" %]>
-    <label for="confirmed" class="inline">[% loc('Confirmed') %]</label>
-  </p>
-
-  <div class="admin-hint">
-    <p>
-      [% loc("Check <strong>deleted</strong> to remove the category from use. 
-        It will not appear as an available category in the drop-down menu on the report-a-problem page.") %]
-    </p>
-  </div>
-  <p>
-    <input type="checkbox" name="deleted" value="1" id="deleted"[% ' checked' IF contact.deleted %]>
-    <label for="deleted" class="inline">[% loc('Deleted') %]</label>
+    <label for="state">[% loc('State') %]</label>
+    <select name="state" id="state">
+      <option value="unconfirmed"[% ' selected' IF contact.state == 'unconfirmed' %]>[% loc('Unconfirmed') %]
+      <option value="confirmed"[% ' selected' IF contact.state == 'confirmed' || contact.state == "" %]>[% loc('Confirmed') %]
+      <option value="inactive"[% ' selected' IF contact.state == 'inactive' %]>[% loc('Inactive') %]
+      <option value="deleted"[% ' selected' IF contact.state == 'deleted' %]>[% loc('Deleted') %]
+    </select>
   </p>
 
   <div class="admin-hint">
diff --git a/templates/web/base/admin/council_contacts.txt b/templates/web/base/admin/council_contacts.txt
index 2d1e04bfa..ffab83ab6 100644
--- a/templates/web/base/admin/council_contacts.txt
+++ b/templates/web/base/admin/council_contacts.txt
@@ -1,4 +1,4 @@
 [% WHILE ( contact = contacts.next ) -%]
-[%- NEXT IF contact.deleted || ! contact.confirmed %]
+[%- NEXT IF contact.state != 'confirmed' %]
 [% contact.category %]	[% contact.email %]
 [%- END %]
diff --git a/templates/web/base/status/stats.html b/templates/web/base/status/stats.html
index 0f201920d..02f9de904 100644
--- a/templates/web/base/status/stats.html
+++ b/templates/web/base/status/stats.html
@@ -10,8 +10,8 @@
     questionnaires_1 = questionnaires.1 | format_number;
     total_bodies = bodies.size | format_number;
     contacts_total = contacts.total | format_number;
-    contacts_1 = contacts.1 | format_number;
-    contacts_0 = contacts.0 | format_number;
+    contacts_confirmed = contacts.confirmed | format_number;
+    contacts_unconfirmed = contacts.unconfirmed | format_number;
 -%]
 
 <ul>
@@ -24,5 +24,5 @@
     <li>[% tprintf( loc('%s confirmed alerts, %s unconfirmed'), decode(alerts_1), decode(alerts_0)) %]</li>
     <li>[% tprintf( loc('%s questionnaires sent &ndash; %s answered (%s%%)'), decode(questionnaires_total), decode(questionnaires_1), questionnaires_pc) %]</li>
     <li>[% tprintf( loc('%s bodies'), decode(total_bodies)) %],
-        [% tprintf( loc('%s council contacts &ndash; %s confirmed, %s unconfirmed'), decode(contacts_total), decode(contacts_1), decode(contacts_0)) %]</li>
+        [% tprintf( loc('%s council contacts &ndash; %s confirmed, %s unconfirmed'), decode(contacts_total), decode(contacts_confirmed), decode(contacts_unconfirmed)) %]</li>
 </ul>
diff --git a/templates/web/zurich/admin/contact-form.html b/templates/web/zurich/admin/contact-form.html
index dddfc6d01..aaf7a1797 100644
--- a/templates/web/zurich/admin/contact-form.html
+++ b/templates/web/zurich/admin/contact-form.html
@@ -7,9 +7,11 @@
     <input type="text" class="form-control" name="email" value="[% contact.email | html %]" size="30">
 
     <p>
-        <input type="hidden" name="confirmed" value="1">
-        <input type="checkbox" name="deleted" value="1" id="deleted"[% ' checked' IF contact.deleted %]>
-        <label class="inline" for="deleted">[% loc('Deleted') %]</label>
+        <label for="state">[% loc('State') %]</label>
+        <select name="state" id="state">
+          <option value="confirmed"[% ' selected' IF contact.state == 'confirmed' %]>[% loc('Confirmed') %]
+          <option value="deleted"[% ' selected' IF contact.state == 'deleted' %]>[% loc('Deleted') %]
+        </select>
         <input type="checkbox" name="photo_required" value="1" id="photo_required"[% ' checked' IF contact.get_extra_metadata('photo_required') %]>
         <label class="inline" for="photo_required">[% loc('Photo required') %]</label>
     </p>
diff --git a/templates/web/zurich/admin/index.html b/templates/web/zurich/admin/index.html
index 275f50183..fb3609bb3 100644
--- a/templates/web/zurich/admin/index.html
+++ b/templates/web/zurich/admin/index.html
@@ -3,7 +3,7 @@
 <ul>
     <li>[% tprintf( loc('<strong>%s</strong> live problems'), total_problems_live ) %];
     [% tprintf( loc('from %s different users'), total_problems_users ) %]</li>
-    <li>[% tprintf( loc('%s council contacts &ndash; %s confirmed, %s unconfirmed'), contacts.total, contacts.1, contacts.0) %]</li>
+    <li>[% tprintf( loc('%s council contacts &ndash; %s confirmed, %s unconfirmed'), contacts.total, contacts.confirmed, contacts.unconfirmed) %]</li>
 </ul>
 
 <h2>[% loc('Problem breakdown by state') %]</h2>