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

echo "<h2>Quick Fix: Adding Missing Columns (v2)</h2>\n";

try {
    $database = new Database();
    $db = $database->getConnection();
    
    // Set buffered queries to avoid the PDO error
    $db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
    
    echo "<p>✓ Database connection successful</p>\n";
    
    // First, check what columns already exist
    $stmt = $db->query("SHOW COLUMNS FROM candidates");
    $existing_columns = $stmt->fetchAll(PDO::FETCH_COLUMN);
    $stmt->closeCursor(); // Close cursor to free up connection
    
    echo "<h3>Current candidates table columns:</h3>\n";
    echo "<p>" . implode(', ', $existing_columns) . "</p>\n";
    
    // Define columns to add with their checks
    $columns_to_add = [
        [
            'name' => 'candidate_type',
            'sql' => "ALTER TABLE candidates ADD COLUMN candidate_type ENUM('internal', 'external') DEFAULT 'external' AFTER gender"
        ],
        [
            'name' => 'employee_code',
            'sql' => "ALTER TABLE candidates ADD COLUMN employee_code VARCHAR(20) NULL AFTER candidate_type"
        ],
        [
            'name' => 'company_id',
            'sql' => "ALTER TABLE candidates ADD COLUMN company_id INT NULL AFTER employee_code"
        ],
        [
            'name' => 'hr_approval_status',
            'sql' => "ALTER TABLE candidates ADD COLUMN hr_approval_status ENUM('pending', 'approved', 'rejected') DEFAULT 'approved' AFTER company_id"
        ],
        [
            'name' => 'hr_approval_date',
            'sql' => "ALTER TABLE candidates ADD COLUMN hr_approval_date TIMESTAMP NULL AFTER hr_approval_status"
        ],
        [
            'name' => 'hr_approval_notes',
            'sql' => "ALTER TABLE candidates ADD COLUMN hr_approval_notes TEXT NULL AFTER hr_approval_date"
        ],
        [
            'name' => 'approved_by',
            'sql' => "ALTER TABLE candidates ADD COLUMN approved_by INT NULL AFTER hr_approval_notes"
        ]
    ];
    
    $success_count = 0;
    $skip_count = 0;
    
    echo "<h3>Adding missing columns to candidates table:</h3>\n";
    
    foreach ($columns_to_add as $column) {
        if (in_array($column['name'], $existing_columns)) {
            $skip_count++;
            echo "<div style='color: orange;'>⚠ Column '{$column['name']}' already exists - skipped</div>\n";
        } else {
            try {
                $db->exec($column['sql']);
                $success_count++;
                echo "<div style='color: green;'>✓ Added column '{$column['name']}'</div>\n";
            } catch (PDOException $e) {
                echo "<div style='color: red;'>✗ Error adding '{$column['name']}': " . $e->getMessage() . "</div>\n";
            }
        }
    }
    
    // Check job_applications table
    $stmt = $db->query("SHOW COLUMNS FROM job_applications");
    $job_app_columns = $stmt->fetchAll(PDO::FETCH_COLUMN);
    $stmt->closeCursor();
    
    echo "<h3>Adding missing columns to job_applications table:</h3>\n";
    
    $job_columns_to_add = [
        [
            'name' => 'hr_approval_required',
            'sql' => "ALTER TABLE job_applications ADD COLUMN hr_approval_required BOOLEAN DEFAULT FALSE AFTER status"
        ],
        [
            'name' => 'hr_approval_status',
            'sql' => "ALTER TABLE job_applications ADD COLUMN hr_approval_status ENUM('pending', 'approved', 'rejected') NULL AFTER hr_approval_required"
        ],
        [
            'name' => 'hr_approved_by',
            'sql' => "ALTER TABLE job_applications ADD COLUMN hr_approved_by INT NULL AFTER hr_approval_status"
        ],
        [
            'name' => 'hr_approval_date',
            'sql' => "ALTER TABLE job_applications ADD COLUMN hr_approval_date TIMESTAMP NULL AFTER hr_approved_by"
        ],
        [
            'name' => 'hr_approval_notes',
            'sql' => "ALTER TABLE job_applications ADD COLUMN hr_approval_notes TEXT NULL AFTER hr_approval_date"
        ]
    ];
    
    foreach ($job_columns_to_add as $column) {
        if (in_array($column['name'], $job_app_columns)) {
            $skip_count++;
            echo "<div style='color: orange;'>⚠ Column '{$column['name']}' already exists in job_applications - skipped</div>\n";
        } else {
            try {
                $db->exec($column['sql']);
                $success_count++;
                echo "<div style='color: green;'>✓ Added column '{$column['name']}' to job_applications</div>\n";
            } catch (PDOException $e) {
                echo "<div style='color: red;'>✗ Error adding '{$column['name']}' to job_applications: " . $e->getMessage() . "</div>\n";
            }
        }
    }
    
    // Add indexes (ignore if they already exist)
    echo "<h3>Adding indexes:</h3>\n";
    $indexes = [
        "ALTER TABLE candidates ADD INDEX idx_candidate_type (candidate_type)",
        "ALTER TABLE candidates ADD INDEX idx_employee_code (employee_code)",
        "ALTER TABLE candidates ADD INDEX idx_company_id (company_id)",
        "ALTER TABLE candidates ADD INDEX idx_hr_approval_status (hr_approval_status)",
    ];
    
    foreach ($indexes as $sql) {
        try {
            $db->exec($sql);
            echo "<div style='color: green;'>✓ Added index</div>\n";
        } catch (PDOException $e) {
            echo "<div style='color: orange;'>⚠ Index might already exist: " . substr($e->getMessage(), 0, 50) . "...</div>\n";
        }
    }
    
    // Add foreign key (ignore if it already exists)
    echo "<h3>Adding foreign key constraints:</h3>\n";
    try {
        $db->exec("ALTER TABLE candidates ADD FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL");
        echo "<div style='color: green;'>✓ Added company foreign key constraint</div>\n";
    } catch (PDOException $e) {
        echo "<div style='color: orange;'>⚠ Foreign key constraint issue: " . substr($e->getMessage(), 0, 100) . "...</div>\n";
    }
    
    echo "<hr>\n";
    echo "<h3>Final Verification:</h3>\n";
    
    // Re-check candidates table structure
    $stmt = $db->query("SHOW COLUMNS FROM candidates");
    $final_columns = $stmt->fetchAll(PDO::FETCH_COLUMN);
    $stmt->closeCursor();
    
    $required_columns = ['candidate_type', 'employee_code', 'company_id', 'hr_approval_status'];
    $all_exist = true;
    
    foreach ($required_columns as $col) {
        if (in_array($col, $final_columns)) {
            echo "<div style='color: green;'>✓ $col column exists</div>\n";
        } else {
            echo "<div style='color: red;'>✗ $col column still missing</div>\n";
            $all_exist = false;
        }
    }
    
    echo "<hr>\n";
    echo "<h3>Summary:</h3>\n";
    echo "<p>✅ Successfully added: $success_count columns/constraints</p>\n";
    echo "<p>⚠️ Skipped (already existed): $skip_count items</p>\n";
    
    if ($all_exist) {
        echo "<div style='color: green; padding: 15px; background: #d4edda; border: 1px solid #c3e6cb; margin: 20px 0; border-radius: 5px;'>\n";
        echo "<h4>🎉 SUCCESS! All required columns have been added.</h4>\n";
        echo "<p><strong>Registration should now work!</strong></p>\n";
        echo "<p><a href='auth/register.php' style='background: #28a745; color: white; padding: 10px 20px; text-decoration: none; border-radius: 4px; display: inline-block; margin-top: 10px;'>🔗 Try Registration Now</a></p>\n";
        echo "</div>\n";
        
        // Show next steps
        echo "<h3>Next Steps:</h3>\n";
        echo "<ol>\n";
        echo "<li><a href='auth/register.php'>Test candidate registration</a> (try both internal and external)</li>\n";
        echo "<li>If registering as internal candidate, make sure you have approved companies</li>\n";
        echo "<li>Test company approval workflow at <a href='company/internal-employees.php'>company dashboard</a></li>\n";
        echo "</ol>\n";
        
    } else {
        echo "<div style='color: red; padding: 15px; background: #f8d7da; border: 1px solid #f5c6cb; margin: 20px 0; border-radius: 5px;'>\n";
        echo "<h4>❌ Some required columns are still missing.</h4>\n";
        echo "<p>You may need to run the full migration script or check for database permission issues.</p>\n";
        echo "<p><a href='run_company_approval_fixed_migration.php' style='background: #dc3545; color: white; padding: 10px 20px; text-decoration: none; border-radius: 4px;'>Run Full Migration Script</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>Fatal Error:</strong> " . $e->getMessage() . "<br>\n";
    echo "<strong>File:</strong> " . $e->getFile() . "<br>\n";
    echo "<strong>Line:</strong> " . $e->getLine() . "\n";
    echo "</div>\n";
}
?>