--
-- This is a log of the changes performed to setup the new board 2015.
--
-- Author: Jan Dittberner
-- Date:   2016-04-10
--
-- The steps in this log have been executed in the webmail container with
-- sqlite3 /var/www/board/database.sqlite
--

-- Date: 2015-11-27
-- find enabled voters
select * from voters where enabled=1;

-- disable voters not part of the board anymore
update voters set enabled=0 where id in (4, 5, 19, 23, 29, 30);

-- insert new board
insert into voters(enabled,name,reminder) values (1,'Peter Yuill','peter@cacert.org');
insert into voters(enabled,name,reminder) values (1,'Marcus Mängel','inopiae@cacert.org');
insert into voters(enabled,name,reminder) values (1,'Stefan Thode','stefan.thode@cacert.org');
insert into voters(enabled,name,reminder) values (1,'Reinhard Mutz','reinhard@cacert.org');
insert into voters(enabled,name,reminder) values (1,'Felix Dörre','felix.doerre@cacert.org');

-- find voter ids for new board
select * from voters where enabled=1;

insert into emails (voter,address) values (31, 'peter@cacert.org');
insert into emails (voter,address) values (32, 'inopiae@cacert.org');
insert into emails (voter,address) values (33, 'stefan.thode@cacert.org');
insert into emails (voter,address) values (34, 'reinhard@cacert.org');
insert into emails (voter,address) values (35, 'felix.doerre@cacert.org');

-- Date: 2016-04-10
-- find enabled voters
select * from voters where enabled=1;

-- disable voters not part of the board anymore, keep Benjamin Ball (id 29)
update voters set enabled=0 where id in (4, 27, 31, 32, 33, 34, 35);

-- enable voting rights for new board
-- Ian Grigg has id 12
-- Piers Lauder has id 18
-- Dirk Astrath has id 19
update voters set enabled=1 where id in (12, 18, 19);

-- add board members that are not in the voting system yet
insert into voters (enabled, name, reminder) values (1, 'Gero Treuner', 'gero.treuner@cacert.org');
insert into voters (enabled, name, reminder) values (1, 'Kevin Dawson', 'kevin.dawson@cacert.org');
insert into voters (enabled, name, reminder) values (1, 'Mathias Subik', 'mathias.subik@cacert.org');

-- find voter ids for new board
select * from voters where enabled=1;

-- insert email address mapping for new voters
insert into emails (voter, address) values (36, 'gero.treuner@cacert.org');
insert into emails (voter, address) values (37, 'kevin.dawson@cacert.org');
insert into emails (voter, address) values (38, 'mathias.subik@cacert.org');

-- Date: 2016-12-27
-- find enabled voters
select * from voters where enabled=1;

-- disable voters not part of the board anymore, keep Gero Treuner and Dirk Astrath (id 36, id 19)
update voters set enabled=0 where id in (12, 18, 29, 37, 38);

-- find existing accounts for Brian Mc Cullough and Etienne Ruedin
select * from voters where name like 'Brian%' or name like 'Etienne%';

-- Etienne has an account with id 30, Brian is not in the system yet
-- enable Etienne Ruedin
update voters set enabled=1 where id=30;

-- add Brian Mc Cullough
insert into voters (name, enabled, reminder) values ('Brian Mc Cullough', 1, 'brian.mccullough@cacert.org');

-- find voter ids for new board
select * from voters where enabled=1;

-- insert email address mapping for Brian
insert into emails (voter, address) values (39, 'brian.mccullough@cacert.org');

-- Date: 2017-08-10
-- add new board members
insert into voters (name, enabled, reminder) values ('Alex Samad', 1, 'a.samad@cacert.org');
insert into voters (name, enabled, reminder) values ('Ross Krumbholz', 1, 'r.krumbholz@cacert.org');
insert into voters (name, enabled, reminder) values ('Peter Nunn', 1, 'p.nunn@cacert.org');

-- find voter ids for new board members
select * from voters where enabled=1;

-- insert email addresses for authentication
insert into emails (voter, address) values (40, 'a.samad@cacert.org');
insert into emails (voter, address) values (41, 'r.krumbholz@cacert.org');
insert into emails (voter, address) values (42, 'p.nunn@cacert.org');