database.sql

-- Database Script for Online Voting System (OVS)
-- Optimized for Spring Boot + Security (BCrypt)

CREATE DATABASE IF NOT EXISTS ovs_db;
USE ovs_db;

CREATE TABLE IF NOT EXISTS admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL
);

CREATE TABLE IF NOT EXISTS voters (
    id INT AUTO_INCREMENT PRIMARY KEY,
    voter_id VARCHAR(50) NOT NULL UNIQUE,
    full_name VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    has_voted BOOLEAN DEFAULT FALSE
);

CREATE TABLE IF NOT EXISTS candidates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    position VARCHAR(100) NOT NULL,
    description TEXT
);

CREATE TABLE IF NOT EXISTS votes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    voter_id INT NOT NULL,
    candidate_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (voter_id) REFERENCES voters(id),
    FOREIGN KEY (candidate_id) REFERENCES candidates(id)
);

CREATE TABLE IF NOT EXISTS election_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    start_time DATETIME,
    end_time DATETIME,
    results_visible BOOLEAN DEFAULT TRUE
);

-- Default Admin (Password: admin123)
-- Hash generated via BCrypt
INSERT IGNORE INTO admins (username, password_hash) 
VALUES ('admin', '$2a$10$8.UnVuG9HHgffUDAlk8qfOuVGkqRzgVymGe07OfGr7ODzByq89336');

-- Sample Candidates
INSERT IGNORE INTO candidates (full_name, position, description) VALUES 
('Alice Smith', 'President', 'Experienced leader with a vision for digital transformation.'),
('Bob Johnson', 'President', 'Community advocate focused on transparency and inclusion.'),
('Charlie Brown', 'Vice President', 'Logistics expert with 10 years of organizational experience.');