<?php
/**
 * Username Population Script
 * 
 * This script helps populate the username field for existing accounts
 * Run this ONCE after adding the username column to your database
 * 
 * IMPORTANT: Delete this file after use for security reasons
 */

require_once 'include/dbconfig.php';

// Configuration - Choose your username generation strategy
$strategy = 'account_number'; // Options: 'account_number', 'name_based', 'manual'

// Security check - uncomment after reviewing the script
// die("Please review this script and uncomment this line before running");

echo "<h2>Username Population Script</h2>";
echo "<p>Strategy: <strong>$strategy</strong></p>";
echo "<hr>";

try {
    // Get all accounts without usernames
    $query = "SELECT id, uname, fname, lname, email FROM account WHERE username IS NULL OR username = ''";
    $result = mysqli_query($connection, $query);
    
    if (!$result) {
        die("Error fetching accounts: " . mysqli_error($connection));
    }
    
    $count = mysqli_num_rows($result);
    echo "<p>Found <strong>$count</strong> accounts without usernames</p>";
    
    if ($count == 0) {
        echo "<p style='color: green;'>All accounts already have usernames!</p>";
        exit;
    }
    
    $updated = 0;
    $errors = 0;
    
    echo "<table border='1' cellpadding='5' style='border-collapse: collapse;'>";
    echo "<tr><th>ID</th><th>Account No</th><th>Name</th><th>Generated Username</th><th>Status</th></tr>";
    
    while ($row = mysqli_fetch_assoc($result)) {
        $id = $row['id'];
        $uname = $row['uname'];
        $fname = $row['fname'];
        $lname = $row['lname'];
        $email = $row['email'];
        
        // Generate username based on strategy
        switch ($strategy) {
            case 'account_number':
                $username = $uname;
                break;
                
            case 'name_based':
                // Generate username from first and last name
                $username = strtolower($fname . '.' . $lname);
                // Remove spaces and special characters
                $username = preg_replace('/[^a-z0-9.]/', '', $username);
                
                // Check if username already exists, add number if needed
                $check_query = "SELECT COUNT(*) as count FROM account WHERE username = '$username'";
                $check_result = mysqli_query($connection, $check_query);
                $check_row = mysqli_fetch_assoc($check_result);
                
                if ($check_row['count'] > 0) {
                    $counter = 1;
                    $original_username = $username;
                    while ($check_row['count'] > 0) {
                        $username = $original_username . $counter;
                        $check_query = "SELECT COUNT(*) as count FROM account WHERE username = '$username'";
                        $check_result = mysqli_query($connection, $check_query);
                        $check_row = mysqli_fetch_assoc($check_result);
                        $counter++;
                    }
                }
                break;
                
            case 'manual':
                // For manual strategy, just display the accounts
                echo "<tr>";
                echo "<td>$id</td>";
                echo "<td>$uname</td>";
                echo "<td>$fname $lname</td>";
                echo "<td colspan='2'>Manual assignment required</td>";
                echo "</tr>";
                continue 2;
                
            default:
                $username = $uname;
        }
        
        // Update the account with the generated username
        $update_query = "UPDATE account SET username = '$username' WHERE id = $id";
        
        if (mysqli_query($connection, $update_query)) {
            echo "<tr>";
            echo "<td>$id</td>";
            echo "<td>$uname</td>";
            echo "<td>$fname $lname</td>";
            echo "<td><strong>$username</strong></td>";
            echo "<td style='color: green;'>✓ Updated</td>";
            echo "</tr>";
            $updated++;
        } else {
            echo "<tr>";
            echo "<td>$id</td>";
            echo "<td>$uname</td>";
            echo "<td>$fname $lname</td>";
            echo "<td>$username</td>";
            echo "<td style='color: red;'>✗ Error: " . mysqli_error($connection) . "</td>";
            echo "</tr>";
            $errors++;
        }
    }
    
    echo "</table>";
    echo "<hr>";
    echo "<h3>Summary</h3>";
    echo "<p>Total accounts processed: <strong>$count</strong></p>";
    echo "<p>Successfully updated: <strong style='color: green;'>$updated</strong></p>";
    echo "<p>Errors: <strong style='color: red;'>$errors</strong></p>";
    
    if ($errors == 0 && $updated > 0) {
        echo "<p style='color: green; font-weight: bold;'>✓ All usernames populated successfully!</p>";
        echo "<p><strong>Next Steps:</strong></p>";
        echo "<ol>";
        echo "<li>Test login with a few accounts</li>";
        echo "<li>If everything works, make username field required:<br>";
        echo "<code>ALTER TABLE account MODIFY COLUMN username VARCHAR(100) NOT NULL;</code></li>";
        echo "<li><strong>DELETE THIS FILE (populate_usernames.php) for security</strong></li>";
        echo "</ol>";
    }
    
} catch (Exception $e) {
    echo "<p style='color: red;'>Error: " . $e->getMessage() . "</p>";
}

mysqli_close($connection);
?>

<style>
    body {
        font-family: Arial, sans-serif;
        margin: 20px;
        background-color: #f5f5f5;
    }
    table {
        background-color: white;
        width: 100%;
        margin: 20px 0;
    }
    th {
        background-color: #4CAF50;
        color: white;
        padding: 10px;
    }
    code {
        background-color: #f4f4f4;
        padding: 2px 5px;
        border: 1px solid #ddd;
        border-radius: 3px;
    }
</style>
