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

echo "<h2>Subject Debug for Student ID 1647</h2>";

// Check subjects table
echo "<h3>All Subjects with গণিত:</h3>";
$stmt = $pdo->query("SELECT id, name, code FROM subjects WHERE name LIKE '%গণিত%' ORDER BY name");
$subjects = $stmt->fetchAll();

echo "<table border='1'>";
echo "<tr><th>ID</th><th>Name</th><th>Code</th></tr>";
foreach ($subjects as $subject) {
    echo "<tr><td>{$subject['id']}</td><td>{$subject['name']}</td><td>{$subject['code']}</td></tr>";
}
echo "</table>";

// Check results for student 1647
echo "<h3>Results for Student 1647 (2024, midterm):</h3>";
$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.id
");
$stmt->execute([1647, '2024', 'midterm']);
$results = $stmt->fetchAll();

echo "<table border='1'>";
echo "<tr><th>Result ID</th><th>Subject ID</th><th>Subject Name</th><th>Subject Code</th><th>CQ</th><th>MCQ</th><th>Practical</th><th>Total</th><th>Grade</th></tr>";
foreach ($results as $result) {
    echo "<tr><td>{$result['id']}</td><td>{$result['subject_id']}</td><td>{$result['subject_name']}</td><td>{$result['subject_code']}</td><td>{$result['cq_marks']}</td><td>{$result['mcq_marks']}</td><td>{$result['practical_marks']}</td><td>{$result['marks_obtained']}</td><td>{$result['grade']}</td></tr>";
}
echo "</table>";

// Check for duplicates
echo "<h3>Duplicate Analysis:</h3>";
$subject_names = [];
$subject_ids = [];
$duplicates = [];

foreach ($results as $result) {
    if (in_array($result['subject_name'], $subject_names)) {
        $duplicates[] = "Duplicate subject name: " . $result['subject_name'];
    }
    if (in_array($result['subject_id'], $subject_ids)) {
        $duplicates[] = "Duplicate subject ID: " . $result['subject_id'];
    }
    $subject_names[] = $result['subject_name'];
    $subject_ids[] = $result['subject_id'];
}

if (empty($duplicates)) {
    echo "<p style='color: green;'>No duplicates found in raw results!</p>";
} else {
    echo "<ul style='color: red;'>";
    foreach ($duplicates as $dup) {
        echo "<li>$dup</li>";
    }
    echo "</ul>";
}

// Check subject names encoding
echo "<h3>Subject Names Encoding Check:</h3>";
foreach ($results as $result) {
    echo "<p><strong>Subject:</strong> {$result['subject_name']} | <strong>Bytes:</strong> " . bin2hex($result['subject_name']) . "</p>";
}

// Check if there are multiple results for same subject_id
echo "<h3>Subject ID Frequency Check:</h3>";
$subject_id_count = [];
foreach ($results as $result) {
    $sid = $result['subject_id'];
    if (!isset($subject_id_count[$sid])) {
        $subject_id_count[$sid] = 0;
    }
    $subject_id_count[$sid]++;
}

echo "<table border='1'>";
echo "<tr><th>Subject ID</th><th>Subject Name</th><th>Count</th><th>Status</th></tr>";
foreach ($subject_id_count as $sid => $count) {
    $subject_name = '';
    foreach ($results as $r) {
        if ($r['subject_id'] == $sid) {
            $subject_name = $r['subject_name'];
            break;
        }
    }
    $status = $count > 1 ? '<span style="color: red;">DUPLICATE</span>' : '<span style="color: green;">UNIQUE</span>';
    echo "<tr><td>$sid</td><td>$subject_name</td><td>$count</td><td>$status</td></tr>";
}
echo "</table>";

echo "<p><a href='marksheet.php?student_id=1647&session=2024&exam_type=midterm&debug=1' style='background: #007bff; color: white; padding: 8px 15px; text-decoration: none; border-radius: 4px;'>🔍 View Marksheet Debug</a></p>";
?>