-- Update candidate_status_history table to include additional fields for better tracking
ALTER TABLE candidate_status_history 
ADD COLUMN IF NOT EXISTS changed_by_name VARCHAR(100) AFTER changed_by,
ADD COLUMN IF NOT EXISTS additional_data TEXT AFTER reason;

-- Create index for better performance
CREATE INDEX IF NOT EXISTS idx_candidate_status_history_candidate_id ON candidate_status_history(candidate_id);
CREATE INDEX IF NOT EXISTS idx_candidate_status_history_changed_at ON candidate_status_history(changed_at);

-- Update existing records to include changed_by_name
UPDATE candidate_status_history csh 
JOIN users u ON csh.changed_by = u.id 
SET csh.changed_by_name = u.username 
WHERE csh.changed_by_name IS NULL;
