<?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>কাঁচা ডেটা (Raw Data): " . count($all_results) . " টি এন্ট্রি</h3>";
echo "<table border='1' style='border-collapse: collapse; width: 100%; font-size: 12px;'>";
echo "<tr style='background: #f0f0f0;'><th>Result ID</th><th>Subject ID</th><th>Subject Name</th><th>Subject Name (Hex)</th><th>CQ</th><th>MCQ</th><th>Practical</th><th>Assignment</th><th>Attendance</th><th>Total</th><th>Grade</th><th>Created</th></tr>";

$subject_counts = [];
foreach ($all_results as $r) {
    $subject_name = trim($r['subject_name']);
    $subject_counts[$subject_name] = ($subject_counts[$subject_name] ?? 0) + 1;
    
    $bg_color = $subject_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>" . bin2hex($r['subject_name']) . "</td>";
    echo "<td>{$r['cq_marks']}</td>";
    echo "<td>{$r['mcq_marks']}</td>";
    echo "<td>{$r['practical_marks']}</td>";
    echo "<td>{$r['assignment_marks']}</td>";
    echo "<td>{$r['attendance_marks']}</td>";
    echo "<td>{$r['marks_obtained']}</td>";
    echo "<td>{$r['grade']}</td>";
    echo "<td>{$r['created_at']}</td>";
    echo "</tr>";
}
echo "</table>";

// Show duplicates
echo "<h3>ডুপ্লিকেট বিশ্লেষণ:</h3>";
$duplicates_found = false;
foreach ($subject_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>";
}

// Check for specific duplicates by subject_id and subject_name
echo "<h3>Subject ID এবং Name ভিত্তিক ডুপ্লিকেট:</h3>";
$stmt = $pdo->prepare("
    SELECT 
        subject_id, 
        COUNT(*) as count,
        GROUP_CONCAT(id ORDER BY id DESC) as result_ids,
        GROUP_CONCAT(marks_obtained ORDER BY id DESC) as marks_list,
        MAX(subject_name) as subject_name
    FROM results 
    WHERE student_id = ? AND session = ? AND exam_type = ?
    GROUP BY subject_id 
    HAVING COUNT(*) > 1
");
$stmt->execute([$student_id, $session, $exam_type]);
$duplicates = $stmt->fetchAll();

if (empty($duplicates)) {
    echo "<p style='color: green;'><strong>✅ Subject ID ভিত্তিক কোন ডুপ্লিকেট নেই!</strong></p>";
} else {
    echo "<table border='1' style='border-collapse: collapse; width: 100%;'>";
    echo "<tr><th>Subject ID</th><th>Subject Name</th><th>Count</th><th>Result IDs</th><th>Marks</th><th>Action</th></tr>";
    
    foreach ($duplicates as $dup) {
        echo "<tr>";
        echo "<td>{$dup['subject_id']}</td>";
        echo "<td>{$dup['subject_name']}</td>";
        echo "<td><strong>{$dup['count']}</strong></td>";
        echo "<td>{$dup['result_ids']}</td>";
        echo "<td>{$dup['marks_list']}</td>";
        echo "<td><a href='?clean=1&subject_id={$dup['subject_id']}' onclick='return confirm(\"এই ডুপ্লিকেট পরিষ্কার করবেন?\")' style='background: #dc3545; color: white; padding: 5px 10px; text-decoration: none; border-radius: 3px;'>পরিষ্কার</a></td>";
        echo "</tr>";
    }
    echo "</table>";
}

// Clean specific duplicate if requested
if (isset($_GET['clean']) && isset($_GET['subject_id'])) {
    $subject_id_to_clean = $_GET['subject_id'];
    
    try {
        $pdo->beginTransaction();
        
        // Get all results for this subject, ordered by ID DESC (latest first)
        $stmt = $pdo->prepare("
            SELECT id FROM results 
            WHERE student_id = ? AND session = ? AND exam_type = ? AND subject_id = ?
            ORDER BY id DESC
        ");
        $stmt->execute([$student_id, $session, $exam_type, $subject_id_to_clean]);
        $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 "Subject ID: $subject_id_to_clean<br>";
            echo "রাখা হয়েছে: Result ID $keep_id<br>";
            echo "মুছে ফেলা হয়েছে: $deleted_count টি ডুপ্লিকেট";
            echo "</div>";
            
            // Refresh the page
            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='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>";
?>