<?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>Email 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> Email Duplicate সমস্যা সমাধান</h4>
                    </div>
                    <div class='card-body'>";

try {
    echo "<h5><i class='fas fa-search'></i> সমস্যা খুঁজে বের করা হচ্ছে...</h5>";
    
    // Check which tables have email columns
    $tables_to_check = ['students', 'teachers', 'users'];
    
    foreach ($tables_to_check as $table) {
        try {
            echo "<hr><h6><i class='fas fa-table'></i> $table টেবিল চেক করা হচ্ছে:</h6>";
            
            // Check if table exists
            $table_exists = $pdo->query("SHOW TABLES LIKE '$table'")->fetch();
            if (!$table_exists) {
                echo "<div class='alert alert-info'>";
                echo "<i class='fas fa-info-circle'></i> $table টেবিল পাওয়া যায়নি";
                echo "</div>";
                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 "<div class='alert alert-info'>";
                echo "<i class='fas fa-info-circle'></i> $table টেবিলে email কলাম নেই";
                echo "</div>";
                continue;
            }
            
            // Find duplicate emails
            $duplicates = $pdo->query("
                SELECT email, COUNT(*) as count 
                FROM $table 
                WHERE email IS NOT NULL AND email != '' 
                GROUP BY email 
                HAVING COUNT(*) > 1
            ")->fetchAll();
            
            if (empty($duplicates)) {
                echo "<div class='alert alert-success'>";
                echo "<i class='fas fa-check'></i> $table টেবিলে কোন duplicate email নেই";
                echo "</div>";
            } else {
                echo "<div class='alert alert-warning'>";
                echo "<i class='fas fa-exclamation-triangle'></i> $table টেবিলে " . count($duplicates) . "টি duplicate email পাওয়া গেছে:";
                echo "<ul class='mt-2'>";
                foreach ($duplicates as $dup) {
                    echo "<li>" . ($dup['email'] ?: '[খালি]') . " (" . $dup['count'] . " বার)</li>";
                }
                echo "</ul>";
                echo "</div>";
            }
            
            // Find empty emails
            $empty_emails = $pdo->query("
                SELECT COUNT(*) as count 
                FROM $table 
                WHERE email IS NULL OR email = ''
            ")->fetchColumn();
            
            if ($empty_emails > 0) {
                echo "<div class='alert alert-info'>";
                echo "<i class='fas fa-info-circle'></i> $table টেবিলে $empty_emails টি খালি email আছে";
                echo "</div>";
            }
            
        } catch (Exception $e) {
            echo "<div class='alert alert-danger'>";
            echo "<i class='fas fa-times'></i> $table টেবিল চেক করতে সমস্যা: " . $e->getMessage();
            echo "</div>";
        }
    }
    
    // Show fix 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-primary text-white'>";
    echo "<h6><i class='fas fa-magic'></i> অটো ফিক্স</h6>";
    echo "</div>";
    echo "<div class='card-body'>";
    echo "<p>সিস্টেম স্বয়ংক্রিয়ভাবে সমস্যা সমাধান করবে:</p>";
    echo "<ul>";
    echo "<li>খালি email গুলোতে unique email তৈরি</li>";
    echo "<li>Duplicate email গুলো আপডেট</li>";
    echo "<li>Email constraints ঠিক করা</li>";
    echo "</ul>";
    echo "<form method='POST'>";
    echo "<input type='hidden' name='auto_fix' value='1'>";
    echo "<button type='submit' class='btn btn-primary' 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-success text-white'>";
    echo "<h6><i class='fas fa-cog'></i> ম্যানুয়াল ফিক্স</h6>";
    echo "</div>";
    echo "<div class='card-body'>";
    echo "<p>নিজে সমস্যা সমাধান করুন:</p>";
    echo "<ul>";
    echo "<li>Email constraints সরানো</li>";
    echo "<li>Email field optional করা</li>";
    echo "<li>Unique constraint পরিবর্তন</li>";
    echo "</ul>";
    echo "<form method='POST'>";
    echo "<input type='hidden' name='manual_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>";
    
    // Handle auto fix
    if (isset($_POST['auto_fix'])) {
        echo "<hr>";
        echo "<h5><i class='fas fa-magic'></i> অটো ফিক্স চলছে...</h5>";
        
        $pdo->beginTransaction();
        
        try {
            foreach ($tables_to_check as $table) {
                // Check if table and email column exist
                $table_exists = $pdo->query("SHOW TABLES LIKE '$table'")->fetch();
                if (!$table_exists) continue;
                
                $columns = $pdo->query("DESCRIBE $table")->fetchAll();
                $has_email = false;
                foreach ($columns as $col) {
                    if ($col['Field'] == 'email') {
                        $has_email = true;
                        break;
                    }
                }
                if (!$has_email) continue;
                
                echo "<div class='alert alert-info'>";
                echo "<i class='fas fa-cog'></i> $table টেবিল ফিক্স করা হচ্ছে...";
                echo "</div>";
                
                // Fix empty emails
                $counter = 1;
                $empty_records = $pdo->query("SELECT id FROM $table WHERE email IS NULL OR email = ''")->fetchAll();
                
                foreach ($empty_records as $record) {
                    $unique_email = $table . '_' . $record['id'] . '_' . time() . '@temp.local';
                    $stmt = $pdo->prepare("UPDATE $table SET email = ? WHERE id = ?");
                    $stmt->execute([$unique_email, $record['id']]);
                }
                
                if (count($empty_records) > 0) {
                    echo "<div class='alert alert-success'>";
                    echo "<i class='fas fa-check'></i> $table: " . count($empty_records) . "টি খালি email ঠিক করা হয়েছে";
                    echo "</div>";
                }
                
                // Fix duplicate emails
                $duplicates = $pdo->query("
                    SELECT email, GROUP_CONCAT(id) as ids
                    FROM $table 
                    WHERE email IS NOT NULL AND email != '' 
                    GROUP BY email 
                    HAVING COUNT(*) > 1
                ")->fetchAll();
                
                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]]);
                    }
                }
                
                if (count($duplicates) > 0) {
                    echo "<div class='alert alert-success'>";
                    echo "<i class='fas fa-check'></i> $table: " . count($duplicates) . "টি duplicate email ঠিক করা হয়েছে";
                    echo "</div>";
                }
            }
            
            $pdo->commit();
            
            echo "<div class='alert alert-success'>";
            echo "<h6><i class='fas fa-thumbs-up'></i> অটো ফিক্স সম্পন্ন!</h6>";
            echo "<p>সব email duplicate সমস্যা সমাধান হয়েছে।</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 manual fix
    if (isset($_POST['manual_fix'])) {
        echo "<hr>";
        echo "<h5><i class='fas fa-cog'></i> ম্যানুয়াল ফিক্স চলছে...</h5>";
        
        try {
            foreach ($tables_to_check as $table) {
                // Check if table exists
                $table_exists = $pdo->query("SHOW TABLES LIKE '$table'")->fetch();
                if (!$table_exists) 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) continue;
                
                echo "<div class='alert alert-info'>";
                echo "<i class='fas fa-cog'></i> $table টেবিলের email constraints পরিবর্তন করা হচ্ছে...";
                echo "</div>";
                
                // Remove unique constraint on email if exists
                try {
                    $indexes = $pdo->query("SHOW INDEX FROM $table WHERE Column_name = 'email' AND Non_unique = 0")->fetchAll();
                    foreach ($indexes as $index) {
                        $pdo->exec("ALTER TABLE $table DROP INDEX " . $index['Key_name']);
                        echo "<div class='alert alert-success'>";
                        echo "<i class='fas fa-check'></i> $table: Unique constraint সরানো হয়েছে";
                        echo "</div>";
                    }
                } catch (Exception $e) {
                    // Index might not exist
                }
                
                // Make email nullable
                try {
                    $pdo->exec("ALTER TABLE $table MODIFY email VARCHAR(255) NULL");
                    echo "<div class='alert alert-success'>";
                    echo "<i class='fas fa-check'></i> $table: Email field nullable করা হয়েছে";
                    echo "</div>";
                } catch (Exception $e) {
                    echo "<div class='alert alert-warning'>";
                    echo "<i class='fas fa-exclamation-triangle'></i> $table: Email field modify করতে সমস্যা";
                    echo "</div>";
                }
            }
            
            echo "<div class='alert alert-success'>";
            echo "<h6><i class='fas fa-thumbs-up'></i> ম্যানুয়াল ফিক্স সম্পন্ন!</h6>";
            echo "<p>Email constraints সরানো হয়েছে। এখন duplicate email এর সমস্যা হবে না।</p>";
            echo "</div>";
            
        } catch (Exception $e) {
            echo "<div class='alert alert-danger'>";
            echo "<i class='fas fa-exclamation-triangle'></i> ম্যানুয়াল ফিক্স করতে সমস্যা: " . $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='classes/add.php' class='btn btn-primary w-100'>
                                    <i class='fas fa-plus'></i> ক্লাস যোগ করুন
                                </a>
                            </div>
                            <div class='col-md-4'>
                                <a href='students/add.php' class='btn btn-success w-100'>
                                    <i class='fas fa-user-plus'></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>";
?>