CREATE TABLE IF NOT EXISTS exam_generation_jobs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    created_by INT NOT NULL,
    status ENUM('queued', 'processing', 'retrying', 'completed', 'failed') NOT NULL DEFAULT 'queued',
    request_payload LONGTEXT NOT NULL,
    attempts INT NOT NULL DEFAULT 0,
    max_attempts INT NOT NULL DEFAULT 3,
    available_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    started_at DATETIME NULL,
    locked_at DATETIME NULL,
    completed_at DATETIME NULL,
    exam_id INT NULL,
    generator_used VARCHAR(100) NULL,
    job_warnings LONGTEXT NULL,
    last_error TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_exam_generation_status (status, available_at),
    KEY idx_exam_generation_creator (created_by),
    KEY idx_exam_generation_exam (exam_id)
);

CREATE TABLE IF NOT EXISTS exam_generation_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    job_id INT NOT NULL,
    log_level VARCHAR(20) NOT NULL DEFAULT 'info',
    message VARCHAR(255) NOT NULL,
    context_json LONGTEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_exam_generation_logs_job (job_id, id)
);

