-- Use this script if you already have the database and need to add missing columns

-- Add missing columns to interviews table
ALTER TABLE interviews 
ADD COLUMN duration_minutes INT AFTER interview_type,
ADD COLUMN next_interview_type ENUM('initial', 'technical', 'hr', 'final') DEFAULT 'initial' AFTER next_interview_date;

-- Add missing columns to joining_details table if they don't exist
ALTER TABLE joining_details 
ADD COLUMN expected_joining_date DATE AFTER candidate_id,
ADD COLUMN work_location VARCHAR(100) AFTER reporting_manager,
ADD COLUMN joining_notes TEXT AFTER work_location,
ADD COLUMN joining_feedback TEXT AFTER joining_notes;

-- Rename joining_date to expected_joining_date if it exists
-- ALTER TABLE joining_details CHANGE joining_date expected_joining_date DATE;

-- Update interview_type enum to include process_ended
ALTER TABLE interviews 
MODIFY COLUMN interview_type ENUM('initial', 'technical', 'hr', 'final', 'reschedule', 'process_ended') DEFAULT 'initial';

-- Update result enum to include process_ended
ALTER TABLE interviews 
MODIFY COLUMN result ENUM('pending', 'shortlisted', 'selected', 'rejected', 'hold', 'process_ended') DEFAULT 'pending';
