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

echo "<h2>Adding company_id Column to Candidates Table</h2>\n";

try {
    $database = new Database();
    $db = $database->getConnection();
    
    echo "<p>✓ Database connection successful</p>\n";
    
    // Check if company_id column already exists
    $stmt = $db->query("SHOW COLUMNS FROM candidates LIKE 'company_id'");
    if ($stmt->fetch()) {
        echo "<div style='color: orange; padding: 10px; background: #fff3cd; border: 1px solid #ffeaa7; border-radius: 5px;'>\n";
        echo "⚠ company_id column already exists in candidates table.\n";
        echo "</div>\n";
    } else {
        // Add company_id column
        echo "<p>Adding company_id column to candidates table...</p>\n";
        $db->exec("ALTER TABLE candidates ADD COLUMN company_id INT NULL AFTER employee_code");
        echo "<div style='color: green; padding: 10px; background: #d4edda; border: 1px solid #c3e6cb; border-radius: 5px;'>\n";
        echo "✓ Successfully added company_id column to candidates table.\n";
        echo "</div>\n";
        
        // Add index for better performance
        try {
            $db->exec("ALTER TABLE candidates ADD INDEX idx_company_id (company_id)");
            echo "<p>✓ Added index for company_id column</p>\n";
        } catch (PDOException $e) {
            echo "<p>⚠ Index might already exist: " . substr($e->getMessage(), 0, 50) . "...</p>\n";
        }
        
        // Add foreign key constraint
        try {
            $db->exec("ALTER TABLE candidates ADD FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL");
            echo "<p>✓ Added foreign key constraint linking candidates to companies</p>\n";
        } catch (PDOException $e) {
            echo "<p>⚠ Foreign key constraint issue: " . substr($e->getMessage(), 0, 80) . "...</p>\n";
        }
    }
    
    // Verify the column was added
    echo "<h3>Verification:</h3>\n";
    $stmt = $db->query("DESCRIBE candidates");
    $columns = $stmt->fetchAll();
    
    $company_id_found = false;
    foreach ($columns as $column) {
        if ($column['Field'] === 'company_id') {
            $company_id_found = true;
            echo "<div style='color: green;'>✓ company_id column exists - Type: {$column['Type']}, Null: {$column['Null']}</div>\n";
            break;
        }
    }
    
    if (!$company_id_found) {
        echo "<div style='color: red;'>✗ company_id column still not found!</div>\n";
        echo "<p>Please run the full migration: <a href='quick_fix_columns_v2.php'>quick_fix_columns_v2.php</a></p>\n";
    } else {
        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>The company_id column has been added successfully. The dashboard should now work!</p>\n";
        echo "<p><a href='company/dashboard.php' style='background: #28a745; color: white; padding: 10px 20px; text-decoration: none; border-radius: 4px;'>Go to Company Dashboard</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";
}
?>