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

$student_roll = '20250020';
$class_id = 10;
$session = 2025;
$exam_type = 'final';

echo "<h2>Exact Tabulation Sheet Logic Debug</h2>\n";

// Get student info
$stmt = $pdo->prepare("
    SELECT s.* FROM students s 
    WHERE s.student_id = ? AND s.class_id = ? AND s.status = 'active'
");
$stmt->execute([$student_roll, $class_id]);
$student = $stmt->fetch();

if (!$student) {
    echo "<p>Student not found!</p>\n";
    exit();
}

echo "<h3>Student: " . htmlspecialchars($student['first_name'] . ' ' . $student['last_name']) . " ($student_roll)</h3>\n";

// Get all subjects for students in this class (same logic as tabulation sheet)
$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 = ?
    ORDER BY sub.name
");
$stmt->execute([$class_id]);
$raw_subjects = $stmt->fetchAll();

// Group Bangla and English subjects (same function as tabulation sheet)
function groupSubjects($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;
        }
    }
    
    // Add combined Bangla subject only if there are multiple Bangla subjects
    if (count($bangla_subjects) > 1) {
        $grouped_subjects[] = [
            'id' => 'combined_bangla',
            'name' => 'বাংলা(একত্রে)',
            'code' => 'CBAN'
        ];
    } else if (count($bangla_subjects) == 1) {
        // Add single Bangla subject as is
        $grouped_subjects[] = $bangla_subjects[0];
    }
    
    // Add combined English subject only if there are multiple English subjects
    if (count($english_subjects) > 1) {
        $grouped_subjects[] = [
            'id' => 'combined_english',
            'name' => 'ইংরেজি(একত্রে)',
            'code' => 'CENG'
        ];
    } else if (count($english_subjects) == 1) {
        // Add single English subject as is
        $grouped_subjects[] = $english_subjects[0];
    }
    
    return $grouped_subjects;
}

$subjects = groupSubjects($raw_subjects);
$original_subjects = $raw_subjects;

