-- Add is_archived and archived_at columns to notifications table
ALTER TABLE notifications ADD COLUMN is_archived INTEGER DEFAULT 0 NOT NULL;
ALTER TABLE notifications ADD COLUMN archived_at DATETIME NULL;

-- Create index for is_archived since it will be used in queries
CREATE INDEX IF NOT EXISTS idx_notifications_is_archived ON notifications(is_archived);

-- Migrate existing archived status to is_archived flag
UPDATE notifications SET is_archived = 1, archived_at = created_at WHERE status = 'archived';