-- Company Approval System for Internal Candidates
-- Update database to allow companies to approve their internal candidates

-- 1. Add company_id field to candidates table for internal candidates
ALTER TABLE candidates 
ADD COLUMN company_id INT NULL AFTER employee_code,
ADD INDEX idx_company_id (company_id);

-- 2. Add foreign key constraint linking internal candidates to companies
ALTER TABLE candidates 
ADD FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL;

-- 3. Update the approved_by field to reference company_users instead of admins
ALTER TABLE candidates 
DROP FOREIGN KEY candidates_ibfk_1;

ALTER TABLE candidates 
ADD FOREIGN KEY (approved_by) REFERENCES company_users(id) ON DELETE SET NULL;

-- 4. Update job_applications approved_by to reference company_users
ALTER TABLE job_applications 
DROP FOREIGN KEY job_applications_ibfk_4;

ALTER TABLE job_applications 
ADD FOREIGN KEY (hr_approved_by) REFERENCES company_users(id) ON DELETE SET NULL;

-- 5. Update the trigger to handle company approval workflow
DROP TRIGGER IF EXISTS set_hr_approval_requirement;

DELIMITER //
CREATE TRIGGER set_company_approval_requirement 
    BEFORE INSERT ON job_applications
    FOR EACH ROW
BEGIN
    DECLARE candidate_is_internal BOOLEAN DEFAULT FALSE;
    DECLARE candidate_company_id INT DEFAULT NULL;
    
    SELECT (candidate_type = 'internal'), company_id INTO candidate_is_internal, candidate_company_id
    FROM candidates 
    WHERE id = NEW.candidate_id;
    
    IF candidate_is_internal THEN
        SET NEW.hr_approval_required = TRUE;
        SET NEW.hr_approval_status = 'pending';
        
        -- Only allow applications to the same company for internal candidates
        DECLARE job_company_id INT;
        SELECT company_id INTO job_company_id FROM jobs WHERE id = NEW.job_id;
        
        IF candidate_company_id != job_company_id THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Internal candidates can only apply for jobs within their own company';
        END IF;
    ELSE
        SET NEW.hr_approval_required = FALSE;
        SET NEW.hr_approval_status = NULL;
    END IF;
END//
DELIMITER ;

-- 6. Create a view for company internal candidates management
CREATE OR REPLACE VIEW company_internal_candidates AS
SELECT 
    c.*,
    cu.name as approved_by_name,
    comp.name as company_name,
    (SELECT COUNT(*) FROM job_applications ja WHERE ja.candidate_id = c.id) as applications_count
FROM candidates c
LEFT JOIN company_users cu ON c.approved_by = cu.id
LEFT JOIN companies comp ON c.company_id = comp.id
WHERE c.candidate_type = 'internal';

COMMIT;