-- credits: André Restivo https://web.fe.up.pt/~arestivo
-- Revised by Gonçalo Gonçalves

PRAGMA foreign_keys = ON;
.headers on
.mode columns

DROP TABLE IF EXISTS elector;
DROP TABLE IF EXISTS candidate;
DROP TABLE IF EXISTS municipality;
DROP TABLE IF EXISTS party;

CREATE TABLE party (
  acronym TEXT PRIMARY KEY, -- e.g. LP
  name TEXT NOT NULL UNIQUE -- e.g. Left Party
);

CREATE TABLE municipality (
  cod INTEGER PRIMARY KEY,
  designation TEXT NOT NULL UNIQUE,              -- e.g. Porto 
  blank INTEGER DEFAULT 0 CHECK (blank >= 0),    -- Number of blank votes
  spoiled INTEGER DEFAULT 0 CHECK (spoiled >= 0) -- Number of spoiled (null) votes
);

CREATE TABLE elector (
  en INTEGER PRIMARY KEY,                               -- Elector number
  name TEXT NOT NULL,
  age INTEGER NOT NULL CHECK (age >= 18),
  gender TEXT NOT NULL CHECK (gender IN ('M', 'F')),
  cod INTEGER NOT NULL REFERENCES municipality,         -- Municipality where this elector (en, name, age, gender, cod, voted) Votes
  voted INTEGER NOT NULL DEFAULT 0                      -- Did he/she vote
);

CREATE TABLE candidate (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  age INTEGER NOT NULL CHECK (age >= 18),
  gender TEXT NOT NULL CHECK (gender IN ('M', 'F')),
  cod INTEGER NOT NULL REFERENCES municipality,        -- Municipality to which he/she is a candidate
  votes INTEGER NOT NULL DEFAULT 0 CHECK (votes >= 0), -- Obtained votes
  acronym TEXT NOT NULL REFERENCES party,              -- Candidate party
  UNIQUE (cod, acronym)
);

INSERT INTO municipality (cod, designation, blank, spoiled) VALUES (1, 'Porto', 0, 1);
INSERT INTO municipality (cod, designation, blank, spoiled) VALUES (2, 'Lisboa', 0, 1);
INSERT INTO municipality (cod, designation, blank, spoiled) VALUES (3, 'Gaia', 2, 2);
INSERT INTO municipality (cod, designation, blank, spoiled) VALUES (4, 'Coimbra', 2, 2);
INSERT INTO municipality (cod, designation, blank, spoiled) VALUES (5, 'Nowhere', 0, 0);

INSERT INTO party (acronym, name) VALUES ('LP', 'Left Party');
INSERT INTO party (acronym, name) VALUES ('RP', 'Right Party');
INSERT INTO party (acronym, name) VALUES ('MP', 'Middle Party');
INSERT INTO party (acronym, name) VALUES ('NCP', 'No Cause Party');

INSERT INTO candidate (id, name, age, gender, cod, votes, acronym) VALUES (1, 'Sara Barbosa', 56, 'F', 2, 2, 'RP');
INSERT INTO candidate (id, name, age, gender, cod, votes, acronym) VALUES (3, 'Leonor Leal', 70, 'M', 4, 3, 'RP');
INSERT INTO candidate (id, name, age, gender, cod, votes, acronym) VALUES (5, 'Miguel Moreira', 34, 'F', 3, 3, 'LP');
INSERT INTO candidate (id, name, age, gender, cod, votes, acronym) VALUES (6, 'Goncalo Ferreira', 32, 'F', 4, 2, 'MP');
INSERT INTO candidate (id, name, age, gender, cod, votes, acronym) VALUES (8, 'Lara Barros', 40, 'M', 2, 5, 'LP');
INSERT INTO candidate (id, name, age, gender, cod, votes, acronym) VALUES (9, 'Mariana Azevedo', 63, 'M', 3, 0, 'RP');
INSERT INTO candidate (id, name, age, gender, cod, votes, acronym) VALUES (10, 'Joao Cunha', 39, 'F', 2, 4, 'MP');
INSERT INTO candidate (id, name, age, gender, cod, votes, acronym) VALUES (11, 'Rita Castro', 52, 'F', 3, 4, 'MP');

INSERT INTO elector (en, name, age, gender, cod, voted) VALUES (1, 'Beatriz Amorim', 49, 'M', 3, 0);
INSERT INTO elector (en, name, age, gender, cod, voted) VALUES (2, 'Miguel Jesus', 20, 'F', 4, 1);
INSERT INTO elector (en, name, age, gender, cod, voted) VALUES (3, 'Rodrigo Henriques', 41, 'F', 1, 0);
INSERT INTO elector (en, name, age, gender, cod, voted) VALUES (4, 'Goncalo Mota', 73, 'M', 2, 1);
INSERT INTO elector (en, name, age, gender, cod, voted) VALUES (5, 'Rafael Antunes', 23, 'M', 4, 1);
INSERT INTO elector (en, name, age, gender, cod, voted) VALUES (6, 'Mariana Miranda', 41, 'F', 4, 1);
INSERT INTO elector (en, name, age, gender, cod, voted) VALUES (7, 'Madalena Figueiredo', 52, 'M', 3, 1);
INSERT INTO elector (en, name, age, gender, cod, voted) VALUES (8, 'Lara Amorim', 23, 'M', 2, 1);
INSERT INTO elector (en, name, age, gender, cod, voted) VALUES (9, 'Leonor Almeida', 42, 'F', 1, 1);
INSERT INTO elector (en, name, age, gender, cod, voted) VALUES (10, 'Francisca Loureiro', 69, 'F', 2, 1);
INSERT INTO elector (en, name, age, gender, cod, voted) VALUES (11, 'Ines Mendes', 30, 'M', 1, 1);
INSERT INTO elector (en, name, age, gender, cod, voted) VALUES (12, 'Sofia Coelho', 79, 'M', 1, 0);
INSERT INTO elector (en, name, age, gender, cod, voted) VALUES (13, 'Santiago Costa', 43, 'F', 2, 0);
INSERT INTO elector (en, name, age, gender, cod, voted) VALUES (14, 'Andre Lima', 30, 'M', 2, 1);
INSERT INTO elector (en, name, age, gender, cod, voted) VALUES (15, 'Maria Brito', 48, 'M', 1, 1);
INSERT INTO elector (en, name, age, gender, cod, voted) VALUES (16, 'Gabriel Gomes', 66, 'M', 4, 1);
INSERT INTO elector (en, name, age, gender, cod, voted) VALUES (17, 'Margarida Costa', 61, 'M', 2, 0);
INSERT INTO elector (en, name, age, gender, cod, voted) VALUES (18, 'Joao Machado', 53, 'M', 3, 1);
INSERT INTO elector (en, name, age, gender, cod, voted) VALUES (19, 'Maria Matos', 51, 'M', 3, 1);
INSERT INTO elector (en, name, age, gender, cod, voted) VALUES (20, 'Maria Guerreiro', 33, 'M', 3, 1);
INSERT INTO elector (en, name, age, gender, cod, voted) VALUES (21, 'David Almeida', 55, 'F', 4, 1);
INSERT INTO elector (en, name, age, gender, cod, voted) VALUES (22, 'Rafael Leite', 41, 'M', 1, 1);
