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

echo "Emergency Roll Constraint Fix\n";
echo "=============================\n";

try {
    echo "Checking students table constraints...\n";
    
    // Get all unique constraints
    $indexes = $pdo->query("SHOW INDEX FROM students WHERE Non_unique = 0")->fetchAll();
    
    echo "Found " . count($indexes) . " unique constraints:\n";
    foreach ($indexes as $index) {
        echo "- " . $index['Key_name'] . " on " . $index['Column_name'] . "\n";
    }
    
    echo "\nRemoving problematic roll constraints...\n";
    
    $removed_count = 0;
    foreach ($indexes as $index) {
        $key_name = $index['Key_name'];
        
        // Skip PRIMARY key
        if ($key_name == 'PRIMARY') {
            echo "- Skipping PRIMARY key\n";
            continue;
        }
        
        // Remove roll-related constraints
        if (strpos($key_name, 'roll') !== false || 
            strpos($key_name, 'class_roll') !== false ||
            $key_name == 'unique_class_roll') {
            
            try {
                $pdo->exec("ALTER TABLE students DROP INDEX $key_name");
                echo "✅ Removed constraint: $key_name\n";
                $removed_count++;
            } catch (Exception $e) {
                echo "❌ Could not remove $key_name: " . $e->getMessage() . "\n";
            }
        } else {
            echo "- Keeping constraint: $key_name\n";
        }
    }
    
    echo "\n" . str_repeat('=', 40) . "\n";
    echo "✅ SUCCESS!\n";
    echo "Removed $removed_count roll-related constraints.\n";
    echo "Now you can add students with same roll numbers in different groups.\n";
    
    // Verify the fix
    echo "\nVerifying fix...\n";
    $remaining_indexes = $pdo->query("SHOW INDEX FROM students WHERE Non_unique = 0")->fetchAll();
    
    $roll_constraints = 0;
    foreach ($remaining_indexes as $index) {
        if (strpos($index['Key_name'], 'roll') !== false) {
            $roll_constraints++;
        }
    }
    
    if ($roll_constraints == 0) {
        echo "✅ No roll-related constraints remaining.\n";
        echo "✅ You can now add students without roll duplicate errors!\n";
    } else {
        echo "⚠️  Still has $roll_constraints roll-related constraints.\n";
    }
    
} catch (Exception $e) {
    echo "❌ ERROR: " . $e->getMessage() . "\n";
}
?>