<?php
require_once 'config/database.php';

echo "<h2>Finding Best Session/Exam Type Combination</h2>\n";

// Get the top 5 session/exam type combinations by result count
$stmt = $pdo->query("
    SELECT session, exam_type, COUNT(*) as count 
    FROM results 
    GROUP BY session, exam_type 
    ORDER BY count DESC 
    LIMIT 5
");
$combinations = $stmt->fetchAll();

echo "<h3>Top 5 Session/Exam Type Combinations:</h3>\n";
echo "<table border='1'>\n";
echo "<tr><th>Session</th><th>Exam Type</th><th>Result Count</th></tr>\n";
foreach ($combinations as $combo) {
    echo "<tr>";
    echo "<td>" . $combo['session'] . "</td>";
    echo "<td>" . htmlspecialchars($combo['exam_type']) . "</td>";
    echo "<td>" . $combo['count'] . "</td>";
    echo "</tr>\n";
}
echo "</table>\n";

// Also get the total number of students in class 10
$stmt = $pdo->prepare("SELECT COUNT(*) as count FROM students WHERE class_id = 10 AND status = 'active'");
$stmt->execute();
$student_count = $stmt->fetch();
echo "<p>Total students in Class 10: " . $student_count['count'] . "</p>\n";

// For the best combination, check how many unique students have results
if (!empty($combinations)) {
    $best = $combinations[0];
    $stmt = $pdo->prepare("
        SELECT COUNT(DISTINCT r.student_id) as student_count
        FROM results r
        JOIN students s ON r.student_id = s.id
        WHERE s.class_id = 10 AND r.session = ? AND r.exam_type = ?
    ");
    $stmt->execute([$best['session'], $best['exam_type']]);
    $student_with_results = $stmt->fetch();
    
    echo "<p>For session " . $best['session'] . " and exam type '" . htmlspecialchars($best['exam_type']) . "':</p>\n";
    echo "<ul>\n";
    echo "<li>Total results: " . $best['count'] . "</li>\n";
    echo "<li>Students with results: " . $student_with_results['student_count'] . "</li>\n";
    echo "<li>Percentage of students with results: " . round(($student_with_results['student_count'] / $student_count['count']) * 100, 2) . "%</li>\n";
    echo "</ul>\n";
    
    echo "<p><a href='reports/tabulation_sheet.php?class_id=10&session=" . $best['session'] . "&exam_type=" . urlencode($best['exam_type']) . "'>View Tabulation Sheet for this combination</a></p>\n";
}
?>