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

echo "<h2>Job Visibility Migration</h2>\n";

try {
    $database = new Database();
    $db = $database->getConnection();
    
    echo "<p>✓ Database connection successful</p>\n";
    
    // Check if job_visibility column already exists
    $stmt = $db->query("SHOW COLUMNS FROM jobs LIKE 'job_visibility'");
    if ($stmt->fetch()) {
        echo "<div style='color: orange; padding: 10px; background: #fff3cd; border: 1px solid #ffeaa7; border-radius: 5px;'>\n";
        echo "⚠ job_visibility column already exists in jobs table.\n";
        echo "</div>\n";
    } else {
        echo "<p>Adding job_visibility column to jobs table...</p>\n";
        
        // Add the column
        $db->exec("ALTER TABLE jobs ADD COLUMN job_visibility ENUM('external', 'internal', 'both') DEFAULT 'external' AFTER experience_level");
        echo "<div style='color: green;'>✓ Added job_visibility column</div>\n";
        
        // Add index
        try {
            $db->exec("ALTER TABLE jobs ADD INDEX idx_job_visibility (job_visibility)");
            echo "<div style='color: green;'>✓ Added index for job_visibility</div>\n";
        } catch (PDOException $e) {
            echo "<div style='color: orange;'>⚠ Index might already exist</div>\n";
        }
        
        // Update existing jobs to be external by default
        $stmt = $db->exec("UPDATE jobs SET job_visibility = 'external' WHERE job_visibility IS NULL");
        echo "<div style='color: green;'>✓ Updated existing jobs to 'external' visibility</div>\n";
    }
    
    // Verify the column was added
    echo "<h3>Verification:</h3>\n";
    $stmt = $db->query("DESCRIBE jobs");
    $columns = $stmt->fetchAll();
    
    $job_visibility_found = false;
    foreach ($columns as $column) {
        if ($column['Field'] === 'job_visibility') {
            $job_visibility_found = true;
            echo "<div style='color: green;'>✓ job_visibility column exists</div>\n";
            echo "<p><strong>Column details:</strong></p>\n";
            echo "<ul>\n";
            echo "<li>Type: {$column['Type']}</li>\n";
            echo "<li>Null: {$column['Null']}</li>\n";
            echo "<li>Default: {$column['Default']}</li>\n";
            echo "</ul>\n";
            break;
        }
    }
    
    if (!$job_visibility_found) {
        echo "<div style='color: red;'>✗ job_visibility column not found!</div>\n";
    } else {
        // Show current job visibility distribution
        echo "<h3>Current Job Visibility Distribution:</h3>\n";
        $stmt = $db->query("SELECT job_visibility, COUNT(*) as count FROM jobs GROUP BY job_visibility");
        $visibility_stats = $stmt->fetchAll();
        
        if (!empty($visibility_stats)) {
            echo "<table border='1' style='border-collapse: collapse; margin: 10px 0;'>\n";
            echo "<tr><th>Visibility</th><th>Count</th><th>Description</th></tr>\n";
            foreach ($visibility_stats as $stat) {
                $descriptions = [
                    'external' => 'Available to external candidates only',
                    'internal' => 'Available to internal employees only', 
                    'both' => 'Available to both internal and external candidates'
                ];
                $description = $descriptions[$stat['job_visibility']] ?? 'Unknown';
                echo "<tr><td>{$stat['job_visibility']}</td><td>{$stat['count']}</td><td>{$description}</td></tr>\n";
            }
            echo "</table>\n";
        }
        
        echo "<div style='color: green; padding: 15px; background: #d4edda; border: 1px solid #c3e6cb; margin: 20px 0; border-radius: 5px;'>\n";
        echo "<h4>🎉 SUCCESS!</h4>\n";
        echo "<p>Job visibility system has been added successfully!</p>\n";
        echo "<p><strong>Next steps:</strong></p>\n";
        echo "<ul>\n";
        echo "<li>Companies can now specify job visibility when posting jobs</li>\n";
        echo "<li>Job listings will be filtered based on candidate type</li>\n";
        echo "<li>Application restrictions will be enforced</li>\n";
        echo "</ul>\n";
        echo "<p><strong>Test the system:</strong></p>\n";
        echo "<p><a href='company/post-job.php' style='background: #28a745; color: white; padding: 10px 20px; text-decoration: none; border-radius: 4px; margin-right: 10px;'>Post New Job</a>\n";
        echo "<a href='jobs.php' style='background: #007bff; color: white; padding: 10px 20px; text-decoration: none; border-radius: 4px;'>View Job Listings</a></p>\n";
        echo "</div>\n";
    }
    
} catch (Exception $e) {
    echo "<div style='color: red; padding: 15px; background: #f8d7da; border: 1px solid #f5c6cb; margin: 20px 0; border-radius: 5px;'>\n";
    echo "<strong>Error:</strong> " . $e->getMessage() . "<br>\n";
    echo "<strong>File:</strong> " . $e->getFile() . "<br>\n";
    echo "<strong>Line:</strong> " . $e->getLine() . "\n";
    echo "</div>\n";
}
?>