<?php
require_once 'config/database.php';

echo "<!DOCTYPE html>
<html lang='bn'>
<head>
    <meta charset='UTF-8'>
    <meta name='viewport' content='width=device-width, initial-scale=1.0'>
    <title>Class Roll Duplicate সমস্যা সমাধান</title>
    <link href='https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css' rel='stylesheet'>
    <link href='https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css' rel='stylesheet'>
</head>
<body class='bg-light'>
    <div class='container mt-5'>
        <div class='row justify-content-center'>
            <div class='col-md-10'>
                <div class='card'>
                    <div class='card-header bg-warning text-dark'>
                        <h4><i class='fas fa-exclamation-triangle'></i> Class Roll Duplicate সমস্যা সমাধান</h4>
                    </div>
                    <div class='card-body'>";

try {
    echo "<h5><i class='fas fa-search'></i> সমস্যা বিশ্লেষণ...</h5>";
    
    // Check students table structure
    echo "<h6>1. Students টেবিল স্ট্রাকচার চেক:</h6>";
    
    $table_exists = $pdo->query("SHOW TABLES LIKE 'students'")->fetch();
    if (!$table_exists) {
        echo "<div class='alert alert-danger'>";
        echo "<i class='fas fa-times'></i> Students টেবিল পাওয়া যায়নি";
        echo "</div>";
    } else {
        // Show current indexes
        $indexes = $pdo->query("SHOW INDEX FROM students")->fetchAll();
        
        echo "<div class='table-responsive'>";
        echo "<table class='table table-sm'>";
        echo "<thead><tr><th>Index Name</th><th>Column</th><th>Unique</th><th>Action Needed</th></tr></thead>";
        echo "<tbody>";
        
        $problematic_indexes = [];
        foreach ($indexes as $index) {
            $unique = ($index['Non_unique'] == 0) ? 'YES' : 'NO';
            $action = '';
            
            // Check for problematic unique constraints
            if ($index['Non_unique'] == 0 && 
                (strpos($index['Key_name'], 'roll') !== false || 
                 strpos($index['Key_name'], 'class') !== false)) {
                $problematic_indexes[] = $index['Key_name'];
                $action = '<span class=\"text-danger\">সরাতে হবে</span>';
            }
            
            echo "<tr>";
            echo "<td>" . $index['Key_name'] . "</td>";
            echo "<td>" . $index['Column_name'] . "</td>";
            echo "<td>" . $unique . "</td>";
            echo "<td>" . $action . "</td>";
            echo "</tr>";
        }
        
        echo "</tbody></table>";
        echo "</div>";
        
        if (!empty($problematic_indexes)) {
            echo "<div class='alert alert-warning'>";
            echo "<i class='fas fa-exclamation-triangle'></i> সমস্যাযুক্ত Unique Constraints পাওয়া গেছে: ";
            echo implode(', ', $problematic_indexes);
            echo "</div>";
        }
    }
    
    // Check current duplicate issues
    echo "<hr>";
    echo "<h6>2. বর্তমান Duplicate Roll সমস্যা:</h6>";
    
    try {
        $duplicates = $pdo->query("
            SELECT 
                class_id, 
                roll_number, 
                session,
                group_name,
                COUNT(*) as count,
                GROUP_CONCAT(CONCAT(first_name, ' ', last_name) SEPARATOR ', ') as students
            FROM students 
            WHERE roll_number IS NOT NULL 
            GROUP BY class_id, roll_number, session, COALESCE(group_name, '')
            HAVING COUNT(*) > 1
        ")->fetchAll();
        
        if (empty($duplicates)) {
            echo "<div class='alert alert-success'>";
            echo "<i class='fas fa-check'></i> কোন duplicate roll পাওয়া যায়নি";
            echo "</div>";
        } else {
            echo "<div class='alert alert-info'>";
            echo "<i class='fas fa-info-circle'></i> " . count($duplicates) . "টি duplicate roll পাওয়া গেছে:";
            echo "</div>";
            
            echo "<div class='table-responsive'>";
            echo "<table class='table table-sm'>";
            echo "<thead><tr><th>Class ID</th><th>Roll</th><th>Session</th><th>Group</th><th>Count</th><th>Students</th></tr></thead>";
            echo "<tbody>";
            
            foreach ($duplicates as $dup) {
                echo "<tr>";
                echo "<td>" . $dup['class_id'] . "</td>";
                echo "<td>" . $dup['roll_number'] . "</td>";
                echo "<td>" . $dup['session'] . "</td>";
                echo "<td>" . ($dup['group_name'] ?: 'No Group') . "</td>";
                echo "<td><span class='badge bg-warning'>" . $dup['count'] . "</span></td>";
                echo "<td><small>" . $dup['students'] . "</small></td>";
                echo "</tr>";
            }
            
            echo "</tbody></table>";
            echo "</div>";
        }
        
    } catch (Exception $e) {
        echo "<div class='alert alert-danger'>";
        echo "<i class='fas fa-times'></i> Duplicate check করতে সমস্যা: " . $e->getMessage();
        echo "</div>";
    }
    
    // Show solution options
    echo "<hr>";
    echo "<h5><i class='fas fa-tools'></i> সমাধানের অপশন:</h5>";
    
    echo "<div class='row'>";
    echo "<div class='col-md-6'>";
    echo "<div class='card'>";
    echo "<div class='card-header bg-success text-white'>";
    echo "<h6><i class='fas fa-magic'></i> স্মার্ট সমাধান (প্রস্তাবিত)</h6>";
    echo "</div>";
    echo "<div class='card-body'>";
    echo "<p><strong>যা করা হবে:</strong></p>";
    echo "<ul>";
    echo "<li>Problematic unique constraints সরানো</li>";
    echo "<li>Group-wise roll numbering allow করা</li>";
    echo "<li>একই ক্লাসে বিভিন্ন গ্রুপে একই roll OK</li>";
    echo "<li>শুধু same class + same session + same group এ duplicate prevent</li>";
    echo "</ul>";
    echo "<form method='POST'>";
    echo "<input type='hidden' name='smart_fix' value='1'>";
    echo "<button type='submit' class='btn btn-success' onclick='return confirm(\"আপনি কি নিশ্চিত?\")'>স্মার্ট সমাধান করুন</button>";
    echo "</form>";
    echo "</div>";
    echo "</div>";
    echo "</div>";
    
    echo "<div class='col-md-6'>";
    echo "<div class='card'>";
    echo "<div class='card-header bg-primary text-white'>";
    echo "<h6><i class='fas fa-cog'></i> সম্পূর্ণ Constraint সরানো</h6>";
    echo "</div>";
    echo "<div class='card-body'>";
    echo "<p><strong>যা করা হবে:</strong></p>";
    echo "<ul>";
    echo "<li>সব roll-related unique constraints সরানো</li>";
    echo "<li>কোন duplicate check থাকবে না</li>";
    echo "<li>সম্পূর্ণ freedom roll numbering এ</li>";
    echo "<li>Manual duplicate management</li>";
    echo "</ul>";
    echo "<form method='POST'>";
    echo "<input type='hidden' name='remove_all_constraints' value='1'>";
    echo "<button type='submit' class='btn btn-primary' onclick='return confirm(\"আপনি কি নিশ্চিত?\")'>সব Constraints সরান</button>";
    echo "</form>";
    echo "</div>";
    echo "</div>";
    echo "</div>";
    echo "</div>";
    
    // Handle smart fix
    if (isset($_POST['smart_fix'])) {
        echo "<hr>";
        echo "<h5><i class='fas fa-magic'></i> স্মার্ট সমাধান চলছে...</h5>";
        
        try {
            $pdo->beginTransaction();
            
            // Remove problematic unique constraints
            $indexes = $pdo->query("SHOW INDEX FROM students WHERE Non_unique = 0")->fetchAll();
            $removed_count = 0;
            
            foreach ($indexes as $index) {
                $key_name = $index['Key_name'];
                
                // Skip PRIMARY key
                if ($key_name == 'PRIMARY') continue;
                
                // Remove constraints that might cause roll duplicate issues
                if (strpos($key_name, 'roll') !== false || 
                    strpos($key_name, 'class') !== false ||
                    strpos($key_name, 'unique_class_roll') !== false) {
                    
                    try {
                        $pdo->exec("ALTER TABLE students DROP INDEX $key_name");
                        echo "<div class='alert alert-success'>";
                        echo "<i class='fas fa-check'></i> Removed constraint: $key_name";
                        echo "</div>";
                        $removed_count++;
                    } catch (Exception $e) {
                        echo "<div class='alert alert-warning'>";
                        echo "<i class='fas fa-exclamation-triangle'></i> Could not remove $key_name: " . $e->getMessage();
                        echo "</div>";
                    }
                }
            }
            
            // Create a new smart constraint that allows same roll in different groups
            // But prevents duplicate in same class + session + group
            try {
                $pdo->exec("
                    ALTER TABLE students 
                    ADD CONSTRAINT unique_class_session_group_roll 
                    UNIQUE (class_id, session, COALESCE(group_name, ''), roll_number)
                ");
                
                echo "<div class='alert alert-success'>";
                echo "<i class='fas fa-plus'></i> Added smart constraint: unique_class_session_group_roll";
                echo "</div>";
                
            } catch (Exception $e) {
                // If constraint creation fails, it's OK - we'll handle in code
                echo "<div class='alert alert-info'>";
                echo "<i class='fas fa-info-circle'></i> Smart constraint creation skipped (will handle in code): " . $e->getMessage();
                echo "</div>";
            }
            
            $pdo->commit();
            
            echo "<div class='alert alert-success'>";
            echo "<h6><i class='fas fa-thumbs-up'></i> স্মার্ট সমাধান সম্পন্ন!</h6>";
            echo "<p>এখন একই ক্লাসে বিভিন্ন গ্রুপে একই roll number ব্যবহার করতে পারবেন।</p>";
            echo "<p>Removed $removed_count problematic constraints.</p>";
            echo "</div>";
            
        } catch (Exception $e) {
            $pdo->rollBack();
            echo "<div class='alert alert-danger'>";
            echo "<i class='fas fa-exclamation-triangle'></i> স্মার্ট সমাধান করতে সমস্যা: " . $e->getMessage();
            echo "</div>";
        }
    }
    
    // Handle remove all constraints
    if (isset($_POST['remove_all_constraints'])) {
        echo "<hr>";
        echo "<h5><i class='fas fa-cog'></i> সব Constraints সরানো হচ্ছে...</h5>";
        
        try {
            $indexes = $pdo->query("SHOW INDEX FROM students WHERE Non_unique = 0")->fetchAll();
            $removed_count = 0;
            
            foreach ($indexes as $index) {
                $key_name = $index['Key_name'];
                
                // Skip PRIMARY key
                if ($key_name == 'PRIMARY') continue;
                
                try {
                    $pdo->exec("ALTER TABLE students DROP INDEX $key_name");
                    echo "<div class='alert alert-success'>";
                    echo "<i class='fas fa-check'></i> Removed constraint: $key_name";
                    echo "</div>";
                    $removed_count++;
                } catch (Exception $e) {
                    echo "<div class='alert alert-warning'>";
                    echo "<i class='fas fa-exclamation-triangle'></i> Could not remove $key_name: " . $e->getMessage();
                    echo "</div>";
                }
            }
            
            echo "<div class='alert alert-success'>";
            echo "<h6><i class='fas fa-thumbs-up'></i> সব Constraints সরানো সম্পন্ন!</h6>";
            echo "<p>এখন কোন roll duplicate restriction নেই।</p>";
            echo "<p>Removed $removed_count constraints.</p>";
            echo "</div>";
            
        } catch (Exception $e) {
            echo "<div class='alert alert-danger'>";
            echo "<i class='fas fa-exclamation-triangle'></i> Constraints সরাতে সমস্যা: " . $e->getMessage();
            echo "</div>";
        }
    }
    
} catch (Exception $e) {
    echo "<div class='alert alert-danger'>";
    echo "<h5><i class='fas fa-exclamation-triangle'></i> ত্রুটি!</h5>";
    echo "<p>" . $e->getMessage() . "</p>";
    echo "</div>";
}

echo "                    </div>
                    <div class='card-footer'>
                        <div class='row'>
                            <div class='col-md-4'>
                                <a href='students/add.php' class='btn btn-primary w-100'>
                                    <i class='fas fa-user-plus'></i> শিক্ষার্থী যোগ
                                </a>
                            </div>
                            <div class='col-md-4'>
                                <a href='students/list.php' class='btn btn-success w-100'>
                                    <i class='fas fa-list'></i> শিক্ষার্থী তালিকা
                                </a>
                            </div>
                            <div class='col-md-4'>
                                <a href='index.php' class='btn btn-secondary w-100'>
                                    <i class='fas fa-home'></i> হোমে ফিরুন
                                </a>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</body>
</html>";
?>