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

echo "Roll Duplicate Fix Test\n";
echo "======================\n";

try {
    echo "1. Testing database constraints...\n";
    
    // Check current constraints
    $indexes = $pdo->query("SHOW INDEX FROM students WHERE Non_unique = 0")->fetchAll();
    
    echo "Current unique constraints:\n";
    $has_roll_constraint = false;
    foreach ($indexes as $index) {
        echo "- " . $index['Key_name'] . " on " . $index['Column_name'] . "\n";
        if (strpos($index['Key_name'], 'roll') !== false) {
            $has_roll_constraint = true;
        }
    }
    
    if (!$has_roll_constraint) {
        echo "✅ No problematic roll constraints found\n";
    } else {
        echo "⚠️  Still has roll constraints\n";
    }
    
    echo "\n2. Testing roll insertion scenarios...\n";
    
    // Get a class ID for testing
    $class = $pdo->query("SELECT id FROM classes LIMIT 1")->fetch();
    if (!$class) {
        echo "❌ No classes found for testing\n";
        exit;
    }
    
    $class_id = $class['id'];
    $session = '2024';
    $roll = 1;
    
    echo "Using class_id: $class_id, session: $session, roll: $roll\n";
    
    // Test 1: Insert student with roll 1 in group A
    echo "\nTest 1: Insert roll $roll in group A...\n";
    try {
        $pdo->exec("
            INSERT INTO students (student_id, first_name, last_name, class_id, session, roll_number, group_name, status) 
            VALUES ('TEST001', 'Test', 'Student1', $class_id, '$session', $roll, 'A', 'active')
        ");
        $id1 = $pdo->lastInsertId();
        echo "✅ Successfully inserted roll $roll in group A (ID: $id1)\n";
    } catch (Exception $e) {
        echo "❌ Failed to insert roll $roll in group A: " . $e->getMessage() . "\n";
    }
    
    // Test 2: Insert student with same roll 1 in group B (should work)
    echo "\nTest 2: Insert same roll $roll in group B...\n";
    try {
        $pdo->exec("
            INSERT INTO students (student_id, first_name, last_name, class_id, session, roll_number, group_name, status) 
            VALUES ('TEST002', 'Test', 'Student2', $class_id, '$session', $roll, 'B', 'active')
        ");
        $id2 = $pdo->lastInsertId();
        echo "✅ Successfully inserted same roll $roll in group B (ID: $id2)\n";
        echo "✅ GREAT! Same roll in different groups is now allowed!\n";
    } catch (Exception $e) {
        echo "❌ Failed to insert same roll $roll in group B: " . $e->getMessage() . "\n";
        echo "   This means the fix didn't work completely.\n";
    }
    
    // Test 3: Try to insert duplicate in same group (should fail)
    echo "\nTest 3: Try to insert duplicate roll $roll in same group A...\n";
    try {
        $pdo->exec("
            INSERT INTO students (student_id, first_name, last_name, class_id, session, roll_number, group_name, status) 
            VALUES ('TEST003', 'Test', 'Student3', $class_id, '$session', $roll, 'A', 'active')
        ");
        $id3 = $pdo->lastInsertId();
        echo "⚠️  Duplicate in same group was allowed (ID: $id3)\n";
        echo "   This might be OK if you want no restrictions at all.\n";
        
        // Clean up this test record
        $pdo->exec("DELETE FROM students WHERE id = $id3");
        
    } catch (Exception $e) {
        echo "✅ Correctly prevented duplicate in same group: " . $e->getMessage() . "\n";
    }
    
    // Clean up test records
    echo "\nCleaning up test records...\n";
    if (isset($id1)) {
        $pdo->exec("DELETE FROM students WHERE id = $id1");
        echo "- Deleted test record $id1\n";
    }
    if (isset($id2)) {
        $pdo->exec("DELETE FROM students WHERE id = $id2");
        echo "- Deleted test record $id2\n";
    }
    
    echo "\n3. Checking current student data...\n";
    
    // Show current roll distribution
    $roll_stats = $pdo->query("
        SELECT 
            class_id,
            session,
            group_name,
            roll_number,
            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, session, COALESCE(group_name, ''), roll_number
        HAVING COUNT(*) > 1
        ORDER BY class_id, session, group_name, roll_number
        LIMIT 10
    ")->fetchAll();
    
    if (empty($roll_stats)) {
        echo "✅ No duplicate rolls found in current data\n";
    } else {
        echo "Current duplicate rolls:\n";
        foreach ($roll_stats as $stat) {
            echo "- Class {$stat['class_id']}, Session {$stat['session']}, Group " . ($stat['group_name'] ?: 'None') . ", Roll {$stat['roll_number']}: {$stat['count']} students\n";
            echo "  Students: {$stat['students']}\n";
        }
    }
    
    echo "\n" . str_repeat('=', 50) . "\n";
    echo "SUMMARY:\n";
    echo "- Roll constraints: " . ($has_roll_constraint ? "Still exists" : "Removed") . "\n";
    echo "- Same roll in different groups: " . (isset($id2) ? "✅ ALLOWED" : "❌ NOT ALLOWED") . "\n";
    echo "- Current duplicate rolls: " . (empty($roll_stats) ? "None" : count($roll_stats)) . "\n";
    
    if (isset($id2)) {
        echo "\n✅ SUCCESS! Roll duplicate fix is working!\n";
        echo "You can now add students with same roll numbers in different groups.\n";
    } else {
        echo "\n⚠️  Roll duplicate fix needs more work.\n";
        echo "Same roll in different groups is still not allowed.\n";
    }
    
} catch (Exception $e) {
    echo "❌ ERROR: " . $e->getMessage() . "\n";
}
?>