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

$student_id = 1647;
$session = '2024';
$exam_type = 'midterm';

echo "<h2>Duplicate Check for Student ID: $student_id</h2>";

// Check for duplicates in results table
$stmt = $pdo->prepare("
    SELECT r.id, r.subject_id, sub.name as subject_name, r.marks_obtained, r.grade,
           COUNT(*) OVER (PARTITION BY r.subject_id) as subject_id_count,
           COUNT(*) OVER (PARTITION BY sub.name) as subject_name_count
    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
");
$stmt->execute([$student_id, $session, $exam_type]);
$results = $stmt->fetchAll();

echo "<h3>All Results with Duplicate Counts:</h3>";
echo "<table border='1' style='border-collapse: collapse;'>";
echo "<tr><th>Result ID</th><th>Subject ID</th><th>Subject Name</th><th>Marks</th><th>Grade</th><th>Subject ID Count</th><th>Subject Name Count</th></tr>";

foreach ($results as $result) {
    $bg_color = ($result['subject_id_count'] > 1 || $result['subject_name_count'] > 1) ? 'background-color: #ffcccc;' : '';
    echo "<tr style='$bg_color'>";
    echo "<td>{$result['id']}</td>";
    echo "<td>{$result['subject_id']}</td>";
    echo "<td>{$result['subject_name']}</td>";
    echo "<td>{$result['marks_obtained']}</td>";
    echo "<td>{$result['grade']}</td>";
    echo "<td>{$result['subject_id_count']}</td>";
    echo "<td>{$result['subject_name_count']}</td>";
    echo "</tr>";
}
echo "</table>";

// Show duplicates only
$duplicates = array_filter($results, function($r) {
    return $r['subject_id_count'] > 1 || $r['subject_name_count'] > 1;
});

if (!empty($duplicates)) {
    echo "<h3 style='color: red;'>Found Duplicates:</h3>";
    echo "<table border='1' style='border-collapse: collapse;'>";
    echo "<tr><th>Result ID</th><th>Subject ID</th><th>Subject Name</th><th>Marks</th><th>Grade</th></tr>";
    foreach ($duplicates as $dup) {
        echo "<tr style='background-color: #ffcccc;'>";
        echo "<td>{$dup['id']}</td>";
        echo "<td>{$dup['subject_id']}</td>";
        echo "<td>{$dup['subject_name']}</td>";
        echo "<td>{$dup['marks_obtained']}</td>";
        echo "<td>{$dup['grade']}</td>";
        echo "</tr>";
    }
    echo "</table>";
} else {
    echo "<h3 style='color: green;'>No duplicates found!</h3>";
}

// Check subjects table for Bengali names
echo "<h3>Bengali Subject Names Check:</h3>";
$stmt = $pdo->query("SELECT id, name, code FROM subjects WHERE name LIKE '%বাংলা%' OR name LIKE '%Bangla%' ORDER BY name");
$bengali_subjects = $stmt->fetchAll();

if (!empty($bengali_subjects)) {
    echo "<table border='1' style='border-collapse: collapse;'>";
    echo "<tr><th>Subject ID</th><th>Name</th><th>Code</th><th>Hex</th></tr>";
    foreach ($bengali_subjects as $subject) {
        echo "<tr>";
        echo "<td>{$subject['id']}</td>";
        echo "<td>{$subject['name']}</td>";
        echo "<td>{$subject['code']}</td>";
        echo "<td>" . bin2hex($subject['name']) . "</td>";
        echo "</tr>";
    }
    echo "</table>";
} else {
    echo "<p style='color: red;'>No Bengali subjects found!</p>";
}
?>