CREATE DATABASE WebChain;
USE WebChain;

-- Create the Ballot table
CREATE TABLE Ballot (
  ballotID INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(255),
  question TEXT,
  answer_choices BLOB,
  privacy BOOLEAN,
  additional_info TEXT
);

-- Create the Vote table
CREATE TABLE Vote (
  voteID INT PRIMARY KEY AUTO_INCREMENT,
  ballotID INT,
  vote_choice INT, -- vote_choice is the index of the answer_choices from the Ballot table
  FOREIGN KEY (ballotID) REFERENCES Ballot(ballotID)
);

SELECT COUNT(*) AS vote_count
FROM Ballot b
JOIN Vote v ON b.ballotID = v.ballotID
WHERE b.ballotID = "[ballotID]" -- Replace [ballotID] with the actual ballotID you want to query
  AND v.vote_choice = '[answer_choice]' -- Replace [answer_choice] with the actual answer choice you want to query
;




