-- Tabla para manejar la cola de envío de certificados
CREATE TABLE IF NOT EXISTS email_queue (
    id INT AUTO_INCREMENT PRIMARY KEY,
    runner_id VARCHAR(50) NOT NULL,
    runner_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    race_category VARCHAR(100) NOT NULL,
    bib_number INT NOT NULL,
    position INT NOT NULL,
    finish_time VARCHAR(20),
    gun_time VARCHAR(20),
    status ENUM('pending', 'sent', 'failed') DEFAULT 'pending',
    attempts INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    sent_at TIMESTAMP NULL,
    last_attempt_at TIMESTAMP NULL,
    error_message TEXT NULL,
    INDEX idx_status (status),
    INDEX idx_email (email),
    INDEX idx_runner_id (runner_id),
    UNIQUE KEY unique_runner_email (runner_id, email)
);

-- Tabla para el log de envíos masivos
CREATE TABLE IF NOT EXISTS email_batch_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    batch_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_queued INT DEFAULT 0,
    total_sent INT DEFAULT 0,
    total_failed INT DEFAULT 0,
    execution_time DECIMAL(10,2) DEFAULT 0,
    notes TEXT
);
