-- -- Definition of the SQL database for keeping ballot information
-- -- drop database elections;
-- -- create database elections;
-- -- \c elections;
-- 
-- -- Cleanup
-- drop view tallysheet;
-- drop table votes;
-- drop table ballots;
-- drop table legalVoters;
-- drop table candidates;
-- drop table election;
-- 
-- -- Identify a specific election
-- create table election (
-- id serial,
-- start_date timestamp not null,
-- end_date timestamp not null,
-- common_name text unique not null,
-- -- For elections with no maximum number of seats, max_seats should be a
-- -- negative number.
-- max_seats integer not null,
-- primary key (id)
-- );
-- 
-- -- Identify the account groups who can vote
-- create table legalVoters (
-- election_id integer references election(id),
-- -- This references cvsacct(project_group(id))
-- group_id integer,
-- primary key (election_id, group_id)
-- );
-- 
-- -- A ballot submitted by a voter
-- create table ballots (
-- -- voter is a reference to the cvsacct(person(id))) field.
-- voter integer,
-- id serial,
-- election_id integer references election(id),
-- unique (voter, election_id),
-- primary key (id)
-- );
-- 
-- -- The candidates for a specific election
-- create table candidates (
-- -- id is a reference to the cvsacct(person(id))) field.
-- id integer,
-- election_id integer references election(id),
-- primary key (id, election_id)
-- );
-- 
-- -- Votes from a ballot for a candidate in a specific election
-- create table votes (
-- ballot_id integer references ballots(id),
-- candidate_id integer,
-- election_id integer references election(id),
-- points integer not null,
-- foreign key (candidate_id, election_id) references candidates(id, election_id),
-- primary key (election_id, ballot_id, candidate_id)
-- );
-- 
-- -- Running tallysheet of votes for each candidate in all the elections
-- create view tallysheet as
-- select sum(points) as vote_count, id, candidates.election_id from candidates, votes where candidates.id = candidate_id group by id, candidates.election_id order by candidates.election_id, vote_count desc;
-- 
-- grant select on tallysheet to apache;
-- grant select on election to apache;
-- grant select on candidates to apache;
-- grant select on legalVoters to apache;
-- grant select on ballots to apache;
-- grant insert on ballots to apache;
-- grant insert on votes to apache;
-- grant update on ballots_id_seq to apache;
