stv-sample.sql

--

-- set up several elections for test purposes

insert into elections
       values (1, 'Magic Roundabout', 5),
       	      (2, 'Wombles', 2),
	 		  (3, 'Testcase - no votes at all', 3),
      	      (4, 'Testcase - exhaustion', 4),
              (5, 'Testcase - no first prefs', 2),
			  (6, 'An actual election', 3);

insert into candidates(election_id, candidate_id, candidate_name)
select 1, ord, n
  from unnest(array['Dougal', 'Florence', 'Zebedee', 'Brian',
                    'Ermintrude', 'Dylan', 'Basil', 'Mr Rusty',
                    'Mr McHenry', 'Zeebad', 'Soldier Sam'])
         with ordinality as u(n,ord);

insert into candidates(election_id, candidate_id, candidate_name)
select 2, ord, n
  from unnest(array['Great Uncle Bulgaria', 'Orinoco', 'Tobermory',
       		    	'Bungo', 'Wellington', 'Madame Cholet', 'Tomsk',
		    		'Cousin Cairngorm'])
         with ordinality as u(n,ord);

insert into candidates(election_id, candidate_id, candidate_name)
select 3, ord, n
  from unnest(array['Fred', 'Jim', 'Sheila'])
         with ordinality as u(n,ord);

insert into candidates(election_id, candidate_id, candidate_name)
select 4, ord, n
  from unnest(array['Kermit', 'Fozzie', 'Miss Piggy', 'Gonzo',
  	   				'Statler', 'Waldorf', 'Swedish Chef'])
         with ordinality as u(n,ord);

insert into candidates(election_id, candidate_id, candidate_name)
select 5, ord, n
  from unnest(array['Apples', 'Pears', 'Bananas', 'Oranges', 'Lemons'])
         with ordinality as u(n,ord);

insert into candidates(election_id, candidate_id, candidate_name)
select 6, ord, n
  from unnest(array['A', 'B', 'C', 'D'])
         with ordinality as u(n,ord);

-- precast lots for each election
-- one has to be careful with the array() call here, if it's not forced
-- to be correlated to the outer table, it'll be computed only once and
-- reused for every candidate. Hence the use of 0*candidate_id

prepare cast_lots(integer) as
update candidates c
   set precast_lots = array(select random()
                              from generate_series(1+0*c.candidate_id,
                                                   1+(select count(*)
												        from candidates c1
													   where c1.election_id = $1)))
 where election_id = $1;

execute cast_lots(1);
execute cast_lots(2);
execute cast_lots(3);
execute cast_lots(4);
execute cast_lots(5);
execute cast_lots(6);

-- insert_votes(election_id, '[[...],[...],[...],...]')
prepare insert_votes(integer, json) as
insert into ballot_preferences(voter_id,
                               election_id,
							   candidate_id,
							   preference)
select voter_ord,
       $1,
	   candidate_ord,
	   pref::integer
  from json_array_elements($2) with ordinality as a1(prefs, voter_ord),
       json_array_elements_text(prefs) with ordinality as a2(pref, candidate_ord);

