-- eCollate — Election Collation System
-- Schema is applied once via setup.php (not recreated on every request).

CREATE DATABASE IF NOT EXISTS ecollate CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE ecollate;

CREATE TABLE IF NOT EXISTS elections (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    election_date DATE NOT NULL,
    status ENUM('setup','active','closed') NOT NULL DEFAULT 'setup',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- constituency_id references npp_iam.constituencies.id (cross-database, no FK)
CREATE TABLE IF NOT EXISTS races (
    id INT AUTO_INCREMENT PRIMARY KEY,
    election_id INT NOT NULL,
    race_type ENUM('president','parliamentary') NOT NULL,
    constituency_id INT DEFAULT NULL,
    title VARCHAR(200) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_race (election_id, race_type, constituency_id),
    FOREIGN KEY (election_id) REFERENCES elections(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS parties (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    abbreviation VARCHAR(20) NOT NULL,
    color_hex VARCHAR(7) DEFAULT '#1e50a0',
    logo_path VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS candidates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    race_id INT NOT NULL,
    party_id INT DEFAULT NULL,
    full_name VARCHAR(200) NOT NULL,
    ballot_number INT DEFAULT 0,
    photo_path VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (race_id) REFERENCES races(id) ON DELETE CASCADE,
    FOREIGN KEY (party_id) REFERENCES parties(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(150) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    phone VARCHAR(20) DEFAULT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('national_admin','regional_officer','constituency_officer','polling_agent') NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    last_login DATETIME DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Geography scope for a user. region_id/constituency_id/polling_station_id reference
-- npp_iam tables (cross-database, no FK). A user can hold multiple rows (e.g. an agent
-- covering several stations). A national_admin typically has zero rows = unrestricted.
CREATE TABLE IF NOT EXISTS user_assignments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    region_id INT DEFAULT NULL,
    constituency_id INT DEFAULT NULL,
    polling_station_id INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- One row per (polling_station, race, source) — the agent's field figures and the
-- officer's official figures live side by side for comparison. Only source='officer'
-- AND status='verified' rows count toward real aggregation.
CREATE TABLE IF NOT EXISTS station_returns (
    id INT AUTO_INCREMENT PRIMARY KEY,
    election_id INT NOT NULL,
    polling_station_id INT NOT NULL,
    race_id INT NOT NULL,
    source ENUM('agent','officer') NOT NULL,
    registered_voters INT DEFAULT NULL,
    ballots_issued INT DEFAULT NULL,
    rejected_ballots INT DEFAULT NULL,
    total_valid_votes INT DEFAULT NULL,
    submitted_by INT DEFAULT NULL,
    photo_path VARCHAR(255) DEFAULT NULL,
    status ENUM('submitted','verified','flagged','superseded') NOT NULL DEFAULT 'submitted',
    submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_return (polling_station_id, race_id, source),
    FOREIGN KEY (election_id) REFERENCES elections(id) ON DELETE CASCADE,
    FOREIGN KEY (race_id) REFERENCES races(id) ON DELETE CASCADE,
    FOREIGN KEY (submitted_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS station_return_votes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    station_return_id INT NOT NULL,
    candidate_id INT NOT NULL,
    votes INT NOT NULL DEFAULT 0,
    UNIQUE KEY uniq_return_candidate (station_return_id, candidate_id),
    FOREIGN KEY (station_return_id) REFERENCES station_returns(id) ON DELETE CASCADE,
    FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT DEFAULT NULL,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50) DEFAULT NULL,
    entity_id INT DEFAULT NULL,
    details VARCHAR(500) DEFAULT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
