tidy up status names in DB, improve tests a little

5 files changed, 111 insertions, 11 deletions

diff --git a/db/schema.sql b/db/schema.sql
index 60ad8eda4..d714a463b 100644
--- a/db/schema.sql
+++ b/db/schema.sql
@@ -182,7 +182,7 @@ create table problem (
         or state = 'partial'
         or state = 'action scheduled'
         or state = 'unable to fix'
-        or state = 'not councils responsibility'
+        or state = 'not responsible'
         or state = 'duplicate'
     ),
     lang text not null default 'en-gb',
@@ -318,7 +318,7 @@ create table comment (
         or problem_state = 'fixed - user'
         or problem_state = 'action scheduled'
         or problem_state = 'unable to fix'
-        or problem_state = 'not councils responsibility'
+        or problem_state = 'not responsible'
         or problem_state = 'duplicate'
     ),
     -- other fields? one to indicate whether this was written by the council
diff --git a/db/schema_0025-add_more_statuses_to_problem.sql b/db/schema_0025-add_more_statuses_to_problem.sql
new file mode 100644
index 000000000..300f12619
--- /dev/null
+++ b/db/schema_0025-add_more_statuses_to_problem.sql
@@ -0,0 +1,75 @@
+BEGIN;
+
+    ALTER TABLE problem DROP CONSTRAINT problem_state_check;
+
+    ALTER TABLE problem ADD CONSTRAINT problem_state_check CHECK (
+        state = 'unconfirmed'
+        or state = 'confirmed'
+        or state = 'investigating'
+        or state = 'planned'
+        or state = 'in progress'
+        or state = 'action scheduled'
+        or state = 'closed'
+        or state = 'fixed'
+        or state = 'fixed - council'
+        or state = 'fixed - user'
+        or state = 'hidden'
+        or state = 'partial'
+        or state = 'unable to fix'
+        or state = 'not responsible'
+        or state = 'duplicate'
+    );
+
+
+    ALTER TABLE comment DROP CONSTRAINT problem_state_check;
+
+    ALTER TABLE comment ADD CONSTRAINT problem_state_check CHECK (
+        problem_state = 'confirmed'
+        or problem_state = 'investigating'
+        or problem_state = 'planned'
+        or problem_state = 'in progress'
+        or problem_state = 'action scheduled'
+        or problem_state = 'closed'
+        or problem_state = 'fixed'
+        or problem_state = 'fixed - council'
+        or problem_state = 'fixed - user'
+        or problem_state = 'unable to fix'
+        or problem_state = 'not responsible'
+        or problem_state = 'duplicate'
+    );
+
+    UPDATE alert_type set item_where = 'nearby.problem_id = problem.id and problem.state in
+    (''confirmed'', ''investigating'', ''planned'', ''in progress'',
+     ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'',
+     ''action scheduled'', ''not responsible'', ''duplicate'', ''unable to fix'' )'
+     WHERE ref = 'postcode_local_problems';
+    UPDATE alert_type set item_where = 'problem.state in
+        (''confirmed'', ''investigating'', ''planned'', ''in progress'',
+         ''fixed'', ''fixed - council'', ''fixed - user'', ''closed''
+         ''action scheduled'', ''not responsible'', ''duplicate'', ''unable to fix'' )'
+        WHERE ref = 'new_problems';
+    UPDATE alert_type set item_where = 'problem.state in (''fixed'', ''fixed - user'', ''fixed - council'')' WHERE ref = 'new_fixed_problems';
+    UPDATE alert_type set item_where = 'nearby.problem_id = problem.id and problem.state in
+    (''confirmed'', ''investigating'', ''planned'', ''in progress'',
+     ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'',
+     ''action scheduled'', ''not responsible'', ''duplicate'', ''unable to fix'' )'
+    WHERE ref = 'local_problems';
+    UPDATE alert_type set item_where = 'problem.state in
+    (''confirmed'', ''investigating'', ''planned'', ''in progress'',
+      ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'',
+     ''action scheduled'', ''not responsible'', ''duplicate'', ''unable to fix'' ) AND
+    (council like ''%''||?||''%'' or council is null) and
+    areas like ''%,''||?||'',%''' WHERE ref = 'council_problems';
+    UPDATE alert_type set item_where = 'problem.state in
+    (''confirmed'', ''investigating'', ''planned'', ''in progress'',
+     ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'',
+     ''action scheduled'', ''not responsible'', ''duplicate'', ''unable to fix'' ) AND
+    (council like ''%''||?||''%'' or council is null) and
+    areas like ''%,''||?||'',%''' WHERE ref = 'ward_problems';
+    UPDATE alert_type set item_where = 'problem.state in
+    (''confirmed'', ''investigating'', ''planned'', ''in progress'',
+     ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'',
+     ''action scheduled'', ''not responsible'', ''duplicate'', ''unable to fix'' ) AND
+    areas like ''%,''||?||'',%''' WHERE ref = 'area_problems';
+
+COMMIT;
diff --git a/perllib/FixMyStreet/DB/Result/Problem.pm b/perllib/FixMyStreet/DB/Result/Problem.pm
index 27613c1c8..24d6d3966 100644
--- a/perllib/FixMyStreet/DB/Result/Problem.pm
+++ b/perllib/FixMyStreet/DB/Result/Problem.pm
@@ -190,7 +190,6 @@ sub open_states {
         'investigating'    => 1,
         'planned'          => 1,
         'in progress'      => 1,
-        'in progress'      => 1,
         'action scheduled' => 1,
     };
 
@@ -231,7 +230,7 @@ sub closed_states {
     my $states = {
         'closed'                      => 1,
         'unable to fix'               => 1,
-        'not councils responsibility' => 1,
+        'not responsible'             => 1,
         'duplicate'                   => 1,
     };
 
@@ -260,7 +259,7 @@ sub visible_states {
         'fixed - council'             => 1,
         'fixed - user'                => 1,
         'unable to fix'               => 1,
-        'not councils responsibility' => 1,
+        'not responsible'             => 1,
         'duplicate'                   => 1,
         'closed'                      => 1,
     };
@@ -292,7 +291,7 @@ sub all_states {
         'fixed - council'             => 1,
         'fixed - user'                => 1,
         'unable to fix'               => 1,
-        'not councils responsibility' => 1,
+        'not responsible'             => 1,
         'duplicate'                   => 1,
         'closed'                      => 1,
     };
@@ -318,7 +317,7 @@ sub council_states {
         'action scheduled'            => 1,
         'fixed - council'             => 1,
         'unable to fix'               => 1,
-        'not councils responsibility' => 1,
+        'not responsible'             => 1,
         'duplicate'                   => 1,
     };
 
diff --git a/perllib/Open311/GetServiceRequestUpdates.pm b/perllib/Open311/GetServiceRequestUpdates.pm
index 2af128ec7..c9bb7a91b 100644
--- a/perllib/Open311/GetServiceRequestUpdates.pm
+++ b/perllib/Open311/GetServiceRequestUpdates.pm
@@ -99,6 +99,8 @@ sub update_comments {
                     my $incoming_status = lc( $request->{status} );
                     my $internal_status = $incoming_status;
                     $internal_status =~ s/_/ /g;
+
+                    $internal_status = 'not responsible' if $internal_status eq 'not councils responsibility';
                     if ( $p->is_open and ( $incoming_status eq 'closed' or $incoming_status eq 'fixed' ) ) {
                         $p->state( 'fixed - council' );
                         $comment->problem_state( 'fixed - council' );
diff --git a/t/open311/getservicerequestupdates.t b/t/open311/getservicerequestupdates.t
index 5ec2b5c26..9f67633aa 100644
--- a/t/open311/getservicerequestupdates.t
+++ b/t/open311/getservicerequestupdates.t
@@ -165,6 +165,30 @@ for my $test (
         end_state => 'action scheduled',
     },
     {
+        desc => 'not responsible status changes problem status',
+        updated_datetime => sprintf( '<updated_datetime>%s</updated_datetime>', $dt ),
+        description => 'This is a note',
+        external_id => 638344,
+        start_state => 'confirmed',
+        comment_status => 'NOT_COUNCILS_RESPONSIBILITY',
+        mark_fixed=> 0,
+        mark_open => 0,
+        problem_state => 'not responsible',
+        end_state => 'not responsible',
+    },
+    {
+        desc => 'duplicate status changes problem status',
+        updated_datetime => sprintf( '<updated_datetime>%s</updated_datetime>', $dt ),
+        description => 'This is a note',
+        external_id => 638344,
+        start_state => 'confirmed',
+        comment_status => 'DUPLICATE',
+        mark_fixed=> 0,
+        mark_open => 0,
+        problem_state => 'duplicate',
+        end_state => 'duplicate',
+    },
+    {
         desc => 'comment closes report',
         updated_datetime => sprintf( '<updated_datetime>%s</updated_datetime>', $dt ),
         description => 'This is a note',
@@ -193,7 +217,7 @@ for my $test (
         updated_datetime => sprintf( '<updated_datetime>%s</updated_datetime>', $dt ),
         description => 'This is a note',
         external_id => 638344,
-        start_state => 'closed',
+        start_state => 'fixed - user',
         comment_status => 'OPEN',
         mark_fixed => 0,
         mark_open => 0,
@@ -205,11 +229,11 @@ for my $test (
         updated_datetime => sprintf( '<updated_datetime>%s</updated_datetime>', $dt ),
         description => 'This is a note',
         external_id => 638344,
-        start_state => 'closed',
-        comment_status => 'CLOSED',
+        start_state => 'fixed - user',
+        comment_status => 'FIXED',
         mark_fixed => 0,
         mark_open => 0,
-        end_state => 'closed',
+        end_state => 'fixed - user',
     },
 ) {
     subtest $test->{desc} => sub {