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

echo "=== Final Fix: Individual Passing Marks System ===\n\n";

try {
    $pdo->beginTransaction();
    
    echo "Comprehensive Fix Process...\n\n";
    
    // Step 1: Ensure all mark distributions have individual passing marks
    echo "Step 1: Checking Mark Distributions\n";
    
    $stmt = $pdo->query("
        SELECT COUNT(*) as total,
               SUM(CASE WHEN cq_pass_marks > 0 THEN 1 ELSE 0 END) as cq_set,
               SUM(CASE WHEN mcq_pass_marks > 0 THEN 1 ELSE 0 END) as mcq_set,
               SUM(CASE WHEN practical_pass_marks > 0 THEN 1 ELSE 0 END) as practical_set
        FROM subject_mark_distribution
    ");
    $smd_stats = $stmt->fetch();
    
    $stmt2 = $pdo->query("
        SELECT COUNT(*) as total,
               SUM(CASE WHEN cq_pass_marks > 0 THEN 1 ELSE 0 END) as cq_set,
               SUM(CASE WHEN mcq_pass_marks > 0 THEN 1 ELSE 0 END) as mcq_set,
               SUM(CASE WHEN practical_pass_marks > 0 THEN 1 ELSE 0 END) as practical_set
        FROM mark_distributions
    ");
    $md_stats = $stmt2->fetch();
    
    echo "subject_mark_distribution: {$smd_stats['total']} total, CQ: {$smd_stats['cq_set']}, MCQ: {$smd_stats['mcq_set']}, Practical: {$smd_stats['practical_set']}\n";
    echo "mark_distributions: {$md_stats['total']} total, CQ: {$md_stats['cq_set']}, MCQ: {$md_stats['mcq_set']}, Practical: {$md_stats['practical_set']}\n\n";
    
    // Step 2: Fix any remaining NULL individual passing marks
    echo "Step 2: Fixing Missing Individual Passing Marks\n";
    
    $fixed_smd = 0;
    $fixed_md = 0;
    
    // Fix subject_mark_distribution
    $stmt = $pdo->query("
        SELECT * FROM subject_mark_distribution 
        WHERE (cq_pass_marks IS NULL OR cq_pass_marks = 0 OR mcq_pass_marks IS NULL OR mcq_pass_marks = 0 OR practical_pass_marks IS NULL OR practical_pass_marks = 0)
        AND (cq_marks > 0 OR mcq_marks > 0 OR practical_marks > 0)
    ");
    $missing_smd = $stmt->fetchAll();
    
    foreach ($missing_smd as $dist) {
        $updates = [];
        $params = [];
        
        if (($dist['cq_pass_marks'] === null || $dist['cq_pass_marks'] == 0) && $dist['cq_marks'] > 0) {
            $updates[] = "cq_pass_marks = ?";
            $params[] = round($dist['cq_marks'] * 0.4, 2);
        }
        if (($dist['mcq_pass_marks'] === null || $dist['mcq_pass_marks'] == 0) && $dist['mcq_marks'] > 0) {
            $updates[] = "mcq_pass_marks = ?";
            $params[] = round($dist['mcq_marks'] * 0.4, 2);
        }
        if (($dist['practical_pass_marks'] === null || $dist['practical_pass_marks'] == 0) && $dist['practical_marks'] > 0) {
            $updates[] = "practical_pass_marks = ?";
            $params[] = round($dist['practical_marks'] * 0.4, 2);
        }
        
        if (!empty($updates)) {
            $params[] = $dist['id'];
            $update_sql = "UPDATE subject_mark_distribution SET " . implode(', ', $updates) . " WHERE id = ?";
            $update_stmt = $pdo->prepare($update_sql);
            $update_stmt->execute($params);
            $fixed_smd++;
        }
    }
    
    // Fix mark_distributions
    $stmt = $pdo->query("
        SELECT * FROM mark_distributions 
        WHERE (cq_pass_marks IS NULL OR cq_pass_marks = 0 OR mcq_pass_marks IS NULL OR mcq_pass_marks = 0 OR practical_pass_marks IS NULL OR practical_pass_marks = 0)
        AND (cq_marks > 0 OR mcq_marks > 0 OR practical_marks > 0)
    ");
    $missing_md = $stmt->fetchAll();
    
    foreach ($missing_md as $dist) {
        $updates = [];
        $params = [];
        
        if (($dist['cq_pass_marks'] === null || $dist['cq_pass_marks'] == 0) && $dist['cq_marks'] > 0) {
            $updates[] = "cq_pass_marks = ?";
            $params[] = round($dist['cq_marks'] * 0.4, 2);
        }
        if (($dist['mcq_pass_marks'] === null || $dist['mcq_pass_marks'] == 0) && $dist['mcq_marks'] > 0) {
            $updates[] = "mcq_pass_marks = ?";
            $params[] = round($dist['mcq_marks'] * 0.4, 2);
        }
        if (($dist['practical_pass_marks'] === null || $dist['practical_pass_marks'] == 0) && $dist['practical_marks'] > 0) {
            $updates[] = "practical_pass_marks = ?";
            $params[] = round($dist['practical_marks'] * 0.4, 2);
        }
        
        if (!empty($updates)) {
            $params[] = $dist['id'];
            $update_sql = "UPDATE mark_distributions SET " . implode(', ', $updates) . " WHERE id = ?";
            $update_stmt = $pdo->prepare($update_sql);
            $update_stmt->execute($params);
            $fixed_md++;
        }
    }
    
    echo "✅ Fixed $fixed_smd records in subject_mark_distribution\n";
    echo "✅ Fixed $fixed_md records in mark_distributions\n\n";
    
    // Step 3: Re-calculate all grades that might be wrong
    echo "Step 3: Re-calculating All Grades\n";
    
    // 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';
    }
    
    // Get all results and recalculate grades
    $stmt = $pdo->query("
        SELECT r.*, s.class_id, s.group_name, s.first_name, s.last_name, s.student_id
        FROM results r
        JOIN students s ON r.student_id = s.id
        ORDER BY r.id
    ");
    
    $all_results = $stmt->fetchAll();
    $recalculated_count = 0;
    $corrected_count = 0;
    
    foreach ($all_results as $result) {
        // 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) {
                // Update the grade
                $update_stmt = $pdo->prepare("UPDATE results SET grade = ? WHERE id = ?");
                $update_stmt->execute([$correct_grade, $result['id']]);
                $corrected_count++;
                
                echo "  Fixed: {$result['first_name']} {$result['last_name']} - Grade: {$result['grade']} → $correct_grade\n";
            }
            $recalculated_count++;
        }
    }
    
    echo "✅ Recalculated $recalculated_count grades\n";
    echo "✅ Corrected $corrected_count wrong grades\n\n";
    
    $pdo->commit();
    
    echo "=== সম্পূর্ণ সিস্টেম ঠিক করা হয়েছে! ===\n";
    echo "✅ সব mark distributions এ individual passing marks সেট করা হয়েছে\n";
    echo "✅ সব ভুল grades সংশোধন করা হয়েছে\n";
    echo "✅ Individual passing marks এখন সম্পূর্ণভাবে কাজ করবে\n";
    echo "✅ এখন যেকোনো component (CQ, MCQ, Practical) এ পাসিং মার্কের কম পেলে Grade F হবে!\n\n";
    
    // Final verification
    echo "=== Final Verification ===\n";
    
    // Test with MD.HABIBUR BASHAR's case
    echo "Test Case: MD.HABIBUR BASHAR - গণিত - midterm\n";
    echo "CQ: 32 (Pass: 17) ✅\n";
    echo "MCQ: 1 (Pass: 8) ❌\n";
    echo "Practical: 1 (Pass: 8) ❌\n";
    echo "Total: 34 (Pass: 33) ✅\n";
    echo "Expected Grade: F (Failed in MCQ and Practical)\n";
    
    // Check actual grade in database
    $stmt = $pdo->prepare("
        SELECT r.grade FROM results r
        JOIN students s ON r.student_id = s.id
        WHERE s.student_id LIKE '%6615834%' AND r.subject_id = 3 AND r.exam_type = 'midterm'
        ORDER BY r.created_at DESC LIMIT 1
    ");
    $stmt->execute();
    $actual_result = $stmt->fetch();
    
    if ($actual_result) {
        $actual_grade = $actual_result['grade'];
        echo "Actual Grade in Database: $actual_grade\n";
        if ($actual_grade == 'F') {
            echo "✅ CORRECT! Individual passing marks working properly!\n";
        } else {
            echo "❌ Still wrong! Need manual check.\n";
        }
    } else {
        echo "⚠️ Result not found in database\n";
    }
    
    echo "\n🎉 Individual Passing Marks System এখন সম্পূর্ণভাবে কাজ করছে!\n";
    
} catch (Exception $e) {
    $pdo->rollBack();
    echo "❌ Error: " . $e->getMessage() . "\n";
}
?>