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

// Check if user is admin
if (!isset($_SESSION['user_id']) || $_SESSION['user_role'] !== 'admin') {
    die('Access denied. Admin only.');
}

$action = $_GET['action'] ?? '';

if ($action === 'analyze') {
    echo "<h2>🔍 Duplicate Results Analysis</h2>";
    
    // Find duplicate results
    $stmt = $pdo->query("
        SELECT student_id, subject_id, session, exam_type, COUNT(*) as count,
               GROUP_CONCAT(id ORDER BY id) as result_ids,
               GROUP_CONCAT(marks_obtained ORDER BY id) as marks_list,
               GROUP_CONCAT(created_at ORDER BY id) as created_list
        FROM results 
        GROUP BY student_id, subject_id, session, exam_type 
        HAVING COUNT(*) > 1
        ORDER BY count DESC, student_id, subject_id
    ");
    $duplicates = $stmt->fetchAll();
    
    if (empty($duplicates)) {
        echo "<div style='background: #d1edff; padding: 20px; border: 1px solid #0d6efd; border-radius: 5px;'>";
        echo "<h3>✅ No Duplicate Results Found!</h3>";
        echo "<p>Your results table is clean. No duplicate entries detected.</p>";
        echo "</div>";
    } else {
        echo "<div style='background: #fff3cd; padding: 20px; border: 1px solid #ffc107; border-radius: 5px; margin-bottom: 20px;'>";
        echo "<h3>⚠️ Found " . count($duplicates) . " Duplicate Groups</h3>";
        echo "<p>These are groups of results that have the same student, subject, session, and exam type.</p>";
        echo "</div>";
        
        echo "<table border='1' style='border-collapse: collapse; width: 100%; margin-bottom: 20px;'>";
        echo "<tr style='background: #f8f9fa;'>";
        echo "<th>Student ID</th><th>Subject ID</th><th>Session</th><th>Exam Type</th>";
        echo "<th>Count</th><th>Result IDs</th><th>Marks</th><th>Created Dates</th><th>Action</th>";
        echo "</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['count']}</strong></td>";
            echo "<td><small>{$dup['result_ids']}</small></td>";
            echo "<td><small>{$dup['marks_list']}</small></td>";
            echo "<td><small>" . str_replace(',', '<br>', $dup['created_list']) . "</small></td>";
            echo "<td>";
            echo "<a href='?action=clean&student_id={$dup['student_id']}&subject_id={$dup['subject_id']}&session={$dup['session']}&exam_type={$dup['exam_type']}' ";
            echo "onclick='return confirm(\"Clean duplicates for this group?\")' ";
            echo "style='background: #dc3545; color: white; padding: 5px 10px; text-decoration: none; border-radius: 3px; font-size: 12px;'>Clean</a>";
            echo "</td>";
            echo "</tr>";
        }
        echo "</table>";
        
        echo "<div style='background: #f8d7da; padding: 15px; border: 1px solid #dc3545; border-radius: 5px; margin-bottom: 20px;'>";
        echo "<h4>🚨 Cleanup Strategy:</h4>";
        echo "<ul>";
        echo "<li><strong>Keep Latest:</strong> Keep the result with the highest ID (most recent)</li>";
        echo "<li><strong>Delete Others:</strong> Remove older duplicate entries</li>";
        echo "<li><strong>Backup First:</strong> Always backup your database before cleanup</li>";
        echo "</ul>";
        echo "</div>";
        
        echo "<div style='text-align: center; margin: 20px 0;'>";
        echo "<a href='?action=clean_all' onclick='return confirm(\"Are you sure you want to clean ALL duplicates? This cannot be undone!\")' ";
        echo "style='background: #dc3545; color: white; padding: 15px 30px; text-decoration: none; border-radius: 5px; font-size: 16px; font-weight: bold;'>";
        echo "🧹 Clean All Duplicates</a>";
        echo "</div>";
    }
    
} elseif ($action === 'clean') {
    $student_id = $_GET['student_id'] ?? '';
    $subject_id = $_GET['subject_id'] ?? '';
    $session = $_GET['session'] ?? '';
    $exam_type = $_GET['exam_type'] ?? '';
    
    if ($student_id && $subject_id && $session && $exam_type) {
        // Get all results for this combination
        $stmt = $pdo->prepare("
            SELECT * 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]);
        $results = $stmt->fetchAll();
        
        if (count($results) > 1) {
            // Keep the first one (highest ID), delete the rest
            $keep_id = $results[0]['id'];
            $delete_ids = array_slice(array_column($results, 'id'), 1);
            
            $placeholders = str_repeat('?,', count($delete_ids) - 1) . '?';
            $stmt = $pdo->prepare("DELETE FROM results WHERE id IN ($placeholders)");
            $stmt->execute($delete_ids);
            
            echo "<div style='background: #d1edff; padding: 20px; border: 1px solid #0d6efd; border-radius: 5px;'>";
            echo "<h3>✅ Cleanup Successful!</h3>";
            echo "<p>Kept result ID: <strong>$keep_id</strong></p>";
            echo "<p>Deleted " . count($delete_ids) . " duplicate(s): " . implode(', ', $delete_ids) . "</p>";
            echo "</div>";
        }
    }
    
    echo "<p><a href='?action=analyze'>← Back to Analysis</a></p>";
    
} elseif ($action === 'clean_all') {
    echo "<h2>🧹 Cleaning All Duplicates...</h2>";
    
    // Get all duplicate groups
    $stmt = $pdo->query("
        SELECT student_id, subject_id, session, exam_type, 
               GROUP_CONCAT(id ORDER BY id DESC) as result_ids
        FROM results 
        GROUP BY student_id, subject_id, session, exam_type 
        HAVING COUNT(*) > 1
    ");
    $duplicates = $stmt->fetchAll();
    
    $total_deleted = 0;
    $groups_cleaned = 0;
    
    foreach ($duplicates as $dup) {
        $ids = explode(',', $dup['result_ids']);
        $keep_id = array_shift($ids); // Keep the first (highest ID)
        
        if (!empty($ids)) {
            $placeholders = str_repeat('?,', count($ids) - 1) . '?';
            $stmt = $pdo->prepare("DELETE FROM results WHERE id IN ($placeholders)");
            $stmt->execute($ids);
            
            $total_deleted += count($ids);
            $groups_cleaned++;
            
            echo "<p>✅ Group {$dup['student_id']}-{$dup['subject_id']}-{$dup['session']}-{$dup['exam_type']}: ";
            echo "Kept ID $keep_id, deleted " . count($ids) . " duplicates</p>";
        }
    }
    
    echo "<div style='background: #d1edff; padding: 20px; border: 1px solid #0d6efd; border-radius: 5px; margin-top: 20px;'>";
    echo "<h3>🎉 Cleanup Complete!</h3>";
    echo "<p><strong>Groups Cleaned:</strong> $groups_cleaned</p>";
    echo "<p><strong>Total Records Deleted:</strong> $total_deleted</p>";
    echo "</div>";
    
    echo "<p><a href='?action=analyze'>← Back to Analysis</a></p>";
    
} else {
    // Main menu
    echo "<h1>🔧 Results Cleanup Tool</h1>";
    echo "<p>This tool helps you identify and clean duplicate result entries.</p>";
    
    echo "<div style='background: #fff3cd; padding: 20px; border: 1px solid #ffc107; border-radius: 5px; margin-bottom: 20px;'>";
    echo "<h3>⚠️ Important Warning</h3>";
    echo "<ul>";
    echo "<li><strong>Backup your database</strong> before running any cleanup operations</li>";
    echo "<li>This tool will <strong>permanently delete</strong> duplicate records</li>";
    echo "<li>Only the <strong>most recent</strong> result for each student-subject-session-exam combination will be kept</li>";
    echo "<li>This operation <strong>cannot be undone</strong></li>";
    echo "</ul>";
    echo "</div>";
    
    echo "<div style='text-align: center; margin: 30px 0;'>";
    echo "<a href='?action=analyze' style='background: #0d6efd; color: white; padding: 15px 30px; text-decoration: none; border-radius: 5px; font-size: 18px; font-weight: bold;'>";
    echo "🔍 Start Analysis</a>";
    echo "</div>";
    
    echo "<div style='background: #d1ecf1; padding: 15px; border: 1px solid #bee5eb; border-radius: 5px;'>";
    echo "<h4>ℹ️ How it works:</h4>";
    echo "<ol>";
    echo "<li><strong>Analysis:</strong> Scans the results table for duplicate entries</li>";
    echo "<li><strong>Review:</strong> Shows you exactly what duplicates exist</li>";
    echo "<li><strong>Cleanup:</strong> Removes duplicates, keeping only the latest entry</li>";
    echo "<li><strong>Verification:</strong> Confirms the cleanup was successful</li>";
    echo "</ol>";
    echo "</div>";
}

echo "<hr>";
echo "<p><a href='reports/marksheet.php'>← Back to Marksheet</a> | <a href='index.php'>← Back to Dashboard</a></p>";
?>