<?php
// fix_names.php - Run once to fix capitalization
require_once 'system/conn.php'; 

// 1. Fetch all employees
$stmt = $conn->prepare("SELECT id, name FROM emp_salary");
$stmt->execute();
$employees = $stmt->fetchAll(PDO::FETCH_ASSOC);

$count = 0;

foreach ($employees as $emp) {
    $old_name = $emp['name'];
    
    // --- FORMATTING LOGIC ---
    
    // 1. Add space before bracket if missing (e.g., "Name(A)" -> "Name (A)")
    $new_name = str_replace('(', ' (', $old_name);
    
    // 2. Convert entire string to lowercase first
    $new_name = strtolower($new_name);
    
    // 3. Capitalize first letter of every word (Title Case)
    // Note: delimiters \t\r\n\f\v and space. We also treat ( as a delimiter effectively.
    $new_name = ucwords($new_name);
    
    // 4. Force text inside brackets to be UPPERCASE (Preserve brackets)
    // Regex finds text like (a) or (sg) and uppercases the content.
    $new_name = preg_replace_callback('/\((.*?)\)/', function($matches) {
        return '(' . strtoupper($matches[1]) . ')';
    }, $new_name);

    // 5. Clean up extra spaces
    $new_name = preg_replace('/\s+/', ' ', $new_name);
    $new_name = trim($new_name);
    
    // ------------------------

    // Only update if the name actually changed
    if ($old_name !== $new_name) {
        $update = $conn->prepare("UPDATE emp_salary SET name = ? WHERE id = ?");
        $update->execute([$new_name, $emp['id']]);
        
        echo "Fixed: <b>$old_name</b> &rarr; <span style='color:green'>$new_name</span><br>";
        $count++;
    }
}

echo "<br><h2>Done! Updated $count names.</h2>";
echo "<p>Please delete this file from your server now.</p>";
?>