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

-- First, run the simplified migration if not already done
-- Add basic columns if they don't exist

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

-- 2. Add foreign key constraint linking internal candidates to companies
-- Check if foreign key already exists before adding
SET @foreign_key_exists = (
    SELECT COUNT(*) 
    FROM information_schema.KEY_COLUMN_USAGE 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'candidates' 
    AND CONSTRAINT_NAME LIKE '%company_id%'
);

-- Only add foreign key if it doesn't exist
SET @sql = IF(@foreign_key_exists = 0, 
    'ALTER TABLE candidates ADD FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL', 
    'SELECT "Foreign key already exists" as message'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 3. Check if approved_by column exists, if not add it
SET @column_exists = (
    SELECT COUNT(*) 
    FROM information_schema.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'candidates' 
    AND COLUMN_NAME = 'approved_by'
);

SET @sql = IF(@column_exists = 0, 
    'ALTER TABLE candidates ADD COLUMN approved_by INT NULL AFTER hr_approval_notes', 
    'SELECT "approved_by column already exists" as message'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 4. Check if hr_approved_by column exists in job_applications, if not add it
SET @column_exists = (
    SELECT COUNT(*) 
    FROM information_schema.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'job_applications' 
    AND COLUMN_NAME = 'hr_approved_by'
);

SET @sql = IF(@column_exists = 0, 
    'ALTER TABLE job_applications ADD COLUMN hr_approved_by INT NULL AFTER hr_approval_status', 
    'SELECT "hr_approved_by column already exists" as message'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 5. Add foreign key for approved_by in candidates (to company_users)
-- First check if foreign key exists
SET @foreign_key_exists = (
    SELECT COUNT(*) 
    FROM information_schema.KEY_COLUMN_USAGE 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'candidates' 
    AND COLUMN_NAME = 'approved_by'
    AND REFERENCED_TABLE_NAME IS NOT NULL
);

SET @sql = IF(@foreign_key_exists = 0, 
    'ALTER TABLE candidates ADD FOREIGN KEY (approved_by) REFERENCES company_users(id) ON DELETE SET NULL', 
    'SELECT "approved_by foreign key already exists" as message'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 6. Add foreign key for hr_approved_by in job_applications (to company_users)
-- First check if foreign key exists
SET @foreign_key_exists = (
    SELECT COUNT(*) 
    FROM information_schema.KEY_COLUMN_USAGE 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'job_applications' 
    AND COLUMN_NAME = 'hr_approved_by'
    AND REFERENCED_TABLE_NAME IS NOT NULL
);

SET @sql = IF(@foreign_key_exists = 0, 
    'ALTER TABLE job_applications ADD FOREIGN KEY (hr_approved_by) REFERENCES company_users(id) ON DELETE SET NULL', 
    'SELECT "hr_approved_by foreign key already exists" as message'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 7. Create a simple trigger for job applications (without complex logic for now)
DROP TRIGGER IF EXISTS set_company_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;
    
    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 ;

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