<?php
session_start();
require_once 'config/database.php';

// Check if user is admin
if (!isset($_SESSION['user_id']) || $_SESSION['user_role'] !== 'admin') {
    die('Access denied. Admin only.');
}

$action = $_GET['action'] ?? '';

if ($action === 'check') {
    echo "<h2>🔍 Database Collation Analysis</h2>";
    
    // Check database collation
    $stmt = $pdo->query("SELECT @@collation_database as db_collation");
    $db_info = $stmt->fetch();
    
    echo "<div style='background: #d1ecf1; padding: 15px; border: 1px solid #bee5eb; border-radius: 5px; margin-bottom: 20px;'>";
    echo "<h3>Database Collation: {$db_info['db_collation']}</h3>";
    echo "</div>";
    
    // Check table collations
    $stmt = $pdo->query("
        SELECT TABLE_NAME, TABLE_COLLATION 
        FROM information_schema.TABLES 
        WHERE TABLE_SCHEMA = DATABASE() 
        ORDER BY TABLE_NAME
    ");
    $tables = $stmt->fetchAll();
    
    echo "<h3>📋 Table Collations:</h3>";
    echo "<table border='1' style='border-collapse: collapse; width: 100%; margin-bottom: 20px;'>";
    echo "<tr style='background: #f8f9fa;'><th>Table Name</th><th>Collation</th><th>Status</th></tr>";
    
    $inconsistent_tables = [];
    foreach ($tables as $table) {
        $status = $table['TABLE_COLLATION'] === 'utf8mb4_unicode_ci' ? '✅ Good' : '⚠️ Needs Fix';
        $row_class = $table['TABLE_COLLATION'] === 'utf8mb4_unicode_ci' ? '' : 'style="background: #fff3cd;"';
        
        echo "<tr $row_class>";
        echo "<td>{$table['TABLE_NAME']}</td>";
        echo "<td>{$table['TABLE_COLLATION']}</td>";
        echo "<td>$status</td>";
        echo "</tr>";
        
        if ($table['TABLE_COLLATION'] !== 'utf8mb4_unicode_ci') {
            $inconsistent_tables[] = $table['TABLE_NAME'];
        }
    }
    echo "</table>";
    
    // Check column collations for important tables
    $important_tables = ['students', 'results', 'subjects', 'classes', 'subject_mark_distribution', 'mark_distributions'];
    
    echo "<h3>📝 Column Collations (Important Tables):</h3>";
    
    foreach ($important_tables as $table_name) {
        $stmt = $pdo->prepare("
            SELECT COLUMN_NAME, COLLATION_NAME 
            FROM information_schema.COLUMNS 
            WHERE TABLE_SCHEMA = DATABASE() 
            AND TABLE_NAME = ? 
            AND COLLATION_NAME IS NOT NULL
            ORDER BY ORDINAL_POSITION
        ");
        $stmt->execute([$table_name]);
        $columns = $stmt->fetchAll();
        
        if (!empty($columns)) {
            echo "<h4>Table: $table_name</h4>";
            echo "<table border='1' style='border-collapse: collapse; width: 100%; margin-bottom: 15px;'>";
            echo "<tr style='background: #f8f9fa;'><th>Column</th><th>Collation</th><th>Status</th></tr>";
            
            foreach ($columns as $column) {
                $status = $column['COLLATION_NAME'] === 'utf8mb4_unicode_ci' ? '✅ Good' : '⚠️ Needs Fix';
                $row_class = $column['COLLATION_NAME'] === 'utf8mb4_unicode_ci' ? '' : 'style="background: #fff3cd;"';
                
                echo "<tr $row_class>";
                echo "<td>{$column['COLUMN_NAME']}</td>";
                echo "<td>{$column['COLLATION_NAME']}</td>";
                echo "<td>$status</td>";
                echo "</tr>";
            }
            echo "</table>";
        }
    }
    
    // Summary and recommendations
    if (!empty($inconsistent_tables)) {
        echo "<div style='background: #fff3cd; padding: 20px; border: 1px solid #ffc107; border-radius: 5px; margin-top: 20px;'>";
        echo "<h3>⚠️ Collation Issues Found</h3>";
        echo "<p><strong>Tables with inconsistent collation:</strong> " . implode(', ', $inconsistent_tables) . "</p>";
        echo "<p><strong>Recommended Action:</strong> Convert all tables to utf8mb4_unicode_ci</p>";
        echo "<div style='text-align: center; margin-top: 15px;'>";
        echo "<a href='?action=fix' onclick='return confirm(\"This will modify your database structure. Are you sure?\")' ";
        echo "style='background: #dc3545; color: white; padding: 10px 20px; text-decoration: none; border-radius: 5px;'>🔧 Fix Collations</a>";
        echo "</div>";
        echo "</div>";
    } else {
        echo "<div style='background: #d1edff; padding: 20px; border: 1px solid #0d6efd; border-radius: 5px; margin-top: 20px;'>";
        echo "<h3>✅ All Good!</h3>";
        echo "<p>All tables are using consistent utf8mb4_unicode_ci collation.</p>";
        echo "</div>";
    }
    
} elseif ($action === 'fix') {
    echo "<h2>🔧 Fixing Database Collations...</h2>";
    
    try {
        // Set database collation
        $pdo->exec("ALTER DATABASE " . $pdo->query("SELECT DATABASE()")->fetchColumn() . " CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
        echo "<p>✅ Database collation updated</p>";
        
        // Get all tables
        $stmt = $pdo->query("SHOW TABLES");
        $tables = $stmt->fetchAll(PDO::FETCH_COLUMN);
        
        foreach ($tables as $table) {
            // Convert table collation
            $pdo->exec("ALTER TABLE `$table` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
            echo "<p>✅ Table '$table' converted to utf8mb4_unicode_ci</p>";
        }
        
        echo "<div style='background: #d1edff; padding: 20px; border: 1px solid #0d6efd; border-radius: 5px; margin-top: 20px;'>";
        echo "<h3>🎉 Collation Fix Complete!</h3>";
        echo "<p>All tables have been converted to utf8mb4_unicode_ci collation.</p>";
        echo "<p><strong>Next Steps:</strong></p>";
        echo "<ul>";
        echo "<li>Test your application to ensure everything works</li>";
        echo "<li>The marksheet collation error should now be resolved</li>";
        echo "<li>Consider restarting your web server</li>";
        echo "</ul>";
        echo "</div>";
        
    } catch (Exception $e) {
        echo "<div style='background: #f8d7da; padding: 20px; border: 1px solid #dc3545; border-radius: 5px;'>";
        echo "<h3>❌ Error During Fix</h3>";
        echo "<p><strong>Error:</strong> " . $e->getMessage() . "</p>";
        echo "<p><strong>Suggestion:</strong> Try running the fix again or contact your database administrator.</p>";
        echo "</div>";
    }
    
    echo "<p><a href='?action=check'>← Back to Analysis</a></p>";
    
} else {
    // Main menu
    echo "<h1>🔧 Database Collation Fix Tool</h1>";
    echo "<p>This tool helps fix collation mismatches that cause SQL errors.</p>";
    
    echo "<div style='background: #fff3cd; padding: 20px; border: 1px solid #ffc107; border-radius: 5px; margin-bottom: 20px;'>";
    echo "<h3>⚠️ About Collation Issues</h3>";
    echo "<p>The error you're seeing happens when different parts of your database use different collations:</p>";
    echo "<ul>";
    echo "<li><strong>utf8mb4_general_ci</strong> - Older, case-insensitive</li>";
    echo "<li><strong>utf8mb4_unicode_ci</strong> - Newer, better Unicode support</li>";
    echo "</ul>";
    echo "<p>When these mix in JOIN operations, MySQL throws a collation error.</p>";
    echo "</div>";
    
    echo "<div style='background: #f8d7da; padding: 20px; border: 1px solid #dc3545; border-radius: 5px; margin-bottom: 20px;'>";
    echo "<h3>🚨 Important Warning</h3>";
    echo "<ul>";
    echo "<li><strong>Backup your database</strong> before running any fixes</li>";
    echo "<li>This tool will <strong>modify your database structure</strong></li>";
    echo "<li>The process may take several minutes for large databases</li>";
    echo "<li>Your application may be temporarily unavailable during the fix</li>";
    echo "</ul>";
    echo "</div>";
    
    echo "<div style='text-align: center; margin: 30px 0;'>";
    echo "<a href='?action=check' style='background: #0d6efd; color: white; padding: 15px 30px; text-decoration: none; border-radius: 5px; font-size: 18px; font-weight: bold;'>";
    echo "🔍 Check Collations</a>";
    echo "</div>";
    
    echo "<div style='background: #d1ecf1; padding: 15px; border: 1px solid #bee5eb; border-radius: 5px;'>";
    echo "<h4>ℹ️ What this tool does:</h4>";
    echo "<ol>";
    echo "<li><strong>Analysis:</strong> Checks current collations across all tables</li>";
    echo "<li><strong>Identification:</strong> Finds inconsistent collations</li>";
    echo "<li><strong>Fix:</strong> Converts everything to utf8mb4_unicode_ci</li>";
    echo "<li><strong>Verification:</strong> Confirms the fix was successful</li>";
    echo "</ol>";
    echo "</div>";
}

echo "<hr>";
echo "<p><a href='reports/marksheet.php'>← Back to Marksheet</a> | <a href='index.php'>← Back to Dashboard</a></p>";
?>