-- Cleanup existing tables and data
DROP TABLE IF EXISTS Elections, Locations, Parties, BallotPos, LocationVoters, Votes;

-- Create all tables, incl constraints
CREATE TABLE Elections(
  EKey INT PRIMARY KEY,
  ElectionID CHAR(6) UNIQUE NOT NULL,
  ElectionNumber INT UNIQUE NOT NULL,
  ElectionDate DATE UNIQUE NOT NULL
);

CREATE TABLE Locations(
  LKey INT PRIMARY KEY,
  LocationID VARCHAR(32) UNIQUE NOT NULL,
  Name VARCHAR(128) NOT NULL,
  ParentLKey INT REFERENCES Locations 
);

CREATE TABLE Parties(
  PKey INT PRIMARY KEY,
  ShortName VARCHAR(64) UNIQUE,
  LongName VARCHAR(256)
);

CREATE TABLE BallotPos(
  EKey INT REFERENCES Elections,
  PKey INT REFERENCES Parties,
  Position INT,
  PRIMARY KEY(EKey, PKey)
);

CREATE TABLE LocationVoters(
  EKey INT REFERENCES Elections,
  LKey INT REFERENCES Locations,
  EligibleTotal BIGINT, -- total != female+male
  EligibleMale BIGINT, 
  EligibleFemale BIGINT,   
  PRIMARY KEY(EKey, LKey)
);

CREATE TABLE Votes(
  EKey INT REFERENCES Elections,
  LKey INT REFERENCES Locations,
  PKey INT REFERENCES Parties,
  VoteCount BIGINT,
  PRIMARY KEY(EKey, LKey, PKey)
);
