1 files changed, 45 insertions, 0 deletions
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;