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

echo "=== Fix Missing Individual Passing Marks ===\n\n";

try {
    $pdo->beginTransaction();
    
    echo "Checking for missing individual passing marks...\n\n";
    
    // Fix subject_mark_distribution table
    echo "1. Fixing subject_mark_distribution table:\n";
    
    $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)
    ");
    
    $missing_distributions = $stmt->fetchAll();
    $fixed_count = 0;
    
    foreach ($missing_distributions as $dist) {
        $updates = [];
        $params = [];
        
        // Set CQ passing marks if missing and CQ marks > 0
        if (($dist['cq_pass_marks'] === null || $dist['cq_pass_marks'] == 0) && $dist['cq_marks'] > 0) {
            $cq_pass = round($dist['cq_marks'] * 0.4, 2); // 40% of CQ marks
            $updates[] = "cq_pass_marks = ?";
            $params[] = $cq_pass;
            echo "  Setting CQ pass marks for ID {$dist['id']}: {$cq_pass}\n";
        }
        
        // Set MCQ passing marks if missing and MCQ marks > 0
        if (($dist['mcq_pass_marks'] === null || $dist['mcq_pass_marks'] == 0) && $dist['mcq_marks'] > 0) {
            $mcq_pass = round($dist['mcq_marks'] * 0.4, 2); // 40% of MCQ marks
            $updates[] = "mcq_pass_marks = ?";
            $params[] = $mcq_pass;
            echo "  Setting MCQ pass marks for ID {$dist['id']}: {$mcq_pass}\n";
        }
        
        // Set Practical passing marks if missing and Practical marks > 0
        if (($dist['practical_pass_marks'] === null || $dist['practical_pass_marks'] == 0) && $dist['practical_marks'] > 0) {
            $practical_pass = round($dist['practical_marks'] * 0.4, 2); // 40% of Practical marks
            $updates[] = "practical_pass_marks = ?";
            $params[] = $practical_pass;
            echo "  Setting Practical pass marks for ID {$dist['id']}: {$practical_pass}\n";
        }
        
        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_count++;
        }
    }
    
    echo "✅ subject_mark_distribution: {$fixed_count} records updated\n\n";
    
    // Fix mark_distributions table
    echo "2. Fixing mark_distributions table:\n";
    
    $stmt2 = $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)
    ");
    
    $missing_distributions2 = $stmt2->fetchAll();
    $fixed_count2 = 0;
    
    foreach ($missing_distributions2 as $dist) {
        $updates = [];
        $params = [];
        
        // Set CQ passing marks if missing and CQ marks > 0
        if (($dist['cq_pass_marks'] === null || $dist['cq_pass_marks'] == 0) && $dist['cq_marks'] > 0) {
            $cq_pass = round($dist['cq_marks'] * 0.4, 2); // 40% of CQ marks
            $updates[] = "cq_pass_marks = ?";
            $params[] = $cq_pass;
            echo "  Setting CQ pass marks for ID {$dist['id']}: {$cq_pass}\n";
        }
        
        // Set MCQ passing marks if missing and MCQ marks > 0
        if (($dist['mcq_pass_marks'] === null || $dist['mcq_pass_marks'] == 0) && $dist['mcq_marks'] > 0) {
            $mcq_pass = round($dist['mcq_marks'] * 0.4, 2); // 40% of MCQ marks
            $updates[] = "mcq_pass_marks = ?";
            $params[] = $mcq_pass;
            echo "  Setting MCQ pass marks for ID {$dist['id']}: {$mcq_pass}\n";
        }
        
        // Set Practical passing marks if missing and Practical marks > 0
        if (($dist['practical_pass_marks'] === null || $dist['practical_pass_marks'] == 0) && $dist['practical_marks'] > 0) {
            $practical_pass = round($dist['practical_marks'] * 0.4, 2); // 40% of Practical marks
            $updates[] = "practical_pass_marks = ?";
            $params[] = $practical_pass;
            echo "  Setting Practical pass marks for ID {$dist['id']}: {$practical_pass}\n";
        }
        
        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_count2++;
        }
    }
    
    echo "✅ mark_distributions: {$fixed_count2} records updated\n\n";
    
    $pdo->commit();
    
    echo "=== সফলভাবে সম্পন্ন! ===\n";
    echo "মোট আপডেট: " . ($fixed_count + $fixed_count2) . " টি record\n";
    echo "- subject_mark_distribution: {$fixed_count} টি record\n";
    echo "- mark_distributions: {$fixed_count2} টি record\n\n";
    echo "এখন সব mark distributions এ individual passing marks (CQ, MCQ, Practical) সেট করা আছে।\n";
    echo "Individual passing marks = Component marks এর 40%\n\n";
    
    // Show verification
    echo "=== Verification ===\n";
    
    $verify_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
    ");
    $verify_result = $verify_stmt->fetch();
    
    echo "subject_mark_distribution table:\n";
    echo "  Total records: {$verify_result['total']}\n";
    echo "  CQ passing marks set: {$verify_result['cq_set']}\n";
    echo "  MCQ passing marks set: {$verify_result['mcq_set']}\n";
    echo "  Practical passing marks set: {$verify_result['practical_set']}\n\n";
    
    $verify_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
    ");
    $verify_result2 = $verify_stmt2->fetch();
    
    echo "mark_distributions table:\n";
    echo "  Total records: {$verify_result2['total']}\n";
    echo "  CQ passing marks set: {$verify_result2['cq_set']}\n";
    echo "  MCQ passing marks set: {$verify_result2['mcq_set']}\n";
    echo "  Practical passing marks set: {$verify_result2['practical_set']}\n\n";
    
    echo "✅ Individual passing marks functionality এখন সম্পূর্ণভাবে কাজ করবে!\n";
    
} catch (Exception $e) {
    $pdo->rollBack();
    echo "❌ ত্রুটি: " . $e->getMessage() . "\n";
}
?>