<?php
/**
 * Comprehensive rank debugging tool
 * This will help identify why stream and overall ranks are showing dashes
 */

require_once 'config.php'; // Adjust path as needed

echo "<h1>🔍 Rank Calculation Debug Report</h1>\n";
define('DB_HOST', 'localhost');
define('DB_USER', 'root'); 
define('DB_PASS', '');
define('DB_NAME', 'divine_reports'); // ← UPDATE this to your database name

// Test database connection
try {
    $connection = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
    if ($connection->connect_error) {
        die("Connection failed: " . $connection->connect_error);
    }
    echo "<p>✅ Database connection successful</p>\n";
} catch (Exception $e) {
    die("Database connection failed: " . $e->getMessage());
}

// Function to check if rank calculation functions exist
echo "<h2>📋 Function Availability Check</h2>\n";
$functions_to_check = ['calculateStreamRank', 'calculateOverallRank', 'getStreamRankFallback', 'getOverallRankFallback'];
foreach ($functions_to_check as $func) {
    if (function_exists($func)) {
        echo "<p>✅ Function exists: <strong>$func</strong></p>\n";
    } else {
        echo "<p>❌ Function missing: <strong>$func</strong></p>\n";
    }
}

// Get all students in senior_level with their stream and class info
echo "<h2>👥 Senior Level Students Overview</h2>\n";

$sql_students = "SELECT u.id as user_id, u.first_name, u.last_name, 
                        s.stream_name, s.stream_id,
                        c.class_name, c.class_id, c.class_level,
                        u.stream_id as user_stream_id
                 FROM users u
                 LEFT JOIN streams s ON u.stream_id = s.stream_id
                 LEFT JOIN classes c ON u.class_id = c.class_id
                 WHERE c.class_level = 'senior_level'
                 ORDER BY u.id";

$result_students = $connection->query($sql_students);

if ($result_students && $result_students->num_rows > 0) {
    echo "<table border='1' style='border-collapse: collapse; width: 100%;'>\n";
    echo "<tr><th>User ID</th><th>Name</th><th>Stream</th><th>Stream ID</th><th>Class</th><th>Class Level</th></tr>\n";
    
    while ($student = $result_students->fetch_assoc()) {
        echo "<tr>";
        echo "<td>{$student['user_id']}</td>";
        echo "<td>{$student['first_name']} {$student['last_name']}</td>";
        echo "<td>{$student['stream_name']}</td>";
        echo "<td>{$student['stream_id']}</td>";
        echo "<td>{$student['class_name']}</td>";
        echo "<td>{$student['class_level']}</td>";
        echo "</tr>\n";
    }
    echo "</table>\n";
} else {
    echo "<p>❌ No senior level students found</p>\n";
}

// Check exam results data
echo "<h2>📊 Exam Results Data Check</h2>\n";

$sql_exam_check = "SELECT COUNT(*) as total_results,
                          COUNT(DISTINCT user_id) as unique_students,
                          COUNT(CASE WHEN grade_letter = 'F' THEN 1 END) as f_grades,
                          COUNT(CASE WHEN grade_letter IS NULL THEN 1 END) as null_grades
                   FROM exam_results er
                   LEFT JOIN grading_scales gs ON er.grade_id = gs.grade_id";

$result_exam = $connection->query($sql_exam_check);
if ($result_exam && $result_exam->num_rows > 0) {
    $exam_data = $result_exam->fetch_assoc();
    echo "<ul>\n";
    echo "<li>Total exam results: <strong>{$exam_data['total_results']}</strong></li>\n";
    echo "<li>Students with results: <strong>{$exam_data['unique_students']}</strong></li>\n";
    echo "<li>F grades: <strong>{$exam_data['f_grades']}</strong></li>\n";
    echo "<li>Null grades: <strong>{$exam_data['null_grades']}</strong></li>\n";
    echo "</ul>\n";
}

// Test stream rank calculation for each student
echo "<h2>🎯 Stream Rank Test Results</h2>\n";

// Test with first few students only for detailed analysis
$sql_test_students = "SELECT DISTINCT u.id as user_id, u.first_name, u.last_name, 
                            s.stream_name, s.stream_id, c.class_name, c.class_id
                     FROM users u
                     LEFT JOIN streams s ON u.stream_id = s.stream_id
                     LEFT JOIN classes c ON u.class_id = c.class_id
                     WHERE c.class_level = 'senior_level' 
                     AND s.stream_id IS NOT NULL
                     ORDER BY u.id 
                     LIMIT 5";

$result_test = $connection->query($sql_test_students);

