<?php
/**
 * Special Access Links Collation Fix
 * Fixes collation issues specifically for Special Access Links feature
 * Safe to run on live server with minimal impact
 */

// 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 "=== Special Access Links Collation Fix ===\n\n";

try {
    $pdo = getDBConnection();

    // Tables used by Special Access Links feature
    $tables = ['special_access_links', 'migrations', 'registrations'];

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

    // Use database default if it's utf8mb4, otherwise use general_ci
    $targetCollation = (strpos($dbCollation, 'utf8mb4') !== false) ? $dbCollation : 'utf8mb4_general_ci';

    // Check if the issue is that some tables use different collations than the database default
    // In that case, we should standardize on the database default
    echo "Will standardize all Special Access Links tables to: {$targetCollation}\n\n";

    foreach ($tables as $table) {
        echo "Checking table: {$table}\n";

        // Check if table exists
        $stmt = $pdo->query("SHOW TABLES LIKE '{$table}'");
        if ($stmt->rowCount() == 0) {
            echo "⚠️  Table {$table} doesn't exist, skipping\n";
            continue;
        }

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

        if (strpos($createSql, 'COLLATE utf8mb4_unicode_ci') !== false) {
            echo "🔧 Converting {$table} to {$targetCollation}...\n";

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

            echo "✅ Fixed collation for {$table}\n";

        } elseif (strpos($createSql, 'COLLATE ' . $targetCollation) !== false) {
            echo "✅ {$table} already uses correct collation\n";

        } else {
            echo "ℹ️  {$table} uses different collation: {$dbCollation}\n";
        }
    }

    // Test the fix
    echo "\n🧪 Testing Special Access Links functionality...\n";

    try {
        // Test basic query
        $stmt = $pdo->query("SELECT COUNT(*) as count FROM special_access_links");
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        echo "✅ Can query special_access_links table. Records: {$result['count']}\n";

        // Test JOIN with registrations table (common cause of collation errors)
        $stmt = $pdo->query("
            SELECT COUNT(*) as count
            FROM special_access_links sal
            LEFT JOIN registrations r ON r.referral_code = sal.link_code
            LIMIT 1
        ");
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        echo "✅ JOIN query works (no collation error)\n";

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

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

    echo "\n🎉 Special Access Links collation fix completed!\n";
    echo "The feature should now work without collation errors.\n";
    echo "\nIf you still get errors, try running: php fix_live_collation.php\n";

} catch (Exception $e) {
    echo "❌ Error: " . $e->getMessage() . "\n";
    echo "\nTroubleshooting:\n";
    echo "1. Check database user permissions (needs ALTER privilege)\n";
    echo "2. Ensure database is not in read-only mode\n";
    echo "3. Verify database connection settings\n";
    exit(1);
}
?>
