<?php
/**
 * Live Server Collation Fix
 * Fixes collation mismatches on live server
 * Run this script on your live server to fix the collation issue
 */

// Prevent web access - only allow CLI execution
if (isset($_SERVER['HTTP_HOST'])) {
    die('This script can only be run from command line.');
}

require_once 'db_config.php';

echo "=== Live Server Collation Fix ===\n\n";

try {
    $pdo = getDBConnection();

    // Get database name
    $dbName = $pdo->query('SELECT DATABASE()')->fetchColumn();
    echo "Database: {$dbName}\n\n";

    // Check database default collation
    $stmt = $pdo->query("SELECT @@collation_database as collation");
    $dbCollation = $stmt->fetchColumn();
    echo "Database default collation: {$dbCollation}\n";

    // Determine target collation (use database default if it's utf8mb4, otherwise use utf8mb4_general_ci)
    if (strpos($dbCollation, 'utf8mb4') !== false) {
        $targetCollation = $dbCollation;
        echo "Using database default collation: {$targetCollation}\n\n";
    } else {
        $targetCollation = 'utf8mb4_general_ci';
        echo "Database doesn't use utf8mb4, using: {$targetCollation}\n\n";
    }

    // Get all tables in the database
    $stmt = $pdo->query("SHOW TABLES");
    $tables = $stmt->fetchAll(PDO::FETCH_COLUMN);

    echo "Found " . count($tables) . " tables. Checking collations...\n\n";

    $fixedTables = 0;
    $alreadyCorrect = 0;

    foreach ($tables as $table) {
        // Get table collation
        $stmt = $pdo->query("SHOW CREATE TABLE `{$table}`");
        $createTable = $stmt->fetch(PDO::FETCH_ASSOC);
        $createSql = $createTable['Create Table'];

        // Check if table uses wrong collation
        if (strpos($createSql, 'COLLATE utf8mb4_unicode_ci') !== false ||
            (strpos($createSql, 'COLLATE') === false && strpos($createSql, 'utf8mb4') !== false)) {

            echo "Fixing table: {$table}\n";

            // Convert table to correct collation
            $alterSql = "ALTER TABLE `{$table}` CONVERT TO CHARACTER SET utf8mb4 COLLATE {$targetCollation}";
            $pdo->exec($alterSql);

            echo "✓ Converted {$table} to {$targetCollation}\n";
            $fixedTables++;

        } else {
            $alreadyCorrect++;
        }
    }

    echo "\n=== Summary ===\n";
    echo "Tables fixed: {$fixedTables}\n";
    echo "Tables already correct: {$alreadyCorrect}\n";
    echo "Total tables: " . count($tables) . "\n\n";

    // Test the fix with a simple query
    echo "Testing fix with sample query...\n";
    try {
        $stmt = $pdo->query("SELECT COUNT(*) as count FROM special_access_links");
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        echo "✅ Query successful. Records: {$result['count']}\n";

        // Test string comparison
        $stmt = $pdo->prepare("SELECT COUNT(*) as count FROM special_access_links WHERE link_name = ?");
        $stmt->execute(['test']);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        echo "✅ String comparison works. Matching records: {$result['count']}\n";

    } catch (Exception $e) {
        echo "❌ Query test failed: " . $e->getMessage() . "\n";
        exit(1);
    }

    echo "\n🎉 Collation fix completed successfully!\n";
    echo "The Special Access Links feature should now work without collation errors.\n";

} catch (Exception $e) {
    echo "❌ Error: " . $e->getMessage() . "\n";
    echo "\nTroubleshooting:\n";
    echo "1. Make sure you have ALTER privileges on the database\n";
    echo "2. Check that the database user can modify table structures\n";
    echo "3. Ensure the database is not in read-only mode\n";
    exit(1);
}
?>