if ($result_test && $result_test->num_rows > 0) {
    echo "<table border='1' style='border-collapse: collapse; width: 100%;'>\n";
    echo "<tr><th>Student</th><th>Stream</th><th>Stream Rank</th><th>Debug Info</th></tr>\n";
    
    while ($student = $result_test->fetch_assoc()) {
        echo "<tr>";
        echo "<td>{$student['first_name']} {$student['last_name']} (ID: {$student['user_id']})</td>";
        echo "<td>{$student['stream_name']} (ID: {$student['stream_id']})</td>";
        
        // Test stream rank calculation
        if (function_exists('calculateStreamRank')) {
            try {
                // Check if student has stream_id
                if ($student['stream_id']) {
                    // Get student count in stream
                    $sql_stream_count = "SELECT COUNT(*) as count FROM users WHERE stream_id = ?";
                    $stmt_count = $connection->prepare($sql_stream_count);
                    $stmt_count->bind_param("i", $student['stream_id']);
                    $stmt_count->execute();
                    $count_result = $stmt_count->get_result();
                    $count_data = $count_result->fetch_assoc();
                    $student_count = $count_data['count'];
                    $stmt_count->close();
                    
                    if ($student_count > 0) {
                        $stream_rank = calculateStreamRank($student['user_id'], $student['class_id'], $connection);
                        echo "<td style='background-color: " . ($stream_rank == '--' ? '#ffcccc' : '#ccffcc') . ";'><strong>$stream_rank</strong></td>";
                        echo "<td>Students in stream: {$student_count}</td>";
                    } else {
                        echo "<td style='background-color: #ffcccc;'>--</td>";
                        echo "<td>No students in stream</td>";
                    }
                } else {
                    echo "<td style='background-color: #ffcccc;'>--</td>";
                    echo "<td>No stream_id</td>";
                }
            } catch (Exception $e) {
                echo "<td style='background-color: #ffcccc;'>--</td>";
                echo "<td>Error: " . htmlspecialchars($e->getMessage()) . "</td>";
            }
        } else {
            echo "<td style='background-color: #ffcccc;'>--</td>";
            echo "<td>Function not found</td>";
        }
        
        echo "</tr>\n";
    }
    echo "</table>\n";
} else {
    echo "<p>❌ No test students found</p>\n";
}

// Test overall rank calculation
echo "<h2>🌍 Overall Rank Test Results</h2>\n";

$result_test->data_seek(0); // Reset result pointer
if ($result_test && $result_test->num_rows > 0) {
    echo "<table border='1' style='border-collapse: collapse; width: 100%;'>\n";
    echo "<tr><th>Student</th><th>Overall Rank</th><th>Debug Info</th></tr>\n";
    
    while ($student = $result_test->fetch_assoc()) {
        echo "<tr>";
        echo "<td>{$student['first_name']} {$student['last_name']} (ID: {$student['user_id']})</td>";
        
        // Test overall rank calculation
        if (function_exists('calculateOverallRank')) {
            try {
                // Get student count in level
                $sql_level_count = "SELECT COUNT(*) as count 
                                   FROM users u 
                                   JOIN classes c ON u.class_id = c.class_id 
                                   WHERE c.class_level = 'senior_level'";
                $stmt_count = $connection->prepare($sql_level_count);
                $stmt_count->execute();
                $count_result = $stmt_count->get_result();
                $count_data = $count_result->fetch_assoc();
                $student_count = $count_data['count'];
                $stmt_count->close();
                
                if ($student_count > 0) {
                    $overall_rank = calculateOverallRank($student['user_id'], 'senior_level', $connection);
                    echo "<td style='background-color: " . ($overall_rank == '--' ? '#ffcccc' : '#ccffcc') . ";'><strong>$overall_rank</strong></td>";
                    echo "<td>Students in level: {$student_count}</td>";
                } else {
                    echo "<td style='background-color: #ffcccc;'>--</td>";
                    echo "<td>No students in level</td>";
                }
            } catch (Exception $e) {
                echo "<td style='background-color: #ffcccc;'>--</td>";
                echo "<td>Error: " . htmlspecialchars($e->getMessage()) . "</td>";
            }
        } else {
            echo "<td style='background-color: #ffcccc;'>--</td>";
            echo "<td>Function not found</td>";
        }
        
        echo "</tr>\n";
    }
    echo "</table>\n";
}

// Check error logs
echo "<h2>📋 Recent Error Log Check</h2>\n";

$log_files = [
    '/var/log/apache2/error.log',
    '/var/log/nginx/error.log',
    '/tmp/error.log',
    './error.log',
    'error.log'
];

echo "<p>Checking for error log files and recent entries...</p>\n";

foreach ($log_files as $log_file) {
    if (file_exists($log_file)) {
        echo "<h3>Log File: $log_file</h3>\n";
        $lines = file($log_file);
        $recent_lines = array_slice($lines, -20); // Last 20 lines
        echo "<div style='background: #f5f5f5; padding: 10px; max-height: 300px; overflow-y: scroll;'>\n";
        foreach ($recent_lines as $line) {
            if (strpos($line, 'rank') !== false || strpos($line, 'STUDENT') !== false) {
                echo htmlspecialchars($line) . "<br>\n";
            }
        }
        echo "</div>\n";
    }
}

echo "<h2>🔧 Debug Commands</h2>\n";
echo "<p>To view more detailed logs, check your server's error log with:</p>\n";
echo "<ul>\n";
echo "<li><code>tail -f /var/log/apache2/error.log | grep -E '(rank|STUDENT)'</code></li>\n";
echo "<li><code>tail -f /var/log/nginx/error.log | grep -E '(rank|STUDENT)'</code></li>\n";
echo "</ul>\n";

echo "<h2>📝 Summary</h2>\n";
echo "<p>This debug report shows:</p>\n";
echo "<ul>\n";
echo "<li>✅ Whether rank calculation functions exist</li>\n";
echo "<li>✅ How many senior level students there are</li>\n";
echo "<li>✅ Whether students have stream assignments</li>\n";
echo "<li>✅ Whether exam results exist in the database</li>\n";
echo "<li>✅ Individual rank calculation results for test students</li>\n";
echo "<li>✅ Recent error log entries</li>\n";
echo "</ul>\n";

$connection->close();
?>