-- Internal/External Candidate System Database Updates
-- Run this script to add support for internal/external candidate types

-- 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 department VARCHAR(100) NULL AFTER employee_code,
ADD COLUMN manager_name VARCHAR(100) NULL AFTER department,
ADD COLUMN manager_email VARCHAR(100) NULL AFTER manager_name,
ADD COLUMN hr_approval_status ENUM('pending', 'approved', 'rejected') DEFAULT 'approved' AFTER manager_email,
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. Create employee codes table for validation
CREATE TABLE employee_codes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_code VARCHAR(20) UNIQUE NOT NULL,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(100),
    position VARCHAR(100),
    manager_name VARCHAR(100),
    manager_email VARCHAR(100),
    status ENUM('active', 'inactive') DEFAULT 'active',
    hire_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_employee_code (employee_code),
    INDEX idx_status (status)
);

-- 4. Create HR approval workflow table
CREATE TABLE hr_approvals (
    id INT PRIMARY KEY AUTO_INCREMENT,
    application_id INT NOT NULL,
    candidate_id INT NOT NULL,
    job_id INT NOT NULL,
    hr_user_id INT NULL,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    approval_date TIMESTAMP NULL,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (application_id) REFERENCES job_applications(id) ON DELETE CASCADE,
    FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
    FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE,
    FOREIGN KEY (hr_user_id) REFERENCES admins(id) ON DELETE SET NULL,
    INDEX idx_status (status),
    INDEX idx_application_id (application_id),
    INDEX idx_candidate_id (candidate_id)
);

-- 5. Add internal candidate notification preferences
CREATE TABLE internal_candidate_notifications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    candidate_id INT NOT NULL,
    manager_notification BOOLEAN DEFAULT TRUE,
    hr_notification BOOLEAN DEFAULT TRUE,
    department_notification BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
    UNIQUE KEY unique_candidate_notification (candidate_id)
);

-- 6. Insert sample employee codes for testing (optional)
INSERT INTO employee_codes (employee_code, employee_name, department, position, manager_name, manager_email) VALUES
('EMP001', 'John Doe', 'IT Department', 'Software Developer', 'Jane Smith', 'jane.smith@company.com'),
('EMP002', 'Mary Johnson', 'HR Department', 'HR Specialist', 'Bob Wilson', 'bob.wilson@company.com'),
('EMP003', 'David Brown', 'Marketing', 'Marketing Coordinator', 'Lisa Davis', 'lisa.davis@company.com'),
('EMP004', 'Sarah Wilson', 'Finance', 'Financial Analyst', 'Mike Thompson', 'mike.thompson@company.com'),
('EMP005', 'Tom Anderson', 'Operations', 'Operations Manager', 'Susan Clark', 'susan.clark@company.com');

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

-- 8. 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 ;

-- 9. 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;

-- 10. 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;