-- ElectVote Database Schema
-- Create database
CREATE DATABASE IF NOT EXISTS electvote CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE electvote;

-- Positions table
CREATE TABLE IF NOT EXISTS positions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    winner_count INT NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Sessions table
CREATE TABLE IF NOT EXISTS sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    status ENUM('draft', 'open', 'closed') DEFAULT 'draft',
    start_datetime DATETIME NOT NULL,
    end_datetime DATETIME NOT NULL,
    winner_count INT NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Sample data for positions
INSERT INTO positions (name) VALUES 
('President'), ('Vice President'), ('Secretary'), ('Treasurer'), ('Auditor')
ON DUPLICATE KEY UPDATE name = name;
