ALTER TABLE `assessment_campaigns`
    ADD COLUMN `recurrence_frequency` ENUM('NONE', 'WEEKLY', 'MONTHLY', 'YEARLY') NOT NULL DEFAULT 'NONE' AFTER `is_auto_generated`,
    ADD COLUMN `recurrence_interval` INTEGER NOT NULL DEFAULT 1 AFTER `recurrence_frequency`,
    ADD COLUMN `auto_assign_mode` ENUM('NONE', 'SELECTED_EMPLOYEES', 'ALL_ACTIVE_EMPLOYEES') NOT NULL DEFAULT 'NONE' AFTER `recurrence_interval`,
    ADD COLUMN `auto_assign_employee_ids` JSON NULL AFTER `auto_assign_mode`,
    ADD COLUMN `next_run_at` DATETIME(3) NULL AFTER `auto_assign_employee_ids`,
    ADD COLUMN `last_generated_at` DATETIME(3) NULL AFTER `next_run_at`,
    ADD COLUMN `source_campaign_id` VARCHAR(191) NULL AFTER `last_generated_at`,
    ADD INDEX `idx_campaigns_recurrence_next_run`(`recurrence_frequency`, `next_run_at`),
    ADD INDEX `idx_campaigns_source_campaign_id`(`source_campaign_id`),
    ADD CONSTRAINT `assessment_campaigns_source_campaign_id_fkey`
      FOREIGN KEY (`source_campaign_id`) REFERENCES `assessment_campaigns`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;