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

echo "<h2>Detailed Debugging for Missing Subject</h2>\n";

$class_id = 10;
$group_name = 'SCIENCE';
$session = 2025;
$exam_type = 'final';

echo "<p>Class ID: $class_id</p>\n";
echo "<p>Group Name: " . htmlspecialchars($group_name) . "</p>\n";
echo "<p>Session: $session</p>\n";
echo "<p>Exam Type: " . htmlspecialchars($exam_type) . "</p>\n";

// Get the exact same query that the tabulation sheet uses
$stmt = $pdo->prepare("
    SELECT DISTINCT sub.id, sub.name, sub.code 
    FROM student_subjects ss
    JOIN subjects sub ON ss.subject_id = sub.id
    JOIN students s ON ss.student_id = s.id
    WHERE s.class_id = ?
    AND s.group_name = ?
    ORDER BY sub.name
");
$params = [$class_id, $group_name];
$stmt->execute($params);
$raw_subjects = $stmt->fetchAll();

echo "<h3>Raw Subjects (from tabulation sheet query):</h3>\n";
echo "<p>Total: " . count($raw_subjects) . "</p>\n";
echo "<table border='1'>\n";
echo "<tr><th>ID</th><th>Name</th><th>Code</th></tr>\n";
foreach ($raw_subjects as $subject) {
    echo "<tr>";
    echo "<td>" . $subject['id'] . "</td>";
    echo "<td>" . htmlspecialchars($subject['name']) . "</td>";
    echo "<td>" . htmlspecialchars($subject['code']) . "</td>";
    echo "</tr>\n";
}
echo "</table>\n";

// Apply the exact same grouping logic as the tabulation sheet
function groupSubjectsDetailed($raw_subjects) {
    $grouped_subjects = [];
    $bangla_subjects = [];
    $english_subjects = [];
    
    foreach ($raw_subjects as $subject) {
        // Check if this is a Bangla subject (more specific matching to avoid false positives)
        // Match subjects that are specifically Bangla subjects, not just containing "Bangla"
        if ((stripos($subject['name'], 'বাংলা') !== false && stripos($subject['name'], 'Bangladesh') === false) || 
            (preg_match('/\bBangla\b/i', $subject['name']) && stripos($subject['name'], 'Bangladesh') === false)) {
            $bangla_subjects[] = $subject;
        }
        // Check if this is an English subject (more specific matching)
        else if (stripos($subject['name'], 'ইংরেজি') !== false || preg_match('/\bEnglish\b/i', $subject['name'])) {
            $english_subjects[] = $subject;
        }
        // For other subjects, add directly
        else {
            $grouped_subjects[] = $subject;
        }
    }
    
    echo "<h4>Bangla Subjects Found (" . count($bangla_subjects) . "):</h4>\n";
    if (!empty($bangla_subjects)) {
        echo "<ul>\n";
        foreach ($bangla_subjects as $b) {
            echo "<li>" . htmlspecialchars($b['name']) . " (ID: " . $b['id'] . ", Code: " . $b['code'] . ")</li>\n";
        }
        echo "</ul>\n";
    }
    
    echo "<h4>English Subjects Found (" . count($english_subjects) . "):</h4>\n";
    if (!empty($english_subjects)) {
        echo "<ul>\n";
        foreach ($english_subjects as $e) {
            echo "<li>" . htmlspecialchars($e['name']) . " (ID: " . $e['id'] . ", Code: " . $e['code'] . ")</li>\n";
        }
        echo "</ul>\n";
    }
    
    // Add combined Bangla subject only if there are multiple Bangla subjects
    if (count($bangla_subjects) > 1) {
        $combined_bangla = [
            'id' => 'combined_bangla',
            'name' => 'বাংলা(একত্রে)',
            'code' => 'CBAN'
        ];
        $grouped_subjects[] = $combined_bangla;
        echo "<p>Created combined Bangla subject: " . htmlspecialchars($combined_bangla['name']) . "</p>\n";
    } else if (count($bangla_subjects) == 1) {
        // Add single Bangla subject as is
        $grouped_subjects[] = $bangla_subjects[0];
        echo "<p>Added single Bangla subject: " . htmlspecialchars($bangla_subjects[0]['name']) . "</p>\n";
    }
    
    // Add combined English subject only if there are multiple English subjects
    if (count($english_subjects) > 1) {
        $combined_english = [
            'id' => 'combined_english',
            'name' => 'ইংরেজি(একত্রে)',
            'code' => 'CENG'
        ];
        $grouped_subjects[] = $combined_english;
        echo "<p>Created combined English subject: " . htmlspecialchars($combined_english['name']) . "</p>\n";
    } else if (count($english_subjects) == 1) {
        // Add single English subject as is
        $grouped_subjects[] = $english_subjects[0];
        echo "<p>Added single English subject: " . htmlspecialchars($english_subjects[0]['name']) . "</p>\n";
    }
    
    return $grouped_subjects;
}

// Group subjects with detailed output
$subjects = groupSubjectsDetailed($raw_subjects);

echo "<h3>Final Grouped Subjects (" . count($subjects) . "):</h3>\n";
echo "<table border='1'>\n";
echo "<tr><th>ID</th><th>Name</th><th>Code</th></tr>\n";
foreach ($subjects as $subject) {
    echo "<tr>";
    echo "<td>" . $subject['id'] . "</td>";
    echo "<td>" . htmlspecialchars($subject['name']) . "</td>";
    echo "<td>" . htmlspecialchars($subject['code']) . "</td>";
    echo "</tr>\n";
}
echo "</table>\n";

// Let's also check what subjects each student actually has
$stmt = $pdo->prepare("
    SELECT s.id, s.student_id, s.first_name, s.last_name 
    FROM students s 
    WHERE s.class_id = ? AND s.group_name = ? AND s.status = 'active'
    ORDER BY s.student_id
    LIMIT 1
");
$stmt->execute([$class_id, $group_name]);
$student = $stmt->fetch();

if ($student) {
    echo "<h3>First Student's Subjects:</h3>\n";
    echo "<p>Student: " . htmlspecialchars($student['first_name'] . ' ' . $student['last_name']) . " (ID: " . $student['id'] . ")</p>\n";
    
    // Get all subjects for this specific student
    $stmt = $pdo->prepare("
        SELECT sub.id, sub.name, sub.code
        FROM student_subjects ss
        JOIN subjects sub ON ss.subject_id = sub.id
        WHERE ss.student_id = ?
        ORDER BY sub.name
    ");
    $stmt->execute([$student['id']]);
    $student_subjects = $stmt->fetchAll();
    
    echo "<p>Student has " . count($student_subjects) . " subjects:</p>\n";
    echo "<ul>\n";
    foreach ($student_subjects as $subj) {
        echo "<li>" . htmlspecialchars($subj['name']) . " (ID: " . $subj['id'] . ", Code: " . $subj['code'] . ")</li>\n";
    }
    echo "</ul>\n";
}

// Check if there's any subject that might be getting filtered out in the tabulation sheet
echo "<h3>Possible Issues:</h3>\n";
echo "<ol>\n";
echo "<li>If you're expecting 14 subjects but seeing only 13, one subject might be getting filtered out in the HTML display</li>\n";
echo "<li>Check if 'Bangladesh and World Introduction' is being displayed properly</li>\n";
echo "<li>Check if there's a CSS or HTML issue hiding one column</li>\n";
echo "<li>Verify that all subjects are being rendered in the table</li>\n";
echo "</ol>\n";

echo "<p><a href='reports/tabulation_sheet.php?class_id=10&group_name=" . urlencode($group_name) . "&session=$session&exam_type=" . urlencode($exam_type) . "' target='_blank'>View Tabulation Sheet</a></p>\n";
?>