<?php
/**
 * Verify all students are now visible in user list
 * This script confirms the fix worked
 */

error_reporting(E_ALL);
ini_set('display_errors', 1);

echo "<!DOCTYPE html>
<html>
<head>
    <title>Student List Verification</title>
    <style>
        body { font-family: Arial, sans-serif; margin: 20px; }
        .success { color: green; }
        .error { color: red; }
        .warning { color: orange; }
        .info { color: blue; }
        table { border-collapse: collapse; width: 100%; margin: 15px 0; }
        th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
        th { background-color: #f2f2f2; }
        .status-box { 
            padding: 15px; 
            margin: 15px 0; 
            border-radius: 8px;
        }
        .complete { background-color: #d4edda; border: 1px solid #c3e6cb; }
        .incomplete { background-color: #f8d7da; border: 1px solid #f5c6cb; }
        .progress { background-color: #fff3cd; border: 1px solid #ffeaa7; }
    </style>
</head>
<body>";

echo "<h1>✅ Student List Verification</h1>";

try {
    // Database connection
    try {
        require_once 'config/database.php';
    } catch (Exception $e) {
        $host = 'localhost';
        $dbname = 'school_management';
        $username = 'root';
        $password = 'root';
        
        try {
            $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $username, $password, [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            ]);
        } catch (Exception $e2) {
            $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $username, '', [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            ]);
        }
    }
    
    echo "<h2>📊 Verification Results</h2>";
    
    // Get comprehensive counts
    $stmt = $pdo->query("SELECT COUNT(*) as total FROM students");
    $total_students = $stmt->fetch()['total'];
    
    $stmt = $pdo->query("SELECT COUNT(*) as count FROM students WHERE user_id IS NOT NULL AND user_id != 0 AND user_id != ''");
    $students_with_user = $stmt->fetch()['count'];
    
    $stmt = $pdo->query("SELECT COUNT(*) as count FROM users WHERE role = 'student'");
    $student_users = $stmt->fetch()['count'];
    
    $completion_rate = $total_students > 0 ? round(($students_with_user / $total_students) * 100, 1) : 0;
    
    echo "<table>";
    echo "<tr><th>Metric</th><th>Count</th><th>Status</th></tr>";
    echo "<tr><td>Total Students in Database</td><td>$total_students</td><td>✅</td></tr>";
    echo "<tr><td>Students with User Accounts</td><td>$students_with_user</td><td>" . ($students_with_user == $total_students ? '✅ COMPLETE' : '⚠️ INCOMPLETE') . "</td></tr>";
    echo "<tr><td>Student User Records</td><td>$student_users</td><td>✅</td></tr>";
    echo "<tr><td>Completion Rate</td><td>$completion_rate%</td><td>" . ($completion_rate == 100 ? '✅ 100%' : '⚠️ ' . $completion_rate . '%') . "</td></tr>";
    echo "</table>";
    
    // Status assessment
    if ($students_with_user == $total_students && $completion_rate == 100) {
        echo "<div class='status-box complete'>";
        echo "<h3>🎉 SUCCESS! All Students Accounted For</h3>";
        echo "<p>All $total_students students now have proper user accounts.</p>";
        echo "<p>The admin users list should now show all students.</p>";
        echo "</div>";
        
        echo "<h3>🚀 Quick Actions</h3>";
        echo "<p><a href='admin/users.php?role_filter=student&per_page=100' target='_blank'><button style='padding: 10px 20px; background: #006747; color: white; border: none; border-radius: 5px; cursor: pointer;'>View Complete Student List</button></a></p>";
        echo "<p><a href='emergency_student_fix.php'><button style='padding: 10px 20px; background: #6c757d; color: white; border: none; border-radius: 5px; cursor: pointer; margin-left: 10px;'>Re-run Fix (if needed)</button></a></p>";
        
    } else {
        $missing = $total_students - $students_with_user;
        echo "<div class='status-box incomplete'>";
        echo "<h3>⚠️ Incomplete - $missing Students Still Missing</h3>";
        echo "<p>$students_with_user out of $total_students students have accounts.</p>";
        echo "<p>$missing students still need user accounts.</p>";
        echo "</div>";
        
        echo "<h3>🔧 Immediate Fix Required</h3>";
        echo "<p><a href='emergency_student_fix.php'><button style='padding: 12px 25px; background: #dc3545; color: white; border: none; border-radius: 5px; cursor: pointer; font-size: 16px;'>Create Missing Accounts Now</button></a></p>";
    }
    
    // Show sample of current student users
    echo "<h3>📋 Sample Student Users</h3>";
    $stmt = $pdo->query("
        SELECT u.id, u.username, u.email, u.status, 
               s.first_name, s.last_name, s.student_id, s.customer_number
        FROM users u
        LEFT JOIN students s ON u.id = s.user_id
        WHERE u.role = 'student'
        ORDER BY u.created_at DESC
        LIMIT 15
    ");
    $sample_users = $stmt->fetchAll();
    
    if (!empty($sample_users)) {
        echo "<table>";
        echo "<tr><th>User ID</th><th>Username</th><th>Name</th><th>Student ID</th><th>Customer No</th><th>Status</th></tr>";
        foreach ($sample_users as $user) {
            $status_class = $user['status'] == 'active' ? 'success' : 'warning';
            echo "<tr>";
            echo "<td>" . $user['id'] . "</td>";
            echo "<td>" . $user['username'] . "</td>";
            echo "<td>" . ($user['first_name'] ? $user['first_name'] . ' ' . $user['last_name'] : 'N/A') . "</td>";
            echo "<td>" . ($user['student_id'] ?? 'N/A') . "</td>";
            echo "<td>" . ($user['customer_number'] ?? 'N/A') . "</td>";
            echo "<td class='$status_class'>" . strtoupper($user['status']) . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    }
    
    // Show students still missing accounts (if any)
    if ($students_with_user < $total_students) {
        echo "<h3>📋 Students Still Missing Accounts</h3>";
        $stmt = $pdo->query("
            SELECT id, first_name, last_name, student_id, customer_number, class_id, session
            FROM students 
            WHERE user_id IS NULL OR user_id = 0 OR user_id = '' 
            ORDER BY id
            LIMIT 20
        ");
        $missing_students = $stmt->fetchAll();
        
        if (!empty($missing_students)) {
            echo "<table>";
            echo "<tr><th>ID</th><th>Name</th><th>Student ID</th><th>Customer No</th><th>Class</th><th>Session</th></tr>";
            foreach ($missing_students as $student) {
                echo "<tr>";
                echo "<td>" . $student['id'] . "</td>";
                echo "<td>" . $student['first_name'] . " " . $student['last_name'] . "</td>";
                echo "<td>" . $student['student_id'] . "</td>";
                echo "<td>" . ($student['customer_number'] ?? 'N/A') . "</td>";
                echo "<td>" . $student['class_id'] . "</td>";
                echo "<td>" . $student['session'] . "</td>";
                echo "</tr>";
            }
            echo "</table>";
            
            if (count($missing_students) == 20) {
                echo "<p class='info'>Showing first 20 of $missing students still missing accounts...</p>";
            }
        }
    }
    
    echo "<h3>✅ Verification Checklist</h3>";
    echo "<ul>";
    echo "<li>" . ($total_students > 0 ? '✅' : '❌') . " Database connection working</li>";
    echo "<li>" . ($students_with_user == $total_students ? '✅' : '❌') . " All students have user accounts</li>";
    echo "<li>" . ($completion_rate == 100 ? '✅' : '❌') . " 100% completion rate</li>";
    echo "<li>" . (count($sample_users) > 0 ? '✅' : '❌') . " Student users are visible</li>";
    echo "</ul>";
    
} catch (Exception $e) {
    echo "<div class='status-box incomplete'>";
    echo "<h3>❌ Verification Error</h3>";
    echo "<p>" . htmlspecialchars($e->getMessage()) . "</p>";
    echo "</div>";
}

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