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

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

echo "<h2>ডুপ্লিকেট ফলাফল খুঁজে বের করা</h2>";

// Find duplicate results in database
$stmt = $pdo->query("
    SELECT 
        student_id, 
        subject_id, 
        session, 
        exam_type, 
        COUNT(*) as duplicate_count,
        GROUP_CONCAT(id ORDER BY id DESC) as result_ids,
        GROUP_CONCAT(marks_obtained ORDER BY id DESC) as marks_list
    FROM results 
    GROUP BY student_id, subject_id, session, exam_type 
    HAVING COUNT(*) > 1
    ORDER BY duplicate_count DESC, student_id, subject_id
");
$duplicates = $stmt->fetchAll();

if (empty($duplicates)) {
    echo "<p style='color: green;'><strong>✅ ডাটাবেসে কোন ডুপ্লিকেট ফলাফল পাওয়া যায়নি!</strong></p>";
} else {
    echo "<p style='color: red;'><strong>⚠️ " . count($duplicates) . " টি ডুপ্লিকেট ফলাফল পাওয়া গেছে:</strong></p>";
    
    echo "<table border='1' style='border-collapse: collapse; width: 100%;'>";
    echo "<tr><th>Student ID</th><th>Subject ID</th><th>Session</th><th>Exam Type</th><th>Duplicate Count</th><th>Result IDs</th><th>Marks</th><th>Action</th></tr>";
    
    foreach ($duplicates as $dup) {
        echo "<tr>";
        echo "<td>{$dup['student_id']}</td>";
        echo "<td>{$dup['subject_id']}</td>";
        echo "<td>{$dup['session']}</td>";
        echo "<td>{$dup['exam_type']}</td>";
        echo "<td><strong>{$dup['duplicate_count']}</strong></td>";
        echo "<td>{$dup['result_ids']}</td>";
        echo "<td>{$dup['marks_list']}</td>";
        echo "<td><a href='?clean_duplicate=1&student_id={$dup['student_id']}&subject_id={$dup['subject_id']}&session={$dup['session']}&exam_type={$dup['exam_type']}' onclick='return confirm(\"এই ডুপ্লিকেট পরিষ্কার করতে চান?\")' style='background: #dc3545; color: white; padding: 5px 10px; text-decoration: none; border-radius: 3px; font-size: 12px;'>পরিষ্কার করুন</a></td>";
        echo "</tr>";
    }
    echo "</table>";
    
    echo "<br><p><a href='?clean_all_duplicates=1' onclick='return confirm(\"সব ডুপ্লিকেট পরিষ্কার করতে চান? এটি পূর্বাবস্থায় ফেরানো যাবে না!\")' style='background: #dc3545; color: white; padding: 10px 20px; text-decoration: none; border-radius: 5px; font-weight: bold;'>সব ডুপ্লিকেট পরিষ্কার করুন</a></p>";
}

// Clean specific duplicate
if (isset($_GET['clean_duplicate']) && $_GET['clean_duplicate'] == '1') {
    $student_id = $_GET['student_id'];
    $subject_id = $_GET['subject_id'];
    $session = $_GET['session'];
    $exam_type = $_GET['exam_type'];
    
    try {
        $pdo->beginTransaction();
        
        // Get all results for this combination, ordered by ID DESC (latest first)
        $stmt = $pdo->prepare("
            SELECT id FROM results 
            WHERE student_id = ? AND subject_id = ? AND session = ? AND exam_type = ?
            ORDER BY id DESC
        ");
        $stmt->execute([$student_id, $subject_id, $session, $exam_type]);
        $result_ids = $stmt->fetchAll(PDO::FETCH_COLUMN);
        
        if (count($result_ids) > 1) {
            // Keep the first (latest) result, delete the rest
            $keep_id = array_shift($result_ids);
            $delete_ids = $result_ids;
            
            $placeholders = str_repeat('?,', count($delete_ids) - 1) . '?';
            $stmt = $pdo->prepare("DELETE FROM results WHERE id IN ($placeholders)");
            $stmt->execute($delete_ids);
            
            $deleted_count = $stmt->rowCount();
            
            $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 "Student ID: $student_id, Subject ID: $subject_id<br>";
            echo "রাখা হয়েছে: Result ID $keep_id<br>";
            echo "মুছে ফেলা হয়েছে: $deleted_count টি ডুপ্লিকেট";
            echo "</div>";
            
            // Refresh the page to show updated results
            echo "<script>setTimeout(function(){ window.location.href = window.location.pathname; }, 2000);</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>";
    }
}

// Clean all duplicates
if (isset($_GET['clean_all_duplicates']) && $_GET['clean_all_duplicates'] == '1') {
    try {
        $pdo->beginTransaction();
        
        $total_deleted = 0;
        
        foreach ($duplicates as $dup) {
            // Get all results for this combination, ordered by ID DESC (latest first)
            $stmt = $pdo->prepare("
                SELECT id FROM results 
                WHERE student_id = ? AND subject_id = ? AND session = ? AND exam_type = ?
                ORDER BY id DESC
            ");
            $stmt->execute([$dup['student_id'], $dup['subject_id'], $dup['session'], $dup['exam_type']]);
            $result_ids = $stmt->fetchAll(PDO::FETCH_COLUMN);
            
            if (count($result_ids) > 1) {
                // Keep the first (latest) result, delete the rest
                $keep_id = array_shift($result_ids);
                $delete_ids = $result_ids;
                
                $placeholders = str_repeat('?,', count($delete_ids) - 1) . '?';
                $stmt = $pdo->prepare("DELETE FROM results WHERE id IN ($placeholders)");
                $stmt->execute($delete_ids);
                
                $total_deleted += $stmt->rowCount();
            }
        }
        
        $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; }, 2000);</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='test_marksheet_duplicates.php' style='background: #28a745; color: white; padding: 10px 15px; text-decoration: none; border-radius: 5px;'>মার্কশীট টেস্ট করুন</a></p>";
?>