-- Migration to allow graduates to apply for jobs
-- This script modifies the applications table to support both students and graduates

-- Step 1: Create a temporary table to hold existing data
CREATE TABLE applications_temp AS SELECT * FROM applications;

-- Step 2: Drop the existing applications table
DROP TABLE applications;

-- Step 3: Create the new applications table with support for both students and graduates
CREATE TABLE applications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    job_id INT NOT NULL,
    applicant_id INT NOT NULL,
    applicant_type ENUM('student', 'graduate') NOT NULL DEFAULT 'student',
    status ENUM('pending', 'reviewed', 'accepted', 'rejected') DEFAULT 'pending',
    applied_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    notes TEXT,
    FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE,
    UNIQUE KEY unique_application (job_id, applicant_id, applicant_type)
);

-- Step 4: Migrate existing data (all existing applications are from students)
INSERT INTO applications (id, job_id, applicant_id, applicant_type, status, applied_date, notes)
SELECT id, job_id, student_id, 'student', status, applied_date, notes
FROM applications_temp;

-- Step 5: Drop the temporary table
DROP TABLE applications_temp;

-- Step 6: Update indexes for better performance
CREATE INDEX idx_applications_job ON applications(job_id);
CREATE INDEX idx_applications_applicant ON applications(applicant_id, applicant_type);