-- Scheduled Emails Table
CREATE TABLE IF NOT EXISTS `scheduled_emails` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `subject` VARCHAR(255) NOT NULL,
    `message` TEXT NOT NULL,
    `recipient_type` ENUM('individual', 'all') NOT NULL DEFAULT 'all',
    `user_ids` TEXT DEFAULT NULL COMMENT 'Comma-separated user IDs for individual sends',
    `scheduled_at` DATETIME NOT NULL,
    `status` ENUM('pending', 'sent', 'failed') NOT NULL DEFAULT 'pending',
    `sent_count` INT DEFAULT 0,
    `failed_count` INT DEFAULT 0,
    `created_by` INT NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `sent_at` DATETIME DEFAULT NULL,
    INDEX `idx_status_scheduled` (`status`, `scheduled_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