-- there are 3 invalid ballots in here
execute insert_votes(1, '[
 [1,2,3,4,5,6,7,8,9,10,11],
 [1,2,3,7,4,null,6,5,null,null,null],
 [2,5,9,4,1,8,6,null,3,7,10],
 [3,2,6,7,null,4,9,8,null,1,5],
 [1,4,5,8,2,6,7,3,10,9,null],
 [4,1,5,6,2,7,8,9,null,null,3],
 [4,5,7,1,null,6,null,8,3,2,null],
 [3,6,7,4,1,5,8,10,2,9,null],
 [1,7,3,null,6,2,9,null,4,8,5],
 [2,1,null,null,null,null,null,null,null,null,null],
 [2,1,null,3,null,null,null,null,null,null,null],
 [3,4,2,6,1,5,7,8,null,9,10],
 [1,3,4,6,7,8,null,5,null,null,2],
 [2,4,8,null,1,7,3,5,null,6,null],
 [2,1,3,null,null,null,null,6,4,null,5],
 [2,null,null,null,null,null,null,1,null,null,null],
 [1,5,6,8,9,4,2,7,3,null,null],
 [1,4,6,2,5,8,null,3,9,7,null],
 [3,4,5,2,7,8,1,6,10,null,9],
 [2,4,1,7,9,6,null,5,8,3,null],
 [1,3,7,4,9,6,10,5,8,null,2],
 [2,1,6,7,4,5,3,8,10,null,9],
 [1,4,6,3,8,7,null,null,2,9,5],
 [1,3,5,null,6,null,null,2,4,null,null],
 [2,3,4,1,6,7,8,5,null,null,null],
 [1,2,null,3,null,5,null,null,4,null,null],
 [2,4,7,8,3,5,1,9,6,null,null],
 [2,6,4,3,9,7,8,null,1,10,5],
 [1,3,4,6,7,9,2,null,8,5,null],
 [2,7,5,4,null,3,6,1,null,null,null],
 [1,2,null,null,null,3,null,null,null,null,null],
 [1,2,4,null,null,null,null,null,null,3,null],
 [3,2,5,7,8,4,6,9,10,null,1],
 [4,3,5,null,1,null,2,null,6,null,null],
 [1,2,4,3,7,6,8,5,null,null,null],
 [1,4,3,2,null,6,null,null,5,null,null],
 [2,3,5,1,7,9,10,8,6,null,4],
 [3,4,1,5,2,7,6,null,null,null,null],
 [1,4,7,5,null,2,null,9,8,6,3],
 [3,1,8,5,7,9,2,null,4,6,null],
 [2,3,5,null,1,null,null,null,6,null,4],
 [2,1,3,4,6,7,8,5,null,null,9],
 [4,2,5,6,8,3,10,9,1,null,7],
 [1,3,4,5,6,2,null,null,null,null,null],
 [1,2,4,5,null,7,null,3,null,null,6],
 [1,2,5,9,8,4,6,7,null,3,10],
 [2,null,null,null,null,null,1,null,null,null,null],
 [2,5,3,6,4,7,null,null,null,null,1],
 [null,1,null,null,null,null,null,null,null,null,null],
 [2,3,4,6,5,7,8,9,null,10,1],
 [3,2,5,6,4,7,null,null,1,null,null],
 [2,3,1,7,8,5,6,4,9,null,10],
 [3,null,2,null,null,null,null,null,1,4,null],
 [4,1,5,8,2,3,7,6,null,9,10],
 [2,3,6,4,1,7,null,5,null,null,null],
 [4,5,7,1,3,8,2,null,null,6,null],
 [3,5,1,2,6,8,7,4,null,9,10],
 [2,3,4,1,null,null,null,null,null,null,null],
 [4,7,3,2,6,1,null,null,5,null,null],
 [3,1,2,6,8,7,4,5,null,null,null],
 [4,2,6,3,7,8,9,null,null,5,1],
 [2,1,4,null,null,5,null,null,3,null,6],
 [3,1,6,2,8,4,5,null,7,null,null],
 [2,4,6,3,8,1,5,7,null,null,null],
 [3,5,4,2,8,7,6,9,null,1,null],
 [1,3,null,2,5,4,null,6,null,null,null],
 [1,2,6,4,7,9,8,3,null,5,10],
 [2,1,3,6,7,5,4,8,9,10,null],
 [3,5,4,1,2,7,6,9,10,null,8],
 [2,4,6,7,5,8,9,10,null,3,1],
 [2,5,6,7,8,1,9,4,3,null,null],
 [1,2,3,null,5,null,7,null,4,null,6],
 [2,null,7,null,null,4,6,5,3,1,null],
 [1,3,7,8,null,4,null,5,6,null,2],
 [2,3,5,7,null,null,8,6,4,1,null],
 [1,2,3,5,8,9,10,6,4,7,null],
 [3,4,6,7,9,10,2,8,null,1,5],
 [1,3,7,6,5,8,null,null,4,9,2],
 [1,6,3,2,5,4,null,null,null,null,null],
 [1,3,null,null,null,2,null,null,null,null,null],
 [2,1,4,5,7,null,6,3,null,null,null],
 [2,1,3,4,7,5,null,8,null,6,null],
 [3,4,7,5,1,null,null,null,2,6,null],
 [1,3,4,5,2,7,8,6,null,9,null],
 [3,2,6,1,null,null,7,null,4,null,5],
 [1,2,3,4,10,7,6,9,5,null,8],
 [1,4,7,3,10,9,6,5,null,8,2],
 [1,3,5,4,6,8,9,2,7,null,10],
 [1,2,3,6,5,8,7,9,null,null,4],
 [1,2,6,null,4,null,8,5,null,3,7],
 [2,1,5,4,3,6,null,9,8,10,7],
 [1,5,3,7,8,4,6,9,2,null,null],
 [1,3,2,4,7,9,6,10,5,null,8],
 [2,1,3,7,5,null,8,null,null,6,4],
 [1,3,null,null,null,null,null,null,2,null,null],
 [1,2,4,5,3,9,7,null,6,null,8],
 [3,null,null,null,null,4,null,null,1,2,null],
 [3,4,null,null,2,1,5,null,null,null,null],
 [2,1,5,6,3,null,8,4,null,7,9],
 [4,1,6,8,2,7,9,10,3,5,null],
 [2,7,4,1,6,8,null,3,5,null,null],
 [2,1,null,null,null,null,null,null,null,null,null],
 [4,5,7,2,null,6,3,1,null,null,null],
 [1,2,5,null,4,3,null,null,null,null,null],
 [1,2,4,6,7,8,3,5,10,null,9],
 [null,null,null,null,1,null,null,null,null,null,null],
 [2,1,6,3,5,10,8,4,9,7,null],
 [4,6,2,null,null,1,3,null,null,null,5],
 [null,2,null,null,null,null,null,1,null,null,null],
 [3,5,7,null,1,8,null,6,4,2,9],
 [5,2,1,7,3,9,6,null,4,null,8],
 [2,3,6,5,1,null,null,null,null,null,4],
 [3,2,6,7,1,8,9,5,4,10,null],
 [1,2,6,4,9,3,null,7,8,null,5],
 [2,1,4,5,3,7,8,6,null,null,9],
 [3,1,null,2,6,null,5,null,null,4,null],
 [1,2,5,4,6,7,9,3,8,null,null],
 [5,4,2,1,6,9,3,7,null,null,8],
 [5,3,6,1,9,8,4,10,2,null,7],
 [3,4,5,1,null,2,null,null,6,null,null],
 [3,4,1,6,7,8,2,5,null,null,null],
 [2,4,null,null,5,null,3,null,null,1,null],
 [1,2,6,5,9,7,3,4,8,10,null],
 [2,4,3,7,1,5,6,null,null,8,null],
 [3,1,2,6,5,4,8,null,7,9,10],
 [1,2,6,null,null,7,4,null,3,8,5],
 [3,2,7,4,5,1,6,8,null,9,10],
 [5,6,8,4,7,2,9,1,null,3,10],
 [4,3,1,2,9,6,5,7,10,null,8],
 [4,null,3,null,5,1,null,null,null,2,null],
 [1,4,5,2,8,3,6,9,7,10,null],
 [2,6,4,7,8,3,9,10,1,5,null],
 [1,2,4,6,null,8,7,5,null,3,null],
 [4,2,8,6,5,3,7,9,null,1,null],
 [2,null,null,null,null,null,1,null,null,null,null],
 [1,3,4,2,5,8,6,7,null,10,9],
 [2,1,8,4,5,7,3,6,null,9,10],
 [1,2,3,7,6,8,5,4,9,null,10],
 [2,1,6,9,3,5,4,null,null,8,7],
 [2,3,4,5,7,null,null,6,null,8,1],
 [1,2,4,3,6,7,9,5,8,null,10],
 [2,3,6,5,1,null,7,null,4,8,null],
 [1,4,6,5,7,9,8,3,2,null,null],
 [2,1,6,3,8,null,null,9,7,4,5],
 [1,4,null,null,null,2,3,null,null,null,null],
 [1,2,4,7,6,5,null,3,9,8,null],
 [1,2,7,null,6,4,null,3,null,5,null],
 [3,null,4,1,null,null,null,5,2,null,null],
 [null,3,null,null,2,null,null,1,null,null,null],
 [1,3,4,2,7,8,9,5,null,6,null],
 [3,5,1,8,9,null,null,6,7,2,4],
 [2,3,4,9,1,5,6,10,7,8,null],
 [1,3,4,null,2,9,null,6,5,7,8],
 [2,1,7,9,4,6,3,null,8,5,null],
 [1,7,8,6,10,4,3,5,2,9,null],
 [3,4,null,1,null,5,6,2,null,null,null],
 [2,4,3,5,7,8,6,1,null,10,9],
 [3,4,6,2,1,7,10,null,9,8,5],
 [1,3,5,2,null,4,6,null,null,null,null],
 [1,3,4,null,5,null,null,2,null,null,null],
 [3,2,1,null,null,4,null,null,null,null,null],
 [1,4,2,6,9,8,10,3,5,7,null],
 [5,3,1,6,2,7,8,4,10,null,9],
 [3,1,4,5,7,8,9,2,6,null,null],
 [2,3,4,1,null,null,6,null,5,7,8],
 [1,2,4,7,8,6,null,null,3,null,5],
 [2,null,null,null,1,null,null,null,null,null,null],
 [2,1,5,3,7,10,9,6,8,4,null],
 [3,2,6,9,1,5,4,null,7,8,null],
 [null,null,null,1,null,2,null,null,null,3,null],
 [5,1,4,3,null,2,6,null,null,7,null],
 [null,null,null,null,null,null,null,null,null,null,null],
 [3,7,1,4,5,8,6,2,9,10,null],
 [2,3,1,null,null,4,null,5,6,null,null],
 [2,5,1,6,3,4,8,7,9,null,null],
 [1,2,4,null,null,null,3,null,null,null,null],
 [1,2,3,4,null,null,5,null,null,null,null],
 [1,4,null,5,3,null,null,null,2,null,null],
 [3,5,7,2,9,1,null,4,8,6,null],
 [2,1,null,null,null,4,5,3,null,null,null],
 [3,5,7,null,6,4,null,1,8,null,2],
 [2,4,3,6,null,5,1,null,null,null,null],
 [2,1,4,7,3,5,8,9,6,10,null],
 [2,3,6,1,8,4,9,7,5,10,null],
 [2,1,6,4,5,3,8,9,null,null,7],
 [3,4,5,7,6,null,2,null,null,1,null],
 [3,5,4,2,1,null,6,7,null,null,null],
 [2,1,5,9,8,4,7,6,null,null,3],
 [1,3,5,7,2,9,4,10,null,8,6],
 [1,5,4,6,2,null,8,null,null,7,3],
 [3,1,4,6,2,7,8,null,5,null,null],
 [1,4,5,6,null,null,8,3,7,null,2],
 [1,2,4,3,null,null,null,null,5,null,null],
 [5,6,1,3,4,null,2,null,null,null,null],
 [2,5,1,7,6,4,9,3,8,null,10],
 [2,5,6,8,1,3,9,4,7,10,null],
 [3,1,6,9,2,7,null,5,4,8,10],
 [1,7,4,3,5,2,8,10,9,null,6],
 [2,5,4,6,3,1,null,null,null,null,7],
 [2,1,3,5,4,7,6,8,null,null,9],
 [1,2,6,7,3,5,null,null,4,null,null],
 [3,null,2,null,1,null,null,null,null,null,null],
 [2,5,1,6,null,7,null,null,4,3,null],
 [2,3,null,null,4,null,null,null,null,null,1],
 [2,null,1,4,null,3,null,null,null,null,null],
 [3,4,null,5,null,null,1,2,null,null,null],
 [3,4,6,null,1,2,null,5,null,null,7],
 [2,4,6,5,9,7,3,10,1,null,8],
 [1,3,6,5,7,8,2,9,10,4,null],
 [1,2,6,7,4,9,8,null,5,3,10],
 [1,2,4,3,5,null,null,8,6,null,7],
 [1,5,3,4,2,6,7,null,null,null,8],
 [3,2,5,6,8,4,9,10,1,7,null],
 [1,4,3,5,2,8,7,6,10,9,null],
 [2,3,4,6,5,1,8,7,10,9,null],
 [1,2,5,4,8,null,3,6,null,7,null],
 [2,null,null,null,null,null,null,null,null,1,null],
 [2,1,3,6,4,null,8,5,9,null,7],
 [null,null,null,null,null,null,null,null,null,1,null],
 [2,1,4,3,7,8,6,9,5,10,null],
 [1,null,null,2,null,3,null,null,null,null,null],
 [2,3,4,6,7,5,10,1,9,8,null],
 [1,2,4,5,6,7,9,8,null,3,10],
 [2,5,1,8,9,7,4,6,3,null,null],
 [1,3,null,2,null,null,null,null,null,null,null],
 [2,6,7,4,1,3,null,null,5,null,null],
 [2,1,3,6,8,7,null,5,null,4,null],
 [1,4,2,7,3,10,5,9,8,6,null],
 [3,5,2,4,6,1,7,null,null,8,null],
 [2,1,3,null,null,null,null,null,null,4,null],
 [4,3,5,2,7,6,9,null,10,8,1],
 [2,3,6,5,4,1,7,8,9,10,null],
 [2,4,5,9,6,1,3,null,7,null,8],
 [1,3,5,2,8,7,null,9,4,6,null],
 [1,2,null,null,null,null,null,3,null,null,null],
 [3,4,1,null,5,2,null,null,6,null,null],
 [2,3,8,4,5,7,1,6,9,10,null],
 [2,5,4,7,8,3,6,null,1,null,null],
 [1,6,4,5,2,null,null,3,8,7,null],
 [2,1,3,6,5,7,null,4,null,null,8],
 [2,4,1,6,8,5,null,3,7,null,null],
 [2,4,3,7,8,1,5,6,null,9,null],
 [2,1,3,4,5,null,null,null,null,null,null],
 [1,2,6,5,7,3,8,null,null,null,4],
 [1,2,3,null,null,null,null,null,null,null,null],
 [1,3,5,4,9,null,8,7,2,6,10],
 [1,2,3,5,8,4,null,6,null,null,7],
 [2,5,1,7,4,6,9,3,10,8,null],
 [1,3,2,6,8,5,null,4,null,null,7],
 [null,4,null,1,null,null,2,3,null,null,null],
 [1,4,2,8,6,7,5,9,null,3,null],
 [1,2,4,3,5,6,7,null,null,8,null],
 [1,5,7,6,3,4,2,null,null,null,null],
 [3,2,7,6,5,8,4,1,null,10,9],
 [1,2,4,5,6,3,7,8,9,null,null],
 [5,2,1,7,4,9,3,6,8,null,null],
 [3,2,1,4,null,5,null,null,null,null,null],
 [3,1,2,5,4,9,8,6,null,null,7],
 [3,2,5,6,1,7,10,4,null,9,8],
 [1,4,3,6,5,7,8,9,2,null,10],
 [1,4,3,2,6,5,8,7,null,9,null],
 [2,null,null,null,null,null,null,null,1,null,null],
 [1,2,null,3,null,null,null,null,null,null,null],
 [1,5,3,4,6,9,8,null,null,2,7],
 [null,3,null,1,2,null,null,null,null,null,null],
 [1,6,8,7,10,4,2,3,9,null,5],
 [5,2,6,8,4,1,10,9,3,null,7],
 [1,6,8,5,4,null,2,3,7,null,null],
 [3,2,5,10,9,8,null,4,1,6,7],
 [1,3,4,6,2,null,null,null,5,null,null],
 [4,2,3,5,6,1,7,null,null,null,null],
 [1,6,2,10,8,5,9,4,3,null,7],
 [1,4,2,7,6,3,5,null,null,null,null],
 [1,6,2,4,9,7,8,3,5,null,10],
 [2,1,4,3,5,7,6,9,8,10,null],
 [3,4,2,5,null,7,6,1,null,null,null],
 [6,1,2,5,3,9,7,8,null,null,4],
 [2,1,4,3,6,null,9,null,8,5,7],
 [2,3,6,5,null,4,null,1,7,null,null],
 [2,3,5,4,1,null,null,null,null,null,null],
 [3,4,6,9,2,null,5,null,8,7,1],
 [1,3,5,8,2,4,6,null,null,null,7],
 [1,null,4,5,2,null,3,null,null,null,null],
 [1,5,2,9,3,7,4,10,8,6,null],
 [3,1,6,4,2,7,5,9,10,8,null],
 [1,2,4,5,3,8,7,6,10,9,null],
 [3,2,null,null,5,6,4,null,null,null,1],
 [3,7,6,1,2,null,null,4,null,null,5],
 [2,4,5,6,3,1,7,8,9,10,null],
 [2,1,3,null,null,5,null,null,null,null,4],
 [4,5,3,1,7,2,9,8,null,6,10],
 [1,4,null,null,2,3,null,null,null,null,null],
 [2,3,1,4,null,null,null,null,null,null,null],
 [1,4,6,5,9,8,2,null,3,7,null],
 [null,null,1,null,null,null,null,null,null,null,null],
 [1,3,6,5,10,9,7,null,2,4,8],
 [1,2,5,4,7,6,null,null,null,3,8],
 [2,4,3,null,null,null,5,null,1,6,null],
 [1,3,2,6,7,9,5,4,8,null,10],
 [2,3,7,4,9,5,6,8,null,1,null],
 [2,3,6,4,7,5,1,9,null,8,null],
 [1,6,2,7,5,8,null,4,3,null,9],
 [2,3,6,7,1,8,4,10,null,9,5],
 [1,4,5,2,7,8,3,10,6,9,null],
 [2,4,1,3,8,6,9,null,5,null,7],
 [1,2,null,6,null,null,3,null,5,null,4],
 [1,4,2,5,8,null,7,null,3,6,9],
 [2,null,null,null,null,null,null,null,1,null,null],
 [2,5,6,8,4,1,10,7,null,3,9],
 [1,null,3,4,null,2,null,null,null,null,null],
 [3,2,4,5,1,null,6,null,null,null,null],
 [2,3,5,1,8,9,10,6,4,null,7],
 [null,null,null,null,null,null,null,null,null,null,null],
 [2,6,7,9,3,4,null,8,5,null,1],
 [4,7,5,3,1,null,6,2,null,8,9],
 [2,3,1,4,8,9,5,6,7,10,null],
 [3,2,6,5,4,8,7,null,1,10,9],
 [1,2,5,6,4,9,7,null,3,8,null],
 [2,1,null,null,4,null,null,null,null,null,3],
 [1,5,2,null,4,null,null,null,3,null,null],
 [1,4,3,8,9,5,2,6,7,10,null],
 [1,4,5,3,null,null,null,2,null,null,6],
 [2,3,5,6,7,1,9,4,10,8,null],
 [1,null,null,null,null,null,null,null,null,2,null],
 [1,5,3,4,8,6,2,9,7,10,null],
 [1,2,6,4,5,9,10,3,8,null,7],
 [1,4,2,6,null,5,null,3,null,null,null],
 [2,4,3,6,7,1,5,null,null,null,null],
 [3,5,6,1,4,2,9,7,10,8,null],
 [1,5,3,8,2,7,9,6,4,10,null],
 [2,1,7,8,3,4,6,9,5,null,10],
 [1,null,null,null,null,null,null,null,null,null,null],
 [1,4,6,3,8,2,5,9,null,7,null],
 [1,4,3,2,8,5,null,7,null,null,6],
 [1,3,6,4,null,null,null,null,2,null,5],
 [1,4,3,5,8,null,6,null,null,2,7],
 [1,3,4,2,6,7,9,8,null,null,5],
 [1,4,null,null,2,null,null,null,null,3,null],
 [1,5,4,null,2,null,null,null,3,null,null],
 [4,5,null,null,null,null,null,3,2,1,null],
 [1,2,4,7,5,null,6,null,3,null,null],
 [1,3,4,2,6,9,8,7,5,null,null],
 [3,1,6,4,9,2,null,8,null,7,5],
 [3,5,4,7,1,6,null,null,null,2,null],
 [5,4,2,6,3,7,1,9,null,8,null],
 [3,6,2,5,7,8,1,null,10,4,9],
 [1,3,4,5,6,2,8,9,null,null,7],
 [2,3,4,null,null,null,null,1,null,null,null],
 [1,2,4,3,5,null,8,null,6,7,null],
 [2,5,3,8,6,9,7,1,10,4,null],
 [1,4,5,2,6,3,null,8,7,10,9],
 [2,4,3,8,7,5,9,1,null,6,null],
 [2,3,5,4,6,8,10,7,1,null,9],
 [1,4,6,5,3,8,2,7,9,10,null],
 [2,4,3,1,null,6,null,null,null,5,null],
 [1,null,null,null,3,null,2,null,null,null,null],
 [2,null,null,null,null,1,null,null,3,null,null],
 [2,5,7,3,8,6,null,4,null,1,null],
 [3,1,2,8,9,null,7,4,5,null,6],
 [1,4,6,7,3,5,10,2,8,null,9],
 [1,2,5,3,7,4,null,null,null,6,null],
 [1,4,2,6,3,7,8,5,9,null,10],
 [2,4,7,6,5,1,10,null,8,9,3],
 [1,4,3,null,2,null,null,null,null,null,null],
 [4,2,7,8,6,10,null,1,9,5,3],
 [3,5,2,1,6,7,null,8,9,4,null],
 [1,4,6,5,2,8,9,null,7,3,null],
 [2,3,1,9,4,7,5,8,10,null,6],
 [6,7,2,1,4,null,3,5,null,8,null],
 [1,null,3,null,2,null,null,null,null,null,null],
 [2,3,4,7,9,5,6,1,8,null,null],
 [1,5,6,2,null,3,null,null,null,4,null],
 [2,5,3,4,1,8,7,6,10,9,null],
 [2,1,3,5,7,6,4,null,null,null,null],
 [1,null,2,null,3,null,null,null,null,null,null],
 [1,2,7,8,5,4,null,9,3,null,6],
 [1,2,5,4,7,6,8,9,3,null,null],
 [1,2,7,8,5,3,6,null,4,null,null],
 [2,4,5,7,1,9,3,10,6,null,8],
 [2,3,1,4,6,null,null,7,null,5,null],
 [1,null,null,null,null,null,2,null,3,4,null],
 [3,1,2,4,5,7,8,null,null,6,null],
 [1,5,null,3,4,null,null,null,null,6,2],
 [1,3,4,2,7,6,5,9,10,null,8],
 [2,1,3,null,null,5,6,4,null,null,null],
 [1,4,3,2,7,5,9,6,null,8,null],
 [3,1,7,6,9,4,10,null,2,8,5],
 [4,2,5,3,6,null,null,null,1,null,7],
 [1,2,null,3,null,null,null,null,null,null,null],
 [1,3,5,2,4,6,7,null,8,null,null],
 [2,3,1,5,6,4,null,null,7,null,8],
 [1,2,null,null,null,4,null,null,null,null,3],
 [4,5,3,null,null,null,null,null,2,null,1],
 [2,null,null,1,null,null,null,null,null,null,null],
 [3,5,4,7,6,1,8,null,null,2,null],
 [1,3,6,null,5,4,2,null,null,null,null],
 [3,2,4,6,5,1,7,9,8,null,null],
 [2,1,3,5,8,6,10,9,4,7,null],
 [1,5,4,2,8,null,7,3,null,null,6],
 [2,1,3,5,6,7,8,null,null,null,4],
 [2,1,4,7,null,null,3,5,null,null,6],
 [2,5,1,3,7,10,8,6,9,4,null],
 [1,4,2,5,3,9,8,10,6,null,7],
 [3,2,6,1,4,7,null,5,null,null,null],
 [1,2,3,5,6,4,null,null,null,null,null],
 [1,3,4,2,6,null,8,7,null,null,5],
 [1,4,2,9,5,null,8,null,6,3,7],
 [2,5,7,8,6,10,null,4,3,9,1],
 [4,null,1,null,null,null,3,null,2,null,null],
 [1,2,3,7,6,8,9,4,5,null,null],
 [4,2,3,8,9,1,7,null,null,6,5],
 [1,2,5,6,4,8,3,null,7,null,9],
 [1,null,null,null,null,null,null,2,null,null,null],
 [3,2,6,null,4,null,null,1,null,5,null],
 [3,1,4,5,null,2,null,null,null,null,null],
 [1,3,5,6,2,8,null,null,7,4,null],
 [1,3,5,6,4,7,9,null,8,10,2],
 [2,3,7,1,5,9,null,8,10,4,6],
 [3,4,5,2,7,1,6,9,10,null,8],
 [3,1,6,null,5,7,null,4,null,2,null],
 [1,2,3,6,null,4,5,null,8,7,null],
 [1,4,3,7,8,5,10,9,6,null,2],
 [3,5,7,2,6,4,10,1,null,9,8],
 [3,4,5,6,8,null,7,1,10,2,9],
 [2,1,5,6,4,7,9,3,null,null,8],
 [2,5,3,6,7,1,4,8,null,null,null],
 [2,1,3,4,6,9,7,5,8,10,null],
 [null,null,null,null,null,null,null,1,null,null,null],
 [3,2,5,7,1,null,null,6,4,null,8],
 [2,1,4,5,3,null,null,null,null,null,null],
 [6,3,null,2,1,4,5,null,null,7,null],
 [1,3,4,null,null,6,5,7,2,null,8],
 [2,1,null,null,null,null,null,null,null,null,null],
 [4,7,6,3,5,8,1,2,null,null,9],
 [1,2,3,5,8,6,7,9,10,null,4],
 [4,3,6,null,null,2,null,1,5,null,null],
 [2,5,1,6,3,9,8,null,4,7,null],
 [2,4,1,null,3,null,null,null,null,null,null],
 [6,2,5,3,8,1,4,null,null,7,null],
 [2,4,null,5,null,null,1,null,3,null,null],
 [null,null,null,null,null,null,null,null,null,null,null],
 [3,4,5,6,7,1,8,9,2,null,10],
 [2,3,1,4,5,8,null,6,null,null,7],
 [1,5,2,3,4,6,7,9,8,null,null],
 [1,3,4,null,null,null,6,null,5,2,null],
 [1,4,6,5,2,10,9,7,3,null,8],
 [2,1,6,7,8,9,5,3,null,4,null],
 [1,2,6,3,8,7,4,10,5,9,null],
 [3,4,2,null,null,null,1,null,null,null,null],
 [2,3,6,8,4,9,1,7,5,null,null],
 [1,5,3,8,7,2,6,null,4,null,null],
 [2,4,3,1,6,10,5,7,8,null,9],
 [2,4,7,1,8,3,10,5,null,9,6],
 [1,6,2,5,8,3,4,null,7,null,9],
 [3,1,7,5,6,2,null,8,4,null,9],
 [1,4,5,6,10,2,8,7,9,3,null],
 [1,2,4,3,null,null,null,null,null,null,null],
 [2,6,5,8,7,null,1,4,10,3,9],
 [2,1,5,3,7,6,null,4,null,null,null],
 [1,2,7,4,5,6,3,null,null,null,null],
 [1,null,null,2,null,null,null,4,null,3,null],
 [2,3,6,7,1,4,5,null,null,null,null],
 [2,4,3,1,null,null,null,null,null,null,null],
 [4,5,1,6,2,8,null,3,null,null,7],
 [1,null,null,2,null,null,null,null,null,null,null],
 [4,5,6,2,1,8,9,3,7,null,10],
 [1,3,4,2,5,6,null,8,7,null,null],
 [1,3,2,5,7,4,6,null,null,8,null],
 [5,1,3,4,null,7,8,2,9,null,6],
 [1,5,7,2,3,4,8,6,10,9,null],
 [1,4,2,6,5,3,8,null,7,null,null],
 [1,3,null,6,5,2,null,4,null,null,null],
 [1,2,3,5,4,9,6,8,7,null,10],
 [1,3,4,7,2,8,6,9,5,10,null],
 [1,6,2,5,null,null,3,null,4,null,null],
 [2,3,5,6,1,10,4,9,null,8,7],
 [4,6,3,1,8,5,2,10,9,null,7],
 [5,3,8,1,7,6,9,4,null,10,2],
 [3,5,7,4,10,1,8,null,6,2,9],
 [2,5,3,4,1,8,7,6,10,null,9],
 [4,2,null,7,null,null,5,3,1,null,6],
 [1,3,2,4,5,null,6,null,7,8,null],
 [1,null,null,null,null,null,null,2,null,null,null],
 [3,2,5,1,4,null,9,8,6,null,7],
 [1,6,2,5,null,null,null,7,4,null,3],
 [3,1,2,null,6,null,5,null,4,7,null],
 [2,3,null,4,1,6,7,null,null,5,null],
 [2,5,4,null,3,null,1,6,null,null,7],
 [2,3,1,6,null,null,5,null,4,null,null],
 [2,3,4,1,null,6,null,7,null,5,null],
 [2,1,7,3,null,5,null,null,4,6,null],
 [2,4,3,7,null,null,5,null,null,6,1],
 [3,1,null,2,null,null,null,null,null,null,null],
 [2,1,9,3,10,7,5,8,4,null,6],
 [2,5,4,3,7,null,6,null,1,null,8],
 [1,5,2,3,6,null,9,4,7,8,null],
 [2,3,4,1,8,null,null,5,null,7,6],
 [2,1,3,4,5,6,7,8,9,null,10],
 [2,1,4,3,8,5,10,7,9,null,6],
 [1,4,2,3,null,5,7,6,null,null,null]
]');


