-- Simplified Internal/External Candidate System Database Updates
-- Run this script to add support for internal/external candidate types (NO employee code validation)

-- 1. Add candidate_type and employee_code fields to candidates table
ALTER TABLE candidates 
ADD COLUMN candidate_type ENUM('internal', 'external') DEFAULT 'external' AFTER gender,
ADD COLUMN employee_code VARCHAR(20) NULL AFTER candidate_type,
ADD COLUMN hr_approval_status ENUM('pending', 'approved', 'rejected') DEFAULT 'approved' AFTER employee_code,
ADD COLUMN hr_approval_date TIMESTAMP NULL AFTER hr_approval_status,
ADD COLUMN hr_approval_notes TEXT NULL AFTER hr_approval_date,
ADD COLUMN approved_by INT NULL AFTER hr_approval_notes,
ADD INDEX idx_candidate_type (candidate_type),
ADD INDEX idx_employee_code (employee_code),
ADD INDEX idx_hr_approval_status (hr_approval_status);

-- 2. Add HR approval status to job applications for internal candidates
ALTER TABLE job_applications 
ADD COLUMN hr_approval_required BOOLEAN DEFAULT FALSE AFTER status,
ADD COLUMN hr_approval_status ENUM('pending', 'approved', 'rejected') NULL AFTER hr_approval_required,
ADD COLUMN hr_approved_by INT NULL AFTER hr_approval_status,
ADD COLUMN hr_approval_date TIMESTAMP NULL AFTER hr_approved_by,
ADD COLUMN hr_approval_notes TEXT NULL AFTER hr_approval_date,
ADD INDEX idx_hr_approval_status (hr_approval_status);

-- 3. Update existing external candidates to have approved HR status
UPDATE candidates 
SET hr_approval_status = 'approved', 
    hr_approval_date = NOW() 
WHERE candidate_type = 'external';

-- 4. Create trigger to automatically set HR approval requirements
DELIMITER //
CREATE TRIGGER set_hr_approval_requirement 
    BEFORE INSERT ON job_applications
    FOR EACH ROW
BEGIN
    DECLARE candidate_is_internal BOOLEAN DEFAULT FALSE;
    
    SELECT (candidate_type = 'internal') INTO candidate_is_internal
    FROM candidates 
    WHERE id = NEW.candidate_id;
    
    IF candidate_is_internal THEN
        SET NEW.hr_approval_required = TRUE;
        SET NEW.hr_approval_status = 'pending';
    ELSE
        SET NEW.hr_approval_required = FALSE;
        SET NEW.hr_approval_status = NULL;
    END IF;
END//
DELIMITER ;

-- 5. Add foreign key constraint for approved_by field in candidates table
ALTER TABLE candidates 
ADD FOREIGN KEY (approved_by) REFERENCES admins(id) ON DELETE SET NULL;

-- 6. Add foreign key constraint for hr_approved_by field in job_applications table  
ALTER TABLE job_applications 
ADD FOREIGN KEY (hr_approved_by) REFERENCES admins(id) ON DELETE SET NULL;

COMMIT;