<?php
/**
 * Ensure Employees Default to Active
 * Updates employees table and existing records
 */

// 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 "🚀 Ensuring Employees Default to Active...\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 employees table exists
    $stmt = $pdo->query("SHOW TABLES LIKE 'employees'");
    if ($stmt->rowCount() === 0) {
        die("❌ Error: employees table does not exist.\n");
    }
    echo "✅ employees table found\n";
    
    // Update default value
    echo "📝 Updating default value to 'active'...\n";
    $pdo->exec("
        ALTER TABLE `employees` 
        MODIFY COLUMN `employment_status` enum('active','inactive','terminated','resigned') 
        DEFAULT 'active' NOT NULL
    ");
    echo "✅ Default value updated\n";
    
    // Update existing NULL or empty records
    echo "📝 Updating existing records with NULL/empty status...\n";
    $stmt = $pdo->exec("
        UPDATE `employees` 
        SET `employment_status` = 'active' 
        WHERE `employment_status` IS NULL 
        OR `employment_status` = ''
    ");
    echo "✅ Updated $stmt records to 'active'\n";
    
    // Show summary
    $stmt = $pdo->query("
        SELECT 
            employment_status, 
            COUNT(*) as count 
        FROM employees 
        GROUP BY employment_status
        ORDER BY employment_status
    ");
    $summary = $stmt->fetchAll();
    
    echo "\n" . str_repeat("=", 60) . "\n";
    echo "✅ Migration completed successfully!\n";
    echo str_repeat("=", 60) . "\n\n";
    echo "📊 Employee Status Summary:\n";
    foreach ($summary as $row) {
        echo "   - {$row['employment_status']}: {$row['count']} employee(s)\n";
    }
    echo "\n";
    
    $totalStmt = $pdo->query("SELECT COUNT(*) as total FROM employees WHERE employment_status = 'active'");
    $total = $totalStmt->fetch()['total'];
    echo "✅ Total Active Employees: $total\n";
    echo "\n✨ All employees will default to 'active' status now!\n";
    echo "\n";
    
} catch (PDOException $e) {
    echo "\n❌ Migration failed!\n";
    echo "Error: " . $e->getMessage() . "\n\n";
    exit(1);
}
