<?php
/**
 * Run Employee Transfers Category Column Migration
 * Adds employee_category column to employee_transfers table
 */

require_once __DIR__ . '/../includes/init.php';

header('Content-Type: text/html; charset=utf-8');
?>
<!DOCTYPE html>
<html>
<head>
    <title>Employee Transfers Category Migration</title>
    <style>
        body { font-family: Arial, sans-serif; margin: 20px; background: #f5f5f5; }
        .container { background: white; padding: 20px; border-radius: 5px; max-width: 900px; margin: 0 auto; }
        .success { color: #28a745; font-weight: bold; }
        .error { color: #dc3545; font-weight: bold; }
        pre { background: #f8f9fa; padding: 15px; border-radius: 3px; overflow-x: auto; }
    </style>
</head>
<body>
    <div class="container">
        <h2>Employee Transfers Category Column Migration</h2>
        <pre>
<?php

try {
    $db = getDB();
    
    echo "Starting migration...\n\n";
    
    // Check if table exists
    $checkTable = $db->query("SHOW TABLES LIKE 'employee_transfers'");
    $tableExists = $checkTable->rowCount() > 0;
    
    if (!$tableExists) {
        echo "✗ Table 'employee_transfers' does not exist. Please create it first.\n";
    } else {
        echo "✓ Table 'employee_transfers' exists.\n\n";
        
        // Check if column exists
        $stmt = $db->query("SHOW COLUMNS FROM employee_transfers LIKE 'employee_category'");
        $columnExists = $stmt->rowCount() > 0;
        
        if ($columnExists) {
            echo "✓ Column 'employee_category' already exists.\n";
        } else {
            echo "✗ Column 'employee_category' does not exist. Adding...\n\n";
            
            // Add the column
            try {
                $sql = "ALTER TABLE employee_transfers ADD COLUMN `employee_category` VARCHAR(50) DEFAULT NULL AFTER `employee_id`";
                $db->exec($sql);
                echo "✓ Column 'employee_category' added successfully!\n";
            } catch (Exception $e) {
                echo "✗ Error adding column: " . $e->getMessage() . "\n";
            }
        }
        
        // Verify final structure
        echo "\n=== Final Table Structure ===\n";
        $stmt = $db->query("DESCRIBE employee_transfers");
        $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
        foreach ($columns as $col) {
            if (in_array($col['Field'], ['employee_id', 'employee_category', 'from_site_id', 'to_site_id', 'transfer_date', 'effective_branch', 'effective_attendance', 'esi_applicability', 'transfer_form_filled', 'approved_by_district_head', 'transfer_reason'])) {
                echo "  ✓ {$col['Field']} ({$col['Type']})";
                if ($col['Null'] === 'NO') echo " NOT NULL";
                if ($col['Default'] !== null) echo " DEFAULT '{$col['Default']}'";
                echo "\n";
            }
        }
    }
    
    echo "\n✅ Migration completed successfully!\n";
    
} catch (Exception $e) {
    echo "\n❌ ERROR: " . $e->getMessage() . "\n";
    echo "\nStack trace:\n" . $e->getTraceAsString() . "\n";
}

?>
        </pre>
        <a href="<?php echo BASE_URL; ?>/modules/transfer/list.php" style="display: inline-block; padding: 10px 20px; background: #007bff; color: white; text-decoration: none; border-radius: 3px; margin-top: 10px;">Go to Transfer Form</a>
    </div>
</body>
</html>

