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

echo "Emergency Email Fix Script\n";
echo "==========================\n";

try {
    $tables = ['users', 'students', 'teachers'];
    
    foreach ($tables as $table) {
        try {
            echo "\nProcessing table: $table\n";
            
            // Check if table exists
            $exists = $pdo->query("SHOW TABLES LIKE '$table'")->fetch();
            if (!$exists) {
                echo "- Table $table does not exist\n";
                continue;
            }
            
            // Check if email column exists
            $columns = $pdo->query("DESCRIBE $table")->fetchAll();
            $has_email = false;
            foreach ($columns as $col) {
                if ($col['Field'] == 'email') {
                    $has_email = true;
                    break;
                }
            }
            
            if (!$has_email) {
                echo "- Table $table has no email column\n";
                continue;
            }
            
            // Remove unique constraints on email
            $indexes = $pdo->query("SHOW INDEX FROM $table WHERE Column_name = 'email' AND Non_unique = 0")->fetchAll();
            foreach ($indexes as $index) {
                try {
                    $pdo->exec("ALTER TABLE $table DROP INDEX " . $index['Key_name']);
                    echo "- Removed unique constraint: " . $index['Key_name'] . "\n";
                } catch (Exception $e) {
                    echo "- Could not remove constraint: " . $e->getMessage() . "\n";
                }
            }
            
            // Make email nullable
            try {
                $pdo->exec("ALTER TABLE $table MODIFY email VARCHAR(255) NULL");
                echo "- Made email nullable\n";
            } catch (Exception $e) {
                echo "- Could not make email nullable: " . $e->getMessage() . "\n";
            }
            
            // Fix empty emails
            try {
                $result = $pdo->exec("UPDATE $table SET email = NULL WHERE email = ''");
                echo "- Fixed $result empty emails\n";
            } catch (Exception $e) {
                echo "- Could not fix empty emails: " . $e->getMessage() . "\n";
            }
            
            // Fix duplicate emails
            try {
                $duplicates = $pdo->query("
                    SELECT email, GROUP_CONCAT(id ORDER BY id) as ids, COUNT(*) as count
                    FROM $table 
                    WHERE email IS NOT NULL AND email != '' 
                    GROUP BY email 
                    HAVING COUNT(*) > 1
                ")->fetchAll();
                
                $fixed_count = 0;
                foreach ($duplicates as $dup) {
                    $ids = explode(',', $dup['ids']);
                    // Keep first one, update others
                    for ($i = 1; $i < count($ids); $i++) {
                        $unique_email = $table . '_' . $ids[$i] . '_' . time() . '_' . $i . '@temp.local';
                        $stmt = $pdo->prepare("UPDATE $table SET email = ? WHERE id = ?");
                        $stmt->execute([$unique_email, $ids[$i]]);
                        $fixed_count++;
                    }
                }
                
                if ($fixed_count > 0) {
                    echo "- Fixed $fixed_count duplicate emails\n";
                } else {
                    echo "- No duplicate emails found\n";
                }
                
            } catch (Exception $e) {
                echo "- Could not fix duplicates: " . $e->getMessage() . "\n";
            }
            
        } catch (Exception $e) {
            echo "- Error processing $table: " . $e->getMessage() . "\n";
        }
    }
    
    echo "\n✅ SUCCESS: Email duplicate issue should be fixed!\n";
    echo "You can now try adding students/teachers again.\n";
    
} catch (Exception $e) {
    echo "\n❌ ERROR: " . $e->getMessage() . "\n";
}
?>