<?php
require_once 'config/config.php';

echo "<h2>Simplified Internal/External Candidate Migration</h2>\n";

try {
    $database = new Database();
    $db = $database->getConnection();
    
    echo "<p>Connected to database successfully.</p>\n";
    
    // Read the simplified SQL migration file
    $sqlFile = __DIR__ . '/simplified_internal_external_update.sql';
    if (!file_exists($sqlFile)) {
        throw new Exception("Migration file not found: $sqlFile");
    }
    
    $sql = file_get_contents($sqlFile);
    echo "<p>Migration file loaded successfully.</p>\n";
    
    // Split SQL statements and execute them
    $statements = array_filter(array_map('trim', explode(';', $sql)));
    
    $executed = 0;
    $errors = 0;
    
    foreach ($statements as $statement) {
        if (empty($statement) || substr($statement, 0, 2) === '--' || substr($statement, 0, 9) === 'DELIMITER') {
            continue;
        }
        
        try {
            $db->exec($statement);
            $executed++;
            echo "<div style='color: green;'>✓ Executed: " . substr($statement, 0, 80) . "...</div>\n";
        } catch (PDOException $e) {
            $errors++;
            // Check if it's just a "table already exists" or "column already exists" error
            if (strpos($e->getMessage(), 'already exists') !== false || 
                strpos($e->getMessage(), 'Duplicate column') !== false ||
                strpos($e->getMessage(), 'Duplicate key') !== false) {
                echo "<div style='color: orange;'>⚠ Skipped (already exists): " . substr($statement, 0, 80) . "...</div>\n";
            } else {
                echo "<div style='color: red;'>✗ Error: " . $e->getMessage() . "</div>\n";
                echo "<div style='color: red;'>Statement: " . substr($statement, 0, 100) . "...</div>\n";
            }
        }
    }
    
    echo "<hr>\n";
    echo "<p><strong>Migration Summary:</strong></p>\n";
    echo "<p>✓ Executed: $executed statements</p>\n";
    echo "<p>⚠ Errors/Skipped: $errors statements</p>\n";
    
    // Verify key columns were added
    echo "<hr>\n";
    echo "<p><strong>Verification:</strong></p>\n";
    
    // Check if candidates table has new columns
    try {
        $stmt = $db->query("DESCRIBE candidates");
        $columns = $stmt->fetchAll(PDO::FETCH_COLUMN);
        
        $required_columns = ['candidate_type', 'employee_code', 'hr_approval_status'];
        $missing_columns = [];
        
        foreach ($required_columns as $col) {
            if (!in_array($col, $columns)) {
                $missing_columns[] = $col;
            }
        }
        
        if (empty($missing_columns)) {
            echo "<div style='color: green;'>✓ Candidates table updated with all required columns</div>\n";
        } else {
            echo "<div style='color: red;'>✗ Candidates table missing columns: " . implode(', ', $missing_columns) . "</div>\n";
        }
        
    } catch (PDOException $e) {
        echo "<div style='color: red;'>✗ Could not verify candidates table: " . $e->getMessage() . "</div>\n";
    }
    
    // Check if job_applications table has new columns
    try {
        $stmt = $db->query("DESCRIBE job_applications");
        $columns = $stmt->fetchAll(PDO::FETCH_COLUMN);
        
        $required_columns = ['hr_approval_required', 'hr_approval_status'];
        $missing_columns = [];
        
        foreach ($required_columns as $col) {
            if (!in_array($col, $columns)) {
                $missing_columns[] = $col;
            }
        }
        
        if (empty($missing_columns)) {
            echo "<div style='color: green;'>✓ Job applications table updated with HR approval columns</div>\n";
        } else {
            echo "<div style='color: red;'>✗ Job applications table missing columns: " . implode(', ', $missing_columns) . "</div>\n";
        }
        
    } catch (PDOException $e) {
        echo "<div style='color: red;'>✗ Could not verify job applications table: " . $e->getMessage() . "</div>\n";
    }
    
    echo "<hr>\n";
    echo "<p><strong>How it works now:</strong></p>\n";
    echo "<ol>\n";
    echo "<li><strong>Registration:</strong> Internal candidates just enter their employee code (no validation)</li>\n";
    echo "<li><strong>HR Approval:</strong> Internal candidates need HR approval before they can login</li>\n";
    echo "<li><strong>Job Applications:</strong> Once approved, internal candidates can apply for jobs normally</li>\n";
    echo "</ol>\n";
    
    echo "<hr>\n";
    echo "<p><strong>Next Steps:</strong></p>\n";
    echo "<ol>\n";
    echo "<li>Test internal candidate registration at <a href='auth/register.php'>Register</a></li>\n";
    echo "<li>Go to <a href='admin/internal-candidates.php'>Admin > Internal Candidates</a> to approve candidates</li>\n";
    echo "<li>Test the complete workflow</li>\n";
    echo "</ol>\n";
    
} catch (Exception $e) {
    echo "<div style='color: red;'><strong>Fatal Error:</strong> " . $e->getMessage() . "</div>\n";
    echo "<p>Please check your database configuration and try again.</p>\n";
}
?>