execute insert_votes(2, '[
 [2,1,5,4,3,7,6,null],
 [1,4,5,6,3,2,null,null],
 [1,3,5,4,6,null,2,null],
 [1,3,2,5,null,6,4,null],
 [2,1,4,3,6,7,5,null],
 [2,null,null,null,null,1,null,null],
 [3,2,4,1,null,5,null,null],
 [null,null,3,null,null,1,2,null],
 [1,null,null,null,null,null,null,null],
 [2,1,3,null,null,4,null,null],
 [2,6,4,7,1,5,null,3],
 [3,null,2,null,null,1,null,null],
 [1,3,4,6,5,7,2,null],
 [1,2,null,3,null,null,null,null],
 [1,4,2,3,null,5,null,null],
 [4,6,5,3,7,2,1,null],
 [2,1,5,3,6,7,null,4],
 [3,1,6,7,5,4,2,null],
 [2,1,6,7,3,4,null,5],
 [1,3,6,5,7,2,4,null],
 [1,4,2,3,5,7,6,null],
 [2,5,1,3,null,4,null,null],
 [1,3,4,null,2,null,null,null],
 [2,4,3,6,1,null,5,7],
 [1,6,5,4,3,2,null,null],
 [3,2,6,4,1,null,7,5],
 [1,2,4,6,3,7,5,null],
 [3,1,null,null,null,null,4,2],
 [1,2,4,6,5,null,null,3],
 [3,4,1,5,2,6,null,7],
 [2,1,6,3,4,5,null,7],
 [1,2,4,6,3,5,null,null],
 [2,3,1,null,null,5,4,null],
 [1,4,5,3,null,2,6,null],
 [2,4,6,5,3,1,7,null],
 [1,2,null,4,3,null,null,null],
 [1,3,4,2,7,null,5,6],
 [2,1,5,3,7,null,4,6],
 [1,3,4,5,7,null,2,6],
 [4,2,3,6,5,1,null,null],
 [3,1,4,6,2,null,5,7],
 [1,5,6,2,null,4,3,null],
 [1,2,4,3,null,null,5,null],
 [2,null,null,null,1,null,null,null],
 [1,4,2,6,7,5,3,null],
 [5,6,1,2,null,null,3,4],
 [1,2,3,4,5,7,null,6],
 [null,null,null,null,null,null,null,1],
 [1,2,3,4,6,5,7,null],
 [3,4,null,1,2,null,null,null],
 [1,4,3,2,6,5,null,null],
 [1,2,3,null,null,4,null,null],
 [1,3,2,4,6,5,null,7],
 [2,1,3,6,5,7,4,null],
 [2,1,5,4,3,6,null,null],
 [1,3,6,2,5,7,null,4],
 [1,2,3,6,null,7,5,4],
 [3,2,6,5,null,4,1,null],
 [2,3,null,4,null,5,1,null],
 [3,1,4,2,6,5,null,null],
 [1,2,3,5,6,4,7,null],
 [3,4,1,null,2,null,null,null],
 [1,2,null,null,null,null,null,3],
 [2,6,4,7,5,1,3,null],
 [1,2,null,3,null,null,null,null],
 [1,2,5,7,6,4,3,null],
 [2,3,4,null,null,null,null,1],
 [3,2,4,6,null,1,5,null],
 [1,2,3,4,5,6,null,null],
 [1,5,4,2,null,null,3,null],
 [1,4,3,null,2,null,null,null],
 [1,4,null,2,null,null,3,null],
 [2,3,5,1,6,4,null,null],
 [4,2,5,1,3,6,null,null],
 [1,3,null,2,null,null,null,null],
 [1,3,2,4,6,null,7,5],
 [1,null,null,null,null,null,null,null],
 [2,4,3,null,1,null,null,null],
 [1,5,4,3,null,null,null,2],
 [1,3,6,4,2,null,null,5],
 [1,3,2,4,null,5,6,null],
 [null,1,null,null,null,null,null,null],
 [1,2,3,4,6,null,5,null],
 [1,4,5,null,3,null,2,null],
 [2,4,1,3,null,6,null,5],
 [1,4,2,6,7,3,null,5],
 [1,4,5,3,2,7,null,6],
 [1,3,6,2,7,5,null,4],
 [2,1,4,3,null,null,null,null],
 [1,null,3,null,2,null,4,null],
 [1,2,4,3,7,6,null,5],
 [3,2,4,7,null,6,1,5],
 [2,1,null,null,null,null,3,null],
 [4,3,null,6,5,null,1,2],
 [2,3,1,4,null,null,null,null],
 [1,null,null,null,null,null,null,null],
 [4,5,6,7,2,null,1,3],
 [1,null,null,null,null,null,null,null],
 [3,1,2,4,6,null,null,5],
 [2,1,3,5,4,null,null,null]
]');