// Get all results for this class, session, and exam (same logic as tabulation sheet)
$stmt = $pdo->prepare("
    SELECT r.*, s.student_id as roll_number
    FROM results r
    JOIN students s ON r.student_id = s.id
    WHERE s.class_id = ? AND r.session = ? AND r.exam_type = ?
");
$result_params = [$class_id, $session, $exam_type];
$stmt->execute($result_params);
$all_results = $stmt->fetchAll();

// Organize results in matrix format (same logic as tabulation sheet)
$results_matrix = [];
foreach ($all_results as $result) {
    $results_matrix[$result['student_id']][$result['subject_id']] = $result;
}

// Simulate the exact tabulation sheet logic for this student
$student_total = 0;
$student_failed = false;
$student_grades = [];
$total_grade_points = 0;
$gpa_subjects = 0; // Subjects used for GPA calculation (excluding failed subjects)
$fourth_subject_found = false; // Track if 4th subject exists
$fourth_subject_grade_point = 0; // Track 4th subject grade point for adjustment

echo "<h3>Processing Subjects:</h3>\n";
echo "<table border='1'>\n";
echo "<tr><th>Subject</th><th>Result</th><th>Grade</th><th>4th Subject</th><th>Student Failed</th><th>Total Points</th><th>GPA Subjects</th></tr>\n";

foreach ($subjects as $subject) {
    // Handle combined subjects (simplified for this debug)
    if ($subject['id'] == 'combined_bangla' || $subject['id'] == 'combined_english') {
        // Skip combined subjects for simplicity in this debug
        continue;
    } else if (isset($results_matrix[$student['id']][$subject['id']])) {
        $result = $results_matrix[$student['id']][$subject['id']];
        
        // Apply custom grading for Bangla and English subjects (simplified)
        // In real tabulation sheet, this would be more complex
        
        $student_total += $result['marks_obtained'];
        $student_grades[] = $result['grade'];
        
        // Check if this subject is a fourth subject
        $stmt_check = $pdo->prepare("SELECT is_fourth_subject FROM student_subjects WHERE student_id = ? AND subject_id = ? LIMIT 1");
        $stmt_check->execute([$student['id'], $subject['id']]);
        $subject_info = $stmt_check->fetch();
        $is_fourth_subject = $subject_info && $subject_info['is_fourth_subject'] ? true : false;
        
        if ($result['grade'] == 'F') {
            // Check if student failed in this subject and it's not a fourth subject
            if (!$is_fourth_subject) {
                $student_failed = true;
            }
            echo "<tr>";
            echo "<td>" . htmlspecialchars($subject['name']) . "</td>";
            echo "<td>Exists</td>";
            echo "<td>" . $result['grade'] . "</td>";
            echo "<td>" . ($is_fourth_subject ? 'YES' : 'NO') . "</td>";
            echo "<td>" . ($student_failed ? 'YES' : 'NO') . "</td>";
            echo "<td>$total_grade_points</td>";
            echo "<td>$gpa_subjects</td>";
            echo "</tr>\n";
        } else {
            // Add grade point for GPA calculation (only for passed subjects)
            if ($is_fourth_subject) {
                $fourth_subject_found = true;
                $fourth_subject_grade_point = getGradePoint($result['grade']);
                // For 4th subject: if GPA > 2.00, subtract 2.00, otherwise subtract 0
                $adjusted_grade_point = ($fourth_subject_grade_point > 2.00) ? ($fourth_subject_grade_point - 2.00) : $fourth_subject_grade_point;
                $total_grade_points += $adjusted_grade_point;
            } else {
                // Regular subject - add full grade point
                $total_grade_points += getGradePoint($result['grade']);
            }
            $gpa_subjects++;
            
            echo "<tr>";
            echo "<td>" . htmlspecialchars($subject['name']) . "</td>";
            echo "<td>Exists</td>";
            echo "<td>" . $result['grade'] . "</td>";
            echo "<td>" . ($is_fourth_subject ? 'YES' : 'NO') . "</td>";
            echo "<td>" . ($student_failed ? 'YES' : 'NO') . "</td>";
            echo "<td>$total_grade_points</td>";
            echo "<td>$gpa_subjects</td>";
            echo "</tr>\n";
        }
    } else {
        // Student did not participate or sit for this exam
        // Check if this subject is a fourth subject before marking as failed
        $stmt_check = $pdo->prepare("SELECT is_fourth_subject FROM student_subjects WHERE student_id = ? AND subject_id = ? LIMIT 1");
        $stmt_check->execute([$student['id'], $subject['id']]);
        $subject_info = $stmt_check->fetch();
        $is_fourth_subject = $subject_info && $subject_info['is_fourth_subject'] ? true : false;
        
        // Only mark as failed if it's not a fourth subject
        if (!$is_fourth_subject) {
            $student_failed = true;
        }
        
        echo "<tr>";
        echo "<td>" . htmlspecialchars($subject['name']) . "</td>";
        echo "<td>Missing</td>";
        echo "<td>F</td>";
        echo "<td>" . ($is_fourth_subject ? 'YES' : 'NO') . "</td>";
        echo "<td>" . ($student_failed ? 'YES' : 'NO') . "</td>";
        echo "<td>$total_grade_points</td>";
        echo "<td>$gpa_subjects</td>";
        echo "</tr>\n";
    }
}

echo "</table>\n";

echo "<h3>Final Values:</h3>\n";
echo "<ul>\n";
echo "<li>Student Total Marks: $student_total</li>\n";
echo "<li>Student Failed: " . ($student_failed ? 'YES' : 'NO') . "</li>\n";
echo "<li>Total Grade Points: $total_grade_points</li>\n";
echo "<li>GPA Subjects: $gpa_subjects</li>\n";
echo "<li>Fourth Subject Found: " . ($fourth_subject_found ? 'YES' : 'NO') . "</li>\n";
if ($fourth_subject_found) {
    echo "<li>Fourth Subject Grade Point: $fourth_subject_grade_point</li>\n";
}
echo "</ul>\n";

// Calculate GPA using exact tabulation sheet logic
$gpa = 0.00;
if ($gpa_subjects > 0) {
    // For Jessore Board: if 6 subjects including 4th subject, divide by 5
    // Otherwise, use normal calculation
    $total_subjects = count($subjects);
    // For Jessore Board: when 4th subject exists, divide by (number of subjects - 1)
    // because 4th subject's extra points are added but the subject itself is not counted in division
    if ($fourth_subject_found) {
        // When 4th subject exists, divide by (gpa_subjects - 1)
        // because 4th subject is not counted in the division
        // Make sure we don't divide by zero
        $gpa = ($gpa_subjects - 1) > 0 ? round($total_grade_points / ($gpa_subjects - 1), 2) : 0.00;
    } else {
        // Normal calculation
        $gpa = round($total_grade_points / $gpa_subjects, 2);
    }
} else {
    $gpa = 0.00; // GPA is 0.00 if all subjects are failed
}

echo "<h3>GPA Calculation (Tabulation Sheet Logic):</h3>\n";
echo "<p>GPA = ";
if ($gpa_subjects > 0) {
    if ($fourth_subject_found) {
        echo "($total_grade_points / ($gpa_subjects - 1)) = ";
    } else {
        echo "($total_grade_points / $gpa_subjects) = ";
    }
}
echo "$gpa</p>\n";

echo "<h3>Display Logic:</h3>\n";
echo "<p>Display GPA: " . (!$student_failed && $gpa_subjects > 0 ? rtrim(rtrim(sprintf('%.2f', $gpa), '0'), '.') : '(empty)') . "</p>\n";
echo "<p>Overall Grade: " . ($student_failed ? 'F' : 'PASS') . "</p>\n";

// Grade point function
function getGradePoint($grade) {
    switch (strtoupper($grade)) {
        case 'A+': return 5.00;
        case 'A': return 4.00;
        case 'A-': return 3.50;
        case 'B': return 3.00;
        case 'C': return 2.00;
        case 'D': return 1.00;
        case 'F': return 0.00;
        default: return 0.00;
    }
}
?>