<?php
/**
 * Add Category Field to Employees Table
 * Runs the migration to add employee_category field
 */

// Get database credentials from config
$configPath = __DIR__ . '/../config/database.php';

if (!file_exists($configPath)) {
    die("❌ Error: config/database.php not found!\n\nPlease create config/database.php first.\n");
}

require_once $configPath;

echo "🚀 Adding Category Field to Employees Table...\n\n";

try {
    // Create PDO connection
    $dsn = "mysql:host=" . DB_HOST . ";dbname=" . DB_NAME . ";charset=utf8mb4";
    $options = [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
    ];
    
    $pdo = new PDO($dsn, DB_USER, DB_PASS, $options);
    echo "✅ Database connection established\n\n";
    
    // Check if column exists
    $stmt = $pdo->query("SHOW COLUMNS FROM employees LIKE 'employee_category'");
    $columnExists = $stmt->rowCount() > 0;
    
    if ($columnExists) {
        echo "ℹ️  Column 'employee_category' already exists. Modifying to enum type...\n";
    } else {
        echo "📝 Column 'employee_category' does not exist. Adding new column...\n";
    }
    
    // Read and execute migration SQL
    $migrationFile = __DIR__ . '/migrations/add_category_to_employees.sql';
    if (!file_exists($migrationFile)) {
        die("❌ Error: Migration file not found: $migrationFile\n");
    }
    
    $sql = file_get_contents($migrationFile);
    
    // Split by semicolon and execute each statement
    $statements = array_filter(array_map('trim', explode(';', $sql)));
    
    foreach ($statements as $statement) {
        if (!empty($statement) && !preg_match('/^--/', $statement)) {
            try {
                if (strpos($statement, 'MODIFY COLUMN') !== false) {
                    // Modify existing column or add if doesn't exist
                    if (!$columnExists) {
                        // Replace MODIFY with ADD
                        $statement = str_replace('MODIFY COLUMN', 'ADD COLUMN', $statement);
                    }
                    $pdo->exec($statement);
                    echo "✅ " . ($columnExists ? "Modified" : "Added") . " employee_category column\n";
                } elseif (strpos($statement, 'ADD INDEX') !== false) {
                    // Check if index exists first
                    $checkStmt = $pdo->query("SHOW INDEX FROM employees WHERE Key_name = 'idx_employee_category'");
                    if ($checkStmt->rowCount() === 0) {
                        $pdo->exec($statement);
                        echo "✅ Added index on employee_category\n";
                    } else {
                        echo "ℹ️  Index already exists, skipping...\n";
                    }
                } else {
                    $pdo->exec($statement);
                    echo "✅ Executed SQL statement\n";
                }
            } catch (PDOException $e) {
                // Ignore "duplicate column/key" errors
                if (strpos($e->getMessage(), 'Duplicate column') === false && 
                    strpos($e->getMessage(), 'Duplicate key') === false &&
                    strpos($e->getMessage(), 'already exists') === false) {
                    throw $e;
                }
                echo "ℹ️  " . $e->getMessage() . "\n";
            }
        }
    }
    
    echo "\n" . str_repeat("=", 60) . "\n";
    echo "✅ Migration completed successfully!\n";
    echo str_repeat("=", 60) . "\n\n";
    
    // Verify column structure
    $stmt = $pdo->query("SHOW COLUMNS FROM employees WHERE Field = 'employee_category'");
    $column = $stmt->fetch();
    
    if ($column) {
        echo "📊 Column Structure:\n";
        echo str_repeat("-", 60) . "\n";
        echo "Field: " . $column['Field'] . "\n";
        echo "Type: " . $column['Type'] . "\n";
        echo "Null: " . $column['Null'] . "\n";
        echo "Default: " . ($column['Default'] ?? 'NULL') . "\n";
        echo "\n";
    }
    
    echo "✨ Employee category field is ready to use!\n";
    echo "\n";
    
} catch (PDOException $e) {
    echo "\n❌ Migration failed!\n";
    echo "Error: " . $e->getMessage() . "\n\n";
    exit(1);
}