-- intentionally no votes in election 3

-- 7 candidates for 4 places, quota is ballots/5 + 1 = 4
-- What should happen is: Statler and Waldorf are eliminated,
-- electing Kermit with their second-place votes, then Chef is
-- eliminated, and the remainder elected by exhaustion
execute insert_votes(4, '[
 [1,null,null,null,null,null,null],
 [null,1,null,null,null,null,null],
 [null,null,1,null,null,null,null],
 [null,null,null,1,null,null,null],
 [2,null,null,null,1,null,null],
 [2,null,null,null,null,1,null],
 [null,null,null,null,null,null,1],
 [null,null,null,null,null,null,1],
 [1,null,null,null,null,null,null],
 [null,1,null,null,null,null,null],
 [null,null,1,null,null,null,null],
 [null,null,null,1,null,null,null],
 [null,1,null,null,null,null,null],
 [null,null,1,null,null,null,null],
 [null,null,null,1,null,null,null]
]');

-- electing 2 places; nobody puts lemons first, but they
-- are elected by second-place votes
execute insert_votes(5, '[
  [1,null,null,null,2],
  [1,null,null,null,2],
  [1,null,null,null,2],
  [1,null,null,null,2],
  [1,null,null,null,2],
  [1,null,null,null,2],
  [1,null,null,null,2],
  [1,null,null,null,2],
  [1,null,null,null,2],
  [1,null,null,null,2],
  [null,1,null,null,2],
  [null,null,1,null,2],
  [null,null,null,1,2],
  [null,1,null,null,2],
  [null,null,1,null,2]
]');

