schema.sql

CREATE DATABASE fribourg_elections;
USE fribourg_elections;

CREATE TABLE `elections` (
  `id` int(11) NOT NULL,
  `election_date` date NOT NULL,
  `label` varchar(255) DEFAULT NULL,
  `power` enum('legislative','executive','','') NOT NULL DEFAULT 'legislative',
  `election_type` enum('majority','proportional') NOT NULL DEFAULT 'proportional',
  `seats` int(11) NOT NULL DEFAULT '80',
  `available` tinyint(1) NOT NULL,
  `info` text,
  `warning` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE people (
    id INT PRIMARY KEY,
    gender ENUM('m', 'f') NOT NULL,
    birth_year INT
) ENGINE=InnoDB;

CREATE TABLE parties (
    id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    short_name VARCHAR(50)
) ENGINE=InnoDB;

CREATE TABLE lists (
    id INT PRIMARY KEY,
    election_id INT NOT NULL,
    list_number INT NOT NULL,
    list_label VARCHAR(255),
    short_label VARCHAR(255),
    supplementary_votes INT DEFAULT 0,
    color VARCHAR(7), -- Hex color code
    FOREIGN KEY (election_id) REFERENCES elections(id)
) ENGINE=InnoDB;

-- 5. Junction for List and Parties
CREATE TABLE list_party_affiliation (
    list_id INT NOT NULL,
    party_id INT NOT NULL,
    PRIMARY KEY (list_id, party_id),
    FOREIGN KEY (list_id) REFERENCES lists(id),
    FOREIGN KEY (party_id) REFERENCES parties(id)
) ENGINE=InnoDB;

CREATE TABLE candidacies (
    id INT PRIMARY KEY,
    person_id INT NOT NULL,
    list_id INT NOT NULL,
    candidate_position INT NOT NULL,
    ballot_name VARCHAR(255) NOT NULL,
    profession VARCHAR(255) NOT NULL,
    compact_votes INT DEFAULT 0,
    incumbent BOOL NOT NULL,
    elected BOOL NOT NULL,
    FOREIGN KEY (person_id) REFERENCES people(id),
    FOREIGN KEY (list_id) REFERENCES lists(id)
) ENGINE=InnoDB;

CREATE TABLE modified_ballot_results (
    id INT PRIMARY KEY,
    candidacy_id INT NOT NULL,
    source_list_id INT NULL,
    vote_count INT DEFAULT 0,
    FOREIGN KEY (candidacy_id) REFERENCES candidacies(id),
    FOREIGN KEY (source_list_id) REFERENCES lists(id)
) ENGINE=InnoDB;