<?php
session_start();
require_once '../config/database.php';

// Check if user is logged in
if (!isset($_SESSION['user_id'])) {
    header('Location: ../login.php');
    exit();
}

echo "<h2>বিষয়ের ক্রম পরীক্ষা</h2>";

// Get a sample student with results
$stmt = $pdo->query("
    SELECT DISTINCT r.student_id, s.first_name, s.last_name, r.session, r.exam_type, s.class_id
    FROM results r
    JOIN students s ON r.student_id = s.id
    LIMIT 10
");
$sample_students = $stmt->fetchAll();

if (empty($sample_students)) {
    echo "<p>কোন ছাত্রের ফলাফল পাওয়া যায়নি।</p>";
    exit();
}

echo "<h3>নমুনা ছাত্রদের তালিকা:</h3>";
echo "<ul>";
foreach ($sample_students as $student) {
    echo "<li><a href='?test_student={$student['student_id']}&session={$student['session']}&exam_type={$student['exam_type']}&class_id={$student['class_id']}'>";
    echo "{$student['first_name']} {$student['last_name']} - Session: {$student['session']}, Exam: {$student['exam_type']}";
    echo "</a></li>";
}
echo "</ul>";

// Test specific student if requested
if (isset($_GET['test_student'])) {
    $student_id = $_GET['test_student'];
    $session = $_GET['session'];
    $exam_type = $_GET['exam_type'];
    $class_id = $_GET['class_id'];
    
    echo "<hr><h3>বিষয়ের ক্রম পরীক্ষা: ছাত্র ID $student_id</h3>";
    
    // Get all subjects for this class
    $stmt = $pdo->prepare("
        SELECT DISTINCT sub.id, sub.name, sub.code 
        FROM subjects sub
        JOIN results r ON sub.id = r.subject_id
        JOIN students s ON r.student_id = s.id
        WHERE s.class_id = ? AND r.session = ? AND r.exam_type = ?
        ORDER BY sub.name
    ");
    $stmt->execute([$class_id, $session, $exam_type]);
    $all_class_subjects = $stmt->fetchAll();
    
    echo "<h4>এই ক্লাসের সব বিষয় (Class ID: $class_id):</h4>";
    echo "<table border='1' style='border-collapse: collapse; width: 100%;'>";
    echo "<tr><th>Subject ID</th><th>Subject Name</th><th>Subject Code</th></tr>";
    foreach ($all_class_subjects as $subject) {
        echo "<tr><td>{$subject['id']}</td><td>{$subject['name']}</td><td>{$subject['code']}</td></tr>";
    }
    echo "</table>";
    
    // Get this student's results
    $stmt = $pdo->prepare("
        SELECT r.*, sub.name as subject_name, sub.code as subject_code
        FROM results r
        JOIN subjects sub ON r.subject_id = sub.id
        WHERE r.student_id = ? AND r.session = ? AND r.exam_type = ?
        ORDER BY r.subject_id, r.id DESC
    ");
    $stmt->execute([$student_id, $session, $exam_type]);
    $student_results = $stmt->fetchAll();
    
    echo "<h4>এই ছাত্রের ফলাফল (কাঁচা ডেটা):</h4>";
    echo "<table border='1' style='border-collapse: collapse; width: 100%;'>";
    echo "<tr><th>Result ID</th><th>Subject ID</th><th>Subject Name</th><th>Marks</th><th>Grade</th></tr>";
    foreach ($student_results as $result) {
        echo "<tr><td>{$result['id']}</td><td>{$result['subject_id']}</td><td>{$result['subject_name']}</td><td>{$result['marks_obtained']}</td><td>{$result['grade']}</td></tr>";
    }
    echo "</table>";
    
    // Apply the same deduplication logic as marksheet.php
    $unique_results_map = [];
    
    foreach ($student_results as $result) {
        $subject_id = $result['subject_id'];
        // Only keep the first (latest) result for each subject_id
        if (!isset($unique_results_map[$subject_id])) {
            $unique_results_map[$subject_id] = $result;
        }
    }
    
    // Convert back to indexed array and sort by subject name
    $unique_results = array_values($unique_results_map);
    
    // Sort by subject name for consistent display
    usort($unique_results, function($a, $b) {
        return strcasecmp(trim($a['subject_name']), trim($b['subject_name']));
    });
    
    echo "<h4>ডুপ্লিকেট সরানো এবং সাজানোর পর:</h4>";
    echo "<table border='1' style='border-collapse: collapse; width: 100%;'>";
    echo "<tr><th>Serial</th><th>Result ID</th><th>Subject ID</th><th>Subject Name</th><th>Marks</th><th>Grade</th></tr>";
    $serial = 1;
    foreach ($unique_results as $result) {
        echo "<tr><td><strong>$serial</strong></td><td>{$result['id']}</td><td>{$result['subject_id']}</td><td>{$result['subject_name']}</td><td>{$result['marks_obtained']}</td><td>{$result['grade']}</td></tr>";
        $serial++;
    }
    echo "</table>";
    
    // Check if any subjects are missing
    $student_subject_ids = array_column($unique_results, 'subject_id');
    $class_subject_ids = array_column($all_class_subjects, 'id');
    
    $missing_subjects = array_diff($class_subject_ids, $student_subject_ids);
    $extra_subjects = array_diff($student_subject_ids, $class_subject_ids);
    
    if (!empty($missing_subjects)) {
        echo "<h4 style='color: red;'>⚠️ অনুপস্থিত বিষয়:</h4>";
        echo "<ul>";
        foreach ($missing_subjects as $missing_id) {
            $subject = array_filter($all_class_subjects, function($s) use ($missing_id) {
                return $s['id'] == $missing_id;
            });
            $subject = reset($subject);
            echo "<li>Subject ID: {$missing_id} - {$subject['name']}</li>";
        }
        echo "</ul>";
    }
    
    if (!empty($extra_subjects)) {
        echo "<h4 style='color: orange;'>ℹ️ অতিরিক্ত বিষয়:</h4>";
        echo "<ul>";
        foreach ($extra_subjects as $extra_id) {
            $result = array_filter($unique_results, function($r) use ($extra_id) {
                return $r['subject_id'] == $extra_id;
            });
            $result = reset($result);
            echo "<li>Subject ID: {$extra_id} - {$result['subject_name']}</li>";
        }
        echo "</ul>";
    }
    
    if (empty($missing_subjects) && empty($extra_subjects)) {
        echo "<p style='color: green;'><strong>✅ সব বিষয় সঠিকভাবে আছে!</strong></p>";
    }
    
    echo "<p><a href='marksheet.php?student_id=$student_id&session=$session&exam_type=$exam_type' target='_blank' style='background: #007bff; color: white; padding: 10px 15px; text-decoration: none; border-radius: 5px;'>মার্কশীট দেখুন</a></p>";
}
?>