-- this has the data in a different format for ease of input
prepare insert_votes2(integer, json) as
insert into ballot_preferences(voter_id,
                               election_id,
							   candidate_id,
							   preference)
select voter_ord,
       $1,
	   candidate_ord::integer,
	   pref::integer
  from json_array_elements($2) with ordinality as a1(prefs, voter_ord),
       json_array_elements_text(prefs) with ordinality as a2(candidate_ord, pref);

execute insert_votes2(6, '[
 [2,3,1,4],
 [2,4,3,1],
 [4,3,1],
 [2,4,1,3],
 [1,4,3,2],
 [2,4],
 [3,4,1],
 [3,4,2,1],
 [1,2,4],
 [2,3,1,4],
 [4,2,3],
 [4,2,1,3],
 [3,1,4],
 [4,1,2,3],
 [2,4,3,1],
 [4,2,1],
 [4,2,1,3],
 [4,2,1,3],
 [2,1,3,4],
 [2,3,1],
 [3,4,2,1],
 [4,2,1,3],
 [2,3,4,1],
 [4,2,3,1],
 [2,1,4,3],
 [1],
 [2,1,3,4],
 [4,1,2,3],
 [2,1,3,4],
 [2,1,3],
 [4,2,1,3],
 [2,1,4,3],
 [4,3,2,1],
 [3,2,4],
 [2,4,3,1],
 [2,4,3,1],
 [1,3,2,4],
 [2,3,4,1],
 [4,2,1],
 [4,3,2,1]
]');