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

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

try {
    echo "Step 1: Checking foreign key constraints...\n";
    
    // Check foreign key constraints
    $fks = $pdo->query("
        SELECT 
            CONSTRAINT_NAME,
            TABLE_NAME,
            COLUMN_NAME,
            REFERENCED_TABLE_NAME,
            REFERENCED_COLUMN_NAME
        FROM information_schema.KEY_COLUMN_USAGE 
        WHERE TABLE_SCHEMA = DATABASE() 
        AND TABLE_NAME = 'students' 
        AND REFERENCED_TABLE_NAME IS NOT NULL
    ")->fetchAll();
    
    echo "Found " . count($fks) . " foreign key constraints:\n";
    foreach ($fks as $fk) {
        echo "- " . $fk['CONSTRAINT_NAME'] . " on " . $fk['COLUMN_NAME'] . " -> " . $fk['REFERENCED_TABLE_NAME'] . "." . $fk['REFERENCED_COLUMN_NAME'] . "\n";
    }
    
    echo "\nStep 2: Dropping foreign key constraints temporarily...\n";
    
    $dropped_fks = [];
    foreach ($fks as $fk) {
        try {
            $pdo->exec("ALTER TABLE students DROP FOREIGN KEY " . $fk['CONSTRAINT_NAME']);
            echo "✅ Dropped FK: " . $fk['CONSTRAINT_NAME'] . "\n";
            $dropped_fks[] = $fk;
        } catch (Exception $e) {
            echo "⚠️  Could not drop FK " . $fk['CONSTRAINT_NAME'] . ": " . $e->getMessage() . "\n";
        }
    }
    
    echo "\nStep 3: Dropping problematic unique constraint...\n";
    
    try {
        $pdo->exec("ALTER TABLE students DROP INDEX unique_class_roll");
        echo "✅ Dropped unique_class_roll constraint\n";
    } catch (Exception $e) {
        echo "⚠️  Could not drop unique_class_roll: " . $e->getMessage() . "\n";
    }
    
    echo "\nStep 4: Recreating foreign key constraints...\n";
    
    foreach ($dropped_fks as $fk) {
        try {
            $sql = "ALTER TABLE students ADD CONSTRAINT " . $fk['CONSTRAINT_NAME'] . 
                   " FOREIGN KEY (" . $fk['COLUMN_NAME'] . ") REFERENCES " . 
                   $fk['REFERENCED_TABLE_NAME'] . "(" . $fk['REFERENCED_COLUMN_NAME'] . ")";
            
            $pdo->exec($sql);
            echo "✅ Recreated FK: " . $fk['CONSTRAINT_NAME'] . "\n";
        } catch (Exception $e) {
            echo "⚠️  Could not recreate FK " . $fk['CONSTRAINT_NAME'] . ": " . $e->getMessage() . "\n";
        }
    }
    
    echo "\nStep 5: Creating smart constraint (optional)...\n";
    
    // Try to create a smart constraint that allows same roll in different groups
    try {
        $pdo->exec("
            ALTER TABLE students 
            ADD CONSTRAINT unique_class_session_group_roll 
            UNIQUE (class_id, session, COALESCE(group_name, ''), roll_number)
        ");
        echo "✅ Created smart constraint: unique_class_session_group_roll\n";
        echo "   This allows same roll in different groups but prevents duplicates in same group.\n";
    } catch (Exception $e) {
        echo "ℹ️  Smart constraint not created (will handle in code): " . $e->getMessage() . "\n";
    }
    
    echo "\nStep 6: Verifying the fix...\n";
    
    $indexes = $pdo->query("SHOW INDEX FROM students WHERE Non_unique = 0")->fetchAll();
    
    echo "Remaining unique constraints:\n";
    $has_problematic = false;
    foreach ($indexes as $index) {
        echo "- " . $index['Key_name'] . " on " . $index['Column_name'] . "\n";
        if ($index['Key_name'] == 'unique_class_roll') {
            $has_problematic = true;
        }
    }
    
    echo "\n" . str_repeat('=', 50) . "\n";
    
    if (!$has_problematic) {
        echo "✅ SUCCESS! Problematic constraint removed.\n";
        echo "✅ You can now add students with same roll numbers in different groups!\n";
    } else {
        echo "⚠️  Problematic constraint still exists.\n";
        echo "   Will handle duplicate prevention in application code.\n";
    }
    
    echo "\nNow you can:\n";
    echo "- Add students with same roll in different groups\n";
    echo "- Same class, same session, different groups = OK\n";
    echo "- Only same class + session + group will prevent duplicates\n";
    
} catch (Exception $e) {
    echo "❌ ERROR: " . $e->getMessage() . "\n";
}
?>