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

echo "=== Fix MD.HABIBUR BASHAR Grade ===\n\n";

try {
    // Find the student and his results
    $stmt = $pdo->prepare("
        SELECT r.*, s.class_id, s.group_name, s.first_name, s.last_name, s.student_id,
               subj.name as subject_name
        FROM results r
        JOIN students s ON r.student_id = s.id
        JOIN subjects subj ON r.subject_id = subj.id
        WHERE s.student_id LIKE '%6615834%'
        ORDER BY r.created_at DESC
    ");
    $stmt->execute();
    $results = $stmt->fetchAll();
    
    if ($results) {
        echo "Found " . count($results) . " results for MD.HABIBUR BASHAR:\n\n";
        
        foreach ($results as $result) {
            echo "Processing: {$result['subject_name']} - {$result['exam_type']}\n";
            echo "Current Grade: {$result['grade']}\n";
            echo "Marks: CQ={$result['cq_marks']}, MCQ={$result['mcq_marks']}, Practical={$result['practical_marks']}\n";
            echo "Total: {$result['marks_obtained']}\n";
            
            // Get mark distribution
            $stmt2 = $pdo->prepare("
                SELECT * FROM subject_mark_distribution 
                WHERE subject_id = ? AND class_id = ? AND session = ? AND exam_type = ?
                AND (group_name = ? OR group_name IS NULL OR group_name = '')
                ORDER BY 
                    CASE 
                        WHEN group_name = ? THEN 1
                        WHEN group_name IS NULL OR group_name = '' THEN 2
                        ELSE 3
                    END
                LIMIT 1
            ");
            $stmt2->execute([
                $result['subject_id'], 
                $result['class_id'], 
                $result['session'], 
                $result['exam_type'],
                $result['group_name'],
                $result['group_name']
            ]);
            $mark_dist = $stmt2->fetch();
            
            if (!$mark_dist) {
                // Try mark_distributions table
                $stmt3 = $pdo->prepare("
                    SELECT * FROM mark_distributions 
                    WHERE subject_id = ? AND class_id = ? AND session = ? AND exam_type = ?
                ");
                $stmt3->execute([$result['subject_id'], $result['class_id'], $result['session'], $result['exam_type']]);
                $mark_dist = $stmt3->fetch();
            }
            
            if ($mark_dist) {
                echo "Mark Distribution:\n";
                echo "  CQ: {$mark_dist['cq_marks']} (Pass: " . ($mark_dist['cq_pass_marks'] ?? 'NULL') . ")\n";
                echo "  MCQ: {$mark_dist['mcq_marks']} (Pass: " . ($mark_dist['mcq_pass_marks'] ?? 'NULL') . ")\n";
                echo "  Practical: {$mark_dist['practical_marks']} (Pass: " . ($mark_dist['practical_pass_marks'] ?? 'NULL') . ")\n";
                echo "  Overall Pass: {$mark_dist['overall_pass_marks']}\n";
                
                // Manual grade calculation
                $should_fail = false;
                $fail_reasons = [];
                
                // Check CQ
                if ($mark_dist['cq_pass_marks'] > 0 && $mark_dist['cq_marks'] > 0) {
                    if ($result['cq_marks'] < $mark_dist['cq_pass_marks']) {
                        $should_fail = true;
                        $fail_reasons[] = "CQ ({$result['cq_marks']} < {$mark_dist['cq_pass_marks']})";
                    }
                }
                
                // Check MCQ
                if ($mark_dist['mcq_pass_marks'] > 0 && $mark_dist['mcq_marks'] > 0) {
                    if ($result['mcq_marks'] < $mark_dist['mcq_pass_marks']) {
                        $should_fail = true;
                        $fail_reasons[] = "MCQ ({$result['mcq_marks']} < {$mark_dist['mcq_pass_marks']})";
                    }
                }
                
                // Check Practical
                if ($mark_dist['practical_pass_marks'] > 0 && $mark_dist['practical_marks'] > 0) {
                    if ($result['practical_marks'] < $mark_dist['practical_pass_marks']) {
                        $should_fail = true;
                        $fail_reasons[] = "Practical ({$result['practical_marks']} < {$mark_dist['practical_pass_marks']})";
                    }
                }
                
                // Check Overall
                if ($result['marks_obtained'] < $mark_dist['overall_pass_marks']) {
                    $should_fail = true;
                    $fail_reasons[] = "Overall ({$result['marks_obtained']} < {$mark_dist['overall_pass_marks']})";
                }
                
                $correct_grade = $should_fail ? 'F' : $result['grade'];
                
                if ($should_fail) {
                    $correct_grade = 'F';
                    echo "Should FAIL because: " . implode(', ', $fail_reasons) . "\n";
                } else {
                    // Calculate percentage grade
                    $percentage = ($result['marks_obtained'] / $mark_dist['total_marks']) * 100;
                    if ($percentage >= 80) $correct_grade = 'A+';
                    elseif ($percentage >= 70) $correct_grade = 'A';
                    elseif ($percentage >= 60) $correct_grade = 'A-';
                    elseif ($percentage >= 50) $correct_grade = 'B';
                    elseif ($percentage >= 40) $correct_grade = 'C';
                    elseif ($percentage >= 33) $correct_grade = 'D';
                    else $correct_grade = 'F';
                    echo "Should PASS with grade: $correct_grade (Percentage: " . round($percentage, 2) . "%)\n";
                }
                
                echo "Correct Grade: $correct_grade\n";
                
                if ($result['grade'] != $correct_grade) {
                    echo "🚨 FIXING: {$result['grade']} → $correct_grade\n";
                    
                    // Update the grade
                    $update_stmt = $pdo->prepare("UPDATE results SET grade = ? WHERE id = ?");
                    $update_stmt->execute([$correct_grade, $result['id']]);
                    
                    echo "✅ Updated!\n";
                } else {
                    echo "✅ Grade is already correct\n";
                }
                
            } else {
                echo "❌ No mark distribution found\n";
            }
            
            echo "\n" . str_repeat("-", 60) . "\n\n";
        }
        
    } else {
        echo "❌ No results found for MD.HABIBUR BASHAR\n";
    }
    
} catch (Exception $e) {
    echo "❌ Error: " . $e->getMessage() . "\n";
}
?>