<?php
/**
 * Diagnostic script for admin users page student data issue
 * This script will check the database and identify why student data is not appearing
 */

require_once 'config/database.php';

echo "<!DOCTYPE html>
<html>
<head>
    <title>Admin Users Page Diagnostic</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: 10px 0; }
        th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
        th { background-color: #f2f2f2; }
        pre { background: #f5f5f5; padding: 10px; }
    </style>
</head>
<body>";

echo "<h1>🔍 Admin Users Page Diagnostic</h1>";

try {
    echo "<h2>📊 Database Structure Check</h2>";
    
    // Check if required tables exist
    $tables_needed = ['users', 'students', 'teachers', 'classes'];
    foreach ($tables_needed as $table) {
        $stmt = $pdo->query("SHOW TABLES LIKE '$table'");
        $exists = $stmt->rowCount() > 0;
        echo "<p>" . ($exists ? '✅' : '❌') . " Table '$table': " . ($exists ? 'EXISTS' : 'MISSING') . "</p>";
    }
    
    echo "<h2>👥 User Data Analysis</h2>";
    
    // Get total users count
    $stmt = $pdo->query("SELECT COUNT(*) as total FROM users");
    $total_users = $stmt->fetch()['total'];
    echo "<p><strong>Total Users:</strong> $total_users</p>";
    
    // Get user count by role
    $stmt = $pdo->query("SELECT role, COUNT(*) as count FROM users GROUP BY role");
    $role_counts = $stmt->fetchAll();
    
    echo "<h3>Users by Role:</h3>";
    echo "<table>";
    echo "<tr><th>Role</th><th>Count</th></tr>";
    foreach ($role_counts as $role_count) {
        echo "<tr>";
        echo "<td>" . $role_count['role'] . "</td>";
        echo "<td>" . $role_count['count'] . "</td>";
        echo "</tr>";
    }
    echo "</table>";
    
    echo "<h2>🎓 Student Data Analysis</h2>";
    
    // Check students table
    $stmt = $pdo->query("SELECT COUNT(*) as total FROM students");
    $total_students = $stmt->fetch()['total'];
    echo "<p><strong>Total Students:</strong> $total_students</p>";
    
    // Check students with user_id
    $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'];
    echo "<p><strong>Students with user_id:</strong> $students_with_user</p>";
    
    // Check students without user_id
    $stmt = $pdo->query("SELECT COUNT(*) as count FROM students WHERE user_id IS NULL OR user_id = 0 OR user_id = ''");
    $students_without_user = $stmt->fetch()['count'];
    echo "<p><strong>Students without user_id:</strong> $students_without_user</p>";
    
    echo "<h3>Sample Students without User Accounts:</h3>";
    $stmt = $pdo->query("SELECT id, first_name, last_name, student_id, class_id, session FROM students WHERE user_id IS NULL OR user_id = 0 OR user_id = '' LIMIT 10");
    $sample_orphaned_students = $stmt->fetchAll();
    
    if (!empty($sample_orphaned_students)) {
        echo "<table>";
        echo "<tr><th>ID</th><th>Name</th><th>Student ID</th><th>Class ID</th><th>Session</th></tr>";
        foreach ($sample_orphaned_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['class_id'] . "</td>";
            echo "<td>" . $student['session'] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    } else {
        echo "<p class='success'>✅ All students have user accounts</p>";
    }
    
    echo "<h2>🔗 User-Student Relationship Check</h2>";
    
    // Check users with role 'student' but no student record
    $stmt = $pdo->query("
        SELECT u.id, u.username, u.email, u.role 
        FROM users u 
        LEFT JOIN students s ON u.id = s.user_id 
        WHERE u.role = 'student' AND s.user_id IS NULL
        LIMIT 10
    ");
    $users_without_student_record = $stmt->fetchAll();
    
    echo "<p><strong>Users with role 'student' but no student record:</strong> " . count($users_without_student_record) . "</p>";
    
    if (!empty($users_without_student_record)) {
        echo "<table>";
        echo "<tr><th>User ID</th><th>Username</th><th>Email</th></tr>";
        foreach ($users_without_student_record as $user) {
            echo "<tr>";
            echo "<td>" . $user['id'] . "</td>";
            echo "<td>" . $user['username'] . "</td>";
            echo "<td>" . $user['email'] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    }
    
    // Check the exact query used in users.php
    echo "<h2>🎯 Testing the Actual Query from users.php</h2>";
    
    $role_filter = 'student';
    $params = [];
    $where_conditions = ["u.role = ?"];
    $params[] = $role_filter;
    
    $where_clause = 'WHERE ' . implode(' AND ', $where_conditions);
    
    $sql = "
        SELECT u.*, 
               CASE 
                   WHEN u.role = 'student' THEN CONCAT(s.first_name, ' ', s.last_name)
                   WHEN u.role = 'teacher' THEN CONCAT(t.first_name, ' ', t.last_name)
                   ELSE u.username
               END as full_name
        FROM users u
        LEFT JOIN students s ON u.id = s.user_id
        LEFT JOIN teachers t ON u.id = t.user_id
        $where_clause
        ORDER BY u.created_at DESC
        LIMIT 10
    ";
    
    echo "<h3>Query Being Executed:</h3>";
    echo "<pre>" . htmlspecialchars($sql) . "</pre>";
    echo "<p><strong>Parameters:</strong> " . json_encode($params) . "</p>";
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $results = $stmt->fetchAll();
    
    echo "<p><strong>Query Results:</strong> " . count($results) . " records found</p>";
    
    if (!empty($results)) {
        echo "<table>";
        echo "<tr><th>User ID</th><th>Username</th><th>Full Name</th><th>Email</th><th>Status</th></tr>";
        foreach ($results as $result) {
            echo "<tr>";
            echo "<td>" . $result['id'] . "</td>";
            echo "<td>" . $result['username'] . "</td>";
            echo "<td>" . $result['full_name'] . "</td>";
            echo "<td>" . $result['email'] . "</td>";
            echo "<td>" . $result['status'] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    } else {
        echo "<p class='error'>❌ No results returned from the query</p>";
    }
    
    // Check for NULL values in full_name
    echo "<h3>Full Name Analysis:</h3>";
    foreach ($results as $result) {
        if (empty($result['full_name']) || $result['full_name'] === ' ') {
            echo "<p class='warning'>⚠️ User ID " . $result['id'] . " has empty/full_name: '" . $result['full_name'] . "'</p>";
        }
    }
    
    echo "<h2>📋 Session and Class Data</h2>";
    
    // Get available sessions
    $stmt = $pdo->query("SELECT DISTINCT session FROM students WHERE session IS NOT NULL AND session != '' ORDER BY session DESC");
    $sessions = $stmt->fetchAll();
    echo "<p><strong>Available Sessions:</strong> " . count($sessions) . "</p>";
    if (!empty($sessions)) {
        echo "<ul>";
        foreach ($sessions as $session) {
            echo "<li>" . $session['session'] . "</li>";
        }
        echo "</ul>";
    }
    
    // Get available classes
    $stmt = $pdo->query("SELECT id, name, section FROM classes WHERE status = 'active' ORDER BY name, section");
    $classes = $stmt->fetchAll();
    echo "<p><strong>Active Classes:</strong> " . count($classes) . "</p>";
    if (!empty($classes)) {
        echo "<ul>";
        foreach ($classes as $class) {
            echo "<li>" . $class['name'] . " - " . $class['section'] . " (ID: " . $class['id'] . ")</li>";
        }
        echo "</ul>";
    }
    
    echo "<h2>🔧 Potential Issues Identified</h2>";
    
    $issues = [];
    
    if ($total_users == 0) {
        $issues[] = "❌ No users found in the database";
    }
    
    if (!isset(array_column($role_counts, 'count', 'role')['student'])) {
        $issues[] = "❌ No users with role 'student' found";
    } else {
        $student_user_count = array_column($role_counts, 'count', 'role')['student'];
        if ($student_user_count == 0) {
            $issues[] = "❌ Users table has role 'student' entry but count is 0";
        }
    }
    
    if ($total_students > 0 && $students_without_user > 0) {
        $issues[] = "⚠️ Found $students_without_user students without user accounts - these won't appear in users list";
    }
    
    if (count($users_without_student_record) > 0) {
        $issues[] = "⚠️ Found " . count($users_without_student_record) . " users with role 'student' but no corresponding student record";
    }
    
    if (count($results) == 0) {
        $issues[] = "❌ Query returned no results for student role filter";
    }
    
    if (empty($sessions)) {
        $issues[] = "⚠️ No sessions found in students table";
    }
    
    if (empty($classes)) {
        $issues[] = "⚠️ No active classes found";
    }
    
    if (empty($issues)) {
        echo "<p class='success'>✅ No major issues detected. The problem might be with filtering or display logic.</p>";
    } else {
        echo "<ul>";
        foreach ($issues as $issue) {
            echo "<li>$issue</li>";
        }
        echo "</ul>";
    }
    
    echo "<h2>💡 Recommended Solutions</h2>";
    
    echo "<ol>";
    if ($total_users == 0) {
        echo "<li>Create some test users in the database</li>";
    }
    
    if (!isset(array_column($role_counts, 'count', 'role')['student']) || array_column($role_counts, 'count', 'role')['student'] == 0) {
        echo "<li>Create users with role 'student'</li>";
    }
    
    if ($students_without_user > 0) {
        echo "<li>Create user accounts for students who don't have them</li>";
        echo "<li>Link existing students to user accounts by updating user_id field</li>";
    }
    
    if (count($users_without_student_record) > 0) {
        echo "<li>Create student records for users with role 'student' that don't have them</li>";
    }
    
    if (count($results) == 0) {
        echo "<li>Check if the role filter is being applied correctly</li>";
        echo "<li>Verify that the JavaScript is properly submitting the form with role_filter=student</li>";
    }
    
    echo "<li>Clear browser cache and try again</li>";
    echo "<li>Check browser console for JavaScript errors</li>";
    echo "</ol>";
    
    echo "<h2>🔧 Quick Fix Options</h2>";
    
    echo "<p><a href='create_sample_students.php' target='_blank'>Create Sample Student Users</a> - This will create sample student users linked to existing students</p>";
    echo "<p><a href='link_orphaned_students.php' target='_blank'>Link Orphaned Students</a> - This will create user accounts for students without them</p>";
    echo "<p><a href='admin/users.php?role_filter=student' target='_blank'>Test Direct Link</a> - This bypasses JavaScript filtering</p>";
    
} catch (Exception $e) {
    echo "<div class='error'>";
    echo "<h3>❌ Database Error</h3>";
    echo "<p>" . htmlspecialchars($e->getMessage()) . "</p>";
    echo "</div>";
}

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