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

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

try {
    $database = new Database();
    $db = $database->getConnection();
    
    echo "<p>✓ Database connection successful</p>\n";
    
    // Add missing columns one by one
    $columns_to_add = [
        "ALTER TABLE candidates ADD COLUMN candidate_type ENUM('internal', 'external') DEFAULT 'external' AFTER gender",
        "ALTER TABLE candidates ADD COLUMN employee_code VARCHAR(20) NULL AFTER candidate_type",
        "ALTER TABLE candidates ADD COLUMN company_id INT NULL AFTER employee_code",
        "ALTER TABLE candidates ADD COLUMN hr_approval_status ENUM('pending', 'approved', 'rejected') DEFAULT 'approved' AFTER company_id",
        "ALTER TABLE candidates ADD COLUMN hr_approval_date TIMESTAMP NULL AFTER hr_approval_status",
        "ALTER TABLE candidates ADD COLUMN hr_approval_notes TEXT NULL AFTER hr_approval_date",
        "ALTER TABLE candidates ADD COLUMN approved_by INT NULL AFTER hr_approval_notes",
    ];
    
    $success_count = 0;
    $skip_count = 0;
    
    foreach ($columns_to_add as $sql) {
        try {
            $db->exec($sql);
            $success_count++;
            echo "<div style='color: green;'>✓ " . substr($sql, 0, 80) . "...</div>\n";
        } catch (PDOException $e) {
            if (strpos($e->getMessage(), 'Duplicate column') !== false) {
                $skip_count++;
                echo "<div style='color: orange;'>⚠ Column already exists: " . substr($sql, 0, 80) . "...</div>\n";
            } else {
                echo "<div style='color: red;'>✗ Error: " . $e->getMessage() . "</div>\n";
            }
        }
    }
    
    // Add job_applications columns
    $job_app_columns = [
        "ALTER TABLE job_applications ADD COLUMN hr_approval_required BOOLEAN DEFAULT FALSE AFTER status",
        "ALTER TABLE job_applications ADD COLUMN hr_approval_status ENUM('pending', 'approved', 'rejected') NULL AFTER hr_approval_required",
        "ALTER TABLE job_applications ADD COLUMN hr_approved_by INT NULL AFTER hr_approval_status",
        "ALTER TABLE job_applications ADD COLUMN hr_approval_date TIMESTAMP NULL AFTER hr_approved_by",
        "ALTER TABLE job_applications ADD COLUMN hr_approval_notes TEXT NULL AFTER hr_approval_date",
    ];
    
    foreach ($job_app_columns as $sql) {
        try {
            $db->exec($sql);
            $success_count++;
            echo "<div style='color: green;'>✓ " . substr($sql, 0, 80) . "...</div>\n";
        } catch (PDOException $e) {
            if (strpos($e->getMessage(), 'Duplicate column') !== false) {
                $skip_count++;
                echo "<div style='color: orange;'>⚠ Column already exists: " . substr($sql, 0, 80) . "...</div>\n";
            } else {
                echo "<div style='color: red;'>✗ Error: " . $e->getMessage() . "</div>\n";
            }
        }
    }
    
    // Add indexes
    $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);
            $success_count++;
            echo "<div style='color: green;'>✓ Added index</div>\n";
        } catch (PDOException $e) {
            if (strpos($e->getMessage(), 'Duplicate key') !== false) {
                $skip_count++;
                echo "<div style='color: orange;'>⚠ Index already exists</div>\n";
            } else {
                echo "<div style='color: red;'>✗ Index error: " . $e->getMessage() . "</div>\n";
            }
        }
    }
    
    // Add foreign key constraints
    try {
        $db->exec("ALTER TABLE candidates ADD FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL");
        $success_count++;
        echo "<div style='color: green;'>✓ Added company foreign key</div>\n";
    } catch (PDOException $e) {
        echo "<div style='color: orange;'>⚠ Foreign key constraint: " . $e->getMessage() . "</div>\n";
    }
    
    echo "<hr>\n";
    echo "<h3>Summary:</h3>\n";
    echo "<p>✅ Successfully added: $success_count items</p>\n";
    echo "<p>⚠️ Skipped (already existed): $skip_count items</p>\n";
    
    // Verify the fix worked
    echo "<h3>Verification:</h3>\n";
    $stmt = $db->query("DESCRIBE candidates");
    $columns = $stmt->fetchAll();
    $column_names = array_column($columns, 'Field');
    
    $required = ['candidate_type', 'employee_code', 'company_id', 'hr_approval_status'];
    $all_exist = true;
    
    foreach ($required as $col) {
        if (in_array($col, $column_names)) {
            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;
        }
    }
    
    if ($all_exist) {
        echo "<div style='color: green; padding: 15px; background: #d4edda; border: 1px solid #c3e6cb; margin: 20px 0;'>\n";
        echo "<h4>✅ SUCCESS! All columns added successfully.</h4>\n";
        echo "<p>You can now try registration again:</p>\n";
        echo "<p><a href='auth/register.php' style='background: #28a745; color: white; padding: 10px 20px; text-decoration: none; border-radius: 4px;'>Try Registration Again</a></p>\n";
        echo "</div>\n";
    } else {
        echo "<div style='color: red; padding: 15px; background: #f8d7da; border: 1px solid #f5c6cb; margin: 20px 0;'>\n";
        echo "<h4>❌ Some columns are still missing.</h4>\n";
        echo "<p>Please run the full migration script:</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</a></p>\n";
        echo "</div>\n";
    }
    
} catch (Exception $e) {
    echo "<div style='color: red; padding: 10px; background: #f8d7da; border: 1px solid #f5c6cb;'>\n";
    echo "<strong>Error:</strong> " . $e->getMessage() . "\n";
    echo "</div>\n";
}
?>