<?php
/**
 * Test the specific query that's causing the collation error
 */

require_once 'db_config.php';

echo "=== Testing Specific Query ===\n\n";

try {
    $pdo = getDBConnection();

    // This is the exact query from special-access-links.php that's causing the error
    $query = "
        SELECT
            sal.*,
            au.full_name as creator_name,
            COUNT(DISTINCT r.id) as actual_conversions
        FROM special_access_links sal
        LEFT JOIN admin_users au ON sal.created_by = au.id
        LEFT JOIN registrations r ON r.referral_code = sal.link_code AND r.payment_status IN ('paid', 'pending')
        GROUP BY sal.id
        ORDER BY sal.created_at DESC
    ";

    echo "Testing query:\n{$query}\n\n";

    $stmt = $pdo->query($query);
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

    echo "✅ Query executed successfully!\n";
    echo "Results: " . count($results) . " links found\n";

    if (count($results) > 0) {
        echo "\nFirst result sample:\n";
        print_r(array_slice($results, 0, 1));
    }

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

    // Check table collations
    echo "\n=== Checking Table Collations ===\n";

    $tables = ['special_access_links', 'registrations', 'admin_users'];

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

            if (strpos($createSql, 'COLLATE utf8mb4_general_ci') !== false) {
                echo "✅ {$table}: utf8mb4_general_ci\n";
            } elseif (strpos($createSql, 'COLLATE utf8mb4_unicode_ci') !== false) {
                echo "⚠️  {$table}: utf8mb4_unicode_ci\n";
            } else {
                echo "❓ {$table}: unknown collation\n";
            }
        } catch (Exception $e2) {
            echo "❌ {$table}: Error checking - " . $e2->getMessage() . "\n";
        }
    }

    echo "\nRun: php fix_sal_collation.php\n";
}
?>
