<?php
/**
 * Username Login Test Script
 * 
 * This script helps test the username login functionality
 * Run this to verify your migration was successful
 * 
 * IMPORTANT: Delete this file after testing for security reasons
 */

require_once 'include/dbconfig.php';
require_once 'include/class.user.php';

$reg_user = new USER();

echo "<!DOCTYPE html>
<html>
<head>
    <title>Username Login Test</title>
    <style>
        body {
            font-family: Arial, sans-serif;
            margin: 20px;
            background-color: #f5f5f5;
        }
        .container {
            max-width: 800px;
            margin: 0 auto;
            background-color: white;
            padding: 20px;
            border-radius: 5px;
            box-shadow: 0 2px 5px rgba(0,0,0,0.1);
        }
        .success {
            color: green;
            font-weight: bold;
        }
        .error {
            color: red;
            font-weight: bold;
        }
        .warning {
            color: orange;
            font-weight: bold;
        }
        table {
            width: 100%;
            border-collapse: collapse;
            margin: 20px 0;
        }
        th, td {
            border: 1px solid #ddd;
            padding: 10px;
            text-align: left;
        }
        th {
            background-color: #4CAF50;
            color: white;
        }
        .test-section {
            margin: 20px 0;
            padding: 15px;
            background-color: #f9f9f9;
            border-left: 4px solid #4CAF50;
        }
    </style>
</head>
<body>
    <div class='container'>
        <h1>Username Login Migration Test</h1>
        <p>This script verifies that the username login migration was successful.</p>
        <hr>";

// Test 1: Check if username column exists
echo "<div class='test-section'>";
echo "<h2>Test 1: Database Schema Check</h2>";

$query = "SHOW COLUMNS FROM account LIKE 'username'";
$result = mysqli_query($connection, $query);

if (mysqli_num_rows($result) > 0) {
    echo "<p class='success'>✓ Username column exists in account table</p>";
    
    $column = mysqli_fetch_assoc($result);
    echo "<p>Column details:</p>";
    echo "<ul>";
    echo "<li>Type: " . $column['Type'] . "</li>";
    echo "<li>Null: " . $column['Null'] . "</li>";
    echo "<li>Key: " . ($column['Key'] ? $column['Key'] : 'None') . "</li>";
    echo "</ul>";
} else {
    echo "<p class='error'>✗ Username column does NOT exist</p>";
    echo "<p>Please run the database migration script first.</p>";
}
echo "</div>";

// Test 2: Check username population
echo "<div class='test-section'>";
echo "<h2>Test 2: Username Population Check</h2>";

$total_query = "SELECT COUNT(*) as total FROM account";
$total_result = mysqli_query($connection, $total_query);
$total_row = mysqli_fetch_assoc($total_result);
$total_accounts = $total_row['total'];

$with_username_query = "SELECT COUNT(*) as count FROM account WHERE username IS NOT NULL AND username != ''";
$with_username_result = mysqli_query($connection, $with_username_query);
$with_username_row = mysqli_fetch_assoc($with_username_result);
$accounts_with_username = $with_username_row['count'];

$without_username = $total_accounts - $accounts_with_username;

echo "<p>Total accounts: <strong>$total_accounts</strong></p>";
echo "<p>Accounts with username: <strong class='success'>$accounts_with_username</strong></p>";
echo "<p>Accounts without username: <strong class='" . ($without_username > 0 ? 'error' : 'success') . "'>$without_username</strong></p>";

if ($without_username > 0) {
    echo "<p class='warning'>⚠ Some accounts don't have usernames. Run populate_usernames.php to fix this.</p>";
} else {
    echo "<p class='success'>✓ All accounts have usernames</p>";
}
echo "</div>";

// Test 3: Check for duplicate usernames
echo "<div class='test-section'>";
echo "<h2>Test 3: Duplicate Username Check</h2>";

$duplicate_query = "SELECT username, COUNT(*) as count FROM account WHERE username IS NOT NULL GROUP BY username HAVING count > 1";
$duplicate_result = mysqli_query($connection, $duplicate_query);
$duplicate_count = mysqli_num_rows($duplicate_result);

