-- Assignment Reminder Schema Updates

-- Add reminder fields to admin_assignments table
ALTER TABLE `admin_assignments` 
ADD COLUMN `reminder_sent` TINYINT(1) DEFAULT 0 COMMENT 'Whether upcoming due reminder was sent',
ADD COLUMN `last_overdue_reminder` DATETIME NULL COMMENT 'When the last overdue reminder was sent',
ADD COLUMN `overdue_reminder_count` INT DEFAULT 0 COMMENT 'Number of overdue reminders sent',
ADD COLUMN `inactive_reminder_sent` TINYINT(1) DEFAULT 0 COMMENT 'Whether inactive assignment reminder was sent',
ADD COLUMN `inactive_reminder_sent_at` DATETIME NULL COMMENT 'When the inactive reminder was sent';

-- Create indices for optimizing reminder queries
CREATE INDEX idx_assignments_status_due_date ON admin_assignments(status, due_date);
CREATE INDEX idx_assignments_status_updated_at ON admin_assignments(status, updated_at);
