ALTER TABLE generated_exams
    ADD COLUMN IF NOT EXISTS status VARCHAR(30) NOT NULL DEFAULT 'draft' AFTER term,
    ADD COLUMN IF NOT EXISTS review_notes TEXT NULL AFTER generation_warnings,
    ADD COLUMN IF NOT EXISTS reviewed_by INT(11) NULL AFTER last_edited_by,
    ADD COLUMN IF NOT EXISTS reviewed_at DATETIME NULL AFTER reviewed_by,
    ADD COLUMN IF NOT EXISTS approved_by INT(11) NULL AFTER reviewed_at,
    ADD COLUMN IF NOT EXISTS approved_at DATETIME NULL AFTER approved_by,
    ADD COLUMN IF NOT EXISTS published_by INT(11) NULL AFTER approved_at,
    ADD COLUMN IF NOT EXISTS published_at DATETIME NULL AFTER published_by;

CREATE TABLE IF NOT EXISTS exam_workflow_logs (
    id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    exam_id INT(11) NOT NULL,
    action VARCHAR(40) NOT NULL,
    from_status VARCHAR(30) NULL,
    to_status VARCHAR(30) NULL,
    note TEXT NULL,
    acted_by INT(11) NULL,
    acted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_exam (exam_id),
    INDEX idx_acted_at (acted_at)
);

