-- ================================================================
-- Configuration initiale de la base de données MariaDB.
-- À exécuter automatiquement au démarrage du conteneur.
-- ================================================================

-- Créer les tables
CREATE TABLE IF NOT EXISTS voters (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    citizen_id VARCHAR(50) UNIQUE,
    public_key LONGBLOB,
    has_voted BOOLEAN DEFAULT FALSE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_citizen_id (citizen_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS elections (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    start_date DATETIME NOT NULL,
    end_date DATETIME NOT NULL,
    elgamal_p INT,
    elgamal_g INT,
    public_key LONGBLOB,
    is_active BOOLEAN DEFAULT TRUE,
    results_published BOOLEAN DEFAULT FALSE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS candidates (
    id INT PRIMARY KEY AUTO_INCREMENT,
    election_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    `order` INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (election_id) REFERENCES elections(id) ON DELETE CASCADE,
    INDEX idx_election (election_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS votes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    voter_id INT NOT NULL,
    election_id INT NOT NULL,
    candidate_id INT NOT NULL,
    encrypted_vote LONGBLOB NOT NULL,
    zero_knowledge_proof LONGBLOB,
    ballot_hash VARCHAR(64),
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    ip_address VARCHAR(45),
    FOREIGN KEY (voter_id) REFERENCES voters(id) ON DELETE CASCADE,
    FOREIGN KEY (election_id) REFERENCES elections(id) ON DELETE CASCADE,
    FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
    INDEX idx_voter_election (voter_id, election_id),
    INDEX idx_election (election_id),
    UNIQUE KEY unique_vote (voter_id, election_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS audit_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    action VARCHAR(100) NOT NULL,
    description TEXT,
    user_id INT,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    ip_address VARCHAR(45),
    user_agent VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES voters(id) ON DELETE SET NULL,
    INDEX idx_timestamp (timestamp)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insérer des données de test
INSERT INTO elections (name, description, start_date, end_date, elgamal_p, elgamal_g, is_active)
VALUES (
    'Élection Présidentielle 2025',
    'Vote pour la présidence',
    NOW(),
    DATE_ADD(NOW(), INTERVAL 7 DAY),
    23,
    5,
    TRUE
);

INSERT INTO candidates (election_id, name, description, `order`)
VALUES 
    (1, 'Alice Dupont', 'Candidate pour le changement', 1),
    (1, 'Bob Martin', 'Candidate pour la stabilité', 2),
    (1, 'Charlie Leclerc', 'Candidate pour l''innovation', 3),
    (1, 'Diana Fontaine', 'Candidate pour l''environnement', 4);