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

try {
    echo "Final Test: GPA 0.00 Students Should Have No Merit Position\n\n";

    // Test with a student who failed in non-4th subject
    $class_id = 10;
    $session = '2025';
    $exam_type = 'final';

    // Get the student who failed (from previous debug)
    $student_id = 6269; // MST. AMENA AKTER TULI - failed in Bangla

    // Simulate the exact logic from bulk_print_marksheets.php
    $stmt = $pdo->prepare("SELECT id FROM students WHERE class_id = ? AND status = 'active'");
    $stmt->execute([$class_id]);
    $all_student_ids = $stmt->fetchAll(PDO::FETCH_COLUMN);

    // Calculate GPA and total marks for each student
    $student_merit_data = [];
    foreach ($all_student_ids as $sid) {
        // Get student results
        $stmt = $pdo->prepare("
            SELECT r.*, sub.name as subject_name, sub.code as subject_code
            FROM results r
            LEFT JOIN subjects sub ON r.subject_id = sub.id
            WHERE r.student_id = ? AND r.session = ? AND r.exam_type = ?
            AND r.id IN (
                SELECT MAX(id)
                FROM results
                WHERE student_id = ? AND session = ? AND exam_type = ?
                GROUP BY subject_id
            )
            ORDER BY sub.name
        ");
        $stmt->execute([$sid, $session, $exam_type, $sid, $session, $exam_type]);
        $student_results = $stmt->fetchAll();

        if (empty($student_results)) {
            $student_merit_data[$sid] = ['gpa' => 0.00, 'total_marks' => 0];
            continue;
        }

        // Get student subjects to determine fourth subjects
        $stmt_subject = $pdo->prepare(
            "SELECT ss.subject_id, s.name as subject_name, s.code as subject_code, ss.is_fourth_subject
            FROM student_subjects ss
            JOIN subjects s ON ss.subject_id = s.id
            WHERE ss.student_id = ?
            ORDER BY s.name"
        );
        $stmt_subject->execute([$sid]);
        $student_subjects = $stmt_subject->fetchAll();

        // Create subject info map
        $student_subject_info_map = [];
        foreach ($student_subjects as $subject) {
            $student_subject_info_map[$subject['subject_id']] = $subject;
        }

        // Simplified processing (no complex grouping for test)
        $processed_results = $student_results;

        // Calculate total marks
        $student_total_obtained = 0;
        foreach ($processed_results as $result) {
            $student_total_obtained += $result['marks_obtained'];
        }

        // Check if student failed in any non-4th subject
        $student_failed_in_non_fourth = false;
        foreach ($processed_results as $result) {
            $subject_id = $result['subject_id'];
            if (isset($student_subject_info_map[$subject_id])) {
                $subject_info = $student_subject_info_map[$subject_id];
                // If failed in non-4th subject, GPA should be 0
                if ($result['grade'] == 'F' && !$subject_info['is_fourth_subject']) {
                    $student_failed_in_non_fourth = true;
                    break;
                }
            }
        }

        // Calculate GPA
        $student_gpa = 0.00;
        if (!$student_failed_in_non_fourth) {
            $student_total_grade_points = 0;
            $student_gpa_subjects = 0;
            $student_fourth_subject_found = false;

            foreach ($processed_results as $result) {
                $subject_id = $result['subject_id'];
                if (isset($student_subject_info_map[$subject_id])) {
                    $subject_info = $student_subject_info_map[$subject_id];
                    if ($result['grade'] != 'F') {
                        $grade_point = getGradePoint($result['grade']);
                        $student_total_grade_points += $grade_point;
                        $student_gpa_subjects++;

                        if ($subject_info['is_fourth_subject']) {
                            $student_fourth_subject_found = true;
                        }
                    }
                }
            }

            if ($student_gpa_subjects > 0) {
                if ($student_fourth_subject_found) {
                    $student_gpa = round($student_total_grade_points / ($student_gpa_subjects - 1), 2);
                } else {
                    $student_gpa = round($student_total_grade_points / $student_gpa_subjects, 2);
                }
                if ($student_gpa > 5.00) {
                    $student_gpa = 5.00;
                }
            }
        }

        $student_merit_data[$sid] = [
            'gpa' => $student_gpa,
            'total_marks' => $student_total_obtained
        ];
    }

    // Sort students by GPA first, then by total marks
    uasort($student_merit_data, function($a, $b) {
        $gpa_compare = $b['gpa'] <=> $a['gpa'];
        if ($gpa_compare !== 0) {
            return $gpa_compare;
        }
        return $b['total_marks'] <=> $a['total_marks'];
    });

    // Create merit order excluding GPA 0.00 students
    $merit_order = [];
    $position = 1;
    foreach ($student_merit_data as $sid => $data) {
        if ($data['gpa'] > 0.00) {
            $merit_order[$sid] = $position++;
        }
    }

    // Check specific students
    $test_students = [
        6279 => "MST. SAKIRA MUSTARI SAMMI (Roll 1)",
        6273 => "MD. ABU BAKKAR SIDDIK RAFI (Roll 3)",
        6269 => "MST. AMENA AKTER TULI (Roll 4 - Failed)",
        6278 => "MST MOHIMA AKTER SUROVI (Roll 5)"
    ];

    echo "Merit Position Results:\n";
    echo "Student ID | Student Name | GPA | Merit Position\n";
    echo "-----------|--------------|-----|---------------\n";

    foreach ($test_students as $sid => $name) {
        $data = $student_merit_data[$sid] ?? ['gpa' => 'N/A', 'total_marks' => 0];
        $merit_pos = isset($merit_order[$sid]) ? $merit_order[$sid] : 'N/A (GPA 0.00)';

        printf("%-10d | %-25s | %-3s | %-15s\n",
            $sid,
            substr($name, 0, 25),
            $data['gpa'],
            $merit_pos
        );
    }

    echo "\nSummary:\n";
    echo "- Students with GPA > 0.00 get merit positions\n";
    echo "- Students with GPA = 0.00 get 'N/A' (no merit position)\n";

} catch (Exception $e) {
    echo 'Error: ' . $e->getMessage() . "\n";
}

function getGradePoint($grade) {
    switch ($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;
        default: return 0.00;
    }
}
?>
