<?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>সরাসরি SQL দিয়ে ডুপ্লিকেট পরিষ্কার</h2>";

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

if (isset($_GET['execute']) && $_GET['execute'] == '1') {
    try {
        $pdo->beginTransaction();
        
        // Find and delete duplicates, keeping only the latest entry for each subject
        $sql = "
        DELETE r1 FROM results r1
        INNER JOIN results r2 
        WHERE r1.student_id = r2.student_id 
        AND r1.session = r2.session 
        AND r1.exam_type = r2.exam_type 
        AND r1.subject_id = r2.subject_id 
        AND r1.id < r2.id
        AND r1.student_id = ?
        AND r1.session = ?
        AND r1.exam_type = ?
        ";
        
        $stmt = $pdo->prepare($sql);
        $stmt->execute([$student_id, $session, $exam_type]);
        $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>✅ SQL দিয়ে সফলভাবে পরিষ্কার করা হয়েছে!</strong><br>";
        echo "মুছে ফেলা হয়েছে: $deleted_count টি ডুপ্লিকেট এন্ট্রি";
        echo "</div>";
        
    } 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>";
    }
} else {
    echo "<p style='background: #fff3cd; padding: 15px; border: 1px solid #ffc107; border-radius: 5px;'>";
    echo "<strong>⚠️ সতর্কতা:</strong> এই অপারেশনটি ডাটাবেস থেকে ডুপ্লিকেট এন্ট্রি স্থায়ীভাবে মুছে দেবে।<br>";
    echo "এটি শুধুমাত্র ছাত্র ID $student_id, Session $session, Exam Type $exam_type এর জন্য প্রযোজ্য।";
    echo "</p>";
    
    echo "<p><a href='?execute=1' onclick='return confirm(\"আপনি কি নিশ্চিত যে ডুপ্লিকেট পরিষ্কার করতে চান?\")' style='background: #dc3545; color: white; padding: 10px 20px; text-decoration: none; border-radius: 5px; font-weight: bold;'>SQL দিয়ে ডুপ্লিকেট পরিষ্কার করুন</a></p>";
}

// Show current status
$stmt = $pdo->prepare("
    SELECT r.*, sub.name as subject_name
    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 sub.name, r.id DESC
");
$stmt->execute([$student_id, $session, $exam_type]);
$current_results = $stmt->fetchAll();

echo "<h3>বর্তমান অবস্থা: " . count($current_results) . " টি এন্ট্রি</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>";

foreach ($current_results as $r) {
    echo "<tr>";
    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>";

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>";
?>