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

// Include calculateGrade function
function calculateGrade($obtained_marks, $total_marks, $mark_distribution = null, $individual_marks = []) {
    if ($total_marks == 0) return 'F';
    
    // Check individual passing marks if set
    if ($mark_distribution) {
        // Check CQ passing marks
        if (!empty($mark_distribution['cq_pass_marks']) && $mark_distribution['cq_marks'] > 0) {
            $cq_obtained = (float)($individual_marks['cq'] ?? 0);
            if ($cq_obtained < $mark_distribution['cq_pass_marks']) {
                return 'F'; // Failed in CQ
            }
        }
        
        // Check MCQ passing marks
        if (!empty($mark_distribution['mcq_pass_marks']) && $mark_distribution['mcq_marks'] > 0) {
            $mcq_obtained = (float)($individual_marks['mcq'] ?? 0);
            if ($mcq_obtained < $mark_distribution['mcq_pass_marks']) {
                return 'F'; // Failed in MCQ
            }
        }
        
        // Check Practical passing marks
        if (!empty($mark_distribution['practical_pass_marks']) && $mark_distribution['practical_marks'] > 0) {
            $practical_obtained = (float)($individual_marks['practical'] ?? 0);
            if ($practical_obtained < $mark_distribution['practical_pass_marks']) {
                return 'F'; // Failed in Practical
            }
        }
        
        // Check overall passing marks
        $overall_pass_marks = $mark_distribution['overall_pass_marks'] ?? $mark_distribution['pass_marks'] ?? ($total_marks * 0.4);
        if ($obtained_marks < $overall_pass_marks) {
            return 'F'; // Failed overall
        }
    }
    
    $percentage = ($obtained_marks / $total_marks) * 100;
    
    if ($percentage >= 80) return 'A+';
    elseif ($percentage >= 70) return 'A';
    elseif ($percentage >= 60) return 'A-';
    elseif ($percentage >= 50) return 'B';
    elseif ($percentage >= 40) return 'C';
    elseif ($percentage >= 33) return 'D';
    else return 'F';
}

echo "=== Fix Wrong Grades for All Students ===\n\n";

try {
    $pdo->beginTransaction();
    
    // Get all results that might have wrong grades
    $stmt = $pdo->query("
        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 r.grade != 'F'
        ORDER BY r.id
    ");
    
    $results = $stmt->fetchAll();
    $fixed_count = 0;
    $checked_count = 0;
    
    echo "Checking " . count($results) . " results for grade calculation errors...\n\n";
    
    foreach ($results as $result) {
        $checked_count++;
        
        // 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) {
            $individual_marks = [
                'cq' => $result['cq_marks'],
                'mcq' => $result['mcq_marks'],
                'practical' => $result['practical_marks'],
                'assignment' => $result['assignment_marks'],
                'attendance' => $result['attendance_marks']
            ];
            
            $correct_grade = calculateGrade($result['marks_obtained'], $mark_dist['total_marks'], $mark_dist, $individual_marks);
            
            if ($result['grade'] != $correct_grade) {
                echo "FIXING: {$result['first_name']} {$result['last_name']} ({$result['student_id']}) - {$result['subject_name']} - {$result['exam_type']}\n";
                echo "  Current Grade: {$result['grade']} → Correct Grade: $correct_grade\n";
                
                // Check reasons for failure
                $reasons = [];
                if ($mark_dist['cq_pass_marks'] > 0 && $result['cq_marks'] < $mark_dist['cq_pass_marks']) {
                    $reasons[] = "CQ ({$result['cq_marks']} < {$mark_dist['cq_pass_marks']})";
                }
                if ($mark_dist['mcq_pass_marks'] > 0 && $result['mcq_marks'] < $mark_dist['mcq_pass_marks']) {
                    $reasons[] = "MCQ ({$result['mcq_marks']} < {$mark_dist['mcq_pass_marks']})";
                }
                if ($mark_dist['practical_pass_marks'] > 0 && $result['practical_marks'] < $mark_dist['practical_pass_marks']) {
                    $reasons[] = "Practical ({$result['practical_marks']} < {$mark_dist['practical_pass_marks']})";
                }
                if ($result['marks_obtained'] < $mark_dist['overall_pass_marks']) {
                    $reasons[] = "Overall ({$result['marks_obtained']} < {$mark_dist['overall_pass_marks']})";
                }
                
                if (!empty($reasons)) {
                    echo "  Reason: Failed in " . implode(', ', $reasons) . "\n";
                }
                
                // Update the grade
                $update_stmt = $pdo->prepare("UPDATE results SET grade = ? WHERE id = ?");
                $update_stmt->execute([$correct_grade, $result['id']]);
                
                $fixed_count++;
                echo "  ✅ Updated!\n\n";
            }
        }
        
        // Progress indicator
        if ($checked_count % 50 == 0) {
            echo "Progress: $checked_count/" . count($results) . " checked, $fixed_count fixed\n";
        }
    }
    
    $pdo->commit();
    
    echo "=== সম্পন্ন! ===\n";
    echo "Total Checked: $checked_count\n";
    echo "Total Fixed: $fixed_count\n\n";
    
    if ($fixed_count > 0) {
        echo "✅ $fixed_count টি ভুল grade সংশোধন করা হয়েছে!\n";
        echo "এখন individual passing marks সঠিকভাবে কাজ করবে।\n";
    } else {
        echo "✅ কোন ভুল grade পাওয়া যায়নি। সব grade সঠিক আছে।\n";
    }
    
} catch (Exception $e) {
    $pdo->rollBack();
    echo "❌ Error: " . $e->getMessage() . "\n";
}
?>