CREATE DATABASE IF NOT EXISTS evoting_app
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE evoting_app;

CREATE TABLE admins (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username        VARCHAR(50) NOT NULL UNIQUE,
    password_hash   VARCHAR(255) NOT NULL,
    full_name       VARCHAR(100) NOT NULL,
    role            ENUM('superadmin','admin') NOT NULL DEFAULT 'admin',
    last_login_at   DATETIME NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE elections (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code            VARCHAR(50) NOT NULL UNIQUE,
    name            VARCHAR(150) NOT NULL,
    description     TEXT NULL,
    is_active       TINYINT(1) NOT NULL DEFAULT 1,
    start_at        DATETIME NULL,
    end_at          DATETIME NULL,
    created_by      INT UNSIGNED NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_elections_created_by
        FOREIGN KEY (created_by) REFERENCES admins(id)
        ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE candidates (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    election_id     INT UNSIGNED NOT NULL,
    name            VARCHAR(150) NOT NULL,
    photo_url       VARCHAR(255) NULL,
    description     TEXT NULL,
    sort_order      INT UNSIGNED NOT NULL DEFAULT 0,
    is_active       TINYINT(1) NOT NULL DEFAULT 1,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_candidates_election
        FOREIGN KEY (election_id) REFERENCES elections(id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    INDEX idx_candidates_election (election_id, is_active, sort_order)
) ENGINE=InnoDB;

CREATE TABLE tokens (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    election_id     INT UNSIGNED NOT NULL,
    token_code      CHAR(3) NOT NULL,
    batch_label     VARCHAR(100) NULL,
    is_used         TINYINT(1) NOT NULL DEFAULT 0,
    used_at         DATETIME NULL,
    used_ip         VARCHAR(45) NULL,
    user_agent      VARCHAR(255) NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by      INT UNSIGNED NULL,
    CONSTRAINT fk_tokens_election
        FOREIGN KEY (election_id) REFERENCES elections(id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_tokens_created_by
        FOREIGN KEY (created_by) REFERENCES admins(id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    UNIQUE KEY uniq_token_per_election (election_id, token_code),
    CONSTRAINT chk_token_code_digits
        CHECK (token_code REGEXP '^[0-9]{3}$')
) ENGINE=InnoDB;

CREATE TABLE votes (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    election_id     INT UNSIGNED NOT NULL,
    candidate_id    INT UNSIGNED NOT NULL,
    token_id        INT UNSIGNED NOT NULL,
    cast_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    client_ip       VARCHAR(45) NULL,
    user_agent      VARCHAR(255) NULL,
    client_fingerprint VARCHAR(100) NULL,
    CONSTRAINT fk_votes_election
        FOREIGN KEY (election_id) REFERENCES elections(id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_votes_candidate
        FOREIGN KEY (candidate_id) REFERENCES candidates(id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_votes_token
        FOREIGN KEY (token_id) REFERENCES tokens(id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    UNIQUE KEY uniq_vote_token (token_id),
    INDEX idx_votes_election_candidate (election_id, candidate_id),
    INDEX idx_votes_election (election_id)
) ENGINE=InnoDB;

CREATE TABLE audit_logs (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    election_id     INT UNSIGNED NULL,
    admin_id        INT UNSIGNED NULL,
    token_id        INT UNSIGNED NULL,
    vote_id         BIGINT UNSIGNED NULL,
    action_type     VARCHAR(50) NOT NULL,
    description     TEXT NULL,
    details         TEXT NULL,
    ip_address      VARCHAR(45) NULL,
    user_agent      VARCHAR(255) NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_audit_election
        FOREIGN KEY (election_id) REFERENCES elections(id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_audit_admin
        FOREIGN KEY (admin_id) REFERENCES admins(id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_audit_token
        FOREIGN KEY (token_id) REFERENCES tokens(id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_audit_vote
        FOREIGN KEY (vote_id) REFERENCES votes(id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    INDEX idx_audit_election (election_id),
    INDEX idx_audit_action (action_type),
    INDEX idx_audit_created_at (created_at)
) ENGINE=InnoDB;

/* Seed one admin (change password later)
   password: admin123  (bcrypt hash must be generated manually)
*/