if ($duplicate_count > 0) {
    echo "<p class='error'>✗ Found $duplicate_count duplicate username(s)</p>";
    echo "<table>";
    echo "<tr><th>Username</th><th>Count</th></tr>";
    while ($dup = mysqli_fetch_assoc($duplicate_result)) {
        echo "<tr><td>" . $dup['username'] . "</td><td>" . $dup['count'] . "</td></tr>";
    }
    echo "</table>";
    echo "<p class='warning'>⚠ Please fix duplicate usernames before proceeding.</p>";
} else {
    echo "<p class='success'>✓ No duplicate usernames found</p>";
}
echo "</div>";

// Test 4: Sample accounts display
echo "<div class='test-section'>";
echo "<h2>Test 4: Sample Accounts</h2>";

$sample_query = "SELECT id, uname, username, fname, lname, email FROM account LIMIT 10";
$sample_result = mysqli_query($connection, $sample_query);

if (mysqli_num_rows($sample_result) > 0) {
    echo "<p>Here are some sample accounts (showing first 10):</p>";
    echo "<table>";
    echo "<tr><th>ID</th><th>Account No</th><th>Username</th><th>Name</th><th>Email</th></tr>";
    
    while ($account = mysqli_fetch_assoc($sample_result)) {
        $username_status = $account['username'] ? $account['username'] : "<span class='error'>NOT SET</span>";
        echo "<tr>";
        echo "<td>" . $account['id'] . "</td>";
        echo "<td>" . $account['uname'] . "</td>";
        echo "<td>" . $username_status . "</td>";
        echo "<td>" . $account['fname'] . " " . $account['lname'] . "</td>";
        echo "<td>" . $account['email'] . "</td>";
        echo "</tr>";
    }
    echo "</table>";
} else {
    echo "<p class='warning'>No accounts found in database</p>";
}
echo "</div>";

// Test 5: Login method check
echo "<div class='test-section'>";
echo "<h2>Test 5: Login Method Check</h2>";

$test_file = 'user_access.php';
if (file_exists($test_file)) {
    $content = file_get_contents($test_file);
    
    if (strpos($content, "name=\"username\"") !== false) {
        echo "<p class='success'>✓ Login form uses 'username' field</p>";
    } else {
        echo "<p class='error'>✗ Login form still uses old field name</p>";
    }
    
    if (strpos($content, "WHERE username=") !== false || strpos($content, "WHERE username =") !== false) {
        echo "<p class='success'>✓ Login query uses username field</p>";
    } else {
        echo "<p class='error'>✗ Login query doesn't use username field</p>";
    }
} else {
    echo "<p class='warning'>⚠ Could not check user_access.php file</p>";
}
echo "</div>";

// Summary
echo "<div class='test-section'>";
echo "<h2>Summary & Next Steps</h2>";

$all_good = true;

if (mysqli_num_rows(mysqli_query($connection, "SHOW COLUMNS FROM account LIKE 'username'")) == 0) {
    $all_good = false;
    echo "<p class='error'>✗ Username column missing - run database migration</p>";
}

if ($without_username > 0) {
    $all_good = false;
    echo "<p class='error'>✗ Some accounts missing usernames - run populate_usernames.php</p>";
}

if ($duplicate_count > 0) {
    $all_good = false;
    echo "<p class='error'>✗ Duplicate usernames found - fix manually</p>";
}

if ($all_good) {
    echo "<p class='success' style='font-size: 18px;'>✓ All tests passed! Your username login system is ready.</p>";
    echo "<h3>Final Steps:</h3>";
    echo "<ol>";
    echo "<li>Test login with a real account using username and password</li>";
    echo "<li>Verify OTP and PIN authentication work correctly</li>";
    echo "<li>Make username field required (if not already):<br>";
    echo "<code>ALTER TABLE account MODIFY COLUMN username VARCHAR(100) NOT NULL;</code></li>";
    echo "<li><strong>DELETE test files for security:</strong>";
    echo "<ul>";
    echo "<li>test_username_login.php (this file)</li>";
    echo "<li>populate_usernames.php</li>";
    echo "</ul></li>";
    echo "</ol>";
} else {
    echo "<p class='error'>Some issues need to be resolved before going live.</p>";
}

echo "</div>";

mysqli_close($connection);

echo "    </div>
</body>
</html>";
?>
