-- Add longlisted status to job_applications table
-- This script adds 'longlisted' status between 'applied' and 'shortlisted'

USE recruitment_system;

-- Update the job_applications table to include longlisted status
ALTER TABLE job_applications 
MODIFY COLUMN status ENUM('applied', 'longlisted', 'shortlisted', 'interviewed', 'hired', 'rejected') DEFAULT 'applied';

-- Update any existing applications that need status adjustments (optional)
-- This is commented out to preserve existing data integrity
-- UPDATE job_applications SET status = 'longlisted' WHERE status = 'applied' AND updated_at < DATE_SUB(NOW(), INTERVAL 7 DAY);

SELECT 'Longlisted status successfully added to job_applications table' as message;