init.sql

CREATE DATABASE IF NOT EXISTS electiondb;
USE electiondb;

-- ------------------------------------------------------------
-- Parties
-- ------------------------------------------------------------
CREATE TABLE parties (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(255) NOT NULL UNIQUE,
    ideology   VARCHAR(255),
    founded    YEAR,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ------------------------------------------------------------
-- Regions
-- ------------------------------------------------------------
CREATE TABLE regions (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    name         VARCHAR(255) NOT NULL UNIQUE,
    total_voters INT DEFAULT 0
);

-- ------------------------------------------------------------
-- Constituencies
-- ------------------------------------------------------------
CREATE TABLE constituencies (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    name         VARCHAR(255) NOT NULL,
    region_id    INT NOT NULL,
    total_voters INT DEFAULT 0,
    FOREIGN KEY (region_id) REFERENCES regions(id) ON DELETE CASCADE
);

-- ------------------------------------------------------------
-- Embassies
-- ------------------------------------------------------------
CREATE TABLE embassies (
    id                  INT AUTO_INCREMENT PRIMARY KEY,
    country             VARCHAR(255) NOT NULL,
    city                VARCHAR(255) NOT NULL,
    address             VARCHAR(500),
    registered_diaspora INT DEFAULT 0,
    votes_cast          INT DEFAULT 0,
    UNIQUE KEY unique_embassy (country, city)
);

-- ------------------------------------------------------------
-- Special Voting Centers
-- ------------------------------------------------------------
CREATE TABLE special_voting_centers (
    id                   INT AUTO_INCREMENT PRIMARY KEY,
    name                 VARCHAR(255) NOT NULL,
    type                 ENUM('hospital','care_home','mobile_unit') NOT NULL,
    region_id            INT NOT NULL,
    capacity             INT DEFAULT 0,
    registered_voters    INT DEFAULT 0,
    braille_equipment    TINYINT(1) DEFAULT 0,
    wheelchair_access    TINYINT(1) DEFAULT 0,
    sign_language_staff  TINYINT(1) DEFAULT 0,
    FOREIGN KEY (region_id) REFERENCES regions(id) ON DELETE CASCADE
);

-- ------------------------------------------------------------
-- Voters
-- ------------------------------------------------------------
CREATE TABLE voters (
    id                   INT AUTO_INCREMENT PRIMARY KEY,
    name                 VARCHAR(255) NOT NULL,
    email                VARCHAR(255) NOT NULL UNIQUE,
    national_id          VARCHAR(50)  NOT NULL UNIQUE,
    age                  INT NOT NULL,
    region_id            INT,
    constituency_id      INT,
    voter_category       ENUM('regular','sick','elderly','pregnant',
                              'blind','deaf','physically_disabled',
                              'diaspora') NOT NULL DEFAULT 'regular',
    has_voted_president  TINYINT(1) DEFAULT 0,
    has_voted_governor   TINYINT(1) DEFAULT 0,
    has_voted_senator    TINYINT(1) DEFAULT 0,
    has_voted_mp         TINYINT(1) DEFAULT 0,
    password             VARCHAR(255) NOT NULL,
    role                 ENUM('voter','admin') NOT NULL DEFAULT 'voter',
    created_at           TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (region_id)       REFERENCES regions(id)       ON DELETE SET NULL,
    FOREIGN KEY (constituency_id) REFERENCES constituencies(id) ON DELETE SET NULL
);

-- ------------------------------------------------------------
-- Candidates
-- ------------------------------------------------------------
CREATE TABLE candidates (
    id               INT AUTO_INCREMENT PRIMARY KEY,
    name             VARCHAR(255) NOT NULL,
    position         ENUM('president','governor','senator',
                          'member_of_parliament') NOT NULL,
    party_id         INT NOT NULL,
    region_id        INT,
    constituency_id  INT,
    manifesto        TEXT,
    created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (party_id)        REFERENCES parties(id)        ON DELETE CASCADE,
    FOREIGN KEY (region_id)       REFERENCES regions(id)        ON DELETE SET NULL,
    FOREIGN KEY (constituency_id) REFERENCES constituencies(id) ON DELETE SET NULL
);

-- ------------------------------------------------------------
-- Special Voters
-- ------------------------------------------------------------
CREATE TABLE special_voters (
    id                    INT AUTO_INCREMENT PRIMARY KEY,
    voter_id              INT NOT NULL UNIQUE,
    category              ENUM('sick','elderly','pregnant',
                               'blind','deaf',
                               'physically_disabled') NOT NULL,
    facility_id           INT NOT NULL,
    assistance_needed     TINYINT(1) DEFAULT 0,
    assistance_type       ENUM('braille','sign_language',
                               'wheelchair','assisted_voting'),
    braille_ballot_issued TINYINT(1) DEFAULT 0,
    registered_at         TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (voter_id)    REFERENCES voters(id)                ON DELETE CASCADE,
    FOREIGN KEY (facility_id) REFERENCES special_voting_centers(id) ON DELETE CASCADE
);

-- ------------------------------------------------------------
-- Diaspora Voters
-- ------------------------------------------------------------
CREATE TABLE diaspora_voters (
    id                   INT AUTO_INCREMENT PRIMARY KEY,
    voter_id             INT NOT NULL UNIQUE,
    embassy_id           INT NOT NULL,
    home_constituency_id INT,
    home_region_id       INT,
    registered_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (voter_id)             REFERENCES voters(id)         ON DELETE CASCADE,
    FOREIGN KEY (embassy_id)           REFERENCES embassies(id)      ON DELETE CASCADE,
    FOREIGN KEY (home_constituency_id) REFERENCES constituencies(id) ON DELETE SET NULL,
    FOREIGN KEY (home_region_id)       REFERENCES regions(id)        ON DELETE SET NULL
);

-- ------------------------------------------------------------
-- Inspectors
-- ------------------------------------------------------------
CREATE TABLE inspectors (
    id               INT AUTO_INCREMENT PRIMARY KEY,
    name             VARCHAR(255) NOT NULL,
    national_id      VARCHAR(50)  NOT NULL UNIQUE,
    email            VARCHAR(255) NOT NULL UNIQUE,
    phone            VARCHAR(20),
    constituency_id  INT NOT NULL,
    status           ENUM('active','suspended','completed') DEFAULT 'active',
    appointed_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (constituency_id) REFERENCES constituencies(id) ON DELETE CASCADE
);

-- ------------------------------------------------------------
-- Vote Collectors
-- ------------------------------------------------------------
CREATE TABLE vote_collectors (
    id                INT AUTO_INCREMENT PRIMARY KEY,
    name              VARCHAR(255) NOT NULL,
    national_id       VARCHAR(50)  NOT NULL UNIQUE,
    email             VARCHAR(255) NOT NULL UNIQUE,
    phone             VARCHAR(20),
    assignment_type   ENUM('special_center','embassy') NOT NULL,
    special_center_id INT,
    embassy_id        INT,
    votes_collected   INT DEFAULT 0,
    status            ENUM('active','completed') DEFAULT 'active',
    appointed_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (special_center_id) REFERENCES special_voting_centers(id) ON DELETE SET NULL,
    FOREIGN KEY (embassy_id)        REFERENCES embassies(id)              ON DELETE SET NULL
);

-- ------------------------------------------------------------
-- Tallying Stations
-- ------------------------------------------------------------
CREATE TABLE tallying_stations (
    id                      INT AUTO_INCREMENT PRIMARY KEY,
    name                    VARCHAR(255) NOT NULL,
    constituency_id         INT NOT NULL,
    location                VARCHAR(500),
    capacity                INT DEFAULT 0,
    total_ballots_received  INT DEFAULT 0,
    total_ballots_counted   INT DEFAULT 0,
    status                  ENUM('pending','counting',
                                 'completed','disputed') DEFAULT 'pending',
    opened_at               TIMESTAMP NULL,
    closed_at               TIMESTAMP NULL,
    FOREIGN KEY (constituency_id) REFERENCES constituencies(id) ON DELETE CASCADE
);

-- ------------------------------------------------------------
-- Helpers
-- ------------------------------------------------------------
CREATE TABLE helpers (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    name            VARCHAR(255) NOT NULL,
    national_id     VARCHAR(50)  NOT NULL UNIQUE,
    email           VARCHAR(255) NOT NULL UNIQUE,
    phone           VARCHAR(20),
    specialization  ENUM('blind','deaf','physically_disabled',
                         'elderly','sick','pregnant') NOT NULL,
    facility_id     INT NOT NULL,
    voters_assisted INT DEFAULT 0,
    status          ENUM('active','completed') DEFAULT 'active',
    appointed_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (facility_id) REFERENCES special_voting_centers(id) ON DELETE CASCADE
);

-- ------------------------------------------------------------
-- Helper Assignments
-- ------------------------------------------------------------
CREATE TABLE helper_assignments (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    helper_id    INT NOT NULL,
    voter_id     INT NOT NULL,
    assigned_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed    TINYINT(1) DEFAULT 0,
    FOREIGN KEY (helper_id) REFERENCES helpers(id)  ON DELETE CASCADE,
    FOREIGN KEY (voter_id)  REFERENCES voters(id)   ON DELETE CASCADE,
    UNIQUE KEY unique_assignment (helper_id, voter_id)
);

-- ------------------------------------------------------------
-- Votes
-- ------------------------------------------------------------
CREATE TABLE votes (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    voter_id      INT NOT NULL,
    candidate_id  INT NOT NULL,
    position      ENUM('president','governor','senator',
                       'member_of_parliament') NOT NULL,
    voted_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (voter_id)     REFERENCES voters(id)     ON DELETE CASCADE,
    FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
    UNIQUE KEY unique_vote (voter_id, position)
);

-- ------------------------------------------------------------
-- Results
-- ------------------------------------------------------------
CREATE TABLE results (
    id               INT AUTO_INCREMENT PRIMARY KEY,
    position         ENUM('president','governor','senator',
                          'member_of_parliament') NOT NULL,
    constituency_id  INT,
    region_id        INT,
    candidate_id     INT NOT NULL,
    party_id         INT NOT NULL,
    total_votes      INT DEFAULT 0,
    vote_share       DECIMAL(5,2) DEFAULT 0.00,
    is_winner        TINYINT(1) DEFAULT 0,
    wasted_votes     INT DEFAULT 0,
    surplus_votes    INT DEFAULT 0,
    FOREIGN KEY (constituency_id) REFERENCES constituencies(id) ON DELETE SET NULL,
    FOREIGN KEY (region_id)       REFERENCES regions(id)        ON DELETE SET NULL,
    FOREIGN KEY (candidate_id)    REFERENCES candidates(id)     ON DELETE CASCADE,
    FOREIGN KEY (party_id)        REFERENCES parties(id)        ON DELETE CASCADE
);

-- ------------------------------------------------------------
-- Government Summary
-- ------------------------------------------------------------
CREATE TABLE government_summary (
    id                  INT AUTO_INCREMENT PRIMARY KEY,
    party_id            INT NOT NULL UNIQUE,
    total_seats_won     INT DEFAULT 0,
    president_won       TINYINT(1) DEFAULT 0,
    governor_seats      INT DEFAULT 0,
    senator_seats       INT DEFAULT 0,
    mp_seats            INT DEFAULT 0,
    majority_threshold  INT DEFAULT 0,
    government_status   ENUM('majority','minority','coalition') DEFAULT 'minority',
    FOREIGN KEY (party_id) REFERENCES parties(id) ON DELETE CASCADE
);

-- ------------------------------------------------------------
-- Seed: Parties
-- ------------------------------------------------------------
INSERT INTO parties (name, ideology, founded) VALUES
    ('National Alliance',  'Centre-right',  2005),
    ('Peoples Party',      'Centre-left',   1998),
    ('United Front',       'Populist',      2010),
    ('Green Alliance',     'Progressive',   2015);

-- ------------------------------------------------------------
-- Seed: Regions
-- ------------------------------------------------------------
INSERT INTO regions (name, total_voters) VALUES
    ('Nairobi',     2000000),
    ('Coast',        800000),
    ('Rift Valley', 1500000),
    ('Central',     1200000);

-- ------------------------------------------------------------
-- Seed: Constituencies
-- ------------------------------------------------------------
INSERT INTO constituencies (name, region_id, total_voters) VALUES
    ('Westlands',       1, 150000),
    ('Starehe',         1, 120000),
    ('Mombasa Central', 2,  90000),
    ('Kilindini',       2,  85000),
    ('Eldoret North',   3, 110000),
    ('Uasin Gishu',     3, 105000),
    ('Kiambu Town',     4,  95000),
    ('Thika',           4,  88000);

-- ------------------------------------------------------------
-- Seed: Embassies
-- ------------------------------------------------------------
INSERT INTO embassies (country, city, address, registered_diaspora) VALUES
    ('United States', 'Washington DC', '2249 R St NW',    1240),
    ('United Kingdom','London',        '45 Portland Place', 890),
    ('Canada',        'Ottawa',        '415 Laurier Ave',   340),
    ('Germany',       'Berlin',        '19 Wallstrasse',    210),
    ('Australia',     'Canberra',      '33 Ainslie Ave',    180);