<?php
session_start();
require_once '../config/database.php';

// Check if user is logged in
if (!isset($_SESSION['user_id'])) {
    header('Location: ../login.php');
    exit();
}

$student_id = 1647;
$session = '2024';
$exam_type = 'midterm';

echo "<h2>ছাত্র ID: $student_id এর ডুপ্লিকেট সমাধান</h2>";

// Get all results for this specific student
$stmt = $pdo->prepare("
    SELECT r.*, sub.name as subject_name, sub.code as subject_code
    FROM results r
    JOIN subjects sub ON r.subject_id = sub.id
    WHERE r.student_id = ? AND r.session = ? AND r.exam_type = ?
    ORDER BY r.subject_id, r.id DESC
");
$stmt->execute([$student_id, $session, $exam_type]);
$all_results = $stmt->fetchAll();

echo "<h3>বর্তমান অবস্থা:</h3>";
echo "<table border='1' style='border-collapse: collapse; width: 100%;'>";
echo "<tr><th>Result ID</th><th>Subject ID</th><th>Subject Name</th><th>Marks</th><th>Grade</th></tr>";

$subject_name_counts = [];
foreach ($all_results as $r) {
    $subject_name = trim($r['subject_name']);
    $subject_name_counts[$subject_name] = ($subject_name_counts[$subject_name] ?? 0) + 1;
    
    $bg_color = $subject_name_counts[$subject_name] > 1 ? 'background-color: #ffcccc;' : '';
    
    echo "<tr style='$bg_color'>";
    echo "<td>{$r['id']}</td>";
    echo "<td>{$r['subject_id']}</td>";
    echo "<td>{$r['subject_name']}</td>";
    echo "<td>{$r['marks_obtained']}</td>";
    echo "<td>{$r['grade']}</td>";
    echo "</tr>";
}
echo "</table>";

// Show duplicates by subject name
echo "<h3>ডুপ্লিকেট বিশ্লেষণ (Subject Name ভিত্তিক):</h3>";
$duplicates_found = false;
foreach ($subject_name_counts as $subject_name => $count) {
    if ($count > 1) {
        echo "<p style='color: red;'><strong>⚠️ ডুপ্লিকেট:</strong> '$subject_name' - $count বার</p>";
        $duplicates_found = true;
    }
}

if (!$duplicates_found) {
    echo "<p style='color: green;'><strong>✅ কোন ডুপ্লিকেট পাওয়া যায়নি!</strong></p>";
} else {
    echo "<p><a href='?fix_all=1' onclick='return confirm(\"সব ডুপ্লিকেট পরিষ্কার করবেন?\")' style='background: #dc3545; color: white; padding: 10px 20px; text-decoration: none; border-radius: 5px; font-weight: bold;'>সব ডুপ্লিকেট পরিষ্কার করুন</a></p>";
}

// Fix all duplicates if requested
if (isset($_GET['fix_all']) && $_GET['fix_all'] == '1') {
    try {
        $pdo->beginTransaction();
        
        // Group results by subject name (case-insensitive)
        $grouped_results = [];
        foreach ($all_results as $result) {
            $subject_name = strtolower(trim($result['subject_name']));
            if (!isset($grouped_results[$subject_name])) {
                $grouped_results[$subject_name] = [];
            }
            $grouped_results[$subject_name][] = $result;
        }
        
        $total_deleted = 0;
        
        foreach ($grouped_results as $subject_name => $results) {
            if (count($results) > 1) {
                // Sort by ID DESC (latest first)
                usort($results, function($a, $b) {
                    return $b['id'] - $a['id'];
                });
                
                // Keep the first (latest) result, delete the rest
                $keep_result = array_shift($results);
                $delete_results = $results;
                
                foreach ($delete_results as $delete_result) {
                    $stmt = $pdo->prepare("DELETE FROM results WHERE id = ?");
                    $stmt->execute([$delete_result['id']]);
                    $total_deleted++;
                    
                    echo "<p style='color: orange;'>মুছে ফেলা হয়েছে: Result ID {$delete_result['id']} - {$delete_result['subject_name']} (Marks: {$delete_result['marks_obtained']})</p>";
                }
                
                echo "<p style='color: green;'>রাখা হয়েছে: Result ID {$keep_result['id']} - {$keep_result['subject_name']} (Marks: {$keep_result['marks_obtained']})</p>";
            }
        }
        
        $pdo->commit();
        
        echo "<div style='background: #d4edda; color: #155724; padding: 15px; margin: 10px 0; border: 1px solid #c3e6cb; border-radius: 5px;'>";
        echo "<strong>✅ সব ডুপ্লিকেট সফলভাবে পরিষ্কার করা হয়েছে!</strong><br>";
        echo "মোট মুছে ফেলা হয়েছে: $total_deleted টি ডুপ্লিকেট এন্ট্রি";
        echo "</div>";
        
        // Refresh the page to show updated results
        echo "<script>setTimeout(function(){ window.location.href = window.location.pathname; }, 3000);</script>";
        
    } catch (Exception $e) {
        $pdo->rollBack();
        echo "<div style='background: #f8d7da; color: #721c24; padding: 15px; margin: 10px 0; border: 1px solid #f5c6cb; border-radius: 5px;'>";
        echo "<strong>❌ ত্রুটি:</strong> " . $e->getMessage();
        echo "</div>";
    }
}

echo "<br><p><a href='marksheet.php?student_id=$student_id&session=$session&exam_type=$exam_type' target='_blank' style='background: #007bff; color: white; padding: 10px 15px; text-decoration: none; border-radius: 5px;'>মার্কশীট দেখুন</a></p>";
echo "<p><a href='marksheet.php?student_id=$student_id&session=$session&exam_type=$exam_type&debug=1' target='_blank' style='background: #28a745; color: white; padding: 10px 15px; text-decoration: none; border-radius: 5px;'>ডিবাগ মোডে মার্কশীট দেখুন</a></p>";
?>